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.
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 [ TRANSACTION ]
Description: Saves a transaction to the database.
CREATE INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME)
Description: Creates an index on a table.
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 TABLE_NAME
( COLUMN1 DATA_TYPE [NULL|NOT NULL],
COLUMN2 DATA_TYPE [NULL|NOT NULL])
Description: Creates a database table.
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 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 username IDENTIFIED BY password
Description: Creates a user account in the database.
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
FROM TABLE_NAME
[ WHERE CONDITIONS ]
Description: Deletes rows of data from a table.
DROP INDEX INDEX_NAME
Description: Drops an index on a table.
DROP TABLE TABLE_NAME
Description: Drops a table from the database.
DROP USER user1 [, user2, ...]
Description: Drops a user account from the database.
DROP VIEW VIEW_NAME
Description: Drops a view of a table.
GRANT PRIVILEGE1, PRIVILEGE2, ... TO USER_NAME
Description: Grants privileges to a user.
INSERT INTO TABLE_NAME [ (COLUMN1, COLUMN2,...]
VALUES ('VALUE1','VALUE2',...)
Description: Inserts new rows of data into a table.
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 PRIVILEGE1, PRIVILEGE2, ... FROM USER_NAME
Description: Revokes privileges from a user.
ROLLBACK [ TO SAVEPOINT_NAME ]
Description: Undoes a database transaction.
SAVEPOINT SAVEPOINT_NAME
Description: Creates transaction savepoints in which to roll back if necessary.
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 TABLE_NAME
SET COLUMN1 = 'VALUE1',
COLUMN2 = 'VALUE2',...
[ WHERE CONDITIONS ]
Description: Updates existing data in a table.
SELECT *
SELECT COLUMN1, COLUMN2,...
SELECT DISTINCT (COLUMN1)
SELECT COUNT(*)
Description: Defines columns to display as part of query output.
FROM TABLE1, TABLE2, TABLE3,...
Description: Defines tables from which to retrieve data.
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_COLUMN1, GROUP_COLUMN2,...
Description: Divides output into logical groups; a form of sorting operation.
HAVING GROUP_COLUMN1 = 'VALUE1'
AND GROUP_COLUMN2 = 'VALUE2'
...
Description: Places conditions on the GROUP BY
clause; similar to the WHERE
clause.
ORDER BY COLUMN1, COLUMN2,...
ORDER BY 1,2,...
Description: Sorts a query’s results.
18.222.184.200