Before you start this test, you need to create tables and insert data using following commands:
———–
CREATE TABLE dept ( deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(14) NOT NULL CONSTRAINT dept_dname_uq UNIQUE, loc VARCHAR2(13) );
CREATE TABLE emp ( empno NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0), comm NUMBER(7,2), deptno NUMBER(2) CONSTRAINT emp_ref_dept_fk REFERENCES dept(deptno) );
CREATE TABLE jobhist ( empno NUMBER(4) NOT NULL, startdate DATE NOT NULL, enddate DATE, job VARCHAR2(9), sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2), chgdesc VARCHAR2(80), CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate), CONSTRAINT jobhist_ref_emp_fk FOREIGN KEY (empno) REFERENCES emp(empno) ON DELETE CASCADE, CONSTRAINT jobhist_ref_dept_fk FOREIGN KEY (deptno) REFERENCES dept (deptno) ON DELETE SET NULL, CONSTRAINT jobhist_date_chk CHECK (startdate <= enddate) );
-- -- Load the 'dept' table --
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
-- -- Load the 'emp' table --
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
-- Load the 'jobhist' table --
INSERT INTO jobhist VALUES (7369,'17-DEC-80',NULL,'CLERK',800,NULL,20, 'New Hire');
INSERT INTO jobhist VALUES (7499,'20-FEB-81',NULL,'SALESMAN',1600,300,30, 'New Hire');
INSERT INTO jobhist VALUES (7521,'22-FEB-81',NULL,'SALESMAN',1250,500,30, 'New Hire');
INSERT INTO jobhist VALUES (7566,'02-APR-81',NULL,'MANAGER',2975,NULL,20, 'New Hire');
INSERT INTO jobhist VALUES (7654,'28-SEP-81',NULL,'SALESMAN',1250,1400,30, 'New Hire');
INSERT INTO jobhist VALUES (7698,'01-MAY-81',NULL,'MANAGER',2850,NULL,30, 'New Hire');
INSERT INTO jobhist VALUES (7782,'09-JUN-81',NULL,'MANAGER',2450,NULL,10, 'New Hire');
INSERT INTO jobhist VALUES (7788,'19-APR-87','12-APR-88','CLERK',1000,NULL,20, 'New Hire');
INSERT INTO jobhist VALUES (7788,'13-APR-88','04-MAY-89','CLERK',1040,NULL,20, 'Raise');
INSERT INTO jobhist VALUES (7788,'05-MAY-90',NULL,'ANALYST',3000,NULL,20, 'Promoted to Analyst');
INSERT INTO jobhist VALUES (7839,'17-NOV-81',NULL,'PRESIDENT',5000,NULL,10, 'New Hire');
INSERT INTO jobhist VALUES (7844,'08-SEP-81',NULL,'SALESMAN',1500,0,30, 'New Hire');
INSERT INTO jobhist VALUES (7876,'23-MAY-87',NULL,'CLERK',1100,NULL,20, 'New Hire');
INSERT INTO jobhist VALUES (7900,'03-DEC-81','14-JAN-83','CLERK',950,NULL,10, 'New Hire');
INSERT INTO jobhist VALUES (7900,'15-JAN-83',NULL,'CLERK',950,NULL,30, 'Changed to Dept 30');
INSERT INTO jobhist VALUES (7902,'03-DEC-81',NULL,'ANALYST',3000,NULL,20, 'New Hire');
INSERT INTO jobhist VALUES (7934,'23-JAN-82',NULL,'CLERK',1300,NULL,10, 'New Hire');
-----
Question 1 (1 point)
Which of the following code fragments would not raise an error?
Question 1 options:
CREATE OR REPLACE FUNCTION ship_calc(p_qty IN NUMBER)
RETURN NUMBER
IS
lv_ship_num NUMBER(5,2);
BEGIN
IF p_qty > 10 THEN
lv_ship_num := 11.00;
ELSE
lv_ship_num := 5.00;
END IF;
RETURN lv_ship_num;
END;
CREATE OR REPLACE FUNCTION ship_calc(p_qty IN NUMBER)
IS
lv_ship_num NUMBER(5,2);
BEGIN
IF p_qty > 10 THEN
lv_ship_num := 11.00;
ELSE
lv_ship_num := 5.00;
END IF;
RETURN lv_ship_num;
END;
CREATE OR REPLACE ship_calc(p_qty)
RETURN NUMBER
IS
lv_ship_num NUMBER(5,2);
BEGIN
IF p_qty > 10 THEN
lv_ship_num := 11.00;
ELSE
lv_ship_num := 5.00;
ENDIF;
RETURN lv_ship_num;
END;
CREATE OR REPLACE FUNCTION ship_calc(p_qty IN NUMBER)
RETURN NUMBER
BEGIN
IF p_qty > 10 THEN
lv_ship_num := 11.00;
ELSE
lv_ship_num := 5.00;
END IF;
RETURN lv_ship_num;
END;
Question 2 (1 point)
The ____ mode type is considered constant because it cannot be changed within the procedure
Question 2 options:
OUT
IN OUT
INOUTIN
IN
Question 3 (0.5 points)
PL/SQL is a procedural language extension for SQL.
Question 3 options:
True
False
Question 4 (0.5 points)
Following SQL statement returns more than one row:
select * from Cat where Age>10;
To access these rows within a PL/SQL block, one should declare a Variable of type Cat.Age and use ÍNTO clause within the select statement.
Question 4 options:
True
False
Question 5 (3 points)
The following code cannot be compiled because of two (2) syntax errors. Find the errors and correct them.
Note: In your answers, clearly identify each error and the correction for each error. Also give the final executed code.
create or replace function get_department_man_name(dept_id number)
return varchar is
v_ename HR.employees.first_name&type;
begin
select first_name into v_ename
from HR.employees
where employee_id = ( select manager_id from HR.departments where department_id = deptid);
return v_ename;
end;
/
Question 5 options:
Question 6 (3 points)
Following question relates to one or combination of following concepts: (If-then-else & Loops)
Create a PL/SQL block that checks whether the department name of a given employee is Sales or not. You need to use HR database for this question. Steps involved in that process:
a. Declare a variable v_empid of type NUMBER in the declarative section. Initialise it to 101 [id of the given employee];
b. Declare a variable v_deptid of type NUMBER in the declarative section.
c. Declare a variable v_deptname of type same as DEPARTMENTS.Department_name column.
d. Start the executable section with the BEGIN keyword and include a SELECT statement to retrieve the department id of the given employee from EMPLOYEES table.
e. Using the department id retrieved in previous step, retrieve department name from the DEPARTMENTS table.
f. Check whether the department name is same as ‘Sales’ or not.
g. If it is not ‘Sales’, then display that ‘given employee does not belong to Sales department’. Otherwise, display that ‘given employee belong to Sales department’.
Question 6 options:
Question 7 (2 points)
Modify Question 5 so that it becomes a PL/SQL function CheckDept which takes employee id as input parameter and return true or false depending on whether a given employee is from Sales department or not.
Question 7 options:
Question 8 (4 points)
Following question relates to one or combination of following concepts: (Cursor, Exception & Trigger)
Write a Pl/SQL code to display the number of employees that belongs to each department with their department ids. The output should be displayed in following format: “<
Question 8 options:
Submit Quiz0 of 8 questions saved