Table of contents

1 - Basics

1.1 - Quotes

• The single quote is used to tell the SQL statement that the value a not numeral value is.
• The double quote can only be used for a column alias. It tell SQL to use everything between the quotes as alias, and keep it case sensitive.

1.2 - Readable query's

   SELECT        alias1.column1
   ,             alias1.column2 AS "Column alias"
   ,             alias2.column1
   FROM          table1 alias1
   ,             table2 alias2
   WHERE         alias1.column1 = 'value'
   AND           alias2.column1 = (SELECT        alias3.column2
                                   FROM          table3 alias3
                                   WHERE         alias3.column1 < alias1.column3
                                  )
-- AND           alias1.column2 is not null
   ;

• If you have 20 field, and write them all on one line, it will be a very long line and hard to read.
• To change a field you can simply modify a line, when adding an alias, simply put it behind the field.
• When you want to skip a field, just comment the line out with -- (2x minus sign).
• When an error occurred, SQL often gives a line number. The less on one line, the faster the error can be found.

2 - Datatypes

2.1 - VARCHAR2(size)

• Can store all possible characters.
• Length is depended on the data with a specified maximum.
• Default length is 1.
• Maximum size is 4000.

2.2 - CHAR(size)

• Can store all possible characters.
• Space reserved by the database is the specified size.
• Default length is 1.
• Maximum size is 4000.

2.3 - NUMBER(p,s)

• Can store only numeral values.
• Space reserved by the database is depending on the data.
• Default length is unknown.
• Maximum size is 38 digits.
• Scale can range from -84 to 127.

2.4 - DATE

• Can store only date and time.
• Space reserved by the database is fixed.
• Only one format can be entered.
• Date can range between January 1, 4712 B.C. and December 31, 9999 A.D.

2.5 - LONG

• Can store all possible characters.
• Length is depended on the data.
• No size can be specified.
• Maximum size is 2Gbytes.

2.6 - CLOB

• Can store all possible characters.
• Length is depended on the data.
• No size can be specified.
• Maximum size is 4Gbytes.

2.7 - RAW(size)

• Can store binary data.
• Length is depended on the data with a specified size.
• No default size, size must be specified.
• Maximum size is 2000bytes.

2.8 - LONGRAW

• Can store binary data.
• Length is depended on the data.
• No size can be specified.
• Maximum size is 2Gbytes.

2.9 - BLOB

• Can store binary data.
• Length is depended on the data.
• No size can be specified.
• Maximum size is 4Gbytes.

2.10 - BFILE

• Can store a link to an external binary datafile.
• Stored information in the database is only the link.
• No size can be specified.
• Maximum size of the external datafile is 4Gbytes.

3 - Formatting

3.1 - Number format elements

3.2 - Date format elements

4 - Select statement

4.1 - Basic select

SELECT        [DISTINCT]{*, column[, column]}
FROM          table;

4.2 - Where statement

4.3 - Order by statement

SELECT        {*, column[, column]}
FROM          table
ORDER BY      {column, expr} [ASC|DESC];

4.4 - Group by statement

SELECT        {*, column[, column]}
FROM          table
GROUP BY      expr
[HAVING       group condition];

4.5 - Connected by

SELECT                {*, column [[AS] alias],[LEVEL],…}
FROM                  table
CONNECTED BY PRIOR    column1 = column2
[START WITH           condition(s)];

4.6 - Aliases

SELECT        column [AS] alias[, ...]
FROM          table alias[, ...];

SELECT        a.NAME firstname, b.NAME lastname
FROM          FIRSTTABLE a, NEXTABLE b
ORDER BY      lastname;

5 - String manipulation

5.1 - Lower

LOWER (column|expr)

5.2 - Upper

UPPER (column|expr)

5.3 - Initcap

INITCAP (column|expr)

5.4 - Concat

SELECT column || column, 'text' || column

5.5 - Substr

SUBSTR (column|expr, n[, m])

5.6 - Length

LENGTH (column|expr)

5.7 - Instr

INSTR (column|expr, m)

5.8 - Lpad/Rpad

LPAD (column|expr, n[, m])

5.9 - To_char

TO_CHAR (number|date[,fmt])

5.10 - To_number

TO_NUMBER (string)

5.11 - To_date

TO_DATE (string[, fmt])

5.12 - Nvl

NVL (column|expr, m)

5.13 - Decode

DECODE (column|expr, search1, result1[,...][,default])

5.14 - Round

ROUND (column|expr|date,n|fmt)

5.15 - Trunc

TRUNC (column|expr|date,n|fmt)

5.16 - Mod

MOD (m,n)

5.17 - Months_between

