Instance learning SQL's SELECT command _mssql

-1, find the employee's number, name, department and date of birth, if the date of birth is a null value,
--The display date is unknown, sorted by department output, date format is YYYY-MM-DD.
Select Emp_no, Emp_name, dept,
IsNull (CONVERT (char, birthday,120), ' date unknown ') birthday
From employee
ORDER BY Dept

--2, find the name, Gender, department, and title of the employee in the same unit as Zichiang
Select Emp_no,emp_name,dept,title
From employee
where emp_name<> ' Zichiang ' and dept in
(Select Dept from employee
where emp_name= ' Zichiang ')

--3, summary by department, statistics of the total wages in each department
Select Dept,sum (Salary)
From employee
GROUP BY Dept

--4, look for products with the name of 14-inch display merchandise sales,
--Displays the number, sales quantity, unit price, and amount of the item
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 monitor '

--5, in the sales schedule by the product number of the summary, statistics of each product sales quantity and amount
Select Prod_id,sum (Qty) totqty,sum (qty*unit_price) Totprice
From Sale_item
GROUP BY prod_id

--6, use the CONVERT function to count the total order amount for 1996 years per customer 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 the customer number, name, and order totals with sales records
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 totals for sales records in 1997 years
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 one sale
Select Order_no,cust_id,sale_id,tot_amt
From sales
where tot_amt=
(select Max (Tot_amt)
From sales)

--10, find the Salesperson list and date of sale at least 3 times
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, using existential quantifiers to find customer names without order records
Select Cust_name
From Customer A
Where NOT EXISTS
(SELECT *
From Sales B
where a.cust_id=b.cust_id)

--12, use left outer join to find each customer's customer number, name, order date, order Amount
--order date not to show time, date format is YYYY-MM-DD
--sorted by customer number, the same customer then sorted output by order descending
Select A.cust_id,cust_name,convert (char (), order_date,120), Tot_amt
From the customer a left outer join sales B on a.cust_id=b.cust_id
ORDER BY A.cust_id,tot_amt Desc

--13, to find the sales of 16M dram, required to display the corresponding salesperson's name,
-Sex, sales date, quantity and amount of sales, in which sex is indicated by male and female
Select Emp_name name, sex = Case a.sex when ' m ' Then ' man '
When ' F ' then ' woman '
Else ' not '
End
Sales date = IsNull (convert (char, c.order_date,120), ' date unknown '),
Qty Quantity, Qty*unit_price as Amount
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 each person's sales record, require the salesperson's number, name, gender,
--Product name, quantity, unit price, amount and date of sale
Select emp_no number, emp_name name, sex = Case a.sex when ' m ' Then ' man '
When ' F ' then ' woman '
Else ' not '
End
Prod_name product Name, Sales date = IsNull (convert (char, c.order_date,120), ' date unknown '),
Qty Quantity, Qty*unit_price as Amount
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 largest customer name and total payment for the 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 salesperson number, name and sales amount less than 1000 RMB
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 customer number, customer name, commodity number, product name, quantity and amount of at least 3 items 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 at least the same customer number, name and commodity number, product name, quantity, and amount as the world technology Development company sells
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 list of all the employees surnamed Liu's work number, department, salary
Select Emp_no,emp_name,dept,salary
From employee
Where Emp_name like ' Liu% '

20. Find all customer numbers for all order amounts above 2000
Select cust_id
From sales
where tot_amt>2000

21. The number of employees in the statistics table between 4000-6000
Select COUNT (*) as number
From employee
Where salary between 4000 and 6000

22. The average wage of the employees in the same department in the enquiry form, but only the "address" is the employee of "Shanghai City"
Select AVG (Salary) avg_sal,dept
From employee
Where addr like ' Shanghai% '
GROUP BY Dept

23, the table in the address of "Shanghai city," the employee address changed to "Beijing"
Update employee
Set addr like ' Beijing '
Where addr like ' Shanghai City '

24, find the business department or the accounting department of the female staff of the basic information.
Select Emp_no,emp_name,dept
From employee
where sex= ' F ' and dept in (' Business ', ' accounting ')

