MySQL Made Easy - Part One

Introduction

This tutorial, being the first in MySQL Made Easy series assumes zero knowledge of SQL. MySQL Made Easy series is an invaluable material for anyone with zero programming knowledge or with experience in other programming language who want to learn SQL, or anyone with prior knowledge of SQL but needs a refresher.

Although there are different SQL Database Management System (DBMS), in this tutorial, we are going to use MySQL Database Management System for our SQL demonstrations. The concepts and syntax you will learn using MySQL are largely applicable in other DBMS such as PostgreSQL, SQLServer, OracleDB and SQLite.

SQL, often pronounced Sequel means Structured Query Language. It is the major programming language for storing, accessing, updating, manipulating, interacting and working with data in Relational Databases. Hence, learning and being able to apply SQL skill on your job, especially if your role involve dealing with with data, will significantly increase your effectiveness and efficiency, which surely will give you big edge in your career.

How to Enhance your Understanding

In this tutorial, we shall provide you with example codes to explain the concepts. In order to enhance your understanding and retention of the concepts, we strongly advice that you follow the rules below:

• Do not copy and paste the example code into your IDE (MySQL Workbench) to practice. Type them inside your IDE. Typing the code rather than copying and pasting into your IDE will significantly enhance your learning and understanding. Hence, we recommend that you should type all the example codes in this tutorial. Typing the example codes develops and strengthens your programming muscle. However, if you decide to do copy and paste, do not copy the serial numbers in the example codes into the IDE, otherwise you will get error message.

• In addition to the sample codes we provide for you to practice, you should always come up and practice with your own version of codes. Coding is better learnt by practice

SQL Statements 

SQL statements are issued with SQL commands and keywords to give instruction to the computer to perform a task on the database. 

Note: SQL commands and keywords are not case sensitive but customarily they are usually written in uppercase. For example, SELECT  is the same as select.

SQL Commands 

There are four categories of SQL commands, namely: Data Manipulation Language, Data Definition Language, Data Control Language and Transaction Control Language. In this tutorial, we shall majorly focus on Data Manipulation Language. Other categories will be considered in subsequent tutorials.

MySQL Database Management System

Now, to enable you practice SQL on your computer, you need to do the following:

• Download and Install MySQL Community Server

• Download and Install MySQL Workbench

• Download and Create Sample Databases

How to Download MySQL Community Server

Follow the steps below to download and install My SQL community server on your computer:

1. Go to mysql.com and select DOWNLOADS

2. Scroll down to the bottom of the downloads page and click on MySQL Community (GPL) Downloads

3. Click on MySQL Community Server

4. Select macOS in the Select Operating System dropdown menu if you are on mac. Select Microsoft Windows if you are on Windows

5. Click on Download button to the right of macOS 10.5 (x86, 64-bit), DMG Archive if you are on mac. Click on Download button to the right of Windows (x86, 64-bit), MSI Installer if you are on Windows

6. You will see buttons to Login or Sign Up to your Oracle Web account. Click on the link that says “No thanks, just start my download”

   Wait for the download to finish and then carry out the installation, following the steps in the next section below.

How to Install MySQL and Workbench on macOS

1. Double-click the DMG file to open it

2. Double-click the PKG installer

3. A dialog box comes up telling you it will check for prerequisites. Click Continue to start.

4. Then click Continue until you get to the license screen, accept the software license

5. Click on Install and enter your password, then click on Install Software

6. In the Configure MySQL Server screen, select Use Strong Password Encryption, then click Next

7. You will be asked for MySQL root password. Enter a password of your choice. Do not forget whatever you entered as the password here, you will need it to connect to the server later. Click Finish, then go to viii

8. Click here to download MySQL Workbench

9. Select Download to the right of macOS 10.5 (x86, 64-bit), DMG Archive

10. Double click on the file once the download is complete, then drag the workbench logo in the direction of the arrow as shown in the screen short below into the application folder

    

11. Go to application folder and open MySQL Workbench

12. Click on local instance box under MySQL Connections

