4. Create an alphabetical list of all criminals, including criminal ID, name, vi

Question

4. Create an alphabetical list of all criminals, including criminal ID, name, violent offender status, parole status, and any known aliases. 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 toprobation). 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. 6. A column named Mgr_ID has been added to the Prob_Officers table and contains the ID number of the probation supervisor for each officer. Produce a list showing each probation officer’s name and his or her supervisor’s name. Sort the list alphabetically by probation officer name.

Explanation / Answer

1. Using traditional approach withot using JOIN operator

SELECT p1.Officer_name,p2.Officer_name as supervisor FROM Prob_Officers p1, Prob_Officers p2 where p1.Mgr_ID=p2.Officer_ID;

This query retrieves the the officer name and his manager name. In this we have self join with the same table by creating alliases as p1 and p2. here in this supervisor is also an employee like other but he is the manager to other employees. Like all employees he i also having ID.

Now we are comparing the MGR_ID with the OFFICER_ID so that it prints the name of that manager for the officer which matches his MGR_ID with the Officer_ID.

2. Using JOIN keyword.

SELECT e.ename,e1.ename as manager FROM emp e JOIN emp e1 on e.mgr=e1.empno;

This query retrieves the same as the above query. The only difference is in this we have used JOIN keyword. This is the self join of the same table. This exactly the same as the above query........

Related Questions

Navigate

• Browse (All)
• Browse 4
• Subjects

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