SQL Lecture 6: Multi-Table Select Statements Mid-Term Review

1. COP 2700 – Data Structures - SQL Lecture 6 – June 3, 2015 Multi Table Select Statements Mid-Term Review

2. Announcements • Assignment 2 is due Monday, June 8 by 11:59 PM • The Mid-Term has been re-scheduled for Wednesday, June 10 • Two parts. • The first part will be essay/short answer and will be close book and notes • The second part will be SELECT statements and you will be allowed to use your computer/laptops and notes • Mid-Term Grades will be sent to each of you by End of Day, June 13. Will include Assignments 1-3, Mid Term and Attendance. • Last Day to Drop is June 15 by 6 PM. • Read Chapters 3 and 5 sometime (maybe after the test)

3. Tonight • Multi-Table Selects • Intro and Database Design Review

4. Our “Tools” • Basic Select Components • Expressions • Conditions • Aggregates • AVG, SUM, MIN, MAX, COUNT • GROUP BY • HAVING • ORDER BY • Joins • Inner • Outer • Sub-queries • IN • Exists • Scalars (I think ALL and ANY are stupid) • Queries on Queries • Queries in Queries • Set Operations • Union • Intersection • Except (Minus)

5. Registration Database

6. Priority Database

7. Basic Select Statement SELECT {List of Columns or Expressions or * or Literals} FROM {Tables or Views} JOIN {Links between Tables and Views} WHERE {Conditional Statements} GROUP BY {List of Columns} HAVING {More Conditional Statements} ORDER BY {Another List of Columns}

8. Definition of an Expression • An Expression is a series of constants, function calls, and/or columns that are evaluated and then displayed as a single column in a Select clause's Result Set. • Different rules are applied based on the type of column/constants used in the expression.

9. Simple Arithmetic Expressions • Without any database columns • Select SQRT(45)+36 • Notice, no Table name in this case. Only constants. • Notice the use of a function. Check out the other arithmetic functions available for SQLServer at • https://msdn.microsoft.com/en-us/library/ms177516.aspx • With database columns • SELECT Part_Num, Description, On_Hand * Price AS Inventory_Value FROM Part

10. ExpressionsConverting From One Type to Another • SQLServer uses the Cast function to convert from one data type to another. • Best example is converting date or numeric data to string for concatenation (remember the “+” with string data is used to concatenate or “add” strings together.) • SELECT ORDER_DATE+ '-' + Customer_Name AS Date_Customer FROM Premiere • SELECT CAST(ORDER_DATE as varchar(11))+ '-' + Customer_Name AS Date_Customer FROM Premiere • Same idea can be used for numeric data. You have to be careful with the size though. May truncate or round data, and sometimes can return an error. • SELECT CAST(price AS varchar(2)) as Too_Short, Description FROM Part • Notice the use of column aliases above!!

11. Expressions – Some String Functions • Substring(Expression,Start,Length) • returns a string starting at character Start for Length positions. • CharIndex(Compare, Expression) • returns an integer of the first occurrence of Compare in the string Expression SELECT SUBSTRING(street,1,CHARINDEX(' ',street)) AS Street_Number, SUBSTRING(Street, CHARINDEX(' ',Street)+1,20) AS Street_Name FROM customer

12. Where Clause • Series of conditional statements that evaluate to True or False • Each conditional statement is of the form • Expression Comparison-Operator Expression • The Conditional Operators Include • = < > <= >= != <> • IN, EXISTS, IS NULL Plus NOT • Notice the Conditional Statements compares Expressions, not necessarily just columns and constants. SELECT * FROM Part WHERE Class = 'HW' SELECT * FROM PART WHERE 'HW' = Class SELECT * FROM PART WHERE ON_HAND * PRICE > 2000

13. Where Clause • Can be connected with AND/OR making a compound condition • Can be logically defined with parenthesis SELECT * FROM PART WHERE ON_HAND * PRICE > 3000 AND CLASS = 'SQ' OR CLASS = 'HW' SELECT * FROM PART WHERE ON_HAND * PRICE > 3000 AND (CLASS = 'SQ' OR CLASS = 'HW') • Remember. The default in SQLServer is that capitalization of data does not matter, so unless you specifically install SQLServer requiring conditional statements to differentiate between capitalization, 'Miami', 'MIAMI' or 'miami' will all match.

