SQL_学生

 
第二部分
1.所有男生的姓名、年龄:
Select Sname，Sage
from student;
2.所有年龄大于20，计算机科学系学生名单:
SELECT Sname
from Student
WHERE Sage>20 AND Sdept='CS';
3.成绩大于60的学生学号:
SELECT Sno
from SC
WHERE Grade>60;
4.成绩在70到80之间的学生学号:
SELECT Sno
from SC
WHERE Grade>70 AND Grade<80;
SELECT Sno 学号
from SC
WHERE Grade BETWEEN 70 AND 80;
5.1985到1989年出生的学生姓名:
SELECT Sname 姓名
from Student
WHERE 2009-Sage BETWEEN 1985 AND 1989;
SELECT Sname
from Student
WHERE 2009-Sage>1985 AND 2009-Sage<1989;
6.查询成绩不在70到85的及格学生名单:
SELECT DISTINCT Sname
from Student,SC
WHERE Student.Sno=SC.Sno AND Grade NOT BETWEEN 70 AND 80 AND Grade>60;
7.查询成绩是70、80、90的学生学号:
SELECT Sno
from SC
WHERE Grade IN(70,80,90);
8.查询1985、1989年的学生学号和姓名:
SELECT Sno,Sname
from Student
WHERE 2009-Sage IN('1985','1989');
9.查询学分不是3、4、5的课号:
SELECT Cno
from Course
WHERE Ccredit NOT IN(3,4,5);
10.查询2002级所有学生情况:
SELECT *
from Student
WHERE Sno LIKE '2002%';
SELECT *
from Student
WHERE Sno='2002%';
11.查询姓刘'CS'系的姓名和性别:
SELECT Sname,Ssex
from Student
WHERE Sdept='CS' AND Sname LIKE '刘%';
12.查询姓刘、张、李的学生名单:
SELECT Sname
from Student
WHERE Sname LIKE '[刘张李]%';
13.查询课程名含有数据的课程名称和学分:
SELECT Cname,Ccredit
from Course
WHERE Cname LIKE '%数据%';
14.查询没有参加考试,课程号为001的学生的学号:
SELECT Sno
from SC
WHERE Cno LIKE '001' AND Grade IS NULL;
15.所有参加了课程号为002的学生学号，成绩。
SELECT Sno,Grade
from SC
WHERE Cno LIKE '%2';
16.查询所有2002级男生和MA系的学生学号和姓名:
SELECT Sno,Sname
from Student
WHERE Sno LIKE '2002%' AND Ssex LIKE '男' OR Sdept LIKE 'MA';
17.查询所有4学分和先行课为6的课程号和课程名:
SELECT Cno,Cname
from Course
WHERE Ccredit LIKE '4' AND Cpno LIKE '6';
18.查询'CS'系学生名单,并按年龄降序,按性别升序:
SELECT Sname,Sage,Ssex
from Student
WHERE Sdept LIKE 'CS'
ORDER BY