PostgreSQL Bootcamp: SQL and PostgreSQL Database Masterclass

Discover how the select statement retrieves data from tables or views, listing columns or using an asterisk to fetch all columns, and understand error handling for invalid table names.

Learn to rename columns and tables with alias in select statements, using column alias and table alias, optional as, and quoted names to handle spaces, keeping queries concise and readable.

Explore literals in select statements, including string, numeric, and date/time constants, and learn how they appear as columns with aliases for readability.

Explore arithmetic operators on columns in a select segment in PostgreSQL, including addition, subtraction, multiplication, division, remainder, power, and factorial, with results such as new unit price and total cost.

Master the PostgreSQL concatenation operator, using the pipe symbol to join columns and literals, producing a single combined column in queries.

Master the where clause to filter records in select, update, and delete statements. Learn string literals with quotes, numeric filters, and simple equality conditions on employee data.

Apply logical operators and, or, and not to combine multiple conditions in SQL queries, filtering employees by job title and city to retrieve matching records.

Master the like operator in PostgreSQL to search text patterns using percent and underscore wildcards. Practice filtering data by starts with, ends with, and contains patterns.

learn how to use the between operator to select a range of data in sql, with inclusive bounds and examples on shipping fees, dates, and city names.

Master the in and not in operators in SQL to select or exclude predefined value sets, with examples on ship variety and payment types, including null considerations.

Master comparison operators in sql: equal, not equal, greater than, greater than or equal, less than, and less than or equal, with practical where clause examples.

Explore how to limit query results using limit and fetch operators, including applying an offset to skip records and retrieve the top five rows from the employees table.

learn how to use the distinct clause in a select statement to remove duplicate rows and return unique values from one or more columns, including handling nulls.

Learn to order query results with the order by clause, using ascending or descending on single or multiple columns and handling nulls with nulls first or last.

Learn to handle null data in columns by using is null and not null filters to select missing values or ignore them, while avoiding incorrect use of equals with null.

Learn how joins combine data from tables using a common column, with examples from salary and employee tables, and review outer, left, right, full, self, and cross joins.

Learn how inner join returns only matching records from two tables using a join condition on employee id, illustrated with salary and employee data and join syntax.

Master the left outer join by seeing how it returns all rows from the left table, such as salary, and matches from the right (employee), with nulls for missing data.

Explore right outer join concepts by comparing it to inner and left outer joins, showing how the right table's data is retained and non-matching rows from the right are included.

Learn how the full outer join combines inner, left, and right joins to return all matching and nonmatching rows from two tables, illustrated with salary and employee data.

Explore self join concepts by joining the employee table to itself to match employees with their managers, using inner and left joins with table aliases.

Learn how cross join, also called Cartesian join, pairs every row from the first table with every row from the second, without any join condition.

Explore the basics of set operators in databases, including union, intersect, and except, with rules that all queries share the same column count and compatible data types.

Explore union and union all to combine queries across tables and manage duplicates. See examples with integer it and character name columns, using order by and aliases.

Explore the intersect and intersect all operators in SQL by creating test tables, inserting data, and identifying common records and duplicates across tables.

Explore the except operator in PostgreSQL by returning rows from the left table not in the right, with and without duplicates using except all.

Explore how aggregate functions operate on groups of data in PostgreSQL to produce a single scalar value, using count, average, sum, min, and max to summarize results.

Explore how PostgreSQL handles aggregate functions sum, average, count, max, and min on numeric and string data, including nulls, aliases, and scalar values.

Group data by a column, such as subject, with the group by clause in a select statement and apply aggregate functions to each group to compute averages in PostgreSQL.

Explains group by syntax within a select statement, how where filters data before grouping, and how to use aggregate functions with having for post-group filtering across one or more columns.

Build complex sql queries step by step by inner and left joining orders and order details, applying filters with like and between, removing duplicates with distinct, and aggregating with group by.

Explore built-in database functions for strings, including upper, lower, and length, and see how to apply them to columns and literals in queries.

Master ltrim, rtrim, btrim, and trim in PostgreSQL to remove leading, trailing, or both sides of strings. See examples with whitespace and characters like hyphens, and verify with length checks.

Master initcap, left, and right string functions in PostgreSQL to transform text, slice characters from the left or right, and handle negative indices for flexible data shaping.

Discover ASCII and chr conversions, and practice concatenating strings with concat and concat_ws in PostgreSQL, using practical examples of converting characters to ASCII, back to characters, and building delimited text.

Explore position and string position functions in PostgreSQL to locate a character or substring in a text field, with examples from the ship_name column and handling multiple or no matches.

Explore the substring function in PostgreSQL to slice strings from a start position for a chosen length, using various syntaxes, including from and for and regex patterns.

Learn how to pad strings with lpad and rpad to a specified length using custom padding characters, and how to extract segments with split_part by a delimiter.

Master string manipulation in PostgreSQL by using reverse to invert text and repeat to duplicate values, with practical examples and length considerations.

Master string functions in PostgreSQL by studying translate and replace, their character-by-character versus substring replacement, with practical examples.

Discover PostgreSQL numeric functions, including absolute value, ceiling, floor, round, truncate, sign, and random to operate on numeric columns. See how these functions handle decimals and integers.