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