DEV Community: Danilsa Caraballo

SQL: How to consult the database.

Danilsa Caraballo — Wed, 27 Oct 2021 15:25:13 +0000

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             |

DELETE on SQL

Danilsa Caraballo — Wed, 27 Oct 2021 15:19:20 +0000

When a row is deleted from the database, using in the restriction the id attribute, whose value is unique. However, all also it’s possible using in the restriction others attributes or columns with specific value, eg:

~ DELETE FROM friends WHERE name = 'Danil' OR name = 'Gracia';

SQLite: A tool that allows creating databases from the terminal

Danilsa Caraballo — Fri, 06 Aug 2021 14:43:05 +0000

I had learned how to create a table for a new database using tools like Excel, Matlab, My sql and table plus.

Now, with SQLite I can create the databases in a very practical way, it is similar to Mysql, so today I a practical example, comparing Mysql with SQLite,

We must be in the folder where we will create the database:

To SQLite

in the case of Mysql, you must find the location of the program to be able to execute it

brew search mysql
//or
./mysql -u root -p

Open the tool, giving name to database:

To SQLite

~ sqlite3 mydatabase

in the case of Mysql

~ create database mydatabase

Create new table, taking into account the type of the column:

SQLite and Mysql are the same

~ use mydatabase
~ create table dataUsers(
          Id integer primary key,
          Name text,
          Last_name text,
          age integer,
          Cell integer 
)

insert the data:

SQLite andMysql are the same

insert into dataUsers(0001,'Ema','Garces', 21, 0573033333055 );

//or

insert into dataUsers( Name text,Last_name text, age real, Cell integer ) VALUES (0001,'Ema','Garces', 21, 0573033333055);

Show table:

To SQLite

~ .mode {mode of choice}
~ select * from dataUsers;

there are several view modes to display the table:
ascii, box, csv, column, html, insert, line, list, tabs, tcl, etc.

in the case of Mysql

~ show tables;
~ describe dataUsers;
~ select * from dataUsers;

Bonus: Connect to database with table plus

6.1 open table plus:

6.2 create new connection:

6.3 name the connection and find the location of the database: