MySQL单表查询作业和多表查询作业截图

根据上图的表用MySQL查询出以下题

1:查询所有学生的信息
select * from students

2：查询姓名是李白的学生信息
select * from students where name='李白'

3：查询1班是上海的学生
select * from students where class='1班' and hometown='上海'

4：查询家乡是北京或者上海的学生
select * from students where hometown='北京' or hometown='上海'


5：查询小乔的年龄
select name,age as '年龄' from students where name='小乔'


6：查询家乡不在北京的学生
select * from students where hometown!='北京'

7：查询年龄小于20的女同学
select * from students where age<20 and sex='女'

8：查询年龄为18至20的学生
select * from students where age<=20 and age>=18

9：查询北京学生的年龄总和
select sum(age) from students where hometown='北京'

10：查询女生的平均年龄
select avg(age) from students where sex='女'

11：查询姓名包含白的学生
select * from students where name like '%白%'

12：查询所有学生信息，俺年龄从大到小排序，年龄相同时，再按学号从小到大排序
select * from students order by age desc,studentNo asc


13：查询女生的最大年龄
select max(age) from students where sex='女'

14：查询北京学生的年龄总和
select sum(age) from students where hometown='北京'

15：查询各种性别的人数
select count(*),sex from students group by sex

16：查询男生总人数

select count(*),sex from students where sex='男'

根据上图三个表用MySQL进行查询以下三个题

1.查询sc表中对应何昊老师所教授课程的女生的信息

select x.sname from xueshengxinxibiao as x inner join teacher as t inner join sc on x.sno=sc.sno and t.Cno = sc.cno where x.sex='女' and t.Cteacher='何昊'

2.找出没有选修何老师的课程的所有学生的姓名

SELECT xueshengxinxibiao.sname FROM xueshengxinxibiao WHERE sno not in (SELECT sc.sno FROM sc INNER JOIN teacher ON sc.cno = teacher.cno AND teacher.Cteacher = '何昊');

3.列出有不及格课程（成绩小于60）的学生的姓名

select distinct(x.sname) from xueshengxinxibiao as x inner join teacher as t inner join sc on x.sno=sc.sno and t.Cno = sc.cno where sc.scgrade < 60