同步课 / 2016级数据库原理

--51)

--连接查询
select distinct a.*
from kuangshenshen.course a, kuangshenshen.teacher_course b, kuangshenshen.teacher c, 

kuangshenshen.department d
where a.cno = b.cno and b.tno = c.tno and c.dno = d.dno and dname = '软件学院'

--不相关子查询
select *
from kuangshenshen.course
where cno in (
	select cno
	from kuangshenshen.teacher_course
	where tno in (
		select tno
		from kuangshenshen.teacher
		where dno in (
			select dno
			from kuangshenshen.department
			where dname = '软件学院'
		)
	)
)

--相关子查询
select *
from kuangshenshen.course a
where exists (
	select *
	from kuangshenshen.teacher_course b
	where b.cno = a.cno and exists (
		select *
		from kuangshenshen.teacher c
		where c.tno = b.tno and exists (
			select *
			from kuangshenshen.department d
			where d.dno = c.dno and dname = '软件学院'
		)
	)
)

--52)

--连接查询
select distinct sno, a.cno, score
from kuangshenshen.student_course a, kuangshenshen.teacher_course b, yyh.teacher c, 

kuangshenshen.department d
where a.cno = b.cno and b.tno = c.tno and c.dno = d.dno and dname = '软件学院'
order by a.cno desc

--不相关子查询
select sno, cno, score
from kuangshenshen.student_course
where cno in (
	select cno
	from kuangshenshen.teacher_course
	where tno in (
		select tno
		from kuangshenshen.teacher
		where dno in (
			select dno
			from kuangshenshen.department
			where dname = '软件学院'
		)
	)
)
order by cno desc

--相关子查询
select sno, cno, score
from kuangshenshen.student_course a
where exists (
	select *
	from kuangshenshen.teacher_course b
	where b.cno = a.cno and exists (
		select *
		from kuangshenshen.teacher c
		where c.tno = b.tno and exists (
			select *
			from kuangshenshen.department d
			where d.dno = c.dno and dname = '软件学院'
		)
	)
)
order by cno desc

--53)

--连接查询+不相关子查询
select tname, dname
from kuangshenshen.teacher a, kuangshenshen.department b
where tno not in (
	select tno
	from kuangshenshen.teacher_course
) and a.dno = b.dno

--连接查询+相关子查询
select tname, dname
from kuangshenshen.teacher a, kuangshenshen.department b
where not exists (
	select *
	from kuangshenshen.teacher_course c
	where c.tno = a.tno
)and a.dno = b.dno

--差操作
select tname, dname 
from kuangshenshen.teacher a, kuangshenshen.department b
where a.dno = b.dno
except
select distinct tname, dname 
from kuangshenshen.teacher_course a, kuangshenshen.department b, 

kuangshenshen.teacher c
where a.tno = c.tno and c.dno = b.dno 

--54)

--连接查询
select pno, COUNT(tno)
from kuangshenshen.teacher a, kuangshenshen.department b
where a.dno = b.dno and dname = '软件学院'
group by pno

--不相关子查询
select pno, COUNT(tno)
from kuangshenshen.teacher
where tno in (
	select tno 
	from kuangshenshen.teacher
	where dno in (
		select dno
		from kuangshenshen.department
		where dname = '软件学院'
	)
)
group by pno

--相关子查询
select pno, COUNT(tno)
from kuangshenshen.teacher a
where exists (
	select *
	from kuangshenshen.teacher b
	where b.tno = a.tno and exists (
		select *
		from kuangshenshen.department c
		where c.dno = b.dno and dname = '软件学院'
	)
)
group by pno

--55)

--建立视图法
create view avg_3
as
select AVG(score) savg
from kuangshenshen.student_course a, kuangshenshen.course b
where a.cno = b.cno and cname = '数据结构'

select b.*
from kuangshenshen.student_course a, kuangshenshen.student b, kuangshenshen.course c
where a.sno = b.sno and c.cno = a.cno and cname = '数据结构' and score > (
	select savg
	from avg_3
)

--连接+嵌套查询
select b.*
from kuangshenshen.student_course a, kuangshenshen.student b, kuangshenshen.course c
where a.sno = b.sno and c.cno = a.cno and cname = '数据结构' and score > (
	select AVG(score) savg
	from kuangshenshen.student_course d, kuangshenshen.course e
	where d.cno = e.cno and cname = '数据结构'
)

--56)
select a.sno, sname, tname, cname, score
from kuangshenshen.student a, kuangshenshen.teacher b, ykuangshenshen.course c, 

kuangshenshen.student_course d, kuangshenshen.teacher_course e
where a.sno = d.sno and b.tno = e.tno and d.cno = c.cno and e.cno = d.cno

--57)

--连接查询
select distinct classno
from kuangshenshen.teacher_course a, kuangshenshen.teacher b, 

kuangshenshen.department c
where a.tno = b.tno and b.dno = c.dno and dname = '法政学院' and semester = '1'

