lcain@cucstud.UUCP (Leroy Cain) (06/13/88)
I have got a number of messages about missing files in the
SQL parser I posted. These file are the verification tests I had setup.
vtest2 -- test scrip
testpar -- a shell script to run the test.
tf.l -- strips lines that start with ##. lex and compile to tf.
These files compose a primitive yet useful test bed.
-----Cut Here-----Cut Here-----Cut Here-----Cut Here-----
#!/bin/sh
# shar: Shell Archiver
# Run the following text with /bin/sh to create:
# testpar
# tf.l
# vtest2
echo x - extracting shar
sed 's/^X//' << 'SHAR_EOF' > testpar
Xtf <vtest2 |tpar
SHAR_EOF
echo x - extracting shar
sed 's/^X//' << 'SHAR_EOF' > tf.l
X%%
X#[0-9]+# ;
X%%
SHAR_EOF
echo x - extracting shar
sed 's/^X//' << 'SHAR_EOF' > vtest2
X#0#
Xcreate domain s# is char5 where s# is not null;
X#1#
Xcreate domain p# is char6 where p# is not null;
X#2#
Xcreate domain j# is char4 where j# is not null;
X#3#
Xcreate domain name is char20;
X#4#
Xcreate domain status, weight, qty is int;
X#5#
Xcreate domain city is char15;
X#6#
Xcreate domain color is char6 where color in ( "green", "red", "blue");
X#7#
Xcreate table s
X primary (s#)
X ( s_name, status, city);
X
X#8#
Xcreate table p
X primary (p#)
X ( p_name, color, weight, city);
X
X#9#
Xcreate table j
X primary (j#)
X ( j_name, city);
X
X#10#
Xcreate table spj
X primary (s#, p#, j#)
X foreign (s# identifies s
X nulls not allowed
X delete of s restricted
X update of s.s# cascades)
X foreign (p# identifies p
X nulls not allowed
X delete of p restricted
X update of p.p# restricted)
X foreign (j# identifies j
X nulls not allowed
X delete of s restricted
X update of j.j# cascades)
X (qty);
X
X#11#
Xcreate unique index sx on s (s#);
X#12#
Xcreate unique index px on p (p#);
X#13#
Xcreate unique index jx on j (j#);
X#14#
Xcreate unique index spjx on spj (s#, p#, j#);
X
X#15#
Xinsert into s from sdate;
X#16#
Xinsert into p from pdate;
X#17#
Xinsert into j from jdate;
X#18#
Xinsert into spj from spjdate;
X
X#19#
Xcreate view sp
X as select s#, p# from spj;
X
X#20#
Xselect s#, status
Xfrom s
Xwhere city = 'Paris';
X
X#21#
Xselect p#
Xfrom sp;
X
X#22#
Xselect unique p#
Xfrom sp;
X
X#23#
Xselect p#, weight * 454
Xfrom p;
X
X#24#
Xselect p#, "Weight in grams =", weight * 454
Xfrom p;
X
X#25#
Xselect *
Xfrom s;
X
X#26#
Xselect s#, s_name, status, city
Xfrom s;
X
X#27#
Xselect s.*
Xfrom s;
X
X#28#
Xselect s#
Xfrom s
Xwhere city = "paris"
Xand status > 20;
X
X#29#
Xselect s#, status
Xfrom s
Xwhere city = "paris"
Xorder by status desc;
X
X#30#
Xselect p#, weight * 454
Xfrom p
Xorder by 2, P#;
X
X#31#
Xselect p#, pname, color, weight, city
Xfrom p
Xwhere weight between 16 and 19;
X
X#32#
Xselect p#, pname, color, weight, city
Xfrom p
Xwhere weight not between 16 and 19;
X
X#33#
Xselect p#, pname, color, weight, city
Xfrom p
Xwhere weight in (12, 16, 17);
X
X#34#
Xselect p#, pname, color, weight, city
Xfrom p
Xwhere weight not in (12, 16, 17);
X
X#35#
Xselect p#, pname, color, weight, city
Xfrom p
Xwhere weight = 12 or weight = 16 or weight = 17;
X
X#36#
Xselect p#, pname, color, weight, city
Xfrom p
Xwhere pname like 'C%';
X
X#37#
Xselect s#
Xfrom s
Xwhere status is null;
X
X#38#
Xselect s.*, p.*
Xfrom s, p
Xwhere s.city = p.city;
X
X#39#
Xselect s.*, p.*
Xfrom s, p
Xwhere s.city > p.city;
X
X#40#
Xselect s.*, p.*
Xfrom s, p
Xwhere s.city = p.city
Xand s.status != 20;
X
X#41#
Xselect s.s#, p.p#
Xfrom s, p
Xwhere s.city = p.city;
X
X#42#
Xselect unique s.*, p.*
Xfrom s, sp, p
Xwhere s.s# = sp.s#
Xand sp.p# = p.p#;
X
X#43#
Xselect first.s#, second.s#
Xfrom s first, s second
Xwhere first.city = second.city;
X
X#44#
Xselect first.s#, second.s#
Xfrom s first, s second
Xwhere first.city = second.city
Xand first.s# < second.s#;
X
X#45#
Xselect s#, p#, j#
Xfrom s, p, j
Xwhere not
X (s.city = p.city and p.city = j.city);
X
X#46#
Xselect s#, p#, j#
Xfrom s, p, j
Xwhere s.city != p.city
Xand p.city != j.city
Xand j.city != s.city;
X
X#47#
Xselect unique j#
Xfrom spj spjx
Xwhere not exists
X (select *
X from spj spjy
X where spjy.j# = spjx.j#
X and not exists
X (select *
X from spj spjz
X where spjz.p# = spjy.p#
X and spjz.s# = 'S1'
X )
X );
X
X#48#
Xselect unique j#
Xfrom spj spjx
Xwhere not exists
X (select *
X from spj spjy
X where exists
X (select *
X from spj spja
X where spja.s# = spjy.s#
X and spja.p# in
X (select p#
X from p
X where color = 'red')
X and not exists
X (select *
X from spj spjb
X where spjb.s# = spjy.s#
X and spjb.j# = spjx.j# )));
X
X#49#
Xselect unique s#
Xfrom spj
Xwhere p# in
X ( select p#
X from spj
X where s# in
X (select s#
X from spj
X where p# in
X (select p#
X from p
X where color = "red" )));
X
X#50#
Xselect unique s#
Xfrom spj spjx
Xwhere p# = 'p1'
Xand qty >
X ( select avg(qty)
X from spj spjy
X where p# = 'P1'
X and spjy.j# = spjx.j# );
X
X#51#
Xselect count(unique j#)
Xfrom spj
Xwhere s# = 's1';
X
X#52#
Xselect j#
Xfrom spj
Xwhere p# = 'p1'
Xgroup by j#
Xhaving avg (qty) >
X (select max(qty)
X from spj
X where j# = 'j1');
X
X#53#
Xselect city from s
Xunion
Xselect city from p
Xunion
Xselect city from j
Xorder by 1;
X
X#54#
Xselect p#, 'weight >16 lb'
Xfrom p
Xwhere weight >16
Xunion
Xselect p#, 'supplied by S2'
Xfrom sp
Xwhere s# = 'S2'
Xorder by 2, 1;
X
X#55#
Xselect p.p#, 'Weight in grams =', p.weight * 454, p.color,
X 'Max shipped quantity =', max(sp.qty)
Xfrom p, sp
Xwhere p.p# = sp.p#
Xand p.color in ('red', 'blue')
Xand sp.qty >200
Xgroup by p.p#, p.weight, p.color
Xhaving sum(qty) >350
Xorder by 6, p.p# desc;
X
X#56#
Xselect unique spjx.j#
Xfrom spj spjx
Xwhere exists
X (select *
X from spj spjy
X where spjy.p# = spjx.p#
X and spjy.s# = 's1');
X
X#57#
Xupdate p
Xset color = 'Orange'
XWhere color = 'Red';
X
X#58#
Xdelete
Xfrom j
Xwhere j# not in
X (select j#
X from spj );
X
X#59#
Xcreate table reds (s#);
X
X#60#
Xinsert into red (s#)
X select unique s#
X from spj, p
X where spj.p# = p.p#
X and color = 'Red';
X
X#61#
Xupdate spj
Xset qty = qty *1.1
Xwhere s# in
X (select s#
X from REDS );
X
X#62#
Xdrop table reds;
X
X#63#
Xdelete
Xfrom spj
Xwhere 'Rome' =
X (select city
X from j
X where j.j# = spj.j#);
X
X#64#
Xdelete
Xfrom j
Xwhere city = 'Rome';
X
X#65#
Xinsert
Xinto s (s#, s_name, city)
Xvalues ('S10', 'white', 'New York');
X
X#66#
Xinsert
Xinto s (s#, s_name, status, city)
Xvalues ('S11', 'white', NULL, 'New York');
X
X#67#
Xcreate view spv (s#, p#, qty )
X as select s#, p#, sum (qty)
X from spj
X group by s#, p#;
X
X#68#
Xcreate view jc (j#, city )
X as select unique j.j#, j.city
X from j, spj
X where j.j# = spj.j#
X and (spj.s# = 'S1' or spj.p# = 'P1' );
X
X#69#
Xcreate view jc (j#, city)
X as select j.j#, j.city
X from j
X where j.j# in
X (select j#
X from spj
X where s# = 'S1')
X and j.j# in
X (select j#
X from spj
X where p# = 'P1');
X
X#70#
Xcreate view non_colocated
X as select s#, p#
X from s, p
X where s.city != p.city;
X
X#71#
Xcreate view london_suppliers
X as select s#, s_name, status
X from s
X where city = "London";
X
X#72#
Xgrant select on table s to charly;
X
X#73#
Xgrant select , update(status, city) on table s
X to judy, jack, john;
X
X#74#
Xgrant createtab on database dbx to sharon;
X
X#75#
Xgrant select on table s to u2 with grant option;
X
X#76#
Xrevoke select on table s from u2;
X
X#77#
Xrevoke select, update on table s from u2;
X
X#78#
Xlock table s in exclusive mode;
X
X#79#
Xlock table p in share mode;
X
X#80#
Xstore program backup ( name = "p", fname = "/backup/p" )
Xlock table name in exclusive mode;
Xdump table name to fname;
Xlock table name in share mode;
Xcommit work;
Xend store;
X
X#81#
Xfirstprog ( "p", "/backup/p" );
SHAR_EOF
exit