TSQL Lab 05 習題

LAB 05

Exercise 1
---------------------------------------------------------------------
Task 1

Write a SELECT statement that will return the custid, companyname, contactname, address, city, country, and phone columns from the Sales.Customers table. 
Filter the results to include only the customers from the country Brazil.

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

select custid, companyname, contactname, address, city, country, phone
from Sales.Customers

where country = 'Brazil'

---------------------------------------------------------------------
Task 2

Write a SELECT statement that will return the custid, companyname, contactname, address, city, country, and phone columns from the Sales.Customers table. 
Filter the results to include only customers from the countries Brazil, UK, and USA.
---------------------------------------------------------------------

select custid, companyname, contactname, address, city, country, phone
from Sales.Customers
where country in ('Brazil','UK','USA')

order by country asc

---------------------------------------------------------------------
Task 3

Write a SELECT statement that will return the custid, companyname, contactname, address, city, country, and phone columns from the Sales.Customers table. 
Filter the results to include only the customers with a contact name starting with the letter A.
---------------------------------------------------------------------

select custid, companyname, contactname, address, city, country, phone
from Sales.Customers

where contactname like 'A%'

---------------------------------------------------------------------
Task 4a

The IT department has written a T-SQL statement that retrieves the custid and companyname columns from the Sales.Customers table and the orderid column from the Sales.Orders table.

Execute the query. Notice two things: 
First, the query retrieves all the rows from the Sales.Customers table. 
Second, there is a comparison operator in the ON clause specifying that the city column should be equal to the value aris?
---------------------------------------------------------------------

SELECT
c.custid, c.companyname, o.orderid
FROM Sales.Customers AS c
LEFT OUTER JOIN Sales.Orders AS o ON c.custid = o.custid AND c.city = N'Paris';

---------------------------------------------------------------------
Task 4b

Copy the provided T-SQL statement and modify it to have a comparison operator for the city column in the WHERE clause. Execute the query. 

Is the result the same as in the first T-SQL statement? Why? What is the difference between specifying the predicate in the ON clause and in the WHERE clause?
---------------------------------------------------------------------

SELECT
c.custid, c.companyname, o.orderid , c.city
FROM Sales.Customers AS c
LEFT OUTER JOIN Sales.Orders AS o ON c.custid = o.custid

where c.city = N'Paris';

---------------------------------------------------------------------
Task 5

Write a T-SQL statement to retrieve customers from the Sales.Customers table that do not have matching orders in the Sales.Orders table. 
Matching customers with orders is based on a comparison between the customer custid value and the order custid value. 
Retrieve the custid and companyname columns from the Sales.Customers table. 
(Hint: Use a T-SQL statement that is similar to the one in the previous task.)
---------------------------------------------------------------------

SELECT
c.custid, c.companyname, o.orderid
FROM Sales.Customers AS c
LEFT OUTER JOIN Sales.Orders AS o ON c.custid = o.custid

where o.orderid is null

22, 57為沒有訂單的客戶

Exercise 2
--------------------------------------------------------------------
Task 1

Write a SELECT statement to retrieve the custid and contactname columns from the Sales.Customers table and the orderid and orderdate columns from the Sales.Orders table. 
Filter the results to include only orders placed on or after April, 1 2008 (filter the orderdate column). 

Then sort the result by orderdate in descending order and custid in ascending order.
---------------------------------------------------------------------

select c.custid,c.contactname ,o.orderid ,o.orderdate
from Sales.Customers c
inner join Sales.Orders o
on c.custid = o.custid
where orderdate >= '2008-04-01'
order by o.orderdate desc , c.custid asc

---------------------------------------------------------------------
Task 2

Execute the query exactly as written inside a query window and observe the result.

You get an error. What is the error message? Why do you think you got this error? (Tip: Remember the logical processing order of the query.)

Apply the needed changes to the SELECT statement so that it will run without an error. Test the changes by executing the T-SQL statement.
---------------------------------------------------------------------

