| 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 |
|