Introduction

Welcome to SQL and thank you so much for picking up my book. I sincerely hope that this book can help you master SQL fast and introduce you to the exciting world of databases.

This book adopts a hands-on approach to learning. As we progress from one chapter to another, we’ll be doing various exercises. You are strongly encouraged to follow along these exercises.

At the end of the book, we’ll also be working on a new project together. This project involves building a SQL database for a sports complex. We’ll learn to build the database, insert data, perform queries, write routines, views, cursors, and more.

Excited and ready to start embarking on our SQL learning journey? Let’s do it!

What is SQL?

Simply stated, SQL stands for Structured Query Language and is a language used to manage data stored in a relational database. This brings us to the next question — What is a database?

A database is a collection of data organized in some format so that the data can be easily accessed, managed and updated. The predominant type of database is a relational database. Relational databases organize data in the form of tables. In addition, they contain queries, views and other elements to help us interact with the data.

In order to manage our database, we need to use a software application known as a database management system (DBMS).

Clear?

So far, we have the following terminologies:

1. SQL is a language
2. A database is a structured collection of data
3. A DBMS is a software that we use to manage our databases

With regards to DBMS, there are a large number of them available. Some are free to download and use while others are not. The most commonly used DBMS include MySQL, Oracle, Microsoft SQL Server and IBM DB2.

Each of these DBMS have their own versions of SQL. While this may sound intimidating, rest assured that all DBMS support the major SQL commands (such as SELECT, UPDATE, DELETE, INSERT) in a similar manner. Hence, if you know one version of SQL, it is very easy to pick up other versions.

In this book, we'll be using MySQL. This is one of the most popular DBMS available. Best of all, it’s free! From this point forward, whenever I mention SQL, I’m referring to the MySQL version.

Getting Ready to Code

In order to start using MySQL, we need to first download and install two applications: MySQL Server and My SQL Workbench.

Installing MySQL applications

Windows

For Windows users, go to https://dev.mysql.com/downloads/windows/installer/.

Scroll down and click on the first “Download” button to download the application. You’ll be directed to another page. Scroll to the bottom of the page and click on “No thanks, just start my download.”

Once you have downloaded the program, double-click on the file and follow the instructions to install the software.

When prompted to choose a setup type, select “Custom” and click “Next”.

You’ll be asked to select the products and features to install.

Under “Available Products”, fully expand “MySQL Servers” by clicking on the + signs. Once fully expanded, click on the last item and click on the green arrow to move this item to the “Products/Features To Be Installed” section.

Next, expand “Applications” and fully expand “MySQL Workbench”. Click on the last item and click on the green arrow to move this item to the “Products/Features To Be Installed” section.

Once you are done, click on “Next” and follow the instructions to continue with the installation. Stick to the default options selected at each stage.

When prompted to enter a password, enter your desired password and jot down the password. You’ll need this password later.

Click on “Next” to continue and complete the installation.

Mac OS

For Mac users, to install MySQL Server, go to https://dev.mysql.com/downloads/mysql/.

Scroll down and click on the first “Download” button to download the application.

Once you do that, you’ll be directed to another page. Scroll to the bottom of the page and click on “No thanks, just start my download.”

Once you have downloaded the program, double-click on the file to unzip it. Next, double-click on the unzipped file and follow the instructions to install the software. At the configuration stage, choose “Use Strong Password Encryption” and click “Next”. Enter your desired password and jot down the password. You’ll need this password later. Ensure that the "Start MySQL Server once the installation is complete" option is selected and click on "Finish" to complete the installation.

Once you are done installing MySQL Server, you need to install another software known as MySQL Workbench. This software provides us with a graphical user interface to make it easier for us to interact with MySQL. To download MySQL Workbench, go to https://dev.mysql.com/downloads/workbench/.

Click on the first “Download” button to download the application. Once again, you’ll be directed to another page. Scroll to the bottom of the page and click on “No thanks, just start my download.” to download the program. Double-click on the downloaded file and follow the instructions to install it after downloading.

Launching MySQL Workbench

Once you have installed the necessary applications, we are ready to do some coding.

First, launch MySQL Workbench.

You’ll get the screen below:

Click on the grey rectangle under "MySQL Connections" (named "Local instance 3306" in the image above). You’ll be prompted for a password. Enter the password that you keyed in previously when you installed MySQL Server and select “Save password in vault” (or “Save password in keychain” for Mac users). Next, press OK to proceed. If all goes well, you should be directed to the screen below:

The main area is a text editor where we’ll be entering our SQL commands. The window below is the output window (refer to screenshot above).

Got that?

Great!

Writing our first MySQL code

Now, we are ready to get our feet wet and write some SQL code.

Type the following lines into the text editor (for the first line, be sure to add a space after the two hyphens):

You should notice that the first line is in gray while the word SELECT is in blue and 'Hello World' and 'MySQL is fun!' are in green.

This is the software’s way of making our code easier to read. Different words serve different purposes in our program, hence they are displayed using different colors. We’ll go into more details in later chapters.

There are two ways to run the SQL commands that we wrote.

The first is to select all the code that we want to run and click on the “Execute Selected” button (the button with a lightning bolt). This button should normally be located to the right of the “Save” button (refer to the previous screenshot).

This will execute all the code that is selected. You’ll see a new panel called “Result Grid”, with two tabs as shown below:

These two tabs give the results of the last two lines of code that we wrote (i.e. the two lines that start with the word SELECT).

The first line of code that we wrote does not give any result as it is a comment. We’ll talk about comments in the next section.

Besides running all the code at one go, you can also choose to execute them one by one. To do that, simply position your cursor on the line that you want to execute and click on the “Execute Single” button to run that statement. The “Execute Single” button shows a lightning bolt and a cursor and is located to the right of the “Execute Selected” button.

For instance, if you place your cursor as shown in the image below

and click on the “Execute Single” button, you’ll get a single tab in the “Result Grid” that displays the message Hello World.

Comments

Now that we know how to execute SQL statements, we are ready to start learning some SQL commands. However, before we do that, there’s more one concept that I’ll like to cover - comments.

As mentioned previously, the first line in the code that we wrote (-- Using SELECT to display messages) is a comment.

Comments are written to make our code more readable for other programmers. They are meant for humans only and are ignored by the DBMS.

To add comments to our program, we type two hyphens, followed by a space, as demonstrated in the example above.

Alternatively, we can also use the # symbol as shown below:

Last, but not least, if we want to type multiple lines comments, we can use the /*...*/ symbols: