SQL Basics

What is SQL?

SQL, or Structured Query Language, is a standard language for managing relational databases. SQL is used for retrieving, inserting, modifying, and deleting data from databases. There are many different types of SQL databases, such as MySQL/MariaDB, PostgreSQL, Oracle, and Microsoft SQL.

What is a relational database?

Relational database is a database management system (DBMS) that organizes data into one or multiple tables with a mutual connection. Each table consists of columns and rows, which are called records. The different tables in the database are connected to each other using specific fields, called keys. In a relational database, an individual table can be large and complex, but it is easy to handle when it is processed according to a certain structure.

Relational databases are very common in business, administration, and information technology fields. They enable efficient storage, retrieval, and maintenance of data, as well as establishment of connections between data and maintenance of data integrity.

How does the application connect to the database?

A connection is established between the web application and the database management system using the TCP-based SQL protocol. For example, MySQL/MariaDB listens on port 3306 by default. The application is configured with a username and password/secret/certificate that the application uses to identify itself to the database. The database has certain privileges set for the application user, which should be kept to a minimum in order to minimize potential damage in case the application is compromised.

When the connection is open, the application sends SQL queries reminiscent of the English language to the database, and the database responds by returning rows. Not always are rows returned, if it is, for example, an INSERT query that does not retrieve anything but rather creates new data.

SQL Database Structure

The database consists of multiple tables, each of which contains a specific type of information. The tables can, for example, be a customer registry, a product registry, or order history. In order to store the information in the tables in a SQL database, a column is needed that describes the data in the table. Columns define the structure of the table and determine the data types, which determine what type of data the column can contain. For example, if the table has columns 'name' and 'address', the name column stores the person's name and the address column stores their address.

Each table has a unique name that describes its content. The name of the table can be, for example, "customers". Tables can also contain a key that helps to connect tables to each other. Keys are often used to refer to another table, resulting in tables being combined and their relationships formed.

The data stored in an SQL database consists of tables with multiple columns that represent the information in the table. The tables can be connected to each other using keys. Efficient use of an SQL database requires a well-designed table structure that is clear and easily understandable.

SQL Queries

SQL is used slightly differently in different databases, but the basic principles are the same. SQL is used for retrieving, adding, modifying, and deleting data from databases.

SELECT query

This query selects all customers who live in Helsinki.

SELECT * FROM Customers WHERE City = 'Helsinki';

| Name | Address | City |
| -------------| --------------------| ----------|
| Matti Meikäläinen | Example street 1 | Helsinki |
| Maija Virtanen | Testikatu 10 A 4 | Helsinki |

INSERT-query

This query adds a new client to the table Clients.

INSERT INTO Customers (FirstName, LastName, Email) VALUES ('Matti', 'Meikälainen', 'matti.meikalainen@gmail.com');

UPDATE-Survey

This query updates the customer's city to Tampere.

UPDATE Customers SET City = 'Tampere' WHERE First Name = 'Matti' AND Last Name = 'Meikäläinen';

DELETE Query

This query deletes the customer from the table Customers.

DELETE FROM Customers WHERE First Name = 'Matti' AND Last Name = 'Meikäläinen';

JOIN-Survey

This query connects two tables, Customers and Orders, and returns the first names of all customers and the order dates.

SELECT Customers.FirstName, Order.OrderDate
FROM Customers
JOIN Order ON Customers.CustomerID = Order.CustomerID;

| First name | Date of order |
| ---------| ------------|
| Matt | 2022-01-01 |
| Maija | 2022-02-01 |
| Juha | 2022-02-15 |
| Laura | 2022-03-01 |

GROUP BY Query

This survey groups customers by cities and calculates the number of customers in each city.

SELECT City, COUNT(*) AS Num
FROM Customers
GROUP BY City;

| City | Qty |
| ---------| --- |
| Helsinki | 2 |
| Tampere | 1 |

ORDER BY -Query

This query returns all customers from the table Customers in ascending order by last name.

SELECT * FROM Customers ORDER BY Surname ASC;

| Name | Address | City |
| ---------------| ----------------------| ----------|
| Maija Virtanen | Testikatu 10 A 4 | Helsinki |
| Matti Meikäläinen | Example street 1 | Helsinki |
| Laura Lahtinen | Example street 10 B 12 | Tampere |

DISTINCT Query

This query returns all unique cities where customers reside.

SELECT DISTINCT City FROM Customers;

| City |
| ----------|
| Helsinki |
| Tampere |

LIKE -Survey

This query returns all customers whose last name starts with the letter M.

SELECT * FROM Customers WHERE Surname LIKE 'M%';

| Name | Address | City |
| ---------------| ---------------------| ----------|
| Matti Meikäläinen | Example street 1 | Helsinki |
| Matti Virtanen | Testikatu 5 A 6 | Tampere |

IN -Query

This query returns all customers who reside either in Helsinki or Tampere.

SELECT * FROM Customers WHERE City IN ('Helsinki', 'Tampere');

| Name | Address | City |
| ----------------| ---------------------| ----------|
| Matti Meikäläinen | Example street 1 | Helsinki |
| Maija Virtanen | Testikatu 10 A 4 | Helsinki |
| Laura Lahtinen | Example street 10 B 12 | Tampere |

UNION -Query

This query combines two separate queries into one result table, which contains all the rows that are returned from either query. The returned rows will appear as follows:

(SELECT Name, Address, City FROM Customers WHERE City = 'Helsinki')
UNION
(SELECT Name, Address, City FROM Customers WHERE City = 'Tampere')

| Name | Address | City |
| ----------------| ----------------------| ----------|
| Matti Meikäläinen | Example street 1 | Helsinki |
| Maija Virtanen | Testikatu 10 A 4 | Helsinki |
| Laura Lahtinen | Example street 10 B 12 | Tampere |

Subquery

This query uses a subquery to find all cities where orders were made on a specific day. After that, it returns the names of all customers and the cities that are located in these cities.

SELECT Name, City
FROM Customers
WHERE City IN
    (SELECT City FROM Order WHERE Order date = '2022-02-01')

| Name | City |
| ----------------| ----------|
| Matti Meikäläinen | Helsinki |
| Maija Virtanen | Helsinki |

Comments

SQL comments are strings that are intended to explain SQL code and are ignored by the database when executing code. They are useful when explaining complex queries or when marking code that should not be executed.

There are two types of SQL comments, single-line and multi-line.

Single-line comments

A single-line comment begins with either "--" or "#" notation. All characters after the comment notation are ignored. Note that the # symbol does not work in all SQL databases. Also note that there must be a space after -- before starting the comment.

-- This is a one-line comment
SELECT * FROM Customers; -- Another one-line comment

Multiple-line comments

A multi-line comment starts with "/*" and ends with "*/". All characters between these characters are ignored.

/* This is
multi-line
comment */
SELECT * FROM Customers; /* Another multiline comment */