SQL Basics

Day 1

• History of SQL and PostgreSQL
• Connecting to the server with “psql, pgAdmin and other clients
• How to use psql
• SQL syntax elements: keywords, identifiers, constants

Data types

• numbers
• strings
• date/time
• binary data
  

The NULL value

Basic CREATE TABLE

• NOT NULL
• DEFAULT

Schemas

Day 2

Basic INSERT

Basic DELETE

Temporary tables

 Constraints:

• CHECK
• PRIMARY KEY
• UNIQUE
• FOREIGN KEY constraints
• Cascading delete

ALTER TABLE

Sequences, serial, identity columns

Day 3

Basic SELECT: the SELECT list

• Aliases
• Aggregates, FILTER
• DISTINCT
• CASE
• COALESCE

SELECT: the FROM clause

• Table functions
• JOIN
  • CROSS JOIN
  • INNER JOIN
  • OUTER JOIN

SELECT: the WHERE clause

• BETWEEN
• IN (with lists and subqueries)
• EXISTS / NOT EXISTS

SELECT: the GROUP BY clause, HAVING

SELECT: the ORDER BY clause

SELECT: LIMIT and OFFSET

UNION, EXCEPT, INTERSECT

UPDATE, UPDATE … FROM

DELETE … USING

INSERT … SELECT

INSERT … ON CONFLICT

Views

Day 4

COPY

Database Design und Normalization

Transactions

• Deferred Constraints

SQL Injection and Security

Advanced SELECT

• LATERAL Joins
• Grouping Sets
• Window Functions
• Common Table Expressions (CTE)