Building database and table:

if DB_id('TicketManager ')is not null --Judge whether the database already exists
drop database TicketManager --Remove the existing database
go
create database TicketManager --New database
on
(
    name='TicketManager ',--Database name
    filename='E:\CS Review cases of Architecture\TicketManager\DB\TicketManager.mdf'--Write where to build the database
)
use TicketManager--Open database
if OBJECT_ID('Ticket')is not null--Judge whether the table already exists
drop table Ticket--Remove the existing database
go
create table Ticket--new table
(
    TrainID int identity(1,1) primary key,--Column data type constraint
    TrainNO nvarchar(50) not null unique,
    leaveCity nvarchar(50) not null,
    arriveCity nvarchar(50) not null,
    LeaveTime nvarchar(50) not null,
    SeatPrice int not null,
    BedPrice int not null
)

insert Ticket values('N5545','Beijing','Wuhan','6:11','150','200') --Add data
insert Ticket values('N5546','Wuhan','Beijing','6:12','150','200')
insert Ticket values('N5547','Guangdong','Jiangxi','4:13','150','200')
insert Ticket values('N5548','Jiangxi','Guangdong','7:14','150','200')
insert Ticket values('N5549','Zhuhai','Shenzhen','6:15','50','70')

54 classic SQL cases from introduction to mastery:

--1,Find the number, name, department and date of birth of the employee. If the date of birth is blank, the date is unknown,And sort the output by Department,Date format is yyyy-mm-dd. 
select emp_no,emp_name,dept,isnull(convert(char(10),birthday,120),'Date unknown') birthday
from employee
order by dept

--2,Find the employee's name, gender, department and title in the same unit as Yu Ziqiang
select emp_no,emp_name,dept,title
from employee
where emp_name<>'Yu Ziqiang' and dept in
(select dept from employee
where emp_name='Yu Ziqiang')

--3,Summarize by department and count the total salary of each department
select dept,sum(salary)
from employee
group by dept

--4,Search the sales status of the 14 inch display commodity with the commodity name, and display the serial number, sales quantity, unit price and amount of the commodity
select a.prod_id,qty,unit_price,unit_price*qty totprice
from sale_item a,product b
where a.prod_id=b.prod_id and prod_name='14 Inch display'

--5,Summarize by product number in the sales details, and count the sales quantity and amount of each product
select prod_id,sum(qty) totqty,sum(qty*unit_price) totprice
from sale_item
group by prod_id

--6,Use convert Function to count the total order amount of each customer in 1996 by customer number
select cust_id,sum(tot_amt) totprice
from sales
where convert(char(4),order_date,120)='1996'
group by cust_id

--7,Find customer number, name and order total with sales record
select a.cust_id,cust_name,sum(tot_amt) totprice
from customer a,sales b
where a.cust_id=b.cust_id
group by a.cust_id,cust_name

--8,Find customer number, name, and order total with sales records in 1997
select a.cust_id,cust_name,sum(tot_amt) totprice
from customer a,sales b
where a.cust_id=b.cust_id and convert(char(4),order_date,120)='1997'
group by a.cust_id,cust_name

--9,Find the largest sales record in a sale
select order_no,cust_id,sale_id,tot_amt
from sales
where tot_amt=
(select max(tot_amt)
from sales)

--10,Find the list of salesmen and sales date with at least 3 sales
select emp_name,order_date
from employee a,sales b 
where emp_no=sale_id and a.emp_no in
(select sale_id
from sales
group by sale_id
having count(*)>=3)
order by emp_name

--11,Use the existing quantifier to find the customer name without ordering record
select cust_name
from customer a
where not exists
(select *
from sales b
where a.cust_id=b.cust_id)

--12,Use the left outer link to find the customer number, name, order date, order amount and order date of each customer. The date format is yyyy-mm-dd Sort by customer number, and output by descending order of the same customer
select a.cust_id,cust_name,convert(char(10),order_date,120),tot_amt
from customer a left outer join sales b on a.cust_id=b.cust_id
order by a.cust_id,tot_amt desc

