Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
1. What is the difference between a view and a materialized view? 2. What is a sequence? Write its syntax. 3. What is a trigger, and what is its purpose? Give an example.

1. What is the difference between a view and a materialized view? 2. What is a sequence? Write its syntax. 3. What is a trigger, and what is its purpose? Give an example.

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
icon
Concept explainers
Question

Am  asking for help question 1 to 15 this database question 

CHAPTER 8 ADVANCED SQL ASSIGNMENT 2 Use the database tables in Figure P8.16 FIGURE P8.16 CH08_SIMPLECO DATABASE TABLES Table name: CUSTOMER CUST_NUM CUST_LNAME CUST_FNAME CUST BALANCE 1050 11 840 92 1000 Smith 1001 Onega Jeanne Juan Database name: Ch08 SimpleCo Table name: INVOICE NV NUM CUST NUM 8000 8001 8002 8003 8004 INV DATE NV AMOUNT 1000 23-Mar-16 1001 23-Mar-16 1001 30-Mar-16 1000 12-Apr-16 1000 23-Apr-16 235.89 31282 520 10 194.78 619.44 1. What is the difference between a view and a materialized view? 2. What is a sequence? Write its syntax. 3. What is a trigger, and what is its purpose? Give an example. 4. What is a stored procedure, and what are its advantages? Give an example. 5. Create the CUSTOMER table structure illustrated in Figure P8.16. The customer number should store integer values. The name attributes should support variable length character data up to 30 characters each. The customer balance should support up to six digits on the left of the decimal place and two digits to the right of the decimal place. 6. Create the INVOICE table structure illustrated in Figure P8.16. The invoice number should store integer values. The invoice date should store date values. The invoice amount should support up to 8 digits to the left of the decimal place and two digits to the right of the decimal place. 7. Write the set of SQL commands necessary to insert the data into the CUSTOMER table you created in Problem 1, as illustrated in Figure P8.16. 8. Write the set of SQL commands necessary to insert the data into the INVOICE table you created in Problem 2, as illustrated in Figure P8.16. 9. Create an Oracle sequence named CUST_NUM_SEQ to generate values for customer numbers. The sequence should start with the value 2000. 10. Create an Oracle sequence named INV_NUM_SEQ to generate values for invoice numbers. The sequence should start with the value 9000. 11. Insert the following customer into the CUSTOMER table, using the Oracle sequence created in Problem 20 to generate the customer number automatically:. "Powers', 'Ruth', 500 13. Modify customer 1000 to indicate the date of birth on March 15, 1989. 14. Modify customer 1001 to indicate the date of birth on December 22, 1988. 12. Modify the CUSTOMER table to include the customer's date of birth (CUST_DOB), which should store date data 15. Create a trigger named trg_updatecustbalance to update the CUST_BALANCE in the CUSTOMER table when a new invoice record is entered. (Assume that the sale is a credit sale.) Whatever value appears in the INV_AMOUNT column of the new invoice should be added to the customer's balance. Test the trigger using the following new INVOICE record, which would add 225.40 to the balance of customer 1001: 8005, 1001, 27-APR-18', 225.40
Transcribed Image Text:CHAPTER 8 ADVANCED SQL ASSIGNMENT 2 Use the database tables in Figure P8.16 FIGURE P8.16 CH08_SIMPLECO DATABASE TABLES Table name: CUSTOMER CUST_NUM CUST_LNAME CUST_FNAME CUST BALANCE 1050 11 840 92 1000 Smith 1001 Onega Jeanne Juan Database name: Ch08 SimpleCo Table name: INVOICE NV NUM CUST NUM 8000 8001 8002 8003 8004 INV DATE NV AMOUNT 1000 23-Mar-16 1001 23-Mar-16 1001 30-Mar-16 1000 12-Apr-16 1000 23-Apr-16 235.89 31282 520 10 194.78 619.44 1. What is the difference between a view and a materialized view? 2. What is a sequence? Write its syntax. 3. What is a trigger, and what is its purpose? Give an example. 4. What is a stored procedure, and what are its advantages? Give an example. 5. Create the CUSTOMER table structure illustrated in Figure P8.16. The customer number should store integer values. The name attributes should support variable length character data up to 30 characters each. The customer balance should support up to six digits on the left of the decimal place and two digits to the right of the decimal place. 6. Create the INVOICE table structure illustrated in Figure P8.16. The invoice number should store integer values. The invoice date should store date values. The invoice amount should support up to 8 digits to the left of the decimal place and two digits to the right of the decimal place. 7. Write the set of SQL commands necessary to insert the data into the CUSTOMER table you created in Problem 1, as illustrated in Figure P8.16. 8. Write the set of SQL commands necessary to insert the data into the INVOICE table you created in Problem 2, as illustrated in Figure P8.16. 9. Create an Oracle sequence named CUST_NUM_SEQ to generate values for customer numbers. The sequence should start with the value 2000. 10. Create an Oracle sequence named INV_NUM_SEQ to generate values for invoice numbers. The sequence should start with the value 9000. 11. Insert the following customer into the CUSTOMER table, using the Oracle sequence created in Problem 20 to generate the customer number automatically:. "Powers', 'Ruth', 500 13. Modify customer 1000 to indicate the date of birth on March 15, 1989. 14. Modify customer 1001 to indicate the date of birth on December 22, 1988. 12. Modify the CUSTOMER table to include the customer's date of birth (CUST_DOB), which should store date data 15. Create a trigger named trg_updatecustbalance to update the CUST_BALANCE in the CUSTOMER table when a new invoice record is entered. (Assume that the sale is a credit sale.) Whatever value appears in the INV_AMOUNT column of the new invoice should be added to the customer's balance. Test the trigger using the following new INVOICE record, which would add 225.40 to the balance of customer 1001: 8005, 1001, 27-APR-18', 225.40
Expert Solution
steps

Step by step

Solved in 3 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Query Syntax
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.
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS