Oracle sql语句

最新推荐文章于 2022-04-19 20:38:17 发布

小飞侠xi

最新推荐文章于 2022-04-19 20:38:17 发布

阅读量1.1k

点赞数 5

分类专栏： Oracle

本文链接：https://blog.csdn.net/sinat_42410314/article/details/101422044

版权

Oracle 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

3-2

use jxsk

insert into SC values('s3','c2','70')

insert into SC values('s3','c4','85')

insert into SC values('s4','c2','85')

insert into SC values('s4','c3','83')

insert into SC values('s5','c2','89')

3-3

导入数据->平面文件源->右边未对齐，在第一个数据行中显示列名称->目标：M OLE DB For SQL Server

3-4

update S set age=age+1

where

DEPT='计算机'

3-5

update SC set SCORE=SCORE-10

where SCORE<70

select * into makeup_s

from SC where SCORE<60

3-6

delete from makeup_s where SCORE<20

4-1

alter table T

add constraint pk_tno primary key (TNO)

create table TEST_C(

CNO char(2) constraint PK_TEST_C PRIMARY KEY,

CN char(10),

CT tinyint)

create table TEST_TC(

CNO char(2),

TNO char(2),

constraint PK_TEST_TC PRIMARY KEY(TNO,CNO))

alter table C

add constraint unique_c unique(CN)

alter table test_tc

add id_tc int identity

use jiaoxuedb

alter table Student add constraint con_1 primary key(SNO)

alter table Teacher add constraint con_2 primary key(TNO)

alter table Course add constraint con_3 primary key(CNO)

alter table SC add constraint con_4 primary key(SNO,CNO)

alter table TC add constraint con_5 primary key (TNO,CNO)

4-2

use jiaoxuedb

alter table TC with nocheck

add constraint FK_T_TC foreign key(TNO) references Teacher(TNO)

on delete cascade

use jiaoxuedb

alter table SC with nocheck

add constraint FK_S_SC foreign key(SNO) references Student(SNO)

on delete cascade

4-3

use jiaoxuedb

alter table Student

add constraint IX_Student unique(Sname)

use jiaoxuedb

alter table Teacher with nocheck

add constraint CK_Teacher check(sex='男' or sex='女')

alter table Teacher

add constraint DF_Teacher_Sex default '男' for sex

use jiaoxuedb

alter table Teacher with nocheck

add constraint CK_Teacher_Age check(age>=0 and age<=100)

alter table SC with nocheck

add constraint CK_SC_Score check(Score>=0 and Score<=100)

验证主码，插入数据主码为空不能插入

use jiaoxuedb

insert into Student(Sno) values(null)

参照完整性，删除Teacher表中的某一TNO TC中的TNO随之消失

delete from Teacher

where Tno=000001

唯一键，插入与之相同的数据，发生错误

use jiaoxuedb

insert into Student(Sno,Sname) values('991205',N'王一山')

性别，年龄，输入大于约束的值，发生错误

use jiaoxuedb

insert into Student(Sno,Sname,Age) values('991205',N'王二山',101)

5-1

use jxsk

create clustered index index_TNO on T(TNO desc)

5-2

use jxsk

create clustered index index_SC_CNO on SC(TNO asc)

use jxsk

create clustered index index_SC_SCORE on SC(SCORE desc)

5-3

use jxsk

create unique index index_S_SNO on S(SNO asc)

6-1

create view SCORE_VIEW as

select student.SNO,SName,CNo,Score from Student,SC

where Student.SNO=SC.SNO

6-2

create view S_VIEW

as select Sno,Sname,Sex

from Student

where Dept=N'计算机'

6-3

update SCORE_VIEW set SCORE=100

where Sno='991102' and Cno='01001'

6-4

update S_VIEW set sex=N'男'

where Sname=N'王蕾'

6-5

create view SCORE_VIEW_CDEPT as select

student.Sno,Sname,Cname,Score from Student, Course,SC

where Student.sno=sc.sno and course.cno=sc.cno and student.dept=N'计算机'

6-6

ALTER VIEW S_VIEW

AS SELECT Sno,Sname,Sex,Age

FROM Student

WHERE Dept=N'计算机'

6-7

create view SAL_VIEW as select

Tname,Sex,Prof,Salsum=Sal+Comm,Dept

from Teacher

6-8

select Salsum from SAL_VIEW

where Tname=N'张朋'

实验七内容

7.1

select * from S

select sn,year(getdate())-age as 出生年份from s

select sno from sc where score<60

select sn,dept,age from s where (age>=20 and age<=23)

select sn,sno,sex from s where sn like N'李%'

