数据库操作-5(多表查询)

1、查询每门课的间接选修课:

SELECT d1.cno, d2.pcno FROM Course AS d1, Course AS d2 
WHERE d1.pcno = d2.cno

2、查询选课成绩在80分以上的男生姓名、课程名、成绩

SELECT sname, cname, grade
FROM (
	SELECT sname, cname, grade
	FROM Student, SC2, Course
	WHERE Student.sno = SC2.sno 
		and SC2.cno = Course.cno
		and sex = 'M') 
	AS temp(sname, cname, grade)
WHERE grade >=60

3、查询没有选修1号课程的学生学号、姓名

select sno,sname FROM Student
WHERE not EXISTS(
	SELECT * FROM SC2
	WHERE sno = Student.sno and cno = '1'
)

4、查询选修了全部课程的学生姓名

-- 查询这样一个学生没有一门课程不选修
select sno,sname FROM Student
WHERE not EXISTS(
	SELECT * FROM Course    --每门课都没选
	WHERE not EXISTS(
		SELECT * FROM SC2     --某学生选某课
		WHERE sno=Student.sno and cno=Course.cno
	)
)

5、 查询至少选修了学号为1的同学选修的全部课程的学生的学号*

select sno FROM Student X 
WHERE not EXISTS (
	SELECT * FROM SC2 Y
	WHERE Y.sno = 1 AND not EXISTS(
		SELECT * FROM SC2 Z
		WHERE X.sno = Z.sno and Y.cno=Z.cno
	)
)

6、选修了1或2号课的学生学号

-- union 并
-- intersect 交
-- except 差
(SELECT sno FROM SC2
WHERE cno = '1')
union
(
	SELECT sno FROM SC2 WHERE cno = '3'
)

7、连接

SELECT * FROM Student NATURAL JOIN SC2 --自然连接 在目标列中去除相同的字段名
SELECT * FROM Student NATURAL left OUTER JOIN SC2 --左外连接
SELECT * FROM Student NATURAL right OUTER JOIN SC2 

SELECT * FROM Student NATURAL full OUTER JOIN SC2 --全连接
SELECT * FROM Student NATURAL left OUTER JOIN SC2
UNION
SELECT * FROM Student NATURAL right OUTER JOIN SC2

8、插入多条数据

create table st1_grade(
	gno CHAR(10),
	name char(10),
	avg FLOAT
);
insert into st1_grade(gno, name, avg)
SELECT Student.sno, sname, avg(grade) as avg_good 
FROM Student, SC2
WHERE Student.sno = SC2.sno 
GROUP BY sno 
HAVING avg_good >=70

9、1号同学在数学课上考试作弊成绩置为0

UPDATE SC2 SET grade = 0
WHERE sno = 1 and cno = (
	SELECT cno FROM Course
	WHERE cname='math'
)

10、删除选课不足10人的课程记录

delete FROM Course
WHERE cno in (
	SELECT cno FROM SC2
	GROUP BY cno
	HAVING count(*) < 10
)