常用sql

一个表中的ID有多个记录，把所有这个id的记录查出，并显示共 有多少条记 录数（SQL实现）
1、SELECT id,COUNT(id) FROM table GROUP BY id HAVING COUNT(id) >1
2、SELECT * FROM (SELECT id,COUNT (id) sumid FROM table GROUP BY id ) t WHERE t. sumid >1
6、查询表A中存在ID重复三次以上的记录。（使用SQL实现）
1.SELECT id,COUNT (id) FROM table GROUP BY id HAVING COUNT (id)>3
2.SELECT * FROM (SELECT id,COUNT (id) sumid FROM table GROUP BY id) t WHERE t.sumid > 3

1.查询“001”课程比“002”课程成绩高的所有学生的学号;
select a.sid from(select sid,score from SC where cid=‘001’) a,(select sid, score3.from SC where cid=‘002’) b where a.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 sidnotin(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=‘001’ and 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 students (SELECT sid,COUNT (cid) FROM sc WHERE cid IN(‘001’,‘002’) GROUP BY sid HAVING COUNT (cid)>=2)t WHERE s.sid=t.sid
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
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”课程低的所有同学的学号、姓名;
1> Select 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<score;
2> SELECT s.sid,s.sName FROM students,
(SELECT sid,score FROM sc WHERE cid=‘001’) sc_1,(SELECT sid,score FROM sc WHERE cid=‘002’)sc_2 WHERE sc_1.sid=sc_2.sid AND s.sid=sc_2.sid AND sc_2.score<sc_1.score
9、查询所有课程成绩小于60分的同学的学号、姓名;
select sid,Sname from Student where sid not in(select Student.sid from Student ,SC where S.sid=SC.sid and score>60);
10、查询没有学全所有课的同学的学号、姓名;
1> select Student.sid,Student.Sname from Student,SC where Student.sid=SC.sid group by Student.sid,Student.Sname having count(cid)<(select count(cid) from Course);
2> SELECT s.sid,s.sname FROM students, (SELECT sid,COUNT (cid) FROM sc GROUP
BY sid HAVING COUNT(cid)<(SELECT COUNT(cid) FROM course))t WHERE s.sid=t.sid
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
select sid,Sname from Student,SC where Student.sid=SC.sid and cid in(select cid from SC where sid=‘1001’);
12、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
UPDATE sc (SELECT c.cid,AVG (score) avgs FROM sc,coursec,teachert WHERE sc.cid=c.cid AND c.tid=t.tid AND t.tName=‘叶平’ GROUP BY c.cid) sc_2 SET sc.score=sc_2 .avgs WHERE sc.cid=sc_2.cid
13、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
select sid from SC where cid in(select cid from SC where sid=‘1002’) group by sid having count()=(select count() from SCwhere sid=‘1002’);
14、删除学习“叶平”老师课的SC表记录;
DELETEF ROM sc WHERE sc.cid IN(SELECT sc.cid FROM coursec,teachert WHERE sc.cid=c.cid AND c.tid=t.tid AND t.tName=‘叶平’)
15、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示：学生ID,数据库,企业管理,英语,有效课程数,有效平均分
SELECT sid as 学生ID ,(SELECT score FROM SC WHERE SC.sid=t.sid AND cid=‘004’) AS 数据库 ( SELECT score FROM SC WHERE SC.sid=t.sid AND cid=‘001’) AS 企业管理 (SELECT score FROM SC WHERE SC.sid=t.sid AND cid=‘005’)AS 英语 COUNT(*) AS 有效课程数, AVG (t.score) AS 平均成绩 FROM SC AS t GROUP BY sid ORDER BY avg ( t.score)

16、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cid “课程ID”,max(score) “最高分”,min(score) “最低分” from sc group by cid 84.85.
17、按各科平均成绩从低到高和及格率的百分数从高到低排序86.oracle>87.
SELECT t.cid AS 课程号,MAX(course.Cname) AS 课程名,nvl(AVG(score),0) AS 平均成绩100SUM(CASE WHEN nvl (score,0)>=60 THEN 1 ELSE 0 END)/COUNT()AS及格百分数FROM SCT,Course WHERE t.cid=course.cid GROUP BY t.cid ORDER BY 100SUM(CASE WHEN nvl(score,0)>=60 THEN 1 ELSE 0 END)/COUNT()DESC Mysql>SELECT t.cid AS 课程号,MAX(course.Cname) AS 课程名,IFNULL(AVG(score),0)AS平均成绩 100SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT()AS及格百分数 FROM SCT,Course WHERE t.cid=course.cid GROUP BY t.cid ORDER BY 100SUM(CASE WHEN IF NULL(score,0)>=60THEN 1 ELSE 0 END)/COUNT() DESC
18、查询如下课程平均成绩和及格率的百分数(用"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企业管理平均分100SUM(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 马克思平均分,100SUM(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平均分,100SUM(CASE WHEN cid=‘003’ AND score>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid=‘003’ THEN 1 ELSE 0 END)AS UML 及格百分数108.,SUM(CASE WHEN cid=‘004’ THEN score ELSE 0 END)/SUM(CASE cid WHEN ‘004’ THEN 1 ELSE 0 END) AS 数据库平均分,100SUM(CASE WHEN cid=‘004’AND score>=60THEN 1 ELSE 0 END)/SUM(CASE WHEN cid=‘004’ THEN 1 ELSE 0 END) AS 数据库及格百分数 FROM SC
19、查询不同老师所教不同课程平均分从高到低显示要求显示：教师ID,教师姓名，课程ID,课程名称，平均成绩
SELECT MAX(t.tid) “教师ID”,MAX(t.tName) “教师姓名”,c.cid “课程ID”,MAX(c.cName) “课程名称”,AVG (sc.score) “平均成绩” FROM sc,coursec,teachert WHERE sc.cid=c.cid AND c.tid=t.tid GROUP BY c.tid,c.cid ORDER BY AVG (sc.score) DESC
20、统计列印各科成绩,各分数段人数:课程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<60 THEN 1 ELSE 0 END)AS [60-] FROM SC,Course where SC.cid=Course.cid GROUP BYSC.cid,Cname;
21、查询每门课程被选修的学生数
select cid,count(sid) from sc group by cid;
22、查询出只选修了一门课程的全部学生的学号和姓名
select SC.sid,Student.Sname,count(cid)AS 选课数from SC,Student where SC.sid=Student.sid group by SC.sid,Student.Sname having count (cid)=1;
23、查询男生、女生人数
Select count(Ssex) as 男生人数from Student group by Ssex having Ssex=‘男’;
Select count(Ssex)as 女生人数 from Student group by Ssex having Ssex=‘女’;
24、查询姓“张”的学生名单
SELECT Sname FROM Student WHERE Sname like ’ 张%’;
30、查询同名同性学生名单，并统计同名人数
SELECT sName,sSex,COUNT() FROM student GROUP BY sName,sSex HAVING COUNT()>11
25、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)Mysql>
select Sname,CONVERT (char(11),DATEPART (year,Sage)) as age from student where CONVERT (char(11),DATEPART (year,Sage))=‘1981’;149.Oracle>150.select * from student where substr(to_char(sage,‘yyyy-MM-dd’),1,4)=‘1981’
26、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列Select cid,Avg(score) from SC group by cid order by Avg(score),cid DESC;
27、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select Sname,SC.sid,avg(score) from Student,SC where Student.sid=SC.sid group by SC.sid,Sname having avg(score)>85;
28、查询课程名称为“数据库”，且分数低于60的学生姓名和分数161.SelectSname,isnull(score,0)162.fromStudent,SC,Course163.whereSC.sid=Student.sidandSC.cid=Course.cidandCourse.Cname='数据库’andscore<60;164.165.–
29、查询所有学生的选课情况;
SELECT SC.sid,SC.cid,Sname,Cname FROM SC,Student,Course168.where SC.sid=Student.sidandSC.cid=Course.cid;
30、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
SELECT distinct student.sid,student.Sname,SC.cid,SC.score FROM student,Sc WHERE SC.score>=70 AND SC.sid=student.sid;
31、查询不及格的课程，并按课程号从大到小排列
select cid from sc where score<60 order by cid;
32、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
select SC.sid,Student.Sname from SC,Student where SC.sid=Student.sid and Score>80 and cid=‘003’;
33、求选了课程的学生人数
select count() from sc;
34、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩select Student.Sname,score from Student,SC,CourseC,Teacher where Student.sid=SC.sid andSC.cid=C.cid and C.tid=Teacher.tid and Teacher.Tname=‘叶平’ and SC.score=(select max(score) from SC where cid=C.cid);
35、查询各个课程及相应的选修人数
select count() from sc group by cid;
36、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select distinct A.sid,B.score from SC A,SC B where A.Score=B.Score and A.cid<>B.cid;
37、查询每门功课成绩最好的前两名
SELECT * FROM sc t1 WHERE (SELECT COUNT () FROM sc
t2 WHERE t1.cid=t2.cid AND t2.score>=t1.score)<=2 ORDER BY t1.cid
38、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT cid “课程号”,COUNT ()" 选修人数" FROM sc GROUP BY cid HAVING COUNT()>10 ORDER BY COUNT() DESC,cid
39、检索至少选修两门课程的学生学号
select sid from sc group by sid having count()>=2
40、查询全部学生都选修的课程的课程号和课程名
SELECT s.sName,c.cName,COUNT() FROM students,coursec,sc WHERE s.sid=sc.sid AND sc.cid=c.cid GROUP BY sc.cid HAVING COUNT()=( SELECT COUNT()FROM student)
41、查询没学过“叶平”老师讲授的任一门课程的学生姓名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=‘叶平’);
42、查询两门以上不及格课程的同学的学号及其平均成绩
select sid,avg(ifnull(score,0)) from SC where sid in(select sid from SC where score<60 group by sid having count(*)>2) group by sid;
43、检索“004”课程分数小于60，按分数降序排列的同学学号
select sid from SC where cid=‘004’ and score<60 order by score desc;
44、删除“1002”同学的“001”课程的成绩
delete from Sc where sid=‘1002’ and cid=‘001’;