Hello, I  am Tane J. Tangu, a forever beginner (learning never ends) and software developer (freelancer as of now).  I love writing beginner articles, and this article is the start of a tutorial series on SQL for Beginners. If you are an SQL beginner or have little or no knowledge of SQL I believe this series is for you.

This article will be pretty straight to the point, just in case you missed it this is a continuation of an earlier article I wrote introducing SQL. You should probably check it out here 

So we will be looking at SQL Queries, essentially how you retrieve information from a database.

Simple Queries

Selecting all records from a table. Remember our table student_basic_info
let us query all the students from the table with all their information.

mysql> SELECT * FROM student_basic_info;
+------------+-----------+---------------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+
| first_name | last_name | email               | id | password     | gender | about | created_at          | banned | deleted | last_updated        |
+------------+-----------+---------------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+
| nji        | Fidelis   | njifidelis@test.com |  2 | testpassword | NULL   | NULL  | 2019-06-01 21:38:14 |      0 |       0 | 2019-06-01 21:39:07 |
| Patrick    | Njassap   | patrick@test.com    |  3 | testpassword | NULL   | NULL  | 2019-06-01 21:38:14 |      0 |       0 | 2019-06-01 21:39:07 |
| Fred       | Zurich    | fred@test.com       |  4 | testpassword | NULL   | NULL  | 2019-06-01 21:38:14 |      0 |       0 | 2019-06-01 21:39:07 |
| Chris      | Ateba     | chris@test.com      |  5 | testpassword | NULL   | NULL  | 2019-06-01 21:38:14 |      0 |       0 | 2019-06-01 21:39:07 |
| Atangana   | Christian | email@test.com      |  6 | testpassword | NULL   | NULL  | 2019-06-01 21:38:14 |      0 |       0 | 2019-06-01 21:39:07 |
+------------+-----------+---------------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+
5 rows in set (0.00 sec)

 

So we may want to retrieve some particular information about the students from the table, rather than querying all of the info like above. Let's retrieve/query the first name, last name and email of the students.

mysql> SELECT first_name, last_name, email  FROM student_basic_info;
+------------+-----------+---------------------+
| first_name | last_name | email               |
+------------+-----------+---------------------+
| nji        | Fidelis   | njifidelis@test.com |
| Patrick    | Njassap   | patrick@test.com    |
| Fred       | Zurich    | fred@test.com       |
| Chris      | Ateba     | chris@test.com      |
| Atangana   | Christian | email@test.com      |
+------------+-----------+---------------------+
5 rows in set (0.00 sec)

Sometimes you will have some particular information about a field of a database table and you want to get the other fields, for example, suppose you are looking for a student with email christ@test.com, you will need to add WHERE clause to the SELECT query.

mysql> SELECT * FROM student_basic_info WHERE email='chris@test.com';
+------------+-----------+----------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+
| first_name | last_name | email          | id | password     | gender | about | created_at          | banned | deleted | last_updated        |
+------------+-----------+----------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+
| Chris      | Ateba     | chris@test.com |  5 | testpassword | NULL   | NULL  | 2019-06-01 21:38:14 |      0 |       0 | 2019-06-01 21:39:07 |
+------------+-----------+----------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+
1 row in set (0.00 sec)

 

Just to add that for the SELECT statement above you could have still chosen to query only some information about the student like last name, gender, id and not necessarily get everything as I did above.

In your WHERE clause, you can specify other conditions by combining them with AND and OR in the WHERE clause. For example, we want to have information concerning male students that are banned from the course, probably due to a violation of the terms and conditions of the course. For that let's modify our table a bit by adding some ladies, this tutorial was made on the fly so.

mysql> UPDATE student_basic_info SET banned=true, gender='M' WHERE email='njifidelis@test.com';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET banned=true, gender='M' WHERE email='fred@test.com';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET gender='F' WHERE email='chris@test.com';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> INSERT INTO student_basic_info(first_name, last_name, email, gender) VALUES ("Rudy", "kam", "menkam@test.com", "F")
    -> ,("Jen", "Eb", "jenieb@test.com", "F"),
    -> ("Ange", "De", "angede@test.com", "F");
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE student_basic_info SET banned=true WHERE email='menkam@test.com';
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET banned=true WHERE email='angede@test.com';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET banned=true WHERE email='email@test.com';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

