Structured Query Language (SQL) is the most commonly used language when working with relational databases. The language can be used for much more than simple queries, despite its name. It can also be used to perform all operations necessary to create and maintain databases.

What are the Advantages of Structured Query Language?

The structured query language provides many functions to read, modify or delete data. In addition, it is preferred by many analysts over other languages for the following reasons:

• It is semantically very easy to read and understand. The commands can be interpreted to a large extent even by beginners.
• The language can be used directly within the database environment. For basic work with information, the data does not have to be transferred from the database to another tool first. Simple calculations and queries are possible directly in the database.
• Compared to other spreadsheet tools, such as Excel, data analysis with Structured Query Language can be easily replicated and copied because everyone has access to the same data in the database. Thus, the same query always leads to the same result.

SQL provides an alternative to the kind of summaries and calculations that most perform in Excel spreadsheets, such as totals, averaging, or finding the maximum within a column. These calculations can also be performed over multiple data sets simultaneously.

Categories of SQL commands

The Structured Query Language can be used for all kinds of work related to relational databases. Therefore, we distinguish a total of three categories of commands.

Category	Description
Data Manipulation Language	– Inserting data into an existing table – Updating data – Deleting table rows – Selecting data from tables under certain conditions.
Data Definition Language	– Creation, modification, and deletion of tables
Data Control Language	– Administrative tasks – Creating new users – Granting access to parts of the database

Data types in the Structured Query Language

If we want to create a table in a relational database, we always have to specify a data type that is to apply to the individual columns. All data, which are added over time, must then also correspond to this schema.

The most popular data types are the following:

• Integer: For integers without decimal places. These can be both positive and negative.
• Numeric or Decimal: These are used for the so-called fixed-point numbers, i.e. there is a fixed number of digits before and after the decimal place. If we want to define booleans, i.e. columns that accept only one of two values, we also use numeric.
• Float, Real or Double: We use these types for floating-point numbers. These are numbers that do not have a fixed number before and after the decimal place.
• Character and Text: For all types of strings.
• Date and Time: These data types can be used for all columns where we want to store date or time information.

In addition, there are of course significantly more data types that we can use in the Structured Query Language. Depending on the use case, the data types and their advantages and disadvantages must be evaluated.

Which Commands should you know as a Beginner?

As we have already learned, SQL commands are classified into three areas. In each of these areas, there are basic queries that are useful when working with relational databases.

Data Definition Language

This section includes all commands that can be used to create new data structures, such as databases, tables, or views. For our examples, we use the table “orders”, which contains order information in the columns “order_id”, “customer_id” and “sales”.

With the command “CREATE TABLE” the table can be defined and the columns with their data types can be specified:

CREATE TABLE orders (
    order_id int,
    customer_id int,
    sales decimal(6,2))

Data Manipulation Language

This area is about displaying or even changing concrete data.

Using the “SELECT” command, data can be retrieved from the mentioned table. To retrieve the “order_id” column from the “orders” table, use the following query:

SELECT order_id FROM orders

This basic query can be filtered using “WHERE”. For example, if we want to query all order numbers that had a turnover higher than $50, the command looks like this:

SELECT order_id FROM orders WHERE sales > 50

If more than one filter is needed, they can be connected with “AND” and “OR”. With the help of the “AND” connection, you can then find out, for example, which order numbers had a higher turnover than 50€ and were transacted by the customer “12345”:

SELECT order_id FROM order WHERE sales > 50 AND customer_id = 12345

Data Control Language

Now that we have created the table and can also query the data, we still need to distribute rights so that not every user can make changes. Otherwise, problems can arise very quickly if, for example, inexperienced users change or even delete data.

The “GRANT” command can be used to define which types of queries a user is allowed to execute on a specific table or database. In our case, user 1 is allowed to query data as well as modify data. User 2, on the other hand, is only allowed to query data.

GRANT SELECT, UPDATE ON orders TO user_1
GRANT SELECT ON orders TO user_2

The opposite command to this is “REVOKE” with which existing rights can also be withdrawn again:

REVOKE UPDATE ON orders FROM user_1

How do you pronounce SQL?

If you have read this article so far and wondered from the beginning how to pronounce the abbreviation SQL, we are sorry to disappoint you. This will probably remain a mystery forever. In general, there are two camps: S-Q-L and “Sequel”. You can find a detailed article on this topic here. The author actually asks one of the inventors of SQL, Don Chamberlin, this very question.

This is what you should take with you

• The Structured Query Language can be used for all work around a relational database.
• This includes, for example, the creation of new tables, the definition of user permissions, and of course the query of data.
• When we create new tables we have to define the data types in the schema. For this, there are some classic data types, like integer, float, or date.
• Unfortunately, it is still not finally clarified whether the abbreviation of Structured Query Language is pronounced S-Q-L or “Sequel”.

Other Articles on the Topic of SQL

• You can find a detailed tutorial for the query language here.