Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

SQL Joins exercises on soccer Database: Find the most number of cards shown in the matches

SQL soccer Database: Joins Exercise-44 with Solution

44. From the following tables, write a SQL query to find those matches where most number of cards shown. Return match number, number of cards shown.

Sample table: player_booked


Sample Solution:

SQL Code:

SELECT match_no, Booked FROM (
SELECT match_no,COUNT(*) Booked 
FROM player_booked  
GROUP BY match_no) M1 where Booked=(
SELECT MAX(MX1) 
FROM (SELECT match_no,COUNT(*) MX1 
FROM player_booked  
GROUP BY match_no) M2);

Sample Output:

 match_no | booked 
----------+--------
       51 |     10
(1 row)

Practice Online


Sample Database: soccer

soccer database relationship structure

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following tables, write a SQL query to find the number of players booked for each team. Return country name, number of players booked.
Next: From the following table, write a SQL query to find the assistant referees. Return match number, country name, assistant referee name.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3