Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Previous chapterContents


- B -

Answers


Day 1, "Introduction to SQL Server 6.5 and Relational Databases"

Quiz Answers

1. What is the building block of a relational database?

a. The table.


2. What are some of the objects held in a database?

a. Tables, columns, datatypes, stored procedures, triggers, rules, keys, constraints, defaults, and indexes.


3. Who has responsibility for backing up SQL Server databases?

a. Most often, the SQL Server administrator.

Exercise Answer

1. Try to design a database on your own. You go through a simulated interview and then get to make some sense out of the interview by creating some variables and organizing them into tables. You can look at the following example. Remember that in this case there isn't just one right answer, but different ways of doing the same thing.

Imagine that your Uncle Joel has had a used car lot for as long as you can remember. You have helped him set up his computers and network, and now he calls you into his office.

Joel: Glad you could come by. My lot has grown so big I'm having a hard time keeping track of everything. Sue almost sold a car we didn't have, and Larry practically gave one away because he wrote down the wrong price. We need to get organized.

You: Have you considered some sort of database?

Joel: You're the computer expert--just design something that I can use to keep track of my cars. I'm also having a hard time keeping track of my salespeople and how much they have sold. It wasn't that hard when I had a small lot, but now it takes too much of my time.

You: Would you want the database to print reports based on monthly activity by salesperson and other such reports?

Joel: That would help a lot.

You: Do you want the database to have pictures with it?

Joel: Can you do that? That would be really neat. I've also been reading about this Internet stuff, and I think it would be great if I could have my cars on it.

You: Just what were you thinking?

Joel: I don't know--you're the computer expert.

You: Do you want people to be able to look at your cars and prices on the Internet?

Joel: Can you do that? That would be neat. Can we show color pictures, too?

You: Yes, we can put pictures and prices and features on the Web page. What exact information do you want to put in the database?

Joel: I would want the year, make, model, color, mileage, features such as air conditioning, four-wheel-drive, CD player, blue-book, and retail price for everyone to see. I'd also want them to see a picture of it, and be able to compare the different cars I have. I'd want additional stuff that only my sales people would see, such as the actual cost and any notes about the car, such as how anxious we are to get rid of it, and when the car came on the lot.

You: That should be enough to start. Do you have a budget in mind?

Joel: Well, I can't blow the whole budget on it, but I have to get something or my salespeople will be losing money on deals if I'm not careful.

You: I'll come up with some ideas and get back to you.

a. What you learned from the interview is that not only is a database that will keep track of cost and sales information desired, but also the database should link to a Web page so that anyone can access public data about the various cars available for sale.


The Day 5 quiz answers include one way to look at creating variables and organizing them into tables. Remember that more details on different variable types are found in Day 5.

Day 2, "Installing SQL Server 6.5"

Quiz Answers

1. What two installation options, which if changed at a later date, would require you to drop your databases, re-create them, and reload data?

a. Character Set and Sort Order.


2. The best place to install SQL Server is on a member server with access to domain accounts through a trust relationship.

a. True. By having access to domain accounts, your SQL Server can participate in Integrated security.


3. What protocols support integrated security on SQL Server?

a. Named Pipes and Multi-Protocol.


4. What three different ways can you start and stop SQL Server?

a. Control Panel/Services, SQL Enterprise Manager, COMMAND prompt, or SQL Service Manager.


5. Where can you find information about SQL Server events and errors?

a. Windows NT Event Log and the SQL Server Error Log. You can also find errors stored in *.OUT files located in the \MSSQL\Log folder.


6. What command-line utilities can you use to test Named Pipes?

a. makepipe and readpipe


7. What type of disk partition is recommended for use by SQL Server?

a. NTFS is recommended for both file security and fault-tolerance.

Exercise Answer

1. Test the Named-Pipes protocol on your local machine and across a network.

a. Open two command windows. In Command window A, enter makepipe. In Command window B, enter readpipe /S<servername> /D"Test Test Test".

Day 3, "SQL Server Tools and Utilities"

Quiz Answers

1. Where is the login security information kept in Enterprise Manager?

a. In the registration information for each server.


2. Where would you set the default Network Library for your client computer?

a. In the client configuration utility.


3. Which utility would you use to monitor Performance Information about SQL Server?

a. In the SQL Performance Monitor, which is actually Windows NT performance monitor.


4. Where would you add TCP/IP sockets support for SQL Server?

