Microsoft MTA: Database Administration Fundamentals

This is a practical course for someone new to relational databases that moves you through basic concepts right into real-world usage, demonstrating core tasks in Microsoft SQL Server itself.
Course info
Rating
(514)
Level
Beginner
Updated
Oct 2, 2012
Duration
4h 35m
Table of contents
Getting Started with Database Administration Fundamentals
Different Types of Databases
Relational Database Concepts
Database Data Types
The SQL Language
Introducing the SQL Server Management Studio
Creating and Using Tables
Creating and Using Views
Creating Stored Procedures
Basic Queries
Complex Conditions
Multi-Table Queries
Modifying Data
Database Normalization
Keys and Constraints
Indexes
Security
Backup and Recovery
Continual Maintenance
Preparing for Your DBA Fundamentals (98-364) Exam
Next Steps
Description
Course info
Rating
(514)
Level
Beginner
Updated
Oct 2, 2012
Duration
4h 35m
Description

This is a practical course for someone new to relational databases that moves you through basic concepts right into real-world usage, demonstrating core tasks in Microsoft SQL Server itself. You'll not only gain new knowledge, but cement that knowledge by seeing it in action and being able to follow along at home.

About the author
About the author

Don Jones' broad IT experience comes from 20 years in the business, with a strong focus on Microsoft server technologies.

More from the author
PowerShell & DevOps Global Summit 2016 Sessions
Intermediate
25h 35m
Apr 29, 2016
More courses by Don Jones
Section Introduction Transcripts
Section Introduction Transcripts

Different Types of Databases
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Different Types of Databases lesson in the Database Administration Fundamentals course. In this lesson we'll be covering some of the fundamental concepts about databases, including defining what a database actually is. We'll look at three different types of databases that you may work with, flat file databases, hierarchical databases, and relational databases.

Relational Database Concepts
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Relational Database Concepts lesson in the Database Administration Fundamentals course. In this lesson, we'll talk about some of the foundation concepts for relational databases. Specifically we'll talk about the relationships between tables, rows, and columns, do a brief overview of something called normalization, briefly cover indexes, and talk about SQL Server's physical storage, pages, and physical files.

Database Data Types
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Database Data Types lesson in the Database Administration Fundamentals course. In this lesson we'll be talking about database data types, we'll talk about what data types are, how data types are stored within the database, and about SQL Server's data type conversion capabilities. Each column in a database table, whether it's a customer name or an order total or a date, it doesn't matter, they all have a designated data type that you select when you design the table, and this data type indicates the type of data that the column will store. These are set when you create the table's design or schema, and some cases they can be a little bit difficult to change once the table actually contains data. So this is something that has to be thought out ahead of time, well in advance, so that you can get the right data type for whatever type of data it is you plan to store. SQL Server includes a fairly large number of built-in data types, and programmers can create their own, or user-defined data types. The built-in data types suffice for almost all applications, so seeing user-defined data types is actually pretty rare. SQL Server also distinguishes between CLR data types, which are data types from Microsoft's. NET Framework Common Language Runtime, and that's actually embedded in SQL Server. So we'll cover the built-in data types first.

The SQL Language
Hello, and welcome to TrainSignal. I'm Don Jones, and this is The SQL Language lesson in the Database Administration Fundamentals course. SQL, or Structured Query Language, is the language that SQL Server takes its name from, and in this lesson we'll talk about some of the basics of that language. We'll focus on really three different portions of it, DML, or Data Manipulation Language, DDL, or Data Definition Language, and Control Language.

Introducing the SQL Server Management Studio
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Introducing the SQL Server Management Studio lesson of the Database Administration Fundamentals course. In this lesson I'll be introducing you to SQL Server Management Studio, the primary graphical user interface used to manage and interact with SQL Server as an administrator. I'll show you where to get the studio, we'll briefly explore the interface to familiarize you with where things are, identify some of the key objects that you'll be working with, I'll show you how to execute queries, which will be a big part of what you do in Studio, and briefly mention some other tools that you might need to know about when you start administering SQL Server. Once again, SQL Server Management Studio, or just Management Studio, or some people will call it SSMS, is the main graphical user interface by which SQL Server is managed. It includes capabilities for issuing queries, reviewing query results, creating and modifying database schemas, and perhaps most importantly, managing server and database security.

Creating and Using Tables
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Creating and Using Tables lesson in the Database Administration Fundamentals course. In this lesson we'll start using SQL Server by creating and using some tables. I'll show you how to create tables in the graphical user interface, how to create them by using DDL, the Data Definition Language, how to review existing table designs, and where to locate SQL Server's system tables.

Creating Stored Procedures
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Creating Stored Procedures lesson in the Database Administration Fundamentals course. In this lesson we'll talk about SQL Server stored procedures, including the purpose of stored procedures, how to create stored procedures in the graphical user interface, and how to create them by using the data definition language. I'll show you how to execute a stored procedure, and try and help you understand the dangers of SQL injection attacks.

Basic Queries
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Basic Queries lesson in the Database Administration Fundamentals course. In this lesson we'll dive into the most basic and fundamental query in SQL, the SELECT query. We'll start with a simple SELECT FROM query, add a WHERE clause, add sorting with an ORDER BY clause, and then discuss columns aliases and naming rules.

