CSE $111$ DATABASE SYSTEMS

Lab $9$: Views

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. You will do all these in a Java or Python application,

for which we provide skeleton code $($Lab $9.$java$)$ and Python $($Lab $9.$py$).$ While you have the freedom to

choose which programming language you use, you have to completely implement the lab in one of the two

languages.

The tasks you have to implement are the following:

$1.$ Create a view V$1($c custkey, c name, c address, c phone, c acctbal, c mktsegment, c comment, c nation,

c region$)$ that appends the country and region name to every customer. Rewrite Q$1$ from Lab $4$ with

view V$1.$

$2.$ Create a view V$2($o orderkey, o custkey, o orderstatus, o totalprice, o orderyear, o orderpriority, o clerk,

o shippriority, o comment$)$ that replaces o orderdate with the year o orderyear and contains all the

other attributes in orders. Rewrite Q$2$ from Lab $4$ with views V$1$ and V$2.$

$3.$ Rewrite Q$3$ from Lab $4$ with view V$1.$

$4.$ Create a view V$4($s suppkey, s name, s address, s phone, s acctbal, s comment, s nation, s region$)$ that

appends the country and region name to every supplier. Rewrite Q$4$ from Lab $4$ with view V$4.$

$5.$ Rewrite Q$5$ from Lab $4$ with view V$4.$

$6.$ Rewrite Q$6$ from Lab $4$ with views V$2.$

$7.$ Rewrite Q$7$ from Lab $4$ with views V$1$ and V$2.$

$8.$ Rewrite Q$8$ from Lab $4$ with views V$2$ and V$4.$

$9.$ Rewrite Q$9$ from Lab $4$ with views V$2$ and V$4.$

$10.$ Create a view V$10($p type, min discount, max discount$)$ that computes the minimum and maximum

discount for every type of part. Rewrite Q$10$ from Lab $4$ with view V$10.$

$11.$ Create two views V$111($c custkey, c name, c nationkey, c acctbal$)$ and V$112($s suppkey, s name, s nationkey,

s acctbal$)$ that contain the customers with negative balance and the suppliers with positive balance,

respectively. Rewrite Q$11$ from Lab $4$ with views V$111$ and V$112.$

$12.$ Rewrite Q$12$ from Lab $4$ with view V$4.$

$13.$ Rewrite Q$13$ from Lab $4$ with views V$1$ and V$4.$

$14.$ Rewrite Q$14$ from Lab $4$ with views V$1$ and V$4.$

$15.$ Rewrite Q$15$ from Lab $4$ with view V$4.$

In order to complete the lab you have to perform the following tasks:

$1.$ Write the Java code that implements the required functionality in the corresponding methods in

file Lab $9.$java. If you use Python, you edit the file Lab $9.$py$.$ This is the only file you have to

edit. Moreover, you have to write code only in the specified methods$/$functions$.$ There are $21$ such

methods$/$functions overall.

$2.$ For your reference, we provide you the SQL statements for all the queries in Lab $4$ in file queries$-$lab$-4.$sql$.$

$3.$ The format of the expected output for every query is available in output$/$x$.$out.