Learning The Structure Query Language (SQL)
Tags: database, sql
Published: 2021-11-04 13:16:33

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t8yik074da1pdxaizxeu.png)
As you may already know, most applications need a database. The language implemented by most databases is the Structured Query Language (SQL). Although there are many other databases as "MongoDB", and "Cassandra" which don't require the SQL. Even that mentioned databases do not require SQL, databases like  "mySQL," "MariaDB," "SQLite," "PostgreSQL," etc. need SQL.  This tutorial is not going to dive v very deep into these databases. We are going to  learn the language for these databases. Once we learn the language, to learn to manage the mentioned databases would be very easy. Let us start learning the Structured query language (SQL).

### Setting a Work Environment ###

 In order to start working with  the SQL language we first need to set up a work environment. There are many work environments to work with databases. Environments like "<a href="https://www.docker.com/">Docker</a>,"<a href="https://www.apachefriends.org/index.html"> "Xampp</a>," "<a href="https://www.microsoft.com/en-us/sql-server/sql-server-downloads">SQL Server</a>," and many others, are ready for your database work.  In this tutorial we are going to use the <a href="https://www.microsoft.com/en-us/sql-server/sql-server-downloads">Online Docker Playground</a>. Although it's a good practice to set up a local work environment.

### Installing Necessary Files ###
 As the Online Docker Playground is similar to locally used Docker. We need to first initialize the SQL environment. In this case we are using MariaDB to set our commands. Please note that this tutorial uses MariaDB; but it's not a MariaDB tutorial. This tutorial is about the SQL language. MariaDB is used only for showing purposes. We provide you some examples below that shows you how to initialize MariaDB in Docker or Docker playground. In case you use another work environment, you need to read their documentation on how to initialize MySQL or MariaDB.

We first need to install Get the necessary files for MariaDB using the following command. 

``` shell
docker pull mariadb
```

### Starting the Installed Files ###

Once you  pull MariaDB you are going to see a list of files loading to your computer or your playground. After the list completely loads, you continue by typing the following command.

```shell
docker run --name mycontainer -e MARIADB_ROOT_PASSWORD=password -d mariadb
```
The above command creates a container. You can call you container as you want; in this case we called it mycontainer. After the name of the container you can see that we created an environment using the -e keyword. The environment is MARIADB_ROOT_PASSWORD and the password is the word password. in case we want to know more environments for MariaDB, we need to see MariaDB documentation.  Then we have the -d keyword to specify our database.  Once we type the command we are going to get a encoded string. that means that everything is fine.

```shell
docker run --name mycontainer -e MARIADB_ROOT_PASSWORD=password -d mariadb

85375561b1e295c002db675cf849247f823a9ba229ead8c07b6a6b63a7d532c4

```
### Initializing SQL ###

After your environment has been created, you are ready to start creating your databases. You can start MariaDB  by typing  the exec -it  command and specifying the database; in this case we use mariadb. You can also use mysql. The example below shows how to initialize MariaDB.   The -it keyword is for iteration. The -u is for the user; in this case our user is root. The -p keyword is for password; The example above set the password to password. You can use any word you want.

```shell
$ docker exec -it mycontainer  mariadb -u root -p

Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.4-MariaDB-1:10.6.4+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 


```

### First SQL steps ###

The steps above were only to initialize MariaDB in the Docker environment. Now it's time to dive  into SQL by creating a database. Before creating a database let's follow one of the instructions we got when we started MariaDB. if you type \h or help you are going to see a list of useful commands for most SQL environments. The example below shows you some of these useful commands.

```shell
MariaDB [(none)]> \h

General information about MariaDB can be found at
http://mariadb.org

List of all client commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to MariaDB server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to MariaDB server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

MariaDB [(none)]> 

```

### Creating a Database ###

The example above provide you with all the client commands in case you need any one of them. Now we are  going to start creating our database using SQL. The sample database will be a customers database. By creating and working with this sample database we are going to learn about SQL.  We first start with the command CREATE DATABASE CUSTOMERS. It does not matter if we use lower case or upper case, SQL is not case sensitive. Most times we use upper case because it's easier to identify commands in  upper case. 

