Quick SQL 2

Posted on March 2, 2022
Tags: sql

on CAP on CAP 2

CREATE TABLE bleh (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
INSERT INTO bleh (name,age) VALUES ('bob',30);
INSERT INTO bleh (name,age) VALUES ('rex',2);
.mode column
.headers on

1 Quick Theory

Tables are represented as relations \((A\times B\times C)\)
\(A\), \(B\), \(C\) are the columns of the table

Symbol Name Operation Related SQL
\(\pi\) Projection \((A\times B\times C)\rightarrow (A\times C)\) Select a,b FROM ..
\(\sigma\) Selection \(\{x\in (A\times B\times C)\ |\ Pred(x)\}\) … WHERE a*b==3

1.1 Candidate Key

  • Candidate key, minimum attributes that uniquely identifies an object
    • Many times a candidate key is the primary key
    • But sometimes it may be multiple attributes combined

1.2 Functional dependency

  • Notation : \(X \rightarrow Y\)
    • \(X\) is called Determinant and \(Y\) is called Dependent
    • \(X_{information} \gt Y_{information}\)
    • Ask yourself if I have information on \(X\) can i be sure of \(Y\) ?

Example: \(DepartmentName \rightarrow DepartmentBuilding\)

  • \(DepartmentName\) gives us information on \(\{DepartmentBuilding, DepartmentDean, Subject \}\) .
  • therefore \(DepartmentName_{information} \gt DepartmentBuilding_{information}\)

2 JOINS

3 Normalization

3.1 1NF - Merge/Remove redundency

  • By default SQL is 1NF since it does not allow nested tables
    • 1NF also means if business requirements deem Address as ATOMIC, you may not split it up into City,Zip,House# which are subATOMIC wrt to busienss requirements
  • By default noSQL mongoDB, dynamoDB are NOT 1NF because they allow nesting

3.1.1 To convert

  • If a DB has nested table then it would have to extract the nested table into another table with a foreign key.
  • Obvious drawback to 1NF is more JOIN operations

3.2 2NF - Eliminate Repeats

  • Each non-key attribute is completely dependent on the Candidate Key/Primary key

Some value repeats multiple times.
Collapse the dependency into a new table.

3.3 3NF - Move out Cols not dependent on Key

3.4 4NF

4 Entity Relation

@startuml
Entity01 }|--|| Entity02
Entity03 }o--o| Entity04
Entity05 ||--o{ Entity06
Entity07 |o--|| Entity08

entity Entity11{
 * user_id : INTEGER PK
}
entity Entity12{
 * post_id : TEXT PK
}

Entity11::user_id }|--|| Entity12::post_id
@enduml

5 Timescale

docker run -d --name timescaledb -p 5432:5432 -e POSTGRES_PASSWORD=password timescale/timescaledb:latest-pg14

login with username “postgres”

psql -U postgres -h localhost

tsdb=> CREATE database example;
# connect to db named "example"
tsdb=> \c example

# list tables
tsdb=> \dt

6 Db Driver

import psycopg2
username = "postgres"
password = "password"
host = "localhost"
port = 5432
dbname = "example"
CONNECTION = f"postgresql://{username}:{password}@{host}:{port}/{dbname}"
with psycopg2.connect(CONNECTION) as conn:
    cursor = conn.cursor()
    SQLquery = """
    DROP TABLE IF EXISTS "btc_prices";
    CREATE TABLE "btc_prices"(
   time            TIMESTAMP WITH TIME ZONE NOT NULL,
   open   DOUBLE PRECISION,
   high   DOUBLE PRECISION,
   low    DOUBLE PRECISION,
   close   DOUBLE PRECISION,
   volume      DOUBLE PRECISION
    );
    SELECT create_hypertable('btc_prices','time');
    """
    cursor.execute(SQLquery)
    conn.commit
    cursor.close()

7 Python

Assuming stocks is a pandas DataFrame.
Below will create a table named temp in SQL and fill it with stocks DataFrame.

from sqlalchemy import create_engine
username = "postgres"
password = "password"
host = "localhost"
port = 5432
dbname = "example"
CONNECTION = f"postgresql://{username}:{password}@{host}:{port}/{dbname}"

engine = create_engine(CONNECTION, echo=False)
stocks.to_sql("temp", con=engine, if_exists="replace")

8 psql commands

8.1 Get Users

\du+
#                                           List of roles
#  Role name |                         Attributes                         | Member of | Description 
# -----------+------------------------------------------------------------+-----------+-------------
#  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

8.2 List databases

\l+
#                                  List of databases
#    Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
# -----------+----------+----------+------------+------------+-----------------------
#  example   | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
#  postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
#  template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
  • if none create db with CREATE database example;

8.3 Connect to database

  • \c someDBname : connect to DB
\c example
# You are now connected to database "example" as user "postgres".
# example=#

8.4 Get tables in database

WARN: First must connect to database

  • \d+ : check existing tables
  • \d+ someTable : describe table schema of “someTable”q

if none create table with CREATE TABLE bleh (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);

8.5 Create new user

CREATE USER bob WITH ENCRYPTED PASSWORD 'root';
GRANT ALL PRIVILEGES ON DATABASE example TO bob;

8.6 other commands

  • \l+ : check db storage mem used

9 SQL scenarios

9.1 Check head of table

SELECT * FROM temp LIMIT 5;

9.2 Dropping a column

ALTER TABLE temp DROP COLUMN somejunk;

9.3 Transform into Hypertable

Note that this automatically turns the datetime column into an btree Index.

SELECT create_hypertable('temp', 'datetime',migrate_data => TRUE);

10 Transactions