Joins “Using the ssh, Oracle database 11g” * for the table: select table_name fr

Question

Joins “Using the ssh, Oracle database 11g”

* for the table:

select table_name

from User_tables;

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.

--------------------------------------------------------------------------

*I have this answer but I think there are an erorr,

1.

select books.title from books, bookauthor, author where books.isbn = bookauthor.isbn and bookauthor.authorid = author.authorid and author.lname = ‘Adams’;

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 criminals.criminal_id, criminals.first, criminals.last, criminals.v_status, criminals.p_status, aliases.alias from criminals LEFT OUTER JOIN aliases ON criminals.criminal_id = aliases.criminal_id ORDER BY criminals.last, criminals.first;

--------------------------------------------------------------------------------

* I think we need to add something a letter, for ex.

SELECT cls.criminal_id, cls.first, cls.last, cc.crime_code, cc.fine_amount

FROM criminals cls JOIN crimes cr

ON cr.criminal_id = cls.criminal_id
JOIN crime_charges cc
ON cc.crime_id = cr.crime_id
ORDER BY criminal_id, first, last, crime_code, fine_amount;

** Or need new answer, help me please

Explanation / Answer

Hi In part 2,3 of your solution
You haven't specified title or gift belong to which table. As in you need to specify book.title so that it select title column from book table. Similar to what you've suggested in last example naming tables as cls, cc helps specifying which column of table we're talking about.
Every time when there's more than 1 table, please specify Tablename.columnName.

Assuming that promotion table has column named retail. While idea is to gift in promotion table should be between these 2 values of books.

select promotion.gift from books join promotion on promotion.retail BETWEEN books.minretail and books.maxretail where books.title = ‘Shortest Poems’;

Related Questions

Navigate

• Browse (All)
• Browse J
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.