DBMS Lab Tasks: SQL Queries and Outputs

Nemish Lathiya                                                        DBMS  20se02ce032 Lab - 13, 14 & 15 1. Display the employees whose job ID is the same as that of employee 141. Ans:- SQL> select employee_id, first_name, last_name, job_id from employee_master32    2  where job_id = (select job_id from employee_master32 where employee_id=141); Output:- EMPLOYEE_ID FIRST_NAME           LAST_NAME                 JOB_ID----------- -------------------- ------------------------- ----------    141 Trenna               Rajs                      ST_CLERK          142 Curties              davies                    ST_CLERK          143 Randall              Matos                     ST_CLERK 144 ter                Vargas                    ST_CLERK 2. Display employees whose job ID is the same as that of employee 141 and whose salary is greater than that of employee 143. Ans:- SQL> select employee_id, first_name, salary from employee_master32    2  where job_id = (select job_id from employee_master32 where employee_id=143); Output:- EMPLOYEE_ID FIRST_NAME               SALARY----------- -------------------- ----------        141 Trenna                     7000          142 Curties                    6200            143 Randall                    5200 144 ter                     10000 3. Display employee last name, job ID and salary of all employees whose salary is equal to the maximum salary.

Nemish Lathiya                                                        DBMS  20se02ce032 Ans. SQL> select last_name,job_id,salary  2  from employee_master32      3  where salary=(select max(salary) from employee_master32); Output:- LAST_NAME                 JOB_ID         SALARY------------------------- ---------- ----------Kochhar                   AD_VP           27000 4. Display all the departments that have the minimum salary less thanthat of department 110 . Ans:- SQL> select department_master32.department_name    2  from department_master32            3  join employee_master32 on  department_master32.department_id=employee_master32.department_id    4  join job_master32 on employee_master32.job_id=job_master32.job_id    5  where job_master32.min_salary <all (select job_master32.min_salary from job_master32 joinemployee_master32 on job_master32.job_id=employee_master32.job_id where  department_id=380)    6  group by department_name; Output:- DEPARTMENT_NAME------------------------------AdministrationITShippingMarketing 5. Write SQL query to find the job which is having highest average salary. Ans:- SQL> select job_id, job_title    2  from job_master32    3  where max_salary = (select avg(max_salary) from job_master32); Output:- no rows selected 6. Find the employees who earn the same salary as the maximum salary for each department.

Nemish Lathiya                                                        DBMS  20se02ce032 Ans:- SQL> select employee_id, first_name, salary, department_id, job_id    2  from employee_master32    3  where salary IN (select max(salary) from employee_master32 group by department_id); Output:- EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID JOB_ID----------- -------------------- ---------- ------------- ----------           201 Michael                   13000            20 MK_MAN           205 Shelley                   12000           110 AC_MGR          100 Steven                    24000            90 AD_PRES          101 Neena                     27000            10 AD_VP          103 Alexander                   990            60 IT_PROG          144 Peter                     10000            50 ST_CLERK          149 Eleni                     10500            80 SA_MAN 7. Enlist employees who are not IT programmers and whose salary is greater than that of any IT programmer. Ans:- SQL> select employee_id, job_id, first_name, last_name, salary    2  from employee_master32    3  where job_id <> 'IT_PROG' and salary >ALL (select salary from employee_master32 wherejob_id='IT_PROG'); Output:- EMPLOYEE_ID JOB_ID     FIRST_NAME           LAST_NAME                     SALARY----------- ---------- -------------------- ------------------------- ----------          149 SA_MAN     Eleni                Zlotkey                        10500          174 SA_REP     Ellen                Abel                           11000          205 AC_MGR     Shelley              Higgins                        12000          201 MK_MAN     Michael              Hartstein                      13000          102 AD_VP      Lex                  De Haan                        17000          100 AD_PRES    Steven               King                           24000          101 AD_VP      Neena                Kochhar                        27000 8. Display employees whose salary is less than the salary of all  employees with a job ID of IT_PROG and whose job is not IT_PROG. Ans:- SQL> select employee_id, job_id, first_name, last_name, salary    2  from employee_master32    3  where job_id <> 'IT_PROG' and salary <ALL (select salary from employee_master32 wherejob_id='IT_PROG'); Output:-

