4)查询选修了“以数据库作为先行课”的课程的学生姓名和学号(用多表连接查询两种方法完成)
时间: 2023-06-26 08:03:43 浏览: 103
假设我们有以下三张表:
**student表**
| id | name |
| ---- | ------ |
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie|
**course表**
| id | name |
| ---- | -----------|
| 1 | Database |
| 2 | Algorithm |
| 3 | OS |
**sc表**
| id | student_id | course_id |
| ---- | ----------| ---------|
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 3 | 2 |
| 4 | 1 | 3 |
| 5 | 2 | 3 |
方法一:使用子查询
```sql
SELECT id, name FROM student
WHERE id IN (SELECT student_id FROM sc WHERE course_id = (SELECT id FROM course WHERE name = 'Database'));
```
方法二:使用多表连接
```sql
SELECT student.id, student.name FROM student
JOIN sc ON student.id = sc.student_id
JOIN course ON sc.course_id = course.id
WHERE course.name = 'Database';
```
以上两种方法均可以实现查询选修了“以数据库作为先行课”的课程的学生姓名和学号。