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

The challenge asks to select the “Name” column from the MediaType table. So, let’s see what is there in the MediaType table.

desc MediaType

We should get something like shown below:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| MediaTypeId | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name        | varchar(120) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

All right! Let’s select just the Name column from the MediaType table.

mysql> SELECT Name FROM MediaType;
+-----------------------------+
| Name                        |
+-----------------------------+
| MPEG audio file             |
| Protected AAC audio file    |
| Protected MPEG-4 video file |
| Purchased AAC audio file    |
| AAC audio file              |
+-----------------------------+
5 rows in set (0.00 sec)

This is it! We got exactly what the challenge says we should expect!

Challenge 2

The second challenge says:

Select the 'FirstName', 'LastName' and 'Title' Columns from the 'Employee' Table, filtering the results to only those with a Title of 'IT Staff'.

Expected:
Robert King IT Staff
Laura Callahan IT Staff

So, first thing first I always check the table. In this case it is the Employee table.

mysql> desc Employee;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| EmployeeId | int(11)     | NO   | PRI | NULL    | auto_increment |
| LastName   | varchar(20) | NO   |     | NULL    |                |
| FirstName  | varchar(20) | NO   |     | NULL    |                |
| Title      | varchar(30) | YES  |     | NULL    |                |
| ReportsTo  | int(11)     | YES  | MUL | NULL    |                |
| BirthDate  | datetime    | YES  |     | NULL    |                |
| HireDate   | datetime    | YES  |     | NULL    |                |
| Address    | varchar(70) | YES  |     | NULL    |                |
| City       | varchar(40) | YES  |     | NULL    |                |
| State      | varchar(40) | YES  |     | NULL    |                |
| Country    | varchar(40) | YES  |     | NULL    |                |
| PostalCode | varchar(10) | YES  |     | NULL    |                |
| Phone      | varchar(24) | YES  |     | NULL    |                |
| Fax        | varchar(24) | YES  |     | NULL    |                |
| Email      | varchar(60) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
15 rows in set (0.01 sec)

We have many columns there but we need only three of them: FirstName, Last Name, and Title. Let’s do it!

mysql> SELECT FirstName, LastName, Title FROM Employee;
+-----------+----------+---------------------+
| FirstName | LastName | Title               |
+-----------+----------+---------------------+
| Andrew    | Adams    | General Manager     |
| Nancy     | Edwards  | Sales Manager       |
| Jane      | Peacock  | Sales Support Agent |
| Margaret  | Park     | Sales Support Agent |
| Steve     | Johnson  | Sales Support Agent |
| Michael   | Mitchell | IT Manager          |
| Robert    | King     | IT Staff            |
| Laura     | Callahan | IT Staff            |
+-----------+----------+---------------------+
8 rows in set (0.00 sec)

Still not good enough because we need to filter the result and show only those with a Title of “IT staff”. Filtering in SQL is done with the WHERE clause.

mysql> SELECT FirstName, LastName, Title FROM Employee
    -> WHERE Title = 'IT Staff';
+-----------+----------+----------+
| FirstName | LastName | Title    |
+-----------+----------+----------+
| Robert    | King     | IT Staff |
| Laura     | Callahan | IT Staff |
+-----------+----------+----------+
2 rows in set (0.00 sec)

This is it! Selecting and filtering the result in SQL!

Leave a Reply