Please go through the below Function and Exception spec:(a) Function: A Function must be created that will accept the dept_id as input. Based on this input, the function must return the department name. Function name : find_dept_name, Input Parameter : dept_id in intOutput Parameter : A VARIABLE WITH varchar TYPE Design rules:1)If department id(ie dept_id) passed as input, matches with the department_id in the department table,then it returns the department_name of the given dept_id. 2)If the deparment id passed as input, does not match with the department_id in the department table,then it throws 'no_data_found' exception and display it as 'No such department'Note: Kindly use variable to print the exceptions instead of 'dbms_output.put_line'ie: umpire_name := 'No such umpire';Note:Do not change the function nameDo not change the argument count and orderDo not change the output text. Instructions:1. Create the function successfully2. Once the function is created, check the functionality of the function.3. Submit only the CREATE FUNCTION query. SubmitSaveExecutePrevious Submission1
Question
Please go through the below Function and Exception spec:(a) Function: A Function must be created that will accept the dept_id as input. Based on this input, the function must return the department name. Function name : find_dept_name, Input Parameter : dept_id in intOutput Parameter : A VARIABLE WITH varchar TYPE Design rules:1)If department id(ie dept_id) passed as input, matches with the department_id in the department table,then it returns the department_name of the given dept_id. 2)If the deparment id passed as input, does not match with the department_id in the department table,then it throws 'no_data_found' exception and display it as 'No such department'Note: Kindly use variable to print the exceptions instead of 'dbms_output.put_line'ie: umpire_name := 'No such umpire';Note:Do not change the function nameDo not change the argument count and orderDo not change the output text. Instructions:1. Create the function successfully2. Once the function is created, check the functionality of the function.3. Submit only the CREATE FUNCTION query. SubmitSaveExecutePrevious Submission1
Solution
It seems like you are asking for a function to be created in SQL. Here is a basic structure of how you can create this function:
CREATE OR REPLACE FUNCTION find_dept_name(dept_id IN NUMBER)
RETURN VARCHAR2 IS
dept_name VARCHAR2(100);
BEGIN
SELECT department_name INTO dept_name FROM department WHERE department_id = dept_id;
RETURN dept_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'No such department';
END find_dept_name;
This function find_dept_name takes an integer dept_id as an input. It then tries to find a department with this id in the department table. If it finds a department, it returns the name of the department. If it doesn't find a department with this id, it returns 'No such department'.
Please replace department and department_name with your actual table and column names. Also, please note that this function assumes that department_id is unique in your table. If it's not, this function will raise a TOO_MANY_ROWS exception.
Similar Questions
QueryException_2Please go through the below Function and Exception spec:(a) Function: Create a function named 'find_staff_name_by_dept' thatl accepts the dept_id as input. Based on this input, the function must return the staff name. ie the function returns the names of staff belonging to this department.Hints:Function name : find_staff_name_by_dept, Input Parameter : dept_id in numberOutputs : A VARIABLE WITH varchar TYPE Design rules:1)If department id(ie dept_id) passed as input, matches with the department_id in the department table,then it returns the staff_name of the given dept_id. 2)If the deparment id(ie dept_id) passed as input, does not match with the department_id in the department table,then it throws 'no_data_found' exception and display it as 'No Such Department'. 3)If the deparment has more than one staff ,then it throws an exeption 'TOO_MANY_ROWS' and display it as 'Multiple Rows Returned' .Note: Kindly use variable to print the exceptions instead of 'dbms_output.put_line'ie: umpire_name := 'No such umpire';Note:Do not change the procedure nameDo not change the argument count and orderDo not change the output text. Instructions:1. Create the procedure successfully2. Once the procedure is created, check the functionality of the procedure using different anonymous block call.3. DO NOT submit the anonymous block. Submit only the CREATE PROCEDURE query. SubmitSaveExecute
Problem StatementWrite a query to display the details of employees who are not in the 'Developer' department. Table: EmployeeInput format :The input table is already created, and records are already prepopulated, as mentioned in the problem statement.Output format :The output displays a list of employees who work in departments other than 'Developer' as shown below.id name Department102 Stark HR104 Jack Finance106 Scott AdminRefer to the sample output for the column headers.Note :The program will be evaluated only after the “Submit Code” is clicked.Extra spaces and new line characters in the program output will result in the failure of the test case.
Create a package named "dept_package".Declare and define the following 4 Procedures in this package.1) Procedure to insert a new record in the department table. Name : addDepartment Parameters and their types : dept_id department.department_id%type dept_name department.department_name%type dept_block_number department.department_block_number%type 2) Procedure to delete a department when department id is passed as the input parameter. Name : deleteDepartment Parameters and their types : dept_id department.department_id%type 3) Procedure to list the details of all departments (id, name and block number) in ascending order based on id. Name : listDepartment Parameters and their types : It uses cursors dept_details OUT SYS_REFCURSOR 4) Procedure to update a department name (Given the old dept name and new dept name) Name : updateDepartment Parameters and their types : dept_name department.department_name%type new_dept_name department.department_name%type Note:Do not change the package or procedure nameDo not change the argument count and orderDo not change the output text.Instructions:1. Create the package successfully2. Once the package is created, check the functionality of the procedure using different anonymous block call.3. DO NOT submit the anonymous block. Submit only the CREATE PACKAGE query. SubmitSaveExecutePrevious Submission
Evaluate below sql statement SELECT emp_name, department FROM employees WHERE department = 'HR' UNION SELECT contractor_name, department FROM contractors WHERE department = 'IT';Select one:a. Retrieves the names of all employees and contractors from the HR and IT departments.b. Retrieves the names of employees from the HR department and all contractors.c. Retrieves the names of employees and contractors from the HR and IT departments, excluding employees from the IT department and contractors from the HR department.d. Retrieves the names of employees from the HR department and contractors from the IT department.
Review this SQL Statement: SELECT ename, emp_number, salary FROM employee WHERE dept_number = (SELECT dept_number FROM department WHERE location IN('CHICAGO','ATLANTA')); Why may this statement return an error?
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.