We are creating more SQL queries!

1. We are creating more SQL queries! Login to SQL Server 2012 Management Studio If you weren’t here last class, execute the script file called “create-emp1.sql”. It is located on the k: drive in the IS475\f12 directory.

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. What is the syntax of a SQL query? SELECT (list of columns) FROM (tables with declaration of inner and/or outer joins with join conditions) WHERE (condition for each row) GROUP BY (summary control break field(s)) HAVING (condition for each group) ORDER BY (sort columns)

4. Revisit last class • Single row queries. • Designed to process individuallyeach row of the underlying table and produce a result table. • Usually includes fewer columns and rows than underlying table(s). • May include calculations and functions. • Rows selected with the WHERE clause. • Columns selected with the SELECT clause.

5. Sample single row query SELECT ename "Employee Name", hiredate "Date Hired", MONTH(hiredate) "Month Integer", DATEDIFF(MONTH, hiredate, getdate()) "Number of Months Employed", salary, commission, salary + ISNULL(commission,0) "Total Compensation" FROM emp1 WHERE deptno= 30 and salary <=3000;

6. Aggregate (Group) Queries • Summarizesdata and provides more meaningful and informative output from the database. Sometimes referred to as “summary queries.” • Aggregate/group functions differ from single row SELECT statements: • A SELECT statement processes every row in the underlying table. The result table (unless a WHERE clause is used) contains one row per row in the underlying table. • An aggregate function collects data from multiple rows and produces summarizeddata in the result table. There should be one row in the result table per aggregate group. • If an aggregate function is run on the whole table, without grouping, it generates a single row result table. • If an aggregate function is run with grouping , then it generates one row per group in the result table.

7. What are the aggregate functions in SQL?

8. Sample Aggregate Queries SELECT COUNT(empno), SUM(salary), MIN(salary) FROM emp1; SELECT deptno, SUM(salary) FROM emp1 GROUP BY deptno; SELECT deptno, SUM(salary) FROM emp1 GROUP BY deptno HAVING SUM(salary) > 15000;

9. 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.

10. Time for some new tables!! Create the two tables above with the script file called “JoinLab1.sql”. It is located on the k: drive in the IS475\f12 directory.

11. 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?

12. tblOrder tblCustomer + Result Table =

13. SELECT * FROM tblOrder, tblCustomer

14. +

15. Cartesian Product Or Cross Join

17. SELECT * FROM tblOrder INNER JOIN tblCustomer ON tblOrder.custID = tblCustomer.custID ORDER BY tblOrder.orderID

19. 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 #18? SELECT * FROM tblCustomer INNER JOIN tblOrder ON tblOrder.custID= tblCustomer.custID ORDER BY tblOrder.orderID

20. Finalize the query by SELECTing only the required columns SELECT ord.orderid, ord.orderdate, ord.duedate, cust.customername FROM tblOrderord INNER JOIN tblCustomercust ON Ord.custID= Cust.custID ORDER BY ord.orderid

21. Let’s make a new query!

22. tblOrder tblCustomer + =

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

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

25. FROM tblOrder RIGHT OUTER JOIN tblCustomer Result Table + = tblOrder tblCustomer Left Side of the join Right Side of the join

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

27. Execute the script file called: JoinLabExpand1.sql on the k: drive in the IS475\f12 directory to create a table called “tblOrder1”.

28. SELECT * FROM tblOrder1, tblCustomer How many rows and columns in the cartesian product (cross join)?

29. What would the results look like from an inner join? SELECT * FROM tblOrder1 INNER JOIN tblCustomer ON tblOrder1.custID = tblCustomer.custID

30. What is missing??

31. What would the results look like from a right outer join? SELECT * FROM tblOrder1 RIGHT OUTER JOINtblCustomer ON tblOrder1.custID = tblCustomer.custID

32. The row is still missing...

33. What would the results look like from a left outer join? SELECT * FROM tblOrder1 LEFT OUTER JOINtblCustomer ON tblOrder1.custID = tblCustomer.custID

35. All rows from both tables! SELECT * FROM tblOrder1 FULL OUTER JOINtblCustomer ON tblOrder1.custID = tblCustomer.custID

37. Write a 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 the customer name in the WHERE clause. The result table should look like the one provided below. Does the query change if the database DOES enforce referential integrity?

38. 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.

39. 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. 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.