14. WHERE Clause - NOT • Reverses the logical value of a conditional statement. • Applies to the next conditional statement (or parenthetical group of statement) SELECT * FROM Part WHERE On_Hand * Price > 3000 is the same as SELECT * FROM PartWHERE NOT On_Hand * Price <= 3000

15. WHERE Clause - IN • Creates a Special “shorthand” Conditional Statement • Allows a user to list a set of possible values in a WHERE clause. • List of Parts in Warehouses 1 or 3 using OR SELECT * FROM Part WHERE Warehouse = 1 OR Warehouse = 3 • Same Query using IN SELECT * FROM Part WHERE Warehouse IN (1, 3) • Since it's just another Conditional Statement, we can connect them together with other conditional statements SELECT * FROM Part WHERE Warehouse IN (1, 3) AND SUBSTRING(Description,1,1) = 'D' • The NOT is placed in front of the IN to negate the condition SELECT * FROM PART WHERE Warehouse NOT IN (1,3)

16. WHERE Clause - BETWEEN • Allows one to specify a from and to boundary (inclusive) for a WHERE clause List all Parts with an on-hand inventory cost >= 2000 and <= 5000 SELECT * FROM Part WHERE On_Hand * Price BETWEEN 2000 and 5000

17. Dates • GetDate() = Current system date and time • Cast(GetDate() as Date) = Just the current Date!! • Date Arithmetic uses Functions to return a result • DateAdd(interval, increment, date or column) • Returns a Date type • DateDiff (interval, starting, ending) • return a Number type • To use in a WHERE clause, always format the date as 'DD-MMM-YYYY' to ensure the correct date is selected • SQLServer also has functions that can be used when specifying a date or time that use specific formats. We'll check out Cast and Convert later.

18. Nulls • A Null is NOT a space and is NOT a blank. • With nulls, the column never had anything inserted into it. • Blank, the column was used in an insert with a value of ''. • Nulls many times make it difficult to find data if you are not aware that they exist. • Select * from Transcript Where Grade != 'A' Will not return any transcript records where the Grade is null. If you want them as well, must specify • Select * from Transcript Where Grade != 'A' OR Grade IS NULL • If we wanted everything that has a grade • Select * from Transcript Where Grade is NOT NULL • Notice the special syntax for NULL conditionals (IS NOT NULL, IS NULL) • A special function called COALESCE can be used to replace a NULL value with something else. • Select COALESCE(Grade,'Pending') From Transcript • (Coalesce is the Standard, but SQLServer also supports ISNULL(Grade,'Pending') • The really aren't identical, but discussing the differences goes a little deep • I never remember how to spell Coalesce

19. Distinct • SELECT Warehouse FROM Part • Warehouse IDs will be repeated • SELECT DISTINCT Warehouse FROM Part • Warehouse IDs will NOT be repeated

20. Like – Wildcards for Strings • % - Matches any number of characters • Which Parts start with the letter “D” • SELECT Part_Num, Description FROM Part WHERE Description Like 'D%' • Which Parts have “Wash” as part of the description • SELECT Part_Num, Description FROM Part WHERE Description Like '%Wash%' • How could I have done the above using String Functions? • SELECT Part_Num, Description FROM Part WHERE SubString(Description,1,1) = 'D' • SELECT Part_Num, Description FROM Part WHERE CharIndex(“Wash”,Description)>0 • _ - Matches one character (Underscore) • SELECT Part_Num, Description FROM Part WHERE Description LIKE '_o%' • Remember, if you need to search for a percent or under_score, you can use the Escape clause (but I have never ever had to do that!!) • SELECT Part_Num, Description FROM Part Where Description LIKE '%!%Wash%' Escape '!' Wil get all record with %wash somewhere in the descritpion.

21. Aggregates • Sum, Max, Min, Count(*), Avg Functions • Others also available for statistics and analysis How many parts have a price greater than 500?: SELECT Count(*) FROM Part WHERE Price > 500' What is the average part price in the Premiere database SELECT AVG(Price) as Average_Price, Description FROM Part What is the total inventory price (the sum of the on_hand quantity times price)?? SELECT SUM(Price*On_Hand) FROM Inventory.

22. Count (Distinct Expression) • Used if you need to know the number of UNIQUE items in an aggregate. • Let's do this using our Registration view. • How many grades has each student received or will receive? select student_id, count(*) from transcript group by student_id • How many grades has each student received (that is, grade is not null) select student_id, count(Grade) from transcript group by student_id • How many different grades has each student been given? select student_id, count(Distinct Grade) from transcript group by student_id

23. Group By • Uses the Aggregates to get Subtotals of partitioned data What is the total inventory price (the sum of the on_hand quantity times price) for each warehouse?? SELECT Warehouse, SUM(On_Hand*Price) as Total_Price FROM Part GROUP BY Warehouse The Group By must at least include ALL fields in the result set that are not aggregated.

24. More Group By • Multiple Aggregates can be placed on the Select. • List the maximum item price, the minimum on hand quantity and the total inventory for each warehouse SELECT Warehouse, MAX(Price), MIN(On_Hand), SUM(Price*On_Hand) FROM Part Group By Warehouse

25. More Group By • You can group by more stuff than is in the Select clause (which comes in handy for the Having we will get to in a second) SELECT Rep_Num FROM Part GROUP BY Rep_Num • This looks stupid, but see what we do with it in the Having

26. Having • Applies a second “WHERE” Clause to the result set of a Group By Select • So Basically, it does the Aggregate, THEN it applies the Having clause as a secondary filter, so it is Filtering the Aggregate Result. • Display all Reps and their customer's total credit limit where the total credit limit is greater than 2800. SELECT Rep_Num, SUM(Credit_Limit) as Total_Limit FROM CUSTOMER GROUP BY REP_NUM HAVING SUM(Credit_Limit) > 28000 Having CANNOT use Column Aliases!!

27. Having • Back to that “Stupid” Group By • We don't want the Credit Limit in the Select, only the Rep_Num SELECT Rep_Num FROM CUSTOMER GROUP BY REP_NUM HAVING SUM(Credit_Limit) > 28000

28. Order By • Is done AFTER the query is complete. • What does a Select statement return? That is what the Order By is applied against!! • Either Column Aliases, Expressions or Column Names can be used • The Expressions or Column Names do NOT have to be in the Select (but must be in the table) • Orders the resulting table by the fields specified • Each field can be sorted either Ascending (ASC) which is the Default) or Descending (DESC) • List all Customers sorted by Rep Number and then by Balance Descending SELECT * FROM Customer ORDER BY REP_NUM, BALANCE DESC

