Scribd Logo
Upload

Welcome to Scribd!

  • 234 views

    Práctica 7

    Uploaded by

    Itzzy Prin
    The document contains 7 SQL queries and instructions for an Oracle DBA practicum. Query 1 prompts the user for an employee last name and displays coworkers in the same department excluding the entered employee. Query 2 displays employees earning more than the average salary sorted by ascending salary. Query 3 displays employees working in a department with anyone with "u" in their last name. Query 4 displays employees in a given location ID for a HR report. Query 5 displays employees reporting to "King". Query 6 displays the Executive department employees. Query 7 modifies Query 3 to add a salary filter.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd

    Práctica 7

    Uploaded by

    Itzzy Prin
    234 views9 pages
    The document contains 7 SQL queries and instructions for an Oracle DBA practicum. Query 1 prompts the user for an employee last name and displays coworkers in the same department excluding the entered employee. Query 2 displays employees earning more than the average salary sorted by ascending salary. Query 3 displays employees working in a department with anyone with "u" in their last name. Query 4 displays employees in a given location ID for a HR report. Query 5 displays employees reporting to "King". Query 6 displays the Executive department employees. Query 7 modifies Query 3 to add a salary filter.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains 7 SQL queries and instructions for an Oracle DBA practicum. Query 1 prompts the user for an employee last name and displays coworkers in the same department excluding the entered employee. Query 2 displays employees earning more than the average salary sorted by ascending salary. Query 3 displays employees working in a department with anyone with "u" in their last name. Query 4 displays employees in a given location ID for a HR report. Query 5 displays employees reporting to "King". Query 6 displays the Executive department employees. Query 7 modifies Query 3 to add a salary filter.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    234 views9 pages

    Práctica 7

    Uploaded by

    Itzzy Prin
    The document contains 7 SQL queries and instructions for an Oracle DBA practicum. Query 1 prompts the user for an employee last name and displays coworkers in the same department excluding the entered employee. Query 2 displays employees earning more than the average salary sorted by ascending salary. Query 3 displays employees working in a department with anyone with "u" in their last name. Query 4 displays employees in a given location ID for a HR report. Query 5 displays employees reporting to "King". Query 6 displays the Executive department employees. Query 7 modifies Query 3 to add a salary filter.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 9

    Prctica 7 Oracle DBA

    Itzel Garca Gonzlez 7.1


    The HR department needs a query that prompts the user for an employee last name. The query then displays the last name and hire date of any employee in the same department as the employee whose name they supply (excluding that employee). For example, if the user enters Zlotkey, find all employees who work with Zlotkey (excluding Zlotkey). UNDEFINE Enter_name SELECT last_name, hire_date FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE last_name = '&&Enter_name') AND last_name <> '&Enter_name';

    7.2
    Create a report that displays the employee number, last name, and salary of all employees who earn more than the average salary. Sort the results in order of ascending salary. SELECT employee_id, last_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) ORDER BY salary;

    7.3
    Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains a u. Save your SQL statement as lab_07_03.sql. Run your query. SELECT employee_id, last_name FROM employees WHERE department_id IN (SELECT department_id FROM employees WHERE last_name like '%u%');

    7.4
    The HR department needs a report that displays the last name, department number, and job ID of all employees whose department location ID is 1700 SELECT last_name, department_id, job_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

    Modify the query so that the user is prompted for a location ID. Save this to a file named lab_07_04.sql
    SELECT last_name, department_id, job_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = &Enter_location);

    7.5
    Create a report for HR that displays the last name and salary of every employee who reports to King.

    SELECT last_name, salary FROM employees WHERE employee_id = (SELECT employee_id FROM employees WHERE last_name = 'King');

    SELECT employee_id, first_name, last_name FROM employees WHERE last_name = 'King'

    SELECT last_name, salary FROM employees WHERE manager_id = 100; SELECT last_name, salary FROM employees WHERE manager_id = 156;

    7.6
    Create a report for HR that displays the department number, last name, and job ID for every employee in the Executive department. SELECT department_id, last_name, job_id FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Executive');

    7.7
    Modify the query in lab_07_03.sql to display the employee number, last name, and salary of all employees who earn more than the average salary and who work in a department with any employee whose last name contains a u. Resave lab_07_03.sql to lab_07_07.sql. Run the statement in lab_07_07.sql. SELECT employee_id, last_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM employees WHERE last_name like '%u%') AND salary > (SELECT AVG(salary) FROM employees);

    You might also like