7/7: is this code correct?


#1

Problem: Use any join you like to combine the albums and tracks table. Rename the album_id column to Albums.

My answer (I think):

SELECT * FROM tracks
JOIN albums
ON tracks.id = albums.id
SELECT tracks.album_id AS 'Albums';

Is this correct?


#2

No, you have to think of organizing your SQL in the three main sections (I'm generalizing here)

SELECT ...
FROM ...
WHERE ...


The SELECT portion contains the output columns you would like. So in the text of the problem it states that it wants you to select the albums and tracks table and to specifically use an alias for the album_id column. That should all be in the top SELECT portion. You should think about explicitly listing your columns and avoid using the *.

What I mean is write this:
SELECT id, name
FROM artists;

rather than
SELECT *
FROM artists;

The reason why is that when you join two tables you are dealing with one or more columns that are the same. If you write the '*' you are stating the common fields twice.


The FROM you wrote looks like it might not be correct. I believe these are the tables you are working with:


I find it always best to think of what it is that makes one table reference another.

So in this particular exercise you need to join the TRACKS table with the ALBUMS table. Looking at the TRACKS table I see that there is a field called album_id. The ALBUMS table has a field called id. Those are the two fields that mean the same thing. In your code you tried joining the tracks.id = albums.id, which are not the same thing. So if you were to imagine drawing a line between the TRACKS and ALBUMS tables make sure to draw a line between the two fields that are the same thing.

What you need to do is join the two tables with:
tracks.album_id = albums.id


In this particular case you are not being asked to filter or meet certain conditions so there is no need for the WHERE clause.


I will leave you try to correct this on your own. Message back in here if you run into further issues or need more feedback. I'll happily help.


#3

Am I correct?

SELECT tracks.id, tracks.title, tracks.album_id AS 'albums' FROM tracks JOIN albums ON tracks.album_id = albums.id ;


#4

No, if you do that, you only get results from the tracks table, so the JOIN is useless as you are only using one table in the end, for the results shown. You should also use some data from the albums table in your SELECT. For example:

SELECT tracks.id, tracks.title, tracks.album_id AS 'albums', albums.name, albums.id FROM tracks JOIN albums ON tracks.album_id = albums.id


#5

This works:

CREATE TABLE tracks (id INTEGER PRIMARY KEY, title TEXT, album_ID INTEGER);

INSERT INTO tracks(id, title, album_id) VALUES (1, 'Smooth Criminal', 8);

INSERT INTO tracks(id, title, album_id) VALUES (2, 'Hey Jude', 5);

SELECT name, artist_id, year, album_id AS 'Albums' FROM albums JOIN tracks ON tracks.album_id = albums.id ORDER BY album_id;

but if I include (ID) after select it will not work.


#6

The following queries are working fine. you can try it.
1.Create a table named tracks with an id, title, and album_id column. The id column should be the PRIMARY KEY.
2."Smooth Criminal" is a track from Michael Jackson's "Bad" album. Add this track to the database.
3.Add more tracks to the database.
4.Combine the albums and tracks tables using an INNER JOIN. Order the query by album_id.
5.Combine the albums and artists table using a LEFT OUTER JOIN. Let albums be the left table.
6.Combine the albums and artists table using a LEFT OUTER JOIN. Let artists be the left table.
7.Use any join you like to combine the albums and tracks table. Rename the album_id column to Albums.

Answers:

1.CREATE TABLE tracks(id INTEGER PRIMARY KEY, title TEXT, album_id INTEGER);
2.INSERT INTO tracks VALUES(1,'Smooth Criminal',8);
3.INSERT INTO tracks VALUES(2,'Blank Space',5);INSERT INTO tracks VALUES(3,'Rain it over me',1);INSERT INTO tracks VALUES(4,'Shake it off',5);
4.SELECT albums.name, tracks.title FROM albums JOIN tracks ON albums.id=tracks.album_id;*/
5.SELECT * FROM albums LEFT JOIN tracks ON albums.artist_id=tracks.id;
6.SELECT * FROM artists LEFT JOIN albums ON albums.artist_id=artists.id;
7.SELECT tracks.album_id AS 'Albums',albums.name FROM tracks JOIN albums ON albums.id=tracks.album_id;


#7

what is the meaning of the Values are following, why they need to be indiacted as theay are, why exactly 2 or 5

VALUES (2, 'Hey Jude', 5)