SQL - The Complete Developer's Guide (MySQL, PostgreSQL)

Explore the base syntax and core SQL concepts, and distinguish between defining data and manipulating data, as you begin your SQL journey.

End statements with semicolons when multiple statements appear in one command. SQL keywords are case-insensitive; quote identifiers with double quotes or backticks, and follow the clause order: select, from, where.

Explore the distinction between data definition and data manipulation in SQL, including creating tables and relationships (DDL) and CRUD operations (DML) with joins and data filtering.

Learn SQL concepts applicable to any database, with hands-on examples using MySQL and PostgreSQL. Install these engines on Mac, Linux, and Windows, and note syntax differences and official docs.

Gain a practical overview of installing MySQL on macOS, choosing the community edition, setting up the server, and using the MySQL shell and Workbench alongside Windows setup notes.

Install mysql on windows with the mysql installer, select server and workbench, set the root password, configure the service, and explore the shell and port 3306 in the next lecture.

Install and configure PostgreSQL on Windows by downloading the Windows installer, setting a password for the postgres user, and using pgAdmin and SQL Shell to connect and administer the server.

Learn to set up Visual Studio Code with SQLTools extension to connect to MySQL and PostgreSQL databases, install drivers, configure connections, and run queries with auto completion and syntax highlighting.

Explore data definition statements for creating databases and tables and configuring their data types, with alter database and alter table commands for updates.

Create a new database named talently with the CREATE DATABASE command, using lowercase identifiers and underscores for multiword names. If not exists works in MySQL but not PostgreSQL.

Learn why databases store only primitive data and how to handle files by saving uploaded images to dedicated file storage and storing the file path in the database.

Create the first text column by naming it and using varchar with a defined length. Use quotes or backticks for reserved words and compare varchar to char for padding.

Define the yearly salary column as an integer for performance, since salaries are usually whole numbers, and understand when to use integer, decimal, or float types in MySQL and PostgreSQL.

Learn to use enum data types to constrain a user status field across MySQL and PostgreSQL, defining allowed values and a custom type during table creation.

Learn to insert data into the users table with the insert into command in MySQL and PostgreSQL, using values in column order and validating enum fields like employment status.

Insert Julie Barnes and Michael Smith into the users table, with $25,000 salary and zero salary respectively. Then query all data in MySQL and PostgreSQL to verify results.

Create a conversations table to store username, employer, messages, and a timestamp, with username as varchar, message as text, and date sent as timestamp, compatible with MySQL and PostgreSQL.

Learn how to set default values for columns, using current_timestamp for date_sent to auto insert on row creation. Apply boolean defaults like is_hiring while adjusting existing tables without data loss.

Explore the check constraint to enforce data rules, like ensuring yearly salary is an integer greater than zero (null allowed), with column and table-wide examples in PostgreSQL and MySQL.

Ensure every row has a unique id to reliably identify users and avoid name based updates. Use primary keys with not null and unique constraints, and auto increment for ids.

Learn how to add auto incrementing primary keys to MySQL and PostgreSQL tables, recreate users, employers, and conversations with an ID column, and adapt enum and serial types for database.

Learn how constraints restrict values inserted into a table, including not null, unique, and primary key, with check and foreign key constraints shaping data integrity and table- versus column-level applications.

Understand how text encoding and collation decide which characters can be stored and how they compare in MySQL and PostgreSQL.

Learn how temporary tables use the TEMPORARY keyword to store in-memory results that vanish on restart. Discover creating tables from other tables with AS to pull and persist subset data.

Learn to create databases and tables with create database and create table, define columns, data types, and constraints. Explore updating structures with alter table and PostgreSQL vs MySQL differences.

Practice exercise walks you through creating a shop database with a products table, defining data types, inserting sample data, adding constraints, and setting a primary key with auto increment.

Create and connect to online_shop in MySQL and PostgreSQL; build a products table with name varchar(200), price numeric(10,2), description text, amount_in_stock smallint, image_path varchar(500), and insert dummy data.

Update the products table with not null and check constraints for name, price (>0), and description; ensure stock nonnegative and add an auto-increment id primary key for MySQL and PostgreSQL.

Master data manipulation in sql by performing crud operations—insert, delete, fetch, and filter data—using where clauses and combining complex queries across mysql and postgresql.

Discover CRUD operations in SQL, the core data manipulation actions. Learn to insert data into tables, select and filter results, and apply update and delete commands.

Explore how to insert data in SQL using insert into, with optional column lists, and multiple rows, including inserting from a select query.

Master reading data with the select command, choosing specific columns or all columns from a table, and filtering rows with the where clause.

Learn how to update data with the update command, specifying the target table, using set to assign new values, and applying where to select rows, including multiple-row updates.

Explore how to delete data with the delete from command, using a where clause to target rows, including single and multiple-row deletions, as part of CRUD operations practice.

Practice basic crowd operations on a sales table by setting up the database and table, inserting example data, and performing select queries with filtering, updates, and deletes.

