SQL with PostgreSQL for Beginners: From Zero to Hero

Discover the fundamentals of databases, focusing on relational databases, tables, columns, and rows, and learn how SQL queries retrieve data with a DBMS.

Explore database types and distributions—on premise, cloud, and distributed—along with categories like relational, analytics, document oriented, and cloud databases, and see how SQL and SQL-like languages power Cassandra.

Explore the SQL standard and its common groups, and note vendor-specific dialects and abbreviations like DDL and DXL. DXL stands for data query language, i.e., a select.

Learn to install PostgreSQL 16 and DBeaver on Windows, Mac, and Linux, including apt-based Linux setup, configuring the postgres superuser password, and connecting with DBeaver.

Import the SQL with PostgreSQL db project into DBeaver, set it active, and verify the database connection. Run SQL statements and enable line numbers and font size in preferences.

Explore five SQL projects from Petaltail to a bookstore and HR system, including importing the Gila and Sakila databases, with a reference attachment outlining sections.

Open window, go to preferences, then editors and text editors, and enable show line numbers. Apply and close to activate line numbers for all open and future scripts in DBeaver.

Create a database using the create database statement, follow lowercase with underscores, configure the Postgres connection, set pat_hotel as the default, and verify the setup.

Create a table using SQL syntax, define columns with data types and not null constraints, and build three tables (pets, owners, pet types) with foreign key relationships in PostgreSQL.

Learn how to insert data in PostgreSQL with insert into statements, including one-row inserts, inserting specific columns, and running a script to populate owners, pet types, and pets tables.

Master multi-line inserts in PostgreSQL by adding multiple rows in one insert into the pets table, specifying a custom column order and using values with a terminating semicolon.

Demonstrate selecting all data with select asterisk from a table to retrieve all rows and columns, using pet hotel data in Postgres as live examples.

Master selecting specific columns and applying aliases with or without as, preserving casing with quotes, shown using pets table (owner id, pet name, pet type id).

Explore how the where clause filters rows with single and multiple conditions using and or in PostgreSQL, with examples from pets and pet types.

Learn to obtain distinct values for a column using the distinct keyword, see how duplicates are removed and casing affects results, with practical examples from pants and pets data.

Explore how to handle null values in SQL using is null and is not null to filter records, such as missing birth dates or non-null emails.

Explore how to use the order by clause to sort data by one or more columns, including multiple sort conditions, with ascending or descending order and nulls first or last.

Use limit and offset to retrieve a subset of rows from the pets table, such as the first ten, or skip rows to fetch the next few.

Explore the core data types in database tables, such as integer, varchar, and boolean, and follow setup steps to create and connect to the HR_system database using dbeaver.

Learn about numeric data types, including integer variants and numeric with precision and scale, and how rounding and floating point comparisons affect salaries and IDs.

Explore character data types in PostgreSQL, including varchar, text, and char, with emphasis on fixed vs variable lengths, UTF-8 encoding, and behavior of trimming and padding spaces in inserts.

Explore date and time data types in PostgreSQL, including date, time, timestamp, and interval, and learn to insert, query, and extract year.

Explore the boolean data type in PostgreSQL, using true and false values, yes on and one, and zero for false, with queries on active and not active and not null.

Learn how to delete data with the SQL delete from statement, using a where clause to target rows, verify results with a select, and remember to back up before deleting.

Use the truncate statement to remove all rows from a table while preserving its structure, contrasting it with delete from. Be mindful of data loss, backups, and privileges.

Drop table removes both data and the table structure, demonstrated on the employee table in DBeaver, and confirms the drop by returning relation does not exist.

Drop a database in PostgreSQL with the drop database statement and super user privileges, then create, delete drop MDB, and verify removal in DBeaver.

Execute an overview of alter and update to modify databases by adding columns, dropping columns, and applying constraints, then relate employees to departments using the title_department table and update department_id.

Master the alter statement to add a column and assign a primary key, then create the department table and apply the primary key with alter table.

Learn to use the update statement to modify data with set and where clauses, update multiple rows, and enforce data integrity with foreign keys and not null constraints.

Explore constraints in an online store database with customer and orders tables, including primary keys, not null, foreign keys, the unique constraint on email, default country, and check constraints.

Learn how the default constraint assigns default values in PostgreSQL during table creation and when adding a default to an existing table, illustrated with id, active flag, and country.

Explore how a check constraint enforces data validity before insert by adding a premium boolean to the customer table and permitting only specific active and premium combinations using alter table.

Learn how the not null constraint prevents empty fields, how to add it during table creation or via alter table, and why empty strings differ from null.

Learn how the unique constraint prevents duplicate emails, how to add it with alter table, and how existing data affects constraint creation.

Explore primary key constraints to enforce unique identifiers, using named and inline approaches on customers and orders tables, and learn to alter tables to add and name primary keys.

Learn how a foreign key constraint links orders to customers, enforcing referential integrity and guiding cascade updates and restricted deletes.

Learn how normalization prevents insert, update, and delete anomalies. See how extracting authors and publication types moves a books table from unnormalized form to first, second, and third normal forms.

Move from unnormalized form to a primary key in the books table by adding an ISBN column, setting it not null, and declaring it the primary key.

Transform the unnormalized form to the first normal form by splitting authors into a separate table, linking via ISBN with a foreign key, and validating results in DBeaver.

Explore the second normal form (2NF) with a single-column primary key, including ISBN handling, isolating authors from the books table, and relating them back to books.

Move from second to third normal form by creating a publication_type table, converting the books table's publication_type to a foreign key integer, and enforcing referential integrity to normalize the schema.

Create and view database diagrams in DBeaver for the bookstore database, arranging tables and visualizing relationships such as ISBN connections and publication type keys.

Explore operators—arithmetic, logical, and comparison—understanding their precedence and associativity, and use parentheses to control evaluation, illustrated through a PostgreSQL Peguila setup in DBeaver.

Explore arithmetic operators in SQL, including addition, subtraction, multiplication, division, division remainder via the modulo symbol, and the power operation, with rental and payment data examples.

Master comparison operators on the payment table to filter amounts using less than, greater than, equal to, and in, and use distinct, order by, and count to verify 15,945 rows.

Explore how to combine conditions with and, or, in, and not in to filter films by rating and length, order results by length, and use functions as extensions of operators.