Pass Your Microsoft Exams Quickly and Easily. Click Here!

SQL SERVER 2000 DB DESIGN EXAM - SQL DRILL ANSWERS

1. JOINS

a. select a.au_fname, a.au_lname, p.pub_name,a.city,a.state
from authors as a JOIN publishers as p
ON a.city=p.city and a.state=p.state
order by a.au_fname

b. select * from authors as a JOIN publishers as p
ON a.state=p.state and a.city=p.city
order by a.state

c. select a.au_fname, a.au_lname,p.pub_name
from authors as a LEFT OUTER JOIN publishers as p
ON a.city = p.city and a.state = p.state
order by a.au_fname

d. select p.pub_name,a.au_fname, a.au_lname
from authors as a RIGHT OUTER JOIN publishers as p
ON a.city = p.city
order by p.pub_name

e. select p.pub_name, a.au_fname, a.au_lname
from authors as a RIGHT OUTER JOIN publishers as p
ON a.city = p.city
and pub_name <> 'New Moon Books'
order by pub_name

f. select a.au_fname, a.au_lname, p.pub_name
from authors as a FULL OUTER JOIN publishers as p
ON a.city=p.city
order by a.au_fname

g. select a.au_fname, a.au_lname, p.pub_name
from authors as a CROSS JOIN publishers as p

h. select a.au_fname, a.au_lname, p.pub_name
from authors as a CROSS JOIN publishers as p
where a.city = p.city

i. select a1.au_fname, a1.au_lname, a2.au_fname, a2.au_lname
from authors as a1 JOIN authors as a2
ON a1.zip = a2.zip
and a1.city='Oakland'

j. select a1.au_fname, a1.au_lname, a2.au_fname, a2.au_lname
from authors as a1 JOIN authors as a2
on a1.zip=a2.zip
and a1.city ='Oakland'
and a1.au_fname <> a2.au_fname

k. select a1.au_fname, a1.au_lname, a2.au_fname, a2.au_lname
from authors as a1 JOIN authors as a2
on a1.zip=a2.zip
and a1.city ='Oakland'
and a1.au_fname < a2.au_fname

l. select t.title, a.au_fname, a.au_lname
from titles as t JOIN titleauthor as ta
on t.title_id = ta.title_id
join authors as a
on ta.au_id = a.au_id
and t.type='business'

2. GROUP BY

a. select type, Revenue=sum(ytd_sales)
from titles
group by type

b. select Year=datepart(yy,pubdate), Books=count(*)
from titles
group by datepart(yy,pubdate)

c. select type,pub_id,average=avg(price),revenue=sum(ytd_sales)
from titles
group by type, pub_id

d. select type,pub_id,average=avg(price),revenue=sum(ytd_sales)
from titles
where pub_id <> 0877
group by type,pub_id

e. select type, pub_id, average=avg(price), revenue=sum(ytd_sales)
from titles
group by type, pub_id
having sum(ytd_sales) > 1000

f. select type, average=avg(price)
from titles
where pub_id = 0736
group by type

g. select type, average=avg(price)
from titles
where pub_id = 0736
group by all type

h. select royalty, avg(price) as Average
from titles
group by royalty

i. select royalty, avg(price) as Average
from titles
where royalty is not null

group by royalty

3. EXISTS / NOT EXISTS

a. select pub_name
from publishers
where exists
(select * from titles
where titles.pub_id = publishers.pub_id
and type like '%cook')

b. select pub_name
from publishers
where not exists
(select * from titles
where pub_id = publishers.pub_id
and type <> 'business')

4. USER DEFINED FUNCTIONS

a. TO CREATE THE FUNCTION:

create function ship2 (@FreightIWantBig int, @FreightIWantSmall int)
RETURNS @tab_Pricing TABLE
(shipperid int,
companyname nvarchar(80),
orderid int,
shippeddate datetime,
freight money
)
AS
BEGIN
INSERT @tab_pricing
SELECT s.shipperid, s.companyname, o.orderid, o.shippeddate, o.freight
FROM Shippers AS s JOIN Orders as o ON s.shipperid = o.shipvia
WHERE o.freight < @FreightIWantBig and o.freight > @FreightIWantSmall
RETURN
END

TO CALL THE FUNCTION:
select * from ship2(1000,1)

b. CREATE FUNCTION CalcSalesTax (@Price money)
RETURNS money
AS
BEGIN
RETURN ((@Price / 100) * 17.5)
END

c. create table MyProducts
(
ProductID int PRIMARY KEY,
ProductDescription nchar(20),
ProductPrice money,
SalesTax AS
(dbo.CalcSalesTax(ProductPrice))
)

d. insert MyProducts
(ProductID,ProductDescription,ProductPrice) VALUES (1,'test',200)
select * from myproducts

5. STORED PROCEDURES

a. create procedure myproc1
as
select a.au_fname, a.au_lname, t.title_id
from authors as a join titleauthor as ta
ON a.au_id=ta.au_id join titles as t
ON ta.title_id=t.title_id
myproc1

b. create proc myproc2 (@FirstName nvarchar(20),@LastName nvarchar(40))
as
select a.au_fname, a.au_lname, t.title
from authors as a join titleauthor as ta
on a.au_id = ta.au_id
join titles as t
on ta.title_id = t.title_id
and a.au_fname = @FirstName
and a.au_lname = @LastName
exec myproc2 @FirstName='Dean', @LastName='Straight'

c. create procedure myproc3 (@FirstName nvarchar(20)='%', @LastName nvarchar(40)='%')
as
select a.au_fname,a.au_lname,t.title
from authors as a join titleauthor as ta
on a.au_id=ta.au_id
join titles as t
on ta.title_id=t.title_id
and a.au_fname like @FirstName
and a.au_lname like @LastName
exec myproc3
exec myproc3 'a%','r%'

d. create procedure myproc4 @title varchar(40)='%',@sum money output
as
select title from titles
where title like @title
select sum(price) from titles
where title like @title
declare @total money
exec myproc4 'The%',@total
print cast(@total as varchar(20))

Pass Your Microsoft Exams Quickly and Easily. Click Here!


IT banner exchange