these answers are all wrong i need the correct sql command please. below is the

Question

these answers are all wrong i need the correct sql command please. below is the schema.

(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 );

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.

Your Answer: wrong

SELECT COUNT(Bdrms), AVG(CondoFee)

Group by Bdrms

having sqrft > 700 AND

AVG(CondoFee) < 500

OrderBy AVG(CondoFee);

List the condo id and description for every service request that contains "kitchen" in the description.

Your Answer:

SELECT CondoID, Description

FROM CondoUnit, ServiceRequest

WHERE CondoUnit.CondoID = ServiceRequest.CondoID

AND ServiceRequest.Description = 'Kitchen';

List the location number, average square footage, and average condo fee at each location.

Your Answer: wrong

SELECT locationNum, avg(sqrFt) AverageSquareFeet, avg(CondoFee)

FROM Location, CondoUnit

WHERE location.locationNum = CondoUnit.locationNum;

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.

Your Answer:wrong

SELECT lastName, FirstName, CategoryNum, Description FROM

Owner, ServiceRequest, CondoUnit

WHERE Owner.ownerNum = CondoUnit.OwnerNum AND

ServiceRequest.CondoID = CondoUnit.COndoID

AND OrderBy CategoryNum;

Explanation / Answer

1.

SELECT Bdrms, AVG(CondoFee) from CondoUnit Group by Bdrms having sqrft > 700 AND AVG(CondoFee) < 500 OrderBy AVG(CondoFee);

2.

SELECT CondoID, Description FROM CondoUnit, ServiceRequest WHERE CondoUnit.CondoID = ServiceRequest.CondoID AND ServiceRequest.Description LIKE '%Kitchen%;

3.

SELECT locationNum, avg(sqrFt) AverageSquareFeet, avg(CondoFee) FROM CondoUnit

Group by locationNum ;

4.

SELECT lastName, FirstName, CategoryNum, Description FROM Owner, ServiceRequest, CondoUnit group by ServiceID having Owner.ownerNum = CondoUnit.OwnerNum AND ServiceRequest.CondoID = CondoUnit.CondoID

OrderBy CategoryNum;

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.