T-SQL for Querying Microsoft SQL Server: Selected Topics

In this Microsoft Transact-SQL (T-SQL) course we concentrate on five Transact-SQL topic that every IT professional who need to query data in Microsoft SQL Server should know. These topics are:

1. Foundation of T-SQL querying - You learn the importance of writing your queries in a relational way and how to avoid using cursors. We describe the root of the T-SQL language and the Microsoft SQL Server implementation of the standard SQL. The course provides you will clues for the mindset that you have to adopt. In this section, we describe the most important concepts you need to know about the language: the logical query processing. After this lesson, you will be able to (a) describe the foundation that T-SQL is based on (b) describe the importance of using Transact-SQL in a relational way, and (c) use correct terminology when describing T-SQL-related elements. This lesson finishes with exercises and case scenarios and quizzes to test your understanding of the topics.

   
   

2. Filtering and sorting data - Filtering and sorting data are the most foundational, as well as most common, aspect of querying data, and many queries involve sorting. The traditional way to filter data in T-SQL is based on predicates. However, T-SQL also supports filtering data based on another concept -- a specified number of rows and ordering. In this option, we look at TOP and OFFSET-FETCH. As for sorting, even though it might seem like a trivial aspect of querying, it is actually a source of quite a lot of confusion and misunderstanding, which this lesson tries to clarify. Lesson in this section includes: (a) Filtering data with predicates (b) Sorting data and (c) Filtering data with TOP and OFFSET-FETCH. We treat topics such as predicates, three-valued logic, search arguments, combining sets, filtering character data, filtering date and time, using the ORDER BY clause to sort data, understanding when order is/is not guaranteed. This lesson finishes with exercises, quizzes and case scenarios.

   
   

3. Combining sets - T-SQL provides a number of different ways to combine data from multiple tables. In this part of the course, we describe the different options including:

   1. joins: often data that you need to query is spread across multiple tables. The more normalized the environment is, the more tables you usually have. The tables are usually related through keys, such as foreign key in one table and primary key in the other. Then you can use joins to query the data from the different tables to match the rows that need to be related. Here, you learn how to write queries that use cross joins, inner joins and outer joins.

   2. subqueries: T-SQL supports nesting queries. This is a convenient part of the language that you can use to refer to one query's result from another. Under this topic we learn how to use: (1) self-contained subqueries and correlated subqueries (2) Subqueries that return scalar and multi-valued results, derived tables

   3. table expressions (names queries): derived tables; common table expressions (CTEs); views; inline table-valued functions

   4. the APPLY operator: CROSS APPLY and OUTER APPLY

   5. set operators: UNION; UNION ALL; INTERSECT; EXCEPT set operators

      
      

4. Window functions: This section of our course focuses on data analysis operations. A data analysis function is a function applied to a set of rows, and returns a single value (e.g. the SUM aggregate function). A data analysis function can be either a group function or a window function. In this section we treat only window functions. We teach you the following:

   1. Use window aggregate: Window aggregate functions (SUM, AVG, COUNT, MAX, MIN) apply to a window of rows defined by the OVER clause. We teach you how to use these powerful functions to produce results such as running totals, optionally partitioned by any column of your choice and show both detail and aggregate values in the same result

   2. Window offset functions: Window offset functions return an element from a single row that is a given offset from the current row in the window partition, or from the first or last row in the window frame. You will learn about the following window offset functions: LAG, LEAD, FIRST_VALUE, and LAST_VALUE

   3. Define window partitioning, ordering and framing in your window functions

   4. Understand the OVER clause syntax and how to control the window

   5. Avoid framing errors that can lead to unexpected results

      
      

5. Creating tables and enforcing data integrity: Tables are the primary method of data storage in Microsoft SQL Server. To use tables, you need to master how to create them, in addition to adding constraints to protect the integrity of the stored data. In this section, you learn how to create and alter tables, in addition to enforcing data integrity between tables by using table constraints: We treat the following topics:

   • Use the CREATE TABLE statement to create a table

   • Understand how to specify data types for columns

   • Use the ALTER TABLE statement to change some properties of columns

   • Implement declarative data integrity on your tables

   • Define and use primary key constraints

   • Define and use unique constraints

   • Define and use foreign key constraints

   • Define and use check constraints

   • Define default constraints

     
     

6. Inserting, Updating, and Deleting Data: This section of the course covers certain aspects of data modification. It describes how to insert , update, and delete data using T-SQL statements. You will master the following topics after this section:

   1. Insert single and multiple rows into a table by using the INSERT VALUES statement

   2. Insert the result of a query into a table by using the INSERT SELECT statement

   3. Insert the result of a stored procedure or a dynamic batch into a table by using the INSERT EXEC statement

   4. Use a query result to create and populate a table by using the SELECT INTO statement

   5. Use UPDATE statement to modify rows

   6. Update data by using joins

   7. Describe the circumstances in which you get nondeterministic updates

   8. Update data through table expressions

   9. Update data by using variables

   10. Describe the implications of the all-at-once property of SQL on updates

   11. Use the DELETE and TRUNCATE statements to delete rows from a table

   12. Use a DELETE statement based on joins

   13. Use a DELETE statement based on table expressions

COURSE STRUCTURE:

· The course is video-based. We utilize a custom interface to explain concepts and use SQL Server Management Studio (SSMS) to provide coding examples and exercises. Please watch our promotional video see a demonstration of our video presentation. We expect you to follow the codes we explain to you in your own SSMS. The course is based on a sample database called AcotexDB. Run the AcotexDB.sql SQL script in your SSMS to create the sample database.

2. QUIZZES:

· Each lesson comes with about half a dozen multiple choice quizzes. We suggest that you immediately repeat the lesson if you experience difficulties completing these quizzes.

3. HANDS-ON EXERCISES and CASE SCENARIOS:

• At the end of each section, we provide both hands-on exercises and case scenarios in Word document or Excel macro files (docm) based on the topics in the section. Answers to the exercises are provided but hidden. We display a button beneath each exercise that you can click (toggle) to display/hide the solutions to the exercises. We suggest that you give the exercises a go before you look at the solution. You are to perform these exercises in your SSMS.

• In the case scenarios, we describe a scenario about a problem for a fiticious company and ask you (a consultant) to provide solution. Again, we provide solutions to these scenarios.

4. SOLUTIONS TO EXERCISES

We have powered our exercises with VBA code (with buttons) to make the exercises as interactive and interesting as possibel. We hope you will enjoy the design of our course, and please, if you go ahead to take this course, let us receive your feedback and suggestions for improvement.

THANK YOU and GOOD LUCK