oracle 版sql五十题，亲手验证过

最新推荐文章于 2024-07-12 14:41:43 发布

louzhu126

最新推荐文章于 2024-07-12 14:41:43 发布

阅读量717

点赞数 1

分类专栏： sql 文章标签： sql语言

本文链接：https://blog.csdn.net/louzhu126/article/details/78962490

版权

本文提供了50道关于Oracle SQL的实践题目，涉及学生、课程、教师和成绩等多个表的数据查询，包括比较不同课程成绩、筛选特定条件的学生、计算平均分、查询选课情况、统计成绩分布等复杂查询。通过这些题目，读者可以深入理解Oracle SQL的使用和查询技巧。

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

- –1.学生表 Student(S,Sname,Sage,Ssex) –S 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 –2.课程表 Course(C,Cname,T) –C –课程编号,Cname 课程名称,T 教师编号 –3.教师表 Teacher(T,Tname) –T 教师编号,Tname 教师姓名 –4.成绩表 SC(S,C,score) –S 学生编号,C 课程编号,score 分数

–创建测试数据

create table Student(S varchar(10),Sname varchar(10),Sage
datetime,Ssex nvarchar(10)) insert into Student values(‘01’ , ‘赵雷’ ,
‘1990-01-01’ , ‘男’) insert into Student values(‘02’ , ‘钱电’ ,
‘1990-12-21’ , ‘男’) insert into Student values(‘03’ , ‘孙风’ ,
‘1990-05-20’ , ‘男’) insert into Student values(‘04’ , ‘李云’ ,
‘1990-08-06’ , ‘男’) insert into Student values(‘05’ , ‘周梅’ ,
‘1991-12-01’ , ‘女’) insert into Student values(‘06’ , ‘吴兰’ ,
‘1992-03-01’ , ‘女’) insert into Student values(‘07’ , ‘郑竹’ ,
‘1989-07-01’ , ‘女’) insert into Student values(‘08’ , ‘王菊’ ,
‘1990-01-20’ , ‘女’) create table Course(C
varchar(10),Cname,varchar(10),T varchar(10)) insert into Course
values(‘01’ , ‘语文’ , ‘02’) insert into Course values(‘02’ , ‘数学’ ,
‘01’) insert into Course values(‘03’ , ‘英语’ , ‘03’) create table
Teacher(T varchar(10),Tname,varchar(10)) insert into Teacher
values(‘01’ , ‘张三’) insert into Teacher values(‘02’ , ‘李四’) insert
into Teacher values(‘03’ , ‘王五’) create table SC(S varchar(10),C
varchar(10),score decimal(18,1)) insert into SC values(‘01’ , ‘01’ ,
80) insert into SC values(‘01’ , ‘02’ , 90) insert into SC
values(‘01’ , ‘03’ , 99) insert into SC values(‘02’ , ‘01’ , 70)
insert into SC values(‘02’ , ‘02’ , 60) insert into SC values(‘02’ ,
‘03’ , 80) insert into SC values(‘03’ , ‘01’ , 80) insert into SC
values(‘03’ , ‘02’ , 80) insert into SC values(‘03’ , ‘03’ , 80)
insert into SC values(‘04’ , ‘01’ , 50) insert into SC values(‘04’ ,
‘02’ , 30) insert into SC values(‘04’ , ‘03’ , 20) insert into SC
values(‘05’ , ‘01’ , 76) insert into SC values(‘05’ , ‘02’ , 87)
insert into SC values(‘06’ , ‘01’ , 31) insert into SC values(‘06’ ,
‘03’ , 34) insert into SC values(‘07’ , ‘02’ , 89) insert into SC
values(‘07’ , ‘03’ , 98)

–1、查询”01”课程比”02”课程成绩高的学生的信息及课程分数

Select * from student t1,( Select a.s,a.score from (select s,score,c
from sc where sc.c=’01’ ) a, Select a.s,a.score from (select
s,score,c from sc where sc.c=’02’ ) b Where a.s=b.s and
a.score

–2、查询”01”课程比”02”课程成绩低的学生的信息及课程分数

Select a.,c. From ( Select a.* from (select * from sc where sc.c in (‘01’))a Left join (select *
from sc where sc.c in (‘02’)) b On a.s=b.s where a.score,b.score) a,
student c Where a.s