Category Archives: SQL | MySQL

MySQL / SQL – Join 3 tables together and filter the result

I have looked at how to create queries using two tables. What if I need to join 3 tables to create a more complex query?

Let’s say for example that I need to create a query that shows the name of a track, the name of it’s MediaType, and the name of it’s genre. For that I will need to join 3 tables together with the appropriate join columns then I will have to add a filter to only show tracks with a MediaType of “Protected AAC audio file” and a Genre of “Soundtrack”.

As always, I need to get to the sql prompt. For that I need to run the following commands in the terminal:

service mysql start
mysql -u root -p
use Chinook;

Now, I need to take a look at the tables I need to use: Track, MediaType, and Genre.

mysql> show tables;
+-------------------+
| Tables_in_Chinook |
+-------------------+
| Album             |
| Artist            |
| Customer          |
| Employee          |
| Genre             |
| Invoice           |
| InvoiceLine       |
| MediaType         |
| Playlist          |
| PlaylistTrack     |
| Track             |
+-------------------+
11 rows in set (0.00 sec)

The above command lists all the tables from the Chinook database. To display the tables I want the desc command is used.

Continue reading

MySQL / SQL – Join tables, use aliases, and filter the result

Using the same Chinook database as I used in the previous posts I am going to join two tables to create a query. The tables I am going to use this time are the Track table and the Genre table. As usual, I am going to display them first.

To start everything we have to go through the same process:

service mysql start
mysql -u root -p

The above commands will take you to the MySQL prompt where you can use the Chinook database.

show databases;
use Chinook;

Let’s take a look at the Track table:

mysql> desc Track;
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| TrackId      | int(11)       | NO   | PRI | NULL    | auto_increment |
| Name         | varchar(200)  | NO   |     | NULL    |                |
| AlbumId      | int(11)       | YES  | MUL | NULL    |                |
| MediaTypeId  | int(11)       | NO   | MUL | NULL    |                |
| GenreId      | int(11)       | YES  | MUL | NULL    |                |
| Composer     | varchar(220)  | YES  |     | NULL    |                |
| Milliseconds | int(11)       | NO   |     | NULL    |                |
| Bytes        | int(11)       | YES  |     | NULL    |                |
| UnitPrice    | decimal(10,2) | NO   |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

Continue reading

MySQL / SQL – SELECT and filter the result with WHERE clause

The following two exercises where given in the classroom as challenges during my time at Code Institute Dublin.

Challenge 1

The first challenge says:

Select the 'Name' column from the 'MediaType' table.

Expected:

MPEG audio file
Protected AAC audio file
Protected MPEG-4 video file
Purchased AAC audio file
AAC audio file

Before doing anything we have to get to the Chinook database. As we did before, the following commands are necessary:

Start MySQL:

service mysql start

Connect to MySQL:

mysql -u root -p

Use Chinook database:

use Chinook

Continue reading

JOIN clause in SQL

The JOIN clause is extremely important in SQL language. The JOIN clause joins rows from two or more tables. That being said, let’s move straight to the examples and see what exactly the JOIN clause does!

Note:
You need my MySQL installed on your local machine.
You need Chinook database as well since the examples are based on Chinook (see this link for getting started http://dan.saigon.ro/mysql-and-sql-getting-started/).

As usual, start the server, connect to the server, display the databases, select Chinook, and show its tables.

service mysql start
mysql -u root -p

mysql> show databases;
mysql> use Chinook;
mysql> show tables;

Continue reading

Selecting data with SQL | SELECT statement and WHERE clause in SQL

In this post I am going to take a look at the SELECT statement for selecting data and the WHERE clause for filtering the results.

Before everything else, we have to start the MySQL server, connect to it, and select a database. You can do that by running the following commands:

To start the MySQL server:

service mysql start

To connect to the MySQL server:

mysql -u root -p

To select the Chinook database:

use Chinook;

Ok, we selected the Chinook database. Since it is made of tables we want now to see the tables.

Continue reading

MySQL and SQL – Getting started

First of all, to avoid any confusion, SQL it is the standard language for relational database management systems and MySQL is an open-source relational database management system. So, MySQL is a database and SQL is a language.

In order to use SQL language you have to install a relational database. One of the most popular relational databases is MySQL. Let’s install it!

I am on Linux and I installed MySQL via terminal by running the command:

sudo apt install mysql-client-core-5.7

Continue reading