Tag: Databases

Hello everybody,

This is Michael, and today’s post will be about creating views. This will be the last post in this series of MySQL lessons, but I’ll likely have more MySQL lessons and/or analyses in the future, so keep following my posts.

Anyway, views are basically virtual tables created from queries that allow you to query the data within them. Here’s an example:

Here, I am building a view from the query, which consists of the block of code between the parentheses. In the query, I selected the foreign key Album_idAlbum and the fields Ceremony and Won/Nominated from the Awards table. I narrowed down the criteria so that Album_idAlbum with keys greater than 30 and less than 58 will be displayed. In other words, I only want the query-and in turn, the view-to include awards considerations for albums released in the 2010s, hence the view’s name 2010s Album Awards.

• The syntax for views is ALWAYS:
  • CREATE VIEW (name of view) AS (query)
• Parentheses aren’t necessary when creating views. I just use them to improve readability of the code.

If you press the refresh button on the “Schemas” tab, the view will appear.

You can even query the data in the view. Here’s an example:

Using every field from the view, I asked the query to only display the Album_idAlbum of any album that was considered (whether they won or were just nominated) for a Teen Choice Award, which only applied to one album in this case (Usher’s 2010 album Raymond v. Raymond).

Let’s try another example. First we’ll create a view consisting of any debut albums listed on the database (WHERE Album Number = 1 refers to debut albums), along with the corresponding [Album] Release Date and Singer Name (referring to which singer/group created the album):

• JOINs are perfectly fine to include in views-honestly anything you can include in a normal query you can include in a view.

Then we hit the refresh button on the “Schema” tab to see if the view was created (which it was).

And finally, let’s query this view:

In this query, I selected the fields [Album] Name and Singer Name from the view and asked the query to only display albums WHERE MONTH(Release Date) = 1 OR MONTH(Release Date) = 2, which refers to albums released in January or February. As you can see, two albums fit the criteria.

Thanks for reading,

Michael

 

Hello everybody,

My name is Michael, and today’s post will be a continuation of the previous post’s lesson on JOINs. As I mentioned in the previous post, JOINs are queries that join columns from different tables together based on a common column in each table (or in the case of SELF-JOINs, connected columns from the same table-more on that later in this post).

The first type of JOIN I will cover in this post is FULL JOIN, which returns all records from both the left-linked and right-linked tables and fills in NULL for missing matches from either table. Here’s an example:

I selected the fields Name and Release Date from the Album table and the fields Ceremony and Won/Nominated from the Awards table (which I FULL JOINed to the Album table). I then linked the two tables using the idAlbum/Album_idAlbum primary/foreign key. Had there been an album that wasn’t in consideration for an award (for instance Grace VanderWaal’s Just The Beginning), then the Ceremony and Won/Nominated fields corresponding to that album would NULL (Name and Release Dat will still be displayed).

With FULL JOINs, keep in mind that if you don’t filter out results, you could end up with a large result set (this query returned 216 rows of data). Here’s the same FULL JOIN query except with a WHERE clause to filter out results:

This query is similar to the previous one, except that it only shows the rap albums that were considered for awards (and as a result, show only 41 rows of data as opposed to the 216 rows returned from the previous query).

The next type of join I will cover is the SELF JOIN, which basically joins a table to itself. Here’s an example:

I selected the fields Singer Name, Age, and Member of from the Singer table, but there’s a catch. See, because I’m joining a table to itself, I need to give aliases to each of the instances of the table in this query. I went with S* for the Singer table before the JOIN clause and B** for the Singer table after the JOIN clause. As you can see, I had to include the aliases before each field name (S for Singer Name and Age and B for Member of). In my WHERE clause, notice how I mention Member of twice in the line WHERE B.Member of IS NOT NULL AND S.Member of IS NOT NULL. I did this so that only the members of a band are listed alongside the corresponding band/group; when I only included WHERE B.Member of IS NOT NULL, the band name would display alongside itself (e.g. Imagine Dragons was listed as a member of Imagine Dragons).

*for Singer

**for Band (or Group)

Now let’s filter the results further:

It’s the same query as above, except with the addition of the AND S.Death IS NOT NULL, which asks the query to display any deceased singers part of groups. As you can see, only 1 singer in the database meets this criteria (Linkin Park’s Chester Bennington, who died on July 20, 2017).

Let’s try a different filter to the query:

This is similar to the first SELF JOIN query I did, except the filter asks the query to only display band singers under 40 years old (hence the S.Age < 40 line), of which there are 10.

The last JOIN I will cover in this post is the UNION query, which, even though it does not have JOIN in its name, still serves a function similar to JOINs (except entire queries are being joined together, not just tables).