Let's now query our information of male students banned from the course.

mysql> SELECT * FROM student_basic_info WHERE gender='M' AND banned=true;
+------------+-----------+---------------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+
| first_name | last_name | email               | id | password     | gender | about | created_at          | banned | deleted | last_updated        |
+------------+-----------+---------------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+
| nji        | Fidelis   | njifidelis@test.com |  2 | testpassword | M      | NULL  | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |
| Fred       | Zurich    | fred@test.com       |  4 | testpassword | M      | NULL  | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |
+------------+-----------+---------------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+
2 rows in set (0.00 sec)

Now let's retrieve female students or students with unknown gender that are banned from our suppose platform

mysql> SELECT first_name, last_name, email, gender, banned FROM student_basic_info WHERE (gender='F' OR gender IS NULL) AND banned IS true;
+------------+-----------+-----------------+--------+--------+
| first_name | last_name | email           | gender | banned |
+------------+-----------+-----------------+--------+--------+
| Atangana   | Christian | email@test.com  | NULL   |      1 |
| Rudy       | kam       | menkam@test.com | F      |      1 |
| Ange       | De        | angede@test.com | F      |      1 |
+------------+-----------+-----------------+--------+--------+
3 rows in set (0.00 sec)

 

Using brackets you can group conditions.

You may want to Order the information you query by some particular field or fields. For that, you use the Order By clause. We are going to SELECT all the students and Order by the first_name.

mysql> SELECT first_name, last_name, email, gender, banned FROM student_basic_info ORDER BY first_name DESC;
+------------+-----------+---------------------+--------+--------+
| first_name | last_name | email               | gender | banned |
+------------+-----------+---------------------+--------+--------+
| Rudy       | kam       | menkam@test.com     | F      |      1 |
| Patrick    | Njassap   | patrick@test.com    | NULL   |      0 |
| nji        | Fidelis   | njifidelis@test.com | M      |      1 |
| Jen        | Eb        | jenieb@test.com     | F      |      0 |
| Fred       | Zurich    | fred@test.com       | M      |      1 |
| Chris      | Ateba     | chris@test.com      | F      |      0 |
| Atangana   | Christian | email@test.com      | NULL   |      1 |
| Ange       | De        | angede@test.com     | F      |      1 |
+------------+-----------+---------------------+--------+--------+
8 rows in set (0.00 sec)

 

From the table above we see that the results have been sorted in descending order by the first name. Leaving out DESC, automatically orders in ascending order. Let's Order by first name and date created. For this let add another record INSERT INTO student_basic_info(first_name, last_name, email, gender) VALUES ("nji", "desmond", "desmond@test.com", "F");

mysql> SELECT first_name, last_name, email, gender, banned, created_at FROM student_basic_info ORDER BY first_name DESC, created_at ASC LIMIT 5;
+------------+-----------+---------------------+--------+--------+---------------------+
| first_name | last_name | email               | gender | banned | created_at          |
+------------+-----------+---------------------+--------+--------+---------------------+
| Rudy       | kam       | menkam@test.com     | F      |      1 | 2019-06-14 03:21:26 |
| Patrick    | Njassap   | patrick@test.com    | NULL   |      0 | 2019-06-01 21:38:14 |
| nji        | Fidelis   | njifidelis@test.com | M      |      1 | 2019-06-01 21:38:14 |
| nji        | desmond   | desmond@test.com    | F      |      0 | 2019-06-14 04:33:57 |
| Jen        | Eb        | jenieb@test.com     | F      |      0 | 2019-06-14 03:21:26 |
+------------+-----------+---------------------+--------+--------+---------------------+
5 rows in set (0.00 sec)

Looking at the students with first name nji we notice from the dates that they have been ordered in ASC ascending order. Let's change ASC to DESC and see the change. I added LIMIT 5, this clause limits the search to 5 results, I wanted to retrieve essential information to demonstrate Ordering.

