TSQL Lab 09 習題

LAB 09
Exercise 1

---------------------------------------------------------------------
-- Task 2
-- 
-- Write a SELECT statement that will return groups of customers that made a purchase.
-- The SELECT clause should include the custid column from the Sales.Orders table 
-- and the contactname column from the Sales.Customers table. 
-- Group by both columns and filter only the orders from the sales employee 
-- whose empid equals five.
---------------------------------------------------------------------

寫法1
select c.custid,c.contactname
from Sales.Customers c
inner join Sales.Orders o
on c.custid = o.custid
where o.empid = 5
group by c.custid,c.contactname


寫法2
select distinct(o.custid), c.contactname,o.empid
from Sales.Orders o
inner join Sales.Customers c
on o.custid = c.custid
where o.empid = 5
order by o.custid asc

---------------------------------------------------------------------
-- Task 3
-- 
-- Copy the T-SQL statement in task 2 and modify it to include the city column 
-- from the Sales.Customers table in the SELECT clause. 
--
-- Execute the query. You will get an error. What is the error message? Why?
--
-- Correct the query so that it will execute properly.
---------------------------------------------------------------------

select c.custid,c.contactname,c.city
from Sales.Customers c
inner join Sales.Orders o
on c.custid = o.custid
where o.empid = 5
group by c.custid,c.contactname

-- error

select c.custid,c.contactname,c.city
from Sales.Customers c
inner join Sales.Orders o
on c.custid = o.custid
where o.empid = 5
group by c.custid,c.contactname,c.city
-- correct

因為多增加的欄位沒有包含在group by 群組裡

---------------------------------------------------------------------
-- Task 4
-- 
-- Write a SELECT statement that will return groups of rows based on the custid column 
-- and a calculated column orderyear representing the order year based on the orderdate column
-- from the Sales.Orders table. 
-- Filter the results to include only the orders from the sales employee whose empid 
-- equal five.
---------------------------------------------------------------------

寫法1
SELECT
custid, YEAR(orderdate) AS orderyear
FROM Sales.Orders
WHERE empid = 5
GROUP BY custid, YEAR(orderdate)
ORDER BY custid, orderyear;

寫法2
select distinct(custid),YEAR(orderdate) as orderyear,empid
from Sales.Orders
where empid = 5
order by orderyear asc,custid asc

---------------------------------------------------------------------
-- Task 5
-- 
-- Write a SELECT statement to retrieve groups of rows based on the categoryname column 
-- in the Production.Categories table. Filter the results to include only the 
-- product categories that were ordered in the year 2008.
---------------------------------------------------------------------

select
--distinct(p.productid),p.productname,
--o.orderdate,
c.categoryid,c.categoryname
from Production.Categories c
inner join Production.Products p
on c.categoryid = p.categoryid
inner join Sales.OrderDetails od
on p.productid = od.productid
inner join Sales.Orders o
on o.orderid = od.orderid
where YEAR(o.orderdate) = '2008'
group by c.categoryid,c.categoryname

Exercise 2

---------------------------------------------------------------------

-- Task 1

-- 

-- Write a SELECT statement to retrieve the orderid column from the Sales.Orders table

-- and the total sales amount per orderid. (Hint: Multiply the qty and unitprice columns

-- from the Sales.OrderDetails table.) Use the alias salesmount for the calculated column.

-- Sort the result by the total sales amount in descending order.

--------------------------------------------------------------------

select o.orderid, o.orderdate,SUM(od.qty*od.unitprice) as salesmount

from Sales.Orders o

inner join Sales.OrderDetails od

on o.orderid = od.orderid 

group by o.orderid,o.orderdate

order by salesmount desc

---------------------------------------------------------------------

-- Task 2

-- 

-- Copy the T-SQL statement in task 1 and modify it to include

-- the total number of order lines for each order and 

-- the average order line sales amount value within the order. 

-- Use the aliases nooforderlines and avgsalesamountperorderline, respectively.

--------------------------------------------------------------------

select 

o.orderid

,o.orderdate

,SUM(od.qty*od.unitprice) as salesmount

,COUNT(*) AS noofoderlines

,AVG(od.qty*od.unitprice) as avgsalesamountperorderline

from Sales.Orders o

inner join Sales.OrderDetails od

on o.orderid = od.orderid 

