I need help with these SQL questions. Thanks 1) List the information on crime ch
ID: 3712502 • Letter: I
Question
I need help with these SQL questions. Thanks
1) List the information on crime charges for each charge that has had a fine above average and a sum paid below average.
2) List all the names of all criminals who have had any of the crime code charges involved in crime ID 10089.
3) Use a correlated subquery to determine which criminals have had at least one probation period assigned.
4) List the names of officers who have booked the highest number of crimes. Note that more than one officer might be listed.
Note: Use a MERGE statement to satisfy the following request:
5) The criminal data warehouse contains a copy of the CRIMINALS table that needs to be updated periodically from the production CRIMINALS table. The data warehouse table is named CRIMINALS_DW. Use a single SQL statement to update the data warehouse table to reflect any data changes for existing criminals and to add new criminals.
Here is the database:
DROP TABLE aliases CASCADE CONSTRAINTS;
COMMIT;
DROP TABLE criminals CASCADE CONSTRAINTS;Explanation / Answer
--1) List the information on crime charges for each charge that has had a fine above average and a sum paid below average.
SELECT * FROM crime_charges WHERE fine_amount > (SELECT AVG(fine_amount) FROM crime_charges)
AND amount_paid <(SELECT AVG(fine_amount) FROM crime_charges);
-- List all the names of all criminals who have had any of the crime code charges involved in crime ID 10089.
SELECT CONCAT(c.last, c.first) AS Name fROM criminals c INNER JOIN crime_charges cc ON c.crime_id=cc.crime_id
WHERE cc.crime_id=10089;
--Use a correlated subquery to determine which criminals have had at least one probation period assigned.
SELECT * FROM criminals WHERE Exists (SELECT crime_id FROM WHERE criminal_id=criminals.criminal_id AND criminals.p_Status='Y')
--List the names of officers who have booked the highest number of crimes. Note that more than one officer might be listed.
SELECT o.last ,o.first FROM officers o INNER JOIN crime_officers c ON o.officer_id=c.officer_id
GROUP BY c.officer_id HAVING COUNT(c.crimeid)=(SELECT top 1 COUNT(c.crimeid) FROM crime_officers GROUP BY officer_id ORDER BY DESC)
-- The criminal data warehouse contains a copy of the CRIMINALS table that
-- needs to be updated periodically from the production CRIMINALS table. The data warehouse table is named CRIMINALS_DW.
MERGE INTO CRIMINALS c
USING CRIMINALS_DW d
ON (c.criminal_id = d.criminal_id)
WHEN MATCHED THEN
UPDATE SET
criminal_id =d.criminal_id,
last =d.last,
first =d.first,
street=d.street ,
city=d.city ,
state =d.state,
zip =d.zip,
phone =d.phone
WHEN NOT MATCHED THEN
INSERT (criminal_id ,
last ,
first ,
street,
city ,
state,
zip ,
phone )
VALUES (d.criminal_id ,
d.last ,
d.first ,
d.street,
d.city,
d.state,
d.zip ,
d.phone);