Grouping Records, Joins in SQL — Question 10
Back to all questionsGiven the following table :
Table : CLUB
| COACH-ID | COACHNAME | AGE | SPORTS | DATOFAPP | PAY | SEX |
|---|---|---|---|---|---|---|
| 1 | KUKREJA | 35 | KARATE | 27/03/1996 | 1000 | M |
| 2 | RAVINA | 34 | KARATE | 20/01/1998 | 1200 | F |
| 3 | KARAN | 34 | SQUASH | 19/02/1998 | 2000 | M |
| 4 | TARUN | 33 | BASKETBALL | 01/01/1998 | 1500 | M |
| 5 | ZUBIN | 36 | SWIMMING | 12/01/1998 | 750 | M |
| 6 | KETAKI | 36 | SWIMMING | 24/02/1998 | 800 | F |
| 7 | ANKITA | 39 | SQUASH | 20/02/1998 | 2200 | F |
| 8 | ZAREEN | 37 | KARATE | 22/02/1998 | 1100 | F |
| 9 | KUSH | 41 | SWIMMING | 13/01/1998 | 900 | M |
| 10 | SHAILYA | 37 | BASKETBALL | 19/02/1998 | 1700 | M |
Give the output of following SQL statements :
- SELECT COUNT(DISTINCT SPORTS) FROM Club ;
- SELECT MIN(Age) FROM CLUB WHERE Sex = 'F' ;
- SELECT AVG(Pay) FROM CLUB WHERE Sports = 'KARATE' ;
- SELECT SUM(Pay) FROM CLUB WHERE Datofapp > '1998-01-31' ;
1.
+------------------------+
| COUNT(DISTINCT SPORTS) |
+------------------------+
| 4 |
+------------------------+
The SQL query SELECT COUNT(DISTINCT SPORTS) FROM Club ; calculates the count of unique values in the 'SPORTS' column of the 'Club' table. This query helps us to get information about the number of sports offered by the club.
2.
+----------+
| MIN(Age) |
+----------+
| 34 |
+----------+
The SQL query SELECT MIN(Age) FROM CLUB WHERE Sex = 'F' ; retrieves the minimum Age from the 'CLUB' table where the 'Sex' column has the value 'F'. This query gives us the age of the youngest female coach in the club.
3.
+-----------+
| AVG(Pay) |
+-----------+
| 1100.0000 |
+-----------+
The SQL query SELECT AVG(Pay) FROM CLUB WHERE Sports = 'KARATE' ; calculates the average value of the 'Pay' column from the 'CLUB' table where the 'Sports' column has the value 'KARATE'. This query helps us to get information about the average pay of karate coaches in the club.
4.
+----------+
| SUM(Pay) |
+----------+
| 7800 |
+----------+
The SQL query SELECT SUM(Pay) FROM CLUB WHERE Dateofapp > '1998-01-31'; calculates the sum of the 'Pay' column from the 'CLUB' table where the 'Dateofapp' column has a date value greater than '1998-01-31'. This query gives us the total pay of all the coaches who joined after 31/01/1998.