SQL

SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is a data retrieval and manipulation language used to communicate with the Oracle database. Some people (incorrectly) pronounce SQL as "sequel".

SQL was developed by IBM in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard.

Users looking for a GUI tools to help them with SQL may install SQL Developer or TOAD.

Queries

Queries are used to select or extract data from a database.

SELECT

Example SELECT statements:

SELECT * FROM scott.emp;

SELECT * FROM scott.emp WHERE emp_no=12341 AND dept_no=20;

SELECT *
  FROM tableName
 WHERE col1 = 'value1'
   AND col2 = 'value2';

SELECT col1, col5
  FROM tableName
 WHERE col3 = 'value3'
 ORDER BY col 5;

Data Manipulation Statements

INSERT

Insert a single row into a table:

INSERT INTO table_name VALUES (col1, col2, ...);

Insert a single row into a table with user-defined column order:

INSERT INTO table_name (col3, col1, col2, ...) VALUES (value3, value1, value2, ...);

Insert rows from one table into another:

INSERT INTO table_name(col1, col2, ...) (SELECT 'value1', 'value2', ... from table_name);

Insert multiple rows into a table using INSERT ALL:

INSERT ALL
   INTO table_name(col1, col2, ...) VALUES (value1_1, value2_1, ...)
   INTO table_name(col1, col2, ...) VALUES (value1_2, value2_2, ...)
   INTO table_name(col1, col2, ...) VALUES (value1_3, value2_3, ...)
SELECT * FROM dual;

UPDATE

Update the entire column of that table (all rows):

UPDATE customer SET state='CA';

Update the specific record of the table:

UPDATE customer SET name='Joe' WHERE customer_id=10;

Updates the column invoice as paid when paid column has more than zero:

UPDATE movies SET invoice='paid' WHERE paid >= 0;

DELETE

Delete all rows from a table:

DELETE FROM tab1;

Conditionally delete rows:

DELETE 
  FROM tab1 
 WHERE col1 = '123';

Data Control Language

GRANT

Grant privileges to users or roles:

GRANT create session TO scott;

REVOKE

Revoke privileges from users or roles:

REVOKE unlimited tablespace FROM scott;

Data Definition Statements

Tables

Create a table

The syntax to create a table is:

CREATE TABLE [table name] (
      columnname datatype, 
      ...
);

For example:

CREATE TABLE customers (
  col1 NUMBER,
  col2 VARCHAR2(20)
);

Rename a table

The syntax to rename a table is:

ALTER TABLE [table name]
      RENAME TO [new table name];

For example:

ALTER TABLE customers
      RENAME TO customer;

Add a column

The syntax to add a column is:

ALTER TABLE [table name]
      ADD ( [column name] [datatype], ... );

For example:

ALTER TABLE employee
      ADD (id int);

Modify a column

The syntax to modify a column is:

ALTER TABLE [table name]
      MODIFY ( [column] [new data type] );

For example:

ALTER TABLE employee
      MODIFY( sickHours float );

Drop a column

The syntax to drop a column is:

ALTER TABLE [table name]
      DROP COLUMN [column name];

For example:

ALTER TABLE employee
      DROP COLUMN vacationPay;

Indexes

Create an index

The syntax for creating an index is:

CREATE INDEX index_name
    ON table_name (col1, col2, ...); 

Example of how to add a primary key constraint to a table:

ALTER TABLE table_name ADD CONSTRAINT constraint_name
      PRIMARY KEY('eno');

Rename an index

The syntax for renaming an index is:

ALTER INDEX index_name
    RENAME TO new_index_name;

For example:

ALTER INDEX customer_idx
    RENAME TO new_customer_idx;

Drop an index

The syntax for dropping an index is:

DROP INDEX index_name;

For example:

DROP INDEX customer_idx;

Sequences

A sequence is an object that can generate numeric value in sequence. Sequences are typically used to generate values for primary keys.

Create a sequence

The syntax to create a sequence is:

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

For example:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

Generating sequence values

Use the NEXTVAL function to generate the next sequence value. CURRVAL will return the last generated value. Example:

SELECT supplier_seq.NEXTVAL FROM dual;

Alter a sequence

Increment a sequence by a certain amount:

ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>;
ALTER SEQUENCE seq_inc_by_ten  INCREMENT BY 10;

Change the maximum value of a sequence:

ALTER SEQUENCE <sequence_name> MAXVALUE <integer>;
ALTER SEQUENCE seq_maxval  MAXVALUE  10;

Set the sequence to cycle or not cycle:

ALTER SEQUENCE <sequence_name> <CYCLE | NOCYCLE>;
ALTER SEQUENCE seq_cycle NOCYCLE;

Configure the sequence to cache a value:

ALTER SEQUENCE <sequence_name> CACHE <integer> | NOCACHE;
ALTER SEQUENCE seq_cache NOCACHE;

Set whether or not the values are to be returned in order

ALTER SEQUENCE <sequence_name> <ORDER | NOORDER>;
ALTER SEQUENCE seq_order NOORDER;

Views

A view is a named SQL query or precompiled query which is stored in the database. Views do not contain any data - it is just a stored query in the database that can be executed when called. Example:

CREATE VIEW dept20 
     AS SELECT ename, sal*12 annual_salary 
          FROM emp 
         WHERE deptno = 20;

Materialized Views

A materialized view is like a view in that it represents data that is contained in other database tables and views. However, unlike a view, a materialized view contains actual data.

Materialized View Logs

DML performed on a table can be stored in a materialized view log. So, next time a materialized view is refreshed, it doesn't have to read the entire table to get the changes performed.

Synonyms

A synonym is a database object used for assign an alias to an object. This is very usefull for Schema Transparency. For example:

CREATE SYNONYM emp FOR scott.emp;

So, instead of selecting from "scott.emp", you can now select from "emp".

Also see

• SQL FAQ, frequently asked questions
• SQL Code examples
• SQL Best Practices, a presentation by Stéphane Faroult
• Hint - Query hints

External links

• sqlzoo.net, an interactive SQL tutorial

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#