25, show the sum of the sales amount of each product, and according to the sales amount from large to small output.
Select prod_id, sum (qty*unit_price)
From Sale_item
GROUP BY prod_id
ORDER by sum (qty*unit_price) desc

26, select the number of ' C0001 ' and ' C0004 ' customer number, customer name, customer address.
Select Cust_id,cust_name,addr
From customer
where cust_id between ' C0001 ' and ' C0004 '

27, calculated a total of several products sold.
Select COUNT (Distinct prod_id) as ' total number of products sold '
From Sale_item

28, the Department of Business staff salary increase by 3%.
Update employee
Set salary=salary*1.03
where dept= ' business '

29, from the employee table to find the lowest salary of the employee information.
SELECT *
From employee
where salary=
(select min (Salary)
From employee)

30, the use of Join query customer name is "Customer C" of the goods purchased "customer name", "Order Amount", "Order Date", "Telephone 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 ' customer C '

31, from the sales table to find out the order amount is greater than "E0013 salesman in 1996/10/15 this day of the amount of each order received" all orders.
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, calculate the ' P0001 ' product average sales unit price
Select AVG (unit_price)
From Sale_item
where prod_id= ' P0001 '

33, find out the company's female employees to receive orders
Select Sale_id,tot_amt
From sales
where sale_id in
(Select sale_id from employee
where sex= ' F ')

34, find the same day to enter the company's services staff
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 current performance of more than 232000 Yuan employee number and name.
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. Check out the average wage of all female employees in the employee table and their address in "Shanghai City"
Select AVG (Salary)
From employee
where sex like ' F '
Union
Select AVG (Salary)
From employee
where sex like ' F ' and addr like ' Shanghai% '

37, in the Employee table query salary exceeds the average salary of staff information.
Select *
From employee
Where salary> (select AVG (Salary)
From employee)

38, find out the current sales performance of more than 10000 yuan salesman number and sales performance, and according to sales performance from large to small sort.
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 the company male salesman and order amount of more than 2000 yuan order number and order amount.
Select Order_no,tot_amt
From sales, employee
Where sale_id=emp_no and sex= ' M ' and tot_amt>2000

40. The order number and the order amount of the highest order amount in the sales table are inquired.
Select Order_no,tot_amt from Sales
Where tot_amt= (select Max (Tot_amt) from sales)

41, inquiries in each order amount of more than 4000 yuan to order the customer name and its address.
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, showing the customer number and total order amount, and in descending order by total ordering amount.
Select Cust_id,sum (Tot_amt) from sales
Group by cust_id
ORDER by sum (TOT_AMT) desc

43, for each customer order the total number of each product and the average unit price, and according to customer number, product number from small to large arrangement.
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, the inquiry ordered more than three product order number.
Select Order_no
From Sale_item
Group by Order_no
Having Count (*) >3

45, the inquiry order product contains at least the order of the product order 3rd.
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, in the sales table to find out the order amount is greater than "E0013 salesman on the day in 1996/11/10 to receive the amount of each order" of all orders, and show those who take the orders of the salesman 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, inquiries at the end of the staff to undertake business information.
Select *
From Employee a
Where NOT EXISTS
(SELECT * from sales b where a.emp_no=b.sale_id)

48, inquires from the Shanghai customer's name, telephone number, order number and order amount.
Select Cust_name,tel_no,order_no,tot_amt
From customer A, sales B
Where a.cust_id=b.cust_id and Addr= ' Shanghai city '

49, inquires each salesman each month the performance, and by the salesman number, the 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, for each product total sales volume and total sales amount, requirements show the product number, product name, total quantity and total amount, and by product number from small to large arrangement.
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, inquires the total order amount exceeds ' C0002 ' customer's total order amount the customer number, the customer name and the address.
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 performance of the salesman number, salesman name and the total amount of sales.
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, inquires each customer to order each kind of product detailed list, the request displays the customer number, the customer name, the product number, the product name, the quantity and the 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, to ask the average salary of each department, according to the average salary from small to large sort.
Select Dept,avg (Salary)
From employee
GROUP BY Dept
ORDER by AVG (Salary)