Download
1 / 10
110 likes | 226 Vues
Explore the essential concepts of Join Operations in SQL as part of your data analysis toolkit. Learn how to combine multiple tables effectively using primary and foreign keys to gain insights into your datasets. This guide covers practical examples, including how to retrieve employee names and hours worked, filter data with WHERE conditions, and use table aliases for clarity. Discover how to utilize INNER JOIN syntax and leverage graphical interfaces for simplified query creation. Attention to detail in SQL is crucial; duplicate field names and correct syntax ensure accurate results in your analyses.
E N D
CSCI N207 Data Analysis with Spreadsheets 4d. Structured Query Language – JOIN Operation Lingma Acheson Department of Computer and Information Science IUPUI
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;
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;
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’;
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;
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)
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’;
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
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;
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’;
