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


Q5. Display the property address, the year when the rent start and the year when the rent finished for all properties that have been rented so far

Q6. Display the cust_name(s) who has rented a property maximum number of times

Q7. Display the property address, rent, rent_start, rent_finish of the customer
       Named ‘Amit’

Q8. Display the names of all owners who have rented their property to Amit

Q9. Display the names of the properties which are not taken on rent. Using correlated queries

Q10. Create a view and display the following
            cust id, cust name, property address, rent, rent_start,rent_finish

Q11. Insert  the new property information in the property table with all relevant information