Day 10

Quiz

1:What are the three different types of temporary tables and how do you create them?
A1: Local temporary tables are created with a single # symbol as the first character in the table name; global temporary tables are created with two # symbols; permanent temporary tables are created by creating the table in the TEMPDB database.
2:If I need to append 10 tables together, how can I do it?
A2: You would create a view with 10 SELECT statements that are joined together using a UNION clause. This will work only if all the tables have the same number and type of columns.
3:Who can access a global temporary table? A permanent temporary table?
A3: Global temporary tables can be accessed by anyone connected to the server. Permanent temporary tables are not accessible until permission to that table is granted. By default, most users get Guest access to TEMPDB, so they can access a permanent temporary table.
4:How can I make changes to a view?
A4: A view can be modified either by using the tools in the Enterprise Manager or by using the ALTER VIEW statement.

Exercises

1:Using the Pubs database, create a global temporary table that includes the author names along with the revenue they have generated.
A1: By using a mixture of columns and aggregates, you can easily create this temporary table. The SQL code required is as follows: 
Select a.au_lname, a.au_Fname,
       Sum(t.price * s.qty) as 'Revenue'
Into ##tp_Revenue
From authors as a
    Inner Join Titleauthor as ta
    On a.au_id = ta.au_id
    Inner Join Sales as s
    On ta.title_id = s.title_id
    Inner Join titles as t
    On s.title_id = t.title_id and
       Ta.title_id = t.title_id
Group by au_lname, au_fname
Order by revenue

2:Create a view that shows sales information for the United States only. This should include the company name, order date, product name, and the total sales for each.
A2: The answer to this exercise is a view that contains four tables joined together: 
Create view vw_sales as
select     c.CompanyName,
    o.OrderDate,
    p.ProductName,
    sum(od.UnitPrice * od.Quantity) as sales
from customers as c
    inner join orders as o
    on o.customerid = c.customerid
    inner join [order details] as od
    on o.orderid = od.orderid
    inner join products as p
    on p.productid = od.productid
where c.country = 'USA'
group by c.companyname, o.orderdate, p.productname

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

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