Day 5

Quiz

1:For each of the following items, identify the database name, owner, table name, and column name (if available). Where the value will default, indicate that.

  1. CompanyName

  2. Customers.CompanyName

  3. Northwind.dbo.Products.QuantityPerUnit

  4. Northwind..Products.UnitPrice

  5. dbo.Suppliers.City

  6. Mary.Suppliers.City

  7. Suppliers

  8. dbo.Suppliers

A1: Answer:

  1. CompanyName—Column name in a table in query. Unqualified, so cannot determine other information.

  2. Customers.CompanyName—Column name in Customers table. Current database is assumed. Table is owned by current user or dbo.

  3. Northwind.dbo.Products.QuantityPerUnit—Fully qualified column name of QuantityPerUnit column in Products table owned by dbo in the Northwind database.

  4. Northwind..Products.UnitPrice—Same as (c), except that owner is allowed to default. Table owned either by current user or database owner.

  5. dbo.Suppliers.City—City column in Suppliers table owned by dbo in current database.

  6. Mary.Suppliers.City—City column in Suppliers table owned by Mary in current database.

  7. Suppliers—Unqualified table name, owned by current user or dbo in current database.

  8. dbo.Suppliers—Suppliers table owned by dbo in current database.

2:How many join conditions are required to perform a three-table join?
A2: Two join conditions are required.
3:What is a cross join?
A3: A join whose result set includes every combination of rows from the tables.
4:How do you decide whether an outer join should be a right or left outer join?
A4: The "side" of the join depends on the syntactic table order. Use a left outer join when you want to include rows from the first table in the list. Use right outer join when you want to include rows from the second table in the list.
5:How many ORDER BY clauses can appear in a query containing the UNION keyword?
A5: Only one ORDER BY clause may appear at the end of the query.

Exercises

1:Display the names and hire dates of five sales representatives. 
select top 5
        e.LastName,
        e.FirstName,
        e.HireDate
from
        Employees
 ewhere
        e.Title = 'Sales Representative'

A1: Results:
LastName             FirstName  HireDate
-------------------- ---------- ---------------------------
Davolio              Nancy      1992-05-01 00:00:00.000
Leverling            Janet      1992-04-01 00:00:00.000
Peacock              Margaret   1993-05-03 00:00:00.000
Suyama               Michael    1993-10-17 00:00:00.000
King                 Robert     1994-01-02 00:00:00.000

2:Modify the query in exercise 1 to include a list of order numbers for each employee. Display the first five rows. 
select top 5
        e.LastName,
        e.FirstName,
        e.HireDate,
        o.OrderID
from
        Employees e
    inner join
        Orders o
            on e.EmployeeID = o.EmployeeID
where
        e.Title = 'Sales Representative'

A2: Results:
LastName             FirstName  HireDate   OrderID
-------------------- ---------- ---------- -----------
Davolio              Nancy      1992-05-01       10258
Davolio              Nancy      1992-05-01       10270
Davolio              Nancy      1992-05-01       10275
Davolio              Nancy      1992-05-01       10285
Davolio              Nancy      1992-05-01       10292

3:Modify the last query to include the product IDs and the total dollar value of each sale item. Display five total rows. 
select top 5
        e.LastName,
        e.FirstName,
        e.HireDate,
        o.OrderID,
        od.ProductID,
        od.UnitPrice * od.Quantity * (1 - od.Discount) 'Dollars'
from
        Employees e
    inner join
        Orders o
            on e.EmployeeID = o.EmployeeID
    inner join
        [Order Details] od
            on od.OrderID = o.OrderID
where
        e.Title = 'Sales Representative'

A3: Results:
LastName    FirstName  HireDate   OrderID ProductID Dollars
----------- ---------- ---------- ------- --------- -------
Davolio     Nancy      1992-05-01   10258         2   608.0
Davolio     Nancy      1992-05-01   10258         5   884.0
Davolio     Nancy      1992-05-01   10258        32  122.88
Davolio     Nancy      1992-05-01   10270        36   456.0
Davolio     Nancy      1992-05-01   10270        43   920.0

4:Group the last result by employee and show the five employees with the worst sales overall. 
select top 5
        e.LastName,
        e.FirstName,
        e.HireDate,
        sum(od.UnitPrice * od.Quantity * (1 - od.Discount)) 'Dollars'
from
        Employees e
    inner join
        Orders o
            on e.EmployeeID = o.EmployeeID
    inner join
        [Order Details] od
            on od.OrderID = o.OrderID
where
        e.Title = 'Sales Representative'
group by
        e.LastName,
        e.FirstName,
        e.HireDate
order by
        'Dollars'asc

A4: Results:
LastName             FirstName  HireDate   Dollars
-------------------- ---------- ---------- ----------------
Suyama               Michael    1993-10-17         73913.12
Dodsworth            Anne       1994-11-15         77308.06
King                 Robert     1994-01-02        124568.23
Davolio              Nancy      1992-05-01        192107.60
Leverling            Janet      1992-04-01        202812.84

5:Challenge: Modify the previous query to display five employees with the worst average yearly performance. Hint: Use the employee hire date to determine the number of years the employee has worked for the firm. 
select top 5
        e.LastName,
        e.FirstName,
        e.HireDate,
        datediff(yy, e.Hiredate, getdate()) 'Years of service',
        sum(od.UnitPrice * od.Quantity * (1 - od.Discount)) 'Dollars',
        sum(od.UnitPrice * od.Quantity * (1 - od.Discount))
                   / datediff(yy, e.Hiredate, getdate())
                   'Average Sales Per Year'
from
        Employees e
    inner join
        Orders o
            on e.EmployeeID = o.EmployeeID
    inner join
        [Order Details] od
            on od.OrderID = o.OrderID
where
        e.Title = 'Sales Representative'
group by
        e.LastName,
        e.FirstName,
        e.HireDate
order by
        'Average Sales Per Year'ascLastName     FirstName  HireDate
Years Average Sales

A5: Results:
------------ ---------- ---------- ----- ---------------
Suyama       Michael    1993-10-17     7        10559.01
Dodsworth    Anne       1994-11-15     6        12884.67
King         Robert     1994-01-02     6        20761.37
Davolio      Nancy      1992-05-01     8        24013.45
Leverling    Janet      1992-04-01     8        25351.60

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset