SQL Practice paper-2

Use tables given below for answering the questions


Tables -
1. Person : 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. Cars : 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. Owns: 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 total damage amount of accidents up to 31-dec-2006

Q2. Display the car id, model, person name, acc_date, damage_amt
        Where accident occurred in the year 2005

Q3. Display model and no.of accident of that model for each year

Q4.  There could be many accidents of a specific model. Display each model
With the number of accidents of that model where the damage amount is more
Than 20,000


Q5. Display the car id and the day of the Accident from the accident table

Q6. Display person_id, car_id, acc_date, damage_amt, driver_name for all persons.
If a car does not undergo a accident, the relevant accident information
Should be null.

Q7. Display the information of car whose driver name is ‘Ram’ and had an accident
        Before ’31-mar-2007’

Q8. Display the information of a car that met an accident in the year 2005 and
Driver name starts with ‘r’  . The information that should be displayed is Car_id,   person_name, model

Q9. Display the cars that have not met any accident so far. Use correlated query

Q10. Create a view containing car_id, make year, model, driver name, acc_date

Q11. Modify  the model of a car maruti 800 to maruti AC 800