1.檢索liu老師所傷的課程號(hào)和課程名
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歲的學(xué)生的學(xué)號(hào)和姓名
SELECT STUDENT.CLA_ID,STUDENT.NAME
FROM STUDENT
WHERE DATEDIFF(Y,STUDENT.BORNDATA,'1997/3/2')>10

3.檢索學(xué)好為32010103的學(xué)生所學(xué)的課程號(hào)與任課教師名
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老師所受課程中的一門課程的女大學(xué)生姓名
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課程的女學(xué)生的平均年齡
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老師的學(xué)生的學(xué)號(hào)

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開頭的所有學(xué)號(hào)和年齡
SELECT STUDENT.NAME,DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2')
FROM STUDENT
WHERE NAME LIKE 'l%'

8.查詢成績(jī)?yōu)榭盏膶W(xué)號(hào)
SELECT *
FROM GRADE
WHERE GRADE is null

9.查詢大于女同學(xué)平均年齡的男同學(xué)的姓名和年齡
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.查詢大于所有女同學(xué)年齡的男同學(xué)的姓名和年齡

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.所有男生所學(xué)的課程號(hào)和課程名
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.查詢所有男生所學(xué)課程任課教師的姓名
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.查詢低于全部課程平均成績(jī)的課程號(hào)和成績(jī)

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.查詢又不及格成績(jī)的課程號(hào)與成績(jī)
select cour_id,grade
FROM GRADE
WHERE GRADE IS NOT NULL AND GRADE<60

15.查詢年紀(jì)最大的女同學(xué)的姓名與年齡
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.查詢女同學(xué)的平均年齡
select avg(DATEDIFF(YY,STUDENT.BORNDATA,'2017-3-2'))
FROM student
WHERE sex=0

17.查詢沒有學(xué)生學(xué)的課程
select cour_id,COURSENAME
from course
where cour_id not in (select cour_id from TEA_COURSE)