SQL statement Exercises

2. List the names, numbers and department numbers of all Clerks (clerk):

SELECT Ename,empno,deptno from emp WHERE UPPER (Job) = ' clerk ';

5. Find out the details of all the clerks (clerk) in all manager and department 20 in department 10

SELECT * from EMP WHERE (deptno=10 and job= ' MANAGER ') or (deptno=20 and job= ' clerk ');

6. Identify all the managers in department 10, all the clerks in department 20, who are neither managers nor clerks, but who have salaries greater than or equal to 2000 of all employees ' information:

SELECT * from EMP WHERE

(Deptno=10 and job= ' MANAGER ') OR

(Deptno=20 and job= ' clerk ') OR

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

7. Find out the different work of employees who receive Commission (COMM):[distinct-> Eliminate duplicate keywords]

SELECT DISTINCT job from EMP WHERE comm are not NULL;

8. Find employees who do not receive commissions or commissions less than 100:

SELECT * from EMP WHERE comm is NULL OR comm<100;

9. Find all employees employed on the third day of each month:

• Use the Last_day () function

SELECT * from emp WHERE (Last_day (HireDate)-2) =hiredate;

10. Identify employees employed prior to 12:

• Note the use of Months_between (today, hire date)

SELECT * from emp WHERE months_between (sysdate,hiredate)/12>12;

11. Display the employee's name in first letter capitalization

SELECT Initcap (ename) from EMP;

12. Display the names of employees with exactly 5 characters

SELECT ename from emp WHERE LENGTH (ename) = 5;

13. Display the name of the employee without "R":

SELECT ename from emp WHERE ename does like '%r% ';

14. Displays the first 3 characters of all employee names:

SELECT SUBSTR (ename,0,3) from EMP;

15. Display the names of all employees and replace all "A" with "X";

SELECT REPLACE (ename, ' A ', ' X ') from EMP;

16. Name and date of employment for employees with 10 years of service life:

SELECT ename,hiredate from emp WHERE months_between (sysdate,hiredate)/12 >10;

17. Show employee details, sorted by name:

SELECT * from emp ORDER by ename;

18. Display the employee's name and the date of employment, and according to their service life, the oldest staff in the first line:

SELECT ename,hiredate from emp ORDER by HireDate;

19. Displays the names, jobs and salaries of all employees, sorted in descending order of work, or by salary if the work is the same:

SELECT ename,job,sal from emp ORDER by Job desc,sal;

20. Display the names of all employees, enter the company's year and month, sorted by the year of employment, and if the year is the same, the first month's employees are ranked at the front:

• Use the To_char () function

SELECT Ename,to_char (hiredate, ' yyyy ') Year,to_char (hiredate, ' MM ') Mon from emp ORDER by Year,mon;

21. Displays the daily salary for all employees in the one month period of 30 days, and ignores the remainder:

ROUND () rounding

SELECT Ename,round (SAL/30) daily salary from EMP;

22. Identify all employees employed in February (in any year):

SELECT * from emp WHERE to_char (hiredate, ' MM ') = 2;

23. For each employee, show the number of days they joined the company:

SELECT ROUND (sysdate-hiredate) from EMP;

24. Display names of all employees with "A" anywhere in the Name field:

SELECT ename from emp WHERE ename like '%a% ';

25. Show the years of service for all employees:

• Year: Find out the total number of months/12 and generate decimals, and cannot be rounded

• Month: 12 of the remainder

SELECT ename,

TRUNC (Months_between (sysdate,hiredate)/12) year,

