Introduction to T-SQL Querying
Sep 6, 2019 • 8 Minute Read
Introduction to T-SQL Querying
Structured Query Language (SQL) is a set-based and declarative language which is used to communicate to the database to instruct it what operation you want to perform against the database (select, update..). There are several databases available on the market today to ensure cohesion between the databases in which SQL standard was implemented. Also known as the SQL ANSI standard, it is a set of commands that operates across all ANSI standard databases. The two most common versions of ANSI standard are SQL-89 and SQL-92 specifications. Both are either fully or partly supported by Transact-SQL (T-SQL) which is Microsoft's implementation of the industry-standard Structured Query Language.
Categories of T-SQL Statements
There are 3 major types of T-SQL Statements
-
Data Manipulation Language (DML) is the set of T-SQL statements that are used to query and modify data. This includes SELECT, to fetch data, and modification statements such as INSERT, UPDATE, and DELETE.
-
Data Definition Language (DDL) is the set of T-SQL statements that are used to define database objects such as tables, views, and procedures. This includes statements such as CREATE, ALTER, and DROP.
-
Data Control Language (DCL) is the set of T-SQL statements used to manage security permissions for users and objects. DCL includes statements such as GRANT, REVOKE, and DENY.
T-SQL Language Elements
T-SQL, like other programming languages, has its own syntax and commands to interact with the database. For example, you can use predicates to filter rows, operators to perform comparisons, functions and expressions to manipulate data or retrieve system information, and comments to document your code.
- Predicates are expressions that evaluate to TRUE, FALSE, or UNKNOWN. Predicates are often used in the search condition of WHERE clauses and HAVING clauses. Example:
SELECT * FROM PEOPLE WHERE FIRST_NAME = 'John'
-
Functions in SQL Server are either deterministic or non-deterministic. Deterministic functions always return the same result any time they are called by using a specific set of input values. Whereas non-deterministic functions return different results every time they are called.
Some of the most common functions are:
- String Functions: Substring, Replace..
- Date Functions: Getdate, Dateadd..
- Aggregate functions: Min, Max, Sum.. Mathematical Functions: Round, Ciel..
-
Variables are used to temporarily store the value of a specific data type. To create a local variable in T-SQL, you must give a name, data type, and initial value.
DECLARE @FirstName varchar(30) = 'John%';
- Expressions are combinations of symbols and operators that the SQL Server Database evaluates to obtain a single data value. They can be used in select statements, CASE expressions or where statements.
SELECT FirstName, Age+1 from Person
-
Comments can be used to document code in T-SQL. The following methods can be used: a. For single-line comments, you can use -- b. For multi-lines comments, you can enclose your comment between /* and */
-
Batch separators combine one or more SQL Server statements that are sent to the engine as one set of statements. The GO statement is used at batch separator in SQL Server. Therefore, any variables declared in the current batch will not be visible in the next batch.
Thinking Sets
A set is defined as "a collection of distinct objects considered as a whole." Although a table is represented as rows and columns, to understand how to query data, you need to picture a table as a set.
Thinking your data as a set will help you understand the following:
-
In SQL, you interact with the whole set at once. That is, when fetching data from a set, the database engine won't go through each row sequentially, instead, it will query the whole table at once.
-
SQL is a declarative language. Therefore, when interacting with the database via queries, you will tell the database what information you want to retrieve and not where to fetch it. For example, you will write a query to retrieve all people who live in the US instead of fetching the rows at positions 10 and 11.
-
Sorting is not guaranteed in SQL which is why you would be required to specify a sorting order. We'll learn more about it in the next articles.
In SQL, to get data from your sets or tables, the select statement is used. It specifies the columns to be returned by the query.
Elements of a Select Statement
A select statement is used to fetch data in SQL. It can operate both with and without a from clause. A select statement with a from clause will fetch data from a table, whereas a select statement without a from clause will fetch data from an "in-memory" or "imaginary" table.
Below are the elements of a select statement:
-
SELECT: specifies to which column to return.
-
FROM: specifies from which table to fetch the data.
-
WHERE: specifies how to filter the data.
-
GROUP BY: arranges the data to be returned by groups.
-
HAVING: filter groups by predicates.
-
ORDER BY: sorts the results.
Let's see these in action. Let's say we have a table of employees, as follows:
Table: Employee
| id | firstname | lastname | age |
|-----|-----------|----------|-----|
| 1 | Dominic | Toretto | 45 |
| 2 | Brian | OConner | 40 |
| 3 | Roman | Pearce | 35 |
To select all records from the employee table, you will write the following
SELECT * FROM Employee
Now, to select only the employee whose id is 2, you will need to add a predicate in the where condition as follows:
SELECT * FROM Employee
WHERE id = 2
The results will then be as follows:
| id | firstname | lastname | age |
|-----|-----------|----------|-----|
| 2 | Brian | OConner | 40 |
You can also add more interesting predicates such as greater than and less than. Observe the query below:
SELECT * FROM Employee
WHERE age > 35
| id | firstname | lastname | age |
|-----|-----------|----------|-----|
| 1 | Dominic | Toretto | 45 |
| 2 | Brian | OConner | 40 |
Understanding the Logical Order of Operations in SELECT Statements
In SQL, the order in which you write a statement is not necessarily the order in which the database engine will evaluate and process it. Database engines may optimize their execution of a query, providing the accuracy of the result (as determined by the logical order) is retained. As a result, unless you learn the logical order of operations, you may find both conceptual and practical obstacles to writing your queries.
SQL Server executes queries in the following order:
-
FROM
-
WHERE
-
GROUP BY
-
HAVING
-
SELECT
-
ORDER BY
Now, consider the following query:
SELECT firstname, lastname FROM Employee
WHERE age > 35
The FROM clause is evaluated first to provide the source rows for the rest of the statement. Then, the WHERE clause will be evaluated, filtering the rows from the source table that match a predicate. The filtered data set is passed to the next step. Finally, the SELECT clause will execute, determining which columns will appear in the query results.
Conclusion
In this guide, you had an overview of the basics of SQL Server and how it processes queries. In my upcoming guide, Writing SELECT Queries, we shall discuss how in detail how to write SQL select queries.
Happy coding!
