1 / 10

4d. Structured Query Language – JOIN Operation

Learn how to join multiple tables using the JOIN operation in data analysis with spreadsheets and SQL. Explore examples and practice queries to enhance your skills.

avarney
Download Presentation

4d. Structured Query Language – JOIN Operation

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. CSCI N207 Data Analysis with Spreadsheets 4d. Structured Query Language – JOIN Operation Lingma Acheson Department of Computer and Information Science IUPUI

  2. Joining Multiple Tables • What if we also want to see the Hours Worked for the previous query? • If we need to display columns from different tables, subqueries won’t work. Must use a Join Operation. • Two tables can be joined together to form a large table based on the matching of the primary key and the foreign key. • E.g. Show the names of all the employees and the hours worked. SELECTFirstName, LastName, HoursWorked FROM EMPLOYEE,ASSIGNMENT WHEREEMPLOYEE.EmployeeNumber= ASSIGNMENT.EmployeeNumber;

  3. Joining Multiple Tables • Join Operation – • Must list all the tables in the FROM clause. • Must indicate which field in what table is matched with which field in what table. • Duplicate field names from different tables must be prefixed by their table names using the dot notation. • E.g. SELECTEMPLOYEE.EmployeeNumber, FirstName, LastName, HoursWorked FROM EMPLOYEE,ASSIGNMENT WHEREEMPLOYEE.EmployeeNumber= ASSIGNMENT.EmployeeNumber;

  4. Joining Multiple Tables • Notice that the records that don’t match in two tables won’t show in the result. • Can apply other WHERE conditions. • E.g. Show the names of the employees and their hours worked from the Accounting department. SELECTEMPLOYEE.EmployeeNumber, FirstName, LastName, HoursWorked FROM EMPLOYEE,ASSIGNMENT WHEREEMPLOYEE.EmployeeNumber= ASSIGNMENT.EmployeeNumber AND Department = ‘Accounting’;

  5. Joining Multiple Tables • E.g. Show the names of the employees and their hours worked but only interested in hours worked more than 45. SELECTEMPLOYEE.EmployeeNumber, FirstName, LastName, HoursWorked FROM EMPLOYEE,ASSIGNMENT WHEREEMPLOYEE.EmployeeNumber= ASSIGNMENT.EmployeeNumber AND HoursWorked > 45;

  6. Joining Multiple Tables • Practice: • Show projects whose maximum hours are smaller than 140. Need to see all the project information, as well as the budget code for departments. • An email list of employees, need to see their names, emails, department name, and department phone number. Sorted by last name. • How many total hours have been worked on a certain project? Show the project name, and the total hours. (hint: a combination of SUM, WHERE and GROUP BY)

  7. Joining Multiple Tables • Using table aliases to avoid repeatedly typing long table names – in the FROM clause, use AS XX after a table name with XX as the new shortened name for that table. • E.g. Show the names of the employees and their hours worked from the Accounting department. SELECTE.EmployeeNumber, FirstName, LastName, HoursWorked FROM EMPLOYEE AS E, ASSIGNMENT AS A WHEREE.EmployeeNumber =A.EmployeeNumber AND Department = ‘Accounting’;

  8. Joining Multiple Tables • Can join more than two tables. • E.g. Show the names of the employees and their hours worked on each project, including the project name. SELECTP.ProjectName, FirstName, LastName, HoursWorked FROM EMPLOYEE AS E, ASSIGNMENT AS A, PROJECT AS P WHEREE.EmployeeNumber =A.EmployeeNumber AND P.ProjectID = A.ProjectID

  9. Joining Multiple Tables • Using the JOIN … ON key words • ACCESS syntax: must use INNER JOIN … ON • E.g. Show the names of all the employees and the hours worked. Without using the INNERJOIN key word: SELECTFirstName, LastName, HoursWorked FROM EMPLOYEE AS E,ASSIGNMENT AS A WHEREE.EmployeeNumber= A.EmployeeNumber; Using the INNER JOIN key word: SELECTFirstName, LastName, HoursWorked FROM EMPLOYEE AS E INNER JOIN ASSIGNMENT AS A ON E.EmployeeNumber= A.EmployeeNumber;

  10. Joining Multiple Tables • Often use the graphic interface to add the tables to be joined. The table will be joined automatically based on the relationships defined. • It’s handy to create and save a query that shows all the joined data and later create queries based on this grand query, thus no need to join all the time. E.g. QuerySaved - ProjectAndAssignment:SELECT * FROM PROJECT AS A INNER JOIN ASSIGNMENT AS A ONP.ProjectID = A.ProjectID; Query - ProjectAndHoursAccounting SELECT ProjectName, HoursWorked FROM ProjectAndAssignment WHERE Department = ‘Accounting’;

More Related