SQL Tutorial Part 1

I'll explain about SQL for begginer with reference to w3schools tutorial. If you haven't seen it, please check it.

1. What is SQL?

SQL is progamming language for mainly handle to Database.

1.1 What is the benefit?

Here are key benefits of SQL:

1. Efficient Data Management and Querying:

   • SQL allows for efficient management and retrieval of data from relational databases. You can easily insert, update, delete, and query large datasets with complex conditions using simple and intuitive commands.

2. Data Integrity and Security:

   • SQL ensures data integrity through constraints (like primary keys, foreign keys, and unique constraints) and provides robust security features, including user authentication, access controls, and permissions management, ensuring that only authorized users can access or modify data.

3. Standardization and Flexibility:

   • SQL is a standardized language supported by many database systems, ensuring compatibility and portability across different platforms. It offers flexibility in handling various data types and supports complex operations like filtering, grouping, aggregating, and joining tables, which are essential for comprehensive data analysis and reporting.

1.2 Benefits compare to pandas

Here are benefits of SQL when compare to pandas:

Memory limitation:
・ For example, when grouping the data, pandas has read the all data from database, but the SQL can return only results of grouping. The scene that you have to handle so big data, pandas can't grouping because the memory has the limit(in normaly).

And almost data that company has is organized for SQL, so SQL is needed many place. There's no harm in learning.

2. SELECT

SELECT is basic method to get the data from table.
Below code gets all columns(*) by table(Customers).

SELECT *

Get all columns

SELECT * FROM Customers;

# Output
| CustomerID | CustomerName                        | ContactName         | Address                  | City        | PostalCode | Country |
|------------|-------------------------------------|---------------------|--------------------------|-------------|------------|---------|
| 1          | Alfreds Futterkiste                 | Maria Anders        | Obere Str. 57            | Berlin      | 12209      | Germany |
| 2          | Ana Trujillo Emparedados y helados  | Ana Trujillo        | Avda. de la Constitución 2222 | México D.F.  | 05021      | Mexico  |
| 3          | Antonio Moreno Taquería             | Antonio Moreno      | Mataderos 2312           | México D.F.  | 05023      | Mexico  |
| 4          | Around the Horn                     | Thomas Hardy        | 120 Hanover Sq.          | London      | WA1 1DP    | UK      |
| 5          | Berglunds snabbköp                  | Christina Berglund  | Berguvsvägen 8           | Luleå       | S-958 22   | Sweden  |
...

SELECT columns

Get specified columns

SELECT CustomerName, City FROM Customers;

| CustomerName                        | City       |
|-------------------------------------|------------|
| Alfreds Futterkiste                 | Berlin     |
| Ana Trujillo Emparedados y helados  | México D.F.|
| Antonio Moreno Taquería             | México D.F.|
| Around the Horn                     | London     |
| Berglunds snabbköp                  | Luleå      |
...

SELECT DISTINCT colmuns

Get unique value from specified columns(each value shows only once)

SELECT DISTINCT Country FROM Customers;

| Country  |
|----------|
| Germany  |
| Mexico   |
| Mexico   |
| UK       |
| Sweden   |
...

・ SELECT COUNT(DISTINCT column)
Get number of unique value in column.

SELECT COUNT(DISTINCT Country) FROM Customers;

| COUNT(DISTINCT Country) |
|-------------------------|
| 21                      |

3. WHERE

WHERE is used to filter records.

3.1 WHERE column='value'

Get rows that only has value 'Mexico' at Country column.

SELECT * FROM Customers
WHERE Country='Mexico';

| CustomerID | CustomerName                        | ContactName         | Address                       | City        | PostalCode | Country |
|------------|-------------------------------------|---------------------|-------------------------------|-------------|------------|---------|
| 2          | Ana Trujillo Emparedados y helados  | Ana Trujillo        | Avda. de la Constitución 2222 | México D.F. | 05021      | Mexico  |
| 3          | Antonio Moreno Taquería             | Antonio Moreno      | Mataderos 2312                | México D.F. | 05023      | Mexico  |
| 13         | Centro comercial Moctezuma          | Francisco Chang     | Sierras de Granada 9993       | México D.F. | 05022      | Mexico  |
| 58         | Pericles Comidas clásicas           | Guillermo Fernández | Calle Dr. Jorge Cash 321      | México D.F. | 05033      | Mexico  |
| 80         | Tortuga Restaurante                 | Miguel Angel Paolino| Avda. Azteca 123              | México D.F. | 05033      | Mexico  |

WHERE is also used in UPDATE, DELETE, etc.!

3.2 Other operator

WHERE can use other operators than the =.

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

・Example

SELECT * FROM Products
WHERE Price BETWEEN 50 AND 60;

