How to Write SQL Queries?

The SQL language (Structural Query Language) was created in order to standardize data manipulation commands in DBMSs. Today, although the language has a considerable amount of proprietary extensions and implementations, it can be said that the goal has been achieved when you write SQL queries. Knowing the language well, it is possible to access the basic resources of any relational bank, such as Oracle, SQL Server, or MySQL, with virtually no changes.

What Is a Query?

The query is a syntax or command people use to access and display data in a database system. Queries can set which data needs to be displayed the way you want. Besides, everyone uses queries to make data interact with each other.

The query is also commonly referrers to as a query language or query language. Currently, the most popular query language among Database Administrators is SQL.

Three Types of Database Queries in SQL

There are three types of database queries in SQL, namely:

DDL ( Data Definition Language )
DML ( Data Manipulation Language )
DCL ( Data Control Language )

These three queries function to create/define database objects, such as creating tables, manipulating the database, and controlling it.

Basic Data Manipulation Operations In SQL

As we have seen, the DML (Data Manipulation Language) instructions work on the data it stores in our DBMS, allowing us to consult or modify them.

In general, the basic data manipulation operations that we can perform with SQL are called CRUD operations (from C-reate, R-ead, U-pdate, and D-elete, that is, Create, Read, Update and Delete, it would be CLAB in Spanish, but not used). You will see it used this way in many places, so learn that acronym.

There are four instructions for performing these tasks:

INSERT: Insert rows into a table. It corresponds to the “C” in CRUD.
SELECT: displays information about the data stored in the database. This information can belong to one or more tables. It’s the “R.”
UPDATE: Updates information in a table. It is obviously the “U.”
DELETE: Delete rows from a table. It corresponds to the “D.”

Data Query

Now we are going to focus on the “R” in CRUD, that is, on how to retrieve the information that interests us from within a database, using the query language or SQL. We will worry about how we get to enter the data first.

To make queries on the database tables, we have the SELECT statement. With it, we can consult one or more tables. Without a doubt, it is the most versatile command in the SQL language when you write SQL queries.

There are many clauses associated with the SELECT statement (GROUP BY, ORDER, HAVING, UNION). It is also one of the instructions in which database engines most frequently incorporate additional clauses to the standard, which is the one we will see here.

Let’s start by looking at simple queries based on a single table. We will see how to obtain rows and columns of a table in the order we need. The result of a SELECT query returns a logical table. That is, the results are a data relationship, which has rows/records, with a series of fields/columns. Just like any table in the database. However, this table is in memory while we use it, and then it is discarded. Whenever we run the query, the result is recalculated.

The basic syntax of a SELECT query is as follows (optional values are enclosed in brackets):

SELECT [ ALL / DISTINC ] [ * ] / [ListaColumnas_Expresiones] AS [Expresion]
FROM Nombre_Tabla_Vista
WHERE Condiciones
ORDER BY ListaColumnas [ ASC / DESC ]

Let’s Analyze Each of the Parts of the Query to Better Understand it.

SELECT

Allows you to select the columns to be displayed and in the order in which they are to be displayed. It is simply the instruction that the database interprets as that we are going to request information.

ALL / DISTINCT

ALL is the default, specifies that the result set can include duplicate rows. As a rule, it is never used.

DISTINCT specifies that the result set can only include single rows. That is, if there are exactly the same records (satisfying WHERE condition) that appear more than once when performing a query, they are eliminated. Very useful on many occasions.

Field Names

We must specify a list of field names in the table that interests us and, therefore, we want to return. Usually, there will be more than one, in which case we separate each name from the others with commas.

You can prepend the name of the table to the name of the columns using the table—column format. In addition to column names, you can put constants, arithmetic expressions, and functions in this list to obtain dynamically calculated fields. If we want it to return all the fields in the table, we use the wildcard “*” (asterisk).

The names indicated must exactly match the names of the fields in the table, but if we want our logical results table to have a different name, we can use:

AS

It allows renaming columns if we use it in the SELECT clause or renaming tables if we use it in the FROM clause. It’s optional. With this, we can create various aliases of columns and tables. We will see an example next.

WHERE

Specifies the filter condition for the returned rows. It is used when you do not want all the rows in a table to be returned, but only those that meet certain conditions. Typically, this clause is used in most queries.

LIMIT

In most cases, we will not need to work with all the records in our table or even in our query; That is why it is a very good idea to limit the number of records that we will obtain from the server; in this way, our query will be faster. The clauses that we will use in MySQL to limit records will be: LIMIT and ORDER BY.

First Records:

SELECT 
  first name, 
  last name 
FROM 
  users 
LIMIT 
  3;

Latest records:

SELECT 
  first name, 
  last name 
FROM 
  users 
ORDER BY 
  id DESC 
LIMIT 
  3;

If we are using another database manager, like SQL Server, we can implement clauses such as TOP and ROW NUMBER.

Some Examples

Show all the data of the Clients of our company:

SELECT 
  * 
FROM 
  Customers

Show surname, city, and region (LastName, city, region) of the USA employees (note the use of AS to give the name in Spanish to the returned fields):

SELECT 
  E.LastName AS Apellido, 
  City AS Ciudad, 
  Region

FROM 
  Employees AS E

WHERE 
  Country = ‘USA’

Clients that we do not know to which region they belong (that is, they do not have any associated region):

SELECT 
  * 
FROM 
  Customers 
WHERE 
  Region IS NULL

The different regions from which we have a client, accessing only the client table:

SELECT 
  DISTINCT Region 
FROM 
  Customers 
WHERE 
  Region IS NOT NULL

Clients that belong to regions CA, MT, or WA, sorted by region ascendingly and by name descending:

CODE 
SELECT 
  * 
FROM 
  Customers 
WHERE 
  Region IN(‘CA, ’ ‘MT, ’ ‘WA’)

ORDER BY 
  Region, 
  CompanyName DESC

Customers whose name begins with the letter “W”:

SELECT 
  * 
FROM 
  Customers 
WHERE 
  CompanyName LIKE ‘W %.’

Employees whose code is between 2 and 9:

SELECT 
  * 
FROM 
  Employees 
WHERE 
  EmployeeID BETWEEN 2 
  AND 9

Customers whose address contains “ki”:

SELECT 
  * 
FROM 
  Customers 
WHERE 
  Address LIKE ‘ % ki %.’

Sales of product 65 with quantities between 5 and 10, or that do not have a discount:

SELECT 
  * 
FROM 
  [Order Details] 
WHERE 
  (
    ProductID = 65 
    AND Quantity BETWEEN 5 
    AND 10
  ) 
  OR Discount = 0

Note: In SQL Server, to use object names with special characters, they must be enclosed in square brackets. For example, in the previous query, [Order Details] is written in brackets because it has a blank space in its name. In other RDBMS, double quotes are used (Oracle, for example: “Order Details”), and in others, single quotes are used (for example, in MySQL).

FAQs on How to Write SQL Queries

What is the most popular database Administrator query language?

Right now, the most popular query language among Database Administrators is SQL.

Are there additional clauses associated with the Select statement?

Yes, there are many clauses associated with the SELECT statement, such as GROUP BY, ORDER, HAVING, UNION.

Is there a way I can eliminate the records that appear more than once?

Yes, you can eliminate them with the DISTINCT option. If there are exactly the same records that appear more than once when performing a query, this option eliminates them. But while using it, one must be careful while setting WHERE condition.

Should I limit the number of records that I obtain from the server?

This depends on the situation you are in; it will not work with all the records in our table or even in our query; It is a great idea to limit the number of records you will obtain from the server. In addition to that, your query will be faster and uses less memory.

How can I implement clauses while using another database manager?

If you are using a database manager, such as SQL Server, you can implement clauses such as TOP and ROW NUMBER.