group by o.orderid,o.orderdate

ORDER BY salesmount DESC;

---------------------------------------------------------------------

-- Task 3

-- 

-- Write a select statement to retrieve the total sales amount for each month.

-- The SELECT clause should include a calculated column 

-- named yearmonthno (YYYYMM notation) based on the orderdate column in the Sales.Orders

-- table and a total sales amount (multiply the qty and unitprice columns from 

-- the Sales.OrderDetails table). Order the result by the yearmonthno calculated column.

-- Execute the written statement and compare the results that you got with the recommended result shown in the file 64 - Lab Exercise 2 - Task 3 Result.txt.

---------------------------------------------------------------------

select 

CONVERT(nvarchar(6), o.orderdate, 112) as yearmonthno

,SUM(od.qty*od.unitprice) as saleamountpermonth

from Sales.Orders o

inner join Sales.OrderDetails od

on o.orderid = od.orderid 

group by CONVERT(nvarchar(6), o.orderdate, 112)

ORDER BY yearmonthno ASC;

--------------------------------------------------------------------

-- Task 4

-- 

-- Write a select statement to retrieve all the customers 

-- (including those that did not place any orders) and their total sales amount,

--  maximum sales amount per order line, and number of order lines. 

--

-- The SELECT clause should include the custid and contactname columns from 

-- the Sales.Customers table and four calculated columns based on appropriate aggregate functions:

-- totalsalesamount, representing the total sales amount per order

--  maxsalesamountperorderline, representing the maximum sales amount per order line

--  numberofrows, representing the number of rows (use * in the COUNT function)

--  numberoforderlines, representing the number of order lines (use the orderid column in the COUNT function)

--

-- Order the result by the totalsalesamount column.

-- Notice that the custid 22 and 57 rows 

-- have a NULL in the columns with the SUM and MAX aggregate functions. 

-- What are their values in the COUNT columns? Why are they different?

---------------------------------------------------------------------

select 

c.custid

,c.contactname

,SUM(od.qty*od.unitprice) as totalsalesamount

,MAX(od.qty*od.unitprice) as maxsalesamountperorderline

,count(*) as numberofrows

,count(o.orderid) as numberoforderlines

from Sales.Customers c

left join Sales.Orders o

on o.custid = c.custid

left join Sales.OrderDetails od

on o.orderid = od.orderid

group by c.custid,c.contactname

order by c.custid asc

因 numberofrows 按照custid去計算因此有一筆

numberoforderlines 按照orderid去計算，顧客沒訂單，所以0筆

Exercise 3

---------------------------------------------------------------------

-- Task 1

-- 

-- A junior analyst prepared a T-SQL statement to retrieve the number of orders

-- and the number of customers for each order year. 

-- Observe the provided T-SQL statement and execute it:

--

-- Observe the result and notice that the number of orders is the same 

-- as the number of customers. Why?

--

-- Correct the T-SQL statement to show the correct number of customers 

-- that placed an order for each year.

---------------------------------------------------------------------

SELECT

YEAR(orderdate) AS orderyear, 

COUNT(orderid) AS nooforders, 

count(distinct(custid)) AS noofcustomers

FROM Sales.Orders 

GROUP BY YEAR(orderdate);

---------------------------------------------------------------------

-- Task 2

-- 

-- Write a SELECT statement to retrieve the number of customers based on the first letter

-- of the values in the contactname column from the Sales.Customers table. 

-- Add an additional column to show the total number of orders placed by each group of customers.

-- Use the aliases firstletter, noofcustomers and nooforders. 

-- Order the result by the firstletter column.

------------------------------------------------------------------

select 

SUBSTRING(c.contactname,1,1) as firstletter

,COUNT(distinct c.custid) as noofcustomers

,COUNT(o.orderid) as nooforders

from Sales.Customers c

left join Sales.Orders o

on c.custid = o.custid

group by SUBSTRING(c.contactname,1,1)

order by firstletter asc

---------------------------------------------------------------------

-- Task 3

-- 

-- Copy the T-SQL statement in exercise 1, task 4, 

-- and modify to include the following information about for each product category: 

-- total sales amount, number of orders, and average sales amount per order. 

-- Use the aliases totalsalesamount, nooforders, and avgsalesamountperorder, respectively.

---------------------------------------------------------------------

