Simple Queries:
. List all the emp details
. List all the dept details
. List all job details
. List all the locations
. List out first name,last
name,salary, commission for all emps
. List out emp_id,last name,dept id
for all employees and rename emp id as
“ID of the emp”, last name as “Name of
the emp”, dept id as “dept ID”
. List out the employees annual
salary with their names only.
Where Conditions:
. List the details about “SMITH”
. List out the employees who are
working in dept 20
. List out the employees who are
earning salary between 3000 and 4500
. List out the employees who are
working in dept 10 or 20
. Find out the employees who are not
working in dept 10 or 30
. List out the employees whose name
starts with “S”
. List out the employees whose name
start with “S” and end with “H”
. List out the employees whose name
length is 4 and start with “S”
. List out the employees who are
working in dept 10 and draw the salaries more than 3500
. list out the employees who are not
receiving commission.
Order By Clause:
. List out the emp id, last name in
ascending order based on the emp id.
. List out the emp id, name in
descending order based on salary column
. list out the emp details according
to their last_name in ascending order and salaries in descending order
. list out the emp details according
to their last_name in ascending order and then on dept_id in descending order.
Group By & Having Clause:
. How many employees who are working
in different depts wise in the organization
. List out the dept wise maximum
salary, minimum salary, average salary of the emps
. List out the job wise maximum
salary, minimum salary, average salaries of the emps.
. List out the no.of employees
joined in every month in ascending order.
. List out the no.of employees for
each month and year, in the ascending order based on the year, month.
. List out the dept id having at least four emps.
. How many employees in January
month.
. How many employees who are joined
in January or September month.
. How many employees who are joined
in 1985.
. How many employees joined each
month in 1985.
. How many employees who are joined
in March 1985.
. Which is the dept id, having
greater than or equal to 3 employees joined in April 1985.
Sub-Queries
. Display the emp who got the
maximum salary.
. Display the employees who are
working in Sales dept
. Display the employees who are
working as “Clerk”.
. Display the employees who are
working in “New York”
. Find out no.of employees working
in “Sales” dept.
. Update the employees salaries, who
are working as Clerk on the basis of 10%.
. Delete the employees who are
working in accounting dept.
. Display the second highest salary
drawing emp details.
. Display the Nth highest salary
drawing emp details
Sub-Query operators: (ALL,ANY,SOME,EXISTS)
. List out the employees who earn
more than every emp in dept 30.
. List out the employees who earn
more than the lowest salary in dept 30.
. Find out whose dept has not emps.
. Find out which dept does not have
any emps.
Co-Related Sub Queries:
.Find out the
employees who earn greater than the average salary for their dept.
Joins
Simple join
.List our employees
with their dept names
.Display employees
with their designations (jobs)
.Display the
employees with their dept name and regional groups.
.How many employees
who are working in different depts and display with dept name.
.How many employees
who are working in sales dept.
.Which is the dept
having greater than or equal to 5 employees and display the dept names in
ascending order.
.How many jobs in the
organization with designations.
.How many employees
working in “New York”.
Non – Equi Join:
.Display emp details
with salary grades.
.List out the no. of
employees on grade wise.
.Display the employ
salary grades and no. of employees between 2000 to 5000 range of salary.
Self Join:
.Display the emp
details with their manager names.
.Display the emp
details who earn more than their managers salaries.
.Show the no. of
employees working under every manager.
Outer Join:
.Display emp details
with all depts.
.Display all
employees in sales or operation depts.
Set Operators:
.List out the
distinct jobs in Sales and Accounting depts.
.List out the ALL
jobs in Sales and Accounting depts.
.List out the common
jobs in Research and Accounting depts in ascending order.
Answers
. SQL > Select * from emp;
. SQL > Select * from dept;
. SQL > Select * from job;
. SQL > Select * from loc;
. SQL > Select first_name,
last_name, salary, commission from emp;
. SQL > Select emp_id “id of the
emp”, last_name “name", dept id as “dept id” from emp;
. SQL > Select last_name,
salary*12 “annual salary” from emp
. SQL > Select * from emp where
last_name=’SMITH’;
. SQL > Select * from emp where dept_id=20
. SQL > Select * from emp where
salary between 3000 and 4500
. SQL > Select * from emp where
dept_id in (20,30)
. SQL > Select last_name, salary,
commission, dept_id from emp where dept_id not in (10,30)
. SQL > Select * from emp where
last_name like ‘S%’
. SQL > Select * from emp where
last_name like ‘S%H’
. SQL > Select * from emp where
last_name like ‘S___’
. SQL > Select * from emp where
dept_id=10 and salary>3500
. SQL > Select * from emp where
commission is Null
. SQL > Select emp_id, last_name
from emp order by emp_id
. SQL > Select emp_id, last_name,
salary from emp order by salary desc
. SQL > Select emp_id, last_name,
salary from emp order by last_name, salary desc
. SQL > Select emp_id, last_name,
salary from emp order by last_name, dept_id desc
. SQL > Select dept_id, count(*),
from emp group by dept_id
. SQL > Select dept_id, count(*),
max(salary), min(salary), avg(salary) from emp group by dept_id
. SQL > Select job_id, count(*),
max(salary), min(salary), avg(salary) from emp group by job_id
. SQL > Select
to_char(hire_date,’month’)month, count(*) from emp group by
to_char(hire_date,’month’) order by month
. SQL > Select
to_char(hire_date,’yyyy’) Year, to_char(hire_date,’mon’) Month, count(*) “No.
of emps” from emp group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’)
. SQL > Select dept_id, count(*)
from emp group by dept_id having count(*)>=4
. SQL > Select
to_char(hire_date,’mon’) month, count(*) from emp group by
to_char(hire_date,’mon’) having to_char(hire_date,’mon’)=’jan’
. SQL > Select
to_char(hire_date,’mon’) month, count(*) from emp group by
to_char(hire_date,’mon’) having to_char(hire_date,’mon’) in (‘jan’,’sep’)
. SQL > Select to_char(hire_date,’yyyy’)
Year, count(*) from emp group by to_char(hire_date,’yyyy’) having
to_char(hire_date,’yyyy’)=1985
. SQL > Select
to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of
emps” from emp where to_char(hire_date,’yyyy’)=1985 group by
to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)
. SQL > Select
to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of
emps” from emp 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’)
. SQL > Select dept_id, count(*)
“No. of emps” from emp 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’), dept_id having count(*)>=3
. SQL > Select * from emp where
salary=(select max(salary) from emp)
. SQL > Select * from emp where
dept_id IN (select dept_id from dept where name=’SALES’)
. SQL > Select * from emp where
job_id in (select job_id from job where function=’CLERK’
. SQL > Select * from emp where
dept_id=(select dept_id from dept where location_id=(select location_id from
location where regional_group=’New York’))
. SQL > Select * from emp where
dept_id=(select dept_id from dept where name=’SALES’ group by dept_id)
. SQL > Update emp set
salary=salary*10/100 wehre job_id=(select job_id from job where
function=’CLERK’)
. SQL > delete from emp where
dept_id=(select dept_id from dept where name=’ACCOUNTING’)
. SQL > Select * from emp where
salary=(select max(salary) from emp where salary <(select max(salary) from
emp))
. SQL > Select distinct e.salary
from emp where & no-1=(select count(distinct salary) from emp where
sal>e.salary)
. SQL > Select * from emp where
salary > all (Select salary from emp where dept_id=30)
. SQL > Select * from emp where
salary > any (Select salary from emp where dept_id=30)
. SQL > Select emp_id, last_name,
dept_id from emp e where not exists (select dept_id from dept d where
d.dept_id=e.dept_id)
. SQL > Select name from dept d
where not exists (select last_name from emp e where d.dept_id=e.dept_id)
. SQL > Select emp_id, last_name,
salary, dept_id from emp e where salary > (select avg(salary) from emp where
dept_id=e.dept_id)
. SQL > Select emp_id, last_name,
name from emp e, dept d where e.dept_id=d.dept_id
. SQL > Select emp_id, last_name,
function from emp e, job j where e.job_id=j.job_id
. SQL > Select emp_id, last_name,
name, regional_group from emp e, dept d, location l where e.dept_id=d.dept_id
and d.location_id=l.location_id
. SQL > Select name, count(*)
from emp e, dept d where d.dept_id=e.dept_id group by name
. SQL > Select name, count(*)
from emp e, dept d where d.dept_id=e.dept_id group by name having name=’SALES’
. SQL > Select name, count(*)
from emp e, dept d where d.dept_id=e.dept_id group by name having count
(*)>=5 order by name
. SQL > Select function, count(*)
from emp e, job j where j.job_id=e.job_id group by function
. SQL > Select regional_group, count(*)
from emp e, dept d, location l where e.dept_id=d.dept_id and
d.location_id=l.location_id and regional_group=’NEW YORK’ group by
regional_group
. SQL > Select emp_id, last_name,
grade_id from emp e, salary_grade s where salary between lower_bound and
upper_bound order by last_name
. SQL > Select grade_id, count(*)
from emp e, salary_grade s where salary between lower_bound and upper_bound
group by grade_id order by grade_id desc
. SQL > Select grade_id, count(*)
from emp 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
. SQL > Select e.last_name
emp_name, m.last_name, mgr_name from emp e, emp m where e.manager_id=m.emp_id
. SQL > Select e.last_name
emp_name, e.salary emp_salary, m.last_name, mgr_name, m.salary mgr_salary from
emp e, emp m where e.manager_id=m.emp_id and m.salary<e.salary
. SQL > Select m.manager_id,
count(*) from emp e, emp m where e.emp_id=m.manager_id group by m.manager_id
. SQL > Select last_name,
d.dept_id, d.name from emp e, dept d where e.dept_id(+)=d.dept_id
. SQL > Select last_name,
d.dept_id, d.name from emp e, dept d where e.dept_id(+)=d.dept_id and
d.dept_idin (select dept_id from dept where name IN (‘SALES’,’OPERATIONS’))
. SQL > Select function from job
where job_id in (Select job_id from emp where dept_id=(select dept_id from dept
where name=’SALES’)) union Select function from job where job_id in (Select
job_id from emp where dept_id=(select dept_id from dept where
name=’ACCOUNTING’))
. SQL > Select function from job
where job_id in (Select job_id from emp where dept_id=(select dept_id from dept
where name=’SALES’)) union all Select function from job where job_id in (Select
job_id from emp where dept_id=(select dept_id from dept where
name=’ACCOUNTING’))
. SQL > Select function from job
where job_id in (Select job_id from emp where dept_id=(select dept_id from dept
where name=’RESEARCH’)) intersect Select function from job where job_id in
(Select job_id from emp where dept_id=(select dept_id from dept where
name=’ACCOUNTING’)) order by function
No comments:
Post a Comment