MySQL Bootcamp: Build Databases for Real-World Scenarios

Explore data types in MySQL, including string, numeric, date and time, boolean, and large objects, and learn how constraints validate column data for tables like an employee record.

Explore the string data type and its subtypes—char, varchar, and enum—and learn how char uses fixed-length memory with a mandatory size, quotes, and possible wastage.

Learn how varchar provides variable length storage in MySQL, its memory benefits over char, and when to use it for fixed versus variable length data.

Explore enum string types to restrict column values to predefined options, and learn numeric types from tinyint to bigint and float, double, and decimal, covering signed and unsigned ranges.

Explore MySQL date and time data types, including date, date time, timestamp, time, and year, with default formats and specific year ranges.

Explore large objects in MySQL, storing binary data such as images, audio, and video with tiny blob through long blob types, supporting up to 8 tb to 128 tb.

This lecture introduces the five SQL statement types—DDL, DML, TCL, DCL, and DQL—explaining how they define structure, manipulate data, control transactions, manage security, and query data in MySQL.

Learn the overview of DQL statements in MySQL, including select, projection, selection, and joins, with arithmetic, comparison, and logical operators, and the order by and limit clauses.

Study the DQL select clause, its role in projection of data into the result table, and how to apply select from where group by having in MySQL Workbench.

Explore sql projection by selecting columns with syntax rules, understand when to use asterisk, distinct, or expressions, and apply aliases in from and select clauses using a sample employee table.

Explore sql operators across arithmetic, comparison, relational, logical, and special operators (not in, is not, between, like) and subquery operators (any, exists, not exists) in expressions of operands.

Learn to apply logical operators in SQL where clauses, using and, or, and not to combine conditions, compare salaries, departments, and jobs, and group them with parentheses for precise results.

Explore how to use special operators in MySQL, including in and not in for multi-valued comparisons, and the is operator for null checks, with department, job, and commission examples.

Understand the is not operator in SQL by querying for non-null commission values, using 'commission is not null' to filter records and verify the results.

Master range queries in MySQL by displaying employee records with salaries between 1250 and 3000, including 1250 and 3000 using >= and <= comparisons.

Learn how to use the between and not between operators in MySQL to filter ranges, including numeric values and date ranges, with correct syntax and inclusive endpoints.

Learn the like operator in MySQL, using percentile and underscore wildcards to match patterns such as employee names starting with a or ending with s, including not like.

Sort results with order by in ascending or descending order; it runs last by default ascending. Use limit with offset and distinct to fetch top results, the third maximum salary.

Explore the limit clause in MySQL to fetch number of records, sample top n salaries, and use offset with distinct and order by to identify third or fourth maximum values.

Explore MySQL case statements to add conditional logic in queries, manage null values with ifnull and coalesce, and generate a salary status column.

Learn how null values disrupt calculations in MySQL and how the ifnull function handles them, while understanding why null differs from zero or spaces in salary totals.

learn how to handle null values in MySQL with the ifnull function, substituting an alternative value for null expressions to compute total salary from salary and commission.

Learn how the coalesce function in mysql evaluates multiple arguments to return the first non-null value, unlike ifnull which accepts only two. See practical examples where 100 plus coalesce(null, 10) yields 110 and coalesce(null, null, 20) yields 120, illustrating that coalesce can take any number of arguments.

Explore SQL functions, including built-in single row and multi row (aggregate) functions, with group by and having clauses.

Explore multi-row and aggregate functions in sql, including max, min, average, sum, and count, with practical queries on the employee table in Scott's database.

Understand that aggregate functions take only one argument, a column or expression. Ignore nulls; don’t use aggregates in the where clause; count accepts an asterisk.

Master multirow functions in mysql, using max, sum, min, and count on the emp table, and use group by to get per-department counts and manager-related queries.

Explore how the group by clause groups records, use aggregate and group expressions, and apply optional where and having clauses to count and categorize data by department or job.

Learn how the having clause filters grouped results after group by, with examples counting employees per department, and compare it to where for non-aggregate filtering.

Explore single row functions in MySQL, including string, numeric, and date functions; master upper, lower, concat, length, and reverse, and apply nested and space-delimited concat.

Explore MySQL constraints including unique, not null, check, default, primary key, and foreign key, and learn how column-level and table-level constraints differ.

Learn how unique constraints prevent duplicate values in a column or across multiple columns, illustrated with an employee table in MySQL Workbench, demonstrating column-level and table-level constraints.

