Make your own free website on Tripod.com

CHAPTER 5

5-1 Answers:

1- properties

2- NIK4877

3- DAC

4- INSERT INTO properties ( PropertyID, TypeOfHouse, YearBuilt, NumBedrooms, NumBathrooms, Area, Rooms,OtherFeatures, Price, Location , ZipCode, ImageFilename, AgentID) values( 'NIK4877', 'Two -story home',1975,3,2.5,2100,'MASTER BEDROOM , LANUDRAY','HARDWOOD , PANTRY',121621,' Cedar Falls',50613,'NA.spg','DAC')

 

5-3

1- 311

2-119

3-187

4-13

1-SELECT count(propertyID)
FROM Properties;

 

2-SELECT count(propertyID)
FROM Properties
WHERE Location like 'Cedar Rapids';

 

3-SELECT count(propertyID)
FROM Properties
WHERE Location like 'Cedar Rapids' or Location Like 'Waterloo';

 

4-SELECT count(propertyID)
FROM Properties
WHERE Price > 300000 AND NumBedrooms >= 5;

 

PROJECT 5-4

 

PROJECT 5-5 Answers:

1-

lastName numproperties
Adachi 14
Callejo 34
Carter 41
Hadik 36
Kernan 14
LeFevre 34
Little 6
Lowell 18
Petrie 17
Przyborski 46
Stout 30
Talati 21

2-

lastName Average Maximum Minimum Total
Adachi $208,874.21 $332,872.00 $117,019.00 $2,924,239.00
Callejo $205,780.62 $415,153.00 $98,709.00 $6,996,541.00
Carter $201,178.07 $395,079.00 $92,134.00 $8,248,301.00
Hadik $224,139.19 $534,388.00 $116,252.00 $8,069,011.00
Kernan $212,122.71 $306,046.00 $110,060.00 $2,969,718.00
LeFevre $211,785.29 $450,871.00 $107,355.00 $7,200,700.00
Little $229,343.17 $315,360.00 $115,242.00 $1,376,059.00
Lowell $192,724.94 $457,640.00 $79,576.00 $3,469,049.00
Petrie $194,984.59 $334,755.00 $116,156.00 $3,314,738.00
Przyborski $209,206.46 $456,245.00 $82,467.00 $9,623,497.00
Stout $210,353.77 $450,000.00 $93,592.00 $6,310,613.00
Talati $209,620.14 $470,607.00 $71,424.00 $4,402,023.00

3-lastName numproperties location
Callejo 1 Cedar Rapids
Hadik 36 Cedar Rapids
Kernan 1 Cedar Rapids
LeFevre 33 Cedar Rapids
Little 1 Cedar Rapids
Przyborski 46 Cedar Rapids
Stout 1 Cedar Rapids

 

1-

SELECT a.LastName, Count(p.PropertyID) AS numproperties
FROM Agents AS a, properties AS p
WHERE (((a.AgentID)=[p].[AgentID]))
GROUP BY a.LastName;

2-SELECT a.lastName, Avg(p.price) AS Average, max(p.price) AS Maximum, min(p.price) AS Minimum, sum(p.price) AS Total
FROM Agents AS a, properties AS p
WHERE a.AgentID = p.AgentID
GROUP BY a.lastName;

3-SELECT a.lastName, count(p.PropertyID) AS numproperties, p.location AS location
FROM Agents AS a, properties AS p
WHERE a.AgentID = p.AgentID and p.location = 'Cedar Rapids'
GROUP BY a.lastName, p.location;