please give the sql command. List the most expensive and least expensive propert

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

Explanation / Answer

select max(ListPrice) maxPrice,min(ListPrice) minPrice from properties where Construction ='brick';---max function for maximum price and min function minimum price

select ListingID,ListPrice,DATEDIFF(d,DateSold,DateListed) days listed from properties where DateSold is not null;---datediff function extract the the date difference from two date---  

select ListingID,Address from properties where Roof='tile' or Roof='shaker' or Roof=='shingle' or Roof='slate';

select count(ListingID) from properties where Beds=3 and ListPrice>4000000

select ListingID,ListPrice,SqFeet,(CAST(ListPrice as floot)/CAST(SqFeet as floot)) PricePerSqFeet from properties where year(DateListed)>'2000' Order By PricePerSqFeet;-----year extrract the year from date----

select SubDivisionID,SubDivision from subdivison where BikeTrail is not null and Pool is not null and RecreationCenter is null order by SubDivision;

select ListingID,SqFeet,Construction,YearBuilt from properties where Construction <>'brick' order by(YearBuilt);----order by doing the ordering the result by yearbuilt;

select ListingID,Address from properties where address like '%apple%' or address like '%peach%'; --------------like operator for checking apple or peach in address---------

select ListingID,Address,ListPrice from properties p,subdivision s where p.SubDivisionID=s.SubDivisionID? and s.Pool is not null;-----innner joining with subdivision----

select ListingID,,Construction.avg(ListPrice) AvgListPrice from properties p,subdivision s,Agents a where p.SubDivisionID=s.SubDivisionI and p.ListintAgent=a.AgentID and a.LastName='Hart' and s.RecreationCenter is not null and order by(AvgListPrice);-------inner joining with agent and subdivison---

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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