First article on oracle classical Query

This article will discuss and share with you some common classic queries of oracle SQL. You are welcome to add them. You can also share some classic queries. In this article, if you think there is any better solution to every problem, you are welcome to raise it in time. Communication and sharing can make progress together. Thank you!

--------------------------------------------------------------------------------

 

The instance table structure and table data used in this article are as follows:
The scott. emp employee table structure is as follows:

Name Type Nullable Default Comments
-------------------------------------------
Empno number (4) employee NUMBER
ENAME VARCHAR2 (10) Y employee name
JOB VARCHAR2 (9) Y JOB
Mgr number (4) Y Superior NUMBER
Hiredate date y employment DATE
Sal number (7,2) Y salary
Comm number (7, 2) Y Commission
Deptno number (2) Y department no.

Scott. dept Department table

Name Type Nullable Default Comments
-----------------------------------------
Deptno number (2) department no.
DNAME VARCHAR2 (14) Y Department name
Location of LOC VARCHAR2 (13) Y

Tip: salary = salary + Commission

The existing data in the scott. emp table is as follows:

 

SQL> select * from emp;
 
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----------------------------------------------------------------
7369 smith clerk 7902 800.00 20
7499 allen salesman 7698 1981-2-20 1600.00 300.00 30
7521 ward salesman 7698 1981-2-22 1250.00 500.00 30
7566 jones manager 7839 1981-4-2 2975.00 20
7654 martin salesman 7698 1250.00 1400.00 30
7698 blake manager 7839 1981-5-1 2850.00 30
7782 clark manager 7839 1981-6-9 2450.00 10
7788 scott analyst 7566 1987-4-19 4000.00 20
7839 king president 1981-11-17 5000.00 10
7844 turner salesman 7698 1500.00 0.00 30
7876 adams clerk 7788 1987-5-23 1100.00 20
7900 james clerk 7698 1981-12-3 950.00 30
7902 ford analyst 7566 1981-12-3 3000.00 20
7934 miller clerk 7782 1982-1-23 1300.00 10
102 EricHu Developer 1455 2011-5-26 1 5500.00 14.00 10
104 huyong PM 1455 2011-5-26 1 5500.00 14.00 10
105 WANGJING Developer 1455 2011-5-26 1 5500.00 14.00 10
 
17 rows selected
 

The existing data in the Scott. dept table is as follows:

 

SQL> select * from dept;
 
DEPTNO DNAME LOC
---------------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 50abc 50def
60 Developer HaiKou
 
6 rows selected
 

 

Use SQL to complete the following problem list:

 

1. List all departments with at least one employee.
2. List all employees with higher salaries than SMITH.
3. List the names of all employees and their immediate superiors.
4. list all employees whose employment date is earlier than their direct superiors.
5. List department names and employee information of these departments, and list departments without employees
6. List the names of all "Clerks" (Clerks) and their department names.
7. List jobs with a minimum salary of more than 1500.
8. List the names of employees who work in the SALES Department. Assume they do not know the Department Number of the SALES Department.
9. list all employees whose salaries are higher than the company's average salaries.
10. List and