script>
SQL learning
1
2
3
4
5
6
Seduce
8
9
10
11
12
.
.
.
.
.
.
.
.
.
.
.
.
A b C DD E f
-1, find the number of employees, name, department, and date of birth, if the date of birth is empty,
- The date is unknown, and sorted according to the 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, find the employee name, gender, department, and title SELECT EMP_NO, EMP_NAME, DEPT, TIMP_NAMEEEEE EMP_NAME = 'Yu Zhiqi')
--3, summarized according to the department, count the total salary of each department SELECT DEPT, SUM (Salary) from Employee GROUP BY DEPT
--4, find the sales of the product name 14 inch display item, - Show the number, sales of the item, number, 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 display '
--5, in the sales schedule, summarize the product number, summarize the quantity of each product and the amount Select Prod_ID, SUM (Qty) TotQty, SUM (Qty * Unit_price) Totprice from Sale_Item Group by Product_ID
--6, using the CONVERT function to count the total amount of each customer in 1996 in 1996, using the CONVERT function. SELECT CUST_ID, SUM (TOT_AMT) Totprice from Sales Where Convert (CHAR (4), Order_Date, 120) = '1996' Group by Cust_ID
--7, Find a customer number, name and order total 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 a customer number, name and order total SELECT A.CUST_ID, CUST_NAME, SUM (Tot_AMT) Totprice from Customer A, Sales B Where A.Cust_ID = B.CUST_ID AND Convert_ID = B.CUST_ID AND CHAR (4), ORDER_DATE, 120) = '1997' Group by a.cust_id, cust_name
--9, Find a sales volume record SELECT ORDER_NO, CUST_ID, SALE_ID, TOT_AMT from sales where tot_amt = (SELECT MAX (TOT_AMT) from sales
--10 Find at least 3 salesperson lists and sales date 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
--12 Order descending order Sort 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 16M DRAM sales, require the name, - gender, sales date, number of sales of the corresponding salesperson, the number of gender, female, female, express emp_name name, gender = case a.sex when 'm' Then 'men's' f 'Then' female 'Else' und' end, sales dates = isnull (CONVERT (CHAR (10), C.ORDER_DATE, 120), 'Date Unknown'), Qty Quantity, Qty 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 the sales record of everyone, require the salesperson number, name, gender, - product name, quantity, unit price, amount and sales date Select EMP_NO number, EMP_NAME name, gender = case a.sex when ' M 'TEN' Male 'When' f 'The' Else 'Uns 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, find the maximum customer name and total payment Select Cust_Name, D.Cust_ID, Cust_Sum from (SET_AMT) AS CUST_SUM from Sales Group By Cust_Sum from Sales Group By Cust_Sum from Sales Group By Cust_Sum from Sales Group by Cust_Sum from Sales Group by Cust_Sum from Sales Group By Cust_Sum from Sales Group by Cust_Sum from Sales Group by Cust_Sum from Sales Group by Cust_Sum from Sales Group by Cust_Sum from Sales Group By Cust_ID) B Where b. Cust_Sum = (Select Max (Select Cust_ID, SUM (SET_AMT) AS Cust_Sum from Sales Group By Cust_ID) C)) D where a.cust_id = D.CUST_ID - 16, find sales of total sales than 1000 yuan Number, Name and Sales Select Emp_no, Emp_name, D.Sale_Sum from Employee A, (Select Sale_ID, Sale_Sum from (SET_AMT) AS SALE_S SALE B.SALE_SUM <1000) D Where a.emp_no = d.sale_id
--17, find at least 3 merchandis, customer name, product number, product name, quantity, and amount Select a.cust_id, custom_name, d.Qty, D.QT_Name, D.QTY, D.QT_Name, D.QTY, D.QT_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 CUST_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 Product_ID)> = 3) H)
--18, find at least the same customer number, name, and product number, product name, quantity and amount SELECT A.CUST_ID, CUST_NAME, D.PROD_ID, CUST_NAME, QTY, Qty * Unit_Price from Customer A, Qty * Unit_name, Qty, Qty * Unit_name, Qty, Qty * Unit_price from Customer A, Qty * Unit_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 Company' 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 all the employees of Liu's employee, department, salary select EMP_NO, EMP_NAME, DEPT, SALARY FROM EMPLOYEE WHERE EMP_NAME LIKE 'Liu% '20, find all customer number of all custom orders 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 SALY BETWEEN 4000 and 6000
22. The average salary of the employees of the same sector in the table, but only the address is "Shanghai" employee SELECT AVG (SALARY) AVG_SAL, DEPT from Employee ADDR LIKE 'Shanghai%' Group by DEPT
23. Change the staff address of "Shanghai" in the table to "Beijing" Update Employee Set Addr Like 'Beijing' Where Addr Like 'Shanghai'
24. Find the basic information of the business unit or the female employee of the accounting department. SELECT EMP_NO, EMP_NAME, DEPT from Employee Where Sex = 'F'and Dept in (' Business', 'Accounting')
25. Shows the sum of the sales amount of each product, and the amount of sales is large to small output. SELECT PROD_ID, SUM (Qty * Unit_Price) from sales by prod_id order by sum (Qty * Unit_price) DESC
26. Select the number of customer numbers, customer names, and customer addresses that are bounded in 'c0001' and 'c0004'. SELECT CUST_ID, CUST_NAME, ADDR from Customer WHERE CUST_ID BETWEEN 'C0001' And 'C0004'
27, calculated several products sold. SELECT Count (Distinct Product PROD_ID) AS 'Total Sales Products' from Sale_Item
28, raising the salary of the business unit employee 3%. Update Employee Set Salary = SALARY * 1.03 Where Dept = 'Business'
29. Find the lowest employee information from the Employee table. Select * from Employee WHERE SALY = (SELECT MIN (SALARY) from EMPLOYEE
30. Use Join Query the customer name, "customer name", "order amount", "order date", "customer name", "phone number", "phone number", "" SELECT A.CUST_DATE, B.TOT_AMT, B.Order_Date, A. Tel_no from customer a join sales b on a.cust_id = b.cust_id and custom 丙 _ = 客户 'customer
31. All orders that look out of the order amount greater than "E0013 Salesman in 1996/10/15). Select * from sales where tot_amt> all (select tot_amt from sales where sales_date = '1996/10/15) ORDER BY TOT_AMT32, calculation of the average sales of' P0001 'products SELECT AVG (Unit_Price) from sale_item WHERE PROD_ID = 'P0001'
33. Find the order Select Sale_ID, Tot_amt from Employe WHERE Sex = 'F') of the company female employee.
34. Find the employee of the same day to enter the company's service 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 the current performance of the employee number and name of more than 232,000 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. The average salary of all female employees in the Employee table in "Shanghai" all female workers have average salary SELECT AVG (Salary) from Employee Where Sex Like 'F' Union Select Avg (Salary) from Employee Where Sex Like 'F' and Addr Like 'Shanghai%'
37. Inquiry a salary exceeding employee information in the EMPLOYEE table. Select * from Employee WHERE SALE> (SALARY) from Employee
38. Find the salesman number and sales performance current sales performance of more than 10,000 yuan, and sort by sales performance from large to small. SELECT SALE_ID, SUM (TOT_AMT) from sales group by sales> 10000 ORDER BY SUM (TOT_AMT) DESC
39. Find out the order number and order amount of the company's male salesman and the order amount exceeding 2,000 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 in the order amount in the Sales table. Select ORDER_NO, TOT_AMT from Sales Where Tot_AMT = (SELECT MAX (TOT_AMT) from sales
41. The query is the customer name and its address of more than 4,000 yuan per order. Select Cust_Name, AddR from Customer A, Sales B Where A.cust_Id = B.CUST_ID AND TOT_AMT> 4000
42. Ask each customer's total order amount, display the customer number and total order amount, and arranged in descending order according to the total order. SELECT CUST_ID, SUM (TOT_AMT) from sales group by cust_id order by sum (tot_amt) desc43, seeking total quantity and average unit price of each customer ordering, and is ranked from small to large to large. SELECT CUST_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 query ordering the order number of three or more products. Select ORDER_NO from Sale_Item Group by Order_no Having Count (*)> 3
45. The product of the query ordered contains at least the order of the product ordered in Order 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. Find out all orders in the Sales table greater than "E0013 Savers at 1996/11 this day) all orders for each order of each order, and display the salesman of these 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. Information of the employee of the end to undertake the business. Select * from Employee a where not exists (SELECT * from sales b where a.emp_no = b.sale_id)
48, query the name, phone, order number and order amount from the 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 each salesman's performance, and sorted by the salesman number, 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. See the total sales quantity and total sales amount of each product, require the product number, product name, total quantity and total amount, and arrange the product number from small to large. 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 total order amount More than the total order amount of the total order amount of 'C0002' customers, the name and its 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, the best performance of the query business Wemon number, business name and its 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, requiring 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. Ask the average salary of each department, requiring average salary from small to large. Select Dept, Avg (Salary) from Employee Group by Dept Order by AVG (SALARY)