Crime-Tracking database that contains following tables:
Â
Following questions are based on Crime-Tracking database that contains following tables:
Ø Criminals
o  Name, address, phone number, violent offender status (yes/no), probation status(yes/no), and aliases
Ø Crimes
o  Classification (felony, misdemeanor, other), date charged, appeal status(closed, can appeal, in appeal), hearing date, appeal cutoff date(always 60 days after the hearing date), arresting officers(can be more than one officer), crime codes(such as burglary, forgery, assault; hundreds of codes exists), amount of fine, court fee, amount paid, payment due date, and charge status(pending, guilty, not guilty)
Ø Sentencing:
o  Start date, end date, number of violations(such as not reporting to probation officer), and type of sentence (jail period, house arrest, probation)
Ø Appeals
o  Appeal filing date, appeal hearing date, status(pending, approved, and disapproved)
o  Note: Each crime case can be appealed up to three times
Ø Police Officers
o  Name, precinct, badge number, phone contact, status(active/inactive)
Notes:
·       A single crime can involve multiple crime charges, such as burglary and assault
·       Criminals can be assigned multiple sentences. For example, a criminal might be required to serve a jail sentence following by a period of probation
Â
- List the name of each officer who has reported more than the average number of cirmes officers have reported
Â
Answer:
Â
select co.officer_id, o.last, o.first
from crime_officers co join
    officers o
    on co.officer_id = o.officer_id
group by co.officer_id, o.last, o.first
having count(*) > (select count(*) / count(distinct officer_id)
                  from crime_officers
                 );
Â
Â
Â
Â
Â
Â
Â
Â
Â
Â
- List the name of all criminals who have committed less than average number of crimes and aren’t listed as violent offenders
- List appeal information for each appeal that has a less than average number of days between the filing and hearing dates
- List the names of probation officers who have had a less than average number of criminals assigned
- List each crime that has had the highest number of appeals recorded
- List the information on crime charges for each charge that has had a fine above average and a sum paid below average
- List the names of all criminals who have had any of the crime code charges involved in crime ID 10089
- Use a correlated subquery to determine which criminals have had at least one probation period assigned
- List the names of officers who have booked the highest number of crimes. Note that more than one officer might be listed.
- The criminal data warehouse contains a copy of the criminals table that needs to be updated periodically from the production Criminal table. The data warehouse table is named Criminals_DW. Use a single SQL statement to update the data warehouse table to reflect any data change for existing criminals and to add new criminals.
Â
Attachments:
Answers
Status NEW
Posted 28 Nov 2017 10:11 AM
My Price 10.00
----------- Â ----------- H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly
Not Rated(0)