SQL Server Essentials, from Scratch from Udemy

What's inside

Learning objectives

By the end of the course, you will have a foundation in sql server and the vocabulary and understanding to talk about and use sql server with confidence.
You will know how to navigate sql server management studio (ssms) to find database objects and business data.
You will understand how to take advantage of management studio to make you work faster and more efficient.
You will understand the different pieces in sql server. including, tables, views, stored procedures, security, backups, indexes and schemas to help you talk to dba's, programmers, it and to sound smart when you are speaking about databases.
You will know how to write sql statements to create, retrieve, update, and delete data.

You will be able to create basic views and stored procedures.
You will be able to filter, join, sort, and group data.
You will be able to create basic views, stored procedures, and functions.
You will have foundational knowledge in security, backups, and indexes
You will understand how all of these pieces work together in a database.

By the end of the course, you will have a foundation in sql server and the vocabulary and understanding to talk about and use sql server with confidence.
You will know how to navigate sql server management studio (ssms) to find database objects and business data.
You will understand how to take advantage of management studio to make you work faster and more efficient.
You will understand the different pieces in sql server. including, tables, views, stored procedures, security, backups, indexes and schemas to help you talk to dba's, programmers, it and to sound smart when you are speaking about databases.
You will know how to write sql statements to create, retrieve, update, and delete data.
You will be able to create basic views and stored procedures.
You will be able to filter, join, sort, and group data.
You will be able to create basic views, stored procedures, and functions.
You will have foundational knowledge in security, backups, and indexes
You will understand how all of these pieces work together in a database.

Syllabus

Foundation

Welcome to the SQL Training Academy's SQL Server Essentials Training Program. This video is an overview of what you will be learning in your training.

I put together this Learning Plan to keep you moving forward in the training. As with any training, after the initial excitement wears off you must be able to track your goals and accomplishments to keep yourself moving forward. I want you to get the most you can out of this program, so go ahead and download this Learning Plan and start filling it out.

The learning outline gives you descriptions of all of the lessons in the SQL Essentials training. Use this document as a Table of Contents to help you understand the full scope of this training. You should read over these before you start the training. Just like reading a book, you will learn more if you read the table of contents before you dive into the actual training.

This Pre Assessment is a little questionnaire designed specifically for you. By answering the questions, you will have a better idea of what you want to get out of this program. Knowing what you are trying to accomplish will help align your expectations and help you stay motivated to push through the struggle that comes with learning something new.

I am providing this comprehensive vocabulary list as an added resource for your learning. Download it and follow the directions to get the most benefit from the list.

SQL Server Developer Edition is the free edition of SQL Server that you will use for this training. I show you how to install SQL Server in this video.

SQL Server Management Studio (SSMS) is the tool used to connect to a SQL Server database. In this lecture I show you how to install SQL Server Management Studio.

The SqlTrainingOnlineSimpleDB is a small practice database that you will use throughout your training. It is designed to make your learning easier by using small tables. In this video, I demonstrate how to install this database in step-by-step instructions.

AdventureWorks is a practice database that Microsoft supports for training and demonstrations of their products. It is hosted on GitHub, https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/adventure-works. I use this database for demonstration purposes in a couple of videos, so it is optional for most of the training and you can skip this lecture and come back later if needed.

In this video, I walk you through the process of installing/restoring this database from a backup. The backup files are located on GitHub, https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks. You will use the backup with the same year as the SQL Server version you are using for this training.

Module #1: Introduction to Databases

In order to have a good foundation, we need to start out with some information on what a database is. This video will give you short history of the different types of databases we encounter and then work our way to the Relational Database Management System (RDBMS). SQL Server is a RDBMS. We then move into some examples of companies that use Relational Databases.

In order to use a database, you must know the steps that are needed to start a “conversation” with the database. This is especially important when you are trying to access a database you haven’t accessed before. In this video, I outline the 8 steps to help you start talking to a database. Once you have gone through the steps a few times, you won’t even know you are using them.

