学籍管理系统数据库中的表。表Student_course,Student,Course ,Tearch_course和Teacher 。各个表的具体结构如下:
表1 student表(学生信息表)
1 简单查询操作
对EDUC 数据库实现以下查询: (1)求计算机系的学生学号和姓名;
select sno, sname from student
where sdept='IS'
(2)求选修C1 课程的学生学号和成绩,并要求对查询结果按成绩的降序排列,如果成绩相同则按学号的升序排列; select sno,score from student_course where cno='101'
ORDER BY score DESC,sno
(3)求选修课程C1 且成绩在80-90 之间的学生学号和成绩,并将成绩乘以系数0.75 输出;
select sno,score*0.75 from student_course
where cno='101' AND (score=80)
(4)求计算机系和数学系的姓张的学生的信息; SELECT *
FROM student
where (sdept='CS' OR sdept='MS' )AND sname LIKE '张%'
(5)求缺少了成绩的学生的学号和课程号。 SELECT sno,cno
FROM student_course where score IS NULL
(6)将成绩大于90分的学生成绩存入永久成绩表;将成绩小于90存入临时成绩表中。
SELECT score into prescore FROM student_course where score >90
SELECT score into #posscore FROM student_course where score
2 连接查询操作
对EDUC 数据库实现以下查询:
(1)查询每个学生的情况以及他(她)所选修的课程;
SELECT student.*,student_course.* FROM student, student_course
where student_course. sno =student . sno
(2)求学生的学号、姓名、选修的课程名及成绩;
SELECT student.sno,sname,course.cname,student_course.score FROM student,course,student_course
where student_course.sno=student.sno and student_course.cno= course.cno
(3)求选修C1 课程且成绩在90 分以上的学生学号、姓名及成绩; SELECT student.sno,sname,score FROM student, student_course
WHERE cno='102' AND score>90 and student_course.sno=student.sno
(4)查询每一门课的间接先行课。
SELECT first. cno , second . spno FROM course first, course second
WHERE first. spno = second. cno and second. spno is not NULL
3.子查询操作,在数据库EDUC 中实现查询: (1)求选修了高等数学的学生学号和姓名;
SELECT sno,sname from student
where sno in (SELECT sno from student_course
where cno in ( SELECT cno from course
where cname='高等数学'))
SELECT student.sno,sname
from student,course,student_course
where student.sno=student_course.sno AND student_course.cno=course.cno
AND course.cname='高等数学'
(2)求C1 课程的成绩高于张三的学生学号和成绩;
SELECT sno, score from student_course
where cno = '101' AND score > SOME ( SELECT score from student_course
where cno ='101' and sno in( SELECT sno from student
where sname=' 张楠' ))
(3)求其他系中比计算机系某一学生年龄小的学生信息(即求其它系中年龄小于计算机系年龄最大者的学生);some(any)均可以
SELECT * FROM student
where sdept != 'CS' and sage
(4)求其他系中比计算机系学生年龄都小的学生信息; SELECT * FROM student
where sdept != 'CS' and sage
(5)求选修了select sname
from student
where sno in (select sno from student_course where cno='102' )
C2 课程的学生姓名;
(6)求没有选修C2 课程的学生姓名;
select sname
from student
where sno not in (select sno from student_course where cno='102' )
(7)查询选修了全部课程的学生的姓名;
select sname from student
where sno in (select sno from student_course where cno is not null)
(8)求至少选修了学号为“1103”的学生所选修的全部课程的学生学号和姓名。
select sno, sname from student where sno in ( select sno
from student_course where cno in (select cno
from student_course where sno= '1103' ))
4.使用子句的查询操作,在数据库EDUC 中实现查询: (1) 求学生的总人数。
SELECT count(sno) FROM studen
(2) 求选修了课程的学生人数。
SELECT count (distinct sno) FROM student_course
(3) 求课程的课程号和选修该课程的人数。
SELECT cno,count (sno )
FROM student_course Group by cno
(4) 求选修课超过3 门课的学生学号
SELECT sno
FROM student_course
Group by sno having count (cno )>=3
学籍管理系统数据库中的表。表Student_course,Student,Course ,Tearch_course和Teacher 。各个表的具体结构如下:
表1 student表(学生信息表)
1 简单查询操作
对EDUC 数据库实现以下查询: (1)求计算机系的学生学号和姓名;
select sno, sname from student
where sdept='IS'
(2)求选修C1 课程的学生学号和成绩,并要求对查询结果按成绩的降序排列,如果成绩相同则按学号的升序排列; select sno,score from student_course where cno='101'
ORDER BY score DESC,sno
(3)求选修课程C1 且成绩在80-90 之间的学生学号和成绩,并将成绩乘以系数0.75 输出;
select sno,score*0.75 from student_course
where cno='101' AND (score=80)
(4)求计算机系和数学系的姓张的学生的信息; SELECT *
FROM student
where (sdept='CS' OR sdept='MS' )AND sname LIKE '张%'
(5)求缺少了成绩的学生的学号和课程号。 SELECT sno,cno
FROM student_course where score IS NULL
(6)将成绩大于90分的学生成绩存入永久成绩表;将成绩小于90存入临时成绩表中。
SELECT score into prescore FROM student_course where score >90
SELECT score into #posscore FROM student_course where score
2 连接查询操作
对EDUC 数据库实现以下查询:
(1)查询每个学生的情况以及他(她)所选修的课程;
SELECT student.*,student_course.* FROM student, student_course
where student_course. sno =student . sno
(2)求学生的学号、姓名、选修的课程名及成绩;
SELECT student.sno,sname,course.cname,student_course.score FROM student,course,student_course
where student_course.sno=student.sno and student_course.cno= course.cno
(3)求选修C1 课程且成绩在90 分以上的学生学号、姓名及成绩; SELECT student.sno,sname,score FROM student, student_course
WHERE cno='102' AND score>90 and student_course.sno=student.sno
(4)查询每一门课的间接先行课。
SELECT first. cno , second . spno FROM course first, course second
WHERE first. spno = second. cno and second. spno is not NULL
3.子查询操作,在数据库EDUC 中实现查询: (1)求选修了高等数学的学生学号和姓名;
SELECT sno,sname from student
where sno in (SELECT sno from student_course
where cno in ( SELECT cno from course
where cname='高等数学'))
SELECT student.sno,sname
from student,course,student_course
where student.sno=student_course.sno AND student_course.cno=course.cno
AND course.cname='高等数学'
(2)求C1 课程的成绩高于张三的学生学号和成绩;
SELECT sno, score from student_course
where cno = '101' AND score > SOME ( SELECT score from student_course
where cno ='101' and sno in( SELECT sno from student
where sname=' 张楠' ))
(3)求其他系中比计算机系某一学生年龄小的学生信息(即求其它系中年龄小于计算机系年龄最大者的学生);some(any)均可以
SELECT * FROM student
where sdept != 'CS' and sage
(4)求其他系中比计算机系学生年龄都小的学生信息; SELECT * FROM student
where sdept != 'CS' and sage
(5)求选修了select sname
from student
where sno in (select sno from student_course where cno='102' )
C2 课程的学生姓名;
(6)求没有选修C2 课程的学生姓名;
select sname
from student
where sno not in (select sno from student_course where cno='102' )
(7)查询选修了全部课程的学生的姓名;
select sname from student
where sno in (select sno from student_course where cno is not null)
(8)求至少选修了学号为“1103”的学生所选修的全部课程的学生学号和姓名。
select sno, sname from student where sno in ( select sno
from student_course where cno in (select cno
from student_course where sno= '1103' ))
4.使用子句的查询操作,在数据库EDUC 中实现查询: (1) 求学生的总人数。
SELECT count(sno) FROM studen
(2) 求选修了课程的学生人数。
SELECT count (distinct sno) FROM student_course
(3) 求课程的课程号和选修该课程的人数。
SELECT cno,count (sno )
FROM student_course Group by cno
(4) 求选修课超过3 门课的学生学号
SELECT sno
FROM student_course
Group by sno having count (cno )>=3