Brian Manning | Project Portfolio

I completed a course on Oracle SQL last fall. I was familiar with standard joins, select, and insert statements but this class covered a lot more. This was one of my favorite classes. I really enjoyed the puzzle-like nature of the work.

Below are some samples of scripts written for the course. Each is prefaced with the specifications for the script.

Create View:

Create a view in your database that fulfills the following requirements:

• The view name is DEPTINFO.
• DEPTINFO will contain all the rows and columns currently in the Departments table, and, in addition will contain a column which contains each department manager’s name (which must be obtained from the Employees table by the view).
• The department’s manager name must be listed as a first name, followed by a space, followed by the last name.
• If a department has no manager, that row’s manager name field must contain the phrase: “NO MANAGER.”
• The rows of the view must be in order by department ID number.

Submit the series of SQL statements used to accomplish each step arranged in the same order as the steps are specified.

Show createView.sql

                
CREATE VIEW deptinfo
  AS SELECT d.department_id, d.department_name, d.manager_id, d.location_id,
    NVL(e.first_name, 'NO') || ' ' || NVL(e.last_name, 'MANAGER') AS manager_name
  FROM departments d LEFT JOIN employees e
    ON d.manager_id = e.employee_id
  ORDER BY department_id;

SELECT *
FROM deptinfo;

            

Nested Selects:

Write/execute a SQL SELECT statement that lists the employee_id, last_name, department_id and salary of the four highest-salaried employees in the Employees table. The SELECT statement may not be written to take advantage of the fact that it contains only 20 records. Rather, the SELECT must be one that would continue to work if the Employees table contained thousands of records and had salaries that were constantly changing from one day to the next.

Show nestedSelect.sql

                
  SELECT *
  FROM (  SELECT employee_id, last_name, department_id, salary
          FROM employees
          ORDER BY salary DESC  )
  WHERE ROWNUM <= 4;

            

Create Sequence:

Create a copy of your Departments table, named Dept_copy.

Create a sequence object, named as you wish, intended to be used in conjunction with dept_copy as a source of department_id numbers when rows are added to the Dept_copy table. The sequence object must be set up so that the first number it generates will be 300 and subsequent numbers will be 310, 320, 330, 340, etc.

Write two separate Insert statements to add new rows to the Dept_copy table using the sequence object to generate the new sequential department_id values for the rows. Use literals to place valid data into all the other fields on those rows.

Show createSequence.sql

                
CREATE TABLE dept_copy
  AS ( SELECT *
       FROM departments );

CREATE SEQUENCE dept_copy_departmentid_seq
  INCREMENT BY 10
  START WITH 300;

INSERT INTO dept_copy (department_id, department_name, manager_id, location_id)
  VALUES (dept_copy_departmentid_seq.NEXTVAL, 'Support', 103, 1400);

INSERT INTO dept_copy (department_id, department_name, manager_id, location_id)
  VALUES (dept_copy_departmentid_seq.NEXTVAL, 'Investment', 200, 1700);

            

Union:

Employees who work in location 1700 appear to make more money than the average of employees who work at all other locations. You are asked to substantiate this claim by providing salary information for two groups of employees, those who work at location 1700, and, those who work anywhere else (as a group). Write a single query that provides the following for each of these two groups: 1) a count of the number of employees who work at location 1700 vs all other locations. 2) the average salary of employees who work at location 1700 vs the average salary of employees who work anywhere else. The output may be one or two rows but the rows or columns should be labeled appropriately to distinguish which data is for location 1700 and non-1700.

Exclude from the analysis any employee who has a null in his/her department_id column, since for such employees there is no way to determine at what location they work. NOTE: The number “1700” is the only number which may be hard-coded as a literal at any point in this request.

Show union.sql

                
SELECT '1700' AS location_id,
        COUNT(*) AS number_employees,
        ROUND(AVG(salary), 2) AS avg_salary
  FROM employees INNER JOIN departments USING (department_id)
  WHERE location_id = 1700
UNION
SELECT 'All Others' AS location_id,
        COUNT(*) AS number_employees,
        ROUND(AVG(salary), 2) AS avg_salary
  FROM employees INNER JOIN departments USING (department_id)
  WHERE location_id != 1700;

            

PL/SQL:

Write a PL/SQL block which will bring back (via the PUT_LINE function) for department 110: the department_id, the department name, and the name of the city in which that department is located. The department_id of 110 should just be hardcoded within the block; it need not be passed in.

Show plsql.sql

                
SET serveroutput ON;

DECLARE
  v_dept_id     departments.department_id%TYPE;
  v_dept_name   departments.department_name%TYPE;
  v_city        locations.city%TYPE;

BEGIN

SELECT department_id, department_name, city
  INTO v_dept_id, v_dept_name, v_city
  FROM departments JOIN locations
    USING(location_id)
  WHERE department_id = 110;

DBMS_OUTPUT.PUT_LINE('ID' || CHR(9) || CHR(9) || 'NAME' || CHR(9) || CHR(9) || CHR(9) || CHR(9) || 'CITY');
DBMS_OUTPUT.PUT_LINE('*************************') ;
DBMS_OUTPUT.PUT_LINE(v_dept_id || CHR(9) || CHR(9) || v_dept_name || CHR(9) || v_city);


EXCEPTION
  WHEN  NO_DATA_FOUND
    THEN
      DBMS_OUTPUT.PUT_LINE
      ('The specified department id does not exist.');
END;