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
);
版权声明:本文为zaifengzhong52原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。