SQL: How to consult the database.

We will first remember the basic structure of one SQL table, 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             |