sql plus A. Answer joining tables using WHERE B. Answer joining tables using a J

Question

sql plus

A. Answer joining tables using WHERE
B. Answer joining tables using a JOIN clause

1. Which books were written by an author with the last name Adams? Perform the search using

the author name.

2. What gift will a customer who orders the book Shortest Poems receive? Use the actual book

retail value to determine the gift.

3. Identify the authors of the books Becca Nelson ordered. Perform the search using the cus-

tomer name.

4.Display a list of all books in the BOOKS table. If a book has been ordered by a customer,

also list the corresponding order number and the state in which the customer resides.

5.An EMPLOYEES table was added to the JustLee Books database to track employee information. Display a list of each employee‘s name, job title, and manager‘s name. Use column aliases to clearly identify employee and manager name values. Include all employees in the list and sort by manager name.

Explanation / Answer

1) SELECT Title
FROM Books
WHERE AuthorLastName = 'Adams';

2) SELECT gift
FROM promotions inner join books
ON JOINCOLUMN
WHERE title = 'Shortest Poems'

3) SELECT AuthorLastName, AuthorFistName
FROM ORDERS, BOOKS, Customers
WHERE orders.JOINCOLUMN = books.JOINCOLUMN
AND orders.customer# = customers.customer#
AND CustomerLastName = 'Nelson'
AND CustomerFirstName = 'Becca'

4) SELECT BookName, Order#, State
FROM Orders, Customers, Books
WHERE Orders.Customer# = Customers.Customer#
AND Books.Book# = Orders.Book#

5) SELECT EmployeeName, JobTitle, ManagerName
FROM EMPLOYEES, Managers
   ORDER BY ManagerName

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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