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 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.
Create 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';
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 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 thathave 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 foreach 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 arethey? If not, why not?arrow_forwardCreate 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 that lists the ISBN and title for each book in inventory along with the name and phone number of the person to contact if the book needs to be reordered. Name the view REORDERINFO.arrow_forward
- Task 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_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 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_forward
- Task 5: Write and execute the command to retrieve all information for a property for every property in the PROPERTY_OWNERS view with a monthly rent of less than $1675. Task 6: Repeat Task 5 without using the PROPERTY_OWNERS VIEW.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 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_forward
- Create a view named myView_757 (last 3 digits of your student number) That view should display employee_id, first_name,last_name & department ID from the Employees table Create the view Show that the view is created Run the view and show the result CREATE TABLE employees ( employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, first_name VARCHAR2 (20) DEFAULT NULL, last_name VARCHAR2 (25) NOT NULL, email VARCHAR2 (100) NOT NULL, phone_number VARCHAR2 (20) DEFAULT NULL, hire_date DATE NOT NULL, job_id NUMBER NOT NULL, salary NUMBER (8, 2) NOT NULL, manager_id NUMBER DEFAULT NULL, department_id NUMBER DEFAULT NULL ) INSERT INTO employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,manager_id,department_id) VALUES (100,'Steven','King','steven.king@sqltutorial.org','515.123.4567',DATE '1987-06-17',4,24000.00,NULL,9);INSERT INTO…arrow_forwardCreate a view named NHTrips. It consists of the trip ID, trip name, start location, distance, maximum group size, type, and season for every trip located in New Hampshire (NH). Display the data in the viewarrow_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
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