SQL query Exercises

--(1) Query all employee information in department 20th.

SELECT * from emp e where e.deptno=20;

--(2) query bonus (COMM) employee information that is higher than the salary (SAL).

SELECT * FROM EMP where comm>sal;

--(3) To inquire about 20% of the employee information of the bonus above the salary.

SELECT * FROM EMP where comm>sal*0.2;

--(4) To inquire the information of the employees in Department No. 10th who work in the Department of Manager and unit 20th in the clerk of jobs.

SELECT * FROM emp E

Where (e.deptno=10 and e.job= ' MANAGER ')

or (E.deptno=20 and e.job= ' clerk ')

--(5) Check that all jobs are not manager and clerk,

--Details of employees with a salary greater than or equal to 2000.

SELECT * FROM emp

where job not in (' MANAGER ', ' Clerk ') and sal>=2000;

--(6) Check the different types of employees who have bonuses.

SELECT * FROM EMP where comm are NOT null;

--(7) To inquire about the wages and bonuses of all employees.

Select (E.SAL+NVL (e.comm,0)) from EMP E;

--(8) Inquire about employees who have no bonuses or bonuses below 100.

SELECT * FROM EMP where comm is null or comm<100;

--(9) query employee information for employees who have a seniority greater than or equal to 10 years.

SELECT * from emp where (sysdate-hiredate)/365>=10;

--(10) query employee information and require the names of all employees to be displayed in uppercase letters.

Select Initcap (ename) from EMP;

Select Upper (substr (ename,1,1)) | | Lower (substr (ename,2)) from EMP;

--(11) Displays the names of all employees, the year and month of entry, sorted by the month in which the entry date is placed,

-If the month is the same, sort by the year of the entry.

Select Ename,to_char (hiredate, ' yyyy ') Year,to_char (hiredate, ' MM ') month

From EMP

Order BY Month,year;

--(12) query all employee information for entry in February.

SELECT * from emp where TO_CHAR (hiredate, ' MM ') = ' 02 '

--(13) To inquire about the working period of all employees since their entry into the company, in the form of "* * * * * * * * * * * * * * * *

Select E.ename,floor ((sysdate-e.hiredate)/365) | | ' Years

|| Floor (mod ((sysdate-e.hiredate), 365)/30) | | Months

|| Floor (MoD (mod ((sysdate-e.hiredate), 365), 30)) | | Day

From EMP E;

--(14) Inquire about employees who work in the same job but do not belong to the same department.

Select A.ename,a.job,a.deptno,b.ename,b.job,b.deptno

From EMP A,emp b

where A.job=b.job and a.deptno<>b.deptno;

--(15) query the details of each department and the number of departments and the average salary of the department.

Select D.deptno,count (e.empno), avg (e.sal), D.dname,d.loc

From EMP E, Dept D

where E.deptno=d.deptno

GROUP BY D.deptno,d.dname,d.loc

--(16) Consult the staff of department 10th and the information of the leader.

SELECT * from EMP where empno in (

Select Mgr from EMP where deptno=10) or deptno=10;

--(17) To inquire employee information about the average salary of a department.

SELECT * FROM emp

Where Sal in (the Select AVG (SAL) from the EMP Group by DEPTNO);

--(18) to inquire about employees with wages higher than the average salary of the department.

SELECT * FROM EMP E1

Where Sal > (select AVG (SAL) from EMP E2 where E2.deptno=e1.deptno);

--(19) To inquire about the employee's salary above the average wage of the department and the average salary of its department.

Select E.*,a.avgsal

From EMP E,

(select Deptno,avg (SAL) as avgsal from EMP Group by Deptno) a

where A.deptno=e.deptno and e.sal>a.avgsal;

--(20) statistics on the number of jobs and the average wage.

Select COUNT (*), E.job,avg (e.sal) from EMP E

GROUP BY E.job

-(21) to count the number and average wage of each job in each department.

Select Deptno,job,count (empno), avg (SAL) from EMP E

GROUP BY E.deptno,e.job

--(22) To inquire about the department of all employees who pay more than 1000.

SELECT * FROM dept where Deptno in

(Select Deptno from emp

where Deptno not in

(SELECT DISTINCT deptno from emp where sal<1000));

--(23) To inquire about the department's information and employee information for all employees with a salary greater than 1000.

SELECT * FROM emp e join Dept D

On D.deptno

In (select Deptno from emp

where Deptno not in

(SELECT DISTINCT deptno from emp where sal<1000))

and D.deptno=e.deptno;

--(24) To inquire about the department's information of all employees ' wages in 900~3000.