+------------+-----------+---------------------+--------+--------+---------------------+
| first_name | last_name | email               | gender | banned | created_at          |
+------------+-----------+---------------------+--------+--------+---------------------+
| Rudy       | kam       | menkam@test.com     | F      |      1 | 2019-06-14 03:21:26 |
| Patrick    | Njassap   | patrick@test.com    | NULL   |      0 | 2019-06-01 21:38:14 |
| nji        | desmond   | desmond@test.com    | F      |      0 | 2019-06-14 04:33:57 |
| nji        | Fidelis   | njifidelis@test.com | M      |      1 | 2019-06-01 21:38:14 |
| Jen        | Eb        | jenieb@test.com     | F      |      0 | 2019-06-14 03:21:26 |
+------------+-----------+---------------------+--------+--------+---------------------+
5 rows in set (0.00 sec)

You can combine WHERE clause with an Order By clause, let's retrieve students that are female or not specified genre and who have been banned.

mysql> SELECT * FROM student_basic_info WHERE (gender='F' OR gender IS NULL) AND banned=1 ORDER BY first_name;
+------------+-----------+-----------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+
| first_name | last_name | email           | id | password     | gender | about | created_at          | banned | deleted | last_updated        |
+------------+-----------+-----------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+
| Ange       | De        | angede@test.com |  9 | testpassword | F      | NULL  | 2019-06-14 03:21:26 |      1 |       0 | 2019-06-14 03:21:26 |
| Atangana   | Christian | email@test.com  |  6 | testpassword | NULL   | NULL  | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |
| Rudy       | kam       | menkam@test.com |  7 | testpassword | F      | NULL  | 2019-06-14 03:21:26 |      1 |       0 | 2019-06-14 03:21:26 |
+------------+-----------+-----------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+
3 rows in set (0.05 sec)

We can also group our results by some particular criteria into summary rows using the GROUP BY statement. For example, we want to have a count of all the genders in our table. GROUP BY statements are often used with aggregate functions like COUNT, MAX, MIN, AVG, and SUM

mysql> SELECT gender, count(*) FROM student_basic_info GROUP BY gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| NULL   |        2 |
| F      |        5 |
| M      |        2 |
+--------+----------+
3 rows in set (0.00 sec)

Let's modify some of the tables to demonstrate this better. We are going to add another field score to the student_basic_info table. and populate this.

