Basics
1.List all the employee details
1.List all the employee details
A: SQL > Select * from employee;
2.List all the department details
A: SQL > Select * from department;
3.List all job details
A: SQL > Select * from job;
4.List all the locations
A: SQL > Select * from loc;
5.List out first name,last name,salary, commission for all employees
A: SQL > Select first_name, last_name, salary, commission from employee;
6.List out employee_id,last name,department id for all employees and rename employee id as “ID of the employee”, last name as “Name of the employee”, department id as “department ID”
A: SQL > Select employee_id “id of the employee”, last_name “name",
department id as “department id” from employee;
7.List out the employee’s anuual salary with their names only.
A: SQL > Select last_name, salary*12 “annual salary” from employee
Where Conditions:
8.List the details about “SMITH”
A: SQL > Select * from employee where last_name=’SMITH’;
9.List out the employees who are working in department 20
A: SQL > Select * from employee where department_id=20
10.List out the employees who are earning salary between 3000 and 4500
A: SQL > Select * from employee where salary between 3000 and 4500
11.List out the employees who are working in department 10 or 20
A: SQL > Select * from employee where department_id in (10, 20)
12.Find out the employees who are not working in department 10 or 30
A: SQL > Select last_name, salary, commission, department_id from employee where department_id not in (10, 30)
13.List out the employees whose name starts with “S”
A: SQL > Select * from employee where last_name like ‘S%’
14.List out the employees whose name start with “S” and end with “H”
A: SQL > Select * from employee where last_name like ‘S%H’
15.List out the employees whose name length is 4 and start with “S”
A: SQL > Select * from employee where last_name like ‘S___’
16.List out the employees who are working in department 10 and draw the salaries more than 3500
A: SQL > Select * from employee where department_id=10 and salary>3500
17.List out the employees who are not receiving commission.
A: SQL > Select * from employee where commission is Null
Order By Clause:
18.List out the employee id, last name in ascending order based on the employee id.
A: SQL > Select employee_id, last_name from employee order by employee_id
19.List out the employee id, name in descending order based on salary column
A: SQL > Select employee_id, last_name, salary from employee order by salary desc
20.list out the employee details according to their last_name in ascending order and salaries in descending order
A: SQL > Select employee_id, last_name, salary from employee order by last_name, salary desc
21.List out the employee details according to their last_name in ascending order and then on department_id in descending order.
A: SQL > Select employee_id, last_name, salary from employee order by last_name, department_id desc
Group By & Having Clause:
22. How many employees who are working in different departments wise in the organization
A: SQL > Select department_id, count (*), from employee group by department_id
23.List out the department wise maximum salary, minimum salary, average salary of the employees
A: SQL > Select department_id, count (*), max (salary), min (salary), avg (salary) from employee group by department_id
24.List out the job wise maximum salary, minimum salary, average salaries of the employees.
A: SQL > Select job_id, count (*), max (salary), min (salary), avg (salary) from employee group by job_id
25.List out the no.of employees joined in every month in ascending order.
A: SQL > Select to_char (hire_date,’month’) month, count (*) from employee group by to_char (hire_date,’month’) order by month
26.List out the no.of employees for each month and year, in the ascending order based on the year, month.
A: 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.List out the department id having atleast four employees.
A: SQL > Select department_id, count (*) from employee group by department_id having count(*)>=4
28.How many employees in January month.
A: 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.How many employees who are joined in January or September month.
A: 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.How many employees who are joined in 1985.
A: 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.How many employees joined each month in 1985?
A: 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.How many employees who are joined in March 1985.
A: 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.Which is the department id, having greater than or equal to 3 employees joined in Apr 1985?
A: 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
Sub-Queries :
34.Display the employee who got the maximum salary.
A: SQL > Select * from employee where salary= (select max (salary) from employee)
35.Display the employees who are working in Sales department
A: SQL > Select * from employee where department_id IN (select department_id from department where name=’SALES’)
36.Display the employees who are working as “Clerk”.
A: SQL > Select * from employee where job_id in (select job_id from job where function =’CLERK’)
37.Display the employees who are working in “New York”
A: 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.Find out no.of employees working in “Sales” department.
A: SQL > Select count (*) “No. of employees” from employee where department_id=(select department_id from department where name=’SALES’)
39.Update the employees salaries, who are working as Clerk on the basis of 10%.
A: SQL > Update employee set salary=salary+ (salary*10/100) where job_id=(select job_id from job where function=’CLERK’)
40. Delete the employees who are working in accounting department.
A: SQL > delete from employee where department_id = (select department_id from department where name=’ACCOUNTING’)
41.Display the second highest salary drawing employee details.
A: SQL > Select * from employee where salary= (select max (salary) from employee where salary < (select max (salary) from employee))
42. Display the Nth highest salary drawing employee details
A: SQL > Select distinct a.salary from employee a where & n-1= (select count (distinct b.salary) from employee b where b.salary>a.salary)
Sub-Query operators: (ALL, ANY, SOME, EXISTS)
43. List out the employees who earn more than every employee in department 30.
A: SQL > Select * from employee where salary > all (Select salary from employee where department_id=30)
44. List out the employees who earn more than the lowest salary in department 30.
A: SQL > Select * from employee where salary > any (Select salary from employee where department_id=30)
45. Find out whose department has not employees.
A: 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.Find out which department does not have any employees.
A: SQL > Select name from department d where not exists (select last_name from employee e where d.department_id=e.department_id)
Co-Related Sub Queries:
47. Find out the employees who earn greater than the average salary for their department.
A: 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)
Joins
Simple join
48. List our employees with their department names
A: SQL > Select employee_id, last_name, name from employee e, department d where e.department_id=d.department_id
49. Display employees with their designations (jobs)
A: SQL > Select employee_id, last_name, function from employee e, job j where e.job_id =j.job_id
50. Display the employees with their department name and regional groups.
A: 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. How many employees who are working in different departments and display with department name.
A: SQL > Select name, count (*) from employee e, department d where d.department_id = e.department_id group by name
52. How many employees who are working in sales department.
A: SQL > Select name, count (*) from employee e, department d where d.department_id = e.department_id group by name having name=’SALES’
53. Which is the department having greater than or equal to 5 employees and display the department names in ascending order?
A: 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.How many jobs in the organization with designations.
A: SQL > Select function, count (*) from employee e, job j where j.job_id=e.job_id group by function
55.How many employees working in “New York”.
A: 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
Non–Equi Join:
56.Display employee details with salary grades.
A: 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.List out the no. of employees on grade wise.
A: 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.Display the employ salary grades and no. of employees between 2000 to 5000 range of salary.
A: 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
Self Join:
59.Display the employee details with their manager names.
A: 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. Display the employee details who earn more than their manager’s salaries.
A: 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<e.salary
61.Show the no. of employees working under every manager.
A: SQL > Select m.manager_id, count (*) from employee e, employee m where e.employee_id=m.manager_id group by m.manager_id
Outer Join:
62. Display employee details with all departments.
A: SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id (+) =d.department_id
63. Display all employees in sales or operation departments.
A: SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id (+) = d.department_id and d.department_id in (select department_id from department where name IN (‘SALES’,’OPERATIONS’))
Set Operators:
64. List out the distinct jobs in Sales and Accounting Departments.
A: 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. List out the ALL jobs in Sales and Accounting Departments.
A: 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. List out the common jobs in Research and Accounting Departments in ascending order.
A: 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
