SQL Masterclass: From Zero to Hero with SQL Server (2024)

Learn to create and delete a database using sql server management studio, including create database and drop database statements and ui methods.

Learn how to create a table in SQL Server by defining columns and data types, switch databases with use, and manage tables with drop and UI-based creation.

Learn to design string data types for a market management product table, using char for fixed three-character categories, and varchar or nvarchar for variable-length names, descriptions, and Unicode support.

Learn how to design numeric columns in a product table by comparing float, real, and decimal, balancing weight precision and price accuracy for reliable SQL Server data.

Learn how to implement SQL Server date, time, and datetime data types by creating a launch date, launch time, and expiry date columns, and validating input using management studio scripts.

Apply the not null constraint after a column’s data type to enforce non-null values for key fields like product name and weight in SQL Server.

Learn how the unique column constraint enforces distinct values in a table, using real examples with a products table and SQL Management Studio to prevent duplicate names.

Learn how to apply the check column constraint to ensure weight, price, or quantity are nonnegative in SQL Server, using check (weight >= 0) and the syntax with brackets.

Learn how primary key column constraint uniquely identifies a row, enforces not null, uses identity to increment, and creates a clustered index to boost performance with product_id in SQL Server.

Learn to modify existing tables in SQL Server using sp_help and sp_rename, and alter table statements to add, drop, or change columns and enforce constraints.

Add a quantity column to the products table with a data type and constraints, remove the category column, and create categories and orders tables with auto-increment keys, order_date, and customer_email_address.

Alter table add quantity to products (int or bigint) with optional check constraint; drop column category; create categories and orders tables with identity keys and not null constraints.

Establish a one-to-many relationship by linking products to categories via a category_id foreign key. Enforce not null, populate data, and verify referential integrity in SQL Server Management Studio.

Enforce data integrity by adding a foreign key constraint from products.category_id to categories.category_id. Validate references, observe constraint name fk_product_category, and prevent invalid inserts.

Explore many-to-many relationships by using the registration table between courses and students and the order lines table between orders and products, implemented with foreign keys in SQL Server.

Identify relationships among posts and comments, books and authors, departments and employees, and movies and actors, then design blocks and movies databases with tables and many-to-many mapping tables.

Learn to identify one-to-many and many-to-many relationships, from posts and comments to blocks database and movies database with actors, and walk through creating SQL Server databases with relational tables.

Learn how to use the SQL insert statement to add single and multiple rows, specify or omit columns, handle null values, and manage identity columns in SQL Server.

Learn how to use the update statement in SQL Server, including where clause filtering, updating single or multiple columns, and handling swaps safely with examples using products data.

Master the delete from table where clause to safely target rows in SQL Server, avoid accidental full table deletions, and see results in SQL Management Studio.

Learn how the truncate table statement in SQL Server deletes data and resets the identity, compared to delete, with a live demo in SQL Server Management Studio.

Practice creating a company database with a sales table for assignment 3, inserting records, and updating and deleting rows based on conditions. The next video will provide the instructor's answers.

Execute a SQL server assignment in SQL Management Studio: create a company database and sales table, insert ten records, boost low performers by ten, then delete those under 50.

Learn to use the select statement to manipulate columns: select all or specific columns, apply aliases, add a report date, and create calculated columns like total price (price times quantity).

Explore filtering data with the where clause using mathematical operators—equal, less than, greater than, not equal, and their application to numbers, strings, and dates.

Learn to use and or operators in the select statement to combine conditions in the where clause, with brackets for precedence and examples on launch date, price, quantity, and weight.

Explore wildcards in SQL Server with the like operator to filter description, using percent for any sequence and underscore for a single character to match starts, ends, and contains.

Learn how to use the is and is not operators to filter null values in select statements, price is null or price is not null.

Learn how to use the distinct keyword in SQL Server to deduplicate data and return unique values from one or more columns, with real-world examples on country and last name.

Learn how to sort query results using the order by clause, including ascending and descending options, multiple columns, and the correct placement after the where clause.

Learn how inner join connects categories and products via the on condition categories.category_id equals products.category_id to form a single result set.

Join tables in the market management database (excluding the person table) to produce an order report for customer number one, including customer name, order number, order date, category, and product.

Apply inner join techniques to build a multi-table query in SQL Server Management Studio, linking orders, order lines, products, and categories, filter by customer, and compute item totals.

Learn how inner join, left outer join, right outer join, and full outer join work using category and product data, and how non-matching rows appear as nulls.

Master built-in SQL Server functions charindex and substring to locate a substring and extract it in queries, including removing the word product from descriptions.

Master the built-in SQL replace function for string manipulation in SQL Server. See practical examples with select and update statements that replace characters in descriptions.

Master the cast function to convert data types in SQL Server, casting integers to varchar for safe concatenation with strings and preventing conversion errors.

