Murach’s SQL for SQL Server – Exercise 10 – Guaranteed 100% Score
1. Create a new database named Membership.
2. Write the CREATE TABLE statements needed to implement the following design in
the Membership database. Include reference constraints. Define IndividualID and
GroupID with the IDENTITY keyword. Decide which columns should allow null
values, if any, and explain your decision. Define the Dues column with a default of
zero and a check constraint to allow only positive values.
GroupID, int
GroupName, varchar
Dues, money
Groups
GroupID, int
IndividualID, int
GroupMembership
IndividualID, int
FirstName, varchar
LastName, varchar
Address, varchar
Phone, varchar
Individuals
3. Write the CREATE INDEX statements to create a clustered index on the GroupID
column and a nonclustered index on the IndividualID column of the
GroupMembership table.
STATUS
4. Write an ALTER TABLE statement that adds a new column, DuesPaid, to the
Individuals table. Use the bit data type, disallow null values, and assign a default
Boolean value of False.
5. Write an ALTER TABLE statement that adds two new check constraints to the
Invoices table of the AP database. The first should allow (1) PaymentDate to be null
only if PaymentTotal is zero and (2) PaymentDate to be not null only if
PaymentTotal is greater than zero. The second constraint should prevent the sum of
PaymentTotal and CreditTotal from being greater than InvoiceTotal.
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.