--13,Find 16 M DRAM It is required to display the name, gender, sales date, sales quantity and amount of the corresponding salesperson, in which gender is represented by male and female
select emp_name Full name, Gender= case a.sex when 'm' then 'male'
when 'f' then 'female' 
else 'not'
end,
//Sales date= isnull(convert(char(10),c.order_date,120),'Date unknown'),
qty Number, qty*unit_price as Amount of money
from employee a, sales b, sale_item c,product d
where d.prod_name='16M DRAM' and d.prod_id=c.prod_id and
a.emp_no=b.sale_id and b.order_no=c.order_no

--14,Find the sales record of each person, and display the number, name, gender, product name, quantity, unit price, amount and sales date of the salesperson
select emp_no number,emp_name Full name, Gender= case a.sex when 'm' then 'male'
when 'f' then 'female' 
else 'not'
end,
prod_name Product name,Sales date= isnull(convert(char(10),c.order_date,120),'Date unknown'),
qty Number, qty*unit_price as Amount of money
from employee a left outer join sales b on a.emp_no=b.sale_id , sale_item c,product d
where d.prod_id=c.prod_id and b.order_no=c.order_no

--15,Find the customer name and total payment with the largest sales amount
select cust_name,d.cust_sum
from customer a,
(select cust_id,cust_sum
from (select cust_id, sum(tot_amt) as cust_sum
from sales
group by cust_id ) b
where b.cust_sum = 
( select max(cust_sum)
from (select cust_id, sum(tot_amt) as cust_sum
from sales
group by cust_id ) c )
) d
where a.cust_id=d.cust_id 

--16,Find the number, name and sales volume of the salesperson whose total sales amount is less than 1000 yuan
select emp_no,emp_name,d.sale_sum
from employee a,
(select sale_id,sale_sum
from (select sale_id, sum(tot_amt) as sale_sum
from sales
group by sale_id ) b
where b.sale_sum <1000 
) d
where a.emp_no=d.sale_id 

--17,Find the customer number, customer name, product number, product name, quantity and amount of at least 3 products sold
select a.cust_id,cust_name,b.prod_id,prod_name,d.qty,d.qty*d.unit_price
from customer a, product b, sales c, sale_item d
where a.cust_id=c.cust_id and d.prod_id=b.prod_id and 
c.order_no=d.order_no and a.cust_id in (
select cust_id
from (select cust_id,count(distinct prod_id) prodid
from (select cust_id,prod_id
from sales e,sale_item f
where e.order_no=f.order_no) g
group by cust_id
having count(distinct prod_id)>=3) h )

--18,Find the customer number, name and item number, item name, quantity and amount at least the same as the sales of World Technology Development Corporation
select a.cust_id,cust_name,d.prod_id,prod_name,qty,qty*unit_price
from customer a, product b, sales c, sale_item d
where a.cust_id=c.cust_id and d.prod_id=b.prod_id and 
c.order_no=d.order_no and not exists
(select f.*
from customer x ,sales e, sale_item f
where cust_name='World Technology Development Corporation' and x.cust_id=e.cust_id and
e.order_no=f.order_no and not exists
( select g.*
from sale_item g, sales h
where g.prod_id = f.prod_id and g.order_no=h.order_no and
h.cust_id=a.cust_id)
)

19,Look up the job number, department and salary of all employees surnamed Liu in the table
select emp_no,emp_name,dept,salary
from employee
where emp_name like 'Liu%'

20,Find all customer numbers with orders above 2000
select cust_id
from sales
where tot_amt>2000

21,The salary of the employees in the statistical table is 4000-6000 Number of people between
select count(*)as Number
from employee
where salary between 4000 and 6000

22,Query the average wage of employees in the same department in the table, but only query＂address＂yes＂Shanghai＂Employees of
select avg(salary) avg_sal,dept 
from employee 
where addr like 'Shanghai%'
group by dept

