数据库——SQL实验二

SQL实验二

---

“至少” 表示 “存在” 可以用 in 来表达
1. 选出这个学生的选课表（可以考虑重修情况，但是课程号唯一
2. 对每一条选课信息对判断，如果课程号在表中，则保留人名
注意：关系本身就是一个集合，也可以使用 distinct

craete or replace view test2_02 as (
	select sid, name 
	from pub.student
	where sid in (
		select sid 
		from pub.sudent_course
		where cid in (
			select cid
			from pub.student_course
			where sid=200900130417
		)
	)
)
minus (
	select sid, name
	from pub.student
	where sid=200900130417
);

"至少" 表示 “存在”
模式和上一题一样：先找一个参考表，再依据参考表筛选

solution 1:
select sid, name 
from pub.student
where sid in (
	select sid
	from pub.student_course
	where cid in (
		select cid
		from pub.course
		where fcid=300002
	)
);

solution 2:
select sid, name
from pub.student_course natural join pub.student
where cid in (
	select cid
	from pub.course
	where fcid=300002
);

select sid, name
from pub.student natural join pub.student_course
where sid in (
	select sid 
	from pub.student_course
	where cid = 300002
) and sid in (
	select sid 
	from pub.student_course
	where cid = 300005
) and sid not in (
	from pub.student_course
	where cid = 300001
);

“平均值”： 分组聚集
1. 找到分组标准
2. 明确聚集函数类型再计算
注意：SQL的书面写法更名要有 as ，即 avg(score) as avg_score

select sid, name, round(avg(score)) avg_score, sum(sore) sum_score
from pub.student natural join pub.student_course
where age=20
group by sid, name

“最高”：可以用 max，也可以用笛卡尔积的原始方法
“计数”：count
拆分任务：
	1. 使用 cid 聚集，找出每门课最大值
	2. 再上一基础上选出分数和课程号相同的记录，再做count 聚集
	3. 将1，2表连起来

select a.cid, a.name, max_score, max_score_count
from (
	  select cid, name
	  from pub.course
) a, (
	select sc.cid, count(distinct sid) max_score_count
	from pub.student_course
	group by cid
) b, (
	select sc.cid, count(distinct sid) max_score_count
	from pub.student_course sc,
	( 
		select cid,max(score) max_score from pub.student_course
		group by cid 
	) max_sc
	where sc.cid=max_sc.cid and sc.score=max_sc.max_score
	group by sc.cid
) d
where a.cid = b.cid and b.cid = d.cid

like 语句

select sid, name
from pub.student
minus (
	select sid,name
	from pub.student
	where name like '张%' or name like '李%' or name like '王%'
);

字串函数 substr(pos, len)
count(*) 为自己计数

select substr(name,1,1)second_name, count(*) p_count
from pub.student
group by substr(name,1,1)

select sid, name, score
from pub.student natural join pub.student_course
where cid = 300003;

本质还是计数的聚合，就是外面再套一层选择

select sid, name
from pub.student
where sid in (
	select sid 
	from (
		select sc.sid, sc.cid, count(*) count
		from pub.student_course sc
		where score < 60
		group by sc.sid, sc.cid
	)
	where count >= 2
);