The NorthWind Database Tutorial free essay sample

Subqueries are queries embedded in queries. They are used to retrieve data from one table based on data in another table. They generally are used when tables have some kind of relationship. For example, in the NorthWind database, the Orders table has a CustomerID field, which references a customer in the Customers table. Retrieving the CustomerID for a specific order is pretty straightforward. Code Sample 1 /* Find the CustomerID of the company that placed order 10290. */ SELECT CustomerID FROM Orders WHERE OrderID = 10290; Code Explanation This will return COMMI, which is very likely meaningless to the people reading the report. The next query uses a subquery to return a meaningful result. 1 Code Sample 2 Find the name of the company that placed order 10290. SELECT CompanyName FROM Customers WHERE CustomerID = (SELECT CustomerID FROM Orders WHERE OrderID = 10290); Code Explanation The above code returns Comercio Mineiro, which is a lot more useful than COMMI. The subquery can contain any valid SELECT statement, but it must return a single column with the expected number of results. We will write a custom essay sample on The NorthWind Database Tutorial or any similar topic specifically for you Do Not WasteYour Time HIRE WRITER Only 13.90 / page For example, if the subquery returns only one result, then the main query can check for equality, inequality, greater than, less than, etc. On the other hand, if the subquery returns more than one record, the main query must check to see if a field value is (or is NOT) IN the set of values returned. Code Sample 3 Find the Companies that placed orders in 1997 /****************************** The query below will work in SQL Server ******************************/ SELECT CompanyName FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate BETWEEN 1-Jan-1997 AND 31-Dec-1997); Code Explanation The above SELECT statement will return the following results: 2 3 EXERCISE 1†¦ Subqueries Duration: 20 to 30 minutes. In this exercise, you will practice writing subqueries. 1. Create a report that shows the product name and supplier id for all products supplied by Exotic Liquids, Grandma Kellys Homestead, and Tokyo Traders. o You will need to escape the apostrophe in Grandma Kellys Homestead. To do so, place another apostrophe in front of it. For example, o o SELECT * FROM Suppliers WHERE CompanyName=Grandma Kellys Homestead; 2. Create a report that shows all products by name that are in the Seafood category. 3. Create a report that shows all companies by name that sell products in CategoryID 8. 4. Create a report that shows all companies by name that sell products in the Seafood category. Joins How can we find out†¦ †¢ †¢ †¢ Which products are provided by which suppliers? Which customers placed which orders? Which customers are buying which products? Such reports require data from multiple tables. Enter joins. Syntax SELECT table1. column, table2. column FROM table1 JOIN table2 ON (table1. column=table2. column) WHERE conditions Creating a report that returns the employee id and order id from the Orders table is not difficult. 4 Code Sample 4 Find the EmployeeID and OrderID for all orders SELECT EmployeeID, OrderID. FROM Orders; But this isn’t very useful as we cannot tell who the employee is that got this order. The next sample shows how we can use a join to make the report more useful. Code Sample 5 Create a report showing employee orders. SELECT Employees. EmployeeID, Employees. FirstName, Employees. LastName, Orders. OrderID, Orders. OrderDate FROM Employees JOIN Orders ON (Employees. EmployeeID = Orders. EmployeeID) ORDER BY Orders. OrderDate; Code Explanation The above SELECT statement will return the following results: 5 Table names are used as prefixes of the column names to identify the table in which to find the column. Although this is only required when the column name exists in both tables, it is always a good idea to include the prefixes as it makes the code more efficient and easier to read. Table Aliases Using full table names as prefixes can make SQL queries unnecessarily wordy. Table aliases can make the code a little more concise. The example below, which is identical in functionality to the query above, illustrates the use of table aliases. 6 Code Sample 6 Create a report showing employee orders using Aliases. SELECT e. EmployeeID, e. FirstName, e. LastName, o. OrderID, o. OrderDate FROM Employees e JOIN Orders o ON  (e. EmployeeID = o. EmployeeID) ORDER BY o. OrderDate; Multi-table Joins Multi-table joins can get very complex and may also take a long time to process, but the syntax is relatively straightforward. Syntax SELECT table1. column, table2. column, table3. column FROM table1 JOIN table2 ON (table1. column=table2. column) JOIN table3 ON (table2. column=table3. column) WHERE con ditions Note that, to join with a table, that table must be in the FROM clause or must already be joined with the table in the FROM clause. Consider the following. SELECT table1. column, table2. column, table3. column FROM table1 The above code would break because it attempts to join table3 with table2 before table2 has been joined with table1. Code Sample 7 /* Create a report showing the Order ID, the name of the company that placed the order, and the first and last name of the associated employee. Only show orders placed after January 1, 1998 that shipped after they were required. Sort by Company Name. */ 7 /****************************** This query will work in SQL Server ******************************/ SELECT o. OrderID, c. CompanyName, e. FirstName, e. LastName FROM Orders o JOIN Employees e ON (e. EmployeeID = o. EmployeeID) JOIN Customers c ON (c. CustomerID = o. CustomerID) WHERE o. ShippedDate o. RequiredDate AND o. OrderDate 1-Jan-1998 ORDER BY c. CompanyName; Code Explanation The above SELECT statement will return the following results: 8 EXERCISE 2†¦ Using Joins Duration: 25 to 40 minutes. In this exercise, you will practice using joins. 1. Create a report that shows the order ids and the associated employee names for orders that shipped after the required date. It should return the following. There should be 37 rows returned. 9 2. Create a report that shows the total quantity of products (from the OrderDetails table) ordered. Only show records for products for which the quantity ordered is fewer than 200. The report should return the following 5 rows. 3. Create a report that shows the total number of orders by Customer since December 31, 1996. The report should only return rows for which the NumOrders is greater than 15. The report should return the following 5 rows. 4. Create a report that shows the company name, order id, and total price of all products of which NorthWind has sold more than $10,000 worth. There is no need for a GROUP BY clause in this report. 10 Outer Joins So far, all the joins we have worked with are inner joins, meaning that rows are only returned that have matches in both tables. For example, when doing an inner join between the Employees table and the Orders table, only employees that have matching orders and orders that have matching employees will be returned. As a point of comparison, lets first look at another inner join. Code Sample 8 /* Create a report that shows the number of employees and customers from each city that has employees in it. */