|
The ORDER BY clause allows you to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements.
The syntax for the ORDER BY clause is:
SELECT columns
FROM tables
WHERE predicates
ORDER BY column ASC/DESC;
The ORDER BY clause sorts the result set based on the columns specified. If the ASC or DESC value is omitted, the system assumed ascending order.
ASC indicates ascending order. (default)
DESC indicates descending order.
Example #1
SELECT supplier_city
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY supplier_city;
This would return all records sorted by the supplier_city field in ascending order.
Example #2
SELECT supplier_city
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC;
This would return all records sorted by the supplier_city field in descending order.
Example #3
You can also sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on.
SELECT supplier_city
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY 1 DESC;
This would return all records sorted by the supplier_city field in descending order, since the supplier_city field is in position #1 in the result set.
Example #4
SELECT supplier_city, supplier_state
FROM supplier
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC, supplier_state ASC;
This would return all records sorted by the supplier_city field in descending order, with a secondary sort by supplier_state in ascending order.
|
Oracle/PLSQL: Primary Keys
|
What is a primary key?
A primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.
Note: In Oracle, a primary key can not contain more than 32 columns.
A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
Using a CREATE TABLE statement
The syntax for creating a primary key using a CREATE TABLE statement is:
CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name PRIMARY KEY (column1, column2, . column_n)
);
For example:
| CREATE TABLE supplier |
| ( |
supplier_id |
numeric(10) |
not null, |
|
supplier_name |
varchar2(50) |
not null, |
|
contact_name |
varchar2(50), |
|
|
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) |
| ); |
In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field.
We could also create a primary key with more than one field as in the example below:
| CREATE TABLE supplier |
| ( |
supplier_id |
numeric(10) |
not null, |
|
supplier_name |
varchar2(50) |
not null, |
|
contact_name |
varchar2(50), |
|
|
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name) |
| ); |
Using an ALTER TABLE statement
The syntax for creating a primary key in an ALTER TABLE statement is:
ALTER TABLE table_name
add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);
For example:
ALTER TABLE supplier
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);
In this example, we've created a primary key on the existing supplier table called supplier_pk. It consists of the field called supplier_id.
We could also create a primary key with more than one field as in the example below:
ALTER TABLE supplier
add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);
Drop a Primary Key
The syntax for dropping a primary key is:
ALTER TABLE table_name
drop CONSTRAINT constraint_name;
For example:
ALTER TABLE supplier
drop CONSTRAINT supplier_pk;
In this example, we're dropping a primary key on the supplier table called supplier_pk.
Disable a Primary Key
The syntax for disabling a primary key is:
ALTER TABLE table_name
disable CONSTRAINT constraint_name;
For example:
ALTER TABLE supplier
disable CONSTRAINT supplier_pk;
In this example, we're disabling a primary key on the supplier table called supplier_pk.
Enable a Primary Key
The syntax for enabling a primary key is:
ALTER TABLE table_name
enable CONSTRAINT constraint_name;
For example:
ALTER TABLE supplier
enable CONSTRAINT supplier_pk;
In this example, we're enabling a primary key on the supplier table called supplier_pk.
|
Oracle/PLSQL: Foreign Keys
|
What is a foreign key?
A foreign key means that values in one table must also appear in another table.
The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.
A foreign key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
Using a CREATE TABLE statement
The syntax for creating a foreign key using a CREATE TABLE statement is:
CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
);
For example:
| CREATE TABLE supplier |
| ( |
supplier_id |
numeric(10) |
not null, |
|
supplier_name |
varchar2(50) |
not null, |
|
contact_name |
varchar2(50), |
|
|
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) |
| ); |
| CREATE TABLE products |
| ( |
product_id |
numeric(10) |
not null, |
|
supplier_id |
numeric(10) |
not null, |
|
CONSTRAINT fk_supplier |
|
FOREIGN KEY (supplier_id) |
|
REFERENCES supplier(supplier_id) |
| ); |
In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.
We could also create a foreign key with more than one field as in the example below:
| CREATE TABLE supplier |
| ( |
supplier_id |
numeric(10) |
not null, |
|
supplier_name |
varchar2(50) |
not null, |
|
contact_name |
varchar2(50), |
|
|
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name) |
| ); |
| CREATE TABLE products |
| ( |
product_id |
numeric(10) |
not null, |
|
supplier_id |
numeric(10) |
not null, |
|
supplier_name |
varchar2(50) |
not null, |
|
CONSTRAINT fk_supplier_comp |
|
FOREIGN KEY (supplier_id, supplier_name) |
|
REFERENCES supplier(supplier_id, supplier_name) |
| ); |
In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.
Using an ALTER TABLE statement
The syntax for creating a foreign key in an ALTER TABLE statement is:
ALTER TABLE table_name
add CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n);
For example:
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id);
In this example, we've created a foreign key called fk_supplier that references the supplier table based on the supplier_id field.
We could also create a foreign key with more than one field as in the example below:
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name);
Oracle/PLSQL: Foreign Keys with cascade delete
|
What is a foreign key?
A foreign key means that values in one table must also appear in another table.
The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.
A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This is called a cascade delete.
A foreign key with a cascade delete can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
Using a CREATE TABLE statement
The syntax for creating a foreign key using a CREATE TABLE statement is:
CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE
);
For example:
| CREATE TABLE supplier |
| ( |
supplier_id |
numeric(10) |
not null, |
|
supplier_name |
varchar2(50) |
not null, |
|
contact_name |
varchar2(50), |
|
|
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) |
| ); |
| CREATE TABLE products |
| ( |
product_id |
numeric(10) |
not null, |
|
supplier_id |
numeric(10) |
not null, |
|
CONSTRAINT fk_supplier |
|
FOREIGN KEY (supplier_id) |
|
REFERENCES supplier(supplier_id) |
|
ON DELETE CASCADE |
| ); |
In this example, we've created a primary key on the supplier table called supplier_pk. It consists of only one field - the supplier_id field. Then we've created a foreign key called fk_supplier on the products table that references the supplier table based on the supplier_id field.
Because of the cascade delete, when a record in the supplier table is deleted, all records in the products table will also be deleted that have the same supplier_id value.
We could also create a foreign key (with a cascade delete) with more than one field as in the example below:
| CREATE TABLE supplier |
| ( |
supplier_id |
numeric(10) |
not null, |
|
supplier_name |
varchar2(50) |
not null, |
|
contact_name |
varchar2(50), |
|
|
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name) |
| ); |
| CREATE TABLE products |
| ( |
product_id |
numeric(10) |
not null, |
|
supplier_id |
numeric(10) |
not null, |
|
supplier_name |
varchar2(50) |
not null, |
|
CONSTRAINT fk_supplier_comp |
|
FOREIGN KEY (supplier_id, supplier_name) |
|
REFERENCES supplier(supplier_id, supplier_name) |
|
ON DELETE CASCADE |
| ); |
In this example, our foreign key called fk_foreign_comp references the supplier table based on two fields - the supplier_id and supplier_name fields.
The cascade delete on the foreign key called fk_foreign_comp causes all corresponding records in the products table to be cascade deleted when a record in the supplier table is deleted, based on supplier_id and supplier_name.
Using an ALTER TABLE statement
The syntax for creating a foreign key in an ALTER TABLE statement is:
ALTER TABLE table_name
add CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE;
For example:
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE;
In this example, we've created a foreign key (with a cascade delete) called fk_supplier that references the supplier table based on the supplier_id field.
We could also create a foreign key (with a cascade delete) with more than one field as in the example below:
ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE CASCADE;
|
|
|
|
|
|