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))
 
 不一定都对,有错欢迎指正

最新文章

  1. web前端历史的总结
  2. git的一些常用方法
  3. 重构第16天 封装条件(Encapsulate Conditional)
  4. Python 基本类型转换
  5. IOS第三方地图-百度地图集成
  6. Particle designer 粒子工具中属性对应功能的简单介绍
  7. ubuntu14_pip 安装
  8. InstallShield Clone dialog
  9. 三.GC相关之三分钟认识GC算法
  10. SQL 软解析和硬解析详解
  11. linxu下redis安装实战
  12. 对比剖析Swarm Kubernetes Marathon编排引擎
  13. bzoj 2286: [Sdoi2011]消耗战 虚树+树dp
  14. 镜像文件挂载及本地yum搭建
  15. Delphi把一张PNG横向分割成N张透明通道的图片
  16. 在springMVC中使用自定义注解来进行登录拦截控制
  17. 一次真实的蓝屏分析 ntkrnlmp.exe
  18. scala &quot;←&quot; &quot;&lt;-&quot;
  19. Custom Draw 基础(转载)
  20. CSS3 3D旋转下拉菜单

热门文章

  1. P3089 [USACO13NOV]POGO的牛Pogo-Cow
  2. 谷歌AMP和百度MIP是什么鬼?
  3. [Apio2012]dispatching 左偏树做法
  4. Spring RedisTemplate操作-xml配置(1)
  5. SQL Server 基础之《学生表-教师表-课程表-选课表》(二)
  6. JavaScript编写风格指南 (二)
  7. iOS 提交应用过程出现的错误及#解决方案#images can't contain alpha channels or transparencies
  8. HDU 1863 畅通工程 最下生成树问题
  9. 第11月第8天 ffmpeg ffplay
  10. Python网络通信 (一)