SQL Course subquery

Today, I mainly learned the content of subqueries.

Create DATABASE lianxi0720

Go

Use lianxi0720
Go
CREATE TABLE Bumen
(
Bcode INT PRIMARY KEY,--Department number
bname varchar (,--) Department name
Bceo varchar (,--department head)
Btel varchar (20)--Department phone
)
Go
CREATE TABLE Renyuan
(
Code int primary key identity (10001,1),
Name varchar (20),
Sex varchar (10),
Age int,
BC INT
)
Go

--Set up the external relationship and then insert the data.
--Insert the department's data first
INSERT into bumen values (1001, ' Finance department ', ' Zhang San ', ' 1234567 ')
INSERT into bumen values (1002, ' Sales department ', ' John Doe ', ' 2345678 ')
INSERT into bumen values (1003, ' hr ', ' Harry ', ' 3456789 ')
INSERT into bumen values (1004, ' technical department ', ' Zhao Liu ', ' 4567890 ')
Go

SELECT * FROM Bumen
--Insert the information of the People table
INSERT into Renyuan values (' Zhang San ', ' Male ', 33,1001)
INSERT into Renyuan values (' John Doe ', ' female ', 27,1002)
INSERT into Renyuan values (' Harry ', ' Male ', 25,1003)
INSERT into Renyuan values (' Zhao Liu ', ' female ', 24,1004)

INSERT into Renyuan values (' Franklin Wang Blue ', ' Male ', 38,1001)
INSERT into Renyuan values (' Ma Song ', ' female ', 33,1002)
INSERT into Renyuan values (' Wangbaoqiang ', ' Male ', 36,1003)
INSERT into Renyuan values (' Angelababy yang ', ' female ', 28,1004)

INSERT into Renyuan values (' Zheng Kai, ', ' Male ', 29,1001)
INSERT into Renyuan values (' Fan bingbing ', ' female ', 40,1002)
INSERT into Renyuan values (' Zhang Wei ', ' Male ', 30,1003)
INSERT into Renyuan values (' Jolin Tsai ', ' female ', 37,1004)

--Query The department name of the oldest person
Select MAX (age) from Renyuan
Select BC from Renyuan where age=40
Select Bname from Bumen where bcode=1002
--Sub-query
Select Bname from Bumen where bcode=

(select BC from Renyuan where age =
(select Max (age) from Renyuan)
)

--All information for the first three people sorted by age
Select Top 3 * from Renyuan ORDER by age
--All information about the 6/7/8 person by age
Select Top 3 * from Renyuan where code not in
(select Top 5 code from Renyuan order by age)
ORDER BY age
--Paging query, require a page to display 5 data
--First page
Select Top 5 * from Renyuan
--second page
Select Top 5 * from Renyuan where code not in (select Top 5 code from Renyuan)
--Page Three
Select Top 5 * from Renyuan where code not in (select top of code from Renyuan)

--a total of several pages????
Select CEILING (COUNT (*)/5.0) from Renyuan
--Find out all information about people older than 35 years in the sales department
SELECT * from Renyuan where code in
(select code from Renyuan where age>35 and bc=
(select Bcode from bumen where bname= ' sales Department ')
)

--View all personnel information and replace the department number with the department name
Select code, name, sex, age, (select Bname from bumen where bumen.bcode= RENYUAN.BC) as department from Renyuan
--Add each person's supervisor
Select code, name, sex, age, (select Bname from bumen where bumen.bcode= RENYUAN.BC) as department,
(select Bceo from bumen where bumen.bcode= renyuan.bc) from Renyuan

SQL Course subquery