sql语句查询计算机系,SQL查询语句基础

1.查询全体学生的学号与姓名

select sno,sname

from student;

3.查询全体学生的详细信息

select *

from student;

4.查询全体学生的姓名及其出生年份

select sname,2004-sage

from student;

5.查询全体学生的姓名，出生年份和所在院系，要求用小写字母表示所有系名

select sname,'year of birth:',2004-sage,lower(sdept)

from student;

6.查询选修课程的学生学号，DISTINCT去掉重复行

select DISTINCT sno

from SC;

select sno

from SC;

等价于

select ALL sno

from SC;

7.查询计算机科学系全体学生的名单

select sname

from student

where sdept='cs';

8.查询所有年龄在20岁以下的学生姓名及其年龄

select sname,sage

from student

where sage<20;

9.查询考试成绩有不及格的学生的学号

select DISTINCT sno

from sc

where grade<60;

10.查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名，系别和年龄

select sname,sdept,sage

from student

where sage between 20 and 23;

11查询年龄不在20~23岁之间的学生的姓名，系别和年龄

select sname,sdept,sage

from student

where sage not between 20 and 23;

12.查询计算机科学系cs，数学系ma和信息系is学生的姓名和性别

select sname,ssex

from student

where sdept in('cs','ma','is');

13.查询不是计算机科学系cs，数学系ma和信息系is学生的姓名和性别

select sname,ssex

from student

where sdept not in('cs','ma','is');

14.查询学号为200215121的学生的详细情况

select *

from student

where sno like '200215121';

等价于

select *

from student

where sno='200215121';

15.查询所有姓刘的学生的姓名，学号和性别

select sname,sno,ssex

from student

where sname like '刘%';

16.查询所有不姓刘的学生的姓名，学号和性别

select sname,sno,ssex

from student

where sname not like '刘%';

17.查询姓欧阳且全名为3个汉字的学生的姓名

select sname

from student

where sname like '欧阳_';

19.查询DB_Design课程的课程号和学分

select cno,ccredit

from course

where cname like 'DB_Design'ESCAPE'';

ESCAPE''表示""为换码字符。这样匹配串中紧跟着""后面的字符"_"不再具有通配符的含义，转义为普通的"_"字符

20.查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况

select *

from course

where cname like 'DB_%i__'ESCAPE'';

21.查询缺少成绩的学生的学号和相应的课程号

select sno,cno

from sc

where grade is null;

22.查询计算机科学系年龄在20岁以下的学生姓名

select sname

from student

where sdept='cs' and sage<20;

23.查询计算机科学系cs，数学系ma和信息系is学生的姓名和性别

select sname,ssex

from student

where sdept='cs' or sdept='ma' or sdept='is';

24.查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排序

select sno,grade

from sc

where cno='3'

order by grade DESC;

25.查询全体学生情况，查询结果按所在系的系号升序排列，同一系的学生按年龄降序排列

select *

from student

order by sdept,sage DESC;

26.查询学生总人数

select COUNT(*)

from student;

27.查询选修了课程的学生人数

select COUNT(DISTINCT sno)

from sc;

28.计算1号课程的学生的平均成绩

select AVG(grade)

from sc

where cno='1';

29.查询选修1号课程的学生的最高分数

select MAX(grade)

from sc

where cno='1';

30.查询学生200215012选修课程的总学分数

select SUM(ccredit)

from sc,course

where sno='200215012' and sc.cno=course.cno;

31.求各个课程号及相应的选课人数

select cno,count(sno)

from sc

group by cno;

32.查询选修了3门以上课程的学生学号

select sno

from sc

group by sno

having count(*) >3;

33.查询每个学生及其选课情况

select student.*,sc.*

from student,sc

where student.sno=sc.sno;

35.查询每一门课的间接先修课

select first.cno=second.cpno

from course first,course second

where first.cpno =second.cno;

36.

select student.sno,sname,ssex,sage,sdept,cno,grade

from student left out join sc on (student.sno=sc.sno);

37.查询选修2号课程且成绩在90分以上的所有学生

select student.sno,sname

from student,sc

where student.sno=sc.sno and

sc.cno='2' and sc.grade>90;

38.查询每个学生的学号，姓名，选修的课程名及成绩

select student.sno,sname,cname,grade

from student,sc,course

where student.sno=sc.sno and sc.cno=course.cno;

39.查询与刘晨在同一系学习的学生

select sno,sname,sdept

from student

where sdept in

(select sdept

from student

where sname='刘晨');

等同于

select s1.sno,s1.sname,s1.sdept

from student s1,student s2

where s1.sdept=s2.sdept and

s2.sname='刘晨';

40.查询选修了课程名为“信息系统”的学生学号和姓名

select sno,sname

from student

where sno in

(select sno

from sc

where cno in

(select cno

from course

where cname='信息系统'

)

);

等价于

select student.sno,sname

from student,sc,course

where student.sno=sc.sno and

sc.cno=course.cno and

course.cname='信息系统';

41.找出每个学生超过他选修课程平均成绩的课程号

select sno,cno

from sc x

where grade>=(select AVG(grade)

from sc y

where y.sno=s.sno

);

42.查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄

select sname,sage

from student

where sage

from student

where sdept='cs'

)

and sdept <> 'cs';

43.查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄

select sname,sage

from student

where sage < all

(select sage

from student

where sdept='cs'

)

and sdept <> 'cs';

44.查询所有选修了1号课程的学生姓名

select sname

from student

where EXISTS

(select *

from sc

where sno=student.sno and cno='1');

使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值

45.查询没有选修1号课程的学生姓名

select sname

from student

where not exists

(select *

from sc

where sno=student.sno and cno='1');

46.查询选修了全部课程的学生姓名

select sname

from student

where not exists

(select *

from course

where not exists

(select *

from sc

where sno=student.sno

and cno=course.cno));

47.查询至少选修了学生200215122选修的全部课程的学生号码

select distinct sno

from sc scx

where not exists

(select *

from sc scy

where scy.sno='200215122' and

not exists

(select *

from sc scz where scz.sno=scx.sno and scz.cno=scy.cno));

48.查询计算机科学系的学生及年龄不大于19岁的学生

select *

from student

where sdept='cs'

union

select *

from student

where sage<=19;

union会自动去掉重复元组，而union all操作符可以保存重复元组

49.查询选修了课程1或者课程2的学生集合的并集

select sno

from sc

where cno='1'

union

select sno

from sc

where cno='2';

50.查询计算机科学系的学生与年龄不大于19岁的学生的交集

select *

from student

where sdept='cs'

intersect

select *

from student

where sage<=19;