29. Order By • You can Order By ANYTHING from the Select statement, including aggregate fields • List all parts sorted by total value SELECT *, On_Hand * Price As Total_Value FROM Part Order By On_Hand * Price • or even SELECT *, On_Hand * Price As Total_Value FROM Part Order By Total_Value

30. Sub Queries • You can also use a query within a query • With a Sub Query a single column is returned, but with multiple rows that can be used with an “IN” statement • With a Scalar a single row with a single column was returned that you can use as a value in a conditional statement

31. Sub Queries • List the Order Number of each part in class AP. • Without a Sub Query we first get the list of parts in class AP SELECT Part_Num FROM Part WHERE Class = 'AP' • Returns CD52, DR93, DW11, KL62, KT03 • Then use that to get the Order Number SELECT Distinct Order_Num FROM Order_Line WHERE Part_Num IN ('CD52', 'DR93', 'DW11', 'KL62', 'KT03')

32. Sub Queries • Just stick the two queries together, and Voila!! SELECT Distinct Order_Num FROM Order_Line WHERE Part_Num IN (SELECT Part_Num FROM Part WHERE Class = 'AP')

33. Sub Queries - Scalar Selects • Selects that return a single value and can be used in a where clause with an equal or not equal List all orders with the highest number of credit hours offered: First, let's get the highest number of credit hours: SELECT MAX(Credit_Hours) FROM Course That's the Scalar (Returns One Column and One Row!!) Now use that as the right side of a condition in a WHERE clause SELECT Distinct Course_ID, Section, Semester FROM Registration WHERE Credit_Hours = (SELECT MAX(Credit_Hours) FROM Course)

34. Other Things You Might See • Brackets or Double Quotes Around Variable Names • Allows for spaces in Variable Names SELECT Class, Sum(On_Hand) AS "On Hand Qty" FROM Part GROUP BY Class SELECT Class, Sum(On_Hand) AS [On Hand Qty]" FROM Part GROUP BY Class • TOP x • Only show the Top x (x is a number) from the query. • The select is done after everything within the query is completed, including the ORDER BY) • Show the two parts that cost the most • SELECT TOP 2 * FROM Part ORDER BY Price

