From Tables and SELECT queries to superior SQL. SQL Server 2012, 2014, 2016, 2017, 2019, exams 70-461 and 70-761
What you’ll study
create tables in a database and ALTER columns within the desk.
Know what knowledge sort to make use of in varied conditions, and use features to govern date, quantity and string knowledge values.
retrieve knowledge utilizing SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.
JOIN two or extra tables collectively, discovering lacking knowledge.
INSERT new knowledge, UPDATE and DELETE present knowledge, and export knowledge INTO a brand new desk.
Create constraints, views and triggers
Use UNION, CASE, MERGE, procedures and error checking
Apply rating and analytic features, grouping, geography and geometry database
Create subqueries and CTEs, PIVOTs, UDFs, APPLYs, synonyms.
Manipulate XMLs and JSONs.
Study transactions, optimise queries and row-based v set-based operations
It’s worthwhile to know tips on how to use a pc, and hopefully know tips on how to use a spreadsheet.
No prior information of SQL Server required.
SSMS can’t be put in on the Mac OS. Should you want to set up it on a Mac, you will have both to twin boot into Home windows or be working Parallel Desktop.
You don’t even want SQL Server put in – I’ll present you need to set up it in your pc free of charge!
There’s a 30-day a refund assure of this Udemy course.
Why not take a look on the curriculum beneath and see what you possibly can study?
Beforehand obtainable as seven separate programs, now introduced in a single huge course.
“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
“Should get tutorial. Adore 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 muse for the Microsoft Certificates 70-461: “Querying Microsoft SQL Server 2012” and 70-761 “Querying Data with Transact-SQL”.
The fundamentals introduced are: tips on how to set up SQL Server, and tips on how to create and drop tables.
We then attempt to create a extra superior desk, however discover that we have to know extra about knowledge sorts – so we go into some element about knowledge sorts and knowledge features, the muse of T-SQL.
We’ll create tables which use these, after which INSERT some knowledge into them. Then we’ll write queries which is able to retrieve and abstract this knowledge, utilizing SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.
We’ll then JOIN these tables collectively to seek out the place we’re lacking knowledge and the place we’ve got inconsistent knowledge. We’ll then UPDATE and DELETE knowledge from the tables. This can permit as much as totally full goal #1 from the 70-461 examination.
We’ll now use that knowledge to create views, which allow us to retailer these SELECT queries for future use, and triggers, which permit for code to be routinely run when INSERTing, DELETEing or UPDATEing knowledge.
We’ll take a look at the database that we developed in session 2, and see what’s mistaken with it. We’ll add some constraints, akin to UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY constraints, to cease inaccurate knowledge from being added some knowledge. By doing this, we are going to full aims 2, 3, 4 and 5 from the 70-461 examination
We are going to additional encapsulate our routines by creating procedures, permitting us to EXECUTE parameterised instructions with only one assertion, and we’ll add some error dealing with with TRY, CATCH and THROW.
We’ll additionally mix datasets collectively, by UNION and UNION ALL, INTERSECT and EXCEPT, CASE, ISNULL and Coalesce, and the mighty MERGE assertion. By doing this, we are going to full aims 11, 12, 13 and components of 6 and 18 from the 70-461 examination.
We’ll will now be creating combination queries, working by means of goal 9 of the examination 70-461. We’ll be reviewing the rating features ROW_NUMBER, RANK, DENSE_RANK and NTILE. We’ll take a look at the 8 analytic features information to SQL Server 2012, akin to LAG, LEAD, FIRST_VALUE and LAST_VALUE.
We’ll take a look at other ways of grouping and including totals, utilizing ROLLUP, CUBE, GROUPING SETS and GROUPING_ID. If you wish to take the 70-461 examination, we’ll additionally take a look at the geometry and geography knowledge sorts, plotting areas on a grid, collectively with features and aggregates.
We’ll will now be creating sub-queries, working by means of aims 7b-e of the examination 70-461. We’ll be created correlated subqueries, the place the outcomes of the subquery depend upon the principle question. We’ll be Frequent Desk Expressions utilizing the WITH assertion, and we’ll be utilizing what we’ve got realized to resolve a standard enterprise drawback.
We’ll be features (goal 14), together with the three various kinds of Person Outlined Capabilities (UDF): scalar features, inline desk features, and multi-statement desk features. We’ll then full goal 6 by synonyms and dynamic SQL, and goal 8 by the usage of GUIDs. We’ll additionally take a look at sequences.
We’ll take a look at XML. Lastly, for SQL Server 2016 and later (examination 70-761), we’ll study JSON and Temporal Tables.
On this session we’ll be transactions, seeing tips on how to explicitly begin and finish them, and discovering out how they’ll block different customers within the database. Then we’ll see about tips on how to indexes and their position in optimising queries.
We’ll additionally see how we will use Dynamic Administration Views to see how we will enhance our use of indexes. We’ll then take a look at tips on how to write a cursor, and when to make use of this row-based operation, and the influence of utilizing scalar UDFs.
No prior information is required – I’ll even present you tips on how to set up SQL Server in your pc free of charge!
There are common quizzes that will help you bear in mind the knowledge.
As soon as completed, you’ll know what tips on how to manipulate numbers, strings and dates, and create database and tables, create tables, insert knowledge and create analyses, and have an appreciation of how they’ll all be utilized in T-SQL.
Who this course is for:
- This SQL course is supposed for you, you probably have not used SQL Server a lot (or in any respect), and wish to study T-SQL.
- This course can be for you if you’d like a refresher on SQL. Nonetheless, no prior SQL Server information is required.
Created by Phillip Burton
Final up to date 11/2020
English, French [Auto], 2 extra
Measurement: 15.58 GB
The put up 70-461, 761: Querying Microsoft SQL Server with Transact-SQL .
DISCLAIMER: No Copyright Infringement Supposed, All Rights Reserved to the Precise Proprietor. This content material has been shared below Instructional Functions Solely. For Copyright Content material Removing Please Contact the Administrator or E mail at Getintocourse@gmail.com