SQL Homework: 1. Write INSERT statements that add two rows to the Members table
ID: 3713181 • Letter: S
Question
SQL Homework:
1.
Write INSERT statements that add two rows to the Members table for member IDs 1 and 2, two rows to the Groups table for group IDs 1 and 2, and 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. Then, write a SELECT statement that joins the three tables and retrieves the group name, member last name, and member first name.
2.
Create sequences that can be used to number the member ID and group ID values starting with 3 (since you already added members and groups for IDs 1 and 2).
3.
Write an INSERT statement that adds another row to the Groups table. This statement should use the NEXTVAL pseudo column to get the value for the next group ID from the sequence that you created in exercise 5. Then, write a SELECT statement that gets all of the data for all of the rows in the Groups table to make sure your sequence worked correctly.
4.
Write an ALTER TABLE statement that adds two new columns to the Members table: one column for annual dues that provides for three digits to the left of the decimal point and two to the right; and one column for the payment date. The annual dues column should have a default value of 52.50.
5.
Write an ALTER TABLE statement that modifies the Groups table so the group name in each row has to be unique. Then, re-run the INSERT statement that you used in exercise 6 to make sure this works.
Explanation / Answer
1.
Add two rows to the Members table for member IDs 1 and 2.
INSERT INTO Members ( Id, firstName, lastName) VALUES (1,'member'1, 'one'), (2, 'member2', 'two')
Add two rows to the Groups table for group IDs 1 and 2
INSERT INTO Groups ( Id, Name) VALUES (1, 'Group1'), (2, 'Group2')
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.
INSERT INTO Group_Membership Values (MemberId, GroupId) Values (1,2),(2,1),(2,2)
Then, write a SELECT statement that joins the three tables and retrieves the group name, member last name, and member first name.
SELECT M.lastName, M.firstName, G.name
From Members M
INNER JOIN Group_Membership MG on M.Id = MG.MemberId
INNER JOIN Groups G on MG.GroupId = G.Id
2.
CREATE SEQUENCE members_seq
START WITH 3
INCREMENT BY 1;
CREATE SEQUENCE groups_seq
START WITH 3
INCREMENT BY 1;
3.
Write an INSERT statement that adds another row to the Groups table. This statement should use the NEXTVAL pseudo column to get the value for the next group ID from the sequence that you created in exercise 5
INSERT INTO Groups
(id, name)
VALUES
(groups_seq.NEXTVAL, 'Group3');
Then, write a SELECT statement that gets all of the data for all of the rows in the Groups table to make sure your sequence worked correctly.
SELECT * FROM Groups
4.
Write an ALTER TABLE statement that adds two new columns to the Members table: one column for annual dues that provides for three digits to the left of the decimal point and two to the right; and one column for the payment date. The annual dues column should have a default value of 52.50.
ALTER TABLE Members
ADD annualDues decimal(5, 2),
ADD paymentDate Date
5.
Write an ALTER TABLE statement that modifies the Groups table so the group name in each row has to be unique.
ALTER TABLE Groups
ADD UNIQUE (name)
Then, re-run the INSERT statement that you used in exercise 6 to make sure this works.
INSERT INTO Groups (Id, Name) VALUES (1, 'Group1'), (2, 'Group2')
*NOTE : Please change the column names to be exactly like they are in the created tables.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.