SQL for Absolute Beginner - With Portfolio Projects

Install PostgreSQL and its pgadmin and psql tools, learn SQL basics, and follow step-by-step setup—from download to default port and superuser password—for the chosen DBMS.

Explore the PostgreSQL GUI with PgAdmin to create databases and tables via GUI or SQL, manage schemas, and define data types, primary keys, not null constraints, and varchar fields.

Learn to create PostgreSQL tables with sql via the gui query tool, including create table syntax and constraints like primary key, unique, not null, varchar lengths, and timestamps.

Explore how a composite primary key combines user_id and row_id to uniquely identify rows in a many-to-many relationship, and how foreign keys link accounts and rows using lower-case names.

Learn to import csv files into postgres via pgadmin and fix data types with alter table, using both gui import/export and sql copy methods.

Import csv files to pgAdmin II using sql copy commands to populate pizza types, orders, and order details, verify via select, and prepare for mastering the select statement.

Master simple SQL select statements by choosing columns from tables, using order by to sort results, and applying aliasing, star selects, and comments for readable queries.

Learn how to use distinct to remove duplicates in select queries, compare with non-distinct results, and apply aliasing when selecting categories from pizza types.

Explore aggregate functions in sql, including average, count, max, min, and sum, and learn to group by id, filter with having, and use order by and where clauses.

Learn to write subqueries in the select clause using a pizza sales database, including inner queries and the average price computation. Explore three subquery scenarios and related operators.

Learn how to apply subqueries after the from keyword, enabling the main query to use the inner query results, and see an example computing the average price of pizzas with IDs starting with T.

Use subqueries in the where clause to filter pizzas by the average price and apply the in operator for subquery results, with examples from pizzas and order details.

Build a university database with lecturer and student tables, then use any and all in subqueries to compare ages; explore differences and complete pizza sales tasks.

Learn how joins combine columns from multiple tables using the on condition, explore inner, left, right, outer, cross, natural, and self joins, and extend to unions.

Explore inner join basics: return only matching rows from two tables using on, with an example joining an order table to a client table to show customer names and locations.

Explore left join (left outer join) and see how it returns all rows from the left table, with matching right values or nulls, including joining orders with clients and products.

Explore right join as the inverse of left join. Return all right-table records with nulls for nonmatches; use orders and products to calculate profit from selling price minus cost price.

Master full join concepts, including full outer join, by comparing them with left and right joins across two tables.

Explore union and union all to combine two select results, enforce same data types and order, and see distinct values versus all records in pgAdmin with practical examples.

Learn how to clean dirty data by trimming spaces and manipulating strings using the concat function to join first and last names, create full names, and format data for analysis.

Explore sql's upper and lower functions to convert strings to uppercase or lowercase, demonstrated on first and last names using a string manipulation dataset in Postgres.

Use the replace function to search a column for a string and replace it with a new text. See syntax replace(source, old_text, new_text, table) and examples like updating a surname.

Determine the number of characters in a string column with the length function, noting that spaces count toward the total and that whitespace removal is covered in the next video.

Learn how ltrim, rtrim, and btrim remove spaces from char, varchar, and text data, with practical Postgres examples trimming the gender column and comparing left, right, and both trims.

Master substring, left, and right functions to extract string parts in SQL. Use practical examples to pull city and country from addresses and clean punctuation with replace.

Learn how to work with dates in Postgres, including current date, intervals, formatting with to_char, and calculating age from birth date or duration since hire date.

Master date functions in PostgreSQL with the age function to compute age and tenure. Use extract to pull year and month from birth and hire dates.

Learn to create and drop views in PostgreSQL, where a view is a virtual table of a query result. Save a complex pizza-order query as a view for easy reuse.

Master sql case statements to build conditional logic, using case, when, then, else, and end, with examples showing expensive categories and text checks in pgadmin.

Learn to use the sql shell with PostgreSQL to connect to databases, view tables, and run queries like select * from pizzas.

Apply Postgres skills to the Maven Toys capstone project, analyzing profit by category across stores, inventory value and turnover, and stock-out losses with three focused queries.