The Ultimate POSTGRE SQL Bootcamp: SQL Beginner to Expert

Learn the basics of PostgreSQL data definition language (ddl) and how to create, alter, drop, and truncate databases, tables, and schemas, including renaming columns and tables.

Create a database via the interface by right-clicking and choosing create database, or by writing a create database query. Examples include employee_database and customer_details.

Discover SQL data types for table columns, including integer, smallint, bigint, decimal, float, and boolean. Understand char, varchar, text, date, time, and timestamp to store accurate, efficient data.

Explore SQL constraints such as not null, unique, primary key, foreign key, check constraint, and default, and learn how they enforce data type accuracy, integrity, and nonredundancy in tables.

Create a PostgreSQL table by defining a table name, columns, data types, and constraints, illustrated with employee example: id integer primary key, name varchar(50) not null, salary integer not null.

Learn to modify a table with alter table, including adding and dropping columns. See how the employee data table demonstrates changing data types and renaming columns or the table itself.

Learn how to drop a database or a table using SQL commands and a UI, with confirmation prompts and notes that dropping databases is not really recommended.

Explore data manipulation language in SQL, including insert, select, update, and delete, and distinguish it from data definition language.

Master how to use insert into to add new rows to tables, including single and multiple row inserts, constraints, defaults, and basic table creation in PostgreSQL.

Apply the SQL update statement to modify specific records using set and where clauses, illustrated with employee data to update age, experience, and company names.

Master the delete statement in SQL by removing specific records with a where clause, understand that without a where clause all rows are deleted, and verify changes with a select.

Truncate removes all rows from a table to yield an empty table, while drop removes the table structure and delete removes rows by a condition.

Explore transaction control in PostgreSQL, focusing on commit, begin transaction, rollback, and savepoint, and learn how commits permanently save changes even if the system crashes.

Learn rollback in PostgreSQL by starting a begin transaction to undo changes since the last commit, restoring deleted records and returning tables to their prior state.

Explore how savepoints create checkpoints within a transaction, enabling rollback to a named savepoint while distinguishing permanent changes from temporary ones, followed by commit.

Launch PostgreSQL with Pgadmin, create the e-commerce data database, load the Superstore sales data by pasting and executing the provided script, then run a select from the Superstore sales table.

Learn the basics of the select statement in SQL, including syntax for selecting all columns with the asterisk and choosing specific columns from a table, using the superstore sales example.

Learn how to use select distinct to find unique values in single or multiple columns, such as region, category, and subcategory, and to obtain distinct combinations in PostgreSQL.

Learn to assign temporary names to columns and tables in SQL using aliases, with or without the as keyword. See how aliases help joins and aggregates.

Learn to use the where clause in SQL to fetch data by conditions, using comparison and logical operators such as and, or, not.

Understand how to use the where clause with comparison operators in PostgreSQL to filter data by a single condition using equal, not equal, greater than, less than, and other operators.

Learn to use the and operator in sql to filter data with multiple conditions in a where clause, with examples on region, category, and profit.

Use the or operator in SQL to return rows that satisfy either condition, with syntax like select asterisk from table where condition1 or condition2.

Learn how the not operator in PostgreSQL returns records opposite to a condition, using where not, and combine it with and or to filter by region, subcategory, or technology.

Learn how the in operator replaces multiple or conditions to filter data in PostgreSQL, with examples like region in (east, west, south) and category in (chairs, tables, phones).

Master the between operator to filter numeric and date ranges with inclusive endpoints in PostgreSQL, using syntax where column between value1 and value2, and apply it to orders and dates.

Discover how the limit keyword restricts the number of rows returned by a SQL query, with syntax like select * from table limit n, plus region or subcategory filters.

Explain how offset and limit in PostgreSQL skip rows and return a specific number of results, including the last five or last twenty using a dataset like Superstore sales.

Sort data with order by in SQL, using ascending by default and descending when you specify desc, and combine with where and limit for top or bottom results.

Explore SQL aggregate functions that perform calculations on a set of rows and return a single value, using max, min, sum, average, and count for data analysis, reporting, and dashboards.

Master SQL aggregate functions—max, min, sum, average, and count—through practical Superstore examples, including filtering with where and combining all aggregates in one query.

Explore the group by clause in PostgreSQL, using aggregate functions such as count, max, min, sum, and average to group data by category, region, city, and other columns.

Learn to use the having clause to filter group by results with aggregates, and master the correct order of execution—from where to having, then order by and limit in PostgreSQL.

Explore subqueries by embedding an inner query inside an outer query, enclosed in parentheses, with the inner query executing first to enable filtering and comparisons in PostgreSQL.

Learn how to use subqueries in SQL, combining inner and outer queries to find employees by max, min, and average salaries, including department-specific examples.

Learn how to find the maximum salary by department with subqueries and group by, then identify the corresponding employees using in and composite salary department checks.

Explore how window functions use the over clause with partition by and order by to calculate across related rows, enabling aggregations, rankings, and running totals.

Apply aggregate window functions to compute department-wise max and min salaries, identify top earners per department, and count employees using over partitions, with optional CTE or subquery approaches.

Explore ranking window functions in PostgreSQL, including row_number, dense_rank, and rank, and learn how unique values versus repetitions affect finding top salaries.

Master row_number with an over clause and order by salary in descending, to identify the sixth highest salary and the second highest salary within each department.

Explore how the rank function assigns the same rank to duplicates and skips ranks, and learn to find the second highest salary per department using dense_rank and partition by.

Explore dense_rank, a window function that assigns consecutive salaries ranks with no gaps using over and partition by. Rank eighth highest and fifth lowest salaries, including per department.

Use the lead window function in PostgreSQL to compare each employee's salary with the next joined employee, sorting by employee ID, and compute salary differences.

Learn how the lag window function compares each employee salary with the previous one, ordered by employee id, and how to compute the difference using current minus previous.

Learn the cumulative distribution in Postgres SQL, using the formula rows less than or equal to the current row divided by total rows, shown with salary examples for interviews.

Apply ntile, a window function that divides data into n equal buckets using order by salary, showing how to assign employees to lowest or highest salary groups in PostgreSQL.

Learn how to compute percent_rank in PostgreSQL using the formula (rank-1) divided by (total number of rows minus one), with salary examples and window function concepts.

Explore value window functions, focusing on first_value, last_value, and nth_value; learn how first_value retrieves first salary in employee data and, with over clause partition by department, mirrors a maximum salary.

Unlock last_value window function to extract the last value in a column, using over with unbounded preceding and unbounded following, and apply partition by to find minimum salary by department.

Explore nth_value, a window function, to find the third highest salary and its employee details, then apply partition by department to compute the second minimum salary.

Explore SQL joins, including inner, left, right, full, cross, self, and natural, that combine rows from related tables via common columns and primary and foreign keys for cross-table analysis.

Discover how inner join returns only matching rows from two tables by joining on a common column, with practical employee and department examples and the select, from, and on syntax.

Explore the right join in PostgreSQL by joining two tables, returning all rows from the right table with matching left-side data and nulls where there is no match.

Learn how the left join (left outer join) returns all rows from the left table with matching data from the right table, or null when no match.

Explore how a full join (full outer join) retains all rows from both tables with matching data and nulls for nonmatching rows, like a union in PostgreSQL.

Explore how a cross join produces the Cartesian product of two tables in PostgreSQL by pairing every shirt color with every size, yielding nine combinations.

Discover how natural join automatically identifies common columns by name and returns matching records like an inner join; it works only when common columns share the same name.