SQL Statement Syntax
To help you find the syntax you need when you need it, this tear card lists the syntax for the most frequently used SQL
Operation. When reading statement syntax, remember the following:
The | symbol is used to indicate one of several option, so NULL | NOT NULL means specify either NULL or NOT NULL.
Keywords or clauses contained within square parentheses [like this] are optional.
The syntax listed below will work with almost all DBMS’s. You are advised to consult your own DBMS documentation for
details of implementing specification syntactical changes.
CREATE DATABASE
CREATE DATABASE is used to create database.
CREATE DATABASE <database_name>
ON [PRIMARY]
[ <filespec> [, . . . . n] ]
[ , <filegroup> [, . . . . n] ]
[ LOG ON { <filespec> [, . . . . n] } ]
[ FOR LOAD | FOR ATTACH ]
<filespec> : : =
( [ NAME = logical_file_name, ]
FILENAME = ‘os_file_name’
[ , SIZE = size ]
[ , MAXSIZE = { maxsize | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] ) [ , . . . . n ]
<filegroup> : : =
FILEGROUP filegroup_name <filespec> [, . . . . n ]
CREATE TABLE
CREATE TABLE is user to create new database tables.
CREATE TABLE tablename
(
Column <datatype_definition> [NULL | NOT NULL] [CONSTRAINTS],)
Column <datatype_definition> [NULL | NOT NULL] [CONSTRAINTS],)
....
);
CREATE INDEX
CREATE INDEX is used to create an index on one or more columns.
CREATE INDEX indexname
ON tablename (column, . . . . );
CREATE VIEW
CREATE VIEW is used to create a new view of one or more tables.
CREATE VIEW viewname AS
SELECT columns, . . .
FROM table, . . .
[WHERE . . . ]
[GROUP BY . . . ]
[HAVING . . . ];
INSERT
INSERT adds a single row to table.
INSERT INTO tablename [ (columns, . . . ) ]
VALUES (values, . . . );
INSERT SELECT
INSERT SELECT insert the results of a SELECT into table.
INSERT INTO tablename [ (columns, . . . ) ]
SELECT column, . . .
FROM tablename, . . .
[ WHERE . . . ];
ROLLBACK
ROLLBACK is used to undo a transaction block.
ROLLBACK [ TO savepointname ]; Or ROLLBACK TRANSACTION;
SELECT
SELECT is used to retrieve data from one or more tables (or views).
SELECT columnname, . . .
FROM tablename, . . .
[WHERE . . . ]
[GROUP BY . . . ]
[HAVING . . . ];
[ ORDER BY . . . ];
UPDATE
UPDATE Updates one or more row in a table.
UPDATE tablename
SET columnname = value, . . . .
[WHERE . . . ];
ALTER TABLE
ALTER TABLE is used to update the schema of an existing table.
ALTER TABLE tablename
{
ADD|DROP column datatype [NULL | NOT NULL] [CONSTRAINTS],
ADD|DROP column datatype [NULL | NOT NULL] [CONSTRAINTS],
. ....
};
COMMIT
COMMET is used to write a transaction to the database.
COMMIT [TRANSACTION];
DELETE
DELETE deletes one or more rows from table.
DELETE FROM tablename
[WHERE . . .];
DROP
DROP permanently remove database object (tables, views, indexes, and so worth)
DROP INDEX|TABLE|VIEW indexname | tablename | viewname;