13. You will be prompted to enter your root password. Once you do that, you get connected and your workbench is ready for coding

How to Install MySQL on Windows

1. Open the file downloaded in a above to launch MySQL Installer

2. Select Full and then click on Next

3. Follow the subsequent prompts, until you get to Installation Complete screen. They are very simple and similar to mac installation above. On Installation Complete screen, select Start MySQL Workbench after setup and click Finish

4. MySQL Workbench opens up

5. Click on local instance box under MySQL Connections

6. You will be prompted to enter your root password. Once you do that, you get connected and your workbench is ready for coding

Example Database Installation

In this tutorial, we are going to use Sakila sample database for our demonstrations and practice. You can download Sakila database from MySQL website. Follow the steps below to download the sample database on your computer

1. Click here to open “Other MySQL Documentation” page

2. Scroll down on the page to “Example Databases” see the screenshot below for guidance

   

3. Click on Zip to the right of sakila database to download the file. the file contains sakila schema, sakila data and sakila mweb. Ignore or delete the sakila mweb

4. Open MySQL Workbench and click on the second icon under the Home icon

   

5. Navigate to the sakila folder on your computer and click on sakila schema, then click on open

   

6. Click on the execute icon shown below to run the code. Do not bother about the content of the code for now

   

7. Right click in the space below sys under schemas tab and select Refresh All, you will see sakila coming up

   

8. Repeat steps d to f for sakila data. Now you are ready to write your first SQL query

9. Click on the first icon under the Home icon on MySQL Workbench to open a fresh SQL file. Note that you can save this file by clicking on save icon on MySQL Workbench. We saved our own as MySQLTutorial

10. Double click on sakila schema on the Workbench, then double click on the tables under sakila schema to see the list of tables. One of the tables in the long list is “film”

11. On MySQLTutorial file created in i above, TYPE the code in Fig 2.k1 below and click on execute icon.

    Note the emphasis on TYPE. Type do not copy and paste.

    Fig 2.k1

    1 USE sakila; 
    2 SELECT * FROM film;

    When you execute (run) the code above, you should have 1000 rows of items returned in the lower part of the Workbench. You can export the output to excel.

SQL Comments

In programming, a comment is a non-executable single or multiple line statement(s) which provide more information about the program. Comment can also be used to disable a line or multiple lines of code to make computer ignore them while executing other lines of code.

1. Single Line Comment

   You can create a single line comment or comment out a line of code by starting it with two hyphens follow by a space “-- ”. See examples below. Note that the first code in Fig 3.a1 will not work

   Fig3.a1

   1 Write a code to generate a report of all the Company's customers
   2
   3 SELECT * FROM customer;

   If you type all the statement in Fig 3.a1 above on your workbench and attempt to run it, you will get an error message because line 1 does not follow SQL syntax. It is just an explanation of what the code in line 3 should do. If you want the code to run successfully, you need to make modifications as shown in fig 3.a2 below. Start line 1 with double hyphen follow by a space to change it into a comment. Then execute the code and it will run perfectly

   Fig 3.a2

   1 -- Write a code to generate a report of all the Company's customers
   2 SELECT * FROM customer;

2. Multiple Line Comment

   You can create a multiple line comments by starting the first line with “/*” and end the last line in the comments with “*/” . In the example in Fig 3.b1 below lines 1 to 4 have been disabled. Only the code on line 6 is active and will be executed if you run the code.

   Fig 3.b1

   1 /* Write a code to generate a report of all the Company's customers
   2 SELECT * FROM customer;
   3 SELECT film_id, title, release_year, last_update 
   4 FROM film;*/
   5 
   6 SELECT * FROM film;

Data Manipulation Language (DML)

These are used to exploit data in the database. They include following commands: USE, SELECT, UPDATE, DELETE and INSERT

USE Statement

The USE statement is used to select the database or schema on which you want to perform operations, especially when you have multiple databases or schema on the workbench. The general syntax is as indicated below:

USE database_name

Recall we used the example code below earlier in this tutorial. USE sakila; in line 1 is used to tell the computer that we want to work on sakila database and not sys database

