2) Create in PL/SQL a storedprocedure called 'Del Details' todelete the record oforderedProduct table for which itreceives as input the order number(orderNO) and theproduct number (productNO).3)Create in PL/SQL a stored Functioncalled 'productPerOrder' to returnthe number ofproducts placed for any order itsnumber is given as input.4) Write a Trigger called'changeCustomer Category'. Thistrigger should be invoked beforeany update of customer balance incustomer table. Its job is to modifythe category of acustomer from B2 to B1 and from C2to C1 when a customer's balancestatus drops belowa certain threshold (-10000).

2. The SQL query is given below: CREATE PROCEDURE [dbo].[Del Details] -- Add the parameters for the…

Answered: 2) Create in PL/SQL a stored procedure…

2) Create in PL/SQL a stored procedure called 'Del Details' to delete the record of orderedProduct table for which it receives as input the order number (orderNO) and the

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter8: Sql Functions And Procedures

Section: Chapter Questions

Problem 6CAT

See similar textbooks

Related questions

Q: Draw a push down automata for an+1bn | n>=1

A: We are going to design PDA for the given language : an+1bn | n>=1 . Please refer to the image for...

Q: What exactly are bignums? Which languages are they compatible with?

A: It is a tool that is used to represent the code for the value of the integer which contains a very l...

Q: Implement the Gauss-Seidel Method. Iterate until the relative approcimate error is less than 0.00001...

Q: In C, what is a volatile variable? In what situations is it useful?

A: In computer programming, particularly in the C, C++, C#, and Java programming languages, a variable ...

Q: Demonstrate why a batch operating system is unsuitable with modern applications like airline reserva...

A: Introduction: The operating system (OS) is a piece of software that serves as a link between the use...

Q: What is the difference between TCP and UDP? Identify and discuss the four most notable distinctions ...

A: Introduction : TCP is a connection-oriented networking protocol that enables the transmission of mes...

Q: Write a contrast between 3GL and 4GL

A: 3GL: 3GL stands for 3rd generation language (Programming) and is a procedure-oriented programming la...

Q: 10) Which of the following is an advantage of integrating a marketplace with a platform? Mark ALL th...

A: Benefits of eCommerce marketplace integration: 1. To Beat the Competition: In this ever-growing reta...

Q: What are the critical haptic display parameters?

A: Answer: Haptic Display: Haptic display represents a promising interface for human-computer interacti...

Q: What is a two-way communication in which each party take turns transmitting?

A: Simplex: Only one way communication. Send data one point to other where sender send the data and rec...

Q: What is the significance of address alignment?

A: Introduction: When you do unaligned address access on a processor that supports it, the processor wi...

Q: es susceptible to cy

A: There are some Internet of Things (IoT) devices susceptible to cyber threats

Q: The term "multicore processor" means "processor with several cores."

A: Hello student Greetings Hope you are doing great Thank You!!!

Q: PROBLEM: Write a program that computes the equivalent resistance of a purely series and purely paral...

A: Given: there is no programming language mentioned so I am answering in C++ programming language. ...

Q: Examine the differences between enterprise and project-level data models.

A: An enterprise data model is essentially utilized as a structural system for planning and keeping up ...

Q: Is there a easy way to learn SPSS

A: SPSS stands for Statistical Package for the Social Sciences. SPSS can take data from almost any typ...

Q: or for your application

A: given - Create a segment selector for your application.

