This task concerns the following tables:PersonsidnameaddressageeyeColorgenderLikesidpersonA_id → PersonspersonB_id → PersonsThe semantics of the Likes table is that personA likes personB.Use a LEFT JOIN to write a query that returns the name of all people that do not like anyone.ImportantYou can test whether an attribute is null by attribute IS NULL
Question
This task concerns the following tables:PersonsidnameaddressageeyeColorgenderLikesidpersonA_id → PersonspersonB_id → PersonsThe semantics of the Likes table is that personA likes personB.Use a LEFT JOIN to write a query that returns the name of all people that do not like anyone.ImportantYou can test whether an attribute is null by attribute IS NULL
Solution
To write a SQL query that returns the name of all people that do not like anyone using a LEFT JOIN, you would do the following:
SELECT Persons.name
FROM Persons
LEFT JOIN Likes
ON Persons.id = Likes.personA_id
WHERE Likes.personB_id IS NULL;
Here's the step by step explanation:
-
SELECT Persons.name: This line is telling SQL that we want to get the names from the Persons table. -
FROM Persons: This line is telling SQL which table to look at first, in this case, it's the Persons table. -
LEFT JOIN Likes ON Persons.id = Likes.personA_id: This line is joining the Likes table to the Persons table where the id in the Persons table matches the personA_id in the Likes table. A LEFT JOIN returns all the records from the left table (Persons), and the matched records from the right table (Likes). If there is no match, the result is NULL on the right side. -
WHERE Likes.personB_id IS NULL: This line is the condition that filters the results. It's saying that we only want the names of people where there is no corresponding personB_id in the Likes table, meaning they don't like anyone.
Similar Questions
This task concerns the following tables:PersonsidnameaddressageeyeColorgenderLikesidpersonA_id → PersonspersonB_id → PersonsKnowsidpersonA_id → PersonspersonB_id → PersonsThe semantics of the Likes table is that personA likes personB.The semantics of the Knows table is that personA knows personB.Write a query that returns the name of all persons that like everyone they know.
Persons who like someone with blue eyesThis task concerns the following tables:PersonsidnameaddressageeyeColorgenderLikesidpersonA_id → PersonspersonB_id → PersonsThe semantics of the Likes table is that personA likes personB.Write a query that returns the name of all persons that like someone with blue eyes. Eliminate duplicates from your query result.
This task concerns the following tables:PersonsidnameaddressageeyeColorgenderWrite a query that returns the name of all persons that are at least as old as everybody else.TipRecall the logic recap: FOR ALL X can be expressed by NOT EXISTS NOT X
This task concerns the following tables:PersonsidnameaddressageeyeColorgenderKnowsidpersonA_id → PersonspersonB_id → PersonsThe semantics of the Knows table is that personA knows personB.Write a query that returns the name of all people that know precisely 2 people that are older than 60.
This task concerns the following tables:PersonsidnameaddressageeyeColorgenderSportTogetheridsportpersonA_id → PersonspersonB_id → PersonsThe semantics of the Sport table is that personA does sport with personB.CautionNote that the table does not contain redundancies. The table may contain a row (id, sport, personA, personB) without containing the symmetric row (id, sport, personB, personA). Nethertheless, the relation is to be understood as symmetric: if personA does sport with personB, then of course personB also does sport with personA! You need to take this into account in your query!Write a query that returns a table with columns: name and rugby. The table should contain the names of all people and the columns rugby there should contain "Yes" or "No" depending on whether this person plays rugby or not.
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.