SQL - GROUPING, ID, NAME

Question

I was wondering what the answer should be in 1c. on this website:

http://sqlzoo.net/6.htm

SELECT company FROM route WHERE stop=4 GROUP BY name, ID 

this obviously does not work, id and name are not displayed no matter what. What is missing here? Thanks.

Answer 1

 SELECT stops.id, stops.name FROM route INNER JOIN stops on route.stop = stops.id WHERE route.num = 4 AND route.company = 'LRT' 

You need to join the tables, because the data you want to return is in another table in the one that filters the data.

Answer 2

This works and does not include any unnecessary table joins. A good rule is to use EXISTS to check the values ​​in a table for which you do not need output. Otherwise, you must use JOIN

 SELECT stops.id, stops.name FROM stops WHERE EXISTS ( SELECT 1 FROM route WHERE route.stop = stops.id AND num = '4' AND company = 'LRT' ) 

Answer 3

He gives you this error:

sql: Unknown column 'name' in 'group statement'

There is no name tag in route . Table structure:

 stops(id, name) route(num,company,pos, stop) 

So the answer to this quiz is:

 SELECT s.id, s.name FROM route r, stops s WHERE r.stop= s.id and r.num = 4 AND r.company = 'LRT' 

Answer 4

 select s.id, s.name from stops s inner join route r on s.id = r.stop where r.num= 4 AND r.company= 'LRT'