```sql
MariaDB [(none)]> CREATE DATABASE CUSTOMERS;
Query OK, 1 row affected (0.000 sec)

```

### Confirm Database Creation ###

The example above shows you how simple is to create a database in SQL. Just be aware that you need to end statements with semicolons. In case you miss it, the statement is going to remain open. You could see that once you created the database you get a confirmation of Query OK. That confirms that database was created. In case you really want to make sure you database is created, you may type the command SHOW DATABASES. that command is going to show you a list of the existent databases; including the one you just created. You can see below how the mentioned command works.

```sql
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| CUSTOMERS          |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.001 sec)

MariaDB [(none)]> 

```

### Using a Database ###

Once you confirmed that your database is created, you can use it. Using a database after its creation is a quite  easy task. In order to start using the customers database you just created, you only need to type the command USE CUSTOMERS. the USE command is followed by the database name as you can see in the example below.

```sql
MariaDB [(none)]> USE CUSTOMERS;

Database changed

MariaDB [CUSTOMERS]> 

```

### Creating Database Tables ###

In the previous example you could see that now you started working on the database. That is a recently created database; as result database is empty. Now it's time to create a table  in our database. In SQL Databases, we keep data in tables. In order to create a table in our database, we only need to type the  command CREATE TABLE followed by the data names and types we want  in a parentheses. The example below shows you how to create a table in the customers database.

``` sql

MariaDB [CUSTOMERS]> CREATE TABLE usual_customers(
customer_name VARCHAR(100),
customer_city VARCHAR(20), 
customer_gender VARCHAR(10),
customer_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);

Query OK, 0 rows affected (0.008 sec)

```

### SQL Data Types ###

In the example above you could see that we created a table in the previously created customers database. You could also see that fields for the table are followed by another keyword. That is the data type of the field.  Below we show you a list with the description for the most used SQL Data types. 

<table><tbody><tr><td>CHAR</td><td>One Single Character; can contain  a letter, a number, or a special character</td></tr><tr><td>VARCHAR</td><td>A fixed set of Characters.</td></tr><tr><td>TEXT</td><td>It's a String with Maximum size of 65,535 Bytes</td></tr><tr><td>INT </td><td>Integer signed or unsigned</td></tr><tr><td>FLOAT</td><td>Floating point  number</td></tr><tr><td>DOUBLE</td><td>Similar to floating number, values can be higher</td></tr><tr><td>BOOL</td><td>Boolean. True or False</td></tr><tr><td>DATE</td><td>Date in a format YYYY-MM-DD</td></tr><tr><td>DATETIME</td><td>Date and Time Combined  YYYY-MM-DD hh : mm : ss</td></tr><tr><td>TIME</td><td>Time  hh : mm : ss</td></tr></tbody></table>

The list above shows you some of the most used SQL Data types. There are even more; Here you have a reference of  <a href="https://www.w3schools.com/sql/sql_datatypes.asp">SQL Data Types</a>. You could also see some other values as PRIMARY KEY  and AUTO_INCREMENT. The PRIMARY KEY value is used as an unique value  that identifies some data in a row. The AUTO_INCREMENT is self explanatory; it increases the value of the previous number or letter.

### Seeing Your Table ###

When you created your table you could see that your query was OK.  We can use the SHOW command to see our table and confirm its creation. The example below  shows how you can see your table using the SHOW TABLES command.

```sql
MariaDB [CUSTOMERS]> SHOW TABLES ;
+---------------------+
| Tables_in_CUSTOMERS |
+---------------------+
| usual_customers     |
+---------------------+
1 row in set (0.001 sec)

```

### Describing our Table ###

The example above shows that we can see the tables in a list of tables. We also have the ability to see a detailed description of the table using the DESCRIBE command. In this case we can type  DESCRIBE usual_customers. The result will be similar to the one we show below.

```sql
MariaDB [customers]> DESCRIBE usual_customers;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| customer_name   | varchar(100) | YES  |     | NULL    |                |
| customer_city   | varchar(20)  | YES  |     | NULL    |                |
| customer_gender | varchar(10)  | YES  |     | NULL    |                |
| customer_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
+-----------------+--------------+------+-----+---------+----------------+
4 rows in set (0.006 sec)


```

### Inserting Data into Tables ###

