SQL
Published: 2021-05-05 08:17:18

Should be a quick cramming / revision guide for SQL stuff. 
Tis is for Oracle SQL (some basics)

## TOC

<u>basics</u>
  * [Oracle DUAL table](#dual-table)
  * [constant](#constant)
  * [concat](#concat)

<u>select</u>
  * [Even Number / remainder](#even-number)
  * [Distinct / no duplicates](#distinct-results)
  * [Count](#count)
  * [Top / first X rows](#first-rows)
  * [Pattern matching](#pattern-match)
  * [SubString](#substring)
  * [Order by](#order-by)
  * [If else](#if-else-case)
  * [group](#group-by)
  * [null](#is-null)



<u>aggregate</u>
  * [sum](#sum)
  * [average](#average)
  * [round](#round)
  * [floor](#floor)
  * [ceil](#ceil)
  * [max](#max)
  * [min](#min)
<u> manipulation </u>
  * [replace](#replace)

<u> joins </u>
  * [types of joins](#types-of-joins)
  * [select from join](#select-from-join)


### <a name="dual-table">Oracle Dual Table</a>
A special table, accessible to all users, used for evaluating expressions or calling functions.

example:
```
SELECT
  (10 + 5)/2
FROM
  dual;
```
<small>Taken from [source](https://www.oracletutorial.com/oracle-basics/oracle-dual-table/)</small>

### <a name="constant">Constant</a>
To output a String / nice result just select string
```
SELECT 'There are ' || COUNT(ID) || ' participants'
FROM PARTICIPANTS
```

### <a name="concat">Concatenate</a>
Concatenate strings in oracle SQL.
You can use the `||` operator instead of nested `CONCAT` functions
```
'Tech on' || ' the Net'
Result: 'Tech on the Net'
```
<small>[reference](https://www.techonthenet.com/oracle/functions/concat2.php)</small>

### <a name="even-number">Even Number</a>
> mod(<<variable>>,2) = 0

example:
```
SELECT DISTINCT(CITY)
FROM STATION
WHERE MOD(ID,2) =0;
```

### <a name="distinct-results">No Duplicates</a>
> SELECT DISTINCT(COLUMN)

```
SELECT DISTINCT(CITY)
FROM STATION
WHERE MOD(ID,2) =0;
```

### <a name="count">count</a>
> COUNT(ROW)

to count distinct rows:
> COUNT(DISTINCT(ROWS))

### <a name="first-rows">First X rows</a>
Oracle 12c (see [here](https://blogs.oracle.com/optimizer/fetch-first-rows-just-got-faster))
> Fetch First X Rows only

```
select   *
from     the_table
order by object_id
fetch first 10 rows only;
```

Otherwise,
> ROWNUM <= X

```
SELECT *
  FROM (SELECT * FROM MyTbl ORDER BY Fname )
 WHERE ROWNUM = 1;
```
<small>see [here](https://stackoverflow.com/a/3451577)</small>

### <a name="pattern-match">Pattern Mathcing / LIKE</a>
1. `LIKE` / `NOT LIKE` - pattern matching
2. `REGEXP_LIKE` / `NOT REGEXP_LIKE`- pattern matching with regular expression

Wild card characters are:
- `%` : any number of characters
- `_` : 1 character

example:
```
# ending with er
SELECT
    first_name,
    last_name,
    phone
FROM
    contacts
WHERE
    last_name LIKE '%er'

# Je 1 character and then i
# eg. Jeni/ Jedi
SELECT
    first_name,
    last_name,
    email,
    phone
FROM
    contacts
WHERE
    first_name LIKE 'Je_i'
```

NOT LIKE
can you not like to find string no matching pattern
```
SELECT
  first_name, last_name, phone
FROM
  contacts
WHERE
  phone NOT LIKE '+1%'
```
<small>[reference](https://www.oracletutorial.com/oracle-basics/oracle-like/)</small>

REGEXP_LIKE
> REGEXP_LIKE(source_string, search_pattern [, match_parameter]);

```
/* starts with m or n */
SELECT
  first_name
FROM
  employees
WHERE
  REGEXP_LIKE(first_name,'^m|^n','i')

/* does not end with vowel */
SELECT DISTINCT(CITY)
FROM STATION
WHERE NOT REGEXP_LIKE(CITY,'(a|e|i|o|u)$');
```

<small>[reference](https://www.oracletutorial.com/oracle-string-functions/oracle-regexp_like/)</small>

### <a name="substring">Substring</a>
extracts a substring from a string
> SUBSTR( str, start_position [, substring_length, [, occurrence ]] );

- start_position
+ve / 0 -> count from beginning of string 
0 is first character
1 can also be first character 
start_position can be *negative*. will count backward from end of str to find first character of substring

- substring_length
if omitted gets string from start_position till end of string

example:
```
/*get last 3 characters*/
SUBSTR(NAME, -3)

SUBSTR( 'Oracle Substring', - 16, 6 ) 
SUBSTR( 'Oracle Substring', 1, 6 ) /*result is Oracle*/
```
<small>[reference](https://www.oracletutorial.com/oracle-string-functions/oracle-substr/)</small>

### <a name="order-by">Order By / Ordering</a>
Sorting results
```
ORDER BY
    column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],
    column_1 [ASC | DESC] [NULLS FIRST | NULLS LAST],
```

to sort multiple columns just add another argument. SQL will sort by first condition first then second condition

### <a name="if-else-case">If Else</a>
`CASE` & `WHEN` to use if else logic in sql query

```
CASE [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result
END
```
you can omit expression to return a constant value (eg. hackerrank binary tree question)
```
SELECT 
    CASE 
      WHEN P IS NULL THEN N || ' Root'
      WHEN N IN(SELECT DISTINCT(P) FROM BST) THEN N || ' Inner'
      ELSE N || ' Leaf'
    END 
FROM BST
ORDER BY N;
```

```
SELECT
  product_name,
  list_price,
  CASE category_id
    WHEN 1
    THEN ROUND(list_price * 0.05,2) 
    WHEN 2
    THEN ROUND(List_price * 0.1,2) 
    ELSE ROUND(list_price * 0.08,2)
  END discount
FROM
  products
ORDER BY
  product_name
```

### <a name="group-by">Group By</a>
group rows into groups

Basic example ` SELECT status FROM orders GROUP BY status;`
This is equivalent to `SELECT DISTINCT status FROM orders;`

Aggregate functions. Find count of order_id for same customer_id
```
SELECT
    customer_id,
    COUNT( order_id )
FROM
    orders
GROUP BY
    customer_id
ORDER BY
    customer_id;
```

### <a name="is-null">Is Null</a>
to check if column value is null or not null
SELECT * FROM orders 
WHERE salesman_id IS NULL
ORDER BY order_date DESC;
<small>[reference](https://www.oracletutorial.com/oracle-basics/oracle-is-null/)</small>

### <a name="sum">Sum</a>
> sum of all or distinct values

```
SELECT
    product_id,
    SUM( quantity )
FROM
    order_items

SELECT
    order_id,
    SUM( quantity * unit_price ) order_total
FROM
    order_items
```


### <a name="average">Average</a>
> calculate average of a group

```
SELECT AVG( list_price ) avg_list_price
FROM products;
```

### <a name="round">Round</a>
> round **off** number to decimal place
`ROUND(n, integer)`
if integer not given it is 0. 

```
SELECT ROUND(15.193,1) "Round" FROM DUAL;

     Round
----------
      15.2

## round to left of decimal place
SELECT ROUND(15.193,-1) "Round" FROM DUAL;

     Round
----------
        20 
```


https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm

### <a name="floor">floor</a>
> Returns the largest integer value that is equal to or less than a number.

```
FLOOR(5.9)
Result: 5

FLOOR(34.29)
Result: 34

```

https://www.techonthenet.com/oracle/functions/floor.php

### <a name="max">max</a>
> return maximum value of expression

```
### maximum salary per department
SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department;

### find top earnings (salary * months) & number of employees with top earnings
SELECT MAX(salary*months), COUNT(*) FROM EMPLOYEE 
WHERE salary * months = (SELECT MAX(salary*months) FROM EMPLOYEE);
```

https://www.techonthenet.com/oracle/functions/max.php

### <a name="types-of-joins">types of joins</a>
![alt text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/din8tn4bns4vwtwn4eh2.png)

To exclude entries in left or right table (get right entries that DO NOT match left) try WHERE .id IS NULL
```
SELECT
    a.id id_a,
    a.color color_a,
    b.id id_b,
    b.color color_b
FROM
    palette_a a
RIGHT JOIN palette_b b ON a.color = b.color
WHERE a.id IS NULL;
```
 
https://www.oracletutorial.com/oracle-basics/oracle-joins/
### <a name="select-from-join">select from joins</a>
```
SELECT SUM(CITY.population)
FROM CITY
FULL OUTER JOIN COUNTRY
ON CITY.CountryCode = COUNTRY.Code
WHERE COUNTRY.Continent = 'Asia';
```