Answered: 4. Using an account with the required…

4. Using an account with the required privileges, create a role named CUSTOMERREP that allows inserting new rows in the ORDERS and ORDERITEMS tables and deleting rows from these tables.

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter7: User Creation And Management

Section: Chapter Questions

Problem 4HOA

See similar textbooks

Related questions

Q: Task 7: Create a procedure to add a row to the INVOICES table. CALL ADD_INVOICE('00001',…

A: Name of the procedure - ADD_INVOICE Name of relation - INVOICES Suppose datatype of INVOICE_NUM,…

Q: Revoke the privilege to del

A: Revoke the privilege to delete rows in the ORDERS and ORDERITEMS tables from theCUSTOMERREP role…

Q: The Developers team also wants you to ensure that emails are converted to lowercase after an update…

A: A collected information or structured data in the form of table that are stored electronically which…

Q: A create view statement creates a new table with its on data using the view select query. True False

A: A view is the data set of a data storage query that database clients can query much like a constant…

Q: 2. Create a trigger that will maintain the correct value in the membership balance in the MEMBERSHIP…

A: the code is given below :

Q: Design two stored procedures as the following (4 marks) Create a PARAMETER based SELECT QUERY stored…

A: Q: Write query based on given values Note: Please ensure to update correct field names as they are…

Q: A table can be deleted from the database by using the _____ TABLE command. Select one: a. MODIFY b.…

A: Drop function is used to delete the table from a database. Delete is used to delete a row from a…

Q: Create the UPDATE_INVOICE procedure to change the date of the invoice whose number is stored in…

A: It enables to save query with a name so that it can be executed whenever required rather than…

Q: b.All rows will be deleted.

A: Delete from command will delete all row from table by default if where clause not specified.

Q: 15. Create a new table named Hiking that includes all columns in the Reservation table where the…

A: As per our company guidelines, we are not supposed to answer more than three questions per post and…

Q: The Driver Relationship team wants to have quick search options for the active drivers. The team…

A: Task 4: INDEX is used search/retrieve data more quickly. It is basically used to speed up query.…

Q: How many NOT NULL constraints can be created at the table level by using the CREATE TABLE command? (…

A: Utilizing the CREATE TABLE command, "0" NOT NULL constraints can be created at the table level.…

Q: 8. In the OrderItems table, display the minimum item_price with the alias shown. 2.49 1 rows…

A: Below is the complete solution with explanation in detail for the given question about writing query…

Q: I have the following SQL Script: CREATE OR REPLACE TRIGGER SCSP_VIEW_UPDATE INSTEAD OF UPDATE ON…

A: We have to create How to add logic so that blanks and empty strings cannot be used to update…

Q: write a trigger that will be activated after a DELETE activity. The trigger will INSERT all deleted…

A: The solution is given in the below step with explanation

Q: Task 9: Create the DELETE_INVOICE procedure to delete the invoice whose number is stored in…

A: syntax to create delete procedure are as follows create procedure procedure name (declare a…

Q: It takes a special command to copy and paste information between tables.

A: INSERT instruction The INSERT INTO command is used to add data to an existing table by copying it…

Q: Create two different forms of the INSERT command to add a student with a student ID of 65798 and…

A: note, we can only solve three subparts in one question. There are two types of INSERT INTO…

Q: In a MERGE statement, an INSERT is placed in which conditional clause? (refer to the tables in the…

A: An INSERT operation is performed WHEN NOT MATCHED conditional clause is used in a MERGE statement.…

Q: 7. In the OrderItems table, display the maximum item_price with the alias shown. 11.99 Max Item…

A: Algorithm:Select the maximum value of the item_price column from the OrderItems table.Display the…

Q: EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in…

A: MPDET is an external table containing the columns EMPNO and ENAME. Which command would work in…

Q: Create a trigger for the Invoices table that automatically inserts the vendor name and address for a…

A: Trigger When an event occurs, the trigger runs automatically which is a stored procedure. The…

Q: Create a user with the name IS Sead and password a789 and give him permission to connect to the…

A: The answer is given in the below step

Q: Create the DELETE_INVOICE procedure to delete the invoice whose number is stored in I_INVOICE_NUM.

A: We need to make an SQL query to delete the invoice based on some condtion.

Q: The previous administrator created a table named CONTACTS, which contains outdated data. You want to…

A: DROP TABLE is the answer Because DROP command delete both table and data present in it. Option…

Q: Task 5: The Developers team also wants you to ensure that emails are converted to lowercase after an…

A: Given Data : Table named: USERS The attributes of the table are : USER_ID USER_FIRST_NAME…

Q: the UPDATE_INVOICE procedure to change the date of the invoice whose number is stored in…

A: Dear Student, The procedure is given below -

Q: Write and execute the command to retrieve the customer ID, first name, and last name of each…

A: In SQL, A view is a virtual table based on the result-set of an SQL statement. A view contains rows…

Q: Task 4: The Driver Relationship team wants to have quick search options for the active drivers.…

A: Here we write a simple mysql code for find the active driver id,name: we write two method to get…

Q: When is creating an index manually inappropriate?a. when queries return a large percentage of rows…

A: The DBMS software tools create an index for each record automatically. Although the developers can…

Q: The Developers team also wants you to ensure that emails are converted to lowercase after an update…

A: We need to create a TRIGGER function which will update the USER_EMAIL to upper case. Hence the…

Q: Open the UnsoldComponents query and complete the following tasks: a. Delete the criteria row that…

A: Find the required query given as below : As per company guidelines we are supposed to answer only…

Q: Write an UPDATE statement that modifies the Customers table. Change the password column to “reset”…

A: Let's see the solution in the next steps

Question

4. Using an account with the required privileges, create a role named CUSTOMERREP that
allows inserting new rows in the ORDERS and ORDERITEMS tables and deleting rows
from these tables.

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

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, but do not execute, the commands to grant the following privileges: a. User Ashton must be able to retrieve data from the ITEM table. b. Users Kelly and Morgan must be able to add new orders and order lines. c. User James must be able to change the price for all items. d. User Danielson must be able to delete customers. e. All users must be able to retrieve each customers number, name, street, city, state,and postal code. f. f User Pere7 must be able to create an index on the ORDFRS table. g. User Washington must be able to change the structure of the ITEM table. h. User Grinstead must have all privileges on the ORDERS table.
In 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.
134. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table? a. UPDATE empdetSET ename = 'Amit'WHERE empno = 1234; b. DELETE FROM empdetWHERE ename LIKE 'J%'; c. CREATE VIEW empvuASSELECT * FROM empdept; d. CREATE INDEXempdet_idxON empdet(empno);
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;
Task 5: The Developers team also wants you to ensure that emails are converted to lowercase after an update operation. Currently, new insertions are guaranteed to have lowercase emails, but there is no such guarantee for legacy emails. Therefore, the team wants to ensure that emails are being retained in a lowercase irrespective of any changes in the database. Create a new TRIGGER called email_update for the USERS table that runs before an UPDATE operation. Task: Create a TRIGGER to run on the USERS table before any UPDATE operation. (SQL Database Test): Create a TRIGGER before any UPDATE operations on the USERS table
write a trigger that will be activated after a DELETE activity. The trigger will INSERT all deleted data to a backup table having same attributes as original table
1 rows returned in 0.00 seconds Download 7. In the OrderItems table, display the maximum item_price with the alias shown. 11.99 1 rows returned in 0.00 seconds Download Max Item Price
Task 9: Create the DELETE_INVOICE procedure to delete the invoice whose number is stored in I_INVOICE_NUM.
Task 7: Create a procedure to add a row to the INVOICES table. CALL ADD_INVOICE('00001', '2021-11-20', '294') SELECT * FROM INVOICES WHERE INVOICE_NUM = '00001' PLEASE ANSWER IF YOU KNOW THE ANSWER!!!!!
Task 5: The Developers team also wants you to ensure that emails are converted to lowercase after an update operation. Currently, new insertions are guaranteed to have lowercase emails, but there is no such guarantee for legacy emails. Therefore, the team wants to ensure that emails are being retained in a lowercase irrespective of any changes in the database. Create a new TRIGGER called email_update for the USERS table that runs before an UPDATE operation.
Task 8: Create the DEL_OWNER procedure to delete the owner whose number is stored in I_OWNER_NUM (provided as a parameter).
2. Create a trigger that will maintain the correct value in the membership balance in theMEMBERSHIP table when videos are returned late. The trigger should execute as anFOR/AFTER trigger when the due date or return date attributes are updated in theDETAILRENTAL table. The trigger should satisfy the following conditions. a) Calculate the value of the late fee prior to the update that triggered this execution ofthe trigger. The value of the late fee is the days late times the daily late fee. If theprevious value of the late fee was null, then treat it as zero (0).b) Calculate the value of the late fee after the update that triggered this execution of thetrigger. If the value of the late fee is now null, then treat it as zero (0).c) Subtract the prior value of the late fee from the current value of the late fee todetermine the change in late fee for this video rental.d) If the amount calculated in part c is not zero (0), then update the membership balanceby the amount calculated for the…