SQL Class Exercise--Views

                         
0.  Use the SHOW CREATE TABLE tableName command to view the structure of the 
    database tables STUDENT, ENROLLMENT, FACULTY and COURSE, and to verify that
    primary and foreign keys have been defined, and that the database engine = 
    InnoDB (alter table if necessary).  


1.  Create a restrict view named Number_Cruncher that includes the sid, sname, 
    major, and classYear from STUDENT for 'ACCT' or 'CSCI' or 'MATH' majors.
    Note: by default, the view column names are given the base column names.
    Also, for views to be updateable, the select list may not use distinct,
    aggregate function(s), nor can the statement include group by, having,
    or include the union operator.


    
    
    
2.  Materialize (query) the view Number_Cruncher.


[Issue start transaction command to allow a rollback after updating a table.]
3.  Update Number_Cruncher to set the major to 'CSE' for student 200, and also
    also set major to 'CSE' for student 200.


4.  Query Number_Cruncher and Student. Rollback and repeat the querys.



5.  Create and display a join view named Class_Schedule that will contain sid, 
    sname and classYear from STUDENT plus ccode from ENROLLMENT for all enrolled 
    students.





6.  Define a view named Senior_Schedule in terms of the view Class_Schedule. 
    This nested view should include student id, student name, and class code
    for all rows with classYear value 'Senior'. 
    Display the view Senior_Schedule.





7.  Create a summary view (as Course_Load) that reduces the data in the view 
    Class_Schedule into a list containing student names and a count of the number 
    of classes for which they enrolled (note: name the calculated column).
    Query Course_Load to show the data sorted by class count in descending order.





8.  Retrieve the table(view) name and definition for the views defined for this 
    exercise (use show create view viewName and describe viewName or query
    view in information_schema).


9.  Drop the views created for this exercise.