Appendix A. Common SQL Commands
This appendix details some of the most common SQL commands that you will use. As we have stated throughout the book, check your database documentation, because some of the statements vary depending upon your implementation.
SQL Statements
ALTER TABLE
ALTER TABLE TABLE_NAME
[MODIFY | ADD | DROP]
[COLUMN COLUMN_NAME][DATATYPE|NULL NOT NULL] [RESTRICT|CASCADE]
[ADD | DROP] CONSTRAINT CONSTRAINT_NAME]
Description: Alters a table’s columns.
COMMIT
COMMIT [ TRANSACTION ]
Description: Saves a transaction to the database.
CREATE INDEX
CREATE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME)
Description: Creates an index on a table.
CREATE ROLE
CREATE ROLE ROLE NAME
[ WITH ADMIN [CURRENT_USER | CURRENT_ROLE]]
Description: Creates a database role to which system and object privileges can be granted.
CREATE TABLE
CREATE TABLE TABLE_NAME
( COLUMN1 DATA_TYPE [NULL|NOT NULL],
COLUMN2 DATA_TYPE [NULL|NOT NULL])
Description: Creates a database table.
CREATE TABLE AS
CREATE TABLE TABLE_NAME AS
SELECT COLUMN1, COLUMN2,...
FROM TABLE_NAME
[ WHERE CONDITIONS ]
[ GROUP BY COLUMN1, COLUMN2,...]
[ HAVING CONDITIONS ]
Description: Creates a database table based on another table.
CREATE TYPE
CREATE TYPE typename AS OBJECT
( COLUMN1 DATA_TYPE [NULL|NOT NULL],
COLUMN2 DATA_TYPE [NULL|NOT NULL])
Description: Creates a user-defined type that can define columns in a table.
CREATE USER
CREATE USER username IDENTIFIED BY password
Description: Creates a user account in the database.
CREATE VIEW
CREATE VIEW AS
SELECT COLUMN1, COLUMN2,...
FROM TABLE_NAME
[ WHERE CONDITIONS ]
[ GROUP BY COLUMN1, COLUMN2,... ]
[ HAVING CONDITIONS ]
Description: Creates a view of a table.
DELETE
DELETE
FROM TABLE_NAME
[ WHERE CONDITIONS ]
Description: Deletes rows of data from a table.
DROP INDEX
DROP INDEX INDEX_NAME
Description: Drops an index on a table.
DROP TABLE
DROP TABLE TABLE_NAME
Description: Drops a table from the database.
DROP USER
DROP USER user1 [, user2, ...]
Description: Drops a user account from the database.
DROP VIEW
DROP VIEW VIEW_NAME
Description: Drops a view of a table.
GRANT
GRANT PRIVILEGE1, PRIVILEGE2, ... TO USER_NAME
Description: Grants privileges to a user.
INSERT
INSERT INTO TABLE_NAME [ (COLUMN1, COLUMN2,...]
VALUES ('VALUE1','VALUE2',...)
Description: Inserts new rows of data into a table.
INSERT...SELECT
INSERT INTO TABLE_NAME
SELECT COLUMN1, COLUMN2
FROM TABLE_NAME
[ WHERE CONDITIONS ]
Description: Inserts new rows of data into a table based on data in another table.
REVOKE
REVOKE PRIVILEGE1, PRIVILEGE2, ... FROM USER_NAME
Description: Revokes privileges from a user.
ROLLBACK
ROLLBACK [ TO SAVEPOINT_NAME ]
Description: Undoes a database transaction.
SAVEPOINT
SAVEPOINT SAVEPOINT_NAME
Description: Creates transaction savepoints in which to roll back if necessary.
SELECT
SELECT [ DISTINCT ] COLUMN1, COLUMN2,...
FROM TABLE1, TABLE2,...
[ WHERE CONDITIONS ]
[ GROUP BY COLUMN1, COLUMN2,...]
[ HAVING CONDITIONS ]
[ ORDER BY COLUMN1, COLUMN2,...]
Description: Returns data from one or more database tables; used to create queries.
UPDATE
UPDATE TABLE_NAME
SET COLUMN1 = 'VALUE1',
COLUMN2 = 'VALUE2',...
[ WHERE CONDITIONS ]
Description: Updates existing data in a table.
SQL Clauses
SELECT
SELECT *
SELECT COLUMN1, COLUMN2,...
SELECT DISTINCT (COLUMN1)
SELECT COUNT(*)
Description: Defines columns to display as part of query output.
FROM
FROM TABLE1, TABLE2, TABLE3,...
Description: Defines tables from which to retrieve data.
WHERE
WHERE COLUMN1 = 'VALUE1'
AND COLUMN2 = 'VALUE2'
...
WHERE COLUMN1 = 'VALUE1'
OR COLUMN2 = 'VALUE2'
...
WHERE COLUMN IN ('VALUE1' [, 'VALUE2'] )
Description: Defines conditions (criteria) placed on a query for data to be returned.
GROUP BY
GROUP BY GROUP_COLUMN1, GROUP_COLUMN2,...
Description: Divides output into logical groups; a form of sorting operation.
HAVING
HAVING GROUP_COLUMN1 = 'VALUE1'
AND GROUP_COLUMN2 = 'VALUE2'
...
Description: Places conditions on the GROUP BY clause; similar to the WHERE clause.
ORDER BY
ORDER BY COLUMN1, COLUMN2,...
ORDER BY 1,2,...
Description: Sorts a query’s results.