Appendix B. SQL Statement Syntax

To help you find the syntax you need when you need it, this appendix lists the syntax for the most frequently used SQL operations. Each statement starts with a brief description and then displays the appropriate syntax. For added convenience, you’ll also find cross-references to the lessons where specific statements are taught.

When reading statement syntax, remember the following:

The | symbol is used to indicate one of several options, so NULL|NOT NULL means specify either NULL or NOT NULL.

Keywords or clauses contained within square brackets [like this] are optional.

The syntax listed below will work with almost all DBMSs. You are advised to consult your own DBMS documentation for details of implementing specific syntactical changes.

ALTER TABLE

ALTER TABLE is used to update the schema of an existing table. To create a new table, use CREATE TABLE. See Lesson 17, “Creating and Manipulating Tables,” for more information.

Input ▾

Click here to view code image

ALTER TABLE tablename
(
  ADD|DROP  column  datatype  [NULL|NOT NULL]  [CONSTRAINTS],
  ADD|DROP  column  datatype  [NULL|NOT NULL]  [CONSTRAINTS],
    ...
);

COMMIT

COMMIT is used to write a transaction to the database. See Lesson 20, “Managing Transaction Processing,” for more information.

Input ▾

COMMIT [TRANSACTION];

CREATE INDEX

CREATE INDEX is used to create an index on one or more columns. See Lesson 22, “Understanding Advanced SQL Features,” for more information.

Input ▾

CREATE INDEX indexname
ON tablename (column, ...);

CREATE PROCEDURE

CREATE PROCEDURE is used to create a stored procedure. See Lesson 19, “Working with Stored Procedures,” for more information. Oracle uses a different syntax as described in that lesson.

Input ▾

Click here to view code image

CREATE PROCEDURE procedurename [parameters] [options]
AS
SQL statement;

CREATE TABLE

CREATE TABLE is used to create new database tables. To update the schema of an existing table, use ALTER TABLE. See Lesson 17 for more information.

Input ▾

Click here to view code image

CREATE TABLE tablename
(
    column    datatype    [NULL|NOT NULL]    [CONSTRAINTS],
    column    datatype    [NULL|NOT NULL]    [CONSTRAINTS],
       ...
);

CREATE VIEW

CREATE VIEW is used to create a new view of one or more tables. See Lesson 18, “Using Views,” for more information.

Input ▾

CREATE VIEW viewname AS
SELECT columns, ...
FROM tables, ...
[WHERE ...]
[GROUP BY ...]
[HAVING ...];

DELETE

DELETE deletes one or more rows from a table. See Lesson 16, “Updating and Deleting Data,” for more information.

Input ▾

DELETE FROM tablename
[WHERE ...];

DROP

DROP permanently removes database objects (tables, views, indexes, and so forth). See Lessons 17 and 18 for more information.

Input ▾

Click here to view code image

DROP INDEX|PROCEDURE|TABLE|VIEW indexname|procedurename|tablename|
viewname;

INSERT

INSERT adds a single row to a table. See Lesson 15, “Inserting Data,” for more information.

Input ▾

Click here to view code image

INSERT INTO tablename [(columns, ...)]
VALUES(values, ...);

INSERT SELECT

INSERT SELECT inserts the results of a SELECT into a table. See Lesson 15 for more information.

Input ▾

Click here to view code image

INSERT INTO tablename [(columns, ...)]
SELECT columns, ... FROM tablename, ...
[WHERE ...];

ROLLBACK

ROLLBACK is used to undo a transaction block. See Lesson 20 for more information.

Input ▾

ROLLBACK [TO savepointname];

or

Input ▾

ROLLBACK TRANSACTION;

SELECT

SELECT is used to retrieve data from one or more tables (or views). See Lesson 2, “Retrieving Data,” Lesson 3, “Sorting Retrieved Data,” and Lesson 4, “Filtering Data,” for more basic information. (Lessons 2–14 cover aspects of SELECT.)

Input ▾

SELECT columnname, ...
FROM tablename, ...
[WHERE ...]
[UNION ...]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...];

UPDATE

UPDATE updates one or more rows in a table. See Lesson 16 for more information.

Input ▾

UPDATE tablename
SET columname = value, ...
[WHERE ...];