select sn,dept from s where sn like N'_明%'and sex=N'男'

select sn,dept from S where dept=N'信息' or dept=N'计算机'

order by dept asc,sn desc

select sno,cno,score from sc

where cno='C2'and score is not null

order by score desc

select count(*)from s where dept=N'计算机'

select count(*),avg(score),max(score)

from sc where cno=

(select cno from c where cn=N'微机原理')

select cno as 课程号,count(sno) as 人数from sc group by cno

select sn,avg(score)as 平均成绩from s,sc

where s.sno=sc.sno group by sn,sc.sno having count(*)>2

7.2

select S.SNO,SN,CN,SCORE from S,SC,C

where s.sno=sc.sno and sc.cno=c.cno

select c.cno as 课程号,tn as 任课教师姓名,count(sc.sno) as 选课人数from c,tc,t,sc

where c.cno=tc.cno and t.tno=tc.tno and c.cno=sc.cno

group by c.cno,t.tn

select distinct t2.tn,t2.sal from t t1,t t2

where t1.tn=N'刘伟' and t1.sal<t2.sal or t2.tn=N'刘伟'

select distinct(sn),dept from c c1,c c2,sc sc1,sc sc2,s

where c1.cno=sc1.cno and c2.cno=sc2.cno and

c1.cn=N'程序设计' and c2.cn=N'微机原理' and

sc1.sno=sc2.sno and sc1.sno=s.sno

select s.sno,sn,cn,score from s

left outer join sc on s.sno=sc.sno

left outer join c on c.cno=sc.cno

7.3

select t1.tno,t1.tn,t1.prof from t t1,t t2

where t2.tn=N'刘伟' and t1.prof=t2.prof

select tn from t

where tno=any(select tno from tc where cno='C5')

select tn from t

where tno in (select tno from tc where cno='C5')

select tn,sal,dept from t

where sal>all(select sal from t where dept=N'计算机')

and (dept<>N'计算机')

select tn,dept from t

where not exists (select * from tc where tno=t.tno and cno='c5')

select distinct sno from sc sc1

where not exists (select * from sc sc2

where sc2.sno='s2' and not exists

(select * from sc sc3

where sc3.sno=sc1.sno and sc3.cno=sc2.cno))

select * from s where dept=N'计算机'

union select * from s where age<=19

7-1

select * from sc

where score between 80 and 90

7-2

select cname from student,sc,course

where student.sno=sc.sno and sc.cno=course.cno

group by cname

having count(course.cname)>=4

7-3

select sname,age from student

where age>all(select age from student where dept=N'信息')

and dept<>N'信息'

order by age desc

7-4

select s1.sno,s1.sname,s1.dept from student s1,student s2

where s2.sname=N'张建国' and s1.age=s2.age

7-5

select sname from course,sc,student

where course.cno=sc.cno and student.sno=sc.sno

group by(student.sname)

having count(student.sname)>2

7-6

select sname,cname,dept from student,sc,course

where student.sno=sc.sno and sc.cno=course.cno and

sc.cno=any(select sc.cno from student ,course ,sc

where student.sno=sc.sno and course.cno=sc.cno and sname=N'张建国' )

and sname<>N'张建国'

7-8

select sname,c1.cname,sc1.score,c2.cname,sc2.score

from sc sc1,sc sc2,student,course c1,course c2

where sc1.sno=sc2.sno and sc1.cno='01001'

and sc2.cno='01002'

and sc1.score>sc2.score

and student.sno=sc1.sno and c1.cno=sc1.cno and c2.cno=sc2.cno

7-9

select sname from student

where not exists (select sno from sc

where sno=student.sno and cno='01001')

7-10

SELECT Sname AS 姓名,AVG(Score) AS 平均,MAX(Score) AS 最高

FROM Student,SC

WHERE Student.Sno=SC.Sno

GROUP BY Sname

ORDER BY Sname DESC

7-11

select course.cno,course.cname from course where cno in

(select distinct cno from sc group by cno having count(*) =(select count(*) from student ))

select course.cno,course.cname from course join sc on (course.cno=sc.cno)

group by course.cno,course.came having count(sc.cno)=(select count(sno) from student)

7-12

select distinct Sname,student.sno from student,sc

where student.sno=sc.sno

and cno in

(select cno from sc

where sno='991102')

8-1

use jiaoxuedb

if object_id('pro_1','p')is not null

drop procedure pro_1

CREATE PROCEDURE pro_1(@sname nchar(20))

BEGIN

SELECT cname,score,tname from student,sc,teacher,tc,course

where student.sno=sc.sno and sc.cno=course.cno and