23,The address in the table is"Shanghai"'s employee address changed to"Beijing"
update employee 
set addr like 'Beijing'
where addr like 'Shanghai'

24,Find the basic information of female employees in business department or accounting department.
select emp_no,emp_name,dept
from employee 
where sex='F'and dept in ('business','accounting')

25,Display the total sales amount of each product, and output according to the sales amount from large to small.
select prod_id ,sum(qty*unit_price)
from sale_item 
group by prod_id
order by sum(qty*unit_price) desc

26,Select number between'C0001'and'C0004'Customer number, customer name, customer address of.
select CUST_ID,cust_name,addr
from customer 
where cust_id between 'C0001' AND 'C0004'

27,Several products have been sold.
select count(distinct prod_id) as 'Total number of products sold'
from sale_item 

28,Increase the salary of the employees in the business department 3%. 
update employee
set salary=salary*1.03
where dept='business'

29,from employee Find the lowest paid employee information in the table.
select *
from employee
where salary=
(select min(salary )
from employee )

30,Use join Query customer name as"Client c"Of the goods purchased"Customer name","Order amount","date of contract","phone number"
select a.cust_id,b.tot_amt,b.order_date,a.tel_no
from customer a join sales b
on a.cust_id=b.cust_id and cust_name like 'Client c'

31,from sales It is found in the table that the order amount is greater than"E0013 Salesman in 1996/10/15 Amount of each order received on this day"All orders for.
select *
from sales
where tot_amt>all
(select tot_amt 
from sales 
where sale_id='E0013'and order_date='1996/10/15')
order by tot_amt

32,Calculation'P0001'Average sales unit price of products
select avg(unit_price)
from sale_item
where prod_id='P0001'

33,Find out the orders received by the female employees of the company
select sale_id,tot_amt
from sales
where sale_id in 
(select sale_id from employee
where sex='F')

34,Identify employees who entered the company on the same day
select a.emp_no,a.emp_name,a.date_hired
from employee a
join employee b
on (a.emp_no!=b.emp_no and a.date_hired=b.date_hired)
order by a.date_hired

35,Find out the employee number and name whose current performance is more than 232000 yuan.
select emp_no,emp_name
from employee 
where emp_no in
(select sale_id
from sales 
group by sale_id
having sum(tot_amt)<232000)

36,Find out employee The average wage and address of all female employees in the table are＂Shanghai＂The average wage of all female employees
select avg(salary)
from employee
where sex like 'f'
union
select avg(salary)
from employee
where sex like 'f' and addr like 'Shanghai%'

37,stay employee Query the information of employees whose salary exceeds the average salary in the table.
Select * 
from employee 
where salary>( select avg(salary) 
from employee)

38, Find out the salesperson number and sales performance whose current sales performance is more than 10000 yuan, and rank them according to the sales performance from large to small.
Select sale_id ,sum(tot_amt)
from sales 
group by sale_id 
having sum(tot_amt)>10000
order by sum(tot_amt) desc

39, Find out the order number and order amount received by the male salesman of the company with the order amount exceeding 2000 yuan. 
Select order_no,tot_amt
From sales ,employee
Where sale_id=emp_no and sex='M' and tot_amt>2000

40, query sales The order number and order amount with the highest order amount in the table.
Select order_no,tot_amt from sales 
where tot_amt=(select max(tot_amt) from sales)

41, Inquire the name and address of the customer whose order amount is more than 4000 yuan in each order.
Select cust_name,addr from customer a,sales b 
where a.cust_id=b.cust_id and tot_amt>4000

42, Find out the total order amount of each customer, display the customer number and total order amount, and arrange them in descending order according to the total order amount.
Select cust_id,sum(tot_amt) from sales
Group by cust_id 
Order by sum(tot_amt) desc

