Jail House, Working with JOINS “Using the ssh, Oracle database 11g” * for the ta

Question

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

* for the table:

select table_name

from User_tables;

1.Using either the WHERE clause or JOIN techniques, List all criminals along with the crime charges filled. The report need to include the criminal ID, Name, Crime, and fine amount. This query will require that you join criminals, crimes, and crime charges tables

2. List all criminals along with crime status and appeal status (if applicable). The reports need to include the criminal ID, name, crime classification, date charged, appeal filing date, and appeal status. Show all criminals, regardless of whether they have filed an appeal.

3. List all criminals along with crime information. The report needs to include the criminal ID, name, crime classification, date charged, crime code, and fine amount. Include only crimes classified as “Other”. Sort the list by criminal ID, and date charged.

Explanation / Answer

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;

Query 2:

SELECT cls.criminal_id, cls.first, cls.last, cr.classification, cr.date_charged, ap.filing_date, ap.status
FROM criminals cls JOIN crimes cr
ON cr.criminal_id = cls.criminal_id
JOIN appeals ap
ON ap.crime_id = cr.crime_id
ORDER BY criminal_id;

Query 3:

SELECT cls.criminal_id, cls.first, cls.last, cr.classification, cr.date_charged, cc.fine_amount, cc.crime_code
FROM criminals cls JOIN crimes cr
ON cr.criminal_id = cr.criminal_id
JOIN crime_charges cc
ON cc.crime_id = cr.crime_id
WHERE classification = 'O'
ORDER BY criminal_id, date_charged;

Related Questions

Navigate

• Browse (All)
• Browse J
• Subjects

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