CSc 4340/6340, Introduction to Compilers
Fall 2004
PROJECT - Phase II

Phase II: (Due: 8 November, 2004 - Monday)
Electronic submission under Assignment 5
Continue building the SQL-subset compiler by adding "semantic" checks. You will build an abstract syntax tree (as discussed in class) and perform the following semantic checks:

To allow for "Catalog" information to be accessible, we will add to the grammar the "create table" statement. See sql-create.grammar. You will process these statements and create the "Catalog" data. The dbEngine with the implementation of the catalog is available here.

A sample run for Phase II would be: 

$ java OurSQL
SQL>@create-tables;

create table zipcodes (
  zip      integer,
  city     varchar);

create table employees (
  eno      integer,
  ename    varchar,
  zip      integer,
  hdate    varchar);

create table parts(
  pno      integer,
  pname    varchar,
  qoh      integer,
  price    decimal,
  olevel   integer);

create table customers (
  cno      integer,
  cname    varchar,
  street   varchar,
  zip      integer,
  phone    varchar);
 
create table orders (
  ono      integer,
  cno      integer,
  eno      integer,
  received varchar,
  shipped  varchar);

create table odetails (
  ono      integer,
  pno      integer,
  qty      integer);
;

Relation ZIPCODES created
Relation EMPLOYEES created
Relation PARTS created
Relation CUSTOMERS created
Relation ORDERS created
Relation ODETAILS created

SQL>select pno,pname
SQL>from parts;

No Syntax Error
No Semantic Error
Not Nested

SQL>select distinct cname
SQL>from   customers
SQL>where  cno in (select cno
SQL>               from   orders
SQL>               where  ono in (select ono
SQL>                              from   odetails
SQL>                              where  pno in (select pno
SQL>                                             from   parts
SQL>                                             where  price < 20.00 )));

No Syntax Error
No Semantic Error
Nested - Not Correlated

SQL>select cno
SQL>from   orders
SQL>where  exists (select ono
SQL>               from   odetails
SQL>               where  odetails.ono = orders.ono and
SQL>                      odetails.pno = 10506) and
SQL>       exists (select ono
SQL>               from   odetails
SQL>               where  odetails.ono = orders.ono and
SQL>                      odetails.pno = 10507) and;

Syntax Error

SQL> @q1;
select cno
from   orders
where  exists (select ono
               from   odetails
               where  odetails.ono = orders.ono and
                      odetails.pno = 10506) and
       exists (select ono
               from   odetails
               where  odetails.ono = orders.ono and
                      odetails.pno = 10507);

No Syntax Error
No Semantic Error
Nested - Correlated

SQL> @q2;
select a, b c
from r, s
where r.a=s.a;
Error in line 1, column 13 : Syntax error
Error in line 1, column 13 : Couldn't repair and continue parse
SQL> select a, b from r, s where r.a = 'aaa' and s.a = r.b;

No Syntax Error
Semantic Error - undefined column

SQL> exit;
$