SQLite Exercise: Display the department name, manager name, and city
Write a query to display the department name, manager name, and city.
Sample table : employees
+-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+ | 100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 | | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90 | | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1987-06-20 | IT_PROG | 9000.00 | 0.00 | 102 | 60 | | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 1987-06-21 | IT_PROG | 6000.00 | 0.00 | 103 | 60 | | 105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1987-06-23 | IT_PROG | 4800.00 | 0.00 | 103 | 60 | | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1987-06-24 | IT_PROG | 4200.00 | 0.00 | 103 | 60 | | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1987-06-25 | FI_MGR | 12000.00 | 0.00 | 101 | 100 | | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 1987-06-26 | FI_ACCOUNT | 9000.00 | 0.00 | 108 | 100 | ........... | 206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 | +-------------+-------------+-------------+----------+--------------------+------------+------------+----------+----------------+------------+---------------+
Sample table : departments
+---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | ....... | 270 | Payroll | 0 | 1700 | +---------------+----------------------+------------+-------------+
Sample table : locations
location_id street_address postal_code city state_province country_id ----------- -------------------- ----------- ---------- -------------- ---------- 1000 1297 Via Cola di Rie 989 Roma IT 1100 93091 Calle della Te 10934 Venice IT 1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefectu JP 1300 9450 Kamiya-cho 6823 Hiroshima JP 1400 2014 Jabberwocky Rd 26192 Southlake Texas US 1500 2011 Interiors Blvd 99236 South San California US 1600 2007 Zagora St 50090 South Brun New Jersey US 1700 2004 Charade Rd 98199 Seattle Washington US 1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA 1900 6092 Boxwood St YSW 9T2 Whitehorse Yukon CA ......... 3200 Mariano Escobedo 999 11932 Mexico Cit Distrito Feder MX
SQLite Code:
-- Selecting specific columns from the "departments," "employees," and "locations" tables
SELECT d.depart_name, e.first_name, l.city
-- Specifying the primary table from which to retrieve the data, in this case, "departments" (aliased as "d")
FROM departments d
-- Performing an inner join with the "employees" table (aliased as "e") based on the common column "manager_id"
JOIN employees e
ON (d.manager_id = e.employee_id)
-- Performing another inner join with the "locations" table (aliased as "l") based on the common column "location_id"
JOIN locations l USING (location_id);
Output:
depart_name first_name city -------------- ---------- ---------- Administration Jennifer Seattle Marketing Michael Toronto Purchasing Den Seattle Human Resource Susan London Shipping Adam South San IT Alexander Southlake Public Relatio Hermann Munich Sales John Oxford Executive Steven Seattle Finance Nancy Seattle Accounting Shelley Seattle
Explanation:
The above SQLite query retrieves information about departments from the "departments" table, including department name, employee information from the "employees" table, and city information from the "locations" table. The joins are performed based on the conditions specified in the ON and USING clauses.
Here's a brief explanation of each part of SQLite code:
- SELECT clause:
- It selects specific columns, including 'depart_name' from the "departments" table (aliased as "d"), 'first_name' from the "employees" table (aliased as "e"), and 'city' from the "locations" table (aliased as "l").
- FROM clause:
- Specifies the primary table from which to retrieve the data, in this case, the "departments" table. The table is given an alias "d."
- JOIN clause (1st join):
- Performs an inner join with the "employees" table. The table is joined with itself (aliased as "e") based on the condition that the 'manager_id' in the "departments" table matches the 'employee_id' in the "employees" table.
- ON clause (1st join):
- Specifies the join condition using the ON keyword. The condition is (d.manager_id = e.employee_id), indicating that the "departments" and "employees" tables are joined based on the common column "manager_id."
- JOIN clause (2nd join):
- Perform another inner join with the "locations" table. The table is aliased as "l," and the join is based on the common column "location_id."
- USING clause (2nd join):
- Specifies the columns used for the join condition using the USING keyword. The condition is (location_id), indicating that the "locations" and "employees" tables are joined based on the common column "location_id."
Relational Algebra Expression:
Relational Algebra Tree:
Go to:
PREV : Write a query to display the department ID, department name, and manager first name.
NEXT : Write a query to display the job title and average salary of employees.
Practice SQLite Online
Model Database
Structure of 'hr' database :
Improve this sample solution and post your code through Disqus.
What is the difficulty level of this exercise?