SQL Full Course for free 🐬

This section introduces SQL as the Structured Query Language used to create, retrieve, update, and delete data from a database. It explains the concept of relational versus non-relational databases, with SQL primarily used for relational databases that organize data in tables similar to Excel spreadsheets. The role of Database Management Systems (DBMS) like MySQL is highlighted as a workspace for writing SQL statements, noting that while syntax may vary, the core concepts remain consistent across different DBMS.

This part provides a step-by-step guide on downloading and installing MySQL Workbench on Windows. It instructs users to visit mysql.com, navigate to 'MySQL Community Downloads,' and select the 'MySQL Installer for Windows.' The 'Custom' setup type is recommended to install both the MySQL Server and MySQL Workbench. Configuration details like setting the root password and initiating the workbench after setup are also covered, along with instructions for establishing a local instance connection.

This segment guides Mac OS users through the installation of MySQL. Similar to the Windows guide, it directs users to mysql.com, to download the MySQL server and workbench as DMG archives. It includes instructions for installing the server, setting a password, and manually starting the server through 'System Preferences.' Finally, it details how to open the MySQL Workbench, access the local instance, and create a new connection if needed.

This section explains how to manage databases in MySQL. It defines a database as a container for tables and demonstrates how to create a database using `CREATE DATABASE database_name;`. It also shows how to select a database for use with `USE database_name;` and how to delete one with `DROP DATABASE database_name;`. Additionally, it covers `ALTER DATABASE` statements to set a database to read-only mode (`READ ONLY = 1`) or disable it (`READ ONLY = 0`), emphasizing that read-only mode prevents modifications.

This part focuses on managing tables within a database. It teaches how to create a table with `CREATE TABLE table_name (column_name data_type, ...);`, defining columns and their respective data types (e.g., `INT`, `VARCHAR(50)`, `DECIMAL(5,2)`, `DATE`). The section also covers renaming tables using `RENAME TABLE old_name TO new_name;`, adding new columns with `ALTER TABLE table_name ADD column_name data_type;`, renaming columns with `ALTER TABLE table_name RENAME COLUMN old_name TO new_name;`, modifying column data types with `ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;`, reordering columns (`AFTER column_name` or `FIRST`), and dropping columns with `ALTER TABLE table_name DROP COLUMN column_name;`.

This segment demonstrates how to add data to tables. It begins with inserting a single row using `INSERT INTO table_name VALUES (value1, value2, ...);`, highlighting the importance of matching data types and using quotes for `VARCHAR` and `DATE` fields. It then shows how to insert multiple rows efficiently by listing several sets of values in a single `INSERT` statement. The video also explains how to insert data into specific columns by listing the column names after `table_name` in the `INSERT` statement, allowing for omission of certain columns which will then default to `NULL` if no `NOT NULL` constraint is applied.

This section covers selecting and querying data. It starts with `SELECT * FROM table_name;` to retrieve all columns and rows. It then explains how to select specific columns, such as `SELECT first_name, last_name FROM employees;`, and adjust the order of displayed columns. The `WHERE` clause is introduced for filtering rows based on conditions (`WHERE column = value`), using comparison operators like `=`, `>`, `<`, `>=`, `<=`, and `!=`. It also demonstrates finding null values with `WHERE column IS NULL` and non-null values with `WHERE column IS NOT NULL`.

This part explains how to modify and remove existing data. It shows how to update specific rows using `UPDATE table_name SET column = value WHERE condition;`, including updating multiple columns in one statement. It addresses setting a column to `NULL` and warns against `UPDATE` statements without a `WHERE` clause, as they affect all rows. For deleting data, it covers `DELETE FROM table_name WHERE condition;` and critically warns against `DELETE FROM table_name;` without a `WHERE` clause, which would erase all records in the table.

This topic explains transaction control in MySQL. It introduces `AUTOCOMMIT`, which is `ON` by default, saving transactions automatically. To manually control transactions, `SET AUTOCOMMIT = OFF;` is used. With `AUTOCOMMIT` off, `COMMIT;` creates a save point and permanently saves changes, while `ROLLBACK;` undoes all unsaved changes since the last `COMMIT;` or the beginning of the transaction. This is demonstrated with a scenario where all rows are accidentally deleted and then restored using `ROLLBACK;`.

A brief overview of retrieving current date and time in MySQL. It introduces `CURRENT_DATE()`, `CURRENT_TIME()`, and `NOW()` functions. These functions are useful for timestamping events, such as when a record was created or updated. The example demonstrates creating a temporary table with `DATE`, `TIME`, and `DATETIME` columns and inserting current values using these functions. It also shows basic arithmetic operations with dates and times, such as adding or subtracting days, and how to drop a temporary table.

This section covers the `UNIQUE` constraint, which ensures all values in a specified column are distinct. It shows how to apply this constraint during table creation by adding `UNIQUE` after the data type (`CREATE TABLE products (product_name VARCHAR(25) UNIQUE, ...);`). It also demonstrates how to add the `UNIQUE` constraint to an existing table using `ALTER TABLE table_name ADD CONSTRAINT UNIQUE (column_name);`. The consequences of violating this constraint (e.g., trying to insert duplicate values) are illustrated.

