Basic SQL Statements
Structured Query Language (SQL) is used to perform relational database operations. Despite the existence of an ANSI (American National Standards Institute) SQL standard, different database providers may provide SQL implementations that are not exactly the same and may contain nuanced differences and proprietary additions.
SQL statements can be logically grouped into a number of languages, in which they are identified by SQL keywords:
- Data Manipulation Language (DML) comprises the SELECT, INSERT, UPDATE, and DELETE statements, used to query, create, modify, and remove table rows. Sometimes the SELECT statement is described to be in its own Data Query Language (DQL) group.
- Data Definition Language (DDL) comprises the CREATE, ALTER, and DROP statements, whose purpose is to create, modify, and remove tables. We will refer to the CREATE, ALTER, and DROP statements as DDL operations.
- Transaction Control Language (TCL) comprises the COMMIT and ROLLBACK statements, whose purpose is to save or undo pending changes, respectively.
Often the INSERT, SELECT, UPDATE, and DELETE statements (in that order) are referred to as CRUD operations, denoting the Create, Read/Retrieve, Update, and Delete operations implied by the acronym, respectively. Note the name mismatch of the CRUD operations and the SQL statements is implied. In particular, the set of CRUD operations does not include the CREATE statement in SQL that creates relational tables.
The rest of this subsection presents basic syntax patterns and examples of creating relational tables and the CRUD operations in SQL. Note that the semicolon (;) is a statement terminator in SQL.
The CREATE Statement
The CREATE statement can be used to create a new table in the database:
CREATE TABLE
table_name
(
column_definitions
);
where table_name is the name of the table to be created, and column_definitions is a comma-delimited list that describes each column in this table:
column_name column_type constraints
, …
The following statement creates an empty compositions table:
CREATE TABLE compositions (isrc VARCHAR(12) PRIMARY KEY,
title VARCHAR(40) NOT NULL, duration INT NOT NULL);
The CREATE statement above will create an empty relational table whose columns will correspond to the table shown in Table 24.2.
The INSERT Statement
The INSERT statement can be used to insert a new row into a table:
INSERT INTO
table_name
VALUES (
actual_values
);
where actual_values in the VALUES clause is a comma-delimited list that provides values for the columns of the table specified by table_name.
The following statement inserts a new row into the compositions table:
INSERT INTO compositions VALUES (‘ushm91736697’, ‘Vacation’, 231);
The number of rows in the compositions table increases by 1. By default, the values specified are interpreted in the same order as the columns are defined in the table. Note that string literals in SQL are enclosed in single quotes (‘).
The SELECT Statement
The SELECT statement can be used to query or read rows from the database:
SELECT
column_list
FROM
table_name
WHERE
row_filter
;
The column_list is a comma-delimited list of column names, whose values are selected from the table specified with table_name in the FROM clause. The ‘*’ symbol can be used as column_list to specify that all columns should be included in the returned result. A list of conditions is specified by row_filter in the WHERE clause to determine which specific rows should be selected. Such conditions may be constructed using a variety of operators to compare column values, such as = (equals), > (greater than), < (less than), LIKE (which can use “%” wildcards), and many others. Conditions in row_filter can be combined using AND and OR operators.
column_name
=
some_value
AND
column_name
LIKE
some_value
The following statement selects values of isrc and title for all rows in the compositions table whose duration is greater than 200 and whose title starts with ‘V’:
SELECT isrc, title FROM compositions WHERE duration > 200 AND title LIKE ‘V%’;
Applied to Table 24.2, the above query will return the values of isrc and title in the first row, which is the only row that meets the row_filter criteria—that is, (ushm91736697, Vacation).
The WHERE clause is optional. In that case, the values of column_list in the entire table are returned.
SELECT * FROM compositions;
The above SELECT statement will return all the rows in the specified table.
