createtable student(sid number(3),sname varchar2(10),age number(2),sex char(3),department varchar2(10),birthplace varchar2(10));insertinto s values(100,'曾华',21,'男','信息系','威海');insertinto s values(105,'张明',20,'男','信息系','烟台');insertinto s values(108,'王小丽',21,'女','旅游系','威海');insertinto s values(101,'李军',19,'男','金融系','青岛');insertinto s values(107,'王芳',24,'女','机电系','临沂');insertinto s values(102,'刘红英',22,'男','信息系','枣庄');insertinto s values(109,'唐奇',21,'男','机电系','威海');insertinto s values(103,'赵晨',21,'女','金融系','济南');
createtable c(cid number(4),cname varchar2(20),teacher varchar2(10));insertinto c values(3105,'计算机导论','王萍');insertinto c values(3245,'操作系统','李诚');insertinto c values(6166,'英语','张旭');insertinto c values(9888,'数学','刘冰');
四、完成查询
李老师所教的课程号、课程名称
select cid,cname,teacher from c where teacher like'李%';
年龄大于23岁的女学生的学号和姓名
select sid,sname,age,sex from s where age>23and sex='女';
“王小丽”所选修的全部课程名称
select cname from c,sc,s where c.cid=sc.cid and sc.sid=s.sid and sname='王小丽';
所有成绩都在80分以上的学生姓名及所在系
select sname,department from s where sid=(select sid from sc groupby sid havingmin(grade)>=80);
没有选修“操作系统”课的学生姓名
selectdistinct s.sid, sname from s where s.sid notin(select sc.sid from sc,c where cname='操作系统'and sc.cid=c.cid);
与“王小丽”同乡的男生姓名及所在系
select sname,department from s where sex='男'and birthplace=(select birthplace from s where sname='王小丽');
英语成绩比数学成绩好的学生
select*from s where(select grade from sc join c on sc.cid=c.cid where sc.sid=s.sid and cname='英语')>(select grade from sc join c on sc.cid=c.cid where sc.sid=s.sid and cname='数学');
选修同一门课程时,女生比男生成绩好的学生名单
selectDISTINCT sname from s,sc x where sex='女'and s.sid=x.sid and grade>all(select grade from sc,s where sex='男'and sc.cid=x.cid and sc.sid=s.sid);
至少选修两门以上课程的学生姓名、性别
select sname,sex from s where sid in(select sid from sc groupby sid havingcount(sid)>=2);
选修了李老师所讲课程的学生人数;
selectcount(*)from sc where cid=(select cid from c where teacher like'李%');
没有选修李老师所讲课程的学生人数;
selectcount(DISTINCT sid)from sc where sid notin(select sid from sc,c where sc.cid=c.cid and teacher like'李%');
“操作系统”课程得最高分的学生姓名、性别、所在系;
select sname,sex,department from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and cname='操作系统'and grade=(selectmax(grade)from sc,c where sc.cid=c.cid and cname='操作系统');