Relational Database Review 8 | A Quick Review of the Major Topics — Part 2
Relational Database Review 8 | A Quick Review of the Major Topics — Part 2

1. JOIN
CROSS JOIN # cartesian product
JOIN ... ON ... # no NULL values
LEFT JOIN ... ON ... # values in the left table else NULL
RIGHT JOIN ... ON ... # values in the right table else NULL
FULL JOIN ... ON ... # values in both tables else NULL
2. Conditional Statement
CASE WHEN ... THEN ...
WHEN ... THEN ...
ELSE ...
END AS ...
3. Pivot Table
Input: finalscore table
+---------+----------+---------+
| sid | subject | grade |
+---------+----------+---------+
| 1 | Math | 94 |
| 1 | Physics | 87 |
| 2 | English | 82 |
| 3 | Math | 91 |
| 3 | English | 97 |
+---------+----------+---------+
Output:
+---------+----------+-----------+-----------+
| sid | Math | Physics | English |
+---------+----------+-----------+-----------+
| 1 | 94 | 87 | |
| 2 | | | 82 |
| 3 | 91 | | 97 |
+---------+----------+-----------+-----------+
Wrong Code:
SELECT sid,
CASE WHEN subject = 'Math' THEN grade END AS Math,
CASE WHEN subject = 'Physics' THEN grade END AS Physics,
CASE WHEN subject = 'English' THEN grade END AS English
FROM finalscore
ORDER BY sid;
Because the output will be,
+---------+----------+-----------+-----------+
| sid | Math | Physics | English |
+---------+----------+-----------+-----------+
| 1 | 94 | | |
| 1 | | 87 | |
| 2 | | | 82 |
| 3 | 91 | | |
| 3 | | | 97 |
+---------+----------+-----------+-----------+
Answer Code:
WITH tmp AS (
SELECT sid,
CASE WHEN subject = 'Math' THEN grade END AS Math,
CASE WHEN subject = 'Physics' THEN grade END AS Physics,
CASE WHEN subject = 'English' THEN grade END AS English
FROM finalscore
)
SELECT sid,
SUM(Math) AS Math,
SUM(Physics) AS Physics,
SUM(English) AS English
FROM tmp
GROUP BY sid
ORDER BY sid;
4. <> vs != Debate
There is a conflict between the operation of “not equal”. In fact, you can use either !=
or <>
for not equal and they have the same meaning in PostgreSQL. You can see a discussion of which one to use from here.
5. NULL Functions
ISNULL(colname, expression) # return value if not null else exp
COALESCE(col1, col2, ...) # return 1st not null value else null
NULLIF(col1, col2) # return value of col1 if col1 = col2 else null
6. Get Running Time
EXPLAIN ANALYZE VERBOSE
7. Indexing
- Indexing By Hash Table (Equality Searching / Deleting)
CREATE INDEX indexname
ON tablename
USING hash (colname);
- Indexing By Binary Tree (Range Searching / Sorting / Deleting)
CREATE INDEX indexname
ON tablename
USING btree (colname);
CLUSTER tablename
USING indexname
- No Index (Inserting)
- Delete indexing
DROP INDEX IF EXISTS indexname CASCADE;
8. Window Function
# SUM all column name
SUM(col) OVER ()
# SUM the col1 values group by cole2
SUM(col1) OVER (
PARTITION BY col2)
# SUM the col1 values group by cole2
SUM(col1) OVER (
PARTITION BY col2
ORDER BY col1
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
# Accumulate col1 values group by cole2
SUM(col1) OVER (
PARTITION BY col2
ORDER BY col1)
# Accumulate col1 values group by cole2
SUM(col1) OVER (
PARTITION BY col2
ORDER BY col1
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
# Add the col1 values (current, current + 1) group by col2
SUM(col1) OVER (
PARTITION BY col2
ORDER BY col1
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
9. CTE
WITH tablename1 AS (SELECT * FROM tablename3),
tablename2 AS (SELECT * FROM tablename4),
...
SELECT tablename1.colname1, tablename2.colname2, ...
FROM tablename1, tablename2, ...;
10. Function
- Example
CREATE OR REPLACE FUNCTION sid_by_name(name VARCHAR)
RETURNS TABLE (sid INTEGER, sname VARCHAR) AS
$$
SELECT sid, sname
FROM students
WHERE sname = name
$$
LANGUAGE SQL;
- Delete a function
DROP FUNCTION IF EXISTS functname;
11. View
- Create a view
CREATE VIEW viewname AS
...
- Delete a view
DROP VIEW IF EXISTS viewname CASCADE;
12. JSON
- Get a JSON value of ‘_id’ in a json datatype
SELECT colname -> '_id'
FROM tablename;
- Get a text value of ‘_id’ in a json datatype
SELECT colname ->> '_id'
FROM tablename;
- Convert a table to JSON
SELECT TO_JSON(tablename)
FROM tablename;