SQL

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

TOC

basics

• Oracle DUAL table
• constant
• concat

select

• Even Number / remainder
• Distinct / no duplicates
• Count
• Top / first X rows
• Pattern matching
• SubString
• Order by
• If else
• group
• null

aggregate

• sum
• average
• round
• floor
• ceil
• max
• min manipulation
• replace

joins

• types of joins
• select from join

Oracle Dual Table

A special table, accessible to all users, used for evaluating expressions or calling functions.

example:

SELECT
  (10 + 5)/2
FROM
  dual;

Taken from source

Constant

To output a String / nice result just select string

SELECT 'There are ' || COUNT(ID) || ' participants'
FROM PARTICIPANTS

Concatenate

Concatenate strings in oracle SQL.
You can use the || operator instead of nested CONCAT functions

'Tech on' || ' the Net'
Result: 'Tech on the Net'

reference

Even Number

mod(<>,2) = 0

example:

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

No Duplicates

SELECT DISTINCT(COLUMN)

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

count

COUNT(ROW)

to count distinct rows:

COUNT(DISTINCT(ROWS))

First X rows

Oracle 12c (see here)

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;

see here

Pattern Mathcing / LIKE

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%'

reference

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)$');

reference

Substring

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*/

reference

Order By / Ordering

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

If Else

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

Group By

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;

Is Null

to check if column value is null or not null
SELECT * FROM orders
WHERE salesman_id IS NULL
ORDER BY order_date DESC;
reference

Sum

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

Average

calculate average of a group

SELECT AVG( list_price ) avg_list_price
FROM products;

Round

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

floor

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

max

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

types of joins

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/

select from joins

SELECT SUM(CITY.population)
FROM CITY
FULL OUTER JOIN COUNTRY
ON CITY.CountryCode = COUNTRY.Code
WHERE COUNTRY.Continent = 'Asia';