Two-stage test

--Create a database
Create DATABASE Dhgl
--Lock the database you want to use
Use Dhgl
--Create a warehouse table (warehouse number, city, area)
CREATE TABLE Cangku
(
CNO nvarchar () NOT NULL,
City nvarchar (+) NOT NULL,
area int NOT NULL
)
--Create a staff table (warehouse number, employee number, salary)
CREATE TABLE Zhigong
(
CNO nvarchar () NOT NULL,
ZnO nvarchar () NOT NULL,
Wages int NOT NULL
)
--Create order form (employee number, supplier number, Order slip, order date)
CREATE TABLE Dinggou
(
ZnO nvarchar () NOT NULL,
Gno nvarchar () null,
DNO nvarchar () NOT NULL,
Timers datetime NULL
)
--Create supplier table (supply firm, supplier name, address)
CREATE TABLE Gongying
(
Gno nvarchar () NOT NULL,
Gname nvarchar () NOT NULL,
City nvarchar (+) NOT NULL
)
--adding data to a table
--Warehouse Table
Insert Cangku values (' wh1 ', ' Beijing ', 370)
Insert Cangku values (' wh2 ', ' Shanghai ', 500)
Insert Cangku values (' Wh3 ', ' Guangzhou ', 200)
Insert Cangku values (' Wh4 ', ' Wuhan ', 400)
--Staff table
Insert Zhigong values (' wh2 ', ' E1 ', 1220)
Insert Zhigong values (' wh1 ', ' E3 ', 1210)
Insert Zhigong values (' Wh2 ', ' E4 ', 1250)
Insert Zhigong values (' Wh3 ', ' E6 ', 1230)
Insert Zhigong values (' wh1 ', ' E7 ', 1250)
--Order Form
Insert Dinggou values (' E3 ', ' S7 ', ' or67 ', ' 2015-6-23 ')
Insert Dinggou VALUES (' E1 ', ' S4 ', ' or67 ', ' 2015-7-28 ')
Insert Dinggou values (' E7 ', ' S4 ', ' or67 ', ' 2015-5-25 ')
Insert Dinggou values (' E6 ', null, ' or67 ', null)
Insert Dinggou values (' E3 ', ' S4 ', ' or67 ', ' 2015-6-13 ')
Insert Dinggou VALUES (' E1 ', null, ' or67 ', null)
Insert Dinggou values (' E3 ', null, ' or67 ', null)
Insert Dinggou values (' E3 ', ' S3 ', ' or67 ', ' 2015-7-13 ')
--Supplier table
Insert gongying values (' S3 ', ' Zhenhua electronics factory ', ' Xian ')
Insert gongying values (' S4 ', ' Hua-powered subsidiaries ', ' Beijing ')
Insert gongying values (' S6 ', ' Factory 607 ', ' Zhengzhou ')
Insert gongying values (' S7 ', ' ai Hua Electronics Factory ', ' Beijing ')
--1. Search for employee numbers with a salary of more than $1230
Select ZnO from Zhigong where wages > 1230
--"2". Retrieve warehouse address of warehouse workers ' salary more than 1210 yuan
Select city from Cangku where CNO
In (select CNO from Zhigong where wages > 1210)
--"3". Give the employee who works in the warehouse "WH1" or "WH2" and has a salary of less than 1250 yuan.
Select ZnO from Zhigong where wages < 1250 and (CNO = ' wh1 ' or CNO = ' wh2 ')
--4. Find out the number of workers with wages of more than $1230 and the city where their warehouses are located.
Select ZnO from Zhigong where wages > 1230
Union
Select city from Cangku where CNO in (select CNO from Zhigong where wages > 1230)
--5. Find the number of employees working in warehouses larger than 400 and the cities in which these warehouses are located.
Select ZnO from Zhigong where CNO
In (select CNO from Cangku, where area > 400)
Union
Select city from Cangku where area > 400
--6. Which cities have at least one warehouse staff wages of 1250 yuan
Select city from Cangku where CNO in (select CNO from zhigong where wages = 1250)
--7. Inquire about the warehouse where all employees are paid more than 1210 yuan.
Select *from Cangku where CNO in (select CNO from Zhigong where wages > 1210)
--8. Find out all the employees who earn the same salary as E4, including himself.
Select ZnO from zhigong where wages = (select wages from zhigong where ZnO = ' E4 ')
--9. Retrieves employee information for wages in the range of $1220 to $1240.
Select *from Zhigong where wages >= 1220 and wages <= 1240
Select *from Zhigong where wages between 1220 and 1240
--10. Retrieve all employee information in ascending order by employee's salary value.
Select *from Zhigong ORDER by wages ASC
--11. Find out all the supplier information that is not in Beijing.
Select *from gongying where city! = ' Beijing '
--12. Sort by warehouse number in ascending order, then descending by salary and outputting all employee information.
Select *from zhigong ORDER by CNO asc,wages desc
--"13". Find out the number of supplier locations.
Select COUNT (Distinct city) as ' quantity ' from gongying
--14. Total wage payable
Select SUM (wages) as ' total payroll ' from Zhigong
--"15". The average area of a warehouse where all employees are paid more than $1210
Select AVG (area) from Cangku where CNO
>any (select CNO from Zhigong Group by CNO)
and
CNO in (select CNO from Zhigong where wages >1210)
--16. Average salary for employees in each warehouse
Select AVG (wages) as ' average salary ' from Zhigong Group by CNO
--17. Ask for the average salary of at least two employees per warehouse.
Select Cno,avg (wages) as ' average wage ' from Zhigong Group by CNO have COUNT (ZnO) >=2
--18. Retrieving information about warehouses in which warehouses do not yet have workers
Select *from Cangku where CNO not in (select CNO from Zhigong)
--19. Retrieve all information about the warehouse where the employee's wages are greater than or equal to the wages of any employee in the WH1 warehouse.
--does not contain WH1 warehouse information
Select *from Cangku where CNO
In (select CNO from Zhigong where wages
> Any (select wages from zhigong where cno = ' wh1 '))
and CNO! = ' WH1 '
--20. Change the salary of the warehouse address in Beijing to 1300
Update Zhigong Set wages = 1300 where ZnO
Inch
(
Select ZnO from Zhigong where CNO
Inch
(select CNO from Cangku, where city = ' Beijing ')
)

