关于学生表、课程表、成绩表的查询

admin

学生表tbl_student：

学号：sid	姓名：sname	性别:sex	年龄:age	组织部门:dept

课程表tbl_course：

课程编号：cid	课程名称：cname

成绩表tbl_grade：

成绩编号：gid	学号：sid	课程编号：cid	成绩：grade

1. 取出学生个数

2. 计算每个学生的总成绩

3. 查询选修了“计算机原理”的学生学号和姓名

4. 查询“周星驰”同学选修了的课程名字

5. 查询选修了五门课程的学生学号和姓名

6. 查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩

7. 如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。

建表sql：

create table tbl_student(
 sid   number,
 sname varchar2(10),
 sex   varchar2(10),
 age   number,
 dept  number
);
create table tbl_course(
 cid   number,
 cname varchar2(10)
);
create table tbl_grade(
 gid   number,
 sid   number,
 cid   number,
 grade number
);
insert into tbl_student (sid,sname)values(1,'张三');
insert into tbl_student (sid,sname)values(2,'李四');
insert into tbl_student (sid,sname)values(3,'王五');
insert into tbl_student (sid,sname)values(4,'赵六');

insert into tbl_course (cid,cname)values(1,'英语');
insert into tbl_course (cid,cname)values(2,'语文');
insert into tbl_course (cid,cname)values(3,'数学');

insert into tbl_grade (gid,sid,cid,grade) values(1,1,1,78);
insert into tbl_grade (gid,sid,cid,grade) values(2,1,2,74);
insert into tbl_grade (gid,sid,cid,grade) values(3,1,3,88);
insert into tbl_grade (gid,sid,cid,grade) values(4,2,1,98);
insert into tbl_grade (gid,sid,cid,grade) values(5,2,2,56);
insert into tbl_grade (gid,sid,cid,grade) values(6,2,3,48);
insert into tbl_grade (gid,sid,cid,grade) values(7,3,1,74);
insert into tbl_grade (gid,sid,cid,grade) values(8,3,2,97);
insert into tbl_grade (gid,sid,cid,grade) values(9,4,1,28);
insert into tbl_grade (gid,sid,cid,grade) values(10,4,2,79);
insert into tbl_grade (gid,sid,cid,grade) values(11,4,3,76);
commit;

 

yjz123456

    --取出学生个数
select count(1) from tbl_student;
    --计算每个学生的总成绩
select s.sname,sum(g.grade)from tbl_student s,tbl_grade g where s.sid=g.sid group by s.sname;
select s.sname,t.sg from tbl_student s,(select sid,sum(grade) from tbl_grade group by sid) t where s.sid=t.sid ;
    --查询选修了“计算机原理”的学生学号和姓名
select s.sid,s.same from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and g.cid=c.cid and c.cname='计算机原理';
    --查询“周星驰”同学选修了的课程名字
select c.cname from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and g.cid=c.cid and s.sname='周星驰'
    --查询选修了五门课程的学生学号和姓名
    select s.sid,s.sname from  tbl_student s,
(select sid from tbl_grade group by sid having count(1)=3) t where s.sid=t.sid;
    --查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩
select s.sid,s.sname,c.cname,t1.maxg
    from(select g.sid,t.cid,t.maxg from
        (select cid,max(grade) maxg from tbl_grade g group by cid) t,tbl_grade g where t.cid=g.cid and maxg=g.grade) t1,tbl_student s,tbl_course c where t1.cid=c.cid and t1.sid=s.sid;
    --如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。
    
select s.sname,t.avg_g from
    (select sid ,avg(grade) avg_g from tbl_grade where grade>=60 group by sid) t,tbl_student s where t.sid=s.sid;

马鹏磊

--创建学生表
 create table tbl_student(
 sid   number,
 sname varchar2(10),
 sex   varchar2(10),
 age   number,
 dept  number
);
--创建课程表
create table tbl_course(
 cid   number,
 cname varchar2(10)
);
--创建成绩表
create table tbl_grade(
 gid   number,
 sid   number,
 cid   number,
 grade number
);
insert into tbl_student (sid,sname)values(1,'张三');
insert into tbl_student (sid,sname)values(2,'李四');
insert into tbl_student (sid,sname)values(3,'王五');
insert into tbl_student (sid,sname)values(4,'赵六');

insert into tbl_course (cid,cname)values(1,'英语');
insert into tbl_course (cid,cname)values(2,'语文');
insert into tbl_course (cid,cname)values(3,'数学');

