TSQL Lab 04 習題

Lab 04 

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

Write a SELECT statement that will return the productname column from the Production.Products table (use table alias ? and the categoryname column from the Production.Categories table (use table alias ? using an inner join. 

Which column did you specify as a predicate in the ON clause of the join? Why?

Let us say that there is a new row in the Production.Categories table and this new product category does not have any products associated with it in the Production.Products table. Would this row be included in the result of the SELECT statement written in task 1? Please explain.
---------------------------------------------------------------------

select p.productname,c.categoryname
from Production.Products p
inner join Production.Categories c

on p.categoryid=c.categoryid

不會 因為Production.Products沒有相關FK



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

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? 
---------------------------------------------------------------------

並無指定custid 來自哪個Table

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

Notice that there are full source table names written as table aliases. 

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 

customers.custid, contactname, orderid

FROM Sales.Customers  

INNER JOIN Sales.Orders ON Customers.custid = Orders.custid;

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

Task 3

 

Copy the T-SQL statement from task 2 and modify it to use the table aliases ?for the Sales.Custumers table and ?for the Sales.Orders table.

Change the prefix of the columns in the SELECT statement with full source table names and execute the statement.

You get an error. Why?

Change the SELECT statement to use the table aliases written at the beginning of the task.

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

SELECT 

c.custid, c.contactname, o.orderid

FROM Sales.Customers  c

INNER JOIN Sales.Orders o ON c.custid = o.custid;

取了別名就要用別名 + 欄位

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

Task 4

Copy the T-SQL statement from task 3 and modify it to include three additional columns from the Sales.OrderDetails table: productid, qty, and unitprice.

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

SELECT 

c.custid, c.contactname, o.orderid,od.productid,od.qty,od.unitprice

FROM Sales.Customers  c

INNER JOIN Sales.Orders o 

ON c.custid = o.custid

inner join Sales.OrderDetails od

ON o.orderid = od.orderid

Exercise 03

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

Task 1

 

In order to better understand the needed tasks, you will first write a SELECT statement against the HR.Employees table showing the empid, lastname, firstname, title, and mgrid columns.

Notice the values in the mgrid column. The mgrid column is in a relationship with empid column. This is called a self-referencing relationship. 

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

select empid,lastname,firstname,title,mgrid

from HR.Employees

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

Task 2

 

Copy the SELECT statement from task 1 and modify it to include additional columns for the manager information (lastname, firstname) using a self-join. Assign the aliases mgrlastname and mgrfirstname, respectively, to distinguish the manager names from the employee names.

Is it mandatory to use table aliases when writing a statement with a self-join? Can you use a full source table name as alias? Please explain.

Why did you get fewer rows in the T-SQL statement under task 2 compared to task 1?

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

select e.empid,e.lastname,e.firstname,e.title,e.mgrid,m.firstname as mgrfirstname , m.lastname as mgrlastname
from HR.Employees e
inner join hr.Employees m
on e.mgrid = m.empid
order by e.empid asc

自我連結不能用full table name 名字相同會無法區分
因為最大的主管Davis Sara沒有更上層的主管了，所以會少一筆資料

如果要show出所有員工，要用left join

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

Write a SELECT statement to retrieve the custid and contactname columns from the Sales.Customers table and the orderid column from the Sales.Orders table. The statement should retrieve all rows from the Sales.Customers table.

Notice the values in the column orderid. Are there any missing values (marked as NULL)? Why? 
---------------------------------------------------------------------

select c.custid , c.contactname , o.orderid

from Sales.Customers c

left outer join Sales.Orders o

on c.custid = o.custid

因為有客戶沒有下過訂單，所以會為NULL

此例 cust_id = 22 or 57 會為NULL

Exercise 05

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

Task 1

 

Execute the T-SQL code under Task 1. Do not worry if you do not understand the provided T-SQL code, as it is used here to provide a more realistic example for a cross join in the next task. 

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

SET NOCOUNT ON;

IF OBJECT_ID('HR.Calendar') IS NOT NULL 

DROP TABLE HR.Calendar;

CREATE TABLE HR.Calendar (

calendardate DATE CONSTRAINT PK_Calendar PRIMARY KEY

);

DECLARE 

@startdate DATE = DATEFROMPARTS(YEAR(SYSDATETIME()), 1, 1),

@enddate DATE = DATEFROMPARTS(YEAR(SYSDATETIME()), 12, 31);

WHILE @startdate <= @enddate

BEGIN

INSERT INTO HR.Calendar (calendardate)

VALUES (@startdate);

SET @startdate = DATEADD(DAY, 1, @startdate);

END;

SET NOCOUNT OFF;

GO

-- observe the HR.Calendar table

SELECT 

calendardate

FROM HR.Calendar;

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

Task 2

 

Write a SELECT statement to retrieve the empid, firstname, and lastname columns from the HR.Employees table and the calendardate column from the HR.Calendar table.

What is the number of rows returned by the query? There are nine rows in the HR.Employees table. Try to calculate the total number of rows in the HR.Calendar table.

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

select e.empid,e.firstname,e.lastname

from hr.Employees e

cross join hr.Calendar

回傳3285 ( 9位員工 x 365天)

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

Task 3

Execute the provided T-SQL statement to remove the HR.Calendar table.

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

IF OBJECT_ID('HR.Calendar') IS NOT NULL 

DROP TABLE HR.Calendar;