SELECT
e.empid, e.lastname, e.firstname, e.title, e.mgrid,
m.lastname AS mgrlastname, m.firstname AS mgrfirstname
FROM HR.Employees AS e
INNER JOIN HR.Employees AS m ON e.mgrid = m.empid
WHERE
mgrlastname = N'Buck';

因為where子句不能使用別名
修正如下

SELECT
e.empid, e.lastname, e.firstname, e.title, e.mgrid,
m.lastname AS mgrlastname, m.firstname AS mgrfirstname
FROM HR.Employees AS e
INNER JOIN HR.Employees AS m ON e.mgrid = m.empid
WHERE

m.lastname  = N'Buck';

---------------------------------------------------------------------
Task 3a

Copy the existing T-SQL statement from task 2 and modify it so that the result will return all employees and be ordered by the manager first name. 
Try first to use the source column name
---------------------------------------------------------------------

SELECT
e.empid, e.lastname, e.firstname, e.title, e.mgrid,
m.lastname AS mgrlastname, m.firstname AS mgrfirstname
FROM HR.Employees AS e
LEFT JOIN HR.Employees AS m
ON e.mgrid = m.empid

order by m.firstname asc

---------------------------------------------------------------------
Task 3b

Now try to use the alias column name.

Why were you equally able to use a source column name or an alias column name? 

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

SELECT
e.empid, e.lastname, e.firstname, e.title, e.mgrid,
m.lastname AS mgrlastname, m.firstname AS mgrfirstname
FROM HR.Employees AS e
LEFT JOIN HR.Employees AS m
ON e.mgrid = m.empid

order by mgrfirstname asc

ORDER子句可以使用別名


Exercise 3
--------------------------------------------------------------------
Task 1

Write a SELECT statement against the Sales.Orders table and retrieve the orderid and orderdate columns. 
Retrieve the 20 most recent orders, ordered by orderdate.
---------------------------------------------------------------------

select top 20 orderid,orderdate
from Sales.Orders

order by orderdate desc

---------------------------------------------------------------------
Task 2

Write a SELECT statement to retrieve the same result as in task 1, but use the OFFSET-FETCH clause.
---------------------------------------------------------------------

select orderid,orderdate
from Sales.Orders
order by orderdate desc

offset 0 rows fetch next 20 rows only ;

---------------------------------------------------------------------
Task 3

Write a SELECT statement to retrieve the productname and unitprice columns from the Production.Products table.

Execute the T-SQL statement and notice the number of the rows returned.

Modify the SELECT statement to include only the top 10 percent of products based on unitprice ordering.

Is it possible to implement this task with the OFFSET-FETCH clause?

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

select productname,unitprice

from Production.Products

總共77筆

select top(10) percent productname,unitprice

from Production.Products

order by unitprice desc

select productname,unitprice

from Production.Products

order by unitprice desc

offset 0 rows fetch next 8 rows only ;

Exercise 4
---------------------------------------------------------------------
Task 1

Write a SELECT statement to retrieve the custid, orderid, and orderdate columns from the Sales.Orders table. Order the rows by orderdate and orderid. Retrieve the first 20 rows.
---------------------------------------------------------------------

select top 20 custid,orderid,orderdate
from sales.Orders

order by orderdate,orderid

---------------------------------------------------------------------
Task 2

Copy the SELECT statement in task 1 and modify the OFFSET-FETCH clause to skip the first 20 rows and fetch the next 20 rows.
---------------------------------------------------------------------

select custid,orderid,orderdate
from sales.Orders
order by orderdate,orderid

offset 20 rows fetch next 20 rows only ;

---------------------------------------------------------------------
Task 3

You are given the parameters @pagenum for requested page number and @pagesize for requested page size. Can you work out how to write a generic form of the OFFSET-FETCH clause using those parameters? (Do not worry about not being familiar with those parameters yet.)
---------------------------------------------------------------------

declare @pagenum int = 5

declare @pagesize int = 20

select custid,orderid,orderdate

from sales.Orders

order by orderdate,orderid

offset (@pagenum-1)*@pagesize rows fetch next @pagesize rows only ;