Here’s an example of a UNION query:

In each individual query, I selected the fields Track Name and Track Duration from the Tracks table and joined the two queries together with a UNION clause. The one difference between the two queries is their WHERE clauses; the top query retrieves the name of the longest song (Empire of the Clouds by Iron Maiden at a whopping 18 minutes and 1 second) and the bottom query retrieves the name of the shortest song (Intro by 50 Cent at a mere 6 seconds).

Some things to keep in mind with UNION queries include:

• UNIONs only work with the same number of columns in both queries (I selected two columns in both the top and bottom queries)
• They work best when the columns selected in each query-along with any aliases- and the order in which the columns/aliases are listed are identical. Though there should be at least one difference between the two queries, as I have in my query (the WHERE clause has MAX for the top query and MIN for the bottom one).

Here’s another slightly more complex UNION query

In each query, I selected the foreign key Album_idAlbum and the aggregate function COUNT(Album_idAlbum)-using the alias Awards Considerations-from the Awards table. I also remember to GROUP BY the same thing in each table (the Album_idAlbum foreign key). The one difference between each query is the WHERE clause, as I selected Album_idAlbum 34 for the top query and Album_idAlbum 48 for the bottom one (both are rock albums, with 34 being Mylo Xyloto by Coldplay and 48 being Smoke+Mirrors by Imagine Dragons). As you can see, Mylo Xyloto was considered for 8 awards while Smoke+Mirrors was only considered for 1 award.

Before I go, here’s a spreadsheet with the modified Singer table (couldn’t save SQL files on WordPress)-Singer table modified.

Thanks for reading,

Michael

Hello everybody,

It’s Michael, and this post (and the next) will be covering joins in MySQL. Joins are essentially queries that are meant to retrieve data from several tables rather than just a single table. I’ll take two posts to cover joins because there are several different types of joins.

But first, here’s a slight modification to the database, which I felt would be appropriate when learning about joins:

The two new fields included are:

• Singer_idSinger-Ok, this is more of a foreign key, but it indicates the band/group the singer belongs to (e.g. Dan Reynolds is a part of the band Imagine Dragons). For individual singers, the value in this field will be the same as the value in the idSinger primary key.
• Member of-What band (if any) a singer is part of. If the singer isn’t part of a band, then this field will be NULL.

The arrow on top of the singer table indicates a unary one-to-many relationship. Unary basically means the table relates to itself (which is perfectly fine by MySQL standards); the one-to-many in this case means that a band must have MANY (at least 2) singers while each singer must belong to only ONE band.

This type of relationship works best for SELF JOIN queries, which are queries that join a table to itself. I’ll cover them more in the next post.

The first type of join I will focus on is an INNER JOIN. Here’s a query using INNER JOIN:

INNER JOIN queries basically join columns from different tables using values common to both tables (which are usually foreign/primary keys); results are returned only if a row in both tables matches the JOIN condition. In this case; the common column is Singer_idSinger-the Singer and Album tables both have this column.

• You don’t have to display the common column in the query results; just remember to include it in the ON clause. This goes for all JOIN queries.

I first selected the field Singer Name from the Singer table and the fields Name and Release Date from the Album table (which is INNER JOINed to the Singer table). I then linked both tables through the Singer_idSinger primary/foreign key (which is the common column among both tables). I finally narrowed down the criteria to only include albums WHERE Release Date > Death so that only posthumous albums (along with corresponding singer name and release dates) are displayed.

• INNER JOIN queries will work the same without the word INNER, as seen below:

The next type of JOIN I will cover is a LEFT JOIN, which basically goes:

• SELECT (columns) FROM (table A) LEFT JOIN (table B) ON (table A.column) = (table B.column)

Like INNER JOIN queries, LEFT JOIN queries link tables based on common columns. The difference between these two types of queries are that INNER JOIN queries retrieve results that have matching values in both tables, while LEFT JOIN queries retrieve all results from the left-linked table (table A) and any matching results from the right-linked table (table B).  If there are no matching results from the right-linked table, NULL will be displayed on any rows connected to the right-linked table (this is not the case with INNER JOINS).

Here’s an example of a LEFT JOIN query:

In this query, I am selecting the fields Track Name from the Tracks table and Featured Artist Name from the Featured Artist table (which is LEFT JOINed to the Tracks table). I filtered out the results so that only tracks WHERE Album_idAlbum = 44  (44 is the primary key for Linkin Park’s The Hunting Party if you were wondering) are displayed. The query will display all the records from the left-linked table (in other words, every track from The Hunting Party) along with matching records from the right-linked table (meaning if there are any guest artists on a track). For tracks without guest artists, NULL is displayed on the right side rows.

