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.
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)
);
ALTER TABLE table_name
add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);
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.
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)
);
ALTER TABLE table_name
disable CONSTRAINT constraint_name;
ALTER TABLE table_name
enable CONSTRAINT constraint_name;
What is a unique constraint?
A
unique constraint is a single field or
combination of fields that uniquely defines a record. Some of the fields
can contain null values as long as the combination of values is unique.
CREATE TABLE table_name
(
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT constraint_name UNIQUE (column1, column2, . column_n)
);
ALTER TABLE table_name
add CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);
Note:
In Oracle, a unique constraint can not contain more than 32 columns.
A unique constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.
What is an Index?
An
index is a performance-tuning method of
allowing faster retrieval of records. An index creates an entry for each
value that appears in the indexed columns. By default, Oracle creates
B-tree indexes.
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, ... column_n)
[ COMPUTE STATISTICS ];
Rename Index
ALTER INDEX index_name
RENAME TO new_index_name;
Rebuild :If you forgot to collect statistics on the index when you first created
it or you want to update the statistics, you can always use the ALTER
INDEX command to collect statistics at a later date.
ALTER INDEX index_name
REBUILD COMPUTE STATISTICS;
DROP INDEX index_name;
SQL: SELECT Statement
The SQL SELECT statement allows you to retrieve records from one or more tables in your SQL database.
syntax:
SELECT columns
FROM tables
WHERE predicates;
SQL: INSERT Statement
The SQL INSERT statement allows you to insert a single record or multiple records into a table.
Steps of insert data into table using both the insert commands
syntax:
INSERT INTO table
(column-1, column-2, ... column-n)
VALUES
(value-1, value-2, ... value-n);
insert multiple rows of explicit data in one SQL command
INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;
INSERT INTO suppliers (supplier_id, supplier_name)
SELECT ss.supplier_id, ss.supplier_name FROM xx_suppliers_stg ss;
SQL: UPDATE Statement
The
SQL UPDATE statement allows you to update a single record or multiple records in a table.
UPDATE table
SET column = expression
WHERE predicates;
UPDATE suppliers
SET supplier_name = (SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.name
FROM customers
WHERE customers.customer_id = suppliers.supplier_id);
SQL: DELETE Statement
The SQL DELETE statement allows you to delete a single record or multiple records from a table.
DELETE FROM table
WHERE predicates;
DELETE FROM TableA
WHERE NOT EXISTS
( select *
from TableB
where TableA.field1 = TableB.fieldx
and TableA.field2 = TableB.fieldz );
SQL: ALTER Statement
ALTER PACKAGE [schema.]package_name COMPILE [DEBUG PACKAGE|SPECIFICATION|BODY];
ALTER PACKAGE APPS.AD_ZD_SEED COMPILE BODY
