SQL Bootcamp: Real Practice for Data Careers

Install and configure VSCode, MySQL, and DBeaver on Windows and MacOS, create a LeetCode database, and connect locally with the tools.

Set up a MySQL database for the sql course, create the student, course, and student course tables with primary and foreign keys, and populate the data by running the scripts.

Explore database normalization and 1nf by turning nonatomic data into atomic columns, reducing redundancy, and modeling student enrollment with separate address fields and a single course per row.

Explore second normal form by using functional dependencies to reduce duplication, employing a composite primary key (student id and course) and evolving into a three-table design: student, course, and student_course.

Discover how third normal form eliminates redundancy from transitive dependencies of non-primary key columns by using lookup tables and surrogate keys in a three-table design for students, courses, and enrollments.

Visualize database tables and their relationships with entity relationship diagrams, showing primary keys, foreign keys, and crowfoot notation for one-to-one, one-to-many, and many-to-many cardinalities.

Master the select statement to retrieve data from the student table, specify from which table, use an alias, and star to output all columns or a subset, with a semicolon.

Master the order by clause to arrange query results in ascending or descending order, including multi-column sorting by state and enrolled date, with default ascending and alias options.

Use the where clause in a select statement to filter rows, and manage case sensitivity in MySQL across Windows, Mac OS, and Linux with lowercase naming.

Master the where clause to filter single-column, single-table queries using equal, not equal, greater or less than, and pattern matching with like and wildcards, using and/or logic and proper quoting.

Learn to filter using the like operator with percent and underscore wildcards, finding names starting with a, streets containing pine, and states ending with l, with results sorted by name.

Master advanced filtering with the where clause by combining conditions using and and or, using in and between for student data and date ranges.

Learn to handle null values in SQL where clauses with is null and is not null, and master filtering with in, not in, between, like, and logical operators.

Master a LeetCode SQL challenge by listing customer names not referred by id two, handling nulls, and refining queries with MySQL, DBeaver, and VS Code.

Explore built-in SQL functions for data transformation, including lower/upper, concat, date extraction, coalesce, rounding, and aggregation, with practical examples using student, course, and vehicle data.

Use the limit clause to restrict query results and find top end insights, such as the top three fuel-efficient vehicles by converting miles per gallon to kilometers per liter.

Use the case expression to implement conditional logic and convert state abbreviations into full state names, with an optional else clause and end to handle defaults.

Explore how inner join combines rows from the student, student course, and course tables to show each student with their enrolled courses.

Learn how left outer join lists all rows from the left table and makes right-side data optional, shown as null, unlike inner join that returns only matching rows.

Use a self-join to list courses with prerequisites by joining the course table to itself on prerequisite_id = course_id, returning each course name with its prerequisite name.

Learn how cross join generates the cartesian product by pairing every row from the left table with every row from the right table, creating all student-course combinations; beware large results.

Explore the power of subqueries by nesting an inner query inside an outer query, deduplicate results with distinct, and identify students not enrolled in any course using a two-step approach.

Use subqueries to filter data by calculating the average mpg, then select cars with above-average fuel efficiency.