SQL – using the advanced options for functions, procedures and variables

Training process

Training needs analysis

If you have specific requirements regarding the training programme, we will carry out a training needs analysis for you. This will guide us on which aspects of the programme should receive greater emphasis, so that the training programme meets your specific needs.

What will you gain?

Precise querying - You will learn to write advanced SELECT queries with WHERE, GROUP BY, HAVING, and ORDER BY, so you can pull exactly the data you need from a database on your own.

Secure dynamic SQL - You will work with dynamic SQL and practical ways to reduce SQL Injection risk, so you can build flexible queries and procedures without exposing applications to common security flaws.

Efficient subqueries - You will practice nested and correlated subqueries as well as EXISTS, which will help you create cleaner and more accurate solutions when simple JOINs are not enough.

Functions and procedures - You will understand when to use a stored procedure and when a scalar or table-valued function is the better choice, making your SQL logic easier to organize and reuse.

Better data type choices - You will understand the differences between numeric, text, date, and XML types, helping you design tables more confidently and avoid conversion, performance, and data quality issues.

SQL flow control - You will master IF statements and WHILE or FOR loops, allowing you to build more advanced administrative and data-processing scripts without moving all logic into the application layer.

Transaction control - You will learn to use transactions in line with ACID principles and handle errors properly, so you can run multi-step operations safely without leaving data in an inconsistent state.

Automation with triggers - You will learn how DML and DDL triggers work, so you can automate reactions to data and schema changes, such as validation, auditing, or blocking unwanted operations.

Training programme

1. Detailed discussion of SQL queries

selection queries (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY),
data modification queries (INTO, INSERT INTO, UPDATE, DELETE),
table transformations,
preparing views (CREATE VIEW),
dynamic SQL and counteracting SQL Injection,
cursor attributes.

2. Analysis of the application of subqueries in SQL

nested subqueries,
correlated subqueries,
EXISTS operator.

3. Understanding the concept of variables in SQL

creating and modifying scalar functions,
table-valued (DECLARE).

4. Detailed overview of data types in relational databases

numeric (tinyint, smallint, int, bigint),
text (char, varchar, nchar, ntext, nvarchar),
date and time (datetime, smalldatetime),
XML (Extensible Markup Language),
other (e.g. hierarchyid, cursor).

5. Discussion of control statements

conditional statement IF,
loops (LOOP, WHILE, FOR).

6. Characteristics of tables in relational databases

temporary,
global.

7. Analysis of differences between stored procedures and functions in SQL

8. Discussion of stored procedures

application,
general structure of a stored procedure in SQL,
creating a procedure.

9. Presentation of functions in SQL

scalar:
- date and time,
- logical,
- text,
- conversion,
table:
- simple (inline table UDF),
- complex (multi-statement table valued functions),
grouping and ordering data,
user-defined.

10. Explanation of the role of transactions in SQL

theoretical basis (ACID principle),
implementation examples,
error handling.

11. Discussion of triggers as automation tools in SQL

DML (INSERT, UPDATE, DELETE),
DDL (CREATE, ALTER, DROP).

What are the prerequisites for participating in the training?

SQL basics - You should be comfortable with simple SELECT queries, data filtering, and basic JOINs, so during the course you can focus on advanced topics instead of core syntax.

Working with tables - You should understand tables, rows, columns, and keys, and be able to perform simple INSERT, UPDATE, and DELETE operations in a relational database.

Relational database concepts - You should know basic relational database concepts such as relationships, join conditions, and common data type usage, so you can follow the course examples with ease.

Hands-on experience - It is recommended that you have already worked in a database environment and run SQL queries, because this course builds practical skills rather than starting from zero.