Working with multiple tables
.pdf
School
University of Ibadan**We aren't endorsed by this school
Course
TME 214
Subject
Information Systems
Date
Dec 31, 2024
Pages
6
Uploaded by aiyegbeniisrael
Hands-on Lab: Working with Multiple TablesEstimated time needed:20 minutesObjectivesAfter completing this lab, you will be able to:Write SQL queries that access more than one tableCompose queries that access multiple tables using a nested statement in the WHERE clauseBuild queries with multiple tables in the FROM clauseWrite Implicit Join queries with join criteria specified in the WHERE clauseSpecify aliases for table names and qualify column names with table aliasesIn this lab, you will complete SQL practice problems that will provide hands-on experience with SQL queries that access multiple tables. You will be:Accessing Multiple Tables with Sub-QueriesAccessing Multiple Tables with Implicit JoinsSoftware used in this labIn this lab, you will use MySQL. MySQL is a Relational Database Management System (RDBMS) designed to store, manipulate, and retrieve data efficiently.To complete this lab, you will utilize MySQL relational database service available as part of IBM Skills Network Labs (SN Labs) Cloud IDE. SN Labs is a virtual lab environmentused in this course.Database used in this labThe database used in this lab is internal. You will be working on a sample HR database. This HR database schema consists of 5 tables called EMPLOYEES, JOB_HISTORY,JOBS, DEPARTMENTSand LOCATIONS. Each table has a few rows of sample data. The following diagram shows the tables for the HR database:Load the databaseUsing the skills acquired in the previous modules, you should first create the database in MySQL. Follow the steps below:1. Open the phpMyAdmin interface from the Skills Network Toolbox in Cloud IDE.2. Create a blank database named HR. Use the script shared in the link below to create the required tables.Script_Create_Tables.sql3. Download the files in the links below to your local machine (if not already done in previous labs).Departments. csvJobs. csvJobsHistory.csv8/23/24, 10:11 PMabout:blankabout:blank1/6
Locations. csvEmployees. csv4. Use these files to the interface as data for respective tables in the HR database.Accessing multiple tables with sub-queriesLet us see some examples of queries requiring multiple table access using sub-queries.1. Retrieve only the EMPLOYEES records corresponding to jobs in the JOBS table.For such a question, you can implement the sub-query in the WHERE clause, such that the overlapping column of JOD ID can identify the required entries.1. 11. SELECT * FROM EMPLOYEES WHERE JOB_ID IN (SELECT JOB_IDENT FROM JOBS);Copied!The expected output would look as shown below.2. Retrieve JOB information for employees earning over $70,000.For this example, retrieve the details from the JOBS table, which has common IDs with those available in the EMPLOYEES table, provided the salary in the EMPLOYEES table isgreater than $70,000. You can write the query as:1. 12. 23. 31. SELECT JOB_TITLE, MIN_SALARY, MAX_SALARY, JOB_IDENT2. FROM JOBS3. WHERE JOB_IDENT IN (select JOB_ID from EMPLOYEES where SALARY > 70000 );Copied!The expected output would look as shown below.Accessing multiple tables with Implicit JoinsLet us see some examples of queries that require access of multiple tables using Implicit Joins.1. Retrieve only the EMPLOYEES records corresponding to jobs in the JOBS table.8/23/24, 10:11 PMabout:blankabout:blank2/6
The same question as before, but now we will use Implicit Join to retrieve the required information. For this, you will combine the tables based on job IDs. Using the followingquery for this:1. 12. 23. 31. SELECT *2. FROM EMPLOYEES, JOBS3. WHERE EMPLOYEES.JOB_ID = JOBS.JOB_IDENT;Copied!The expected output is shown below.2. Redo the previous query using shorter aliases for table names.Note that the tables in question can be assigned shorter aliases. This is especially helpful in cases where specific columns are to be accessed from different tables. The query wouldbe modified to:1. 12. 23. 31. SELECT *2. FROM EMPLOYEES E, JOBS J3. WHERE E.JOB_ID = J.JOB_IDENT;Copied!The output would look like:8/23/24, 10:11 PMabout:blankabout:blank3/6
Notice that the two queries are giving the same response.3. In the previous query, retrieve only the Employee ID, Name, and Job Title.Notice that Job Title is a column of the JOBS table, and other details are coming from the EMPLOYEES table. The two tables will be joined on Job ID. The query would be asfollows:1. 12. 23. 31. SELECT EMP_ID,F_NAME,L_NAME, JOB_TITLE2. FROM EMPLOYEES E, JOBS J3. WHERE E.JOB_ID = J.JOB_IDENT;Copied!8/23/24, 10:11 PMabout:blankabout:blank4/6
The output would look as shown below.4. Redo the previous query, but specify the fully qualified column names with aliases in the SELECT clause.The column names can also be prefixed with table aliases to keep track of where each column is coming from. The above query will be modified as shown below.1. 12. 23. 31. SELECT E.EMP_ID, E.F_NAME, E.L_NAME, J.JOB_TITLE2. FROM EMPLOYEES E, JOBS J3. WHERE E.JOB_ID = J.JOB_IDENT;Copied!8/23/24, 10:11 PMabout:blankabout:blank5/6
The expected output is:Practice problems1. Retrieve only the list of employees whose JOB_TITLE is Jr. Designer.a. Using sub-queriesSolutionb. Using Implicit JoinsSolution2. Retrieve JOB information and a list of employees whose birth year is after 1976.a. Using sub-queriesSolutionb. Using implicit joinSolutionConclusionCongratulations! You have completed this lab and are ready for the next topic.At the end of this lab, you are now able to:Write SQL queries that access more than one tableCompose queries that access multiple tables using a nested statement in the WHERE clauseBuild queries with multiple tables in the FROM clauseWrite Implicit Join queries with join criteria specified in the WHERE clauseSpecify aliases for table names and qualify column names with table aliasesAuthor(s)Abhishek GagnejaLakshmi HollaMalika Singla© IBM Corporation 2023. All rights reserved.8/23/24, 10:11 PMabout:blankabout:blank6/6