SQL Starter Kit

SQL Starter Notes for Project 2

I write this document so you can read about the steps I took to get our Project 2 (Manufacture) database ready for your use. As you learn SQL, you will be able to try out your SQL Commands via our class SQL Select Tool. From there, you can type in SQL SELECT statements against our class project database.

After using SSH (secure shell) software to connect to www.cev.washington.edu (our class server) with my account, I created the database by using the createdb command that comes as part of the Red Hat 9.0 operating system (the operating system came with PostGres 7.4 included). So, I typed createdb manufacture at a ssh prompt to create a database named manufacture. Once the database existed, I could use the psql interface to run SQL commands against our database. To get the psql interface pointed towards our database, I typed psql manufacture at the ssh command prompt. The ssh console responded:

bash-2.05b$ psql manufacture
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

manufacture=#

I then ran the following CREATE TABLE and INSERT INTO commands at the psql prompt (psql stands for Postgres Structured Query Language which is an extension of the SQL specification. Note how all the commands handle the appropriate type information for each data attribute:


CREATE TABLE ProductSale (
ProductSale_ID int,
Product_FK int,
Customer_FK int,
Qty int,
Price int,
Date date,
Owner text);

INSERT INTO ProductSale VALUES (0, 0, 0, 2, 500, '05/10/2004', 'brc');

CREATE TABLE Production (
Production_ID int,
Product_FK int,
Material_FK int,
Date date,
Cost real,
Owner text);

INSERT INTO Production VALUES(0, 0, 0, '05/12/2004', 340.00, 'brc');

CREATE TABLE MaterialPurchase (
MaterialPurchase_ID int,
Material_FK int,
Supplier_FK int,
Date date,
Qty int,
Cost real,
Quality int,
Owner text);

INSERT INTO MaterialPurchase VALUES (0, 0, 0, '05/12/2004', 14, 1209.94, 7, 'brc');

CREATE TABLE Customer (
Customer_ID int,
Name text,
Gender int,
Standing int,
State text,
ZipCode text,
Owner text);

INSERT INTO Customer VALUES (0, 'The Rivet Factory', 0, 2, 'Wisconsin', '53723', 'brc');

CREATE TABLE Product (
Product_ID int,
Name text,
Line text,
Mat1_FK int,
Qty1 int,
Mat2_FK int,
Qty2 int,
Mat3_FK int,
Qty3 int,
Mat4_FK int,
Qty4 int,
Owner text);

INSERT INTO Product VALUES (0, 'Widget X', 'X Line', 0, 2, 1, 4, 2, 2, -1, 0, 'brc ');

CREATE TABLE Material (
Material_ID int,
OnHand int,
LeadTime int,
Unit text,
Owner text);

INSERT INTO Material VALUES (0, 0, 14, 'Pieces', 'brc');

CREATE TABLE Supplier (
Supplier_ID int,
Name text,
Location text,
Owner text);

INSERT INTO Supplier VALUES (0, 'ABC Power', 'Cincinnati, OH', 'brc');

CREATE TABLE Generic (
Gen_ID int,
First text,
Second text,
Third text,
Fourth text,
Fifth int,
Sixth int,
Seventh int,
Eighth real,
Ninth real,
Tenth date,
Owner text);