8、select student.* from student left join (select s_id from score join (select c_id from course join teacher on course.t_id=teacher.t_id and t_name='张三')tmp2 on score.c_id=tmp2.c_id )tmp on student.s_id = tmp.s_id where tmp.s_id is null;关系代数表达式
时间: 2023-06-16 08:05:11 浏览: 60
这条 SQL 查询语句的关系代数表达式如下:
π<sub>student.*</sub> (student) - π<sub>tmp.s_id</sub> (σ<sub>tmp.s_id is not null</sub> (student ⨝<sub>s_id=s_id</sub> tmp))
其中,π 表示投影运算符,σ 表示选择运算符,⨝ 表示自然连接运算符,tmp 是一个子查询结果的别名,其代表的是选取该老师所教授的课程的成绩表中的学生 ID,然后与学生表进行左连接,最终选取左表中存在但右表中不存在的学生记录。
相关问题
我给你四张表,表结构是第一张表名称是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 查询和连接的知识才能解决。
以下查询请尽量多使用不同的SQL语句实现! (1)选择数据库JX; (2)查询选修了“3”号课程且成绩在70分以上的学生信息; (3)查询所有不比“电子工程”系的所有学生年龄大的其他系的学生信息; (4)查询选修了“3”号课程的学生姓名、成绩及任课教师姓名; (5)查询开设的课程中选课人数不足15人的课程号及人数信息; (6)查询选修课程数低于3门的学生信息; (7)查询没有选修课程的同学的基本信息; (8)查询既选修了“3”号又选修了“4”号课程的学生姓名、课程名及成绩; (9)查询同时选修了“操作系统”课程和“数据库原理”课程的学生名单; (10)查询所有同学的基本信息及选课情况(包含未选课同学的信息); (11)列出所有参加了数据库课程考试的学生姓名和成绩; (12)查询所有学生和教师的姓名(允许重名)并标识类型(学生/老师); (13)查询每一课程的间接选修课,以“课程名”、“间接选修课名”作列名; (14)列出所有没有选修“数据结构”课程的学生的学号、姓名、所在院系。
1. 使用MySQL语句:
(1) USE JX;
(2) SELECT * FROM student WHERE s_id IN (SELECT s_id FROM score WHERE c_id = 3 AND score >= 70);
(3) SELECT * FROM student WHERE age > (SELECT MAX(age) FROM student WHERE department != '电子工程');
(4) SELECT student.s_name, score.score, teacher.t_name FROM student LEFT JOIN score ON student.s_id = score.s_id LEFT JOIN course ON score.c_id = course.c_id LEFT JOIN teacher ON course.t_id = teacher.t_id WHERE score.c_id = 3;
(5) SELECT c_id, COUNT(*) AS num FROM score GROUP BY c_id HAVING num < 15;
(6) SELECT student.s_id, student.s_name FROM student LEFT JOIN score ON student.s_id = score.s_id GROUP BY student.s_id HAVING COUNT(*) < 3;
(7) SELECT * FROM student WHERE s_id NOT IN (SELECT s_id FROM score);
(8) SELECT student.s_name, course.c_name, score.score FROM student LEFT JOIN score ON student.s_id = score.s_id LEFT JOIN course ON score.c_id = course.c_id WHERE score.c_id IN (3,4);
(9) SELECT student.s_name FROM student LEFT JOIN score ON student.s_id = score.s_id LEFT JOIN course ON score.c_id = course.c_id WHERE course.c_name = '操作系统' AND student.s_id IN (SELECT student.s_id FROM student LEFT JOIN score ON student.s_id = score.s_id LEFT JOIN course ON score.c_id = course.c_id WHERE course.c_name = '数据库原理');
(10) SELECT student.s_id, student.s_name, student.department, course.c_id, course.c_name, score.score FROM student LEFT JOIN score ON student.s_id = score.s_id RIGHT JOIN course ON score.c_id = course.c_id;
(11) SELECT student.s_name, score.score FROM student LEFT JOIN score ON student.s_id = score.s_id LEFT JOIN course ON score.c_id = course.c_id WHERE course.c_name = '数据库';
(12) SELECT s_name AS name, '学生' AS type FROM student UNION SELECT t_name AS name, '老师' AS type FROM teacher;
(13) SELECT course.c_name AS course, indirect_course.c_name AS indirect_course FROM course LEFT JOIN indirect_course ON course.c_id = indirect_course.c_id;
(14) SELECT student.s_id, student.s_name, student.department FROM student WHERE student.s_id NOT IN (SELECT s_id FROM score WHERE c_id = 2);
2. 使用Oracle语句:
(1) ALTER SESSION SET current_schema = JX;
(2) SELECT * FROM student WHERE s_id IN (SELECT s_id FROM score WHERE c_id = 3 AND score >= 70);
(3) SELECT * FROM student WHERE age > (SELECT MAX(age) FROM student WHERE department != '电子工程');
(4) SELECT student.s_name, score.score, teacher.t_name FROM student, score, course, teacher WHERE student.s_id = score.s_id AND score.c_id = course.c_id AND course.t_id = teacher.t_id AND score.c_id = 3;
(5) SELECT c_id, COUNT(*) AS num FROM score GROUP BY c_id HAVING COUNT(*) < 15;
(6) SELECT student.s_id, student.s_name FROM student, score WHERE student.s_id = score.s_id GROUP BY student.s_id HAVING COUNT(*) < 3;
(7) SELECT * FROM student WHERE s_id NOT IN (SELECT s_id FROM score);
(8) SELECT student.s_name, course.c_name, score.score FROM student, score, course WHERE student.s_id = score.s_id AND score.c_id = course.c_id AND score.c_id IN (3,4);
(9) SELECT student.s_name FROM student, score, course WHERE student.s_id = score.s_id AND score.c_id = course.c_id AND course.c_name = '操作系统' AND student.s_id IN (SELECT student.s_id FROM student, score, course WHERE student.s_id = score.s_id AND score.c_id = course.c_id AND course.c_name = '数据库原理');
(10) SELECT student.s_id, student.s_name, student.department, course.c_id, course.c_name, score.score FROM student, score, course WHERE student.s_id = score.s_id(+) AND score.c_id = course.c_id(+);
(11) SELECT student.s_name, score.score FROM student, score, course WHERE student.s_id = score.s_id(+) AND score.c_id = course.c_id(+) AND course.c_name = '数据库';
(12) SELECT s_name AS name, '学生' AS type FROM student UNION SELECT t_name AS name, '老师' AS type FROM teacher;
(13) SELECT course.c_name AS course, indirect_course.c_name AS indirect_course FROM course, indirect_course WHERE course.c_id = indirect_course.c_id(+);
(14) SELECT student.s_id, student.s_name, student.department FROM student WHERE student.s_id NOT IN (SELECT s_id FROM score WHERE c_id = 2);
相关推荐
![sql](https://img-home.csdnimg.cn/images/20210720083646.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://img-home.csdnimg.cn/images/20210720083646.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)