course.cno=tc.cno and teacher.tno=tc.tno and sname=@sname

END

exec pro_1 N'王蕾'

8-2

use jiaoxuedb

if object_id('pro_2','p')is not null

drop procedure pro_2

CREATE PROCEDURE pro_2(@dept nchar(10),@max_age int output,@min_age int output)

BEGIN

SELECT @max_age=max(age),@min_age=min(age) from student where dept=@dept

END

declare @max_age int,@min_age int

exec pro_2 N'计算机',@max_age output,@min_age output

select @max_age,@min_age

8-3

use jiaoxuedb

if object_id('pro_3','p')is not null

drop procedure pro_3

CREATE PROCEDURE pro_3(@sname nchar(10),@cname nchar(10),@score int output)

BEGIN

SELECT @score=score from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and sname=@sname and cname=@cname

END

declare @score int

exec pro_3 N'王蕾',N'计算机基础',@score output

select @score as 成绩

8-4

use jiaoxuedb

if object_id('pro_4','p')is not null

drop procedure pro_4

create procedure pro_4(@prof nchar(10),@count int output)

begin

select @count=count(*) from teacher where prof=@prof

end

declare @count int

exec pro_4 N'副教授',@count output

select @count as 人数

8-5

use jiaoxuedb

if object_id('pro_5','p')is not null

drop procedure pro_5

create procedure pro_5(@dept nchar(10),@prof nchar(10),

@count int output,@avg_age int output,@avg_sal int output,@max_sal int output)

begin

select @count=count(*),@avg_age=avg(age),@avg_sal=avg(sal),@max_sal=max(sal) from teacher

where dept=@dept and prof=@prof

end

declare @count int,@avg_age int,@avg_sal int,@max_sal int

exec pro_5 N'计算机',N'教授',@count output,@avg_age output,@avg_sal output,@max_sal output

select @count ,@avg_age ,@avg_sal,@max_sal

8-6

use jiaoxuedb

if object_id('pro_6','p')is not null

drop procedure pro_6

create procedure pro_6(@dept nchar(10),@count1 int output,@avg_age int output,@count int output)

begin

select @count1=count(*),@avg_age=avg(age)from teacher where dept=@dept

select @count=count(sno)from student where dept=@dept

end

declare @count1 int ,@avg_age int ,@count int

exec pro_6 N'计算机',@count1 output,@avg_age output,@count output

select @count1,@avg_age,@count

8-7

if object_id('pro_7','p')is not null

drop procedure pro_7

create procedure pro_7(@cname nchar(10))

begin

select sname,dept,score from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and cname=@cname

end

exec pro_7 N'程序设计'

8-8

use jiaoxuedb

if object_id('pro_8','p')is not null

drop procedure pro_8

create procedure pro_8(@tname nchar(10),@cname nchar(10))

Begin

select cname,chour,count(*)as 选课人数,avg(score)as 平均成绩,max(score)as 最高成绩from teacher,sc,tc,course where teacher.tno=tc.tno and sc.cno=course.cno and tc.cno=course.cno and cname=@cname and tname=@tname group by cname,tname,chour

end

exec pro_8 N'张雪',N'程序设计'

8-9

drop procedure pro_1

8-10

drop procedure pro_3 drop procedure pro_4

--8-1利用学生姓名查询该生选修的课程名，成绩，以及任课教师姓名

if object_id('pro_1','p')is not null

drop procedure pro_1

CREATE PROCEDURE pro_1(@sname char(20))

BEGIN

SELECT cname,score,tname from student,sc,teacher,tc,course

where student.sno=sc.sno and sc.cno=course.cno and

course.cno=tc.cno and teacher.tno=tc.tno and sname=@sname

END

DECLARE

@sname_in CHAR(10)

SELECT @sname_in='王蕾'

EXEC Pro_Sn @sname_in

--8-2查询某系的学生的最大年龄和最小年龄

if object_id('pro_2','p')is not null

drop procedure pro_2

CREATE PROCEDURE pro_2

@dept char(10),@max_age int output,@min_age int output

SELECT @max_age=max(age),@min_age=min(age) from student where dept=@dept

declare @max_age int,@min_age int

exec pro_2 '计算机',@max_age output,@min_age output

select @max_age,@min_age

--8-3利用学生姓名和课程名检索该生该课程的成绩

use jiaoxuedb

if object_id('pro_3','p')is not null

drop procedure pro_3

CREATE PROCEDURE pro_3

@sname char(10),@cname char(10),@score int output

BEGIN

SELECT @score=score from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and sname=@sname and cname=@cname