Q: ndicate the least integer n for which (3x + x²)4 € O(x").

A: We are given an asymptotic relation and we are going to find least value of n for which (3x5+x2)4 is...

Q: Java is considered a memory-efficient programming language. Explain why in your own words.

A: Java is considered a memory-efficient programming language. Memory Management in Java refers to allo...

Q: Write a word-wrapping program ww that reads the contents of a file and prints it to standard output,...

A: In figuring, word wrapping is an interaction by which a word which comes toward the finish of a line...

Q: What is the best way to utilise the RSA algorithm to verify the identity of a receiver? In this situ...

A: Introduction: The RSA algorithm is a type of asymmetric cryptography. It's asymmetric because it use...

Q: What exactly is an expert system, and how can a data warehouse assist in its development?

A: Introduction: An expert system is a computer-based decision-making system that handles difficult dec...

Q: What is the difference between a maskable interrupt and a nonmaskable interrupt?

A: Maskable interrupt is a hardware Interrupt that can be disabled or ignored by the instructions of CP...

Q: Consider a 4-way set associative mapped cache. The size of cache memory is 1 MB and there are 12 bit...

A: Set size = 4Cache memory size = 1 MBNo.of.bits in tag = 12 bits No.of.bits in set number = x1 bitsNo...

Q: Design a Turing Machine to accept the language L = {wCw | w in (0 + 1)*). %3D

A: The Answer is in below Steps

Q: Write a C++ program that will allow the u First Name, Last name, Middle Initial, student. The follow...

A: here in this question we have asked to write a program which take name and marks /grade from user an...

Q: Some information technology security professionals feel that their firms should hire former computer...

A: Introduction: A cyber-attack is a cybercriminal attack on one or more computers or networks. It will...

Q: Define dirty read, nonrepeatable read, and phantom read.

A: Dirty Read A dirty read occurs when one transaction is changing the record, and therefore the other...

Q: What exactly is the goal of concurrency control?

A: Purpose of Concurrency Control Concurrency controls prevent data integrity problems, which can aris...

Q: What are the various factors that influence network communication performance?

A: Introduction: The performance of computer networks is influenced by a few things. These variables wi...

Q: Why is the sliding window technique preferable to the stop-and-go protocol?

A: Let's see the solution

Q: Why information is considered as an asset and valuable to organization? 2. Explain why information ...

A: 1. Why information is considered as an asset and valuable to organization? --> Data is one of th...

Q: A common measure of transmission for digital data is the baud rate, defined as the number of bits tr...

A: Introduction: Baud is a standard unit of symbol rate measurement in telecommunications and electroni...

Q: Matrix ---Select-- --Select-- an array with two or more dimensions an array with only one row and on...

A: Please refer below for your reference: According to the question asked by you about matrix the corre...

Q: USING SWITCH CASE make a c++ program that generates a plate number based on the user’s answer to ...

A: Introduction USING SWITCH CASE make a c++ program that generates a plate number based on the user’...

Q: 7*(3+5 (St4)/7+8 - (a) Prefix (b) postfix

Q: PROBLEM: Write a C++ program that will accept 3 strings (these should be of different sizes) then ...

A: ALGORITHM:- 1. Take input for the strings from the user. 2. If size is greater than display error me...

Q: Describe the idiom of generate-and-test programming.

A: Generate-and Test strategy A goal may consist of many subgoals. The generate and test strategy proce...

Q: Does Amazon's cloud computing platform qualify as an enterprise system?

A: Introduction: An enterprise system sometimes referred to as an enterprise resource planning (ERP) sy...

Q: What is the return type of a method that returns null?

A: The return type of a method must be made void if it is not returning any value.

Q: What is the name of the class? Name and colour the qualities that you want to use.

A: Introduction: The class name is used as the name for the class. It is also used as the name for the ...

Q: Consider the following program: Fib[n] if(n==0) then return 0 else if(n==1) then return else retiurn...

A: Below the consider the following program Fib[n]if(n==0) then return 0else if(n==1) then return 1el...

Q: Please construct the truth table of the following, thank you. a) [p-q] A [-p-q] b) [-p--q]- [p-q] c)...

A: Conjunction: It is denoted by (∧). For any two propositions p and q, (p∧q) is true when both p and q...

Q: SELinux is an example of what kind of Access Control? RBAC O DAC МАС O Rule-based Access Contral

A: SELinux stands for Security Enhanced Linux and this is a security architecture for Linux system that...

Q: What role do frequent data backups have in protecting organisations against threats such as ransomwa...

A: Introduction: When someone gets Ransomware, their files are encrypted, and they can't open them. It ...

Q: Distinguish the benefits and drawbacks of a two-tiered architectural structure.

A: A two-tier architecture is a kind of software architecture in which the presentation layer or user i...

Q: What exactly is deadlock? What can be done to avoid it? When it occurs, how can it be resolved?

A: What are Deadlocks? A deadlock occurs when two different users or transactions require access to da...

Q: Computer Science Write a program that fulfills following requirements: a. Read two hexadecimal numbe...

A: Below is the complete solution with explanation in detail for the given question. Note:- Here, the ...

Q: How do you convert data written on a device that only allows sequential access to a device that only...

A: Introduction: We have to find out data that allows the sequential access can be converted into the ...

Q: There is an auto-complete feature in MATLAB. If a user starts to type a command and then presses the...

A: Given: There is an auto-complete feature in MATLAB. If a user starts to type a command and then pr...

Question

2) Create in PL/SQL a stored
procedure called 'Del Details' to
delete the record of
orderedProduct table for which it
receives as input the order number
(orderNO) and the
product number (productNO).
3)Create in PL/SQL a stored Function
called 'productPerOrder' to return
the number of
products placed for any order its
number is given as input.
4) Write a Trigger called
'changeCustomer Category'. This
trigger should be invoked before
any update of customer balance in
customer table. Its job is to modify
the category of a
customer from B2 to B1 and from C2
to C1 when a customer's balance
status drops below
a certain threshold (-10000).

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 3

VIEW

Step by step

Solved in 3 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