a. In the SQL Setup program.


5. Where would you check to determine whether a service pack had been applied to your server?

a. On the Server Configuration/Options screen | Attributes tab of SQL Enter prise Manager.

Day 4, "Devices and Databases"

Quiz Answers

1. List the tables that record information about databases and devices.

a. sysdevices, sysdatabases, sysusages.


2. How is each table used?

a. sysdevices holds information about devices, sizes, and file locations. sysdatabases holds information about the user databases, and sysusages is the table that holds information on which databases are held on which devices.


3. Write Transact-SQL statements to create the following devices:

One to hold an accounting database. It must be large enough to hold 30MB of data.

One to hold an employee information database. It must be large enough to hold 10MB of data.

a. DISK INIT NAME = `Accounting',

	PHYSNAME = `c:\MSSQL\data\accounting.dat',
	VDEVNO = 6,
	SIZE = 15000
a. DISK INIT
	NAME = `Employees',
	PHYSNAME = `c:\MSSQL\data\employee.dat',
	VDEVNO = 6,
	SIZE = 5000
4. Write SQL statements that creates the following databases and their logs:

A database called Accounting that takes 30MB on a device called Accounting_data, with a Transaction Log that is 10MB on a device called Accounting_log.

a. CREATE DATABASE accounting

	ON accounting_data = 30
	log on accounting_log=10
5. Can you create a database across three devices by using SQL Enterprise Manager? If so, how?
a. Yes, but you initially can create only the database on a single device; then you can use Enterprise Manager to expand it to multiple devices.

Day 5, "Creating Tables"

Quiz Answers

1. Which datatype (be sure to add any characteristics such as number of items in the string, or the precision or scale) would you use for the following data?

A. zip code

a. Or a 5 digit code: char[5], if it can hold 10 digits as in 40317-2291, varchar[10].


B. birth date

a. smalldatetime


C. year of car manufacture

a. to store just the year value, tinyint


D. vehicle identification number

a. VIN (assuming up to 20 chars) char[20]


E. store ID where 95 percent of the stores require a 10-digit alphanumeric entry; the other 5 percent vary in size, but none require over 10 digits; the company owner is thinking of requiring all stores to have a 10-digit store number

a. char[10]


F. company name where the name varies in length from 10 to 50 characters

a. varchar[50]


G. a date value in a short term database; dates will range over a period of 10 years

a. smalldatetime


H. money where you need to account for a scale of six

a. timestamp


2. You have created a table using the following create table statement:
	CREATE TABLE table1
	( 
	id char[10],
	fname char[15] NULL,
	lname char[15] NOT NULL,
	comment varchar[255] 
	)
When you look in the table editor you notice that fname appears to have changed from char[15] as you created it, to varchar[15]. What has happened?

a. When a character column allows NULL values, the system changes it to a varchar column.


3. Can the following CREATE TABLE statement be implemented? Why or why not?
	CREATE TABLE phooey
	(id char[10] NOT NULL,
	fname char[15] NULL,
	lname char[15] NOT NULL,
	comment varchar[255],
	notes text,
	directions varchar[255],
	house_picture image)
What is the maximum row size?

a. Yes. Note that the fname field will be changed to a varchar field and the text and image columns will have a 16-byte pointer to the data pages where their data is stored. The maximum row size should be 549 bytes.

Exercise Answers

Instructions: First write down the answers to the following. When you are sure your answers are correct, create these objects in the database.

1. Create the following user-defined datatypes:

zip_code


phone_number


store_id


fax


email

a. sp_addtype zip_code char[5], NOT NULL

	sp_addtype phone_number char[14]
	sp_addtype store_id int, NOT NULL
	sp_addtype fax char[14]
	sp_addtype email varchar[50]
2. Create a table to hold information about stores. Data should include store identification, name, address, city, state, zip, owner, contact, fax, and email.

a. CREATE TABLE stores(

	id store_id,
	name varchar[30],
	addr1 varchar[50],
	addr2 varchar[50],
	city varchar[30],
	state char[2] NOT NULL,
	zip zip_code,
	owner varchar[30],
	contact varchar[30],
	fax fax,
	email email
	)
3. Create a table to hold information about store sales. Data should include store identification, sales date, total sales, total returns, and deposit.

a. CREATE TABLE sales (

	id store_id,
	sales_date datetime,
	tot_sales money,
	tot_returns money,
	deposit money
	)

