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.

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

SQL Practice paper-3

Use tables given below for answering the questions


Tables -
1. Customer : Contains customer’s information who rent a property
Field Name
Width
Constraint
Description
Cust_id
Number(6)
Primary Key
Id of the customer
Cust_name
Varchar2(20)
Not Null
Name of the customer
Address
Varchar2(35)

Address of the customer
Phone_num
Number(10)
Not Null
Phone number of the customer

2. Property : Contains properties information
Field Name
Width
Constraint
Description
Property _id
Number(6)
Primary Key
Id of the Property
paddress
Varchar2(35)

Address of the property
rent
Number(5)

Rent of the property
Owner_id
Number(6)
Foreign key
Id of the owner

3. Owner : Contains owners  information
Field Name
Width
Constraint
Description
Owner_id
Number(6)
Primary Key
Id of the owner
Owner_name
Varchar2(20)
Not Null
Name of the owner
Owner_address
Varchar2(35)

Address of the owner
Own_ph_no
Number(10)

Phone number of the owner

4. Cust_property: Stores the information about properties taken on rent
Field Name
Width
Constraint
Description
cust_id
Number(6)
Foreign key
Id of the customer
Property _id
Number(6)
Foreign key
Id of the property
Rent_start               
date

Date on which rent started
Rent_finish              
date

Date on which rent finished



Q1. Display the average rent.

Q2. Display customer name, property address, rent, rent_start, and rent_finish of all
       Customers

Q3. Display the owner name and no.of properties owned by the owner

Q4. Display property address and number of customers lived in that property.
       The property rent should be more than Rs.5000