COP 2700 – Data Structures - SQL

1. COP 2700 – Data Structures - SQL Lecture 5 – June 3, 2015 Multi Table Select Statements

2. Announcements • Assignment 2 is due Monday, June 8 by 11:59 PM • The Mid Term Exam is next Monday (June 8)

3. Tonight • Review Homework • Multi-Table Selects

4. Registration Database

5. Basic Select Statement Again 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}

6. The Basic Join • We just went through normalizing information to make the correct tables in our database • Joins pull back the information we normalized so that the information from two or more tables can be queries and presented.

7. The Basic Join • List the Instructor First Name, Last Name, Semester and Course_ID for all courses taught by an instructor. • We have information we need to pull from two tables. • Instructor has the Last Name and First Name • Schedule has the Course_ID and Semester • The two tables are related by the Instructor_ID SELECT First_Name, Last_Name, Semester, Course_ID FROM Instructor JOIN Schedule ON Instructor.Instructor_ID = Schedule.Instructor_ID

8. The Basic Join • Let’s add to our query that we also want to know the Course Name. Now we have three tables being pulled together to give a result. How is the Course Table related to the two tables we already have in our query? 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. Why did we have to do that?

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

10. The Basic Join • The JOIN …ON is the current “standard” and rumor has it that SQLServer will soon start requiring this syntax. (I can’t believe they will actually do this since so many legacy systems use the Implicit Join, but stranger things have happened.) • You can do your joins either way you feel comfortable for this class. Just realize that both are acceptable. • I’ll be using the JOIN…ON in (most of) the examples I give. • If your SELECT statement has N tables, normally you will need (at least) N-1 joins to correctly link the tables together.

11. Inner Joins or SubQueries • We’ll discuss this a little more later this evening, but 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’

12. 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’

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

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

15. A Word on Aliases • In SQLServer one can use the verb “as” for either a column or table alias, or you can just leave place the alias directly after the column name: 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

16. What Do Some Sites Do? • Tables are given abbreviations • These abbreviations are then used are the prefix for all columns for the table • Table and column aliases are no longer needed nor are table prefixes for column names since every column is already unique • We still use column aliases for calculated field

17. Bradley’s New Survey System

18. AND With the Same Column • List all Instructor_IDs that have taught 'CDA3201' and have taught 'COP3014' We know the following is wrong: • SELECT Instructor_ID FROM Schedule • WHERE Course_ID = ‘CDA3201' and • Course_ID = 'COP3014 ' • What do we need to be able to answer the question?

19. AND With the Same Column • We actually need pairs of schedule records for each instructor so that we can check to see if both conditions are met. • Three ways to do this. We'll cover two now: SELECT S1.Instructor_ID FROM Schedule S1 JOIN Schedule S2 ON S1.Instructor_ID = S2.Instructor_ID AND S1.Course_ID = ' CDA3201' AND S2.Course_ID = 'COP3014' Notice how we joined a table with itself so that we can have pairs of answers to check SELECT Instructor_ID FROM Schedule WHERE Course_ID = ' CDA3201' AND Instructor_ID IN (SELECT Instructor_ID FROM Stay WHERE Course_ID = 'COP3014 ')

20. Let's Combine What We LearnedMulti Table Selects + In/Between/Aggregates • List all instructor information for instructors that taught 'COP4220' and whose last name begins with 'F' • Count the number of times each student took 'COP3540'. • List all student information, course ID, course name and semesters for students that took a course in Jupiter. • List the student_ID, student last name, birthdate, gender and a count of all classes taken for students with birthdates between Jan 1, 1983 and Dec 31, 1985. • List all course_ids that have at least one student that received an 'A' and at least one student that received a 'C'. • List all course_ids and course_names that have at least one student that received an 'A' or at least one student that received a 'C'.

21. 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' • Are you singing “Conjunction Junction”? • Do you even know what “Conjunction Junction” is?

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

23. And the third way from earlier… • List all Instructor_IDs that have taught 'CDA3201' and have taught 'COP3014 ' SELECT Instructor_ID FROM Schedule WHERE Course_ID = 'CDA3201' INTERSECT SELECT Instructor_ID FROM Schedule WHERE Course_ID = 'COP3014 '

24. Don't Be So Negative • Many times a not equal will not give you what you think you need. • Instead in many cases it is better to make a positive statement • Then, turn that frown upside down. Use it in a NOT IN subquery or use Except. • List all course IDs that have never been held in Boca. • Why wouldn’t this work? Select Course_Id From Course where Course_ID IN (Select Course_ID from Schedule Where City != 'Boca Raton') • Two Ways: Select Course_Id From Course where Course_ID NOT IN (Select Course_ID from Schedule Where City = 'Boca Raton') or Select Course_ID from Course EXCEPT Select Course_ID from Schedule Where City = 'Boca Raton' • Why did we use Course table in this example instead of just pulling everything from Schedule? • There are times when a Not Equal is fine, but it is usually on a specific table and not when you are trying to determine the outcome with a sub-query. • List all book titles where the publisher is not RH – no problem – Single table – Direct compare

25. Everyone and Everything • List all Student_IDs that have taken ALL offered courses • How many offered courses are there? • Who took that many courses? SELECT student_id FROM Transcript, Schedule WHERE transcript.schedule_id = schedule.schedule_id GROUP BY student_id HAVING COUNT(DISTINCT Course_id) = (SELECT COUNT(*) FROM Course WHERE Course_id IN (SELECT Course_id FROM schedule)) • Notice the extra IN. Why did we add this? • Notice the parenthesis around the comparison Select statement. Why??

