Use tables given below for answering the questions
Q11. Modify
the model of a car maruti 800 to maruti AC 800
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
No comments:
Post a Comment