Learn how the not null constraint enforces mandatory values for individual columns like employee_id and employee name, distinguishes null from zero and blank, and modify constraints with alter table.

Learn to define and apply check constraints for column and table level validation, using patterns like start with and like operator to enforce employee id and phone number checks.

Default constraints automatically fill a column when data is missing during insert. The employee table example shows defaulting the phone number to zero and inserting without that value.

Identify the primary key as the constraint that uniquely identifies each row and prevents null values. Choose between a single-column or composite table-level primary key, with one per table.

Learn how a foreign key creates a referential constraint between child and parent tables, linking employee to department via a common column and distinguishing primary keys from foreign keys.

Identify how a primary key uniquely identifies records within a table. Describe how a foreign key links tables, may be null, and allows duplicates.

Learn how to combine data from multiple tables with MySQL joins, including cartesian (cross), inner, outer (left, right, full), self, and natural joins. Explore related set operators.

Explore cartesian cross joins in MySQL, learn how they produce a cartesian product and why they yield incorrect records, and see how inner join remedies the issue.

This lecture covers inner join (equi join) to return only matched records by joining two tables on a common column using on and aliases, and contrasts it with outer joins.

Master outer joins, including left, right, and full types, to return unmatched and matched records using examples with employee and department tables and join conditions.

Explore set operators like union, union all, intersect, and minus, and see how they merge results vertically, unlike joins that merge horizontally, with MySQL full outer join via set operators.

Explore how union merges two select results and returns distinct rows, while union all preserves duplicates; compare with intersect as the third set operator introduced in the lecture.

Intersect combines two select statements to return only the common records. Except returns records from the first query not present in the second, and MySQL does not support these operators.

Discover how to simulate a full outer join by combining left and right joins with union to include unmatched and matched records without duplicates.

Explain self join by pairing a single table with itself to show each employee beside their manager, using aliases e1 and e2 and a join on e1.mgr = e2.employee_number.

Natural join handles two tables with unknown common columns, acting as an inner join when a common column exists and as a cross join when none does.

Explore data definition language (ddl) in sql, mastering create, drop, alter, truncate, rename, and comment commands to define and modify the database structure.

Learn to use the DDL create command to construct databases and tables, show databases, use a database, show tables, and describe the table structure.

Explore DDL alter and rename operations by renaming tables, adding and dropping columns, renaming columns, and modifying data types and null constraints to shape table structures.

Truncate removes all rows from a table while keeping its structure, whereas drop deletes the table (and can remove the entire database) along with its objects, both are ddl statements.

Explain how mysql comments clarify sql statements and prevent execution. Identify single line comments with two hyphens and a space, and multiline comments with /* ... */.

Explore data manipulation language (DML) in MySQL, including insert, update, and delete, and TCL commands like commit, rollback, and save point, with hands-on examples and a contrast with DDL.

Learn how TCL manages insertion, updation, and deletion as transactions, control auto commit, and use commit and rollback to save or undo changes in MySQL workbench.

Learn how TCL commit saves all transaction changes permanently, how rollback undoes uncommitted work, and how savepoints bookmark positions to rollback partial transactions in MySQL.

Explore data control language (dcl) and how grant and revoke manage rights to control database access. Cover views and indexes, describe dcl's implicit commit, and note dbas usage.

Explore how data control language grants privileges to connect to a database, create tables, and access stored procedures, with auto commit ensuring immediate saves.

Learn how to grant specific privileges in MySQL using DCL. The lecture covers grant syntax, object types (table, view, procedure, function), single and multi-privilege grants, and limits on multi-object grants.

Explore dcl grant and revoke syntax, including from and to clauses, as dbas assign column-specific privileges on an employee table (ename and salary) to user two.

Learn how SQL views in MySQL act as virtual tables that do not store data themselves but fetch data from base tables, enabling restricted access by selecting columns and rows.

Differentiate views from a single table with no group by, order by, group functions, joins, or subqueries, from views built with these clauses or multiple tables; learn DML access differences.

Learn how MySQL indexes speed data retrieval, differentiate clustered and non-clustered indexes, and apply single or composite indexes, with explain demonstrations on an employee table.

Learn how to enforce value uniqueness in MySQL using unique indexes, including creating unique indexes on existing tables, supporting multiple and composite columns, and distinguishing from primary keys.