| ProductID | ProductName            | SupplierID | CategoryID | Unit              | Price |
|-----------|------------------------|------------|------------|-------------------|-------|
| 51        | Manjimup Dried Apples  | 24         | 7          | 50 - 300 g pkgs.  | 53    |
| 59        | Raclette Courdavault   | 28         | 4          | 5 kg pkg.         | 55    |

SELECT * FROM Customers
WHERE City IN ('Paris','London');

| CustomerID | CustomerName         | ContactName        | Address                     | City    | PostalCode | Country |
|------------|----------------------|--------------------|-----------------------------|---------|------------|---------|
| 4          | Around the Horn      | Thomas Hardy       | 120 Hanover Sq.             | London  | WA1 1DP    | UK      |
| 11         | B's Beverages        | Victoria Ashworth  | Fauntleroy Circus           | London  | EC2 5NT    | UK      |
| 16         | Consolidated Holdings| Elizabeth Brown    | Berkeley Gardens 12 Brewery | London  | WX1 6LT    | UK      |
| 19         | Eastern Connection   | Ann Devon          | 35 King George              | London  | WX3 6FW    | UK      |
| 53         | North/South          | Simon Crowther     | South House 300 Queensbridge| London  | SW7 1RZ    | UK      |
| 57         | Paris spécialités    | Marie Bertrand     | 265, boulevard Charonne     | Paris   | 75012      | France  |
| 72         | Seven Seas Imports   | Hari Kumar         | 90 Wadhurst Rd.             | London  | OX15 4NB   | UK      |
| 74         | Spécialités du monde | Dominique Perrier  | 25, rue Lauriston           | Paris   | 75016      | France  |

4. ORDER BY

ORDER BY sorts by refer to specified column's value.

4.1 ORDER BY column

Sort by column's value. Normaly order is ascending(smaller is first)
* If column value is string, ordered by alphabetically.

SELECT * FROM Products
ORDER BY Price;

| ProductID | ProductName          | SupplierID | CategoryID | Unit               | Price |
|-----------|----------------------|------------|------------|--------------------|-------|
| 33        | Geitost              | 15         | 4          | 500 g              | 2.5   |
| 24        | Guaraná Fantástica   | 10         | 1          | 12 - 355 ml cans   | 4.5   |
| 13        | Konbu                | 6          | 8          | 2 kg box           | 6     |
| 52        | Filo Mix             | 24         | 5          | 16 - 2 kg boxes    | 7     |
...
| 38        | Côte de Blaye        | 18         | 1          | 12 - 75 cl bottles | 263.5 |

4.2 ORDER BY column DESC

Sort with descending order.

SELECT * FROM Products
ORDER BY Price DESC;

| ProductID | ProductName            | SupplierID | CategoryID | Unit                 | Price |
|-----------|------------------------|------------|------------|----------------------|-------|
| 38        | Côte de Blaye          | 18         | 1          | 12 - 75 cl bottles   | 263.5 |
| 29        | Thüringer Rostbratwurst| 12         | 6          | 50 bags x 30 sausgs. | 123.79|
| 9         | Mishi Kobe Niku        | 4          | 6          | 18 - 500 g pkgs.     | 97    |
| 20        | Sir Rodney's Marmalade | 8          | 3          | 30 gift boxes        | 81    |
...
| 33        | Geitost                | 15         | 4          | 500 g                | 2.5   |

4.3 ORDER BY columns

If moultiple columns are specified, sorted by first column, and if exist same value at first column, then sorted by second column's order.

SELECT * FROM Customers
ORDER BY Country, CustomerName;

| CustomerID | CustomerName              | ContactName     | Address                               | City         | PostalCode | Country   |
|------------|---------------------------|-----------------|---------------------------------------|--------------|------------|-----------|
| 12         | Cactus Comidas para llevar| Patricio Simpson| Cerrito 333                           | Buenos Aires | 1010       | Argentina |
| 54         | Océano Atlántico Ltda.    | Yvonne Moncada  | Ing. Gustavo Moncada 8585 Piso 20-A   | Buenos Aires | 1010       | Argentina |
| 64         | Rancho grande             | Sergio Gutiérrez| Av. del Libertador 900                | Buenos Aires | 1010       | Argentina |
| 20         | Ernst Handel              | Roland Mendel   | Kirchgasse 6                          | Graz         | 8010       | Austria   |
| 59         | Piccolo und mehr          | Georg Pipps     | Geislweg 14                           | Salzburg     | 5020       | Austria   |
...

4.4 ORDER BY column ASC, column DESC

Sorted first column with ascending, second colmun with descending.

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