1 USE sakila;
2 SELECT * FROM film;

Note:

• Database and schema are usually used interchangeably

• *USE statement, is not mandatory. For example, USE sakila in the code above is not mandatory, provided you double click on sakila schema and sakila is showing to the right of Schema under Object Information before you run the code. In the screenshot below, USE sakila was commented out and the line two of the code still executed successfully:

• *Sometimes you may still get an error message (Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.) in the Action Output pane even when you have selected the schema. In that case, just go ahead and apply USE statement.

SELECT statement

SELECT Statement is used to extract data and prepare report from a database to help management in decision making. SELECT is often used alongside other keywords such as FROM, WHERE, DISTINCT etc. The general syntax for SELECT Statement is as shown below:

How to Select only specified fields (columns)

To select specified fields or columns in a table, follow the syntax below:

SELECT Column1Name, Column2Name, …, ColumnXName
FROM TableName;

Practice with the code below using sakila database:

1 SELECT film_id, title, release_year 
2 FROM film;
3
4 -- The lines of code above will only return the three columns specified

How to Select all the fields in a Table

To select all the fields or columns in a table, use SELECT follow by “*”. See the general syntax below:

SELECT * FROM TableName;

Practice with the code below using sakila database:

1 SELECT * FROM film;
2
3 -- The line of code above will return all the columns in the film table

SELECT Statement with other keywords

SELECT statement can be used with other SQL keywords to further refine or manipulate the data to provide a more useful information or report. Let us quickly take a look at the commonly used ones.

SELECT DISTINCT Statement

This statement is used to return unique entries in the database. This, for example can help you to avoid having a report with duplicated entries. The general syntax is:

SELECT DISTINCT Column1Name
FROM TableName;

Practice with the code below using sakila database:

1 SELECT DISTINCT customer_id 
2 FROM rental;
3
4 -- The code above will return 599 rows. If you remove DISTINCT from the code and run it, you will get 1000 rows because of duplicate customer_id

SELECT and ORDER BY

Using SELECT alongside ORDER BY enables us to sort the output of the script either in ascending or descending order. The general syntax is as shown below:

SELECT Column1Name, Column2Name, …, ColumnXName
FROM TableName
ORDER BY ColumnXName;

Practice with the code below using sakila database:

1 SELECT DISTINCT customer_id, first_name, last_name 
2 FROM customer
3 ORDER BY first_name;

Note that ORDER BY keyword sorts the data in ascending order by default. However, if you want the data to be sorted in descending order, you will need to end the code with DESC keyword as shown below. You can also use ASC keyword to sort in ascending order.

1 SELECT DISTINCT customer_id, first_name, last_name 
2 FROM customer
3 ORDER BY first_name DESC;

SELECT and LIMIT keyword

To return only a specified number of records or rows from the output of the query, follow the general syntax below:

SELECT Column1Name, Column2Name, …, ColumnXName
FROM TableName
LIMIT x -- where x is the number of rows you want to return

Practice with the code below using sakila database:

1 SELECT film_id, title, release_year 
2 FROM film
3 LIMIT 25;

In addition to the example above, LIMIT keyword can be used to achieve more data manipulation. We shall discuss this with examples below:

1 -- To select rows 11 to 25 from the film table use the code in lines 3 and 4. Ignore the comments, they are just explanatory notes
2
3 SELECT * FROM film
4 LIMIT 10, 15; -- (the first number, 10 represent the number of rows you want to cut off from the top of the report, while the second number, 15 is the number of rows you want to return after the cutting off)
5
6
7 -- To select the top 50 film by duration. Firstly, you need to identify the right table to use. Your ability to identify the right table is based on your knowledge of the tables in the schema and the data they contain. Our best option to achieve this in sakila schema is the film table. Secondly, write your code. 
8 -- Practice with the code below:
9 
10 SELECT * FROM film
11 ORDER BY length DESC
12 LIMIT 50; -- (LIMIT should be the last statement)

INSERT INTO