Nemish Lathiya                                                        DBMS  20se02ce032 EMPLOYEE_ID JOB_ID     FIRST_NAME           LAST_NAME                      SALARY ----------- ---------- -------------------- -------------------------  ----------         141 ST_CLERK   Trenna               Rajs                            3500        142 ST_CLERK   Curties              davies                          3100        143 ST_CLERK   Randall              Matos                           2600        144 ST_CLERK   Peter                Vargas                          2500 9. Enlist all employees who do not have any subordinates. Ans:- SQL> select employee_id, first_name, last_name    2  from employee_master32    3  where manager_id IS NULL; Output:- no rows selected 10. List out all the employees who work with Taylor. Ans:- SQL> select employee_id, first_name, last_name, job_id    2  from employee_master32    3  where last_name <> 'Taylor'    4  and job_id = (select job_id from employee_master32 where last_name='Taylor'); Output:- FIRST_NAME           LAST_NAME-------------------- -------------------------Ellen                AbelKimberely            Grant 11. Write SQL query to display employee number, first name  and salary of all employees who earn more than the average salary. Sort the result in descending order by salary. Ans:- SQL> select employee_id, first_name, salary    2  from employee_master32    3  where salary > (select avg(salary) from employee_master32)    4  order by salary DESC; Output:- EMPLOYEE_ID FIRST_NAME               SALARY----------- -------------------- ----------          101 Neena                     27000         100 Steven                    24000         102 Lex                       17000         201 Michael                   13000         205 Shelley                   12000

Nemish Lathiya                                                        DBMS  20se02ce032          149 Eleni                     10500         144 Peter                     10000 12. Write a query that gives the employee number and last  name of all employees who work in a department with any employee whose first name contains a ‘e’. Ans:- Output:- 13. Generate a report which contains the last name, department  number and job ID of all employees whose department location ID is 2500. Ans:- SQL> select employee_master32.employee_id, department_master32.department_id,employee_master32.job_id, employee_master32.last_name    2  from employee_master32    3  join department_master32 onemployee_master32.department_id=department_master32.department_id    4  join location_master32 on department_master32.location_id=location_master32.location_id    5  where location_master32.location_id=2500; Output:- EMPLOYEE_ID DEPARTMENT_ID JOB_ID     LAST_NAME----------- ------------- ---------- -------------------------          149            80 SA_MAN     Zlotkey          174            80 SA_REP     Abel          176            80 SA_REP     Taylor          178            80 SA_REP     Grant 14. Perform SQL query to display the department number, first  name and job ID for every employee in the Shipping department. Ans:- SQL> select employee_master32.employee_id, department_master32.department_id,employee_master32.first_name, employee_master32.job_id    2  from department_master32    3  join employee_master32 on  department_master32.department_id=employee_master32.department_id   4  where department_master32.department_name='Shipping'; Output:- EMPLOYEE_ID DEPARTMENT_ID FIRST_NAME           JOB_ID----------- ------------- -------------------- ----------          124            50 Kevin                ST_MAN         141            50 Trenna               ST_CLERK

Nemish Lathiya                                                        DBMS  20se02ce032          142            50 Curties              ST_CLERK         143            50 Randall              ST_CLERK         144            50 Peter                ST_CLERK 15. Perform SQL query to display the last name and salary of  every employee who reports to Kevin. Ans:- SQL> select employee_id, first_name, last_name, salary    2  from employee_master32    3  where manager_id = (select manager_id from employee_master32 where first_name='Kevin'); Output:- EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY----------- -------------------- ------------------------- ----------          124 Kevin                Mourgos                         5800 16. Perform SQL query to display the employee number, last  name and salary of all the employees who earn more than the average salary and who work in a department with any employee whose last name contains a ‘u’. Ans:- SQL> select employee_id, first_name, last_name, salary    2  from employee_master32    3  where job_id in(select job_id from employee_master32 where first_name like '%u%')    4  and salary>(select avg(salary) from employee_master32); Output:- EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY----------- -------------------- ------------------------- ----------          144 Peter                Vargas                         10000 17. Find the grade level of Neena Kochhar. Ans:- SQL> select employee_master32.employee_id, employee_master32.first_name,employee_master32.last_name, jobgrade_master32.gread_level    2  from employee_master32    3  join jobgrade_master32 on employee_master32.salary<jobgrade_master32.higest_sal    4   where employee_master32.first_name='Neena'; Output:- EMPLOYEE_ID FIRST_NAME           LAST_NAME                 GRE----------- -------------------- ------------------------- ---          101 Neena                Kochhar                   F

