Please enter your SQL command carefully for each part of the qestion (A-H) provi
ID: 3698588 • Letter: P
Question
Please enter your SQL command carefully for each part of the qestion (A-H) provided. Do not give the results, just simply query command required to get the results.
A. List the number, first name, and last name of every owner whose last name ends with "on".
B. List the number, last name, and first name of every owner who owns a three-bedroom condo. Sort the results by the owner's last name.
C.List the location and unit number of every condo that has scheduled service request.
D. List the number of bedrooms and average condo fee per bedroom. Sort the results by the number of bedrooms in descending order.
E. List the category number and the longest and shortest estimated hours, name them Longest and Shortest respectively, of each service request and sort by the longest hours in descending order.
F.For every service request, list the last name and first name of the owner and the category and description of the service. Sort the results by service category.
G.List the number, square footage, bedrooms, bathrooms, and condo fee for all units. Sort the results by condo fee in descending order within the bedrooms.
H. Calculate the average square footage, name it AvgSqrFt, of the condo units.
CREATE TABLE Location
(LocationNum DECIMAL (2,0) PRIMARY KEY,
LocationName CHAR(25),
Address CHAR(25),
City CHAR(25),
State CHAR(2),
PostalCode CHAR(5) );
CREATE TABLE CondoUnit
(CondoID DECIMAL(4,0) PRIMARY KEY,
LocationNum DECIMAL (2,0),
UnitNum CHAR(3),
SqrFt DECIMAL(5,0),
Bdrms DECIMAL(2,0),
Baths DECIMAL(2,0),
CondoFee DECIMAL(6,2),
OwnerNum CHAR(5) );
CREATE TABLE Owner
(OwnerNum CHAR(5) PRIMARY KEY,
LastName CHAR(25),
FirstName CHAR(25),
Address CHAR(25),
City CHAR(25),
State CHAR(2),
PostalCode CHAR(5) );
CREATE TABLE ServiceCategory
(CategoryNum DECIMAL(4,0) PRIMARY KEY,
CategoryDescription CHAR(35) );
CREATE TABLE ServiceRequest
(ServiceID DECIMAL(4,0) PRIMARY KEY,
CondoID DECIMAL(4,0),
CategoryNum DECIMAL(4,0),
Description CHAR(255),
Status CHAR(255),
EstHours DECIMAL(4,2),
SpentHours DECIMAL(4,2),
NextServiceDate DATE );
sqlite> .schema
CREATE TABLE Location
(LocationNum DECIMAL (2,0) PRIMARY KEY,
LocationName CHAR(25),
Address CHAR(25),
City CHAR(25),
State CHAR(2),
PostalCode CHAR(5) );
CREATE TABLE CondoUnit
(CondoID DECIMAL(4,0) PRIMARY KEY,
LocationNum DECIMAL (2,0),
UnitNum CHAR(3),
SqrFt DECIMAL(5,0),
Bdrms DECIMAL(2,0),
Baths DECIMAL(2,0),
CondoFee DECIMAL(6,2),
OwnerNum CHAR(5) );
CREATE TABLE Owner
(OwnerNum CHAR(5) PRIMARY KEY,
LastName CHAR(25),
FirstName CHAR(25),
Address CHAR(25),
City CHAR(25),
State CHAR(2),
PostalCode CHAR(5) );
CREATE TABLE ServiceCategory
(CategoryNum DECIMAL(4,0) PRIMARY KEY,
CategoryDescription CHAR(35) );
CREATE TABLE ServiceRequest
(ServiceID DECIMAL(4,0) PRIMARY KEY,
CondoID DECIMAL(4,0),
CategoryNum DECIMAL(4,0),
Description CHAR(255),
Status CHAR(255),
EstHours DECIMAL(4,2),
SpentHours DECIMAL(4,2),
NextServiceDate DATE );
sqlite>
Explanation / Answer
A) For this result Owner table is used. In where clause wild card with like statement is used to get all owner whose last name ends with 'on'.
SELECT OwnerNum, LastName, FirstName
FROM Owner
WHERE LastName LIKE '%on';
B) Table Owner and CondoUnit are joined, in where clause result is filtered out for bedroom 3. The result is sorted out based on the last name of the owner.
SELECT o.OwnerNum, o.LastName, o.FirstName
FROM Owner o
INNER JOIN CondoUnit c
ON o.OwnerNum = c.OwnerNum
WHERE c.Bdrms = 3
ORDER BY o.LastName;
c) Table location, CondoUnit and ServiceCategory are joined. This will give list of all CondoUnit with service request.
SELECT LocationName, UnitNum
FROM Location l
INNER JOIN CondoUnit c
ON c.LocationNum = l.LocationNum
INNER JOIN ServiceCategory s
ON s.CondoID = c.CondoID;
D) Aggregate function AVG() is used to get the average price of bedrooms. Result is grouped based on the number of bedrooms.
SELECT Bdrms, AVG(CondoFee)
FROM CondoUnit
GROUP BY Bdrms
ORDER BY Bdrms DESC;
E) Aggregate function MAX and MIN is used to get the longest and shortest estimated hours.
SELECT ServiceID, CategoryNum, MAX(EstHours) AS 'Longest', MIN(EstHours) AS 'Shortest'
FROM ServiceRequest
GROUP BY ServiceID, CategoryNum;
F)
SELECT o.LastName, o.FirstName, sc.CategoryDescription, s.Description
FROM ServiceRequest s
INNER JOIN ServiceCategory sc
ON sc.CategoryNum = s.CategoryNum
INNER JOIN CondoUnit c
ON c.CondoID = s.CondoID
INNER JOIN Owner o
ON o.OwnerNum = c.OwnerNum
ORDER BY sc.CategoryNum;
G)
SELECT CondoUnit, SqrFt, Bdrms, Baths, CondoFee
FROM CondoUnit
ORDER BY Bdrms ASC, CondoFee DESC;
H)
SELECT CondoUnit, AVG(SqrFt) AS 'AvgSqlFt'
FROM CondoUnit;