DATABASE PROGRAMMING - WRITE THE SQL QUERIES FOR THE FOLLOWING QUESTIONS

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;

Q1. (20 points) Please edit the codes, use cursor for loop (For index IN cursor_name LOOP). The output of your new codes should be the same as the original program.

DECLARE

CURSOR c_emp IS

SELECT last_name ||', ' || first_name Full_name, salary

FROM employees

WHERE department_id in (10, 20)

ORDER BY salary DESC;

Fname varchar2 (45);

v_salary employees.salary%TYPE;

BEGIN

DBMS_OUTPUT.put_line (' No Emp Full Name Salary ');

DBMS_OUTPUT.put_line ('--- --------------------- --------');

OPEN c_emp;

LOOP

FETCH c_emp INTO Fname, v_salary;

EXIT WHEN c_emp%NOTFOUND;

DBMS_OUTPUT.put_line (rpad ( c_emp%ROWCOUNT, 7) ||

RPAD (Fname, 25) || to_char (v_salary, '$99,999'));

END LOOP;

CLOSE c_emp;

END;

Q2. (20 points)

Assume that the company has decided a one-time bonus for the employees in department 80 only, the amount is decided as below.

For employees that get commission_pct equal to or great than .25:

if salary > = 10000 then bonus := 1000

if salary < 10000 and salary >= 7000 then bonus := 800

If salary < 7000 then bonus := 600

For employees that get commission_pct less than .25 and equal and great than .15:

if salary > = 10000 then bonus := 700

if salary < 10000 and salary >= 7000 then bonus := 600

If salary < 7000 then bonus := 500

All other employees in department 80 will get $450.

Write an anonymous PL/SQL program, retrieve the info needed for each employee (department 80 only), calculate the amount of bonus he/she should get. Your program will accumulate the bonus from each employee, print out the total amount of the bonuses.

Q3. (20 points)

This question will use some views(tables) in the system catalog: all_tab_columns.

All_tables lists all tables that user can read, but it does not list the details of the columns in each table. In the view all_tab_columns, it list the owner of the tables, the table_name and information of columns under each table, such as the column_name, the column data_type and the length of data_type of that column. The command desc all_tab_columns will display the info related. Do not simply select * from all_tab_columns, the output is not easy to read. You need to use SQL Plus command to format the output, such as:

column Owner format A10

column table_name format A30

In this question, you will write an anonymous PL/SQL program. The program will define a cursor with two parameters. The select statement will retrieve the column_name, data_type and data_length from the view all_tab_columns for a certain owner and certain table_name that will be passed to it through the parameters.

In the executable section, you will open that cursor two times, first time with the parameters of USER and EMPLOYEES, the second time with USER and DEPARTMENTS. Your program will print out the information you have retrieved.

Q4. (20 points) Cursor for update, based on table employees. Write an anonymous PL/SQL program, it will increase the salary to 107% (*1.07) for those employees whose salary is less than 2,399 and without commission.

Your program should declare a cursor with FOR UPDATE request, thus it can request the system to lock those records retrieved and change these records later.

After update, print out those affected employees, display their ID, last name, old salaries (before this time increase) and new salary.

It is better to rollback after the print out and before the end of program. (in the real life, program should commit the changes).

Q5. (20 points) Cursor Variable

Define a strong cursor variable type, name it as empcurtyp. Its return type is

employees%ROWTYPE, based on table Employees.

Then declare a cursor variable of this type.

In your program, first open the cursor variable for a select statement as

SELECT * FROM employees

WHERE department_id = 60

ORDER BY last_name;

print out the employees employee ID and full names in department 60.

(please pay attention, the Cursor-For loop does NOT work for cursor variable)

Then, open that cursor variable again, this time for the statement of

SELECT * FROM employees

WHERE commission_pct is null and salary < 2399

ORDER BY employee_id ;

then print out the employee IDs, department_ID, last name and their salary. Last, do not forget to close the cursor variable.