Typical Cases of Database SQL learning: exercises on scores of Professional Students

Typical Cases of Database SQL learning: exercises for scores of professional students; for one question ------ which students have chosen course 1 but not course 2? The following SQL statements are written in different ways ~ No mysqldescSC; + ------- + --------------- + ------ + ----- + --------- + ------- + | Field | Type | Null

Typical Cases of Database SQL learning: exercises for scores of professional students; for one question ------ which students have chosen course 1 but not course 2? The following SQL statements are written in different ways ~ No mysql desc SC; + ------- + --------------- + ------ + ----- + --------- + ------- + | Field | Type | Null

Typical Cases of Database SQL learning: exercises for scores of professional students; for one question ------ which students have chosen course 1 but not course 2?

The following SQL statements are written in different ways ~ <无>

mysql> desc SC;+-------+---------------+------+-----+---------+-------+| Field | Type          | Null | Key | Default | Extra |+-------+---------------+------+-----+---------+-------+| S_id  | varchar(10)   | YES  |     | NULL    |       || C_id  | varchar(10)   | YES  |     | NULL    |       || score | decimal(18,1) | YES  |     | NULL    |       |+-------+---------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> select * from SC;+------+------+-------+| S_id | C_id | 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 || 08   | 04   |  79.0 || 11   | 03   |  77.9 || 12   | 02   |  47.9 || 12   | 04   |  47.9 || 11   | 01   |  77.9 || 01   | 04   |  73.9 || 01   | 05   |  83.9 || 06   | 04   |  75.0 || 06   | 05   |  85.0 || 11   | 05   |  81.0 || 11   | 04   |  91.0 |+------+------+-------+29 rows in set (0.00 sec)--1select B.* from SC B where B.C_id = '01' and not exists(select * from SC B2 where B.S_id = B2.S_id and B2.C_id = '02');--2select * from (select B.S_id, B.C_id aC_id, B.score ascore, B2.C_id bC_id, B2.score bscore from SC B inner join SC B2 on B.S_id = B2.S_id and B.C_id = '01' and B2.C_id != '01') BBB where not exists (select * from (select B.S_id, B.C_id aC_id, B.score ascore, B2.C_id bC_id, B2.score bscore from SC B inner join SC B2 on B.S_id = B2.S_id and B.C_id = '01' and B2.C_id != '01') CCC where BBB.S_id = CCC.S_id and CCC.bC_id = '02' );--3select * from SC where C_id = '01' and S_id not in (select distinct S_id from SC where C_id = '02');--4select B.S_id, B.C_id aC_id, B.score ascore, B2.C_id bC_id, B2.score bscore from SC B inner join SC B2 on B.S_id = B2.S_id and B.C_id = '01' and B2.C_id != '01' group by S_id having (aC_id, bC_id) != ('01','02');--5select B.*, B2.* from SC B left join SC B2 on B.S_id = B2.S_id and B2.C_id = '02' where B.C_id = '01' and B2.C_id is null;--6select B.*, B2.* from SC B right join SC B2 on B.S_id = B2.S_id and B.C_id = '02' where B2.C_id = '01' and B.C_id is null;--7select B.S_id, B.C_id aC_id, B.score ascore, B2.C_id bC_id, B2.score bscore from SC B inner join SC B2 on B.S_id = B2.S_id and B.C_id = '01' and B2.C_id != '01' group by S_id having nullif(bC_id, '02') >> 1;