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

Series: Relational Database

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;