查询表A中存在ID重复三次以上的记录
Select *
From A
Where id in(select ID from A group by id having count(id)>3)
一.有以下几张表及表结构
Student(Sid,Sname,Sage,Ssex) 学生表
Course(Cid,Cname,Tid) 课程表
SC(Sid,Cid,Score) 成绩表
Teacher(Tid,Tname) 教师表
题目:
1.查询“某1”课程比“某2”课程成绩高的所有学生的学号;
select a.Sid from(select Sid,Score from SC where Cid=1) a, (select Sid,Score from SC where Cid=3) 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.Cid group by Student.Sid,Sname;
4.查询姓“李”的老师的个数;
select count(Teacher.Tid)from Teacher where Teacher.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 = '和平' );
distinct:去重复
6.查询学过“```”并且也学过编号“```”课程的同学的学号、姓名;
select a.Sid,a.Sname from(select Student.Sname,Student.Sid from Student,Course,SC where Cname='数学'and SC.Sia=Student.Sid and SC.Cid = Course.Cid) a ,
(select Student.Sname,Student.Sid from Student,Course,SC where Cname='毛概' and SC.Sid = Student.Sid and SC.Cid=Course.Cid) b where a.Sid=b.Sid;
7.查询学过“叶平”老师所教的所有课的同学的学号、姓名;
//select a.Sid,a.Sname from(select Student.Sid,Student.Sname from Student,Teacher,Course,SC where Teacher.Tname='叶平'and Teacher.Tid=Course.Tid and Course.Cid=SC.Cid andStudent.Sid=SC.Sid)a;
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