Complete Microsoft SQL Server Database Design Masterclass from Udemy

Overview

Microsoft SQL Server is a powerful and widely-used database management system used by organizations of all sizes to store and retrieve data. This course teaches you how to use Microsoft SQL Server to design, implement, and maintain a database. You will learn key concepts such as database design, query writing, and stored procedure creation. You will have the opportunity to practice your skills through hands-on exercises and projects.

This is a zero-to-hero course on Microsoft SQL Database development and querying techniques. This course provides you with fundamental SQL Server training and is enough to get you started as a Database Developer or Database Administrator. There are no prerequisites for this course.

By the end of this course, you will have a strong understanding of how to use Microsoft SQL Server to manage and manipulate data and will be able to use it effectively in your projects. Whether you are new to database management or have some experience, this course is designed to provide a comprehensive overview of Microsoft SQL Server and help you become proficient in its use.

What you’ll learn

How to Install SQL Server and SQL Server Management Studio (Windows or Docker for Mac and Linux)
How to Connect to a Database Instance and create Databases and Tables
How to Use SQL Management Studio (SSMS)
How to use Azure Data Studio
How to Author queries to Create, Read, Update and Delete data in tables.
How to develop entity relationship diagrams using visual design tools
How to normalize a database and create relationships and foreign keys
How to Use Aggregate Functions to do quick mathematical operations
How to export data to Excel using the Management Studio
How to create and manage Database Views
How to create and manage Functions, Table Triggers, and Stored Procedures
How to Backup and Restore Databases
How to manage users and database security

Why Learn Microsoft SQL Server

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications.

We will explore best practices in database development using SQL Server and will be building a small school management system database.

All you need is a capable machine, and we will walk through setting up an environment, creating a database, creating your first table, and writing queries.

At the end of this course, you should be comfortable writing queries for multiple situations and reports.

Are there any course requirements or prerequisites?

A Windows PC (or Docker if using Linux/Mac)

Who this course is for:

This course is for beginners with absolutely no experience.
This course is for you if you are already comfortable with fundamental database operations.
This course is for you if you feel a bit 'rusty' and need to refresh your knowledge.
This course is for you if you want to understand design principles in database development.
This course is for you if you are proficient in Microsoft Access and want to learn a new Database Management System.
This course is for EVERYONE.

Content and Overview

This is a complete course. I take you through various scenarios and techniques to write queries, build a database and further administrate, then we touch on more advanced topics that are in great demand in the workplace. Over 6 hours of premium content, all put together to ensure that; then get up and running with becoming e Developer and Administrator.

By the time you finish the course, you will have moved around in Microsoft SQL Manager Studio so much that it will become second nature for you when interacting with your databases on different servers. This will put your newly learned skills into practical use and impress your boss and coworkers.

The course has working files and scripts, making it easy to replicate the demonstrated code. You will be able to work alongside the author as you work through each lecture and will receive a verifiable certificate of completion upon finishing the course.

Clicking the Take This Course button could be the best step to increase your income and marketability quickly. Also, remember that if you think the course is not worth what you spent, you have a full 30 days to get a no questions asked refund.

It's time to take action.

See you in the course.

What's inside

Learning objectives

Basic database design (tables, columns, data types, primary keys, normalization)
Using microsoft sql management studio
Select statements: queries against single tables (filtering, ordering, grouping)
Delete: how to remove data from a database
Insert into: how to insert data into a table
Update: how to update existing data
Filter groups of data using the having clause
Overall database development best practices
Creating relationships and foreign keys
How to query related data from multiple tables

Using aggregate functions in sql
How to export query results to excel
How to craft sql statements based on real scenarios
Create complex database objects (stored procedures, functions, views, triggers)
Backup and restore databases
Generate database scripts for automation
How to manage database users and security
Show more
Show less

Basic database design (tables, columns, data types, primary keys, normalization)
Using microsoft sql management studio
Select statements: queries against single tables (filtering, ordering, grouping)
Delete: how to remove data from a database
Insert into: how to insert data into a table
Update: how to update existing data
Filter groups of data using the having clause
Overall database development best practices
Creating relationships and foreign keys
How to query related data from multiple tables
Using aggregate functions in sql
How to export query results to excel
How to craft sql statements based on real scenarios
Create complex database objects (stored procedures, functions, views, triggers)
Backup and restore databases
Generate database scripts for automation
How to manage database users and security
Show more
Show less

Syllabus

Here we will learn the different software and tools that we will need for this course.

Introduction

Understanding Types of Data and Storage

Environment Setup

Download SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, as well as build queries and scripts.

Microsoft® SQL Server® 2019 Express

https://www.microsoft.com/en-us/download/details.aspx?id=55994

Microsoft® SQL Server® 2019 Express is a powerful and reliable free data management system that delivers a rich and reliable data store for lightweight Web Sites and desktop applications.

