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.
A table schema is defined using the CREATE command.
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.
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.
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.
Having generated the table schema, values are entered into the table using the INSERT command.
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.
Values in a database table may be modified using the UPDATE command.
The rows of the specified column, within the specified table, that satisfy the row_condition, will be changed to the new value.
The DELETE command can be used to remove specific rows from a table.
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.
In some DBMS, it is even possible to “drop” an entire database (and all of its tables).
These commands should obviously be used with extreme caution.
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 New Zealand License.