Problem
Table: NewYork
+-------------+------+
| Column Name | Type |
+-------------+------+
| student_id | int |
| score | int |
+-------------+------+
student_id is the primary key for this table. Each row contains information about the score of one student from New York University in an exam.
Table: California
+-------------+------+
| Column Name | Type |
+-------------+------+
| student_id | int |
| score | int |
+-------------+------+
student_id is the primary key for this table. Each row contains information about the score of one student from California University in an exam.
There is a competition between New York University and California University. The competition is held between the same number of students from both universities. The university that has more excellent students wins the competition. If the two universities have the same number of excellent students, the competition ends in a draw.
An excellent student is a student that scored 90% or more in the exam.
Write an SQL query to report:
“New York University” if New York University wins the competition. “California University” if California University wins the competition. “No Winner” if the competition ends in a draw.
Solution
Method 1 - Using CTE
Code
Sql
WITH NewYorkExcellent AS (
SELECT COUNT(*) AS count
FROM NewYork
WHERE score >= 90
),
CaliforniaExcellent AS (
SELECT COUNT(*) AS count
FROM California
WHERE score >= 90
),
Results AS (
SELECT
CASE
WHEN nx.count > cx.count THEN 'New York University'
WHEN cx.count > nx.count THEN 'California University'
ELSE 'No Winner'
END AS Result
FROM NewYorkExcellent nx, CaliforniaExcellent cx
)
SELECT Result FROM Results;