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
Textbook Question
Chapter 8, Problem 8RQ
What is the difference between using a subquery with a CREATE TABLE command and using a subquery with an INSERT command?
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
THIS MODULE IS ABOUT SUBQUERIES, SO YOU MUST USE SUBQUERIES INSTEAD OF TABLE JOINS IN THISASSIGNMENT.
Two tables are created:
Horse with columns:
ID - integer, primary key
RegisteredName - variable-length string
Student with columns:
ID - integer, primary key
FirstName - variable-length string
LastName - variable-length string
Create the LessonSchedule table with columns:
HorseID - integer with range 0 to 65 thousand, not NULL, foreign key references Horse(ID)
StudentID - integer with range 0 to 65 thousand, foreign key references Student(ID)
LessonDateTime - date/time, not NULL
Primary key is (HorseID, LessonDateTime)
If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically.
If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically.
Notes: Table and column names are case sensitive in the auto-grader. Your SQL code does not display any results in Develop mode. Use Submit mode to test your code.
Two tables are created:
Horse with columns:
ID - integer, primary key
RegisteredName - variable-length string
Student with columns:
ID - integer, primary key
FirstName - variable-length string
LastName - variable-length string
Create the LessonSchedule table with columns:
HorseID - integer with range 0 to 65 thousand, not NULL, foreign key references Horse(ID)
StudentID - integer with range 0 to 65 thousand, foreign key references Student(ID)
LessonDateTime - date/time, not NULL
Primary key is (HorseID, LessonDateTime)
If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically.
If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically.
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
- 8. Create a trigger for the Invoices table that automatically inserts the vendor name and address for a paid invoice into a table named ShippingLabels. The trigger should fire any time the PaymentTotal column of the Invoices table is updated. The structure of the ShippingLabels table is as follows: CREATE TABLE ShippingLabels (VendorName varchar(50), VendorAddress1 varchar(50), VendorAddress2 varchar(50), VendorCity VendorState VendorZipCode varchar(50), char(2), varchar(20)); Use this UPDATE statement to test the trigger: UPDATE Invoices SET PaymentTotal = 67.92, PaymentDate = '2020-02-23' WHERE InvoiceID = 100;arrow_forwardThe Driver Relationship team realized that maintaining driver IDs is difficult and requested an automatic way of incrementing the value when a new driver is added. You need to make the changes on the table to automatically increment the Driver ID. After the change, you need to insert the following driver: First Name: Nursin Last Name: Yilmaz Driving License ID: 4141447 Start Date: 2019-12-28 Driving License Checked: True Rating: 4.0arrow_forwardCan you identify anything about a table definition that cannot be changed using the ALTER command?arrow_forward
- Create table “Cleaner” (cNo varchar2(5), cName varchar2(20), cSalary number(6,2), dNo varchar2(5)). Write a table level trigger which can tell how the restriction on ‘cSalary’ is violated. Whenever the value of ‘cSalary’ is beyond the valid range (0 – 5000), the trigger will generate an error message informing user whether it is greater than 5000 or a negative numberarrow_forwardThe Driver Relationship team realized that maintaining driver IDs is difficult and requested an automatic way of incrementing the value when a new driver is added. You need to make the changes on the table to automatically increment the Driver ID. After the change, you need to insert the following driver: First Name: Nursin Last Name: Yilmaz Driving License ID: 4141447 Start Date: 2019-12-28 Driving License Checked: True Rating: 4.0 THIS IS IN SQL FORMAT FOR CENGAGE MINDTAP, PLEASE HELP I AM STUCKarrow_forwardUsing a CREATE statement to create a table called s1, which is a duplicate of table STUDENT, then remove all the data from s1 (you need to write two statements for this question.)arrow_forward
- Explain the difference between truncating a table and deleting a table.arrow_forwardWhich of the following statements is correct? A table can contain a maximum of only one column marked as unused. You can delete a table by removing all columns in the table. Using the SET UNUSED clause allows you to free up storage space used by a column. None of the above statements are correct.arrow_forwardWhich of the following is a correct statement? You can restore the data deleted with the DROP COLUMN clause, but not the data deleted with the SET UNUSED clause. You can’t create empty tables—all tables must contain at least three rows of data. A table can contain a maximum of 1000 columns. The maximum length of a table name is 265 characters.arrow_forward
- How do you add data from an existing table to another table?arrow_forwardIn the initial creation of a table, if a UNIQUE constraint is included for a composite column that requires the combination of entries in the specified columns to be unique, which of the following statements is correct? a. The constraint can be created only with the ALTER TABLE command. b. The constraint can be created only with the table-level approach. c. The constraint can be created only with the column-level approach. d. The constraint can be created only with the ALTER TABLE MODIFY command.arrow_forwardWhich of the following commands removes all data from a table but leaves the tables structure intact? a. ALTER TABLE secustomerorders DROP UNUSED COLUMNS; b. TRUNCATE TABLE secustomerorders; c. DELETE TABLE secustomerorders; d. DROP TABLE secustomerorders;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