Pass Your Microsoft Exams Quickly and Easily. Click Here! SQL SERVER 2000 DB DESIGN EXAM - SQL DRILLS These drills are designed to easily and naturally make you memorise the syntax some of the uses of SQL that will be important to you in the exam. Don't copy and paste when you're doing this exercise, even if you are doing multiple similar queries one after the other. That defeats the whole object of the exercise. They all use the pubs database, except where it specifically states another database (i.e. Northwind). 1. JOINS a. Make an inner join to get any authors and publishers that live in the same city and state. Have your query show the author's first and last name and the publisher's name only. b. Make another join similar to the one in (a) but have your query return all the columns from both tables (an equi-join) c. Make a join that shows all the authors, and for those authors who live in the same city as their publishers, show the publisher name as well. (TIP: use a left outer join) d. Make a join that shows all the publishers, and for those publishers who have an author in the same city, show the author as well. (TIP: use a right outer join) e. Make another join similar to the one in (d) but this time exclude the New Moon Books publisher. f. Make a join showing ALL the rows from both the publishers and the authors tables. Where the author and publisher are in the same city, have the rows joined. (TIP: Use a full outer join) g. Write a query that will combine every publisher with every author, this is called a Cartesian product. (TIP: Use a cross join) h. Now write another cross join query but this time make it behave like an inner join by adding a WHERE clause to only show those who are in the same city as their publisher. i. Write a simple self join to find out all the authors who live in the city of Oakland. j. The trouble with the results from (i) is that they show the same authors on both sides as well as combining different authors on each side. So write a similar query but this time make sure that authors only combine with other authors and not with themselves. k. Do a similar query to j. again, but this time also eliminate any rows where the rows are the same except that the order of the authors is reversed. l. Write a query to show all books of type 'business', and their authors first and last names. (TIP: you will need to join the titles, authors and titleauthor tables) 2. GROUP BY a. Write a query to return the total year to date sales (ytd_sales) for each different type of book from the titles table. b. Write a query to show how many books were published in each year. (TIP: use the DATEPART function on the pubdate column) c. Write a query to show the average price and the year-to-date sales, grouped first by type and then by publisher id. d. Now write a similar query to that in (c), but this time exclude any book types whose publisher id is 0877. e. Write a similar query to (c) again, but this time exclude any book types that have had total year to date sales of under $1,000. f. For New Moon Publishers, write a query that will show the average book price, grouped by type. g. You will notice that (f) returned only two lines. Now write a similar query, which will also show the lines with no average price. (TIP: use the ALL keyword) h. Write a query to show the average prices grouped by royalty. i. Write a similar query to the one in (h) but exclude the row which has summed the average prices for those items with 'NULL' in the royalty column. 3. EXISTS a. Write a query to show the names of all the publishers who have published cookery books. b. Write a query to show the names of all the publishers who have NOT published business books. 4. USER DEFINED FUNCTIONS a. Create a function in the Northwind database to return the following data: ShipperID ShipperName OrderID ShippedDate Freight based on two parameters the user provides for the freight column - a maximum freight price and a minimum freight price. (NB This will be a multistatement table-valued function) b. Create a function to calculate sales tax at 17.5%. The user will pass the full price of an item to the function, and the function will return the sales tax amount. (NB This will be a scalar function). c. Using the function you created in (b), write a statement that will create a MyProducts table, including a computed column to show sales tax. d. Add some data to the table you created in (c), without specifying any values for your computed column, then view the data. 5. STORED PROCEDURES a. Write a stored procedure that takes no parameters and returns a list of authors and their titles. Execute the procedure to make sure it works. b. Write a similar stored procedure to the one in (a), but this time have it accept two parameters, @FirstName and @LastName and return the titles for that author. Call your procedure to make sure it works. c. What happens if you call the procedure you created in (b) without passing it any parameters? Try it and see. To overcome this, write a new stored procedure which is very similar to the one you wrote in (b), but this time give the parameters default values, and also enable the user to specify wildcards when calling the procedure. Try calling your new procedure both without parameters and with parameters with wildcards. d. Write a stored procedure that executes against the titles table. It will accept an input parameter for the titles column, and the user should be able to use wildcards. Have the procedure return an output parameter consisting of the sum of the prices of the titles that matched the user's search. e. Write a query to call the stored procedure you wrote in (d), to display the resulting titles and the total cost of these titles. Pass Your Microsoft Exams Quickly and Easily. Click Here!

IT banner exchange