Lab 01 - OLAP

LAB 01: OLAP
Given schema:
/* Delete the tables if they already exist */

drop table if exists Student;
drop table if exists Instructor;
drop table if exists Class;
drop table if exists Took;
/* Create the schema for our tables */

create table Student(studID char(6) primary key, name text, major text);
create table Instructor(instID char(6) primary key, dept text);
create table Class(classID char(6) primary key, univ text, region text, country
text);
create table Took(studID char(6) references Student, instID char(6) references
Instructor, classID char(6) references Class, score int);
/* Populate the tables with our data */

insert into Student values ('stud1', 'Amy', 'CS');
insert into Student values ('stud2', 'Brian', 'CS');
insert into Student values ('stud3', 'Carol', 'CS');
insert into Student values ('stud4', 'David', 'CS');
insert into Student values ('stud5', 'Emily', 'EE');
insert into Student values ('stud6', 'Frank', 'EE');
insert into Instructor values ('inst1', 'CS');

insert into Instructor values ('inst2', 'EE');
insert into Instructor values ('inst3', 'CS');
insert into Class values ('class1', 'Stanford', 'CA', 'USA');

insert into Class values ('class2', 'Stanford', 'CA', 'USA');
insert into Class values ('class3', 'Berkeley', 'CA', 'USA');
insert into Class values ('class4', 'MIT', 'MA', 'USA');
insert into Class values ('class5', 'MIT', 'MA', 'USA');
insert into Class values ('class6', 'McGill', 'Quebec', 'Canada');
insert into Class values ('class7', 'McGill', 'Quebec', 'Canada');
insert into Class values ('class8', 'Toronto', 'Ontario', 'Canada');
insert into Class values ('class9', 'Waterloo', 'Ontario', 'Canada');
insert into Took values ('stud1', 'inst1', 'class1', 70);

Requirements:
1. Find all students who attended a class in California taught by an instructor outside
of the student's major department and received a score above 80. Return the
student name, university, and score.
2. Calculate the average scores, grouped by student and instructor, for courses taught
in Quebec.
3. "Roll up" your result from problem 2 so it's grouping by instructor only.
4. Determine the average scores grouped by student major.
5. "Drill down" on your result from problem 4 so it's grouping by instructor's

department as well as student's major.
6. Use "WITH ROLLUP" on the attributes of the Class table to get average scores at
all geographical levels: by country, region, and university, including the overall
average.
7. Create a table with the results from problem 6. Use this table to compare how
much better students from the USA perform on average compared to students from
Canada.
8. Verify your results for problem 7 by writing the same query on the original tables
without using "WITH ROLLUP".
9. Create the following table that simulates the unsupported "WITH CUBE"
operator. Using table Cube instead of table Took, and taking advantage of the
special tuples with NULLs, find the average score of CS major students taking a
course at MIT.
10. Verify your result for problem 9 by writing the same query over the original
tables.
11. Whoops! Did you get a different answer for problem 10 than you got for problem
9? What went wrong? Assuming the answer on the original tables is correct, create
a slightly different data cube that allows you to get the correct answer using the
special NULL tuples in the cube. Hint: Change what dependent value(s) you store
in the cells of the cube; no change to the overall structure of the query or the cube
is needed.

Lab 01 - OLAP

Uploaded by

Copyright:

Available Formats

Lab 01 - OLAP

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lab 01 - OLAP

Uploaded by

Copyright:

Available Formats

LAB 01: OLAP

/* Delete the tables if they already exist */

/* Create the schema for our tables */

/* Populate the tables with our data */

insert into Instructor values ('inst1', 'CS');

insert into Class values ('class1', 'Stanford', 'CA', 'USA');

insert into Took values ('stud1', 'inst1', 'class1', 70);

5. "Drill down" on your result from problem 4 so it's grouping by instructor's

You might also like