How does SQL know that I want to join the two table using the primary key without using a join statement. In other words, how does SQL know that artist_id is a foreign key related to the primary key in the artists table and not some other numeric data.
I have the same problem.
I think a lot of things were difficult to understand with the last part of the SQL course.
I am trying to figure out as well why one of the exercises returns so many queries.
Yes, I saw a query returned about 10 extra lines with just one select statement.
The Foreign Key is the key denoted as such, like the "Artists_ID" column name in the Albums Table. It's obviously named to reference the primary key "ID", in the Artists Table. Both Albums and Artists are Tables from the same Database. The difference is a Primary key field cannot be Null (empty) and has to be unique, while a Foreign Key can be Null and/or not unique. Account numbers and email addresses are the two most common Primary Keys.
Tables make up a single Database, while other Databases can be on completely different servers and will have their own Tables. To link Tables on separate Databases use JOIN something like this (Some types of SQL require "INNER JOIN", not just "JOIN" like most):
SELECT a.userID, b.usersFirstName, b.usersLastName FROM databaseA.dbo.TableA a inner join database B.dbo.TableB b ON a.userID=b.userID