SQL statement Exercises

Using the EMP and dept tables under the Scott user

--(1) To inquire all employee information of 20 department door.

SELECT * from EMP where deptno=20;
--(2) To inquire the employee number, employee name and department number of all employees who work for clerk. (Note case)
Select Empno,ename,deptno from emp where upper (Job) = ' Clerk '
--(3) query bonus (COMM) employee information that is higher than the salary (SAL).
SELECT * FROM EMP where comm>sal
--(4) To inquire about 20% of the employee information of the bonus above the salary.
SELECT * FROM EMP where comm>sal*0.2
--(5) To inquire the information of the employees in department 10th who work as manager and 20 departments in the clerk of jobs.
SELECT * from emp where (job= ' MANAGER ' and deptno=10)
OR (job= ' clerk ' and deptno=20)
--(6) Check the details of all employees who are not managers and clerk, and who have a salary greater than or equal to 2000.
SELECT * from emp where job is not in (' MANAGER ', ' clerk ')
and Sal >=2000
--(7) Check the different types of employees who have bonuses.
SELECT DISTINCT job from EMP where comm>0
--(8) To inquire about the wages and bonuses of all employees.
Select ENAME,NVL (sal,0) +NVL (comm,0) from EMP
--(9) Inquire about employees who have no bonuses or bonuses below 100.
SELECT * FROM EMP where comm is null or comm <100
--(10) Check employee information for the 2nd day of the month. Last_day
SELECT * from emp where Hiredate=last_day (hiredate)-1
(11) Query employee information that is greater than or equal to 10 years of seniority. Months_between
SELECT * from emp where trunc (Months_between (sysdate,hiredate)/12) >=10
SELECT * from EMP where ((sysdate-hiredate)/365) >=10
(12) Query employee information and require the names of all employees to be displayed in uppercase and lowercase letters in the first letter. Initcap
Select Initcap (ename) from EMP
(13) Query the employee's information for employees with a name that is exactly 6 characters.
SELECT * from emp where length (ename) =6
(14) Query employees whose names do not contain the letter "s". Like or instr
SELECT * from emp where ename '%s% '
SELECT * from emp where InStr (ename, ' S ') =0
(15) The employee information of "M" is queried for the second letter of the employee's name.
SELECT * from emp where ename like ' _m% '
(16) The first three characters of all employee names are queried.
Select substr (ename,1,3) from EMP
(17) Check the names of all employees and replace them with "s" if they contain the letter "s". Replace
Select Replace (ename, ' s ', ' s ') from EMP
(18) Check the employee's name and entry date, and sort by binate to the date of the entry.
Select Ename,hiredate from emp ORDER by hiredate ASC
(19) Display the names, jobs, wages and bonuses of all employees, sorted in descending order of job type, and sort in ascending order by salary if the work is the same.
Select Ename,job,sal,comm from emp order by job desc,sal ASC
(20) Displays the names of all employees, the year and month of entry, sorted by the month in which the entry date is, and the year in which the month is the same.
Select Ename,to_char (hiredate, ' yyyymm ') from the EMP order by
To_char (hiredate, ' mm '), To_char (hiredate, ' yyyy ')
(21) Enquiries about all employees who entered the job in February.
SELECT * from emp where TO_CHAR (hiredate, ' mm ') =2
(22) To inquire about the working period of all employees since their entry into the company, in the form of "* * * * * * * * * * * * * * * *
Select Aa.ename,aa.nian| | ' Year ' | | aa.yue| | ' Month ' | | aa.ri| | ' Days ' from (select Ename,trunc ((sysdate-hiredate)/365) as Nian,trunc (mod (Months_between (sysdate,hiredate),) as Yue, Trunc
(MoD ((sysdate-hiredate), ()) as RI from EMP) AA
(23) Query the department information of at least one employee.
SELECT * FROM dept where deptno on (select Deptno from EMP)
(24) To inquire about all employees who pay higher wages than Smith employees.
SELECT * from emp where sal > (select sal from emp where ename= ' SMITH ')
(25) The names of all employees and their direct superiors are queried.
Select A.ename,b.ename from emp a,emp b where a.mgr=b.empno
(26) Inquiry into the date of entry is earlier than the direct leadership of all staff information.
SELECT * from emp a where hiredate<
(select HireDate from emp b where a.mgr=b.empno)
(27) query all departments and their employee information, including those without employees.
Select D.deptno,d.dname,d.loc,e.empno,e.ename
From Dept d,emp E where D.deptno=e.deptno (+)
(28) To inquire all employees and their department information, including those who do not belong to any department of staff.
Select D.deptno,d.dname,d.loc,e.empno,e.ename
From Dept d,emp E where D.deptno (+) =e.deptno
(29) To inquire the names of all employees who work for clerk and their departments.
Select E.ename,d.dname from emp e,dept D where e.deptno=d.deptno
and e.job= ' clerk '
(30) To inquire the minimum wage of more than 2500 of various jobs.
Select Job,min (SAL) from the EMP group by Job have min (sal) >2500
(31) To inquire the department and employee information of the average salary below 2000.
Select AVG (SAL), job from EMP GROUP BY job have avg (SAL) <2000;
(32) Query the name information of the employee who is working in the sales department.
Select ename from emp where deptno= (select Deptno from dept where Dname= ' SALES ')
Select ename from emp e,dept D where E.deptno=d.deptno and D.dname= ' SALES '
(33) To inquire about all employees who pay more than the average salary of the company.
SELECT * from emp where sal > (select AVG (SAL) from EMP)
(34) To inquire about all employees who work in the same way as Smith employees.
SELECT * from emp where job= (select Job from emp where ename= ' SMITH ')
(35) List the names and wages of all employees who are equal to the wages of an employee in the 30 department.
Select Ename,sal from emp where Sal in (select Sal from emp where deptno=30)
(36) The employee's name and salary are queried for all employees whose wages are higher than 30 jobs.
Select Ename,sal from emp where sal > All (select Sal from emp where deptno=30)
Select Ename,sal from emp where sal > (select Max (SAL) from EMP where deptno=30)
(37) To inquire about the number of employees, average salary and working life of each department.
Select Deptno,count (empno), avg (SAL), Avg (Trunc ((sysdate-hiredate)/365))
From EMP GROUP BY Deptno
(38) Inquire about employees who work in the same job but do not belong to the same department.
SELECT DISTINCT a.* from emp a,emp b where a.job=b.job and A.deptno!=b.deptno
(39) Query the details of each department and the number of departments and the average salary of the department.
Select D.deptno,d.dname,d.loc,count (e.empno), avg (SAL) from Dept d,emp E where D.deptno=e.deptno
GROUP BY D.deptno,d.dname,d.loc
(40) Check the minimum wage for various jobs.
Select Job,min (SAL) from the EMP group by job
(41) To inquire the maximum wage of different types of jobs in each department.
Select Job,max (SAL) from the EMP group by job
(42) Consult the staff of department 10th and their leadership information.
Select A.ename,b.ename as manager from EMP A,emp b where A.mgr=b.empno and a.deptno=10
(43) Check the number and average salary of each department.
Select Deptno,count (*), AVG (SAL) from the EMP Group by Deptno
(44) To inquire about the employee's salary for a department's average salary.
Select Ename,sal from emp where Sal in (select AVG (SAL) from EMP Group by DEPTNO);
(45) To inquire about employees who pay more than the average salary of the department.
Select E1.ename,e1.job,e1.sal,e1.deptno from EMP E1 where sal> (select AVG (SAL) from EMP E2 where E1.deptno=e2.deptno);
(46) To inquire about the employee's salary above the average wage of the department and the average salary of its department.
SELECT * FROM EMP, (select AVG (SAL) Sal,deptno from EMP Group by Deptno) Avgsal where Emp.deptno=avgsal.deptno and emp.sal& Gt;avgsal.sal;
(47) The information of employees who pay more than the salary of an employee in department 20th.
SELECT * from emp where sal> (select min (sal) from EMP where deptno=20)
(48) Statistics of the number of employees and average wages of various types of jobs.
Select Job,count (empno), avg (SAL) from the EMP group by job
(49) to count the number of jobs and the average wage in each department.
Select Job,count (empno), avg (SAL), Deptno from EMP Group by Deptno,job;
(50) Inquire about salary, bonus and employee's salary and bonus in department 10th.
SELECT * FROM EMP, (select Sal,comm from emp where deptno=10) a where emp.sal=a.sal and Emp.comm=a.comm;
(51) To inquire the staff information of department with more than 5 department number.
SELECT * from EMP where deptno in (select Deptno from (select Deptno,count (*) to EMP GROUP by DEPTNO have count (*) > 5))
(52) Check the information of all the departments with employees ' salary greater than 2000.
SELECT * FROM dept where Deptno in (select Deptno from (select Deptno,min (SAL) from EMP Group by Deptno have min (sal) ; 2000))
--(53) To inquire about the department's information and employee information for all employees with a salary greater than 2000.
SELECT * from Emp,dept, ((select Deptno,min (SAL) from EMP Group by Deptno have min (sal) >2000)) a where DEPT.DEPTNO=A.D Eptno and Dept.deptno=emp.deptno;
--(54) To inquire about the department's information of all employees ' wages in 2000~3000.
SELECT * FROM Dept, (select Deptno,min (SAL), Max (SAL) from EMP Group by Deptno have min (sal) >2000 and Max (SAL) <3000) a where dept.deptno=a.deptno;
--(55) inquire about employee information in the Department of employees who have wages between 2000~3000.
SELECT * FROM EMP, (SELECT * from emp where sal>2000 and sal<3000) a where emp.deptno=a.deptno;
--(56) To inquire the information of each employee's leadership department.
SELECT * FROM dept where Deptno in (select E2.deptno from emp e1,emp E2 where e1.empno=e2.mgr);
--(57) The department information with the largest number of queries.
SELECT * FROM (select d.* to Dept D, (select Deptno,count (*) enum from EMP Group by Deptno) T where D.deptno=t.deptno Ord ER by t.enum desc) where rownum=1

SELECT * FROM dept where deptno= (select A.deptno from (select count (empno) Count,deptno from EMP Group by Deptno) a where a . count= (Select Max (count (empno)) from the EMP Group by
DEPTNO));


--(58) Inquire the employee information of the first 3 employees in the Department of No. 30th.
SELECT * FROM (select A.*,rownum as RN from EMP a where deptno=30 order by Sal) where rn<=3


--(59) query employee information for all employees in the order between 5 and 10.
SELECT * FROM (select A.*,rownum rn from (SELECT * to emp ORDER by Sal) a) where rn<=10 and rn>=5;
--(60) Insert a record into the EMP table, the employee number is 1357, the employee name is Oracle, the salary is 2050, the department number is 20, the entry date is May 10, 2002.
INSERT into EMP (empno,ename,sal,deptno,hiredate) VALUES (1357, ' Oracle ', 2050,20,to_date (' 2002-5-10 ', ' yyyy-mm-dd '))
--(61) Insert a record into the EMP table whose employee name is fan, the employee number is 8000, and the other information is the same as the Smith employee.
INSERT INTO EMP Select 8000, ' FAN ', job,mgr,hiredate,sal,comm,deptno from emp where ename= ' SMITH '
--(62) Revise the salary of each department employee to the average salary plus 1000 of the employee's department.
Update EMP A set sal=1000+ (select AVG (SAL) from EMP B GROUP by DEPTNO have A.deptno=b.deptno)

It's http://blog.csdn.net/zbdba/article/details/16806937.