CIS 331: Database Management Using SQL

A Guide to MySQL, Chapter 3: Introduction to SQL

Objectives:

This chapter introduces the student to the relations, relational algebra, and several functions common to queries. The objectives important to this chapter are:

  1. Understand concepts and terminology of relational databases
  2. Create and run SQL commands
  3. Create tables using SQL
  4. Identify and use data types to define columns in SQL tables
  5. Understand and use nulls
  6. Add rows to tables
  7. Describe a table's layout using SQL
Concepts:
Relational Model

The chapter begins with a description of relational databases. This discussion is practically a repeat of the material from chapter two of Concepts of Database Management.

The difference is that this chapter centers on the use of a command line interface to our SQL engine. If you log into Crux, then access MySQL, you can use commands demonstrated in the chapter to CREATE tables, DROP tables, INSERT data into tables, etc. (You can also do this on the SQLCourse web site. There is a link on the menu for this course.)

For example, this command will create the Customer table:

CREATE TABLE Customer
(CustomerNum CHAR(3) PRIMARY KEY,
CustomerName CHAR(35) NOT NULL,
Street CHAR(15),
City CHAR(15),
State CHAR(2),
Zip CHAR(5),
Balance DECIMAL(8,2),
CreditLimit DECIMAL(8,2),
RepNum CHAR(2) )
;

The words shown above in capital letters need not be entered capitalized. I have capitalized them to show that they are actual SQL command words.

This example would insert a record into the Customer table:

INSERT INTO Customer
VALUES
('524','Kline''s','838 Ridgeland','Fillmore','FL','33336',12762.00,15000.00,'20');

In the student download files, there is a problem. The commands to insert data do not always refer to the table with the same capitalization as the command to create the table. This is a problem because Crux is a Unix system. Remember, Unix is very particular about capital and lower case letters. (Suggestion: If you user the student download files to create your data files, open them in Notepad first, and make sure that all references to a given file use the same capitalization.)

To erase a table, use the drop command:

DROP TABLE Customer;

This example would remove this table from the database.

As noted in the relational algebra material, you can use the select command to see the data in a table. To see the whole table use a command like this:

SELECT * FROM Customer;

To see selected fields, specify them in a comma delimited list, instead of using an asterisk.