SQL题目

create table student
(
    id   int(11)     not null primary key auto_increment,
    name varchar(16) not null,
    age  int(11),
    sex  varchar(1)
);
alter table student
    modify column sex varchar(1) comment '1=男,0=女';
insert into student
values (1, '张三', 14, '1');
insert into student
values (2, '王红', 16, '0');
insert into student
values (3, '王五', 14, '1');

create table course
(
    id   int(11)     not null primary key auto_increment,
    name varchar(16) not null,
    t_id int(11)     not null
);
insert into course
values (1, '英语', 2);
insert into course
values (2, '语文', 1);
insert into course
values (3, '数学', 3);

create table teacher
(
    id   int(11)     not null primary key auto_increment,
    name varchar(16) not null
);
insert into teacher
values (1, '李老师');
insert into teacher
values (2, '张老师');
insert into teacher
values (3, '赵老师');

create table score
(
    s_id  int(11) not null comment '学号',
    s_no  int(11) not null comment '课程编号',
    score double  not null comment '成绩'
);
insert into score
values (1, 1, 60);
insert into score
values (1, 2, 80);
insert into score
values (1, 3, 90);
insert into score
values (2, 1, 90);
insert into score
values (2, 2, 80);
insert into score
values (2, 3, 60);
insert into score
values (3, 1, 55);
insert into score
values (3, 2, 60);
insert into score
values (3, 3, 40);

1. 查询语文成绩比数学成绩高的所有学生学号
   select t1.s_id
   from score t1,
   score t2
   where t1.s_no = 2
   and t2.s_no = 3
   and t1.s_id = t2.s_id
   and t1.score > t2.score;

2. 查询平均成绩大于60分的学生的学号和平均成绩？
   select s_id, avg(score) as avg_score from score group by s_id having avg_score > 60

3. 查询所有学生的学号、姓名、选课数、总成绩
   select t1.*,t2.name from (select s_id,count(s_no) as courses, sum(score) from score group by s_id) t1 left join student t2 on t1.s_id = t2.id;
   select count(t2.s_no), sum(t2.score), t1.id,t1.name from student t1 join score t2 on t1.id = t2.s_id group by t2.s_id;