数据库简单练习 建表+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))  不一定都对，有错欢迎指正