Untitled

Question 1
Needs Grading

Remember you need te SQL and the resulting output to get any marks.

SET PAGESIZE 200 also

Execute he following command

SET AUTOCOMMIT ON (do this each time you log on for this lab) so any updates, deletes and inserts are automatically committed before you exit from Oracle.

When done answer YES in the answer section


Selected Answer:
yes

Correct Answer:
Correct
YES should appear as the answer to doing it

Response Feedback:	[None Given]
Question 2
Needs Grading

Did you read the previous instructions about auto commit.


Change the following insert


(1) make sure you exist as an employee (2) with a NULL salary and (3) 0.2 commission_pct  (4)in department 90 and (5) your email.


Leave the rest as is


INSERT INTO EMPLOYEES
  VALUES
    (777,'Ron','Tarr','youremail','123.123.1234',
       '31-DEC-99','IT_PROG', 22000,.15,102,90);

Selected Answer:
INSERT INTO employees

VALUES

(777, 'Ethan', 'Johnson', 'ejohnson16@myseneca.ca', '123.123.1234', SYSDATE, 'IT_PROG', NULL, 0.2, 102, 90);


Correct Answer:
Correct

INSERT INTO EMPLOYEES
  VALUES
    (777,'Ron','Tarr','youremail','123.123.1234',
       '31-DEC-99','IT_PROG',NULL,.2,102,90);

Response Feedback:	[None Given]
Question 3
Needs Grading

YOU MUST USE SUBQUERIES for these questions.