Install SQL Server on Mac and Linux

Understanding The Toolset

Explore SQL Server Management Studio

Explore Azure Data Studio

Explore Draw.io

Creating And Manipulating Databases

Section Overview

Create Database using Management Studio

Create a Database using SQL Script

How to Remove a Database

How To Change Database Name

Section Review

Managing Tables and Data

What is a Database Table?

Create Entity Data Model

Create a Database Table using Management Studio

What is SQL?

Create a Database Table using SQL Script

Review Table Creation

Insert Data into the Table using Management Studio

Insert Data into the Table using SQL Scripts

Select Data from Tables - Simple Queries

Select Data from Tables - Adding Filtering

Select Data from Tables - Exporting Data

Update Data in Tables

Delete Data from the Tables

SQL Transactions

Review CRUD Operations

Normalization, Relationships and Foreign Keys

What are Relationships, Foreign Keys and Normalization

Normalizing Our Database

Creating a Relationship in SQL Server

Inserting Records in One To Many Related Tables

Inserting Records in Many To Many Related Tables

Using Inner Joins To Read Related Data

Understanding Left, Right and Full Joins

Foreign Key Cascade Options

Review

Group By and Aggregate Functions

Grouping Data using SQL GROUP BY Clause

SQL COUNT Aggregate Function

SQL AVG (Average) Aggregate Function

SQL MAX and MIN Aggregate Functions

SQL SUM Aggregate Function

Filtering on Aggregate Values

Aggregate Function Scripts

Advanced SQL Server Objects and Concepts

Create and Manage Views

Scalar Valued Functions

Inline Table Valued Functions

Multi Statement Table Valued Functions

Stored Procedures

Table Triggers

Generating Database Scripts

Database Backup and Restore

SQL Server Security

Create an SQL User Login

Add Windows Authenticated Login

Add Server Logins With Script

BONUS Lecture

Bonus Lecture

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Provides a comprehensive overview of Microsoft SQL Server, starting with installation and basic concepts, making it suitable for individuals with little to no prior experience in database management

Covers essential aspects of database management, including database design, query writing, stored procedure creation, and user management, which are crucial for database developers and administrators

Explores best practices in database development using SQL Server and involves building a small school management system database, offering practical experience for aspiring database professionals

Teaches how to create complex database objects like stored procedures, functions, views, and triggers, which are essential for advanced database development and administration tasks

Requires a Windows PC (or Docker if using Linux/Mac), which may pose a barrier for students who do not have access to these resources or prefer other operating systems

Uses Microsoft SQL Server 2019 Express, which may not be the latest version, potentially missing out on newer features and updates available in more recent releases

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Complete Microsoft SQL Server Database Design Masterclass with these activities:

Review Relational Database Concepts

Show steps

Reinforce your understanding of relational database concepts before diving into SQL Server specifics. A solid grasp of these fundamentals will make learning SQL Server much easier.

Browse courses on Relational Databases

Show steps

Review the definitions of primary keys, foreign keys, and indexes.
Study the different normal forms (1NF, 2NF, 3NF) and their importance.
Practice designing simple database schemas on paper.

Review 'SQL Queries for Mere Mortals'

Show steps

Learn the fundamentals of SQL query writing. This book will help you write effective queries to retrieve and manipulate data.

View SQL Queries for Mere Mortals on Amazon

Show steps

Read the chapters on basic SELECT statements and filtering data.
Practice the examples provided in the book using a sample database.
Try to rewrite some of the queries using different SQL Server syntax.

Design a Simple Database for a Library

Show steps

Apply your knowledge by designing a database for a library. This project will help you solidify your understanding of database design principles and SQL Server concepts.

Show steps

Identify the entities (e.g., books, authors, members) and their attributes.
Create an entity-relationship diagram (ERD) using draw.io or a similar tool.
Implement the database schema in SQL Server using SQL scripts.
Populate the database with sample data and write queries to retrieve information.

Four other activities

Expand to see all activities and additional details

Show all seven activities

SQL Query Exercises on HackerRank

Show steps

Sharpen your SQL skills by solving practice problems on HackerRank. Regular practice will improve your query writing speed and accuracy.

Show steps

Create an account on HackerRank and navigate to the SQL section.
Solve the beginner and intermediate level SQL problems.
Analyze the solutions provided by other users to learn different approaches.

Create a Blog Post on SQL Server Security Best Practices

Show steps

Deepen your understanding of SQL Server security by researching and writing a blog post. Teaching others is a great way to reinforce your own knowledge.

Show steps

Research SQL Server security best practices from Microsoft documentation and other sources.
Outline the key topics to cover in your blog post (e.g., user authentication, permissions, encryption).
Write a clear and concise blog post with examples and code snippets.
Publish your blog post on a platform like Medium or your own website.

