SQL code for SQL and Relational Theory


I am reading SQL and Relational Theory by C. J. Date. Baron Schwartz wrote a nice review for it. I am reading the online version, about half way through, so am not sure if it has an accompanying CD with source code. In any case, if you want to play with some SQL code listed in the book, here is the script to generate the tables and rows (or should I say relations at a certain point in time with tuples?)

[sourcecode language=”sql”]
CREATE TABLE S
( SNO VARCHAR(5) NOT NULL ,
SNAME VARCHAR(25) NOT NULL ,
STATUS INTEGER NOT NULL ,
CITY VARCHAR(20) NOT NULL ,
UNIQUE ( SNO ) ) ;

CREATE TABLE P
( PNO VARCHAR(6) NOT NULL ,
PNAME VARCHAR(25) NOT NULL ,
COLOR CHAR(10) NOT NULL ,
WEIGHT NUMERIC(5,1) NOT NULL ,
CITY VARCHAR(20) NOT NULL ,
UNIQUE ( PNO ) ) ;

CREATE TABLE SP
( SNO VARCHAR(5) NOT NULL ,
PNO VARCHAR(6) NOT NULL ,
QTY INTEGER NOT NULL ,
UNIQUE ( SNO , PNO ) ,
FOREIGN KEY ( SNO )
REFERENCES S ( SNO ) ,
FOREIGN KEY ( PNO )
REFERENCES P ( PNO ) ) ;

INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES (‘S1’, ‘Smith’, 20, ‘London’);
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES (‘S2’, ‘Jones’, 10, ‘Paris’);
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES (‘S3’, ‘Blake’, 30, ‘Paris’);
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES (‘S4’, ‘Clark’, 20, ‘London’);
INSERT INTO S (SNO, SNAME, STATUS, CITY) VALUES (‘S5’, ‘Adams’, 30, ‘Athens’);

INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES (‘P1’, ‘Nut’, ‘Red’, 12.0, ‘London’);
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES (‘P2’, ‘Bolt’, ‘Green’, 17.0, ‘Paris’);
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES (‘P3’, ‘Screw’, ‘Blue’, 17.0, ‘Oslo’);
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES (‘P4’, ‘Screw’, ‘Red’, 14.0, ‘London’);
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES (‘P5’, ‘Cam’, ‘Blue’, 12.0, ‘Paris’);
INSERT INTO P (PNO, PNAME, COLOR, WEIGHT, CITY) VALUES (‘P6’, ‘Cog’, ‘Red’, 19.0, ‘London’);

INSERT INTO SP (SNO, PNO, QTY) VALUES (‘S1’, ‘P1’, 300);
INSERT INTO SP (SNO, PNO, QTY) VALUES (‘S1’, ‘P2’, 200);
INSERT INTO SP (SNO, PNO, QTY) VALUES (‘S1’, ‘P3’, 400);
INSERT INTO SP (SNO, PNO, QTY) VALUES (‘S1’, ‘P4’, 200);
INSERT INTO SP (SNO, PNO, QTY) VALUES (‘S1’, ‘P5’, 100);
INSERT INTO SP (SNO, PNO, QTY) VALUES (‘S1’, ‘P6’, 100);
INSERT INTO SP (SNO, PNO, QTY) VALUES (‘S2’, ‘P1’, 300);
INSERT INTO SP (SNO, PNO, QTY) VALUES (‘S2’, ‘P2’, 400);
INSERT INTO SP (SNO, PNO, QTY) VALUES (‘S3’, ‘P2’, 200);
INSERT INTO SP (SNO, PNO, QTY) VALUES (‘S4’, ‘P2’, 200);
INSERT INTO SP (SNO, PNO, QTY) VALUES (‘S4’, ‘P4’, 300);
INSERT INTO SP (SNO, PNO, QTY) VALUES (‘S4’, ‘P5’, 400);
[/sourcecode]


3 responses to “SQL code for SQL and Relational Theory”

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.