select 

c.categoryid

,c.categoryname

,SUM(od.unitprice*od.qty) as totalsalesamount

,COUNT(distinct od.orderid) as nooforders

,SUM(od.unitprice*od.qty)/COUNT(distinct od.orderid) as avgsalesamountperorder

from Production.Categories c

inner join Production.Products p

on c.categoryid = p.categoryid

inner join Sales.OrderDetails od

on p.productid = od.productid

inner join Sales.Orders o

on o.orderid = od.orderid

where YEAR(o.orderdate) = '2008'

group by c.categoryid,c.categoryname

Exercise 4

---------------------------------------------------------------------

-- Task 1

-- 

-- Write a SELECT statement to retrieve the top 10 customers by total sales amount 

-- that spent more than $10,000 in terms of sales amount. 

-- Display the custid column from the Orders table and a calculated column

-- that contains the total sales amount based on the qty and unitprice columns 

-- from the Sales.OrderDetails table. Use the alias totalsalesamount for the calculated column.

---------------------------------------------------------------------

select top 10 o.custid,SUM(od.unitprice*od.qty) as totalsalesamount

from Sales.Orders o

inner join Sales.OrderDetails od

on o.orderid = od.orderid

group by o.custid

having SUM(od.unitprice*od.qty) > 10000

order by totalsalesamount desc

---------------------------------------------------------------------

-- Task 2

-- 

-- Write a SELECT statement against the Sales.Orders and Sales.OrderDetails tables 

-- and display the empid column and a calculated column representing the total sales amount. 

-- Filter the result to group only the rows with an order year 2008.

---------------------------------------------------------------------

select o.orderid, o.empid, SUM(od.unitprice*od.qty) as totalsalesamount

from Sales.Orders o

inner join Sales.OrderDetails od

on o.orderid = od.orderid

where YEAR(o.orderdate) = 2008

group by o.orderid,o.empid

---------------------------------------------------------------------

-- Task 3

-- 

-- Copy the T-SQL statement in task 2 and modify it to apply an additional filter

-- to retrieve only the rows that have a sales amount higher than $10,000.

--

-- Execute the written statement and compare the results 

-- that you got with the recommended result shown in the file 84 - Lab Exercise 4 - Task 3_1 Result.txt.

-- Apply an additional filter to show only employees with empid equal number 3.

--

-- Execute the written statement and compare the results that you got with the recommended result

-- shown in the file 85 - Lab Exercise 4 - Task 3_2 Result.txt.

--

-- Did you apply the predicate logic in the WHERE or in the HAVING clause? 

-- Which do you think is better? Why?

---------------------------------------------------------------------

--Add having

select o.orderid, o.empid, SUM(od.unitprice*od.qty) as totalsalesamount

from Sales.Orders o

inner join Sales.OrderDetails od

on o.orderid = od.orderid

where YEAR(o.orderdate) = 2008

group by o.orderid,o.empid

having SUM(od.unitprice*od.qty) >= 10000

--Add predicate

select o.orderid, o.empid, SUM(od.unitprice*od.qty) as totalsalesamount

from Sales.Orders o

inner join Sales.OrderDetails od

on o.orderid = od.orderid

where YEAR(o.orderdate) = 2008 and o.empid = 3

group by o.orderid,o.empid

having SUM(od.unitprice*od.qty) >= 10000

--HAVING是給匯總函數條件使用，一般過濾條件還是用WHERE

---------------------------------------------------------------------

-- Task 4

-- 

-- Write a SELECT statement to retrieve all customers who placed more than 25 orders

-- and add information about the date of the last order and the total sales amount. 

-- Display the custid column from the Sales.Orders table and two calculated columns: 

-- lastorderdate based on the orderdate column and totalsalesamount based on 

-- the qty and unitprice columns in the Sales.OrderDetails table.

---------------------------------------------------------------------

select o.custid

,MAX(o.orderdate) as lastorderdate

,SUM(od.unitprice*od.qty) as totalsalesamount

-- ,COUNT(distinct o.orderid)

from Sales.Orders o

inner join Sales.OrderDetails od

on o.orderid = od.orderid

group by o.custid

having COUNT(distinct o.orderid) > 25

--串了OrderDetails後，Orderid會因每筆訂單不同商品數而重複所以需DISTINCT