Querying Microsoft SQL Server with Transact-SQL
278
Lessons
All
Skill Level
29:47h
course Duration
English
Audio
Eng & Esp
Subtitles
Overview
This course is the foundation for learning T-SQL. It follows the Microsoft Certificate 70-461: “Querying Microsoft SQL Server 2012” and 70-761 “Querying Data with Transact-SQL”. While these certificates are no longer available, its curriculum allows you to gain a master of T-SQL.
To start with, you will be installing for free the software that we will be using throughout this course (SQL Server and SQL Server Management Studio – SSMS).
This course is divided into 7 sessions, each of which can be completed in a morning or afternoon.
No prior knowledge is required.
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
Completing this course will help you:
- 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.
- Learn about transactions, optimise queries and row-based v set-based operations
- Manipulate XMLs and JSONs.
Who is the course 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.
Learning Path
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.
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.
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.
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
