Question
Please enter your SQL command carefully for each question provided. I don't need the results, just the query command. 1.Calculate the average condo fee and
Please enter your SQL command carefully for each question provided. I don't need the results, just the query command.
1.Calculate the average condo fee and name it AverageCondoFee.
2.List the location number, unit number, square footage, and condo fee for all units. Sort the results by condo fee within the square footage
3.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.
4.For every service request in the "Painting" category, list the condo ID, description, and status.
5.List the location number, average square footage, and average condo fee at each location.
6.List the location number and the condo fee of the most expensive and least expensive condo fees at each location.
7.List the number, last name, and first name of every owner who has requested a service.
8.List the location and unit number of every condo that has scheduled service request.
9.List the square footage and condo fee for every condo except unit number 201, 401, 405, and 503 without using AND or OR keyword.
10.List the condo id and description for every service request that contains "kitchen" in the description.
11.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.
12.List the average condo fee of three-bedroom condos at each location that the average condo fee is greater than $300.00.
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>
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