Task 7: Create a view named MONTHLY_RENTS. It consists of three columns: the first is the number of bedrooms, the second is the average square feet, and the third is the average monthly rent for all properties in the PROPERTY table that have that number of bedrooms. Use BEDROOMS, AVG_SQUARE_FEET, and AVG_MONTHLY_RENT as the column names. Group and order the rows by number of bedrooms.Task 8: Write and execute the command to retrieve the average square footage and average monthly rent for each property for which the average monthly rent is greater than $1,400.Task 9: Without using the MONTHLY_RENTS VIEW, write and execute the command to retrieve the average square footage and average monthly rent for each property for which the average monthly rent is less than $1,400. OFFICE tableStayWell stores information about the owners of each property in the OWNER table. Each owner isidentified by a unique owner number that consists of two uppercase letters followed by a three-digit number. For each owner, the table also includes the last name, first name, address, city, state,and ZIP code. Notice the owners are from across the United States. Although some apartmentsmay be owned by a couple or a family, only the primary contact is given.OWNER_NUMLAST_NAMEFIRST_NAMEADDRESSCITYSTATEZIP_CODEAK102AksoyCeyda411 Griffin Rd.SeattleWA98131BI109BianchiNicole7990 Willow Dr.New YorkNY10005BU106BurkeErnest613 Old Pleasant St.Twin FallsID83303Co103ColeMeerab9486 Circle Ave.OlympiaWA98506J0110JonesAmmarah730 Military Ave.SeattleWA98126KO104KowalczykJakub7431 S. Bishop St.BellinghamWA98226LO108LopezJanine9856 Pumpkin Hill Ln.EverettWA98213мо100MooreElle-May8006 W. Newport Ave.RenoNV89508PA101PatelMakesh7337 Sheffield St.SeattleWA98119RE107RedmanSeth7681 Fordham St.SeattleWA98119S1105SimsHaydon527 Primrose Rd.PortlandOR97203OWNER tableEach property at each location is identified by a property ID, as seen in the PROPERTY table.Each property also includes the office number that manages the property, address, floor size, thenumber of bedrooms, the number of floors, monthly rent per property, and the owner number. ThePROPERTY_ID is an integer unique for each property.PROPERTY IDOFFICE NUMADDRESSSQR_FTBDRMSFLOORSMONTHLY_RENTOWNER_NUM130 West Thomas Rd.160031400BU1061782 Queen Ln.210041900AK1029800 Sunbeam Ave.100521200BI1094105 North Illinois Rd.175031650ко1041887 Vine Rd.112511160SI105618 Laurel Dr.2125422050MO1007447 Goldfield St.167521700Co1038594 Leatherwood Dr.27002750кО104504 Windsor Ave.70011050PA101 The RESIDENTS table includes details about the residents living in each property. TheRESIDENTS table includes the first name and surname (last name) for each of the residents,along with a resident ID. The PROPERTY_ID is the unique identification number of the property inwhich they are staying.RESIDENT_IDFIRST_NAMESURNAMEPROPERTY_ID1AlbieODRyan12TariqKhan3IsmailSalib14CallenBeckMiloszPolansky6AshantiLucas2RandyWoodrue8AislinnLawrence9MoniqueFrench310AmaraDejsuwan4RESIDENTS tableThe SERVICE_REQUEST table shows requests that residents have put into the offices formaintenance. Each row contains a unique service ID number, the property ID, the categorynumber associated with the type of work, the office managing the property, a description of therequest, the current status of the request, the estimated hours to complete the request, the hoursspent on the request, and the scheduled service date.SERVICE ID PROPERTY_ID CATEGORY_NUMBER OFFICE_ID DESCRIPTIONEST HOURS SPENT HOURS NEXT SERVICE_DATESTATUSProblem has been confirmed. central heating11The second bedroom upstairs is not heating up at night.212019-11-01engineer has been scheduled.2.1.1.A new strip light is needed for the kitchen.Scheduled2019-10-02Service rep has confirmed issue. Scheduled to berefitted.361The bathroom door does not close properly.312019-11-09New outlet has been requested for the first upstairs bedroom.(There is currently no outlet).4.241Scheduled2019-10-0283.2New paint job requested for the common area (lounge).Open10NULL6.41Shower is dripping when not in use.Problem confirmed. Plumber has been scheduled.422019-10-07Service rep confirmed the issue to be dust in theheating unit. To be cleaned.7.21Heating unit in the entrance smells like itos burning.2019-10-09892Kitchen sink does not drain properly.Problem confirmed. Plumber scheduled.6.2019-11-12122New sofa requested.OpenNULL

Views in SQL correspond to a virtual table consisting of rows and columns. It represents result set…

Answered: Task 7: Create a view named…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

100%

Task 7: Create a view named MONTHLY_RENTS. It consists of three columns: the first is the number of bedrooms, the second is the average square feet, and the third is the average monthly rent for all properties in the PROPERTY table that have that number of bedrooms. Use BEDROOMS, AVG_SQUARE_FEET, and AVG_MONTHLY_RENT as the column names. Group and order the rows by number of bedrooms.

Task 8: Write and execute the command to retrieve the average square footage and average monthly rent for each property for which the average monthly rent is greater than $1,400.

Task 9: Without using the MONTHLY_RENTS VIEW, write and execute the command to retrieve the average square footage and average monthly rent for each property for which the average monthly rent is less than $1,400.

