2. Databases and Queries

What We Will Cover

Elucidations

Homework Questions?

Quiz Questions?

The term database usually refers to a software application that consists of which of the following?

Files used to store physical data.
An application for managing and accessing certain files.
Both A and B.
None of the above.

Questions from last class?

^ top

2.1: Introduction to Databases and SQL

Objectives

At the end of the lesson the student will be able to:

  • Discuss the organization of relational databases
  • Create and drop databases
  • Import and export data

^ top

2.1.1: About Databases

  • Recall that a database is an organized collection of logically-related data
    • Data are numbers, characters, sounds or images that can be stored in a computer for processing or transmitting
    • Data is organized so a computer can quickly select desired pieces of data
  • Databases have several advantages for storing data
    • Faster filing and retrieval of records
    • Flexible retrieval order
    • Flexible output format
    • Easier changes to data
    • Increased security
  • Database Management Systems (DBMS) provide the tools to work with databases
    • Creating and removing databases
    • Adding, deleting and updating data
    • Controlling user access to data
  • We will use phpMyAdmin to interface with the MySQL DBMS
  • phpMyAdmin, on our computers, located at: localhost/phpmyadmin
  • Note that phpMyAdmin can be set to use any one of 44 languages

^ top

2.1.2: Relational Database Concepts

  • MySQL is a relational DBMS
    • Relational databases devised by Dr. E. F. Codd in early 1970s
    • Developed theory underlying all relational database systems in use today
  • All data is stored in tables
  • Each type of data is usually stored in one table, for example:
    • Customers table
    • Addresses table
    • Products table
    • Orders table
    • etc.
  • Each table has one or more columns (fields) and any number of rows
  • Columns represent attributes -- data we want to save about the object
  • Each row holds data for one customer, address, product, order, etc.
  • For example, for Products we may decide on a table containing:

ProductID ProductName ProductDescription Price InStock
 1  Canvas  Good canvas for quality paint  22.50  30
 2  Brush, Big  Big brush for large areas  4.75  47
 3  Brush, Small  Small brush of fine material  3.75  34

  • Most tables need a primary key that provides a unique identifier for a row
  • In this example, we might choose ProductID as a primary key

^ top

2.1.3: About Structured Query Language

  • Structured Query Language (SQL) is standard language for communicating with databases
  • SQL pronounced as "es-que-el" or "sequel"
  • Easy to use, readable and powerful
  • SQL is not a complete programming language
    • For instance, cannot branch or repeat sections of code
    • However, many variations of SQL have added such statements
  • SQL derived from SEQUEL-2 released in 1976 by IBM
  • SQL first standardized by the American National Standards Institute (ANSI) in 1986
  • ANSI is a voluntary standardization and conformity assessment system (see About ANSI)
  • Most databases claim at least ANSI SQL-92 conformance
  • Most recently defined standard is INCITS/ISO/IEC 9075-1-1999 (A.K.A. SQL-99)
  • Most databases do not follow the standard exactly
  • Common variants include:

^ top

2.1.4: Creating and Selecting Databases

  • A DBMS can manage several databases
  • We select the SQL tab of the test database
  • Can create a new database by just entering a name in the phpMyAdmin text field
    • samp_db
  • Left menu of phpMyAdmin allows you to select which database to use
  • You can remove an existing database by clicking the Drop tab

Creating Databases with SQL

  • You can create databases using SQL as well
    • CREATE DATABASE samp_db;
  • Or remove (drop) databases using SQL
    • DROP DATABASE samp_db;
  • Note that phpMyAdmin will not let you drop databases using SQL
    • You must use the Drop tab
    Warning: Do NOT drop the mysql database!
  • Most Web applications do not create or remove databases
  • Most applications add, change and delete data in existing databases

^ top

2.1.5: Importing and Exporting Data

  • MySQL allows you to import and export data using SQL statements
  • You can import and export entire databases
  • We will use phpMyAdmin to import and export data

Importing Data

  1. Before importing data or databases, first create a new database or select an existing database.

    2. You should see a form with several folder tabs at the top. One of the tabs should be labeled SQL.

  2. Select the SQL tab.

    3. You should see a form with a large text area and a smaller text field with an adjacent Browse... button.

  3. Enter the commands to import data by either
    • Cutting and pasting into the text area, or
    • Pressing the Browse... button and selecting a file.
  4. Press the Go button to execute the commands.

    5. You will see a message either confirming your updates or reporting an error.

