Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Question 1
- Needs Grading
- Display full name of the employees using format of Last, First, their hire date and salary together with their department name and city, but only for departments which names start with an A or the department name starts with S and end with g.
- Sort by department name and employee name. Show me one row and tell me how many rows were in the output
- Sample output: Termede, Ingrid 01-DEC-14 11000 Sales city
- Selected Answer:
- SELECT E.LAST_NAME||' '||E.FIRST_NAME AS "FULLL NAME" ,E.HIRE_DATE, E.SALARY, D.DEPARTMENT_NAME,L.CITY
- FRoM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
- WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
- AND L.LOCATION_ID=D.LOCATION_ID
- AND (D.DEPARTMENT_NAME LIKE 'A%' OR D.DEPARTMENT_NAME LIKE 'S%g')
- ORDER BY 4,1;
- =====
- output
- =====
- Gietz William 94-06-07 8300 Accounting Seattle
- 9 total rows
- Correct Answer:
- Correct
- SELECT E.LAST_NAME||' '||E.FIRST_NAME AS "FULLL NAME" ,E.HIRE_DATE, E.SALARY, D.DEPARTMENT_NAME,L.CITY
- FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
- WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID -- these 2 lines cover the joins
- AND L.LOCATION_ID=D.LOCATION_ID -- you may use another join style
- AND (D.DEPARTMENT_NAME LIKE 'A%' OR D.DEPARTMENT_NAME LIKE 'S%g')
- ORDER BY D.DEPARTMENT_NAME,"FULL NAME";
- name HIRE_DATE SALARY DEPARTMENT_NAME CITY
- ----------------------------------------------- --------- ---------- ------------------------------ ------------------------------
- Gietz, William 07-JUN-94 8300 Accounting Seattle
- Higgins, Shelley 07-JUN-94 12000 Accounting Seattle
- Whalen, Jennifer 17-SEP-87 4400 Administration Seattle
- should get 9 rows
- Response Feedback: [None Given]
- Question 2
- Needs Grading
- Display the department name, city, street address and postal code for all departments sorted by city and department name.
- Selected Answer:
- select department_id, city, street_address, postal_code
- from departments d, locations l
- where d.location_id = l.location_id
- order by city, department_id;
- =====
- output
- =====
- DEPARTMENT_ID CITY STREET_ADDRESS POSTAL_CODE
- ------------- ------------------------------ ---------------------------------------- ------------
- 80 Oxford Magdalen Centre, The Oxford Science Park OX9 9ZB
- 10 Seattle 2004 Charade Rd 98199
- 90 Seattle 2004 Charade Rd 98199
- 110 Seattle 2004 Charade Rd 98199
- 190 Seattle 2004 Charade Rd 98199
- 200 Seattle 2004 Charade Rd 98199
- 60 Southlake 2014 Jabberwocky Rd 26192
- 20 Toronto 147 Spadina Ave M5V 2L7
- 50 south San Francisco 2011 Interiors Blvd 99236
- 9 rows selected.
- Correct Answer:
- Correct
- SELECT department_name, city, street_address, postal_code
- FROM departments JOIN locations USING (location_id)
- ORDER BY city, department_name;
- DEPARTMENT_NAME CITY STREET_ADDRESS POSTAL_CODE
- ------------------------------ ------------------------------ ---------------------------------------- ------------
- Sales Oxford Magdalen Centre, The Oxford Science Park OX9 9ZB
- Accounting Seattle 2004 Charade Rd 98199
- Administration Seattle 2004 Charade Rd 98199
- Contracting Seattle 2004 Charade Rd 98199
- Executive Seattle 2004 Charade Rd 98199
- Shipping South San Francisco 2011 Interiors Blvd 99236
- IT Southlake 2014 Jabberwocky Rd 26192
- Marketing Toronto 147 Spadina Ave M5V 2L7
- 8 rows selected
- Response Feedback: [None Given]
- Question 3
- Needs Grading
- Display the full name of the manager of each department in states/provinces of Ontario, California, Bavaria and Washington along with the department name, city, postal code and province name. Sort the output by city and then by department name.
- Selected Answer:
- select first_name, last_name, job_id, city, postal_code, state_province
- from employees E
- join departments D
- ON E.employee_id = D.manager_id
- join locations L
- on D.location_id = L.location_id
- where state_province = 'Ontario' or state_province = 'California'
- or state_province = 'Bavaria' or state_province = 'Washington'
- order by 4,2;
- =====
- output
- =====
- FIRST_NAME LAST_NAME JOB_ID CITY POSTAL_CODE STATE_PROVINCE
- -------------------- ------------------------- ---------- ------------------------------ ------------ -------------------------
- Shelley Higgins AC_MGR Seattle 98199 Washington
- Steven King AD_PRES Seattle 98199 Washington
- Jennifer Whalen AD_ASST Seattle 98199 Washington
- Eleni Zlotkey SA_MAN Seattle 98199 Washington
- Michael Hartstein MK_MAN Toronto M5V 2L7 Ontario
- Kevin Mourgos ST_MAN south San Francisco 99236 California
- 6 rows selected.
- Correct Answer:
- Correct
- SELECT distinct m.last_name as "Manager",
- department_name, city, postal_code, state_province
- FROM employees E
- JOIN employees M
- On (e.manager_id = m.employee_id)
- JOIN departments D
- on (m.manager_id = d.manager_id)
- JOIN locations L
- USING (location_id)
- WHERE LOWER(state_province) IN ('ontario', 'bavaria', 'california', 'washington')
- ORDER BY city, department_name;
- Manager DEPARTMENT_NAME CITY POSTAL_CODE STATE_PROVINCE
- ------------------------- ------------------------------ ------------------------------ ------------ -------------------------
- De Haan Executive Seattle 98199 Washington
- Hartstein Executive Seattle 98199 Washington
- Kochhar Executive Seattle 98199 Washington
- Mourgos Executive Seattle 98199 Washington
- Zlotkey Executive Seattle 98199 Washington
- Response Feedback: [None Given]
- Question 4
- Needs Grading
- Display employee’s Managers ID, managers Last Name and employee number nd last name
- Label the columns Manager, Mgr# ,Employee and Emp#, respectively. Sort by manager number
- Selected Answer:
- SELECT M.employee_id as MGR#,
- M.last_name AS Manager,
- W.last_name AS Employee,
- w.employee_id as Emp#
- FROM employees W JOIN employees M
- ON W.manager_id = M.employee_id
- order by 1;
- =====
- output
- ======
- MGR# MANAGER EMPLOYEE EMP#
- ---------- ------------------------- ------------------------- ----------
- 100 King Kochhar 101
- 100 King De Haan 102
- 100 King Mourgos 124
- 100 King Zlotkey 149
- 100 King Hartstein 201
- 101 Kochhar Whalen 200
- 101 Kochhar Higgins 205
- 102 De Haan Hunold 103
- 103 Hunold Ernst 104
- 103 Hunold Lorentz 107
- 124 Mourgos Rajs 141
- 124 Mourgos Davies 142
- 124 Mourgos Matos 143
- 124 Mourgos Vargas 144
- 149 Zlotkey Abel 174
- 149 Zlotkey Vargas 176
- 149 Zlotkey Grants 178
- 149 Zlotkey de Man 180
- 149 Zlotkey Flertjan 1
- 149 Zlotkey Grovlin 3
- 149 Zlotkey Smertal 4
- 149 Zlotkey Mustaine 5
- 149 Zlotkey Harvey 6
- 149 Zlotkey LeDuc 7
- 149 Zlotkey Bergsteige 8
- 149 Zlotkey Gruber 9
- 149 Zlotkey Sanchez 11
- 149 Zlotkey Chancevente 12
- 149 Zlotkey Torson 14
- 149 Zlotkey Cornel 15
- 149 Zlotkey Gibbons 16
- 149 Zlotkey Pallomine 17
- 149 Zlotkey Jacobs 18
- 149 Zlotkey Strandherst 19
- 149 Zlotkey Brigade 21
- 149 Zlotkey Litrand 22
- 149 Zlotkey Armarillo 23
- 149 Zlotkey Mot 24
- 149 Zlotkey Turcotte 25
- 149 Zlotkey LeBlanc 26
- 149 Zlotkey Rodriguez 27
- 149 Zlotkey Young 28
- 149 Zlotkey Loo Nam 29
- 149 Zlotkey Chan 30
- 149 Zlotkey Wandiko 33
- 149 Zlotkey Gregson 34
- 149 Zlotkey Krain 35
- 149 Zlotkey Termede 36
- 149 Zlotkey Testorok 39
- 149 Zlotkey Whiteduck 40
- 149 Zlotkey Montoya 41
- 201 Hartstein Fay 202
- 205 Higgins Gietz 206
- 53 rows selected.
- Correct Answer:
- Correct
- Will look like this , just different order of columns and more results (over 50 rows)
- SELECT e.last_name as "Employee", e.employee_id "Emp#", m.last_name as "Manager", e.manager_id as "Mgr#"
- FROM employees e LEFT OUTER JOIN employees m ON (e.manager_id = m.employee_id);
- Employee Emp# Manager Mgr#
- ------------------------- ---------- ------------------------- ----------
- Zlotkey 149 King 100
- Kochhar 101 King 100
- De Haan 102 King 100
- Mourgos 124 King 100
- Hartstein 201 King 100
- Whalen 200 Kochhar 101
- Higgins 205 Kochhar 101
- Hunold 103 De Haan 102
- Ernst 104 Hunold 103
- Lorentz 107 Hunold 103
- Rajs 141 Mourgos 124
- Davies 142 Mourgos 124
- Vargas 144 Mourgos 124
- Matos 143 Mourgos 124
- Abel 174 Zlotkey 149
- Taylor 176 Zlotkey 149
- Grant 178 Zlotkey 149
- Fay 202 Hartstein 201
- Gietz 206 Higgins 205
- King 100
- 20 rows selected
- NOTE: left join required to include employees without value for manager_id since manager_id in employees table permits nulls. But this is lab 5a that is about simpler joins.
- Response Feedback: [None Given]
- Question 5
- Needs Grading
- Display the location id, address, city, state and country for each location id.
- Selected Answer:
- SELECT D.location_id,
- L.street_address,
- L.city,
- L.state_province,
- L.country_id
- FROM Departments D JOIN Locations L
- ON D.Location_id = L.location_id
- order by 1;
- ======
- output
- ======
- LOCATION_ID STREET_ADDRESS CITY STATE_PROVINCE CO
- ----------- ---------------------------------------- ------------------------------ ------------------------- --
- 1400 2014 Jabberwocky Rd Southlake Texas US
- 1500 2011 Interiors Blvd south San Francisco California US
- 1700 2004 Charade Rd Seattle Washington US
- 1700 2004 Charade Rd Seattle Washington US
- 1700 2004 Charade Rd Seattle Washington US
- 1700 2004 Charade Rd Seattle Washington US
- 1700 2004 Charade Rd Seattle Washington US
- 1800 147 Spadina Ave Toronto Ontario CA
- 2500 Magdalen Centre, The Oxford Science Park Oxford
- Correct Answer:
- Correct
- SELECT location_id, street_address, city, state_province, country_name
- FROM locations LEFT JOIN countries using (country_id);
- NOTE: left join required to include locations without value for country_id since country_id in locations table permits nulls
- ALTERNATE solution:
- SELECT location_id, street_address, city, state_province, country_name
- FROM countries RIGHT JOIN locations using (country_id);
- L OCATION_ID STREET_ADDRESS CITY STATE_PROVINCE COUNTRY_NAME
- ----------- ---------------------------------------- ------------------------------ ------------------------- ----------------------------------------
- 1000 1297 Via Cola di Rie Roma Italy
- 1100 93091 Calle della Testa Venice Italy
- 1200 2017 Shinjuku-ku Tokyo Tokyo Prefecture Japan
- 1300 9450 Kamiya-cho Hiroshima Japan
- 1400 2014 Jabberwocky Rd Southlake Texas United States of America
- 1500 2011 Interiors Blvd south San Francisco California United States of America
- 1600 2007 Zagora St South Brunswick New Jersey United States of America
- 1700 2004 Charade Rd Seattle Washington United States of America
- 1800 147 Spadina Ave Toronto Ontario Canada
- 1900 6092 Boxwood St Whitehorse Yukon Canada
- 2000 40-5-12 Laogianggen Beijing China
- 2100 1298 Vileparle (E) Bombay Maharashtra India
- 2200 12-98 Victoria Street Sydney New South Wales Australia
- 2300 198 Clementi North Singapore Singapore
- 2400 8204 Arthur St London United Kingdom
- 2500 Magdalen Centre, The Oxford Science Park Oxford Oxford United Kingdom
- 2600 9702 Chester Road Stretford Manchester United Kingdom
- 2700 Schwanthalerstr. 7031 Munich Bavaria Germany
- 2800 Rua Frei Caneca 1360 Sao Paulo Sao Paulo Brazil
- 2900 20 Rue des Corps-Saints Geneva Geneve Switzerland
- 3000 Murtenstrasse 921 Bern BE Switzerland
- 3100 Pieter Breughelstraat 837 Utrecht Utrecht Netherlands
- 3200 Mariano Escobedo 9991 Mexico City Distrito Federal, Mexico
- Argentina
- Israel
- Nigeria
- Egypt
- Kuwait
- France
- HongKong
- Belgium
- Zimbabwe
- Zambia
- Denmark
- 34 rows selected
- Response Feedback: [None Given]
- Question 6
- Needs Grading
- Display the employee id, last name, job, department name, and job grade for all employees. Should get 50+ rows.
- Selected Answer:
- SELECT E.employee_id, E.LAST_NAME, job_id, E.SALARY, J.GRADE
- FROM EMPLOYEES E JOIN JOB_GRADES J
- ON E.SALARY
- BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL
- order by 5;
- =====
- output
- =====
- EMPLOYEE_ID LAST_NAME JOB_ID SALARY G
- ----------- ------------------------- ---------- ---------- -
- 143 Matos ST_CLERK 2600 A
- 144 Vargas ST_CLERK 2500 A
- 29 Loo Nam SA_REP 5500 B
- 142 Davies ST_CLERK 3100 B
- 19 Strandherst SA_REP 4500 B
- 200 Whalen AD_ASST 4400 B
- 107 Lorentz IT_PROG 4200 B
- 26 LeBlanc SA_REP 3700 B
- 141 Rajs ST_CLERK 3500 B
- 21 Brigade SA_REP 5750 B
- 124 Mourgos ST_MAN 5800 B
- 17 Pallomine SA_REP 5000 B
- 206 Gietz AC_ACCOUNT 8300 C
- 176 Vargas SA_REP 8600 C
- 8 Bergsteige SA_REP 8000 C
- 23 Armarillo SA_REP 7500 C
- 7 LeDuc SA_REP 7000 C
- 14 Torson SA_REP 7000 C
- 9 Gruber SA_REP 7000 C
- 30 Chan SA_REP 7000 C
- 178 Grants SA_REP 7000 C
- 180 de Man SA_REP 7000 C
- 33 Wandiko SA_REP 6700 C
- 15 Cornel SA_REP 6500 C
- 104 Ernst IT_PROG 6000 C
- 202 Fay MK_REP 6000 C
- 35 Krain SA_REP 8700 C
- 28 Young SA_REP 9000 C
- 41 Montoya SA_REP 9000 C
- 40 Whiteduck SA_REP 9000 C
- 103 Hunold IT_PROG 9000 C
- 4 Smertal SA_REP 9000 C
- 5 Mustaine SA_REP 9000 C
- 34 Gregson SA_REP 8800 C
- 6 Harvey SA_REP 10000 D
- 25 Turcotte SA_REP 10000 D
- 12 Chancevente SA_REP 12000 D
- 205 Higgins AC_MGR 12000 D
- 22 Litrand SA_REP 10000 D
- 11 Sanchez SA_REP 10500 D
- 149 Zlotkey SA_MAN 10500 D
- 27 Rodriguez SA_REP 11000 D
- 36 Termede SA_REP 11000 D
- 174 Abel SA_REP 11000 D
- 39 Testorok SA_REP 11000 D
- 16 Gibbons SA_REP 11500 D
- 3 Grovlin SA_REP 12000 D
- 18 Jacobs SA_REP 13000 D
- 201 Hartstein MK_MAN 13000 D
- 1 Flertjan AC_REP 12000 D
- 101 Kochhar AD_VP 17000 E
- 102 De Haan AD_VP 17000 E
- 100 King AD_PRES 24000 E
- 53 rows selected.
- SELECT E.employee_id, E.LAST_NAME, job_id, E.SALARY, J.GRADE
- FROM EMPLOYEES E JOIN JOB_GRADES J
- ON E.SALARY
- BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL
- order by 5;
- =====
- output
- =====
- EMPLOYEE_ID LAST_NAME JOB_ID SALARY G
- ----------- ------------------------- ---------- ---------- -
- 143 Matos ST_CLERK 2600 A
- 144 Vargas ST_CLERK 2500 A
- 29 Loo Nam SA_REP 5500 B
- 142 Davies ST_CLERK 3100 B
- 19 Strandherst SA_REP 4500 B
- 200 Whalen AD_ASST 4400 B
- 107 Lorentz IT_PROG 4200 B
- 26 LeBlanc SA_REP 3700 B
- 141 Rajs ST_CLERK 3500 B
- 21 Brigade SA_REP 5750 B
- 124 Mourgos ST_MAN 5800 B
- 17 Pallomine SA_REP 5000 B
- 206 Gietz AC_ACCOUNT 8300 C
- 176 Vargas SA_REP 8600 C
- 8 Bergsteige SA_REP 8000 C
- 23 Armarillo SA_REP 7500 C
- 7 LeDuc SA_REP 7000 C
- 14 Torson SA_REP 7000 C
- 9 Gruber SA_REP 7000 C
- 30 Chan SA_REP 7000 C
- 178 Grants SA_REP 7000 C
- 180 de Man SA_REP 7000 C
- 33 Wandiko SA_REP 6700 C
- 15 Cornel SA_REP 6500 C
- 104 Ernst IT_PROG 6000 C
- 202 Fay MK_REP 6000 C
- 35 Krain SA_REP 8700 C
- 28 Young SA_REP 9000 C
- 41 Montoya SA_REP 9000 C
- 40 Whiteduck SA_REP 9000 C
- 103 Hunold IT_PROG 9000 C
- 4 Smertal SA_REP 9000 C
- 5 Mustaine SA_REP 9000 C
- 34 Gregson SA_REP 8800 C
- 6 Harvey SA_REP 10000 D
- 25 Turcotte SA_REP 10000 D
- 12 Chancevente SA_REP 12000 D
- 205 Higgins AC_MGR 12000 D
- 22 Litrand SA_REP 10000 D
- 11 Sanchez SA_REP 10500 D
- 149 Zlotkey SA_MAN 10500 D
- 27 Rodriguez SA_REP 11000 D
- 36 Termede SA_REP 11000 D
- 174 Abel SA_REP 11000 D
- 39 Testorok SA_REP 11000 D
- 16 Gibbons SA_REP 11500 D
- 3 Grovlin SA_REP 12000 D
- 18 Jacobs SA_REP 13000 D
- 201 Hartstein MK_MAN 13000 D
- 1 Flertjan AC_REP 12000 D
- 101 Kochhar AD_VP 17000 E
- 102 De Haan AD_VP 17000 E
- 100 King AD_PRES 24000 E
- 53 rows selected.
- Correct Answer:
- Correct
- SELECT employee_id, last_name, job_id, department_name, grade
- FROM employees
- LEFT JOIN departments USING (department_id)
- LEFT JOIN job_grades ON (salary BETWEEN lowest_sal AND highest_sal);
- NOTE: left joins required to include employees without departments or without salary since nulls are permitted for both these columns in employees table
- Alternate Solution:
- SELECT employee_id, last_name, job_id, department_name, grade
- FROM departments
- RIGHT JOIN employees USING (department_id)
- LEFT JOIN job_grades ON (salary BETWEEN lowest_sal AND highest_sal)
- Response Feedback: [None Given]
- Question 7
- Needs Grading
- Display the employee id and first name of every employee along with the first name and id of the employee's manager (if applicable).
- order by employee d
- only show rows with employee id greater than 30 and less than 110
- Selected Answer:
- SELECT M.employee_id as MGR#,
- M.last_name AS Manager,
- W.last_name AS Employee,
- w.employee_id as Emp#
- FROM employees W left JOIN employees M
- ON W.manager_id = M.employee_id
- where w.employee_id between 30 and 110
- order by 4;
- =====
- output
- =====
- MGR# MANAGER EMPLOYEE EMP#
- ---------- ------------------------- ------------------------- ----------
- 149 Zlotkey Chan 30
- 149 Zlotkey Wandiko 33
- 149 Zlotkey Gregson 34
- 149 Zlotkey Krain 35
- 149 Zlotkey Termede 36
- 149 Zlotkey Testorok 39
- 149 Zlotkey Whiteduck 40
- 149 Zlotkey Montoya 41
- King 100
- 100 King Kochhar 101
- 100 King De Haan 102
- 102 De Haan Hunold 103
- 103 Hunold Ernst 104
- 103 Hunold Lorentz 107
- 14 rows selected.
- Correct Answer:
- Correct
- SELECT e.employee_id, e.first_name, m.first_name as Manager_Last_Name, m.employee_id as Manager_ID
- FROM employees e LEFT JOIN employees m
- ON (e.manager_id = m.employee_id)
- WHERE e.employee_id < 110
- and e.employee_id > 30
- order by e.employee_id;
- EMPLOYEE_ID FIRST_NAME MANAGER_LAST_NAME MANAGER_ID
- ----------- -------------------- -------------------- ----------
- 33 Torey Eleni 149
- 34 Kaley Eleni 149
- 35 Hari Eleni 149
- 36 Ingrid Eleni 149
- 39 Lisa Eleni 149
- 40 Marthe Eleni 149
- 41 Inigo Eleni 149
- 100 Steven
- 101 Neena Steven 100
- 102 Lex Steven 100
- 103 Alexander Lex 102
- 104 Bruce Alexander 103
- 107 Diana Alexander 103
- 13 rows selected
- Response Feedback: [None Given]
- Question 8
- Needs Grading
- Display the employee id, last name of every employee and the name of the department and city that the employee is assigned to (if applicable).
- Selected Answer:
- SELECT E.Employee_id,
- E.last_name,
- E.job_id,
- L.city
- FROM employees E left join Departments D
- on E.department_id = D.department_id
- left join Locations L
- on L.location_id = D.location_id
- order by 1;
- ======
- output
- ======
- EMPLOYEE_ID LAST_NAME JOB_ID CITY
- ----------- ------------------------- ---------- ------------------------------
- 1 Flertjan AC_REP Seattle
- 3 Grovlin SA_REP Oxford
- 4 Smertal SA_REP Oxford
- 5 Mustaine SA_REP Oxford
- 6 Harvey SA_REP Oxford
- 7 LeDuc SA_REP Oxford
- 8 Bergsteige SA_REP Oxford
- 9 Gruber SA_REP Oxford
- 11 Sanchez SA_REP Oxford
- 12 Chancevente SA_REP Oxford
- 14 Torson SA_REP Oxford
- 15 Cornel SA_REP Oxford
- 16 Gibbons SA_REP Oxford
- 17 Pallomine SA_REP Oxford
- 18 Jacobs SA_REP Oxford
- 19 Strandherst SA_REP Oxford
- 21 Brigade SA_REP Oxford
- 22 Litrand SA_REP Oxford
- 23 Armarillo SA_REP Oxford
- 24 Mot SA_REP Oxford
- 25 Turcotte SA_REP Oxford
- 26 LeBlanc SA_REP Oxford
- 27 Rodriguez SA_REP Oxford
- 28 Young SA_REP Oxford
- 29 Loo Nam SA_REP Oxford
- 30 Chan SA_REP Oxford
- 33 Wandiko SA_REP Oxford
- 34 Gregson SA_REP Oxford
- 35 Krain SA_REP Oxford
- 36 Termede SA_REP Oxford
- 39 Testorok SA_REP Oxford
- 40 Whiteduck SA_REP Oxford
- 41 Montoya SA_REP Oxford
- 100 King AD_PRES Seattle
- 101 Kochhar AD_VP Seattle
- 102 De Haan AD_VP Seattle
- 103 Hunold IT_PROG Southlake
- 104 Ernst IT_PROG Southlake
- 107 Lorentz IT_PROG Southlake
- 124 Mourgos ST_MAN south San Francisco
- 141 Rajs ST_CLERK south San Francisco
- 142 Davies ST_CLERK south San Francisco
- 143 Matos ST_CLERK south San Francisco
- 144 Vargas ST_CLERK south San Francisco
- 149 Zlotkey SA_MAN Oxford
- 174 Abel SA_REP Oxford
- 176 Vargas SA_REP Oxford
- 178 Grants SA_REP
- 180 de Man SA_REP
- 200 Whalen AD_ASST Seattle
- 201 Hartstein MK_MAN Toronto
- 202 Fay MK_REP Toronto
- 205 Higgins AC_MGR Seattle
- 206 Gietz AC_ACCOUNT Seattle
- 54 rows selected.
- Correct Answer:
- Correct
- SELECT employee_id, last_name, department_name, city
- FROM employees
- LEFT JOIN departments using (department_id)
- LEFT JOIN locations using (location_id);
- Response Feedback: [None Given]
- Question 9
- Needs Grading
- Display the name of each city and the names of employees assigned to that city (if applicable).
- Selected Answer:
- SELECT E.first_name,
- E.last_name,
- L.city
- FROM employees E left join Departments D
- on E.department_id = D.department_id
- left join Locations L
- on L.location_id = D.location_id
- order by 1;
- =====
- output
- =====
- FIRST_NAME LAST_NAME CITY
- -------------------- ------------------------- ------------------------------
- Alexander Hunold Southlake
- Bil Gibbons Oxford
- Bill Smertal Oxford
- Bjorn Flertjan Seattle
- Bruce Ernst Southlake
- Carlos Rodriguez Oxford
- Charles Loo Nam Oxford
- Chris Cornel Oxford
- Conrad Bergsteige Oxford
- Curtis Davies south San Francisco
- Dan Chancevente Oxford
- Dave Mustaine Oxford
- Diana Lorentz Southlake
- Eleni Zlotkey Oxford
- Ellen Abel Oxford
- Francoise LeBlanc Oxford
- Gilles Turcotte Oxford
- Greg Torson Oxford
- Gus Grovlin Oxford
- Hari Krain Oxford
- Henri LeDuc Oxford
- Henry Harvey Oxford
- Ingrid Termede Oxford
- Inigo Montoya Oxford
- Jane Litrand Oxford
- Jennifer Whalen Seattle
- Jonathon Vargas Oxford
- Kaley Gregson Oxford
- Kevin Mourgos south San Francisco
- Kimberely Grants
- Kurt Gruber Oxford
- Lee Chan Oxford
- Lex De Haan Seattle
- Lisa Testorok Oxford
- Lyn Jacobs Oxford
- Malcom Young Oxford
- Marthe Whiteduck Oxford
- Matt Mot Oxford
- Michael Hartstein Toronto
- Miguel Sanchez Oxford
- Neena Kochhar Seattle
- Pat Fay Toronto
- Peter Vargas south San Francisco
- Randall Matos south San Francisco
- Russ Pallomine Oxford
- Sally Strandherst Oxford
- Shelley Higgins Seattle
- Spence de Man
- Steven King Seattle
- Thomas Brigade Oxford
- Tony Armarillo Oxford
- Torey Wandiko Oxford
- Trenna Rajs south San Francisco
- William Gietz Seattle
- 54 rows selected.
- Correct Answer:
- Correct
- Means show every city and if an employee is associated with the city then show the employee also. Means it is likely there are cities without employees;
- SELECT city, last_name, first_name
- FROM locations
- LEFT JOIN departments USING (location_id)
- LEFT JOIN employees USING (department_id);
- 73 rows. The last ones look like this showing cities and no employees
- Oxford Turcotte Gilles
- Oxford Vargas Jonathon
- south San Francisco Vargas Peter
- Oxford Wandiko Torey
- Seattle Whalen Jennifer
- Oxford Whiteduck Marthe
- Oxford Young Malcom
- Oxford Zlotkey Eleni
- Munich
- Mexico City
- Roma
- Stretford
- Hiroshima
- Singapore
- Whitehorse
- Venice
- Sao Paulo
- Geneva
- Bombay
- South Brunswick
- Utrecht
- Sydney
- Tokyo
- London
- Bern
- Beijing
- Seattle
- Seattle
- 73 rows selected
- Response Feedback: [None Given]
- Question 10
- Needs Grading
- Display all employees and all cities.
- Selected Answer:
- SELECT first_name || last_name as Employee, city
- FROM employees
- FULL JOIN departments using (department_id)
- FULL JOIN locations using (location_id);
- ======
- output
- ======
- EMPLOYEE CITY
- --------------------------------------------- ------------------------------
- StevenKing Seattle
- NeenaKochhar Seattle
- LexDe Haan Seattle
- AlexanderHunold Southlake
- BruceErnst Southlake
- DianaLorentz Southlake
- KevinMourgos south San Francisco
- TrennaRajs south San Francisco
- CurtisDavies south San Francisco
- RandallMatos south San Francisco
- PeterVargas south San Francisco
- EleniZlotkey Oxford
- EllenAbel Oxford
- JonathonVargas Oxford
- KimberelyGrants
- Spencede Man
- JenniferWhalen Seattle
- MichaelHartstein Toronto
- PatFay Toronto
- ShelleyHiggins Seattle
- WilliamGietz Seattle
- BjornFlertjan Seattle
- GusGrovlin Oxford
- BillSmertal Oxford
- DaveMustaine Oxford
- HenryHarvey Oxford
- HenriLeDuc Oxford
- ConradBergsteige Oxford
- KurtGruber Oxford
- MiguelSanchez Oxford
- DanChancevente Oxford
- GregTorson Oxford
- ChrisCornel Oxford
- BilGibbons Oxford
- RussPallomine Oxford
- LynJacobs Oxford
- SallyStrandherst Oxford
- ThomasBrigade Oxford
- JaneLitrand Oxford
- TonyArmarillo Oxford
- MattMot Oxford
- GillesTurcotte Oxford
- FrancoiseLeBlanc Oxford
- CarlosRodriguez Oxford
- MalcomYoung Oxford
- CharlesLoo Nam Oxford
- LeeChan Oxford
- ToreyWandiko Oxford
- KaleyGregson Oxford
- HariKrain Oxford
- IngridTermede Oxford
- LisaTestorok Oxford
- MartheWhiteduck Oxford
- InigoMontoya Oxford
- Seattle
- Seattle
- Beijing
- Bern
- London
- Tokyo
- Sydney
- Utrecht
- South Brunswick
- Bombay
- Geneva
- Sao Paulo
- Venice
- Whitehorse
- Singapore
- Hiroshima
- Stretford
- Roma
- Mexico City
- Munich
- 74 rows selected.
- Correct Answer:
- Correct
- SELECT city, employee_id, last_name, first_name
- FROM employees
- FULL JOIN departments using (department_id)
- FULL JOIN locations using (location_id);
- NOTE: full joins (left and right) required since some employees do not have location_ids and some locations may not have country_id and some countries do not have locations and some locations do not have employees
- Response Feedback: [N
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
