A Guide to SQL
9th Edition
ISBN: 9781111527273
Author: Philip J. Pratt
Publisher: Course Technology Ptr
expand_more
expand_more
format_list_bulleted
Concept explainers
Textbook Question
Chapter 7, Problem 3SCG
Create a view named CONDO_FEES. It consists of two columns: the first is the square footage, and the second is the average fee for all condos in the CONDO_UNIT table that have that square footage. Use AVERAGE_FEE as the column name for the average fee. Group and order the rows by square footage.
- a. Write and execute the CREATE VIEW command to create the CONDO_FEES view.
- b. Write and execute the command to retrieve the square footage and average fee for each square footage for which the average fee is greater than $500.
- c. Write and execute the query that the DBMS actually executes.
- d. Does updating the
database through this view create any problems? If so, what are they? If not, why not?
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
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.
2
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.
I am working on Task 7.
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.
Here is Tasks 1-7 and what I wrote for Task 8.
When I input Task 8, I am not getting results at all.
Expected Results
Tables_in_StayWell
Table_type
MONTHLY_RENTS
VIEW
Chapter 7 Solutions
A Guide to SQL
Ch. 7 - What is a view?Ch. 7 - Which command creates a view?Ch. 7 - Prob. 3RQCh. 7 - What happens when a user retrieves data from a...Ch. 7 - What are three advantages of using views?Ch. 7 - Which command deletes a view?Ch. 7 - Prob. 8RQCh. 7 - Which command terminates previously granted...Ch. 7 - Prob. 10RQCh. 7 - How do you create an index? How do you create a...
Ch. 7 - Prob. 12RQCh. 7 - Does the DBMS or the user make the choice of which...Ch. 7 - Describe the information the DBMS maintains in the...Ch. 7 - The CUSTOMER table contains a foreign key,...Ch. 7 - Prob. 16RQCh. 7 - Prob. 17RQCh. 7 - Prob. 18RQCh. 7 - Prob. 19RQCh. 7 - When would you usually specify primary key...Ch. 7 - Prob. 21RQCh. 7 - Prob. 22RQCh. 7 - Prob. 23RQCh. 7 - Use SQL to make the following changes to the TAL...Ch. 7 - Create a view named ITEM_ORDER. It consists of the...Ch. 7 - Create a view named ORDER_TOTAL. It consists of...Ch. 7 - Write, but do not execute, the commands to grant...Ch. 7 - Prob. 5TDCh. 7 - Perform the following tasks: a. Create an index...Ch. 7 - Delete the index named ITEM_INDEX3.Ch. 7 - Write the commands to obtain the following...Ch. 7 - Prob. 9TDCh. 7 - Prob. 10TDCh. 7 - Toys Galore currently has a credit limit of 7,500....Ch. 7 - Use SQL to make the following changes to the...Ch. 7 - Create a view named RESERVATION_CUSTOMER. It...Ch. 7 - Create a view named TRIP_INVENTORY. It consists of...Ch. 7 - Write, but do not execute, the commands to grant...Ch. 7 - Prob. 5CATCh. 7 - Create the following indexes: a. Create an index...Ch. 7 - Prob. 7CATCh. 7 - Write the commands to obtain the following...Ch. 7 - Prob. 9CATCh. 7 - Ensure that the only legal values for the TYPE...Ch. 7 - Prob. 11CATCh. 7 - Use SQL to make the following changes to the...Ch. 7 - Create a view named CONDO_OWNERS. It consists of...Ch. 7 - Create a view named CONDO_FEES. It consists of two...Ch. 7 - Write, but do not execute, the commands to grant...Ch. 7 - Prob. 5SCGCh. 7 - Prob. 6SCGCh. 7 - Delete the OWNER_INDEX 3 index from the OWNER...Ch. 7 - Write the commands to obtain the following...Ch. 7 - Prob. 9SCGCh. 7 - Ensure that the only legal values for the BDRMS...Ch. 7 - Prob. 11SCG
Additional Engineering Textbook Solutions
Find more solutions based on key concepts
Leap Year Detector Design a program that asks the user to enter a year, and then displays a message indicating ...
Starting Out with Programming Logic and Design (5th Edition) (What's New in Computer Science)
Using your text editor, enter (that is, type in) the C++ program shown in Display 1.8. Be certain to type the f...
Problem Solving with C++ (10th Edition)
How does a computers main memory differ from its auxiliary memory?
Java: An Introduction to Problem Solving and Programming (7th Edition)
Are reflex actions (such as flinching from a hot stove) rational? Are they intelligent?
Artificial Intelligence: A Modern Approach
What are the design issues for character string types?
Concepts Of Programming Languages
Look at the following description of a problem domain:
Starting Out with Java: From Control Structures through Data Structures (3rd Edition)
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.Similar questions
- Create a view named CONDO_OWNERS. It consists of the location number, unit number, square footage, bedrooms, baths, condo fee, and owners last name for every condo inwhich the number of bedrooms is three. a. Write and execute the CREATE VIEW command to create the CONDO_OWNERSview. b. Write and execute the command to retrieve the location number, unit number,condo fee, square footage, and owners last name for every condo in theCONDO_OWNERS view with a condo fee of less than 500. c. Write and execute the query that the DBMS actually executes. d. Does updating the database through this view create any problems? If so, what arethey? If not, why not?arrow_forwardWhich of the following commands drops any columns marked as unused from the SECUSTOMERORDERS table? DROP COLUMN FROM secustomerorders WHERE column_status = UNUSED ALTER TABLE secustomerorders DROP UNUSED COLUMNS; ALTER TABLE secustomerorders DROP (unused); DROP UNUSED COLUMNS;arrow_forwardTask 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.arrow_forward
- The Magazine table contains three fields. The Cost field is numeric. The Code and Name fields contain text. Write a SQL SELECT statement that arranges the records in descending order by the Cost field. Write a SQL SELECT statement that selects only the Name and Cost fields from records having a code of PG10. Write a SQL SELECT statement that selects only the Name and Cost fields from records having a cost of $3 or more. Write a SQL SELECT statement that selects the Visual Basic record. Write a SQL SELECT statement that selects only the Name field from records whose magazine name begins with the letter C. Write a SQL SELECT statement that selects only the Name field from records whose magazine name contains two characters. Write a SQL SELECT statement that selects only the Name and Cost fields from records whose cost is from $4 to $6, inclusive. Write a SQL SELECT statement that selects only records for the Code provided by the user. Write a SQL SELECT statement that selects only…arrow_forwardThe Magazine table contains three fields. The Cost field is numeric. The Code and Name fields contain text. Write a SQL SELECT statement that arranges the records in descending order by the Cost field. Write a SQL SELECT statement that selects only the Name and Cost fields from records having a code of PG10. Write a SQL SELECT statement that selects only the Name and Cost fields from records having a cost of $3 or more. Write a SQL SELECT statement that selects the Visual Basic record. Write a SQL SELECT statement that selects only the Name field from records whose magazine name begins with the letter C. Write a SQL SELECT statement that selects only the Name field from records whose magazine name contains two characters. Write a SQL SELECT statement that selects only the Name and Cost fields from records whose cost is from $4 to $6, inclusive. Write a SQL SELECT statement that selects only records for the Code provided by the user. Write a SQL SELECT statement that selects only…arrow_forwardViews: Create a view “customer_guangzhou”, it indicates the customers from the city “guangzhou”; Create a view “ordersum_goods”, it indicates the total quantity of the orders of each product (goods).arrow_forward
- Which of the following statements can be used to display data using the view called "VIEW_PAYE_Kenya"? a.SELECT * FROM VIEW_PAYE_Kenya; b.CALL VIEW_PAYE_Kenya();arrow_forwardCreate a VIEW named MAJOR_CUSTOMER. It consists of the customer ID, first name, last name, balance, credit limit, and rep ID for every customer whose credit limit is $500 or less.arrow_forwardCreate a view named ORDER_TOTAL. It consists of the order number and order total for each order currently on file. (The order total is the sum of the number of units ordered multiplied by the quoted price on each order line for each order.) Sort the rows by order number. Use TOTAL_AMOUNT as the name for the order total. a.Write and execute the CREATE VIEW command to create the ORDER_TOTAL view. b.Write and execute the command to retrieve the order number and order total for only those orders totaling more than $500. c.Write and execute the query that the DBMS actually executes. d.Does updating the database through this view create any problems? If so, what are they? If not, why not?arrow_forward
- Task 7: Create a view named INVOICE_TOTAL. It consists of the invoice number and invoice total for each invoice currently on file. (The invoice total is the sum of the number of units ordered multiplied by the quoted price on each invoice line for each invoice.) Sort the rows by invoice number. Use TOTAL_AMOUNT as the name for the invoice total. Task 8: Using the INVOICE_TOTAL VIEW write and execute the command to retrieve the invoice number and invoice total for only those orders totaling more than $250. 1 Task 9: Repeat Task 8 without using the INVOICE_TOTAL VIEW. Task 10: List all the tables contained within the system catalog. 1 0 out of 1 checks passed. Review the results below for more details. Checks Custom TestIncomplete Complete task 10 Task 11: List all the columns contained within the system catalog. 1 0.00 out of 10.00 Task 12: List all the views contained within the system catalog.arrow_forwardThe Magazine table contains three fields. The Cost field is numeric. The Code and Name fields contain text. Write a SQL SELECT statement that selects the Visual Basic record. Write a SQL SELECT statement that selects only the Name field from records whose magazine name begins with the letter C. Write a SQL SELECT statement that selects only the Name field from records whose magazine name contains two characters.arrow_forwardTask 2: Write and execute the command to retrieve the office number, property ID, and monthly rent for every property in the SMALL_PROPERTY view with a monthly rent of $1150 or more. This is my response. However it isn't working when I check the file. Also the table isn't popping up like the other completed queries, can you let me know where I went wrong and provide correct reponse. Thanks, CREATE VIEW TASK2 AS SELECT OFFICE_NUM, PROPERTY_ID, MONTHLY_RENTFROM SMALL_PROPERTYWHERE MONTHLY_RENT < 1150;arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrProgramming with Microsoft Visual Basic 2017Computer ScienceISBN:9781337102124Author:Diane ZakPublisher:Cengage Learning
- Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
dml in sql with examples; Author: Education 4u;https://www.youtube.com/watch?v=WvOseanUdk4;License: Standard YouTube License, CC-BY