35. The Basic Join • List all Instructor First and Last Names, Along with the Semester, Course_ID and Course_Name for courses they taught. • Where does this information come from? • Instructor, Schedule and Course Tables • How are these tables related? • There is a many to many relationship between Instructor and Courses that is implemented by the Schedule table • Schedule.Instructor_ID = Instructor.Instructor_ID and Schedule.Course_ID = Course.Course_ID • Put together the Select statements. Start with the Table Names and Joins, then add the column names and finally add any Conditions. SELECT First_Name, Last_Name, Semester, Course.Course_ID, Course_Name FROM Instructor JOIN Schedule ON Instructor.Instructor_ID = Schedule.Instructor_ID JOIN Course ON Schedule.Course_ID = Course.Course_ID • Notice we included the table name on one column for our select. • If your SELECT statement has N tables, normally you will need (at least) N-1 joins to correctly link the tables together.

36. The Basic Join • What we just did is called an “Explicit Join” since the Select statement used the word “Join” in the Select. One can also do the same Select, without the Join clause: SELECT First_Name, Last_Name, Semester, Course.Course_ID, Course_Name FROM Instructor, Schedule, Course WHERE Instructor.Instructor_ID = Schedule.Instructor_ID AND Schedule.Course_ID = Course.Course_ID

37. Inner Joins or SubQueries • There are usually several ways to do the same query: • Display all Instructor IDs, First_Names and Last_Names that live in 'Miami” and ever taught a class in 'Boca Raton'. SELECT Instructor_ID, First_Name, Last_name FROM Instructor WHERE Instructor_ID IN (SELECT Instructor_ID FROM Schedule WHERE City = 'Boca Raton') AND City = 'Miami' • OR SELECT Instructor.Instructor_ID, First_Name, Last_name FROM Instructor INNER JOIN Schedule ON Instructor.Instructor_ID = Schedule.Instructor_ID AND Schedule.City = 'Boca Raton' AND Instructor.City = 'Miami' • OR SELECT Instructor.Instructor_ID, First_Name, Last_name FROM Instructor, Schedule WHERE Instructor.Instructor_ID = Schedule.Instructor_ID AND Schedule.City = 'Boca Raton AND Instructor.City = 'Miami'

38. Required Inner JoinWe need data from multiple tables • Display all Instructor IDs, First_Names and Last_Names, Course_ID and Semester where the Instructor lives in 'Miami” and the class was taught in 'Boca Raton'. SELECT Instructor.Instructor_ID, First_Name, Last_name, Course_Id, Semester FROM Instructor INNER JOIN Schedule ON Instructor.Instructor_ID = Schedule.Instructor_ID AND Schedule.City = 'Boca Raton' AND Instructor.City = 'Miami'

39. Aliases • Column Specification (Which Course_ID?) • You need to specific which table a column comns from any time you have a join of tables that have the same column name. • Table Aliases (I'm tired of typing!!) • You can “rename” your tables with a shortcut name so that you don't have to continously type out the full name. • Column Aliases (I need BOTH Cities!!) • This is the alias we used for computed fields. Also comes in handy when you need to display two or more fields from different tables that happen to have the same field name.

40. Using Aliases • List all Instructor IDs, Last Names, First Names, Instructor City, Semester, Course_ID, Section and Course City where the Instructor's City is not equal to the City where the course is being held. SELECT I.Instructor_ID, Last_Name, First_Name, I.City as Instr_City, Semester, Course_ID, Section, S.City as Course.City FROM Schedule S INNER JOIN Instructor I ON I.Instructor_ID = S.Instructor_ID WHERE S.City <> I.City

41. Set Operations • Union (This OR That) • List Instructor_IDs that teach in Boca OR live in Miami. Select Instructor_ID from Schedule Where City = 'Boca Raton' UNION Select Instructor_ID from Instructor Where City = 'Miami' Union also useful to pull disparate data from multiple tables List all cities where either a student or instructor lives or a class is held. • Intersection (This AND That) • List Instructor_IDs that teach in Boca AND live in Miami. Select Instructor_ID from Schedule Where City = 'Boca Raton' INTERSECT Select Instructor_ID from Instructor Where City = 'Miami' . • Minus/Except (This NOT That) • List Instructor_IDs that teach in Boca and do NOT live in Miami. Select Instructor_ID from Schedule Where City = 'Boca Raton' EXCEPT Select Instructor_ID from Instructor Where City = 'Miami' One Note on Set operations. The Select Expression List must match at least by data type, and to be useful for Intersect and Minus should match by Fields. There are exceptions, but in most cases that is the rule. But, check out above how the same field is coming from different tables!!

42. Set Operations B A Union B Yellow+Green+Blue A Intersect B Green A Minus B Yellow A

43. Exists • The SQL EXISTS condition is considered "to be met" if the subquery returns at least one row. • List all courses that have ever been taught. SELECT * FROM Course WHERE EXISTS (SELECT * FROM Schedule WHERE Course.Course_ID = Schedule.Course_ID) Notice how we linked from the main query to the sub-query • NOT EXISTS also available • List all courses that have Never been taught. SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM Schedule WHERE Course.Course_ID = Schedule.Course_ID)

