Complete Microsoft SQL Server Database Design Masterclass

Install SQL Server Express on Windows by downloading the latest stable version and using the basic installer, then install SQL Server Management Studio to manage databases.

Install Docker on your machine, set up Windows Subsystem for Linux, and launch Docker Desktop. Explore Docker Hub to pull images and create containers for databases and services.

Learn to deploy SQL Server in Docker by running an image as a container, accept the eula, set the sa password, map port 1433, and use a persistent volume.

Learn how SQL Server Management Studio connects to local or remote SQL Server databases, including Docker containers, using Windows authentication, server name, port, and alias settings.

Learn to use Visual Studio Code with the SQL Server extension as a cross-platform alternative to SSMS, including setting up connections, Docker host, and SQL login for developing databases.

Explore Azure Data Studio as a cross-engine database tool with dark mode, IntelliSense, and snippets, then connect to SQL Express on localhost to write queries and create databases.

Explore diagrams.net to create entity relationship diagrams directly in your browser, save as XML, export as images, and share diagrams via GitHub, Google Drive, or cloud storage for database design.

Explore the mechanics of creating and removing a database, using both the UI and scripts, and learn how to remove it effectively.

Create a new database in SQL Server Management Studio, choose a name with no spaces using Pascal case or underscores, and learn rename procedures when the database isn’t in use.

Switch to master with use master, then create a database via sql script. Understand naming pitfalls, the mdf file, and existence checks with if not exists, then refresh to verify.

Learn to remove a database via the UI with delete prompts or by dropping the database with a script. Close all connections, consider backups, and target master for safety.

Learn to rename a database with alter database, updating both the object explorer name and the underlying file. Highlight and execute only the targeted command, with Azure Data Studio compatibility.

Learn how relational databases use tables to store data with rows and columns, primary keys for unique records, and indexes to speed lookups, with examples like persons and student IDs.

Create an entity data model by identifying entities such as student, lecturer, and course, and define their attributes, primary keys, and data types for SQL Server table design.

Create a students table in SQL Server Management Studio via design view, define an auto-incrementing primary key, unique student id, and fields using varchar and date data types.

Explore sql as the standard language for storing, manipulating, and retrieving data in databases. Learn ddl and dml, with examples creating a lecturers table and deleting a student by id.

Write a sql script to use a database, create a lecturers table with an identity primary key, and define name and date of birth, then test and modify.

Compare designer vs script to create courses, lectures, and students tables, and build a single script to create, use, and manage the school management database efficiently.

Learn to insert data into SQL Server tables using Management Studio's edit top 200 rows UI, including auto-incrementing IDs, pending changes, and truncation errors.

Execute data insertion into a SQL Server table using insert statements. Specify columns or rely on table order, insert multiple rows, and handle nulls and auto-incrementing primary keys.

Master the basics of selecting data from tables with simple SQL queries, using top 1000, select star, and column lists to generate read-only results and study the underlying scripts.

Learn to filter data with where clauses in SQL Server, targeting specific records by ID or other columns using and/or, like wildcards, and date or numeric comparisons.

Learn how to update data in tables using the editor and update statements, including the set and where clauses, the optional where clause caution, and how to perform bulk updates.

Learn to delete data from tables using the UI or delete statements with precise where clauses to target specific records. Preview results with selects, handle duplicates, and consider bulk operations.

Master SQL transactions to safely stage inserts, updates, and deletes, using begin transaction, commit, and rollback to ensure atomic changes across multiple tables.

Explore normalization to remove insert, update, and delete anomalies and design relationships with primary and foreign keys. Learn how referential integrity and one-to-many and many-to-many associations prevent data redundancy.

Normalize the database by centralizing the program of study in its own table and linking with foreign keys; use a class table to map lectures to courses and student enrollments.

Master creating relationships in SQL Server by designing with diagrams, adding foreign keys, and scripting table creation to enforce correct dependency order.

Learn to insert records in a many-to-many database setup by linking lectures, courses, classes, and enrollments through ids, performing reconnaissance to resolve references, and tailoring selects to present human-readable results.

Learn how inner joins connect related data across multiple tables by matching foreign keys to primary keys, yielding concise results.

Explore inner, left, right, and full joins in SQL Server using class and lecturer data to show when to retain unmatched rows, plus cross join basics.

Explore how foreign keys enforce referential integrity by preventing deletions of parent records with dependent children. Examine cascade, set default, and no action delete rules to avoid orphan records.

Master normalization and design many-to-many mappings with a mapping table, enforce referential integrity via foreign keys, and visualize database with ERD for sql server; practice queries and prepare aggregate functions.

Examine how distinct and group by control duplicates and aggregate data, using student enrollments and courses, and learn to structure selects, joins, and filtering for upcoming average calculations.

Learn how to use the sql avg aggregate function to compute each student's average grade from multiple enrollments, including grouping by student id, counting grades, and presenting a joined report.

Apply SQL max and min aggregate functions to calculate class averages and the lowest and highest grades, gaining per-class insights with aggregate functions.

Explore using the SQL sum aggregate function to calculate total credits per student by joining enrollments, classes, and courses, then apply group by and filter aggregates.

Learn to filter by aggregate values using the having clause after group by, applying sum, avg, min, and max to credits.

Explore advanced SQL Server concepts, starting with views—how to create and manage them and why we need them—and delve into programmability with stored procedures, functions, and triggers.

Create and test an inline table valued function named get_grade_range that returns grades within a minimum and maximum grade using a table return type.

Learn to create and use multi-statement table-valued functions in SQL Server, returning a named table populated from students and lecturers via insert into and select.

Explains how to create and test stored procedures in SQL Server, including defining parameters, inserting and selecting data, executing procedures, and using output parameters to return new identities.

Explore how triggers run after insert on a table, use the inserted data to set defaults like a program of study, and log audit entries.

Back up sql server databases using the backup task and save to a .bak file; then restore to the same or another instance, adjusting database and file names as needed.

Learn SQL Server security by creating database users, adjusting permissions, and monitoring access to protect against unauthorized operations across databases.

Add Windows authenticated logins to SQL Server via security, new login, and Windows authentication. Map the user to the database, assign roles (owner or writer/reader), and manage or disable access.

Learn to add users to a SQL Server database with scripts by creating a Windows login, mapping it to a user, and assigning data reader or data writer roles.