The last of the joins I will cover is the RIGHT JOIN, which is basically the opposite of LEFT JOIN because the query retrieves all results from the right-linked table (table B) and any matching results from the left-linked table (table A). Should there be no matching results in the left-linked table, NULL will be displayed on any rows connected to the left-linked table (so basically the opposite of what happens in a LEFT JOIN query).

Here’s an example of a RIGHT JOIN query:

In this query, I selected the field Name from the Album table and the field Won/Nominated from the Awards table (which is RIGHT JOINed to the Album table). I then used the idAlbum/Album_idAlbum (they’re the same thing) primary/foreign keys to link the two tables. I also narrowed down the criteria so as to only display albums WHERE Won/Nominated = "Won" AND Ceremony = "Grammys" (referring to album that won Grammys). The query will display all results from the right-linked table (any Grammy wins) and any matching records from the left-linked table (the names of albums that won Grammys).

• For every result from the right-linked table (Awards), there are matching results from the left-linked table (Album). Had this not been the case, you would be seeing NULL in rows linked to the Name column.

Thanks for reading, and be sure to check out the next post with more JOINs.

Michael

 

 

Hello everybody,

It’s Michael, and today’s post will be about subqueries. Subqueries are basically queries-within-queries; think mini-queries within a larger query, and you’ve got the idea. The purpose of subqueries is to retrieve data for the main query that will be used as a condition to filter the query output. You can usually find subqueries in three types of clauses (SELECT, FROM, and WHERE) and inside other subqueries.

Here’s a simple example with a WHERE clause:

In this query, I selected the fields Singer Name and Death (referring to a singer’s date of death) from the Singer table. The line WHERE Death = (SELECT MAX(Death) FROM mydb.Singer) asks the query to select and display the maximum, or most recent, date of death listed in the database (along with displaying the corresponding singer name). As you can see, the most recent date of death in the database is June 18, 2018, which corresponds to the rapper XXXTentacion.

• Even though MAX is in the subquery, GROUP BY is not necessary. Don’t worry about GROUP BY when including an aggregate function (COUNT, MAX, MIN, etc.) in your subquery.

Here’s a slightly more complicated example:

In this query, I selected the Singer Name, Age, and Gender fields from the Singer table. The subquery in Line 2 asks the query to only display records for singers within a certain age range (18-36, essentially any of the millennials). Line 3 simply orders the results from oldest to youngest singer.

Let’s try another subquery, except this time using the SELECT clause:

The subquery in the select clause-SELECT MAX(Track Duration) FROM mydb.Tracks-tells the main query to display the length of the longest song listed on this table as a column; the result is 18 minutes and 1 second. For those wondering, the song “Empire of the Clouds” from Iron Maiden’s The Book of Souls is the longest song listed on this table.

• SELECT DISTINCT ensures that the maximum track duration only appears once, otherwise it will appear 800 times (this happened when I first ran the query without the DISTINCT keyword).

Here’s a subquery with the FROM clause:

In this query, I am trying to find out the average song length for songs on each of the 57 albums listed on the database. The subquery `SELECT Album_idAlbum, SEC_TO_TIME(AVG(Track Duration)) AS Average Song Length FROM mydb.Tracks
GROUP BY Album_idAlbum ORDER BY Average Song Length ASC` basically functions as the whole query, as SELECT * asks the query to select everything that matches the criteria listed in the FROM clause subquery.

In the subquery, I selected the Album_idAlbum foreign key and the (average) Track Duration field from the Tracks Table. I then group the results by Album and order the results by Average Song Length in ascending order (from albums with the shortest average track length to albums with the longest average track length).

• SEC_TO_TIME displays the average track for each album in hour:minute:second form, because without this function the time displays as a decimal.

One last thing I wanted to mention with regards to subqueries is that it’s possible to have subqueries-within-subqueries. In these cases, the innermost subquery will run first, followed by the outer subquery, and finally the main query. Here’s an example:

In this query, I am trying to find out which rap album has the longest duration. I first select the Name, Release Date, and Duration fields from the Album table. The inner subquery-SELECT Genre FROM mydb.Album WHERE Genre IN (RAP)-retrieves all the rap albums from the database. The outer subquery-SELECT MAX(Duration) FROM mydb.Album WHERE Genre IN (inner subquery)-retrieves the rap album with the longest duration. WHERE Duration = (outer subquery(inner subquery)) tells the query to display the rap album (and corresponding release date) with the longest duration (which is Tupac’s album Until The End of Time).