insert into tbl_grade (gid,sid,cid,grade) values(1,1,1,78);
insert into tbl_grade (gid,sid,cid,grade) values(2,1,2,74);
insert into tbl_grade (gid,sid,cid,grade) values(3,1,3,88);
insert into tbl_grade (gid,sid,cid,grade) values(4,2,1,98);
insert into tbl_grade (gid,sid,cid,grade) values(5,2,2,56);
insert into tbl_grade (gid,sid,cid,grade) values(6,2,3,48);
insert into tbl_grade (gid,sid,cid,grade) values(7,3,1,74);
insert into tbl_grade (gid,sid,cid,grade) values(8,3,2,97);
insert into tbl_grade (gid,sid,cid,grade) values(9,4,1,28);
insert into tbl_grade (gid,sid,cid,grade) values(10,4,2,79);
insert into tbl_grade (gid,sid,cid,grade) values(11,4,3,76);
commit;

--取出学生个数
select count(1)
from tbl_student
--计算每个学生的总成绩
select ts.sname,sum(grade)
from tbl_student ts,tbl_grade tg,tbl_course tc
where ts.sid=tg.sid
and tc.cid=tg.cid
group by ts.sname
--查询选修了"计算机原理"的学生学号和姓名
select ts.sid,ts.sname
from tbl_student ts,tbl_course tc
where tc.cname='计算机原理'
--查询周星驰同学选修了的课程名字
select ts.sname，tc.cname
from tbl_student ts,tbl_course tc
where ts.sname='周星驰'
--查询选修了五门课程的学生学号和姓名
select ts.sid,ts.sname
from tbl_student ts,
     (select sid from tbl_grade group by sid having count(1)=5)tg
where ts.sid=tg.sid
--查询单科最高成绩的sql语句，查询的内容包括学生学号，学生姓名，课程编号，成绩

--先查询出课程编号和最高成绩 从成绩表中 想办法查出 学生学号和学生姓名
select cid,max(grade) from tbl_grade tg group by tg.cid
--先找学号？？怎么着学号
select s.sid, s.sname, c.cname, t1.maxg
  from (select g.sid, t.cid, t.maxg
       
          from (select cid, max(grade)maxg from tbl_grade group by cid) t,
               tbl_grade g
         where t.cid = g.cid
           and maxg = g.grade) t1,tbl_student s,
       tbl_course c
 where t1.cid = c.cid
   and t1.sid = s.sid
 
--如果以60分为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序

--先查询及格的学生姓名
select sid,avg(grade)  from tbl_grade where grade>'60' group by sid
--删选
select s.sname, t.avg_g

  from (select sid, avg(grade) avg_g
          from tbl_grade
         where grade > '60'
         group by sid) t,
       tbl_student s
 where t.sid = s.sid

 

WR

      --关于学生表、课程表、成绩表的查询
       create table tbl_student(
       sid   number,
       sname varchar2(10),
       sex   varchar2(10),
       age   number,
       dept  number
      );
      create table tbl_course(
       cid   number,
       cname varchar2(10)
      );
      create table tbl_grade(
       gid   number,
       sid   number,
       cid   number,
       grade number
      );
      insert into tbl_student (sid,sname)values(1,'张三');
      insert into tbl_student (sid,sname)values(2,'李四');
      insert into tbl_student (sid,sname)values(3,'王五');
      insert into tbl_student (sid,sname)values(4,'赵六');
     
      insert into tbl_course (cid,cname)values(1,'英语');
      insert into tbl_course (cid,cname)values(2,'语文');
      insert into tbl_course (cid,cname)values(3,'数学');
     
      insert into tbl_grade (gid,sid,cid,grade) values(1,1,1,78);
      insert into tbl_grade (gid,sid,cid,grade) values(2,1,2,74);
      insert into tbl_grade (gid,sid,cid,grade) values(3,1,3,88);
      insert into tbl_grade (gid,sid,cid,grade) values(4,2,1,98);
      insert into tbl_grade (gid,sid,cid,grade) values(5,2,2,56);
      insert into tbl_grade (gid,sid,cid,grade) values(6,2,3,48);
      insert into tbl_grade (gid,sid,cid,grade) values(7,3,1,74);
      insert into tbl_grade (gid,sid,cid,grade) values(8,3,2,97);
      insert into tbl_grade (gid,sid,cid,grade) values(9,4,1,28);
      insert into tbl_grade (gid,sid,cid,grade) values(10,4,2,79);
      insert into tbl_grade (gid,sid,cid,grade) values(11,4,3,76);
      commit;

    --取出学生个数
    select count(1) from tbl_student
    --计算每个学生的总成绩
    select s.sname, sum(g.grade)
      from tbl_student s, tbl_grade g
     where s.sid = g.sid
     group by s.sname
     --4条记录:效率高
    select s.sname, t.sg
      from tbl_student s,
           (select sid, sum(grade) sg from tbl_grade group by sid) t
     where s.sid = t.sid
    --查询选修了“计算机原理”的学生学号和姓名
    select s.sid,s.sname
      from tbl_student s, tbl_grade g, tbl_course c
     where s.sid = g.sid
       and c.cid = g.cid
       and c.cname = '英语'
    --查询“周星驰”同学选修了的课程名字
   select c.cname
     from tbl_student s, tbl_grade g, tbl_course c
    where s.sid = g.sid
      and c.cid = g.cid
      and s.sname = '周星驰'
    --查询选修了五门课程的学生学号和姓名
    select s.sid, s.sname
      from tbl_student s,
           (select sid from tbl_grade group by sid having count(1) = 3) t
     where s.sid = t.sid
    --查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩
    select s.sid, s.sname, c.cname, t1.maxg
      from (select g.sid, t.cid, t.maxg
              from (select cid, max(grade) maxg from tbl_grade group by cid) t,
                   tbl_grade g
             where t.cid = g.cid
               and maxg = g.grade) t1,
           tbl_student s,
           tbl_course c
     where t1.cid = c.cid
       and t1.sid = s.sid
    --如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。
   select s.sname, t.avg
     from (select sid, avg(grade) avg
             from tbl_grade
            where grade >= 60
            group by sid) t,
          tbl_student s
    where t.sid = s.sid

