SQL SEVER◇_查询非陕西籍的学生中比陕西籍任意一个学生年龄小的学生学号和姓名。 ◇查询其他系中比“自控系”某一学生年龄小的学生学号、姓名和年龄。◇查询其他系中比“自控系”所有学生年龄都小的学生学号、姓名及年龄。 ◇查询选课关系中所选课程平均成绩最高的学生的基本信息。
时间: 2023-11-16 17:13:18 浏览: 87
1. 查询非陕西籍的学生中比陕西籍任意一个学生年龄小的学生学号和姓名:
```
SELECT *
FROM student
WHERE province != '陕西省' AND birthday < (SELECT MIN(birthday) FROM student WHERE province = '陕西省');
```
2. 查询其他系中比“自控系”某一学生年龄小的学生学号、姓名和年龄:
```
SELECT *
FROM student
WHERE dept != '自控系' AND birthday < (SELECT birthday FROM student WHERE dept = '自控系' AND name = '某一学生');
```
3. 查询其他系中比“自控系”所有学生年龄都小的学生学号、姓名及年龄:
```
SELECT *
FROM student
WHERE dept != '自控系' AND birthday < ALL (SELECT birthday FROM student WHERE dept = '自控系');
```
4. 查询选课关系中所选课程平均成绩最高的学生的基本信息:
```
SELECT student.id, student.name, student.dept, MAX(elective.score) AS max_score
FROM student, elective
WHERE student.id = elective.id AND elective.score = (SELECT MAX(avg_score) FROM (SELECT AVG(score) AS avg_score FROM elective GROUP BY id) AS avg_scores);
```
阅读全文