We are creating SQL queries!

1. We are creating SQL queries!

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 individually each row of the underlying table and produce a result table. • Usually includes fewer columns than underlying table. • Usually includes fewer rows than the underlying table. • 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", • sal Salary, • comm Commission • FROM emp2 • WHERE deptno = 30 and sal <=3000;

6. 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. • What happens when multiple tables are accessed in a single query?

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

10. tblOrder tblCustomer + Result Table =

11. SELECT * FROM tblOrder, tblCustomer

12. +

13. Cartesian Product Or Cross Join

15. SELECT * FROM tblOrder INNER JOIN tblCustomer ON tblOrder.custID = tblCustomer.custID

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

18. SELECT ord.orderid, ord.orderdate, ord.duedate, cust.customername FROM tblOrderord INNER JOIN tblCustomercust ON Ord.custID= Cust.custID ORDER BY ord.orderid

19. Let’s make a new query!

20. tblOrder tblCustomer + =

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

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

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

24. Let’s say that referential integrity is not enforced...

26. SELECT * FROM tblOrder, tblCustomer How many rows and columns in the cartesian product?

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

28. What is missing??

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

30. The row is still missing...

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

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