OFFICE table
StayWell stores information about the owners of each property in the OWNER table. Each owner is
identified by a unique owner number that consists of two uppercase letters followed by a three-
digit number. For each owner, the table also includes the last name, first name, address, city, state,
and ZIP code. Notice the owners are from across the United States. Although some apartments
may be owned by a couple or a family, only the primary contact is given.
OWNER_NUM
LAST_NAME
FIRST_NAME
ADDRESS
CITY
STATE
ZIP_CODE
AK102
Aksoy
Ceyda
411 Griffin Rd.
Seattle
WA
98131
BI109
Bianchi
Nicole
7990 Willow Dr.
New York
NY
10005
BU106
Burke
Ernest
613 Old Pleasant St.
Twin Falls
ID
83303
Co103
Cole
Meerab
9486 Circle Ave.
Olympia
WA
98506
J0110
Jones
Ammarah
730 Military Ave.
Seattle
WA
98126
KO104
Kowalczyk
Jakub
7431 S. Bishop St.
Bellingham
WA
98226
LO108
Lopez
Janine
9856 Pumpkin Hill Ln.
Everett
WA
98213
мо100
Moore
Elle-May
8006 W. Newport Ave.
Reno
NV
89508
PA101
Patel
Makesh
7337 Sheffield St.
Seattle
WA
98119
RE107
Redman
Seth
7681 Fordham St.
Seattle
WA
98119
S1105
Sims
Haydon
527 Primrose Rd.
Portland
OR
97203
OWNER table
Each property at each location is identified by a property ID, as seen in the PROPERTY table.
Each property also includes the office number that manages the property, address, floor size, the
number of bedrooms, the number of floors, monthly rent per property, and the owner number. The
PROPERTY_ID is an integer unique for each property.
PROPERTY ID
OFFICE NUM
ADDRESS
SQR_FT
BDRMS
FLOORS
MONTHLY_RENT
OWNER_NUM
1
30 West Thomas Rd.
1600
3
1400
BU106
1
782 Queen Ln.
2100
4
1900
AK102
9800 Sunbeam Ave.
1005
2
1200
BI109
4
105 North Illinois Rd.
1750
3
1650
ко104
1
887 Vine Rd.
1125
1
1160
SI105
6
1
8 Laurel Dr.
2125
4
2
2050
MO100
7
447 Goldfield St.
1675
2
1700
Co103
8
594 Leatherwood Dr.
2700
2750
кО104
504 Windsor Ave.
700
1
1050
PA101

The RESIDENTS table includes details about the residents living in each property. The
RESIDENTS table includes the first name and surname (last name) for each of the residents,
along with a resident ID. The PROPERTY_ID is the unique identification number of the property in
which they are staying.
RESIDENT_ID
FIRST_NAME
SURNAME
PROPERTY_ID
1
Albie
ODRyan
1
2
Tariq
Khan
3
Ismail
Salib
1
4
Callen
Beck
Milosz
Polansky
6
Ashanti
Lucas
2
Randy
Woodrue
8
Aislinn
Lawrence
9
Monique
French
3
10
Amara
Dejsuwan
4
RESIDENTS table
The SERVICE_REQUEST table shows requests that residents have put into the offices for
maintenance. Each row contains a unique service ID number, the property ID, the category
number associated with the type of work, the office managing the property, a description of the
request, the current status of the request, the estimated hours to complete the request, the hours
spent on the request, and the scheduled service date.
SERVICE ID PROPERTY_ID CATEGORY_NUMBER OFFICE_ID DESCRIPTION
EST HOURS SPENT HOURS NEXT SERVICE_DATE
STATUS
Problem has been confirmed. central heating
11
The second bedroom upstairs is not heating up at night.
2
1
2019-11-01
engineer has been scheduled.
2.
1.
1.
A new strip light is needed for the kitchen.
Scheduled
2019-10-02
Service rep has confirmed issue. Scheduled to be
refitted.
3
6
1
The bathroom door does not close properly.
3
1
2019-11-09
New outlet has been requested for the first upstairs bedroom.
(There is currently no outlet).
4.
2
4
1
Scheduled
2019-10-02
8
3.
2
New paint job requested for the common area (lounge).
Open
10
NULL
6.
4
1
Shower is dripping when not in use.
Problem confirmed. Plumber has been scheduled.
4
2
2019-10-07
Service rep confirmed the issue to be dust in the
heating unit. To be cleaned.
7.
2
1
Heating unit in the entrance smells like itos burning.
2019-10-09
8
9
2
Kitchen sink does not drain properly.
Problem confirmed. Plumber scheduled.
6.
2019-11-12
12
2
New sofa requested.
Open
NULL

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 4 steps

SEE SOLUTION Check out a sample Q&A here

Follow-up Questions

Read through expert solutions to related follow-up questions below.

Follow-up Question

This was the answer I calculated too for Task 8: Write and execute the command to retrieve the average square footage and average monthly rent for each property for which the average monthly rent is greater than $1,400.

The answer expected is not the answer that was generated. Why isn't the correct answer and why isthe generated and expected different. Thanks

My answer

SELECT AVG(SQR_FT) AS AVG_SQUARE_FEET, AVG(MONTHLY_RENT) AS AVG_MONTHLY_RENT

FROM PROPERTY

GROUP BY PROPERTY_ID

HAVING AVG(MONTHLY_RENT) > 1400;

AVG_SQUARE_FEET	AVG_MONTHLY_RENT
2100.0000	1900.0000
1750.0000	1650.0000
2125.0000	2050.0000
1537.5000	1700.0000
2700.0000	2750.0000
1300.0000	1600.0000

Expected Results

AVG_SQUARE_FEET	AVG_MONTHLY_RENT
1545.0000	1610.0000
2112.5000	1975.0000
2700.0000	2750.0000

Solution

by Bartleby Expert

SEE SOLUTION

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.