While you are waiting for class to start...

1. While you are waiting for class to start... (1) Login to SQL Server 2012 Management Studio (2) Execute the file called “SQLLab3.sql”. It is located on the k: drive in the IS475\ directory. You will see two errors, and then the tables will create and populate. There are two tables created and populated with this file – one has three rows and one has four rows of data.

2. What is the goal of a SQL query? • To produce an accurate result table. • To produce an accurate result table that contains meaningful information. • To produce an accurate result table that contains meaningful information that will help solve a business problem. • To produce an accurate result table that contains meaningful information that will help solve a business problem and is capable of being viewed through a front-end visualization program to make an impact.

3. Getting data from multiple tables • Why do you want to access data from multiple tables in a single query? • To provide more complete information in a result table. • To support decision making. • SQL programmers need to understand what happens when multiple tables are accessed in a single query.

4. Time for some new tables!! These two tables above are created and populated with the file called “SQLLab3.sql”. If you were late to class, look back on the first page of this handout to see the location of the file and information about execution.

5. Questions about design on previous page • Does the design indicate whether or not referential integrity is enforced in the database? • Does the inclusion of a foreign key to relate tables imply that referential integrity is enforced in the database? • What does it mean to say “referential integrity is enforced” vs. “referential integrity is not enforced” in a database? • Is it necessary to enforce referential integrity to relate tables in a relational database?

6. What do we want to accomplish? A simple result table with a combination of data from the order and customer tables, as shown on the next page.

7. ord cust + Result Table =

8. Let’s try and create the result table on the previous page. Open a new query window and type the following statement. SELECT * FROM ord, cust

9. +

10. Cartesian Product Or Cross Join

12. SELECT * FROM ord INNER JOIN cust ON ord.custID = cust.custID ORDER BY ord.orderID

14. Open a new query window, and type the SQL code below. This code has the customer table placed first in the FROM statement. How do the results contrast with the SQL code on slide #13? SELECT * FROM cust INNER JOIN ord ON ord.custID= cust.custID ORDER BY ord.orderID

15. Finalize the query by SELECTing only the required columns SELECT ord.orderid, ord.orderdate, ord.duedate, cust.customername FROM ord INNER JOIN cust ONOrd.custID= Cust.custID ORDER BY ord.orderid

16. Results of Inner Join Cust Ord

17. Let’s make a new query!

18. ord cust + =

19. SELECT cust.CustomerName, ISNULL(ord.orderID, ‘No Order’) OrderID, ord.DueDate FROMord INNER JOIN cust ON Ord.custID = Cust.custID ORDER BY cust.customername

20. Results of One-Sided Outer Join Results of Outer Join Cust Ord

21. SELECT cust.CustomerName, ISNULL(ord.orderID, ‘No Order’) OrderID, ord.DueDate FROMord RIGHT OUTER JOIN cust ON Ord.custID = Cust.custID ORDER BY cust.customername

22. FROM ord RIGHT OUTER JOIN cust Result Table + = ord cust Left Side of the join Right Side of the join

23. Let’s say that referential integrity is not enforced and we have more rows in our tables...

24. Execute the script file called: SQLLab3Expand.sql on the k: drive in the IS475\ directory to create a table called “ord1”.

25. SELECT * FROM ord1, cust How many rows and columns in the cross join?

26. What would the results look like from an inner join? SELECT * FROM ord1 INNER JOIN cust ON ord1.custID = cust.custID

27. Why is OrderID 500 missing?

28. What would the results look like from a right outer join? SELECT * FROM ord1 RIGHT OUTER JOINcust ON ord1.custID = cust.custID

29. The row is still missing...

30. What would the results look like from a left outer join? SELECT * FROM ord1 LEFT OUTER JOINcust ON ord1.custID = cust.custID

31. Results of One-Sided Outer Join Results of Outer Join Cust Ord

33. How do you make the NULL data more meaningful? Open a new query window and type the following query: SELECT ord1.OrderID, ord1.OrderDate, ord1.CustID, ord1.DueDate, ISNULL(cust.CustID, ‘n/a’), ISNULL(cust.CustomerName, ‘Missing Name’) FROM ord1 LEFT OUTER JOIN cust ON ord1.custID = cust.custID

34. All rows from both tables! SELECT * FROM ord1 FULL OUTER JOINcust ON ord1.custID = cust.custID

35. Results of Both-Sided Outer Join Results of Outer Join Results of Outer Join Cust Ord

37. Write a query on your own! Normally, we want to see all the rows in the child table, rather than the parent. Write a query that displays all the orders in the ord1 table, but include only the columns shown below:

38. Write a new query Write a query that displays all the orders placed by the customer “Jane Doe”. Assume that you don’t know Jane Doe’s customer ID and have to use her name in the WHERE clause. The join condition is the same, but you are adding a WHERE clause. The goal is to realize that once you have joined tables, you have all fields from all tables available in all parts of the query. The result table should look like the one provided below.

39. Write another query Write a query that displays all the orders that don’t have a valid customer. The result table should look like the one provided below.

40. Last 2 queries of the day… First, write a query that summarizes order data by customer. The result table should look like the one provided below. Hint: Use the GROUP BY clause. Second, change it so that all customers are displayed, whether or not they have an order. The result table should look like the one provided below.