Sql: Find Country Name of the Team Having the Most Players Who Have Never Scored a Goal

SQL: Find country name of the team having the most players who have never scored a goal

You can use a CTE:

with cte as (
  select
    t.id, t.country, count(*) players 
  from teams t inner join (
    select * from players 
    where id not in (select scoredby from goals)
  ) p on p.memberOf = t.id
  group by t.id, t.country
)
select country, players 
from cte
where players = (select max(players) from cte)
order by country

See the demo.

Results:

country   | players 
Australia |       2
Japan     |       2

how to retrieve player names who never scored a century?

Try Group by.. Having clause with MAX() to find out players name who never scored century

SELECT NAME FROM table
GROUP BY NAME
HAVING MAX(SCORE) < 100;

Select the number of English players from team which scored the most goals at home on a match

You need to first get the highest scoring team, then left-join NATION to PLAYER, then count the number of non-null NATION_ID.

You are also grouping by the wrong values

SELECT TOP (1)
  p.TEAM_ID,
  NumPlayers = COUNT(*),
  NumEnglishPlayers = COUNT(n.NATION_ID),
  HighestScore = m.HOME_SCORE
FROM (
    SELECT TOP (1) *
    FROM MATCH m
    ORDER BY
      m.HOME_SCORE DESC
) m
JOIN PLAYER p ON m.HOME_TEAM_ID = p.TEAM_ID
LEFT JOIN NATION n ON p.NATION_ID = n.NATION_ID
    AND n.NATION_NAME = 'England'
GROUP BY
  m.HOME_TEAM_ID,
  m.HOME_SCORE;

db<>fiddle

How to query this table to find player who scored most amount of goals?

You need to aggregation after combining the tables:

select player, sum(goals)
from ((select player1_id as player, player1_goals as goals
       from results
       where community_id = 5
      ) union all
      (select player2_id as player, player2_goals as goals
       from results
       where community_id = 5
      )
     ) p
group by player
order by sum(goals) desc
limit 1;

How do i crack this SQL Soccer Matches assignment?

I suggest the following:

WITH cteHostPoints AS (SELECT HOST_TEAM AS TEAM,
                              CASE
                                WHEN HOST_GOALS > GUEST_GOALS THEN 3
                                WHEN HOST_GOALS = GUEST_GOALS THEN 1
                                ELSE 0
                              END AS POINTS
                         FROM MATCHES),
     cteGuestPoints AS (SELECT GUEST_TEAM AS TEAM,
                               CASE
                                 WHEN GUEST_GOALS > HOST_GOALS THEN 3
                                 WHEN GUEST_GOALS = HOST_GOALS THEN 1
                                 ELSE 0
                               END AS POINTS
                          FROM MATCHES),
     cteAllPoints AS (SELECT TEAM, POINTS FROM cteHostPoints
                      UNION ALL
                      SELECT TEAM, POINTS FROM cteGuestPoints)
SELECT t.TEAM_ID, t.TEAM_NAME, COALESCE(SUM(ap.POINTS), 0) AS TOTAL_POINTS
  FROM TEAMS t
  LEFT OUTER JOIN cteAllPoints ap
    ON ap.TEAM = t.TEAM_ID
  GROUP BY t.TEAM_ID, t.TEAM_NAME
  ORDER BY COALESCE(SUM(POINTS), 0) DESC, t.TEAM_ID

dbfiddle here

Case statement in a SQL query

Use a CTE to return the total goals for each match and team.

Then join Match to 2 copies of Team and 2 copies of the CTE:

with cte as (
  select mid, tid, count(*) goals
  from Goal
  group by mid, tid
)  
select m.date, m.stadium,
  t1.tid team1, coalesce(c1.goals, 0) goals_1,
  t2.tid team2, coalesce(c2.goals, 0) goals_2
from `Match` m
inner join Team t1 on t1.tid = m.team1
inner join Team t2 on t2.tid = m.team2
left join cte c1 on c1.mid = m.mid and c1.tid = m.team1
left join cte c2 on c2.mid = m.mid and c2.tid = m.team2

SQL Query: Parameter input=[Buyers.Name], get other [Buyers.Name]s who have the same [Stores.StoreID]

You can use in with a subquery:

select distinct s.buyerid
from sellers s
where s.storeid in (select s2.storeid
                    from sellers s2 join
                         buyers b2
                         on s2.buyerId = b2.buyerId
                    where b2.name = 'Sten'
                   );

Here is a db<>fiddle.

---

Related Topics