Scribd Logo
Upload

Welcome to Scribd!

  • 26 views

    Lab Manipulating Data

    Uploaded by

    gomez.mafe
    This document provides instructions for a lab assignment on manipulating data in an Oracle database for a college. Students are asked to write SQL scripts to make several changes to the database, including adding a new department and instructor, modifying course codes, and removing an instructor. The assignment will be evaluated based on correctly completing the modifications, compatibility with different datasets, inclusion of spool and attribution files.

    Copyright:

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

    Lab Manipulating Data

    Uploaded by

    gomez.mafe
    26 views3 pages
    This document provides instructions for a lab assignment on manipulating data in an Oracle database for a college. Students are asked to write SQL scripts to make several changes to the database, including adding a new department and instructor, modifying course codes, and removing an instructor. The assignment will be evaluated based on correctly completing the modifications, compatibility with different datasets, inclusion of spool and attribution files.

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document provides instructions for a lab assignment on manipulating data in an Oracle database for a college. Students are asked to write SQL scripts to make several changes to the database, including adding a new department and instructor, modifying course codes, and removing an instructor. The assignment will be evaluated based on correctly completing the modifications, compatibility with different datasets, inclusion of spool and attribution files.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    26 views3 pages

    Lab Manipulating Data

    Uploaded by

    gomez.mafe
    This document provides instructions for a lab assignment on manipulating data in an Oracle database for a college. Students are asked to write SQL scripts to make several changes to the database, including adding a new department and instructor, modifying course codes, and removing an instructor. The assignment will be evaluated based on correctly completing the modifications, compatibility with different datasets, inclusion of spool and attribution files.

    Copyright:

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

    Student Name: Weight: 2.

    3%

    Student ID: Marks: /12

    Lab: Manipulating Data


    Equipment and Materials
    For this lab, you will need:
     A Windows computer with a minimum of 16GB RAM and 250GB of free disk space,
    capable of nested virtualization
     Access to Oracle Version 11g or better through SQL*PLUS
     The Grand Hill College Database that you populated as part of earlier learning activities
     The Grand Hill College Physical Model that was created as part of earlier learning
    activities

    Instructions
    1. First, work through steps 2-5 individually. Then arrange a time to come together as a small
    group to create one solution based on the best individual solution to each problem. Further
    refine the solution as a group if needed.
    2. Review the physical model for Grand Hill College.
    3. Using the Grand Hill College Database, write a single script that fulfills all the requirements
    outlined in the Problem Set.
    4. Compare your solution against the Marking Criteria.
    5. See Brightspace for exact due dates.
    6. Only one submission per group is required. Your submission should include:
    a. One script file
    b. One spool file showing all results
    c. One attribution list that outlines the activities associated with completing this
    assignment. A sample attribution list is provided on Brightspace.

    Problem Set
    Write SQL code to perform the following modifications to the Grand Hill College Database.

    1. Add a new department.


    a. Department number: 100
    b. Name: Astrophysics

    © 2022, Southern Alberta Institute of Technology 1


    INSERT INTO Department (DepartmentNumber, DepartmentName)
    VALUES (100, 'Astrophysics');

    2. Add a new instructor.

    a. Faculty ID: 1001


    b. Name: Danny Faulkner
    c. Hired: Jan 1, 2022
    d. This instructor is currently available and teaches in the Astrophysics department

    INSERT INTO Instructor (FacultyID, InstructorName, HireDate, Available,


    DepartmentNumber)
    VALUES (1001, 'Danny Faulkner', TO_DATE('2022-01-01', 'YYYY-MM-DD'), 'Yes', 100);

    3. Add the following courses that Danny Faulkner has expertise to teach:
    a. Course Code: APHY202, The Solar System, 5 credits
    b. Course Code: APHY203, Nebula, 5 credits
    c. Course Code: APHY204, Global Clusters, 5 credits

    INSERT INTO Course (CourseCode, CourseName, Credits, DepartmentNumber)


    VALUES
    ('APHY202', 'The Solar System', 5, 100),
    ('APHY203', 'Nebula', 5, 100),
    ('APHY204', 'Global Clusters', 5, 100);

    INSERT INTO Instructor_Course (FacultyID, CourseCode)


    VALUES
    (1001, 'APHY202'),
    (1001, 'APHY203'),
    (1001, 'APHY204');

    4. The Nebula Course has moved to the third semester, so its new course code is ‘APHY302’.
    Make that change throughout the database.
    INSERT INTO Course (CourseCode, CourseName, Credits, DepartmentNumber)
    VALUES ('APHY302', 'Nebula', 5, 100);

    UPDATE Instructor_Course
    SET CourseCode = 'APHY302'
    WHERE CourseCode = 'APHY203';

    DELETE FROM Course


    WHERE CourseCode = 'APHY203';

    © 2022, Southern Alberta Institute of Technology 2


    Hint: You will need to add the new course first and then update/delete existing data.

    5. Remove Danny Faulkner (ID: 1001) and the record of his expertise. Leave the new courses
    and the Astrophysics department in the databases.
    DELETE FROM Instructor
    WHERE FacultyID = 1001;

    DELETE FROM Instructor_Course


    WHERE FacultyID = 1001;

    Marking Criteria
    Needs
    Adequate Good
    Categories Improvement Score
    2 3
    1

    Correctly performs 3+ modifications 1 or 2 modifications Yes /3


    all modifications missing missing

    Will work for all 2+ questions will 1 question will not Yes
    not work for all work for all datasets /3
    datasets
    datasets

    Spool file included No N/A Yes /3

    Attribution list No Some details Yes


    included /3
    missing

    Total /12

    © 2022, Southern Alberta Institute of Technology 3

    You might also like