Exporting Data

  1. Before exporting data, first select a database.

    2. You should see a form with several folder tabs at the top. One of the tabs should be labeled Export.

  2. Select the Export tab

    3. You should see a form labeled View dump (schema) of database.

  3. Choose the options you are interested in and view the results. If you want to export the entire database, select the following options:
    • In the Export area, press Select All.
    • In the Structure area, check the Add 'drop table' checkbox.
  4. If you want to save the data to a file, check the Save as file checkbox.
  5. Press the Go button to execute the commands.

    6. If you are saving to a file, you will see a download dialog. Follow the instructions and save the file to the desired location, such as your home directory.

^ top

2.1.6: Summary

  • MySQL is a Database Management System and can control several databases
  • phpMyAdmin provides a Web-based user-interface for MySQL
  • MySQL is a relational DBMS
  • All data is stored in tables
  • Columns are used for different types of information
  • Each row contains the data for one thing in the database
  • Structured Query Language (SQL) is the standard language for communicating with databases
  • You can create databases using the SQL statement:
    • CREATE DATABASE samp_db;
  • Also, you can drop databases using SQL:
    • DROP DATABASE samp_db;
  • phpMyAdmin will generate some SQL automatically
  • MySQL provides commands to import and export data
  • phpMyAdmin will generate this SQL automatically
    • You will explore this in the next exercise

Check Yourself

  1. What is meant by the term "database management system"?
  2. What is the language used to query a database?
  3. What is the logical storage structure used by relational databases?
  4. How is a CREATE DATABASE statement coded?
  5. How is a DROP DATABASE statement coded?

^ top

Exercise 2.1

With your partner, take 10 minutes to complete the following:

  1. Start a text file named exercise2.txt.
    You will be adding to this file during the lesson, so save it often.
  2. Prepare the exercise header as described in the HowTo on submitting exercises
  3. Label this exercise: Exercise 2.1
  4. Do not submit exercises until all from today's lesson are finished
  5. Complete the following exercises and answer any questions.

Exercises and Questions

  1. Open a Web browser and enter localhost.phpmyadmin in the address field.

    2. You should see phpMyAdmin running in the browser window.

  2. Create and select a database named "artzy".
  3. Download artzy.txt into a convenient location such as your home directory.
  4. Import the Artzy database into MySQL.

    5. You can either copy and paste or import the artzy.txt file. Verify you get a message indicating success.

  5. Verify the import by selecting the artzy database and viewing the list of tables.
  6. Record the number of tables you see in your exercise file.
  7. Export the artzy database and save the file as "artzy_export.txt".
  8. Submit the artzy_export.txt file along with your exercise2.txt file after you complete today's exercises.

^ top

2.2: Single-Table Queries

Objectives

At the end of the lesson the student will be able to:

  • Make simple and conditional queries
  • Sort records retrieved by a query
  • Limit query results to a certain number of records

^ top

2.2.1: About Queries

  • You use SELECT statements to retrieve records from a database
  • Most of your SQL coding will use the SELECT command
  • Main clauses of SELECT statement we will cover today:
    • SELECT [DISTINCT] columnNames
FROM tableNames
[ WHERE conditions ]
[ ORDER BY columnList [ ASC | DESC ] ]
[ LIMIT numRows ];
  • Note that clauses in square brackets [ ] are optional
  • Will cover GROUP BY and HAVING later

Processing Queries

  • To prepare for making queries, open localhost/phpMyAdmin
  • Select the Artzy database and the SQL tab
  • You can type queries in the form's text area
  • Also, you can make queries that are stored in text files by using the Browse... button

^ top

2.2.2: Making Simple Queries

  • Query: a question represented so a DBMS can understand it
  • To use SQL, you first decide what data you want
  • Then you write a SQL query to extract the data from the database

Retrieving Certain Columns and All Rows

  • You can retrieve all rows with specified columns from a table using SELECT with a FROM clause
    • For Example:

    List the product number, product name and price for every product.

  • Translating the query into SQL:
    • SELECT ID, ProductName, Price
FROM products;

Retrieving All Columns and All Rows

  • You can retrieve all columns and rows from a table using SELECT and FROM clauses and the * wildcard
    • For Example:

    List the complete Products table.

  • Translating the query into SQL:
    • SELECT *
FROM products;

Performing Calculations

  • You can perform arithmetic on columns if they contain numerical data
    • For Example:

    List the order number and the total dollar value for every order item.

    SELECT PriceEach, Quantity, (PriceEach * Quantity)
FROM orderitems;

^ top

2.2.3: Making Conditional Queries

  • You can retrieve records that satisfy some condition using the WHERE clause
  • Also, you can use logical and relational operators for more complex queries

WHERE conditions

  • Allows selecting only records that meet specific criteria
  • Conditions are boolean expression which each record must satisfy
    • WHERE ID = 2
  • Here only records with the ID column set to 2 are retrieved
  • SQL supports these relational operators: >, >=, =, <>, <, <=
  • For example:
    • WHERE Price < 10

Logical Operators

  • You may use logical operators: AND, OR, NOT
  • Logical expressions evaluate to TRUE, FALSE or NULL
    • NULL means "unknown"
  • For example:
    • SELECT ProductName
  FROM products
  WHERE ID > 1
  AND ID  < 5

Common Mistake with Boolean Expressions

  • Boolean expressions often read like "normal" English
  • However, SQL syntax requires more precision
  • For example we might say in English: ID equals 2 or 4
  • However the corresponding boolean expression in SQL is: ID = 2 OR ID = 4

Using IN and BETWEEN

  • The IN operator allows an item to be tested against a list of values
    • WHERE ProductName IN ('Canvas', 'Oil Paint')
  • The BETWEEN operator is convenient for checking ranges
  • Checks column value is BETWEEN min AND max values (inclusive)
    • WHERE ID BETWEEN 2 AND 4