Once we create a database and a table, it's time to put some data in our database. We only need the INSERT INTO operation in order to insert data in our database. As you may already know, we insert data into tables.  Now Let us insert some data in our customers database, as you can see in the following example.

```sql
MariaDB [CUSTOMERS]> INSERT INTO usual_customers(customer_name, customer_city, customer_gender, customer_id)
    VALUES ('John Doe', 'Washington', 'Male', 1);
Query OK, 1 row affected (0.002 sec)


```
You could see that we could insert a row into our table. Now let us insert some more rows.

```sql
MariaDB [CUSTOMERS]> INSERT INTO usual_customers(customer_name, customer_city, customer_gender, customer_id) 
  VALUES('Sarah Parker', 'New York', 'Female', 2),
     ('Josh Perez', 'Chicago', 'Male', 3),
    ('William Johnson' , 'Los Angeles', 'Male', 4),
    ('Peter Parker' , 'San Francisco', 'Male', 5);
Query OK, 4 rows affected (0.004 sec)
Records: 4  Duplicates: 0  Warnings: 0
```

### Visualize Data ###

You could see in the previous example that we could insert data into our table. The only result you could see was Query Ok, 1 row affected once we added one row to our database.  When we added 4 you could see the same result with a number changed. You could not see the data you entered. In order to see the data entered we need to use the SELECT command.  To see all the data we can use the SELECT *  FROM  table_name. In this case the table name is usual_customers. The  following example shows you how to visualize data in our tables.

```sql
MariaDB [CUSTOMERS]> SELECT * FROM usual_customers;
+-----------------+---------------+-----------------+-------------+
| customer_name   | customer_city | customer_gender | customer_id |
+-----------------+---------------+-----------------+-------------+
| John Doe        | Washington    | Male            |           1 |
| Sarah Parker    | New York      | Female          |           2 |
| Josh Perez      | Chicago       | Male            |           3 |
| William Johnson | Los Angeles   | Male            |           4 |
| Peter Parker    | San Francisco | Male            |           5 |
+-----------------+---------------+-----------------+-------------+
5 rows in set (0.001 sec)

```

### Auto Increment Values ###

As you can see we created a customers database with a usual_customers table.  The last column of our table is the customer_id. As you could see that is the PRIMARY KEY for customers in the table. These values  an AUTO_INCREMENT values. When we inserted data into our tables we also inserted the customer_id values. We can also insert data without that value, and the value will be  placed automatically. You can se below how  we can do it.

``` sql
MariaDB [CUSTOMERS]> INSERT INTO usual_customers(customer_name, customer_city, customer_gender) VALUES ('Tiffany Clark', 'Richmond','Female');
Query OK, 1 row affected (0.002 sec)

MariaDB [CUSTOMERS]> SELECT * FROM  usual_customers;
+-----------------+---------------+-----------------+-------------+
| customer_name   | customer_city | customer_gender | customer_id |
+-----------------+---------------+-----------------+-------------+
| John Doe        | Washington    | Male            |           1 |
| Sarah Parker    | New York      | Female          |           2 |
| Josh Perez      | Chicago       | Male            |           3 |
| William Johnson | Los Angeles   | Male            |           4 |
| Peter Parker    | San Francisco | Male            |           5 |
| Tiffany Clark   | Richmond      | Female          |           6 |
+-----------------+---------------+-----------------+-------------+
6 rows in set (0.000 sec)

```
### Selecting Only Certain Data ###

The previous examples shows you how to  select all the  data in a table. You have the ability to select  only certain rows where data have similar values. In case  data does not have similar values in a table, we can select only one. In order to select only a part of the data we use the WHERE keyword. Then we specify our data. We can use the statement  SELECT customer_name FROM usual_customers WHERE customer_gender = 'Female'. Then we can see the result.

```sql
MariaDB [customers]> SELECT customer_name FROM usual_customers WHERE customer_gender = 'Female';
+---------------+
| customer_name |
+---------------+
| Sarah Parker  |
| Tiffany Clark |
+---------------+
2 rows in set (0.001 sec)

```

### Using the AS keyword ###

