70-461 Session 1: Querying Microsoft SQL Server 2012

Install SQL Server, learn how to create and drop tables using T-SQL, and data types and functions in detail.
4.4 (60 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.
346 students enrolled
$19
$20
5% off
Take This Course
  • Lectures 41
  • Length 4.5 hours
  • Skill Level Beginner Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 7/2015 English

Course Description

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

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.

What are the 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.

What am I going to get from this course?

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

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

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Let's begin, and let's download SQL Server
02:23

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

08:54

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

07:22

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.

07:07

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

Section 2: Starting SQL Server
03:01

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

04:45

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

01:41

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

06:08

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

Article

Now it's your turn. 

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

2 questions

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

Section 3: Creating tables - First pass
02:10

Here we will create a table using the graphical user interface

02:58

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

2 questions

Two quick questions

02:30

We will use the Graphical User Interface to add data.

04:48

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

1 question

Let's test what you've learned.

04:33

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

1 question

Let's test what you have learned.

03:08

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

2 questions

Let's test what you have learned.

Practice Activity Number 2
Article
Section 4: Number types and functions
04:27

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.

Resources
Article
11:45

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

07:47

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

5 questions

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.

Article

Now it´s your turn.

15:09

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

3 questions

Let's test what you have just learned.

12:38

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

2 questions

Quick questions.

08:41

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

1 question

Quick question:

Section 5: String data types and functions
15:43

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

2 questions

A few questions about what you have learned.

07:15

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

2 questions

A few questions about what you have learned.

09:17

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

11:49

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

2 questions

Some questions about NULL.

08:46

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

2 questions

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

Section 6: Date data types and functions
07:45

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

06:23

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

3 questions

Let's test your knowledge about date data types.

07:49

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

2 questions

Let's see what you can remember.

09:47

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

09:53

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

1 question

A quick question to end this first session...

Section 7: End of session 1
Article

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

03:38

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.

01:46

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

Section 8: Bonus - Installing SQL Server 2016 Developer Edition
07:50

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.

13:49

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

12:34

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.

Article

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

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Phillip Burton, SQLIntro.com

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.

Ready to start learning?
Take This Course