Use tables given below for answering the questions
Q11. Insert the
new property information in the property table with all relevant information
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
No comments:
Post a Comment