Day 6, "SQL Server Login and User Security"

Quiz Answers

1. How would I revoke the right of Windows NT administrators to log in to SQL Server as SA?

a. By adding a new Windows NT group with SA rights with the SQL Security Manager, and then revoking the Administrators group.


2. How would I enable auditing of failed logins in SQL Server?

a. By going to the Security Options tab of the Server Configuration/Options dialog of Enterprise Manager and checking the appropriate check box.


3. If you received an error when running sp_addalias for a login, what do you think the likely cause of the problem is?

a. The problem is most likely that the login already has a mapping as a username in the database.

Exercise Answers

1. Create the following logins in SQL Server. Add each login to a user-defined database on your server.

George

Henry

Ida

John

This can be done with either sp_addlogin and sp_adduser, or the Enterprise Manager's Manage Logins dialog box.

2. Make John the DBO of the database you just referenced. Fix any errors you receive to make this change possible.

a. Remove John from the database user, and then run sp_changedbowner to make John the DBO.

Day 7, "User Permissions"

Quiz Answers

1. How would I grant a user, Mary, permissions to read data from the table MyTable?

a. GRANT SELECT ON MyTable TO MARY.


2. Mary created a table called MaryTable and gave Joe SELECT permissions on it. Joe created a view called JoeView. Joe wants Paul to have permissions to SELECT from his view. What requirements must be met?

a. Paul needs permission to SELECT on JoeView and MaryTable.


3. Who can create a device?

a. SA.


4. Joe is granted permission to SELECT on MYTABLE. Public is revoked SELECT on MYTABLE. What are the effective permissions?

a. Joe can SELECT from MYTABLE.


5. What is the preferred way to prevent broken chains of ownership?

a. Have all objects made by DBO (including SA and aliases to DBO).


6. You are the owner of a database. You want all users to be able to query the table MYTABLE. What command would you execute?

a. GRANT SELECT ON MYTABLE TO PUBLIC.


7. You execute the command GRANT ALL TO JOE in a user database. What permissions does Joe have?

a. All statement permissions except CREATE DATABASE.

Day 8, "Backing Up and Restoring"

Quiz Answers

1. If you receive an error when restoring a transaction log backup, what's the first thing you should check?

a. That you specified the fileno parameter to restore the logs in the proper sequence.


2. Which RAID option would be preferred for holding transaction logs?

a. RAID 1 performs better (in general) than RAID 5 for writes, hence is a better option for holding transaction logs.


3. Can you back up to a device whose physical location is \\myserver\sqlbackups \master_backup.dat?

a. Yes, network locations are available for backup devices.


4. In the Database Backup/Restore dialog, the transaction log backup option is unavailable. Can you explain why this might be the case?

a. If the log is not separate from the data, you will see this option grayed out.


5. If you rebuild the master database, what other database must be recovered?

a. The MSDB database will be reinitialized by a rebuild of master.

Day 9, "Importing and Exporting Data"

Quiz Answers

1. Can I transfer data from my SQL Server 6.5 server to a SQL Server 6.0 database?

a. No, you can only transfer data into SQL Server 6.5.


2. Can BCP data be out of a view? Into a view?

a. Yes. Simply type in a view name instead of a table name.


3. What stored procedure can be used to reset login IDs to database user IDs after I restore a database on a new server?

a. Sp_change_users_login


4. Why do I get warnings when I request that logins be transferred with Enterprise Manager's TMI?

a. Because logins are scripted with null passwords.

Day 10, "Data Retrieval"

Quiz Answers

1. What does this query return?
SELECT * FROM authors
WHERE au_lname LIKE `M%'
a. It returns all authors that have a last name that begins with the letter "M."

2. What does this query return?
SELECT emp_id AS EmployeeID,
    lname AS LastName,
    fname AS FirstName
    FROM employee
a. This returns emp_id, lname, and fname columns from the employee table with column names of EmployeeID, LastName, and FirstName.

3. What does this query return?
SELECT ROUND ($7725.53, 1)
a. This rounds off the value to 7725.

4. What does this query return?
SELECT lname + `, ` + SUBSTRING(fname,1,1) + `.' AS Name,
emp_id AS EmployeeID
FROM employee
a. This returns the last name and the first initial of the first name in a column called Name and the emp_id in a field called EmployeeID from the employee table.

Exercises

1. You want to retrieve title_id, title, and price for all books that have a publisher ID of 0877 or computer in the title, and for which the price is not null. What T-SQL would you use? (Hint: Use the titles table.)

