IN SQL Tables are in SQL 1. List books written by author’s last name. Complete u
ID: 3778843 • Letter: I
Question
IN SQL
Tables are in SQL
1. List books written by author’s last name. Complete using both the where clause and the From clause. tables needed include books, bookauthor, and author
2. Narrow the same query down to books written by an author with the last name Adams. Perform the search using the author name. Complete using both the where clause and the From clause.
3. Non-equality Join: What gift will a customer who orders the book Shortest Poems receive? Use the actual book retail value to determine the gift.
Outer Join Question
4. Create an alphabetical list of all criminals, including criminal ID, name, violent offender status, parole status, and any know aliases.
Explanation / Answer
1.
select b.title from books b, bookauthor ba, author a where b.isbn = ba.isbn and ba.authorid = a.authorid and a.lname = ‘Adams’;
alias b---books,ba ----bookauthor and a----author
2.
select title from (books join bookauthor on (books.isbn = bookauthor.isbn)) join author on (bookauthor.authorid = author.authorid) where book.lname = ‘Adams’;
3. Non equality Join
select gift from books join promotion on retail between minretail and maxretail where title = ‘Shortest Poems’;
4. Outer Join
Select cr.criminal_id, cr.first, cr.last, cr.v_status, cr.p_status, al.alias from criminals cr LEFT OUTER JOIN aliases al ON cr.criminal_id = al.criminal_id ORDER BY cr.last, cr.first;