Set up a sales database in MySQL and PostgreSQL, create a sales table with an auto incrementing id, date_created default today, and customer_name, product_name, volume fields.

Download and run the 03-insert-more-data script to seed Postgres and MySQL with 12 rows of dummy data for selecting and filtering. Future lectures cover updating and deleting data.

practice updating and deleting data using the update command, set clauses, and where clauses in SQL, correcting a wrong product name and volume for ID 13.

Execute data deletion with the delete from command, using the where clause to target specific rows in the sales table, as shown with MySQL and PostgreSQL examples.

Master the basics of reading data with sql using select to fetch all columns or specific ones from a table, and understand the result set and filtering with where.

Learn to filter rows with the where clause in SQL, using equality, is null, inequality, greater or smaller, between, and and/or combinations.

Learn to filter sales data with where clauses to find volume greater than 1000 and recurring sales using is true, ensuring consistent results across MySQL and PostgreSQL.

Combine conditions with and/or in sql to filter disputed sales by volume, using is_disputed and volume greater than 5000, with brackets for precise evaluation in MySQL and PostgreSQL.

Filter sales data by date and numeric ranges in SQL using greater than, less than, and the between keyword, with examples for MySQL and PostgreSQL.

Learn to filter text values in SQL using equality, inequality, and greater than or less than comparisons with single-quoted strings, and understand text comparisons are character-by-character under collation.

Sort results with order by to fetch top or bottom sales, apply limit and offset for pagination, and use distinct to remove duplicates when listing customers or products.

Order by volume in descending order to show the top sales, use limit for the top 10, and apply where and offset for the right subset across pages.

Explore subqueries and views by using a subquery to filter a result set, wrap it in a from clause with an alias, and create reusable views in PostgreSQL and MySQL.

Master inserting data with insert into, including multiple rows. Explore updating, deleting, and selecting with conditions, between for dates, ordering, limiting, and distinct values.

Explore data relationships and normalization, mastering one-to-one, one-to-many, and many-to-many relationships. Learn how inner join and left join merge data across tables.

Explore how relational databases relate data across tables using primary and foreign keys, how inner joins link related records, and why we split data across multiple tables.

Data normalization reduces redundancy and improves maintainability by splitting data into multiple tables; starting with separating full names into first and last name columns to clarify storage and queries.

Explore the six forms of data normalization and apply a practical rule to split data across tables with one value per cell and one data entity per table.

Set up a new database named relations, connect to MySQL and PostgreSQL, then create a normalized schema with users and addresses, linking via IDs and optional city and street tables.

Insert related data across cities, addresses, and users in the correct order using insert statements, referencing city_id and address_id, and verify IDs across PostgreSQL and MySQL.

Learn how to merge data from multiple tables with the inner join clause, using on conditions, aliases, and dot notation to create a single result set.

Combine data from users, addresses, and cities using multiple inner joins to bring city names into the result set, and apply aliases to clarify column names.

Master filtering and sorting joined data with inner joins, applying where clauses across users, addresses, and cities to fetch Berlin or New York users, and order results by id.

Learn how the left join returns all rows from the left table and only matched rows from the right table, using addresses and users as examples.

Right join exists, but you rarely use it because a right join can be replaced by a left join with the same results. Prefer left joins for convention and clarity.

Explore inner join and left join concepts using cities, addresses, and users in Postgres and MySQL. See how left joins include all cities and how joins affect the result rows.

Explore the cross join, which pairs every row from one table with every row from another to form a cartesian product, not requiring an on clause and used rarely.

Set up foreign key constraints to enforce referential integrity between users and addresses, using references, on delete cascade, and on update rules to prevent orphaned data and enforce valid relations.

Apply foreign key constraints by updating tables to reference addresses from users, and configure on delete and on update actions (restrict, cascade, set null, set default).

Explore how foreign key constraints enforce referential integrity across tables, demonstrate on delete actions (restrict, no action, cascade) and explain when to reorder tables and how cascades delete related rows.

Explore the three core data relationship types: one-to-many, many-to-many, and one-to-one, and how to model related data across tables while preserving referential integrity.

Explore a comprehensive database example modeling employees, teams, intranet accounts, buildings, and projects, and map one-to-many and many-to-many relationships with tables and foreign keys.

Builds a SQL schema for employees and intranet accounts, with one-to-one relations, foreign keys, and cascade delete. Covers table creation, data types, and insertions in PostgreSQL and MySQL.

Expand the schema by adding teams and buildings, establish one-to-many relations with employees, implement foreign keys and on delete actions, and populate with dummy data.

Explore modeling many-to-many relationships between employees and projects using an intermediate linking table, with foreign keys to employees and projects, cascading deletes, and clear naming conventions like project_employees.

Explore setting up and inserting data for a many-to-many relationship using an intermediate projects_employees table, then practice left and inner joins to merge employees with projects and filter results.

Explore using inner and left joins to merge employees with teams and buildings, then apply where filters to target specific teams or buildings.

