WRITE SQL QUERIES FOR THE FOLLOWING QUESTIONS WHICH IS BASED ON EMPLOYEES TABLE

1. Below is a sample of searched CASE statement. It finds the average salary of the employees in the Employees table, using $5,000 as the lowest salary in calculation (if the salary of an employee is less than $5,000, than count it as $5,000, not use it as the real salary amount)

Column Average_Salary format $99,999.99

SELECT AVG (CASE WHEN salary > 5000 THEN salary

ELSE 5000

END ) Average_Salary

FROM employees

where department_id = 60 ;

OUTPUT

AVERAGE_SALARY

--------------

$6,000.00

Write a PL/SQL block to do the same job as above, Instead of using the case statement as above, use if-else. Your program will calculate and print out the average salary of the department ID = 60. Your program will use the cursor to retrieve salary of each employee in Department 60, if salary < $5,000, count this as $5,000; if salary >= $5,000, then treat the salary as is.

2. Write a PL/SQL program which will insert a new record into the Departments table. The new department has department_id as 299, department name as Future, manager_ID as 145, location ID as 1700. After insertion, your code should display the contents of this record.
3. Delete the record with department_id of 299 that was just inserted in Q2. Print out the department name and the manager id for the record just deleted - via the variables in the returning clause of DELETE statement.
4. This question is based on the table EMPLOYEES. Assume that the company has decided a one-time bonus for all the employees, the policy is as below.

The total bonus consists of three parts.

(a) Each person will get a base amount $500.

(b) For employees that have worked 24 years or more, their bonus will add $240; if not reaches 24 years (less than) then no this work-year extra bonus.

This expression may help you to calculate the work year:

floor (months_between (sysdate, hire_date)/12)

(c) According to salary range, each may get the third part:

for those their salary > 10000, then get 200;

for those their salary <= 10000 and salary >= 6000, then get 400;

for those their salary < 6000, then get 800.

Write an anonymous PL/SQL program, retrieve the necessary information for some employee with a certain ID (say 114), then calculate the second, third part bonus that employee should get.

Suggest that you may choose these three employees (run your program three times, each with a different employee_ID) to test your code:

EMPLOYEE_ID SALARY YEARS

----------- --------- ----------

114 $11,000 25

122 $7,900 25

135 $2,400 20

5. Using basic loop, for loop and while loop. Write an anonymous PL/SQL blocks, inside the execution section, the program will use basic loop, for loop and while loop to display the values of a variable that changes from 25 to 27.

6. Based on table employees, please remember to rollback to recovery the table contents back to original when the table was created.

Write an anonymous PL/SQL block.

In the executable sections, give a 10 % raise of the salary to each employee who works in department_id = 60;

And delete the employees who has no department assigned yet (department_id is null);

Using SQL%ROWCOUNT, display how many rows are affected for each of the two statements.

7. Write an anonymous PL/SQL block, based on table employees. The program will print out the employee ID, full name (using concatenate operator to merge the last and first name), salary of those employees whose salary are among the top three in that company. In this question, you are required to define a cursor based record variable (cursor_name%ROWTYPE) for the FETCH into statement.

(You may use either the attribute %RWOCOUNT, or define a counter)

(This clause may help: select ... from employees order by salary desc)

CREATE TABLE dept (

deptno NUMBER(2,0),

dname VARCHAR2(14),

loc VARCHAR2(13),

CONSTRAINT n_pk_dept PRIMARY KEY (deptno)

);

CREATE TABLE emp(

empno NUMBER(4,0),

ename VARCHAR2(10),

job VARCHAR2(9),

mgr NUMBER(4,0),

hiredate DATE,

sal NUMBER(7,2),

comm NUMBER(7,2),

deptno NUMBER(2,0),

CONSTRAINT pk_emp PRIMARY KEY (empno),

CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)

);

I

/* DROP TABLE bonus;

CREATE TABLE bonus(

ename VARCHAR2(10),

job VARCHAR2(9),

sal NUMBER,

comm NUMBER );

*/

DROP TABLE salgrade CASCADE CONSTRAINTS;

CREATE TABLE salgrade(

grade NUMBER,

losal NUMBER,

hisal NUMBER );

CREATE TABLE regions

( region_id NUMBER CONSTRAINT region_id_nn NOT NULL

, region_name VARCHAR2(25) );

CREATE UNIQUE INDEX reg_id_pk ON regions (region_id);

ALTER TABLE regions

CREATE TABLE countries

( country_id CHAR(2) CONSTRAINT country_id_nn NOT NULL

, country_name VARCHAR2(40)

, region_id NUMBER

, CONSTRAINT country_c_id_pk PRIMARY KEY (country_id)

) ORGANIZATION INDEX;