song

取出学生个数

select count (sid)

from tbl_student

计算每个学生的总成绩

select sname ,sum(grade)
from tbl_student,tbl_grade
where tbl_student.sid=tbl_grade.sid
group by sname

查询选修了“计算机原理”的学生学号和姓名

select sname,tbl_student.sid
from tbl_student,tbl_course,tbl_grade
where tbl_student.sid=tbl_grade.sid  and tbl_grade.cid=tbl_course.cid
   and cname='计算机原理'

查询“周星驰”同学选修了的课程名字
select cname
from tbl_student,tbl_course,tbl_grade
where tbl_student.sid=tbl_grade.sid  and tbl_grade.cid=tbl_course.cid
   and sname='周星驰'

查询选修了五门课程的学生学号和姓名

select sname,tbl_student.sid
from tbl_student,
    (select sid from tbl_grade group by sid having count(1)=5) t
  where tbl_student.sid=t.sid

 

查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩

select s.sid ,s.sname, c.cname,t1.maxg
from ( select g.sid,t.cid,t.maxg
       from (select cid,max(grade) maxg from tbl_grade group by cid) t,
        tbl_grade g
       where t.cid=g.cid
       and maxg=g.grade) t1,
tbl_student s,
tbl_course c
where t1.cid=c.cid
and t1.sid=s.sid
   
如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序
select sname, avg(grade)
from tbl_student,tbl_grade
where tbl_student.sid=tbl_grade.sid and grade>60
group by sname
order by avg(grade)

一个人的时光

1,取出学生个数
select count(*)
from student
2, 计算每个学生的总成绩
select sname,sum(grade)
from tbl_student s,tbl_grade g
where s.sid=g.sid
group by s.sname
3,查询选修了“计算机原理”的学生学号和姓名
select distinct s.sid, sname
  from tbl_student s,
       (select sid
          from tbl_grade g, tbl_course c
         where cname = '计算机原理'
           and g.cid = c.cid) sc
 where s.sid = sc.sid;
4,查询“周星驰”同学选修了的课程名字
select cname
  from tbl_student s, tbl_grade g, tbl_course c
 where g.cid = c.cid
   and s.sid = g.sid
   and sname = '周星驰';
5,查询选修了五门课程的学生学号和姓名
select s.sid, sname
  from tbl_student s,
       (select sid
          from tbl_grade g
         group by sid
        having count(1)= 5) sc
 where s.sid = sc.sid;
6,查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩
select s.sid, sname, c.cid, cname, t1.maxg
  from (select g.sid, sc.cid, sc.maxg
          from (select cid, max(grade) maxg from tbl_grade group by cid) sc,
               tbl_grade g
         where sc.cid = g.cid
           and maxg = g.grade) t1,
       tbl_student s,
       tbl_course c
 where t1.cid = c.cid
   and t1.sid = s.sid;
7,如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。
select sname, t.avgg
  from (select sid, avg(grade) avgg
          from tbl_grade g
         where grade >=60
         group by sid) t,
       tbl_student s
 where t.sid = s.sid
order by t.avgg

zhangjialong

--创建学生表
create table tbl_student(
 sid   number not null primary key,
 sname varchar2(10),
 sex   varchar2(10),
 age   number,
 dept  number
);
--创建课程表
create table tbl_course(
 cid   number not null primary key,
 cname varchar2(10)
);
--创建成绩表
create table tbl_grade(
 gid   number not null primary key,
 sid   number,
 cid   number,
 grade number,
 foreign key(sid) references tbl_student(sid),
 foreign key(cid) references tbl_course(cid)
);

--录入数据
insert into tbl_student (sid,sname)values(1,'张三');
insert into tbl_student (sid,sname)values(2,'李四');
insert into tbl_student (sid,sname)values(3,'王五');
insert into tbl_student (sid,sname)values(4,'赵六');

