Answers for the Sql queries
1. SQL > Select * from employee;
2. SQL > Select * from department;
3. SQL > Select * from job;
4. SQL > Select * from loc;
5. SQL > Select first_name, last_name, salary, commission from employee;
6. SQL > Select employee_id “id of the employee”, last_name “name", department id as “department id” from employee;
7. SQL > Select last_name, salary*12 “annual salary” from employee
8. SQL > Select * from employee where last_name=’SMITH’;
9. SQL > Select * from employee where department_id=20
10. SQL > Select * from employee where salary between 3000 and 4500
11. SQL > Select * from employee where department_id in (20,30)
12. SQL > Select last_name, salary, commission, department_id from employee where department_id not in (10,30)
13. SQL > Select * from employee where last_name like ‘S%’
14. SQL > Select * from employee where last_name like ‘S%H’
15. SQL > Select * from employee where last_name like ‘S___’
16. SQL > Select * from employee where department_id=10 and salary>3500
17. SQL > Select * from employee where commission is Null
18. SQL > Select employee_id, last_name from employee order by employee_id
19. SQL > Select employee_id, last_name, salary from employee order by salary desc
20. SQL > Select employee_id, last_name, salary from employee order by last_name, salary desc
21. SQL > Select employee_id, last_name, salary from employee order by last_name, department_id desc
22. SQL > Select department_id, count(*), from employee group by department_id
23. SQL > Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id
24. SQL > Select job_id, count(*), max(salary), min(salary), avg(salary) from employee group by job_id
25. SQL > Select to_char(hire_date,’month’)month, count(*) from employee group by to_char(hire_date,’month’) order by month
26. SQL > Select to_char(hire_date,’yyyy’) Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’)
27. SQL > Select department_id, count(*) from employee group by department_id having count(*)>=4
28. SQL > Select to_char(hire_date,’mon’) month, count(*) from employee group by to_char(hire_date,’mon’) having to_char(hire_date,’mon’)=’jan’
29. SQL > Select to_char(hire_date,’mon’) month, count(*) from employee group by to_char(hire_date,’mon’) having to_char(hire_date,’mon’) in (‘jan’,’sep’)
30. SQL > Select to_char(hire_date,’yyyy’) Year, count(*) from employee group by to_char(hire_date,’yyyy’) having to_char(hire_date,’yyyy’)=1985
31. SQL > Select to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 group by to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)
32. SQL > Select to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 and to_char(hire_date,’mon’)=’mar’ group by to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)
33. SQL > Select department_id, count(*) “No. of employees” from employee where to_char(hire_date,’yyyy’)=1985 and to_char(hire_date,’mon’)=’apr’ group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’), department_id having count(*)>=3
34. SQL > Select * from employee where salary=(select max(salary) from employee)
35. SQL > Select * from employee where department_id IN (select department_id from department where name=’SALES’)
36. SQL > Select * from employee where job_id in (select job_id from job where function=’CLERK’
37. SQL > Select * from employee where department_id=(select department_id from department where location_id=(select location_id from location where regional_group=’New York’))
38. SQL > Select * from employee where department_id=(select department_id from department where name=’SALES’ group by department_id)
39. SQL > Update employee set salary=salary*10/100 wehre job_id=(select job_id from job where function=’CLERK’)
40. SQL > delete from employee where department_id=(select department_id from department where name=’ACCOUNTING’)
41. SQL > Select * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))
42. SQL > Select distinct e.salary from employee where & no-1=(select count(distinct salary) from employee where sal>e.salary)
43. SQL > Select * from employee where salary > all (Select salary from employee where department_id=30)
44. SQL > Select * from employee where salary > any (Select salary from employee where department_id=30)
45. SQL > Select employee_id, last_name, department_id from employee e where not exists (select department_id from department d where d.department_id=e.department_id)
46. SQL > Select name from department d where not exists (select last_name from employee e where d.department_id=e.department_id)
47. SQL > Select employee_id, last_name, salary, department_id from employee e where salary > (select avg(salary) from employee where department_id=e.department_id)
48. SQL > Select employee_id, last_name, name from employee e, department d where e.department_id=d.department_id
49. SQL > Select employee_id, last_name, function from employee e, job j where e.job_id=j.job_id
50. SQL > Select employee_id, last_name, name, regional_group from employee e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id
51. SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name
52. SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name having name=’SALES’
53. SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name having count (*)>=5 order by name
54. SQL > Select function, count(*) from employee e, job j where j.job_id=e.job_id group by function
55. SQL > Select regional_group, count(*) from employee e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id and regional_group=’NEW YORK’ group by regional_group
56. SQL > Select employee_id, last_name, grade_id from employee e, salary_grade s where salary between lower_bound and upper_bound order by last_name
57. SQL > Select grade_id, count(*) from employee e, salary_grade s where salary between lower_bound and upper_bound group by grade_id order by grade_id desc
58. SQL > Select grade_id, count(*) from employee e, salary_grade s where salary between lower_bound and upper_bound and lower_bound>=2000 and lower_bound<=5000 group by grade_id order by grade_id desc
59. SQL > Select e.last_name emp_name, m.last_name, mgr_name from employee e, employee m where e.manager_id=m.employee_id
60. SQL > Select e.last_name emp_name, e.salary emp_salary, m.last_name, mgr_name, m.salary mgr_salary from employee e, employee m where e.manager_id=m.employee_id and m.salary
61. SQL > Select m.manager_id, count(*) from employee e, employee m where e.employee_id=m.manager_id group by m.manager_id
62. SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id(+)=d.department_id
63. SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id(+)=d.department_id and d.department_idin (select department_id from department where name IN (‘SALES’,’OPERATIONS’))
64. SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’SALES’)) union Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’ACCOUNTING’))
65. SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’SALES’)) union all Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’ACCOUNTING’))
66. SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’RESEARCH’)) intersect Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name=’ACCOUNTING’)) order by function
No comments :
Post a Comment