
Embark on a hands on journey to SQL mastery with practical coding challenges, diagrams, and lifetime access. Learn to use SQL Server, with transferable skills to other SQL systems.
Explore the foundations of SQL by linking the relational model, set theory, and predicate logic to tables' attributes and rows while emphasizing primary keys and set-based operations.
Learn how to write basic and extended select statements to retrieve data from employees, select columns, use aliases with as, and create virtual columns like bonus and total.
Explore SQL Server architecture, including the relational engine and storage engines, and learn to query and manage data using SSMS, with macOS setup via Azure Data Studio and Docker.
Install SQL Server Express and SQL Server Management Studio on Windows, using an administrator account. Download from microsoft.com and note the Sqlexpress instance for the next lesson.
Learn to set up the SAMPLEDB database on Windows by downloading the SAMPLEDB.sql file, executing it in SQL Server Management Studio, and querying the hcm.employees table.
Covers how to download and install SQL Server on a Mac using Docker. Also covers, how to download and install Azure Data Studio and how to create the SAMPLEDB database using Azure Data Studio.
Tackle SQL challenges that reinforce learning by writing queries against course database schemas, and practice creating tables, stored procedures, and views, with hint PDFs, solutions, and alternative approaches.
Tackle three SELECT challenges to practice writing simple queries with hints. Retrieve first and last names from hcm.employees, last_name as customer_last_name from oem.customers, and all columns from oes.order_items, with discussion of pros and cons.
Explore solutions to select challenges, including querying hcm.employees for first and last names in SAMPLEDB, aliasing customer_last_name and city from oes.customers, and evaluating select * versus explicit columns in oes.order_items.
Use the select distinct clause to remove duplicates and return unique values or unique column combinations. Learn how single vs multiple columns and nulls affect results.
Practice the select distinct keyword to extract unique locality values from the bird.antarctic_populations table, then obtain distinct locality and species_id combinations, using a sample database and a sql editor.
Explore solutions to the select distinct challenges by querying the antarctic_populations table in the bird schema to reveal four unique localities and six unique locality and species_id combinations.
Learn how to order rows with the order by clause, use ascending or descending order, and apply a tie-breaker for deterministic results.
Use SQL Server's top to limit results and order by order_date to fetch the most recent orders, using with ties or a tiebreaker such as order_id for determinism.
Explore six order by challenges on the hcm.employees table. Rank by last name, salary high to low, most recent hire date, and department, with ties for lowest salaries.
Explore how to use order by to sort employees by last_name ascending, salary descending, hire_date descending, and department_id, including top with ties.
Explore the where clause and three-valued logic, using nulls to represent unknown data, and learn to filter with is null, =, and <> combined by or.
Replace nulls in queries with is null and coalesce, applying to salary plus bonus and emergency contact data. Compare behavior and data type effects to choose a robust approach.
Explore the where clause by applying comparison operators—equals, not equals (<> and !=), in, less than, greater than, between (inclusive)—demonstrated with price queries.
Practice the where clause to filter data, including products priced over 100 and orders not yet shipped. Learn date filtering with literals like 2020-02-26 and 2020-01-01 to compare dates.
Explore practical where clause solutions, filter products by list_price over 100, identify unshipped orders with shipped_date is null, and query orders by specific dates or after a date.
Learn how collations affect case sensitivity in SQL Server and use like with wildcards to pattern match, inspecting column metadata via information_schema for USB hub and tablet.
Explore pattern matching in sql by using like with underscores and percent signs, learn escaping of special characters with escape, and apply range patterns for digits and letters.
Introduce character data types in SQL Server, including varchar, nvarchar, fixed-length char and nchar, and explain information_schema.columns, maximum and octet lengths, plus explicit versus implicit conversion.
Master pattern matching in SQL with like and percent wildcard. Tackle challenges: countries starting with a letter, earliest gmail customers, product names containing mounts, earliest products ending in a number.
Explore pattern matching in SQL Server through practical challenges. Filter countries starting with n, Gmail addresses, product names containing the word mouse, and product names ending with a number.
Explore aggregate functions in SQL, learn min, max, sum, avg, and count, including count distinct, and see how nulls and distinct values influence results.
Learn to write grouped queries using group by and aggregate functions to compute totals and counts by zone, occupied status, and combinations, handling nulls correctly.
Learn to filter grouped data using the having clause after a group by, applying aggregate functions like count to return groups that meet a predicate.
Learn the logical query processing order in SQL, where from, where, group by, having, and select feed into order by to yield average price by category, with descending order.
tackle five group by challenges in sql server, using count and avg with group by to summarize department totals, warehouse on-hand, and most recent population counts by locality and species.
Explore group by challenges, computing counts per department, averages by department with order by, and sums by warehouse, using having and round for recent population counts by locality and species.
Using the and operator, the query returns only rooms where room style is single and window view is ocean, returning rooms one and three in the hotel example.
learn how the or operator filters hotel rooms by at least one true condition, using room style equals single or room view equals ocean, and see which rows return.
Explore sql operator precedence, mastering how and, or, and parentheses influence query results and how grouping with parentheses changes which rows are returned.
Explore how the in operator filters rows by matching a column against a list of values, including subqueries, with examples on supplier IDs and quoted strings.
Master sql challenges on logical operators, using or, not, and precedence to filter Seattle or Sydney employees by salary over 200k and exclude IDs 1, 2, or 5 in products.
Solve SQL logical operator challenges by filtering employees in Seattle or Sydney, combining conditions with and/or, enforcing precedence with parentheses, and excluding products in categories 1, 2, or 5.
Learn how inner joins connect departments and employees by matching dept_id on both tables, filter out nonmatching rows, and use aliases to simplify queries while selecting specific columns.
Explore how left outer join preserves all rows from the left table and shows unmatched right data as nulls, and compare with right outer join.
Explore full outer join by linking the students and test scores on student id, showing how unmatched rows from either table appear with nulls, unlike inner joins that omit them.
Enforce data integrity in relational databases by applying primary key and foreign key constraints, ensure unique values and referential integrity, and understand one-to-many relationships between parent and child tables.
Model many-to-many relationships using an associative table with a composite primary key of doctor id and patient id, linking doctors and patients in an entity relationship diagram.
Explore SQL join challenges to retrieve employee id, first name, last name, sellery, and department name with inner joins, then outer joins and group by for unassigned departments.
learn to join employees to departments to display department names, using inner, left, and right outer joins, and count employees per department with group by.
Master composite joins by using city and country as a key to correctly link cities and stores, avoiding incorrect matches from single-column joins.
Demonstrate joining more than two tables via a linking table to model many-to-many relationships, using doctors and patients as examples. Explain left outer joins and how to adjust joins.
Learn how predicate placement in on clause vs where clause affects results for inner and outer joins, with practical examples using Indian suppliers and their products.
Explore self-referencing joins by joining the employees table to itself to reveal manager names and titles; learn when to use inner versus outer joins to include or exclude unmatched rows.
Explore cross joins between two unlinked tables, producing a cartesian product of all combinations, using from stores s cross join products p or, alternatively, a comma in from clause.
Develop advanced sql skills through five challenges, including self joins for manager relationships, outer joins to include all employees, and multi-table queries across products, warehouses, stock, departments, jobs, and countries.
Work through advanced sql joins, including self joins, left outer joins, and inner joins, across employees, products, inventory, warehouses, departments, jobs, and countries, to compute totals and filter by Australia.
Use union or union all to combine queries, removing duplicates by default, while ensuring the same column count and compatible data types; column names come from the first statement.
Explore the intersect set operator, which returns distinct rows common to two queries with matching column counts and compatible data types. See an example contrasting customers and Canadian subscribers.
Explore how the except set operator returns distinct rows from the first set not in the second, and how the order affects results.
Learn how SQL set operators are evaluated, noting that intersect precedes union, and that parentheses can force a different left-to-right order.
Tackle set operator challenges using union, intersect, and except across California, Arizona, and Florida bird sightings. Extract unique species by scientific name and state name, with careful handling of nulls.
Apply set operators to combine queries and deduplicate results, using union, union all, intersect, and except across multiple tables, with attention to column order, data types, and expressions.
Explore self-contained subqueries in SQL, using scalar subqueries to fetch the highest priced product and in-clauses to relate products to order details.
Learn how correlated subqueries reference the outer query, perform row-by-row evaluation, and return products with the highest price per category using a correlated subquery.
Learn how the exists operator uses a correlated subquery to return products whose IDs appear in the order details, and compare performance with in for large versus small subquery results.
Learn to use SQL window functions to rank products by category with rank function, partition by category, order by price desc, and filter rank = 1 to find top items.
Learn how to use common table expressions (CTEs) to modularize queries, compare them with derived tables, write with the keyword syntax, and define multiple CTEs before the outer query.
Learn to avoid the not in trap in SQL, using not exists or left join with null checks to identify departments that currently have no employees.
Explore practical subquery techniques in SQL Server, including self-contained and correlated subqueries, derived tables, common table expressions, not in, not exists, and joins, for cheapest products and customer order results.
Explore subquery challenges in SQL Server, including cheapest product by category, derived tables, and common table expressions, with not exists and not in patterns.
Learn how to concatenate first, middle, and last names in SQL Server using the plus operator and concat function, and handle nulls with coalesce or isnull to manage spaces.
Discover how to extract first and last names from full names in SQL Server using charindex, left, and substring. Learn to compute the space position and derive name parts.
Master sql server date and time functions, including getdate, current_timestamp, utc date, datepart, date name, cast, dateadd, and datediff, with practical examples.
Master sql function challenges by plus or concat-based concatenation of first, middle, and last names; extracting genus and species from scientific names; and calculating employee ages and shipping date estimates.
Explore function challenges in SQL Server: concatenate first and last names, handle null middle names, extract genus and species, compute ages, estimate shipping dates, and average shipping days by company.
Master the case expression in SQL using when, then, and else logic to classify customers as domestic or foreign and member or non-member, with both search and simple forms.
Explore case expression challenges in sql server by building case-based columns for discontinued status, price grade, and shipping status, using date functions and counting groups.
Solve case expression challenges in sql using simple and searched forms to derive discontinued descriptions, price grades, and shipping status; demonstrate common table expressions as alternatives.
Explore SQL Server data types, including character, numeric, and date/time categories, plus binary, XML, and geometry, and learn how they govern table creation.
Explore integer data types in SQL Server, including tinyint, smallint, int, and bigint, their value ranges, and how functions like count and division affect types and storage units.
Explore the decimal data type with fixed precision and scale, shown by decimal(8,5) and its three left, five right digits; note decimal equals numeric and storage grows with precision.
Grasp that the float data type is an approximate number, trading precision for storage. Use decimal for monetary values to avoid rounding errors.
Create tables in SQL Server using the create table syntax, defining columns, data types, not null constraints, and primary keys with identity, then add foreign keys.
Master the insert statement to add rows by specifying columns and values, insert multiple rows, and insert data from another table with insert into select.
Create and populate SQL depth and employee tables through hands-on challenges, establishing identity keys, primary and foreign key constraints, and data movement with insert and select statements.
Create a departments table with identity primary key and not null constraints, then use insert into select to copy department names and define a foreign key in employees.
Explore the uniqueidentifier data type, a 16-byte globally unique value used to distinguish records across databases. Learn how NEWID() and defaults generate GUIDs and support safe data merging.
Master the update statement to modify existing data with syntax update table set column = value, with a where clause, and apply expressions like price = price * 1.1.
Learn how to delete rows in SQL using the delete statement, with a where clause to target specific records, and compare it to truncating a table for removing all rows.
Truncate table deletes all rows with a simple syntax and is faster than delete. It resets identity seeds and cannot be used with foreign keys.
Use the drop table statement to remove a table and its constraints with a fully qualified name. If a foreign key exists, drop the constraint or child table first.
Explore transactions in SQL Server, ensuring atomic, consistent, isolated, and durable changes. Learn explicit transactions, begin transaction, commit or rollback, and using the scope identity function to capture new keys.
Stored procedures save SQL code in the database, execute as a logical unit within a transaction, and can accept input and output parameters to reduce network traffic.
Learn to create and test stored procedures in SQL Server Management Studio using input and output parameters, with examples like getting employees by department and filtering customers by street address.
Practice creating and executing stored procedures for inventory and product queries, including get quantity on hand and products with search and max list price, plus transferring funds in bank accounts.
Explore solutions to three stored procedures: get quantity on hand by product and warehouse, fetch current products by name and max price, and transfer funds with a transactional log.
Learn how to use alter table to add, modify, or drop columns, apply not null constraint, rename columns with sp_rename, and manage dependencies while understanding table structure.
Learn three sql challenges on altering tables: add termination date to employees, set customers' first and last names to varchar(60), and rename phone to main phone using built-in rename procedure.
Add a termination_date column to the employees table, change first_name and last_name to nvarchar(60) in customers, and rename Thone to main phone using sp_rename, with dependency cautions.
Learn how unique constraints enforce distinct values in a column or columns, how they differ from primary keys, and how to add them with alter table.
Discover how check constraints enforce column conditions, defined via alter table add constraint, with examples like quantity > 0, color in ('red','blue','yellow','black','white'), and salary >= 0.
Learn how indexes speed up select queries, when to use different index types, and how cardinality and selectivity influence performance, including table scans, index scans, and filtered indexes.
Explore index types in SQL Server, including non-clustered, clustered, unique, and filtered indexes, and learn practical guidelines for designing efficient indexing strategies.
Explore composite indexes and included columns in SQL Server, learning how the leftmost column affects index seeks, when to use include, and how covering indexes optimize queries.
Master sargable queries that leverage indexes for fast execution, avoid manipulating the filter column, and use range conditions and like patterns to maintain efficient searches.
Create nonclustered and unique indexes with included columns, and rewrite queries to use indexes effectively. Explore filtered and composite indexes to optimize date range and predicate filtering in key tables.
Learn practical indexing and sargable query techniques, including creating nonclustered and unique indexes, using include columns, and evaluating execution plans to optimize SQL Server queries.
Create views to expose a subset of columns as a virtual table that hides sensitive data, using create view as select, and query them like tables.
Explore data normalization and why relational databases organize attributes into tables. See how primary keys, foreign keys, and an association table support many-to-many relationships and reduce anomalies.
Identify functional dependencies as relationships where a value or set uniquely determines another attribute, illustrated by student id determining first name and building name plus conference room determining room capacity.
Explore functional dependencies with a hands-on exercise analyzing a four-attribute table, identifying which attributes deterministically map to others, including A to C and A to D, while noting non-deterministic cases.
Identify candidate keys as minimal unique identifiers that functionally determine all attributes, such as Student ID or text number, and distinguish super keys as unique but not necessarily irreducible.
discover how first normal form enforces atomic column values, unique rows, and consistent data types, and how to model one-to-many data with a parent and child tables and foreign keys.
Normalize multi-valued and multi-type fields by splitting values into rows, creating dedicated tables (such as stations), and linking outputs by year with foreign keys for deeper, not wider, design.
Apply second normal form by removing partial dependencies in a supplier parts table, creating a parent table for supply main cities, and enforcing foreign key integrity via the composite key.
Explore third normal form by analyzing functional dependencies and transitive dependencies, and learn to normalize a supplies table using surrogate keys and foreign keys to reduce redundancy.
Explore Boyce Codd normal form, a strict division of third normal form where every determinant is a super key, via functional dependencies in a department room example.
Learn how to write effective and accurate SQL with this course!
You will learn how to read and write complex SQL queries in a relational database (SQL Server). The skills you will learn are also largely applicable to any other major database system, such as MySQL, PostgreSQL, Oracle Database, and much more.
Knowing how to write SQL is one of the fastest ways to reach your career goals. This is because SQL is consistently the most in-demand skills in the tech sector. SQL can seem simple at first but it can quickly become complicated. It is common for people to a write SQL query without realizing they are getting an incorrect result returned. This course is designed with a focus on accuracy and understanding. You will learn how to avoid the common mistakes people make when writing SQL. Not only that.. but you will get a visual guide to the SQL language by seeing how queries work step-by-step. This is a complete course which covers all the core skills you need to master the SQL language.
I believe that the best way to learn SQL is by writing lots of SQL. For that reason, this course includes over 80 coding challenges where you get to write SQL queries and create database objects. These challenges are based on real-world scenarios and are designed for optimal understanding.
In this course you will learn everything you need to master SQL! Including:
Get started with SQL Server and SSMS, two of the world's most popular SQL tools
How to install SQL Server on Windows and Mac-OS computers
Note: that Mac users will need to use Azure Data Studio rather than SSMS
Learn how to do data analysis and data analytics using SQL. Including advanced query techniques
Start by learning the fundamentals of the relational model and SQL language
Analyzing data using aggregate functions with the GROUP BY command
Writing advanced queries with string and date functions
Learn to use logical operators to add logic flow to your SQL queries
Learn how to write both self-contained and correlated sub queries
Learn how to join data by using the different types of JOIN commands
Learn how to do more advanced joins such as joining more than two tables and joining on multiple columns
Learn to create tables and integrity constraints
How to update, insert and delete data
How to create indexes
How to make your queries run faster by making them SARGable
Learn to write advanced SQL such as stored procedures, window functions, and common table expressions
Bonus section on Database Design - includes data normalization up to and including Boyce-Codd Normal Form.
and much, much more!
This course is one that puts you in control. Where you get to write SQL throughout the course, instead of watching someone else code. Every section comes with fresh challenges, modeled after real-world tasks and situations.
This comprehensive course allows you to learn at your own pace through an interactive environment. You will start with the basics and soon find yourself writing advanced SQL queries.