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 4SCG
Write, but do not execute, the commands to grant the following privileges:
- a. User Oliver must be able to retrieve data from the CONDO_UNIT table.
- b. Users Crandall and Perez must be able to add new owners and condo units to the
database . - c. Users Johnson and Klein must be able to change the condo fee of any unit.
- d. All users must be able to retrieve the unit number, condo fee, and owner number for every condo unit.
- e. User Klein must be able to add and delete service categories.
- f. User Adams must be able to create an index on the SERVICE_REQUEST table.
- g. Users Adams and Klein must be able to change the structure of the CONDO_UNIT table.
- h. User Klein must have all privileges on the LOCATION, OWNER, and CONDO_UNIT tables.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
PLZ help with the following:
The manager wants to add a new painter as an artist in the database. What would the insert statement look like?
For a painter to be considered in the Artist database, the painter must have painted at least one painting, however, a painting might not be exhibited in a gallery. Based on these business rules, please insert one painting for the painter that you inserted in Q1.
sql file
CREATE TABLE GALLERY (GAL_NUM char(4),GAL_OWNER varchar(35),GAL_AREACODE char(3),GAL_PHONE char(8),GAL_RATE number);INSERT INTO GALLERY VALUES('5','L. R. Gilliam','901','123-4456',0.37);INSERT INTO GALLERY VALUES('6','G. G. Waters','405','353-2243',0.45 );INSERT INTO GALLERY VALUES('1','N. D. Cosner','203','123-9956',0.67);INSERT INTO GALLERY VALUES('2','S. H. Artwork','415','154-3243',0.30);
/* -- */
CREATE TABLE PAINTER (PTR_NUM char(4),PTR_LASTNAME varchar(15) NOT NULL,PTR_FIRSTNAME varchar(15) NOT NULL,PTR_AREACODE char(3),PTR_PHONE char(8));INSERT INTO PAINTER…
Create a table in your own database using the following statement. CREATE TABLE DateRange (DateID INT IDENTITY, DateValue DATE, DayOfWeek SMALLINT, Week SMALLINT, Month SMALLINT, Quarter SMALLINT, Year SMALLINT ); Write a stored procedure that accepts two parameters: A starting date The number of the consecutive dates beginning with the starting date The stored procedure then inserts data into all columns of the DateRange table according to the two provided parameters.
You have a table that contains the following fields: MemberLastName, MemberFirstName, Street, City, State, ZipCode, and MembershipFee. There are 75,000 records in the table. What indexes would you create for the table, and why would you create these indexes?
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
The following C++ program will not compile because the lines have been mixed up. cout Success\n; cout Success...
Starting Out with C++ from Control Structures to Objects (8th Edition)
Run the hello, world program on your system. Experiment with leaving out parts of the program, to see what erro...
C Programming Language
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)
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++ (9th Edition)
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
- Which of the following data dictionary objects should be used to view information about the constraints in a database? USER_TABLES USER_RULES USER_COLUMNS USER_CONSTRAINTS None of the above objects should be used.arrow_forwardsimple database composed following les: PATI DRUG, and PRESCRIPTION, is now loaded with this assignment and you are ready to start. Please explore the database and then write the appropriate SQL command to answer the following questions. 1. List all the rows in which the prescription dates occur on or after November 14. 2. How many doctors provide prescriptions with 30 tablets in total? 3. List doctor's details where specialty is either Dermatology or Neurology. (Use IN operator). List the results by the first name in ascending order and the last name in descending order 4. Find all patients rows whose first names include 'George' (Assume case sensitivity). 5. List number of patients that do not have a phone number. 6. How many drugs with price < 40$? 7. How many prescriptions are generated for each patient, limit the listing to patients who have more than 2 prescriptions? 8. List number of female patients born in 1970 9. List the drug codes and prices (rounded to one decimal place).…arrow_forwardTask 3: Create(Enter) new product by using a Procedure with IN Parameters Follow these steps to create a procedure that allows a company employee to add a new product to the database. This procedure needs only IN parameters. In SQL Developer, create a procedure named PROD_ADD_SP that adds a row for a new product in the BB_PRODUCT table. Keep in mind that the user provides values for the product name, description, image filename (use same template for file name as ‘roasted.jpg’ or ‘double-roasted.jpg’ ) , price, and active status. Address the input values or parameters in the same order as in the preceding sentence. Call the procedure with these parameter values: ('Roasted Blend', 'Well-balanced mix of roasted beans, a medium body', 'roasted.jpg',9.50,1). Check whether the update was successful by querying the BB_PRODUCT table. -This will be a page where you allow new product information to be entered into Edit box by end user and then there will be INSERT or ADD Product…arrow_forward
- Task 2: The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENANCE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys. SQL DataBase Test: Create a new table to store maintenance operations Test Query: DESCRIBE MAINTENANCES Expected Results Field Type Null Key Default Extra CAR_ID char(5) NO PRI NULL MAINTENANCE_TYPE_ID char(5) NO PRI NULL MAINTENANCE_DUE date NO PRI NULLarrow_forwardTask 2: The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENANCE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys. Create a new table to store maintenance operations Test Query DESCRIBE MAINTENANCES Expected Results Field Type Null Key Default Extra CAR_ID char(5) NO PRI NULL MAINTENANCE_TYPE_ID char(5) NO PRI NULL MAINTENANCE_DUE date NO PRI NULLarrow_forwardPlease written by computer source 1. For this project, you need to create the company database and populate it with the data given in the Oracle SQL PLUS environment (if you have not done so) 2. Your SQL commands should be placed in file(s) and run from the file(s). 3. Implement Triggers on all the tables to change character data to uppercase in the character data type columns. 4. Implement a Function that returns Dname from Department for any give employee. The parameter to the function would be the SSN. 5. Implement a Function that returns manager’s full name for any given department. The parameter to the function would be the department name. 6. Implement another Function that returns manager’s full name for any given department. The parameter to the function would be the department number. 7. Implement a Procedure that increases an employee salary by x%. Employee is identified by SSN and the percentage of increase is given as an input. 8. Implement a Package that contains the…arrow_forward
- Create a PL/SQL anonymous block to insert a new project in DoGood Donor database. Create and use a sequence to handle generating and populating the project ID. The first number issue by the sequence should be 800, and no caching should be used. Use a record variable to handle the data to be added. Data for the new row should be the following: project name is “Covid-19 relief fund”, start date: Feb 1, 2023, end date: Jun 30, 2023, and fundraising goal is half million. Any columns not addressed in the data list are currently unknown.arrow_forwardCreate a PL/SQL anonymous block to insert a new project in DoGood Donor database. Create and use a sequence to handle generating and populating the project ID. The first number issue by the sequence should be 600, and no caching should be used. Use a record variable to handle the data to be added. Data for the new row should be the following: project name is “Covid-19 relief fund”, start date: Feb 1, 2022, end date: Jun 30, 2022, and fundraising goal is half million. Any columns not addressed in the data list are currently unknown. 2.Create anonymous block to retrieve and display data for all pledges made in a specified month. One row of output should be displayed for each pledge. More specifically, each row include: Pledge ID, donor ID, and pledge amount If the pledge is being paid in a lump sum, display “Lump Sum” If the pledge is being paid in monthly, display “Monthly ** ” followed by number of months for payment The list should be sorted to display all lump sum pledges firstarrow_forwardTask 2: The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENACNE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys. Answer in MYSQL pleasearrow_forward
- Task 6: ' The Car Maintenance team wants to update the price of Oil Change to 75. In addition, they will no longer provide or require a Gas Pump Change in the future and there is no need to keep the maintenance item in the database. Update the cost of the oil change and remove the gas pump change option from the MAINTENANCE_TYPES table. ANSWER IN MYSQL PLEASEarrow_forwardTask 6: The Car Maintenance team considered that the available maintenance tasks should also have the price information in the database. Alter the MAINTENANCE_TYPES table to include a new column named MAINTENANCE_PRICE of type FLOAT. Alter the MAINTENANCE_TYPES table to include pricing information.arrow_forwardTask 3: The InstantRide Finance team wants to collect the price and discount information with the driver names for each travel in the system. You need to return the TRAVEL_ID, DRIVER_FIRST_NAME, DRIVER_LAST_NAME, TRAVEL_PRICE, and TRAVEL_DISCOUNT information from the TRAVELS and DRIVERS tables combined over DRIVER_ID field with the ON keyword. Task: Calculate each user's price and discount information. (SQL Database Test)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