TheLazy.Dev
Portfolio GitHub

Blog

sql |term |database |crud

SQL: How to consult the database.

danilsa0109

Danilsa Caraballo

Posted on October 27, 2021

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);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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|
Enter fullscreen mode Exit fullscreen mode
  • To query a column in special:

~ SELECT departament FROM colombia ;

| departament    |
| -------------- |
| Antioquia      |
| Bolívar        |
| Valle del cauca|
Enter fullscreen mode Exit fullscreen mode
  • To query a several columns:

~ SELECT departament, departament_capital FROM colombia ;

| departament    | departament_capital  |
| -------------- |----------------------|
| Antioquia      | Medellin             |
| Bolívar        | Cartagena            |
| Valle del cauca| Cali                 |
Enter fullscreen mode Exit fullscreen mode
  • 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      |
Enter fullscreen mode Exit fullscreen mode

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

| departament    | departament_capital  |
| -------------- |----------------------|
| Antioquia      | Medellin             |
Enter fullscreen mode Exit fullscreen mode

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

| departament    | departament_capital  |
| -------------- |----------------------|
| Bolívar        | Cartagena            |
| Valle del cauca| Cali                 |
Enter fullscreen mode Exit fullscreen mode

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

| departament    | geographic_extent_km2 |
| -------------- |-----------------------|
| Antioquia      |                  63612|
| Valle del cauca|                  22195|
Enter fullscreen mode Exit fullscreen mode

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

| departament    | departament_capital  |
| -------------- |----------------------|
| Bolívar        | Cartagena            |
| Valle del cauca| Cali                 |
Enter fullscreen mode Exit fullscreen mode

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

| departament    | departament_capital  |
| -------------- |----------------------|
| Antioquia      | Medellin             |
Enter fullscreen mode Exit fullscreen mode
💖 💪 🙅 🚩
danilsa0109
Danilsa Caraballo

Posted on October 27, 2021

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

SQL: How to consult the database.
sql SQL: How to consult the database.

October 27, 2021