1 / 8

SQL – Part 2 Multiple Tables

SQL – Part 2 Multiple Tables. CIS 324 – Chapter 5. Sub-Queries. Example – We need the names of all employees (not their employee number) for all employees who worked more than 40 hours on an assignment.

arleen
Télécharger la présentation

SQL – Part 2 Multiple Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL – Part 2 Multiple Tables CIS 324 – Chapter 5

  2. Sub-Queries Example – We need the names of all employees (not their employee number) for all employees who worked more than 40 hours on an assignment. Sub-Queries – Nested Queries (Effective when the results ultimately comes from ONE table) SELECT Name FROM EMPLOYEE WHERE EmployeeNumber IN (SELECT DISTINCT EmployeeNum FROM ASSIGNMENT WHERE HoursWorked > 40);

  3. Sub Queries (con’t) Multiple nesting is allowed: What if we only need Accounting projects from the above example: SELECT Name FROM EMPLOYEE WHERE EmployeeNumber IN (SELECT DISTINCT EmployeeNum FROM ASSIGNMENT WHERE ProjectID IN ( SELECT ProjectID FROM PROJECT WHERE Department = ‘Accounting’));

  4. Joins When we need to display data from 2 or more tables SELECT Name, HoursWorked FROM EMPLOYEE, ASSIGNMENT WHERE EmployeeNumber = EmployeNum; This creates a new table with Name from the EMPLOYEE table and HoursWorked from the ASSIGNMENT table when the condition of matching employee numbers occurs. A join is just another table so all earlier SQL statements are available for use. (Group BY, WHERE, etc.)

  5. Multiple Table Joins We can join more than 2 tables together: SELECT Project.Name, HoursWorked, EMPLOYEE.Name FROM PROJECT, ASSIGNMENT, EMPLOYEE WHERE PROJECT.ProjectID = ASSIGNMENT.ProjectID AND EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNum;

  6. Joins (con’t) If two columns in separate tables have the same column name you will need to indicate the table name and column name in the WHERE statement: SELECT Name, HoursWorked FROM PROJECT, ASSIGNMENT WHERE PROJECT.ProjectID = ASSIGNMENT.ProjectID; If not all rows in both tables have a match in the join condition – these rows will not appear in the join table.

  7. Outer Joins OUTER JOIN – Not part of SQL Standard but supported by most DBMS – Solution to dropping data in standard join An outer join appends the rows in the select statement onto the existing table on either the left or right side: SELECT Name, HoursWorked FROM PROJECT LEFT JOIN ASSIGNMENT WHERE PROJECT.ProjectID = ASSIGNMENT.ProjectID;

  8. Sample Outer Join This appends the name of the project to the left side of the assignment table. The unmatched rows will receive a null value: Q3 Portfolio Analysis 17.50 Q3 Portfolio Analysis 12.50 Q3 Portfolio Analysis 8.00 Q3 Portfolio Analysis 20.25 Q3 Tax Prep 45.75 Q3 Tax Prep 70.50 Q3 Tax Prep 40.50 Q4 Product Plan 75.00 Q4 Product Plan 20.25 Q4 Product Plan 25.25 Q4 Portfolio Analysis null

More Related