8.3 Other SQL commands

This section deals with SQL commands that perform other common useful actions on a database.

We start with entering the data into a database.

Creating a table proceeds in two steps: first we must define the schema or structure of the table and then we can load rows of values into the table.

8.3.1 Defining tables

A table schema is defined using the CREATE command.

CREATE TABLE tablename
    (col1name col1type,
     col2name col2type)
    column_constraints;

This command specifies the name of the table, the name of each column, and the data type to be stored in each column. A common variation is to add NOT NULL after the column data type to indicate that the value of the column can never be NULL. This must usually be specified for primary key columns.

The set of possible data types available depends on the DBMS being used, but some standard options are shown in Table 8.1.

The column_constraints are used to specify primary and foreign keys for the table.

CREATE TABLE table1
    (col1name col1type NOT NULL,
     col2name col2type)
    CONSTRAINT constraint1
        PRIMARY KEY (col1name)
    CONSTRAINT constraint2
        FOREIGN KEY (col2name)
        REFERENCES table2 (table2col);

Table 8.1: Some common SQL data types.

Type	Description
CHAR(n)	Fixed-length text (n characters)
VARCHAR(n)	Variable-length text (maximum n characters)
INTEGER	Whole number
REAL	Real number
DATE	Calendar date

The primary key constraint specifies which column or columns make up the primary key for the table. The foreign key constraint specifies which column or columns in this table act as a foreign key and the constraint specifies the table and the column in that table that the foreign key refers to.

As concrete examples, the code in Figure 8.1 shows the SQL code that was used to create the database tables date_table, location_table, and measure_table for the Data Expo case study in Section 7.1.

Figure 8.1: The SQL code used to define the table schema for storing the Data Expo data set in a relational database (see Section 7.1).

 

CREATE TABLE date_table 
    (ID    INTEGER NOT NULL,
     date  DATE,
     month CHAR(9),
     year  INTEGER,
     CONSTRAINT date_table_pk PRIMARY KEY (ID));

CREATE TABLE location_table 
    (ID    INTEGER NOT NULL,
     longitude   REAL,
     latitude    REAL,
     elevation   REAL,
     CONSTRAINT location_table_pk PRIMARY KEY (ID));

CREATE TABLE measure_table 
    (location    INTEGER NOT NULL,
     date        INTEGER NOT NULL,
     cloudhigh   REAL,
     cloudmid    REAL,
     cloudlow    REAL,
     ozone       REAL,
     pressure    REAL,
     surftemp    REAL,
     temperature REAL,
     CONSTRAINT measure_table_pk 
         PRIMARY KEY (location, date),
     CONSTRAINT measure_date_table_fk 
         FOREIGN KEY (date)
         REFERENCES date_table(ID),
     CONSTRAINT measure_location_table_fk 
         FOREIGN KEY (location)
         REFERENCES location_table(ID));

The primary key of the date_table is the ID column and the primary key of the location_table is its ID column. The (composite) primary key of the measure_table is a combination of the location and date columns. The measure_table also has two foreign keys: the date column acts as a foreign key, referring to the ID column of the date_table, and the location column also acts as a foreign key, referring to the ID column of the location_table.

8.3.2 Populating tables

Having generated the table schema, values are entered into the table using the INSERT command.

INSERT INTO table VALUES
    (value1, value2);

There should be as many values as there are columns in the table, with values separated from each other by commas. Text values should be enclosed within single-quotes.

Most DBMS software also provides a way to read data values into a table from an external (text) file. For example, in SQLite, the special .import command can be used to read values from an external text file.

8.3.3 Modifying data

Values in a database table may be modified using the UPDATE command.

UPDATE table
    SET column = value
    WHERE row_condition

The rows of the specified column, within the specified table, that satisfy the row_condition, will be changed to the new value.

8.3.4 Deleting data

The DELETE command can be used to remove specific rows from a table.

DELETE FROM table
    WHERE row_condition;

The DROP command can be used to completely remove not only the contents of a table but also the entire table schema so that the table no longer exists within the database.

DROP TABLE table;

In some DBMS, it is even possible to “drop” an entire database (and all of its tables).

DROP DATABASE database;

These commands should obviously be used with extreme caution.

Paul Murrell

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 New Zealand License.