SQL Basics & PostgreSQL

Let's run some SQL together!

Refer to this repo for the complete code.

PostgreSQL Setup

Installation

PostgreSQL Download Package Installer(EDB)

Please select at least PostgreSQL Server and pgAdmin for installation!

Selecting components

In case you still forget to select pgAdmin, you can always downloaded it separately here

Database Connection

When you open up pgAdmin, click on Servers, there should already be a default Server PostgreSQL <version>, with a postgres database in it. In case you don't, we can also create a new server and database with the following steps:

create server

Recommended Config:

Hostname: localhost
user: postgres (default)

create database

After you are connected to the database, you can open the query tool and run your SQL queries there.

CRUD

Create Table

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE,  
  username TEXT,
  password_hash TEXT
);

-- Create Notebook table
CREATE TABLE notebooks (
  id SERIAL PRIMARY KEY,
  nb_name TEXT,  -- avoid reserved keywords like 'name'
  nb_description TEXT,
  created_at TIMESTAMP,
  created_by INT,
  FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Create Note table
CREATE TABLE notes (
  id SERIAL PRIMARY KEY,
  title TEXT,
  content TEXT,
  created_at TIMESTAMP,
  notebook_id INT,
  FOREIGN KEY (notebook_id) REFERENCES notebooks(id)
);

Insert

INSERT INTO users (email, username, password_hash) VALUES ('[email protected]', 'alice', 'hash1');

INSERT INTO users VALUES (DEFAULT, '[email protected]', 'charlie', 'hash2');

-- Do refer to the repo for complete code!

Update

UPDATE users SET username = 'alex' WHERE username = 'alice';

Delete

DELETE FROM users WHERE id = 1;

Drop

DROP TABLE users;

Simple Queries

-- Retrieve all users:
SELECT * FROM users;

-- Retrieve a specific user by their email:
SELECT * FROM users WHERE email = '[email protected]';

-- Retrieve all distinct usernames:
SELECT DISTINCT username FROM users;

-- Retrieve all users without a specific username:
SELECT * FROM users WHERE username IS NULL;

-- Retrieve all users whose username is not null:
SELECT * FROM users WHERE username IS NOT NULL;

Algebraic Queries

Inner Join

-- Retrieve all notebooks along with their corresponding user information:
SELECT nb.*, u.username, u.email
FROM notebooks nb
INNER JOIN users u ON nb.created_by = u.id;
-- is equivalent to
SELECT nb.*, u.username, u.email
FROM notebooks nb, users u
WHERE nb.created_by = u.id;

Left Outer Join

Here is an excellent article explaining the different join concepts, do give it a read!

OUTER JOINS are joins that return matched values and unmatched values from either or both tables. In the case of left outer join, we return matched values and unmatched values from the table to the left of the JOIN keyword.

-- Retrieve all notebooks along with their note ids, even if the notebook does not contain any note:
SELECT nb.*, nt.id AS note_id
FROM notebooks nb
LEFT OUTER JOIN notes nt ON nb.id = nt.notebook_id;

Set Operators

UNION, INTERSECT, EXCEPT

They eliminate duplicates unless annotated with the keyword ALL.

To use the set operators on two tables, they must satisfy the condition:

• The two queries must be union-compatible.

• They must return the same number of columns with the same domains in the same order.

-- select customers who downloaded both version 1.0 and 2.0 for game Aerified
SELECT d.customerid FROM downloads d
WHERE d.name = 'Aerified' AND d.version = '1.0'
INTERSECT
SELECT d.customerid FROM downloads d
WHERE d.name = 'Aerified' AND d.version = '2.0';

Aggregate Queries

COUNT, MAX, MIN, AVG, SUM, STDDEV, ...

-- Retrieve the total number of users:
SELECT COUNT(*) FROM users;

-- Retrieve total number of users w/ a username:
SELECT COUNT(username) FROM users;

-- Retrieve total number of distinct usernames:
SELECT COUNT(DISTINCT username) FROM users;

Group By, Having

• GROUP BY creates groups of records that have the same values for the specified fields before computing the aggregate functions. Without a clause, only one group is formed implicitly by SQL as soon as an aggregate function is called.

  • Aggregate functions cannot appear before GROUP BY(in WHERE), hence we have HAVING.

• HAVING adds conditions to be checked after the evaluation of the GROUP BY clause. It only involves aggregate functions, columns listed in the GROUP BY clause and subqueries.

• Attributes in the SELECT clause must appear in the GROUP BY clause, unless it is used in an aggregate function.

A slightly complicated query:

Try to figure out what it does!

SELECT u.username, COUNT(nb.id) AS total_notes
FROM users u
LEFT OUTER JOIN notebooks nb ON u.id = nb.created_by
LEFT OUTER JOIN notes nt ON nb.id = nt.notebook_id
GROUP BY u.id, u.username
ORDER BY total_notes DESC;

Nested Queries

Nest a subquery in a query.

• Subqueries can be used only in FROM clause or WHERE clause

• Comparison to a subquery should always use the quantifier ALL or ANY

Many ways to do the same thing. Just for illustration.

-- Retrieve user who created the most notes
SELECT username, total_notes
FROM (SELECT u.username, COUNT(nb.id) AS total_notes
        FROM users u
        LEFT OUTER JOIN notebooks nb ON u.id = nb.created_by
        LEFT OUTER JOIN notes nt ON nb.id = nt.notebook_id
        GROUP BY u.id, u.username
        ORDER BY total_notes DESC) 
LIMIT 1;