SQL Practice paper-1

Use tables given below for answering the questions


1. Person_info : Contains person’s information
Field Name
Width
Constraint
Description
person_id
Number(6)
Primary Key
Id of the person
person_name
Varchar2(20)
Not Null
Name of the person
address
Varchar2(35)

Address of the person
Phone_num
Number(10)

Phone number of the person

2. Car_info : Contains car’s information.
Field Name
Width
Constraint
Description
car_id
Number(4)
Primary Key
Id of the car
Make_yr
Number(4)
Not Null
Year of manufacturing of the car
model
Varchar2(35)
Not Null
Model of the car

3. Own_info: Contains person and its car information.
Field Name
Width
Constraint
Description
person_id
Number(6)
Foreign key
Id of the person
Car_id
Number(4)
Foreign key
Id of the car
Driver_name
Varchar2(20)
Not null
Driver name





4. Accident : Contains accident’s information of a car
Field Name
Width
Constraint
Description
Car_id
Number(4)
Primary key
Id of the car
Acc_date
Date
Primary Key
Date of the accident
Damage_amt
Number(6)

Damage amount
Car_id and acc_date is composite primary key



Q1.  Display the car id and date of accident in the day, month, and yyyy format.

Q2. Display the person id, person name, car id, year of make and model of all
       Person where year of manufacturing of a car is less than 2005

Q3. Display the person's name and the number of cars owned by that person

Q4.  There could be many cars of a specific model. Display each model with the number of cars of that model and the number of people who own that specific model. A person may own multiple cars of the same model.


Q5. The damage_amt is reimbursed to the owner 1 month after the accident. Display the 'car Id' and the 'date of reimbursement' for all cars involved in an accident. The column headings should be as mentioned.

Q6. Display person_id, car_id ,make_yr, model, driver_name for all persons. If a person does not own a car, the relevant car information should be null.

Q7.Display all the information of person who own ‘maruti 800’

Q8.Display the  model that has met maximum number of accidents

Q9. Display the  cars that have damage amount more than the average damage amount of cars of the same model. Solve this as a correlated query.

Q10. Create a view containing person_id, person_name, car_id, model and Driver name who have not met any accident so far.

Q11. Insert the information of a accident of a car