Nemish Lathiya                                                        DBMS  20se02ce032 18. List out all employees who joined after Peter Vargas. Ans:- SQL> select employee_id, first_name, last_name, hire_date, salary    2  from employee_master32    3  where hire_date > (select hire_date from employee_master32 where first_name='Peter'); Output:- EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE     SALARY----------- -------------------- ------------------------- --------- ----------          141 Trenna               Rajs                      05-DEC-97       7000         142 Curties              davies                    08-JAN-94       6200         143 Randall              Matos                     07-MAR-96       5200 19. Display last name and country name of all the employees  who are working in the same country where Shelley Higgins is working. Ans:- SQL> select employee_master32.employee_id, employee_master32.first_name,employee_master32.last_name, country_master32.country_name    2  from employee_master32    3  join department_master32 onemployee_master32.department_id=department_master32.department_id    4  join location_master32 on department_master32.location_id=location_master32.location_id    5  join country_master32 on location_master32.country_id=country_master32.country_id    6  where employee_master32.first_name='Shelley'; Output:- EMPLOYEE_ID FIRST_NAME           LAST_NAME----------- -------------------- -------------------------COUNTRY_NAME----------------------------------------          205 Shelley              Higgins United States of America 20. List out the employees who salary is higher than minimum  salary of Sales Representative. Ans:- SQL> select employee_master32.employee_id, employee_master32.first_name,employee_master32.last_name, employee_master32.salary    2  from employee_master32    3  join job_master32 on employee_master32.job_id=job_master32.job_id    4  where salary > (select min(salary) from employee_master32 join job_master32 onemployee_master32.job_id=job_master32.job_id where job_master32.job_title='Sales  Representative');

Nemish Lathiya                                                        DBMS  20se02ce032 Output:- EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY----------- -------------------- ------------------------- ----------          201 Michael              Hartstein                      13000         205 Shelley              Higgins                        12000         206 William              Gietz                           8300         100 Steven               King                           24000         101 Neena                Kochhar                        27000         102 Lex                  De Haan                        17000         144 Peter                Vargas                         10000         149 Eleni                Zlotkey                        10500 21. Perform SQL query to list out all the employees who are  colleagues(working in same department) of Trenna Rajs. Ans:- SQL> select employee_id, first_name, last_name    2  from employee_master32    3  where first_name <> 'Trenna' and    4  job_id=(select job_id from employee_master32 where first_name='Trenna'); Output:- EMPLOYEE_ID FIRST_NAME           LAST_NAME----------- -------------------- -------------------------          142 Curties              davies         143 Randall              Matos         144 Peter                Vargas 22. Perform SQL query to list out all the employees who are  working under the same manager as of Trenna Rajs. Ans:- SQL> select first_name,last_name   2  from employee_master32    3  where first_name<> 'Trenna'    4  and job_id=(select job_id from employee_master32 where first_name='Trenna'); Output:- FIRST_NAME           LAST_NAME-------------------- -------------------------Curties              daviesRandall              MatosPeter                Vargas 23. Update employee 114’s job and salary to match that of  employee 205. Ans:- SQL> update employee_master32 set job_id=(select job_id from employee_master32 where

Nemish Lathiya                                                        DBMS  20se02ce032 employee_id=205), salary=(select salary from employee_master32 where employee_id=205) whereemployee_id=144; Output:- 1 row updated. 24. Delete all the employees who are in a department where the  department name contains the string ’Public’. Ans:- SQL> delete from employee_master32 where department_id = (select department_id fromdepartment_master32 where department_name like '%Public%'); Output:- 0 rows deleted. 25. Display last name of all the employees who working in the  same city that of Jennifer Whalen. Ans:- SQL> select employee_master32.employee_id, employee_master32.last_name,  location_master32.city   2  from employee_master32    3  join department_master32 onemployee_master32.department_id=department_master32.department_id    4  join location_master32 on department_master32.location_id=location_master32.location_id    5  where location_master32.city=(select location_master32.city from location_master32    6  join department_master32 on location_master32.location_id=department_master32.location_id    7  join employee_master32 on  department_master32.department_id=employee_master32.department_id   8  where first_name='Jennifer'); Output:- EMPLOYEE_ID LAST_NAME                 CITY----------- ------------------------- ------------------------------          102 De Haan                   Seattle         101 Kochhar                   Seattle         200 Whalen                    Seattle         100 King                      Seattle         206 Gietz                     Seattle         205 Higgins                   Seattle