SQL Quick Reference

From SquadLimberWiki

Contents 1 Indexes 2 Finding NULL values 3 Using SQL Operators ( '=' '>' '<' '<=' '>=' '<>' ) 4 Using SQL Regex - the LIKE command 5 Using SQL aggregate functions - AVG, COUNT, MAX, MIN, SUM 6 Using GROUP BY with aggregate functions 7 Using HAVING, the WHERE for aggregate functions 8 Ordering Results 9 Finding unique values 10 Simple Updates 11 Simple Inserts 12 Simple Deletes 13 Emptying a table 14 Advanced Select Statements 15 Viewing Blobs as Text 16 Concatenating Strings (Oracle) 17 Select-ing particular row numbers (Oracle) 18 Creating views 19 Show Column Names (Oracle) 20 Show Tables (Oracle) 21 Oracle Built-ins Reference 22 Prepared Statements 23 Cursors 24 Joins - Inner, Outer, Left, Right 25 TODO: Constraints 26 TODO: DDL

Indexes

CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, ...)
[ COMPUTE STATISTICS ];

ALTER INDEX index_name
RENAME TO new_index_name;

DROP INDEX index_name;

Finding NULL values

SELECT * from TABLE where COLUMN IS NULL;

Using SQL Operators ( '=' '>' '<' '<=' '>=' '<>' )

SELECT * FROM monkeys WHERE bananas > '3';

SELECT * FROM monkeys WHERE name <> 'Bubbles';

Using SQL Regex - the LIKE command

Wildcard (%): Value ends with:

SELECT * FROM monkeys WHERE type LIKE '%panzee';

Wildcard (%): Value starts with:

SELECT * FROM monkeys WHERE type LIKE 'chimp%';

Any char (_):

SELECT * FROM monkeys WHERE type LIKE 'ch_mp_nzee';

Using SQL aggregate functions - AVG, COUNT, MAX, MIN, SUM

SELECT COUNT(*) total_monkeys, SUM(bananas) total_bananas FROM monkeys;

Using GROUP BY with aggregate functions

The following without a GROUP BY will be invalid:

SELECT monkey_type, SUM(bananas) FROM monkeys;

...and return something like:

monkey_type  |   SUM(bananas)
------------------------------
chimp        |     73
gorilla      |     73
chimp        |     73

Using GROUP BY gets the sum of bananas per monkey type:

SELECT monkey_type, SUM(bananas) FROM monkeys GROUP BY monkey_type;

monkey_type  |   SUM(bananas)
------------------------------
chimp        |     53
gorilla      |     20

Using HAVING, the WHERE for aggregate functions

Using the example above:

SELECT monkey_type, SUM(bananas) FROM monkeys GROUP BY monkey_type HAVING SUM(bananas) > 50;

monkey_type  |   SUM(bananas)
------------------------------
chimp        |     53

Ordering Results

Order the monkeys in descending order by the number of bananas and then by name:

SELECT * FROM monkeys ORDER BY bananas DESC, name;

Finding unique values

SELECT DISTINCT type FROM monkeys;

Simple Updates

UPDATE monkeys SET bananas = bananas + 20;

Simple Inserts

INSERT INTO monkeys (name, bananas) VALUES ('Mr Chimp', 34);

INSERT INTO monkeys VALUES ('Mr Chimp', 'chimpanzee', 2);

Simple Deletes

Delete everything:

DELETE FROM monkeys;

Delete specific monkeys:

DELETE FROM monkeys WHERE bananas = '0';

Emptying a table

DELETE * FROM table_name;

Advanced Select Statements

See [ SELECT Statement -- Extended Query Capabilities ]

Viewing Blobs as Text

SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(monkeys_blob,2000,1)) FROM monkeys;

Concatenating Strings (Oracle)

SELECT name || ' has ' || bananas || ' bananas' FROM monkeys;

Select-ing particular row numbers (Oracle)

SELECT * FROM monkeys WHERE rownum < 10;

Creating views

CREATE OR REPLACE VIEW Monkey_Rich_List AS
    SELECT name Monkeys_Name, bananas Food_Rich_Index
    FROM monkeys
    WHERE bananas > 100000
    ORDER BY bananas DESC;

Show Column Names (Oracle)

DESCRIBE monkeys;

Show Tables (Oracle)

SELECT * FROM tabs;

or

SELECT * FROM user_tables;

Oracle Built-ins Reference

See [ Oracle built-in functions ]

Prepared Statements

Just prepared statements using ? as a placeholder for inserting different values e.g.

SELECT * FROM monkeys WHERE name = ?

Cursors

Cursors provide a way to iterate through multiple rows (usually in PL/SQL) returned as part of a query.

CURSOR monkey_names
        IS
        SELECT name FROM monkeys;

Joins - Inner, Outer, Left, Right

See [ Joins in SQL ]

TODO: Constraints

TODO: DDL