课本p72实验3答案.sql

SQL SERVER2000实用教程蒋文沛主编课

create database STUINFO
on primary
(name=STUINFO,filename='d:\STUINFO.mdf')
log on
(name=STUINFO,filename='d:\STUINFO_log.ldf')
for attach
go
SELECT * FROM T_STUDENT
SELECT * FROM T_COURSE
SELECT * FROM T_SCORE

S_NUMBER,S_NAME,SEX,BIRTHDAY,
C_NUMBER,C_NAME,TEACHER,HOURS,CREDIT
S_NUMBER,C_NUMBER,SCORE


--(1)
SELECT S_NAME,BIRTHDAY,YEAR(GETDATE())-YEAR(BIRTHDAY) 年龄 FROM T_STUDENT WHERE SEX='男'
--(2)
SELECT *,COUNT(SEX) FROM T_STUDENT GROUP BY S_NUMBER,S_NAME,SEX,BIRTHDAY HAVING SEX='女' COMPUTE SUM(COUNT(SEX))

SELECT * FROM T_STUDENT WHERE SEX='女' COMPUTE COUNT(SEX)

--(3)
SELECT C_NAME,SCORE FROM T_COURSE,T_SCORE WHERE T_COURSE.C_NUMBER=T_SCORE.C_NUMBER AND C_NAME='SQL SERVER' COMPUTE SUM(SCORE) COMPUTE AVG(SCORE)

SELECT COUNT(*)AS 及格学生人数 FROM T_SCORE,T_COURSE WHERE T_SCORE.C_NUMBER=T_COURSE.C_NUMBER AND SCORE>=60 AND C_NAME='SQL SERVER'
SELECT COUNT(*)AS 不及格学生人数 FROM T_SCORE,T_COURSE WHERE T_SCORE.C_NUMBER=T_COURSE.C_NUMBER AND SCORE<60 AND C_NAME='SQL SERVER'

--(4)
SELECT S_NAME,SEX,C_NAME,SCORE FROM T_STUDENT,T_COURSE,T_SCORE WHERE T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=T_SCORE.C_NUMBER AND C_NAME='SQL SERVER' AND SEX='男' COMPUTE SUM(SCORE) COMPUTE AVG(SCORE)

SELECT S_NAME,SEX,C_NAME,SCORE FROM T_STUDENT,T_COURSE,T_SCORE WHERE T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=T_SCORE.C_NUMBER AND C_NAME='SQL SERVER' AND SEX='女' COMPUTE SUM(SCORE) COMPUTE AVG(SCORE)

--（5）
SELECT S_NAME,SEX,C_NAME,SCORE FROM T_STUDENT,T_COURSE,T_SCORE WHERE T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=T_SCORE.C_NUMBER AND S_NAME LIKE '李%' AND SEX='男'

--(6)
SELECT S_NAME,C_NAME,SCORE FROM T_STUDENT,T_COURSE,T_SCORE WHERE T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER AND T_COURSE.C_NUMBER=T_SCORE.C_NUMBER AND SCORE<60

--(7)
SELECT * FROM T_STUDENT GROUP BY S_NUMBER,S_NAME,SEX,BIRTHDAY HAVING SEX='男'

--(8)
SELECT TOP 40 PERCENT S_NUMBER,S_NAME INTO T_STUDENT1 FROM T_STUDENT
SELECT * FROM T_STUDENT1