This segment explains the `NOT NULL` constraint, which prohibits null values in a column. It shows how to apply it during table creation (`CREATE TABLE products (price DECIMAL(4,2) NOT NULL, ...);`) and how to add it to an existing table using `ALTER TABLE table_name MODIFY COLUMN column_name data_type NOT NULL;`. The video demonstrates an error when attempting to insert a null value into a column with this constraint, highlighting its purpose in ensuring data completeness.

This part delves into the `CHECK` constraint, which limits the range of values that can be placed in a column. An example is given for ensuring an `hourly_pay` column is greater than or equal to a minimum wage. It shows how to add this constraint during table creation (`CREATE TABLE employees (... CHECK (hourly_pay >= 10));`) and to an existing table (`ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (column_condition);`). The importance of naming the constraint for easier management and an example of violating the constraint are provided.

This section explains the `DEFAULT` constraint, which assigns a default value to a column when no value is explicitly provided during an `INSERT` operation. It demonstrates setting a default price of 0 for free items in a `products` table (`CREATE TABLE products (... price DECIMAL(4,2) DEFAULT 0);`). It also covers modifying an existing table to include a default constraint (`ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT value;`). A practical example is shown for automatic timestamping of transactions using `DEFAULT NOW()`.

This topic clarifies the `PRIMARY KEY` constraint, which ensures all values in a column are both unique and not null, serving as a unique identifier for each row. It emphasizes that a table can only have one primary key. The video demonstrates creating a table with a primary key (`CREATE TABLE transactions (transaction_id INT PRIMARY KEY, ...);`) and shows how attempts to insert duplicate or null values into a `PRIMARY KEY` column will fail. It highlights the utility of primary keys for efficient data retrieval and referencing.

This section introduces `AUTO_INCREMENT`, an attribute applied to a key column (typically a `PRIMARY KEY`) that automatically generates a unique number for each new row, incrementing by one by default. It shows how to create a table with an `AUTO_INCREMENT` primary key (`CREATE TABLE transactions (transaction_id INT PRIMARY KEY AUTO_INCREMENT, ...);`). The video also demonstrates how to set a different starting value for the `AUTO_INCREMENT` sequence using `ALTER TABLE table_name AUTO_INCREMENT = start_value;`.

This topic explains the `FOREIGN KEY` constraint, which establishes a link between two tables by referencing the primary key of another table. It demonstrates how a `customer_id` in a `transactions` table can link to the `customer_id` in a `customers` table. The video shows adding a foreign key during table creation (`FOREIGN KEY (column) REFERENCES parent_table(parent_column)`) and to an existing table (`ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column) REFERENCES parent_table(parent_column);`). A key benefit is preventing actions that would destroy the link, such as deleting a parent row referenced by a foreign key.

This segment covers SQL `JOIN` clauses, used to combine rows from two or more tables based on a related column. It introduces three types: `INNER JOIN` (returns rows when there is a match in both tables), `LEFT JOIN` (returns all rows from the left table, and the matched rows from the right table), and `RIGHT JOIN` (returns all rows from the right table, and the matched rows from the left table). Practical examples are provided using `transactions` and `customers` tables, demonstrating how to retrieve combined information and select specific columns.

This part explores various SQL functions. It introduces aggregate functions like `COUNT()`, `MAX()`, `MIN()`, `AVG()`, and `SUM()`, used to perform calculations on a set of rows and return a single value. These are demonstrated with the `transactions` table to find total transactions, highest/lowest/average amounts, and total sum. The `CONCAT()` string function is also covered, showing how to combine multiple string values (e.g., first name and last name) into a single column with an optional alias (`AS full_name`). This section emphasizes that these are just a few examples of the many functions available in MySQL.

This section explains logical operators used to combine multiple conditions in a `WHERE` clause. It covers `AND` (both conditions must be true), `OR` (at least one condition must be true), and `NOT` (reverses the condition). Examples include finding employees based on job title and hire date using `AND`, and finding cooks or cashiers using `OR`. It also introduces `BETWEEN` (for values within a range) and `IN` (for values matching a list), showing their use for clearer and more efficient querying of data within specific criteria.

This topic explains SQL wildcard characters: `%` (represents any number of characters) and `_` (represents a single character). Used with the `LIKE` operator in the `WHERE` clause, they help search for patterns in strings. Examples include finding first names starting with 'S' (`'S%'`), last names ending with 'r' (`'%r'`), and jobs with specific character patterns (`'__ok'`). The practical application of combining these wildcards for complex pattern matching is also demonstrated.

This segment focuses on the `ORDER BY` clause, which sorts the result set of a query based on one or more columns. It demonstrates sorting in `ASC` (ascending, default) or `DESC` (descending) order using examples like ordering employees by last name or hire date. It also explains how to sort by multiple columns, where a secondary sort key is used if values in the primary sort key are identical (e.g., `ORDER BY amount, customer_id`).

