Database SQL Server primary FOREIGN key subquery

Use Lianxi
Go
--Create a departmental table
CREATE TABLE Bumen
(
Bmcode int PRIMARY KEY,--department number
Bmname varchar (20),--Department name
Bmceo varchar (20),--head of department
Bmtel varchar (20),--Department phone
)
Go
--Create a people table
CREATE TABLE Renyuan
(
Code int primary key identity (1001,1),--employee number
Name varchar (20),--Employee name
Sex varchar (10),--employee gender
Age int,--employee ages
YGBM int,--employee department
)
Go

--Set the foreign key relationship, then insert the data.

--Insert the department's data first
INSERT into bumen values (1, ' Finance Department ', ' Zhang San ', ' 1111111 ')
INSERT into bumen values (2, ' hr ', ' John Doe ', ' 2222222 ')
INSERT into Bumen values (3, ' technical department ', ' Harry ', ' 3333333 ')
INSERT into Bumen values (4, ' Sales department ', ' Zhao Liu ', ' 4444444 ')
Go

Select *from bumen

--Insert the information of the People table

INSERT into Renyuan values (' Zhang San ', ' Male ', 22, 1)
INSERT into Renyuan values (' John Doe ', ' female ', 32,2)
INSERT into Renyuan values (' Harry ', ' Male ', 42, 3)
INSERT into Renyuan values (' Zhao Liu ', ' female ', 52,4)

INSERT into Renyuan values (' One ', ' Male ', 28, 1)
INSERT into Renyuan values (' two ', ' female ', 38,2)
INSERT into Renyuan values (' Three ', ' Male ', 48, 3)
INSERT into Renyuan values (' Four ', ' female ', 58,4)

INSERT into Renyuan values (' Five ', ' Male ', 25, 1)
INSERT into Renyuan values (' Six ', ' female ', 35,2)
INSERT into Renyuan values (' Seven ', ' Male ', 45, 3)
INSERT into Renyuan values (' eight ', ' female ', 55,4)

Select *from Renyuan
--delete from Bumen
--delete from Renyuan

--Query The department name of the oldest person
Select Bmname from Bumen where bmcode=
(select YGBM from Renyuan where code=
(select code 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
Select Top 5*from Renyuan
Select top 5*from Renyuan where code not in (select Top 5 code from Renyuan)
Select top 5*from Renyuan where code not in (select Top ten code from Renyuan)

--a total of a few 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 ygbm=
(select Bmcode from bumen where bmname= ' sales Department ')
)

--View all personnel information and replace the department number with the department name
Select code, name, sex, age, (select Bmname from bumen where bumen.bmcode= RENYUAN.YGBM) as department from Renyuan

--Add each person's supervisor
Select code, name, sex, age, (select Bmname from bumen where bumen.bmcode= RENYUAN.YGBM) as department,
(select Bmceo from bumen where bumen.bmcode= RENYUAN.YGBM) from Renyuan

Database SQL Server primary FOREIGN key subquery