ALTER TABLE <TABLE NAME> ADD|DROP|MODIFY (COLUMN SPECIFICATION[S]...see Create Table);
--allows you to add or delete a column or columns from a table,
or change the specification (data type, etc.) on an existing column;
this statement is also used to change the physical specifications of a table
(how a table is stored, etc.),
but these definitions are DBMS-specific, so read the documentation.
Also, these physical specifications are used with the Create Table statement,
when a table is first created.
In addition, only one option SQL Tutorial can be performed per Alter Table statement
--either add, drop, OR modify in a single statement.
--makes changes made to some database systems permanent
(since the last COMMIT; known as a transaction)
CREATE [UNIQUE] INDEX <INDEX NAME> ON <TABLE NAME> (<COLUMN LIST>);
--UNIQUE is optional; within brackets.
CREATE TABLE <TABLE NAME>
(<COLUMN NAME> <DATA TYPE> [(<SIZE>)] <COLUMN CONSTRAINT>, ...other columns);
(also valid with ALTER TABLE)
--where SIZE is only used on certain data types (see above),
and constraints include the following possibilities
(automatically enforced by the DBMS; failure causes an error to be generated):
1. NULL or NOT NULL (see above)
2. UNIQUE enforces that no two rows will have the same value for this column
3. PRIMARY KEY tells the database that this column is the primary key column
(only used if the key is a one column key,
otherwise a PRIMARY KEY (column, column, ...) statement appears
after the last column definition.
4. CHECK allows a condition to be checked for
when data in that column is updated or inserted;
for example, CHECK (PRICE 0) causes the system
to check that the Price column is greater than zero before accepting the value
...sometimes implemented as the CONSTRAINT statement.
5. DEFAULT inserts the default value into the database
if a row is inserted without that column's data being inserted;
for example, BENEFITS INTEGER DEFAULT = 10000
6. FOREIGN KEY works the same as Primary Key,
but is followed by: REFERENCES <TABLE NAME> (<COLUMN NAME>),
which refers to the referential primary key.
CREATE VIEW <TABLE NAME> AS <QUERY>;
DELETE FROM <TABLE NAME> WHERE <CONDITION>;
INSERT INTO <TABLE NAME> [(<COLUMN LIST>)] VALUES (<VALUE LIST>);
--Takes back any changes to the database that you have made,
back to the last time you gave a Commit command
...beware! Some software uses automatic committing on systems
that use the transaction features,
so the Rollback command may not work.
SELECT [DISTINCT|ALL] <LIST OF COLUMNS, FUNCTIONS, CONSTANTS, ETC.>
FROM <LIST OF TABLES OR VIEWS>
[GROUP BY <GROUPING COLUMN(S)>]
[ORDER BY <ORDERING COLUMN(S)> [ASC|DESC]]
--where ASC|DESC allows the ordering to be done in ASCending or DESCending order
UPDATE <TABLE NAME> SET <COLUMN NAME> = <VALUE> [WHERE <CONDITION>];
--if the Where clause is left out, all rows will be updated according to the Set statement.
<% Call DisplayLastModified("ANSI_SQL_summary.asp") %>