University:
College
Course:
College Computer Science
Academic year:

2023
Views:

8

Pages:

9
Author:

20se02ce032

Nemish Lathiya 20se02ce032 DBMS 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 20se02ce032 DBMS 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 than that 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 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 20se02ce032 DBMS 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 where job_id='IT_PROG'); Output:EMPLOYEE_ID JOB_ID FIRST_NAME LAST_NAME ----------- ---------- -------------------- ------------------------- ---------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 SALARY 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 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 Abel Kimberely 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 ----------- -------------------- ---------101 Neena 27000 100 Steven 24000 102 Lex 17000 201 Michael 13000 205 Shelley 12000 SALARY Nemish Lathiya 20se02ce032 149 Eleni 144 Peter DBMS 10500 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 on employee_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 ----------- ------------- -------------------- ---------124 50 Kevin ST_MAN 141 50 Trenna ST_CLERK JOB_ID Nemish Lathiya 20se02ce032 142 143 144 50 Curties 50 Randall 50 Peter DBMS ST_CLERK ST_CLERK 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 ----------- -------------------- ------------------------- ---------124 Kevin Mourgos 5800 SALARY 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 ----------- -------------------- ------------------------- ---------144 Peter Vargas 10000 17. SALARY 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 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 ----------- -------------------- ------------------------- --------- ---------141 Trenna Rajs 05-DEC-97 7000 142 Curties davies 08-JAN-94 6200 143 Randall Matos 07-MAR-96 5200 SALARY 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 on employee_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 on employee_master32.job_id=job_master32.job_id where job_master32.job_title='Sales Representative'); Nemish Lathiya 20se02ce032 Output:EMPLOYEE_ID FIRST_NAME LAST_NAME ----------- -------------------- ------------------------- ---------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 DBMS SALARY 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 davies Randall Matos Peter 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 20se02ce032 DBMS employee_id=205), salary=(select salary from employee_master32 where employee_id=205) where employee_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 from department_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 on employee_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