Oracle

Need Help in Oracle Assignment?
We can help you if you are having difficulty with your Oracle Assignment. Just email your Oracle Assignment at admin@assignmentcache.com.
We provide help for students all over the world in Oracle Assignment.

1 Item(s)

per page

Grid  List 

Set Descending Direction
  1. Student Oracle Database Part1 Create Table

    Student Database Oracle DDL and Queries

    Regular Price: $40.00

    Special Price: $30.00

    Student Database Oracle DDL and Queries


    Create the following tables.
    STUDENT
    Student Number (PK)
    Student Last Name
    Student Major
    Department ID (FK)
    Student GPA
    Student Hours
    Student Class
    Advisor ID (FK)


    ADVISOR
    Advisor ID (PK)
    Advisor Last Name
    Advisor Office
    Advisor Building
    Advisor Phone


    DEPARTMENT
    Department ID (PK)
    Department Code
    Department Name
    Department Phone


    *NOTE* You will have to decide on how to handle the Department and Advisor ID foreign keys in terms of a numbering system as well as appropriate field widths and types for the fields.
    The business rules which govern this database are: A student may have one advisor while an advisor may advise multiple students. A student belongs to only one department but each department can have many students.
    Populate the table with data from Table P6.4 (p. 217).

    There is an error in the book! Change Ortiz’s student number to be 200888. In addition, add these three students to your database.
    STU_NUM 123984 995133 367181
    STU_LNAME Freeman Wilder Green
    STU_MAJOR CIT CIT BIS
    DEPT_CODE CS CS IS
    DEPT_NAME Computer Science Computer Science Business Informatics
    DEPT_PHONE 5234 3951 3951
    COLLEGE_NAME Informatics Informatics Informatics
    ADVISOR_LNAME Strand Zhang Goh
    ADVISOR_BLDG Griffin Griffin Griffin
    ADVISOR_OFFICE 5132 3451 5612
    ADVISOR_PHONE 1603 3512 7922
    STU_GPA 2.5 3.9 2.3
    STU_HOURS 97 58 63
    STU_CLASS Senior Junior Junior


    Part 1
    Provide all DDL-related code. This includes table definition and creation, fk/pk creation, and populating the tables with data. Please include the output from Oracle that shows that everything was created correctly.


    Part 2
    Queries – For each query provide 1) What the output, specifically, should be based upon eyeballing the data 2) the SQL code used to generate the query, and 3) the output from Oracle. Please include any relevant fields you think the user of the query would need to interpret the output.
    1. Advisors need the capability to generate a query that returns *all* student information based upon a student number. This will help them in the advisement process. Create a query that returns all information for student Freeman from each table.
    2. Each year, college administrators need to know how many students are in each major. Create a query that counts the number of students in each major while displaying the major name.
    3. Kroger has approached NKU with an internship opportunity! The business department chair needs to generate a mailing list to inform great students about a job opportunity. Create a query that shows all the student numbers, last names, majors, department names, and advisor’s last name for students in Business Admin. Students should have a minimum GPA of 2.5 or greater to be on this mailing list. List in ascending order of GPA.
    4. To get an idea of the adequacy of admission standards, NKU needs to have an idea of the breakdown of students. By each college, show the number of students in each classification (freshmen, sophomore, junior, senior).
    5. Due to a fire, Griffin Hall has burnt down. Write a SQL statement that updates all faculty who had offices in Griffin Hall to now be housed in the University Center.
    6. New funding may be able to pay for an advising center. The dean would like to get an idea of how many students each faculty member currently advises. Create a query that shows the names and majors of students for each advisor along with the advisor’s name.
    7. (Extra credit) Create a query that counts the number of students who are eligible for the Dean’s list (GPA >= 3.5) in each department.

    Learn More

1 Item(s)

per page

Grid  List 

Set Descending Direction