山东大学数据库实验2（2021年最新版）

最新推荐文章于 2024-04-09 20:59:06 发布

weixin_47373497

最新推荐文章于 2024-04-09 20:59:06 发布

阅读量625

点赞数 1

文章标签：数据库 oracle

本文链接：https://blog.csdn.net/weixin_47373497/article/details/116503629

版权

这篇博客详细展示了如何使用SQL创建和操作视图，包括筛选特定学生课程、查找同时选修特定课程的学生、计算平均成绩、统计首字母出现频率、查询特定科目成绩及不及格课程次数等步骤，涉及数据库查询的交集、差集、聚合函数和分组等概念。

摘要由CSDN通过智能技术生成

山东大学数据库实验2（2021年最新版）

本人分步写的，中间有空行的话，代表上一步结束，转向下一步，一个题由多步完成

2
create or replace view test2_02 as
(select sid,name
from pub.student_course natural join pub.student
where sid!='200900130417'and 
cid in(
select cid
from pub.student_course
where sid='200900130417'));

4
create or replace view test2_04 as(
(select sid,name
from pub.student natural join pub.student_course
where cid =
(
select cid
from pub.course
where pub.course.name='操作系统'
))
intersect
(select sid,name
from pub.student natural join pub.student_course
where cid =
(
select cid
from pub.course
where pub.course.name='数据结构'
))
minus
select sid,name
from pub.student natural join pub.student_course
where cid =(
select cid
from pub.course
where pub.course.name='程序设计语言'));


5

create or replace view test2_05 as(
select sid,name,round(avg(score),0) avg_score,sum(score) sum_score
from pub.student natural join pub.student_course
where  age='20'
group by sid,name);
8

create or replace view test2_08 as(
select substr(name,1,1) second_name,count(sid ) p_count
from pub.student
group by substr(name,1,1));

9

create or replace view test2_09 as(
select sid,name,score
from pub.student natural join pub.student_course
where cid ='300003');



	create or replace view test2_10 as(
select sid,name,count(score) count_score
from pub.student natural join pub.student_course
where score<60
group by sid,cid
having count_score>1
);

10

create table a as(
select sid,name,count(score) count_score
from pub.student natural join pub.student_course
where score<60 
group by sid,cid,name
having count(score) >1
);
create or replace view test2_10 as(
select sid,name
from a);


create  or replace view test2_06  as(
select cid,name,max(score) max_score,count(sid) max_score_count,score
From（ pub.student_course right join pub.course），c
Where(cid=s.cid and score=c.s
group by cid,name
);



创建c表
create table c as(
select cid,name,max(score) s
from pub.student_course natural join pub.course
group by cid,name
);