This chapter introduces the student to the relations, relational algebra, and several functions common to queries. The objectives important to this chapter are:
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
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
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.