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
Read more
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
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".
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.
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
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 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
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
We'll also combine datasets together, by looking at UNION and
Session 5
We'll will now be creating aggregate queries. We'll be reviewing the ranking functions ROW_ We'll look at the 8 analytic functions news to SQL Server 2012, such as
We'll look at alternative ways of grouping and adding totals, using 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 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 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.
14 deals to help you save
BFCMSALE24
BFCMSALE24
UDEAFNULP2024
What's inside
Learning objectives
- 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
- Show more
- Show less
- 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
- Show more
- Show less
Syllabus
You will know what the overall curriculum is, and you will have downloaded SQL Server
Hello! In this video, I'll talk in general terms who this course is for, and what you will be learning today.
Read more
Syllabus
Hello! In this video, I'll talk in general terms who this course is for, and what you will be learning today.
What more specifically we will cover in this course, and how this fits into the 70-461 exam.
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.
Step by step how to install the back engine of SQL Server on your computer.
Step by step how to install SQL Server Management Studio (SSMS) onto your computer.
Here we will see the main components of Management Studio, which is where we will do most of our work.
We will create a database, which will be used to hold all of the components of our work.
Our first queries, using mathematical calculations - SELECT and GO.
Now it's your turn.
This exercise is to practice writing a query, and creating simple mathematical expressions.
This quiz is a brief test on what you have just learned.
Here we will create a table using the graphical user interface
Here we will create a table using T-SQL using CREATE TABLE, GO
Two quick questions
We will use the Graphical User Interface to add data.
We will enter data in a table using T-SQL. INSERT
Let's test what you've learned.
Using the SELECT statement, and whether to use the semicolon.
Let's test what you have learned.
We will delete all the data using TRUNCATE, and then the table using DROP TABLE.
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.
Using the DECLARE function in an SQL batch to create a temporary variable, setting it to a value, and then querying it.
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).
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.
Now it's turn for non-integer numbers: float and real, money and smallmoney, decimal and numeric.
Let's test what you have just learned.
SQUARE, POWER and SQRT, FLOOR, CEILING and ROUND, PI and EXP, ABS and SIGN, RAND and trigonometric functions.
Quick questions.
How to convert between integers and non-integer numbers - CAST and CONVERT.
Quick question:
We will look at the four major string types: char, varchar, nchar and nvarchar.
A few questions about what you have learned.
Looking at how to extract strings - LEFT, RIGHT, SUBSTRING, LTRIM, RTRIM, REPLACE, UPPER and LOWER.
What is NULL? Is it good, bad, or just plain necessary? Also, we'll look at TRY_CAST and TRY_CONVERT.
Why a + sign doesn't always work, and how CONCAT is better. Also, converting from number data to strings.
Some questions about NULL.
We've seen how to join two strings together. Now, we need to convert a number to a string to join them together.
Let's test your knowledge. You may have to think more carefully about the answers to the questions.
The various non-offset date data types: datetime, datetime2, smalldatetime, time and date.
Setting dates using quotation marks and DATEFROMPARTS, DATETIMEFROMPARTS and TIMEFROMPARTS, and YEAR, MONTH, DAY
Let's test your knowledge about date data types.
CURRENT_TIMESTAMP, GETDATE and SYSDATETIME, Plus DATEADD, DATENAME, DATEPART and DATEDIFF.
Let's see what you can remember.
International dates: datetimeoffset, SYSDATETIMEOFFSET, SYSUTCDATETIME, TODATETIMEOFFSET, DATETIMEOFFSETFROMPARTS, SWITCHOFFSET.
A quick question to end this first session...
I'll talk about what we learnt in Session 1, and what we will learn in this Session 2.
We will recreate the Employee table we tried to create earlier, and will consider what the appropriate data types should be used.
We will create a new column in existing tables , and then alter it.
We will use the WHERE clause to retrieve only part of a table, and the LIKE clause to use pattern matching.
A few questions, if I may.
Still using the WHERE clause, we will use =, <, >, <=, >=, !, NOT, BETWEEN and IN.
Let's see what you remember about using the WHERE with numbers
We investigate the WHERE clause for dates, and use a date criteria to summarise data, using the GROUP BY clause, and then order it using the ORDER BY clause.
That's a lot to remember. Let's test yourself, and maybe learn a bit more!
The GROUP BY reduced the number of rows to look at. What if we want to run criteria on this summary? We look at the HAVING clause.
It's important to get the clauses in the right order - let's see if you can remember them.
Good to know
Save this course
Save
Reviews summary
Recommended sql server course
Good examples are included
Instructor explains material clearly
Course is well-organized for learning
Activities
Review SQL syntax
Show steps
Refreshing your knowledge of SQL syntax will help you to write more efficient and accurate queries.
Browse courses on
SQL Syntax
Show steps
-
Review the SQL syntax documentation.
-
Take a practice quiz on SQL syntax.
Create SQL queries to retrieve data
Show steps
Creating SQL queries will help you practice the syntax and concepts covered in the course, and it will help you to solidify your understanding of how to retrieve data from a database.
Browse courses on
SQL
Show steps
-
Choose a table to query.
-
Write a SQL query to retrieve the data you want.
-
Run your query and review the results.
Participate in a peer discussion on SQL
Show steps
Participating in a peer discussion on SQL will allow you to share your knowledge with others, ask questions, and learn from the experiences of others.
Browse courses on
SQL
Show steps
-
Find a peer discussion on SQL.
-
Participate in the discussion.
Two other activities
Expand to see all activities and additional details
Show all five activities
Create a data visualization
Show steps
Creating a data visualization will help you to present your data in a clear and concise way, which will make it easier for others to understand your findings.
Browse courses on
Data Visualization
Show steps
-
Choose a data set to visualize.
-
Choose a type of visualization to create.
-
Create the visualization.
Create a blog post about SQL
Show steps
Creating a blog post about SQL will help you to organize your thoughts on the topic and share your knowledge with others.
Browse courses on
SQL
Show steps
-
Choose a topic for your blog post.
-
Write your blog post.
Review SQL syntax
Show steps
Refreshing your knowledge of SQL syntax will help you to write more efficient and accurate queries.
Browse courses on
SQL Syntax
Show steps
- Review the SQL syntax documentation.
- Take a practice quiz on SQL syntax.
Create SQL queries to retrieve data
Show steps
Creating SQL queries will help you practice the syntax and concepts covered in the course, and it will help you to solidify your understanding of how to retrieve data from a database.
Browse courses on
SQL
Show steps
- Choose a table to query.
- Write a SQL query to retrieve the data you want.
- Run your query and review the results.
Participate in a peer discussion on SQL
Show steps
Participating in a peer discussion on SQL will allow you to share your knowledge with others, ask questions, and learn from the experiences of others.
Browse courses on
SQL
Show steps
- Find a peer discussion on SQL.
- Participate in the discussion.
Create a data visualization
Show steps
Creating a data visualization will help you to present your data in a clear and concise way, which will make it easier for others to understand your findings.
Browse courses on
Data Visualization
Show steps
- Choose a data set to visualize.
- Choose a type of visualization to create.
- Create the visualization.
Create a blog post about SQL
Show steps
Creating a blog post about SQL will help you to organize your thoughts on the topic and share your knowledge with others.
Browse courses on
SQL
Show steps
- Choose a topic for your blog post.
- Write your blog post.
Career center
Database Administrator
Data Analyst
Business Analyst
Data Engineer
Data Scientist
Quantitative Analyst
Software Engineer
Statistician
Computer Systems Analyst
Operations Research Analyst
Web Developer
Information Security Analyst
Financial Analyst
Actuary
Market Researcher
Reading list
Save
Share
Similar courses
OpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.
Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.
Find this site helpful? Tell a friend about us.
We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.
Your purchases help us maintain our catalog and keep our servers humming without ads.
Thank you for supporting OpenCourser.