Generate insert statements from the sales table using string concatenation and the cost function to convert numbers, excluding the identity sales_id for portable data replication.

Construct an insert statement from the sales table by concatenating first name and last name, escaping single quotes, and casting performance to word char ten, while excluding the sales id.

Master built-in date functions in SQL Server, including getdate, dateadd, datediff, and datepart, and apply them in queries, calculations, and reporting such as deriving launch year from launch date.

Generate a report of products launched in the past ten years using at least two different approaches with the functions learned, applying them in the where clause.

Complete assignment six by demonstrating multiple SQL methods to filter launch dates within ten years of today using dateadd, getdate, datediff, and datepart, yielding the same five records.

Learn how the isnull function handles null values by returning a replacement when needed, preventing nulls in calculations, and updating data to replace nulls with zero.

Explore aggregation functions in SQL Server, including count, sum, average, min, and max, with overviews of using where clauses and distinct to analyze sales data.

Group by divides data into groups and applies aggregations such as average, max, min, and count for each country or category.

Learn how the having clause filters grouped results by aggregated values—such as average performance or counts—after a group by and before order by.

Apply group by and having queries on the market management database, populating orders and order lines, then analyze orders and products to answer six questions.

Explore assignment seven by counting customer orders, counting products per order with left joins, computing price times quantity for averages, listing frequent customers, ranking products by popularity, and applying having.

Explore union and union all to combine two result sets with matching column counts and data types; union removes duplicates, while union all keeps them, shown with categories and products.

Practice union to generate reports across categories by combining result sets with equal column counts and compatible data types, as you complete assignment 8.

Unite two queries over categories and products to produce a final report with four columns—category id, category name, product name, and product price—using a left join, isnull, and post-union ordering.

Discover how to use the top keyword in SQL Server to limit rows, with examples for top n and top percent, and ordering results by performance.

Learn how to create and use a SQL view to encapsulate a query, treat it as a table, benefit from pre-compiled performance, and protect sensitive columns by granting view-level access.

Learn how variables serve as input parameters and store intermediate results in a multi-statement SQL script, with examples using average performance to filter salespeople.

Learn to use variables to store the most popular category in the market management database, then run two queries: identify the top category, then list its products.

Identify the most popular category by joining order lines to products, summing quantities, and ordering by total, then fetch matching products for that category.

Explain how if...else in SQL controls flow by evaluating a boolean expression to execute the first or second statement, with optional else and begin/end blocks, demonstrated using price examples.

Add a status column to the orders table and use if else logic on order date to set shipped for older dates and pending for newer ones.

Add a status column to the orders table and populate it using conditional updates in SQL Server, including declaring variables, retrieving the order date, and testing with select statements.

Explore the exists function in SQL Server, learning how it checks row existence, drives if-else logic, and filters categories with in-stock products using where clauses and subqueries.

Master the case expression in SQL Server to replace if-else logic inside queries, using case when, then, and end to compute stock level and other expressions.

Apply a case expression to analyze product demand, returning category name and quantity ordered with a calculated demand type—low (1–9), mid (10–30), high (>30)—for each category.

Learn to tackle a complicated sql assignment by joining category and order lines, aggregating total quantity by category, and using a case expression to label low, mid, and high demand.

Master the sql while loop, its begin and end syntax, its boolean condition, and how break or continue manage repeated execution within a set-based language.

Practice temporary tables to identify top two customers by spend, store them in a temp table, and generate an orders report with email, order number, date, product name, and quantity.

Identify the top two customers by money spent by joining orders, order lines, and products, then use a temporary table and sum price times quantity per customer.

Discover how to declare and use table variables as an alternative to temporary tables, including defining email and money spent columns and inserting data in a marketing management database.

Compare temp tables and table variables in SQL Server, highlighting when to use each for performance, memory, and disk considerations.

Create, modify, and drop stored procedures in SQL Server, parameterize queries with begin and end blocks, and use exec while managing result sets with set no count on.

Learn how stored procedures boost performance on SQL Server by compiling once and reusing the execution plan on subsequent runs, unlike raw scripts that recompile each time.

Learn to create user defined functions in SQL Server, including scalar and table valued functions, with parameters and returns, and how to call them in queries.

Write a stored procedure that accepts a product id, uses a function to calculate the total sales amount, and returns a result set; test with different product ids.

Learn dynamic SQL in SQL Server, including constructing queries as strings and executing with the execute function. Understand when dynamic SQL is necessary and its tradeoffs compared to static SQL.

Use update with joins to modify multiple rows in one statement by joining products to categories, filtering meat, and increasing prices by 10% using an alias.

Explore subqueries in SQL Server, including simple and repeating subqueries, compare with joins for efficiency, and learn how to implement nested queries in select and where clauses.

Learn to use derived tables by turning subqueries into table-like expressions in the from clause, compare them with temporary tables, and optimize queries by avoiding repeating subqueries.