SQL Assignment

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.
MemIDchar(5), lastName char(15),firstName char(15).

Write an SQL query to create an Author table.
AuthIDchar(5), lastName char(15),firstName char(15).

Write an SQL query to create a Works table.
WorkIDchar(5), Title char(40), Type char(5).
[Type will be book, movie, etc.]

Write an SQL query to make a Created table.
WorkIDchar(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.