Oracle SQL statement Exercises

--1, select all the information of employees in department 30Select *  fromEmpwhereEMPNO= -;--2. The number of the employee who listed the position (MANAGER), nameSelectEMPNO, ename fromEmpwhereJob='MANAGER';--3. Finding employees with bonuses above wagesSelect *  fromEmpwhereCOMM>SAL;--4. Find out the sum of bonuses and wages for each employeeSelectEmpno,ename,comm+SAL fromEMP;--5. Identify the manager in department 10 (manager) and general Staff in department 20 (clerk)Select *  fromEmpwhere(DEPTNO=Ten  andJob='MANAGER')or(DEPTNO= -  andJob='Clerk'); --6. Find out if the department 10 is not a manager or an ordinary employee, and the salary is equal to 2000 of the EmployeesSelect *  fromEmpwhereJob not inch('MANAGER','Clerk') andSAL>= -;--7. Find out the different jobs of the employees with bonusSelect distinctJob fromEMP;--8. Find employees without bonuses or bonuses below 500Select *  fromEmpwhereCOMM is NULL orCOMM> -;--9, show the employee's name, according to their service life, the oldest employees in the front rowSelectEname,hiredate fromEmpOrder  byHireDatedesc;--10. Find employees who are employed on the third day of each month (e.g. 2009-5-29)Select *   fromEMPwhereEXTRACT ( Day  fromTo_date (Sysdate,'YYYY-MM-DD'))-EXTRACT ( Day  fromTo_date (HireDate,'YYYY-MM-DD'))=3; --11. Identify employees hired 25 years agoSelect *   fromEMPwhereEXTRACT ( Year  fromTo_date (Sysdate,'YYYY-MM-DD'))-EXTRACT ( Year  fromTo_date (HireDate,'YYYY-MM-DD'))> -; --12. All employee names are preceded by Dear, and the first letter of the name is capitalizedSelect 'Dear'||Initcap (ename) fromEMP;--13. Find employees with a name of 5 lettersSelect *  fromEmpwhereEname like '_____';--14. Find employees whose names do not have the letter RSelect *  fromEmpwhereEname not  like '%r%';--15. Display the first word of all employees ' namesSelectSUBSTR (ename,0,1) asName fromEMP;--16. The average wage of employees under the >500 of each department in the group statistics,Select avg(SAL) fromEmpwhereSAL> - ;--17, statistics departments under the average wage greater than 500 of the departmentSelectDEPTNO,avg(SAL) fromEmpGroup  byDEPTNO having avg(SAL)> -; --18, calculate the department 30 to get the most bonus employee bonusSelect Max(COMM) fromEmpwhereDEPTNO= -;--19. Figure out the name of the employee who received the most bonus in department 30SelectEname fromEmpwhereComminch(Select Max(COMM) fromEmpwhereDEPTNO= -);--20. Figure out the number of employees and the minimum wage for each positionSelectJobCount(*) asNummin(SAL) fromEmpGroup  byJob

View Code

Employee table: Records the basic information of an employee
EMP (Employee table)
NO Field type description
1 EMPNO Number (4) Employee ID
2 ename VARCHAR2 (10) indicates employee name
3 Job VARCHAR2 (9) indicates jobs
4 MGR Number (4) indicates an employee's leader ID
5 HireDate Date indicates employment dates
6 SAL Number (7,2) indicates the monthly salary, salary
7 COMM Number (7,2) means bonus or commission
8 DEPTNO Number (2) indicates the department code

Department Table: represents the specific information of a department
DEPT (Department table)
NO Field type description
1 DEPTNO Number (2) Department No.
2 dname VARCHAR2 (14) Department name
3 LOC VARCHAR2 (13) departmental Location

Bonus table: Represents the salary and bonus of an employee.

BONUS (Bonus table)
NO Field type description
1 ename VARCHAR2 (10) Employee name
2 Job VARCHAR2 (9) Employee work
3 SAL Number Employee wages
4 COMM Number employee bonus

A company is a hierarchical system that uses this table to represent a salary level
Salgrade (Salary scale table)
NO Field type description
1 GRADE number Rank name
2 losal number minimum wage for this level
3 Hisal Number maximum wage for this level

Topic:

1. Select all information for employees in department 30

2. The number of theemployee who listed the position (MANAGER), name

3 to find employees with bonuses above their salaries

4 , find out the sum of bonuses and wages for each employee

5 , identify the manager in department 10 (manager) and general Staff in department 20 (clerk)

6 , find out that department 10 is neither a manager nor an ordinary employee, and the employee with a salary greater than or equal to 2000

7 and find out the different jobs of the employees with bonuses

8. Find employees without bonuses or bonuses below 500

9, show the employee's name, according to their service life, the oldest employees in the front row

Ten to find the employee who was hired on the third day of the month (e.g. 2009-5-29)

One and find out who they hired 25 years ago.

12. All employee names are preceded by Dear, and the first letter of the name is capitalized

- and find employees with names of 5 letters

14. Find employees whose names do not have the letter R

the , display the first word of all employees ' names

- , the average wage of employees under each department, >500

17, statistics departments under the average wage greater than 500 of the department

18, calculate the department 30 to get the most bonus employee bonus

19. Figure out the name of the employee who received the most bonus in department 30

figure out the number of employees and the minimum wage for each position

Oracle SQL statement Exercises