Scribd Logo
Upload

Welcome to Scribd!

  • 32 views

    m8 Formdoc Bautista Ishmael

    Uploaded by

    Ish Bautista
    The document provides details about a laboratory exercise for an Information Management course. The exercise focuses on using advanced SQL statements to query and manipulate data from multiple tables. Students will write SQL queries involving joins, subqueries, and other SQL features. The exercise is intended to help students gain skills in using SQL commands to retrieve and manage organizational data from relational databases.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    m8 Formdoc Bautista Ishmael

    Uploaded by

    Ish Bautista
    32 views12 pages
    The document provides details about a laboratory exercise for an Information Management course. The exercise focuses on using advanced SQL statements to query and manipulate data from multiple tables. Students will write SQL queries involving joins, subqueries, and other SQL features. The exercise is intended to help students gain skills in using SQL commands to retrieve and manage organizational data from relational databases.

    Original Description:

    m8 infoman lab

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document provides details about a laboratory exercise for an Information Management course. The exercise focuses on using advanced SQL statements to query and manipulate data from multiple tables. Students will write SQL queries involving joins, subqueries, and other SQL features. The exercise is intended to help students gain skills in using SQL commands to retrieve and manage organizational data from relational databases.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    32 views12 pages

    m8 Formdoc Bautista Ishmael

    Uploaded by

    Ish Bautista
    The document provides details about a laboratory exercise for an Information Management course. The exercise focuses on using advanced SQL statements to query and manipulate data from multiple tables. Students will write SQL queries involving joins, subqueries, and other SQL features. The exercise is intended to help students gain skills in using SQL commands to retrieve and manage organizational data from relational databases.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 12

    COLLEGE OF COMPUTER STUDIES AND MULTIMEDIA ARTS

    CCS0021L
    (INFORMATION MANAGEMENT)

    EXERCISE

    8
    ADVANCED SQL
    Student Name / Bautista, Ishmael Jehoshaphat
    Group Name: J.

    Name Role
    Members (if Group):
    CCS0021L – TN21
    Section:

    Ma’am Beau Habal


    Professor:

    I. PROGRAM OUTCOME/S (PO) ADDRESSED BY THE LABORATORY EXERCISE


    a. Apply knowledge through the use of current techniques and tools necessary for the IT profession. [PO:
    I]

    II. COURSE LEARNING OUTCOME/S (CLO) ADDRESSED BY THE LABORATORY


    EXERCISE

    • Create SQL statements that retrieve information requirements of the organization needed for reports
    generation. [CLO: 4]

    III. INTENDED LEARNING OUTCOME/S (ILO) OF THE LABORATORY EXERCISE


    At the end of this exercise, students must be able to:
    • Use SQL command to manipulate the data in the table
    • Use the commit, rollback and save point as transaction control

    IV. BACKGROUND INFORMATION

    Write the appropriate SQL statement for the following queries. The result of the queries will be
    checked from your computer.

    • Use a join to query data from more than one table:

    CCS0021L-Information Management Page 2 of 12


    SELECT table1.column, table2.column
    FROM table1
    [NATURAL JOIN table2] |
    [JOIN table2 USING (column_name)] |
    [JOIN table2
    ON (table1.column_name = table2.column_name)]|
    [LEFT|RIGHT|FULL OUTER JOIN table2
    ON (table1.column_name = table2.column_name)]|
    [CROSS JOIN table2];

    • Join–a relational operation that causes two or more tables with a common domain to be
    combined into a single table or view
    • Equi-join–a join in which the joining condition is based on equality between values in the
    common columns; common columns appear redundantly in the result table
    • Natural join–an equi-join in which one of the duplicate columns is eliminated in the result
    table
    • Outer join–a join in which rows that do not have matching values in common columns
    are nonetheless included in the result table (as opposed to inner join, in which rows must
    have matching values in order to appear in the result table)
    • Union join–includes all columns from each table in the join, and an instance for each row
    of each table
    • The common columns in joined tables are usually the primary key of the dominant table
    and the foreign key of the dependent table in 1:M relationships. Example:

    Processing Multiple Tables Using Subqueries

    • Subquery–placing an inner query (SELECT statement) inside an outer query


    • Options:
    – In a condition of the WHERE clause – As a “table” of the FROM clause
    – Within the HAVING clause
    • Subqueries can be:
    – Noncorrelated–executed once for the entire outer query
    – Correlated–executed once for each row returned by the outer query
    Example

    CCS0021L-Information Management Page 3 of 12


    V. GRADING SYSTEM / RUBRIC

    VI. LABORATORY ACTIVITY

    Part 1 Multiple Queries

    1. Write a query for the HR department to produce the addresses of all the departments.
    Use the LOCATIONS and COUNTRIES tables. Show the location ID, street address,
    city, state or province, and country in the output. Use a NATURAL JOIN to produce the
    results.
    SELECT LOCATION_ID, STREET_ADDRESS, CITY, STATE_PROVINCE, COUNTRY_ID
    FROM LOCATIONS
    NATURAL JOIN COUNTRIES;

    2. The HR department needs a report of all employees. Write a query to display the last
    name, department number, and department name for all the employees.
    SELECT LAST_NAME, DEPARTMENT_ID, DEPARTMENT_NAME
    FROM EMPLOYEES
    JOIN DEPARTMENTS
    USING(DEPARTMENT_ID);

    CCS0021L-Information Management Page 4 of 12


    3. The HR department needs a report of employees in Toronto. Display the last name, job,
    department number, and the department name for all employees who work in Toronto.
    SELECT E.LAST_NAME, E.JOB_ID, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
    FROM EMPLOYEES E JOIN DEPARTMENTS D
    ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
    JOIN LOCATIONS L
    ON (D.LOCATION_ID = L.LOCATION_ID)
    WHERE LOWER (L.CITY) = 'toronto';

    4. Create a report to display employees’ last name and employee number along with their
    manager’s last name and manager number. Label the columns Employee, Emp#,
    Manager, and Mgr#, respectively.
    SELECT W.LAST_NAME "EMPLOYEE", W.EMPLOYEE_ID "EMP#",
    M.LAST_NAME "MANAGER", M.EMPLOYEE_ID "MGR#"
    FROM EMPLOYEES W JOIN EMPLOYEES M
    ON (W.MANAGER_ID = M.EMPLOYEE_ID);

    CCS0021L-Information Management Page 5 of 12


    5. Modify question#4 to display all employees including King, who has no manager. Order
    the results by the employee number.
    SELECT W.LAST_NAME "EMPLOYEE", W.EMPLOYEE_ID "EMP#",
    M.LAST_NAME "MANAGER", M.EMPLOYEE_ID "MGR#"
    FROM EMPLOYEES W
    LEFT OUTER JOIN EMPLOYEES M
    ON (W.MANAGER_ID = M.EMPLOYEE_ID);

    6. Create a report for the HR department that displays employee last names, department
    numbers, and all the employees who work in the same department as a given employee.
    Give each column an appropriate label.
    SELECT E.DEPARTMENT_ID DEPARTMENT, E.LAST_NAME EMPLOYEE,
    C.LAST_NAME COLLEAGUE
    FROM EMPLOYEES E JOIN EMPLOYEES C
    ON (E.DEPARTMENT_ID = C.DEPARTMENT_ID)
    WHERE E.EMPLOYEE_ID <> C.EMPLOYEE_ID
    ORDER BY E.DEPARTMENT_ID, E.LAST_NAME, C.LAST_NAME;

    CCS0021L-Information Management Page 6 of 12


    7. The HR department needs a report on job title and salaries. To familiarize yourself with
    the JOBS table, first show the structure of the JOBS table. Then create a query that
    displays the name, job title, department name, and salary for all employees.
    DESC JOBS
    SELECT E.LAST_NAME, E.JOB_ID, D.DEPARTMENT_NAME,
    E.SALARY, J.GRADE_LEVEL
    FROM EMPLOYEES E JOIN DEPARTMENTS DEPARTMENTS
    ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
    JOIN JOBS J
    ON (E.SALARYS BETWEEN J.LOWEST_SAL AND J.HIGHEST_SAL);

    Part 2 Subqueries
    8. 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).
    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';

    9. 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 ASC;

    CCS0021L-Information Management Page 7 of 12


    10. 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.
    SELECT EMPLOYEE_ID, LAST_NAME
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES
    WHERE LAST_NAME LIKE '%U%');

    11. 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.
    Modify the query so that the user is prompted for a location ID.
    SELECT LAST_NAME, DEPARTMENT_ID, JOB_ID
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
    FROM DEPARTMENTS
    WHERE LOCATION_ID = 1700);

    CCS0021L-Information Management Page 8 of 12


    12. 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 MANAGER_ID IN (SELECT EMPLOYEE_ID
    FROM EMPLOYEES
    WHERE LAST_NAME = 'King');

    13. 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');

    CCS0021L-Information Management Page 9 of 12


    VII. QUESTION AND ANSWER

    1. What is the difference between a Join and a Natural Join?


    - A Natural Join joins two tables dependent on same trait name and
    datatypes. As far as coming about table while a Join alludes to a
    two-table dependent on the fragment which is unequivocally
    decided in the ON explanation while. Normal Join will contain every
    one of the traits of the two tables yet will just keep one duplicate of
    every normal segment. Join will contain every one of the qualities
    of both the tables just as the copy sections. Their fundamental
    contrasts are their grammar.

    VIII. REFERENCES

    • Hoffer, J.A., Prescott, M.B., McFadden, F.R. (2007). Modern Database


    Management 8th Edition. New Jersey: Pearson Prentice Hall.
    CCS0021L-Information Management Page 10

    of 10
    CCS0021L-Information Management Page 11

    You might also like