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 2SCG
Create a view named CONDO_OWNERS. It consists of the location number, unit number, square footage, bedrooms, baths, condo fee, and owner’s last name for every condo in which the number of bedrooms is three.
- a. Write and execute the CREATE VIEW command to create the CONDO_OWNERS view.
- b. Write and execute the command to retrieve the location number, unit number, condo fee, square footage, and owner’s last name for every condo in the CONDO_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 are they? If not, why not?
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
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.
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.
Create 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?
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)
What are the design issues for character string types?
Concepts Of Programming Languages
The job of the _____ is to fetch instructions, carry out the operations commanded by the instructions, and prod...
Starting Out With Visual Basic (8th Edition)
Define each of the following terms: determinant functional dependency transitive dependency recursive foreign k...
Modern Database Management
Which of the following are illegal variable names in Python, and why? x 99bottles july2009 theSalesFigureForFis...
Starting Out with Python (3rd Edition)
In Problems 1 through 10, find a function y=f(x) satisfying the given differential equation and the prescribed ...
Differential Equations: Computing and Modeling (5th Edition), Edwards, Penney & Calvis
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 called HOMEWORK13 that includes the columns named Col1 and Col2 from the FIRSTATTEMPT table. Make sure the view is created even if the FIRSTATTEMPT table doesn’t exist.arrow_forwardCreate 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. 2arrow_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
- 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_forwardWrite and execute the command to retrieve the customer ID, first name, and last name of each customer in the MAJOR_CUSTOMER VIEW with a balance that exceeds the credit limit.arrow_forwardCreate a view named RESERVATION_CUSTOMER. It consists of the reservation ID, tripID, trip date, customer number, customer last name, customer first name, and phonenumber. Write and execute the command to retrieve the reservation ID, trip ID, trip date, andcustomer last name for every reservation in the RESERVATION_CUSTOMER viewwith a trip date of September 11 , 2016. WHAT AM I MISSING OR HAVE INCORRECT ? SELECT Reservation_ID, Trip_ID, Trip_Date, Last_Name FROM RESERVATION_CUSTOMER WHERE TRIP_DATE = '09-SEP-2016';arrow_forward
- Views: 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_forwardCreate a view named TopLevelCust view. It consists of the number, name, address, balance, and credit limit ofall clients with credit limits that are greater than or equal to $10,000. Display the data in the view.arrow_forwardCreate a VIEW named ITEM_INVOICE. It consists of the item ID, description, price, invoice number, invoice date, quantity, and quoted price for all invoice lines currently on file.arrow_forward
- Task 9: Repeat Task 8 without using the INVOICE_TOTAL VIEW. (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.)arrow_forwardCreate a view named ITEM_INVOICE. It consists of the item ID, description, price, invoice number, invoice date, number ordered, and quoted price for all invoice lines currently on file.arrow_forwardWhich 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_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 Ptr
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
dml in sql with examples; Author: Education 4u;https://www.youtube.com/watch?v=WvOseanUdk4;License: Standard YouTube License, CC-BY