43, Ask for the total quantity and average unit price of each product ordered by each customer, and arrange according to customer number and product number from small to large.
Select cust_id,prod_id,sum(qty),sum(qty*unit_price)/sum(qty)
From sales a, sale_item b
Where a.order_no=b.order_no
Group by cust_id,prod_id
Order by cust_id,prod_id

44, Inquire the order number of more than three products ordered.
Select order_no 
from sale_item
Group by order_no
Having count(*)>3

45, The products ordered by the query include at least the orders of the products ordered in order No. 3.
Select distinct order_no
From sale_item a
Where order_no<>'3'and not exists ( 
Select * from sale_item b where order_no ='3' and not exists 
(select * from sale_item c where c.order_no=a.order_no and c.prod_id=b.prod_id))

46, stay sales It is found in the table that the order amount is greater than"E0013 Salesman in 1996/11/10 Amount of each order received on this day"All orders, and displays the operator who took the orders and the amount of the order.
Select sale_id,tot_amt from sales
where tot_amt>all(select tot_amt 
from sales 
where sale_id='E0013' and order_date='1996-11-10') 

47, Query the information of the employees who have not undertaken the business.
Select *
From employee a
Where not exists 
(select * from sales b where a.emp_no=b.sale_id)

48, Check the name, phone number, order number and order amount of customers from Shanghai.
Select cust_name,tel_no,order_no,tot_amt
From customer a ,sales b
Where a.cust_id=b.cust_id and addr='Shanghai'

49, Query the performance of each operator in each month, and sort by operator number and month descending order.
Select sale_id,month(order_date), sum(tot_amt) 
from sales 
group by sale_id,month(order_date)
order by sale_id,month(order_date) desc

50, The total sales quantity and total sales amount of each product shall be calculated, and the product number, product name, total quantity and total amount shall be displayed, and they shall be arranged from small to large according to the product number. 
Select a.prod_id,prod_name,sum(qty),sum(qty*unit_price)
From sale_item a,product b
Where a.prod_id=b.prod_id 
Group by a.prod_id,prod_name
Order by a.prod_id
51, The total order amount is more than'C0002'Customer number, customer name and address of the total order amount of the customer.
Select cust_id, cust_name,addr
From customer
Where cust_id in (select cust_id from sales 
Group by cust_id
Having sum(tot_amt)>
(Select sum(tot_amt) from sales where cust_id='C0002'))

52, Query the best salesman number, salesman name and total sales amount.
select emp_no,emp_name,sum(tot_amt)
from employee a,sales b
where a.emp_no=b.sale_id
group by emp_no,emp_name
having sum(tot_amt)=
(select max(totamt)
from (select sale_id,sum(tot_amt) totamt
from sales
group by sale_id) c)

53, Query the detailed list of each product ordered by each customer, and display the customer number, customer name, product number, product name, quantity and unit price.
select a.cust_id, cust_name,c.prod_id,prod_name,qty, unit_price
from customer a,sales b, sale_item c ,product d
where a.cust_id=b.cust_id and b.order_no=c.order_no and c.prod_id=d.prod_id

54, The average salary of each department is required to be sorted from small to large.
select dept,avg(salary) 
from employee
group by dept 
order by avg(salary)

SQL syntax format:

Select < queried Object > from < file where the object is located >
Delete < deleted object > from < file where the object is located >
Insert < added table (added column) > select < 'added data' > or values < ('added data') >
Update < modify object table > set < modify Object > = < value > where < condition Object > = < condition content '>

create database [add type database] < database name >
on [where to add]
(
Name = '< database name >',
filename='E:\SQL database \ Tourism.Mdf '[' < path information + database name. MDF > ']
)
create table [add type table] < table name >
(
< column name > < Data Format > < constraint > [identity(1,1) primary key - primary key],
< column name > < Data Format > < constraint > [check - other constraints]
)
Foreign key (< column name >) references < table name > (< column name >) [foreign key - connected from the column of this table to the column of the target table]

select * into < backup table name > from < target table name > where 1 = 0