A relational database stores information in tables where the data is organized in predefined relationships. In order to retrieve data from such a database, SQL can be used. SQL (Structured Query Language) is a declarative programming language designed to generate queries, update and manage relational databases, create database schemas and modify those, and control access to databases. SQL is a very in-demand skill these days when the cloud and big data are on the rise. It means that having a basic understanding of how SQL queries work can be very helpful for you whether you are a business analyst or a database tester.
Structure of SQL queries
The structure of any SQL query is basically the same. It all begins with a statement that is followed by additional parameters and operands that apply to that particular statement. Each statement and its modifiers are usually based on official SQL standards and certain extensions relating to the specific database.
In this article, we are going to take a closer look at the SQL queries for a better understanding of the structure and peculiarities. For this purpose, let us use the BicycleStoreDev database as an example:
First query — SELECT
Tables are the way how databases go about storing data. Each table consists of rows representing unique values and columns representing fields in those records. You can use SQL queries to work with this structure and retrieve information from a database. Use SQL Fiddle website to practice SQL code writing.
One of the most frequently used SQL queries is SELECT. It allows retrieving certain types of data from a database. The most basic syntax of this statement is:
SELECT select_listFROM schema_name.table_name;
In the syntax above:
- Instead of select_list, specify the list of the column names and separate them by commas
- As their names suggest, schema_name and table_name placeholders are to be substituted by the names of the schema and table where the columns are located
In real life, the SELECT statement will look somewhat like this:
To get data from all table columns, you do not have to specify all of them in the query. To make it nice and brief, you can use SELECT * as a shorthand. This might be helpful while examining the tables that you are not familiar with or for ad-hoc queries. However, it is important to use it only when necessary since it might cause some performance issues if you are dealing with large tables.
Filtering — WHERE clause
When it comes to filtering data, the SELECT statement is usually accompanied by the WHERE clause. It is designed to extract only those records that fulfill a specified condition. You can define the said conditions by means of the corresponding operators: LIKE, IN, BETWEEN, AND, OR, NOT. Now, we will take a closer look at each of these operators separately and provide you with sample queries along with the results of their execution.
LIKE
The basic syntax of the query can be adjusted according to your particular database:
SELECT select_listFROM schema_name.table_nameWHERE column_name LIKE 'pattern';
After we execute this query on the BicycleStoreDev database, the results will contain all the columns from the select_list, but only those rows that contain the specified values.
IN
SELECT select_listFROM schema_name.table_nameWHERE column_name IN (values);
On running the query on the BicycleStoreDev database, we will see all the columns from the select_list, but only those rows that contain the specified year.
BETWEEN
SELECT select_listFROM schema_name.table_nameWHERE column_name BETWEEN value AND value;
The BETWEEN operator is usually used to retrieve only the rows between certain minimum and maximum values.
AND
Use the AND logical operator to combine two Boolean expressions. It will return only those rows where both expressions evaluate to TRUE.
SELECT select_listFROM schema_name.table_nameWHERE boolean_value AND other_boolean_value;
As illustrated on the screenshot, the query returns only those rows where both conditions are met: the product belongs to category 1 and costs more than 400:
OR
The OR logical operator will return a row if at least one of the specified Boolean expressions is TRUE.
SELECT select_listFROM schema_name.table_nameWHERE boolean_value OR other_boolean_value;
In this case, on running the query you will get all the rows where at least one of the conditions is met: it either belongs to category 1 or costs more than 400 (or both). This way there turns out to be more query results:
NOT
NOT adds a kick to other logical operators. If you run a query with NOT, the result of the expression following this operator be reversed.
SELECT select_listFROM schema_name.table_nameWHERE boolean_value AND/OR NOT other_boolean_value;
The NOT operator can be combined with the other ones making the query results different. For example, if you add NOT to one of the previously mentioned query, the output will look like this:
Sorting — ORDER BY & GROUP BY statement
The SELECT command by itself returns the values in no particular order. The ORDER BY and GROUP BY statements come in handy when you need to give some structure to the query output. The ORDER BY clause sorts the query results by one and presents them in ascending or descending order. While the GROUP BY clause uses aggregate functions to arrange data into groups. It relates to columns containing identical values in different rows. The ORDER BY and GROUP BY statements can complete one another, therefore, such combinations are rather common.
ORDER BY
The basic syntax looks like this:
SELECT select_listFROM schema_name.table_nameWHERE conditionsORDER BY column1, column2, .. columnN [ASC | DESC];
-
ASCis a command used to sort the results in ascending order. Adding this condition is optional, as it is the default way to sort the query results in SQL. -
DESCis a command used to sort the results in descending order. UnlikeASC, we must defineDESCexplicitly when we would like the ORDER BY SQL command to return the results in descending order.
As you can see from the screenshot, we have filtered the results by the year 2016 and sorted the results alphabetically in ascending order:
Usually, when it is necessary to limit the output to the desired number, the LIMIT operator is used. However, SELECT LIMIT is not supported in all SQL databases. For such databases as SQL Server or MS Access, use the SELECT TOP. Therefore, in case you are willing to limit the query output to the first ten results, use SELECT TOP (10):
GROUP BY
The GROUP BY clause can be combined with ORDER BY:
SELECT select_listFROM schema_name.table_nameWHERE conditionGROUP BY column_name1, column_name2 ,...ORDER BY column_name1, column_name2 ,...;
The following query allows you to select the orders that were placed by certain users along with the order date and display the results sorted by the ID for the specified users:
Cleaning the duplicate — DISTINCT clause
There are cases when you need to retrieve only the unique records from a table. In this case, it is convenient to use the DISTINCT operator:
SELECT DISTINCT select_listFROM schema_name.table_name;
In this example, the simple SELECT statement executed in SQL Query Builder returns all cities of all customers in the customer table:
However, once you add the DISTINCT operator to the query, you will see all the duplicates disappear from the output:
Let's make it easier with SQL Query Builder
What makes query building with dbForge Query Builder for SQL Server so fast and simple? It is definitely not having to type most statements manually. Our SQL query tool brings you to a whole new interactivity level thanks to the visual features of SQL query designer. Let us take a look at several examples of how to write complex queries without having to worry about syntax:
1. With our user-friendly graphical interface, all you have to do is simply drag the customer table into the working area and select the columns you would like to include in the query. For example, select FirstName, LastName, and Email on the diagram and click Execute:
You can also view the previously generated and executed query by clicking Text:
2. Now, let us select State on the diagram and type "like ca" in the WHERE column. Once done, we will see that Query Bulder has enclosed 'ca' in single quotation marks automatically:
The result of the query execution will consist only of those rows, that contain "CA" in the State column:
3. To sort the query results by LastName in descending order, simply choose Descending from the drop-down menu in the Order By column:
Check out more dbForge Query Builder features on the product overview page.