SELECT * FROM Dept

where Deptno not in (

Select Deptno from emp

Where Sal not between and 3000);

--(25) Inquire about employee information in the department where all wages are between 900~3000.

SELECT * FROM EMP a

where A.deptno in

(SELECT DISTINCT E.deptno from emp E

where e.sal between and 3000);

--(26) To inquire the information of each employee's leadership department.

Select d.* from Dept D

where D.deptno in

(SELECT DISTINCT E2.deptno from EMP e1,emp E2

where e1.empno=e2.mgr);

--(27) The department information with the largest number of queries.

SELECT * FROM Dept

where Deptno in

(Select Deptno from (SELECT COUNT (*) Count,deptno from EMP Group by DEPTNO)

where count in (select Max (count)

From (select COUNT (*) count, Deptno from EMP Group by Deptno));

--(28) inquire the employee information of the first 3 employees in the Department of No. 30th.

SELECT * FROM

(select Sal from EMP where deptno=30 order BY sal Desc) E

where rownum<4

-(29) Inquire about the ' JONES ' employees and all their direct and indirect employees.

Select e.* from emp E

Start with ename= ' JONES '

Connect by prior Empno=mgr;

---(30) Find information about Scott employees and their direct and indirect superior employees.

Select e.* from emp E

Start with ename= ' SCOTT '

Connect by prior Mgr=empno;

--(31) Querying the hierarchical relationship of all employees and leaders in a tree-like structure.

Select substr (Sys_connect_by_path (ename, '), 3), level

From EMP

Start with MGR is null

Connect by prior Empno=mgr;

--(32) Insert a record into the EMP table, employee number 1357, employee name is Oracle,

--Salary is 2050 yuan, department number is 20, the entry date is May 10, 2002.

--(33) revise the salary of each department employee to the average salary plus 1000 of the employee's department.

Update EMP E set sal=

1000+ (select AVG (SAL) from EMP where Deptno=e.deptno);

--(34) The enquiry work level is level 2, the work place after 1985 is Dallas's employee number,

--Name and salary.

Select E.ename,e.empno,e.sal from emp e,salgrade s,dept D

Where (E.sal between S.losal and S.hisal)

and (s.grade=2)

and To_char (e.hiredate, ' yyyy ') >1985

and E.deptno=d.deptno

and d.loc= ' DALLAS ';

--35. Department number of the department with the highest average salary

SELECT * FROM (

Select AVG (SAL) Avgsal,deptno

From EMP GROUP BY Deptno ORDER BY avgsal Desc)

where Rownum=1;

Select Deptno,avg (SAL) from the EMP GROUP by DEPTNO have avg (sal) = (

Select Max (SAL) avgsal

From EMP GROUP by DEPTNO)

--36, department name with the highest average salary

Select d.* from Dept D where Deptno in (

Select Deptno from EMP GROUP BY DEPTNO have avg (sal) = (

Select Max (SAL) avgsal

From EMP GROUP by DEPTNO)

--37. Department name of the department with the lowest average salary

Select d.* from Dept D where Deptno in (

Select Deptno from EMP GROUP BY DEPTNO have avg (sal) = (

Select min (avg (SAL)) avgsal

From EMP GROUP by DEPTNO)

--38. Department name of the department with the lowest average salary level

Select D.dname from Dept D

where D.deptno in

(Select A.deptno from

(select E.deptno from EMP e,salgrade s

Where (E.sal between S.losal and S.hisal)

Group BY E.deptno ORDER by AVG (S.grade)) a

where rownum=1);

--39. Department manager, the lowest-paid division name

Select Dname from dept where deptno=

(Select Deptno from

(select Deptno from emp where job= ' MANAGER ' GROUP by Deptno

Order by Min (sal)) where rownum=1)

--40. A manager's name that is higher than the average employee's maximum salary

Select ename from emp where sal>

(select Max (SAL) from EMP where job is not in

(' MANAGER ', ' President ') and job= ' MANAGER ' or job= ' president '

--41. Delete duplicate departments, but leave a

INSERT INTO Dept values ("DALLAS")

Select Deptno,dname,rowid from Dept

Delete from Dept D

where rowid<>

(select min (rowid) from dept where Dname=d.dname and D.loc=loc)

--42. Updating the employee's salary for his supervisor's salary, bonus

Update emp E set sal= (select Sal from emp where empno=e.mgr),

comm= (select comm from EMP where empno=e.mgr)

Update emp e set (SAL,COMM) = (select Sal,comm from emp where empno=e.mgr)

Rollback

SELECT * from EMP;