TRUNC (MOD (Months_between (sysdate,hiredate)) Mon

from EMP;

/*-------------complex query, subquery, multi-Table Association--------------*/

26. List all Department and department information for at least three employees. [!!]

SELECT D.*,ed.count from Dept D, (

SELECT Deptno,count (empno) count from emp GROUP by Deptno have COUNT (empno) >3) Ed

WHERE D.deptno=ed.deptno;

27. List all employees who pay more than "ALLEN"

SELECT sal from emp WHERE ename= ' ALLEN '; Sub-query

SELECT * from EMP

where Sal> (SELECT sal from emp WHERE ename= ' ALLEN ');

28. List the names of all employees and their superiors:

SELECT E.ename employees, M.ename Superior

From EMP e,emp m

WHERE E.mgr=m.empno (+);

because King does not have a superior, so adding a (+) number indicates a left connection

29. List the number, name, and department name of all employees whose employment date is earlier than the direct superior

SELECT E.ename,e.empno,d.dname

From EMP e,emp m,dept D

WHERE E.mgr=m.empno and E.deptno=d.deptno and E.hiredate<m.hiredate

;

30. List the department names and information about the employees of these departments, and list the departments that have no employees.

• Left and right related issues, even if no employees are shown

SELECT D.deptno,d.dname,e.empno,e.ename

From Dept D,emp E

WHERE D.deptno=e.deptno (+);

31. List the names and department names of "clerk" and the number of departments:

• Find out the name and department number of all clerks:

SELECT E.ename,d.dname,ed.cou

From EMP e,dept D, (SELECT Deptno,count (empno) cou from emp GROUP by Deptno) Ed

WHERE E.deptno=d.deptno and job= ' clerk ' and Ed.deptno=d.deptno;

32. List of jobs with a minimum salary greater than 1500 and the total number of employees engaged in the work

• Grouped by work, the minimum wage is greater than 1500

SELECT Job,min (SAL) from EMP

GROUP by Job have MIN (SAL) >1500;

• In accordance with

SELECT E.job,count (empno)

From EMP E

WHERE Job in (

SELECT Job from EMP

GROUP by Job have MIN (SAL) >1500

)

GROUP by E.job;

33. List the name of the employee who is working in the department sales department, assuming you do not know the department number of the sales department

• Query the department number of the sales department according to the Dept Table (subquery)

SELECT Deptno

From Dept

WHERE dname= ' SALES ';

• The sub-query above

SELECT ename from EMP

WHERE deptno= (

SELECT Deptno

From Dept

WHERE dname= ' SALES ');

34. List all employees with salary above average salary, department, supervisor, and salary level of the company.

• Find the average salary:

SELECT AVG (SAL) from EMP;

• List all employee information for salaries above average

SELECT * from emp WHERE sal> (

SELECT AVG (SAL) from EMP

);

• Associate with the Department table and query the department's information (note that King is the right connection without superior attention)

SELECT E.*,d.dname,d.loc,m.ename

From EMP e,dept d,emp m

WHERE

E.mgr=m.empno (+) and

E.deptno=d.deptno and

E.sal> (

SELECT AVG (SAL) from EMP

);

• Find out the employee's salary level

SELECT E.*,d.dname,d.loc,m.ename,s.grade

From EMP e,dept d,emp m,salgrade S

WHERE

E.mgr=m.empno (+) and

E.deptno=d.deptno and

E.sal> (SELECT AVG (SAL) from EMP)

and e.sal between S.losal and S.hisal

;

35. List all employees and department names that are in the same job as "SCOTT":

· The work Scott is doing

SELECT job from emp WHERE ename= ' SCOTT ';

• Make sub-queries

SELECT e.*,d.dname from EMP e,dept D

where e.job= (SELECT job from emp WHERE ename= ' SCOTT ')

and E.deptno=d.deptno;

• The above results exist for Scott and should be removed

SELECT e.*,d.dname from EMP e,dept D

where e.job= (SELECT job from emp WHERE ename= ' SCOTT ')

and E.deptno=d.deptno

and e.ename!= ' SCOTT ';

36. Name and salary of all employees whose salaries are equal to staff salaries in department 30

• Find out the employee's salary in department 30

SELECT sal from EMP WHERE deptno=30;

• Sub-query

SELECT ename,sal from EMP

where Sal in (SELECT sal from emp WHERE deptno=30)

and deptno!=30;

37. Name and salary, department name of employees who pay more than the salaries of all employees working in department 30

• The previous program was modified using >all , which is larger than the maximum

SELECT ename,sal from EMP

where Sal >all (SELECT sal from emp WHERE deptno=30)

and deptno!=30;

• Re-associate with dept to find out the department name

SELECT E.ename,e.sal,d.dname

From EMP e,dept D

where E.sal >all (SELECT sal from emp WHERE deptno=30)

and e.deptno!=30

and E.deptno=d.deptno;

38. List the number of employees, average wages and duration of service for each department

• Number of employees working in each department:

SELECT D.dname,count (E.empno)

From EMP e,dept D

WHERE E.deptno=d.deptno

GROUP by D.dname;

• Finding the average salary and service life

SELECT D.dname,count (e.empno), avg (SAL), AVG (Months_between (sysdate,hiredate)/12) year

From EMP e,dept D

WHERE E.deptno=d.deptno

GROUP by D.dname;

39. List all employees ' names, departments and wages

SELECT e.ename,d.dname,e.sal from emp e,dept d WHERE E.deptno=d.deptno;

40. List of trust information and department numbers in all sectors

• List the number of people in all departments

SELECT deptno dno,count (empno) cou

From EMP

GROUP by Deptno;

• Use the table above as a temporary table: "Since 40 departments have no employees, 0 should be used to indicate"

Select D.*,NVL (ed.cou,0) from Dept D, (select Deptno dno,count (empno) cou

From EMP

GROUP by Deptno) Ed

WHERE D.deptno=ed.dno (+);

41. List the minimum wage for each job and the name of the employee engaged in the work:

• Find the minimum wage by job grouping

SELECT MIN (SAL) m from the EMP GROUP by job;

• Sub-query

SELECT E.ename from EMP E

WHERE E.sal in (a SELECT MIN (SAL) m from the EMP GROUP by Job);

42. List the manager's minimum salary for each department:

• Find out the payroll for each department manager, grouped by department

SELECT Deptno,min (SAL) from the EMP WHERE job= ' MANAGER ' GROUP by Deptno;

43, list all employees annual salary, according to the annual salary from low to high ranking:

• Pay attention to bonuses and bonuses to be treated with the NVL function

SELECT ename, (SAL+NVL (comm,0)) *12 income from EMP ORDER by income

44. Check out the supervisor of an employee and ask for more than 3000 of the salary in these supervisors

SELECT DISTINCT m.*

From EMP e,emp m

WHERE E.mgr=m.empno and m.sal>3000;

45, to find out the department name with the ' S ' character of the department employees total wages, the number of departments

• Query the department name in the department table and use the fuzzy query to determine the department number

SELECT deptno from dept WHERE dname like '%s% ';

• According to the above as sub-query, to find out the total wage and the number of departments

SELECT SUM (SAL), COUNT (empno)

From EMP E

where E.deptno in (SELECT deptno from dept WHERE dname like '%s% ')

GROUP by Deptno;

46. Pay a raise of 10% for those who have been in office for more than 10 years;

UPDATE emp SET sal=sal*1.1 WHERE months_between (sysdate,hiredate)/12>10;

SQL statement Exercises