insert into tbl_course (cid,cname)values(1,'英语');
insert into tbl_course (cid,cname)values(2,'语文');
insert into tbl_course (cid,cname)values(3,'数学');

insert into tbl_grade (gid,sid,cid,grade) values(1,1,1,78);
insert into tbl_grade (gid,sid,cid,grade) values(2,1,2,74);
insert into tbl_grade (gid,sid,cid,grade) values(3,1,3,88);
insert into tbl_grade (gid,sid,cid,grade) values(4,2,1,98);
insert into tbl_grade (gid,sid,cid,grade) values(5,2,2,56);
insert into tbl_grade (gid,sid,cid,grade) values(6,2,3,48);
insert into tbl_grade (gid,sid,cid,grade) values(7,3,1,74);
insert into tbl_grade (gid,sid,cid,grade) values(8,3,2,97);
insert into tbl_grade (gid,sid,cid,grade) values(9,4,1,28);
insert into tbl_grade (gid,sid,cid,grade) values(10,4,2,79);
insert into tbl_grade (gid,sid,cid,grade) values(11,4,3,76);
commit;

--1.取出学生个数
select count(sname)
from tbl_student

--2.计算每个学生的总成绩
select sname,sum(grade)
from tbl_student s,tbl_grade g
where s.sid=g.sid
group by sname

--3.查询选修了“计算机原理”的学生学号和姓名
select s.sid,s.sname
from tbl_student s,tbl_course c,tbl_grade g
where s.sid=g.sid and c.cid=g.cid and c.cname='计算机原理'

--4.查询“周星驰”同学选修了的课程名字
select sname,c.cname
from tbl_student s,tbl_course c,tbl_grade g
where s.sid=g.sid and c.cid=g.cid and s.sname='周星驰'

--5.查询选修了五门课程的学生学号和姓名
select sname, s.sid
  from tbl_student s,
       (select sid from tbl_grade group by sid having count(1) = 5) t
 where s.sid = t.sid

--6.查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩
select s.sid, s.sname, c.cid, t1.maxg
  from (select g.sid, t.cid, t.maxg
          from (select cid, max(grade) maxg from tbl_grade group by cid) t,
               tbl_grade g
         where t.cid = g.cid
           and maxg = g.grade) t1,
       tbl_student s,
       tbl_course c
 where t1.cid = c.cid
   and t1.sid = s.sid

--7.如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。
select sname,avg(grade)
from tbl_student s,tbl_grade g
where s.sid=g.sid and g.grade>60
group by sname
order by avg(grade)

Cracia

取出学生个数
select count(1) from tbl_student
 计算每个学生的总成绩
select sum(grade) from tbl_grade group by sid
 查询选修了“计算机原理”的学生学号和姓名
select tbl_student.sname,tbl_student.sid from tbl_student,tbl_course where tbl_course.cname='英语'
 查询“周星驰”同学选修了的课程名字
select tbl_course.cname
  from tbl_student, tbl_course
 where tbl_student.sname = '张三'
 查询选修了五门课程的学生学号和姓名
select s.sid, s.sname
  from tbl_student s,
       (select sid from tbl_grade group by sid having count(1) = 3) t
 where s.sid = t.sid
 查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩
select s.sid, s.sname, c.cname, t1.maxg
  from tbl_student s,
       tbl_course  c,(select g.sid,
                             t.cid,
                             t.maxg from (select cid, max(grade) maxg
                                       from tbl_grade
                                      group by cid)    t,
                             tbl_grade g where t.cid = g.cid and maxg = g.grade)  t1
 where s.sid = t1.sid
   and t1.cid = c.cid
 如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。
select s.sname,t.avgg from tbl_student s,
(select sid,avg(grade) avgg from tbl_grade where grade>=60 group by sid) t
where s.sid=t.sid

liufangyuan

--取出学生个数
select count(1)学生个数 from tbl_student

--计算每个学生的总成绩
--1
select sname, t.sg
  from tbl_student s,
       (select sid, sum(grade) sg from tbl_grade group by sid) t
 where s.sid = t.sid

--2
select sname, sum(grade) 总成绩
  from tbl_student s, tbl_grade g
 where s.sid = g.sid

 group by s.sname

--查询选修了“英语”的学生学号和姓名
select s.sid, s.sname
  from tbl_student s, tbl_grade g, tbl_course c
 where s.sid = g.sid
   and c.cid = g.cid
   and cname = '英语'

--查询“王五”同学选修了的课程名字
select cname
  from tbl_student s, tbl_grade g, tbl_course c
 where s.sid = g.sid
   and s.sname = '王五' group　by cname

--查询选修了三门课程的学生学号和姓名
select t.sid, sname
  from tbl_student s,
       (select sid from tbl_grade group by sid having count(1) = 3) t
 where s.sid = t.sid

