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.