Supplier-Parts Database SQL Script

Below is a DDL script to create Chris Date's supplier-parts database. If Chris Date can explain SQL using this simple database it is worth trying. There is another another version of this database with an additional table that I may post here eventually. A few points of interest in the DDL below:  1) in the Parts table note the use of a unique constraint at the table level to define the uniqueness of the the combination of pname, color and city, 2) in the SupplierParts table there is again a table level constraint as the primary key is the combination of pnum and snum; in the same table definition note that the foreign key references clause does not include a referenced field name because the field names are the same.

CREATE TABLE Suppliers
  (
 SNUM int NOT NULL PRIMARY KEY,
 SNAME varchar(16) NOT NULL UNIQUE,
 STATUS int NOT NULL,
 CITY varchar(20) NOT NULL
  );

CREATE TABLE Parts
  (
 PNUM int NOT NULL PRIMARY KEY,
 PNAME varchar(18) NOT NULL,
 COLOR varchar(10) NOT NULL,
 WEIGHT decimal(4,1) NOT NULL,
 CITY varchar(20) NOT NULL,
 UNIQUE (PNAME, COLOR, CITY)
  );

CREATE TABLE SupplierParts
  (
 SNUM int NOT NULL REFERENCES Suppliers,
 PNUM int NOT NULL REFERENCES Parts,
 QTY int NOT NULL,
 PRIMARY KEY (SNUM, PNUM)
  );

  INSERT INTO Suppliers VALUES (1, 'Smith', 20, 'London');
  INSERT INTO Suppliers VALUES (2, 'Jones', 10, 'Paris');
  INSERT INTO Suppliers VALUES (3, 'Blake', 30, 'Paris');
  INSERT INTO Suppliers VALUES (4, 'Clark', 20, 'London');
  INSERT INTO Suppliers VALUES (5, 'Adams', 30, 'Athens');
  INSERT INTO Parts VALUES (1, 'Nut', 'Red', 12, 'London');
  INSERT INTO Parts VALUES (2, 'Bolt', 'Green', 17, 'Paris');
  INSERT INTO Parts VALUES (3, 'Screw', 'Blue', 17, 'Oslo');
  INSERT INTO Parts VALUES (4, 'Screw', 'Red', 14, 'London');
  INSERT INTO Parts VALUES (5, 'Cam', 'Blue', 12, 'Paris');
  INSERT INTO Parts VALUES (6, 'Cog', 'Red', 19, 'London');
  INSERT INTO SupplierParts VALUES (1, 1, 300);
  INSERT INTO SupplierParts VALUES (1, 2, 200);
  INSERT INTO SupplierParts VALUES (1, 3, 400);
  INSERT INTO SupplierParts VALUES (1, 4, 200);
  INSERT INTO SupplierParts VALUES (1, 5, 100);
  INSERT INTO SupplierParts VALUES (1, 6, 100);
  INSERT INTO SupplierParts VALUES (2, 1, 300);
  INSERT INTO SupplierParts VALUES (2, 2, 400);
  INSERT INTO SupplierParts VALUES (3, 2, 200);
  INSERT INTO SupplierParts VALUES (4, 2, 200);
  INSERT INTO SupplierParts VALUES (4, 4, 300);
  INSERT INTO SupplierParts VALUES (4, 5, 400);