–3.查询出9月份入学的所以学生 班级名称 学生姓名 –4.查询出所以班级里面的男生和女生的数量 –5.查询出所以班级的内蒙古地区的学生的数量;使用oracle数据库的sql语句
时间: 2024-10-11 07:15:43 浏览: 7
好的,以下是使用Oracle数据库SQL语句来完成这三个查询的例子:
1. 查询9月份入学的所有学生及其班级名称和学生姓名:
```sql
SELECT students.name AS student_name, classes.class_name
FROM students
JOIN enrollments ON students.id = enrollments.student_id
JOIN classes ON enrollments.class_id = classes.id
WHERE enrollments.enrollment_date >= '2022-09-01' AND enrollments.enrollment_date < '2022-10-01';
```
这里假设`students`表有`id`, `name`字段,`enrollments`表有`student_id`, `class_id`, 和 `enrollment_date`字段,`classes`表有`id`, `class_name`字段。
2. 查询所有班级里的男生和女生数量:
```sql
SELECT classes.class_name,
(SELECT COUNT(*) FROM students WHERE gender = '男' AND class_id = classes.id) AS male_count,
(SELECT COUNT(*) FROM students WHERE gender = '女' AND class_id = classes.id) AS female_count
FROM classes;
```
这个查询假设`students`表有一个`gender`字段表示性别。
3. 查询所有班级中来自内蒙古地区的学生活动人数:
```sql
SELECT classes.class_name, COUNT(*) AS students_from Inner Mongolia
FROM students
JOIN enrollments ON students.id = enrollments.student_id
JOIN classes ON enrollments.class_id = classes.id
WHERE students.province = '内蒙古'
GROUP BY classes.class_name;
```
这个查询假设`students`表有`province`字段记录学生的省份信息。