Question
please give the sql command. List the most expensive and least expensive properties that are made of brick . List the id, list price, and
please give the sql command.
List the most expensive and least expensive properties that are made of brick.
List the id, list price, and days listed (i.e., the number of days since the property was listed until it was sold). Exclude the property that has no date sold value (IS NULL/IS NOT NULL).
List the id and address of every property whose roof is tile or shaker or shingle or slate.
List the number of 3-bedroom properties that are listed less than $400,000.
List the id, list price, square footage, and price per square foot (PricePerSqFoot) for every property that is built after 2000. Sort the results by price per square foot.
List the id and name of every subdivision that has bike trail and pool but no recreation center. Sort the results by subdivision name.
List the id, square footage, construction type, and year built of every property that the construction is NOT brick. Sort the results by year built.
List the id and address of every property whose address contains "apple" or "peach".
List the id, address, and listing price of every property that is located in the subdivision that has pool.
For every property that is listed by the agent whose last name is "Hart" and is located in a subdivision with a recreation center, list the construction and average listing price by construction if the average listing price is greater than $1.5 million.
sqlite> .schema
CREATE TABLE IF NOT EXISTS "SubDivision" ( `SubDivisionID` TEXT NOT NULL, `SubDivision` TEXT, `Pool` TEXT, `RecreationCenter` TEXT, `BikeTrail` TEXT ); CREATE TABLE IF NOT EXISTS "Properties" ( `ListingID` INTEGER NOT NULL, `DateListed` REAL, `DateSold` REAL, `ListPrice` REAL, `SalePrice` REAL, `SqFeet` REAL, `Beds` INTEGER, `Baths` INTEGER, `Address` TEXT, `SubDivisionID` INTEGER, `City` INTEGER, `Sold` TEXT, `SellingAgent` INTEGER, `ListingAgent` INTEGER, `Style` TEXT, `Construction` TEXT, `Garage` TEXT, `YearBuilt` INTEGER, `Roof` TEXT ); CREATE TABLE IF NOT EXISTS "Agents" ( `AgentID` INTEGER, `FirstName` TEXT, `LastName` TEXT, `HomePhone` TEXT, `Title` TEXT, `CellPhone` TEXT, `Performance` TEXT, PRIMARY KEY(`AgentID`) );
CREATE TABLE Agents ( `AgentID` INTEGER, `FirstName` TEXT, `LastName` TEXT, `HomePhone` TEXT, `Title` TEXT, `CellPhone` TEXT, `Performance` TEXT, PRIMARY KEY(`AgentID`) ); | CREATE TABLE SubDivision ( `SubDivisionID` TEXT, `SubDivision` TEXT, `Pool` TEXT, `RecreationCenter` TEXT, `BikeTrail` TEXT, PRIMARY KEY(`SubDivisionID`) ); | CREATE TABLE Properties ( `ListingID` INTEGER PRIMARY KEY, `DateListed` REAL, `DateSold` REAL, `ListPrice` REAL, `SalePrice` REAL, `SqFeet` REAL, `Beds` INTEGER, `Baths` INTEGER, `Address` TEXT, `SubDivisionID` INTEGER, `City` INTEGER, `Sold` TEXT, `SellingAgent` INTEGER, `ListingAgent` INTEGER, `Style` TEXT, `Construction` TEXT, `Garage` TEXT, `YearBuilt` INTEGER, `Roof` TEXT ); |
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