How to Write Basic SQL Queries

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_list
FROM 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_list
FROM schema_name.table_name
WHERE 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_list
FROM schema_name.table_name
WHERE 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_list
FROM schema_name.table_name
WHERE 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_list
FROM schema_name.table_name
WHERE 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_list
FROM schema_name.table_name
WHERE 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_list
FROM schema_name.table_name
WHERE 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_list
FROM schema_name.table_name
WHERE conditions
ORDER BY column1, column2, .. columnN [ASC | DESC];
                                    

• ASC is 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.
• DESC is a command used to sort the results in descending order. Unlike ASC, we must define DESC explicitly 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_list
FROM schema_name.table_name
WHERE condition
GROUP 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_list
FROM 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.