Write a PL/SQL stored procedure to print number of employees who are working in a job with Title "Tester". Include procedure call also.If the total count of Employees is more than 5 then print "Sufficient number of Employees"If the total count of Employees is less than 5 then print "Insufficient number of Employees"Else print "there are 5 employees".Use Oracle's NO DATA_FOUND build in exception to display the last message.DEPARTMENTSLOCATIONSP DEPARTMENT_ID* DEPARTMENT_NAMEMANAGER_IDLOCATION_IDP LOCATION_IDSTREET ADDRESSPOSTAL_CODE* CITYSTATE PROVINCECOUNTRY_IDJOB_HISTORYPF* EMPLOYEE_IDP * START DATE* END DATEF JOB IDFDEPARTMENT_IDEMPLOYEESP EMPLOYEE_IDFIRST NAME* LAST_NAMEEMAILCOUNTRIESCOUNTRY_IDCOUNTRY_NAMEFREGION IDPHONE_NUMBER* HIRE DATEF JOB_IDJOBSP JOB_ID* JOB_TITLEMIN_SALARYMAX_SALARYSALARYCOMMISSION_PCTMANAGER_IDFREGIONSDEPARTMENT_IDP* REGION_IDREGION_NAMEI--

create procedure Tester as Select count(*) case when count(*) >5 then print 'Sufficient number…

Write a PL/SQL stored procedure to print number of employees who are working in a job with Title "Tester". Include procedure call also. If the total count of Employees is more than 5 then print "Sufficient number of Employees" If the total count of Employees is less than 5 then print "Insufficient number of Employees" Else print "there are 5 employees". Use Oracle's NO DATA_FOUND build in exception to display the last message.

Write a PL/SQL stored procedure to print number of employees who are working in a job with Title "Tester". Include procedure call also. If the total count of Employees is more than 5 then print "Sufficient number of Employees" If the total count of Employees is less than 5 then print "Insufficient number of Employees" Else print "there are 5 employees". Use Oracle's NO DATA_FOUND build in exception to display the last message.

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 9TD

See similar textbooks

Similar questions

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
pl/sql Product(pid, pname, pprice, category) Use the table given above to write a PL/SQL procedure that accepts a category value as parameter and prints name and price of all products in that category sorted according to the name; prints the total price of all products on screen; handles the following exceptions by printing an error message: One relevant named exception One unnamed exception with oracle error number ORA-01555 If the price of any product is negative, treat this as a user defined exception.
Write a procedure which takes customer id as argument and return name of customer and his/her total bill of all orders. You must display Customer name and total bill which is returned by procedure in main body. in SQL
MS SQL SERVER Write a statement to create a stored procedure named Find_Contact based on the Contacts table in question 1 A, which does the following: Accepts an input parameter named @FindMobile that has the data type varchar. IF MobileNo exists: Print the message “Mobile No for:” concatenated with the input parameter. IF MobileNo do not exists: Print the message “Do Not Exist” concatenated with the input parameter. Executes a SELECT statement on the table Contacts, passing the input parameter as part of the WHERE clause. Return the contact details that match the input parameter value.
Write an SQL statement that will insert an order in the orders table with the following data: cutomerID=3, orderDate = current date, shipAmount=20, taxAmount=23.56, shipDate=a week after current date, shipAddressID=3, cardType=2, cardNumber=4123123412341234, cardExpires=05/2024, billingAddressID=3 Write an SQL statement to delete all orders that are created between 2017-06-01 and 2017-06-07.
What happens if we don’t specify the WHERE clause in an SQL DELETE FROM statement? a) statement compilation causes a “Syntax error”, and the statement is not executed. b) all the tuples of the relaton are deleted. c) none of the tuples of the relation is deleted. d) all the tuples not satisfying the condition are deleted. e) none of the above. Which one(s) of the following is(are) procedural? a) Relational Algebra b) Relational Calculus c) Tuple Relational Calculus d) Domain Relational Calculus e) SQL f) (a) and (e) e) (b) and (e).
In the SQL Oracle Application Write one query to return the employee_id, job_id, hire_date, and department_id of all employees and a second query listing employee_id, job_id, start_date, and department_id from the job_history table and combine the results as one single output. Make sure you suppress duplicates in the output.
In The SQL Oracle Application Using the UNION operator, write a query that displays the employee_id, job_id, and salary of ALL present and past employees. If a salary is not found, then just display a 0 (zero) in its place.
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 ?
Note: PL/SQL can be executed in SQL*Plus or SQL Developer or Oracle Live SQL. 1. Write an anonymous block to retrieve the doctor’s ID and name which in charge of certain patient. Allow the user to enter the patient’s ID. 2. Write an anonymous PL/SQL block that will insert a new doctor into the DOCTOR table. Verify that insert has been done successfully by issuing a select * from doctor. 3. Write an anonymous PL/SQL block that will update the salary of all doctors in the Pediatrics area by 1000 (Note: Current salary + 1000). Verify that the salary has been updated by issuing a select * from doctor where area = ‘Pediatrics’. You may have to run the select statement twice to check the data before and after the update. 4. Write an anonymous PL/SQL block that will delete all patients for doctors that works in the Family Practice area. Verify that patients have been deleted accordingly. Then do a select * from patients where doc_id = (select doc_id from doctor where area = ‘Family Practice’.…
ina sql developer code .i have added for price <150 like ADD CONSTRAINT price CHECK (price < 150); DECLARE price BEGIN exception insert into orders values (430 king street',books,225); when (price > 150); dbms_output.put_line('too expensive '); END;when price is over 150 ,will show too expensivewhats the wrong of my code ?how to fix it ?
The given SQL creates a Movie table and inserts some movies. The SELECT statement selects all movies. Modify the SELECT statement to select movies with the word 'star' somewhere in the title. Run your solution and verify the result table shows just the movies Rogue One: A Star Wars Story, Star Trek and Stargate. CREATE TABLE Movie ( ID INT AUTO_INCREMENT, Title VARCHAR(100), Rating CHAR(5) CHECK (Rating IN ('G', 'PG', 'PG-13', 'R')), ReleaseDate DATE, PRIMARY KEY (ID)); INSERT INTO Movie (Title, Rating, ReleaseDate) VALUES ('Rogue One: A Star Wars Story', 'PG-13', '2016-12-16'), ('Star Trek', 'PG-13', '2009-05-08'), ('The Dark Knight', 'PG-13', '2008-07-18'), ('Stargate', 'PG-13', '1994-10-28'), ('Avengers: Endgame', 'PG-13', '2019-04-26'); -- Modify the SELECT statement:SELECT *FROM Movie;