Develop a Stored Procedure for Data Validation

Show steps

Practice creating stored procedures by developing one for data validation. This will help you understand how to encapsulate complex logic within the database.

Show steps

Identify a table in your database that requires data validation.
Define the validation rules for the table's columns.
Write a stored procedure that checks the data against the validation rules.
Test the stored procedure with different data inputs to ensure it works correctly.

Review 'Microsoft SQL Server 2012 Internals'

Show steps

Gain a deeper understanding of SQL Server internals. This book will help you understand the architecture, storage engine, and query processing.

View Microsoft SQL Server 2012 Internals on Amazon

Show steps

Read the chapters on storage engine and query processing.
Try to relate the concepts to the course materials.
Research any unfamiliar terms or concepts.

Career center

Learners who complete Complete Microsoft SQL Server Database Design Masterclass will develop knowledge and skills that may be useful to these careers:

Database Developer

A Database Developer designs and implements databases, writing the code to create and maintain these systems. This course helps you build a strong understanding of Microsoft SQL Server, which is a valuable skill for any aspiring database developer. The course covers key development topics such as database design, query writing, and stored procedure creation, all essential for a database developer to function effectively in the field. Through hands-on exercises and projects, you will gain practical experience that prepares you to work on real-world projects. In particular, this course gives considerable attention to querying techniques, and crafting complex stored procedures, which are both crucial skills for a database developer.

See salaries and explore the career path for Database Developer

Database Administrator

A Database Administrator is responsible for the performance, integrity, and security of a database. This course, which offers a zero-to-hero introduction to Microsoft SQL Server, may be useful for those pursuing a career as a database administrator. You will learn how to install SQL Server, create databases and tables, manage users, and ensure that data is backed up and recoverable, all important tasks for a database administrator. The course also delves into security management, which is a critical aspect of a database administrator's role. The practical focus, including how to export data to Excel, is another tool in a database administrator's kit. This course will also provide you with experience connecting to database instances using SQL Server Management Studio which is a tool database administrators routinely use.

See salaries and explore the career path for Database Administrator

SQL Developer

An SQL Developer is responsible for designing, developing, and maintaining databases using SQL. This course provides extensive training in Microsoft SQL Server, which is a fundamental tool in the field. You will learn how to create and manage databases, tables, views, stored procedures, and functions, which are all crucial tasks handled by an SQL developer. The course also covers querying techniques, including how to filter, order, and group data, as well as how to create relationships between tables using foreign keys. The focus on best practices in database development using SQL Server and the building of a small school management system database within the course are helpful for an SQL developer.

See salaries and explore the career path for SQL Developer

Data Analyst

A Data Analyst uses data to generate insights that inform business decisions. A data analyst often works with relational databases such as Microsoft SQL Server to extract and manipulate data. This course helps you acquire the skills to write effective queries using SQL to retrieve necessary information from a database. You will learn how to use aggregate functions to perform calculations on the data, which will enable you to summarize and analyze large datasets. The course also covers exporting data to Excel, which is a common practice for data analysts. The extensive coverage of the SELECT statement and how to filter, order, and group data using SQL commands should be useful to a data analyst, as this is often a large part of their day to day work.

See salaries and explore the career path for Data Analyst

Reporting Analyst

A Reporting Analyst gathers data from various sources to create reports that inform business decisions. This role often requires a strong understanding of databases and how to extract data using SQL. This course, which focuses on Microsoft SQL Server, helps you learn how to write queries to retrieve the data you need. The course also teaches you how to use aggregate functions to summarize data and export query results to Excel, which is a common practice for reporting analysts. Additionally, the course provides instruction on creating database views, which can help simplify reporting processes. Therefore, this course helps build a foundation for a career in reporting.

See salaries and explore the career path for Reporting Analyst

Business Intelligence Analyst

A Business Intelligence Analyst uses data to help organizations understand market trends. This role involves extracting, cleaning, and analyzing data to present information to stakeholders to improve business performance. A key skill for business intelligence is the ability to retrieve and manipulate data from databases. This course, by helping you become proficient in using Microsoft SQL Server, can be useful for performing these functions. The course provides instruction in writing SQL queries to extract data, using aggregate functions, creating views, and exporting data to Excel, all of which are helpful for a business intelligence analyst. In particular, the focus on reporting and query writing makes this course a good fit for business intelligence analysts.

See salaries and explore the career path for Business Intelligence Analyst

Data Quality Analyst

A Data Quality Analyst is responsible for ensuring that data is accurate, complete, and reliable. This role often requires working with databases to identify and correct data errors. This course may be useful to a data quality analyst due to its comprehensive introduction to Microsoft SQL Server. The course covers how to write SQL queries to retrieve data, which you can use to investigate data quality issues. It also explores how to use aggregate functions in SQL, which is useful for summarizing data and identifying anomalies. The course gives you the crucial ability to manipulate the data within the database, which is an essential tool for data quality analysis.

