显示"计算机工程系"老师所教课程的成绩表.,数据库简单练习 建表+select(示例代码)...

create table student (

sno int primary key,

sname char(20),

sex char(2),

birthday datetime,

class int

)

create table teacher (

tno int primary key,

tname char(20),

sex char(2),

birthday datetime,

prof char(10),

depart char(20)

)

create table course (

cno char(20) primary key,

cname char(20),

tno int foreign key references teacher(tno)

)

create table score (

sno int foreign key references student(sno),

cno char(20) foreign key references course(cno),

degree int

)

insert into student values(108,‘曾华‘,‘男‘,‘09/01/1977‘,95033);

insert into student values(105,‘匡明‘,‘男‘,‘10/02/1975‘,95031);

insert into student values(107,‘王丽‘,‘女‘,‘01/23/1976‘,95033);

insert into student values(101,‘李军‘,‘男‘,‘02/20/1976‘,95033);

insert into student values(109,‘王芳‘,‘女‘,‘02/10/1975‘,95031);

insert into student values(103,‘陆军‘,‘男‘,‘06/03/1974‘,95031);

insert into teacher values(804,‘李诚‘,‘男‘,‘12/02/1958‘,‘副教授‘,‘计算机系‘);

insert into teacher values(856,‘李旭‘,‘男‘,‘03/12/1969‘,‘讲师‘,‘电子工程系‘);

insert into teacher values(825,‘王萍‘,‘女‘,‘05/05/1972‘,‘助教‘,‘计算机系‘);

insert into teacher values(831,‘刘冰‘,‘女‘,‘08/14/1977‘,‘助教‘,‘电子工程系‘);

insert into course values(‘3-105‘,‘计算机导论‘,825);

insert into course values(‘3-245‘,‘操作系统‘,804);

insert into course values(‘6-166‘,‘数字电路‘,856);

insert into course values(‘9-888‘,‘高等数学‘,825);

insert into score values(103,‘3-245‘,86);

insert into score values(109,‘3-245‘,68);

insert into score values(105,‘3-245‘,75);

insert into score values(103,‘3-105‘,92);

insert into score values(105,‘3-105‘,88);

insert into score values(109,‘3-105‘,76);

insert into score values(101,‘3-105‘,64);

insert into score values(107,‘3-105‘,91);

insert into score values(108,‘3-105‘,78);

insert into score values(101,‘6-166‘,85);

insert into score values(107,‘6-166‘,79);

insert into score values(108,‘6-166‘,81 );

drop table student

drop table teacher

drop table course

drop table score

--1、列出student表中所有记录的

--sname、sex和class列。

select sname,sex,class from student

--2、显示教师所有的单位即不重复的depart列。

--select distinct depart from teacher

--3、显示学生表的所有记录。

--select * from student

--4.显示score表中成绩在60到80之间的所有记录

--select * from score where degree>=60 and degree<=80

--5.显示score表中成绩在为85，86，88的所有记录

--select * from score where degree in (85,86,88)

--6.显示   表中   班或性别为女的同学的记录

--select *from student where class=‘95033‘and sex=‘女‘

--7.以class降序显示   表中所有记录

-- select * from student order by class desc

--8.以cno升序 degree降序显示   表中所有记录

-- select * from student order by cno asc,degree desc

--9.显示  班的学生人数

--select COUNT(*),COUNT(sno)from student where class=‘95033‘

--10.显示score表中的最高分的学生学号和课程号

--select max(degree),nim(degree),avg(degree) from score

--select * from score where degree=(select max(degree) from score)

--11.显示“3-105”号课程的平均分

--12.显示score表中至少有5名学生选修并以3开头的课程号的平均分数

--select cno,count(*),AVG(degree) from score where cno like ‘3%‘ group by cno having count(cno)>=5

--13.显示最低分大于70，最高分小于90的sno列

--select sno from score group by sno having max(degree)<90 and min(degree)>70

--select sno from score where max(degree)<90 and min(degree)>70

--14.显示所有学生的sname、cno和degree列

--select sname、cno、degree from score,student where student.sno=score.sno

--select sname、cno、degree from score join student on student.sno=score.sno

--15.显示所有学生的 sname,cname 和degree

--select sname,cname,DEGREE from student,score,course where student.sno=score.sno and score.cno=course.cno

--select sname,cname,DEGREE from student join score on student.sno=score.sno join course on score.cno=course.cno

--16.列出“95033”班所选课程的平均分

--select avg(degree) from score where sno in (select sno from student where class=‘95033‘)

--17.显示选修"3-105"课程的成绩高于“109”号同学成绩的所有同学的记录

--select * from score where degree>(select degree from score where sno=109 and cno=‘3-105‘) and cno =‘3-105‘

--18.显示score中选修多门课程的同学中分数为非最高分成绩的记录

select * from score where sno in (select sno from score group by sno having count(sno)>1 ) and degree not in (select max(degree) from score group by cno)

--20.显示出和学号“108”号同学同年出生的所有学生的sno、sname、birthday列

--select sno,sname,birthday from student where day(birthday)= (select day(birthday) from student where sno=108)

--21.显示“张旭”老师上课的学生的成绩

--select * from score where cno=(select cno form course where tno=(select tno from teacher where tname=‘XXX‘))

--select tracher.* ,course.* ,score.* from teacher,course,score where teacher.tno=course.tno and course.cno=score.cno and tname=‘XXX‘

--select tracher.* ,course.* ,score.* from teacher join course on teacher.tno=course.tno join score on course.cno=score.cno where tname=‘XXX‘

--22.显示选修某课程的同学人数多余5人的老师姓名

--select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*)>=5))

--23.显示“95033”班和“95031”班全体学生的记录

--select * from student where class =‘95033‘ or class=‘95031‘

--24.显示存在有85分以上成绩的课程编号

--select distindt cno from score where degree>85

--     相同的显示一次

--25.显示“计算机系”老师所教课程的成绩表

--select * from score where cno in(select cno from course where tno in(select tno from teacher where depart =‘计算机系‘))

--27.显示选修编号为“3-105”课程且成绩至少高于“3-245”课程的同学的cno、sno和degree，并按degree从高到低次序排列

--select cno,sno,degree from score where cno=‘3-105‘ and degree>any(select degree from c=score where cno=‘2_245‘)

--select cno,sno,degree from score where cno=‘3-105‘ and degree>(select min(degree) from c=score where cno=‘2_245‘)

--28.

--29、列出所有任课老师的tname和depart

--select tname,depart from teacher where tno in(select tno from course)

--30、列出所有未讲课老师的tname和depart

--select tname,depart from teacher where tno not in(select tno from course)

--31、列出所有老师和同学的 姓名、性别和生日。

--select sname,sex,birthday from student  union select tname,sex,birthday from teacher

--*32、检索所学课程包含学生“103”所学课程的学生学号

--select distinct sno  from score x where not exists(select * from score y where y.sno=103 and not exists(select * from score z where z.sno=x.sno and z.cno=y.cno))

--*33、检索选修所有课程的学生姓名

--select sname from student where sno in(select sno from score group by sno having count(*)=     (select count(*) from course))

不一定都对，有错欢迎指正