The Complete SQL Course - Learn by Doing - SQL Server

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.

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.

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.

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 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Explore index types in SQL Server, including non-clustered, clustered, unique, and filtered indexes, and learn practical guidelines for designing efficient indexing strategies.

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 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.

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.

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.