CSE111-Lab 9 SQL Vews Solved

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:

•    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.

3.   Rewrite Q3 from Lab 4 with view Q1.

4.   Rewrite Q4 from Lab 4 with view Q2.

5.   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.

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

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

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

10.   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.

12.   Rewrite Q12 from Lab 4 with view Q2.

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

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

15.   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.