Question

Case Study

The City Jail Technologies Department is constructing an application to allow users in the Crime Analysis Unit to query data more easily. This system requires creating a number of views, described in the following list. Provide the SQL statement to perform each task and test your views with a query.

1. Create a statement that always returns the names of the three criminals with the highest number of crimes committed.

2. Create a view that includes details for all crimes, including criminal ID, criminal name, criminal parole status, crime ID, date of crime charge, crime status, charge ID, crime code, charge status, pay due date, and amount due. This view shouldn’t allow performing any DML operations. Each time the view is used in the application, the data should be queried from the database. (For example, each use of the view should reflect the most current data in the database.)

3. Create a view that includes all data for officers, including the total number of crimes in which they participated in filing charges. To speed up the officer queries, store this view data and schedule the data to be updated every two weeks.

Answer 1

--1

Select c.frist,c.last,cr.criminal_id,count(cr.crime_id) As NumberOfCrimes from crimes INNER join criminals c on cr.criminal_id=c.criminal_id group by cr.criminal_id,c first,c.last order by count(cr.crime_id) desc fetch first 3 rows only;

In above we have used fetch to limit records and inner join between table to get count of crimes of each criminal.and with help of group by aggregate of crimes.