Oracle's previous Oracle Learning Notes (4-7) multi-table federated query, subquery, dynamic condition query

One, multi-table joint query

Second, sub-query

Three, dynamic condition query

LESSON 4 Displaying Data from multiple Tables
--------------------------------------------------------

Query the maximum number of wages in the S_emp table, and show the employee name of the maximum wage

Select Last_name,max (Salary)
From S_emp;

Multi-Table Query

A Cartesian product is generated when querying multiple tables

In order to prevent the production of Cartesian product, we need to use certain conditions to connect two or more tables (typically using a relationship of equal primary foreign keys between the two tables).

1) Equivalent connection
Internal connection
Self-connect, connect yourself

2) No equivalent connection
Three kinds of non-equivalent connections:
Table name 1 LEFT OUTER JOIN table name 2 on join condition
Table name 1 right outer join table name 2 on join condition
Table name 1 full outer join table name 2 on join condition
One of the outer can be omitted without writing

Query the name and department number of each employee in the S_emp table

Select last_name,dept_id
From S_emp

Query the name and department number of each employee in the S_emp table, and display the name of the department
Select Se.last_name,se.dept_id,sd.id,sd.name
From S_emp se,s_dept SD
Order BY Se.last_name;

Select Se.last_name,se.dept_id,sd.name
From S_emp se,s_dept SD
where se.dept_id = Sd.id
Order BY Se.last_name;

Query the name and department number of each employee in the S_emp table, and display the name of the department and the name of the department's area

Select Se.last_name,se.dept_id,sd.name,sd.region_id,sr.name
From S_emp SE, s_dept SD, S_region SR
where se.dept_id = sd.id and sd.region_id = Sr.id
ORDER BY last_name ASC;

Query the ID of each employee in the S_emp table, the name, the leader's ID, and the leader's name, with the exception of employees without a leader.

Select S1.id,s1.last_name,s1.manager_id,s2.last_name
From s_emp s1,s_emp s2
where s1.manager_id = S2.id and s1.manager_id are NOT null
ORDER BY s2.id ASC;

Inserting a test data into the S_emp
This is a DML statement that generates a transaction
Insert into S_emp (id,last_name)
VALUES ("Tom");
So after inserting the data to be submitted
Commit

Below are examples of non-equivalent connections:
---------------------------------
Query employee's name, department number, department name
(even if an employee has no department number to display)

If you still use such an equivalent connection, the query can't come out, Tom.
Select Se.last_name,se.dept_id,sd.name
From S_emp se,s_dept SD
where se.dept_id = Sd.id;

There's less data over there, plus this plus.
Select Se.last_name,se.dept_id,sd.name
From S_emp se,s_dept SD
where se.dept_id = Sd.id (+);

Left join (the left table as the primary table)
Select Se.last_name,se.dept_id,sd.name
From S_emp se left join s_dept SD
on se.dept_id = sd.id;

Right connection
Select Se.last_name,se.dept_id,sd.name
From S_emp se right join s_dept SD
on se.dept_id = sd.id;

Insert into s_dept (id,name) VALUES (Wuyi, ' Kunshan ');

Commit

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

Query employee's name, department number, department name
(departments that do not have employee presence are also shown)

Select Se.last_name,se.dept_id,sd.name
From S_emp se,s_dept SD
where se.dept_id (+) = sd.id;

Query employee's name, department number, department name
(even if an employee does not have a department number to display, a department that does not have an employee presence)

Fully connected
Select Se.last_name,se.dept_id,sd.name
From S_emp se full outer join s_dept SD
on se.dept_id = sd.id;

Select Se.last_name,se.dept_id,sd.name
From S_emp se full join s_dept SD
on se.dept_id = sd.id;

Keywords that manipulate two result sets
---------------------------------------------
(The result set referred to here is the result of an SQL statement query)

Union

UNION ALL

Minus

Intersect

First sql:
Select Id,last_name
From S_emp
where ID < 7;

Result set One:
ID last_name
-- ---------------
1 Velasquez
2 Ngao
3 Nagayama
4 Quick-to-see
5 Ropeburn
6 Urguhart

The second SQL statement:
Select Id,last_name
From S_emp
where ID < 6;

Result set Two:
ID last_name
-- --------------
1 Velasquez
2 Ngao
3 Nagayama
4 Quick-to-see
5 Ropeburn

1 Union of two result sets obtained

Select Id,last_name
From S_emp
Where ID < 7
Union
Select Id,last_name
From S_emp
where ID < 6;

2 UNION ALL put together two result sets, regardless of duplicate data

Select Id,last_name
From S_emp
Where ID < 7
UNION ALL
Select Id,last_name
From S_emp
where ID < 6;