INSERT INTO statement is used alongside VALUES keyword to write new records into the specified table. Here is the general syntax:

INSERT INTO TableName (Column1Name, Column2Name, …, ColumnXName)
VALUES (‘Value1’, ‘Value2’, …, ‘ValueX’);

Practice with the code below using actor table in sakila database:

1 -- To know the names of all the columns in the actor table, do:
2
3 SELECT * FROM actor
4
5 -- Alternatively, you can get the names of all the column in a table by double clicking on the appropriate schema >> double click on the table >> double click on the column to reveal the table columns and their names
6
7 -- Then, execute the code in lines 9 and 10 below. Scroll down to the end of the table, the number of rows would have increased from 200 to 201
8
9 INSERT INTO actor (actor_id, first_name, last_name, last_update)
10 VALUES ('201', 'King', 'Bradley', '2022-08-15 04:34:30');

If you are writing into all the columns in the table, you don’t have to specify the column names, provided the order of the values is the same as the order of the columns in the table. In that case, the general syntax will be as shown below:

INSERT INTO TableName 
VALUES (‘Value1’, ‘Value2’, …, ‘ValueX’);

Practice with the code below using sakila database:

1 INSERT INTO actor
2 VALUES ('202', 'Kimberly', 'Buffet', '2022-08-15 04:34:30'); 
3
4 -- When you run the code in lines 1 and 2 above, *scroll to the end of the table, a new record with actor_id 202 would have been added. 

*NOTE: Scrolling will be highly inefficient in a large table where you have thousands of rows. Rather than doing the long scroll, you can check if the record has been added as intended by running the code below:

1 SELECT * FROM actor
2 WHERE actor_id = 202;
3
4 —- Note that you can use any of the fields(columns) to search for the newly added record or existing records. We used actor_id because it is a unique attribute. It is the Primary Key, you will learn about Primary Key in subsequent tutorials.

UPDATE Statement

UPDATE statement is used with SET and WHERE keyword to modify existing records in a specified table. The general syntax is as follows:

UPDATE TableName
SET Column1Name = Value1, Column2Name = Value2, ….
WHERE condition;

Example: Imagine a customer with customer_id 8 got married and she has written the company to change her last name to THOMAS to reflect her current status. See what you need to do below:

1 —- Firstly, query for the record with customer_id 8 as shown in lines 3 and 4 below to confirm her current last_name
2 
3 SELECT * FROM customer 
4 WHERE customer_id = 8;
5 
6 -- Now run the code in lines 8 to 10 below to change the last_name of customer with ID No 8 to THOMAS
7 
8 UPDATE customer
9 SET last_name = 'THOMAS'
10 WHERE customer_id = 8;
11
12 -- Then do:
13
14 SELECT * FROM customer 
15 WHERE customer_id = 8;
16
17 -- You will notice that Susan's last name would have changed from Wilson to Thomas

Note: You can run the codes in lines 8 to 10 and lines 14 and 15 simultaneously

DELETE Statement

DELETE statement is used alongside FROM and WHERE keywords to delete records in a specified table. The general syntax is as follows:

DELETE FROM TableName
WHERE condition;

Let us demonstrate DELETE statement by removing Kimberly with actor_id 202 who was added to the actor table earlier. Practice with the codes below:

1 —- Run the code in lines 3 and 4 below to confirm the existence of the record with actor_id 202
2
3 SELECT * FROM actor
4 WHERE actor_id = 202;
5 
6 —- Then run the code in lines 8 and 9 below:
7 
8 DELETE FROM actor
9 WHERE actor_id = 202;
10
11 —- Finally, run the code in lines 13 and 14 to confirm if the record had been deleted. This step is not mandatory, it is only included for learning purpose or to double check, in case you specify a wrong condition. Once the workbench shows that a delete statement has run successfully be rest assured the record has been deleted
12 
13 SELECT * FROM actor
14 WHERE actor_id = 202;

CAUTION: You must be very cautious while using DELETE Statement. Ensure the WHERE condition is stated and stated correctly. If you forget the WHERE clause, all the records in the table will be deleted. If you state the wrong WHERE condition, you will end up deleting wrong record.

