SQL Keywords or Functions

Keyword	Sub-keyword	Format	Explanation
+		SELECT firstname + ', ' + initials AS Student FROM student;	Concanates the values of columns and Strings.
ALTER TABLE	ADD columnname	ALTER TABLE tableName ADD column1 type1, column2 type2, …
ALTER TABLE	ADD FOREIGN KEY	ALTER TABLE employee ADD FOREIGN KEY (dept_num) REFERENCES department (dept_num);
ALTER TABLE	ADD PRIMARY KEY	ALTER TABLE tableName ADD PRIMARY KEY (columnname)
ALTER TABLE	DROP CONSTRAINT	ALTER TABLE employee DROP CONSTRAINT sys_c0011124;	Check the three columns CONSTRAINT_NAME, R_CONSTRAINT_NAME and TABLE_NAME of system table USER_CONSTRAINTS for a list of existing constraints. The R_CONSTRAINT_NAME is the other constraint refered by this constraint, e.g., primary key referred by foreign key. Better way is to always name your constraint so that you needn't look up the USER_CONSTRAINTS table.
ALTER TABLE	MODIFY	ALTER TABLE tableName MODIFY (columnName newtype)ALTER TABLE TableName MODIFY (ColumnName NOT NULL)
ANY/ALL		WHERE course_code = ANY (SELECT Course_Code FROM …) Can be > all, < all, = all, > any, < any, = any, etc.
AS		SELECT COUNT(DISTINCT suburb) AS result FROM student;	In most cases derived columns has the same name as the base columns. If it can not be explicitly decided, you should assign a name using keyword AS, or the system or will automatically assign a name to it.
BETWEEN		SELECT name, age FROM employee WHERE age BETWEEN 40 AND 50	Including the boundary: >= 40 and <= 50.
COMMIT			Inserts, updates and deletes are not made final until commit.
Constraint	CONSTRAINT	CREATE TABLE tableName (column1 type1 column_constraint1,  column2 type2 column_constraint2, …, columnN typeN column_constraintN,table_constraint); CREATE TABLE order_line ( order_no CHAR(10) CONSTRAINT FK1 REFERENCES order(order_no), product_no CHAR(6) CONSTRAINT FK2 REFERENCES product(product_no), QTY NUMBER, CONSTRAINT PK1  PRIMARY KEY (Order_No, Product_No));	Explicitly define the name of a constraint. Otherwise it will be given a system name.
Constraint - Column	CHECK	CREATE TABLE student (name VARCHAR2(20), age NUMBER CONSTRAINT check_age CHECK(Age BETWEEN 16 AND 55), …)	Apply data integrity rules
Constraint - Column	NULL/NOT NULL
Constraint - Column	ON DELETE CASCADE	CREATE TABLE book_author (call_number varchar2(10) CONSTRAINT fk1_book_author REFERENCES book (call_number) ON DELETE CASCADE, author_id varchar2(10) CONSTRAINT fk2_book_author REFERENCES author(author_id), CONSTRAINT pk_book_author PRIMARY KEY (call_number, author_id);	Used only on foreign keys. Delete cascade. The only rule Oracle allows. Default is on delete restrict.  All other policies must be coded yourself using triggers and procedures.
Constraint - Column	PRIMARY KEY	See "ON DELETE CASCADE".
Constraint - Column	REFERENCES	See "ON DELETE CASCADE".	Indicates that this attribute is a foreign key to another primary key
Constraint - Column	UNIQUE
Constraint - Table	CHECK
Constraint - Table	CONSTRAINT
Constraint - Table	DISABLE
Constraint - Table	FOREIGN KEY	CREATE TABLE order (order_no VARCHAR2(10), product_no VARCHAR2(10) CONSTRAINT FK1 REFERENCES product(product_no), qty NUMBER, customer_id VARCHAR2(10), CONSTRAINT FK2 FOREIGN KEY (customer_id)REFERENCES customer(customer_id) ON DELETE CASCADE	Define a foreign key
Constraint - Table	NULL/NOT NULL
Constraint - Table	PRIMARY KEY	See "ON DELETE CASCADE".
Constraint - Table	UNIQUE
COPY TABLE		COPY TABLE tableName (column1=type1, column2=type2, …)INTO filenameCOPY TABLE tableName (column1=type1, column2=type2, …)FROM filename
CREATE	CLUSTER	CREATE CLUSTER cluster1 (call_number, varchar2(10)); -- Later we can say: CREATE TABLE book (call_number varchar2(10), …) CLUSTER cluster1(call_number); CREATE TABLE book_author(call_number varchar2(10), author_name varchar2(30)) CLUSTER cluster1(call_number); -- Before populating tables with cluster, you must create cluster index: CREATE INDEX index_cluster1 ON CLUSTER cluster1;	This way the two tables book and book_author are joined together when they are created. Index must be created on a cluster before tables joined on that cluster is populated.
CREATE	INDEX	CREATE INDEX indexname ON tablename(column1, column2, …);CREATE INDEX indexname ON CLUSTER clustername;
CREATE	SYNONYM	CREATE SYNONYM book FOR username.book;	Create a synnonym named "Book" for username's table "Book".
CREATE	TABLE	CREATE TABLE TableName (column1 type1, column2 type2, …);	Type1, Type2 are SQL types
CREATE	TABLE AS	CREATE TABLE student2 AS (SELECT student_No, surname FROM student);
CREATE	UNIQUE INDEX	CREATE UNIQUE INDEX indexname ON tablename(column1, column2, …);
CREATE	VIEW	CREATE VIEW student_name (student_no, name) AS (SELECT student_id, firstname || ' ' || lastname FROM student);	A view is only saved in the system as its query. Only at the point when a view is used, the query is excuted to get the temporary table. Therefore in many cases a view is not updatable.
DELETE FROM		DELETE FROM student WHERE student_id = '12345678';
DESCRIBE		DESCRIBE tablename;	Oracle command. List all the column names and its data types of a table stored as metadata.
DROP	CLUSTER	DROP CLUSTER cluster1;
DROP	INDEX	DROP INDEX index1;
DROP	TABLE	DROP TABLE tablename	Delete the whole table
Functions	AVG( )	SELECT AVG(year_mark) AS result FROM student WHERE suburb = 'ringwood' SELECT Name FROM Pupil WHERE Year_Mark > (SELECT AVG(Year_Mark) FROM Pupil)	Calculate the average year_mark of all students from Ringwood. Select all students whose year_mark is greater than average.
Functions	COUNT	SELECT COUNT(*) FROM student WHERE suburb = 'rinwood' SELECT COUNT(DISTINCT suburb) AS result FROM student; SELECT Country, COUNT(CustomerId) as NumberOfCustomers FROM CustomersGROUP BY Country SELECT subject.subject_id, subject_name FROM subject WHERE subject.subject_id IN (     SELECT book_subject.subject_id     FROM book_subject     GROUP BY book_subject.subject_id     HAVING COUNT(*) =     (         SELECT COUNT(book.call_number)         FROM book     ) );	Number of entries in a column. So in ( ) it can be * or a column name. By default, all values will be counted. By DISTINCT, only different values will be count. Count how many customers are there in each country. Print the subjects into which all books has been classified
Functions	LEN( )	select CompanyName, len(CompanyName) as CompanyNameLength from Customers	Length of a column
Functions	MAX( ) MIN( )	SELECT MAX(year_mark) FROM students WHERE suburb = 'ringwood'	The Maximum or Minimum value of a column
Functions	SUM( )	SELECT CustomerId, SUM(TotalPrice) as CustomerTotal FROM OrdersGROUP BY CustomerId
GRANT		GRANT SELECT, UPDATE ON book TO username;	Grant the right to select and update table "Book" to "username".
GROUP BY	HAVING	SELECT product_no, SUM(Qty) AS order_qty FROM order_line GROUP BY product_no HAVING COUNT(*) < 20
GROUP BY		SELECT product_no, SUM(qty) AS order_qty FROM order_line  GROUP BY product_no SELECT suburb, COUNT(*) AS no_students, AVG(year_mark) as average_mark FROM student  GROUP BY suburb SELECT first_name, last_name, total_books FROM (SELECT author_id, COUNT(*) as total_books FROM author_isbn GROUP BY author_id), authors WHERE author_isbn.author_id = authors.author_id SELECT product_no, SUM(Qty) AS order_qty FROM order_line GROUP BY product_noHAVING COUNT(*) < 20	Entries in the group-by column with the same values are treated as in a group. Entries in other selected columns which corresponds to the grouped entries must also have one single value, so that when the group-by column is "shrinked" no values in other selected columns will be lost. All the set functions work with the group. If the primary key is a composite key say (Order_No, Product_No), just say GROUP BY Order_No, Product_No HAVING is a WHERE clause for GROUP BY. Show the total ordered quantities of all products which appear on less than 20 orders.
IN		SELECT name, suburb FROM employee WHERE suburb IN ("murrumbeena", "caufield", …) SELECT name, suburb FROM employee WHERE suburb IN (SELECT suburb FROM suburbs WHERE population > 1000);	IN can also be followed by a subquery
INSERT INTO		INSERT INTO tablename (column1, column2, …)VALUES(value1, value2, …)INSERT INTO table1 (column1, column2, column3) SELECT column1, column2, column3 FROM table2	(Column1, Column2, …) are needed only if you do not insert values into all columns. Insert multiple rows into a table using a SELECT statement.
LIKE		SELECT Column1, Column2, … FROM Table1 Alias1, Table2 Alias2, … WHERE Column2 LIKE ‘Frank%’ OR Column2 LIKE ‘Frank_’or could be ‘Frank[012]’,  ‘Frank[0-2]’,  ‘Frank[^3-9]’	"%": stands for any number of arbitrary characters "_": for one arbitrary character. Frank[012]: could be Frank0, Frank1 or Frank2. Frank[0-2]: ditto. Frank[^3-9]: could not be Frank3, Frank4, …, Frank9.
ORDER BY		SELECT column1, column2, … FROM table, table2, … WHERE … ORDER BY column2 ASCor could beORDER BY column2 DESC	ASC: assending order DESC: desending order
Outer join (+)		SELECT student_no, name, course_no FROM student, enrolment WHERE sutent.student_no = enroment.student_no (+);
ROLLBACK			Undo the changes until last commit.
SELECT		SELECT TABLE_NAME FROM USER_TABLES;	Oracle command. USER_TABLES is a system-generated table containing all metadata.
SELECT	CAST	SELECT ProductName, Quantity, UnitPrice, Discount, CAST((UnitPrice * Quantity) * (1 - Discount) AS MONEY) AS total_price FROM  dbo.[Order Details Extended]	Do a calculation with columns and put the result in the new column.
SELECT	FULL OUTER JOIN	SELECT * FROM test1  FULL OUTER JOIN test2ON test1.c1 = test2.c1	The AND of LEFT OUTER JOIN and RIGHT OUTER JOIN.
SELECT	INNER JOIN	select * from customers INNER JOIN orderson customers.customerId = orders.customerId	Result set contains all the combinations of the left-table rows and right-table rows that has the same CustomerId.
SELECT	LEFT OUTER JOIN	SELECT * FROM test1  LEFT OUTER JOIN test2ON test1.c1 = test2.c1	Result set contains all INNER JOIN results plus all rows in the LEFT table that has no matching rows in the right table, with right-table columns being null.
SELECT	RIGHT OUTER JOIN	SELECT * FROM test1  RIGHT OUTER JOIN test2ON test1.c1 = test2.c1	Result set contains all INNER JOIN results plus all rows in the RIGHT table that has no matching rows in the left table, with left-table columns being null.
SELECT	INTO	SELECT * INTO BadCustomers FROM Customers WHERE balance < -10000
SET	AUTOCOMMIT ON/OFF	SET AUTHCOMMIT ON/OFF;	Turn on/off the auto committing function. When AUTOCOMMIT is on, every operation is automatically committed right after it is finished.
TRUNCATE		TRUNCATE tablename;	Remove all rows of a table but keeps the table. May not be rolled back.
UNION		SELECT family_name, suburb FROM teacher UNION SELECT surname, suburb FROM student;
UPDATE	SET	UPDATE tablenameSET columnName = value WHERE …UPDATE table1 INNER JOIN table2 ON table1.c1 = table2.c1 SET table1.c3 = 'blar' WHERE table2.c7 = 'boo'	Updating through a joined table.
WHERE		SELECT column1, column2, … FROM table1 alias1, table2 alias2, … WHERE … AND … AND …
by:http://progtutorials.tripod.com