--查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩
select s.sid, s.sname, m.cid, m.max
  from (select g.sid, t.cid, t.maxg max
          from (select cid, max(grade) maxg from tbl_grade group by cid) t,
               tbl_grade g
         where g.cid = t.cid
           and maxg = g.grade) m,
       tbl_student s
 where m.sid = s.sid


--如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。
select sname, t.avgg
  from (select sid, avg(grade) avgg
          from tbl_grade g
         where grade > 60
         group by sid
         order by avgg) t,
       tbl_student s
 where s.sid = t.sid

XIAOC

select count(0) from tbl_student;

select t.sname,(select sum(g.grade) from tbl_grade g where g.sid=t.sid) "sun(grade)" from tbl_student t;

select s.sid,s.sname from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and c.cid=g.cid and c.cname='语文';

select c.cname from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and c.cid=g.cid and s.sname='张三';

select (select max(g.grade) from tbl_grade g where g.sid=c.scd) from tbl_course c;

select s.sname,dd.sid,dd.cid,dd.grade from (select g.sid,g.cid,g.grade from (select cid,max(grade) xgrade from tbl_grade group by cid) d,tbl_grade g where d.cid=g.cid and d.xgrade=g.grade) dd,tbl_student s where s.sid=dd.sid；

Sarbr

   -- 取出学生个数
   select count(1) 学生个数 from tbl_stu
   -- 计算每个学生的总成绩
   select s.sname, t.sg
     from tbl_stu s ,(select sid, sum(grade) sg from tbl_grade group by sid) t
    where t.sid = s.sid
   -- 查询选修了“英语”的学生学号和姓名
   select *
   from tbl_grade g, tbl_stu s, tbl_course c
   where g.sid=s.sid
   and g.cid = c.cid
   and c.cname='英语'  
   --查询“张三”同学选修了的课程名字
   select s.sname,c.cname
   from tbl_grade g, tbl_stu s, tbl_course c
   where g.sid=s.sid
   and g.cid = c.cid
   and s.sname='张三'
   -- 查询选修了三门课程的学生学号和姓名
   select s.sname 姓名, t.sg 选修三门,s.sid 学生学号
     from tbl_stu s ,(select sid, count(cid) sg from tbl_grade group by sid having count(3)>=3) t
    where t.sid = s.sid
   --查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩
   select  g.sid 学生学号,t.cid 课程编号,t.maxg 最高成绩,s.sname 学生姓名
   from (select cid,max(grade) maxg
  from tbl_grade
  group by cid)t,tbl_grade g , tbl_stu s
  where t.cid=g.cid
  and maxg=g.grade
  and s.sid=g.sid

    --如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。
    select s.sname 学生姓名, t.sg 总成绩, t.ac 平均成绩
      from tbl_stu s,
           (select sid, sum(grade) sg, avg(grade) ac
              from tbl_grade
             where grade >=60
             group by sid) t
     where t.sid = s.sid
     order by t.ac desc
     
    
    
    
    

renmihe

--取出学生个数
select count(sid)
  from tbl_student
  --计算每个学生的总成绩
  select sum(g.grade)
  from tbl_student s,tbl_grade g
  where s.sid=g.sid
  group by s.sid
 
--查询选修了“计算机原理”的学生学号和姓名
select s.sid,s.sname
from tbl_student s,tbl_course c,tbl_grade g
where s.sid=g.sid and c.cid=g.cid and c.cname='计算机原理'

--查询“周星驰”同学选修了的课程名字
select c.cname
from tbl_student s,tbl_course c,tbl_grade g
where s.sid=g.sid and c.cid=g.cid and s.sname='周星驰'
--查询选修了五门课程的学生学号和姓名
select s.sid,s.sname
from tbl_student s,
(select sid from tbl_grade group by sid having count(1)=5) t
where s.sid=t.sid
--查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩
select s.sid, s.sname, c.cname, t1.maxg
  from (select g.sid, t.cid, t.maxg
          from (select cid, max(grade) maxg from tbl_grade group by cid) t,
               tbl_grade g
         where t.cid = g.cid
           and maxg = g.grade) t1,
       tbl_student s,
       tbl_course c
 where t1.cid = c.cid
   and t1.sid = s.sid
--如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。
select s.sname, t.avg_g
  from (select sid, avg(grade) avg_g
          from tbl_grade
         where grade >= 60
         group by sid) t,
       tbl_student s
 where t.sid = s.sid

