Assigment 2 (Due September 6, 11:55 pm)

  1. Practicing Oracle
    bulletcreate the tables (as described in the textbook  (similar to pg. 43))
    bullet Students (with Id, Name, Address, EnrollmentDate, and Status)
    bulletCourse (with CrsCode, DeptId, CrsName, Descr) and
    bullet Transcripts (with Studid, CrsCode, Semester, and Grade)
    bulletinsert 5 students into the table Students (2 seniors, 1 freshman, 1 junior, 1 sophomore)
    bulletinsert 5 courses into the table Course (CS482, CS584, CS101, CS372, and one of the class of your choice)
    bulletinsert 10 tuples into the table Transcripts,  (2 for each students), make sure that the course code is present in the Course table and the student id is present in the Students table.  

    In the above tables, the only difference between what is asked and what is in the book is the attribute 'EnrollmentDate' of the Students table. This attribute should have the type DATE (a built-in type of Oracle). A good manual on how to work with the DATE type can be found in http://www-db.stanford.edu/~ullman/fcdb/oracle/or-time.html#date%20format.

    Since we do not have real data to work with, please use the mathematician names from http://www-gap.dcs.st-and.ac.uk/~history/BiogIndex.html for the names of students in your table(s).  It is reasonable to expect that no pair of students (in our class) selects the same set of names as their student list. 

    You should submit the following:
    bulletthe queries that you use to create the tables and insert data into the tables

    Leave the data in your Oracle account so that the TA can check your work.

     

  2. Write the SQL queries to answer the following questions:
    bulletlist all students with their name, date of enrollment;
    bulletcreate the transcripts of all students (student name, id, course code, course description, and grade) 
    bulletcreate the list of students attending CS482

    You should submit the SQL commands as part of the answer to this question. Furthermore, you should execute and store the result of your SQL commands on your Oracle account so the TA can check your work. In order to do this, you should add

        CREATE VIEW  answer2_x AS

    before the keyword 'SELECT' of your SQL query and execute it ('x' is either 1, 2, or 3, indicating the first, second, and third SQL query, respectively). For example, if  your query for the first question is

        SELECT * FROM students;

    you would have to write:

        CREATE VIEW answer2_1 AS SELECT * FROM students;

    After this is executed, you can use

        SELECT * from answer2_1;

    to review the result of your query. If you would like to redo it, use

        DROP VIEW answer2_x;

    before 'CREATE VIEW ....'.