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.
- 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.
- Create a view Q2 that appends the country and region name to each supplier. The schema of Q2 is:
- ssuppkey decimal(8,0) not null,
- sname char(25) not null,
- saddress varchar(40) not null,
- sphone char(15) not null,
- sacctbal decimal(7,2) not null,
- scomment varchar(101) not null,
- snation char(25) not null,
- sregion char(25) not null
Rewrite Q2 from Lab 4 with view Q2.
- Rewrite Q3 from Lab 4 with view Q1.
- Rewrite Q4 from Lab 4 with view Q2.
- 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.
- Rewrite Q7 from Lab 4 with views Q1 and Q5.
- Rewrite Q8 from Lab 4 with views Q2 and Q5.
- Rewrite Q9 from Lab 4 with views Q2 and Q5.
- 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.
- Rewrite Q11 from Lab 4 with view Q2.
- Rewrite Q12 from Lab 4 with view Q2.
- Rewrite Q13 from Lab 4 with views Q1 and Q2.
- Rewrite Q14 from Lab 4 with views Q1 and Q2.
- Create two views Q151 and Q152. Q151 contains the customers with negative balance and has theschema:
- 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:
- ssuppkey decimal(8,0) not null,
- sname char(25) not null,
- snationkey decimal(3,0) not null,
- sacctbal decimal(7,2) not null,
Rewrite Q15 from Lab 4 with views Q151 and Q152.