gaoye

   --1、取出学生个数
   select count(sid)
   from tbl_student
   
    --2、计算每个学生的总成绩
    select tbl_student.sname,t.s
    from tbl_student,(select sid,sum(tbl_grade.grade)s from tbl_grade group by tbl_grade.sid)t
    where tbl_student.sid=t.sid

    --3、查询选修了“英语”的学生学号和姓名
    select tbl_student.sid,tbl_student.sname
    from tbl_student,tbl_course,tbl_grade
    where tbl_grade.cid=(select cid from tbl_course where cname='英语')
      and tbl_grade.cid=tbl_course.cid
      and tbl_grade.sid=tbl_student.sid

    --4、查询“王五”同学选修了的课程名字
    select tbl_course.cname
      from tbl_student,tbl_course,tbl_grade
     where tbl_grade.sid=(select sid from tbl_student where sname='王五')
       and tbl_grade.cid=tbl_course.cid
       and tbl_grade.sid=tbl_student.sid

    --5、查询选修了三门课程的学生学号和姓名
    select t.sid,sname
    from tbl_student,(select sid,count(sid) s from tbl_grade group by sid having count(3)=3)t
    where tbl_student.sid=t.sid
    
    --6、查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩
    select tbl_grade.sid,sname,t.cid,t.m
    from tbl_student,(select cid,max(grade)m from tbl_grade group by cid)t,tbl_grade
    where t.m=tbl_grade.grade
      and tbl_grade.sid=tbl_student.sid

    --7、如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。
    select sname,t.a
    from tbl_student,(select sid,avg(grade)a from tbl_grade where grade>=60 group by sid)t
    where tbl_student.sid=t.sid
    order by t.a desc

刘瑞

1. 取出学生个数

2. select count(1) from tbl_student
3. 计算每个学生的总成绩
4. select s.name,sum(g.grade) from tbl_student s,tbl_grade g where s.sid=g.sid group by s.sname.
5. 查询选修了“计算机原理”的学生学号和姓名
6. select s.sid ,s.sname from  tbl_student s,tbl_grade g,tbl_course c where s.sid=g.sid and c.cid=g.sid and c.cname="计算机原理"
7. 查询“周星驰”同学选修了的课程名字
8. select s.sid ,s.sname from  tbl_student s,tbl_grade g,tbl_course c where s.sid=g.sid and c.cid=g.sid and s.sname="周星驰"
9. 查询选修了五门课程的学生学号和姓名
10. (select sid from tbl_grade groug by sid having count(1)=5) t select s.sid , s.sname from tbl_students, where s,sid=t.sid
11. 查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩
12. (select sid from tbl_grade groug by sid having count(1)=5) t select s.sid , s.sname from tbl_students, where s,sid=t.sid
13. 如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序
14. (select sid from tbl_grade groug by sid having count(1)=5) t select s.sid , s.sname from tbl_students, where s,sid=t.sid
15. 
    

Yc

1取出学生个数

select count(*) from tbl_student;

2计算每个学生的总成绩

select sname, sum(grade)from tbl_student s,tbl_grade g where s.sid=g.cid group by sname;

3查询选修了“计算机原理”的学生学号和姓名

select sid,sname from tbl_student s,tbl_course c where s.sid=c.cid and c.cname='计算机原理';

4查询“周星驰”同学选修了的课程名字

select cname from tbl_student s,tbl_course c where s.sid=c.cid and s.sname='周星驰';

5查询选修了五门课程的学生学号和姓名

select sid,sname from tbl_student where sid in (select sid from tbl_grade group by sid having count(*)=5);

6查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩

SQL> select s.sid,s.sname,c.cname,t1.maxg(select g.sid,t.cid,t.maxg from (select cid , max(grade)from tbl_grade group by cid) t tbl_grade g where t.cid=g.cid and maxg=g.grade) t1,tbl_student g,tbl_course c where t1.cid=c.cid and t1.sid=s.sid ;

如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。

select s.sname,t.avg_g from(select sid,avg(grade) avg_g from tbl_grade where grade>=60 group by sid) t, tbl_student s where t.sid=s.sid

王之神判

select count(sid) from tbl_student;


select s.sname,sum(g.grade)
from tbl_student s,tbl_grade g
where s.sid=g.sid
group by s.sid,s.sname;


select s.sid,s.sname
from tbl_student s,tbl_grade g,tbl_course c
where s.sid=g.sid and g.cid=c.cid and c.cname='计算机原理';


select c.cname
from tbl_student s,tbl_grade g,tbl_course c
where s.sid=g.sid and g.cid=c.cid and s.sname='周星驰';


select s.sid,s.sname,count(1)
from tbl_student s,tbl_grade g,tbl_course c
where s.sid=g.sid and g.cid=c.cid
group by s.sid,s.sname
having count(1)=5;


select s.sid,s.sname,c.cid,g.grade
from tbl_student s,tbl_grade g,tbl_course c
where s.sid=g.sid and g.cid=c.cid and g.grade in(
select max(g.grade)
from tbl_student s,tbl_grade g,tbl_course c
where s.sid=g.sid and g.cid=c.cid
group by c.cid);


