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.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

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.
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 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
Display the first name, last name and salary of all employees hired after “01-JAN-2006‟. Handle exceptions like Invalid cursor, cursor already open and no data found with oracle pl sql
PL/SQL Display the first name and last name of all employees who work for department 50 or have a salary greater than 3500. Handle the exceptions like No Data Found and Invalid Cursor
SQL code for: Create a stored procedure “setRelocationFee” to set the relocation fee for a given employee. If the employee’s office is in San Francisco, the relocation fee is $10000; if the employee’s office is in Boston, the relocation fee is $8000; if the employee’s office is in London, the relocation fee is $20000; if the employee works in other offices, the relocation fee is $15000. Below is a sample statement to test your stored procedure. set @employeeID = 1501; call setRelocationFee(@employeeID, @relocationfee); select @employeeID, @relocationfee; Having this database CREATE TABLE `offices` ( `officeCode` varchar(10) NOT NULL, `city` varchar(50) NOT NULL, `phone` varchar(50) NOT NULL, `addressLine1` varchar(50) NOT NULL, `addressLine2` varchar(50) DEFAULT NULL, `state` varchar(50) DEFAULT NULL, `country` varchar(50) NOT NULL, `postalCode` varchar(15) NOT NULL, `territory` varchar(10) NOT NULL, PRIMARY KEY (`officeCode`) ) ENGINE=InnoDB DEFAULT…
Guideline: Pls create employees table and populate it with the scripts provided for you (if the employees table does not exist) 1. PL/SQL Cursor: Using Cursor, please write PL/SQL program to retrieve data from employees table and display employee first and last name. 2. PL/SQL Exceptions: Write PL/SQL program to data from employees table and handle the following exceptions 1) NO_DATA_FOUND 2) TOO_MANY_ROWS 3) WHEN OTHERS 4) VALUE_ERROR - This exception is raised, when the value which is returned does not match with the datatype variable. 5) DUP_VAL_ON_INDEX - This exception is raised when we try to insert a duplicate value on a primary key or unique key
Write SQL code for the following: Create a stored procedure “setRelocationFee” to set the relocation fee for a givenemployee. If the employee’s office is in San Francisco, the relocation fee is $10000; ifthe employee’s office is in Boston, the relocation fee is $8000; if the employee’s officeis in London, the relocation fee is $20000; if the employee works in other offices, therelocation fee is $15000. (see image for table structure) I am stuck for the part that I have to select employeeNumber and officeCode and set the relocationFee to be able to obtain the result but I do not know how. Bellow is what I have gotten. Delimiter| CREATE PROCEDURE setRealocationFee(IN EmployeeID INT(11), OUT realocationFee INT(5))
USE NORTHWIND DATABASE Write a SELECT statement using the CASE function. The statement returns two columns basedon how many orders the customer has in the database. The first column is the customer name andthe second column, Membership Type, is based on which case applies to the customer:If the customer has more than 25 orders they are a Diamond Customer.If the customer has more than 15 orders they are a Gold Customer.If the customer has more than 10 orders they are a Silver Customer.If the customer has more than 5 orders they are an Iron Customer.Otherwise the customer is a Valued Customer.
T-SQL procedure for MICROSOFT SQL SERVER A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these values in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. HINT use cursor instructions as a template for the problem. Instructions goes as follows CREATE PROCEDURE usp_DISP_REP_CUST @repnum char(2) AS DECLARE@custnum char(3) DECLARE@custname char(35) DECLARE mycursor CURSOR READ_ONLY FOR SELECT CUSTOMER_NUM, CUSTOMER_NAME FROM CUSTOMER WHERE REP_NUM = @repnum OPEN mycursor FETCH NEXT FROM mycursor INTO @custnum, @custname WHILE @@FETCH_STATUS=0 BEGIN PRINT@custnum+' '+@custname FETCH NEXT FROM mycursor INTO @custnum, @custname END CLOSE mycursor DEALLOCATE mycursor
PL/SQL Display the first name and salary*10000 of all employees who works for department 50. Handle exceptions like Invalid cursor, cursor already open and no data found. Multi Line Text.
PL/SQL Display the first name, last name and salary of all employees hired after “01-JAN-2006‟. Handle exceptions like Invalid cursor, cursor already open and no data found. Multi Line Text.