Relational Database Review & Lab 5 | A Quick Review of the Major Topics

Series: Relational Database

Relational Database Review & Lab 5 | A Quick Review of the Major Topics

0. A Quick Review #1 for the Relational Database

(1) Open the Server

$ postgres -D /usr/local/var/postgres

(2) NULL

NULL means a missing, unknown, undefined, or not applicable value in SQL. Think about the result of the following queries,

SELECT NULL = NULL;                   # NULL
SELECT NULL IS NULL;                  # TRUE
SELECT NULL IS NOT NULL;              # FALSE
SELECT GREATEST(NULL, 2);             # 2
SELECT LEAST(NULL, 2);                # 2
SELECT LEAST(NULL IS NULL, 2);        # Error
SELECT NULL AND TRUE;                 # NULL
SELECT NULL AND FALSE;                # FALSE
SELECT NULL OR TRUE;                  # TRUE
SELECT NULL OR FALSE;                 # NULL
SELECT NULL::BOOLEAN;                 # NULL

(3) Keys

  • Superkey: a set of one or more attributes taken collectively to uniquely identify a record
  • Candidate key: a super key which cannot have any columns removed from it without losing the unique identification property.
  • Unique key: a specific key in the set of candidate key allowing NULL
  • Primary key: a specific key in the set of candidate key not allowing NULL
  • Foreign key: a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
https://www.guru99.com/difference-between-primary-key-and-unique-key.html
PRIMARY KEY
UNIQUE 
FOREIGN KEY (<colname1>) REFERENCES <tablename>(<colname2>)
ON DELETE CASCADE
ON UPDATE CASCADE

(4) Table Modify

  • Delete Table
DROP TABLE IF EXISTS <tablename> CASCADE;
  • Rename Table
ALTER TABLE <tablename>
RENAME TO <new_attname>;
  • Load Data
COPY <tablename>
FROM '<path/filename.csv>'
DELIMITER ','
CSV HEADER;
  • Add Data
INSERT INTO <tablename> 
VALUES <value1, value2, value3, ...>;
  • Update Data
UPDATE <tablename>
SET <att1name> = <val_1>
    <att2name> = <val_2>
    ...
WHERE <constrains>;
  • Delete Data
DELETE FROM <tablename>
WHERE <constraints>;
  • Add column
ALTER TABLE <tablename>
ADD COLUMN <attname> <datatype> <constraint>;
  • Delete Column
ALTER TABLE <tablename>
DROP COLUMN <attname>;
  • Rename Column
ALTER TABLE <tablename>
RENAME COLUMN <attname>
TO <new_attname>;
  • Change the Constraint to NOT NULL
ALTER TABLE <tablename>
ALTER COLUMN <attname>
SET NOT NULL;
  • Delete the Constraint NOT NULL
ALTER TABLE <tablename>
ALTER COLUMN <attname>
DROP NOT NULL;

(5) Type Conversion

Types for PostgreSQL are,

BOOLEAN
CHARACTER(n)
VARCHAR(n)
TEXT
INTEGER
REAL
SERIAL
DATE
TIME(p)
TIMESTAMP(p)
INTERVAL
JSON

To convert a data type, we can use either,

CAST(<expression> AS <TargetType>)

or,

<expression>::<TargetType>
  • Convert Text to Datestamp
TO_TIMESTAMP('05 Dec 2020 54:33:12', 'DD Mon YYYY MI:SS:HH12')
  • Convert Text to Date
TO_DATE('05 Dec 2020 54:33:12', 'DD Mon YYYY')
  • Convert Number to Text
TO_CHAR(-1125.8, '99G999D99S')

The format is,

S -> Sign
D -> Dot
G -> Separator ,
9 -> A Digit
  • Convert Text to Number
TO_NUMBER('12,454.8-', '99G999D99S')

(6) String and Math Functions

# String Functions
UPPER
LOWER
LENGTH
LEFT
RIGHT
REPLACE
SUBSTR
TRIM(<string>)
TRIM(leading <characters> FROM <string>)
TRIM(trailing <characters> FROM <string>)
<string> LIKE <pattern>                       # _ = 1char, *=0+char
<string> SIMILAR TO <regex>
# Math Functions
MAX
MIN
AVG
SUM
COUNT
ABS
ROUND
CEIL
FLOOR
POWER
LOG
GREATEST
LEAST

(8) Set Operations

UNION
UNION ALL
INTERSECT
EXCEPT

(9) Date Time Functions

CURRENT_DATE
CURRENT_TIME                      # with timezone
CURRENT_TIMESTAMP                 # with timezone
EXTRACT(YEAR FROM <timestamp>)
EXTRACT(MONTH FROM <timestamp>)
EXTRACT(DAY FROM <timestamp>)
EXTRACT(HOUR FROM <timestamp>)
EXTRACT(MINUTE FROM <timestamp>)
EXTRACT(SECOND FROM <timestamp>)
INTERVAL '1 DAY'
CURRENT_DATE + INTERVAL '1 DAY'
TO_CHAR(CURRENT_TIMESTAMP, 'HH24:MI:SS')
TO_CHAR(CURRENT_TIMESTAMP, 'HH12:MI:SS')
TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD')
TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH12:MI:SS')
TO_CHAR(CURRENT_TIMESTAMP, 'YY-MON-Mon-Day')
  1. Find the Largest Salary

Suppose we are given an Employee table of employee names and salaries, get the largest salary.

2. Find the Second-Largest Salary

Suppose we are given an Employee table of employee names and salaries, get the second-largest salary.

3. Find the Nth-Largest Salary

Suppose we are given an Employee table of employee names and salaries, get the Nth largest salary.

4. Rank the Score (no “holes” between ranks)

Suppose we are given a Scores table of ids and scores, and we would like to add a Rank column to rank the scores without holes between the ranks.

5. Select Consecutively Numbers

Suppose we are given a Logs table with Id and Num, select the values in Num that appear at least three times consecutively.