select s.sname,avg(g.grade)
from tbl_student s,tbl_grade g,tbl_course c
where s.sid=g.sid and g.cid=c.cid
group by s.sid,s.sname
having min(g.grade)>=60
order by avg(g.grade);

740317431

取出学生个数

select count(1)
from tbl_student s
;

 

计算每个学生的总成绩

select s.sname,sum(g.grade)
from tbl_student s，tbl_grade g
where s.sid=g.sid
group by s.sid,s.sname
;

 

查询选修了“计算机原理”的学生学号和姓名

select stu.sname,stu.sid
from tbl_student stu,tbl_grade g
where stu.sid=g.sid
and g.cid=(select cid from  tbl_course where cname='计算机原理');

 

查询“周星驰”同学选修了的课程名字

select cname from tbl_course where cid in(select cid from tbl_grade
 where sid=(select sid from tbl_student where sname='周星驰));

 

查询选修了五门课程的学生学号和姓名

select sid ,sname from tbl_student where
sid in(select sid from tbl_grade group by sid having count(1)=5);

 

查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩

 select s.sid,s.sname, c.cname, t1.maxg
  from (select g.sid, t.cid, t.maxg
          from (select cid, max(grade) maxg from tbl_grade group by cid) t,
               tbl_grade g
         where t.cid = g.cid
           and maxg = g.grade) t1,
       tbl_student s,
       tbl_course c
 where t1.cid = c.cid
   and t1.sid = s.sid
  

 

如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序
select s.sname, t.avg_g from
(select sid,avg(grade) avg_g from tbl_grade where grade>=60 group by sid) t,
tbl_student s where t.sid=s.sid

 

 

 

 

 

 

 

 

wulinan

--1.取出学生个数 
select count(1)
from tbl_student
--2.计算每个学生的总成绩 
select s.sname,sum(grade)
from tbl_student s,tbl_grade g
where s.sid=g.sid
group by g.sid,s.sname
--3.查询选修了“计算机原理”的学生学号和姓名 

select s.sid , s.sname
from tbl_student s,tbl_grade g,tbl_course c
where s.sid=g.sid and c.cid=g.cid and c.cname='计算机原理'
--4.查询“周星驰”同学选修了的课程名字 
select c.cname
from tbl_student s,tbl_grade g,tbl_course c
where s.sid=g.sid and c.cid=g.cid and  s.sname='周星驰'

--5.查询选修了五门课程的学生学号和姓名
select c.cid,count(1)
from tbl_student s,tbl_grade g,tbl_course c
where s.sid=g.sid and c.cid=g.cid 
group by c.cid
having count(s.sid)>5
--6.查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩 
select t1.*, s.sname
  from (select g.sid, t.cid, t.maxg
          from (select cid, max(grade) Maxg from tbl_grade group by cid) t,
               tbl_grade g
         where t.cid = g.cid
           and maxg = g.grade) t1,
       tbl_student s,tbl_course c
 where t1.sid = s.sid and t1.cid=c.cid 



--7.如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。
select s.sname,t.avg_g
from (select sid ,avg( grade) avg_g
from tbl_grade where grade>=60
group by sid)t, tbl_student s
where t.sid=s.sid

lijiaxing

--1.取出学生个数
select count(1) 学生个数  from tbl_student;
--2.计算每个学生的总成绩
select max(s.sname),sum(g.grade) from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and c.cid=g.cid group by s.sid;
--3.查询选修了“计算机原理”的学生学号和姓名
select s.sid,s.sname from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and c.cid=g.cid and c.cname='计算机原理';
--4.查询“周星驰”同学选修了的课程名字
select c.cid,c.cname from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and c.cid=g.cid and s.sname='周星驰';
--5.查询选修了五门课程的学生学号和姓名
select sid,sname from tbl_student where sid in(select s.sid from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and c.cid=g.cid group by s.sid having count(1)=3);
--6.查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩
select s.sid,s.sname,c.cid,g.grade from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and c.cid=g.cid and g.gid in
(select gid from tbl_grade where grade in(select max(g.grade) from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and c.cid=g.cid group by c.cid))
--7.如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序。
select max(s.sname),avg(g.grade) agrade from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and c.cid=g.cid and g.grade>60 group by s.sid order by agrade

javaxiaozi

取出学生个数

select count(1) from tbl_student

计算每个学生的总成绩

selects.sid,s.sg from tbl_student s, (select from sid,sum(grade) sg tbl_grade group by sid)t where s.sid=t.sid

查询选修了“计算机原理”的学生学号和姓名

select * from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and c.cid=g.cid and c.cname='计算机原理'

查询“周星驰”同学选修了的课程名字

select c.cname from tbl_student s,tbl_course c,tbl_grade g where s.sid=g.sid and c.cid=g.cid and s.sname='周星驰'

查询选修了五门课程的学生学号和姓名

selects.sid,s.sname from tbl_student s(select sid from tbl_grade group by sid having count(1)=5)t where s.sid=t.sid

查询单科最高成绩的sql语句，查询内容包括学生学号，学生姓名，课程编号，成绩

select s.sid,s.sname,c.cname,t1.maxg from tbl_student s(select g.sid,t.cid,t.maxg from (select cid,max(grade) maxg from tbl_grade group by cid)t,tbl_grade g where t.cid=c.vid and maxg=g.grade)t1,tbl_student s,tbl_course c where t1.sid=s.sid and c.cid=t1.cid 

如果以60为及格线，统计成绩及格的学生姓名及平均分，并按平均成绩排序

select s.sname,t.avg_g from (select sid,avg(grade) avg_g from tbl_grade group by sid) t,tbl_student s where t.sid=s.sid

yanghao

SELECT COUNT(1) FROM tbl_student;

SELECT SNAME,SUM(GRADE) FROM tbl_grade TG,tbl_student TS WHERE TG.SID=TS.SID GROUP BY SNAME; 

SELECT SNAME ,SID FROM TBL_STUDENT TS , tbl_course TC,tbl_grade TG WHERE TC.CID=TG.CID AND TG.SID=TS.SID AND CNAME='JSJYL';

SELECT CNAME  FROM TBL_STUDENT TS , tbl_course TC,tbl_grade TG WHERE TC.CID=TG.CID AND TG.SID=TS.SID AND SNAME='ZXC';

SELLECT SID,SNAME FROM

(SELECT COUNT(CID) C,SNAME FROM TBL_STUDENT TS,tbl_grade TG  WHERE TG.SID=TS.SID GROUP BY SNAME)T

WHERE T.C>=5;

SELECT MAX(GRADE),CID  FROM tbl_grade GROUP BY CID;

SELECT SANME,AVG(GRADE) FROM tbl_grade TG,tbl_student TS WHERE GRADE>60  AND TG.SID=TS.SID GROUP BY SNAME;

wangxiaoqing

select count(*) from tbl_student;

select sid,sum(grade)
from tbl_grade
group by sid;

select tbl_student.sname,tbl_course.cname
from tbl_student,tbl_course,tbl_grade
where tbl_student.sid=tbl_grade.sid and
  tbl_grade.cid=tbl_course.cid and
  tbl_course.cname='数学';

select tbl_course.cname
from tbl_student,tbl_course,tbl_grade
where tbl_student.sid=tbl_grade.sid and
  tbl_grade.cid=tbl_course.cid and
  tbl_student.sname='李四';

select a.sid,tbl_student.sname
from
(select sid,count(*)
from tbl_grade
group by tbl_grade.sid
having count(*)>2) a,tbl_student
where a.sid=tbl_student.sid

select distinct(tbl_student.sid),tbl_student.sname,a.grade
from tbl_student,tbl_course,tbl_grade,(select sid,max(grade) grade from tbl_grade group by sid)a
where tbl_student.sid=tbl_grade.sid and
      a.sid=tbl_student.sid

select sid,avg(grade)
from tbl_grade
where grade >60
group by sid

jxx

1.select count(1) from tbl_student

2.select tbl_student.sname, sum(tbl_grade.grade)
  from tbl_student, tbl_grade
 where tbl_grade.sid = tbl_student.sid
 group by tbl_student.sname

3.select tbl_student.sid, tbl_student.sname
  from tbl_student, tbl_course, tbl_grade
 where tbl_grade.sid = tbl_student.sid
   and tbl_grade.cid = tbl_course.cid
   and tbl_course.cname = '计算机原理'
4.select tbl_course.cname
  from tbl_student, tbl_course, tbl_grade
 where tbl_grade.sid = tbl_student.sid
   and tbl_grade.cid = tbl_course.cid
   and tbl_student.sname = '周星驰'
5. select s.sid, s.sname
    from (select tbl_student.sid, tbl_student.sname, count(1) c
            from tbl_grade, tbl_course, tbl_student
           where tbl_grade.cid = tbl_course.cid
             and tbl_grade.sid = tbl_student.sid
           group by tbl_student.sid, tbl_student.sname
           order by tbl_student.sid) s
   where c = 5

6.select tbl_student.sid, tbl_student.sname, m.cid, m.max
  from tbl_student,
       tbl_grade,
       (select tbl_grade.cid, max(tbl_grade.grade) max
          from tbl_student
          left join tbl_grade
            on tbl_student.sid = tbl_grade.sid
         group by tbl_grade.cid) m
 where m.max = tbl_grade.grade
   and tbl_grade.sid = tbl_student.sid
7.select tbl_student.sname, avg(grade) a
  from tbl_student, tbl_grade
 where tbl_grade.grade >= 60
   and tbl_grade.sid = tbl_student.sid
 group by tbl_student.sname
 order by a