(Must also minimize the number of tables when answering. Example don't use 10 tables when 3 will do)

Display the last names of all employees who are in the same department as the employee named who has a name called CHan.

You are NOT asked to prompt the user, but make the SQL flexible as though a user will enter any case.

Alphabetical order from A to Z

Selected Answer:
SELECT last_name AS "Last Name"

FROM employees

WHERE department_id =

                (SELECT department_id

                FROM employees

                WHERE UPPER(last_name) = 'CHAN')

ORDER BY 1 ASC;


=====

output

=====

Last Name

-------------------------

Abel

Armarillo

Bergsteige

Brigade

Chan

Chancevente

Cornel

Gibbons

Gregson

Grovlin

Gruber

Harvey

Jacobs

Krain

LeBlanc

LeDuc

Litrand

Loo Nam

Montoya

Mot

Mustaine

Pallomine

Rodriguez

Sanchez

Smertal

Strandherst

Termede

Testorok

Torson

Turcotte

Vargas

Wandiko

Whiteduck

Young

Zlotkey


35 rows selected.


Correct Answer:
Correct
select last_name
from employees
where department_id = (select department_id
                      from employees
                      where UPPER(last_name) = UPPER('CHan')
                      )
order by last_name;


LAST_NAME
-------------------------
Abel
Armarillo
Bergsteige
Brigade
Chan
Chancevente
Cornel
Gibbons
Gregson
Grovlin
Gruber
Harvey
Jacobs
Krain
LeBlanc
LeDuc
Litrand
Loo Nam
Montoya
Mot
Mustaine
Pallomine
Rodriguez
Sanchez
Smertal
Strandherst
Termede
Testorok
Torson
Turcotte
Vargas
Wandiko
Whiteduck
Young
Zlotkey

 35 rows selected
Response Feedback:	[None Given]
Question 4
Needs Grading

Display the last name then first name of the lowest paid employee(s)

Selected Answer:
select last_name, first_name

from employees

where salary = (select min(salary)

from employees);

=====

output

======


LAST_NAME                 FIRST_NAME

------------------------- --------------------

Vargas                    Peter

Correct Answer:
Correct
LAST_NAME                 FIRST_NAME

------------------------- --------------------

Vargas                    Peter

SELECT LAST_NAME as "Last Name"

  FROM EMPLOYEES

  WHERE SALARY =

          (SELECT MIN(SALARY)

           FROM EMPLOYEES);

Response Feedback:	[None Given]
Question 5
Needs Grading

Change the salary of the both employees with a last name of Matos and Whalen to be 2500.

Selected Answer:
update employees

set salary = 2500

where upper(last_name) = 'MATOS' or upper(last_name) =  'WHALEN' ;


=====

OUTPUT

=====

2 rows updated.
Commit complete.

Correct Answer:
Correct
UPDATE EMPLOYEES

  SET SALARY = 2500

  WHERE LAST_NAME = 'Matos' OR LAST_NAME = 'Whalen';

Response Feedback:	[None Given]
Question 6
Needs Grading

Display the city that the lowest paid employee(s) are located in.

Selected Answer:
SELECT city

FROM locations

WHERE location_id IN (

    SELECT location_id

    FROM departments

    WHERE department_id IN (

        SELECT department_id

        FROM employees

        WHERE salary = (

            SELECT MIN(salary)

            FROM employees)));


======

output

======


CITY

------------------------------

south San Francisco

Seattle


Correct Answer:
Correct
SELECT DISTICT CITY,

  FROM LOCATIONS JOIN DEPARTMENTS USING (LOCATION_ID) JOIN

       EMPLOYEES USING (DEPARTMENT_ID)

  WHERE SALARY =

          (SELECT MIN(SALARY) FROM EMPLOYEES);

CITY

------------------------------

South San Francisco


also Seattle

Response Feedback:	[None Given]
Question 7
Needs Grading

Display the last name of the lowest paid employee(s) in each department

Selected Answer:

select last_name, DEPARTMENT_ID

from employees

where (department_id, salary) in

                                    (select department_id, min(salary)

                                    from employees

                                    group by department_id);


=====

OUTPUT

======

LAST_NAME                 DEPARTMENT_ID

------------------------- -------------

De Haan                              90

Kochhar                              90

Fay                                  20

Gietz                               110

Vargas                               50

Matos                                50

LeBlanc                              80

Lorentz                              60

Whalen                               10


9 rows selected.


Correct Answer:
Correct
SELECT LAST_NAME

  FROM EMPLOYEES

  WHERE SALARY IN

   (SELECT MIN(SALARY)

    FROM EMPLOYEES

    GROUP BY DEPARTMENT_ID);


This answer does not take into account an employee in another department that is not the minimum but matches the minimum.

Need to pass back 2 items from subquery. See what happens when I added 2 more columns to understand the output better.

WHERE (DEPARMENT_ID, SALARY) IN    ... DON'T FORGET TO BRACKET

   (SELECT DEPARTMENT_ID, MIN(SALARY)


LAST_NAME                 DEPARTMENT_ID     SALARY
------------------------- ------------- ----------
Kochhar                              90      17000
De Haan                              90      17000
Ernst                                60       6000
Lorentz                              60       4200
Matos                                50       2500
Vargas                               50       2500
Grants                                        7000
de Man                                        7000
Whalen                               10       2500
Fay                                  20       6000
Gietz                               110       8300
LeDuc                                80       7000
Gruber                               80       7000
Torson                               80       7000
LeBlanc                              80       3700 who is the lowest????
Chan                                 80       7000

 16 rows selected
Response Feedback:	[None Given]
Question 8
Needs Grading

Display the last name of the lowest paid employee(s) in each city

Selected Answer:
SELECT city,last_name, salary

FROM (

    SELECT city, last_name, salary

    FROM (employees e JOIN departments d

         ON e.department_id=d.department_id) JOIN locations l

         ON d.location_id=l.location_id

    )

WHERE city||salary IN (

    SELECT city || MIN(salary)

    FROM (employees e JOIN departments d

         ON e.department_id=d.department_id) JOIN locations l

         ON d.location_id=l.location_id

    GROUP BY city

    );


=====

output

=====


CITY                           LAST_NAME                     SALARY

------------------------------ ------------------------- ----------

Seattle                        Whalen                          2500

south San Francisco            Matos                           2500

south San Francisco            Vargas                          2500

Toronto                        Fay                             6000

Southlake                      Lorentz                         4200

Oxford                         LeBlanc                         3700


6 rows selected.

Correct Answer:
Correct
SELECT LAST_NAME

  FROM EMPLOYEES

  WHERE SALARY IN

        (SELECT MIN(SALARY)

           FROM LOCATIONS JOIN DEPARTMENTS USING (LOCATION_ID)

                JOIN EMPLOYEES USING (DEPARTMENT_ID)

           GROUP BY CITY);

LAST_NAME

-------------------------

Ernst

Lorentz

Vargas

Grants

de Man

Whalen

Fay

LeDuc


Response Feedback:	[None Given]
Question 9
Needs Grading

Display last name and salary for all employees who earn less than the lowest salary in ANY department.

Sort the output by top salaries first and then by last name.

Selected Answer:
select last_name, salary

from employees

 WHERE salary < ANY(

    SELECT MIN(salary)

    FROM employees

    GROUP BY department_id

    )

order by 2 desc, 1


=====

output

=====

LAST_NAME                     SALARY

------------------------- ----------

Hartstein                      13000

Jacobs                         13000

Chancevente                    12000

Flertjan                       12000

Grovlin                        12000

Higgins                        12000

Gibbons                        11500

Abel                           11000

Rodriguez                      11000

Termede                        11000

Testorok                       11000

Sanchez                        10500

Zlotkey                        10500

Harvey                         10000

Litrand                        10000

Turcotte                       10000

Hunold                          9000

Montoya                         9000

Mustaine                        9000

Smertal                         9000

Whiteduck                       9000

Young                           9000

Gregson                         8800

Krain                           8700

Vargas                          8600

Gietz                           8300

Bergsteige                      8000

Armarillo                       7500

Chan                            7000

Grants                          7000

Gruber                          7000

LeDuc                           7000

Torson                          7000

de Man                          7000

Wandiko                         6700

Cornel                          6500

Ernst                           6000

Fay                             6000

Mourgos                         5800

Brigade                         5750

Loo Nam                         5500

Pallomine                       5000

Strandherst                     4500

Lorentz                         4200

LeBlanc                         3700

Rajs                            3500

Davies                          3100

Matos                           2500

Vargas                          2500

Whalen                          2500


50 rows selected.


Correct Answer:
Correct
SELECT last_name, salary

FROM employees

WHERE salary < ANY (

  SELECT MIN(salary)

  FROM employees

  GROUP BY department_id

  )

ORDER BY salary DESC, last_name;


LAST_NAME                     SALARY

------------------------- ----------

Hartstein                      13000

Chancevente                    12000

Higgins                        12000

Abel                           11000

Armarillo                      11000

Brigade                        11000

Chan                           11000

Cornel                         11000

Flertjan                       11000

Gibbons                        11000

Gregson                        11000

Grovlin                        11000

Jacobs                         11000

Krain                          11000

LeBlanc                        11000

Loo Nam                        11000

Montoya                        11000

Mot                            11000

Mustaine                       11000

Pallomine                      11000

Rodriguez                      11000

Sanchez                        11000

Smertal                        11000

Termede                        11000

Testorok                       11000

Torson                         11000

Turcotte                       11000

Wandiko                        11000

Whiteduck                      11000

Zlotkey                        10500

Harvey                         10000

Litrand                        10000

Young                          10000

Gruber                          9000

Hunold                          9000

Strandherst                     9000

Taylor                          8600

Gietz                           8300

Bergsteige                      8000

Grants                          7000

LeDuc                           7000

de Man                          7000

Ernst                           6000

Fay                             6000

Mourgos                         5800

Whalen                          4400

Lorentz                         4200

Rajs                            3500

Davies                          3100

Matos                           2600

Vargas                          2500


 51 rows selected


Response Feedback:	[None Given]
Question 10
Needs Grading

Display last name, job title and salary for all employees whose salary matches any of the salaries from the IT Department.

Do NOT use Join method.

Sort the output by salary ascending first and then by last_name

Selected Answer:


SELECT last_name

    job_id,

    salary

FROM employees

WHERE salary = ANY(

    SELECT salary

    FROM employees

    WHERE department_id = (

        SELECT department_id

        FROM departments

        WHERE UPPER(department_name)='IT'

        )

    )

ORDER BY salary, last_name;


=====

output

=====

JOB_ID                        SALARY

------------------------- ----------

Lorentz                         4200

Ernst                           6000

Fay                             6000

Hunold                          9000

Montoya                         9000

Mustaine                        9000

Smertal                         9000

Whiteduck                       9000

Young                           9000


9 rows selected.


Correct Answer:
Correct
SELECT last_name, job_id, salary

FROM employees

WHERE salary = ANY (

  SELECT salary

  FROM employees

  WHERE job_id = 'IT_PROG'

  )

ORDER BY salary, last_name;

LAST_NAME                 JOB_ID         SALARY

------------------------- ---------- ----------

Lorentz                   IT_PROG          4200

Ernst                     IT_PROG          6000

Fay                       MK_REP           6000

Gruber                    SA_REP           9000

Hunold                    IT_PROG          9000

Strandherst               SA_REP           9000


NOTE: Did we really want to include those from IT as well


 6 rows selected