SQL (Structured Query Language) is the standard language used to access relational databases. SQL was developed to be able to handle a series of databases on a large scale with a fairly fast time. SQL operates with several function programs that can later support and assist all administrative activities in the database. Today, many database software uses SQL as commands to access data, such as ORACLE, MySQL, Microsoft Access, PostgreSQL, and SQLite.

Basic Query

In this section, we will learn the basics of querying using SELECT statements, such as displaying a column in a table, filtering data, relational and boolean operators.

1.Extracts data from a database

To display all the columns can use the following query.


SELECT * FROM table_name

In addition, we can choose which columns we want to display using the following query.


SELECT column1, column2,...
FROM table_name;

2. WHERE

WHERE can be used to select certain rows that have a predefined condition or criteria.


SELECT column1, column2, ...
FROM table_name
WHERE condition;

3. Relational Operator

A relational operator is an operator that is used to perform a comparison between two values. The following are relational operators that are commonly used.

4. Boolean Operators

Boolean operators include AND, OR, and NOT operators. These operators can be combined with a WHERE clause.

a. AND operator


SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3;

b. OR operator


SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3;

c. NOT operator


SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Advanced Query

Some operators are very useful in filtering data such as BETWEEN, IN, and LIKE. In addition, there are also several discussions about unique data, using aggregate functions, sorting data, grouping data, and filtering using aggregation results.

1.BETWEEN and NOT BETWEEN operators

The BETWEEN operator is used to select values in a specified range. The value can be a number, text, or date.


SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

2. Operators IN and NOT IN

The IN operator is used to filter data contained in a particular list. While NOT IN is used to make certain data exceptions.


SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

3. Operators LIKE and NOT LIKE

The LIKE operator is used to display data based on a pattern / characters that has been determined according to needs, while NOT LIKE is used to display data that does not contain these characters.

In searching using the LIKE operator, it is necessary to mention the Wilchard sign in the form of a percent (%). The sign (%) means it matches any character and of any length. The LIKE operator is not case sensitive, where lowercase and uppercase letters are considered the same.


SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

4. DISTINCT

DISTINCT is used to obtain unique data (there are no twin data). This operator is often used to display data without any duplicates.


SELECT DISTINCT column1, column2, ...
FROM table_name;

5. Aggregate Function

An aggregate function is a function provided to generate a single value based on a number of data. The following are some of the frequently used aggregate functions.


SELECT ‘Aggregate Function’(column_name)
FROM table_name
WHERE condition;

6. ORDER BY

The query results can be sorted according to certain criteria using ORDER BY. Sorting can be done in ascending (from smallest to largest) or descending (vice versa).


SELECT column1, column2, …
FROM table_name
ORDER BY column1, column2, … ASC|DESC;

7. GROUP BY

GROUP BY is used to group data based on a category.


SELECT column1, column2, ...
FROM table_name
GROUP BY column_name;

8. HAVING

The HAVING clause is used in SQL because the WHERE clause cannot be used with aggregate functions. The use of the HAVING clause is related to the GROUP BY clause. Its purpose is to define conditions for GROUP BY. Only groups that meet HAVING will be generated.


SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Reference

Abdul Kadir (2018). Tuntunan Praktis Belajar Database Menggunakan MySQL. Revised Edition.
Hariono Blog (https://hariono.site.unwaha.ac.id/)