Learning The Structure 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 "Docker," "Xampp," "SQL Server," and many others, are ready for your database work. In this tutorial we are going to use the Online Docker Playground. 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.

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.

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.

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.

$ 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.

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.

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.

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.

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.

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.

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

The list above shows you some of the most used SQL Data types. There are even more; Here you have a reference of SQL Data Types. 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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 all programming languages. 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 Moe's Link