Thanks for reading,

Michael

 

 

 

 

 

Hello everybody,

It’s Michael, and today’s post will focus on the WHERE clause in MySQL. For those that don’t know, the WHERE clause basically acts like an IF statement in the sense that it tells the query to only selects rows WHERE certain criteria are met.

OK, so I know I mentioned in MySQL Lesson 4 that the HAVING clause also acts like an if statement. Well, here are some differences between HAVING and WHERE:

• HAVING is used to filter grouped records (think of the queries I posted in MySQL Lesson 4) while WHERE is used to filter individual records
• HAVING always goes with GROUP BY while WHERE never goes with GROUP BY; this is because WHERE does not filter aggregate records while HAVING does.

Now, let’s try out a simple WHERE query

In this query, I selected the fields Singer Name, Age, and Gender from the Singer table. The WHERE Age < 30 line asks the query to select only those singers who are under 30 years old, of which there are 5 (Taylor Swift, Soulja Boy, Lorde, Ariana Grande, and Grace VanderWaal).

Let’s try another WHERE query, this time with a logical operator (more on that below)

In this query, I selected the Ceremony, Won/Nominated fields and the Album_idAlbum foreign key from the Awards table. The WHERE Album_idAlbum = 29 OR Album_idAlbum = 53 line asks the query to only display the results corresponding to awards that albums FK29* OR FK53* were considered for  (both FK29 and FK53 are Jay-Z albums, with FK29 being The Blueprint 3 and FK53 being 4:44). As you can see, The Blueprint 3 has two wins out of 3 nominations while 4:44 has 7 nominations.

*FK means having the foreign key, so FK29 means “album with foreign key 29” and FK53 means “album with foreign key 53”

For those wondering what the point of logical operators is, they basically supplement the WHERE clause by setting specific criteria to filter out records. Here are the three most common:

• AND-select records matching (condition 1) AND (condition 2)
• OR-select records matching either (condition 1) OR (condition 2)
• NOT-do not select records matching (condition 1)

You can use all three in the same WHERE statement, just remember that, barring parentheses, NOT will be the first thing read by MySQL, then AND, followed by OR. Portions of the WHERE statement in parentheses are always read first, similar to the order of operations (remember PEMDAS?)

Let’s do another WHERE query, this time working with dates

In this query, I selected the Name and Release Date fields from the Album table. The WHERE MONTH(Release Date) = 12 OR MONTH(Release Date) = 1 OR MONTH(Release Date) = 2 line asks the query to only display the names of albums that were released in the winter months of December, January, or February (represented by 12, 1, and 2, respectively). As you can see, 6 albums meet the criteria

When dealing with dates in a WHERE query, you can either include the whole date (eg. 2011-01-01) or just the YEAR, MONTH, or DAY portion, The correct syntax would be YEAR(field), MONTH(field) or DAY(field), depending on what portion of the date you want to include in your query.

• If you’re dealing with MONTH, remember that months are always represented by the numbers 1-12 (January through December, respectively). Typing in the name of the month usually won’t work for querying.

Sometimes, including the whole date or part of the date won’t make a difference in the results displayed, as shown by these two queries

Each query selects the Name and Release Date fields from the Album table and sorts the results by release date in descending order (starting from the most recently album and then going further back in time). But as you can see, the WHERE line is different for each query. The first query displays WHERE Release Date > 2009-12-31 while the second query displays WHERE YEAR(Release Date) >= 2010. These queries are examples off how including part of the date or the whole date doesn’t make a difference in the output, as both queries select albums released in the 2010s.

• Keep in mind that there are some times that selecting the whole date or part of the date does make a difference in query output.

Now let’s try another WHERE query, this time using string patterns.

In this query, I selected the fields Singer Name and Birthplace from the Singer table. The WHERE Birthplace LIKE '%US' line asks the query only to display the names (and birthplaces) of singers who were born in the US.

You might be wondering what the ‘%US’ means. It basically tells the query to select rows in the Birthplace field that end in ‘US’. Some possible variations of this string pattern include:

• ‘US%’-select all birthplaces that start with ‘US’
• ‘%US%’-select all birthplaces that have ‘US’ anywhere in the name
• ‘_US%’-select all birthplaces that have the letters U and S in the second and third positions, respectively
• ‘US_%_%_%’-select all birthplaces that start with ‘US’ and are at least 4 characters long
• ‘U%S’-select all birthplaces that start with U and end with S

Remember to always use single quotes (‘ ‘) not back-ticks (“) when dealing with string patterns.