END

declare @score int

exec pro_3 '王蕾','计算机基础',@score output

select @score as 成绩

--8-4根据职称查询人数,并给出'副教授'的人数

if object_id('pro_4','p')is not null

drop procedure pro_4

create procedure pro_4(@prof char(10),@count int output)

begin

select @count=count(*) from teacher where prof=@prof

end

declare @count int

exec pro_4 '副教授',@count output

select @count as 人数

--8-5统计某系某职称的人数,平均年龄,平均工资,最高工资

use jiaoxuedb

if object_id('pro_5','p')is not null

drop procedure pro_5

create procedure pro_5

@dept char(10),@prof char(10),

@count int output,@avg_age int output,@avg_sal int output,@max_sal int output

begin

select @count=count(*),@avg_age=avg(age),@avg_sal=avg(sal),@max_sal=max(sal) from teacher

where dept=@dept and prof=@prof

end

declare @count int,@avg_age int,@avg_sal int,@max_sal int

exec pro_5 '计算机','教授',@count output,@avg_age output,@avg_sal output,@max_sal output

select @count ,@avg_age ,@avg_sal,@max_sal

--8-6查询某系的教师人数,平均年龄和学生人数

use jiaoxuedb

if object_id('pro_6','p')is not null

drop procedure pro_6

create procedure pro_6(@dept char(10),@count1 int output,@avg_age int output,@count int output)

begin

select @count1=count(*),@avg_age=avg(age)from teacher where dept=@dept

select @count=count(sno)from student where dept=@dept

end

declare @count1 int ,@avg_age int ,@count int

exec pro_6 '计算机',@count1 output,@avg_age output,@count output

select @count1 教师人数,@avg_age 平均年龄,@count 学生人数

--8-7利用课程名查询选修该课程的学生姓名,系别,成绩,并给出'课程设计'课程的该查询信息

if object_id('pro_7','p')is not null

drop procedure pro_7

create procedure pro_7(@cname char(10))

begin

select sname,dept,score from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and cname=@cname

end

exec pro_7 '程序设计'

--8-8利用教师姓名和课程名检索该教师该任课的课程名,课时数,选课人数,平均成绩,最高成绩,并查询教师'张雪'的'微机原理'课程的情况记录

if OBJECT_ID('pro_8','p')is not null

drop procedure pro_8

CREATE PROCEDURE Pro_8

@tname CHAR(10),@cname CHAR(10)

SELECT Tname,Cname,SUM(Chour) 课时数,COUNT(Sno) 选课人数,AVG(Score) 平均成绩

FROM Teacher,Course,SC,TC

WHERE Tname=@tname

AND Cname=@cname

AND TC.Tno=Teacher.Tno

AND TC.Cno=Course.Cno

AND SC.Cno=Course.Cno

GROUP BY Tname,Cname

DECLARE

@tname CHAR(10),

@cname CHAR(10)

SELECT @tname='张雪'

SELECT @cname='微机原理'

EXEC Pro_8 @tname,@cname

--8-9

drop procedure pro_1

--8-10

drop procedure pro_3

drop procedure pro_4

--1、为表SC创建一触发器：当插入或修改一个记录时，确保此记录的成绩在~100之间。

use jiaoxuedb

if object_ID ('trigger_sc','tr')is not null

drop trigger trigger_sc --判断触发器的名字是否存在，若存在，则删除

create trigger trigger_sc

on sc for insert,update

as --as后边跟条件语句（as，if）

declare @score_new int

select @score_new=score from inserted

if @score_new>=0 and @score_new <=100

begin --给出解决方案

print '操作完成！'

return

end

print '成绩超出~100,请重新输入'

rollback transaction

insert into sc values ('2013508','00000',130)

insert into sc values ('2013509','00000',99)

--2、为教师表T创建一触发器：男职工年龄不能超过周岁，女职工职称是“教授”的年龄

--不能超过岁，其他女职工年龄不能超过岁。

if object_ID ('trigger_2','tr')is not null

drop trigger trigger_2

create trigger trigger_2

on teacher for insert,update

declare @m_age tinyint,@f_age_js tinyint,@f_age tinyint

select @m_age=age from inserted where sex='男'

select @f_age_js=age from inserted where sex='女' and prof='教授'

select @f_age=age from inserted where sex='女' and prof<>'教授'

if @m_age>60 or @f_age_js>60 or @f_age>55

begin

rollback transaction

end

insert into teacher(tno,tname,sex,age,dept) values ('2013508','zhangsan','女','72','讲师')

insert into teacher(tno,tname,sex,age,dept) values ('2013507','张兰','女','59','教授')

