1.List all the employee details.
SELECT * FROM EMPL;
2.List all the department details.
SELECT * FROM DEPARTMENT;
3.List all job details.
SELECT * FROM JOB;
4.List all the locations.
SELECT * FROM LOCATION;
5.List out first name,last name,salary, commission for all employees.
SELECT FIRST_NAME,LAST_NAME,SALARY,COMM FROM EMPL;
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”.
SELECT EMPLOYEE_ID AS “ID of the employee”,LAST_NAME AS “Name of
the employee”,DEPARTMENT_ID AS “department ID” FROM EMPL;
7.List out the employees anuual salary with their names only.
SELECT SALARY*12 AS "ANNUAL SAL",LAST_NAME FROM EMPL;
WHERE CLAUSE:
8.List the details about “SMITH”.
SELECT * FROM EMPL WHERE LAST_NAME='SMITH';
9.List out the employees who are working in department 20.
SELECT LAST_NAME FROM EMPL WHERE DEPARTMENT_ID=20;
10.List out the employees who are earning salary between 3000 and 4500
SELECT LAST_NAME FROM EMPL WHERE SALARY BETWEEN 3000 AND
4500;
11.List out the employees who are working in department 10 or 20.
SELECT LAST_NAME FROM EMPL WHERE DEPARTMENT_ID IN (20,30);
12.Find out the employees who are not working in department 10 or 30
SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME FROM EMPL WHERE
DEPARTMENT_ID NOT IN (10,30);
13.List out the employees whose name starts with “S”
SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME FROM EMPL WHERE
LAST_NAME LIKE'S%';
14.List out the employees whose name start with “S” and end with “H”.
SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME FROM EMPL WHERE
LAST_NAME LIKE'S%H';
15.List out the employees whose name length is 5 and start with “S”
SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME FROM EMPL WHERE
LAST_NAME LIKE'S____';
16.List out the employees who are working in department 10 and draw the salaries
more than 3500.
SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME FROM EMPL WHERE
DEPARTMENT_ID=10 AND SALARY>3500;
17.list out the employees who are not receiving commission.
SELECT FIRST_NAME,MIDDLE_NAME,LAST_NAME FROM EMPL WHERE
COMM IS NULL;
Order By Clause:
18.List out the employee id, last name in ascending order based on the employee id.
SELECT EMPLOYEE_ID,LAST_NAME FROM EMPL ORDER BY
EMPLOYEE_ID;
19.List out the employee id, name in descending order based on salary column.
SELECT EMPLOYEE_ID,LAST_NAME FROM EMPL ORDER BY SALARY
DESC;
20.list out the employee details according to their last_name in ascending order and
salaries in descending order
SELECT * FROM EMPL 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.
SELECT * FROM EMPL 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.
SELECT DEPARTMENT_ID,COUNT(*) FROM EMPL GROUP BY
DEPARTMENT_ID;
23.List out the department wise maximum salary, minimum salary, average salary
of the employees
SELECT DEPARTMENT_ID,MAX(SALARY),MIN(SALARY),AVG(SALARY)
FROM EMPL GROUP BY DEPARTMENT_ID;
24.List out the job wise maximum salary, minimum salary, average salaries of the
employees.
SELECT JOB_ID,MAX(SALARY),MIN(SALARY),AVG(SALARY) FROM EMPL
GROUP BY JOB_ID;
25.List out the no.of employees joined in every month in ascending order.
SELECT TO_CHAR(HIRE_DATE,'MM')MONTH,COUNT(*) FROM EMPL
GROUP BY TO_CHAR(HIRE_DATE,'MM')
ORDER BY MONTH;
26.List out the no.of employees for each month and year, in the ascending order
based on the year, month.
SELECT
TO_CHAR(HIRE_DATE,'YYYY')YEAR,TO_CHAR(HIRE_DATE,'MM')MONTH,CO
UNT(*)
FROM EMPL GROUP BY
TO_CHAR(HIRE_DATE,'YYYY'),TO_CHAR(HIRE_DATE,'MM') ORDER BY
YEAR,MONTH;
27.List out the department id having atleast four employees.
SELECT DEPARTMENT_ID,COUNT(*) FROM EMPL GROUP BY
DEPARTMENT_ID HAVING COUNT
(*)>=4;
28.How many employees in January month.
SELECT TO_CHAR(HIRE_DATE,'MON')MONTH,COUNT(*) FROM EMPL
GROUP BY TO_CHAR(HIRE_DATE,'MON') HAVING
TO_CHAR(HIRE_DATE,'MON')='JAN';
29.How many employees who are joined in FEBRUARY OR JUNE month.
SELECT TO_CHAR(HIRE_DATE,'MON')MONTH,COUNT(*) FROM EMPL
GROUP BY TO_CHAR(HIRE_DATE,'MON') HAVING
TO_CHAR(HIRE_DATE,'MON') IN ('FEB','JUN');
30.How many employees who are joined in 1985.
SELECT TO_CHAR(HIRE_DATE,'YYYY')YEAR,COUNT(*) FROM EMPL
GROUP BY TO_CHAR(HIRE_DATE,'YYYY') HAVING
TO_CHAR(HIRE_DATE,'YYYY')='1985';
31.How many employees joined each month in 1985.
SELECT
TO_CHAR(HIRE_DATE,'YYYY')YEAR,TO_CHAR(HIRE_DATE,'MM')MONTH,CO
UNT(*) FROM EMPL
GROUP BY TO_CHAR(HIRE_DATE,'YYYY'),TO_CHAR(HIRE_DATE,'MM')
HAVING TO_CHAR(HIRE_DATE,'YYYY')='1985';
32.How many employees who are joined in MaY 1985.
SELECT
TO_CHAR(HIRE_DATE,'YYYY')YEAR,TO_CHAR(HIRE_DATE,'MM')MONTH,CO
UNT(*) FROM EMPL
WHERE TO_CHAR(HIRE_DATE,'YYYY')='1985' AND
TO_CHAR(HIRE_DATE,'MM')='MAY'
GROUP BY TO_CHAR(HIRE_DATE,'YYYY'),TO_CHAR(HIRE_DATE,'MM');
33.Which is the department id, having greater than or equal to 3 employees joined
in April 1985.
SELECT
DEPARTMENT_ID,TO_CHAR(HIRE_DATE,'MM')MONTH,TO_CHAR(HIRE_DAT
E,'YYYY')YEAR,
COUNT(*) FROM EMPL WHERE TO_CHAR(HIRE_DATE,'MM')='APR' AND
TO_CHAR(HIRE_DATE,'YYYY')=1985
GROUP BY
TO_CHAR(HIRE_DATE,'YYYY'),TO_CHAR(HIRE_DATE,'MM'),DEPARTMENT_I
D HAVING COUNT(*)>=3;
Sub-Queries:
34.Display the employee who got the maximum salary.
SELECT EMPLOYEE_ID,LAST_NAME,SALARY FROM EMPL WHERE
SALARY IN
(SELECT MAX(SALARY) FROM EMPL);
35.Display the employees who are working in Sales department
SELECT EMPLOYEE_ID,LAST_NAME FROM EMPL WHERE JOB_ID IN
(SELECT JOB_ID FROM JOB WHERE FUNCTION='SALESPERSON');
36.Display the employees who are working as “Clerk”.
SELECT EMPLOYEE_ID,LAST_NAME FROM EMPL WHERE JOB_ID IN
(SELECT JOB_ID FROM JOB WHERE FUNCTION='CLERK');
37.Find out no.of employees working in “Sales” department.
SELECT COUNT(*) FROM EMPL WHERE DEPARTMENT_ID
IN (SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE
NAME='SALES');
38.Display the employees who are working in “New York”
SELECT EMPLOYEE_ID,LAST_NAME FROM EMPL WHERE
DEPARTMENT_ID
IN (SELECT DEPARTMENT_ID FROM DEPARTMENT WHERE
LOCATION_ID
IN (SELECT LOCATION_ID FROM LOCATION
WHERE REGIONAL_GROUP='NEW YORK'));
39.Update the employees salaries, who are working as Clerk on the basis of 10%.
UPDATE EMPL SET SALARY=SALARY+(SALARY*.10) WHERE JOB_ID
IN (SELECT JOB_ID FROM JOB WHERE FUNCTION='CLERK');
40.Delete the employees who are working in accounting department.
DELETE EMPL WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID FROM DEPARTMENT
WHERE NAME='ACCOUNTING');
41.Display the second highest salary drawing employee details.
SELECT * FROM EMPL WHERE SALARY=
(SELECT MAX(SALARY) FROM EMPL WHERE SALARY<
(SELECT MAX(SALARY) FROM EMPL);
42.Display the Nth highest salary drawing employee details.
SELECT * FROM ( SELECT ROWNUM RN,SALARY FROM
(SELECT DISTINCT SALARY FROM EMPL ORDER BY
SALARY DESC)) A WHERE A.RN=2;
Sub-Query operators: (ALL,ANY,SOME,EXISTS)
43.List out the employees who earn more than every employee in department 30.
SELECT * FROM EMPL WHERE SALARY >
ALL (SELECT SALARY FROM EMPL WHERE DEPARTMENT_ID=30);
44.List out the employees who earn more than the lowest salary in department 30.
SELECT * FROM EMPL WHERE SALARY >
(SELECT MIN(SALARY) FROM EMPL WHERE DEPARTMENT_ID=30);
45.Find out whose department has no employees.
SELECT DISTINCT D.DEPARTMENT_ID FROM DEPARTMENT D,EMPL E
WHERE D.DEPARTMENT_ID
NOT IN (SELECT DEPARTMENT_ID FROM EMPL);
46.Find out which department does not have any employees.
SELECT DISTINCT D.NAME FROM DEPARTMENT D,EMPL E
WHERE D.DEPARTMENT_ID
NOT IN (SELECT DEPARTMENT_ID FROM EMPL);
OR
SELECT NAME FROM DEPARTMENT
WHERE DEPARTMENT_ID
NOT IN (SELECT DEPARTMENT_ID FROM EMPL);
Co-Related Sub Queries:
47.Find out the employees who earn greater than the average salary for their
department.
SELECT * FROM EMPL E WHERE SALARY >
(SELECT AVG(SALARY) FROM EMPL E1
WHERE E.DEPARTMENT_ID=E1.DEPARTMENT_ID
GROUP BY DEPARTMENT_ID);
Joins
Simple joins
48.List our employees with their department names
select employee_id,last_name,d.department_id
from empl e,department d where
e.department_id=d.department_id;
49.Display employees with their designations (jobs)
select employee_id,last_name,function
from empl e,job j where
e.job_id=d.job_id;
50.Display the employees with their department name and regional groups.
select employee_id,last_name,name,regional_group
from empl 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.
select count(employee_id),name
from empl e,department d
where e.department_id=d.department_id
group by name;
52.How many employees who are working in sales department.
select count(employee_id) from empl
where department_id in
(select department_id from
department where name='SALES');
doubt:
Select name,count(*) from empl e,department d
where d.department_id=e.department_id and d.name='SALES';
53.Which is the department having greater than or equal to 5 employees and display
the department names in ascending order.
select name,count(*) from empl e,department d
where e.department_id=d.department_id
having count(*)>=5
group by name
order by name;
54.How many jobs in the organization with designations.
select count(*),function from empl e,job j
where e.job_id=j.job_id
group by function;
55.How many employees working in “New York”.
select count(*) from empl where department_id in
(select department_id from department where location_id in
(select location_id from location where regional_group='NEW YORK'));
doubt:
Select regional_group,count(*) from empl 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.
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.
58.Display the employ salary grades and no. of employees between 2000 to 5000
range of salary.
Self Join:
59.Display the employee details with their manager names.
Select e.last_name emp_name,m.last_name mgr_name from empl e,empl m where
e.manager_id=m.employee_id;
60.Display the employee details who earn more than their managers salaries.
Select e.last_name emp_name,e.salary emp_salary,
m.last_name mgr_name,m.salary mgr_salary from empl e,empl m
where e.manager_id=m.employee_id and e.salary>m.salary;
61.Show the no. of employees working under every manager.
Select m.manager_id,count(*) from empl e,empl m
where e.employee_id=m.manager_id group by m.manager_id;
Outer Join:
61.Display employee details with all departments.
select employee_id,last_name,name
from empl e,department d where
e.department_id(+)=d.department_id;
62.Display all employees in sales or operation departments.
Select last_name,d.department_id,d.name
from empl e,department d
where e.department_id=d.department_id
and d.dname in ('SALES','OPERATIONS');
Set Operators:
63.List out the distinct jobs in Sales and Accounting Departments.
Select function from job where job_id in
(Select job_id from empl where department_id=
(select department_id from department where name=’SALES’))
union
Select function from job where job_id in
(Select job_id from empl where department_id=
(select department_id from department where name=’ACCOUNTING’));
64.List out ALL the jobs in Sales and Accounting Departments.
Select function from job where job_id in
(Select job_id from empl 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 empl where department_id=
(select department_id from department where name=’ACCOUNTING’))
65.List out the common jobs in Research and Accounting Departments in ascending
order.
SQL > Select function from job where job_id in
(Select job_id from empl where department_id=
(select department_id from department where name=’RESEARCH’))
intersect
Select function from job where job_id in
(Select job_id from empl where department_id=
(select department_id from department where name='ACCOUNTING'));