Columns and Tables Aliases

In real life you will encounter instances where the table and column names are somehow technical and hence not user friendly. In such situations, you can make your SQL reports user friendly by assigning more meaningful names to tables and column in your report using Alias. Alias is created with AS keyword

• Column Alias

  Here is the general syntax for column alias:

  SELECT Column1Name AS Column1_Alias, Column2Name AS Column2_Alias, …, ColumnXName AS ColumnX_Alias
  FROM TableName;

  Let us use the payment table in the sakila schema to demonstrate alias. Try the code below:

  1 SELECT customer_id AS ID, amount AS AMOUNT_USD, payment_date 
  2 AS DATE
  3 FROM payment;

  When you run the code in lines 1 to 3 above, SQL will assign the alias specified in the code to the columns on the report temporarily, it does not change the column name on the database.

  Note: The use of AS keyword to give an alias to a column or table is optional. The code will still work perfectly without AS keyword. What AS does to the code is to improve its readability. Practice with the modified code below and you will get the same result it was when AS keyword was used:

  1 SELECT customer_id ID, amount AMOUNT_USD, payment_date DATE
  2 FROM payment;

• Alias with space

  You will notice that we use an underscore between AMOUNT and USD while assigning alias to amount in the code above. MySQL will usually gives syntax error if you attempt to put a space in the alias. This syntax error can be avoided by wrapping aliases with space inside back tick “` `”. Practice with the code below:

  1 SELECT customer_id AS `Customer Number`, 
  2	amount AS `AMOUNT (USD)`, 
  3	payment_date AS `Payment Date`
  4 FROM payment;
  5 
  6 -- We broke the code into separate lines and tab-in lines 2 and 3 to enhance readability. The code will still run successfully if everything is on a single line

• Table Alias

  Table alias usually does not show any noticeable change when you run your code. However, it becomes very useful when you need to join tables to combine information from multiple tables in your SQL report. This kind of scenario will be dealt with later. Here is the general syntax for table alias:

  SELECT Column1Name, Column2Name, …, ColumnXName
  FROM TableName AS Table_Alias

  Practice with the example below:

  1 SELECT customer_id AS `Customer Number`, 
  2	amount AS `AMOUNT (USD)`, 
  3	payment_date AS `Payment Date`
  4 FROM payment AS `Payment Report`;
  5
  6 -- As mentioned above, you won't see any noticeable changes when you run this code

Data Filtering

We can filter rows of data we select with the use of WHERE clause alongside other keywords like (AND, OR, IN & BETWEEN) and comparison operators such as (=, <, > etc) depending on what we want to achieve. Now, let us take a look at them one after the other.

1. Filtering with SELECT and WHERE

   For example, if you want to know the volume of business a customer with customer_id 1 has done. Firstly, you need to identify the table that has volume of business done by all the customers in the schema. In the case of sakila schema, the payment table seems to be our best option. You can then filter for customer_id 1 to see the volume of transactions he has done with the code below:

   1 SELECT * FROM payment 
   2 WHERE customer_id = 1;

2. Multiple Condition Filtering with AND

   There will be situations where you need to filter rows of data that satisfy two or more conditions concurrently. This can be achieved using WHERE and AND keyword. Imagine you work with sakila and your CEO ask you to send him a schedule of all the active customer in branch 2. You can easily do this with the code below:

   1 SELECT * FROM customer
   2 WHERE store_id = 2 
   3 AND active = 1

   Note:

   • Active customers have active value of 1, while inactive customers have active value of 0.

   • Try and change the active value from 1 to 0, run the code and check your result. If your CEO had requested for the list of inactive customers, all you need to is to set active value to 0 in your code.

   • Also, you can add as many AND as possible to add more conditions where applicable

