Up and Running With PostgreSQL 11

Learn to use psql to navigate with backslash commands, list databases with \l, and create a new database using create database <name> in PostgreSQL 11.

connect to postgres databases using psql, specify host, port, and user, connect to the test database, list databases with \l, and switch databases with \c.

Discover why the drop database command in PostgreSQL is dangerous, especially in production, and how backups and access controls help prevent data loss.

Create your first Postgres table with CREATE TABLE, defining column names and data types. Review data types like int, varchar, date, timestamp, serial, and text, using a person table example.

Create a PostgreSQL 11 table named person with id, first name, last name, gender, and date of birth, using appropriate data types and exploring constraints and defaults.

Create a PostgreSQL person table with constraints, including a bigserial primary key and not null first name, last name, gender, and date of birth, while email remains nullable.

Insert records into a PostgreSQL table by listing columns like first name, last name, gender, and date of birth; omit email when absent and rely on serial id auto increment.

Insert records into a PostgreSQL table by listing columns and values. Use the id sequence, set first name, last name, gender, date of birth, and email for the person table.

Learn to perform read operations in PostgreSQL by using select from, select star, and selecting first name, last name, and email, while noting id sequencing.

Learn to insert a thousand records into PostgreSQL 11 using mocha room, including adding a country of birth column and a serial id.

Sort data with the order by keyword by using ascending or descending order on columns such as country, birth date, id, or email; learn default behavior and multi-column sorting.

Remove duplicates in PostgreSQL 11 by using the distinct keyword on a column like country of birth, then order the results; apply to dates and emails.

Learn how to use the where clause to filter data with conditions, combining and or logic, and parentheses to refine results by gender, country of birth, and last name.

Learn to use comparison and arithmetic operators in PostgreSQL to filter data in where clauses, evaluate expressions, and handle strings, dates, and other data types.

Learn to limit, offset, and fetch query results in PostgreSQL 11 by selecting the first 10, then rows after an offset, and using fetch as the standard alternative.

Use the in keyword to filter multiple countries in PostgreSQL, replacing multiple or conditions with a single in clause and optionally order results by country of birth.

Learn to query data in PostgreSQL 11 using the between keyword to filter records by a date range, such as birth dates between two dates, with practical examples.

Explore the like and ilike operators in PostgreSQL to match text with patterns, using wildcards and underscore, and understand case sensitivity in queries.

Learn to use the group by keyword to aggregate data by a column, such as country of birth, count records, and sort results by country in PostgreSQL.

Learn how the having keyword filters aggregated results after a group by, using count(*) on country of birth to show countries with at least five people.

Learn to use max and min aggregate functions in PostgreSQL while creating a car table with id, make, model, and price, and populating it from a file.

Compute max, min, and average car prices to identify the most expensive and cheapest models, rounding the average and grouping results by make and model.

Master the sum aggregate function in PostgreSQL by calculating the total price across all cars and by each car make using group by.

Learn how to use PostgreSQL arithmetic operators to perform addition, subtraction, multiplication, division, exponentiation, factorial, and modulus. Apply these operators to compute results like discounted prices and simple statistics.

Learn to use arithmetic operators on the car table to compute original and discounted prices, applying 10 percent off, rounding to two decimals, and displaying the resulting prices.

Learn how to use aliases in PostgreSQL 11 to rename query result columns and override default column names for clearer output.

Learn how to use the coalesce keyword to provide a default value when the first parameter is null, such as substituting missing emails with 'email not provided'.

Learn how to prevent division by zero in PostgreSQL using null if to return null and coalesce to supply a default, enabling safe arithmetic.

Learn how to work with dates and timestamps in PostgreSQL 11, using now(), casting to date or time, and handling timestamp with or without time zone and interval.

Learn to add and subtract dates in PostgreSQL using interval, starting from the current timestamp, with examples for years, months, and days, and casting to date.

Learn how to extract specific values from a timestamp using the extract function in PostgreSQL, including year, month, day, day of week, century, and milliseconds.

Learn how to compute a person’s age in PostgreSQL 11 using the age function, adding an age column from date of birth and the current timestamp.

Identify a record uniquely in a table using primary keys. Learn how sequences generate unique numbers as primary keys, with passport numbers as a real-world example.

Explore how primary keys uniquely identify records and how dropping the primary key constraint affects inserts and duplicates. See how sequences and auto increment work in the person table.

Learn how to add a primary key constraint in PostgreSQL by ensuring the id column is unique, removing duplicates if needed, and then applying the primary key.

Learn how unique constraints enforce unique values per column, preventing duplicate emails. Apply, rename, and drop constraints with alter table, and name them to distinguish from primary keys.

Learn how to enforce data integrity with check constraints in PostgreSQL by restricting the gender column to male or female, using alter table add constraint and check conditions.

Learn to delete records using delete from ... where, prioritizing the primary key, and filter by gender, email, or country of birth; avoid deleting without a where clause.

Learn how to use the update command in PostgreSQL with set and where to modify single rows or multiple columns safely, avoiding updates to all rows.

Learn to handle duplicate key errors in PostgreSQL with on conflict, preventing inserts that violate a primary key or unique constraint by specifying a conflicting column like id or email.

Learn how to use PostgreSQL upsert with on conflict do update to override existing rows, using excluded values to update email and other fields in distributed systems.