mysql> ALTER TABLE student_basic_info ADD COLUMN score FLOAT;
Query OK, 0 rows affected (2.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> UPDATE student_basic_info SET score=14.8 WHERE id=2
    -> ;
Query OK, 1 row affected (0.15 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET score=10 WHERE id=4
    -> ;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET score=10 WHERE id=8;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET score=19.9 WHERE id=5;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET score=19.9 WHERE id=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET score=09.4 WHERE id=10;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET score=11.4 WHERE id=9;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET score=11.4 WHERE id=3;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET score=15 WHERE id=7;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 

Say we want to have an average of the score of the different genders.

 

mysql> SELECT gender, count(*), avg(score) FROM student_basic_info GROUP BY gender;
+--------+----------+--------------------+
| gender | count(*) | avg(score)         |
+--------+----------+--------------------+
| NULL   |        2 | 15.649999618530273 |
| F      |        5 | 13.139999771118164 |
| M      |        2 | 12.400000095367432 |
+--------+----------+--------------------+
3 rows in set (0.02 sec)

Or rather let's group the scores and sum them.

+-------+----------+--------------------+
| score | COUNT(*) | SUM(score)         |
+-------+----------+--------------------+
|   9.4 |        1 |  9.399999618530273 |
|    10 |        2 |                 20 |
|  11.4 |        2 | 22.799999237060547 |
|  14.8 |        1 | 14.800000190734863 |
|    15 |        1 |                 15 |
|  19.9 |        2 |  39.79999923706055 |
+-------+----------+--------------------+
6 rows in set (0.02 sec)

ADVANCED QUERIES

Now we are going to look at some advanced queries, we start by querying 2 tables. You can join 2 tables based on a field value that is common to both tables. Let's retrieve student address together with student basic info using the JOIN ON statement.

mysql> SELECT * FROM student_basic_info JOIN student_address_info ON stud_id;
+------------+-----------+---------------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+-------+----+---------+-----------+----------+--------+----------------+---------+
| first_name | last_name | email               | id | password     | gender | about | created_at          | banned | deleted | last_updated        | score | id | stud_id | address1  | address2 | city   | state_province | country |
+------------+-----------+---------------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+-------+----+---------+-----------+----------+--------+----------------+---------+
| nji        | Fidelis   | njifidelis@test.com |  2 | testpassword | M      | NULL  | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |  14.8 |  3 |       2 | Bolifamba | NULL     | Douala | NULL           | NULL    |
| Patrick    | Njassap   | patrick@test.com    |  3 | testpassword | NULL   | NULL  | 2019-06-01 21:38:14 |      0 |       0 | 2019-06-01 21:39:07 |  11.4 |  3 |       2 | Bolifamba | NULL     | Douala | NULL           | NULL    |
| Fred       | Zurich    | fred@test.com       |  4 | testpassword | M      | NULL  | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |    10 |  3 |       2 | Bolifamba | NULL     | Douala | NULL           | NULL    |
| Chris      | Ateba     | chris@test.com      |  5 | testpassword | F      | NULL  | 2019-06-01 21:38:14 |      0 |       0 | 2019-06-01 21:39:07 |  19.9 |  3 |       2 | Bolifamba | NULL     | Douala | NULL           | NULL    |
| Atangana   | Christian | email@test.com      |  6 | testpassword | NULL   | NULL  | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |  19.9 |  3 |       2 | Bolifamba | NULL     | Douala | NULL           | NULL    |
| Rudy       | kam       | menkam@test.com     |  7 | testpassword | F      | NULL  | 2019-06-14 03:21:26 |      1 |       0 | 2019-06-14 03:21:26 |    15 |  3 |       2 | Bolifamba | NULL     | Douala | NULL           | NULL    |
| Jen        | Eb        | jenieb@test.com     |  8 | testpassword | F      | NULL  | 2019-06-14 03:21:26 |      0 |       0 | 2019-06-14 03:21:26 |    10 |  3 |       2 | Bolifamba | NULL     | Douala | NULL           | NULL    |
| Ange       | De        | angede@test.com     |  9 | testpassword | F      | NULL  | 2019-06-14 03:21:26 |      1 |       0 | 2019-06-14 03:21:26 |  11.4 |  3 |       2 | Bolifamba | NULL     | Douala | NULL           | NULL    |
| nji        | desmond   | desmond@test.com    | 10 | testpassword | F      | NULL  | 2019-06-14 04:33:57 |      0 |       0 | 2019-06-14 04:33:57 |   9.4 |  3 |       2 | Bolifamba | NULL     | Douala | NULL           | NULL    |
+------------+-----------+---------------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+-------+----+---------+-----------+----------+--------+----------------+---------+
9 rows in set (0.04 sec)

Looking at the table there is only one student with an address and that is that of the student with Id 2, but we notice that this goes through all the rows. If we wanted just to get results of both tables just for a student with ID, we need to be specific on our JOIN criteria.

mysql> mysql> SELECT * FROM student_basic_info JOIN student_address_info ON student_basic_info.id=student_address_info.stud_id;
+------------+-----------+---------------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+-------+----+---------+-----------+----------+--------+----------------+---------+
| first_name | last_name | email               | id | password     | gender | about | created_at          | banned | deleted | last_updated        | score | id | stud_id | address1  | address2 | city   | state_province | country |
+------------+-----------+---------------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+-------+----+---------+-----------+----------+--------+----------------+---------+
| nji        | Fidelis   | njifidelis@test.com |  2 | testpassword | M      | NULL  | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |  14.8 |  3 |       2 | Bolifamba | NULL     | Douala | NULL           | NULL    |
+------------+-----------+---------------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+-------+----+---------+-----------+----------+--------+----------------+---------+
1 row in set (0.00 sec)

 

In the above table we have two fields with labels id, what if we want to select a specific element by Id which id shall we be referring to, for this we use aliases.

So aliases allow us to give alternative names to our fields or tables in our SQL queries. Let's add another address record first for a student with id 3.

mysql> INSERT INTO student_address_info(stud_id, address1, city) VALUES (3, "mOLYKO", "bUEA");
Query OK, 1 row affected (0.25 sec)

In the below query we aliased student_basic_info and student_address_info tables as sbi and sai respectively together with id of the address to aid. Aliases in some cases can 

mysql> SELECT sbi.id, sbi.first_name, sbi.last_name, sai.id AS aid, sai.address1, sai.city FROM student_basic_info AS sbi JOIN student_address_info AS sai ON sbi.id=sai.stud_id;
+----+------------+-----------+-----+-----------+--------+
| id | first_name | last_name | aid | address1  | city   |
+----+------------+-----------+-----+-----------+--------+
|  2 | nji        | Fidelis   |   3 | Bolifamba | Douala |
|  3 | Patrick    | Njassap   |   4 | mOLYKO    | bUEA   |
+----+------------+-----------+-----+-----------+--------+
2 rows in set (0.00 sec)

 

When we started this section on JOINS we remember joining student basic info and address info led to the address record with id 2 be repeated across all the columns. Let's say we want to get now all information on both tables, but we do not want information that is not present in one table based on the joining criteria to be replaced by information matching criteria like in above where for other students not having address information there was a record of address to call them after the join, we will want these to be null. So we a have a choice between a LEFT JOIN and a RIGHT JOIN, for the left join rows on the second table with no matching records based on the join will be NULL, whereas for a RIGHT JOIN rows on the first table with no matching record will be NULL. So in our case will do a LEFT JOIN. INNER JOIN is the same as using just JOIN returns the same result as above.

mysql> SELECT sbi.id, sbi.first_name, sbi.last_name, sai.id AS aid, sai.address1, sai.city FROM student_basic_info AS sbi LEFT JOIN student_address_info AS sai ON sai.stud_id=sbi.id;
+----+------------+-----------+------+-----------+--------+
| id | first_name | last_name | aid  | address1  | city   |
+----+------------+-----------+------+-----------+--------+
|  2 | nji        | Fidelis   |    3 | Bolifamba | Douala |
|  3 | Patrick    | Njassap   |    4 | mOLYKO    | bUEA   |
|  4 | Fred       | Zurich    | NULL | NULL      | NULL   |
|  5 | Chris      | Ateba     | NULL | NULL      | NULL   |
|  6 | Atangana   | Christian | NULL | NULL      | NULL   |
|  7 | Rudy       | kam       | NULL | NULL      | NULL   |
|  8 | Jen        | Eb        | NULL | NULL      | NULL   |
|  9 | Ange       | De        | NULL | NULL      | NULL   |
| 10 | nji        | desmond   | NULL | NULL      | NULL   |
+----+------------+-----------+------+-----------+--------+
9 rows in set (0.02 sec)

 

Let's take a look at the searching text from the database. We will use the statement LIKE <pattern>. Let's query all the students whose first name start with A.

mysql> SELECT * FROM student_basic_info WHERE first_name LIKE 'a%';
+------------+-----------+-----------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+-------+
| first_name | last_name | email           | id | password     | gender | about | created_at          | banned | deleted | last_updated        | score |
+------------+-----------+-----------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+-------+
| Atangana   | Christian | email@test.com  |  6 | testpassword | NULL   | NULL  | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |  19.9 |
| Ange       | De        | angede@test.com |  9 | testpassword | F      | NULL  | 2019-06-14 03:21:26 |      1 |       0 | 2019-06-14 03:21:26 |  11.4 |
+------------+-----------+-----------------+----+--------------+--------+-------+---------------------+--------+---------+---------------------+-------+
2 rows in set (0.00 sec)

 

Here are some patterns from W3Schools, you can play with 

LIKE Operator	Description
WHERE CustomerName LIKE 'a%'	Finds any values that start with "a"
WHERE CustomerName LIKE '%a'	Finds any values that end with "a"
WHERE CustomerName LIKE '%or%'	Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%'	Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a__%'	Finds any values that start with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o'	Finds any values that start with "a" and ends with "o"

 

We can also perform full-text searches, where we are able to search for words, synonyms of words, phrases, similar words just to name a few against character-based columns/fields with types CHAR, VARCHAR and TEXT. To be able to use full text we will need to index the table with the index FULLTEXT on the creation or later with ALTER. In our case since we already have our table, we will update the table definition by adding the FULLTEXT index on the about column of the student_basic_info table and the address1 column of the student_address_info table.

mysql> ALTER TABLE student_basic_info ADD FULLTEXT(about)
    -> ;
Query OK, 0 rows affected (1.69 sec)
Records: 0  Duplicates: 0  Warnings: 0

We are going to do some updates on our tables,  adding about information to our students_basic_info table.

mysql> UPDATE student_basic_info SET about="Ruaridh is an award-winning UI/UX digital designer and creative director originally from Edinburgh, Scotland with 14 years of experience designing for brands like Channel 4, Procter & Gamble, Danone, Tottenham Hotspur and Standard Life. In his spare time, he enjoys photography and writing design articles and tutorials for Designmodo and Digital Arts Online." WHERE id=2;
Query OK, 1 row affected (0.30 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET about="At Morgan Stanley, Carole advised eight founders through exits totaling over $750 million and then founded her own practice to manage venture investments for family offices. A former tech CFO, she freelances to leverage her experience with capital raises, strategic pivots, and turnarounds. She enjoys helping innovators and entrepreneurs achieve financial success across many verticals, and has recently focused her efforts on fintech. " WHERE id=3;
Query OK, 1 row affected (0.30 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET about="April is a senior front-end web developer with over 15 years of experience at companies like Starbucks, Rosetta Stone, and Livemocha. She specializes in building front-ends for JavaScript web applications and has a deep expertise in the nuances of cross-platform development." WHERE id=4;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET about="Erin is an Agile product management and product design leader who has led a variety of cross-device/responsive projects in eCommerce, B2C, and B2B organizations. She is experienced working with dispersed teams for organizations ranging from pre-funding startups to large Fortune 500 organizations to create inspired and innovative products. She also enjoys helping build out new Agile product teams and is well-versed in Agile, Scrum, and Kanban." WHERE id=5;
Query OK, 1 row affected (0.23 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET about="Darin is a data scientist and engineer with a PhD in physics from Stanford. He's passionate about data and machine learning and has worked on data science projects across numerous industries and applications. Darin's co-founded an AI company and led a team of data scientists to build a product which uses machine learning and optimization techniques to reduce energy consumption in data centers. He's eagerly waiting for quantum computers." WHERE id=6;
Query OK, 1 row affected (0.15 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET about="Darin is a data scientist and engineer with a PhD in physics from Stanford. He's passionate about data and machine learning and has worked on data science projects across numerous industries and applications. Darin's co-founded an AI company and led a team of data scientists to build a product which uses machine learning and optimization techniques to reduce energy consumption in data centers. He's eagerly waiting for quantum computers." WHERE id=6;
Query OK, 0 rows affected (0.05 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> 
mysql> UPDATE student_basic_info SET about="Anne is an experienced developer who has worked both for large corporations and startups. After building financial trading applications for eight years as an engineer at Merrill Lynch, Anne founded and built LoudUp, a music-based social network that she designed, developed, and launched from the ground up. She specializes in .NET technologies and JavaScript." WHERE id=7;
Query OK, 1 row affected (0.23 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET about="#Sexy# Amusante # Fofolle # Ta Bami Bafang # Ambitieuse# Narcissique#Amoureuse et fière de ses origines Ig @lydiamakake # SC lydiadaniele94 # God is love" WHERE id=8;
Query OK, 1 row affected (0.32 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET about="The Type To Say A Prayer,Then Go Get What I Just Prayed For." WHERE id=9;
Query OK, 1 row affected (0.27 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE student_basic_info SET about="Above all else, guard your heart" WHERE id=10;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

We are going search for students who have mentioned being "experienced developers"

mysql> SELECT * FROM student_basic_info WHERE MATCH(about) AGAINST('experienced developer');
+------------+-----------+-----------------+----+--------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+---------+---------------------+-------+
| first_name | last_name | email           | id | password     | gender | about                                                                                                                                                                                                                                                                                                                                                                                                                                                          | created_at          | banned | deleted | last_updated        | score |
+------------+-----------+-----------------+----+--------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+---------+---------------------+-------+
| Rudy       | kam       | menkam@test.com |  7 | testpassword | F      | Anne is an experienced developer who has worked both for large corporations and startups. After building financial trading applications for eight years as an engineer at Merrill Lynch, Anne founded and built LoudUp, a music-based social network that she designed, developed, and launched from the ground up. She specializes in .NET technologies and JavaScript.                                                                                       | 2019-06-14 03:21:26 |      1 |       0 | 2019-06-14 03:21:26 |    15 |
| Fred       | Zurich    | fred@test.com   |  4 | testpassword | M      | April is a senior front-end web developer with over 15 years of experience at companies like Starbucks, Rosetta Stone, and Livemocha. She specializes in building front-ends for JavaScript web applications and has a deep expertise in the nuances of cross-platform development.                                                                                                                                                                            | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |    10 |
| Chris      | Ateba     | chris@test.com  |  5 | testpassword | F      | Erin is an Agile product management and product design leader who has led a variety of cross-device/responsive projects in eCommerce, B2C, and B2B organizations. She is experienced working with dispersed teams for organizations ranging from pre-funding startups to large Fortune 500 organizations to create inspired and innovative products. She also enjoys helping build out new Agile product teams and is well-versed in Agile, Scrum, and Kanban. | 2019-06-01 21:38:14 |      0 |       0 | 2019-06-01 21:39:07 |  19.9 |
+------------+-----------+-----------------+----+--------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+---------+---------------------+-------+
3 rows in set (0.00 sec)

 

You can equally specify the mode to query. IN BOOLEAN MODE and WITH QUERY EXPANSION 

mysql> SELECT * FROM student_basic_info WHERE MATCH(about) AGAINST('IS A developer' WITH QUERY EXPANSION);
+------------+-----------+---------------------+----+--------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+---------+---------------------+-------+
| first_name | last_name | email               | id | password     | gender | about                                                                                                                                                                                                                                                                                                                                                                                                                                                          | created_at          | banned | deleted | last_updated        | score |
+------------+-----------+---------------------+----+--------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+---------+---------------------+-------+
| Rudy       | kam       | menkam@test.com     |  7 | testpassword | F      | Anne is an experienced developer who has worked both for large corporations and startups. After building financial trading applications for eight years as an engineer at Merrill Lynch, Anne founded and built LoudUp, a music-based social network that she designed, developed, and launched from the ground up. She specializes in .NET technologies and JavaScript.                                                                                       | 2019-06-14 03:21:26 |      1 |       0 | 2019-06-14 03:21:26 |    15 |
| Fred       | Zurich    | fred@test.com       |  4 | testpassword | M      | April is a senior front-end web developer with over 15 years of experience at companies like Starbucks, Rosetta Stone, and Livemocha. She specializes in building front-ends for JavaScript web applications and has a deep expertise in the nuances of cross-platform development.                                                                                                                                                                            | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |    10 |
| Chris      | Ateba     | chris@test.com      |  5 | testpassword | F      | Erin is an Agile product management and product design leader who has led a variety of cross-device/responsive projects in eCommerce, B2C, and B2B organizations. She is experienced working with dispersed teams for organizations ranging from pre-funding startups to large Fortune 500 organizations to create inspired and innovative products. She also enjoys helping build out new Agile product teams and is well-versed in Agile, Scrum, and Kanban. | 2019-06-01 21:38:14 |      0 |       0 | 2019-06-01 21:39:07 |  19.9 |
| Patrick    | Njassap   | patrick@test.com    |  3 | testpassword | NULL   | At Morgan Stanley, Carole advised eight founders through exits totaling over $750 million and then founded her own practice to manage venture investments for family offices. A former tech CFO, she freelances to leverage her experience with capital raises, strategic pivots, and turnarounds. She enjoys helping innovators and entrepreneurs achieve financial success across many verticals, and has recently focused her efforts on fintech.           | 2019-06-01 21:38:14 |      0 |       0 | 2019-06-01 21:39:07 |  11.4 |
| Atangana   | Christian | email@test.com      |  6 | testpassword | NULL   | Darin is a data scientist and engineer with a PhD in physics from Stanford. He's passionate about data and machine learning and has worked on data science projects across numerous industries and applications. Darin's co-founded an AI company and led a team of data scientists to build a product which uses machine learning and optimization techniques to reduce energy consumption in data centers. He's eagerly waiting for quantum computers.       | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |  19.9 |
| nji        | Fidelis   | njifidelis@test.com |  2 | testpassword | M      | Ruaridh is an award-winning UI/UX digital designer and creative director originally from Edinburgh, Scotland with 14 years of experience designing for brands like Channel 4, Procter & Gamble, Danone, Tottenham Hotspur and Standard Life. In his spare time, he enjoys photography and writing design articles and tutorials for Designmodo and Digital Arts Online.                                                                                        | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |  14.8 |
+------------+-----------+---------------------+----+--------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+---------+---------------------+-------+
6 rows in set (0.01 sec)

 

 

Using IN BOOLEAN MODE

mysql> SELECT * FROM student_basic_info WHERE MATCH(about) AGAINST('IS A developer' IN BOOLEAN MODE);
+------------+-----------+-----------------+----+--------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+---------+---------------------+-------+
| first_name | last_name | email           | id | password     | gender | about                                                                                                                                                                                                                                                                                                                                                                    | created_at          | banned | deleted | last_updated        | score |
+------------+-----------+-----------------+----+--------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+---------+---------------------+-------+
| Fred       | Zurich    | fred@test.com   |  4 | testpassword | M      | April is a senior front-end web developer with over 15 years of experience at companies like Starbucks, Rosetta Stone, and Livemocha. She specializes in building front-ends for JavaScript web applications and has a deep expertise in the nuances of cross-platform development.                                                                                      | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |    10 |
| Rudy       | kam       | menkam@test.com |  7 | testpassword | F      | Anne is an experienced developer who has worked both for large corporations and startups. After building financial trading applications for eight years as an engineer at Merrill Lynch, Anne founded and built LoudUp, a music-based social network that she designed, developed, and launched from the ground up. She specializes in .NET technologies and JavaScript. | 2019-06-14 03:21:26 |      1 |       0 | 2019-06-14 03:21:26 |    15 |
+------------+-----------+-----------------+----+--------------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+---------+---------------------+-------+
2 rows in set (0.00 sec)

Running Subqueries 

mysql> SELECT * FROM student_basic_info JOIN (SELECT id, stud_id, address1 FROM student_address_info) AS sai ON sai.stud_id=student_basic_info.id;
+------------+-----------+---------------------+----+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+---------+---------------------+-------+----+---------+-----------+
| first_name | last_name | email               | id | password     | gender | about                                                                                                                                                                                                                                                                                                                                                                                                                                                 | created_at          | banned | deleted | last_updated        | score | id | stud_id | address1  |
+------------+-----------+---------------------+----+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+---------+---------------------+-------+----+---------+-----------+
| nji        | Fidelis   | njifidelis@test.com |  2 | testpassword | M      | Ruaridh is an award-winning UI/UX digital designer and creative director originally from Edinburgh, Scotland with 14 years of experience designing for brands like Channel 4, Procter & Gamble, Danone, Tottenham Hotspur and Standard Life. In his spare time, he enjoys photography and writing design articles and tutorials for Designmodo and Digital Arts Online.                                                                               | 2019-06-01 21:38:14 |      1 |       0 | 2019-06-01 21:39:07 |  14.8 |  3 |       2 | Bolifamba |
| Patrick    | Njassap   | patrick@test.com    |  3 | testpassword | NULL   | At Morgan Stanley, Carole advised eight founders through exits totaling over $750 million and then founded her own practice to manage venture investments for family offices. A former tech CFO, she freelances to leverage her experience with capital raises, strategic pivots, and turnarounds. She enjoys helping innovators and entrepreneurs achieve financial success across many verticals, and has recently focused her efforts on fintech.  | 2019-06-01 21:38:14 |      0 |       0 | 2019-06-01 21:39:07 |  11.4 |  4 |       3 | mOLYKO    |
+------------+-----------+---------------------+----+--------------+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+--------+---------+---------------------+-------+----+---------+-----------+
2 rows in set (0.00 sec)

 

Create user

mysql> CREATE USER 'pinterest'@'localhost' IDENTIFIED BY 'pinterest';
Query OK, 0 rows affected (0.02 sec)

Accessing sql_beginner for new user

mysql> use sql_beginner;
ERROR 1044 (42000): Access denied for user 'pinterest'@'localhost' to database 'sql_beginner'

Granting user permissions.

mysql> GRANT CREATE ON *.* TO 'pinterest'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT UPDATE, DELETE ON *.* TO 'pinterest'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON sql_beginner.student_basic_info TO 'pinterest'@'localhost';
Query OK, 0 rows affected (0.00 sec)

 

mysql> GRANT ALL PRIVILEGES ON sql_beginner.* TO 'pinterest'@'localhost';
Query OK, 0 rows affected (0.01 sec)