--3、为表C、表TC和表SC创建参照完整性：级联删除和级联修改触发器。

if object_ID ('trigger_3_sc','tr')is not null

drop trigger trigger_3_sc--级联删除

create trigger trigger_3_sc

on course for delete

declare @cno_old int

select @cno_old=cno from deleted

delete from sc where cno=@cno_old

delete from tc where cno=@cno_old

if object_ID ('trigger_3_xg','tr')is not null

drop trigger trigger_3_xg--级联修改

create trigger trigger_3_xg

on course for update

declare @cno_new int,@cno_old int

select @cno_new=cno from inserted

select @cno_old=cno from deleted

update sc set cno=@cno_new where cno=@cno_old

update tc set cno=@cno_new where cno=@cno_old

--T

if object_ID ('trigger_3','tr')is not null

drop trigger trigger_3

create trigger trigger_3

on course for delete,update

declare @cno_old int, @cno_new int

select @cno_new=cno from inserted

select @cno_old=cno from deleted

if @cno_new is null

begin

delete from sc where cno=@cno_old

delete from tc where cno=@cno_old

end

else

begin

update sc set cno=@cno_new where cno=@cno_old

update tc set cno=@cno_new where cno=@cno_old

end

/*4、为表T创建一个触发器：当职称从“讲师”晋升为“副教授”时，岗位津贴自动增加元；

从“副教授”晋升为“教授”时，岗位津贴自动增加元。*/

if object_ID ('trigger_4','tr')is not null

drop trigger trigger_4

create trigger trigger_4

on teacher for update

declare @prof_new char(10),@prof_old char(10),@tno int

select @prof_new from inserted

select @prof_old from deleted

if @prof_old='讲师' AND @prof_new='副教授'

begin

update teacher set comm=comm+500 where tno=@tno

end

if @prof_old='副教授' AND @prof_new='教授'

begin

update teacher set comm=comm+900 where tno=@tno

end

/*5、为表SC创建一触发器，将成绩按下列对应关系由分数转换成等级

小于：不及格

60~70：及格

70~80：中

80~90：良

90~100：优*/

CREATE TRIGGER USC

ON SC FOR UPDATE,INSERT

DECLARE @sno CHAR(2)

DECLARE @score TINYINT

SELECT @sno=SNO FROM SC

SELECT @score=SCORE FROM SC

IF @score < 60

BEGIN

UPDATE SC SET SCORE='不及格' WHERE SNO=@sno

END

IF @score > 60 AND @score < 70

BEGIN

UPDATE SC SET SCORE='及格' WHERE SNO=@sno

END

IF @score > 70 AND @score < 80

BEGIN

UPDATE SC SET SCORE='中' WHERE SNO=@sno

END

IF @score > 80 AND @score < 90

BEGIN

UPDATE SC SET SCORE='良' WHERE SNO=@sno

END

IF @score > 90 AND @score < 100

BEGIN

UPDATE SC SET SCORE='优' WHERE SNO=@sno

END

--T

if @score<60

print '不及格'

--3创建标量函数，根据学生姓名和课程名查询该生该课程的成绩

create function func_3

(@Sname_in char(10),@Cname_in char(10))

returns int

begin

declare @Score_out int

select @Score_out=Score

from Student,SC,Course

where Student.Sno=SC.Sno and Course.Cno=SC.Cno and Sname=@Sname_in and Cname=@Cname_in

return(@Score_out)

end

declare @Score_in int

exec @Score_in=func_3 '张彬','计算机基础'

print'张彬计算机基础分数'+str(@Score_in)

--4创建内联表值函数,根据教师姓名查询该教师所教课程名,学生人数,平均成绩,最高成绩,最低成绩

create function func_4

(@Tname_in char(10))

returns table

return(

select Cname,count(*) 学生人数,avg(Score) 平均成绩,max(Score) 最高成绩,min(Score) 最低成绩

from Teacher,TC,Course,SC

where Tname=@Tname_in and Teacher.Tno=TC.Tno and Course.Cno=TC.Cno and SC.Cno=TC.Cno

group by Cname)

select * from func_4('李英')

--5多语句表值函数,统计各系各职称的总人数,平均年龄

create function func_6(@Dept_in char(10))

returns

@Dept_Prof table(Dept char(10) not null,Prof char(10) not null,人数int,平均年龄int)

begin

insert @Dept_Prof

select Dept,Prof,count(Tno),avg(age)

from Teacher

where Dept=@Dept_in

group by Dept,Prof

return

end

select * from func_5('计算机')

小飞侠xi

关注

5
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录