Creating Tables

As mentioned previously, an RDBMS stores data in tables that look similar to spreadsheets. Of course, before you can store any data in a database, you need to create the necessary tables and columns to store the data. You do this by using the create statement.

For example, the cd_coiiection table from Figure 21.2 has 5 columns, or fields: id, title, artist, year, and rating.

SQL provides several column types for data that define what kind of data will be stored in the column. Some of the available types are int, float, char, and varchar. Both char and varchar hold text strings, with the difference being that char holds a fixed-length string, whereas varchar holds a variable-length string.

There are also special column types, such as date, that only take data in a date format, and enums (enumerations), which can be used to specify that only certain values are allowed. If, for example, you wanted to record the genre of your CDs, you could use an enum column that accepts only the values pop, rock, easy_listening, and so on. You will learn more about enum later in this chapter.

Looking at the cd_coiiection table, you can see that three of the columns hold numerical data and the other two hold string data. In addition, the character strings are of variable length. Based on this information, you can discern that the best type to use for the text columns is type varchar, and the best type to use for the others is INT. You should notice something else about the cd_coiiection table: One of the CDs is missing a rating, perhaps because we have not listened to it yet. This value, therefore, is optional; it starts empty and can be filled in later.

You are now ready to create a table. As mentioned before, you do this by using the create statement, which uses the following syntax:

cREATE TABLE tabie_name (coiumn_name coiumn_type(parameters) options, ...);

You should know the following about the create statement:

• SQL commands are not case sensitive For example, create table, create table, and Create Table are all valid.

• Whitespace is generally ignored This means you should use it to make your SQL commands clearer.

The following example shows how to create the table for the cd_coiiection database:

CREATE TABLE cd_collection (

id INT NOT NULL, title VARCHAR(50) NOT NULL, artist VARCHAR(50) NOT NULL, year VARCHAR(50) NOT NULL, rating VARCHAR(50) NULL

Notice that the statement terminates with a semicolon. This is how SQL knows you are finished with all the entries in the statement. In some cases, the semicolon can be omitted, and we will point out these cases when they arise.

SQL has a number of reserved keywords that cannot be used in table names or field names. For example, if you keep track of CDs you want to take with you on vacation, you would not be able to use the field name select because that is a reserved keyword. Instead, you should either choose a different name (selected?) or just prefix the field name with an f, such as fselect.

Was this article helpful?

0 0

Post a comment