-- PROBLEM 4
SELECT
first_name,
IFNULL(AVG(grade), 0) AS average
FROM students
LEFT JOIN papers
ON students.id = papers.student_id
GROUP BY students.id
ORDER BY average DESC;
-- PROBLEM 5
SELECT first_name,
Ifnull(Avg(grade), 0) AS average,
CASE
WHEN Avg(grade) >= 75 THEN 'PASSING'
WHEN Avg(grade) > 60 THEN 'Medium'
ELSE 'FAILING'
end AS passing_status
FROM students
LEFT JOIN papers
ON students.id = papers.student_id
GROUP BY students.id
ORDER BY average DESC;
-----------------------------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use ram
Database changed
mysql> SELECT
-> first_name,
-> IFNULL(AVG(grade), 0) AS average
-> FROM students
-> LEFT JOIN papers
-> ON students.id = papers.student_id
-> GROUP BY students.id
-> ORDER BY average DESC;
+------------+---------+
| first_name | average |
+------------+---------+
| vinod | 97.0000 |
| praveen | 85.0000 |
| ramesh | 66.5000 |
| lakshman | 0.0000 |
| harika | 0.0000 |
+------------+---------+
5 rows in set (0.02 sec)
mysql> select * from papers;
+--------+-------+------------+
| title | grade | student_id |
+--------+-------+------------+
| ds | 61 | 1 |
| c | 72 | 1 |
| java | 95 | 2 |
| python | 99 | 2 |
| telugu | 85 | 4 |
+--------+-------+------------+
5 rows in set (0.00 sec)
mysql> SELECT
-> first_name,
-> IFNULL(AVG(grade), 0) AS average
-> FROM students
-> LEFT JOIN papers
-> ON students.id = papers.student_id
-> GROUP BY students.id
-> ORDER BY average DESC;
+------------+---------+
| first_name | average |
+------------+---------+
| vinod | 97.0000 |
| praveen | 85.0000 |
| ramesh | 66.5000 |
| lakshman | 0.0000 |
| harika | 0.0000 |
+------------+---------+
5 rows in set (0.00 sec)
mysql> SELECT first_name,
-> Ifnull(Avg(grade), 0) AS average,
-> CASE
-> WHEN Avg(grade) IS NULL THEN 'FAILING'
-> WHEN Avg(grade) >= 75 THEN 'PASSING'
-> ELSE 'FAILING'
-> end AS passing_status
-> FROM students
-> LEFT JOIN papers
-> ON students.id = papers.student_id
-> GROUP BY students.id
-> ORDER BY average DESC;
+------------+---------+----------------+
| first_name | average | passing_status |
+------------+---------+----------------+
| vinod | 97.0000 | PASSING |
| praveen | 85.0000 | PASSING |
| ramesh | 66.5000 | FAILING |
| lakshman | 0.0000 | FAILING |
| harika | 0.0000 | FAILING |
+------------+---------+----------------+
5 rows in set (0.01 sec)
mysql> SELECT first_name,
-> Ifnull(Avg(grade), 0) AS average,
-> CASE
->
-> WHEN Avg(grade) >= 75 THEN 'PASSING'
-> ELSE 'FAILING'
-> end AS passing_status
-> FROM students
-> LEFT JOIN papers
-> ON students.id = papers.student_id
-> GROUP BY students.id
-> ORDER BY average DESC;
+------------+---------+----------------+
| first_name | average | passing_status |
+------------+---------+----------------+
| vinod | 97.0000 | PASSING |
| praveen | 85.0000 | PASSING |
| ramesh | 66.5000 | FAILING |
| lakshman | 0.0000 | FAILING |
| harika | 0.0000 | FAILING |
+------------+---------+----------------+
5 rows in set (0.00 sec)
mysql> SELECT first_name,
-> Ifnull(Avg(grade), 0) AS average,
-> CASE
->
-> WHEN Avg(grade) >= 75 THEN 'PASSING'
-> WHEN Avg(grade) > 60 THEN 'Medium'
-> ELSE 'FAILING'
-> end AS passing_status
-> FROM students
-> LEFT JOIN papers
-> ON students.id = papers.student_id
-> GROUP BY students.id
-> ORDER BY average DESC;
+------------+---------+----------------+
| first_name | average | passing_status |
+------------+---------+----------------+
| vinod | 97.0000 | PASSING |
| praveen | 85.0000 | PASSING |
| ramesh | 66.5000 | Medium |
| lakshman | 0.0000 | FAILING |
| harika | 0.0000 | FAILING |
+------------+---------+----------------+
5 rows in set (0.00 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.