Oracle SQL: Chapter 5 of classical query practitioners

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!

Next four articles:

[Recommended] oracle SQL: html "> first article on classical Query

[Recommended] oracle SQL: the second article of classical query practitioners

[Recommended] oracle SQL: The third article of the classical query operator

[Recommended] oracle SQL: article 4 of classical query practitioners

Data Query in this article is a complex business and difficult. Please continue to work hard. Through the series that I set up for you, you can perform tests on each article and each question in a step-by-step manner, exercise carefully. I believe that you are familiar with common, classic, and complex SQL statements!

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

This document uses the HR instance data that comes with ORACLE. The table structure used in this article is as follows:
Table Name: REGIONS

Serial number
Column name
Data Type
Length
Decimal places
Identifier
Primary Key
Null allowed
Default Value
Description
 
1
REGION_ID
NUMBER



Yes
No


 
2
REGION_NAME
VARCHAR2
25



Yes


 

 

Table Name: COUNTRIES

Serial number
Column name
Data Type
Length
Decimal places
Identifier
Primary Key
Null allowed
Default Value
Description
 
1
COUNTRY_ID
CHAR
2


Yes
No


 
2
COUNTRY_NAME
VARCHAR2
40



Yes


 
3
REGION_ID
NUMBER




Yes


 

Table Name: LOCATIONS

Serial number
Column name
Data Type
Length
Decimal places
Identifier
Primary Key
Null allowed
Default Value
Description
 
1
LOCATION_ID
NUMBER
4
0

Yes
No


 
2
STREET_ADDRESS
VARCHAR2
40



Yes


 
3
POSTAL_CODE
VARCHAR2
12



Yes


 
4
CITY
VARCHAR2
30



No


 
5
STATE_PROVINCE
VARCHAR2
25



Yes


 
6
COUNTRY_ID
CHAR
2



Yes


 

Table Name: orders ments

Serial number
Column name
Data Type
Length
Decimal places
Identifier
Primary Key
Null allowed
Default Value
Description
 
1
DEPARTMENT_ID
NUMBER
4
0

Yes
No


 
2
DEPARTMENT_NAME
VARCHAR2
30



No


 
3
MANAGER_ID
NUMBER
6
0


Yes


 
4
LOCATION_ID
NUMBER
4
0


Yes


 

Table Name: JOBS

Serial number
Column name
Data Type
Length
Decimal places
Identifier
Primary Key
Null allowed
Default Value
Description
 
1
JOB_ID
VARCHAR2
10


Yes
No


 
2
JOB_TITLE
VARCHAR2
35



No


 
3
MIN_SALARY
NUMBER
6
0


Yes


 
4
MAX_SALARY
NUMBER
6
0


Yes


 

Table Name: EMPLOYEES

Serial number
Column name
Data Type
Length
Decimal places
Identifier
Primary Key
Null allowed
Default Value
Description
 
1
EMPLOYEE_ID
NUMBER
6
0

Yes
No


 
2
FIRST_NAME
VARCHAR2
20



Yes


 
3
LAST_NAME
VARCHAR2
25



No


 
4
EMAIL
VARCHAR2
25



No


 
5
PHONE_NUMBER
VARCHAR2
20



Yes


 
6
HIRE_DATE
DATE
7



No


 
7
JOB_ID
VARCHAR2
10



No


 
8
SALARY
NUMBER
8
2


Yes


 
9
COMMISSION_PCT
NUMBER
2
2


Yes


 
10
MANAGER_ID
NUMBER
6
0


Yes


 
11
DEPARTMENT_ID
NUMBER
4
0


Yes


 

Erfigure:

 

 

Use SQL to complete the following problem list:

 

1. Which departments have more people than Department 90.
2. Who is the leader of Den (FIRST_NAME) and Raphaely (LAST_NAME) (non-correlated subquery ).
3. Den (FIRST_NAME) and Raphaely (LAST_NAME) leaders who (non-associated subqueries ).
4. Who is the leader of Den (FIRST_NAME) and Raphaely (LAST_NAME) (Associate subquery ).
5. Den (FIRST_NAME) and Raphaely (LAST_NAME) leaders who (Associate subqueries ).
6. List employees who work in the same department on a late employment date but whose salaries are higher than those of other colleagues: name, salary, and employment date
(Associate subquery ).
7. which employees are not in the same department as Den (FIRST_NAME) and Raphaely (LAST_NAME) (non-associated subquery ).
8. which employees are not in the same department (associated with subqueries) as Den (FIRST_NAME) and Raphaely (LAST_NAME ).
9. Positions in the Finance Department (non-associated subquery ).
10. Positions in the Finance Department (associated with subquery ).
 

The answers to each question are as follows (You are welcome to point out different methods or suggestions !) :

 

/* -------- 1. Which departments have more people than Department 90. ---------*/

SQL> SELECT DEPARTMENT_ID, COUNT (*) FROM EMPLOYEES
2 group by DEPARTMENT_ID
3 having count (*)>
4 (select count (*) FROM EMPLOYEES
5 WHERE DEPARTMENT_ID = 90
6 );
 
DEPARTMENT_ID COUNT (*)
-----------------------
30 6
50 45
60 5
& Nb