SQL and PostgreSQL: A Practical Course

Explore the basics of SQL, the structured query language for relational databases, and learn how to create, delete, update, and retrieve data across systems, using PostgreSQL as an example.

Create and manage databases using pgAdmin and SQL, run CREATE DATABASE, connect and drop databases, refresh the UI, and use semicolons and // for comments.

Master select queries by specifying columns and tables, applying where conditions, ordering results, and using distinct, with computed columns like rating divided by 2 as five_star_rating.

Discover how to join film and country tables using inner, left, and right joins, with on conditions and aliases to reveal film titles, production countries, and currencies.

Master aggregate functions such as max, min, count, sum, and average, group data by country, and apply where and having filters after grouping.

Use the update command to modify existing rows in the film table, setting age_restriction to 18 for the row where title = 'The Shining', and verify the change.

Learn how primary keys and foreign keys enforce data integrity in SQL and PostgreSQL. Alter tables to add constraints, prevent duplicates, and ensure referential integrity between film and country.

Understand how a primary key creates an index and how a country index speeds up searches like select * from film where country = 'Italy'.

Understand how transactions group operations into one unit that either completes entirely or not at all, using begin, commit, rollback, and read committed isolation with a banking transfer example.

Explore how the first sections only partially cover SQL and PostgreSQL capabilities. Refer to the official PostgreSQL documentation for introductory guides and deeper insights to be covered in later parts.

Learn to create and drop tables in SQL, define rows, columns, and data types, and use if exists or if not exists, illustrated with a products example.

Learn how check constraints enforce numeric data rules in SQL and PostgreSQL by requiring nonnegative prices on a NUMERIC column, using named constraints like positive_p.

Apply the not null constraint to ensure columns never hold null values and must be initialized, such as product names or other constrained columns, in sql and PostgreSQL.

Discover how unique constraints enforce column or group uniqueness in PostgreSQL, with automatic unique indexes, table constraints, and nullable behavior adjustable via NULLS not distinct.

Learn how to define a primary key in PostgreSQL, converting unique not null constraints to a primary key, and understanding how primary keys enforce uniqueness and non-nullness.

Learn how to modify existing tables with alter table commands: add or drop columns, change data types, set defaults, rename columns, and manage constraints and foreign keys, including cascade options.

Explore inserting data with the insert command, specifying columns, adding multiple rows, and using a select-based approach to modify existing records by prefixing names with pro and doubling prices.

Use the update command to modify existing rows, set new values, and apply conditions with where to target specific records or update all records.

Learn how the delete command removes rows in a table and how to use a where clause to delete specific records, such as product_id = 5.

Use the returning clause to retrieve values from insert, update, and delete operations in PostgreSQL, returning specific columns or all fields, including auto-generated ids.

Learn how to retrieve data with the select command, specifying columns or using * from a table. Explore computing expressions with round and aliases, and note joins, filters, and limits.

Master practical SQL join statements by examining one-to-one, one-to-many, and many-to-many relationships with accounts and profiles, demonstrating inner, left, right, and full joins using foreign keys and aliases.

Use the where clause to filter records after the from clause, applying equality, range, and subqueries; join accounts to profiles to get emails for users over the age of 20.

Learn how to group by and aggregate data in SQL, and apply post-group filtering with having after grouping, using aggregate functions like avg and max across departments.

Explore window functions that compute across related rows without grouping, using partition by department and the over clause; rank and average salary examples.

Combine query results using union, intersect, and except; ensure union compatibility by matching column counts and types, include duplicates with all, and brackets for prioritizing multiple queries.

Sort results with the order by clause, ranking by department in descending order and salary in ascending order, using expressions, handling nulls with nulls first or last for unions.

Learn to retrieve a subset of rows with limit and offset, implement pagination, and return data in manageable chunks for performance.

Learn to define a values list with expressions, query with select, union with season_products, and use the values list as a data source for inserts.

Discover how the with clause and common table expressions simplify complex sql queries by breaking them into readable blocks and enabling recursive and hierarchical data.

Explore PostgreSQL character types: CHAR fixed-length with space padding, errors when a string exceeds the defined length N; VARCHAR variable-length without padding, up to N; TEXT offers unlimited, flexible storage.

Explore PostgreSQL date and time data types, including date, time, timestamp with time zone, and interval, using ISO 8601 formats and special values like today, tomorrow, and yesterday.

Understand PostgreSQL's boolean data type, which stores true, false, and null, with true and false preferred. String literals like 'true' or '1' map to true, and '0' maps to false.

Explore the core functions and operators in PostgreSQL, beyond the SQL standard, and learn how they perform calculations, transform data, and evaluate conditions with both table and non-table values.

Explore SQL comparison operators across numeric, string, and date types, including between, between symmetric, not between, is distinct from, and is null predicates.

Master PostgreSQL's mathematical operators and bitwise functions across numeric types such as SMALLINT, INTEGER, BIGINT, REAL, and DOUBLE PRECISION, and use ABS, CEIL, FLOOR, ROUND, RANDOM, RANDOM_NORMAL, and SETSEED.

Explore string functions and operators in PostgreSQL, including the concatenation operator, trimming with btrim, case conversion with lower and upper, padding with lpad and rpad, and substring and position operations.

Learn to manipulate dates and times in PostgreSQL using operators and functions, including adding and subtracting dates, intervals, and times, extracting fields, and constructing dates with make_date, current_date, and current_time.