Select command for instance learning SQL

Select command for instance learning SQL

Select command for instance learning SQL

-1. Find the employee ID, name, department, and date of birth. If the date of birth is null,
-- Display date is unknown and output by department. The 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. Search for the names, gender, department, and title of employees 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. Collect the total salary of each department by department
Select dept, sum (salary)
From employee
Group by dept

-- 4. Search for the sales status of a 14-inch display product,
-- Display the product ID, sales quantity, unit price, and amount
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 dashboard'

-- 5. In the sales list, summarize the sales quantity and amount of each product by product number.
Select prod_id, sum (qty) totqty, sum (qty * unit_price) totprice
From sale_item
Group by prod_id

-- 6. Use the convert function to calculate the total order amount for each customer in 1996 by customer number
Select cust_id, sum (tot_amt) totprice
From sales
Where convert (char (4), order_date, 120) = '20140901'
Group by cust_id

-- 7. Search for customer numbers, names, and total orders 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. Search for customer numbers, names, and total orders with sales records in June 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) = '123'
Group by a. cust_id, cust_name

-- 9. Search for the largest sales record at a time
Select order_no, cust_id, sale_id, tot_amt
From sales
Where tot_amt =
(Select max (tot_amt)
From sales)

-- 10. Search for the salesperson list and sales date with at least three sales attempts
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 an existing quantizer to find the customer name without an order record
Select cust_name
From customer
Where not exists
(Select *
From sales B
Where a. cust_id = B. cust_id)

-- 12. Use the left outer link to find the customer ID, name, order date, and order amount of each customer.
-- Do not display the time for the order date. The date format is yyyy-mm-dd.
-- Sort by Customer ID, and output by order in 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. Search for the Sales Status of 16 m dram and display the name of the corresponding salesperson,
-- Gender, sales date, sales quantity, and amount, in which gender is represented by male and female
Select emp_name, Gender = case a. sex when 'M' then 'male'
When 'F' then 'female'
Else 'UN'
End,
Sales date = isnull (convert (char (10), 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. query the sales records of each person. The salesperson's ID, name, gender, and,
-- Product name, quantity, unit price, amount, and sales date
Select emp_no, emp_name, Gender = case a. sex when 'M' then 'male'
When 'F' then 'female'
Else 'UN'
End,
Prod_name product name, sales date = isnull (convert (char (10), 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. Search for the customer name and total payment with the largest sales amount
Select cust_name, d. cust_sum
From customer,
(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. Search for the number, name, and sales of the salesperson whose total sales volume is less than 1000 yuan
Select emp_no, emp_name, d. sale_sum
From employee,
(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. Search for customer numbers, Customer names, product numbers, product names, quantities, and amounts that have sold at least three types of products
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. Search for customer numbers, names and product numbers, product names, quantities, and amounts that are at least the same as those sold by World Technology development companies.
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 developers' 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. Find the employee ID, 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. Search for all customer numbers with an order amount greater than 2000
Select cust_id
From sales
Where tot_amt> 2000

21. Number of employees with salaries in the statistical table between and
Select count (*) as count
From employee
Where salesary between 4000 and 6000

22. query the average salary of employees in the same department in the table, but only query the employees whose "residential address" is "Shanghai ".
Select avg (salary) avg_sal, dept
From employee
Where addr like 'shanghai %'
Group by dept

23. Change the employee's address in the table "Shanghai" to "Beijing"
Update employee
Set addr like 'beijing'
Where addr like 'shanghai'

24. Search for basic information about female employees in the business or accounting department.
Select emp_no, emp_name, dept
From employee
Where sex = 'F' and dept in ('business', 'account ')

25. display the total sales amount of each product, and output the total 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 the customer ID, customer name, and customer address of the numbers 'c0001' and 'c0004.
Select CUST_ID, cust_name, addr
From customer
Where cust_id between 'c0001 'AND 'c0004'

27. computing has sold several products in total.
Select count (distinct prod_id) as 'Total number of sold products'
From sale_item

28. Increase the salary of employees in the business department by 3%.
Update employee
Set salary = salary * 1.03
Where dept = 'business'

29. Find the employee information with the lowest salary in the employee table.
Select *
From employee
Where salary =
(Select min (salary)
From employee)

30. Use join to query "customer name", "order amount", "Order Date", and "phone number" of the customer whose name is "customer C" purchased goods"
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 bing'

31. In the sales table, find all orders with the order amount greater than "E0013 clerk's amount for each order received on.
Select *
From sales
Where tot_amt> all
(Select tot_amt
From sales
Where sale_id = 'e0013 'and order_date = '2017/15 ')
Order by tot_amt

32. Calculate the average unit price of the 'p0001' Product
Select avg (unit_price)
From sale_item
Where prod_id = 'p0001'

33. Find the order received by the female employee of the company
Select sale_id, tot_amt
From sales
Where sale_id in
(Select sale_id from employee
Where sex = 'F ')

34. Find the employees who enter the company's services on the same day
Select a. emp_no, a. emp_name, a. date_hired
From employee
Join employee B
On (a. emp_no! = B. emp_no and a. date_hired = B. date_hired)
Order by a. date_hired

35. Find the employee ID and name with the current performance exceeding 232000 RMB.
Select emp_no, emp_name
From employee
Where emp_no in
(Select sale_id
From sales
Group by sale_id
Having sum (tot_amt) <1, 232000)

36. query the average salary of all female employees and the average salary of all female employees whose addresses are in Shanghai.
Select avg (salary)
From employee
Where sex like 'F'
Union
Select avg (salary)
From employee
Where sex like 'F' and addr like 'shanghai %'

37. query the employee information whose salary exceeds the average salary in the employee table.
Select *
From employee
Where salary> (select avg (salary)
From employee)

38. Identify the number of sales personnel whose current sales performance exceeds 10000 yuan and their sales performance, and sort them by 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 the order number and order amount received by the company's male salesman and the order amount exceeds 2000 yuan.
Select order_no, tot_amt
From sales, employee
Where sale_id = emp_no and sex = 'M' and tot_amt> 2000

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

41. query the customer name and address for which the order amount exceeds 4000 RMB 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 the total order amount of each customer, display the customer number and total order amount, and sort 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. Calculate the total quantity and average unit price of each product ordered by each customer, and sort the number by 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. query the order numbers of more than three products.
Select order_no
From sale_item
Group by order_no
Having count (*)> 3

45. The products to be queried include at least the orders for the products ordered in order 3.
Select distinct order_no
From sale_item
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, find all orders with an order amount greater than "E0013 clerk's amount for each order received on, and display the clerk who undertakes the order 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 = '2017-11-10 ')

47. query the information of the employees who undertake the business at the end.
Select *
From employee
Where not exists
(Select * from sales B where a. emp_no = B. sale_id)

48. query the name, phone number, order number, and order amount of a customer 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 clerk in each month, and sort by Clerk ID and month in 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. Calculate the total sales quantity and total sales amount of each product. The product number, product name, total quantity, and total amount must be displayed and arranged in ascending order by 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. query the customer number, customer name, and address of the customer whose total order amount exceeds 'c0002.
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-performing salesman's number, salesman's 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. The customer number, customer name, product number, product name, quantity, and unit price are displayed.
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. Calculate the average salary of each department, and sort the average salary from small to large.
Select dept, avg (salary)
From employee
Group by dept
Order by avg (salary)