44. Outer Joins • Left Outer Join • All Records in the first table plus all matching records in the second table • Right Outer Join • All records in the second table plus all matching records in the first table • Full Outer Join • All records in both tables with matching records joins

45. Outer Join Examples • List all instructors with last name and office with the course_IDs they have taught Select Last_Name, Office, Course_ID FROM Instructor LEFT OUTER JOIN Schedule ON Instructor.Instructor_ID = Schedule.Instructor_ID ORDER BY Last_Name • List all Course_IDs, Course_Names, When and Where they were taught in Summer 2015 Select C.Course_ID, Course_Name, Class_Time, Room FROM Course C LEFT OUTER JOIN Schedule S ON C.Course_ID = S.Course_ID AND Semester = 'SUMMER2015' • What happens if we move the conditional on the Semester to a WHERE (that is, outside the join)?

46. Outer Join Examples • update Student set City = 'Orlando' where Student_ID = 'Z135' –Needed to demostrate • SELECT * FROM student s LEFT OUTER JOINInstructor i On s.City = i.city • SELECT * FROM student s RIGHT OUTER JOINInstructor i On s.City = i.city • SELECT * FROM student s FULL OUTER JOINInstructor i On s.City = i.city

47. Cartesian Join (Or Cross Join) • Basically, a Join without any column conditionals (no ON clause). • If the Join is between two tables, the result set will have all rows from the first table for each row of the second table. • SELECT * FROM Student, Course • Why the heck would we ever want to do this?

48. More on Joins • In most cases your joins are going to be on Primary/Foreign Keys • But the power of the relational database is that you don't always have to do that. You can make two (or more) tables join on any column as long as they are of the same data type. • List all Instructors and Students that live in the same city Select Distinct I.Last_Name +', ' + I.First_Name as Instructor_Name, S.Last_Name +', ' + S.First_Name as Student_Name, I.City FROM Student S JOIN Instructor I ON S.City = I.City More information on Joins can be found here… http://en.wikipedia.org/wiki/Join_%28SQL%29

49. Queries on Queries • And what does a SELECT return… • A Table, Silly • Which means we can use that resulting table in another Select clause. • List the number of credit hours ever taught for an instructor SELECT * FROM (select INSTRUCTOR.INSTRUCTOR_ID, Last_Name, First_Name, SUM(Credit_Hours) as SUMHours FROM INSTRUCTOR JOIN SCHEDULE ON INSTRUCTOR.INSTRUCTOR_ID = schedule.INSTRUCTOR_ID JOIN COURSE ON SCHEDULE.COURSE_ID = COURSE.COURSE_ID GROUP BY INSTRUCTOR.INSTRUCTOR_ID, Last_Name, First_Name) MyTable WHERE LAST_NAME = 'Bradley'

50. Queries in Queries • Scalars that return a single row and column can also be used directly in a SELECT statement to create a computed column. • List the total hours passed by student with student name and city. SELECT Last_Name, First_Name, City, (Select SUM(Credit_Hours) FROM TRANSCRIPT JOIN SCHEDULE ON TRANSCRIPT.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID JOIN COURSE ON SCHEDULE.COURSE_ID = COURSE.COURSE_ID WHERE STUDENT.STUDENT_ID = TRANSCRIPT.STUDENT_ID and GRADE in ('A','B','C')) as Total_Hours_Passed FROM STUDENT