(Solved):   Section 1 Using SQL, create tables according to the given schema shown in Figure 1. Note th ...

Section 1

Using SQL, create tables according to the given schema shown in Figure 1. Note that this section is exactly the same as the one in Assignment 4.  You can use your Assignment 4 directly, or modify your Assignment 4 (if needed).  The type of each attribute is defined in Figure 2. Note that it is case-sensitive.

CUSTOMER table:

SSN: CHAR(9), Customer_Name: VARCHAR(30), Street: VARCHAR(20), City: VARCHAR(10), State: VARCHAR(10), Zip: CHAR(5), Phone: CHAR(10)

OWNS table:

SSN: CHAR(9) , License: VARCHAR(15)

CAR table:

License: VARCHAR(15), Model VARCHAR(15), Year int,  PolicyNo_Ref: int

POLICY table:

PolicyNo:int,  Policy_Rate: DECIMAL (15,2), Policy_Details: VARCHAR(100)

HAD table:

License: VARCHAR(15),  AccidentNo: int

ACCIDENT table:

Accident_no: int,  Driver_name: VARCHAR(30), Accident_Date: DATE, Amount_of_Damage: DECIMAL (15,2), Accident_Details: VARCHAR(100)

Note: DATE is in the format of ‘yyyy-mm-dd’.

Figure 2: Type Definition

Section 2

Populate the database. Please check sections 3 for details.

Section 3

Use SQL to specify the following queries.   When you populate the database, insert data such that at least one row will be display as the result of running each query.

List the cars (license, model and year, amount of damage) that have been involved in any accidents. Order the result by license in descending order.

List the cars that have more than two accidents. List license and number of accidents.

List information (driver name, amount of damage, license, model and SSN) about the accidents in which the owners of the car are involved i.e., the Driver_Name and Customer_Name are the same.

Obtain the information of any policy (policy number, policy rate and policy details) whose policy rate is higher than the rate of policy number 12.

Consider all policies, list the lowest rate, highest rate, and average rate.

Get information (accident number, driver name, accident date, and amount of damage) of the accident which has the highest amount of damage among all the accidents.  If there are more than one satisfies the above condition, list all of them.

Get the information (customer name, city and state) about the customers who own cars with the model of ‘Honda Accord’. Also retrieve those cars’ license, and year made. Order the result by customer name in an ascending order.

Retrieve the customers’ information (SSN, customer name, street, city, state and zip) who live either in ‘Roseville’ or ‘Sacramento’. (‘Roseville’ and ‘Sacramento’ are city names)

Retrieve policies that cover more than 2 cars.  List policy number and number of cars covered.

Section 4

Specify the statements to drop all the tables.