A database user manually notes that The file contains two hundred records, each record containing nine fields. Use appropriate relational database terminology to translate that statement.
A_Create in PL/SQL a stored procedure called 'Del_Details' to delete the record oforderedProduct table for which it receives as input the order number (orderNO) and theproduct number (productNO). B_ Create in PL/SQL a stored Function called 'productPerOrder' to return the number ofproducts placed for any order its number is given as input.C_ Write a Trigger called ‘changeCustomer_Category’. This trigger should be invoked beforeany update of customer balance in customer table. Its job is to modify the category of acustomer from B2 to B1 and from C2 to C1 when a customer's balance status drops belowa certain threshold (-10000).
Database course: a. Write in SQL a command that creates the table Customers according to the above description. b. Write in SQL a statement that adds a new column named accountID of type Char(8). This column should be defined as a foreign key that relates the table Customers to the table Account. c. Write in SQL a command that deletes the customers whose date of birth is after 22-Dec-1990. d. Write a SQL query that displays the number of customers who born on the 16th day of a month. e. Write in SQL a query that displays the ids of the customers who have the highest income. f. Create a view named ‘customerDetails’ to hold all the details of customers whose first names start with ‘S’ and their incomes are between 500 and 1000 Omani Rials. (I need the codes to copy them please) Photo is there.
2.1 In the table above, Vanesa’s last name was omitted. Write an SQL query to add the lastname. Assume the last name is ‘Johnson’ 2.2 John Silas is an employee in a Logistics company with ID = 02361. He was newly promotedwith an increase of R2000 on his salary. Write an SQL query to update his record in thestafflist table 2.3 An alias provides a mechanism to rename a column or an expression. Under whatconditions should you enclose an alias in double quotes?
Please written by computer source 1. For this project, you need to create the company database and populate it with the data given in the Oracle SQL PLUS environment (if you have not done so) 2. Your SQL commands should be placed in file(s) and run from the file(s). 3. Implement Triggers on all the tables to change character data to uppercase in the character data type columns. 4. Implement a Function that returns Dname from Department for any give employee. The parameter to the function would be the SSN. 5. Implement a Function that returns manager’s full name for any given department. The parameter to the function would be the department name. 6. Implement another Function that returns manager’s full name for any given department. The parameter to the function would be the department number. 7. Implement a Procedure that increases an employee salary by x%. Employee is identified by SSN and the percentage of increase is given as an input. 8. Implement a Package that contains the…
MYASQL 7. Create Avgoffreight() stored procedure to retrieve the average of freight from Orders table. Then , show the result.8. Write a database trigger TO insert the UnitPrice field.IF UnitPrice is less than 10 then make it equal to 10.(BEFORE INSERT). Test the trigger and show its result.procedure and trigger9. Select the first 10 characters of each customer's address. (Hint: You can use one of the built-in functions that are explained in functions file). (bonus, if you want to get higher mark, you can answer it, if not ,you can upload the solution file without it )
INFO 2303 Database Programing Assignment # : PL/SQL Procedure & Function Practice Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. Write a function that takes patient date of birth. If the patient born after 30th June 1990 then he/she will be required to set appointment for immunization. Call this function from anonymous block to allow the user to enter the patient ID. Expected output: Enter the patient ID: 168 The status of X-immunization : REQUIRED
1a.Using SQL Terminal window write a query to display the names, block size of Control Files of your database. 1b.Using either SQL developer or SQL Terminal window write a query to display the names parameters related to UNDO. 1c.Using Database Express display the names, Status and Member count of Redo Log Groups.
in pl /sql the commend is like this create table bb_basketstatus (idstatus number, idbasket number, idstage number, dtstage date, shipper varchar(200),shippingnum varchar(299));create sequence bb_status_seq start with 1 increment by 1;create or replace procedure STATUS_SHIP_SP ( p_basketID Number,p_datestage DATE, p_shipper varchar, p_shippingnum varchar)asbegin INSERT INTO bb_basketstatus (idstatus, idbasket, idstage, dtstage, shipper, shippingnum) values (bb_status_seq.NEXTVAL,p_basketID,3,p_datestage,p_shipper,p_shippingnum); commit;end;/select From bb_ basketstatus; Execute status_ship_sp(3,'20-FEB-12','UPS','ZW2384YXK4957') ---- why it only shows "PL/SQL procedure successfully completed."without showing the result of database ?
1.9) Order of execution is critical since some operations are only meaningful after some order (i.e. having)True False 1.10) As being a sub language of SQL, DML has select, insert, update, and delete operationsTrue False 1.11) SQL does not support creating or deleting database object and granting or revoking access rightsTrue False 1.12) Common Table Expression (CTE) is only used to develop recursive SQL queriesTrue False 
Write a PL/SQL block that will allow the user to enter FNo and that will display Customer Name from Booking table. Include predefined exception with suitable messages1- SQL returns more than one records2- Records are not available

Recommended textbooks for you

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

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

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Programming with Microsoft Visual Basic 2017

Computer Science

ISBN:

9781337102124

Author:

Diane Zak

Publisher:

Cengage Learning

A Guide to SQL

Computer Science

ISBN:

9781111527273

Author:

Philip J. Pratt

Publisher:

Course Technology Ptr

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

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

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Programming with Microsoft Visual Basic 2017

Computer Science