Using LIKE

  • Most of the time we use exact matches for retrieving records
  • But some times we need to be a little vague
  • You can use LIKE with a wildcard symbols where:
    • _ means any single character
    • % means any sequence of characters before or after characters specified
  • Note that a string must be enclosed in quotes (')
    • You can use double quotes but I recommend you use single quotes instead
    • This will save you from making some errors when we use PHP with SQL,
    For Example:

    List the product name and price for every product whose name contains the letters 'us'.

  • Translating the query into SQL:
    • SELECT ID, ProductName, Price
FROM products
WHERE ProductName LIKE '%us%';

^ top

2.2.4: Eliminating Duplicates With DISTINCT

  • By default, that result of SELECT can contain duplicate records
  • You use DISTINCT to discard any duplicate records
    • For Example:
    1. First, list the supplier numbers for every product in the Products table.
      2. SELECT SupplierID
FROM products;
    2. Then, list the DISTINCT supplier numbers for every product in the Products table
      3. SELECT DISTINCT SupplierID
FROM products;

^ top

2.2.5: Sorting Results

  • A database may return rows in any order
  • You can specify records in sorted order for any column
  • A column specified for sorting is called the sort key
  • To sort, use the ORDER BY clause followed by a sort key
    • For Example

    List the product number, product name, price and quantity in stock for every product. Order the output in ascending order by product name.

  • Translating the query into SQL:
    • SELECT ID, ProductName, Price, InStock
FROM products
ORDER BY ProductName

Sorting With Multiple Keys

  • When sorting on more than one column, must specify both primary column and secondary column(s)
  • List columns in order of importance from left to right
  • Can also specify descending order using DESC clause

    List the product number, product name, supplier number and quantity in stock for every product. Order the output in descending order by supplier number and ascending order by product name.

  • Translating the query into SQL:
    • SELECT ID, ProductName, SupplierID, InStock
FROM products
ORDER BY SupplierID DESC, ProductName

^ top

2.2.6: Limiting Query Results

  • Another MySQL clause is LIMIT
    • Not standard SQL
  • Restricts how many records to return
  • For Example:
    • SELECT *
FROM products
LIMIT 1;
  • Only the first record of the query will be returned
  • Another example:
    • SELECT *
FROM products
LIMIT 2, 3;
  • Only three records will be returned starting with the second

^ top

2.2.7: Summary

  • You use the SELECT statement to retrieve records from a database
    • SELECT [DISTINCT] columnNames
FROM tableNames
[ WHERE conditions ]
[ ORDER BY columnList [ ASC | DESC ] ]
[ LIMIT numRows ];
  • * is a wildcard for all columns
  • Use the WHERE clause to restrict the rows to those matching the conditions
  • Use DISTINCT to discard any duplicate records
  • Use ORDER BY to sort the rows in ascending (ASC) or descending (DESC) order
  • Use LIMIT to restrict the number of rows returned

Check Yourself

  1. How is a SELECT statement coded?
  2. In a SELECT statement, how do you remove duplicate rows in the result set?
  3. In a SELECT statement, how do you sort rows in the result set?
  4. In a SELECT statement, how do you restrict the rows returned?

^ top

Exercise 2.2

Instructions:

Use the next 10 minutes to complete the following.

  1. Label this exercise: Exercise 2.2
  2. Do not submit exercises until all of them from today's lesson are finished
  3. Develop queries for the following problems:

Problems

  1. List the product name and price of every product in the Products table. Order the output in ascending order by price.
  2. List the product name, price and number of units in stock for every product that currently has a low stock of less than 25 units.
  3. List all the customer names, first and last, sorted by their last name but only display the first two distinct customers.

^ top

2.3: Multi-Table Queries

Objectives

At the end of the lesson the student will be able to:

  • Join two tables in one SQL SELECT statement
  • Join many tables in one SQL SELECT statement
  • Use OUTER JOINs
  • You often need to use data from more than one table
  • SQL lets you SELECT two or more tables with one command

^ top

2.3.1: Joining Two Tables

  • A Join allow you to retrieve data from two or more tables
  • If you join two tables without any restrictions, you get what is known as the cartesian product

Cartesion Product Example

  • Lets say you need data from two tables like the following:

    • List all the product names and the product's supplier name.

  • You might be tempted to use a query like:
    • SELECT ProductName, SupplierName
FROM products, suppliers
  • If you run this query, you probably do NOT get what you want
  • To get only the conditions you want, you need to restrict the query in some way
  • A properly designed database will provide you with the key columns you need in each table
  • Thus, you need to match identical values in some columns of the two tables

Join Example

  • Let us consider a slightly more complicated example:

    • List the product ID, product name and its related supplier ID and company name for every product.

  • You need ID and ProductName from products table
  • Also, you need a SupplierID and SupplierName from suppliers table
  • You must join two tables in same SQL command to retrieve the data you need
  • The general procedure to join two tables is:
    1. Indicate in SELECT clause all columns to display
    2. List in the FROM clause all tables involved in query
    3. Specify condition(s) in the WHERE clause to restrict data to only rows that have common values in matching columns
  • Translating the query into SQL:
    • SELECT products.ID, ProductName,
  suppliers.ID, SupplierName
FROM products, suppliers
WHERE products.SupplierID = suppliers.ID;
  • Note that you can have problem joining different tables because they may have the same column names
  • You must qualify ambiguous column names using dot notation
  • To do this, include both the table and column name separated by a dot (.)
  • You must qualify ambiguous column names using dot notation
  • However, you can qualify column names even if no ambiguity exists

A More Complex Join Example

    List the product name, selling price and its related supplier name for every product that has a selling price of more than 5 dollars.

  • You must relate products and supplier as before
  • However, in this query you have a new condition to restrict the selling price
  • You need to restrict the rows selected both for a join and meet the extra criteria
  • To do this, you need to use a compound condition: AND
  • Translating the query into SQL:
    • SELECT ProductName, Price, SupplierName
FROM products, suppliers
WHERE products.SupplierID = suppliers.ID
AND Price > 5.00;

^ top

2.3.2: Joining Many Tables

  • Joining more than two tables is no more difficult than joining two tables
  • For each pair of tables joined you include a condition for how columns are related
  • Proceed as if joining two tables at a time with an AND between each condition
    • SELECT fields
  FROM table1, table2, table3, ...
  WHERE table1.key1 = table2.commonKey1
  AND table2.key2 = table3.commonKey2
  ...;

Multi-table Join Example

    List the product name, date ordered, quantity ordered and supplier name for every product currently on order from a supplier.

  • Data is contained in three different tables
  • One could start query with:
    • SELECT ProductName,DateOrdered,Qty,SupplierName
FROM products,purchOrders,suppliers;
  • Not sufficient since we need to relate Products to POs and Products to Suppliers
  • You generally need one join condition for each pair of tables
    • SELECT ProductName, DateOrdered, Qty, SupplierName
FROM products, purchorders, suppliers
WHERE products.ID = purchorders.ProductID
AND products.SupplierID = suppliers.ID;
  • Why did we need two conditions for this query?
  • You may need additional conditions if query is more restrictive
  • What additional condition could we add to the query to only show products where the quantity on order is more than 20?

^ top

2.3.3: Making OUTER JOINs

  • OUTER JOINS can return more information than normal (inner) joins
    • Information not matched by a conditional clause
  • OUTER JOINS include LEFT JOIN and RIGHT JOIN
  • They have a slightly different syntax than "natural" joins
    • tableNameOne LEFT | RIGHT [OUTER] JOIN tableNameTwo
  ON condition
  • The ON condition is the condition used in WHERE clauses that are not related to the joins
  • We will look at some examples of each

LEFT JOIN

  • For a LEFT JOIN, all the data from the left table is returned even if there are no matches in the right table
  • Any missing value in the right table is shown as NULL
    • For Example

    List the supplier name and product name for every supplier. Show all the products even if a supplier does not exist for the product.

  • Since we need to show records where values may not exist, will need an OUTER JOIN
  • Since table on left (suppliers) may have values with no corresponding value for table on right (products), need to use LEFT JOIN 
    • SELECT SupplierName, ProductName
FROM suppliers LEFT JOIN products
ON suppliers.ID = products.SupplierID;
  • Why does a NULL cell appear in the query?

RIGHT JOIN

  • RIGHT JOIN works analogously to LEFT JOIN
  • Returns all rows from right table even if no matches in left table
  • Any missing value in the left table is shown as NULL or blank
    • For Example

    List the supplier names and product name for every product that does not have a supplier.

  • Here we want only those rows with a NULL value returned
  • You need to add another condition using a WHERE clause
    • SELECT SupplierName, ProductName
FROM suppliers RIGHT JOIN products
ON suppliers.ID = products.SupplierID
WHERE SupplierName IS NULL;

Further Information

^ top

2.3.4: Using an Alias

    Alias: an alternate name

  • You can use an alias for column names and table names
  • Each use has a different purpose

Column Alias

  • Column aliases exist to help clarify output
    • SELECT columnName AS aliasName FROM tableName
  • Consider the query:
    • SELECT customers.ID, customers.LName, customers.FName,
  orders.ID, productID, OrderDate
FROM customers, orders, orderitems
WHERE customers.ID = orders.CustomerID
AND orders.ID = orderitems.OrderID
ORDER BY orders.ID;
  • There is some ambiguity in the output due to repeating column names
  • Assign column-name aliases to clarify column meanings:
    • SELECT customers.ID AS CustID,
  customers.LName, customers.FName,
  orders.ID AS OID, productID AS PID, OrderDate
FROM customers, orders, orderitems
WHERE customers.ID = orders.CustomerID
AND orders.ID = orderitems.OrderID
ORDER BY orders.ID;

Table Alias

  • Tables listed in FROM clause also can be given an alias
    • SELECT column FROM table [AS] alias
  • Table alias must be used throughout query
    • SELECT C.ID AS CustID, O.ID AS OID, C.LName, C.FName,
  productID AS PID, OrderDate
FROM customers C, orders O, orderitems OI
WHERE C.ID = O.CustomerID
AND O.ID = OI.OrderID
ORDER BY O.ID;
  • Convenient when using data from multiple tables

^ top

2.3.5: Summary

  • You can join multiple tables by listing them in the FROM clause
  • Must be sure to provide a condition in the WHERE clause to remove unwanted combinations
  • Key is to find identical values in matching columns of the two tables
  • Many types of joins are possible
  • LEFT JOIN inserts NULL for rows without matching values on the left
  • RIGHT JOIN inserts NULL for rows without matching values on the right
  • You can use a column alias to change the names of columns displayed
  • Also, you can use a table alias within a query

Check Yourself

  1. What is meant by the term "join"?
  2. How do you join two tables in a SELECT statement?

^ top

Exercise 2.3

Use the next 10 minutes to develop the following query. Record your query in your exercise2.txt file.

Live and Let Buy

List the customer ID, product name, date ordered, quantity ordered and price paid for every product ordered by customer James Bond. Order the results in ascending order by date and then by product name.

The query should produce results similar to the following:

ID ProductName OrderDate Quantity PriceEach
 1  Canvas  2002-02-01  1  22.50
 1  Brush, Small  2002-02-03  1  4.75
 1  Oil Paint  2002-02-03  10  1.00

^ top

Wrap Up

^ top

Home | WebCT | Announcements | Course info | Expectations | Schedule
Project | Help | FAQ's | HowTo's | Links

Last Updated: February 15 2006 @17:36:10