MICROSOFT SQL SERVER: A Beginner to Advanced Guide

Master the select clause in Microsoft SQL Server, progressing from beginner concepts to advanced techniques in this comprehensive guide.

Explore clauses, operators and conditions in Microsoft SQL Server, guiding learners from beginner to advanced levels with practical, hands-on techniques.

Apply the order by clause to sort names in ascending or descending order, and use group by with aggregate functions to compute total salaries by location.

Explore how the where and in clauses filter data before aggregation and how the having clause filters groups after aggregation, using group by and aggregate functions across location and gender.

Explore inner joins to combine employee and department data, and learn how left, right, full, and cross joins relate through foreign keys.

Master outer joins in SQL Server, including left outer join and right outer join, and compare them to inner join using department and employee tables to handle non-matching IDs.

Explore full joins and cross joins in SQL, seeing how full join returns non-matching records from both tables, and how cross join yields a cartesian product without an on clause.

Learn to create and use stored procedures in SQL Server, encapsulating repeatable queries, executing by name or with parameters, and using begin blocks to structure logic.

Learn to create stored procedures with output parameters in SQL Server and count employees by gender using an output parameter; declare, execute, and retrieve results.

Analyze differences between output parameters and return values in stored procedures, including examples and scenarios where each method excels or falls short.

Explore how SQL Server indexes speed queries by avoiding table scans, with a salary column example, and preview various index types to improve data retrieval.

Explore clustered and non-clustered indexes in SQL Server, including how clustered indexes determine physical data order, and how non-clustered indexes enable multiple, separate storage with faster lookups.

Create a unique non-clustered index to enforce distinct first name and last name pairs. A primary key automatically creates a unique clustered index, while unique constraints align with unique indexes.

Learn how views act as virtual tables in SQL Server, enabling joined queries and simplified access. Create, query, and manage views to present aggregated data while maintaining security.

Learn how views reference underlying base tables to hide salary data, create secure views like employees except salary, and update views with multi-table consequences.

Explore how SQL triggers work, including DML triggers, after triggers, and instead of triggers, with examples using insert statements and the inserted pseudo-table.

Learn how instead of triggers replace the insert operation on a view, enabling safe updates across multiple underlying tables and handling invalid department names with explicit errors.

Learn to replace joins with subqueries in SQL using practical examples with product and sales data. Identify unsold items and compute total quantities sold using not in and nested queries.

Master dcl commands in sql, including grant and revoke, create users, and grant select on a table like department, with grant option.

Master SQL Server database backups, including full, differential, and copy-only backups, to protect data during outages. Learn how to set the backup destination, naming, expiration, and complete a successful backup.