Make your own free website on Tripod.com
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 filename

COPY 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 Customers
GROUP 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 Orders
GROUP 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_no
HAVING 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 ASC

or could be

ORDER 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 test2
ON test1.c1 = test2.c1
The AND of LEFT OUTER JOIN and RIGHT OUTER JOIN.
SELECT INNER JOIN select * from
customers INNER JOIN orders
on 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 test2
ON 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 test2
ON 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 tablename
SET 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 …