27、查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学cno、sno和degree,并按照degree从高到低次序排序。
//any代表任一
ysql> select * from score where cno
= '3-105'and degree >any(select degree
from score
where cno = '3-245') order by
degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 99 |
| 103 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
+-----+-------+--------+
28、查询选修编号为“3-105”课程且成绩高于选修编号为“3-245”的同学cno、sno和degree,并按照degree从高到低次序排序。
//all 全选
mysql> select * from score where cno
= '3-105'and degree >all(select degree
from score
where cno = '3-245') order by degree
desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 99 |
| 103 | 3-105 | 92 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
+-----+-------+--------+
29、查询所有教师和同学的name、sex和birthday。
mysql> select tname as name,tsex as
sex,tbirthday as birthday from teacher
union select sname,ssex,sbirthday from
student;
+--------------+-----+---------------------+
| name | sex | birthday |
+--------------+-----+---------------------+
| licheng | nan | 1958-12-02 00:00:00 |
| wangping | nv | 1972-05-05 00:00:00 |
| liubing | nv | 1977-08-14 00:00:00 |
| zhangxu | nan | 1969-03-12 00:00:00 |
| zenghua | nan | 1977-09-01 00:00:00 |
| kuangming | nan | 1975-10-02 00:00:00 |
| wangli | nv | 1976-01-23 00:00:00 |
| lijun | nan | 1976-02-20 00:00:00 |
| wangfang | nv | 1975-02-10 00:00:00 |
| lujun | nan | 1974-06-03 00:00:00 |
| wangnima | nan | 1976-02-20 00:00:00 |
| zhangquandan | nan | 1975-02-10 00:00:00 |
| zhaotiezhu | nan | 1974-06-03 00:00:00 |
| zhangfei | nan | 1974-06-03 00:00:00 |
+--------------+-----+---------------------+
30、查询所有“女”教师和“女”同学的name、sex和birthday。
l> select tname as name,tsex as sex,
tbirthday as birthday from teacher
where tsex = 'nv' union select sname,
ssex,sbirthday from student where
ssex = 'nv';
+----------+-----+---------------------+
| name | sex | birthday |
+----------+-----+---------------------+
| wangping | nv | 1972-05-05 00:00:00 |
| liubing | nv | 1977-08-14 00:00:00 |
| wangli | nv | 1976-01-23 00:00:00 |
| wangfang | nv | 1975-02-10 00:00:00 |
+----------+-----+---------------------+
31、查询成绩比该课程平均成绩低的同学的成绩表。
> select * from score a where degree < (select avg(degree) from score b where a.cno = b.cno);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 104 | 3-105 | 89 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+-----+-------+--------+
32、查询所有任课教师的tname和depart。
mysql> select tname,depart from
teacher where tno in (select tno from
course);
+----------+-------------------+
| tname | depart |
+----------+-------------------+
| licheng | jisuanjixi |
| wangping | jisuanjixi |
| liubing | dianzigongchengxi |
| zhangxu | dianzigongchengxi |
+----------+-------------------+
33、查询至少有2名男生的班号。
mysql> select class from student
where ssex = 'nan' group by class
having count(*) > 1;
+-------+
| class |
+-------+
| 95031 |
| 95033 |
+-------+
34、查询student表中不姓“王”的同学记录。
mysql> select * from student where
sname not like 'wang%';
+-----+--------------+------+---------------------+-------+
| 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 |
| 104 | lijun | nan | 1976-02-20 00:00:00 | 95033 |
| 106 | lujun | nan | 1974-06-03 00:00:00 | 95031 |
| 108 | zhangquandan | nan | 1975-02-10 00:00:00 | 95031 |
| 109 | zhaotiezhu | nan | 1974-06-03 00:00:00 | 95031 |
| 110 | zhangfei | nan | 1974-06-03 00:00:00 | 95038 |
+-----+--------------+------+---------------------+-------+
35、查询student表中每个学生的姓名和年龄。
----当前年份-出生年份=年龄
mysql> select sname,year(now())-
year(sbirthday) as 'nianling' from
student;
+--------------+----------+
| sname | nianling |
+--------------+----------+
| zenghua | 43 |
| kuangming | 45 |
| wangli | 44 |
| lijun | 44 |
| wangfang | 45 |
| lujun | 46 |
| wangnima | 44 |
| zhangquandan | 45 |
| zhaotiezhu | 46 |
| zhangfei | 46 |
+--------------+----------+
36、查询student表中最大和最小的sbirthday日期值。
mysql> select max(sbirthday) as
'zuida',min(sbirthday) as 'zuixiao'
from student;
+---------------------+---------------------+
| zuida | zuixiao |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
37、以班号和年龄从大到小的顺序查询student表中的全部记录。
mysql> select * from student order
by class desc,sbirthday;
+-----+--------------+------+---------------------+-------+
| sno | sname | ssex | sbirthday | class |
+-----+--------------+------+---------------------+-------+
| 110 | zhangfei | nan | 1974-06-03 00:00:00 | 95038 |
| 103 | wangli | nv | 1976-01-23 00:00:00 | 95033 |
| 104 | lijun | nan | 1976-02-20 00:00:00 | 95033 |
| 107 | wangnima | nan | 1976-02-20 00:00:00 | 95033 |
| 101 | zenghua | nan | 1977-09-01 00:00:00 | 95033 |
| 106 | lujun | nan | 1974-06-03 00:00:00 | 95031 |
| 109 | zhaotiezhu | nan | 1974-06-03 00:00:00 | 95031 |
| 105 | wangfang | nv | 1975-02-10 00:00:00 | 95031 |
| 108 | zhangquandan | nan | 1975-02-10 00:00:00 | 95031 |
| 102 | kuangming | nan | 1975-10-02 00:00:00 | 95031 |
+-----+--------------+------+---------------------+-------+
38、查询“nan”教师及其所上的课程。
mysql> select * from course where tno in(select
tno from teacher where tsex='nan');
+-------+--------------+-----+
| cno | cname | tno |
+-------+--------------+-----+
| 3-245 | caozuoxitong | 804 |
| 6-166 | shuzidianlu | 856 |
+-------+--------------+-----+
39、查询最高分同学的sno、cno和degree列。
mysql> select max(degree) from score;
+-------------+
| max(degree) |
+-------------+
| 99 |
+-------------+
1 row in set (0.00 sec)
mysql> select * from score where
degree=(select max(degree) from
score);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 99 |
+-----+-------+--------+
40、查询和“李军”同性别的所有同学的sname。
mysql> select ssex from student where sname='lijun';
+------+
| ssex |
+------+
| nan |
+------+
1 row in set (0.00 sec)
mysql> select sname from student
where ssex like(select ssex from
student where sname='lijun');
+--------------+
| sname |
+--------------+
| zenghua |
| kuangming |
| lijun |
| lujun |
| wangnima |
| zhangquandan |
| zhaotiezhu |
| zhangfei |
+--------------+
41、查询和“李军”同性别同班的所有同学的sname。
mysql> select sname from student
where ssex =(select ssex from
student where sname='lijun')
and class = (select class from
student where sname='lijun');
+----------+
| sname |
+----------+
| zenghua |
| lijun |
| wangnima |
+----------+
42、查询所有选修“计算机导论”课程的“男”同学的成绩表。
mysql> select * from score where sno
in(select sno from student where
ssex = 'nan') and cno=(select cno
from course where cname
='jisuanjidaolun');
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 101 | 3-105 | 99 |
| 102 | 3-105 | 91 |
| 104 | 3-105 | 89 |
| 109 | 3-105 | 76 |
+-----+-------+--------+
43、假设使用如下命令建立了一个grade表:
create table grade(
low int(3),
upp int(3),
grade char(1)
);
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
现查询所有同学的sno、cno和grade。
mysql> select sno,cno,grade from
score,grade where degree between
low and upp;
+-----+-------+-------+
| sno | cno | grade |
+-----+-------+-------+
| 101 | 3-105 | A |
| 102 | 3-105 | A |
| 103 | 3-105 | A |
| 103 | 3-245 | B |
| 103 | 6-166 | B |
| 104 | 3-105 | B |
| 105 | 3-105 | B |
| 105 | 3-245 | C |
| 105 | 6-166 | C |
| 109 | 3-105 | C |
| 109 | 3-245 | D |
| 109 | 6-166 | B |
+-----+-------+-------+