This is a question from Oracle 12c SQL by Joan Casteel.Chapter 9, Hands-On Assig

Question

This is a question from Oracle 12c SQL by Joan Casteel.Chapter 9, Hands-On Assignment.

Note: Two SQL queries (Query 1: Using traditional approach. Query 2: Using the JOIN keyword) are needed for

Question 5: A table named Prob_Contact contains the required frequency of contact with a probation officer, based on the length of the probation period (the number of days assigned to probation). Review the data in this table, which indicates ranges for the number of days and applicable contact frequencies. Create a list containing the name of each criminal who has been assigned a probation period, which is indicated by the sentence type. The list should contain the criminal name, probation start date, probation end date, and required frequency of contact. Sort the list by criminal name and probation start date.

Explanation / Answer

Queries:

*/ /*Traditional Method */
SELECT c.last, c.first, s.start_date, s.end_date, p.con_freq
FROM criminals c, sentences s, prob_contact p
WHERE c.criminal_id = s.criminal_id
AND s.end_date - s.start_date [>=] p.low_amt
AND s.end_date - s.start_date [<=] p.high_amt
AND s.type = 'P'
ORDER BY last, first, start_date;

Explanation:
In Traditional way, we just specify the tables from which we want to fetch the data and then the conditions

/*JOIN Method */
SELECT c.last, c.first, s.start_date, s.end_date, p.con_freq
FROM criminals c JOIN sentences s ON s.type = 'P'
JOIN prob_contact p ON s.end_date - s.start_date >< p.low_amt AND s.end_date - s.start_date <= p.high_amt
ORDER BY last, first, start_date;

Explanation:
In join method, you join one table with other and specify the conditions in the same clause

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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