SQL101v2: Introduction to Transact-SQL
In this course, you’ll master the skills needed to write Transact-SQL SELECT statements accessing one or more tables. You’ll also learn the various techniques for combining data, filtering the results and how to control and format the statement output.
You’ll gain a foundation understanding of a SQL Server database and use the tools available to discover the database definition, submit queries and view results.
We teach students how to write a query the same way that SQL Server processes a SQL statement – following a step-by-step process for creating SQL queries from business requirements. This approach uses the natural way of breaking down a problem into logical steps. Each step can be validated before moving to the next step. This differs from most courses, which present SQL as a set of features.
Class lectures highlight and explain Transact-SQL concepts which are reinforced with extensive follow-along demonstrations and hands-on labs.
Module 1: The Basics
The material we cover in this module will be used throughout the course. Understanding objects and object names, the processing order of a query, literal values, identifiers and delimiters, SSMS basics, and more… these are things that you will use no matter what you do in SQL.
Lessons:
- Introducing databases
- The database table
- SQL basics
- SSMS basics
- Query basics
- The metadata
Module 2: The Relational Model
Any discussion about relational databases will inevitably require an understanding of the Relational Model. The RM describes the organization and structure of the database and will have an impact on almost everything in this course and back at work. Also, understanding this important topic is the first step in understanding and applying joins.
Lessons:
- Introducing the Relational Model
- The need for joins
- Table relationships
Module 3: Getting the Data
The FROM clause always references a table. The table can be a database table, it can be generated by table-type functions, views, subqueries or joins. In this module, our focus will be on joins and subqueries.
Lessons:
- The FROM clause
- Single-join queries
- Multiple-join queries
- Derived-table Subqueries
Module 4: Filtering the data
Now that we can use the FROM clause to define the base table of our query, we need a way to select what we want to include in our results and what we want to exclude. In this module, we look at different ways we can use the WHERE clause to filter the data.
Lessons:
- Introduction
- Expressions
- Data type precedence
- Advanced filtering
Module 5: Displaying the data
At this point, we have learned how to define the data to query using the FROM and WHERE clauses. We are ready to project the results from our query and look at ways that the SELECT clause can be used to affect the rows that the query returns. We will also examine several of the system functions..
Lessons:
- Introduction
- The ORDER BY clause
- The column list
- System functions
- CASE expression
Module 6: Aggregating the data
All levels of management need information to provide decision support. Lower levels of management may need to make decisions about what products and how much to have in stock, and higher levels of management may need to understand industry trends to support strategic decisions about where to guide the company. In this module, you will learn how to create Aggregate queries; they provide this powerful type of high-level information.
Lessons:
- Introduction
- Aggregation with GROUP B
Module 6: Maintain the data
Everything we’ve seen so far in this course has been about reading the data. In this module, we end the course with a look at the three write operations that we collectively call data maintenance: Insert, delete, and update.
Lessons:
- Inserting Data
- Updating Data
- Deleting Data