3. Multiple Condition Filtering with OR

   OR keyword is used in situations where you want your SQL to return rows of data that at the minimum meet any one of the conditions specified. For example, Sakila is organising a raffle draw for his customers where the qualifying criteria is that a customer must belong to branch 2 or is among its first 25 customers. You have been asked to provide the schedule of all the qualifying customers. You can easily do this with with the code below, try and play around with it on the workbench:

   1 SELECT * FROM customer
   2 WHERE store_id = 2
   3 OR customer_id <= 25;

   Note:

   IN Keyword - Where you have multiple condition on the same column, you can use IN keyword rather repeating the column name and OR keywords multiple times in your code. The two set of codes below will achieve the same results but the lines of code 10 to 12 is neater and more efficient:

   1 SELECT * FROM address 
   2 WHERE district = 'Texas' 
   3 OR district = 'England' 
   4 OR district = 'California'
   5 OR district = 'Taipe'
   6 OR district = 'Cordoba';
   7
   8 -- See the alternative approach in lines 10 to 12 below using IN keyword
   9
   10 SELECT * FROM address 
   11 WHERE district 
   12 IN ('Texas', 'England', 'California', 'Taipe', 'Cordoba');

4. Multiple Condition Filtering using AND & OR

   In real life, there will be instances where you need to combine multiple conditions using both AND and OR keywords. If for example you review the data generated in section 5c above, you will notice that it contains inactive customers, which most likely will not be desirable. This anomaly can be fixed by combining AND and OR in setting the conditions and wrapping the conditions inside bracket to ensure correct order of operations as shown below:

   1 SELECT * FROM customer
   2 WHERE (active = 1)
   3 AND (store_id = 2 OR customer_id <= 25);

5. Filtering data BETWEEN a range of values

   BETWEEN keyword comes in handy where you need to generate a report that is filtered by a range of value in a column. Take note that BETWEEN keyword in SQL is inclusive starting and ending values in the range. Practice with the code below:

   1 SELECT * FROM address
   2 WHERE address_id BETWEEN 20 AND 100;
   3
   4 -- Alternatively, the code in lines 7 to 9 below will achieve the same result with code in lines 1 and 2. But the code in lines 1 and 2 is neater and more efficient.
   5
   6 
   7 SELECT * FROM address
   8 WHERE address_id >=  20 
   9 AND address_id <= 100;

6. Filtering using IS NULL or IS NOT NULL

   A NULL value is an unknown value. Please note that unknown value is not the same as “0” or an empty string or cell. We shall discuss how to set the field or column properties in subsequent tutorial on table creation. SQL will not produce desirable result if you use comparison operators such as “=” when filtering for NULL value. Hence, you have to use IS NULL or IS NOT NULL syntax depending on what you want to achieve. For example, in the address table, the value of address2 for some of the records is NULL while some are empty. Practice filtering by value of address2 using the lines code below:

   1 -- To select records where address2 is NULL, practice with the code in lines 3 and 4 below
   2 
   3 SELECT * FROM address
   4 WHERE address2 IS NULL;
   5
   6 -- If you attempt to use equal "=" sign in the code as shown in lines 8 and 9 below, you will not get an error message but no record will be returned 
   7
   8 SELECT * FROM address
   9 WHERE address2 = NULL;
   10 
   11 -- To select records where address2 does not have NULL value, practice with the code in lines 13 and 14 below. You will get 599 rows of data when you run the code
   12
   13 SELECT * FROM address
   14 WHERE address2 IS NOT NULL;
   15 
   16 -- Similar to code in lines 8 and 9, if you attempt to use not equal "!=" sign in the code as shown in lines 18 and 19 below, you will not get an error message but no record will be returned
   17
   18 SELECT * FROM address
   19 WHERE address2 != NULL;
   20 
   21 -- The code in lines 23 and 24 below will produce the same result as the lines of code in 13 and 14 above.
   22
   23 SELECT * FROM address
   24 WHERE address2 = '';