Explore advanced key concepts in relational databases, including composite primary keys, various primary and foreign key definitions, and self-referential relationships, with practical examples.

Primary keys don’t have to be auto-incrementing integers; you can use a unique, non-null column such as email as the primary key, though a table has only one primary key.

Explore composite primary keys, which span multiple columns, and weigh using a real key versus a surrogate id in a linking table for many-to-many employee projects.

Explore composite primary keys and the difference from surrogate keys, then implement a composite key using Employee ID and Project ID with a primary key constraint and foreign keys.

Drop and recreate tables in proper order, then implement a composite primary key on project employees with an employee ID foreign key, demonstrating when and why to use composite keys.

Explore self-referential relationships using an employees table with a supervisor id, foreign key constraints, and on delete set null. Learn joining a table with itself to reveal supervisor relationships.

Explore modeling self-referencing many-to-many relationships in SQL using a users table and a linking users_friends table, with a check constraint ensuring smaller IDs in user_id and larger IDs in friend_id.

Explore aggregate functions, the group by keyword, and the differences between having and where; also learn about nested subqueries and window functions in this module.

Explore aggregate functions in SQL, including sum, count, max, min, and average, and learn how these operations reduce multiple values to a single result, with theory and group by keyword.

Explore the COUNT() aggregate function in SQL, counting rows with * or a column, handling nulls, and using DISTINCT to count unique dates across a bookings table.

Learn to use min and max aggregate functions in MySQL and PostgreSQL, apply them to numbers, decimals, and text, and add aliases like max billed and max tipped.

Apply sum on amount billed and avg on num_guests from the bookings table to compute total revenue and average guests, then use round to format decimals.

Explore how group by organizes data for aggregate functions, pairing non-aggregated identifiers with sums and counts to produce per calendar date booking summaries.

Learn to apply group by with joins and aggregates to summarize guest counts by payment method and booking date, using multiple identifiers and optional aggregates.

Apply the having keyword after group by to filter aggregated data from the bookings table, using count and sum, and distinguish it from where filtering.

Explore nested subqueries to compute the minimum total amount_billed on a calendar day by summing amount_billed per booking_date and selecting the smallest daily total.

Discover how window functions in SQL extend aggregate calculations without reducing rows, using the over clause and partition by. See booking data to compute running sums and per-date totals.

Explore window functions in SQL using over, partition by, and order by to compute sums and ranks within partitions, and see how rank orders tips across booking dates.

Explore advanced function concepts in sql, including mathematical, string, and date functions, learn interval usage and differences between mysql and postgresql, and practice with the like keyword and pattern matching.

Explore a gym membership project built as a single table to simplify queries, detailing membership id, start and end dates, last check-in, and consumption revenue while normalization is not applied.

Insert data into the memberships table using string functions, including lower and trim, to normalize input like gender. Compare behavior in MySQL and PostgreSQL and apply length-based filtering.

Learn to use the extract function to pull year, month, and day from dates and timestamps in MySQL and PostgreSQL, using the memberships table's last_check_in as an example.

Explore extracting weekdays from timestamps in PostgreSQL with dow and isodow, and compare to MySQL’s weekday function. Learn splitting date and time with timestamp suffix and preview interval.

Explore adding days, months, and years to dates using interval in PostgreSQL and the date add function in MySQL, with syntax differences and casting to preserve date types.

Learn how the like keyword enables pattern matching in SQL queries, using % and _ wildcards to filter first names, with case sensitivity and ilike differences for PostgreSQL and MySQL.

Use exists with a subquery to efficiently retrieve order IDs for a specific email by linking orders and customers on their keys, and filtering for that email.

Use subquery expressions and the IN operator to list unique emails of customers who placed orders, combining simple filters with subqueries across MySQL and PostgreSQL.

Explore conditional expressions using case when else to categorize amount_billed in the orders table as good day, normal day, or bad day, and extend with sub-queries in MySQL and PostgreSQL.

Build a solution with an outer query around a sub-query, aliasing weekday_numbers and using a case expression to map weekday_number to Monday through Sunday in MySQL and PostgreSQL.

Explore when to perform data checks and transformations in SQL versus at the application level, including whitespace handling, email validation, and case changes, guided by project needs.

Explore database transactions by bundling statements into a single operation. Learn when transactions are useful, how to start them, and how to use rollback, commit, and savepoint in your projects.

Learn to use transactions to ensure both inserts succeed by starting a transaction, inserting into customers and orders, and either committing or rolling back to keep the database consistent.

Master transaction control with start transaction and begin, then rollback or commit across MySQL and PostgreSQL, and learn how implicit commits affect inserts into the customers and orders tables.

Explore using savepoints within a transaction to roll back to a specific point, enabling targeted fixes in the customer and orders inserts before committing.

Compare PostgreSQL and MySQL transactions in VS Code, using begin, commit, and rollback, and learn PostgreSQL’s default all-or-nothing behavior for multi-statement operations.