We could see above that we can select only part of the database. Also, we can classify the part of the database  we chose  using the AS keyword. Now we are going to use the same example that we used above, and  instead of customer_name, we are going to use the word women.

```sql
MariaDB [customers]> SELECT customer_name AS women FROM usual_customers WHERE customer_gender = 'Female';
+---------------+
| women         |
+---------------+
| Sarah Parker  |
| Tiffany Clark |
+---------------+
2 rows in set (0.001 sec)

```

### Updating Data ###

In some cases like misspelling or entering wrong data, we have the ability to modify it. In SQL that is quite easy to modify data. We only need to use the UPDATE   and SET keywords. We use the UPDATE for the old value, and the SET for the new value. The example below shows you how to modify data using the UPDATE SET keywords.

```sql
MariaDB [customers]> UPDATE usual_customers SET customer_name = 'Josh Lopez' WHERE customer_id = 3;
Query OK, 1 row affected (0.014 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```
Now we can see the result  by only typing SELECT * FROM usual_customers.

``` sql
MariaDB [customers]> SELECT * FROM usual_customers;
+-----------------+---------------+-----------------+-------------+
| customer_name   | customer_city | customer_gender | customer_id |
+-----------------+---------------+-----------------+-------------+
| John Doe        | Washington    | Male            |           1 |
| Sarah Parker    | New York      | Female          |           2 |
| Josh Lopez      | Chicago       | Male            |           3 |
| William Johnson | Los Angeles   | Male            |           4 |
| Peter Parker    | San Francisco | Male            |           5 |
| Tiffany Clark   | Richmond      | Female          |           6 |
+-----------------+---------------+-----------------+-------------+
6 rows in set (0.001 sec)


```
### Deleting Data ###

In case we entered some unnecessary data in our table, we can delete it. It's so simple to delete any row in a SQL table. We only need to use the DELETE FROM keywords followed by the statement we need to delete. In this case we are going to delete using the  ID. The example below shows you how we delete  one row of  our customers database.

```sql
MariaDB [customers]> DELETE FROM usual_customers  WHERE customer_id = 4;
Query OK, 1 row affected (0.004 sec)
```

Using the SELECT * FROM usual_customers command we can see that one row was deleted. 

```sql
MariaDB [customers]> SELECT * FROM usual_customers;
+---------------+---------------+-----------------+-------------+
| customer_name | customer_city | customer_gender | customer_id |
+---------------+---------------+-----------------+-------------+
| John Doe      | Washington    | Male            |           1 |
| Sarah Parker  | New York      | Female          |           2 |
| Josh Lopez    | Chicago       | Male            |           3 |
| Peter Parker  | San Francisco | Male            |           5 |
| Tiffany Clark | Richmond      | Female          |           6 |
+---------------+---------------+-----------------+-------
```

### Some Notes ###

Although this tutorial used most MariaDB to show examples, it's about the SQL Language. By now we have seen most of the operations of SQL.  In this tutorial we only created a database and a table. You can create as many tables as you need in any database.  You should place your semicolon(;) at the end of each statement.  In case you do not place the statement is going to remain open. We also need  to be aware of commas and quotation marks. Also be advised that we typed our commands and keywords using upper case; it does not matter wether they are upper or lower case. Below we have a list of the documentation of many technologies that implement  the SQL language to create Databases.

<ul><li><a href="https://mariadb.org/documentation/" target="_blank" rel="noreferrer noopener">MariaDB</a></li><li><a href="https://dev.mysql.com/doc/" target="_blank" rel="noreferrer noopener">MySQL</a></li><li><a href="https://www.postgresql.org/docs/" target="_blank" rel="noreferrer noopener">PostgreSQL</a></li><li><a href="https://www.sqlite.org/docs.html" target="_blank" rel="noreferrer noopener">SQLite</a></li></ul>

### Conclusion ###

We could see that one language works very well to manage databases. Even we still have a lot to learn regarding databases, this is a great foundation for database development and maintenance.  We use databases for our applications in<a href="https://moeslink.com/the-key-to-learn-all-programming-languages/"> all programming languages</a>. We can choose what technology we used based on SQL. Thanks for reading our tutorial. Hopefully you got great knowledge in this tutorial.

Find More at <a href="https://moeslink.com">Moe's Link</a>