See salaries and explore the career path for Data Quality Analyst

Data Modeler

A Data Modeler creates visual representations of data to help organizations understand complex data structures. Data modelers often work with relational databases such as Microsoft SQL Server to design effective database schemas. This course may be useful to those who wish to become data modelers. The course includes instruction on developing entity relationship diagrams, which are very important for a data modeler. You will also learn about database normalization and how to create relationships and foreign keys between tables, which are key elements in data modeling. This course, with its practical exercises in designing a database from scratch, provides valuable experience for anyone looking to work as a data modeler.

See salaries and explore the career path for Data Modeler

Database Consultant

A Database Consultant advises organizations on how to best utilize their database systems. This course can be useful for anyone pursuing a position as a database consultant. The course covers core concepts such as how to design, implement, and maintain a database using Microsoft SQL Server. It also provides a good foundation for creating queries, backing up and restoring databases, and managing database security. This breadth of knowledge, from basic database operations to more advanced topics, is particularly relevant to a database consultant who needs a full understanding of database best practices. The course also focuses on hands-on exercises and projects, which can provide a consultant with a strong practical approach to addressing client needs.

See salaries and explore the career path for Database Consultant

Data Engineer

A Data Engineer designs, builds, and manages data pipelines and infrastructure. For a data engineer, proficiency in database systems is a crucial skill. This course could be useful to anyone looking to join the field. The course covers topics such as database design, query writing, and creating stored procedures, which are all frequently used by a data engineer. You will learn how to create and manage databases, design tables, insert data, and use SQL to manipulate information which are all core components of the work of a data engineer. This course also includes instruction on how to automate database tasks through the use of generated scripts, which can help improve efficiency in data workflows.

See salaries and explore the career path for Data Engineer

Database Architect

A Database Architect designs and oversees the implementation of an organization's database systems. This course may be useful to those looking to get into database architecture. A database architect requires a deep understanding of database design principles, which are taught in this course, including how to normalize a database and create relationships. You will also learn how to create entity-relationship diagrams using visual design tools, which are very important in designing database systems. Moreover, the course examines how to manage database security, create database backups, and generate scripts, which are all elements of the work of a database architect. Please note that most database architect positions require an advanced degree.

See salaries and explore the career path for Database Architect

Data Architect

A Data Architect is responsible for creating the blueprints for how data is stored, managed, and used within an organization, which often includes designing database systems. This course is a good starting point for those looking to become a data architect. It covers a wide range of topics including database design fundamentals, how to create complex database objects, and how to manage database security. Additionally, this course delves into best practices for database development using SQL Server, which is a crucial skill for a data architect. The practical focus of the course, which includes hands-on exercises, helps build a solid foundation for designing efficient, scalable, and secure database systems. Please note that a data architect typically requires an advanced degree.

See salaries and explore the career path for Data Architect

Software Engineer

A Software Engineer designs, develops, and maintains software applications. Often, applications rely on data stored in a database, which is a main reason why this course might be useful. The course delves into fundamental database operations with Microsoft SQL Server such as setting up the environment, creating a database, and writing queries. This provides hands-on experience for software engineers who need to integrate databases into their applications. The course also teaches best practices in database development using SQL Server, which is useful for creating efficient and stable applications. In particular, the course on building a small school management database might be an interesting mini-project to software engineers.

See salaries and explore the career path for Software Engineer

Application Developer

An Application Developer designs and builds software applications for a variety of purposes. Often applications will require interacting with a database. This course helps you learn the fundamentals of working with Microsoft SQL Server, a popular choice for modern applications. This course covers how to design a database, create tables, and write SQL queries to insert, update, and retrieve data. This is a valuable set of skills for any application developer, as interacting with a database is often a large part of their work. From learning how to connect to a database to learning how to construct complex stored procedures, this course provides relevant experience for an application developer.

See salaries and explore the career path for Application Developer

System Analyst

A System Analyst analyzes an organization's computer systems and procedures. They help determine how systems can be improved. This course may be useful, as many systems rely on databases. In this course, instruction is given on how to create, manage, and secure a database using Microsoft SQL Server, which is used by many systems. The course also goes into detail on how to use the database management studio, how to write SQL queries, and how to manipulate database objects such as views, functions, and stored procedures. Therefore, for a system analyst who needs to understand how data is stored, this course is likely to be useful.

See salaries and explore the career path for System Analyst

Complete Microsoft SQL Server Database Design Masterclass

What's inside

Learning objectives

Syllabus

Good to know

Save this course

Activities

Career center

Reading list

Share

Similar courses