2. Databases and Queries

What We Will Cover Reminders: Please silence all cell phones and pagers When class is over, please shut down your computer if it is on Elucidations 2.1: Introduction to Databases and SQL 2.1.1: About Databases 2.1.2: Relational Database Concepts 2.1.3: About Structured Query Language 2.1.4: Creating and Selecting Databases 2.1.5: Importing and Exporting Data 2.1.6: Summary Exercise 2.1 2.2: Single-Table Queries 2.2.1: About Queries 2.2.2: Making Simple Queries 2.2.3: Making Conditional Queries 2.2.4: Pattern Matching with LIKE 2.2.5: Eliminating Duplicates With DISTINCT 2.2.6: Sorting Results 2.2.7: Limiting Query Results 2.2.8: Summary Exercise 2.2 2.3: Multi-Table Queries 2.3.1: Joining Two Tables 2.3.2: Joining Many Tables 2.3.3: Using the JOIN Clause 2.3.4: Using an Alias 2.3.5: Summary Exercise 2.3 Wrap Up Due Next: A2-SQL Queries (3/1/10) Quiz 2 and Discussion 2 (3/1/10) Elucidations Homework Questions? A1-Exploring Database-driven Web Sites (2/22/10) Quiz 1 and Discussion 1 (2/22/10) Quiz Questions? Login to Blackboard and go to the course homepage For a quick view, click the "My Grades" link on the left menu To view your answers and compare them to the correct answers: Click the "Asessments" link on the left menu Click the View All Submissions button For the assessment you want to review, click the hyperlink under the Attempt column. Questions from last class? What to do when Blackboard has problems Room Policies On a piece of paper and spend one minute writing the following lists: What are three things you choose to do? What are three things you have to do? Making choices ^ top 2.1: Introduction to Databases and SQL Learner Outcomes 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 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, is located at: localhost/phpmyadmin Note that phpMyAdmin is NOT a database management system Rather, phpMyAdmin is a webapp that provides a user interface to a MySQL DBMS ^ top 2.1.2: Relational Database Concepts MySQL is a relational DBMS Relational databases were devised by Dr. E. F. Codd in early 1970s Developed the underlying theory of 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 the standard language for communicating with databases SQL is pronounced either as "es-que-el" or "sequel" SQL is a declarative language and does not have some statements you may expect For instance, it does not have if-statements or looping statements However, many variations of SQL have added such statements SQL is a descendant of SEQUEL-2, released by IBM in 1976 SQL was 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 The most recently defined standard is ISO/IEC 9075 with several parts, collectively known as SQL-2008 Most databases do not follow the standard exactly Common variants include: DB2: IBM Jet SQL: Access PL/SQL: Oracle T-SQL: MS SQL Server, Sybase More Information SQL: from Wikipedia ^ top 2.1.4: Creating, Selecting and Deleting Databases A DBMS can manage several databases You can see how to create new databases using phpMyAdmin Looking around, you will see a text field labeled: Create new database You create a new database by just entering a name in the phpMyAdmin text field For instance: samp_db The menu on the left lets you select which database to use You can delete an existing database by clicking the Drop tab Creating Databases with SQL Statements You can create databases using SQL as well To do so, select the test database and then click the SQL tab In the large text area, enter a SQL statement like the following: CREATE DATABASE samp_db; You can remove (drop) databases using SQL statements as well: DROP DATABASE samp_db; Most Web applications do not create or remove databases Instead, most applications add, change and delete data in an existing database Extra Bits: Character Sets and Collations You may be curious about the Collation options when you create a database MySQL keeps track of character sets and collations for every database so it can be used in various languages around the world A character set is a set of symbols and encodings A collation is a set of rules for comparing characters in a character set For example, the English language (like other romance languages) uses the latin alphabet: A-B-C-D-E-F-G-H-I-J-K-L-M-N-O-P-Q-R-S-T-U-V-W-X-Y-Z The collation rules for the latin alphabet are the well-known alphabetical order That is why you may see a Collation type of latin1_general_ci This is the setting for West European (multilingual), case-insensitive Since English is a Western Eurpean romance language, this setting works well for us The MySQL server can support multiple character sets Whenever you create or alter a database, you can specify an optional character set and collation If you do not specify a character set or collation, then the server default character set and collation is used ^ 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 full of information We will use a database file named artzy.txt to demonstrate how to import a database Note that more database files like this have a suffix of .sql rather than .txt Importing Data Before importing data or databases, first create a new database or select an existing database After creating the database, you should see a form with several folder tabs at the top. One of the tabs should be labeled Import. Select the Import tab The Import form lets you to import SQL statements from a file. Select the text file with the browse button. Then press the Go button to execute the commands in the file. You will see a message either confirming your updates or reporting an error. An alternative is to use the SQL tab and paste statements into the large text area. Exporting Data Before exporting data, first select a database. You should see a form with several folder tabs at the top. One of the tabs should be labeled Export. Click the Export tab to open the View dump (schema) of database page. On this page, verify: In the Export area, all the tables are selected (Select All) Also in the Export area, select SQL as the type of export In the Options area, use SQL compatibility mode of MYSQL40 If you want to save the data to a file, check the Save as file checkbox located toward the bottom of the page. Also, enter a filename in the field labeled, "File name template". Once you have verified the settings, press the Go button in the bottom right corner of the page. 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 MySQL is a relational DBMS A relational database stores all data in tables Each column stores attributes about a data object Each row contains the data for one object in the database Structured Query Language (SQL) is the standard language for communicating with databases You can create databases using a SQL statement like: CREATE DATABASE samp_db Also, you can drop databases using a SQL statement like: DROP DATABASE samp_db phpMyAdmin provides a Web-based user-interface for MySQL Using phpMyAdmin, you can send SQL statements to MySQL and display the data from queries In addition, phpMyAdmin will generate some SQL automatically One of the things that phpMyAdmin helps with is importing and exporting data You will need to import and export data many times while developing your database-driven Web site Check Yourself What is meant by the term "database management system"? What is the logical storage structure used by relational databases? What is the name of the language used to query a database? What are the steps for creating a database using phpMyAdmin? How do you select an existing database using phpMyAdmin? What are the steps for importing data into a database using phpMyAdmin? What are the steps for exporting data from a database using phpMyAdmin? How do you delete an existing database using phpMyAdmin? What is the SQL statement to create a database name "foo"? What is the SQL statement to delete a database name "foo"? ^ top Exercise 2.1 In this exercise, we create a database and import data into the newly created database. Specifications Download artzy.txt and save it in a convenient location such as the Desktop. You can view the text file in a text editor, such as TextPad, if you are curious. Open the XAMPP Control Panel by double-clicking the icon on the desktop or by using the Apache Friends entry in the start menu. Start the Apache and MySQL modules, if they are not already running. Open a new Web browser tab or window and enter localhost/phpmyadmin in the address field. You should see phpMyAdmin running in the browser window. In the Create new database textfield, enter the name "artzy" (without the quotes), and press the Create button. The browser will open a new page and you will see the message, "Database artzy has been created." Click the Import tab to open the Import page. In the File to import section of the Import page, press the Browse button and locate the artzy.txt file on the Desktop. Select the artzy.txt file and press the Open button. In the bottom of the Import page, press the Go button. You will see a message, "Import has been successfully finished..." Verify the import by selecting the artzy database and viewing the list of tables. Click the Export tab to open the View dump (schema) of database page. On this page, verify: In the Export area, all the tables are selected (Select All) Also in the Export area, select SQL as the type of export In the Options area, use SQL compatibility mode of MYSQL40 In the Structure area check both checkboxes for "Add DROP TABLE / DROP VIEW" and "Add IF NOT EXISTS" Toward the bottom of the page, check the Save as file checkbox For the "File name template" use: __DB__%y%m%d Once you have verified the settings, press the Go button in the bottom right corner of the page. Then save the file to a convenient location like the Desktop. Submit the artzy SQL file you downloaded to Blackboard as part of assignment 2. As time permits, be prepared to answer the Check Yourself questions in the section: 2.1.6: Summary. ^ top 2.2: Single-Table Queries Learner Outcomes 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 Now that we have a database loaded into MySQL, we can create queries A database query is a question you ask of a database To create a query with SQL, we use the SELECT statement Most of your SQL coding will use the SELECT command We will cover the following clauses of the SELECT statement today: SELECT [DISTINCT] columnNames FROM tableNames [ WHERE conditions ] [ ORDER BY columnList [ ASC | DESC ] ] [ LIMIT [offset] numberOfRows ]; Note that clauses in square brackets [ ] are optional Will cover the optional clauses GROUP BY and HAVING later in the course Getting Ready to Ask Questions 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 In this section we look at how to make simple SELECT queries To use the SELECT statement, you need to know: What columns a table has What data you want from the columns These queries take the form SELECT columnNames FROM tableNames Let us look at some examples to see how these queries work Retrieving Certain Columns and All Rows You can retrieve all rows with certain columns from a table For example, assume you want to: List the product number, product name and price for every product. Translating the query into the SQL statement: SELECT ID, ProductName, Price FROM products Retrieving All Columns and All Rows You can retrieve all columns and rows from a table using the * wildcard For example, assume you want to: List the complete Products table. Translating the query into the SQL statement: SELECT * FROM products Performing Calculations You can perform arithmetic on columns if they contain numerical data For example, assume you want to: List the order number and the total dollar value for every order item. Translating the query into the SQL statement: SELECT PriceEach, Quantity, (PriceEach * Quantity) FROM orderitems; ^ top 2.2.3: Making Conditional Queries You can retrieve records that satisfy certain conditions by adding a WHERE clause These queries take the form SELECT columnNames FROM tableNames WHERE conditions Let us look at some examples to see how these queries work Simple WHERE Conditions A WHERE condition can be a simple relation such as equality For instance, assume you want to: List all the data about the product with an ID of 2 Translating the query into the SQL statement: SELECT * FROM products WHERE ID = 2 Here only records with the ID column set to 2 are retrieved SQL supports these relational operators: >, >=, =, <>, <, <= For example, you could display all the data for products with a price less that $10 by changing the WHERE clause to: WHERE Price < 10 Logical Operators You can create more complex conditions using the logical operators: AND, OR, NOT For example: SELECT ProductName FROM products WHERE ID >= 2 AND ID <= 4 Logical expressions evaluate to TRUE, FALSE or NULL NULL means "unknown" 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 BETWEEN An alternative to using logical operators is to use BETWEEN Checks column value is BETWEEN min AND max values (inclusive) For instance, we could rewrite our previous query SELECT ProductName FROM products WHERE ID BETWEEN 2 AND 4 BETWEEN does not allow you to do anything new, but can be more intuitive for some people Using IN to Select a List The IN keyword allows you to test against a list of values For example, if you want to get all the data for the products Canvas and Oil Paint, we could write a query: SELECT * FROM products WHERE ProductName = 'Canvas' OR ProductName = 'Oil Paint' Using IN, you can simplify the query like this: SELECT * FROM products WHERE ProductName IN ('Canvas', 'Oil Paint') In essence, you are selecting everything in a list 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 ^ top 2.2.4: Pattern Matching with LIKE Most of the time we use exact matches for retrieving records But some times we need to be a little vague The keyword LIKE allows you to search based on a pattern The wildcard symbols you can use are: _ which matches any single character % which matches any characters before or after characters specified For example, assume you want to: 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.5: Eliminating Duplicates With DISTINCT By default, that result of SELECT can contain duplicate records You use the keyword DISTINCT to discard any duplicate records DISTINCT Example First you list the supplier numbers for every product in the products table using the query: SELECT SupplierID FROM products However, some of the results are duplicates because you use the same suppliers for more than one product To correct this, you add the keyword DISTINCT to the query SELECT DISTINCT SupplierID FROM products ^ top 2.2.6: Sorting Results A database may return rows in any order You can put the rows in sorted order for any column A column specified for sorting is called the sort key To sort, you use the ORDER BY clause followed by a sort key Sorted 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 Also, you can also specify a reverse (descending) order using the DESC keyword For example: SELECT ID, ProductName, Price, InStock FROM products ORDER BY ProductName DESC Sorting With Multiple Keys You can use multiple columns for sorting by separating each column in the ORDER BY list with a comma (,) Data is sorted by the leftmost column first If there are duplicates in this column, then the rows or sorted by the second sort key For example: 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 then 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.7: Limiting Query Results Another MySQL clause is LIMIT Not standard SQL LIMIT restricts how many records to return The LIMIT clause takes either one or two arguments of the form: LIMIT [offset] numberOfRows Where offset is the starting row and numberOfRows is the number of rows to return starting from the offset If no offset is specified, then MySQL assumes 0 as the default For example: SELECT * FROM products LIMIT 1 This query returns only the first row that matches the SELECT Another example: SELECT * FROM products LIMIT 2, 3 Only three records will be returned starting with the second Note that the first row is numbered zero (0) ^ top 2.2.8: Summary You use the SELECT statement to retrieve records from a database We looked at the following clauses: SELECT [DISTINCT] columnNames FROM tableNames [ WHERE conditions ] [ ORDER BY columnList [ ASC | DESC ] ] [ LIMIT [offset] numberOfRows ]; The WHERE clause has many ways to specify conditions that restrict which rows are returned Most of the time you try to find and exact match using a relational operator: >, >=, =, <>, <, <= A more complicated match is to pattern match using LIKE with wildcard symbols The wildcard symbols you can use are: _ which matches any single character % which matches any characters before or after characters specified Sometimes you get duplicate records, and you can use DISTINCT to discard them You can use ORDER BY to sort the rows in ascending (ASC) or descending (DESC) order Also, you can use LIMIT to restrict the number of rows returned Check Yourself How do you code a SELECT statement to return all the data for all the rows in a table? What SQL statement would you write to return all the products with a price of greater than or equal to $1 but less than or equal to $10? If you want to return all the product names that start with 'B', what SQL statement would you write? How do you remove duplicate rows in the result set? How do you sort rows in the result set? How do you limit the number of rows returned? ^ top Exercise 2.2 In this exercise, we execute several prepared SQL queries against the Artzy database. After trying these queries, we write a few on our own. Note that you must complete exercise 2.1, which installs the Artzy database, before you can complete this exercise. Specifications Make sure that Apahe and MySQL are running using the XAMPP control panel. Then open a new Web browser tab or window and enter localhost/phpmyadmin in the address field. You should see phpMyAdmin running in the browser window. Read about and try each of the examples in the remainder of this section (2.2). When you are finished, create a file named singletable.txt. Develop queries for the three Query Problems listed below. In the singletable.txt file, record each query problem and the SQL statement you develop to solve the problem. Submit your singletable.txt file to Blackboard as part of assignment 2. Query Problems List the product name and price of every product in the Products table. Order the output in ascending order by price. 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. List all the customer names, first and last, sorted by their last name but without duplicates. As time permits, be prepared to answer the Check Yourself questions in the section: 2.2.8: Summary. ^ top 2.3: Multi-Table Queries Learner Outcomes 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 ^ top 2.3.1: Joining Two Tables You use a SQL Join whenever you need to select data from 2 or more tables You can join tables by simply listing them in the FROM clause of the SELECT statement For instance, assume you want to: List all the product names and the product's supplier name. This requires data from two tables and you might be tempted to use a query like: SELECT ProductName, SupplierName FROM products, suppliers However, if you run this query, you probably do NOT get what you want What you would get is known as the Cartesian product A Cartesian product is the combinations from all possible orderings of the data Rather than the Cartesian product, we need to join tables based on a common information between the two tables A properly designed database will provide you with the key columns you need in each table For example, looking at the Products tables you see: ID ProductName ProductDescription Path2Image SupplierID Price InStock Weight  1  Canvas  Good canvas for quality paint  canvas2.gif  2  22.50  30    2  Brush, Big  Big brush for large areas  brushlarge.gif  1  4.75  47    3  Brush, Small  Small brush of fine material  brush.gif  1  3.75  34    4  Oil Paint  Top quality oil paint  oil-tube.gif  3  6.25  24    5  Acry. Paint  Top quality acrylic paint  tube2.gif  3  6.50  65    6  Brusher, Hiliner  Fine brush for fine work  brush2.gif  1  4.50  10   And looking at the Suppliers table we see: ID SupplierName SupplierCode  1  Brush Bros.  BRSH  2  Canvas Co.  CAN  3  Painters, LLC  PT  4  Unused Supplier   You find that the SupplierID in the Products table matches with the ID of the Suppliers table This gives you a way join the two tables using the relationship between the columns You show this relationship in the WHERE clause as shown in the modified query: SELECT ProductName, SupplierName FROM products, suppliers WHERE SupplierID = suppliers.ID Note that you can have a 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 (.) Also note that you can qualify column names even if no ambiguity exists A More Complex Join Example Let us consider a slightly more complicated 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 to 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 SupplierID = suppliers.ID AND Price > 5.00 The general procedure to join two tables is: Indicate in SELECT clause all columns to display List in the FROM clause all tables involved in query Specify condition(s) in the WHERE clause to restrict the results to only the rows that have common values in matching columns ^ 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 you were joining two tables at a time and include an AND between each condition: SELECT columns FROM table1, table2, table3, ... WHERE table1.key1 = table2.commonKey1 AND table2.key2 = table3.commonKey2 ... Multi-table Join Example Consider the question: 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, like: 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?3 tables need 2 conditions You need additional terms if you want to restrict what your result set returns 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: Using the JOIN Clause SQL has a JOIN keyword that is sometimes needed The general syntax is: SELECT columnNames FROM table1 [LEFT | RIGHT] [INNER | OUTER] JOIN table2 ON joinCondition [ WHERE queryConditions ] Where the joinCondition is the condition for joining two tables we looked at previously Generally, you should not have any conditions in the ON part that are used to restrict which rows you want in the result set Instead, put all the result set conditions in the WHERE clause As an example, recall our original two-table join statement: SELECT ProductName, SupplierName FROM products, suppliers WHERE SupplierID = suppliers.ID Rewriting the example using a JOIN gives us: SELECT ProductName, SupplierName FROM products JOIN suppliers ON SupplierID = suppliers.ID As you can see, the comma becomes the keyword JOIN and the condition moves to the ON clause INNER and OUTER JOINS There are two types of JOIN: INNER JOIN and OUTER JOIN If you do not specify the type, then SQL assumes an INNER JOIN In other words, JOIN is short for INNER JOIN An INNER JOIN will select rows from both tables only if there is a match between the join columns For instance, our join example does not return any data for the supplier, 'Unused Supplier' Normally, this is what you want However, what if you want to display all the products and suppliers, no matter what? To display this data, you need to make an OUTER JOIN There are two types of OUTER JOINS: LEFT OUTER JOIN and RIGHT OUTER JOIN Note that when you use the keywords LEFT or RIGHT then OUTER is assumed Thus you can call the OUTER JOINS simply: LEFT JOIN and RIGHT JOIN 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 or blank For example, assume you want to: 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 the first (left) table may have values with no corresponding value for the second (right) table, you need to use a LEFT JOIN Here is our example with a LEFT JOIN SELECT SupplierName, ProductName FROM suppliers LEFT JOIN products ON suppliers.ID = products.SupplierID When you see the results, notice that one of the product fields in NULL or blank NULL is the word that SQL uses to mean, "no data" RIGHT JOIN RIGHT JOIN works like a LEFT JOIN except in reverse A RIGHT 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, assume you want to: 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 Thus 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 JOIN Syntax ^ 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 To clarify, you assign column-name aliases using the AS clause: SELECT customers.ID AS CustID, customers.LName, customers.FName, orders.ID AS OrderID, productID AS PID, OrderDate FROM customers, orders, orderitems WHERE customers.ID = orders.CustomerID AND orders.ID = orderitems.OrderID ORDER BY orders.ID; Table Alias You can give tables listed in the FROM clause an alias as well SELECT column FROM table [AS] alias A table alias can save you typing for long table names However, If you use a table alias, you must use it everywhere in the query For example, we can rewrite our previous query using both column and table aliases: SELECT C.ID AS CustID, O.ID AS OrderID, 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; Notice that the query is now shorter ^ 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 What is meant by the term "join"? To join two tables, how many table names are listed in the FROM clause? If you do not restrict a join between two tables, what is the result? How many tables does the following query join? SELECT * FROM Products, Orders, OrderItems How many conditions minimum are needed to join 4 tables? How do you refer to a column with the same name when joining two different tables? What is the difference between an INNER JOIN and an OUTER JOIN? What are the two types of OUTER JOINS? What is the difference between a LEFT JOIN and a RIGHT JOIN? A LEFT JOIN returns all data from the table specified on which side: right or left? A column alias is specified in which clause of a SELECT statement? A table alias is specified in which clause of a SELECT statement? ^ top Exercise 2.3 In this activity, we write a multi-table SQL query for the Artzy database. Note that you must complete exercise 2.1, which installs the Artzy database, before you can complete this exercise. Specifications Make sure that Apahe and MySQL are running using the XAMPP control panel. Then open a new Web browser tab or window and enter localhost/phpmyadmin in the address field. You should see phpMyAdmin running in the browser window. Create a text file named multitable.txt that you will turn into Blackboard after completing this exercise. For the Artzy database, develop a query for the query problem listed below. Submit your multitable.txt file to Blackboard as part of assignment 2. 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 like 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 Check your answer As time permits, be prepared to answer the Check Yourself questions in the section: 2.3.5: Summary. ^ top Wrap Up Due Next: A2-SQL Queries (3/1/10) Quiz 2 and Discussion 2 (3/1/10) When class is over, please shut down your computer if it is on ^ top Home | Blackboard | Syllabus | Expectations | Schedule Project | Help | FAQ's | HowTo's | Links Last Updated: February 21 2010 @20:29:38