This appendix details some of the most common SQL commands. As stated throughout the book, be sure to check your database documentation: Some of the statements vary, depending on your implementation.
ALTER TABLEALTER 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
ALTER USERALTER USER USERNAME IDENTIFIED BY NEW_PASSWORD;
Description: Resets a given user’s password
COMMITCOMMIT [ TRANSACTION ]
Description: Saves a transaction to the database
CREATE INDEXCREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME)
Description: Creates an index on a table
CREATE ROLECREATE ROLE ROLE NAME [ WITH ADMIN [CURRENT_USER | CURRENT_ROLE]]
Description: Creates a database role to which system and object privileges can be granted
CREATE TABLECREATE TABLE TABLE_NAME ( COLUMN1 DATA_TYPE [NULL|NOT NULL], COLUMN2 DATA_TYPE [NULL|NOT NULL])
Description: Creates a database table
CREATE TABLE ASCREATE 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 TYPECREATE 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 USERCREATE USER username IDENTIFIED BY password
Description: Creates a user account in the database
CREATE VIEWCREATE VIEW AS SELECT COLUMN1, COLUMN2,... FROM TABLE_NAME [ WHERE CONDITIONS ] [ GROUP BY COLUMN1, COLUMN2,... ] [ HAVING CONDITIONS ]
Description: Creates a view of a table
DELETEDELETE FROM TABLE_NAME [ WHERE CONDITIONS ]
Description: Deletes rows of data from a table
DROP INDEXDROP INDEX INDEX_NAME
Description: Drops an index on a table
DROP TABLEDROP TABLE TABLE_NAME
Description: Drops a table from the database
DROP USERDROP USER user1 [, user2, ...]
Description: Drops a user account from the database
DROP VIEWDROP VIEW VIEW_NAME
Description: Drops a view of a table
GRANTGRANT PRIVILEGE1, PRIVILEGE2, ... TO USER_NAME
Description: Grants privileges to a user
INSERTINSERT INTO TABLE_NAME [ (COLUMN1, COLUMN2,...]
VALUES ('VALUE1','VALUE2',...)
Description: Inserts new rows of data into a table
INSERT...SELECTINSERT 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
REVOKEREVOKE PRIVILEGE1, PRIVILEGE2, ... FROM USER_NAME
Description: Revokes privileges from a user
ROLLBACKROLLBACK [ TO SAVEPOINT_NAME ]
Description: Undoes a database transaction
SAVEPOINTSAVEPOINT SAVEPOINT_NAME
Description: Creates transaction savepoints to roll back to, if necessary
SELECTSELECT [ 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
UPDATEUPDATE TABLE_NAME
SET COLUMN1 = 'VALUE1',
COLUMN2 = 'VALUE2',...
[ WHERE CONDITIONS ]
Description: Updates existing data in a table
SELECTSELECT * SELECT COLUMN1, COLUMN2,... SELECT DISTINCT (COLUMN1) SELECT COUNT(*)
Description: Defines columns to display as part of query output
FROMFROM TABLE1, TABLE2, TABLE3,...
Description: Defines tables from which to retrieve data
WHEREWHERE 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 BYGROUP BY GROUP_COLUMN1, GROUP_COLUMN2,...
Description: Divides output into logical groups; a form of sorting operation
HAVINGHAVING GROUP_COLUMN1 = 'VALUE1' AND GROUP_COLUMN2 = 'VALUE2' ...
Description: Places conditions on the GROUP BY clause; similar to the WHERE clause
ORDER BYORDER BY COLUMN1, COLUMN2,... ORDER BY 1,2,...
Description: Sorts a query’s results