SQL: How to consult the database.
Tags: sql, term, database, crud
Published: 2021-10-27 15:25:13

We will  first remember the basic structure  of one [SQL table](https://dev.to/danilsa0109/sqlite-a-tool-that-allows-creating-databases-from-the-terminal-78j), to show you some ways to call the information of the table .

I will start by summarizing the basics conventions that make up the declarations that we will be using  throughout this post.

| Convention        | Used for          | Example |
| ----------------- |:-----------------:| -------:|
|CAPITAL LETTER|keyword and data types|SELECT, FROM, WHERE, OR, AND.TEXT, INTEGER, REAL. |
| lower case| Parameters given by the user|nametable, name_column, etc. |
|  * |Select the whole table|All columns separated by rows:`[{ name_column_N : ’row content’ ,name_column_N+1 : ’row content’} , { name_column_N : ’row content’ , name_column_N+1 : ’row content’ }]`|
| a <> b      | Mathematical conditional called function      |   a different to b |
| a > b | Mathematical conditional called function      |    a greater than b |
| a < b      | Mathematical conditional called function      |   a less than b |
| n = c | Mathematical conditional called function      |    n same as c |
| n != c      | Mathematical conditional called function      |   n different to c |


**Example database:**

The table will be called “colombia” , this columns is: “departamento” , “capital_departamento” and  “extencion_geografica_km2”. the columns is type text.
```
~ CREATE TABLE colombia (departament TEXT, departament_capital TEXT, geographic_extent_km2 INTEGRAL);

```
To the previous table, we enter department with their capital cities:
```
~ INSERT INTO colombia (departament, departament_capital, geographic_extent_km2) VALUES ('Antioquia','Medellín',63612 );

~ INSERT INTO colombia (departament, departament_capital, geographic_extent_km2) VALUES ('Bolívar','Cartagena', 25978 );

~ INSERT INTO colombia (departament, departament_capital, geographic_extent_km2) VALUES ('Valle del cauca','Cali', 22195);

```
Starting from the previous table, we make to query of diverse forms:

note: for the view, before selecting, enter the command
`~ .mode table;`

* To query the whole table:

`~ SELECT * FROM colombia ;`
```
| departament    | departament_capital  | geographic_extent_km2 |
| -------------- |----------------------|-----------------------|
| Antioquia      | Medellin             |                  63612|
| Bolívar        | Cartagena            |                  25978|
| Valle del cauca| Cali                 |                  22195|

```

* To query a column in special:

`~ SELECT departament FROM colombia ;`
```
| departament    |
| -------------- |
| Antioquia      |
| Bolívar        |
| Valle del cauca|
```

* To query a several columns:

`~ SELECT departament, departament_capital FROM colombia ;`

```
| departament    | departament_capital  |
| -------------- |----------------------|
| Antioquia      | Medellin             |
| Bolívar        | Cartagena            |
| Valle del cauca| Cali                 |

```
* To query a column or several columns, where the row has a special conditional:

`~ SELECT departament FROM colombia WHERE departament_capital = 'Medellín' ;`

```
| departament    |
| -------------- |
| Antioquia      |
```

`~ SELECT departament, departament_capital FROM colombia WHERE geographic_extent_km2 = 63612 ;`

```
| departament    | departament_capital  |
| -------------- |----------------------|
| Antioquia      | Medellin             |

```

`~ SELECT departament, departament_capital FROM colombia WHERE departament_capital <> 'Medellín';`

```
| departament    | departament_capital  |
| -------------- |----------------------|
| Bolívar        | Cartagena            |
| Valle del cauca| Cali                 |

```

`~ SELECT departament, geographic_extent_km2 FROM colombia WHERE departament_capital != 'Cartagena';`

```
| departament    | geographic_extent_km2 |
| -------------- |-----------------------|
| Antioquia      |                  63612|
| Valle del cauca|                  22195|

```

`~ SELECT departament, departament_capital FROM colombia WHERE geographic_extent_km2 < 26000;`

```
| departament    | departament_capital  |
| -------------- |----------------------|
| Bolívar        | Cartagena            |
| Valle del cauca| Cali                 |

```

`~ SELECT departament, departament_capital FROM colombia WHERE geographic_extent_km2 > 26000;`

```
| departament    | departament_capital  |
| -------------- |----------------------|
| Antioquia      | Medellin             |

```