MP4 | Video: h264, 1280×720 | Audio: AAC, 44.1 KHz, 2 Ch
Genre: eLearning | Language: English + srt | Duration: 66 lectures (10h 45m) | Size: 6.4 GB
With exercises, Case Studies and a unique teaching method to get you deeply involved and in control of your studies
What you’ll learn:
Understand the foundations of T-SQL. Understand the logical query processing of T-SQL
Filter and sorting data: Filter with predicates; filter data with TOP and OFFSET-FETCH; Sorting data
Combining set: Using joins (CROSS, INNER, OUTER joins) Multi-Join queries; Subqueries, Table Expressions, APPLY operator; Set operators (UNION, INTERSECT etc.)
Perform complex data analysis with Window functions: Window aggregate functions, Window Offset functions; LAG, LEAD, PARTITION, FIRST_VALUE, LAST_VALUE
Create tables and enforce data integrity: Create and alter tables; enforce data integrity; Check constraints, Default constraints, Primary/Foreign Key, Unique,
Understand various ways to insert data into tables: INSERT…VALUES; INSERT…SELECT; INSERT…EXEC (for stored procedures); SELECT…INTO;
Understand various ways to update data: Simple update; UPDATE statement; UPDATE based on joins; Nondeterministic UPDATE
Understand the UPDATE All-at-Once concept; UPDATE and Table Expression; UPDATE based on a variable
Understand the different ways to delete data: Simple data; using the DELETE statement; TRUNCATE statement; delete based on a join; delete using Table Expression
Requirements
This course is designed for information technology (IT) professionals who need to query data in Microsoft SQL Server 2012 and above (up to and including SQL Server 2019).
It is assume that before you begin this course, you have a foundational-level understanding of using Transact-SQL (T-SQL) to query data in SQL Server 2012…2019 and have at least 6 months’ experience using the product.
You need access to SQL Server 2012/2014/2019 instance with a logon that has permission to create new databases.
For the purpose of this course, you can use almost any edition of on-premises SQL Server (Standard, Enterprise, Business Intelligence, or Developer)
You need to run the AcotexDB sql script in your SSMS to create the sample database. This file in included in Lesson One
Description
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:
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.
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.
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:
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.
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
table expressions (names queries): derived tables; common table expressions (CTEs); views; inline table-valued functions
the APPLY operator: CROSS APPLY and OUTER APPLY
set operators: UNION; UNION ALL; INTERSECT; EXCEPT set operators
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:
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
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
Define window partitioning, ordering and framing in your window functions
Understand the OVER clause syntax and how to control the window
Avoid framing errors that can lead to unexpected results
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
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:
Insert single and multiple rows into a table by using the INSERT VALUES statement
Insert the result of a query into a table by using the INSERT SELECT statement
Insert the result of a stored procedure or a dynamic batch into a table by using the INSERT EXEC statement
Use a query result to create and populate a table by using the SELECT INTO statement
Use UPDATE statement to modify rows
Update data by using joins
Describe the circumstances in which you get nondeterministic updates
Update data through table expressions
Update data by using variables
Describe the implications of the all-at-once property of SQL on updates
Use the DELETE and TRUNCATE statements to delete rows from a table
Use a DELETE statement based on joins
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
Who this course is for
In this course, we have selected six (6) topics that we think every IT professional who need to query data in SQL Server should know, and we treat this topic in great details.
This course is for SQL Server professional who query data in SQL Server using Transact-SQL (T-SQL). Prospective students must have at least 6 months of working with T-SQL and be familiar with the SQL Server Management Studio (SSMS)
Even though this course teaches some of the same topics as my other T-SQL courses, the approach and presentation is much different
Password/解压密码www.tbtos.com
转载请注明：0daytown » T-SQL for Querying Microsoft SQL Server: Selected Topics