a. SELECT title_id, title, price

	FROM titles
	WHERE pub_id = `0877'
	OR title LIKE `%computer%'
(returns 14 rows)

2. Write a query to find all books in the titles table that have price values and are NOT NULL.

a. SELECT * FROM titles

	WHERE price IS NOT NULL
(returns 16 rows)

3. Write a query to list all book titles and prices in the titles table in descending order based on price.

a. SELECT title, price

	FROM titles
	ORDER BY price DESC
(returns 18 rows)

Day 11, "Data Retrieval (Advanced Topics)"

Quiz Answers

1. Can I run a subquery as a join and vice versa?

a. Yes. In most cases, a subquery could be implemented as a join and vice versa.


2. True or false: ROLLUP and CUBE do not supply summary information.

a. False. ROLLUP and CUBE are designed to give you summary information.


3. Can I use a SELECT INTO statement to build a temporary table that everyone has access to?

a. Yes. To create this table, make sure that you prefix the table name with the ## symbol.

Exercise Answers

1. Create a query that returns the average of the ytd_sales figures from the titles table in the pubs database.

a. SELECT AVG(ytd_sales) FROM titles


2. Using the GROUP BY and HAVING clauses, create a query that finds all the books with more than one author. (Hint: Use the titleauthor table.)

a. SELECT title_id, count(title_id)

	FROM titleauthor
	GROUP BY title_id
	HAVING count(title_id) > 1
3. Using the COMPUTE BY clause, create a query that reports the stor_id and a running sum of the quantity of books ordered. Use the sales table to do this.

a. SELECT stor_id, qty

	FROM sales
	ORDER BY stor_id
	COMPUTE SUM(qty) BY stor_id
4. Create a query using joins (either a SQL Server join, or an ANSI join) to show an author's first name, last name, and the title of his books. (Use the au_fname, au_lname from the authors table and the title field from the titles table.)

(Hint: You must do two joins, one from authors to titleauthor and one from titles to titleauthor.)

a. ANSI Syntax

	SELECT authors.au_fname, authors.au_lname, titles.title
	FROM authors
	INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
	INNER JOIN titles ON titleauthor.title_id = titles.title_id
	ORDER BY authors.au_lname
SQL Server Syntax
	SELECT authors.au_fname, authors.au_lname, titles.title
	FROM authors, titles, titleauthor
	WHERE authors.au_id = titleauthor.au_id
	AND  titleauthor.title_id = titles.title_id
	ORDER BY authors.au_lname
5. Create a subquery to find which authors live in the same state as any of the stores.

a. SELECT * FROM authors

	WHERE authors.state IN
	(SELECT state from stores)
6. Create a temporary table containing all the information from the employees table. Test the existence of your new table by selecting data from it.

a. SELECT *

	INTO #tmpEmployees
	FROM employees
	GO
	SELECT * FROM #tmpEmployees
	GO

Day 12, "Data Modification"

Quiz Answers

1. What happens if I execute a DELETE statement without a WHERE clause?

a. A DELETE without a WHERE removes every row from a table. It is more efficient to use the TRUNCATE TABLE command if you really want to remove all rows.


2. True or false: I must supply a value for every column in a row when inserting a new row.

a. False. Identity values are never supplied; they are calculated by the SQL Server. You do not need to supply a value for any column that has a default value or allows NULLs, but you can supply one if desired.


3. What do joins in a DELETE or UPDATE statement allow me to do?

a. Joins in a DELETE or UPDATE statement allow you to access values in another table to determine which rows to modify. The second table is used only as a lookup table and is not affected. Only one table at a time can be changed with any of the data modification operations.

Exercise Answers

The exercises assume that your titles table in the pubs database is in its initial condition. If you have been modifying the table to practice the statements in this module, you have to rebuild the pubs database. To do this, please run the INSTPUB.SQL script in your \MSSQL\ Install folder.

