1, find the employee's number, name, department and date of birth, if the date of birth is a null value,
--Display date is unknown, and 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 employee names, gender, departments, and titles 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, summarize by department, statistic the total salary of each department
Select Dept,sum (Salary)
From employee
GROUP BY Dept
--4, find the product name for the 14-inch display of the sales of goods,
--Displays the product's number, 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 display '
--5, summarize by product number in sales schedule, count 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 the CONVERT function to count the total order amount for each customer 1996 year 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 total for the 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 the customer number, name, and order total for a sales record 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
Select Order_no,cust_id,sale_id,tot_amt
From sales
where tot_amt=
(select Max (Tot_amt)
From sales)
--10, find sales list and sales date 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 quantifier to find the customer name without an order record
Select Cust_name
From Customer A
Where NOT EXISTS
(SELECT *
From Sales B
where a.cust_id=b.cust_id)
--12, use left outer connection to find customer number, name, order date, order amount for each customer
--Order date do not display time, date format is YYYY-MM-DD
--Sort by customer number, the same customer then sort by order descending output
Select A.cust_id,cust_name,convert (char), order_date,120), Tot_amt
From customer a left outer joins sales B on a.cust_id=b.cust_id
ORDER BY A.cust_id,tot_amt Desc
--13, find the sales of 16M dram, ask to display the corresponding salesperson's name,
-Gender, sales date, sales volume and amount, of which sex is expressed in male and female
Select Emp_name Name, gender = case A.sex if ' 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, ask to show the salesperson's number, name, gender,
--Product name, quantity, unit price, amount and date of sale
Select emp_no number, emp_name name, gender = case A.sex if ' 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 joins 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 for 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 salesperson numbers, names, and sales with a total sales of less than $1000
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 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 product 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 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 names of workers in the table 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 above 2000 for all order amounts
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 of people
From employee
Where salary between 4000 and 6000
22. Check the average salary of the employees in the same department in the table, 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. Change the address of the table to "Shanghai City" to "Beijing"
Update employee
Set addr like ' Beijing '
Where addr like ' Shanghai City '
24. Find basic information about 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 sum of sales amount of each product, and according to the amount of sales 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 in the ' 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. Increase the salary of the staff of the business department by 3%.
Update employee
Set salary=salary*1.03
where dept= ' business '
29, the employee table to find the lowest salary staff information.
SELECT *
From employee
where salary=
(select min (Salary)
From employee)
30. Use Join to query customer name "customer", "Client", "Customer", "Order Amount", "Ordering Date", "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 ' customer C '
31, the sales table to find out the order amount is greater than "E0013 salesman in 1996/10/15 this day, 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 average selling price of ' P0001 ' products
Select AVG (unit_price)
From Sale_item
where prod_id= ' P0001 '
33, find out the company female employees received 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. Find out the average salary and address of all female employees in the employee's table the average salary of all female employees 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, inquire about the employee's salary exceeding the employee's average salary.
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 the 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 out the order number and order amount of the company male salesman and 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. Check the order number and the order amount of the order amount in the Sales table.
Select Order_no,tot_amt from Sales
Where tot_amt= (select Max (Tot_amt) from sales)
41. Inquire the customer name and address of the order amount exceeding 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, to find out the total order amount per customer, showing the customer number and the total order amount, and in descending order of the total amount ordered.
Select Cust_id,sum (Tot_amt) from sales
Group by cust_id
ORDER by sum (TOT_AMT) desc
43, the total number of each product ordered by each customer 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 orders the order number of more than three products.
Select Order_no
From Sale_item
Group by Order_no
Having Count (*) >3
45. The product ordered by the inquiry contains at least the order of the products ordered in order No. 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 the "E0013 salesman in 1996/11/10, the amount of each order received" all orders, and display to undertake these orders of the clerk 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 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 the name of the customer from Shanghai, telephone, 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's performance, and according to the salesman number, the month descending sort.
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, 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 b
Where a.prod_id=b.prod_id
Group by A.prod_id,prod_name
ORDER BY a.prod_id
51. The customer number, customer name and address of the total subscription amount of the total order amount exceeding ' C0002 ' customers.
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, the name of the salesman 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, inquires each customer ordered each 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, the average salary for each department, requires the average salary from small to large order.
Select Dept,avg (Salary)
From employee
GROUP BY Dept
ORDER by AVG (Salary)
The Select command for instance learning SQL
