7 short labs in Database
GEIT 3341 DATABASE 1 LAB 5
GEIT 3341 Database I
Lab 5
Schema Creation using SQL
Due Date:
Objective(s) Target CLO(s) Reference 1. Practice the CREATE TABLE 6 Chapter 6,7
command of SQL DDL by creating two tables and linking them together.
2. Use the CHECK constraint to specify a domain for certain columns.
3. To use the INSERT command of SQL DML in order to populate the tables with data.
4. Use the ALTER TABLE command to add a new column.
5. Use the UPDATE command to assign a value of the new column for each row.
Name
ID
Section
GEIT 3341 DATABASE 1 LAB 5
Instructions:
Use the CREATE TABLE command you learned in Chapter 7 and the INSERT command
you learned in Chapter 6 to create the following schema consisting of 3 tables. For each
field, pick the most appropriate data type for it. While creating the STORE table, add a
CHECK constraint to ensure that values of the STORE_YTD_SALES$ are greater than
zero.
Table Name: EMPLOYEE Primary Key: EMP_CODE
EMP_CODE EMP_LNAME EMP_FNAME EMP_DOB EMP_PHONE 1 Williamson John 29-DEC-62 (06) 434-0095
2 Ratula Nancy 12-MAR-67 (02) 526-1192
3 Greenboro Lottie 02-NOV-59 (03) 231-6292
4 Rumpersto Jennie 11-APR-64 (06) 224-1122
5 Shawn Michael 23-DEC-60 (06) 599-0406
6 Jones Rose 13-SEP-65 (06) 111-6262
7 Rosten Peter 25-OCT-65 (02) 111-1133
8 Bret Hart 12-AUG-67 (02) 796-1122
9 Ron Frank 11-NOV-60 (03) 432-1356
10 Elain Roberts 23-FEB-62 (06) 732-1967
Table Name: STORE Primary Key: STORE_CODE Foreign Key: REGION_CODE references REGION_CODE in REGION table
STORE_CODE STORE_NAME STORE_YTD_SALES$ REGION_CODE
1 Access Jungle 20000.65 1
2 Database Corner 55000.00 2
3 PC Master 110000.44 2
4 Computer City 82000.11 1
GEIT 3341 DATABASE 1 LAB 5
Table Name: REGION Primary Key: REGION_CODE
REGION
REGION_CODE REGION_DESCRIPT
1 East
2 West
3 North
4 South
After creating the three tables, use the ALTER TABLE command to add a new column
STORE_CODE to the EMPLOYEE table. Then use the ALTER TABLE command again to
designate it as a foreign that references STORE_CODE in STORE table. After this, use the
UPDATE command to set the STORE_CODE of each employee according to the following
table:
EMP_CODE STORE_CODE 1 1
2 2
3 2
4 4
5 1
6 2
7 3
8 1
9 2
10 3
Hand in:
1. The three CREATE TABLE commands for the three tables. (5 points) 2. The ALTER TABLE command to add the STORE_CODE column. (1 points) 3. The ALTER TABLE command to designate STORE_CODE as a foreign key. (1 points) 4. The UPDATE commands you used to assign a STORE_CODE for each employee.
(2 points) 5. Screen shots of the content of the three tables. (1 points)