增删改查练习

''''''
# select gender,count(sid) from student group by gender;

# select sname from student where sname like '张%';

# select cname,avg(num) from course left join score on course_id = cid group by cid order by avg(num) desc;

# select student_id from score group by student_id having avg(num) > 60;
# select sname,student_id from student inner join (
# # select student_id from score group by student_id having avg(num) < 60
# # )as t2 on student_id = sid;

# select course_id from score where student_id =1;
# select distinct student_id from score where course_id in(select course_id from score where student_id =1);
# select sname,student_id from student left join (
# select distinct student_id from score where course_id in(select course_id from score where student_id =1)
# )as t1 on student_id = sid;

# select student_id from score group by student_id having count(student_id) =1
# select student_id,sname from student inner join(
# select student_id from score group by student_id having count(student_id) =1
# ) as t1 on student_id = sid;

# select course_id '课程ID',max(num) '最高分',min(num) '最低分' from score group by course_id;

# select sname from student where sid in(
# select student_id from score inner join(
# select student_id s_id,num num1 from score where course_id in(1,2) group by student_id having count(student_id) = 2
# )as t1 on student_id = s_id where course_id = 2 and num < num1;

#先查出来谁有物理课和生物课
# select student_id s_id,num num1 from score where course_id in(1,2) group by student_id having count(student_id)=2;
# select student_id from score inner join(
# select student_id s_id,num num1 from score where course_id in(select cid from course where cname = '生物' or cname = '物理') group by student_id having count(student_id)=2
# ) as t1 on s_id = student_id where course_id = 2 and num > num1;

# select student_id,avg(num) from score group by student_id having avg(num) >60;

# select student_id,sname,count(student_id),sum(num) from score left join student on student_id = student.sid group by student_id;

# select count(tid) from teacher where tname like '李%';

# select student_id from score where course_id = 1;
# select sid from student where sid not in(select student_id from score where course_id = 1);

# select student_id from score where course_id in (1,2) group by student_id having count(student_id) =2;
# select sname,student_id from student inner join(
# select student_id from score where course_id in (1,2) group by student_id having count(student_id) =2
# )as t1 on student_id =sid;

# select student_id from score where course_id in(2,4) group by student_id having count(student_id) =2;
# select student_id,sname from student inner join(
# select student_id from score where course_id in(2,4) group by student_id having count(student_id) =2
# )as t1 on student_id =sid ;

#13题
# select sid,sname from student where sid not in(
# select distinct student_id from score where course_id  in(
# select cid from course right join (
#     select tid from teacher where tname = '张磊老师'
# )as t1 on tid = teacher_id
# ));

#15题
# select cid from course inner join teacher on teacher_id = tid where tname= '李平老师'
#
# select cid from course where teacher_id in(
#     select tid from teacher where tname = '李平老师'
# );