Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Database Systems Design Implementation And Management

Authors: Carlos Coronel, Steven Morris

14th Edition

978-0357673034

More Books

Students also viewed these Databases questions

Question

5.2 Summarize the environment of recruitment.

Answered: 1 week ago