3 minus the same parts of the two result sets

Select Id,last_name
From S_emp
Where ID < 7
Minus
Select Id,last_name
From S_emp
where ID < 6;

4 intersect only the same part (intersection) of two result sets

Select Id,last_name
From S_emp
Where ID < 7
Intersect
Select Id,last_name
From S_emp
where ID < 6;

Sub-query
------------------------------------------------------
Query the number of the department where Ngao is located

Select dept_id from S_emp
where last_name= ' Ngao ';

Check the average wage for department 41st
Select AVG (Salary)
From S_emp
where dept_id = 41
Group BY DEPT_ID;

Select AVG (Salary)
From S_emp
where dept_id = 41;

AVG (SALARY)
-----------
1247.5

Basic format for SQL statements:
-------------
Select ...
From..
where ...
GROUP BY ...
Having ...
ORDER BY:

* * Execution order:---> WHERE---> GROUP BY---> Groups function---> Having---> ORDER BY

Select/having after the group function, then there is the group function decorated column must be written in group by after
-----------------------------------------------------------------------------

Check the average salary of the Ngao department

Select AVG (Salary)
From S_emp
where dept_id =
(
Select dept_id
From S_emp
where last_name = ' Ngao '
);

Exercise 1:
---------------------------------------------------------------------------
Find employee information in departments with a higher average salary than department 41st (show employee's name department number)

Select Last_name,dept_id,salary
From S_emp
Where Dept_id=any (
Select dept_id from S_emp
GROUP BY dept_id
Having avg (Salary) >
(
Select AVG (Salary) from s_emp
where dept_id = 41
)
);

Analysis:
1. Query for employee information
2. The average salary of the employee department is higher than the 41st department

41 average wage of the department
Select AVG (Salary)
From S_emp
where dept_id = 41;

AVG (SALARY)
-----------
1247.5

Department with a higher average wage than 41
Select dept_id
From S_emp
GROUP BY dept_id
Having avg (Salary) >1247.5
Order by dept_id;

Select dept_id
From S_emp
GROUP BY dept_id
Having avg (Salary) >
(
Select AVG (Salary)
From S_emp
where dept_id = 41
)
Order by dept_id;

dept_id
----------
10
31
32
33
35
50

Select Last_name,dept_id,salary
From S_emp
where dept_id in (10,31,32,33,35,50);

Select Last_name,dept_id,salary
From S_emp
where dept_id in
(
Select dept_id
From S_emp
GROUP BY dept_id
Having avg (Salary) >
(
Select AVG (Salary)
From S_emp
where dept_id = 41
)
);

Exercise 2:
-------------------------------------------------------------------------------------------
Query employee information that is higher than the average salary of the department in which Ngao is located, and the average wage for these employees is higher than the average wage in the Ngao department.

Select Last_name,dept_id,salary
From S_emp
where dept_id in
(
Select dept_id
From S_emp
GROUP BY dept_id
Having avg (Salary) >
(
Select AVG (Salary)
From S_emp
where dept_id = 41
)
)
> Salary
(
Select AVG (Salary)
From S_emp
where dept_id =
(
Select dept_id
From S_emp
where last_name = ' Ngao '
)
);

Analysis:
1. Query for employee information
2. The salaries of these employees are higher than the average salary of the Ngao department
3. The average wage of these employees is also higher than that of the Ngao department.

Department number where Ngao is located
Select dept_id
From S_emp
where last_name = ' Ngao ';

dept_id
--------
41

Average wage in Sector 41st
Select AVG (Salary)
From S_emp
where dept_id = 41;

AVG (SALARY)
-----------
1247.5

Department with a higher average wage than 41
Select dept_id
From S_emp
GROUP BY dept_id
Having avg (Salary) >1247.5
Order by dept_id;

dept_id
----------
10
31
32
33
35
50

The final SQL to be executed in this example is actually:
Select Last_name,dept_id,salary
From S_emp
Where Salary > 1247.5
and dept_id in (10,31,32,33,35,50);

So finally replace the conditions in the SQL statement with the subquery as:
Select Last_name,dept_id,salary
From S_emp
Where Salary >
(
Select AVG (Salary)
From S_emp
where dept_id =
(
Select dept_id
From S_emp
where last_name = ' Ngao '
)
)
and dept_id in
(
Select dept_id
From S_emp
GROUP BY dept_id
Having avg (Salary) >
(
Select AVG (Salary)
From S_emp
where dept_id =
(
Select dept_id
From S_emp
where last_name = ' Ngao '
)
)
);

Exercise 3:
----------------------------------------------------------------
Inquiries are higher than the average salary of the department in which Ngao is located, and the average wage of these employees is higher than the average wage in the department of Ngao.
(Show employee's name, department number, salary)
Add the name of the department that displays the employee

Select Se.last_name,se.dept_id,se.salary,sd.name,sr.name,se2.last_name
From S_emp se,s_dept sd,s_region sr,s_emp se2
where Se.salary >
(
Select AVG (Salary)
From S_emp
where dept_id =
(
Select dept_id
From S_emp
where last_name = ' Ngao '
)
)
and se.dept_id in
(
Select dept_id
From S_emp
GROUP BY dept_id
Having avg (Salary) >
(
Select AVG (Salary)
From S_emp
where dept_id =
(
Select dept_id
From S_emp
where last_name = ' Ngao '
)
)
)
and se.dept_id = Sd.id
and sd.region_id = Sr.id
and se.manager_id = se2.id;

1. Query for employee information
2. The salaries of these employees are higher than the average salary of the Ngao department
3. The average wage of these employees is also higher than that of the Ngao department.

Select Se.last_name,se.dept_id,se.salary,sd.dept_name
From s_emp se join s_dept SD
on se.dept_id = sd.id;

Select Last_name,dept_id,salary
From S_emp
Where Salary >
(
Select AVG (Salary)
From S_emp
where dept_id =
(
Select dept_id
From S_emp
where last_name = ' Ngao '
)
)
and dept_id in
(
Select dept_id
From S_emp
GROUP BY dept_id
Having avg (Salary) >
(
Select AVG (Salary)
From S_emp
where dept_id =
(
Select dept_id
From S_emp
where last_name = ' Ngao '
)
)
);

Select Se.last_name,se.dept_id,se.salary,sd.name
From s_emp se join s_dept SD
On ((se.dept_id=sd.id) and
(Se.salary >
(
Select AVG (Salary)
From S_emp
where dept_id =
(
Select dept_id
From S_emp
where last_name = ' Ngao '
)
)
and se.dept_id in
(
Select dept_id
From S_emp
GROUP BY dept_id
Having avg (Salary) >
(
Select AVG (Salary)
From S_emp
where dept_id =
(
Select dept_id
From S_emp
where last_name = ' Ngao '
)
)
)
)
);

Exercise 4:
------------------------------------------
Inquire about employee information in each department with a higher average salary than the department
Show employee's name, salary, department number, average salary for this department

The query results for this SQL statement can be used as a table to query in the future
Select Dept_id,avg (Salary) Avgsal
From S_emp
Group BY DEPT_ID;

dept_id Avgsal
------- -----------
42 1081.66667
43 900
34 1160
44 1050
31 1400
32 1490
35 1450
50 2025
41 1247.5
45 1089
33 1515
10 1450

Select Se.last_name,se.salary,se.dept_id,temp.avgsal
From S_emp SE, (
Select Dept_id,avg (Salary) Avgsal
From S_emp
GROUP BY dept_id
) Temp
where se.dept_id = temp.dept_id
and Se.salary > temp.avgsal;

1. Query for employee information
2. For example, to find out the staff information in Sector 41st, which is higher than the average salary of department 41st (this department)

Select AVG (Salary), dept_id
From S_emp
Group BY DEPT_ID;

Select last_name,salary,dept_id
From S_emp;

Select S1.last_name,s1.salary,s1.dept_id,s2.avg_sal
From S_emp s1
Join (select AVG (Salary) avg_sal,dept_id from S_emp Group by dept_id) S2
On ((s1.dept_id = s2.dept_id) and (S1.salary > S2.avg_sal))
ORDER BY s1.dept_id ASC;

Input parameters (run-time parameters) when SQL statements are executed
------------------------------------------------
Select last_name,salary,dept_id
From S_emp
where last_name=&name;

Select last_name,salary,dept_id
From S_emp
where id = 2;

Select last_name,salary,dept_id
From S_emp
WHERE id = &id;

Assign a value to a string parameter when running an SQL statement
Select last_name,salary,dept_id
From S_emp
where last_name = &last_name;

Select last_name,salary,dept_id
From S_emp
where last_name = ' &last_name ';

Select last_name,salary,dept_id
From S_emp
where ID > &id or last_name = ' &last_name ';

Specific filter conditions are given at runtime
Select last_name,salary,dept_id
From S_emp
where &condition;

Select last_name,salary,dept_id
From S_emp
&condition;

INSERT into student (id,name,age)
VALUES (&id, ' &name ', &age);

Oracle's previous Oracle Learning Notes (4-7) multi-table federated query, subquery, dynamic condition query