SELECT Sname, Sage, Ssex FROM Student WHERE Sid IN (SELECT Sid FROM SC WHERE Cid=1 AND score>80),
时间: 2024-06-01 21:10:43 浏览: 13
This SQL query selects the names, ages, and genders of all students who have taken Course 1 and scored above 80. The subquery selects the student IDs of those who have taken Course 1 and scored above 80, and these IDs are used to filter the results from the Student table.
The resulting table will have three columns: Sname (student name), Sage (student age), and Ssex (student gender).
相关问题
有以下四张表: 1).学生表 Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 2).课程表 Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号 3).教师表 Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名 4).成绩表 SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数 请写出以下
SQL语句:
1. 查询所有学生的姓名、年龄和性别:
```
SELECT Sname, Sage, Ssex FROM Student;
```
2. 查询所有课程的名称和对应的教师姓名:
```
SELECT Cname, Tname FROM Course, Teacher WHERE Course.TID = Teacher.TID;
```
3. 查询所有选修了某门课程的学生的姓名和成绩:
```
SELECT Student.Sname, SC.score FROM Student, SC WHERE Student.SID = SC.SID AND SC.CID = '某门课程编号';
```
4. 查询某个学生的所有成绩及其对应的课程名称:
```
SELECT Course.Cname, SC.score FROM Course, SC WHERE Course.CID = SC.CID AND SC.SID = '某个学生编号';
```
Student(Sid,Sname,Sage,Ssex) 学生表 Course(Cid,Cname,Tid) 课程表 SC(Sid,Cid,score) 成绩表 Teacher(Tid,Tname) 教师表 查询和“”号的同学学习的课程完全相同的其他同学学号和姓名;
可以使用子查询和集合操作来解决这个问题。具体的SQL语句如下:
```
SELECT s2.Sid, s2.Sname
FROM Student s1, Student s2, Course c
WHERE s1.Sid = '""' AND s1.Sid <> s2.Sid
AND s1.Sid = sc.Sid AND s2.Sid = sc.Sid
AND sc.Cid = c.Cid
AND NOT EXISTS (
SELECT *
FROM Course c1
WHERE c1.Cid = c.Cid AND c1.Tid <> c.Tid
)
GROUP BY s2.Sid, s2.Sname
HAVING COUNT(*) = (
SELECT COUNT(*)
FROM Course c2
WHERE c2.Cid IN (
SELECT sc1.Cid
FROM SC sc1
WHERE sc1.Sid = '""'
)
)
```
解释一下上述SQL语句:
- 首先从学生表中找到学号为""的学生s1。
- 然后通过学生表、课程表和成绩表联结,找到学习了和s1完全相同的所有课程的其它学生s2。
- 再通过子查询和集合操作,找到和s1学习课程的数量相同的课程。
- 最后通过GROUP BY和HAVING子句,过滤掉学习课程数量不同的学生,并返回他们的学号和姓名。
需要注意的是,这个SQL语句中的""是一个占位符,实际使用时需要替换成具体的学号。