The Ultimate SQL Bootcamp: Zero to Hero in 9 Hours

Learn to create a database in SQL Server via the graphical interface, and understand system databases like master, model, msdb, and tempdb, plus MDF and LDF files.

Learn how to create a database with a SQL query, specify primary data (MDF) and log (LDF) files, set initial size, growth, and max size, and verify with sp_helpdb.

Learn to add comments to sql queries using single-line comments with two dashes and multi-line comments with slash star and star slash, demonstrated in sql server.

Use the alter database command to modify an existing database in SQL Server, such as renaming the student database to student one, with changes reflected after executing.

Learn to delete a database with the drop database command, including required permissions, closing active connections, and the resulting removal of database files such as LDF and MDF.

Explore numeric, date/time, character, binary, and monetary SQL Server data types (bigint, int, decimal, date, time, char, varchar, nchar, binary, money) and how to define table columns.

Create a table in sql server by naming the table and defining columns, data types, and nullability, as shown in the employee details example within the employee database.

Learn to insert rows into a table using the insert command or the UI, manage not null constraints and optional column lists, with examples on the employee details table.

Learn how to define an identity column in SQL, seed it with 1000 and increment by 1, enabling auto-generated employee IDs during table creation and data insertion.

Master the alter table command to modify the structure of an existing table, including adding, dropping, or changing columns, setting defaults, and altering data types.

Drop a table in sql using drop table or the user interface, noting that all data is permanently deleted; check dependencies and drop dependent objects before proceeding.

Use the delete command with a where clause to remove specific rows while preserving the table structure. Filter criteria like where fees >= 1000 deletes only those rows.

Truncate the table to remove all rows quickly while preserving the table structure and constraints, without logging each deleted row. This is faster than delete for large tables.

Create, alter, and drop schemas, with dbo as default, to organize and secure tables, views, stored procedures, and functions across schemas.

Explore how data integrity ensures accurate, consistent, and reliable database data by enforcing entity, domain, referential, and user defined integrity with concrete SQL examples.

enforce data integrity by applying the not null constraint to a column or set of columns, ensuring they cannot contain null values before a row is inserted.

Explore the primary key constraint, enforcing not null and unique values to identify rows and ensure data integrity in SQL Server.

Learn how unique constraints enforce uniqueness on non-primary key columns and allow nulls. Discover how to create, name, test, and drop them across a table.

Understand how foreign key constraints enforce referential integrity between tables, linking employee details to departments via department code, and prevent invalid inserts by referencing a department's primary key.

Learn how check constraints enforce domain integrity by restricting values with in and between, including city lists and age ranges, and how to add constraints via alter table.

Understand how default constraint in SQL Server assigns a value to a column when no value is provided, using India as country default, or alter the table to add it.

Learn to import Excel data into a SQL Server database using the Import Export Wizard, importing all tables from the Excel file into your Superstore database.

Master the select statement to retrieve data from one or more tables in SQL server, provide column names or use the asterisk, and apply aliases with as.

Apply distinct to eliminate duplicate rows and use top to limit rows in your queries. Explore examples on a US superstore dataset, including single- and multi-column distinct values.

Discover how the where clause filters rows in select statements using comparison operators—equals, not equals, greater than, less than, and their inclusive forms—with practical examples like country, state, and quantity.

Learn how to use the where clause and logical operators and, or, not to build complex SQL Server queries with examples using ship mode, segment, and date comparisons.

Master the between and not between range operators in SQL Server within the where clause, including endpoints, and learn to combine them with other conditions and a category filter.

Explore the in and not in list operators in SQL Server to filter a where clause by multiple values, acting like or logic with examples from the orders table.

Discover wildcard pattern matching in SQL Server with the like keyword, using ampersand, underscore, square brackets, and carrot to filter data and match patterns in states and regions.

Use the where clause with is null and is not null to test for null values in SQL columns, and distinguish null from empty strings in data.

Learn how to use the update command to modify single or multiple rows via a where clause, setting columns such as first name, last name, and salary.

Learn how to sort data in SQL server with the order by clause, using single or multiple columns, default ascending, or descending with desc, and apply top n rows.

Explore aggregate functions in SQL Server, including count, sum, average, min, and max. See how count treats nulls and the difference between count star and column, with an employee example.

Master the max and min aggregate functions in SQL Server, applying max and min to salaries, dates, and non-numeric data such as first names, while handling nulls.

Master the group by clause to summarize data by country and calculate averages using aggregate functions like count, sum, average, max, and min in SQL Server.

Filter grouped results by region using the having clause in SQL Server, after grouping by region, to show regions with sum(profit) > $50,000.

Explore key SQL string functions, including length (Len), upper and lower case, left and right trim, substring, replace, left, right, reverse, and concat, with practical table examples.

Learn how to manipulate dates in SQL Server using get date, date add, and date difference; extract year, month, and day with date part and date name, and format dates.

Learn how SQL Server's mathematical functions perform numeric calculations, including abs, ceiling, floor, round, square root, and power, with practical examples.

Master data conversion in SQL Server using cast and convert to change data types, with convert's style parameter for date formatting; learn practical examples like concatenating integer and varchar.

Learn how COALESCE and ISNULL work in SQL Server, returning the first non-null value and replacing nulls with a chosen value, illustrated with first name and employee details examples.

Learn how string_agg in SQL Server 2017 concatenates multiple row values into one string with a delimiter, illustrated by first names and optional where, join, and distinct clauses.

Inner join returns only the rows with matching values in both tables, using a common key such as employee id or order id, enabling row-level mapping across related tables.

Apply the left join to return all rows from the left table and only the matching rows from the right table, with nulls when no match exists.

Learn how to use the right join in SQL to return all rows from the right table and the matching rows from the left, with nulls for nonmatches.

Discover how the full outer join returns all rows from both tables, filling in nulls for unmatched records, and contrasts with left and right joins using examples.

Learn how to perform a self-join by joining the same table twice with aliases, using inner, left, or right joins to relate employees to their managers.

Explore cross join in sql to produce the cartesian product of two tables, joining every row from the first with every row from the second, without a join condition.

Explore the order of execution for SQL queries, from clause to top, using an employee table example to illustrate how where, group by, having, and select shape the result.

Explore the order of execution in SQL by joining orders and people, filtering for consumer segment, and selecting the top two regions by order counts with regional managers.

Use the simple case expression in SQL Server to implement conditional logic in queries, creating computed columns like stock status and country codes with the when-then-else-end syntax.

Learn how to use a searched case expression in SQL, evaluating multiple when conditions to return the first matching result, with examples for stock levels and salary bucketing.

Learn to declare variables in sql server, assign values with set, and understand their scope in a script or batch. Use print for debugging and display variables with select.

Learn how the if else statement in SQL Server enables conditional execution with begin and end blocks, else if, and else branches, using simple numeric examples.

Explore set operators in SQL Server, focusing on union and union all; learn how union merges results from multiple selects with duplicates removed, while union all preserves duplicates.

Explore set operators in SQL: intersect returns rows common to multiple selects, while except returns distinct rows from the left select not in the right.

Create and use views in SQL Server to turn queries into virtual tables that do not store data, enabling secure, reusable access to specific columns and joined results.

Learn to alter views to add columns, create or alter views to update queries, and join or drop views as you would join tables to fetch employee and department data.