ALTER TABLE countries

ADD ( CONSTRAINT countr_reg_fk FOREIGN KEY (region_id) REFERENCES regions(region_id)

) ;

CREATE TABLE locations

( location_id NUMBER(4)

, street_address VARCHAR2(40)

, postal_code VARCHAR2(12)

, city VARCHAR2(30) CONSTRAINT loc_city_nn NOT NULL

, state_province VARCHAR2(25)

, country_id CHAR(2)

) ;

CREATE UNIQUE INDEX loc_id_pk

ON locations (location_id) ;

ALTER TABLE locations

ADD ( CONSTRAINT loc_id_pk PRIMARY KEY (location_id)

, CONSTRAINT loc_c_id_fk FOREIGN KEY (country_id) REFERENCES countries(country_id)

) ;

CREATE TABLE departments

( department_id NUMBER(4)

, department_name VARCHAR2(30) CONSTRAINT dept_name_nn NOT NULL

, manager_id NUMBER(6)

, location_id NUMBER(4)

) ;

CREATE UNIQUE INDEX dept_id_pk

ON departments (department_id) ;

ALTER TABLE departments

ADD ( CONSTRAINT dept_id_pk PRIMARY KEY (department_id)

, CONSTRAINT dept_loc_fk FOREIGN KEY (location_id) REFERENCES locations (location_id)

) ;

CREATE TABLE jobs

( job_id VARCHAR2(10)

, job_title VARCHAR2(35) CONSTRAINT job_title_nn NOT NULL

, min_salary NUMBER(6)

, max_salary NUMBER(6)

) ;

CREATE UNIQUE INDEX job_id_pk ON jobs (job_id) ;

ALTER TABLE jobs

ADD ( CONSTRAINT job_id_pk PRIMARY KEY(job_id)

) ;

CREATE TABLE employees

( employee_id NUMBER(6)

, first_name VARCHAR2(20)

, last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL

, email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL

, phone_number VARCHAR2(20)

, hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL

, job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL

, salary NUMBER(8,2)

, commission_pct NUMBER(2,2)

, manager_id NUMBER(6)

, department_id NUMBER(4)

, CONSTRAINT emp_salary_min CHECK (salary > 0)

, CONSTRAINT emp_email_uk UNIQUE (email)

) ;

CREATE UNIQUE INDEX emp_emp_id_pk

ON employees (employee_id) ;

ALTER TABLE employees

ADD ( CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id)

, CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments

, CONSTRAINT emp_job_fk FOREIGN KEY (job_id) REFERENCES jobs (job_id)

, CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id) REFERENCES employees

) ;

ALTER TABLE departments

ADD ( CONSTRAINT dept_mgr_fk FOREIGN KEY (manager_id) REFERENCES employees (employee_id)

) ;

CREATE TABLE job_history

( employee_id NUMBER(6) CONSTRAINT jhist_employee_nn NOT NULL

, start_date DATE CONSTRAINT jhist_start_date_nn NOT NULL

, end_date DATE CONSTRAINT jhist_end_date_nn NOT NULL

, job_id VARCHAR2(10) CONSTRAINT jhist_job_nn NOT NULL

, department_id NUMBER(4)

, CONSTRAINT jhist_date_interval CHECK (end_date > start_date)

) ;

CREATE UNIQUE INDEX jhist_emp_id_st_date_pk

ON job_history (employee_id, start_date) ;

ALTER TABLE job_history

ADD ( CONSTRAINT jhist_emp_id_st_date_pk PRIMARY KEY (employee_id, start_date)

, CONSTRAINT jhist_job_fk FOREIGN KEY (job_id) REFERENCES jobs

, CONSTRAINT jhist_emp_fk FOREIGN KEY (employee_id) REFERENCES employees

, CONSTRAINT jhist_dept_fk FOREIGN KEY (department_id) REFERENCES departments

) ;

CREATE OR REPLACE VIEW emp_details_view

(employee_id, job_id, manager_id, department_id, location_id, country_id, first_name,

last_name, salary, commission_pct, department_name, job_title, city, state_province,

country_name,region_name)

AS SELECT

e.employee_id, e.job_id, e.manager_id, e.department_id, d.location_id, l.country_id, e.first_name,

e.last_name, e.salary, e.commission_pct, d.department_name, j.job_title, l.city, l.state_province,

c.country_name, r.region_name

FROM employees e, departments d, jobs j, locations l, countries c, regions r

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id

AND l.country_id = c.country_id

AND c.region_id = r.region_id

AND j.job_id = e.job_id

WITH READ ONLY;