7. Filtering values using wildcard

   Wildcard characters are used to substitute one or multiple characters in a string. They are used alongside LIKE keyword in a WHERE clause. Some of the most common wildcard characters are %, _, [ ], ^, -.

   • Percentage “%” - means any number of characters. See examples below:

     1 -- To filter customers with first name starting with letter "A". Try the code in lines 3 and 4, you should get 44 records of customers whose first name starts with letter "A"
     2
     3 SELECT * FROM customer
     4 WHERE first_name LIKE 'A%';
     5
     6 -- To select customers with first name ending with "S". Try the code in lines 8 and 9, you should have 26 records returned
     7
     8 SELECT * FROM customer
     9 WHERE first_name LIKE '%S';
     10
     11 -- To select customers with first name starting with "KAT". Try the code in lines 13 and 14, you should have 5 records returned
     12
     13 SELECT * FROM customer
     14 WHERE first_name LIKE 'KAT%';
     15
     17 -- To select customers with first name name ending with "NE". Try the code in lines 19 and 20, you should have 33 records returned
     18
     19 SELECT * FROM customer
     20 WHERE first_name LIKE '%NE';
     21
     22 -- To select customers with "ANNE" somewhere in their first name. Try the code in lines 24 and 25, you should have 6 records returned
     23
     24 SELECT * FROM customer
     25 WHERE first_name LIKE '%ANNE%';

   • Underscore “_” - means any single character. See examples below:

     1 -- To select customers with only 5 characters in their last name, use 5 underscores ("_") to represent each of the characters. Try the code in lines 3 and 4, you should have 114 records returned. If you check you will see that the last names for all the records returned has only 5 characters
     2
     3 SELECT * FROM customer
     4 WHERE last_name LIKE '_____';
     5
     6 -- Note that "_" works for both alphabets and numbers. To select customers with id ranging from 20 to 29. Try the code in lines 8 and 9.
     7
     8 SELECT * FROM customer
     9 WHERE customer_id LIKE '2_';

   • Square brackets “[ ]” - means any single character within the bracket. This can be used with REGEXP keyword to perform more complex wildcard search. See examples below:

     1 -- To select customers with last name starting with "a", "b" or "c". Try the code in lines 3 and 4, this should return 127 records.
     2
     3 SELECT * FROM customer
     4 WHERE last_name REGEXP '^[a-c]';
     5
     6 -- To select customers with last name starting with "a", "g" "f" or "r". Try the code in lines 8 and 9, this should return 128 records.
     7
     8 SELECT * FROM customer
     9 WHERE last_name REGEXP '^[agfr]';

8. Filtering with NOT keyword

   In SQL we can filter for records which do not meet specified criteria using NOT keyword. Recall how we used NOT to negate IS NULL in section f. There are other instances where NOT keyword can be used with other keyword(s) to achieve desirable records from the database. Try the examples below:

   1 -- To provide the schedule of address without the addresses in Alberta, Nagasaki, California and Texas districts. Try the code in lines 3 and 5 
   2
   3 SELECT * FROM address
   4 WHERE district 
   5 NOT IN ('Alberta', 'Nagasaki', 'California', 'Texas');
   6
   7 -- To filter customers whose first name does not start with letter "A". Try the code in lines 8 and 9, you should get 555 records.
   8
   9 SELECT * FROM customer
   10 WHERE first_name NOT LIKE 'A%';
   11
   12 -- To select customers whose last name does not start with "a", "g" "f" or "r", use NOT keyword and REGEXP. Try the code in lines 13 and 14, this should return 471 records.
   13
   14 SELECT * FROM customer
   15 WHERE last_name NOT REGEXP '^[agfr]';

Conclusion

In this tutorial, we have laid a solid foundation for your SQL journey. You have learnt various SQL syntaxes which have equipped you with necessary skills required to do but not limited following:

1. download and install MySQL and MySQL Workbench on your computer

2. query data from the database using MySQL

3. refine output of SQL query to make them more useful

4. insert record into and update records inside tables using MySQL

5. delete record from tables using my SQL

In subsequent tutorials, we shall take the learning further to enable you perform more complex tasks using MySQL DBMS. See you in Part Two of MySQL Made Easy series.

kindly help us to share Tutorialsnote with your families, friends, and colleagues using the share button below. Thank you.