MySQL Student Stats Exercise

Mysql> Show CREATE TABLE SC \g
1. Row ***************************
Table:sc
Create table:create Table ' SC ' (
' Sid ' varchar () DEFAULT NULL,
' Cid ' varchar DEFAULT NULL,
' Score ' decimal (18,1) DEFAULT NULL,
KEY ' Sid ' (' Sid '),
KEY ' CID ' (' CID '),
CONSTRAINT ' Sc_ibfk_1 ' FOREIGN KEY (' Sid ') REFERENCES ' Student ' (' Sid '),
CONSTRAINT ' sc_ibfk_2 ' FOREIGN KEY (' cid ') REFERENCES ' Course ' (' CID ')
) Engine=innodb DEFAULT Charset=utf8
1 row in Set (0.05 sec)

Mysql> desc SC
;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| Sid | varchar (10) | YES | MUL |       NULL | |
| Cid | varchar (10) | YES | MUL |       NULL | |
| Score | Decimal (18,1) |     YES | |       NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in Set (0.48 sec)

Mysql> select * from SC;
+------+------+-------+
| Sid | Cid | Score |
+------+------+-------+
| 01 |  01 | 80.0 |
| 01 |  02 | 90.0 |
| 01 |  03 | 99.0 |
| 02 |  01 | 70.0 |
| 02 |  02 | 60.0 |
| 02 |  03 | 80.0 |
| 03 |  01 | 80.0 |
| 03 |  02 | 80.0 |
| 03 |  03 | 80.0 |
| 04 |  01 | 50.0 |
| 04 |  02 | 30.0 |
| 04 |  03 | 20.0 |
| 05 |  01 | 76.0 |
| 05 |  02 | 87.0 |
| 06 |  01 | 31.0 |
| 06 |  03 | 34.0 |
| 07 |  02 | 89.0 |
| 07 |  03 | 98.0 |
+------+------+-------+
Rows in Set (0.00 sec)

The above is my scoure table data

Count each student's total

Select Sid,sum (Score) from the SC group by SID;

To count the number of failed and passed subjects per student

Select Sid,sum (Score <60), SUM (score >=) from the SC group by SID;

Average scores of students with a statistical failing subject greater than 2

Select Sid,avg (Score) from SC where score < GROUP by SID have Count (*) >2;

Statistics of passing and failing numbers and passing rates in each section

Select Cid,sum (Score < 60) failed, sum (score >= 60) passed, sum (case when score >= 1 Else 0 end)/count (distinct s ID) pass rate from the SC group by CID;

This article from "Yun Weibang" blog, reproduced please contact the author!

MySQL Student Stats Exercise