26. 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)

27. Natural Join (Oracle and mySQL) SELECT I.LAST_NAME, S.Course_ID FROM Instructor I NATURAL JOIN Schedule S • Automatic matching on Foreign Keys (It does it by column name!!) • Deprecated in SQLServer • Because natural joins are implicit, there is no way to see what columns will be used in the join. You might not get what you think you’re getting (in our case we have City in both tables and it would have been used) • If a column name or type is altered or the column is removed from one of the tables, the next time the SELECT statement is run the join will break. • If you find this when you are looking at someone else’s code, notify your boss and remove it.

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

29. 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)?

30. Outer Join Examples • update Student set City = 'Orlando' where Student_ID = 'Z135‘ –Needed to demostrate • select * from student s left outer join INSTRUCTOR i on s.City = i.city • select * from student s right outer join INSTRUCTOR i on s.City = i.city • select * from student s full outer join INSTRUCTOR i on s.City = i.city

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

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

33. 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'

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

35. Two Ways and Three Ways (Don’t get the wrong idea!!) • Many times you will see that there are many ways to do the same query that give the exact same results, especially if you are basing the selection condition of one table on another. • This is especially true with compound conditionals that involve ANDs or NOTs • In this case, one can usually do the SELECT using either of these three ways: • SubQuery • Join • Set Operations • Usually, even if it is a “simple” condition, you can do the query two ways using either a SubQuery or Join

36. Two Ways (Single Condition) • List all student names that have ever earned an “A” in a class. • Join SELECT Distinct Last_Name, First_Name FROM Student Join Transcript ON Student.Student_ID = Transcript.Student_ID WHERE Grade = ‘A’ • Sub Query SELECT Distinct Last_Name, First_Name FROM Student WHERE Student_ID IN (SELECT Student_ID FROM Transcript WHERE Grade = ‘A’)

37. Three Ways (Multiple Conditions) • List all student names that have ever earned an “A” in a class and have also earned a “C” in a class: SELECT Distinct Last_Name, First_Name FROM Student JOIN Transcript T1 ON Student.Student_ID = T1.Student_ID JOIN Transcript T2 On T1.Student_ID = T2.Student_ID WHERE T1.Grade = ‘A’ AND T2.Grade = ‘C’ SELECT Distinct Last_Name, First_Name FROM Student Where Student_ID in (SELECT Student_ID FROM Transcript WHERE Grade = ‘A’) AND Student_ID IN (SELECT Student_ID FROM Transcript WHERE Grade = ‘C’) SELECT Distinct Last_Name, First_Name FROM Student JOIN Transcript ON Student.Student_ID = Transcript.Student_ID WHERE Grade = 'A' INTERSECT SELECT Distinct Last_Name, First_Name FROM Student JOIN Transcript ON Student.Student_ID = Transcript.Student_ID WHERE Grade = 'C'

38. Two Ways - Negative • List all student names that have never earned an “C” in a class. SELECT Distinct Last_Name, First_Name FROM Student WHERE Student_ID NOT IN (SELECT Student_ID FROM Transcript WHERE Grade = ‘C’) SELECT Distinct Last_Name, First_Name FROM Student EXCEPT SELECT DISTINCT Last_Name, First_Name FROM Student JOIN Transcript ON Student.Student_ID = Transcript.Student_ID WHERE Grade = ‘C’

39. Developing Correct Select Statements • Will the query require multiple Select statements. Break it down. Do one at a time, then combine them. • What Tables are involved? How are those tables related? • What fields need to be displayed? Where will those fields come from? • What conditions are included? What specific fields and values? • Any Aggregates? Are Scalars required? • Any special considerations: • All • Not • And for Same Field

40. Developing Correct Select Statements • If there are multiple parts of the question, do one part at a time. • Start with your FROM and list your tables. Set aliases if needed. • Add joins between tables or IN sub-queries. • Usually if you have N tables, you will require at least N-1 joins • THIS IS BIG. REMEMBER IT. N Tables----At Least N-1 joins • Next write down your field list including any aggregates. Will we need Distinct? • Should Union, Intersect, Except be used (or be easier to understand?) • Write any specific conditions • Add any required Group By for Aggregates • Finish it all off with an Order By if needed. • Because a query returns no results doesn’t mean that it is wrong. No answer is as important as an answer!!

41. Registration Database

42. Let's Combine What We LearnedMulti Table Selects + In/Between/Aggregates • List all instructor information for instructors that taught 'COP4220' and whose last name begins with 'F' • Count the number of times each student took 'COP3540'. • List all student information, course ID, course name and semesters for students that took a course in Jupiter. • List the student_ID, student last name, birthdate, gender and a count of all classes taken for students with birthdates between Jan 1, 1983 and Dec 31, 1985. • List all course_ids that have at least one student that received an 'A' and at least one student that received a 'C'. • List all course_ids and course_names that have at least one student that received an 'A' or at least one student that received a 'C'.

43. Some More Work Queries • Show all Instructor_IDs that have ever taught a course in the same city that they live. • List all instructors that are not teaching anything this semester. • List all student_ids where the student received an “A” in “COP3530” and received an “A” in “COP3540” • Print all Schedule_Ids, Semester, Course_IDs and Sections that were taught by an instructor with an office in the EE building and the credit hours of the course > 3. • List all instructors that taught COP3813 and taught COP3530. • Which instructor_ID has given the highest percentage of “A”s. • Use the above to get the Instructor’s Name by using an IN • List all Instructor_IDs that have only given ‘A’s.