When working with a database it is crucial that you understand the basics of a Client/Server relationship. In this lesson, I explain the Client/Server relationship and give you some pictures to help you visualize what is going on. This is part 1 of lessons on the Client/Server relationship.

In this lesson I demonstrate some of the tools involved with the Client Server relationships. We talk more about Domains, IP Addresses, and how SQL Server runs as a Service. These concepts will help demystify how SQL Server runs.

Module #2: Management Studio

Part 1 of this lesson is a short introduction on where Management Studio lives in the Client Server relationship. This will help you understand the spatial relationship between the Management Studio Client and the SQL Server.

Part 2 of this lesson digs into the Sql Server Management Studio tool. This tool is where most of your SQL work will happen. Understanding how to navigate around Management Studio will help you get through future lessons.

Understanding the database hierarchy will help you find items in the database and help you understand how the database objects all fit together. In Part 1, I draw some diagrams to help show these concepts.

In this lesson I want to familiarize you with how the database hierarchy actually looks inside a database in Management Studio. I will show you actual schemas, tables, and more. You will need to install the AdventureWorks2012 database if you want to follow along as the video is playing.

You must first understand tables before you can start to work with relational databases. In part 1 of this lesson, I draw a few examples of tables and give you a very small introduction into table/relational theory. Not too much theory, just enough to expose you to it.

We explore tables and data types in part 2 of the Tables lesson. Understanding data types will save you hours of difficulty and frustration working with tables. You will need to have the SqlTrainingOnlineSimpleDB installed to follow along in this lesson and in most of the future lessons.

It’s time to start creating things! In Part 1, we are starting by using the GUI tools inside of Management Studio to help us create a database, table, and add some data to the table. Creating things with the techniques I am demonstrating will give you a shortcut to memorizing all of the SQL commands that are out there.

In Part 2, we continue creating tables using the GUI tools inside of Management Studio. But, we go even further by creating the Primary and Foreign Key relationships that set the relational database apart from other types of databases. Understanding the existence of Primary and Foreign Keys is the first step to understanding data integrity and keeping data clean and reliable.

We are starting to make our transition from using the GUI tools inside of Management Studio to using SQL to create our objects. In Part 1, I will show you how to create a database from a script. Knowing how to create and save scripts will give you a way to save your work and recreate items in seconds.

After this lesson, you will have a script that drops and creates a database, creates a table, and then populates that table with some data. This script will do the work from previous videos in less than a second! This is the power of SQL.

Module #3: Writing SQL

When manipulating data in a table, there are four operations: Create, Retrieve, Update, and Delete. That is what CRUD stands and I will cover Create and Retrieve in part 1. Make sure to pay particular attention to the Retrieve part because we have a number of lessons coming up that build on these initial concepts.

In Part 2 of the CRUD lesson, I demonstrate how to Update and Delete records from a table. I will also get into the Undo functionality in SQL. The undo functionality is called Transactions. When talking to people about relational processes, transactions will often come up, so you should make sure you understand the concept.

In this lesson, I give you a quick introduction to the Select syntax and take you through refreshing your training database so that you get the same answers that I do during the upcoming lessons. You will want to get this refresh done so you can follow along in the lessons.

The Where Clause allows you to filter data that you retrieve from a table. This is the start to mastering the retrieval of data from a database. We are starting slow, but we will keep building until you can create complex objects.

We continue with filters in this Part 2 of the Where Clause. I start to get more complex by adding multiple filters which is how you will normally use the where clause in day to day operations.

In this lesson, we will looking into the Group By Clause that allows you to order your result set. This is used for many purposes, but I often use it when I'm analyzing a dataset.

So much can happen in the Column List of the Select Statement. The Column List defines what you want to return in your Query. In Part 1, we talk about concatenation, aliasing, conversions, and more. Make sure you are trying these queries as I show them to you. This will help you cement these operations in your brain.

In Part 2, I demonstrate the use of built in functions, aggregations, mathematical operations, and more string manipulation. After you understand the basics of these operators and how to use them, I will show you how to find more of them, and you will become self-sufficient when working with built in functions and operators.

Grouping your results allows you to find totals by departments, or cities, or salesmen. Grouping is a valuable tool to help you retrieve rolled up information to be further analyzed in Excel, or Access, or any other tool. I show you how to use grouping in this lesson.

The Having Clause is used with the Group By clause to filter your results after you have grouped them. This is valuable when validating your data doesn’t have duplicates or you just want to narrow down your results. In this lesson we continue building on the knowledge from the previous video.

Joining can take a while to digest, but it is a very important piece to the relational database. Since our data is usually spread across multiple tables, you bring all of this data back together through a table join. Take your time and watch this and the next lesson a couple times to make sure you understand how to Join tables.

In Part 2, I demonstrate an Outer Join and how it is different from the Inner Join in the last lesson. It is important that you have kept up with the other videos to this point, because I start bringing everything together toward the end of this video. After you are finished with this lesson, you should be able to query most relational databases that you will face. You will only need to combine the different techniques from the previous videos.

Module #4: Advanced Objects

Now that you have learned how to write queries using SQL, you will eventually end up with big queries that are complicated and can get pretty messy. That is where Views come in! A view lets you take a complex query and make it look simple. Part 1 will introduce the view to you.

Views have a few more uses other than making a complex query look simple. Part 2 explores some of those other uses adding even more firepower to your ever growing SQL tool belt! After you complete this lesson, you should feel comfortable with the concept of a view and how it can be used.

Scripting enhances the power of SQL and moves toward programming with TSQL (Transact –SQL). This shift is not dramatic and can be as simple as putting multiple SQL statements together. In this lesson we explore the way SQL Server allows you to simply do a select without a table. You will have to watch the video to see what I mean!

In Part 2, we start using variables to store information so that we can use it later in the script. This is an important part to programming and is quite different from the SQL we have been using up until this point. Variables are the most important concept for you moving into the next lessons where you will start creating Stored Procedures.

In this lesson, I will introduce you to Stored Procedures which allow you to program inside of the database. I won’t get that deep into all of the features of SQL Server Stored procedures, but after this lesson you will have basic knowledge and be able to communicate with others about stored procedures.

Now it is time to get a little more complex with the stored procedures by having more than one statement, storing a result, then returning it. You will want to make sure you are following along with your local SQL Server and practicing as you are watching the lesson. This will help you with your understanding of stored procedures.

Do you remember using the aggregate functions Sum, Avg, Min, and Max? Well, now you will learn to create your own functions. They don’t have to be too complex, but can be very useful when regular SQL techniques don’t quite give you what you want. In Part 1, we will introduce the syntax to create a function and use some of our stored procedure and scripting with variable knowledge to make learning Functions easier.

The Function can be used to lookup values and in many 3^rd party systems I have worked with they do just that. In this lesson, I will give you an example of a lookup using a Function and we will also explore how you can call a user created function. Even if you never write a Function in a production system, understanding their structure will help you understand and decipher the data you are working with.

Module #5: Advanced Database Concepts

With all of the credit card and identity theft you hear about in the news, it is even more important than ever to understand the security mechanisms that are available. I draw diagrams of 6 levels of security that you can use to keep the information in your database secure.

Now I move to the computer and show you how the security is used inside the actual database. In Part 2, I secure an individual user by granting permissions directly to that user. This will work for a small number of users and is a great way for you to get started learning SQL Server security.

In Part 3, I use roles to secure objects. As there are more users in the database, you will want to use roles for security because it will be easier for you to manage. If you already work for a large company, you will probably see role-based security already implemented. This concept is a great addition to your knowledge as it translated to all types of security.

Here, I introduce database backups and the different types that are in SQL Server. Without backups, you should be scared to put any data of significance into a database. So, backups are of the utmost importance and understanding the concept and the different types of backups will help you understand some of the worries that a DBA has and help you understand what questions you should be asking your DBA.

