基础sql题练习

1. 查询出所有班级的同学的名字

提示，不是所有的同学都有班级

SELECT student.name,class.class_name  from student 
INNER JOIN class on student.class_num = class.class_num;

2.查询出年龄最高的同学的信息

提示：可以使用limit

select *  FROM student ORDER  BY age DESC LIMIT 1 OFFSET 0;

3.查询出在学生当中那个年龄段的学生最多，并统计其出现的次数

select  age,count(age) num FROM student GROUP BY age LIMIT 1 OFFSET 0;

4.查询出年龄超过21岁，并且选择 语文 的同学名字

SELECT t3.name FROM
   (select t2.sno,student.name,student.age FROM student right JOIN 
        (select t1.sno FROM
	          (select relationship.sno,relationship.cno,scoure.gradeName 
               FROM relationship 
               INNER JOIN scoure on relationship.cno = scoure.cno) t1
	     WHERE t1.gradeName = '语文') t2
   on student.sno = t2.sno) t3
where age > 21;

5.查询出软件工程班年龄最大，所选择的课程名称及该同学的姓名。

SELECT
	t3.NAME,
	scoure.gradeName 
FROM
	(
SELECT
	t2.NAME,
	relationship.cno 
FROM
	(
SELECT
	t1.sno,
	t1.NAME,
	t1.age 
FROM
	(
SELECT
	student.sno,
	student.NAME,
	student.age,
	class.class_name 
FROM
	student
	INNER JOIN class ON student.class_num = class.class_num 
	) t1 
WHERE
	t1.class_name = '软件工程' 
ORDER BY
	age DESC 
	LIMIT 1 OFFSET 0 
	) t2
	INNER JOIN relationship ON t2.sno = relationship.sno 
	) t3
	INNER JOIN scoure ON t3.cno = scoure.cno;