|
所在平台: Udemy |
课程主页: https://www.udemy.com/course/70-461-session-2-querying-microsoft-sql-server-2012/
课程评论：没有评论
本课程“70-461, 761：使用 Transact-SQL 查询 Microsoft SQL Server”是一门全面的 T-SQL 学习之旅，从基础知识到高级概念，旨在帮助学习者掌握 SQL Server 数据查询的核心技能。 课程共分为七个主要会话： **会话一：基础构建** 本会话重点介绍 SQL Server 的安装，以及如何创建和删除表。在此基础上，课程深入讲解了数据类型及其函数，为后续的 T-SQL 学习奠定坚实的基础。 **会话二：数据操作与检索** 学习者将在此会话中创建使用数据类型的表，并插入数据。随后，通过 SELECT、FROM、WHERE、GROUP BY、HAVING 和 ORDER BY 子句进行数据检索和汇总。课程还会讲解如何使用 JOIN 连接表，找出数据缺失和不一致之处，并演示 UPDATE 和 DELETE 数据操作。 **会话三：数据库结构与完整性** 本会话将运用之前的数据创建视图（存储 SELECT 查询）和触发器（自动执行代码）。课程还会检查数据库结构，添加 UNIQUE、CHECK、PRIMARY KEY 和 FOREIGN KEY 等约束，以防止错误数据录入。 **会话四：高级查询与数据合并** 本会话将通过创建存储过程来封装命令，实现参数化执行。同时，会讲解 TRY、CATCH 和 THROW 错误处理机制。此外，还将探索 UNION、UNION ALL、INTERSECT、EXCEPT、CASE、ISNULL、Coalesce 以及强大的 MERGE 语句，用于组合不同数据集。 **会话五：聚合与分析函数** 本会话侧重于聚合查询，回顾了 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE 等排名函数，以及 LAG、LEAD、FIRST_VALUE 和 LAST_VALUE 等 SQL Server 2012 推出的分析函数。课程还会介绍 ROLLUP、CUBE、GROUPING SETS 和 GROUPING_ID 等分组和汇总方式，并触及几何和地理数据类型及其应用。 **会话六：子查询、CTE 与用户定义函数** 本会话深入探讨子查询和相关子查询，以及使用 WITH 语句的公用表表达式 (CTE)。课程还将介绍三种用户定义函数 (UDF)：标量函数、内联表函数和多语句表函数。此外，还会涉及同义词、动态 SQL、GUIDs、序列，并针对 SQL Server 2016 及以上版本介绍 XML、JSON 和时间表。 **会话七：事务、索引与性能优化** 本会话讲解事务的管理，包括显式启动和结束，以及事务可能导致的阻塞问题。同时，将深入介绍索引的作用及其如何优化查询性能，并演示如何使用动态管理视图 (DMVs) 改进索引使用。课程还会讲解游标的编写及其使用场景，以及标量 UDF 对性能的影响。 课程无需任何先验知识，甚至会指导初学者免费安装 SQL Server。此外，课程还包含定期测验，帮助巩固学习内容。完成本课程后，学习者将能够熟练地操作数字、字符串和日期，创建和管理数据库及表，插入数据，进行数据分析，并全面掌握 T-SQL 的应用。 **请注意：** 此课程虽然旨在帮助学习者理解 T-SQL，但与 Microsoft 官方认证 70-461 和 70-761 已不再提供，并且与 Microsoft 没有隶属、认可或赞助关系。然而，课程内容对当前 DP-300 (Azure 数据库管理员关联) 和 DP-600 (使用 Microsoft Fabric 实现分析解决方案) 考试仍有帮助。
Previously available as seven separate courses, now presented in one big course.Reviews"The instructor explain the things in great details. Very easy to follow." - Linda Shen"Excellent course, valuable lessons, very well taught at a great pace." - Shane Tanberg"Must get tutorial. Love it" - Hayford I Osumanu"Perfect step by step guide to learning. Best I've seen." - Charles Schweiger"This course is very well thought out. Its one of the better 70-461 courses on Udemy." - Isrrael MThis course is the foundation for the Microsoft Certificate 70-461: "Querying Microsoft SQL Server 2012" and 70-761 "Querying Data with Transact-SQL". Please note - these certificates are no longer being offered by Microsoft. However, the exam requirements allow you to have a good understanding of T-SQL.It also will help with the current exams DP-300 (Azure Database Administrator Associate) and DP-600 (Implementing Analytics Solutions Using Microsoft Fabric)Please note: This course is not affiliated with, endorsed by, or sponsored by Microsoft.Session 1The basics presented are: how to install SQL Server, and how to create and drop tables.We then try to create a more advanced table, but find that we need to know more about data types - so we go into some detail about data types and data functions, the foundation of T-SQL.Session 2We'll create tables which use these, and then INSERT some data into them. Then we'll write queries which will retrieve and summary this data, using SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY. We'll then JOIN these tables together to find where we are missing data and where we have inconsistent data. We'll then UPDATE and DELETE data from the tables. Session 3We'll now use that data to create views, which enable us to store these SELECT queries for future use, and triggers, which allow for code to be automatically run when INSERTing, DELETEing or UPDATEing data.We'll look at the database that we developed in session 2, and see what is wrong with it. We'll add some constraints, such as UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY constraints, to stop erroneous data from being added some data. Session 4We will further encapsulate our routines by creating procedures, allowing us to EXECUTE parameterised commands with just one statement, and we'll add some error handling with TRY, CATCH and THROW. We'll also combine datasets together, by looking at UNION and UNION ALL, INTERSECT and EXCEPT, CASE, ISNULL and Coalesce, and the mighty MERGE statement. Session 5We'll will now be creating aggregate queries. We'll be reviewing the ranking functions ROW_NUMBER, RANK, DENSE_RANK and NTILE. We'll look at the 8 analytic functions news to SQL Server 2012, such as LAG, LEAD, FIRST_VALUE and LAST_VALUE.We'll look at alternative ways of grouping and adding totals, using ROLLUP, CUBE, GROUPING SETS and GROUPING_ID. We'll also look at the geometry and geography data types, plotting locations on a grid, together with functions and aggregates.Session 6We'll will now be creating sub-queries and correlated subqueries, where the results of the subquery depend on the main query. We'll be looking at Common Table Expressions using the WITH statement, and we'll be using what we have learned to solve a common business problem.We'll be looking at functions, including the three different types of User Defined Functions (UDF): scalar functions, inline table functions, and multi-statement table functions. We'll then look at synonyms and dynamic SQL, and the use of GUIDs. We'll also look at sequences. We'll have a look at XML and, for SQL Server 2016 and later, we'll examine JSON and Temporal Tables.Session 7In this session we'll be looking at transactions, seeing how to explicitly start and end them, and finding out how they can block other users in the database. Then we'll see about how to indexes and their role in optimising queries. We'll also see how we can use Dynamic Management Views to see how we can improve our use of indexes. We'll then look at how to write a cursor, and when to use this row-based operation, and the impact of using scalar UDFs.No prior knowledge is required - I'll even show you how to install SQL Server on your computer for free!There are regular quizzes to help you remember the information.Once finished, you will know what how to manipulate numbers, strings and dates, and create database and tables, create tables, insert data and create analyses, and have an appreciation of how they can all be used in T-SQL.