One more thing I wanted to note that I mentioned earlier in the differences between the WHERE and HAVING clauses. Now I know I said that the WHERE clause deals with individual records while the HAVING clause deals with grouped records. One thing I did not mention is that the WHERE and HAVING clauses can be used in the same query. Here’s an example:

In this query I selected the field Featured Artist Name from the Featured Artist table. I also added COUNT(Featured Artist Name) in order to display the amount of times each singer appears on the featured artist table. The WHERE Featured Artist Age < 40 line asks the query only to display the names of featured artists under the age of 40. Lines 3-5 of the query group the results by Featured Artist Name and add the additional criteria of only displaying the names of featured artists that appear at least twice on the table (in addition to being under 40 years old); the results are then ordered based on number of appearances (from most to least).

Here are some things you should know about joint WHERE/HAVING queries:

• WHERE always comes before HAVING in a query; this is because WHERE deals with data before it has been aggregated and HAVING deals with data after it has been aggregated
• On a similar note, do not forget the GROUP BY clause in the query, as that must always be included before the HAVING clause
  • This is because the data has to be grouped before it can be filtered further

Thanks for reading,

Michael

Hello everybody,

Now that I’ve run through the basics of querying, I figured the next functions to work with should be the GROUP BY, HAVING, and COUNT functions. I will cover all three of these functions in this post because I feel they perfectly compliment one another (and you can use all three in the same query).

I’ll begin with a simple GROUP BY query.

As you can see here, I selected the Genre column from the Album table and asked the query to group by Genre. What the query does is group each of the rows by genre; multiple rows with the same genre are grouped together.

Here’s another GROUP BY query, this time using the Singer table.

The results of this query are grouped by Gender (F for female, M for male, and NULL for bands). Multiple rows with the same gender are grouped together.

Now let’s run the first query again with the addition of the COUNT function.

This basically does the same thing as the first query, except it displays how many times each genre appears in the table (that’s the point of including COUNT(Genre)). As you can see, Pop appears the most times (16 of the 57 albums), followed by Rap (15 albums), and Rock (10 albums).

Here’s the second query, also with the addition of the COUNT function

Just like the second query, the results are grouped by gender, except this time the query also counts how many times each gender appears in the table. In other words, this query shows how many female and male singers are listed on this table (17 and 21 respectively). As for why 0 is listed for NULL, MySQL didn’t count the NULL rows in the Gender column (in case you were wondering, there are 10 NULL columns-or 10 bands-listed on this table).

Now let’s run the third query again, this time with the addition of the HAVING function.

The HAVING function is essentially an if statement; it tells the query only select those results that meet certain criteria. In this case, the query will only select genres with COUNT(Genre) greater than 5, meaning that the genre appears more than 5 times on the table. In this case, pop, rap, and rock meet the criteria (appearing 16, 15, and 10 times respectively)

• Syntax tip to keep in mind-HAVING always comes after GROUP BY.

Let’s run the fourth query again, this time with the addition of the COUNT function.

Just like the query above, only results that meet a certain criteria are selected. In this case, we only want the results where Gender = ‘F’ (meaning how many female singers are listed on this table). As you can see, there are 17 female singers listed.

• HAVING doesn’t just need to include numerical criteria (such as COUNT(column_name)) but can also include non-numerical criteria as well (as can be seen above)

By the way, here’s the Excel workbook with all of the spreadsheets that contain the information used in this database-MySQL database (blog). I tried uploading the SQL file but was unable to do so.

Thanks for reading,

Michael

 

Hello everybody,

The MySQL database I wrote for MySQL Lesson 1 & MySQL Lesson 2 is finally ready for querying (and this series of querying posts). Querying, for those that don’t know, is basically asking the database a question and using SQL to find the answer.

First off, let’s start with the most basic query-SELECT * FROM (table). The asterisk means “everything”, as in “Select everything from a certain table”.

In this case, I wrote SELECT * FROM mydb.Singer, which means I would select every record and every column from the Singer table (remember mydb is the name of the schema).

Let’s try that same query again, except with the album table.

Just as with the previous query, every record and every column are selected from this table.

Now, once again using the album table, here’s a query where only certain columns are selected.

For this query, I selected the columns name, duration, and release date, which means my query will only display every record for these three columns.

• At first my query kept giving me a syntax error message for the Release Date field, so I just put the name of the field in back-ticks; these errors usually happen with fields that have spaces in their names (like Release Date) because MySQL isn’t sure whether the word Date is part of the field name or a separate data type. Back-ticks usually solve this issue.

As you can see, I selected the Singer Name, Age, and Gender columns from the Singer table; thus, only the records for those three columns will be displayed.

Thanks for reading,

Michael