Knowee
Questions
Features
Study Tools

This task concerns the following tables:PersonsidnameaddressageeyeColorgenderKnowsidpersonA_id → PersonspersonB_id → PersonsThe semantics of the Knows table is that the person referenced by personA_id knows the person referenced by personB_id.TakesClassesidperson_id → Personsclass_id → ClassesThe semantics of the TakesClasses table is that person referenced by person_id takes the class referenced by class_id.Use NOT IN to write a query that returns the name of all persons that do not know anyone who takes classes.

Question

This task concerns the following tables:PersonsidnameaddressageeyeColorgenderKnowsidpersonA_id → PersonspersonB_id → PersonsThe semantics of the Knows table is that the person referenced by personA_id knows the person referenced by personB_id.TakesClassesidperson_id → Personsclass_id → ClassesThe semantics of the TakesClasses table is that person referenced by person_id takes the class referenced by class_id.Use NOT IN to write a query that returns the name of all persons that do not know anyone who takes classes.

...expand
🧐 Not the exact question you are looking for?Go ask a question

Solution

To solve this task, we will use SQL language. The query will be as follows:

SELECT p.name
FROM Persons p
WHERE p.id NOT IN (
    SELECT k.personA_id
    FROM Knows k
    WHERE k.personB_id IN (
        SELECT tc.person_id
        FROM TakesClasses tc
    )
)

Here is the step by step explanation:

  1. The innermost subquery SELECT tc.person_id FROM TakesClasses tc selects the IDs of all persons who take classes.

  2. The middle subquery SELECT k.personA_id FROM Knows k WHERE k.personB_id IN (...) selects the IDs of all persons who know someone who takes classes. The IN clause is used to check if personB_id is in the list of persons who take classes.

  3. The outermost query SELECT p.name FROM Persons p WHERE p.id NOT IN (...) selects the names of all persons whose IDs are not in the list of persons who know someone who takes classes. The NOT IN clause is used to check if id is not in the list of persons who know someone who takes classes.

This problem has been solved

Similar Questions

This task concerns the following tables:PersonsidnameaddressageeyeColorgenderTakesClassesidperson_id → Personsclass_id → ClassesWrite a query that returns a table with columns name and nrOfClasses that contains the list of all names of people and the number of classes that this person takes.NoteYou can give names to columns in the SELECT clause by SELECT …​ as someName.

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.

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:PersonsidnameaddressageeyeColorgenderClassesidkindTakesClassesidperson_id → Personsclass_id → ClassesWrite a query that returns the table with the name of persons and the kind of classes that they take. The columns of the resulting table should be:ResultsnamekindFor example, if George Orwell takes the classes "dancing" and "baking", then the result should contain rows (George Orwell, dancing) and (George Orwell, baking).

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.

1/3

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.