SQL Server programming must know--(37-50-point summary)

----------------------------Summary Data---------------------------

--aggregation functions are used to summarize data. A variety of methods to return the desired results, efficient and quick to obtain results;

--37. Returns the average price of all products in the product table
SELECT AVG (prod_price) as Avg_price
From Products

--38. Return the average price of all products from supplier id=1003 in the product table
SELECT AVG (prod_price) as Avg_price
From Products
WHERE vend_id =1003

--39. Returns the total number of customers in the Customers table
SELECT COUNT (*) as Num_cust
From customers

--40. Returns the count of customers with e-mail addresses in the Customers table
SELECT COUNT (cust_email) as Num_cust
From customers

--41. Returns the price of the most expensive item in the Products table
SELECT MAX (prod_price) as Max_price
From Products

--42. Returns the price of the cheapest item in the Products table
SELECT MIN (prod_price) as Max_price
From Products

--43. Returns the sum of all the items in the order number =20005,
SELECT SUM (quantity) as Items_ordered
From OrderItems
WHERE order_num=20005;

--44. Returns the sum of the prices of all items =20005 the order number
SELECT SUM (item_price*quantity) as Total_price
From OrderItems
WHERE order_num=20005;

--45. Aggregating different values, which are computed by default for all rows, can either specify the all parameter or do not give the parameter

--id=1003 the supplier, and only calculates the average price for the goods with different prices.
SELECT AVG (DISTINCT prod_price) as Avg_price
From Products
WHERE vend_id =1003

--46. Composite aggregation functions, which can contain multiple aggregation functions
-Returns the number of items in the Products table, the highest, lowest, and average of the product price.
SELECT COUNT (*) as Num_items,
MIN (Prod_price) as Price_min,
MAX (Prod_price) as Price_max,
AVG (Prod_price) as Price_avg
From Products

------------------------grouped Data-----------------------
--47. Data groups, and create groupings. Returns the number of products supplied by each vendor
SELECT vend_id, COUNT (*) as Num_prods
From Products
GROUP by vend_id;

-Results
vend_id Num_prods
1001 3
1002 2
1003 7
1005 2

--48. Some important provisions of the GROUP by clause

--The GROUP by clause can contain any number of columns;
SELECT Vend_id,count (*) as Num_prods
From Products
GROUP by vend_id;

SELECT vend_id,prod_id, COUNT (*) as Num_prods
From Products
GROUP by vend_id,prod_id;

--The GROUP BY clause can specify multiple groupings, and the data will be aggregated on the last specified group;
SELECT vend_id,prod_id, COUNT (*) as Num_prods
From Products
GROUP by vend_id,prod_id;

--Each column listed by the GROUP by clause must be a retrieval column or a valid expression (but not a clustered function), the following expression is wrong;
SELECT vend_id,prod_id, COUNT (*) as Num_prods
From Products
GROUP by Vend_id,prod_id,num_prods;

--The GROUP BY clause, except for the clustered calculation statement, each column in the SELECT statement must be given in the GROUP by clause;
SELECT vend_id,prod_id, COUNT (*) as Num_prods
From Products
GROUP by vend_id,prod_id;

--GROUP BY clause, Group column China has a null value, then NULL is returned as a grouping. If the columns have multiple rows of null values, they will be grouped into one set;

--The GROUP by clause, which must appear after the WHERE clause, before the ORDER by clause;
SELECT vend_id,prod_id, COUNT (*) as Num_prods
From Products
GROUP by vend_id,prod_id
ORDER by vend_id,prod_id DESC

--49. Filter the grouping, specify which groups to include, and what groups to exclude;

--List all customers with at least two orders
SELECT cust_id, COUNT (*) as Orders
From Orders
GROUP by cust_id
Having COUNT (*) >=2

--list suppliers with more than two products with a price of more than 10:
SELECT vend_id, COUNT (*) as Num_prods
From Products
WHERE Prod_price >= 10
GROUP by vend_id
Having COUNT (*) > = 2

--50. grouping and sorting, stipulating which groupings to exclude, and which groupings are excluded;

ORDER BY: The output produced by the sort, which can be used by any column (even if the column is not selected), does not necessarily require
GROUP BY: Group rows. However, the output may not be in the order of grouping, it is possible to use Select columns or expression columns, and you must use each selection column expression

--Retrieve order number and total order price for orders with a total order price greater than or equal to 50
SELECT Order_num,sum (quantity*item_price) as OrderTotal
From OrderItems
GROUP by Order_num
Having SUM (Quantity*item_price) >=50

Order_num OrderTotal
20005 149.87
20006 55.00
20007 1000.00
20008 125.00

--Retrieve order number and total order price for orders with a total order price greater than or equal to 50, sort output by total order price
SELECT Order_num,sum (quantity*item_price) as OrderTotal
From OrderItems
GROUP by Order_num
Having SUM (Quantity*item_price) >=50
ORDER by OrderTotal

Order_num OrderTotal
20006 55.00
20008 125.00
20005 149.87
20007 1000.00

--select clauses and their order

--select the column or expression to return,
--from table from which data is retrieved
--where row-level filtering
--group by group description
--having Group-level filtering
--order by output sort order

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1702519

SQL Server programming must know--(37-50-point summary)