1. Create a temporary table containing the title ID, title, publisher ID, and price of all modern cooking books (type = `mod_cook'). Use this table for the remaining exercises.

a. select title_id, title, pub_id, price

	into #cook_books
	from titles
	where type = `mod_cook'
2. Insert title ID, title, publisher ID, and price from all the traditional cooking books (type = trad_cook) into the temp table.

a. insert into #cook_books

  	select title_id, title, pub_id, price
  	from titles
  	where type = `trad_cook'
3. Increase the price of all books by 20 percent.

a. update #cook_books

	set price = price * 1.2
4. Decrease the price of all books published by Binnet & Hardley by 10 percent.

a. update #cook_books

	set price = price * 0.9
	where pub_id =
   		(select pub_id from publishers
   		 where pub_name = `Binnet & Hardley' )
5. Delete all books with a price less than $10.

a. delete #cook_books

	where price < $10
6. Delete all books with year-to-date sales greater than 10,000.

a. delete #cook_books

	where title_id in
    (select title_id from titles
     where ytd_sales > 10000)

Day 13, "Indexing"

Quiz Answers

1. How do I force SQL Server to choose a table scan?

a. By specifying an optimizer hint with an index number of 0.


2. How much free space do I need to create a clustered index?

a. At least 120 percent of the size of the table with which you're creating the index.


3. What option do I turn on to verify the index selection of SQL Server?

a. SET SHOWPLAN ON.

Day 14, "Data Integrity"

Quiz Answers

1. Is the following a valid default definition?
Create default mydefault as `UNKNOWN'
a. Yes.

2. Can you drop a rule while it's still bound to a column, even if there's no data in the table?

a. No; it must be referenced elsewhere.


3. Is a numeric datatype allowed for Identity columns?

a. Yes, as long as the scale is 0.


4. Can a foreign key refer to a table in a different database?

a. No; foreign keys can refer only to tables in the same database.


5. Can a unique constraint be deferred or disabled?

a. No; it creates a unique index that cannot be disabled.


6. Can you drop the indexes created by some constraints?

a. Not directly; you must manage them by controlling the constraints.

Day 15, "Views, Stored Procedures, and Triggers"

Quiz Answers

1. What do triggers enforce?

a. Data integrity, referential integrity, and business rules.


2. You can have _____ number of triggers per table.

a. Three, one for INSERT, UPDATE, and DELETE.


3. True or false: Views can focus on only the data needed, provide security, allow modifications to base tables, and are faster than stored procedures.

a. False. Views are not faster than stored procedures. However, all of the other criteria is true of views.


4. True or false: You can update multiple base tables with a view

a. False. You can only update a single base table with a view.


5. True or false: You can use a stored procedure that returns information from base tables that you do not have permission on.

a. True. This is one of the benefits of creating a stored procedure.


6. True or false: You can use a view that returns information from base tables that you do not have permission on.

a. False. You must have permission on the base tables to run a view. It is interesting, however, that you can create a view on base tables that you do not have access to.


7. True or false: Declared Referential Integrity (DRI), constraints, datatypes, defaults, and rules have eliminated the need for triggers.

a. False. While these things have made most triggers unnecessary, there are still many valid uses for triggers: for example, cascading updates, and deletes.


8. When you drop a table, which of the following database objects are also dropped: Views, stored procedures, triggers.

a. Only triggers are dropped.

Exercise Answers

1. Create a view that shows which authors have written which books.

a. Create view authors_books

	As
	Select a.au_lname, a.au_fname, t.title
	From authors a inner join titleauthor ta
 	 On a.au_id = ta.au_id
 	    Inner join titles t
  	       On ta.title_id = t.title_id
2. Create a trigger that will prevent you from deleting an author if she is associated with a book.

a. Create trigger delauthor

	On authors
	For delete
	As
	If (select count(*) from deleted d, titleauthor ta where d.au_id =
	Âta.au_id) > 0 
	   BEGIN
	     ROLLBACK TRANSACTION
	     RAISERROR ("This author still has books assigned",10,1)
	   END
3. Create a stored procedure that will show which books are selling in which stores. Accept one parameter, the title of the book, and assume all books if no parameter is passed. (Hint: You need to join three tables to accomplish this.)

a. Create proc booksales (@titleid char(6) = NULL)

	As
	If @titleid is null 
  	   Select distinct t.title, st.stor_name
  	   From titles t inner join sales s
   	      On t.title_id = s.title_id
   	      Inner Join stores st
     	       On s.stor_id = st.stor_id
	Else	
  	  Select distinct t.title, st.stor_name
 	   From titles t inner join sales s
 	      On t.title_id = s.title_id
	       Inner Join stores st
 	         On s.stor_id = st.stor_id
	    Where t.title_id = @titleid
	RETURN

Day 16, "Programming SQL Server"

Quiz Answers

1. What CREATE statements are allowed within a single batch?

a. CREATE DATABASE, CREATE TABLE, and CREATE INDEX.


2. What locks will be held at the time of the COMMIT TRAN from the following batch?
BEGIN TRAN
    UPDATE authors SET au_lname = `Johnson' WHERE au_lname = `Smith'
    INSERT publishers VALUES (`9991','SAMS','Indianapolis','IN','USA')
    SELECT * FROM publishers (HOLDLOCK)
COMMIT TRAN
a. The UPDATE statement will take an exclusive page lock on the authors table. The INSERT statement will take a page lock on the publishers table. Lastly, the SELECT statement will take a shared table lock on the publisher table. Following are the results of sp_lock.
spid   locktype          table_id         page        dbname
----   --------          --------         ----        ------
12     Ex_intent         16003088    	     0           pubs
12     Ex_page           16003088    	     392         pubs
12     Ex_page           16003088    	     552         pubs
12     Update_page       16003088    	     392         pubs
12     Update_page       16003088    	     552         pubs
12     Ex_intent         112003430   	     0           pubs
12     Ex_page           112003430   	     472         pubs
12     Sh_table          112003430   	     0           pubs
13     Sh_intent         704005539   	     0           master
13     Ex_extent         0           		     336         tempdb

(1 row(s) affected)
3. How would I enable remote-stored procedures to automatically be part of a distributed transaction at all times?

a. By setting the REMOTE_PROC_TRANSACTIONS configuration item. You can set it either with the stored procedure sp_configure, or the configuration screen in SQL Enterprise Manager.

Exercise Answers

1. You have added a new book to your inventory (How to Surf the Net in 3 Easy Steps). Not only is the title new, but so is the publisher (waycool publishers) and the two authors (Ann Jackson and Bob Greene). Write a script to add all of this information so that it all completes or fails together.

a. BEGIN TRAN

	INSERT publishers VALUES (`9993','WAYCOOL PUBLISHERS', 'Indianapolis',
 	     'IN', 'USA')
	INSERT authors VALUES (`111-11-1111','Jackson','Ann','425 999-9000',
  	   'PO Box 1193','Snoqualmie','WA', `98065', 1)
	INSERT authors VALUES (`111-22-1111','Greene','Bob','425 999-9000',
 	    '1204 Sycamore Lane','Boulder City','NV', `89005', 1)
	INSERT TITLES VALUES (`BU1403','How to Surf the Net in 3 Easy Steps',
 	     `business', `9993', $19.95, $3000.00, NULL, NULL, NULL)
	INSERT TITLEAUTHOR VALUES (`111-11-1111','BU1403', 1, 50)
	INSERT TITLEAUTHOR VALUES (`111-22-1111','BU1403', 1, 50)
	COMMIT TRAN
This adds a new publisher, the two authors, the new book title, and then adds the authors as having written the book (50/50 cut). Note that your answer may vary, but should include the same set of tables within a single transaction.

Day 17, "Programming SQL Server (Advanced Topics)"

Quiz Answers

1. True or false: I would use a WHILE statement to update every row in a table, for example, to double the price of every book.

a. False. The UPDATE statement with no WHERE clause will update every row in a table.


2. How do I assign a value to a local variable?

a. A local variable is assigned a value by using a SELECT statement.


3. What is the scope of a local variable?

a. The scope of a local variable is either a single-batch or a single-stored procedure.


4. How do I assign a value to a global variable?

a. Global variables cannot be assigned values. SQL Server sets their values based on the activity that has occurred.


5. What kinds of modifications can you do through a cursor?

a. You can UPDATE and DELETE rows through a cursor.

Exercise Answers

The exercises use the stores table located in the pubs database.

1. Declare a read-only cursor for the following SELECT statement.
SELECT stor_name, stor_address, city, state
FROM stores
a. declare store_cursor cursor for
	SELECT stor_name,  stor_address, city, state
	FROM stores
	for read only
2. Print the address of each store located in Washington state in the following format.
stor_name
stor_address
city, state
Print the address of each store not located in Washington state in the following format.
stor_name
out of state
a. declare @stor_name varchar(40), @stor_address varchar(40),
  	      @city varchar(20), @state char(2),
  	 @city_state varchar(23)
	open store_cursor
	fetch store_cursor into @stor_name, @stor_address, @city, @state
	while (@@fetch_status = 0)
	BEGIN
	   IF @state = `WA' BEGIN
 	    SELECT @city_state = @city + " " + @state
 	     PRINT @stor_name
 	     PRINT @stor_address
 	     PRINT @city_state
	   END
	   ELSE BEGIN
	      PRINT @stor_name
	      PRINT `out of state'
	   END
	 fetch store_cursor into @stor_name, @stor_address, @city, @state
	END
	close store_cursor
	deallocate store_cursor
3. Make a copy of your stores table by using SELECT INTO, and build a unique index on the stor_id column of the new table.

a. SELECT * into new_stores FROM stores

	create unique index idx1 on new_stores (stor_id)
4. Declare an updatable cursor for the following SELECT statement.
SELECT stor_name,  stor_address, city, state
FROM new_stores
a. declare store_cursor cursor for
	SELECT stor_name,  stor_address, city, state 	FROM new_stores
	for update of stor_address, city, state
5. Using the cursor you just defined, update the city and state of each row to be null. Update the stor_address column according to the following criteria:
	SELECT stor_name,  stor_address, city, state 	FROM new_stores
	for update of stor_address, city, state
	declare @stor_name varchar(40), @stor_address varchar(40),
	        @city varchar(20), @state char(2) 
	open store_cursor
	fetch store_cursor into @stor_name, @stor_address, @city, @state
	while (@@fetch_status = 0)
	BEGIN
	   IF @state = `WA'
	     update new_stores
	     SET city = null, state = null,
	     stor_address = `in-state'
	     WHERE current of store_cursor
	   ELSE IF @state in (`OR', `CA')
	     update new_stores
	     SET city = null, state = null,
	     stor_address = `Western Region'
	     WHERE current of store_cursor
	   ELSE
	     update new_stores
	     SET city = null, state = null,
	     stor_address = `out of area'
	     WHERE current of store_cursor
	
	 fetch store_cursor into @stor_name, @stor_address, @city, @state
	END
	close store_cursor
	deallocate store_cursor

Day 18, "SQL Server Automation"

Quiz Answers

1. What is the built-in alternative to the SQLExecutive service to schedule tasks?

a. The AT command and the Windows NT Schedule service.


2. What profile is SQL Server referring to when it requests a mail profile name?

a. The name of the mail profile of the Windows NT user account that is being used to run the MSSQLServer service.


3. Which command would be used to receive a query via email and send the reply back to a user as a text file attachment?

a. Exec sp_processmail.


4. How many scheduled tasks would you create if you want your Transaction Log to be backed up every three hours daily? The first backup should be with init (at 5 a.m.), and the rest of the log backups should be with noinit.

a. Two scheduled tasks (One for the with init command, one without that runs every three hours).

Day 19, "Data Distribution (Replication)"

Quiz Answers

1. Which two types of replication servers need to be the same type?

a. Publication and distribution servers.


2. How much memory does the distribution server need assigned to it?

a. At least 16MB.


3. What type of partitioning allows you to specify only certain rows for replication?

a. Horizontal partitioning.

Day 20, "Performance Tuning and Optimization"

Quiz Answers

1. In terms of overall performance impact, which component of the system deserves the most attention for performance tuning?

a. The physical implementation of the logical database design.


2. Why should the transaction logs be kept on different disks than the Windows NT paging file?

a. So that the disk activity of the log can have the highest priority and speed up overall throughput of SQL Server.


3. If you were concerned that you had over-allocated memory for SQL Server, what Windows NT counter would you monitor?

a. You would monitor the MEMORY object, pages/sec counter to look for a high level of swapping from memory to the paging file.


4. What parameter would you monitor to view Windows NT paging?

a. Memory--Pages/Second in performance monitor.


5. Which hardware solution provides the best performance but doesn't necessarily provide fault tolerance?

a. RAID 0 (striping without parity).

Day 21, "SQL Server and the World Wide Web"

Quiz Answers

1. Which Internet utility is appropriate for use when building static Web pages?

a. The SQL Server Web Assistant.


2. Which technology would you use if you want to build a simple query interface to SQL Server on the Internet?

a. The .IDC/.HTX access mechanisms.


3. Which technology would provide the most functionality for advanced query access and data maintenance?

a. Microsoft ActiveX Data Objects (ADO).


4. If you receive a query syntax error, which file would you examine to find the error between these three: authors.htm, authors.idc, authors.htx?

a. The .IDC file contains the SQL statement.


Previous chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.