免费预览已结束,剩余11页可下载查看
下载本文档
版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领
文档简介
删除除了学号字段以外,其它字段都相同的冗余记录,只保留一条!(也就是要删除凤姐和田七中一条重复数据只留一条)要求结果数据:原始数据:CREATE TABLE tbl_students ( id number(32) NOT NULL, name varchar(10) DEFAULT NULL, sax varchar(10) DEFAULT NULL, age number(6) DEFAULT NULL, PRIMARY KEY (id)insert into tbl_students (id, name, sax, age) values(2,李四,男,21);insert into tbl_students (id, name, sax, age) values(3,张三,女,17);insert into tbl_students (id, name, sax, age) values(4,李四,男,12);insert into tbl_students (id, name, sax, age) values(6,凤姐,女,20);insert into tbl_students (id, name, sax, age) values(5,凤姐,女,20);insert into tbl_students (id, name, sax, age) values(7,田七,男,18);insert into tbl_students (id, name, sax, age) values(1,田七,男,18);insert into tbl_students (id, name, sax, age) values(8,张三,男,17);答案:mySqlDELETE FROM student WHERE sid NOT IN (SELECT sid FROM (SELECT MIN(sid) sid FROM student GROUP BY sName,sSex )t)oracle:DELETE FROM student WHERE sid NOT IN(SELECT MIN(sid) sid FROM student GROUP BY sName,sSex ) 查询各科成绩都及格的学员(要求查询出参加考试的各科成绩都高于60分,不管参加了多少科考试)要求结果:表:CREATE TABLE tbl_score ( id NUMBER(10) NOT NULL, username varchar(20) DEFAULT NULL, course varchar(20) DEFAULT NULL, score NUMBER(10) DEFAULT NULL, PRIMARY KEY (id)数据:insert into tbl_score (id, username, course, score) values(1,张三,语文,50);insert into tbl_score (id, username, course, score) values(2,张三,数学,80);insert into tbl_score (id, username, course, score) values(3,张三,英语,90);insert into tbl_score (id, username, course, score) values(4,李四,语文,70);insert into tbl_score (id, username, course, score) values(5,李四,数学,80);insert into tbl_score (id, username, course, score) values(6,李四,英语,80);insert into tbl_score (id, username, course, score) values(7,王五,语文,50);insert into tbl_score (id, username, course, score) values(8,王五,英语,70);insert into tbl_score (id, username, course, score) values(9,赵六,数学,90);答案:select username,scor from tbl where id not in (select id from tbl where score b.score AND a.sid=b.sid; 2、查询平均成绩大于60分的同学的学号和平均成绩; select sid,avg(score) from sc group by sid having avg(score) 60; 3、查询所有同学的学号、姓名、选课数、总成绩; select Student.sid,Student.Sname,count(SC.cid),sum(score) from Student left Outer join SC on Student.sid=SC.sid group by Student.sid,Sname 4、查询姓“李”的老师的个数; select count(distinct(Tname) from Teacher where Tname like 李%; 5、查询没学过“叶平”老师课的同学的学号、姓名; select Student.sid,Student.Sname from Student where sid not in (select distinct( SC.sid) from SC,Course,Teacher where SC.cid=Course.cid and Teacher.tid=Course.tid and Teacher.Tname=叶平); 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; A:select Student.sid,Student.Sname from Student,SC where Student.sid=SC.sid and SC.cid=001and exists( Select * from SC as SC_2 where SC_2.sid=SC.sid and SC_2.cid=002); B:SELECT s.sid,s.sName FROM student s,(SELECT sid,COUNT(cid) FROM sc WHERE cid IN (001,002) GROUP BY sid HAVING COUNT(cid)=2) t WHERE s.sid = t.sid7、查询学过“叶平”老师所教的所有课的同学的学号、姓名; select sid,Sname from Student where sid in (select sid from SC ,Course ,Teacher where SC.cid=Course.cid and Teacher.tid=Course.tid and Teacher.Tname=叶平 group by sid having count(SC.cid)=(select count(cid) from Course,Teacher where Teacher.tid=Course.tid and Tname=叶平); 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; 1Select sid,Sname from (select Student.sid,Student.Sname,score ,(select score from SC SC_2 where SC_2.sid=Student.sid and SC_2.cid=002) score2 from Student,SC where Student.sid=SC.sid and cid=001) S_2 where score2 SELECT s.sid,s.sName FROM student s, (SELECT sid,score FROM sc WHERE cid = 001) sc_1,(SELECT sid,score FROM sc WHERE cid = 002) sc_2WHERE sc_1.sid = sc_2.sid AND s.sid = sc_2.sid AND sc_2.score 60); 10、查询没有学全所有课的同学的学号、姓名; 1select Student.sid,Student.Sname from Student,SC where Student.sid=SC.sid group by Student.sid,Student.Sname having count(cid) SELECT s.sid,s.sname FROM student s,(SELECT sid,COUNT(cid) FROM sc GROUP BY sid HAVING COUNT(cid) SELECT t.cid AS 课程号,MAX(course.Cname)AS 课程名,nvl(AVG(score),0) AS 平均成绩 ,100 * SUM(CASE WHEN nvl(score,0)=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 FROM SC T,Course WHERE t.cid=course.cid GROUP BY t.cid ORDER BY 100 * SUM(CASE WHEN nvl(score,0)=60 THEN 1 ELSE 0 END)/COUNT(*) DESC MysqlSELECT t.cid AS 课程号,MAX(course.Cname)AS 课程名,IFNULL(AVG(score),0) AS 平均成绩 ,100 * SUM(CASE WHEN IFNULL(score,0)=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数 FROM SC T,Course WHERE t.cid=course.cid GROUP BY t.cid ORDER BY 100 * SUM(CASE WHEN IFNULL(score,0)=60 THEN 1 ELSE 0 END)/COUNT(*) DESC 20、查询如下课程平均成绩和及格率的百分数(用1行显示): 企业管理(001),马克思(002),OO&UML (003),数据库(004) SELECT SUM(CASE WHEN cid =001 THEN score ELSE 0 END)/SUM(CASE cid WHEN 001 THEN 1 ELSE 0 END) AS 企业管理平均分 ,100 * SUM(CASE WHEN cid = 001 AND score = 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = 001 THEN 1 ELSE 0 END) AS 企业管理及格百分数 ,SUM(CASE WHEN cid = 002 THEN score ELSE 0 END)/SUM(CASE cid WHEN 002 THEN 1 ELSE 0 END) AS 马克思平均分 ,100 * SUM(CASE WHEN cid = 002 AND score = 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = 002 THEN 1 ELSE 0 END) AS 马克思及格百分数 ,SUM(CASE WHEN cid = 003 THEN score ELSE 0 END)/SUM(CASE cid WHEN 003 THEN 1 ELSE 0 END) AS UML平均分 ,100 * SUM(CASE WHEN cid = 003 AND score = 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = 003 THEN 1 ELSE 0 END) AS UML及格百分数 ,SUM(CASE WHEN cid = 004 THEN score ELSE 0 END)/SUM(CASE cid WHEN 004 THEN 1 ELSE 0 END) AS 数据库平均分 ,100 * SUM(CASE WHEN cid = 004 AND score = 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = 004 THEN 1 ELSE 0 END) AS 数据库及格百分数 FROM SC 21、查询不同老师所教不同课程平均分从高到低显示 要求显示:教师ID,教师姓名,课程ID,课程名称,平均成绩SELECT MAX(t.tid) 教师ID,MAX(t.tName) 教师姓名,c.cid 课程ID, MAX(c.cName) 课程名称 ,AVG(sc.score) 平均成绩FROM sc,course c,teacher t WHERE sc.cid = c.cid AND c.tid = t.tid GROUP BY c.tid,c.cidORDER BY AVG(sc.score) DESC23、 统计列印各科成绩,各分数段人数:课程ID,课程名称,100-85,85-70,70-60, 60 SELECT SC.cid as 课程ID, Cname as 课程名称 ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS 100 - 85 ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS 85 - 70 ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS 70 - 60 ,SUM(CASE WHEN score 131、1981年出生的学生名单(注:Student表中Sage列的类型是datetime) Mysqlselect Sname, CONVERT(char (11),DATEPART(year,Sage) as age from student where CONVERT(char(11),DATEPART(year,Sage)=1981; Oracleselect * from student where substr(to_char(sage,yyyy-MM-dd),1,4)= 198132、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列 Select cid,Avg(score) from SC group by cid order by Avg(score),cid DESC ; 33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 select Sname,SC.sid ,avg(score) from Student,SC where Student.sid=SC.sid group by SC.sid,Sname having avg(score)85; 34、查询课程名称为“数据库”,且分数低于60的学生姓名和分数 Select Sname,isnull(score,0) from Student,SC,Course where SC.sid=Student.sid and SC.cid=Course.cid and Course.Cname=数据库and score =70 AND SC.sid=student.sid; 37、查询不及格的课程,并按课程号从大到小排列 select cid from sc where scor e 80 and cid=003; 39、求选了课程的学生人数 select count(*) from sc; 40、查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩 select Student.Sname,score from Student,SC,Course C,Teacher where Student.sid=SC.sid and SC.cid=C.cid and C.tid=Teacher.tid and Teacher.Tname=叶平 and SC.score=(select max(score)from SC where cid=C.cid ); 41、查询各个课程及相应的选修人数 select count(*) from sc group by cid; 42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 select distinct A.sid,B.score from SC A ,SC B where A.Score=B.Score and A.cid B.cid ; 43、查询每门功课成绩最好的前两名 SELECT * FROM sc t1WHERE ( SELECT COUNT(*) FROM sc t2 WHERE t1.cid=t2.cid AND t2.score=t1.score) 10 ORDER BY COUNT(*) DESC,cid 45、检索至少选修两门课程的学生学号 select sid from sc group by sid having count(*) = 2 46、查询全部学生都选修的课程的课程号和课程名 SELECT s.sName,c.cName, COUNT(*) FROM student s,course c, sc WHERE s.sid = sc.sid AND sc.cid = c.cid GROUP BY sc.cid HAVING COUNT(*) = (SELECT COUNT(*) FROM student)47、查询没学过“叶平”老师讲授的任一门课程的学生姓名 SELECT DISTINCT Sname FROM Student WHERE sid NOT IN (SELECT sid FROM Course,Teacher,SC WHERE Course.tid=Teacher.tid AND SC.cid=course.cid AND Tname=叶平); 48、查询两门以上不及格课程的同学的学号及其平均成绩 select sid,avg(ifnull(score,0) from SC where sid in (select sid from SC where score 2)group by sid; 49、检索“004”课程分数小于60,按分数降序排列的同学学号 select sid from SC where cid=004and score 60 order by score desc; 50、删除“1002”同学的“001”课程的成绩 delete from Sc where sid=1002 and cid=001; 行转列CREATE TABLE stu_score ( grade_id varchar(10) DEFAULT NULL, subject_name varchar(10) DEFAULT NULL, max_score int(10) DEFAULT NULL)insert into stu_score(grade_id,subject_name,max_score) values(1,语文,98);insert into stu_score(grade_id,subject_name,max_score) values(2,数学,95);insert into stu_score(grade_id,subject_name,max_score) values(2,政治,87);insert into stu_score(grade_id,subject_name,max_score) values(5,语文,97);insert into stu_score(grade_id,subject_name,max_score) values(5,数学,100);insert into stu_score(grade_id,subject_n
温馨提示
- 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
- 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
- 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
- 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
- 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
- 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
- 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
最新文档
- 《课程与教学论》课件 第八章 教研活动
- 登革热流行病学与防制
- 2027年寒假开学摸底考试七年级英语基础卷及答案
- 药物研发新策略与挑战探讨
- 游戏策划师产品开发进度与用户满意度评估表
- 电力系统设备维护与故障处理流程
- 协商拓展市场计划甲方企业乙方团队商洽函(7篇)
- 家用储能电池安全使用规范手册
- 关于终止租赁合同的通知(5篇)
- 【二上语文】课内仿写句子晨读单【知识归纳-专项练习】
- 2026年植物保护(植物病理学基础)试题及答案
- 2026中国航空复合材料适航认证与市场竞争格局报告
- 中国融通笔试题及答案
- 2026重庆保税港区集团校园招聘20人笔试参考题库及答案解析
- ISO-IEC 27701-2025 隐私信息管理体系手册 + 全套程序文件模板
- 中国皮肤型红斑狼疮诊疗指南 2026 年版
- 2026年济南市检察院书记员考试题(附答案)
- 职业病危害现状评价任勇
- 医疗器械经营质量管理制度工作程序手册
- 水稳、沥青施工方案
- 2026年江苏省建筑三类安全员考试题库(含答案)
评论
0/150
提交评论