SQL statement of syntax or Basic SQL

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
Posted by
Labels: