SQL: Oracle SQL Samples
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 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.
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;
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.
SELECT * FROM ( SELECT employee_id, last_name, department_id, salary FROM employees ORDER BY salary DESC ) WHERE ROWNUM <= 4;
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.
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);
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.
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;
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.
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;