DATABASE DESIGN - SQL TABLES

Assignment 3 / Lab 3: Creating Database Tables

SQL Commands

CREATE TABLE

DROP TABLE

SHOW tables

DESCRIBE tblname

Description

In this assignment use the MySQL database software to create a table in the database created in Assignment 2.

Step 1: Use the information in Assignment 2 to identify the name of the database created.

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| wuv22707 |

+--------------------+

5 rows in set (0.1198 sec)

Step 2: Once the Database is identified, enter USE database_name on the command line of the MySQL Shell:

Mysql >use wuv22707;

Step 3: Create a table in the database. The following is provided as an example. You may use any entity that you wish to use.

Mysql> create table if not exists tbl_student (

student_ID CHAR(5),

student_FNAME VARCHAR(25),

student_LNAME VARCHAR(25),

student_ZIP INT,

student_DOB DATE);

Query OK, 0 rows affected (0.1490 sec)

Note: A summary of datatype can be found in the class files (datatypes.txt)

Step 4: Verify that the table exists in the database:

MSQL> SHOW tables;

+--------------------+

| Tables_in_wuv22707 |

+--------------------+

| tbl_student |

+--------------------+

1 row in set (0.0335 sec)

Step 5: Verify that the table can be drop and re added:

MySQL> DROP TABLE tbl_student;

MYSQL> SHOW tables;

MYSQL> CREATE TABLE IF NOT EXISTS tbl_student(…);

MYSQL> SHOW tables;

Step 6: Show the attitubes of the table to very that the table was created with correct datatypes:

MYSQL> DESCRIBE tbl_student

Note: Use the name of the table that you create after the DESCRIBE command