** In fact, this is all the information of tables that I have. Working with JOIN

Question

** In fact, this is all the information of tables that I have.

Working with JOINS “Using the ssh, Oracle database 11g”

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.

4. Create an alphabetical list of all criminals, including criminal ID, name, violent offender status, parole status, and any know aliases.

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

** this is the table we work on it

select table_name

from user_tables;

TABLE NAME ACCT BONUS ACCTMANAGER ALIASES APPEALS AUTHOR B00KAUTHOR CATEGORY CRIMES CRIME CHARGES CRIME CODES CRIME OFFICERS TABLE NAME CRIMINALS GROCERY OFFICERS PROB CONTACT PROB OFFICERS PRODUCTS PROMOTION PUBLISHER SENTENCES TESTING WOW 22 rows selected.

Explanation / Answer

1)

SELECT
   B.Title
FROM books B,bookAuthors BA,Author AU
WHERE
   B.AuthorID = BA.ID
AND
   ON A.ID= AU.ID
GROUP BY A.Last_Name

But we can use inner join as well.if we are using inner join
we dont need to use WHERE clause

SELECT
B.Title
FROM books B
INNER JOIN bookAuthors BA
ON B.AuthorID = BA.ID
INNER JOIN author AU
ON A.ID= AU.ID
GROUP BY A.Last_Name

*Assumed the column names are Title,ID,Last_Name as there are not given.

2)
SELECT
B.Title
FROM books B
INNER JOIN bookAuthors BA
ON B.AuthorID = BA.ID
INNER JOIN author AU
ON A.ID= AU.ID
WHERE A.Last_Name="Adams"

*If the last name is also included in the name column the query would be like this

WHERE A.Last_Name LIKE '%Adams';

*Precentage sign means that any thing can be in beging of the string. But the string must ends with "Adams"

3)
As you haven't mentioned which table has the gift column and other columns needed. let's assume theres a table called gift
which has gifts names and price levels for that.
gift(ID,Tag,Lowest_Price,Highest_Price)

SELECT
   B.Title,G.Tag
FROM
   gift G
JOIN
   books B
ON
   B.retail_value
BETWEEN
   G.Lowest_Price AND G.Highest_Price
WHERE
   B.Title="Shortest Poems"

4)
SELECT
   C.ID,C.Name,CC.ofenderstatus,CC.parolstatus,A.name
FROM
   CRIMINALS C
INNER JOIN
   CHRIME_CHARGES CC
ON
   C.ID=CC.cid
INNER JOIN
   ALLIASES A
ON
   C.ID=A.cid

ORDER BY C.Name

*this will order by crimnal names in acending order if you wan other way around you can use

"ORDER BY C.Name DESC"

NOTE:To give more accurate answer you must include all tables with their columns and relationships(foriegn keys)

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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