| CustomerID | CustomerName              | ContactName     | Address                               | City         | PostalCode | Country   |
|------------|---------------------------|-----------------|---------------------------------------|--------------|------------|-----------|
| 64         | Rancho grande             | Sergio Gutiérrez| Av. del Libertador 900                | Buenos Aires | 1010       | Argentina |
| 54         | Océano Atlántico Ltda.    | Yvonne Moncada  | Ing. Gustavo Moncada 8585 Piso 20-A   | Buenos Aires | 1010       | Argentina |
| 12         | Cactus Comidas para llevar| Patricio Simpson| Cerrito 333                           | Buenos Aires | 1010       | Argentina |
| 59         | Piccolo und mehr          | Georg Pipps     | Geislweg 14                           | Salzburg     | 5020       | Austria   |
| 20         | Ernst Handel              | Roland Mendel   | Kirchgasse 6                          | Graz         | 8010       | Austria   |
...

5. AND, OR

WHERE clause can contain one or more AND operators.

5.1 WHERE condition AND condition

Get data that meets both conditions. LIKE is pattern match and '%' is wild card.

SELECT *
FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';

| CustomerID | CustomerName           | ContactName     | Address                | City      | PostalCode | Country |
|------------|------------------------|-----------------|------------------------|-----------|------------|---------|
| 29         | Galería del gastrónomo | Eduardo Saavedra| Rambla de Cataluña, 23 | Barcelona | 08022      | Spain   |
| 30         | Godos Cocina Típica    | José Pedro Freyre| C/ Romero, 33          | Sevilla   | 41101      | Spain   |

5.2 WHERE condition OR condifiton

Get data that meets either condition.

SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';

| CustomerID | CustomerName                | ContactName   | Address            | City    | PostalCode | Country |
|------------|-----------------------------|---------------|--------------------|---------|------------|---------|
| 1          | Alfreds Futterkiste         | Maria Anders  | Obere Str. 57      | Berlin  | 12209      | Germany |
| 6          | Blauer See Delikatessen     | Hanna Moos    | Forsterstr. 57     | Mannheim| 68306      | Germany |
| 8          | Bólido Comidas preparadas   | Martín Sommer | C/ Araquil, 67     | Madrid  | 28023      | Spain   |
| 17         | Drachenblut Delikatessend   | Sven Ottlieb  | Walserweg 21       | Aachen  | 52066      | Germany |
| 22         | FISSA Fabrica Inter. Salchichas S.A.| Diego Roel    | C/ Moralzarzal, 86  | Madrid  | 28034      | Spain   |
...

5.3 WHERE condition AND condition OR condition

There are some rule of AND, OR in SQL.

1. A takes precedence over B: Conditions combined with AND are evaluated before OR.
2. Left to Right: Within the same precedence level, conditions are evaluated from left to right.
3. Use Parentheses: Parentheses has high precedence than rule 1,2.

・Example

A OR (B OR C) AND D
# example one value=X.
# Against X, condition A,B = true, C,D = false

・Sequence

B OR C → true OR false → true
(true) AND D → true AND false → false
A OR false → true OR false → true

Then, X is contained to result gotten.
This is the rule of AND,OR in SQL.

・Concrete example
Select all customers that either:
are from Spain and starts with either "G", or
starts with the letter "R":

SELECT * FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%' OR CustomerName LIKE 'R%';

6. NOT

Invert the condition after.

6.1 WHERE NOT condition

SELECT * FROM Customers
WHERE NOT Country = 'Spain';

| CustomerID | CustomerName                        | ContactName         | Address                       | City        | PostalCode | Country |
|------------|-------------------------------------|---------------------|-------------------------------|-------------|------------|---------|
| 1          | Alfreds Futterkiste                 | Maria Anders        | Obere Str. 57                 | Berlin      | 12209      | Germany |
| 2          | Ana Trujillo Emparedados y helados  | Ana Trujillo        | Avda. de la Constitución 2222 | México D.F. | 05021      | Mexico  |
| 3          | Antonio Moreno Taquería             | Antonio Moreno      | Mataderos 2312                | México D.F. | 05023      | Mexico  |
| 4          | Around the Horn                     | Thomas Hardy        | 120 Hanover Sq.               | London      | WA1 1DP    | UK      |
| 5          | Berglunds snabbköp                  | Christina Berglund  | Berguvsvägen 8                | Luleå       | S-958 22   | Sweden  |
...

・Sequence

NOT A AND B AND C OR D AND E

same as below.

((NOT A) AND B AND C) OR (D AND E)

First, NOT A → NOT true → false. After this, same as previous.

NOT can use to vairous operator.

SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'A%';

SELECT * FROM Customers
WHERE CustomerID NOT BETWEEN 10 AND 60;

SELECT * FROM Customers
WHERE City NOT IN ('Paris', 'London');

Summary

This time, I summarized w3schools SQL tutorial in my own way. This site has so many tutorial about language and package, I reccomend you to visit once.

The most basically clause is over, but the rest of method of SQL is also important. I'd like to write continue to this when have a time.

Reference

[1] w3schools