DATABASE SYSTEMS
13th Edition
ISBN: 9780357095607
Author: Coronel
Publisher: CENGAGE L
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 8, Problem 27P
Program Plan Intro
Stored procedures:
- A procedure is a collection of procedural and SQL statements.
- A procedure may have input parameter, output parameter and both parameters.
- It has a declared with a unique named with a unit of procedural code using the proprietary RDBMS and it is invoked by a host language library routine.
Syntax for stored procedure:
CREATE FUNCTION fun_name(argument IN data-type)RETRUN data-type[IS]
BEGIN
PL/SQL statements;
Retrun (value or expression);
END;
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Create a Procedure to delete a existing invoice row by accepting INV_NUMBER,CUS_CODE, INV_DATE. Whenever you execute the procedure by passing value, it hasto delete a particular tuple from the INVOICE table.
I need help with an UPDATE statement that will change the CUST_EMAIL value to "unknown" for records in the CUSTOMERS table that have null values for the cust_email field
Trigger please
Chapter 8 Solutions
DATABASE SYSTEMS
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
- Write a procedure named prc_cust_add to add a new customer to the CUST_MYSQL table. Use the following values in the new record: CUIST_NUM CUST_LNAME CUST_FNAME CUST_BALANCE 2003 Rauthor Peter 0.00 (You should execute the procedure and verify that the new customer was added to ensure your code is correct.)arrow_forward1) Add the following record to the OWNER table: INSERT INTO OWNER VALUES('SA100', 'Sam', 'Afyouni', '100 Hello St', 'Anytown', 'MA', '55555'); 2) Create the UPD_OWNER_LAST_NAMEprocedure to change the last name of the owner whose number is stored in I_OWNER_NUM (provided as a parameter) to the value currently found in I_LAST_NAME.arrow_forwardsql question Write an UPDATE statement that modifies the Customers table. Change the password column to “reset” for every customer in the table.arrow_forward
- Use SQL Developer or Oracle Live SQL to write the appropriate SQL commands as follows: ) PROCEDURE: Use the cust_node table to create a procedure called ‘Get_node_id’ that takes the cust_idas an input parameter to show its corresponding node id to the banker. Then execute this procedure withcust_id=5.arrow_forwardInsert into table customerPayment, columns customerOrderld and total result set returned from query a. Select columns id and total from table customerOrder where column orderStateld is equal to subquery i. (select column id from table orderState where column state is equal to "Payment received") Write Test Case 1 as a SQL join query to do the following a. Select using concatenation of columns firstName and lastName from table www customer as Customer b. Select customerOrderId from table customerPayment as "Customer Order" c. Select description from table product as Product d. Select total from table customerOrder as “Product Total" e. Join tables customerPavment, customerOrder, customer, product f. Order by column lastName from table customer Alter table purchaseOrder add column total as data type decimal size 7,2, not null, default value of 0.0arrow_forwardWrite an appropriate SQL query to determine if there are any students that are not registered for any modules.Sample results:STUDENT_ID STUDENTS654321 Joe, ThaboRequirementsCorrect SELECT statement used. Correct JOIN used. Correct WHERE clause. Question 7 Write an appropriate SQL query to delete the following record from the STUDENTS table: STUDENT_ID: S654321STUDENT_SURNAME: JoeSTUDENT_NAME: ThaboRequirementsCorrect DELETE statement used. Correct WHERE clause.arrow_forward
- Q1. Please write SQL codes to query data from member tables based on below criteria Please write a SELECT statement to display each member data that street must NOT be ‘STREET’ and ‘LANE’, and valid_date CANNOT be ‘FEB’, ‘APR’, and ‘JUN’. Please display the result in descending order of the last_name. The result should be identical to below results: [SCREENSHOT] I try to use this, but it gives me this error: SELECT MEMBER_ID, FIRST_NAME, LAST_NAME, STREET, FORMAT(VALID_DATE, 'dd-MM--yy')FROM MEMBERWHERE STREET NOT IN ('STREET', 'LANE')AND MONTH(VALID_DATE) NOT IN (2, 4, 6)ORDER BY LAST_NAME DESC; ====== error ===== ORA-00904: "MONTH": invalid identifier ORA-00904: "FORMAT": invalid identifierarrow_forwardUsing the software.subscription table, pull all columns for subscriptions that have been cancelled. Use the results from the attachment to determine which column you need to filter on in your where clause. This is to write a query in a database.arrow_forwardFrom 16 to 19arrow_forward
- Task 6: Add the following record to the OWNER table: INSERT INTO OWNER VALUES('SA100', 'Sam', 'Afyouni', '100 Hello St', 'Anytown', 'MA', '55555');arrow_forwardQ3: How many unique titles do we have in the employee table? Hint: You need to create TWO queries, 1. The first query returned all the unique titles, i.e., Q03_Unique Titles (use the Distinct keyword and use the SQL View to type in the SQL statement directly (Design View will not work). Please refer to SQL_MIS310 slide#105-107 for guidance. You cannot use Distinct and Count together in Access and this is why you have to do it in two related two queries.). The second query is a query against the first query (i.e., Q03_UniqueTitles) and count the number of unique titles (Use the aggregate function Count) and the query should return a number. 2. The second query is named Q03_UniqueTitlles_Count and it has been created for you already. You should use Q03_UniqueTitles (not a table) to create this query. This query should return a result that is a simple number.arrow_forwardThe following code is for Oracle Oracle SQL. Please check it for errors? CREATE PROCEDURE ADD_INVOICE @INVOICE_NUMint=NULL, @INVOICE_DATEdate=NULL, @CUST_IDint=NULL AS Begin INSERTINTO INVOICES (INVOICE_NUM, INVOICE_DATE, CUST_ID) VALUES(@INVOICE_NUM,@INVOICE_DATE,@CUST_ID); ENDarrow_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 LearningDatabase 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
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