Here I head back to the computer and actually backup and recover one of our practice databases. I even recover it to a different database name. Being able to backup and restore your own databases can help you when you are troubleshooting issues and if you are a developer, you can use this knowledge to get your own development database play area. SQL Server Management Studio really simplifies this process and makes it easier to learn.

If you had to find a person’s name in the phone book, but it wasn’t alphabetized, how long would that take? Indexes are essentially the same as alphabetizing the phonebook. They provide a huge performance boost to your tables. In this lesson, I draw some diagrams to introduce the concept of an index.

I’m back at the whiteboard to talk about Clustered and Non-Clustered indexes. Without the Clustered index (which I will explain in the video), the regular index we spoke of in Part 1 won’t have the impact it is supposed to. As you start to deal with Big Data, you will need to understand the concept of indexing and its effect on performance.

In Part 3, I will create some indexes and show you how SQL Server reacts to different indexes. I introduce the concept of Full Table Scan and Index Seek and you will see these when we show SQL Server’s Query Plan. After this lesson, you will be familiar with these concepts and you will also be able to answer the question of why not just put an index on every column to speed up all queries? (Which you shouldn't do by the way.)

Bonuses

Combining SQL with SQL Server Reporting Services is a powerful combination. I’ve been working with Reporting Services since it came out and it is the superior reporting tool. After watching this video, you will understand how to get started with Reporting Services.

Excel is used throughout business for an unlimited number of purposes. Combining it with the power of SQL Server allows you to hook Excel up to your relational database and automate your personal reporting without the need to go to IT for every request.

PowerPivot is a new Microsoft tool that works with Microsoft Excel to give you more power when connecting to SQL Server. Understanding PowerPivot will put another tool on your tool belt and increase your market value in your career.

Business users have been setting up their own databases since Microsoft Access was released. But, the best way to use Microsoft Access is connected to SQL Server. This video shows you how you should be using Microsoft Access and the differences that you will run into with Microsoft Access SQL.

One issue you will run into over and over again as long as you have to deal with data, is how do you move it from one place to another. In this video, I show you one of the easiest tools to do this. And, it is right inside of SQL Server Management Studio.

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Suited for those who already have an understanding of SQL and want to learn more about SQL databases. Also appropriate for absolute beginners

Provides a solid foundation to help students understand the vocabulary and concepts important to SQL

Walks through SQL Server essentials up to advanced topics like backups, security, and indexes

Recommended for those in business and IT who want to enhance their careers

Reviews summary

Sql basics with growth potential

According to students, this course provides a solid foundation in essential SQL concepts. The stored procedure and function explanations are particularly well-received. However, some students have expressed a desire for more complex stored procedure examples. Overall, this course is seen as a valuable resource for those looking to learn the basics of SQL.

Provides a strong foundation in SQL.

"This course provides a solid foundation in essential SQL concepts."

Excellent explanations of stored procedures and functions.

"I like the stored procedure and function explanation."

Could use more complex stored procedure examples.

"I would like to suggest to add some complex stored procedure sample like getting the result to a CSV report or reading CSV and saving it to table."

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 SQL Server Essentials, from Scratch with these activities:

Review SQL Syntax

Show steps

Review the basic syntax of SQL commands to refresh your knowledge before starting the course.

Show steps

Go through your notes or textbook to review the different SQL commands.
Practice writing SQL queries using an online SQL editor or a local database.

Create a Visual Guide to SQL Concepts

Show steps

Create a visual guide, such as a flowchart or diagram, to help you understand and remember key SQL concepts.

Show steps

Identify the key SQL concepts you want to cover.
Brainstorm different ways to represent these concepts visually.
Create your visual guide using a drawing tool or software.

Solve SQL Practice Problems

Show steps

Solve practice problems to reinforce your understanding of SQL concepts.

Show steps

Find a collection of SQL practice problems online or in a textbook.
Solve the problems on your own, referring to your notes or textbooks for help when needed.
Check your answers against the provided solutions.

One other activity

Expand to see all activities and additional details

Show all four activities

Follow Tutorials on Advanced SQL Techniques

Show steps

Expand your knowledge of SQL by following tutorials on advanced techniques such as joins, subqueries, and stored procedures.

Show steps

Search for tutorials on specific SQL techniques you want to learn.
Follow the tutorials step-by-step, practicing the techniques as you go.
Experiment with the techniques on your own to gain a deeper understanding.

Career center

Learners who complete SQL Server Essentials, from Scratch will develop knowledge and skills that may be useful to these careers:

Database Administrator

A Database Administrator keeps an organization's databases up and running. This course will provide you with the foundational knowledge you need to succeed in this role, such as how to create, maintain, and secure databases, and how to recover data in the event of a disaster.

See salaries and explore the career path for Database Administrator

Data Engineer

A Data Engineer builds and maintains the infrastructure that allows data to be stored, processed, and analyzed. This course will help you understand the fundamentals of databases, data storage, and data processing, which are essential for a career in Data Engineering.

See salaries and explore the career path for Data Engineer

Business Intelligence Analyst

A Business Intelligence Analyst uses data to help businesses make better decisions. This course will provide you with the skills you need to extract, analyze, and interpret data, which are essential for a career in Business Intelligence.

See salaries and explore the career path for Business Intelligence Analyst

Data Analyst

A Data Analyst helps manage, track, and analyze corporate data for the purpose of improving efficiency and advising management on data-driven decisions. This course will help build a foundation in databases and management of database-related infrastructure. Through this course, you'll learn essential SQL concepts, which will help you prepare for a career in Data Analysis.

See salaries and explore the career path for Data Analyst

Information Security Analyst

An Information Security Analyst protects an organization's computer systems and networks from cyberattacks. This course will help build a foundation in database security, which is essential for protecting databases from unauthorized access and data breaches.

See salaries and explore the career path for Information Security Analyst

Data Scientist

A Data Scientist uses data to build models that can predict future events. This course will provide you with the foundational knowledge you need to succeed in this role, such as how to clean and prepare data, and how to build and evaluate models.

See salaries and explore the career path for Data Scientist

Market Researcher

A Market Researcher conducts research to help businesses understand their customers and markets. This course will provide you with the skills you need to collect and analyze data, which are essential for a career in Market Research.

See salaries and explore the career path for Market Researcher

Statistician

A Statistician collects, analyzes, and interprets data to help organizations make informed decisions. This course will provide you with the foundational knowledge you need to succeed in this role, such as how to design and conduct statistical studies, and how to analyze and interpret data.

See salaries and explore the career path for Statistician

Software Engineer

A Software Engineer designs, develops, and maintains software systems. This course will provide you with a foundation in database concepts and SQL, which are essential for building and maintaining software systems that interact with databases.

See salaries and explore the career path for Software Engineer

Financial Analyst

A Financial Analyst analyzes financial data to help businesses make investment decisions. This course may be useful for building a foundation in data analysis and data management, which are essential for a career in Financial Analysis.

See salaries and explore the career path for Financial Analyst

Operations Research Analyst

An Operations Research Analyst uses mathematical models to help businesses improve their operations. This course may be useful for building a foundation in data analysis and data management, which are essential for a career in Operations Research.

See salaries and explore the career path for Operations Research Analyst

Data Visualization Specialist

A Data Visualization Specialist creates visual representations of data to help businesses understand their data. This course may be useful for building a foundation in data analysis and data management, which are essential for a career in Data Visualization.

See salaries and explore the career path for Data Visualization Specialist

Actuary

An Actuary uses mathematics and statistics to assess risk and uncertainty. This course may be useful for building a foundation in data analysis and data management, which are essential for a career in Actuarial Science.

See salaries and explore the career path for Actuary

Business Analyst

A Business Analyst helps businesses analyze their operations and make improvements. This course may be useful for building a foundation in data analysis and data management, which are essential for a career in Business Analysis.

See salaries and explore the career path for Business Analyst

Management Consultant

A Management Consultant helps businesses improve their performance. This course may be useful for building a foundation in data analysis and data management, which are essential for a career in Management Consulting.

See salaries and explore the career path for Management Consultant