POS/410 Week 1 – On-Ground

Scripts – Creating Tables in SQL

The purpose of this lecture is to provide the student with an introduction to the basics of creating tables using an actual script. The script is taken from the instructor supplied material on rEsource. The script creates a simple four table database used to track project assignments. You can select the script text and copy it into the SQL Server Query Analyzer and run it.

In SQL, tables are created using the CREATE statement. They are eliminated from the database using DROP and modified after creation with ALTER  Data is loaded into the tables via the INSERT statement. The overall logic flow of these scripts is:

    1) Drop the old database and create a new fresh one

    2) Create the tables. Note we have to create parent tables before child tables.

    3) Load data into the tables via an INSERT statement. Again parent tables before child tables.

(Note below that if the primary key is only one column it can be set via either a column constraint or a table constraint)

--Begin script; Recreate Database from faculty handout
use master;
drop database POS410_Projects;
go
create database POS410_Projects;
go
use POS410_Projects;

--Create and load tables
CREATE TABLE department
(
    dname VARCHAR(15) NOT NULL,
    dnumber INT NOT NULL,
    PRIMARY KEY (dnumber),
    UNIQUE (dname)
);


CREATE TABLE employee
(
    fname VARCHAR(15) NOT NULL,
    lname VARCHAR(15) NOT NULL,
    ssn INT NOT NULL PRIMARY KEY,
    salary DECIMAL(10,2),
    superssn INT,
    dno INT NOT NULL,
    FOREIGN KEY(superssn) REFERENCES employee(ssn),
    FOREIGN KEY(dno) REFERENCES department(dnumber)
);

CREATE TABLE project
(
    pname VARCHAR(15) NOT NULL,
    pnumber INT NOT NULL,
    dnum INT NOT NULL,
    PRIMARY KEY (pnumber),
    UNIQUE (pname),
    FOREIGN KEY (dnum) REFERENCES department(dnumber)
);

CREATE TABLE works_on
(
    ESSN INT NOT NULL,
    PNO INT NOT NULL,
    PRIMARY KEY(essn, pno),
    FOREIGN KEY(essn) REFERENCES employee(ssn),
    FOREIGN KEY(pno) REFERENCES project(pnumber)
);



--dname, dnumber

INSERT INTO department VALUES('Research', '1001');
INSERT INTO department VALUES('Accounting', '1002');
INSERT INTO department VALUES('Manufacturing', '1003');
INSERT INTO department VALUES('Human Resources', '1004');



--fname lname ssn salary superssn dno

INSERT INTO employee VALUES('Eileen', 'MacAdoo', '12345987', '65000', '12345987', '1004');
INSERT INTO employee VALUES('Nora', 'Watkins', '45123987', '35500', '12345987', '1001');
INSERT INTO employee VALUES('Mary Anne', 'Lazarro', '32145878', '60000', '12345987', '1003');
INSERT INTO employee VALUES('Clara', 'Thompson', '03412344', '53000', '12345987', '1003');
INSERT INTO employee VALUES('Raymond', 'Thompson', '02932455', '22200', '12345987', '1002');
INSERT INTO employee VALUES('Ziggy', 'Gravellese', '45698755', '35000', '12345987', '1002');
INSERT INTO employee VALUES('Frankie', 'Thompson', '32425444', '17500', '12345987', '1001');
INSERT INTO employee VALUES('Jeanne', 'Dyer', '52455666', '43000', '12345987', '1001');
INSERT INTO employee VALUES('Tony', 'Aero', '52432455', '20000', '12345987', '1003');
INSERT INTO employee VALUES('Jonathon', 'Gravellese', '98765422', '52300', '12345987', '1003');



--pname pnumber dnum

INSERT INTO project VALUES('projectA', '222', '1001');
INSERT INTO project VALUES('projectB', '333', '1003');
INSERT INTO project VALUES('projectC', '122', '1003');
INSERT INTO project VALUES('projectD', '232', '1002');
INSERT INTO project VALUES('projectE', '244', '1004');
INSERT INTO project VALUES('projectF', '400', '1002');



--ESSN PNO

INSERT INTO works_on VALUES('45123987', '222');
INSERT INTO works_on VALUES('45123987', '333');
INSERT INTO works_on VALUES('45123987', '244');
INSERT INTO works_on VALUES('03412344', '222');
INSERT INTO works_on VALUES('03412344', '122');
INSERT INTO works_on VALUES('12345987', '400');
INSERT INTO works_on VALUES('12345987', '244');
INSERT INTO works_on VALUES('02932455', '222');
INSERT INTO works_on VALUES('02932455', '400');
INSERT INTO works_on VALUES('32425444', '400');
INSERT INTO works_on VALUES('32425444', '333');
INSERT INTO works_on VALUES('32425444', '244');
INSERT INTO works_on VALUES('98765422', '122');
INSERT INTO works_on VALUES('98765422', '244');
 

We’ll look at queries and summarization later in a future lecture.

Dan D'Urso
Laguna Niguel, CA
April 2005

Back | Home