Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I need the SQL query to create a procedure on PHPMyAdmin, Here are the instructions: Here are the tables on the company database: Here is
I need the SQL query to create a procedure on PHPMyAdmin, Here are the instructions:
Here are the tables on the company database:
Here is an example of another procedure on the same database, so please it should look similar to this:
Create a user defined stored procedure (uspGetDeptStats) on the Company database that would accept a department name (varchar of maximum 15 character text string) and then generates the following statistics only if the given department name exists: The firstname and the lastname of the manager of this department. The number of employees work for this department. The total salary of all employees work for this department. The total number of dependents of all employees who work for this department. The total weekly workhours of employees who belong to this department and who work on the projects that are controlled by this department. LNAME SSN BDATE Smith EMPLOYEE FNAME MINIT John B Franklin Alicia J Jennifer S Ramesh Joyce A Ahmad V James E Wong Zelaya Wallace Narayan English Jabbar Borg 123456789 333445555 999887777 987654321 666884444 453453453 987987987 888665555 1965-01-09 1955-12-08 1968-07-19 1941-06-20 1962-09-15 1972-07-31 1969-03-29 1937-11-10 ADDRESS 731 Fondren, Houston, TX 638 Voss, Houston, TX 3321 Castle, Spring, TX 291 Berry, Bellaire, TX 975 Fire Oak, Humble, TX 5631 Rice, Houston, TX 980 Dallas, Houston, TX 450 Stone, Houston, TX SEX SALARY M 30000 M 40000 F 25000 F 43000 M 38000 F 25000 M 25000 M 55000 SUPERSSN DNO 333445555 5 888665555 5 987654321 4 888665555 4 4 333445555 5 333445555 5 987654321 4 null 1 DEPT_LOCATIONS DNUMBER 1 4 5 DLOCATION Houston Stafford Bellaire Sugarland Houston DEPARTMENT 5 DNAME Research Administration Headquarters DNUMBER 5 4 1 MGRSSN 333445555 987654321 888665555 MGRSTARTDATE 1988-05-22 1995-01-01 1981-06-19 5 WORKS ON PROJECT PNUMBER PLOCATION DNUM 5 1 2 ESSN 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 PNO HOURS 1 32.5 2 7.5 3 40.0 1 20.0 2 20.0 2 10.0 3 10.0 10 10.0 20 10.0 30 30.0 10 10.0 10 35.0 30 5.0 30 20.0 20 15.0 20 null PNAME ProductX ProductY Productz Computerization Reorganization Newbenefits 3 Bellaire Sugarland Houston Stafford Houston Stafford 5 5 4 10 20 1 30 4 DEPENDENT ESSN DEPENDENT NAME SEX BDATE RELATIONSHIP Alice Theodore F M F 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Joy DAUGHTER SON SPOUSE SPOUSE 1986-04-05 1983-10-25 1958-05-03 1942-02-28 1988-01-04 1988-12-30 1967-05-05 Abner Michael Alice Elizabeth SON M M F F DAUGHTER SPOUSE DELIMITER $$ CREATE DEFINER='root'@'localhost' PROCEDURE `uspEmpHours'( -- Add the parameters for the stored procedure here IN empSSN char(9) ) BEGIN /* Only session variables are prefixed with '@' http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference */ -- Declare local variables declare currEmp char(9); declare totHours Decimal (5,1); -- Insert statements for procedure here SELECT ssn INTO currEmp From employee where ssn = empSSN; IF (currEmp is NULL) THEN SELECT concat('The input SSN value ', empSSN, ' is not a valid SSN') As ErrorMessage; ELSE select sum(hours) INTO totHours from works_on where essn = empSSN; -- cast function to varchar data type causes compilation error SELECT concat('Total work hours for ', empSSN, ' is ', cast(totHours as char(10))) as Answer; END IF; END$$ DELIMITERStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started