数据查询练习2

11、查询每门课的平均成绩。

----avg()查询

mysql> select avg(degree) from 
score where cno='3-245';
+-------------+
| avg(degree) |
+-------------+
|     76.3333 |
+-------------+

mysql> select cno,avg(degree) 
from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

12、查询score表中至少有2名学生选修的并以3开头的课程的平均分数。

mysql> select cno,avg(degree),count(*) 
from score group by cno having 
count(cno)>=2 and
 cno like '3%';
+-------+-------------+----------+
| cno   | avg(degree) | count(*) |
+-------+-------------+----------+
| 3-105 |     85.3333 |        3 |
| 3-245 |     76.3333 |        3 |
+-------+-------------+----------+

13、查询分数大于70，小于90的sno列。

select sno,degree from score where 
degree between 70 and 90;

select sno,degree from score where 
degree > 70 and degree < 90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     86 |
| 103 |     85 |
| 105 |     88 |
| 105 |     75 |
| 105 |     79 |
| 109 |     76 |
| 109 |     81 |

14、查询所有学生的sname、cno和degree列。

mysql> select sname,cno,degree from 
student,score where 
student.sno=score.sno;
+------------+-------+--------+
| sname      | cno   | degree |
+------------+-------+--------+
| wangli     | 3-105 |     92 |
| wangli     | 3-245 |     86 |
| wangli     | 6-166 |     85 |
| wangfang   | 3-105 |     88 |
| wangfang   | 3-245 |     75 |
| wangfang   | 6-166 |     79 |
| zhaotiezhu | 3-105 |     76 |
| zhaotiezhu | 3-245 |     68 |
| zhaotiezhu | 6-166 |     81 |
+------------+-------+--------+

15、查询所有学生的sno、cname和degree列。

mysql> select sno,cname,degree from 
course,score where course.cno=score.cno;
+-----+----------------+--------+
| sno | cname          | degree |
+-----+----------------+--------+
| 103 | jisuanjidaolun |     92 |
| 103 | caozuoxitong   |     86 |
| 103 | shuzidianlu    |     85 |
| 105 | jisuanjidaolun |     88 |
| 105 | caozuoxitong   |     75 |
| 105 | shuzidianlu    |     79 |
| 109 | jisuanjidaolun |     76 |
| 109 | caozuoxitong   |     68 |
| 109 | shuzidianlu    |     81 |
+-----+----------------+--------+

16、查询所有学生的sname、cname和degree列。

sql> select sname,cname,degree from 
student,course,score where 
student.sno=score.sno and 
course.cno=score.cno;
+------------+----------------+--------+
| sname      | cname          | degree |
+------------+----------------+--------+
| wangli     | jisuanjidaolun |     92 |
| wangli     | caozuoxitong   |     86 |
| wangli     | shuzidianlu    |     85 |
| wangfang   | jisuanjidaolun |     88 |
| wangfang   | caozuoxitong   |     75 |
| wangfang   | shuzidianlu    |     79 |
| zhaotiezhu | jisuanjidaolun |     76 |
| zhaotiezhu | caozuoxitong   |     68 |
| zhaotiezhu | shuzidianlu    |     81 |
+------------+----------------+--------+

17、查询“95301”班学生每门课的平均分。

mysql> select cno,avg(degree) from score where sno in(select sno from student where class='95031') group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+

18、查询选修“3-105”课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录。

mysql> select * from score where cno = 
'3-105' and degree >(select degree 
from score where 
sno = '109' and cno = '3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
+-----+-------+--------+

19、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

mysql> select * from score where 
degree > (select degree from score 
where sno = '109' and cno = '3-105');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

20、查询和学号为108、101的同学同年出生的所有学生的sno、sname和sbirthday列。

select year(sbirthday) from student 
where sno in(101,108);
select * from student where 
year(sbirthday) in (select 
year(sbirthday) from student sno 
in(101,108)); 
+-----+--------------+------+---------------------+-------+
| sno | sname        | ssex | sbirthday           | class |
+-----+--------------+------+---------------------+-------+
| 101 | zenghua      | nan  | 1977-09-01 00:00:00 | 95033 |
| 102 | kuangming    | nan  | 1975-10-02 00:00:00 | 95031 |
| 105 | wangfang     | nv   | 1975-02-10 00:00:00 | 95031 |
| 108 | zhangquandan | nan  | 1975-02-10 00:00:00 | 95031 |
+-----+--------------+------+---------------------+-------+

21、查询“张旭”教师任课的学生的成绩。

select tno from teacher where tname= 'zhangxu';
+-----+
| tno |
+-----+
| 856 |
+-----+
select cno from course where(select tno from teacher where tname= 'zhangxu');
+-------+-------------+-----+
| cno   | cname       | tno |
+-------+-------------+-----+
| 6-166 | shuzidianlu | 856 |
+-------+-------------+-----+
select * from score where cno = (select cno from course where(select tno from teacher where tname= 'zhangxu'));
 sno | cno   | degree |
+-----+-------+--------+
| 103 | 6-166 |     85 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+

22、查询选修某课程的同学人数多于5人的教师姓名。

mysql> select tname from teacher where 
tno=(select tno from course where cno 
= (select
cno from score group by cno having 
count(*)>5));
+----------+
| tname    |
+----------+
| wangping |
+----------+
1 row in set (0.00 sec)

23、查询95033班和95031班全体学生的记录。

mysql> select * from student where class in (95033,95031);

24、查询存在有85分以上成绩的课程cno。

select cno from score where degree > '85';

25、查询出“计算机系”教师所教课程的成绩表。

select cno,degree from score where 
cno in (select cno from course where 
tno in (select tno from teacher where 
depart = 'jisuanjixi'));
+-------+--------+
| cno   | degree |
+-------+--------+
| 3-245 |     86 |
| 3-245 |     75 |
| 3-245 |     68 |
| 3-105 |     99 |
| 3-105 |     91 |
| 3-105 |     92 |
| 3-105 |     89 |
| 3-105 |     88 |
| 3-105 |     76 |
+-------+--------+

26、查询“计算机系”与“电子工程系”不同职称的教师的tname和prof。

mysql> select * from teacher where depart
='dianzigongchengxi' and prof not in(select prof from 
teacher where depart = 'jisuanjixi') union select * from 
teacher where depart='jisuanjixi' and prof not in
(select prof from teacher where depart 
= 'dianzigongchengxi');
+-----+---------+------+---------------------+------------+-------------------+
| tno | tname   | tsex | tbirthday           | prof       | depart            |
+-----+---------+------+---------------------+------------+-------------------+
| 856 | zhangxu | nan  | 1969-03-12 00:00:00 | jiangshi   | dianzigongchengxi |
| 804 | licheng | nan  | 1958-12-02 00:00:00 | fujiaoshou | jisuanjixi        |
+-----+---------+------+---------------------+------------+-------------------+