Udemy
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
Development
Web Development Data Science Mobile Development Programming Languages Game Development Database Design & Development Software Testing Software Engineering Development Tools No-Code Development
Business
Entrepreneurship Communications Management Sales Business Strategy Operations Project Management Business Law Business Analytics & Intelligence Human Resources Industry E-Commerce Media Real Estate Other Business
Finance & Accounting
Accounting & Bookkeeping Compliance Cryptocurrency & Blockchain Economics Finance Finance Cert & Exam Prep Financial Modeling & Analysis Investing & Trading Money Management Tools Taxes Other Finance & Accounting
IT & Software
IT Certification Network & Security Hardware Operating Systems Other IT & Software
Office Productivity
Microsoft Apple Google SAP Oracle Other Office Productivity
Personal Development
Personal Transformation Personal Productivity Leadership Career Development Parenting & Relationships Happiness Esoteric Practices Religion & Spirituality Personal Brand Building Creativity Influence Self Esteem & Confidence Stress Management Memory & Study Skills Motivation Other Personal Development
Design
Web Design Graphic Design & Illustration Design Tools User Experience Design Game Design Design Thinking 3D & Animation Fashion Design Architectural Design Interior Design Other Design
Marketing
Digital Marketing Search Engine Optimization Social Media Marketing Branding Marketing Fundamentals Marketing Analytics & Automation Public Relations Advertising Video & Mobile Marketing Content Marketing Growth Hacking Affiliate Marketing Product Marketing Other Marketing
Lifestyle
Arts & Crafts Beauty & Makeup Esoteric Practices Food & Beverage Gaming Home Improvement Pet Care & Training Travel Other Lifestyle
Photography & Video
Digital Photography Photography Portrait Photography Photography Tools Commercial Photography Video Design Other Photography & Video
Health & Fitness
Fitness General Health Sports Nutrition Yoga Mental Health Dieting Self Defense Safety & First Aid Dance Meditation Other Health & Fitness
Music
Instruments Music Production Music Fundamentals Vocal Music Techniques Music Software Other Music
Teaching & Academics
Engineering Humanities Math Science Online Education Social Science Language Teacher Training Test Prep Other Teaching & Academics
AWS Certification Microsoft Certification AWS Certified Solutions Architect - Associate AWS Certified Cloud Practitioner CompTIA A+ Cisco CCNA Amazon AWS CompTIA Security+ Microsoft AZ-900
Graphic Design Photoshop Adobe Illustrator Drawing Digital Painting InDesign Character Design Canva Figure Drawing
Life Coach Training Neuro-Linguistic Programming Mindfulness Personal Development Meditation Personal Transformation Life Purpose Emotional Intelligence CBT
Web Development JavaScript React CSS Angular PHP WordPress Node.Js Python
Digital Marketing Google Ads (Adwords) Social Media Marketing Google Ads (AdWords) Certification Marketing Strategy Internet Marketing YouTube Marketing Email Marketing Retargeting
SQL Microsoft Power BI Tableau Business Analysis Business Intelligence MySQL Data Analysis Data Modeling Big Data
Business Fundamentals Entrepreneurship Fundamentals Business Strategy Online Business Business Plan Startup Freelancing Blogging Home Business
Unity Game Development Fundamentals Unreal Engine C# 3D Game Development C++ 2D Game Development Unreal Engine Blueprints Blender
30-Day Money-Back Guarantee
IT & Software IT Certification T-SQL

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

From Tables and SELECT queries to advanced SQL. SQL Server 2012, 2014, 2016, 2017, 2019, exams 70-461 and 70-761
Bestseller
Rating: 4.6 out of 54.6 (6,113 ratings)
31,273 students
Created by Phillip Burton
Last updated 1/2021
English
English, French [Auto], 
30-Day Money-Back Guarantee

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
Curated for the Udemy for Business collection

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 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 1

The 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 2

We'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 3

We'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 exam

Session 4

We 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 5

We'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 6

We'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 7

In 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.

Who this course is for:

  • 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.

Featured review

Leslie Waldron
Leslie Waldron
9 courses
3 reviews
Rating: 5.0 out of 55 months ago
Phillip Burton takes you through everything you need to know, in good detail, giving lots of examples, and encouraging you to work out all the queries on your own. I highly recommend this course if you really want to learn SQL Server.

Course content

