
Teach Yourself Microsoft SQL Server 6.5 in 21 Days

- B -
Answers
- Day 1, "Introduction to SQL Server 6.5 and Relational
Databases"
- Day 2, "Installing SQL Server 6.5"
- Day 3, "SQL Server Tools and Utilities"
- Day 4, "Devices and Databases"
- Day 5, "Creating Tables"
- Day 6, "SQL Server Login and User Security"
- Day 7, "User Permissions"
- Day 8, "Backing Up and Restoring"
- Day 9, "Importing and Exporting Data"
- Day 10, "Data Retrieval"
- Day 11, "Data Retrieval (Advanced Topics)"
- Day 12, "Data Modification"
- Day 13, "Indexing"
- Day 14, "Data Integrity"
- Day 15, "Views, Stored Procedures, and Triggers"
- Day 16, "Programming SQL Server"
- Day 17, "Programming SQL Server (Advanced Topics)"
- Day 18, "SQL Server Automation"
- Day 19, "Data Distribution (Replication)"
- Day 20, "Performance Tuning and Optimization"
- Day 21, "SQL Server and the World Wide Web"
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:
- If the store is in Washington, stor_address should become in-state.
- If the store is in Oregon or California, stor_address should become
Western Region.
- Otherwise, stor_address should become out of area.
-
a. declare store_cursor cursor for
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.

© Copyright, Macmillan Computer Publishing. All
rights reserved.