javaSQL语句 四表查询实例

student(id,name,age)学生表(学生编号，学生姓名，学生年龄)
course(id,name,teacher_id)课程表（课程编号，课程名称，老师编号）
grade(id,studnt_id,course_id,grade)成绩表（成绩编号，学生编号，课程编号,成绩）
teacher(id，name,age,salary )（老师编号，老师姓名，老师年龄，老师工资）
1.找出没有选修李明老师课程的所有学生姓名
2.查看学生年龄低于20的考试成绩和科目
3.查看小明的考试成绩和考试科目及了老师姓名
4.查看成绩低于60分的学生是哪个老师教的
5.查看各个老师所授课程的平均分
6.老师平均分低于60的老师工资减少1000；
7.删除考试成绩大于100的学生信息
8.将没有成绩的学生从成绩表中删除(将成绩null的记录删除)
搭架子：select * from student stu,teacher t,grade g,course c where
        stu.id=g.studnt_id and g.course_id=c.id and c.teacher_id=t.id;
1.select stu.name from student stu,teacher t,grade g,course c where
        stu.id=g.studnt_id and g.course_id=c.id and c.teacher_id=t.id and t.name!='李明';
2.select g.grade,c.name from student stu,teacher t,grade g,course c where
        stu.id=g.studnt_id and g.course_id=c.id and c.teacher_id=t.id and stu.age<20;
3.select g.grade,c.name,t.name from student stu,teacher t,grade g,course c where
        stu.id=g.studnt_id and g.course_id=c.id and c.teacher_id=t.id and stu.name='小明';
4.select t.name from student stu,teacher t,grade g,course c where
        stu.id=g.studnt_id and g.course_id=c.id and c.teacher_id=t.id and g.grade<60;
    
5.select t.id,avg(g.grade) from student stu,teacher t,grade g,course c where
        stu.id=g.studnt_id and g.course_id=c.id and c.teacher_id=t.id group by t.id ;
6.update teacher set salary=salary-1000 where id in(
    select t.id from student stu,teacher t,grade g,course c where
        stu.id=g.studnt_id and g.course_id=c.id and c.teacher_id=t.id group by t.id having avg(g.grade)<60;
)
7.delete from student where id in(
    select stu.id from student stu,teacher t,grade g,course c where
        stu.id=g.studnt_id and g.course_id=c.id and c.teacher_id=t.id and g.grade>100)
简单写法：
delete from student where id in(
    select stu.id from grade g,student stu where g.studnt_id=stu.id and g.grade>100)
8.delete from grade where grade is null；
    ————取自姚老师笔记