Starting with SQL Server and Azure SQL Database

8 hours

No matter how much ado is currently about big data, semi and unstructured data, the appropriate place for the most important data is still the relational database management system (RDBMS). If you need to take care about the data integrity, then you should use the relational model as the logical model for your data and database schema. You have to learn the standard language for working with data in a RDBMS, the Structured Query Language (SQL).

In this course, you will learn the basics about the relational model, including normalization and constraints. You will start using the language that SQL Server and Azure SQL Database understand, the Transact-SQL (T-SQL) language. You will learn how to write basic and advanced queries. You will see how to create tables and insert, update, and delete data. You will learn how to handle errors and define user transactions. You will also understand the programmatic objects in a SQL database, including views, functions, stored procedures, and triggers.

In this introduction to the course module, you will also get the information on the products and demo database used for the course, how to obtain, and how to install them.

Course Outline

Module 01: Core Transact-SQL SELECT Statement Elements

You will start your journey with the mighty SELECT statement. In the first module, you will learn how to write simple queries.

  • Introducing T-SQL
  • Basic SELECT
  • Aggregating and sorting data

Module 02: More Advanced SELECT Techniques

In this module, you will go in depth with the SELECT statement and learn how to write more advanced queries.
  • Subqueries
  • Window functions
  • Filtering top n rows
  • Using set operators

Module 03: Data Definition Language Statements

In order to store your data, you need to create objects. You will learn how to create schemas, tables, and other objects. The SQL Server system data types overview is a part of this module as well.
  • Introduction to schemas
  • Creating tables
  • Data types

Module 04: Data Modification Language Statements

INSERT, UPDATE, DELETE, and MERGE statements dominate this module.
  • Inserting data
  • Updating and deleting data
  • he MERGE statement and the OUTPUT clause

Module 05: Introduction into the Relational Model

In this module, you will learn about the relational model and the mathematical background behind it. This is the only module without and code, only the theory. However, this theory is fundamental for understanding why and how some code written in SQL works.

  • Introduction to the relational model and domains
  • Relational operators, algebra, and calculus

Module 06: Normalization and Constraints

You will learn how to design a normalized database following the relational model. You will also learn how to enforce data integrity.
  • Normalization and denormalization>
  • Specialization and generalization
  • Constraints

Module 07: Dimensional Modeling

For analytical applications, you typically model your data differently. This module introduces the dimensional model, the most widely used model for centralized and self-service reporting and analyzing your data.
  • Star and snowflake schemas and data warehouses
  • Designing dimensions
  • Designing fact tables

Module 08: Programming Views, Functions, Stored Procedures, and Triggers

Going back to practical database development. You will get an initial knowledge about the programmatic objects inside a database, why to use them, and how to create them.
  • Views
  • User-defined functions
  • Stored procedures
  • Triggers

Module 09: Error Handling and Transactions

In the final module, you will learn how to handle errors and define your own transactions.
  • Implementing error handling
  • Introducing transactions

About the Author

Dejan Sarka

Dejan Sarka focuses on development of database & business intelligence applications. Besides projects, he spends about half of the time on training and mentoring. He is the founder of the Slovenian SQL Server and .NET Users Group. Dejan Sarka is the main author or coauthor of numerous books about databases and SQL Server (among these Training Kits for Microsoft Exams 70-461 and 70-463). Dejan Sarka also developed serveral courses for SolidQ. As an MCT, Dejan Sarka speaks on many local and international events. Some of the international events include conferences such as PASS, TechEd, and DevWeek.

Upcoming classes