CSE { DATABASE SYSTEMS Lab 9 Solution

Description

In this Lab session you will learn how to work with SQL views. You will create views based on queries from Lab 4 and rewrite the same queries with the views.

1. Create a view Q1 that appends the country and region name to each customer. The schema of Q1 is:

c custkey decimal(9,0) not null, c name varchar(25) not null, c address varchar(40) not null, c phone char(15) not null,

c acctbal decimal(7,2) not null, c mktsegment char(10) not null, c comment varchar(117) not null, c nation char(25) not null,

c region char(25) not null

Rewrite Q1 from Lab 4 with view Q1.

2. Create a view Q2 that appends the country and region name to each supplier. The schema of Q2 is:

s suppkey decimal(8,0) not null, s name char(25) not null,

s address varchar(40) not null, s phone char(15) not null,

s acctbal decimal(7,2) not null, s comment varchar(101) not null, s nation char(25) not null,

s region char(25) not null

Rewrite Q2 from Lab 4 with view Q2.

3. Rewrite Q3 from Lab 4 with view Q1.

3. Rewrite Q4 from Lab 4 with view Q2.

3. Create a view Q5 that replaces o orderdate with the year o orderyear and contains all the other attributes in orders. The schema of Q5 is:

o orderkey decimal(12,0) not null, o custkey decimal(9,0) not null, o orderstatus char(1) not null,

o totalprice decimal(8,2) not null, o orderyear integer not null,

o orderpriority char(15) not null, o clerk char(15) not null,

o shippriority decimal(1,0) not null,

o comment varchar(79) not null

Rewrite Q5 from Lab 4 with views Q1 and Q5.

6. Rewrite Q6 from Lab 4 with view Q5.

6. Rewrite Q7 from Lab 4 with views Q1 and Q5.

6. Rewrite Q8 from Lab 4 with views Q2 and Q5.

6. Rewrite Q9 from Lab 4 with views Q2 and Q5.

6. Create a view Q10 that computes the maximum discount for each type of part. The schema of Q10 is:

p type varchar(25) not null,

max discount decimal(3,2) not null

Rewrite Q10 from Lab 4 with view Q10.

11. Rewrite Q11 from Lab 4 with view Q2.

11. Rewrite Q12 from Lab 4 with view Q2.

11. Rewrite Q13 from Lab 4 with views Q1 and Q2.

11. Rewrite Q14 from Lab 4 with views Q1 and Q2.

11. Create two views Q151 and Q152. Q151 contains the customers with negative balance and has the schema:

c custkey decimal(9,0) not null, c name varchar(25) not null,

c nationkey decimal(3,0) not null, c acctbal decimal(7,2) not null,

Q152 contains the suppliers with negative balance and has the schema:

s suppkey decimal(8,0) not null, s name char(25) not null,

s nationkey decimal(3,0) not null, s acctbal decimal(7,2) not null,

Rewrite Q15 from Lab 4 with views Q151 and Q152.

Instructions. You are required to execute the queries on the TPCH database you populated in Lab 2. We provide you the correct answers for this database. We will check the correctness of your queries on a di erent database. Folder Lab9 under Lab contains an archive lab9-results.zip with the correct answers. There is a le for each query, e.g., 1.out for query 1, 2.out for query 2, etc. You are required to turn in an archive containing a le with the SQL statement for every query|only the query statement with the view, no view de nition or anything else. The names of the les have to be 1.sql for query 1, 2.sql for query 2, and so on. There should be exactly 15 les in the archive le. The name of the archive le has to be firstname-lastname-lab9.zip where firstname and lastname are your rst and last names, respectively. The type of the archive le has to be zip.

Lab 9 2