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.

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.

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 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.

This query selects all customers who live in Helsinki.

This query adds a new client to the table Clients.

This query updates the customer's city to Tampere.

This query deletes the customer from the table Customers.

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

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

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

This query returns all unique cities where customers reside.

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

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

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:

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.

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.

A single-line comment begins with either "

" 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 

". All characters between these characters are ignored.

## 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.

```sql
SELECT * FROM Customers WHERE City = 'Helsinki';
```

```sh
| 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.

```sql
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.

```sql
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.

```sql
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.

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

```sh
| 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.

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

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

### ORDER BY -Query

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

```sql
SELECT * FROM Customers ORDER BY Surname ASC;
```

```sh
| 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.

```sql
SELECT DISTINCT City FROM Customers;
```

```sh
| City |
| ----------|
| Helsinki |
| Tampere |
```

### 
LIKE -Survey

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

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

```sh
| 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.

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

```sh
| 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:

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

```sh
| 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.

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

```sh
| 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.

```sql
-- 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.

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


SQL Basics

This course is an introduction to web development and focuses on the basics of HTML, CSS, JavaScript and backend systems. The aim of the course is to provide students with the necessary skills and knowledge to design, develop and understand simple web applications.

This course is an introduction to web development and focuses on the basics of building websites, namely HTML, CSS, JavaScript, and fundamentals of backend systems. The goal of the course is to provide you with the necessary skills and knowledge to design and develop simple full-stack web applications, as well as understand web applications made by others.

In the first part, HTML is studied, which is a markup language used in building websites.


The second part covers the basics of CSS, such as the use of style sheets, rules, selectors, and formatting. It also covers advanced features of CSS, such as animations and responsive design.

In the third part, we will study JavaScript, which is currently an incredibly important tool in both frontend and backend development, and thus also in cybersecurity and ethical hacking.

In the final part, we will learn how to build back-end systems using the Python Flask framework. You will learn the basics of back-end systems, such as using databases, authentication, session management, and access control.

You can do the course exercises on your own computer if you want, but each code example has a button that allows you to directly run the code in the browser and see what the page looks like or what the script prints. So you do not need to install anything, but you can if you want.

It is recommended but optional to create an account on replit.com service. In that case, you can just click "Fork" on the code examples to make a copy for yourself to play with

SQL Basics

What is SQL?

Learn to hack — start here