--不相关子查询
select distinct classno
from kuangshenshen.teacher_course
where tno in (
	select tno 
	from kuangshenshen.teacher
	where dno in (
		select dno 
		from kuangshenshen.department
		where dname = '法政学院'
	)
)

--相关子查询
select distinct classno
from kuangshenshen.teacher_course a
where exists (
	select *
	from kuangshenshen.teacher b
	where b.tno = a.tno and exists (
		select *
		from kuangshenshen.department c
		where c.dno = b.dno and dname = '法政学院'
	)
)

--58)

--连接查询
select a.tno, tname 
from kuangshenshen.teacher a, kuangshenshen.teacher_course b
where a.tno = b.tno and semester = '2' and classroom like '公教楼%'

--不相关子查询
select tno, tname
from kuangshenshen.teacher
where tno in (
	select tno
	from kuangshenshen.teacher_course
	where semester = '2' and classroom like '公教楼%'
)

--59)

--连接查询
select cname, AVG(score)
from kuangshenshen.course a, kuangshenshen.student b, kuangshenshen.student_course c, 

kuangshenshen.department d
where a.cno = c.cno and b.dno = d.dno and b.sno = c.sno and dname = '数信学院'
group by cname

--连接+不相关子查询
select cname, AVG(score)
from kuangshenshen.course a, kuangshenshen.student_course b
where score in (
	select score
	from kuangshenshen.student_course
	where sno in (
		select sno 
		from kuangshenshen.student
		where dno in (
			select dno 
			from kuangshenshen.department
			where dname = '数信学院'
		)
	)
)and a.cno = b.cno
group by cname



--60)

select *
from kuangshenshen.student a
where not exists (
	select *
	from kuangshenshen.course b
	where not exists (
		select *
		from kuangshenshen.student_course c
		where c.cno = b.cno and a.sno = c.sno
	)
)


select *
from kuangshenshen.student a, (
					 select a.sno, COUNT(cno) sc_num
					 from kuangshenshen.student a, 

kuangshenshen.student_course b
					 where exists (
						select *
						from kuangshenshen.student_course c
						where a.sno = c.sno
					 )and a.sno = b.sno
					 group by a.sno
					 ) b
where a.sno = b.sno and b.sc_num = (select COUNT(cno) cnum
				    from yyh.course)

--61)

select *
from kuangshenshen.student a
where not exists (
	select *
	from kuangshenshen.teacher_course b, kuangshenshen.teacher c
	where b.tno = c.tno and tname = '鲁婵娟' and not exists (
		select *
		from kuangshenshen.student_course d
		where d.cno = b.cno and d.sno = a.sno
	)

)

--62)

select *
from kuangshenshen.student
where classno in (
	select classno
	from kuangshenshen.student
	where sname = '张小兵'
)
except
select *
from kuangshenshen.student
where sname = '张小兵'

select *
from kuangshenshen.student
where classno in (
	select classno
	from kuangshenshen.student
	where sname = '张小兵'
)and sno != (
	select sno
	from kuangshenshen.student
	where sname = '张小兵'
)

--63)

select *
from kuangshenshen.student
where DATEPART(YY, birthday) = (
	select DATEPART(YY, birthday)
	from kuangshenshen.student
	where sname = '刘英伟'
)
except
select *
from kuangshenshen.student
where sname = '刘英伟'

select *
from kuangshenshen.student a
where exists (
	select *
	from kuangshenshen.student b
	where DATEPART(YY, a.birthday) = DATEPART(YY, b.birthday) and sname = '刘英伟

'
)
except
select *
from kuangshenshen.student
where sname = '刘英伟'

--64)

--不相关子查询
select *
from kuangshenshen.student
where sno in (
	select sno 
	from kuangshenshen.student_course
	group by sno
	having COUNT(cno) > 3
)

--相关子查询
select *
from kuangshenshen.student a
where exists (
	select *
	from kuangshenshen.student_course b
	where a.sno = b.sno
	group by sno
	having COUNT(cno) > 3
)

select *
from kuangshenshen.student a
where 3 < (
	select COUNT(cno)
	from kuangshenshen.student_course b
	where a.sno = b.sno
)

--65)

--不相关子查询
select *
from kuangshenshen.student
where classno in (
	select classno
	from kuangshenshen.student
	where sex = '女'
	group by classno
	having COUNT(sno) >= 2
)

--相关子查询
select *
from kuangshenshen.student a
where exists (
	select *
	from kuangshenshen.student b
	where a.classno = b.classno and sex = '女'
	group by classno
	having COUNT(sno) >= 2
)

select *
from kuangshenshen.student a
where 2 <= (
	select COUNT(sno)
	from kuangshenshen.student b
	where a.classno = b.classno and sex = '女'
)

--66)

--连接查询
select * 
from kuangshenshen.teacher a, kuangshenshen.department b
where a.dno = b.dno and dname = '软件学院' and 2018 - DATEPART(YY, birthday) < 25 


--相关子查询
select *
from kuangshenshen.teacher a
where exists (
	select *
	from kuangshenshen.department b
	where a.dno = b.dno and dname = '软件学院' and 2018 - DATEPART(YY, birthday) 

< 25
)

--不相关子查询
select *
from kuangshenshen.teacher
where dno in (
	select dno 
	from kuangshenshen.department
	where dname = '软件学院'
)and 2018 - DATEPART(YY, birthday) < 25

--67)

--视图+集合查询
create view class_avg
as
select cno, AVG(score) cavg
from kuangshenshen.student_course
group by cno

select a.*
from kuangshenshen.student a, kuangshenshen.student_course b
where a.sno = b.sno 
except
select distinct a.*
from kuangshenshen.student a, kuangshenshen.student_course b, class_avg c
where a.sno = b.sno and b.cno = c.cno and score > cavg

--子表+集合查询
select *
from kuangshenshen.student a
where exists (
	select *
	from kuangshenshen.student_course b
	where a.sno = b.sno 
)
except
select *
from kuangshenshen.student a
where exists (
	select *
	from kuangshenshen.student_course b
	where a.sno = b.sno and exists (
		select *
		from (select cno, AVG(score) cavg
			  from kuangshenshen.student_course
			  group by cno) c
		where c.cno = b.cno and score > cavg
		)
)

--68)

select sno no, sname name
from kuangshenshen.student
where 2018 - DATEPART(YY, birthday) < 23 and sex = '女'
union
select tno no, tname name
from kuangshenshen.teacher
where 2018 - DATEPART(YY, birthday) < 23 and sex = '女'

--69)

select *
from kuangshenshen.teacher
where tno not in (
	select tno
	from kuangshenshen.teacher_course
)

--70)

select *
from kuangshenshen.student
where classno = '3' and sex = '女'

--更新

--1)

update kuangshenshen.student
set sage += 1
where 2018 - DATEPART(YY, birthday) < 18 and sex = '男'

--2)

update kuangshenshen.teacher
set tel = '83421236'
where tname = '王英'

--3)

update kuangshenshen.teacher_course
set classroom = 'D403'
where cno = (
	select cno
	from kuangshenshen.course
	where cname = '数据结构'
)

--4)

update kuangshenshen.student_course
set score += 5
where cno = (
	select cno
	from kuangshenshen.course
	where cname = '数据库原理'
)and score < 70

--5)

update kuangshenshen.student_course
set score += 10
where sno in (
	select sno
	from kuangshenshen.student
	where dno in (
		select dno 
		from kuangshenshen.department
		where dname = '软件学院'
	)
)and cno in (
	select cno
	from kuangshenshen.course
	where cname = '高等数学'
)

--6)

update kuangshenshen.student
set tel = null
where dno in (
	select dno
	from kuangshenshen.department
	where dname = '数信学院'
)

--7)

delete 
from kuangshenshen.student_course
where score = null

--8)

delete 
from kuangshenshen.student
where sname like '%强%'

--9)

delete
from kuangshenshen.student
where dno in (
	select dno 
	from kuangshenshen.department
	where dname = '数信学院'
)and sex = '女'

--10)

delete 
from kuangshenshen.student
where DATEPART(YY, entime) < 2000

--11)

delete
from kuangshenshen.teacher_course
where tno in (
	select tno
	from kuangshenshen.teacher
	where dno in (
		select dno
		from kuangshenshen.department
		where dname = '软件学院'
	)
)

--12)

create view course_avg
as
select cno, AVG(score) cavg
from kuangshenshen.student_course
group by cno

update kuangshenshen.course
set credit += 1
where cno in (
	select cno
	from course_avg
	where cavg in (
		select MAX(cavg)
		from course_avg
	)
)

--13)

update kuangshenshen.student_course
set score *= 1.1
where cno in (
	select cno
	from kuangshenshen.course
	where experiment = 36
)

--14)

delete
from kuangshenshen.teacher
where tno not in (
	select tno 
	from kuangshenshen.teacher_course
)

--视图

--1)

create view teacher_w
as
select * 
from kuangshenshen.teacher
where sex = '女'

--2)

create view stu_class2
as
select sno, sname, sex, dno
from kuangshenshen.student
where dno in (
	select dno 
	from kuangshenshen.department
	where dname = '数信学院'
)and classno = '2'

--3)

create view dep_num
as
select b.dno, COUNT(sno) num
from kuangshenshen.student a, kuangshenshen.department b
where a.dno = b.dno 
group by b.dno

--4)

create view course_avg
as
select a.cno, cname, AVG(score) cavg
from kuangshenshen.student_course a, kuangshenshen.course b
group by a.cno, cname
having AVG(score) > 90

--5)

create view teacher_num
as
select tno, COUNT(sno)
from kuangshenshen.teacher_course a, kuangshenshen.student_course b
where a.cno = b.cno
group by tno

--6)

select dname
from kuangshenshen.department a, kuangshenshen.student b
where a.dno = b.dno
group by dname
having COUNT(sno) > 500

--7

select COUNT(sno)
from kuangshenshen.student_course a, kuangshenshen.teacher_course b, 

kuangshenshen.teacher c
where a.cno = b.cno and b.tno = c.tno and tname = '王强'

任务已更新

任务已更新