Question
7.) For the SQL file listed below, provide the correct SQL command for completing each query. CREATE TABLE Location (LocationNum DECIMAL (2,0) PRIMARY KEY, LocationName
7.) For the SQL file listed below, provide the correct SQL command for completing each query.
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 );
A.) List the number of bedrooms and average condo fee by bedrooms for condo units that are larger than 700 square feet and the average condo fee is less than $500.00. Sort the results by the average condo fee.
B.) List the average condo fee of condos with two baths at each location that the average condo fee is less than $500.00.
C.) List the category number, estimated hours, and description of all service requests. Sort the results by estimated hours in descending order within the category number.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started