54 SQL query statements that must be mastered by the learning database

--1, find the employee's number, name, department, and date of birth, if the date of birth is null, the display date is unknown, and the output is sorted by department, and the date format is YYYY-MM-DD. Select Emp_no,emp_name,dept,isnull (Convert (char), birthday,120), ' date unknown ') Birthdayfrom Employeeorder by Dept--2, Find employee names, gender, departments and titles in the same unit as Zichiang select Emp_no,emp_name,dept,titlefrom employeewhere emp_name<> ' Zichiang ' and dept in ( Select dept from Employeewhere emp_name= ' Zichiang ')--3, aggregated by department, statistics per department total wage select Dept,sum (Salary) from Employeegroup by Dept--4, find the product name for the 14-inch display of the sales of goods, display the product number, sales quantity, Unit price and amount select A.prod_id,qty,unit_price,unit_price*qty totpricefrom sale_ Item A,product bwhere a.prod_id=b.prod_id and Prod_name= ' 14 inch display '--5, summarized in sales list by product number, statistics on sales quantity and amount for each product select prod_id, SUM (qty) totqty,sum (qty*unit_price) Totpricefrom Sale_itemgroup by Prod_id--6, Use the CONVERT function to count the total amount of orders per customer for 1996 years by customer number select Cust_id,sum (tot_amt) totpricefrom saleswhere convert (char (4), Order_date, * = ' 1996 ' GROUP by CUST_ID--7, find the customer number, name and order total of the sales record select A.cust_id,cust_name,sum (Tot_amt) totpricefrom customer A, Sales Bwhere A.cust_id=b.cust_idgroup by A.cust_id,cust_name--8, findCustomer number, name, and order total for sales records in 1997 Select A.cust_id,cust_name,sum (Tot_amt) totpricefrom customer a,sales bwhere a.cust_id= B.CUST_ID and CONVERT (char (4), order_date,120) = ' 1997 ' GROUP by A.cust_id,cust_name--9, find the most sold sales record of select Order_no, Cust_id,sale_id,tot_amtfrom saleswhere tot_amt= (select Max (Tot_amt) from sales)--10, find the Salesperson list and sales date at least 3 times select Emp_  Name,order_datefrom employee A,sales b where emp_no=sale_id and A.emp_no in (select Sale_idfrom salesgroup by sale_idhaving COUNT (*) >=3) Order by emp_name--11, using existential quantifiers to find the customer name without an order record select Cust_namefrom customer awhere NOT EXISTS (select *from Sales bwhere a.cust_id=b.cust_id)--12, use left outer connection to find each customer's customer number, name, order date, order Amount order Date do not display time, the date format is YYYY-MM-DD by customer number sort, Same customer orders descending order output Select A.cust_id,cust_name,convert (char (Ten), order_date,120), Tot_amtfrom customer a left outer join Sales B on A.cust_id=b.cust_idorder by A.cust_id,tot_amt Desc--13, looking for the sale of 16M dram, requires that the corresponding salesperson's name, gender, sales date, sales quantity, and amount be displayed. Where sex with male, female to express select Emp_name Name, gender = Case A.sex when ' m ' then ' male ' when ' f ' then ' women ' else ' not ' end, sales dayPeriod = IsNull (convert (char), c.order_date,120), ' date unknown '), qty Quantity, Qty*unit_price as amount from employee A, sales B, Sale_item C,PR Oduct dwhere d.prod_name= ' 16M DRAM ' and d.prod_id=c.prod_id anda.emp_no=b.sale_id and b.order_no=c.order_no--14, Find sales records for everyone, ask for the salesperson's 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 ' Then ' man ' when ' f ' then ' Women ' 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 dwhere d.prod_id=c.prod_id and B.ord Er_no=c.order_no--15, find the customer name and total payment for the largest sales amount select Cust_name,d.cust_sumfrom Customer A, (select Cust_id,cust_sumfrom ( Select cust_id, sum (tot_amt) as Cust_sumfrom salesgroup by cust_id) bwhere b.cust_sum = (select Max (cust_sum) from (Selec T cust_id, sum (tot_amt) as Cust_sumfrom Salesgroup by cust_id) c) dwhere a.cust_id=d.cust_id--16, find salesperson number, last name, sales total less than $1000 Name and sales Select Emp_no,emp_name,d.sale_sumfrom employee A, (Select Sale_id,sale_sumfrom (select sale_id, sum (tot_amt) as Sale_sumfrom salesgroup by sale_id) bwhere B.sale_sum <10 Dwhere a.emp_no=d.sale_id--17, find the customer number, customer name, product number, product name, quantity, and amount for at least 3 items sold Select A.cust_id,cust_name,b.prod_id,prod_ Name,d.qty,d.qty*d.unit_pricefrom customer A, product B, sales C, Sale_item Dwhere a.cust_id=c.cust_id and D.PROD_ID=B.PR od_id and C.order_no=d.order_no and a.cust_id in (select Cust_idfrom (select Cust_id,count (Distinct prod_id) Prodidfrom (s Elect Cust_id,prod_idfrom sales E,sale_item Fwhere e.order_no=f.order_no) Ggroup by cust_idhaving count (distinct prod_id ) >=3) h)--18, find at least the same customer number, name and product number, product name, quantity, and amount as the World Technology Development Company Sales Select a.cust_id,cust_name,d.prod_id,prod_name,qty,qty* Unit_pricefrom customer A, product B, sales C, Sale_item Dwhere 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 fwhere cust_name= ' World Technology Development company ' and X.cust_id=e.cust_ ID ande.order_no=f.order_no and NOT EXISTS ( Select G.*from Sale_item g, sales hwhere g.prod_id = f.prod_id and G.order_no=h.order_no andh.cust_id=a.cust_id)) 19. Look up the table Workers with surname Liu's work number, department, salary select Emp_no,emp_name,dept,salaryfrom employeewhere emp_name like ' Liu ' 20, Find all customer numbers with an order amount greater than 2000 select Cust_idfrom Saleswhere tot_amt>200021, statistics the number of employees in the employee's salary at 4000-6000 select COUNT (*) as The number from Employeewhere salary between 4000 and 600022, the average salary of the employee in the same department in the query table, but only the "address" is "Shanghai" staff Select AVG (Salary) avg_sal,dept From the employee where addr like ' Shanghai% ' GROUP by dept23, the address of the table "Shanghai" to the "Beijing" update employee set addr like ' Beijing ' where addr l Ike ' Shanghai ' 24, find basic information about female employees in the business or accounting department. Select Emp_no,emp_name,deptfrom employee where sex= ' F ' and dept in (' Business ', ' accounting ') 25, displays the sum of the sales amounts for each product, and is output from large to small according to the sales amount. Select prod_id, sum (qty*unit_price) from Sale_item Group by Prod_idorder by sum (qty*unit_price) desc26, select number bounded by ' C0001 ' and ' C0004 ' customer number, customer name, customer address. Select Cust_id,cust_name,addrfrom customer where cust_id between ' C0001 ' and ' C0004 ' 27, calculates a total of several products sold. Select COUNT (Distinct prod_id) as ' total sales of products ' from Sale_item 28,Increase the salary of the business Department staff by 3%. Update Employeeset salary=salary*1.03where dept= ' business ' 29, the employee table finds the lowest-paid staff information. Select *from employeewhere salary= (select min (Salary) from employee) 30, use join to query customer name "customer" of the goods purchased "customer", "Order Amount", "Ordering Date", " Phone number "Select A.cust_id,b.tot_amt,b.order_date,a.tel_nofrom customer a join sales bon a.cust_id=b.cust_id and Cust_name Like ' Customer C ' 31, the sales table finds out that the order amount is greater than "E0013 clerk received the amount of each order on this day in 1996/10/15" for all orders. Select *from saleswhere tot_amt>all (select Tot_amt from sales where sale_id= ' E0013 ' and order_date= ' 1996/10/15 ') Order by Tot_amt32, calculate the average selling price of the ' P0001 ' product select AVG (unit_price) from Sale_itemwhere prod_id= ' P0001 ' 33, find out the orders received by the company's female employees select Sale_ Id,tot_amtfrom saleswhere sale_id in (select sale_id from Employeewhere sex= ' F ') 34, find employees who entered the company service on the same day select A.emp_no,a.emp_ Name,a.date_hiredfrom Employee Ajoin employee bon (a.emp_no!=b.emp_no and a.date_hired=b.date_hired) Order by A.date_ Hired35, find out the current performance of more than 232000 Yuan employee number and name. Select Emp_no,emp_namefrom Employee where Emp_no in (select Sale_idfrom Sales GROUP by sale_idhaving sUm (tot_amt) <232000) 36. The average wage of all female employees in the employee table and the median salary of all female workers in "Shanghai City" select AVG (Salary) from employeewhere sex like ' F ' unionselect avg (salary) from employeewhere sex like ' f ' and addr like ' Shanghai% ' 37, in the employee table, query employee information for salaries exceeding the employee's average salary. SELECT * from the 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 the sales performance from large to small sort. Select sale_id, SUM (Tot_amt) from the sales group by sale_id have sum (tot_amt) >10000order by sum (tot_amt) desc39, find the company male salesman The order number and the order amount of the order amount exceeding $2000. Select order_no,tot_amtfrom Sales, Employeewhere sale_id=emp_no and sex= ' M ' and tot_amt>200040, Query the sales table for the order number with the highest order amount and the order amount. Select Order_no,tot_amt from Sales where tot_amt= (select Max (Tot_amt) from sales) 41, query the customer name and address of the order amount exceeding $4000 in each order. Select cust_name,addr from customer A,sales b where a.cust_id=b.cust_id and tot_amt>400042, find the total order amount per customer, show customer number and total order Amount and is sorted in descending order by total subscription amount. Select Cust_id,sum (Tot_amt) from Salesgroup to cust_id order by sum (Tot_amt) desc43, asks for the total quantity and average unit price of each product ordered by each customer, and by customer number, Product numbers are arranged from small to large. Select CUST_ID,PROd_id,sum (qty), SUM (qty*unit_price)/sum (qty) from Sales A, Sale_item bwhere A.order_no=b.order_nogroup by Cust_id,prod_ Idorder by CUST_ID,PROD_ID44, inquiry order number of more than three products. Select order_no from Sale_itemgroup by order_nohaving count (*) >345, the product of the inquiry order contains at least the order of the products ordered in order No. 3rd.  SELECT DISTINCT Order_nofrom sale_item awhere 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 the order amount in the sales table is greater than "E0013 salesman in 1996/11/10 this day to receive the amount of each order", and show the amount of the clerk and the order that undertook these orders. Select Sale_id,tot_amt from Saleswhere tot_amt>all (select Tot_amt from sales where sale_id= ' E0013 ' and order_date= ' 199 6-11-10 ') 47, query the end of the staff to undertake business information. Select *from Employee Awhere NOT EXISTS (SELECT * from sales b where a.emp_no=b.sale_id) 48, check the name of the customer from Shanghai, telephone, order number and order amount. Select Cust_name,tel_no,order_no,tot_amtfrom customer A, sales bwhere a.cust_id=b.cust_id and Addr= ' Shanghai City ' 49, Inquire each salesman each month's performance, and according to the salesman number, the month descending sort. Select Sale_id,month (order_date), sum (Tot_amt) from the Sales group by Sale_id,month (order_date) Order by Sale_id,month (order_date) DESC50, total sales quantity for each product and total sales Amount , the requirements show the product number, product name, total quantity and total amount, and according to the product number from small to large arrangement. Select a.prod_id,prod_name,sum (qty), SUM (qty*unit_price) from Sale_item a,product bwhere a.prod_id=b.prod_id Group by A.prod_id,prod_nameorder by A.PROD_ID51, the customer number, customer name and address of the total subscription amount for the total order amount of the ' C0002 ' customer. Select cust_id, Cust_name,addrfrom customerwhere cust_id in (select cust_id from Sales Group by cust_idhaving sum (Tot_amt) > (Select sum (tot_amt) from sales where cust_id= ' C0002 ')) 52, query the best performance of the salesman number, the name of the salesman and its total sales amount. Select Emp_no,emp_name,sum (Tot_amt) from the employee A,sales bwhere A.emp_no=b.sale_idgroup by emp_no,emp_namehaving sum ( Tot_amt) = (select Max (Totamt) from (select Sale_id,sum (Tot_amt) Totamtfrom salesgroup by sale_id) c) 53, To inquire the detailed list of each product ordered by each customer, request to show 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_pricefrom customer A,sales B, Sale_item C, product Dwhere A.cus t_id=b.cust_id and B.order_no=c.order_no and C.prod_ID=D.PROD_ID54, seeking the average salary of each department, requires the average salary to be ordered from small to large. Select Dept,avg (Salary) from Employeegroup to Dept ORDER by AVG (Salary)

54 SQL query statements that must be mastered by the learning database