1 --student学生表(sno,sname,sex,birthday,tel) 2 --Course课程表(cno,cname) 3 --Sc 学生成绩表(sno,cno,score) 4 1、查询数学成绩在60分以下的学生 5 Select sno,sname from sc t1 join student t2 on t1.sno=t2.sno 6 Join course t3 on t1.cno=t3.cno 7 where t3.cname='数学' and t1.score<60
1、为数据创建student表并插入一条数据
create table student( id int auto_increment primary key, name varchar(32), subject varchar(33), Grage int ) insert into student(1,'张三','数学',99)
2、写一条 SQl,查出成绩最高的前10条记录，并按照成绩由高到低
select top 10 from student order by grade desc
3、写一条SQl,查询所有科目分数都在80分以上（含）的学生姓名
select name,min(grade) from student group by name having min(grade)>=80
4、找出至少两门功课及格的学生名字
select name,count(*) from student where grade>60 group by name having count(*)>=2
题目2：
学生表（学生idt名，性别，分数） student(s_id,name,sex,score) 班级表（班级id,班级名称）calss(c_id,c_name) 学生班级标（班级id,学生id）student_class(s_id,c_id) 1、用SQl语句查询一班得分在80以上的学生select t1.*from student t1 ,class t2,student_class t3
where t1.s_id=tt3.s_id and t2.c_id=t3.c_id and t2.c_name='一班'
and t1.score>80
2、查询每门课程大于80分的学生姓名
select name from student group by s_id having min(score)>80
题目3：
2张关联表a和b,关联字段为id,删除主表a中在副表b中没有的信息
delete from a where id not in (select a.id from b join a on a.id=b.id)