3Development / SQL

• 3.1SQL Queries (SELECT, JOIN, Subqueries, Set Operations, DML)

  Learn the clauses of the SELECT statement (WHERE, ORDER BY, GROUP BY, HAVING, DISTINCT, LIMIT, OFFSET), combining tables with JOIN (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN), embedding queries with subqueries, combining result sets with UNION/INTERSECT/EXCEPT, and modifying data with INSERT/UPDATE/DELETE.

• 3.2Data Definition and Database Objects (Data Types, Constraints, Indexes, Views, etc.)

  Learn to design and alter tables with CREATE TABLE/ALTER TABLE/DROP TABLE, key data types (INTEGER/BIGINT/NUMERIC/VARCHAR/TEXT/BOOLEAN/DATE/TIMESTAMP/JSON/JSONB), integrity constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL), search-accelerating indexes, views/materialized views, triggers, sequences, schemas, tablespaces, partitioning, functions via PL/pgSQL, and the basics of streaming/logical replication.

• 3.3Built-in Functions (Aggregate, String, and Date/Time)

  Learn to summarize multiple rows into one value with aggregate functions (count, sum, avg, max, min), compute with arithmetic functions/operators, manipulate strings with string functions (char_length, length, lower, upper, substring, replace, trim, the || concatenation operator, the LIKE predicate), and handle dates/times with date/time functions (age, now, current_date, current_timestamp, extract, to_char).

• 3.4Transaction Concepts (Isolation Levels and Locking)

  Learn how transactions bundle multiple SQL statements into one unit of work (BEGIN, COMMIT, ROLLBACK), how isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) control what concurrent transactions can see, explicit locking with the LOCK statement, the difference between row locks and table locks, and deadlocks where multiple transactions wait on each other forever.