3. add two rows to the members table for the first two member IDs.

add two rows to the groups table for the first two group IDs.

add three rows to the group_membership table: one row for member 1 and group 2; one for member 2 and group 1; and one for member 2 and group 2

Transcribed Image Text:

Exercises 1. Write a script that adds an index to the AP database for the zip code field in the Vendors table. Chapter 11 How to create databases, tables, and indexes 2. Write a script that contains the CREATE TABLE statements needed to imple- ment the following design in the EX database: members member id first name last name Address oily. state phone members groups member id group d groups group_id group rambe These tables provide for members of an association, and each member can be registered in one or more groups within the association. The member_id and group_id columns are the primary keys of the Members and Groups tables, and these columns are foreign keys in the Members Groups table. Include any constraints or default values that you think are necessary. Include statements to drop the tables if they already exist. 3. Write INSERT statements that add rows to the tables that are created in exercise 2. Add two rows to the Members table for the first two member IDs. Add two rows to the Groups table for the first two group IDs. Add three rows to the Group Membership table: one row for member 1 and group 2; one for member 2 and group 1; and one for member 2 and group 2. Write a SELECT statement that joins the three tables and retrieves the group name, member last name, and member first name. Sort the results by the group name, member last name, and member first name. 4. Write an ALTER TABLE statement that adds two new columns to the Members table created in exercise 2. Add one column for annual dues that provides for three digits to the left of the decimal point and two to the right. This column should have a default value of 52.50. Add one column for the payment date. 5. Write an ALTER TABLE statement that modifies the Groups table created in exercise 2 so the group name in each row has to be unique. Then, use an INSERT statement to attempt to insert a duplicate name. This statement should fail due to the unique constraint. 351 Exercises 1. Write a script that adds an index to the AP database for the zip code field in the Vendors table. Chapter 11 How to create databases, tables, and indexes 2. Write a script that contains the CREATE TABLE statements needed to imple- ment the following design in the EX database: members member id first name last name Address oily. state phone members groups member id group d groups group_id group rambe These tables provide for members of an association, and each member can be registered in one or more groups within the association. The member_id and group_id columns are the primary keys of the Members and Groups tables, and these columns are foreign keys in the Members Groups table. Include any constraints or default values that you think are necessary. Include statements to drop the tables if they already exist. 3. Write INSERT statements that add rows to the tables that are created in exercise 2. Add two rows to the Members table for the first two member IDs. Add two rows to the Groups table for the first two group IDs. Add three rows to the Group Membership table: one row for member 1 and group 2; one for member 2 and group 1; and one for member 2 and group 2. Write a SELECT statement that joins the three tables and retrieves the group name, member last name, and member first name. Sort the results by the group name, member last name, and member first name. 4. Write an ALTER TABLE statement that adds two new columns to the Members table created in exercise 2. Add one column for annual dues that provides for three digits to the left of the decimal point and two to the right. This column should have a default value of 52.50. Add one column for the payment date. 5. Write an ALTER TABLE statement that modifies the Groups table created in exercise 2 so the group name in each row has to be unique. Then, use an INSERT statement to attempt to insert a duplicate name. This statement should fail due to the unique constraint. 351

Answer rating: 100% (QA)

To complete the exercises well assume that the AP and EX databases already exist and are currently selected Lets go through each exercise step by step ... View the full answer