SQL
limjy
Posted on May 5, 2021
Should be a quick cramming / revision guide for SQL stuff.
Tis is for Oracle SQL (some basics)
TOC
basics
select
- Even Number / remainder
- Distinct / no duplicates
- Count
- Top / first X rows
- Pattern matching
- SubString
- Order by
- If else
- group
- null
aggregate
joins
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'
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
-
LIKE/NOT LIKE- pattern matching -
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%'
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)$');
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 substringsubstring_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*/
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';
Posted on May 5, 2021
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
November 30, 2024
