SQL Problem Solving

 Today, I will solve SQL problems to get ready for the interview with Yardi Systems, Inc. 

The first thing I want to do is to create a songs table named songs and have properties id (primary key), name (string cannot be null), length (cannot be null, length of song in minutes, float) and album_id (cannot be null, referencing albums table that cannot be null, foreign key). 

We write out 

CREATE TABLE songs (

    id INT NOT NULL AUTO_INCREMENT,

    name VARCHAR(255) NOT NULL,

    length  FLOAT NOT NULL,

    album_id INT NOT NULL,

    PRIMARY KEY (id),

    FOREIGN KEY(album_id) REFERENCES albums(id)

); 

and we create the SONGS table as a result.

Here's an example of inserting values into a table.

INSERT INTO albums(id,name,release_year,band_id) VALUES (1,'Tiara',2018,1);

INSERT INTO albums(id,name,release_year,band_id) VALUES (2,'The Great Escape',2010,1);

INSERT INTO albums(id,name,release_year,band_id) VALUES (3,'Mercy Falls',2008,1);

Now the second exercise is to select ONLY the names of all of the bands, and change the name of the column to be BAND_NAME. We can just do a simple select statement, and since we only want the name column, we can select the name column from the bands table.

SELECT name AS 'Band Name' 

From bands;

 

The next task that we want to perform is to select the oldest album, making sure to only return one result from the query.  

We do the following:

SELECT * from albums

WHERE release_year IS NOT NULL

ORDER BY release_year

LIMIT1;

and here's to get the most recent (hint: make it descending):

SELECT * from albums

WHERE release_year IS NOT NULL

ORDER BY release_year DESC

LIMIT1;

Next, we want to get all bands with albums.

so we do

SELECT bands.name AS 'BAND NAME'

FROM bands

JOIN albums ON bands.id = albums.band_id

This is getting albums with a band id that is stored in the albums table. 

Since we're doing an INNER JOIN here, it's only going to return bands that actually have albums.

To have individual names, add a DISTINCT.

SELECT DISTINCT bands.name AS 'BAND NAME'

FROM bands

JOIN albums ON bands.id = albums.band_id

Now, we want to get all the bands with no albums.

We perform a LEFT JOIN to return bands that do not have any album records. We can use aggregate functions to return which parts of the functions have albums and which ones do not. We get the bands that have no albums as a result. 

SELECT DISTINCT bands.name AS 'BAND NAME'

FROM bands

LEFT JOIN albums ON bands.id = albums.band_id

GROUP BY albums.band_id

HAVING COUNT(albums.id) = 0;

Now, we want to get the longest album in the entire database with the name, the year, and the duration.

We know we will be selecting things from the album table. We have to separate all of the selected rows through commas. 

SELECT 

    albums.name AS Name,

    albums.release_year AS 'Release Year',

    SUM(songs.length) AS 'Duration'

FROM albums  

JOIN songs ON albums.id = songs.album_id

GROUP BY songs.album_id

ORDER BY Duration DESC

LIMIT 1;

This would return the longest album in the list. 

Now, the next problem would be updating the release year for the album that does not have a release year for the database. 

We MUST specify a WHERE clause otherwise it will update all the albums with a particular year.

SELECT * FROM albums

WHERE release_year IS NULL;

UPDATE albums

SET release_year = 1986

WHERE id = 4;

Now we want to insert data into the database. 

Now we first write the INSERT statement for the band

INSERT INTO bands(name)

VALUES('Haken');

In order to check the band was inserted we do 

Select id FROM bands

ORDER by id DESC

LIMIT 1; 

This will get the band with the highest id, which is the most recent band added. 

To insert album we do the columns

INSERT INTO ALBUMS(name, release_year, band_id) 

VALUES ('Vector', 2018, 8);

To find the album we perform

Select id FROM albums

ORDER by id DESC

LIMIT 1; 

Now we want to delete the band and album, so we have to reference the album first since it references te band

DELETE FROM albums 

WHERE id = 19;

DELETE FROM bands 

WHERE id = 8;

SELECT * FROM bands;

SELECT * FROM albums;

Now we want to get the averaged length of all the songs. To do this, we take advantage of aggregate functions. 

SELECT AVG(length) as 'Average Song Duration'

FROM songs;

Now we want to select the longest song of each album, which requires us to do a little bit of joining and grouping. 

We group songs be longest id so that each of the songs is the longest length.

SELECT

    albums.name AS 'Album‘,

    album.release_year AS 'Release Year',

    MAX(songs.length) AS 'Duration'

FROM albums

JOIN songs on albums.id = songs.album_id

GROUP BY songs.album_id;

The last problem is to get the number of songs for each band, and this is tough because it requires us to do multiple joins instead of just one. 

First, to get to the songs, we need to join bands to albums and albums to songs. All the songs will be grouped by the band id on the albums table. 

SELECT 

    bands.name AS 'Band',

    COUNT(songs.id) AS 'Number of Songs'

FROM bands

JOIN albums ON bands.id == albums.band_id

JOIN songs ON albums.id = songs.album_id

GROUP BY albums.band_id;