Army Transportation and Aviation Logistics School SQL Project two images related to the assignment …………………………………….. STAY HOME 4

Army Transportation and Aviation Logistics School SQL Project two images related to the assignment …………………………………….. STAY HOME 4G
8:23 PM
X Х
New complementary assessm…
DOC – 92 KB
Column Name Column Type Column Length Constraint
Payment Id
NUMBER
PRIMARY KEY
Apartment Id NUMBER
FOREIGN KEY
Renter Id NUMBER
FOREIGN KEY
Due_Date DATE
Paid Date DATE
Based on the above database schema, answer the following questions:
1. Write a query to display the renter id, the first letter of the first
name concatenated with the last name, phone number and email.
(1.75 Marks)
2. Write a query to display building id, name in capital letters and
owner. Display only buildings that are located in Sohar area.
(1.25 Marks)
3
find the total number of
Write an
buildings.
SQL statement to
(1 Mark)
4. Write an SQL statement to find the total fees paid by renters in
paid year for each apartment. Display building name, owner,
apartment number, and year. Label the columns year as “Payment
Year”. Sort the output by the building name and apartment
number in descending order.
(2 Marks)
5. Write a query to display apartment id, the renter id, and the paid
date. Format the paid date as “04th of March 2018”.
(1.5 Marks)
6. Write a query to find the lowest and the highest apartment prices.
(1 Mark)
ant
Open Word
est
63
Answer the following question. Using Oracle SQL Developer, execute
the SQL statements given in the file “RealEstate DB.txt” to create the
database that contains four (4) tables called Building, Apartment,
Renter and Payment. Some records are already inserted in all tables
to perform your queries. The structure of those tables is shown belage
1 of 4
Building Table:
Column Name Column Type Column Length Constraint
Building Id
NUMBER
PRIMARY KEY
Building Name VARCHAR2 60
VARCHAR2 160
Address VARCHAR2 100
4
Owner
Apartment Table:
Column Name Column Type Column Length Constraint
Apartment Id NUMBER
PRIMARY KEY
Apartment No VARCHAR2 100
Description VARCHAR2 200
Price
NUMBER
Building Id NUMBER 14
FOREIGN KEY
Renter Table:
Column Name Column Type Column Length Constraint
Renter Id NUMBER 14
PRIMARY KEY
First Name VARCHAR2 30
Last Name VARCHAR2 30
Phone NUMBER 8
Email
VARCHAR2 30
BirthDate Date
Payment Table:
Column Name Column Type Column Length Constraint
Payment Id NUMBER
PRIMARY KEY
Apartment Id NUMBER
FOREIGN KEY
Renter Id NUMBER
FOREIGN KEY
Due Date
DATE
Paid Date DATE
Based on the above database schema, answer the following questions:
1. Write a query to display the renter id, the first letter of the first
name concatenated with the last name, phone number and email.
(1.75 Marks)
2. Write a query to display building id, name in capital letters and
owner. Display only buildings that are located in Sohar area.
(1.25 Marks)
3.
Write an SQL statement
to
find the total number of
buildin
MOL

Purchase answer to see full
attachment