PostgreSQL Katas

Kata (型 or 形 literally: "form"), a Japanese word, are detailed choreographed patterns of movements practiced either solo or in pairs.

Some simple exercises to practise SQL in PostgreSQL database.

CRUD

In computer programming, create, read, update, and delete (as an acronym CRUD) are the four basic functions of persistent storage.

PostgreSQL	crud.sql
INSERT	INSERT
SELECT	SELECT
UPDATE	UPDATE
DELETE	DELETE

String Functions

SQL string functions are used primarily for string manipulation, e.g. combining strings together or replacing certain symbols or words.

PostgreSQL	string_functions.sql
concat()	concat()
substring()	substring()
replace()	replace()
reverse()	reverse()
length()	length()
upper() / lower()	upper()/lower()

Refining Selections

Refine - make minor changes so as to improve or clarify.

PostgreSQL	refining_selection.sql
DISTINCT	DISTINCT
ORDER BY	ORDER BY
LIMIT	LIMIT
LIKE/ILIKE	LIKE/ILIKE

Aggregate Functions

An aggregate function is a function where the values of multiple rows are grouped together to form a single value of more significant meaning or measurement such as a set, a bag or a list.

PostgreSQL	aggregate_functions.sql
count()	count()
GROUP BY	GROUP BY
min()/max()	min()/max()
sum()	sum()
avg()	avg()

Data Types

PostgreSQL has a rich set of native data types available to users. Such as boolean, character, numeric, temporal, array, json, uuid, and other special types.

PostgreSQL	data_types.sql
CHAR/VARCHAR	CHAR/VARCHAR
NUMERIC	NUMERIC
FLOAT/DOUBLE	FLOAT/DOUBLE
DATE/TIME	DATE/TIME
current_date/current_time/now()	current_date/current_time/now()
to_char()	to_char()
INTERVAL	INTERVAL
TIMESTAMP	TIMESTAMP
FUNCTION / TRIGGER	FUNCTION/TRIGGER

Logical Expressions

Logical operators are typically used with Boolean (logical) values. There are three logical operators in PostgreSQL: OR, AND, NOT.

PostgreSQL	logical_expressions.sql
NOT Equal	NOT Equal
NOT LIKE	NOT LIKE
Greater Than	Greater Than
Less Than	Less Than
Greater/Less Than Or Equal	Greater/Less Than Or Equal
Logical AND	Logical AND
Logical OR	Logical OR
BETWEEN	BETWEEN
IN	IN
CASE	CASE

One-to-Many

In a one-to-many relationship, one record in a table can be associated with one or more records in another table. For example, each customer can have many sales orders.

One-to-Many	one_to_many.sql
PRIMARY KEY	PRIMARY KEY
FOREIGN KEY	FOREIGN KEY
JOIN	JOIN
coalesce()	coalesce()
ON DELETE CASCADE	ON DELETE CASCADE

Many-to-Many

A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table. For example, a many-to-many relationship exists between customers and products: customers can purchase various products, and products can be purchased by many customers.

Many-to-Many	many_to_many.sql

Example Instagram Schema

A simple schema for an Instagram like app: instagram.sql