The Traffic Violation database of an insurance company contains the following tables: 

People: Lists the names of all the insured customers and their personal data including the category of their license and the state the license was issued. Each person is uniquely identified by their License number.

Category: Lists the vehicle category type. (i.e. Personal Auto, Commercial Auto, Perosnal Truck, Commercial Truck) Each category is uniquely identified by a CategoryID.

ViolationType: Lists the type of violation. (Infraction, misdemeanor, etc.) Each violation type is uniquely identified by a ViolationTypeID.

ViolationDescription: Lists the description of the violation, the type of violation, the associated points, and the associated fines. Each description is uniquely identified by a DescriptionID.

Violation: Is a record of all traffic violations that have taken place. This table includes the violator's driver's license number, the DescriptionID, the date of the violation, the state where the violation occurred, and whether or not the violation has been paid. Each violation is uniquely identified by a ViolationID.

 

Complete the following queries based on the CSE 2111 Pre Lab 8 Traffic Violations Master database. Save each query as Query 1, Query 2, etc.

Query 1  Write a query to summarize by Category ID, the number of violations for each category type. List the Category ID, Category type, and the number of violations.

Query 2  Write a query to summarize by license number, the number of violations, total points, and average amount of fines incurred. List the License Number, first name, last name, number of violations, total points, and the average amount of the fines incurred.

Query 3  Write a query to summarize by violation type description, the number of violations that have taken place between April 1, 2014 and May 31, 2014. List the Type Description and the number of violations.

Query 4  Your insurance company penalizes drivers by adding a poor driver surcharge on policies of drivers with any  violations. Write a query to calculate the surcharge. The surcharge added is 15% of the value of the total fines received. List the license number, drivers last name, the total amount of fines, and the surcharge value. 

Query 5  Write a query to summarize by Category ID, the number of vehicles in each category. (Personal Auto, Commercial Auto, etc.) List the Category type, and the number of vehicles.

Query 6  Write a query to display the number (count) of violations that occurred in Ohio. List only the number of violations. (Because we only want to list the number of violations, we will not use a Group By. We will only use a Count and a "Where" clause.)

                    

Query 7  Write a query to summarize the average fine amount by State. List the state, and the average fine amount.

Query 8  Write a query to summarize by state, the number of number of times fines were paid in Ohio. List the state and the number of paid fines.

Query 9  Write a query to display the number of violations that occurred (in Ohio after May 11, 2014) or (in New York after June 1, 2014). List the number of violations.

Query 10  Write a query to display the total number of fines (use a count) for all people who live in zipcodes that start with "430". List the number of fines.

  

I NEED HELP IN KNOWING WHAT TABLES TO USE FOR EACH QUERY