Knowing how to write SQL SERVER Query is essential for a developer, even more, to understand the logic. In this guide the method to do it.
di Antonio Lamorgese
The world of databases, today, is more and more in continuous expansion and their use is more and more intensive. Knowing how to Query SQL SERVER is essential for a developer, even more, to understand the logic and operation.
In this guide we will see how to write SQL Queries with Microsoft SQL Server and a specific SQL Server language, called T-SQL, which is the acronym for: “Transact SQL”.
Like SQL Server, all other database management systems also have an autonomous management language which, however, can differ from system to system. Anyway, in this guide I will explain the method to write SQL queries.
Table of contents...
1. Install Microsoft SQL SERVER EXPRESS 2019
As a first step we need to install SQL Server Express 2019. SQL Server Express is a Microsoft tool that can be freely downloaded and used by anyone. However, this software has some limitations in the amount of data that can be managed, which as regards SQL SERVER, is more than enough to develop free small and medium-sized management software.
Just for the record, SQL SERVER EXPRESS can be installed not only on Windows operating systems but also on Linux distributions. At this address it is possible to download SQL SERVER EXPRESS 2019 for Windows operating systems.
Then click on “Download Now” and proceed with the download of the SQL Server Express 2019 version.
Once the software download phase is complete, all you have to do is proceed with the installation which, however simple, still concerns the installation of a server-type software that can also take place on your personal PC with a Windows 8 or Windows operating system. 10. That is, more modern desktop computers, you don’t need a server to install it on.
In this regard, in case you find it difficult during installation, I recommend that you follow this video tutorial to dispel any doubts about the settings and choices to be made during the procedure.
After the installation of SQL SERVER, restart the computer as required and all the services necessary for managing relational databases are active and ready for use. However, there is still a command console for SQL SERVER, that is, the software that will allow you to issue T-SQL commands to managed databases. In fact, in the next paragraph we will see how to install SQL SERVER Management Studio.
2. Install SQL SERVER Management Studio (SSMS)
Once the installation of SQL SERVER EXPRESS 2019 is finished, you must necessarily install another software. This software is SQL SERVER Management Studio. From here on we will refer to this tool with the acronym SSMS.
Then, click on this link and then on the item “Free Download for SQL Server Management Studio (SSMS) 18.10“. Wait for the download to complete and proceed with the installation of SSMS.
Both for SQL SERVER EXPRESS 2019 and for SSMS the download could take up to several minutes, being very full-bodied applications. In any case, the download speed will depend solely and exclusively on the speed and quality of your internet connection.
In the video tutorial that follows, you can see step-by-step how to install SQL SERVER Management Studio and start writing your first SQL queries.
In reality there are also other tools to send commands to SQL SERVER databases, one of the most accredited, in addition to Microsoft’s SSMS, is Toad For SQL SERVER. This application, developed by Quest, is an excellent tool widely used by developers all over the world to manage not only SQL SERVER databases, but also MYSQL, ORACLE, and many others.
Once the SSMS installation is complete, you need to start creating your first databases, a necessary step to be able to write an SQL Query. In the next paragraph we will see how to create a database with SSMS.
3. Create Database on Microsoft SQL SERVER EXPRESS 2019
To create a new database with SSMS it is very simple, in fact, once you have started SSMS all you have to do is click with the right mouse button on the Databases item and then on New Database.
Now, type in the Database Name field the name of the database you want to create, for this purpose I have chosen the test name. Now click on the OK button and your first database has been created.
Once you have created the test database you need to create another SQL SERVER object, I am talking about the table object. In fact, in the next paragraph we will see how to create a table within a database on SQL Server.
4. Create Table with Microsoft SQL SERVER EXPRESS 2019
To create a table with SSMS is very simple, in fact, once you have created and identified the entry relating to the test database, you just have to click with the right mouse button on the test item under Databases and then on New Table.
The operation, however simple, is not yet completely complete. In fact, a table contains a structure, that is, a set of fields or columns necessary to contain the data that will populate the table from time to time.
To achieve the purpose of this guide, we will only create two tables, one will be the anagrafica data table and the other we will call tax_codes. Therefore, the first will contain the fields id, surname and name, while the second will contain the fields id and code_fiscale. At this point, however, there is a clarification to be made, you will surely have noticed the presence of an id field.
This field is mandatory, not for the name you can assign it of your own choice, but for the characteristics it has and each table must have at least one. The feature that distinguishes the id field is the activation of the identity property, this property makes the id field a auto-increment field, that is, the content of this field is managed only and exclusively by SQL SERVER.
And therefore, with each new insertion of records, the value of this field is automatically increased by one unit. Therefore we are talking about a field that will contain only and exclusively numbers.
SQL SERVER needs a field of this type to be able to uniquely identify a record among the many contained in the table. In fact, SQL SERVER, in order to update or delete a record, uses the contents of the id field to identify the record to be updated with T-SQL statements.
Now, proceed with the creation of the anagrafica data table which will have the structure highlighted in green in the figure below:
Now click on the icon depicting a floppy disk, save and assign the registry name to the newly created table. Now, continue with the creation of the tax_code table which will instead have the structure highlighted in green in the figure below:
Also in this case click on the icon depicting a floppy disk, save and assign the name code_fiscal to the newly created table. By the way I forgot: “did you create the self-incrementing id field? I hope so“.
5. How to populate tables with T-SQL
We already talked about T-SQL, remember? It is the language used to write SQL Queries in SQL SERVER. Well, now we’re going to start getting to grips with some SQL commands, so let’s start populating the two created tables with data that is useful for building queries.
Therefore, click on the New Query button and type the instructions shown in the figure, then press the Execute button.
Our two tables have been populated with fictional data for the sole purpose of being able to write SQL queries and return the expected result sets. Now, however, let’s verify the contents of the two tables by typing our first SQL query. Then, click the New Query button and type the following SQL statement:
SELECT * FROM Anagrafica
Now click on the Execute button. You will notice that in the window below the one containing the SELECT statement, all the records of the anagrafica data table have been listed and previously inserted with the INSERT statements.
Now, you can try to check the contents of the tax_codes table by typing the SELECT * FROM codici_fiscali statement, repeating the same operations performed previously for the anagrafica data table.
6. The best way to Query SQL SERVER
Many less experienced programmers use MS Access wizards to produce queries to be fed to SQL SERVER. Unfortunately, the declarative nature of SQL SERVER is completely different from that offered by MS Access. Hence, it is totally wrong to copy and paste queries from MS Access to SQL SERVER or other DBRMS. I’m sorry, but you have no escape, queries, with SQL SERVER, you have to write them from scratch.
So, let’s not waste time and try to focus on how the SQL SERVER database engine cycles through data. It may seem strange to you but the point is all there. When we typed the SELECT command we gave the order to SQL SERVER to cycle through all the records of a table. Keep in mind that a SELECT statement can also operate on multiple tables at the same time, but there is always a table that is the main one, that is, the one on which the main data loop operates.
6.1 The aliases
Fortunately, the SELECT statement is not an armored statement, on the contrary, SELECT allows you to customize the returned result set by working a lot with aliases. That is, whenever you enter the name of a table, in a query, it is always convenient to associate an alias to the table name, in this form:
SELECT A.* FROM anagrafica AS A
But what is the importance of the alias “A“? The alias allows us to refer, during the scanning of the records by SQL SERVER, to the fields of the anagrafica data table and process their contents record after record.
For example, if you want to recover the tax code of each name, the previous instruction could become:
SELECT A.Cognome, A.Nome, ( SELECT TOP 1 C.codice_fiscale FROM codici_fiscali AS C WHERE C.id = A.id ) FROM anagrafica AS A
Taking a look at the previous SELECT statement, we can see that it is now possible to process the content of each individual registry record by following a sort of code block, formed by the two round brackets, within which it is possible to recover, thanks to the aliases, fields or contents of other tables as well. That is, interpose an SQL Query, even a very complex one, between the list of fields in the SELECT clause.
So, thanks to aliases now, instead of a field name in the main table, which is to be scanned, it is possible to insert code to retrieve contents, execute functions, or perform calculations.
This technique is the one that will allow you to understand how to interact, while scanning a table, with the data retrieved from other tables from time to time.
6.2 Condition-based processing; the CASE command
Another aspect not to be overlooked when processing an SQL query is the ability to subject the values retrieved during the scan to conditional expressions.
SELECT A.Cognome, A.Nome, ( CASE WHEN ( SELECT TOP 1 SUBSTRING(C.Codice_fiscale, 7, 2) FROM Codici_fiscali AS C WHERE C.id = A.id ) = '87' THEN '>= 34 year old' ELSE 'less than 34 year old' END ) AS 'age' FROM Anagrafica AS A;
Our example SQL Query has now been modified by interposing conditional statements to fetch data from other tables. From here it is clear how it is possible to cycle at the same time on the data of the main table and at the same time evaluate conditional expressions.
In this case WHEN evaluates the year contained in the tax_code field, retrieved with a SELECT in the tax_code table, and if equal to 87 it returns the string ‘> = 34 years old’ as the content of the calculated field ‘age ‘, otherwise it returns the string ‘Less than 34 years old‘.
In short, by appropriately combining Aliases and conditional expressions it is possible to obtain complex result sets in the face of even very compact code.
7. W3Schools.com the interactive online portal for developers
For years now there has been an interactive portal on the net where it is possible to try scripts in any language. T-SQL is no exception, in fact, at this address you can practice all the SQL SERVER commands in an environment specifically designed to guarantee easier and more immediate learning.
In W3Schools.com you will find thousands of code examples to try instantly using an online editor, you will have the possibility to edit the examples and run the code on your computer within the browser window, to see how your script works, before implementing it.
The topic covered in this guide is very broad and the suggested method for writing SQL Server Queries is to be considered as a starting point for entering the fantastic world of remote database programming.
In any case, I suggest you follow this video tutorial where you will be able to get further tips, in this area, and other useful commands to optimize the writing of SQL Query.