About this Course
During this 5-day course, students will learn Transact-SQL as implemented in SQL Server 2008, 2012 and 2014. The course starts by establishing a foundation understanding of database concepts and terminology. Students are then prepared to use various Microsoft tools to submit queries and view the result.
Following a logical process for creating SQL queries from business requirements, students learn how to write a query based on the way SQL Server processes the SQL statement. This differs from most courses, which present SQL as a set of features. This approach uses the natural way of breaking down the problem into a logical set of steps. Each step can be validated before moving to the next step.
Finally, the course teaches the student how to use T-SQL statements inside common database objects like Views, Stored Procedures and User-Defined Functions.
Lectures that highlight and explain T-SQL concepts are reinforced with extensive demonstrations and hands-on labs.
This course is intended for SQL Server Developers, Database Administrators, and System Engineers who are responsible for writing T-SQL queries for an application
At Course Completion
After attending this course, students will be able to:
- Describe the objects that make up a database, data relationships within the database and how databases guarantee uniqueness of defined objects.
- Understand different sublanguages of SQL.
- Use the SQL Server Management Studio
- Write a simple SELECT statement, returning all the columns and rows.
- Formulate and write a meaningful join strategy to bring together all necessary data.
- Utilize a subquery to reshape data before using it in a query
- Write a WHERE clause to filter the rows
- Control the display of data
- Write Transact-SQL queries and aggregated values
- Define the attributes of a database transaction and generate a result set
- Create a database view and in-line table value functions and execute simple scalar/multi-statement user-defined functions
- Write procedural logic for Stored Procedures and User-defined Functions
1. Database Concepts
Upon completion of this module, students will be able to describe the objects that make up a database, data relationships within the database and how databases guarantee uniqueness of defined objects.
- Conceptual View
- Logical View
- Database Diagramming
2.Using the Tools
Upon completion of this module, students will be able to use SQL Server Management Studio to discover the database definition including tables and data relationships and how databases guarantee uniqueness of defined objects.
- SQL Server Management Studio (SSMS) Overview
- Viewing Basic Table Metadata
- Viewing Other Table Constraints
- SSMS Database Diagramming
3. Getting the Data
Upon completion of this module, students will be able to write a simple SELECT statement, returning all the columns and rows. Students will be able to formulate and write a meaningful join strategy to bring together all necessary data. The students will also be able to utilize a subquery to reshape data before using it in a query, write joins using the old and new form join syntax and write subqueries using Common Table Expressions.
- SQL Language
- The SELECT Statement
- The FROM clause
- Joining Tables
- Using Views and Subqueries
- Reformulating Subqueries using Common Table Expressions
- Old Form Join Syntax
4. Filtering the Data
Upon completion of this module, students will be able to write a WHERE clause to filter the rows based on equality, a list of values, a range of values, a wildcard character condition and the existence of values in a subquery. The student will also be able to consider the impact on filtering based on implicit data conversions using data type precedence and how to create a proper search argument.
- When is the WHERE clause processed?
- The WHERE Clause
- Filtering Data with Equality
- Using a List of Values
- Using Subqueries to Filter Data
- Data Type Precedence
- Proper Search Arguments
5. What to Display
Upon completion of this module, students will be able to control the display of data out of a SQL SELECT, rename column names, write expressions using standard operators and built-in function to change output values, conditionally change the output values using CASE expressions, sort the result set, eliminate duplicate rows and control the number of rows returned when necessary.
- When is the Column List Processed?
- Identifying Columns
- Renaming Columns through a Column Alias
- When and How to use Built-in Functions
- Using Subqueries in the Column List
- Using the CASE Expression
- Ordering Rows
- Controlling Row Display
6. Aggregating the Data
Upon completion of this module, students will be able to write Transact-SQL queries that add up values across rows based by group, filter the result set based on aggregated values, include aggregated values with detail rows, pivot rows into columns and define rank and row numbers.
- When is the Aggregation Performed?
- Grouping and Aggregating
- Pivoting Data
- Aggregate Window Functions
- Using Ranking Functions
7. Modifying the Data
Upon completion of this module, students will be able to define the attributes of a database transaction, control the start and end of a transaction, add rows to a table, delete rows from a table, update values in a table and merge a result set into a table. The student will also be able to generate a result set of changes made during an INSERT, UPDATE, DELETE or MERGE statement.
- Defining Database Transactions
- INSERT Statement
- DELETE Statement
- UPDATE Statement
- MERGE Statement
Upon completion of this module, students will be able to create a database view and in-line table value functions that filter columns and rows. Students also will be able to create and execute a simple stored procedure with parameters and create and execute simple scalar/multi-statement user-defined functions.
- Layers of Abstraction-Three Schema Architecture
- Creating and Using Views
- Creating and Using User-Defined Scalar Functions
- Creating and Using Inline-Table Valued Function
- Creating and using Stored Procedures
- Creating and Using Multi-Statement Table-Valued Functions
9. Procedural Logic
Upon completion of this module, students will be able to write Transact-SQL scripts that loop, test conditions, handle exceptions and conditions and display messages to users. Students will also be able to write procedural logic for Stored Procedures and User-defined Functions.
- SQL Batch
- Niladic Functions
- Local Variables
- Conditional Processing
- Iterative Processing
- Exception Handling
- Structured Exception Handling
- PRINT and RAISERROR Statement
Before attending this course, students should have familiarity with programming and developing software using any language.
Venue: LIVE Online
Live Online Training
Get the same training you expect in the classroom without leaving your office or home. These are NOT recorded classes. They are LIVE sessions with an expert instructor. We use the latest in video conferencing technologies and audio so you can confidently participate in any class just like being right there in person. We guarantee the effectiveness of our online training delivery approach that we will give you your money back if you are not totally satisfied. Ask us for a demo.
Online class requirements:
- Moderate to fast Internet
- A phone or computer headset is required in order to hear the instructor/moderator). You can use Computer Audio (VoIP) or you can dial in from a regular phone. For convenience, we recommend a hands-free headset or phone.
- Training software must be installed on your computer (trial versions are acceptable)
- RECOMMENDED: Dual Monitors or computers. For optimal online learning experience, we recommend participants have dual monitors or two computers. Your online classroom credentials allow you to join multiple times from multiple computers. Participants should use one monitor or computer to view the instructor’s shared screen and another monitor or computer to work with the software.
What happens when you enroll in an online class
When you register for an online class, you will receive a welcome email followed by login access to the Citrix GoToTraining virtual classroom. A workbook (printed copy or eBook) will be sent to you prior to the start of class.
Online Training Advantages
Convenience: You don’t have to travel and can attend from your home, office or anywhere with an internet connection. Our online classes are conducted using GoToTraining, a more robust version of the popular GoToMeeting screen sharing and conferencing platform. To accommodate multiple time zones, courses are typically scheduled from 10am – 5pm Eastern with a one-hour lunch break at 12:30 – 1:30 pm Eastern and a 10-minute break in the morning and afternoon. When conducting custom online course for your group, class times can be modified to accommodate your timezone.
Interactive Learning: Our online training is fully interactive. You can speak and chat with the instructor and classmates at any time. Various interactive techniques are used in every class. Our small class sizes (typically 4 – 8 students), allow our instructors to focus on individual performance and issues and to work closely with you to meet your unique needs. Classes are designed to be a hands-on learning experience, providing opportunities for you to try your new skills while the instructor is available for review, questions, and feedback. You have the option to give the instructor permission to view your computer to provide one-on-one assistance when needed.