0

I have the following table schema:

Person:

Name | Year | Sports
Hans | 23 | Football
Hans | 23 | Baseball
Hans | 23 | Badminton
Albert | 25 | Baseball
Albert | 25 | Badminton

Sports:

Name | Tempo | Amount
Football | Fast | 5
Baseball | Slow | 3
Badminton | Fast | 4

Speed:

Name | Star
Fast | Good 
Slow | Bad

The question I am trying to solve is: Which Sports are used by every person and also has the star value good?

The result I want:

Albert | 25 | Badminton

My question would be: How can I realize this with a select statement? My current solution is:

SELECT * FROM speed JOIN 
(SELECT * FROM person JOIN sports USING (name)) USING (name) WHERE STAR = 'good'

I don't know how to filter this more.

Alternative Tables

Country:

Name | Capital
USA | Washington
Germany | Berlin
France | Paris
Poland | Warsaw

Sports

Country | Sport 
Germany | Football 
Belgium | Baseball
Belgium | Football 
France | Football 
Poland | Baseball 
Poland | Football

Region

Country | Area 
Germany | Europe
Belgium | Europe
France | Europe
Poland | Europe

New Question: Which sport is played by every European country?

Output: Football, because it is played by germany, france, belgium and poland

Andrew
  • 4,264
  • 1
  • 21
  • 65

2 Answers2

1

This is a classic problem solved using the DIVISION relational algebra operation:

SP - "Good" sports used by persons
P  - All persons

SP                  DIVIDE   P       =   S
-----------------            ------      ---------
Name    Sports               Name        Sports   
-----------------            ------      ---------
Hans    Football             Hans        Badminton
Hans    Badminton            Albert      
Albert  Badminton

Examples of explaining and expressing this statement in SQL can be found here:

An example of using the COUNT function to solve this problem:

SELECT p.sports
FROM person p
JOIN sports st ON st.name = p.sports
JOIN speed sd ON tempo = sd.name AND star = 'Good'
GROUP BY p.sports
HAVING COUNT(*) = (SELECT COUNT(DISTINCT name) FROM person)

Update for countries:

SELECT s.sport
FROM sports s
JOIN region r ON s.country = r.country AND r.area = 'Europe'
GROUP BY s.sport
HAVING COUNT(*) = (SELECT COUNT(*) FROM region WHERE area = 'Europe')
id'7238
  • 2,428
  • 1
  • 3
  • 11
  • Thank you very much for the answer, unfortunately I got my tables wrong as I posted this question. I updated them under the "alterantiv tables". Could you maybe edit your answer for the new question? – Andrew Jan 28 '21 at 10:28
  • This will be the same query. The logic is simple. Using GROUP BY, for each sport from the Sports table, we count the number of European countries, and then compare it with the total number of countries in Europe from the Regions table. Try to write such a SQL query in your question, and I will help if it does not work out. – id'7238 Jan 28 '21 at 11:44
  • 1
    If you are still having problems with the solution, I have updated my answer. – id'7238 Jan 29 '21 at 07:32
0

All your joins are done using the name column, but as you can see, in each table the name column means something different. You have to use the on clause and specify which columns must match.

with person(name, year, sports) as (
  select 'Hans', 23, 'Football' from dual union all
  select 'Hans', 23, 'Baseball' from dual union all
  select 'Hans', 23, 'Badminton' from dual union all
  select 'Albert', 25, 'Baseball' from dual union all
  select 'Albert', 25, 'Badminton' from dual
), sports(name, tempo, amount) as (
  select 'Football', 'Fast', 5 from dual union all
  select 'Baseball', 'Slow', 3 from dual union all
  select 'Badminton', 'Fast', 4 from dual
), speed(name, star) as (
  select 'Fast', 'Good' from dual union all
  select 'Slow', 'Bad' from dual
)
select person.*
from person
join sports on person.sports = sports.name
join speed on sports.tempo = speed.name
where speed.star = 'Good'

SQL fiddle

Petr
  • 540
  • 1
  • 3
  • 9
  • It is unimportant that these fields have different meanings. They are just pseudotables and not the real ones – Andrew Jan 27 '21 at 15:14