Author Archives: ddnro

About ddnro

I am Dan, a web developer based in Dublin, Ireland.

SQL - Join 3 tables together and filter the result

006 – 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

SQL - Join tables, use aliases, and filter the result

005 – 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

Select and filter the result with WHERE in SQL

004 – 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

SQL JOIN clause

003 – JOIN clause in SQL

The JOIN clause is extremely important in SQL language. It joins rows from two or more tables.

Note:
You need my MySQL installed on your local machine.
You need Chinook database as well since the examples are based on Chinook.

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

What kind of website do you need

This article helps you to decide what website you need, it tells you what options you have when it comes to building a website and some of the costs that you may expect.

You considered having your own website and you don’t know what kind of website you need, you don’t even know that there are several kinds of websites and building one or another vary greatly.

There are many kinds of websites and regardless if you build the website yourself or hire someone to do it for you, you still need to know what your options are when it comes to building your website.

You also need to know what you can do with that website since they are so different from each other and the functionality is what makes them so different.

Continue reading

SELECT statement and WHERE clause in SQL

002 – Selecting data with SQL – SELECT statement and WHERE clause in SQL

In this post we are 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

Install sql/mysql database

001 – 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