70–461, 761 Querying Microsoft Sql Server With Transact–Sql

What you'll learn
create 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 triggers
Use UNION, CASE, MERGE, procedures and error checking
Apply ranking and analytic functions, grouping, geography and geometry database
Create subqueries and CTEs, PIVOTs, UDFs, APPLYs, synonyms.
Manipulate XMLs and JSONs.
Learn about transactions, optimise queries and row-based v set-based operations
Requirements
You need to know how to use a computer, and hopefully know how to use a spreadsheet.
No prior knowledge of SQL Server required.
SSMS cannot be installed on the Mac OS. If you wish to install it on a Mac, you will need either to dual boot into Windows or be running Parallel Desktop.
You don't even need SQL Server installed - I'll show you have to install it on your computer for free!
There is a 30-day money back guarantee of this Udemy course.
Why not have a look at the curriculum below and see what you can learn?
Description
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)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.
Overview
Section 1: Session 1 - Let's begin, and let's download SQL Server
Lecture 1 Introduction
Lecture 2 Welcome to Udemy
Lecture 3 The Udemy Interface
Lecture 4 Do you want auto-translated subtitles in more languages?
Lecture 5 Exam update
Lecture 6 Curriculum
Lecture 7 Do you have Windows 7, 8 or Windows Vista?
Lecture 8 Downloading SQL Server Developer 2022
Lecture 9 Installing SQL Server Developer 2022
Lecture 10 Which version of SSMS should I use?
Lecture 11 Installing SQL Server Management Studio (SSMS)
Section 2: Session 1 - Starting SQL Server
Lecture 12 Opening SQL Server
Lecture 13 Looking at SQL Server Management Studio
Lecture 14 Create a database
Lecture 15 Creating our first queries
Lecture 16 Practice Activity Number 1 - Writing mathematical queries
Lecture 17 Coding Exercises
Section 3: Session 1 - Creating tables - First pass
Lecture 18 Creating a table - first pass using GUI
Lecture 19 Creating a table - first pass using T-SQL
Lecture 20 Entering data using the GUI
Lecture 21 Entering data using T-SQL
Lecture 22 Retrieving data
Lecture 23 Deleting the data, then the table
Lecture 24 Practice Activity Number 2
Section 4: Session 1 - Number types and functions
Lecture 25 Creating an Employee table
Lecture 26 Session 1 Resources
Lecture 27 Creating temporary variables
Lecture 28 Integer numbers
Lecture 29 Practice Activity Number 3
Lecture 30 Practice Activity Number 3 - Solution
Lecture 31 Non-integer numbers
Lecture 32 Mathematical functions
Lecture 33 Converting between number types
Lecture 34 Practice Activity Number 4
Lecture 35 Practice Activity Number 4 - Solution
Section 5: Session 1 - String data types and functions
Lecture 36 Strings
Lecture 37 String Functions - extraction
Lecture 38 TRIM
Lecture 39 NULL - an introduction
Lecture 40 Joining two strings together
Lecture 41 Joining a string to a number
Lecture 42 Practice Activity Number 5
Lecture 43 Practice Activity Number 5 - The Solution
Lecture 44 Want more string functions?
Section 6: Session 1 - Date data types and functions
Lecture 45 Date data types
Lecture 46 Non-English locales, and Books Online
Lecture 47 Setting dates and Date extraction
Lecture 48 Today's date, and more date functions
Lecture 49 Date offset
Lecture 50 Converting from date to strings
Lecture 51 Want more date functions?
Section 7: Session 1 - Conclusion
Lecture 52 Well done!
Section 8: Welcome to Session 2
Lecture 53 Introduction
Lecture 54 Spreadsheet Data
Lecture 55 Session 2 Resources
Section 9: Session 2 - Creating and querying part of a table
Lecture 56 Creation of tblEmployee table
Lecture 57 Adding additional columns
Lecture 58 SELECTing only part of a table - strings
Lecture 59 SELECTing only part of a table - numbers
Section 10: Session 2 - Summarising and ordering data
Lecture 60 Summarising and ordering data
Lecture 61 Criteria on summarised data
Lecture 62 Changing blank strings to NULLs
Lecture 63 Exercise
Section 11: Session 2 - Adding a second table
Lecture 64 Adding a second table
Lecture 65 Designing a connection
Lecture 66 New spreadsheet data
Lecture 67 Importing data and showing tables graphically
Lecture 68 Writing a JOIN query
Lecture 69 Different types of JOIN
Lecture 70 Creating a third table
Lecture 71 JOINing three tables
Section 12: Session 2 - Find missing data, and delete and update data
Lecture 72 Missing data
Lecture 73 Deleting data
Lecture 74 Updating data
Section 13: End of Session 2
Lecture 75 Thank you
Section 14: Session 3 - Introduction
Lecture 76 Introduction
Lecture 77 Is this your first session?
Lecture 78 Resources
Lecture 79 Objectives
Lecture 80 Summary of and problems with our existing database
Section 15: Session 3 - Objective 4 - Create and modify constraints (simple statements)
Lecture 81 What are constraints?
Lecture 82 Unique constraints - what are they?
Lecture 83 Unique constraints in action
Lecture 84 Default constraints - what are they?
Lecture 85 Default constraints in action
Lecture 86 Check constraint - what are they?
Lecture 87 Check constraints - in practice
Lecture 88 Primary key
Lecture 89 Primary key - in practice
Lecture 90 Foreign key - what is it?
Lecture 91 Foreign key - in practice
Lecture 92 Summary
Section 16: Session 3 - Objectives 2 and 3: Views
Lecture 93 Creating views
Lecture 94 Altering and dropping views
Lecture 95 CREATE OR ALTER VIEW
Lecture 96 Securing views
Lecture 97 Security
Lecture 98 Adding new rows to views
Lecture 99 Deleting rows in views
Lecture 100 What is an index?
Lecture 101 Creating an indexed view
Section 17: Session 3 - Objective 5: Create and alter DML triggers
Lecture 102 What are triggers?
Lecture 103 Creating an AFTER trigger
Lecture 104 Creating an INSTEAD OF trigger
Lecture 105 Nested triggers
Lecture 106 Update functions
Lecture 107 Handling multiple rows in a session
Lecture 108 Summary
Lecture 109 Disable triggers
Section 18: Session 3 - Conclusion
Lecture 110 Well done!
Section 19: Session 4 - Introduction
Lecture 111 Introduction
Lecture 112 Is this your first session?
Lecture 113 Session 4 - Resources
Section 20: Session 4 - Objective 13: Combine datasets
Lecture 114 UNION and UNION ALL
Lecture 115 Intersect and Except
Lecture 116 CASE statement
Lecture 117 IsNull and Coalesce
Lecture 118 MERGE statement - in theory
Lecture 119 Let's Build our MERGE statement
Lecture 120 Let's expand our MERGE statement
Lecture 121 Merge with additional columns
Lecture 122 Summary
Section 21: Session 4 - Objective 11 - Create and alter stored procedures (simple statements
Lecture 123 Let's create our first procedure
Lecture 124 Ask for a specific employee
Lecture 125 Exercise with IF
Lecture 126 Multiple arguments
Lecture 127 While
Lecture 128 Return
Lecture 129 Summary
Section 22: Session 4 - Objective 18a - Implement try/catch/throw
Lecture 130 Procedure Exercise
Lecture 131 TRY/CATCH
Lecture 132 THROW and RAISERROR
Lecture 133 PRINT
Lecture 134 Summary
Section 23: End of Session 4!
Lecture 135 Well done!
Section 24: Session 5 - Introduction
Lecture 136 Introduction
Lecture 137 Is this your first session?
Lecture 138 Session 5 Resources
Section 25: Session 5 - Objective 9: Implement aggregate queries
Lecture 139 Introduction
Lecture 140 OVER()
Lecture 141 PARTITION BY and ORDER BY
Lecture 142 ROWS BETWEEN
Lecture 143 CURRENT ROW and UNBOUNDED
Lecture 144 RANGE versus ROWS
Lecture 145 Omitting RANGE/ROW?
Section 26: Session 5 - Objective 9d: Ranking functions
Lecture 146 ROW_NUMBER, RANK and DENSE_RANK
Lecture 147 NTILE
Section 27: Session 5 - Objective 9a: New analytic functions
Lecture 148 FIRST_VALUE and LAST_VALUE
Lecture 149 LAG and LEAD
Lecture 150 CUME_DIST and PERCENT_RANK
Lecture 151 PERCENTILE_CONT and PERCENTILE_DISC
Section 28: Section 5 - Objective 9b: Grouping sets
Lecture 152 Adding Totals
Lecture 153 ROLLUP, GROUPING and GROUPING_ID
Lecture 154 GROUPING SETS
Section 29: Session 5 - Objective 9c: Spatial aggregates
Lecture 155 Introduction
Lecture 156 POINT
Lecture 157 POINT queries
Lecture 158 Line, POLYGON and Circles
Lecture 159 Line queries
Lecture 160 Geography
Lecture 161 Spatial Aggregates
Lecture 162 Summary
Section 30: End of Session 5
Lecture 163 Well done!
Section 31: Session 6 - Introduction
Lecture 164 Introduction
Lecture 165 Objectives
Lecture 166 Session 6 Resources
Section 32: Session 6 - Objective 7: Sub-queries
Lecture 167 Table structure
Lecture 168 The WHERE clause
Lecture 169 WHERE and NOT
Lecture 170 ANY, SOME and ALL
Lecture 171 The FROM clause
Lecture 172 The SELECT clause
Lecture 173 Correlated subquery - WHERE
Lecture 174 Top 5 from various categories
Section 33: Session 6 - Objective 7e: WITH statement
Lecture 175 WITH statement
Lecture 176 Generating a list of numbers
Lecture 177 Grouping numbers
Section 34: Session 6 - Objective 7b: PIVOTing and UNPIVOTing
Lecture 178 PIVOT
Lecture 179 Replacing NULLs in PIVOTs.
Lecture 180 UNPIVOT
Section 35: Session 6 - Objective 7d: CTE statement
Lecture 181 Self-Join
Lecture 182 Recursive CTE
Section 36: Session 6 - Objective 14: Functions
Lecture 183 Introduction
Lecture 184 Scalar Functions
Lecture 185 A more complicated scalar function
Lecture 186 Inline Table Function
Lecture 187 Multi-statement Table Function
Lecture 188 APPLY
Section 37: Session 6 - Objectives 6b and 6c: Synonyms and Dynamics
Lecture 189 Synonyms
Lecture 190 Dynamic SQL
Section 38: Session 6 - Objectives 8c: GUIDs; and Sequences
Lecture 191 The problems about IDENTITY
Lecture 192 GUIDs
Lecture 193 Creating SEQUENCEs
Lecture 194 Using SEQUENCEs
Section 39: Session 6 - Objective 10: Query and manage XML data - converting tables to XML
Lecture 195 Introduction to XML - Let's make a shopping list
Lecture 196 Attributes
Lecture 197 Creating XML variable and XML field
Lecture 198 FOR XML RAW
Lecture 199 FOR XML AUTO
Lecture 200 FOR XML PATH
Lecture 201 FOR XML EXPLICIT
Section 40: Session 6 - Objective 10: Shredding XML data
Lecture 202 XQuery Value and Exist methods
Lecture 203 XQuery Modify method
Lecture 204 XQuery Query method using for and return
Lecture 205 XQuery Query method using let, where and order by (FLWOR)
Lecture 206 XQuery Nodes - shredding a variable
Lecture 207 XQuery Nodes - shredding a table
Section 41: Session 6 - Objective 10: Other XML considerations
Lecture 208 Importing and exporting using the bcp utility
Lecture 209 Bulk Insert and Openrowset
Lecture 210 Schema
Lecture 211 When should I use XML in SQL Server?
Lecture 212 Creating Primary and Secondary XML Indexes
Section 42: Session 6 - Manipulating JSON data (SQL Server 2016 onwards)
Lecture 213 These next two sections require SQL Server 2016 or later
Lecture 214 Creating JSON and ISJSON
Lecture 215 JSON_VALUE and JSON_QUERY
Lecture 216 JSON_MODIFY
Lecture 217 Converting JSON data into SQL Server tables
Lecture 218 Converting SQL Server data into JSON
Section 43: Session 6 - Temporal Tables
Lecture 219 What are temporal tables?
Lecture 220 Creating temporal tables
Lecture 221 Dropping temporal tables, and specifying the history table
Lecture 222 Altering existing tables to make them temporal tables
Lecture 223 Querying temporal data at a point of time
Lecture 224 Querying temporal data between a range of times
Section 44: Session 6 - Conclusion
Lecture 225 Well done!
Section 45: Session 7 - Introduction
Lecture 226 Introduction
Lecture 227 Curriculum
Lecture 228 Session 7 Resources
Section 46: Session 7 - Manage transactions
Lecture 229 Introduction - What are transactions?
Lecture 230 Implicit transactions
Lecture 231 Explicit Transactions - Start and end transactions
Lecture 232 Mark a transaction
Lecture 233 Trancount
Lecture 234 Scope and type of locks
Lecture 235 Locks in Practice, and using the WAITFOR statement
Lecture 236 What would happen if locks didn't exist?
Lecture 237 What are Isolation levels?
Lecture 238 Isolation Levels in Action
Lecture 239 Conclusion
Section 47: Session 7 - Indexes
Lecture 240 Re-introducing Query plans
Lecture 241 Heaps, and scans
Lecture 242 B-Tree
Lecture 243 Clustered indexes
Lecture 244 Non-clustered indexes
Lecture 245 Filtered Indices
Lecture 246 Include
Section 48: Session 7 - Optimise queries
Lecture 247 What are statistics - Include Client Statistics
Lecture 248 The different join types and seeing HASH join in action
Lecture 249 Nested Loops and Merge Joins in action
Lecture 250 Even bigger savings of time when using a SARG
Lecture 251 Reading Query plans and the cost of Sorting
Lecture 252 A more advanced query plan
Lecture 253 Plan guides
Lecture 254 Hints
Lecture 255 Statistics IO
Lecture 256 SET SHOWPLAN_ALL and Client Statistics
Lecture 257 Dynamic vs. parameterised queries
Section 49: Session 7 - Dynamic Management Views and Functions
Lecture 258 dm_db_index_usage_stats
Lecture 259 sys.dm_db_index_physical_stats
Lecture 260 sys.dm_db_missing_index_details
Lecture 261 Conclusion
Section 50: Session 7 - Evaluate the use of row-based operations vs. set-based operations
Lecture 262 How to write a cursor
Lecture 263 Disadvantages
Lecture 264 Alternatives
Lecture 265 Impact of scalar UDFs
Lecture 266 Combine multiple DML operations
Section 51: Congratulations
Lecture 267 You have finished this course - now what?
Lecture 268 Thank you, and goodbye
Lecture 269 Bonus Lecture SQL
This SQL course is meant for you, if you have not used SQL Server much (or at all), and want to learn T-SQL.,This course is also for you if you want a refresher on SQL. However, no prior SQL Server knowledge is required.
Homepage

https://www.udemy.com/course/70-461-session-2-querying-microsoft-sql-server-2012/

DDOWNLOAD