SQL HOMEWORK

You are required to write a database for the University Near Heaven (UNH) Science Fiction Association.

You may not use QBE for ANY of these actions. ONLY SQL.

Write an SQL query to create a Membership table.

MemID char(5), lastName char(15),firstName char(15).

Write an SQL query to create an Author table.

AuthID char(5), lastName char(15),firstName char(15).

Write an SQL query to create a Works table.

WorkID char(5), Title char(40), Type char(5).

[Type will be book, movie, etc.]

Write an SQL query to make a Created table.

WorkID char(5), AuthID char(5), role char(5).

[Role will be AUTH, SAUT,JAUTH, EDIT, etc for sole author, senior author, junior author, editor, etc.]

Write an SQL query to AddMember. Add the following members, AND YOURSELF, by running it 6 times.

1 Toolkien, KR

2 Weber, David

3 Heinlein, Robert

4 Asimov, Isaac

5 Twain, Mark

Write an SQL query to ADDWorks.

1 Raichel: The Robot of Romance book

2 The Honor of the Queen book

3 Stranger in a Strange Land book

4 The Gods Themselves book

Write an SQL query to AddAuthor. Add the following members, by running it 4 times.

1 Weber, David

2 Heinlein, Robert

3 Asimov, Isaac

4 Toolkien, KR

Write an SQL Query to add Created. Use IDs.

The Honor of the Queen Weber, David AUTH

Stranger in a Strange Land Heinlein, Robert AUTH

The Gods Themselves Asimov, Isaac AUTH

Raichel: The Robot of Romance Toolkien, KR AUTH

The International Library association has decided to eliminate Mark Twain as a pen name.

Write a ChangeAuthorName to change Twain, Mark to Clements, Samuel

Write an SQL query to list the titles of all books written by Asimov, Isaac.