Use tables given below for answering the questions
Q11. Insert the information of a accident of a car
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.
No comments:
Post a Comment