Chapter 12 Application

Question 1

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.

Answer 1

show create table student;

Question 2

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.

Answer 2

create or replace view number_cruncher as select sid, sname, major, classYear from student where major in(‘acct’, ‘csci’, ‘math’);

Question 3

Materialize (query) the view Number_Cruncher.

Answer 3

start transaction;

thats all the code that was recorded for this one

Question 4

Update Number_Cruncher to set the major to ‘CSE’ for student 200.

Answer 4

update number_cruncher set major = ‘CSE’ where sid 200;

Question 5

Query Number_Cruncher and Student. Rollback and repeat the querys.

Answer 5

select * from number_cruncher;
select * from student;
rollback;

Question 6

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

Answer 6

create or replace view Class_Schedule as select s.sid, sname, classYear, ccode from student s, enrollemente where s.sid = e.sid;
select * from Class_Schedule;

Question 7

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.

Answer 7

create or replace view Senior_Schedule as select sid, sname, ccode from Class_Schedule where classYear = ‘senior’;
select * from SeniorSchedule;

Question 8

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.

Answer 8

create or replace view Course_Load as select sname, count(*) as classCount from Senior_Schedule group by sname;
select * from Course_Load order by classCount desc;

Question 9

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)

Answer 9

show create view Number_Cruncher;

Question 10

Drop the views created for this exercise.

Answer 10

drop view Course_Load;