MONTHS_BETWEEN (date1, date2)

5.18 - Add_months

ADD_MONTHS (date,n)

5.19 - Next_day

NEXT_DAY (date,'day')

5.20 - Last_day

LAST_DAY (date)

6 - Where clause

6.1 - Boolean operators

WHERE field1 = 123
WHERE field1 = field2
WHERE 123 = field1

6.2 - BETWEEN

WHERE field1 BETWEEN 100 AND 200

6.3 - IN

ERE field1 IN (100, 120, 300)

6.4 - LIKE

 WHERE field1 LIKE '_A\_%'

6.5 - IS NULL

WHERE field1 IS NULL

6.6 - Logical operators

WHERE field1 = 1
AND field2 = 100
OR field2 = 200

WHERE NOT field1 IN (100, 200)
OR field1 NOT BETWEEN 100 AND 200
OR field1 NOT LIKE '%A%'
OR field1 IS NOT NULL

7 - Group functions

7.1 - AVG

SELECT AVG([DISTINCT|ALL] expr)

7.2 - COUNT

SELECT COUNT(*|[DISTINCT|ALL] expr)

7.3 - MAX

SELECT MAX([DISTINCT|ALL] expr)

7.4 - MIN

SELECT MIN([DISTINCT|ALL] expr)

7.5 - STDDEV

SELECT STDDEV([DISTINCT|ALL] expr) 

7.6 - SUM

SELECT SUM([DISTINCT|ALL] expr)

7.7 - VARIANCE

SELECT VARIANCE([DISTINCT|ALL] expr)

7.8 - GROUP BY

SELECT field1, AVG(field2) FROM table GROUP BY field1; 

7.9 - HAVING

SELECT        field1, AVG(field2)
FROM          table
WHERE         field1 = 10
GROUP BY      field1
HAVING        field2 IN (100, 200)
ORDER BY      field1;

8 - Sub queries

8.1 - Single row subquery

SELECT        field
FROM          table
WHERE         field = (SELECT        MIN(field2)
                       FROM          TABLE2 );

8.2 - Multiple row subquery

SELECT        field
FROM          table
WHERE         field IN (SELECT        field
                        FROM          table );

SELECT        field
FROM          table
WHERE         field < ANY (SELECT        field
                              FROM          table ); 

SELECT        field
FROM          table
WHERE         field < ALL (SELECT        field
                              FROM          table ); 

9 - Data manipulation

9.1 - INSERT

INSERT INTO   table [(column[, column...])]
VALUES        (value[, value...]);

INSERT INTO   table (column1, column3)
VALUES        (100, 'text'); 

INSERT INTO   table
VALUES        (100, NULL, 'text'); 

9.2 - INSERT based on subquery

INSERT INTO   table (column1, column2)
        SELECT        name, address
        FROM          customers
        WHERE         credit > 1000;

9.3 - UPDATE

UPDATE table SET column = value [, column = value...] [WHERE condition];

9.4 - DELETE

DELETE [FROM] table [WHERE condition]; 

10 - Database transactions

10.1 - COMMIT

COMMIT;

10.2 - SAVEPOINT

SAVEPOINT name;

10.3 - ROLLBACK

ROLLBACK [TO name]

11 - Managing tables

11.1 - CREATE

CREATE TABLE    [schema.]table
                (column datatype [DEFAULT expr]
                [column_contraint]
                ,...
                [table_contraint]);

11.2 - CREATE base on subquery

CREATE TABLE    [schema.]table
                [column[,column...]]
AS              subquery;

CREATE TABLE    customers
AS
        SELECT          name, address
        FROM            clients
        WHERE           1 = 2

11.3 - ALTER

• Add a new column or constraint.
• Modify an existing column or constraint.
• Define a default value for a column.

ALTER TABLE    table
ADD|MODIFY     (column datatype [DEFAULT expr],...);

11.4 - DROP

DROP TABLE table;

11.5 - TRUNCATE

TRUNCATE TABLE table;

11.6 - RENAME

RENAME old_name TO new_name

11.7 - DESCRIBE

DESCRIBE table

12 - Constraints

12.1 - NOT NULL

