Knowee
Questions
Features
Study Tools

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.

Question

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.

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

Solution

To solve this task, we can use SQL (Structured Query Language), which is a standard language for managing data held in a relational database management system.

Here is a step-by-step guide on how to write the query:

  1. First, we need to create a subquery that will return all the ids of people who play rugby. We will use the SportTogether table for this. We will select all distinct personA_id and personB_id where sport is 'rugby'. We will use the UNION operator to combine these two sets of ids. This will give us a list of all people who play rugby.
(SELECT personA_id as id FROM SportTogether WHERE sport = 'rugby'
UNION
SELECT personB_id as id FROM SportTogether WHERE sport = 'rugby')
  1. Next, we will join this subquery with the Persons table. We will use a LEFT JOIN so that we get all people, not just those who play rugby. The id from the Persons table will be matched with the id from our subquery.
SELECT Persons.name, 
CASE 
    WHEN rugby_players.id IS NULL THEN 'No' 
    ELSE 'Yes' 
END as rugby
FROM Persons
LEFT JOIN 
    (SELECT personA_id as id FROM SportTogether WHERE sport = 'rugby'
    UNION
    SELECT personB_id as id FROM SportTogether WHERE sport = 'rugby') as rugby_players
ON Persons.id = rugby_players.id
  1. Finally, we will use a CASE statement to create the 'rugby' column. If the id from our subquery is NULL, this means that the person does not play rugby, so we will return 'No'. Otherwise, we will return 'Yes'.

This query will return a table with the names of all people and a column 'rugby' that contains 'Yes' if the person plays rugby and 'No' otherwise.

This problem has been solved

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.

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.

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: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

1/2

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.