53 sections • 278 lectures • 29h 49m total length

  • Preview02:17
  • Welcome to Udemy
    00:35
  • Preview02:00
  • Exam update
    01:49
  • Preview09:31
  • Do you have Windows 7 or Windows Vista?
    00:55
  • Preview06:09
  • Preview09:45
  • Installing SSMS
    03:36

  • Opening SQL Server
    03:01
  • Preview04:45
  • Preview01:41
  • Creating our first queries
    06:08
  • Practice Activity Number 1 - Writing mathematical queries
    00:19
  • Coding Exercises
    00:11
  • Writing mathematical queries
    1 question
  • Databases and Queries
    2 questions

  • Creating a table - first pass using GUI
    02:10
  • Creating a table - first pass using T-SQL
    02:58
  • New Tables
    2 questions
  • Entering data using the GUI
    02:30
  • Entering data using T-SQL
    04:48
  • Entering data using T-SQL
    1 question
  • Retrieving data
    04:33
  • Retrieving data
    1 question
  • The FROM clause - connecting to one table
    1 question
  • How do you like these coding exercises?
    00:25
  • Deleting the data, then the table
    03:08
  • Deleting data using T-SQL
    2 questions
  • Practice Activity Number 2
    00:28

  • Creating an Employee table
    04:27
  • Session 1 Resources
    00:17
  • Creating temporary variables
    11:45
  • Integer numbers
    07:47
  • Integer numbers
    5 questions
  • Practice Activity Number 3
    00:40
  • Practice Activity Number 3 - Solution
    03:44
  • Non-integer numbers
    15:09
  • Non-integer numbers
    3 questions
  • Mathematical functions
    12:38
  • Mathematical functions
    2 questions
  • Converting between number types
    08:41
  • Converting between number types
    1 question
  • Practice Activity Number 4
    00:33
  • Practice Activity Number 4 - Solution
    05:35

  • Strings
    15:43
  • Strings
    2 questions
  • String Functions - extraction
    07:15
  • TRIM
    00:12
  • String Functions - extraction
    2 questions
  • NULL - an introduction
    09:17
  • Joining two strings together
    11:49
  • NULL
    2 questions
  • Joining a string to a number
    08:46
  • Joining numbers to strings
    2 questions
  • Practice Activity Number 5
    00:21
  • Practice Activity Number 5 - The Solution
    00:07

  • Date data types
    07:45
  • Using a computer language other than English?
    00:33
  • Setting dates and Date extraction
    06:23
  • Date data types
    3 questions
  • Today's date, and more date functions
    07:49
  • Today's date and more date functions
    2 questions
  • Date offset
    09:47
  • Converting from date to strings
    09:53
  • Converting from date to strings
    1 question

  • BONUS - Review what you have learnt (PDF)
    00:10
  • Well done!
    01:46

  • Preview01:59
  • Spreadsheet Data
    00:16
  • Session 2 Resources
    00:29

  • Creation of tblEmployee table
    11:39
  • Adding additional columns
    10:56
  • SELECTing only part of a table - strings
    12:25
  • SELECTing only part of a table - strings
    2 questions
  • Preview09:40
  • SELECTing only part of a table - numbers
    3 questions

  • Summarising and ordering data
    12:53
  • Summarising and ordering data
    2 questions
  • Criteria on summarised data
    14:40
  • Criteria on summarised data
    4 questions
  • Changing blank strings to NULLs
    00:07
  • Exercise
    15:41

Instructor

Phillip Burton
Best Selling Instructor - over 250,000 students so far
Phillip Burton
  • 4.5 Instructor Rating
  • 41,656 Reviews
  • 293,134 Students
  • 45 Courses

Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.

He enjoys investigating data, which allows him to maintain up to date and pro-active systems to help control and monitor day-to-day activities. He has also developed expertise and programmes to catalogue and process and control electronic data, large quantities of paper or electronic data for structured analysis and investigation.

He is one of 9 award winning Experts for Experts Exchange's 11th Annual Expert Awards and was one of Expert Exchange's top 10 experts for the first quarter of year 2015.

His interests are working with data, including Microsoft Excel, Access and SQL Server.

  • Udemy for Business
  • Teach on Udemy
  • Get the app
  • About us
  • Contact us
  • Careers
  • Blog
  • Help and Support
  • Affiliate
  • Terms
  • Privacy policy
  • Cookie settings
  • Sitemap
  • Featured courses
Udemy
© 2021 Udemy, Inc.