CREATE ...(column varchar2(10) CONSTRAINT cons_name NOT NULL,

12.2 - UNIQUE key

CREATE ...(column varchar2(10) CONSTRAINT cons_name UNIQUE,
CONSTRAINT cons_name UNIQUE(column));

12.3 - PRIMARY key

CREATE ...(column varchar2(10) CONSTRAINT cons_name PRIMARY KEY,
CONSTRAINT cons_name PRIMARY KEY(column));

12.4 - FOREIGN key

CREATE ...(column varchar2(10)
        CONSTRAINT name_fk FOREIGN KEY REFERENCES table (column),
CONSTRAINT name_fk FOREIGN KEY (column) REFERENCES table (column));

12.5 - CHECK

CREATE ...(column varchar2(10)
        CONSTRAINT cols_name CHECK (column BETWEEN 1 AND 3),

12.6 - Create a table with constraints

CREATE TABLE    [schema].table
                (column datatyep [DEFAULT expr]
                [column_constraint]
                ,...
                [,table_constraint]);

12.7 - Add a constraint

ALTER TABLE     table
ADD             CONSTRAINT table_constaint;

ALTER TABLE     table
MODIFY          column datatype CONSTRAINT column-constraint;

12.8 - Drop a constraint

ALTER TABLE     table
DROP PRIMARY KEY|UNIQUE(column)|CONSTRAINT cons_name [CASCADE];

12.9 - Disable a constraint

ALTER TABLE     table
DISABLE CONSTRAINT cons_name [CASCADE];

12.10 - Enable a constraint

ALTER TABLE     table
ENABLE CONSTRAINT cons_name;

12.11 - View a constraint

SELECT        a.table_name
,             a.column_name
,             b.constraint_name
,             b.constraint_type
,             b.search_condition
FROM          user_cons_columns a
,             user_constraints b
WHERE         a.constraint_name = b.constraint_name
AND           a.table_name IN ('TABLE1', 'TABLE2')
ORDER BY      a.table_name
,             a.column_name;

13 - Sequences

13.1 - Create a sequence

CREATE SEQUENCE schema.sequencename
                START WITH 10
                INCREMENT BY 5
                MAXVALUE 500
                CYCLE;

13.2 - Alter a sequence

ALTER SEQUENCE scheme.sequencename
      INCREMENT BY 3;

13.3 - Drop a sequence

DROP SEQUENCE scheme.sequencename;

13.4 - Using sequences

SELECT        schema.sequencename.CURRVAL
,             schema.sequencename.NEXTVAL
FROM          DUAL;

INSERT INTO   tablename
VALUES        ((        SELECT  sequence.NEXTVAL
                        FROM    DUAL)
,             'column2');

14 - Variables

14.1 - & substitution variable

SELECT        field1
,             field2
,             &variable1
FROM          table
WHERE         field1 = &variable2
ORDER BY      &variable1;

Enter value for variable1: field3
Enter value for variable2: 'text'
Enter value for variable1: field1

14.2 - && substitution variable

SELECT        field1
,             &&variable1
FROM          table
WHERE         &&variable1 = 123;

Enter value for variable1: field2

14.3 - ACCEPT

ACCEPT variablename [datatype] [FORMAT format] [PROMPT text] [HIDE]

14.4 - DEFINE

DEFINE variablename [= value]

14.5 - UNDEFINE

UNDEFINE variablename

15 - System variables

15.1 - _EDITOR

DEFINE _EDITOR=vi

16 - Customizing output

16.1 - COLUMN

COL[UMN]  [{COLUMN|ALIAS} [OPTION]]

COLUMN FIELD1 FORMAT $009999
     FIELD1 FIELD2  FI FIELD4
----------- ------- -- ----------------------------
    $001234 TEXT    01 Some text here
    $001236 TEXT    04 Another text here

16.2 - TTITLE

TTI[TLE] [text|OFF|ON]

16.3 - BTITLE

BTI[TLE] [text|OFF|ON]

16.4 - BREAK

BREAK ON {column|alias|row} [SKIP n|DUP|PAGE] ON ... [ON REPORT]

16.5 - FEEDBACK

16.6 - LINESIZE

SET LIN[ESIZE] n

16.7 - PAGESIZE

SET PAGES[IZE] n

16.8 - PAUSE

SET PAU[SE] {OFF|ON|text}

17 - Customizing output

17.1 - Anonymous

[DECLARE]

BEGIN
  -- statements

[EXCEPTION]

END;

17.2 - Procedure

PROCEDURE name
IS

BEGIN
  -- statements

[EXCEPTION]

END;

17.3 - Function

FUNCTION name
RETURN datatype
IS
BEGIN
  -- statements
  RETURN value;

[EXCEPTION]

END;

18 - System management examples

18.1 - Find running statements

SELECT    s.username
,         s.status
,         a.sql_text
FROM      v$session s
,         v$sqlarea a
WHERE     s.username IS NOT NULL
            -- Ignore the Oracle background processes
AND       s.audsid <> userenv('SESSIONID')
            -- Ignore the current session
AND       s.sql_address = a.address
AND       s.sql_hash_value = a.hash_value
ORDER BY  s.status
            -- to have active sessions first