DATABASE PROGRAMMING

WRITE SQL QUERIES FOR THE FOLLOWING QUESTIONS

1. List all the department IDs in the company, and the amount of highest income (salary plus commission_pct * salary), lowest income for that department. Do not list those departments that do not have employee in it.
2. List the employee IDs, last names, first names and department IDs for those whose last name is not unique among the employees.
3. List the last names, first names, their department ID, their (direct supervisor) manager ID (in employees table) and their department (head) manager ID (in departments table) for those employees who work in department (ID) among the list of 10, 20, 30, 40, 60, and their direct manager/supervisor are not their department manager.
4. List those people's last name, first name and their department ID, who are in charge of a department (as manager_id in departments table), but are not supervisor for any employee directly (their employee_ids do not how on the manager_id column in Employees table).

Questions 9 and 10 are for PL/SQL coding.

9. Write an anonymous PL/SQL block. In the block, declare a variable called emp_name, data type anchored same as the data type of last_name in Employees table, another variable named income, data type as that of salary in Employees table. In the executable section, assign the value of emp_name as King, salary as 12345, then use the DBMS_OUTPUT.PUT_LINE (' ' ) command to print out the values of that employees name and income. Please use the function TO_CHAR to make the output of salary as popular money format like $12,345.00.

10. Based on Q9, assume that you have completed the code for Q9. Now add an inner block to the codes in Q9. To distinguish the outer block, the program needs to label the outer block ( give it a name, such as << outer >> ). The emp_name will be assigned value of King and income as $8,000.

In the inner block, you will declare these two variables again with the same name emp_name and income as in Q9 (now these are in your outer block), they have the same data types as in Q9. This time, in the inner block assign the emp_name as Bell, income to $4,000.

Now, you have defined the emp_name, income twice, one in the outer block, another in the inner block. In your program, you will print out the values/info of the emp_name and income three times: one time from outer block, and another two times from the inner block, one for the values defined in inner block, another for the values defined in outer block.

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;