Chapter 5:
1. Write a SELECT statement that returns two columns from the Invoices table:
VendorID and PaymentSum, where PaymentSum is the sum of the PaymentTotal
column. Group the result set by VendorID.
.
.
.
8. Write a SELECT statement that answers this question: Which vendors are being paid
from more than one account? Return two columns: the vendor name and the total
number of accounts that apply to that vendor’s invoices.
Hint: Use the DISTINCT keyword to count InvoiceLineItems.AccountNo.Chapter 6:
1. Write a SELECT statement that returns the same result set as this SELECT
statement. Substitute a subquery in a WHERE clause for the inner join.
SELECT DISTINCT VendorName
FROM Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
ORDER BY VendorName
.
.
.
8. Write a SELECT statement that returns four columns: VendorName,
InvoiceNumber, InvoiceDate, and InvoiceTotal. Return one row per vendor,
representing the vendor’s invoice with the earliest date.CHAPTER 7:
1. Write SELECT INTO statements to create two test tables named VendorCopy and
InvoiceCopy that are complete copies of the Vendors and Invoices tables. If
VendorCopy and InvoiceCopy already exist, first code two DROP TABLE
statements to delete them.
.
.
.
9. Write a DELETE statement for the VendorCopy table. Delete the vendors that are
located in states from which no vendor has ever sent an invoice.
Hint: Use a subquery coded with “SELECT DISTINCT VendorState” introduced
with the NOT IN operator.
CHAPTER 8:
1. Write a SELECT statement that returns four columns based on the InvoiceTotal
column of the Invoices table:
• Use the CAST function to return the first column as data type decimal with 2
digits to the right of the decimal point.
• Use CAST to return the second column as a varchar.
• Use the CONVERT function to return the third column as the same data type as
the first column.
• Use CONVERT to return the fourth column as a varchar, using style 1.
.
.
.
7. (If you have access to the Examples database) Modify the third SELECT statement
shown in figure 8-11 of the text to return a middle name, if present. Add a third
column, Middle, which is null if no middle name is presentCHAPTER 9:
1. Design a database diagram for a product orders database with four tables. Indicate
the relationships between tables and identify the primary key and foreign keys in
each table. Explain your design decisions.
.
.
.
5. Modify your design for exercise 4 to keep track of the role served by each individual
in each group. Each individual can only serve one role in each group. Each group has
a unique set of roles that members can fulfill. Create additional tables and columns,
if necessary. Explain your design decisions.

CHAPTRR 10:
1. Create a new database named Membership.
.
.
.
6. Delete the GroupMembership table from the Membership database. Then write a
CREATE TABLE statement that recreates the table, this time with a unique
constraint that prevents an individual from being a member in the same group twice.

CHAPTER 11:
1. Using the diagram and grid panes of the Query Designer, query the Vendors table for
vendors in California. The results pane should look like this:
.
.
.
5. a. Use the Enterprise Manager to create a new database called Membership using
the default settings. (If the database already exists, use the Enterprise Manager to
drop it, then recreate it.)

CHAPTER 12:
1. Write a CREATE VIEW statement that defines a view named InvoiceBasic that
returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write
a SELECT statement that returns all of the columns in the view, sorted by
VendorName, where the first letter of the vendor name is N, O, or P.
.
.
.
6. Using the Enterprise Manager, modify the InvoiceBasic view created in exercise 1 to
sort the result set by VendorName. What clause does the system automatically code
to allow the use of an ORDER BY clause in the view?

CHAPTER 13:
1. Write a script that declares and sets a variable that’s equal to the total outstanding
balance due. If that balance due is greater than $10,000.00, the script should return a
result set consisting of VendorName, InvoiceNumber, InvoiceDueDate, and Balance
for each invoice with a balance due, sorted with the oldest due date first. If the total
outstanding balance due is less than $10,000.00, return the message “Balance due is
less than $10,000.00.”
.
.
. 4. Write a script that uses dynamic SQL to return a single column that represents the
number of rows in a particular table in the current database. The script should
automatically choose the user base table that appears first alphabetically. Exclude
system tables, views, and the table named “dtproperties.” Name the column
CountOfTable, where Table is the chosen table name.

CHAPTER 14:
1. Create a stored procedure in the AP database named spWhichTable that accepts a
column name and returns the name of the table or tables that have a column by that
name. Code a statement that calls the procedure.
.
.
. 9. A column that accepts null values but has a unique constraint can only have a single
row with a null value. Write a trigger that prohibits duplicates, except for nulls. Use
the following table. If an INSERT or UPDATE statement creates a duplicate value in
the NoDupName column, roll back the statement and return an error message.
CREATE TABLE TestUniqueNulls
(RowID int IDENTITY NOT NULL,
NoDupName varchar(20) NULL)

.
.
.

CHAPTER 18:
Please confirm from us.