Update Zhigong Set wages = 1300 where CNO
= (select Cno from cangku where city = ' Beijing ')
--select *from Zhigong
--21. Write a view that contains all the information for four tables, named Alltab
CREATE VIEW Alltab
As
Select Cangku.cno,cangku.city,area,zhigong.zno,wages,dinggou.gno,dno,
Timers,gname,gongying.city from Cangku,zhigong,dinggou,gongying
where cangku.cno = zhigong.cno and Zhigong.zno = Dinggou.zno and
Dinggou.gno = Gongying.gno
--22. Write the cascade Delete trigger for the Vendor table and requery the Vendor table after the deletion succeeds
Create Trigger Delete_gongying
On gongying
Instead of delete
As
Delete from Dinggou where Gno = (select Gno from deleted)
Delete from gongying where Gno = (select Gno from deleted)
Select *from delete_gongying
--23. Write a stored procedure that requires the input of two numbers A and B, if a is greater than B returns a difference of two numbers, otherwise returns two numbers of the same;
Create proc Cunchu
@a Int,@b int
As
DECLARE @c int
If @a > @b
Begin
Set @c= @[email protected]
End
Else
Begin
Set @c= @[email protected]
End
return @c
--24. The stored procedure for 23 questions is executed once, passing in Parameters 5 and 3,
--Use a variable to receive the return value and print it separately in the result set and the message box
DECLARE @c int
exec @c = Cunchu 5,3
Print @c
Select @c

--Wrong question number: 2, 3, 13, 15, 22, 23, 24

Two-stage test