70-461 Session 1: Querying Microsoft SQL Server '12-16 T-SQL
4.6 (185 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
856 students enrolled
Wishlisted Wishlist

Please confirm that you want to add 70-461 Session 1: Querying Microsoft SQL Server '12-16 T-SQL to your Wishlist.

Add to Wishlist

70-461 Session 1: Querying Microsoft SQL Server '12-16 T-SQL

T-SQL (Transact SQL Server) beginners: Create tables, data types + functions. Work to certification, inc. 70-761 (2016)
Bestselling
4.6 (185 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
856 students enrolled
Created by Phillip Burton
Last updated 10/2016
English
Current price: $10 Original price: $50 Discount: 80% off
30-Day Money-Back Guarantee
Includes:
  • 4.5 hours on-demand video
  • 6 Articles
  • 3 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • create, alter and drop tables in a database.
  • know what data type to use in various situations.
  • use functions to manipulate date, number and string data values.
  • work towards objectives 1 and 8 from Microsoft Certification 70-461 "Querying Microsoft SQL Server 2012"
View Curriculum
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.
  • You don't even need SQL Server installed - I'll show you have to install it on your computer for free!
  • There's a 30-day money back guarantee on this Udemy course.
  • Why not have a look at the curriculum below and see what you can learn? If you already know them, please see my later sessions.
Description

Reviews:

"Best I've seen. Instead of just throwing information at me, I'm able to work along with the instructor. Thanks!!!" -- Charles Schweiger

"Lots of material covered in an easy-to-follow way. Not too slow, and not too face. Great job!" -- Anita Parks

This course is the foundation for the Microsoft Certificate 70-461: "Querying Microsoft SQL Server 2012/2014".

Rather than present one huge course, this course takes some of the basics and then goes in depth. 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.

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, and have an appreciation of how they can all be used in T-SQL.

Who is the target audience?
  • This SQL course is meant for you, if you have not used SQL Server much (or at all), and want to start 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.
  • If you already know all this, and want to learn about more advanced topics, please see my later sessions.
Curriculum For This Course
41 Lectures
04:17:55
+
Let's begin, and let's download SQL Server
4 Lectures 25:46

Hello! In this video, I'll talk in general terms who this course is for, and what you will be learning today.

Preview 02:23

What more specifically we will cover in this course, and how this fits into the 70-461 exam.

Preview 08:54

Here we will find the relevant SQL Server program, and what to download.

You will need download it to create your own statements and solidify your knowledge of T-SQL.

Preview 07:22

Step by step how to install SQL Server on your computer.

Preview 07:07
+
Starting SQL Server
5 Lectures 15:55

Finding out which program to open, and starting SSMS (SQL Server Management Studio)

Preview 03:01

Here we will see the main components of Management Studio, which is where we will do most of our work.

Looking at SQL Server Management Studio
04:45

We will create a database, which will be used to hold all of the components of our work.

Create a database
01:41

Our first queries, using mathematical calculations - SELECT and GO.

Creating our first queries
06:08

Now it's your turn. 

This exercise is to practice writing a query, and creating simple mathematical expressions.

Practice Activity Number 1 - Writing mathematical queries
00:20

This quiz is a brief test on what you have just learned.

Databases and Queries
2 questions
+
Creating tables - First pass
7 Lectures 20:36

Here we will create a table using the graphical user interface

Creating a table - first pass using GUI
02:10

Here we will create a table using T-SQL using CREATE TABLE, GO

Creating a table - first pass using T-SQL
02:58

Two quick questions

New Tables
2 questions

We will use the Graphical User Interface to add data.

Entering data using the GUI
02:30

We will enter data in a table using T-SQL. INSERT

Entering data using T-SQL
04:48

Let's test what you've learned.

Entering data using T-SQL
1 question

Using the SELECT statement, and whether to use the semicolon.

Preview 04:33

Let's test what you have learned.

Retrieving data
1 question

We will delete all the data using TRUNCATE, and then the table using DROP TABLE.

Deleting the data, then the table
03:08

Let's test what you have learned.

Deleting data using T-SQL
2 questions

Practice Activity Number 2
00:29
+
Number types and functions
8 Lectures 01:01:18

We will recreate a table using T-SQL with multiple fields (EmployeeNumber and EmployeeName), and find why we need to expand on the information we learnt in the last section before we can proceed much further.

Creating an Employee table
04:27

Resources
00:11

Using the DECLARE function in an SQL batch to create a temporary variable, setting it to a value, and then querying it.

Creating temporary variables
11:45

We will look at the four major integer types: tinyint, smallint, int and bigint [8a and 8b]. We'll also look at the +, -, *, / and ^ signs, and the danger of integer division (e.g. 3/2).

Integer numbers
07:47

Can you remember which is which? I will give you a few values, and I want you to give me the smallest integer type which will hold ALL these values.

Yes, bigint will hold all of these integers, but I want the SMALLEST type.

Integer numbers
5 questions

Now it´s your turn.

Preview 00:40

Now it's turn for non-integer numbers: float and real, money and smallmoney, decimal and numeric.

Non-integer numbers
15:09

Let's test what you have just learned.

Non-integer numbers
3 questions

SQUARE, POWER and SQRT, FLOOR, CEILING and ROUND, PI and EXP, ABS and SIGN, RAND and trigonometric functions.

Mathematical functions
12:38

Quick questions.

Mathematical functions
2 questions

How to convert between integers and non-integer numbers - CAST and CONVERT.

Converting between number types
08:41

Quick question:

Converting between number types
1 question
+
String data types and functions
5 Lectures 52:50

We will look at the four major string types: char, varchar, nchar and nvarchar.

Strings
15:43

A few questions about what you have learned.

Strings
2 questions

Looking at how to extract strings - LEFT, RIGHT, SUBSTRING, LTRIM, RTRIM, REPLACE, UPPER and LOWER.

Preview 07:15

A few questions about what you have learned.

String Functions - extraction
2 questions

What is NULL? Is it good, bad, or just plain necessary? Also, we'll look at TRY_CAST and TRY_CONVERT.

NULL - an introduction
09:17

Why a + sign doesn't always work, and how CONCAT is better. Also, converting from number data to strings.

Joining two strings or a string and number together
11:49

Some questions about NULL.

NULL
2 questions

We've seen how to join two strings together. Now, we need to convert a number to a string to join them together.

Joining a string to a number
08:46

Let's test your knowledge. You may have to think more carefully about the answers to the questions.

Joining numbers to strings
2 questions
+
Date data types and functions
5 Lectures 41:37

The various non-offset date data types: datetime, datetime2, smalldatetime, time and date.

Date data types
07:45

Setting dates using quotation marks and DATEFROMPARTS, DATETIMEFROMPARTS and TIMEFROMPARTS, and YEAR, MONTH, DAY

Setting dates and Date extraction
06:23

Let's test your knowledge about date data types.

Date data types
3 questions

CURRENT_TIMESTAMP, GETDATE and SYSDATETIME, Plus DATEADD, DATENAME, DATEPART and DATEDIFF.

Preview 07:49

Let's see what you can remember.

Today's date and more date functions
2 questions

International dates: datetimeoffset, SYSDATETIMEOFFSET, SYSUTCDATETIME, TODATETIMEOFFSET, DATETIMEOFFSETFROMPARTS, SWITCHOFFSET.

Date offset
09:47

CAST, CONVERT, PARSE, FORMAT, TRY_CAST, TRY_CONVERT, TRY_PARSE

Converting from dates to strings
09:53

A quick question to end this first session...

Converting from date to strings
1 question
+
End of session 1
3 Lectures 05:34

Here is a PDF of the code that we have developed.

BONUS - Review what you have learnt (PDF)
00:10

We'll look back at this session, look forward to the next session, and give you a voucher code so that you can save money on your next sessions.

BONUS - Recap, Look ahead and save money on your next course
03:38

We recap what we learned in this session, and look forward to the next one.

Preview 01:46
+
Bonus - Installing SQL Server 2016 Developer Edition
4 Lectures 34:18

It used to be that we had to install a cut-down version of SQL Server. Instead, let's now install a version with the full functionality of the Enterprise edition - for personal use only, though.

Preview 07:50

Now let's go through the process of installing SQL Server. I'll also go through the various editions of SQL Server (e;g. 2008, 2012).

Preview 13:49

Now the back engine has been installed, we've got to install SSMS (SQL Server Management Studio). We can also install Visual Studio and SSDL (SQL Server Data Tools), but this is optional for this course. It takes around 30 minutes, but here's the edited version.

Preview 12:34

You've now installed SQL Server 2016 Developer Edition - what's next?

The next step
00:05
About the Instructor
Phillip Burton
4.4 Average rating
3,257 Reviews
45,787 Students
25 Courses
Best Selling Instructor - over 45,000 students so far

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 me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.

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.