70-461, 761: Querying Microsoft SQL Server with Transact-SQL - learn T-SQL

Link to this course(special discount)
https://www.udemy.com/course/70-461-session-2-querying-microsoft-sql-server-2012/?ranMID=39197&ranEAID=Gw%2FETjJoU9M&ranSiteID=Gw_ETjJoU9M-XnyZK8zU6T3tQw.xxpP5gg&LSNPUBID=Gw%2FETjJoU9M&utm_source=aff-campaign&utm_medium=udemyads
70-461, 761: Querying Microsoft SQL Server with Transact-SQL - learn T-SQL
What you will learn in this course ?
What you'll learncreate tables in a database and ALTER columns in the table.Know what data type to use in various situations, and use functions to manipulate date, number and string data values.retrieve data using SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.JOIN two or more tables together, finding missing data.INSERT new data, UPDATE and DELETE existing data, and export data INTO a new table.Create constraints, views and triggersUse UNION, CASE, MERGE, procedures and error checkingApply ranking and analytic functions, grouping, geography and geometry databaseCreate subqueries and CTEs, PIVOTs, UDFs, APPLYs, synonyms.Manipulate XMLs and JSONs.Learn about transactions, optimise queries and row-based v set-based operationsShow moreShow less
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 M-------------------------This course is the foundation for the Microsoft Certificate 70-461: ""Querying Microsoft SQL Server 2012"" and 70-761 ""Querying Data with Transact-SQL"".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. This will allow up to fully complete objective number 1 from the 70-461 exam.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. By doing this, we will complete objectives 2, 3, 4 and 5 from the 70-461 examSession 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. By doing this, we will complete objectives 11, 12, 13 and parts of 6 and 18 from the 70-461 exam.Session 5We'll will now be creating aggregate queries, working through objective 9 of the exam 70-461. 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. If you want to take the 70-461 exam, 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, working through objectives 7b-e of the exam 70-461. We'll be created 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 (objective 14), including the three different types of User Defined Functions (UDF): scalar functions, inline table functions, and multi-statement table functions. We'll then complete objective 6 by looking at synonyms and dynamic SQL, and objective 8 by looking at the use of GUIDs. We'll also look at sequences. We'll have a look at XML. Finally, for SQL Server 2016 and later (exam 70-761), we'll examine JSON and Temporal Tables.Session 7In this session we'll be looking at t