This section covers the `LIMIT` clause, used to restrict the number of rows returned by a query. It's particularly useful for handling large datasets and implementing pagination. Examples show retrieving the first `N` records (`LIMIT N;`) and using `LIMIT` in conjunction with `ORDER BY`. The concept of an offset is introduced (`LIMIT offset, count;`), which skips a certain number of rows before returning the specified count, useful for displaying data on different pages.

This topic explains the `UNION` operator, which combines the result sets of two or more `SELECT` statements into a single result set. A crucial rule is highlighted: all `SELECT` statements within the `UNION` must have the same number of columns and compatible data types. Examples include combining income and expenses, and listing all employees and customers. `UNION ALL` is also introduced, which includes duplicate rows (unlike `UNION`, which removes them), demonstrating a practical scenario for its use.

This segment explains `SELF JOINs`, which involve joining a table with itself to compare rows within the same table or display hierarchical data. The key is to use aliases for the table to differentiate between the two instances. Examples include identifying customers referred by other customers and establishing supervisor-subordinate relationships within an `employees` table. The use of `INNER JOIN` and `LEFT JOIN` in `SELF JOIN` scenarios is demonstrated, along with selecting and concatenating relevant columns for clear output.

This topic covers `VIEWS`, which are virtual tables based on the result set of an SQL query. Views do not store data themselves but act as a window into one or more underlying real tables. They are always up-to-date with changes in the base tables. The video demonstrates creating a view for an employee attendance sheet (`CREATE VIEW view_name AS SELECT column1, column2 FROM table;`) and a customer emails list, showing how views can be queried and ordered like real tables. Benefits include data simplification, security, and reduced data redundancy.

This section explains `INDEXES`, data structures used to speed up data retrieval operations on database tables. It clarifies that indexes are useful for frequently queried columns but can slow down update operations. The `SHOW INDEXES FROM table_name;` command is introduced. The video demonstrates creating a single-column index (`CREATE INDEX index_name ON table_name (column);`) and a multi-column index (`CREATE INDEX index_name ON table_name (column1, column2);`), emphasizing the importance of column order for multi-column indexes (leftmost prefix rule). It also shows how to drop an index using `ALTER TABLE table_name DROP INDEX index_name;`.

This topic details `SUBQUERIES` (nested queries), which are queries embedded within another SQL query. They can return a single value or a set of values, which are then used by the outer query. Examples include comparing each employee's hourly pay to the average hourly pay of all employees, and finding customers who have placed orders versus those who haven't. The demonstrations involve calculating an aggregate value (average) in a subquery and using the `IN` and `NOT IN` operators to filter results based on a set returned by a subquery.

This section explains the `GROUP BY` clause, which aggregates rows that have the same values in specified columns into summary rows. It's often used with aggregate functions like `SUM()`, `AVG()`, `MAX()`, `MIN()`, and `COUNT()`. Examples include calculating total sales per order date or total spending per customer. The video also clarifies the use of `HAVING` instead of `WHERE` when filtering on aggregate results after a `GROUP BY` clause (e.g., finding customers who visited more than once). This distinction is crucial for correct aggregate filtering.

This topic covers the `ROLLUP` clause, an extension of `GROUP BY` that generates super-aggregate rows, typically including a grand total. It demonstrates how to add `WITH ROLLUP` after the `GROUP BY` clause to see summary data alongside detailed group data (e.g., sum of amounts per order date, plus a grand total of all amounts). Examples also show `ROLLUP` with `COUNT()` for total orders per customer and total hourly pay for all employees, highlighting its utility in accounting and comprehensive reporting.

This section explains the `ON DELETE` clause, which defines actions to take when a referenced row (parent row) is deleted. Two main options are covered: `ON DELETE SET NULL` (sets the foreign key column to `NULL` in the child table if the referenced parent row is deleted) and `ON DELETE CASCADE` (deletes the entire child row if the referenced parent row is deleted). The video demonstrates dropping and re-adding foreign key constraints with these clauses to show their effects on related data when a customer (parent row) is deleted.

This topic introduces `STORED PROCEDURES`, which are pre-compiled SQL code saved in the database, allowing for reuse. They are beneficial for reducing network traffic, increasing performance, and enhancing security. The process of creating a stored procedure involves `DELIMITER` (to redefine the statement terminator), `CREATE PROCEDURE procedure_name (...) BEGIN ... END;`, and then reverting the `DELIMITER`. Examples show creating a simple procedure to select data and more complex ones that accept parameters (e.g., `IN` parameters for customer ID, first name, last name) to retrieve specific records. The `CALL` statement is used to execute a stored procedure, and `DROP PROCEDURE` removes it.

This final section covers `TRIGGERS`, which are special stored procedures that automatically execute (or 'fire') when a specific event (like `INSERT`, `UPDATE`, or `DELETE`) occurs on a table. Triggers are useful for data validation, error handling, and auditing. The video demonstrates creating `BEFORE UPDATE` and `BEFORE INSERT` triggers to automatically calculate and update an employee's salary when their hourly pay changes or a new employee is added. It also shows `AFTER DELETE` and `AFTER INSERT`/`UPDATE` triggers to manage an `expenses` table based on changes in employee salaries, illustrating how triggers can maintain data consistency across related tables.