Question

SQL Homework exercises:

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.

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 1. 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.

3.

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 2 to make sure this works.

Answer 1

Members( mem_id, first_name, last_name)
Groups( grp_id , name)
Group_Mmebership( mem_id, grp_id)

1.

INSERT INTO Members ( mem_id, first_name, last_name)
VALUES ( 1 , 'Tom', 'Kala' );

INSERT INTO Members ( mem_id, first_name, last_name)
VALUES ( 2 , 'Matti', 'Erichsen' );

INSERT INTO Groups ( grp_id , name)
VALUES (1, 'abc');

INSERT INTO Groups ( grp_id , name)
VALUES (1, 'def');

INSERT INTO Group_Membership ( mem_id, grp_id )
VALUES (1, 2);

INSERT INTO Group_Membership ( mem_id, grp_id )
VALUES (2, 1);

INSERT INTO Group_Membership ( mem_id, grp_id )
VALUES (2, 2);

SELECT G.name, M.first_name, M.last_name
FROM Groups_Memebership P
INNER JOIN Members M ON M.mem_id = P.mem_id
INNER JOIN Groups G ON G.grp_id = P.grp_id;

2.

INSERT INTO Groups ( grp_id , name)
VALUES ( grp_seq.NEXTVAL, 'ijk');

SELECT * FROM Groups;

3.

ALTER TABLE Groups
ADD UNIQUE (name);

INSERT INTO Groups ( grp_id , name)
VALUES ( grp_seq.NEXTVAL, 'ijk');