Module 2: Select Statement Fundamentals

Previous Tutorial - Next Tutorial

In the T-SQL code, it's very important to first understand the roots or the fundamentals of the language. The language used to perform the SQL server is called Transact SQL or T-SQL. It is the ANSI standard language used to manage and manipulate data in SQL Server. It is very easy to understand and write because it's like plain English. We will explore it in detail within a few minutes.

The SQL language is subdivided into these several language elements. So the first one is Queries. This is an important element of SQL, which retrieves the data based on specific criteria. It is like finding stuff from something. SQL queries are used to reach into the database and pull out useful information. So this whole part is called query or some people also call it a statement.

Select Statement Fundamentals

The next one is the clauses. This part is optional, but a very important component when specified in queries. This portion is called clauses. It's used to manage data such as filtering, sorting, etc. The next one is the predicates. In an SQL database, each table consists of rows and columns. This defines a logical condition that is applied to rows in a table that can be evaluated to three-valued level logic, which is true, false, and unknown. These are used to limit the result of the queries. This portion is called a predicate.

The next one is expression and expression is a combination of one or more values, operators, and SQL functions that evaluate the common values. The SQL expression is like a formula, which is written in query languages. Now let's start with this SELECT statement.

This is the general structure or syntax of the select statement, which consists of some optional clauses or components. It's very important to understand the structure of the select statement and how to use its various clauses to perform data manipulation tasks, for example, filtering or grouping or sorting to satisfy some business requirements. So I have divided into some parts to understand it clearly.

Part one is SELECT, which is followed by a list of columns. Here you can also specify an asterisk sign which indicates that you want to return all column information from the table. So whenever you specify star in the query instead of individual column names, then the SQL server will return all the column data into the result. Part two is the FROM table. So here you need to specify a table name.

The FROM clause can also include an optional join sub-clause, which is used to fetch records from the multiple tables. We will see how to fetch the multiple records using a joint operator, but later on in the course. Now part three is the WHERE clause which is followed by some filtering criteria. It returns only those rows for which the predicate evaluated to true.

Select Fundamentals

We will explore this part in detail later on in this course. Part four is the GROUP BY clause, which is followed by a list of columns for grouping the result. GROUP BY clause is often used in conjunction with SQL aggregate functions such as Count, Max, Min Some, etc. Here Group Elements is the column name on which you want to group the result. We will understand it practically in detail, but later on this course.

Part five is the HAVING predicate. So the HAVING clause includes a predicate which is used to filter rows resulting from the GROUP BY clause because it acts on the result of the GROUP BY clause, aggregation function can also be used in the HAVING clause predicate. Part six is the ORDER BY clause.

It identifies which columns to use to sort the resulting data and in which direction you want to sort them, which is ascending or descending without an ORDER by clause, the order by rows returned by an SQL query is undefined. We will explore this part in detail later on in this course. The information in the database is maintained in the form of tables, and each table consists of rows and columns to store the data.

The SELECT statement and FROM clause are the required component and the other components are optional. The order of the clauses in the SELECT statement is significant. Any of the optional clauses can be omitted, but when the optional clauses are used, they must appear in the appropriate order. As I said earlier, in SQL you just need to give your order or instruction in an English like manner. So, for example, consider this image.

Here one customer is in one shop and asking the sales executive: Hey, can you please show me an android based smartphone of the HTC? Now, observe that English instruction. How would you define that instruction given to the sales executive? So it's like you are telling or giving an order to the sales executive to show an Android-based smartphone of HTC from the available collection.

Select Statement Fundamentals Tutorial

Now, consider the scene in T-SQL. Look at this query, I see something similar. So here that sales executive is the database engine in our case to whom we are giving an order or instruction to show the required data from the product table. So in this query, first indicate the SELECT list with the column name of which you want to return, and then the FROM clause with the table you want to query, and then the WHERE clause to filter out the result.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials: