select s_id from score group by s_id having c_id not in (select c_id from course where t_id=(select t_id from teacher where t_name='张三'));出现SQL错误怎么修改
时间: 2024-03-18 09:42:30 浏览: 72
可能的原因是子查询返回了多个结果,导致了 SQL 错误。可以将 `=` 操作符替换成 `IN` 操作符来解决这个问题。修改后的 SQL 语句如下所示:
```
SELECT s_id
FROM score
WHERE c_id NOT IN (
SELECT c_id
FROM course
WHERE t_id = (
SELECT t_id
FROM teacher
WHERE t_name = '张三'
)
)
GROUP BY s_id;
```
这个查询语句的作用是查找没有被张三老师教授的课程的学生 ID。
相关问题
create procedure Pro_QCname_Chour_xkrs_AverScore_MaxScore @Tname_in char(20),@Cname_in char(30), @Chour_out tinyint output,@xkrs_out int output, @Aver_Score tinyint output,@Max_Score tinyint output as declare @Sum_Score int select @Chour_out=Chour,@xkrs_out=count(Sno),@Sum_Score=sum(Score),@Max_Score=max(Score) from SC,Course,Teacher,TC where Course.Cno=SC.Cno and Teacher.Tno=TC.Tno and Course.Cno=TC.Cno group by SC.Cno having Tname=@Tname_in and Cname=@Cname_in select @Aver_Score=@Sum_Score/@xkrs_out go,选择列表中的列 'Course.Chour' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
这个错误是因为在查询语句中没有将所有非聚合列包含在 GROUP BY 子句中。在这个例子中,Course.Chour 列没有被包含在 GROUP BY 子句中,所以会报错。你需要在查询语句中将所有非聚合列包含在 GROUP BY 子句中,例如:
```
select Course.Chour, count(SC.Sno) as xkrs, avg(SC.Score) as Aver_Score, max(SC.Score) as Max_Score
from SC,Course,Teacher,TC
where Course.Cno=SC.Cno and Teacher.Tno=TC.Tno and Course.Cno=TC.Cno
group by Course.Chour, Course.Cname, Teacher.Tname
having Teacher.Tname=@Tname_in and Course.Cname=@Cname_in
```
这个查询语句将 Course.Chour 列包含在了 GROUP BY 子句中,同时也包含了其他需要聚合的列。
我给你四张表,表结构是第一张表名称是student,字段是 s_id,s_name,s_birth,s_sex; 第二张表名称是teacher字段是t_id,t_name 第三张表名称是course,字段是 c_id,c_name,t_id; 第四张表名称是score,表字段是 s_id,c_id,s_score,根据以上表结构生成难度高的oracle sql题目及答案
假设四张表的表结构如下:
```
CREATE TABLE student (
s_id INT PRIMARY KEY,
s_name VARCHAR2(50),
s_birth DATE,
s_sex VARCHAR2(10)
);
CREATE TABLE teacher (
t_id INT PRIMARY KEY,
t_name VARCHAR2(50)
);
CREATE TABLE course (
c_id INT PRIMARY KEY,
c_name VARCHAR2(50),
t_id INT,
CONSTRAINT fk_teacher FOREIGN KEY (t_id) REFERENCES teacher(t_id)
);
CREATE TABLE score (
s_id INT,
c_id INT,
s_score INT,
CONSTRAINT pk_score PRIMARY KEY (s_id, c_id),
CONSTRAINT fk_student FOREIGN KEY (s_id) REFERENCES student(s_id),
CONSTRAINT fk_course FOREIGN KEY (c_id) REFERENCES course(c_id)
);
```
现在给出以下复杂的 Oracle SQL 题目和答案:
1. 查询每门课程的平均分和最高分,按照平均分从高到低排序。
答案:
```
SELECT c.c_id, c.c_name, AVG(s.s_score) AS avg_score, MAX(s.s_score) AS max_score
FROM course c
LEFT JOIN score s ON c.c_id = s.c_id
GROUP BY c.c_id, c.c_name
ORDER BY avg_score DESC;
```
2. 查询每个学生的平均成绩,按照平均成绩从高到低排序。
答案:
```
SELECT s.s_id, s.s_name, AVG(sc.s_score) AS avg_score
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
ORDER BY avg_score DESC;
```
3. 查询没有选修任何课程的学生的姓名和性别。
答案:
```
SELECT s.s_name, s.s_sex
FROM student s
LEFT JOIN score sc ON s.s_id = sc.s_id
WHERE sc.s_id IS NULL;
```
4. 查询同时选修了编号为 1 和编号为 2 课程的学生的姓名。
答案:
```
SELECT s.s_name
FROM student s
INNER JOIN score sc ON s.s_id = sc.s_id
WHERE sc.c_id IN (1, 2)
GROUP BY s.s_id, s.s_name
HAVING COUNT(DISTINCT sc.c_id) = 2;
```
5. 查询选修了所有课程的学生的姓名。
答案:
```
SELECT s.s_name
FROM student s
INNER JOIN score sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
HAVING COUNT(DISTINCT sc.c_id) = (SELECT COUNT(*) FROM course);
```
这些题目和答案都比较复杂,需要熟练掌握 SQL 查询和连接的知识才能解决。
阅读全文