SQL Example Data Setup

From Zanecorpwiki

This is the example tables and data used in the SQL category examples.

This data is simplified to the point of being simplistic and is NOT of the style appropriate to real data bases where you'd want to use row IDs and other conventions not shown here.

CREATE TABLE supplier (
  name VARCHAR(16),
  state VARCHAR(16)
);

CREATE TABLE itemsupplier (
  supplier VARCHAR(16),
  item VARCHAR(16),
  price DECIMAL(7,2),
  PRIMARY KEY (supplier, item),
  FOREIGN KEY (supplier) REFERENCES supplier (name)
);

INSERT INTO supplier VALUES('Acme', 'Texas');
INSERT INTO supplier VALUES('Corp', 'New York');
INSERT INTO supplier VALUES('Ma and Pop', 'Main');

INSERT INTO itemsupplier VALUES ('Acme', 'widget', 3.00);
INSERT INTO itemsupplier VALUES ('Acme', 'dongle', 8.00);
INSERT INTO itemsupplier VALUES ('Acme', 'thingy', 10.00);
INSERT INTO itemsupplier VALUES ('Corp', 'widget', 4.50);
INSERT INTO itemsupplier VALUES ('Corp', 'dongle', 6.00);
INSERT INTO itemsupplier VALUES ('Ma and Pop', 'thingy', 9.75);