2. Complete the stored procedure below. The procedure will be used for deleting a row from the PART table by the given part id parameter. If there are any ORDERS records with the given part id, the procedure will give an error and then roll-back, otherwise it will delete the related PART record. CREATE PROCEDURE SPPART Delete @part_id int AS BEGIN

2. Complete the stored procedure below. The procedure will be used for deleting a row from the PART table by the given part id parameter. If there are any ORDERS records with the given part id, the procedure will give an error and then roll-back, otherwise it will delete the related PART record. CREATE PROCEDURE SPPART Delete @part_id int AS BEGIN

Programming with Microsoft Visual Basic 2017

8th Edition

ISBN:9781337102124

Author:Diane Zak

Publisher:Diane Zak

Chapter12: Database Queries With Sql

Section: Chapter Questions

Problem 2MQ1

See similar textbooks

Similar questions

Which 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.
What is the format of the SET clause that changes the value in a column to null in an UPDATE command?
The SERVICE_REQUEST table uses the CHAR data type for the DESCRIPTION and STATUS fields. Is there an alternate data type that could be used to store the values in these fields? Justify your reason for choosing an alternate data type or for leaving the data type as CHAR.
Which of the following statements deletes all rows in the HOMEWORK10 table? a. DELETE FROM homework10; b. DELETE . FROM homework10; c. DELETE FROM homework10; d. DELETE FROM homework10 WHERE amid = ; e. Both c and d delete all rows in the HOMEWORK10 table.
Write a script that creates and calls a stored procedure named spInsertDepartment. First, code a statement that creates a procedure that adds a new row to the Departments table. To do that, this procedure should have one parameter for the department name. Code at least two EXEC statements that test this procedure. (Note that this table does not allow duplicate department names.)
Write a script that creates and calls a stored procedure named spInsertInstructor that inserts a row into the Instructors table. This stored procedure should accept one parameter for each of these columns: LastName, FirstName, Status, DepartmentChairman, AnnualSalary, and DepartmentID. This stored procedure should set the DateAdded column to the current date. If the value for the AnnualSalary column is a negative number, the stored procedure should raise an error that indicates that this column doesn’t accept negative numbers. Code at least two EXEC statements that test this procedure.
Create a trigger named trg_cust_charge that will automatically update the CUSTOMER table’s CUS_BALANCE when a new CHARTER row is added. Use the CHARTER table’s CHAR_TOT_CHG as the update source (Assume that all charter charges are charged to the customer balance.) In addition to the CHAR_TOT_CHG, add a $5 service charge for every 50 miles flown. Create a stored procedure to update an Aircraft’s time till maintenance. Procedure takes the Aircraft number (ac_number) as a parameter. The procedure then resets the aircraft’s attributes: ac_ttaf to 5000, ac_ttel to 7775 and ac_tter to 8805 (right engines last longer, less stress on take offs). Create a stored procedure that will take a Pilot’s number and percentage, then update the corresponding pilot’s annual salary by the input percentage (increase the annual salary, so you are giving the pilot a raise). Hint: you may have to do some additional work (alter table) on this one to get the table correct.
Which of the following is a correct statement?a. If you attempt to add a record that violates a constraint for one of the table’s columns, only the valid columns for the row are added.b. A subquery nested in the VALUES clause of an INSERT INTO command can return only one value without generating an Oracle 12c error message.c. If you attempt to add a record that violates a NOT NULL constraint, a blank space is inserted automatically in the appropriate column so that Oracle 12c can complete the DML operation.d. None of the above statements is correct.
On Microsoft Access: Using an update query, update the field values in the StateMinInc field in the IncomeLevels table to 16780 for all records where the value in the County field is Twin Falls. Save the query using UpdateStateMinInc as the query name, then run and view it. I've created a new query for the IncomeLevels table. I'm in design view, and I've pressed "Update" under the Design tab. What code would I enter, and where, to update the StateMinInc field to 16780 when the County field is Twin Falls.
Task 3: Create(Enter) new product by using a Procedure with IN ParametersFollow 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 button to…
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.