1.检索liu老师所伤的课程号和课程名
SELECT TEA_COURSE.COUR_ID,COURSE.COURSENAME
FROM TEA_COURSE,TEACHER,COURSE
WHERE TEA_COURSE.TEA_ID=TEACHER.TEA_ID AND TEA_COURSE.COUR_ID=COURSE.COUR_ID AND TEACHER.TEA_NAME='LIU'
2.检索年龄大于23岁的学生的学号和姓名
SELECT STUDENT.CLA_ID,STUDENT.NAME
FROM STUDENT
WHERE DATEDIFF(Y,STUDENT.BORNDATA,'1997/3/2')>10
3.检索学好为32010103的学生所学的课程号与任课教师名
SELECT COUR_ID,TEACHER.TEA_NAME
FROM TEA_COURSE,TEACHER,STUDENT
WHERE TEACHER.TEA_ID=TEA_COURSE.TEA_ID AND TEA_COURSE.CLA_ID=STUDENT.CLA_ID AND STUDENT.ID=32010103
4.检索至少选修liu老师所受课程中的一门课程的女大学生姓名
SELECT STUDENT.NAME
FROM TEA_COURSE,TEACHER,STUDENT
WHERE TEACHER.TEA_ID=TEA_COURSE.TEA_ID AND TEA_COURSE.CLA_ID=STUDENT.CLA_ID AND TEACHER.TEA_NAME='LIUWEI'
AND SEX=0
5.检索115课程的女学生的平均年龄
SELECT AVG(DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2'))
FROM TEA_COURSE,TEACHER,STUDENT
WHERE TEACHER.TEA_ID=TEA_COURSE.TEA_ID AND TEA_COURSE.CLA_ID=STUDENT.CLA_ID AND TEA_COURSE.COUR_ID=115
AND SEX=0
6.检索选修课程包含Liu老师的学生的学号
SELECT STUDENT.ID
FROM TEA_COURSE,TEACHER,STUDENT
WHERE TEACHER.TEA_ID=TEA_COURSE.TEA_ID AND TEA_COURSE.CLA_ID=STUDENT.CLA_ID AND TEACHER.TEA_NAME='LIUWEI'
7.检索姓名以L开头的所有学号和年龄
SELECT STUDENT.NAME,DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2')
FROM STUDENT
WHERE NAME LIKE 'l%'
8.查询成绩为空的学号
SELECT *
FROM GRADE
WHERE GRADE is null
9.查询大于女同学平均年龄的男同学的姓名和年龄
SELECT ID,NAME, DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2')
FROM TEA_COURSE,TEACHER,STUDENT
WHERE TEACHER.TEA_ID=TEA_COURSE.TEA_ID AND TEA_COURSE.CLA_ID=STUDENT.CLA_ID AND TEA_COURSE.COUR_ID=115
AND SEX=1 and DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2')> (SELECT AVG(DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2'))
FROM TEA_COURSE,TEACHER,STUDENT
WHERE TEACHER.TEA_ID=TEA_COURSE.TEA_ID AND TEA_COURSE.CLA_ID=STUDENT.CLA_ID AND TEA_COURSE.COUR_ID=115
AND SEX=0)
10.查询大于所有女同学年龄的男同学的姓名和年龄
SELECT ID,NAME, DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2')
FROM TEA_COURSE,TEACHER,STUDENT
WHERE TEACHER.TEA_ID=TEA_COURSE.TEA_ID AND TEA_COURSE.CLA_ID=STUDENT.CLA_ID AND TEA_COURSE.COUR_ID=115
AND SEX=1 and DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2')>all (SELECT (DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2'))
FROM TEA_COURSE,TEACHER,STUDENT
WHERE TEACHER.TEA_ID=TEA_COURSE.TEA_ID AND TEA_COURSE.CLA_ID=STUDENT.CLA_ID AND TEA_COURSE.COUR_ID=115
AND SEX=0)
11.所有男生所学的课程号和课程名
select distinct COURSE.COUR_ID,course.COURSENAME
from COURSE,TEA_COURSE,STUDENT
WHERE STUDENT.SEX=1 AND STUDENT.CLA_ID=TEA_COURSE.CLA_ID AND TEA_COURSE.COUR_ID=COURSE.COUR_ID
12.查询所有男生所学课程任课教师的姓名
select distinct TEACHER.TEA_NAME
from COURSE,TEA_COURSE,STUDENT,teacher
WHERE STUDENT.SEX=1 AND STUDENT.CLA_ID=TEA_COURSE.CLA_ID AND TEA_COURSE.COUR_ID=COURSE.COUR_ID AND TEAcher.TEA_ID=TEA_COURSE.TEA_ID
13.查询低于全部课程平均成绩的课程号和成绩
where GRADE is not null
group BY COUR_ID
having avg(grade) <=all (select avg(grade)
FROM GRADE
where GRADE is not null
group BY COUR_ID )
14.查询又不及格成绩的课程号与成绩
select cour_id,grade
FROM GRADE
WHERE GRADE IS NOT NULL AND GRADE<60
15.查询年纪最大的女同学的姓名与年龄
select id,name,DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2') as age
FROM student
WHERE sex=0 and BORNDATA<=all (select BORNDATA
FROM student
where sex=0)
16.查询女同学的平均年龄
select avg(DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2'))
FROM student
WHERE sex=0
17.查询没有学生学的课程
select cour_id,COURSENAME
from course
where cour_id not in (select cour_id from TEA_COURSE)
