.
278
LESSONS
ALL
SKILL LEVEL
29:47 hr
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” and 70-761 “Querying data with Transact-SQL”. Although these exams are no longer available, this curriculum allows you to master T-SQL.
You will be able to install for free the software used on this course – SQL Server and SQL Server Management Studio (SSMS).
There are regular quizzes to support your learning.
Once finished, you will be able to: manipulate numbers, strings and dates; create databases and tables; insert data and create analyses; and appreciate how they can all be used in T-SQL.
.
WHO IS THE COURSE FOR?
This course is for anybody new to SQL Server, or with limited experience, who wants to learn T-SQL. No prior knowledge is required.
It is also suitable as an SQL refresher course.
.
LEARNING PATH
TABLES, DATA TYPES, AND FUNCTIONS
- Install SQL Server.
- Create and drop tables.
- Number, string and date data types and functions.
THE ‘SELECT’ STATEMENT
- INSERT data.
- Write queries to receive and summarise data using: SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY.
- JOIN tables together.
- UPDATE and DELETE from tables.
CONSTRAINTS, VIEWS, AND TRIGGERS
- Create views to store SELECT queries.
- Create triggers to automatically run code when INSERT, DELETE, or UPDATE is used.
- Use UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY constraints.
COMBINING DATASETS, PROCEDURES, AND ERROR HANDLING
AGGREGATE QUERIES, RANKING AND ANALYTIC FUNCTIONS, GROUPING SETS, AND SPACIAL AGGREGATES
SUB-QUERIES, FUNCTIONS, STATEMENTS AND MORE
- Create procedures.
- EXECUTE parameterised commands.
- Error handling with TRY, CATCH and THROW.
- Combine datasets together, looking at UNION and UNION ALL, INTERSECT and EXCEPT, CASE, ISNULL and COALESCE, and MERGE.
- Create aggregate queries.
- Ranking functions ROW_NUMBER, RANK, DENSE_RANK and NTILE.
- 8 analytic functions new to SQL Server, including LAG, LEAD, FIRST_VALUE and LAST_VALUE.
- Alternative ways of grouping and adding totals.
- Geometry and geography data types.
- Create sub-queries.
- Create correlated sub-queries.
- Common Table Expressions using the WITH statement.
- Solve a common business problem.
- Functions, including scalar functions, inline table functions, and multi-statement table functions.
- Synonyms and dynamics SQL.
- Using GUIDs.
- XML, JSON and Temporal Tables.
TRANSACTIONS, INDEXES, OPTIMISE QUERIES AND MORE
- Start and end transactions.
- How transactions can block other users.
- Indexes and their role in optimising queries.
- Use Dynamic Management Views to improve use of indexes.
- Row-based v set-based operations.
- The impact of using scalar User Defined Functions.
REVIEWS
“Excellent course, valuable lessons, very well taught at a great pace.” Shane
“Must get tutorial. Love it.” Hayford
“Perfect step by step guide to learning. Best I’ve seen.” Charles
“The instructor explain the things in great details. Very easy to follow.” Linda
.
