Database Systems: Design, Implementation, & Management
13th Edition
ISBN: 9781337627900
Author: Carlos Coronel, Steven Morris
Publisher: Cengage Learning
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 8, Problem 55P
Update the VID_STATUS attribute of the VIDEO table using a subquery to set the VID_STATUS to “OUT” for all videos that have a null value in the DETAIL_ RETURNDATE attribute of the DETAILRENTAL table.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Create a new table maned “Hiking” that includes TripID, NumPersons from Reservation table and Trip Type, Trip Name columns from Trip table where Trip type is “Hiking” .
Insert at least three rows into your EMPLOYEE_xx table. Use the next value of the sequence you created , EMPL_SEQUENCE_xx as the value for the employee ID for every row inserted. You may choose values for the other columns
Test your constraint on the rate column by trying to update a row in the EMPLOYEE_xx table with the value of 6 used for the Pay Rate (you choose the row).
Drop your EMPLOYEE_xx table and make sure it HAS been purged from the recyclebin
CREATE TABLE DOCTOR(DOC_ID NUMBER(3),DOC_NAME VARCHAR2(9),DATEHIRED DATE,SALPERMON NUMBER(12),AREA VARCHAR2(20),SUPERVISOR_ID NUMBER(3),CHGPERAPPT NUMBER(3),ANNUAL_BONUS NUMBER(5),CONSTRAINT DOCTOR_DOC_ID_PK PRIMARY KEY (DOC_ID));
INSERT INTO DOCTOR VALUES(432, 'Harrison' , TO_DATE('05-DEC-94'), 12000,'Pediatrics', 100, 75, 4500);
INSERT INTO DOCTOR VALUES(509, 'Vester' , TO_DATE('09-JAN-00'), 8100,'Pediatrics', 432, 40, null);
INSERT INTO DOCTOR VALUES(389, 'Lewis' , TO_DATE('21-JAN-96'), 10000,'Pediatrics', 432, 40, 2250);
INSERT INTO DOCTOR VALUES(504, 'Cotner' , TO_DATE('16-JUN-98'), 11500,'Neurology', 289, 85, 7500);
INSERT INTO DOCTOR VALUES(235, 'Smith' , TO_DATE('22-JUN-98'), 4550,'Family Practice', 100, 25, 2250);
INSERT INTO DOCTOR VALUES(356, 'James' , TO_DATE('01-AUG-98'), 7950,'Neurology', 289, 80, 6500);
INSERT INTO DOCTOR VALUES(558, 'James' , TO_DATE('02-MAY-95'), 9800,'Orthopedics', 876, 85, 7700);
INSERT INTO DOCTOR VALUES(876, 'Robertson' , TO_DATE('02-MAR-95'),…
Chapter 8 Solutions
Database Systems: Design, Implementation, & Management
Ch. 8 - Prob. 1RQCh. 8 - Explain why it might be more appropriate to...Ch. 8 - What is the difference between a column constraint...Ch. 8 - What are referential constraint actions?Ch. 8 - What is the purpose of a CHECK constraint?Ch. 8 - Explain when an ALTER TABLE command might be...Ch. 8 - What is the difference between an INSERT command...Ch. 8 - What is the difference between using a subquery...Ch. 8 - What is the difference between a view and a...Ch. 8 - Prob. 10RQ
Ch. 8 - Prob. 11RQCh. 8 - Prob. 12RQCh. 8 - Write the SQL code that will create only the table...Ch. 8 - Having created the table structure in Problem 1,...Ch. 8 - Prob. 3PCh. 8 - Write the SQL code that will save the changes made...Ch. 8 - Write the SQL code to change the job code to 501...Ch. 8 - Write the SQL code to delete the row for William...Ch. 8 - Write the SQL code to create a copy of EMP_1,...Ch. 8 - Using the EMP_2 table, write the SQL code that...Ch. 8 - Using the EMP_2 table, write the SQL code to...Ch. 8 - Prob. 10PCh. 8 - Prob. 11PCh. 8 - Prob. 12PCh. 8 - Prob. 13PCh. 8 - Prob. 14PCh. 8 - Prob. 15PCh. 8 - Create the CUSTOMER table structure illustrated in...Ch. 8 - Create the INVOICE table structure illustrated in...Ch. 8 - Prob. 18PCh. 8 - Prob. 19PCh. 8 - Create an Oracle sequence named CUST_NUM_SEQ to...Ch. 8 - Create an Oracle sequence named INV_NUM_SEQ to...Ch. 8 - Prob. 22PCh. 8 - Modify the CUSTOMER table to include the customers...Ch. 8 - Prob. 24PCh. 8 - Prob. 25PCh. 8 - Create a trigger named trg_updatecustbalance to...Ch. 8 - Prob. 27PCh. 8 - Prob. 28PCh. 8 - Write a trigger to update the customer balance...Ch. 8 - Prob. 30PCh. 8 - Create a trigger named trg_line_total to write the...Ch. 8 - Create a trigger named trg_line_prod that...Ch. 8 - Create a stored procedure named prc_inv_amounts to...Ch. 8 - Create a procedure named prc_cus_balance_update...Ch. 8 - Modify the MODEL table to add the attribute and...Ch. 8 - Prob. 36PCh. 8 - Modify the CHARTER table to add the attributes...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the sequence of commands required to update...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Write the command required to update the...Ch. 8 - Prob. 43PCh. 8 - Create a trigger named trg_char_hours that...Ch. 8 - Create a trigger named trg_pic_hours that...Ch. 8 - Create a trigger named trg_cust_balance that...Ch. 8 - Write the SQL code to create the table structures...Ch. 8 - The following tables provide a very small portion...Ch. 8 - For Questions 49-63, use the tables that were...Ch. 8 - Prob. 50PCh. 8 - Write a single SQL command to increase all price...Ch. 8 - Alter the DETAILRENTAL table to include a derived...Ch. 8 - Update the DETAILRENTAL table to set the values in...Ch. 8 - Alter the VIDEO table to include an attribute...Ch. 8 - Update the VID_STATUS attribute of the VIDEO table...Ch. 8 - Alter the PRICE table to include an attribute...Ch. 8 - Prob. 57PCh. 8 - Prob. 60P
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
- Add a new row in the ORDERS table with the following data: Order# = 1022, Customer# = 2000, and Order date = August 6, 2009. Describe the error raised and what caused the error.arrow_forwardAdd a constraint to make sure the Rep_ID value entered in the BOOK_STORES table is a valid value contained in the STORE_REPS table. The Rep_ID columns of both tables were initially created as different datatypes. Does this cause an error when adding the constraint? Make table modifications as needed so that you can add the required constraint.arrow_forwardAdd a new row in the ORDERS table with the following data: Order# = 1023 and Customer# = 1009. Describe the error raised and what caused the error.arrow_forward
- Add two columns to the EMPLOYEES table. One column, named EmpDate, contains the date of employment for each employee, and its default value should be the system date. The second column, named EndDate, contains employees date of termination.arrow_forwardCREATE TABLE DOCTOR(DOC_ID NUMBER(3),DOC_NAME VARCHAR2(9),DATEHIRED DATE,SALPERMON NUMBER(12),AREA VARCHAR2(20),SUPERVISOR_ID NUMBER(3),CHGPERAPPT NUMBER(3),ANNUAL_BONUS NUMBER(5),CONSTRAINT DOCTOR_DOC_ID_PK PRIMARY KEY (DOC_ID)); INSERT INTO DOCTOR VALUES(432, 'Harrison' , TO_DATE('05-DEC-94'), 12000,'Pediatrics', 100, 75, 4500); INSERT INTO DOCTOR VALUES(509, 'Vester' , TO_DATE('09-JAN-00'), 8100,'Pediatrics', 432, 40, null); INSERT INTO DOCTOR VALUES(389, 'Lewis' , TO_DATE('21-JAN-96'), 10000, 'Pediatrics', 432, 40, 2250); INSERT INTO DOCTOR VALUES(504, 'Cotner' , TO_DATE('16-JUN-98'), 11500,'Neurology', 289, 85, 7500); INSERT INTO DOCTOR VALUES(235, 'Smith' , TO_DATE('22-JUN-98'), 4550, 'Family Practice', 100, 25, 2250); INSERT INTO DOCTOR VALUES(356, 'James' , TO_DATE('01-AUG-98'), 7950,'Neurology', 289, 80, 6500); INSERT INTO DOCTOR VALUES(558, 'James' , TO_DATE('02-MAY-95'), 9800, 'Orthopedics', 876, 85, 7700); INSERT INTO DOCTOR VALUES(876, 'Robertson' , TO_DATE('02-MAR-95'),…arrow_forwardCREATE TABLE DOCTOR(DOC_ID NUMBER(3),DOC_NAME VARCHAR2(9),DATEHIRED DATE,SALPERMON NUMBER(12),AREA VARCHAR2(20),SUPERVISOR_ID NUMBER(3),CHGPERAPPT NUMBER(3),ANNUAL_BONUS NUMBER(5),CONSTRAINT DOCTOR_DOC_ID_PK PRIMARY KEY (DOC_ID)); INSERT INTO DOCTOR VALUES(432, 'Harrison' , TO_DATE('05-DEC-94'), 12000,'Pediatrics', 100, 75, 4500); INSERT INTO DOCTOR VALUES(509, 'Vester' , TO_DATE('09-JAN-00'), 8100,'Pediatrics', 432, 40, null); INSERT INTO DOCTOR VALUES(389, 'Lewis' , TO_DATE('21-JAN-96'), 10000,'Pediatrics', 432, 40, 2250); INSERT INTO DOCTOR VALUES(504, 'Cotner' , TO_DATE('16-JUN-98'), 11500,'Neurology', 289, 85, 7500); INSERT INTO DOCTOR VALUES(235, 'Smith' , TO_DATE('22-JUN-98'), 4550,'Family Practice', 100, 25, 2250); INSERT INTO DOCTOR VALUES(356, 'James' , TO_DATE('01-AUG-98'), 7950,'Neurology', 289, 80, 6500); INSERT INTO DOCTOR VALUES(558, 'James' , TO_DATE('02-MAY-95'), 9800,'Orthopedics', 876, 85, 7700); INSERT INTO DOCTOR VALUES(876, 'Robertson' , TO_DATE('02-MAR-95'),…arrow_forward
- CREATE TABLE DOCTOR(DOC_ID NUMBER(3),DOC_NAME VARCHAR2(9),DATEHIRED DATE,SALPERMON NUMBER(12),AREA VARCHAR2(20),SUPERVISOR_ID NUMBER(3),CHGPERAPPT NUMBER(3),ANNUAL_BONUS NUMBER(5),CONSTRAINT DOCTOR_DOC_ID_PK PRIMARY KEY (DOC_ID)); INSERT INTO DOCTOR VALUES(432, 'Harrison' , TO_DATE('05-DEC-94'), 12000,'Pediatrics', 100, 75, 4500); INSERT INTO DOCTOR VALUES(509, 'Vester' , TO_DATE('09-JAN-00'), 8100,'Pediatrics', 432, 40, null); INSERT INTO DOCTOR VALUES(389, 'Lewis' , TO_DATE('21-JAN-96'), 10000,'Pediatrics', 432, 40, 2250); INSERT INTO DOCTOR VALUES(504, 'Cotner' , TO_DATE('16-JUN-98'), 11500,'Neurology', 289, 85, 7500); INSERT INTO DOCTOR VALUES(235, 'Smith' , TO_DATE('22-JUN-98'), 4550,'Family Practice', 100, 25, 2250); INSERT INTO DOCTOR VALUES(356, 'James' , TO_DATE('01-AUG-98'), 7950,'Neurology', 289, 80, 6500); INSERT INTO DOCTOR VALUES(558, 'James' , TO_DATE('02-MAY-95'), 9800,'Orthopedics', 876, 85, 7700); INSERT INTO DOCTOR VALUES(876, 'Robertson' , TO_DATE('02-MAR-95'),…arrow_forwardThe option for referential integrity between the Owner table and the Pet table is restrict. An attempt is made to delete the row for owner number 012 in the Owner table. What is the result? (Use only the data in the tables below. Do not assume other data exists if it is not present in the tables below.) The primary keys are underlined. These are the OwnerNum column in the Owner table and the PetNumber in the Pet table. The foreign key is italicized. This is the OwnerNum column in the Pet table. Owner Table OwnerNum LastName FirstName Address City State Zip 005 Smith Jim 100 Main Dune FL 37465 009 Jones Jane 3245 West Sanford FL 32773 012 Johnson Frank 2 Century Easton FL 37256 020 Doe Sally 101 Basel Orange FL 30998 Pet Table PetNumber PetName DOB Species OwnerNum 100 Fido 1/1/2005 Canine 009 200 Ajax 12/3/2003 Canine 020 300 Sunshine 6./12/2004 Feline 009 400 King…arrow_forwardDelete the BOOK_PRICING table permanently so that it isn’t moved to the recycle bin. Delete the JL_EMPS table so that it can be restored. Restore the JL_EMPS table and verify that it’s available again.arrow_forward
- Using R and mtcars data set, perform the exercise Create a new object named car_inner containing the observations that have matching values in both tables mtcars and cars_table using as key the variable ID.arrow_forwardCreate a read-only view, name it as ViewD, that will contain RType and the number of rooms per RType in the ROOMS table.arrow_forwardCreate a trigger named tInstructors_UPDATE that checks the new value for the AnnualSalary column of the Instructors table. This trigger should raise an appropriate error if the annual salary is greater than 120,000 or less than 0.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
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