Complex Conditions
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Complex Conditions lesson in the Database Administration Fundamentals course. In this lesson we'll mainly be working with the WHERE clause of the SELECT query, we'll look at operators, combining conditions, and several different predicates, IN, LIKE, BETWEEN, UNION, and EXCEPT and INTERSECT. Most of the conditions we'll be examining in this lesson will primarily be a part of your query's WHERE clause. For each one, I'll discuss what it's for, and then look at an example query that uses it. Keep in mind that these conditions can usually be combine to create very powerful filter specifications.

Multi-Table Queries
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Multi-Table Queries lesson of the Database Administration Fundamentals course. Multi-table queries are where SQL Server really shines. This is what makes it a relational database management system. In this lesson we'll cover the JOIN clause, talk about performance and multi-table queries, and then look at two specific types of joins, INNER JOINS and OUTER JOINS.

Modifying Data
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Modifying Data lesson in the Database Administration Fundamentals course. In this lesson we'll be covering the various ways that you can modify data in your database tables, including the INSERT INTO command, the UPDATE command, the DELETE command, TRUNCATE TABLE, and DROP TABLE. Because we're going to be talking about deleting data, we'll also do a quick overview of referential integrity. You were introduced to all of these modification statements in another lesson, but now we're going to be using them against a real database, so as we go, we'll also cover a variety of additional gotchas and tips and techniques that make using these more efficient and effective. Now we've already talked a bit about SQL Server indexes and how those can help speed up querying the database. When we start talking about modifying data, well, we've moved into an area where SQL Server indexes can both help and hurt. If your modification statement contains a WHERE clause to limit the scope of the modification, and many modification statements will contain a WHERE clause, then having the right indexes can speed it up, because those indexes essentially speed up the WHERE clause. But, every modification means that the indexes also have to be modified, so having more indexes can slow things down. Consider that as we review the modification queries in detail, and keep it in mind as you think about indexes.

Database Normalization
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Database Normalization lesson in the Database Administration Fundamentals course. In this lesson we'll be talking about something called database normalization. Now it's a somewhat complex topic, so we'll break it down into the five normal forms, first normal form (1NF), second normal form (2NF), third normal form (3NF), and we'll briefly discuss fourth (4NF) and fifth normal form (5NF). We'll also talk briefly about the concept of denormalization. The main purpose of normalization is to reduce data storage and to reduce data redundancy by making sure that any given piece of data is stored only one time. There are five techniques involved in fully normalizing a database design, although many people consider the first three forms to be sufficient and practical for most purposes. The five normal forms are shortened as 1NF, 2NF, for first normal form, second normal form, and so forth. Now these are best seen in examples, so in addition to defining them we'll also cover an example for at least the first three.

Keys and Constraints
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Keys and Constraints lesson in the Database Administrations Fundamentals course. In this lesson we'll be talking about keys and constraints. We'll start with primary keys, move on to composite keys, and discuss the role of foreign keys. And I'll also help you understand the role of constraints.

Backup and Recovery
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Backup and Recovery lesson for the Database Administration Fundamentals course. In this lesson we'll talk about the basics of SQL Server backup and recovery, including database recovery models, how to make a database backup, how to make a transaction log backup, a little bit about backup devices, and we'll cover the database restoration process. Remember that in a previous lesson you learned how SQL Server modifies data and how the transaction log works within that modification process. The log is very important to the entire backup and restore infrastructure, so make sure you review that lesson if needed.

Continual Maintenance
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Continual Maintenance lesson in the Database Administration Fundamentals course. In this lesson we'll talk about four important maintenance topics, statistics, indexes, backups, and maintenance plans. Many of these are things we've discussed in previous lessons, but we'll sort of bring them all together in the form of a continual maintenance procedure. Like any complicated complex machine, SQL Server does need ongoing maintenance to keep it running smoothly. While you won't have to change any belts or change the oil, there are some things you'll definitely have to do on a regular basis to keep the server working well. We'll cover some of the major tasks in this lesson. This is in addition to periodic tasks like performance tuning, which may not be necessary in every database scenario, and which might not be necessary on the same schedule as some of this other continual maintenance.

Next Steps
Hello, and welcome to TrainSignal. I'm Don Jones, and this is the Next Steps lesson in the Database Administration Fundamentals course. In this lesson we'll briefly review what you've learned in this course, provide some followup, and discuss what your next steps might be. In this course you've learned core database concepts. I've shown you how to create database objects and how to manipulate data inside of a database. You should have a strong understanding by now of data storage and understand the key tasks for administering a database, such as setting security permissions and performing backups. For followup, you should prepare to take your Database Administration Fundamentals certification exam. For more information on that exam, visit the Microsoft Learning website at this URL. So what's next? Well, after conquering Database Administration Fundamentals, start getting some hands on, and preferably on-the-job experience with SQL Server. Eventually, with six months to a year of on-the-job experience, you'll be ready to take an exam in the Microsoft Certified Solutions Associate Series, or the Microsoft Certified IT Pro Series. Look for exams focused on SQL Server administration. You may also eventually work up to a Microsoft Certified Solutions Expert, or, Microsoft Certified Database Administration Certification, again, focusing on SQL Server exams. So we've covered what you learned, we've done a little bit of followup, and I've shown you what's next on your certification path in the world of database administration. Thank you for viewing this lesson and this course. I appreciate your time and I hope you found this information to be helpful.