SQL basic statements (queries)

Introduction

SQL (Structured Query Language) is a language used to manage relational databases. This language is designed to allow the user to store, retrieve, manage or manipulate data within a database management system (DBMS).
This article will talk about the commands that are an integral part of the SQL language, which is used to work with a “relational database management system” (DBMS) called “SQLite” (the current version of this DBMS is 3.0, i.e. sqllite3). In addition to this “Database Management System”, the commands can be applied with minor correction for other relational systems (MySql, PostgreSQL, Microsoft SQL Server…). The basic commands for working with SQL databases are very similar in all DBMSs, although each system has its own specifics (eg Microsoft SQL server is statically typed while SQLite is dynamically typed…). Read more about database management systems in the article “Database Management Systems (DBMS)”.

What is sqlite3?

sqlite3 is a frontend tool in Command Prompt or terminal for working with SQLite library. Using it, we can perform queries and display results independently of some other application. You can view the installation of this tool here.

Commands in sqlite3

In sqlite3, there are two types of commands: those that start with a period, so after them it is not necessary to signal to the SQL engine that the command ends, and those that do not start with a point, but must signal to the engine where the command ends with a semicolon. To exit sqlite3, it is necessary to use .quit or .exit or possibly “CTRL + C” in the terminal.

Creating and opening database

The command to create or open a database is the same. Whether the table will be created or just opened depends on whether it exists or not. If it does not exist then it will be created, and if there is such a database in the given folder then it will be opened. The syntax is as follows: first we write sqlite3 and then the database name together with the extension (.db):

After which the database will be created in the current directory and the Command Prompt will appear:

Database verification is done with the command:

Basic commands for working with tables

Creating a table (CREATE TABLE)

The syntax for creating a table is as follows:

By default, a column can contain NULL values (ie, some member can have no value in any of the columns). The constraint NOT NULL obliges the member NOT to accept that there is no value in a field, i.e. to have a NULL value. This requires the field to always contain a value, meaning you cannot insert a new member or update an old one without adding a value to the field that has “NOT NULL” defined.

Example

In the previous example, ID, NAME, and AGE are required fields.

Listing all database tables (.tables)

Listing of all database tables is done with the command:

View table schema (.schema)

In order to see the scheme according to which the table was created, we use:

Deleting the table (DROP TABLE)

Deleting a certain table is done according to the following syntax:

Inserting a table member (INSERT INTO)

Inserting a new member of the table means adding a new row not at the end of the table and is done with the command INSERT INTO.
There are two types of syntax:

• so-called “Detailed” syntax
• so-called “Simple” syntax

Detailed syntax:

It is also necessary to specify the names of the columns of that table, and then insert new values.

Example

In the previous example, we created a table called COMPANY whose columns are (ID,NAME,AGE,ADDRESS,SALARY), so inserting each new member would look like this:

Simple syntax

Simplified syntax implies that column names are not mentioned, but only values:

Update table member (UPDATE)

This query is used to modify an existing table row. The syntax looks like this:

Example

The command in this example will change the company address to “Texas” within the “COMPANY” table member whose ID is 6

NOTE:
If you did not use the WHERE clause, then all the table members would have their address changed to “Texas”.

Deleting a table member (DELETE)

Deleting a specific table member/row is done with the syntax:

Example

In this example, the table member whose ID is 6:

will be deleted

Getting data (query) from a table (SELECT)

The syntax of this command that selects data is as follows:

Example

So this expression returns only two columns:

NOTE:
The order of the columns within the command does not have to follow the order in the database!

Example

Often we want to display all the columns of a table and we can do that by specifying exactly all the column names:

However, we can do it in a simpler way using the wildcard * (star):

Which returns the result:

WHERE clause

This clause is used to define a condition.

Example

This condition returns all rows where AGE is greater than or equal to 25:

LIMIT & OFFSET clause

LIMIT limits the number of rows that are selected, while OFFSET skips a certain number of rows. The syntax looks like this:

Example

Returns the following:

And if we add an OFFSET that will skip these first three lines:

We get a continuation of the table:

ORDER BY clause

This clause is used to sort the members of a table in descending or ascending order. It is always placed at the end of the expression and the syntax looks like this:

Example

This expression returns all members of the table sorted in ascending order by the column SALARY:

AND operator

This operator makes it possible to use multiple conditions that need to be satisfied.

Example

OR operator

This operator also makes it possible to compose a multiple condition, but it is enough that only one condition is satisfied.

Example

LIKE operator

The LIKE operator is used to find results where text values match the requested ones (not case sensitive). If the search text matches the pattern expression, the LIKE operator will return true. There are two wildcard characters used in conjunction with the LIKE operator:

• % (percentage) – represents zero, one or more characters
• _ (underscore) – represents only one character

Example

Returns results starting with 200 (2005, 20035, 200559…)

Example

Returns results containing 200 (152005, 1420035…)

Example

Returns results that end with 2(152, 14205552…)

Example

Returns results where the second digit is 2 and the last digit is 3(123, 1205553…)

Example

Returns results where the first digit is 2 and has at least two more digits.

Example

Returns results that have a middle line (South-Hall) in the text

GLOB operator

This operator is almost the same as the LIKE operator in that it is “case sensitive” and uses other wildcard labels even though they have the same meaning

• * (star) – represents zero, one or more characters (percent code LIKE)
• ? (question mark) – represents only one character (underscore code LIKE)

Example

Returns results where the second digit is 2 and the last digit is 3(123, 1205553…)

DISTINCT

This keyword with SELECT allows the output returned by the expression to have no “duplicates”. The syntax is like this:

Example

This expression returns the NAME and AGE columns but only with unique members in the NAME column:

Export and output formatting

Unordered exit ie. the table view looks like this:

However, there are certain commands that can improve this appearance and contribute to better readability.

.header (showing column names)

Showing the header with column names is enabled withcommand:

.mode (table layout)

The appearance of the displayed table depends on the selected mode:

• csv – Comma-separated values
• column − Left-aligned columns.
• html − HTML <table> code
• insert − SQL insert statements for TABLE
• line − One value per line
• list − Values delimited by .separator string
• tabs – Tab-separated values
• tcl – TCL list elements

The column mode is most often used because it is the most visible:

.width (column width)

Sometimes it happens that the default column width (10 letters) is not enough, so we can predefine the width with the command .width.

After this the first three columns will have the passed widths.

Example

We have seen what the unsorted output of the table looks like from the previous examples, now we will improve it with the following commands:

Now the sorted output looks like this:

NOTE:
Once defined rules for output layout remain until we change the base.

Export to excel

To export the table to Excel, we first need to define the output type:

And then to hash what we want to include in that output: