Oracle sql语句

查询成绩在80~90之间的记录。
SELECT Student.Sno,Sname,Dept,Course.Cno,Score
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno 
    AND SC.Score BETWEEN 80 AND 90
    AND SC.Cno=Course.Cno

查询至少有4个同学选修的课程名。
USE jiaoxuedb
SELECT Cno AS 课程号,Cname AS 课程名
FROM Course
WHERE Cno=ANY 
    (SELECT
        Cno AS 课程号
    FROM SC
    GROUP BY Cno
    HAVING COUNT(*)>=4)
GO

3、    查询其他系中比“信息系”所有学生年龄都打的学生名单及年龄，并按年龄降序输出。
USE jiaoxuedb
SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业
FROM Student
WHERE Age>ALL 
    (SELECT Age
    FROM Student
    WHERE Dept='信息')
    AND Dept <> '信息'
ORDER BY Age DESC
GO

4、    查询与学生张建国同岁的所有学生的学号、姓名和系别。
/*方法一*/
USE jiaoxuedb
SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业
FROM Student
WHERE Age=ANY 
    (SELECT Age
    FROM Student
    WHERE Sname='张建国')
    AND Sname <> '张建国'
ORDER BY Age DESC
GO
/*方法二*/
USE jiaoxuedb
SELECT s2.Sno,s2.Sname,s2.Dept
FROM Student s1, Student s2
WHERE s1.age=s2.age AND s1.Sname='张建国' AND s2.Sname <> '张建国'

5、    查询选修了2门以上课程的学生名单。
USE jiaoxuedb
SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业
FROM Student
WHERE Sno=ANY 
    (SELECT Sno
    FROM SC
    GROUP BY Sno
    HAVING COUNT(*) >=2)
GO

6、    查询至少有一门与“张建国”选课相同的学生的姓名、课程名和系别。
USE jiaoxuedb
SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业
FROM Student
WHERE Sno=ANY 
    (SELECT Sno
    FROM SC
    WHERE Cno=ANY
        (SELECT Cno
        FROM SC
        WHERE Sno='991103'))
    AND Sname <> '张建国'
GO

7、    查询成绩比该课程平均成绩高的学生的成绩表。
/*方法一*/
USE jiaoxuedb
SELECT Sno AS 学号,Sname AS 姓名,Age AS 年龄,Dept AS 专业
FROM Student
WHERE Sno=ANY 
    (SELECT Sno
    FROM SC x
    WHERE Score >= 
        (SELECT AVG(Score)
        FROM SC y
        WHERE y.Sno=x.Sno));
GO
/*方法二*/
SELECT *
FROM SC sc1
WHERE Score >
    (SELECT AVG(Score)
    FROM SC sc2)
    AND sc1.Cno=sc2.Cno;
GO

8、    查询选修课号为01001课程且成绩高于课程01002学生的姓名、此两门课程的课程名和成绩。
/*方法一*/
USE jiaoxuedb
SELECT Sname,Cname,Score
FROM Student ,SC,Course
WHERE Student.Sno=
    (SELECT s1.Sno
    FROM SC s1,SC s2
    WHERE s1.Cno='01001' AND s2.Cno='01002' AND s1.Score>s2.Score AND s1.Sno=s2.Sno)
    AND SC.Sno=Student.Sno
    AND Course.Cno=SC.Cno
GO
/*方法二*/
USE jiaoxuedb
SELECT Sname,Cname,Score
FROM Student ,SC,Course
WHERE Student.Sno=
    (SELECT Sno
    FROM SC x
    WHERE Score >= 
        (SELECT AVG(Score)
        FROM SC y
        WHERE y.Sno=x.Sno
        AND x.Cno='01001'
        AND y.Cno='01002')
    AND SC.Sno=Student.Sno
    AND Course.Cno=SC.Cno)
GO

9、查询所有未修01001号课程的学生名单。
USE jiaoxuedb
SELECT Student.Sno,Sname
FROM Student
WHERE Student.Sno IN
    (SELECT s1.Sno
    FROM SC s1
    WHERE Sno NOT IN
        (SELECT s2.Sno
        FROM SC s2
        WHERE Cno='01001'))
GO

10、查询每个同学各门课程的平均成绩和最高成绩，按降序排列输出姓名、平均成绩、最高成绩。
USE jiaoxuedb
SELECT Sname AS 姓名,AVG(Score) AS 平均,MAX(Score) AS 最高
FROM Student,SC
WHERE Student.Sno=SC.Sno
GROUP BY Sname
ORDER BY Sname DESC
GO

11、查询所有学生都选修了的课程号和课程名。
USE jiaoxuedb
SELECT Cname AS 课程号,Cno AS 课程名
FROM Course
WHERE NOT EXISTS
    (SELECT *
    FROM Student
    WHERE  NOT EXISTS
        (SELECT * FROM SC
        WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno));
GO

12、查询选修了991102号学生选修了的课程的学生学号和姓名。
use jiaoxuedb
select distinct sno from sc s1 
where not exists
(select * from sc s2 
where sno='991101' 
and  not exists(
   select * from sc s3 where s1.sno=s3.sno and
s2.cno=s3.cno))
GO
 

--2查询某系的学生的最大年龄和最小年龄
select sname,age
from student
where dept='计算机' and 
age=(select max(age) 
     from student
     where dept='计算机')
or age=(select min(age) 
     from student
     where dept='计算机')

 

--4根据职称查询人数,并给出'副教授'的人数
select prof,count(tno) from teacher group by prof having prof='副教授'

--5统计某系某职称的人数,平均年龄,平均工资,最高工资
select dept,prof,count(tno),avg(age),avg(sal),max(sal) from teacher group by dept,prof having prof='副教授' and dept='信息'

USE jxsk
SELECT DEPT AS 系别,SN AS 姓名,SEX AS 性别,AGE AS 年龄
FROM S
WHERE SN LIKE '王%'
ORDER BY DEPT,SN,SEX,AGE
COMPUTE COUNT(SN) BY DEPT
COMPUTE COUNT(SN)
GO

USE jxsk
SELECT s1.SN AS 姓名,s1.DEPT AS 系别
FROM S s1,S s2
WHERE s1.SN=s2.SN AND s1.SNO <> s2.SNO
ORDER BY s1.DEPT,s1.SN
COMPUTE COUNT(s1.SN) BY s1.DEPT
COMPUTE COUNT(s1.SN)
GO

USE jxsk
SELECT PROF,TN AS 姓名,SEX,AGE,SAL+COMM AS 工资
FROM T
ORDER BY PROF 
COMPUTE MAX(SAL+COMM) BY PROF
COMPUTE MIN(SAL+COMM) BY PROF
COMPUTE AVG(SAL+COMM) BY PROF
GO

USE jxsk
SELECT  CN AS 课程名,COUNT(*) AS '60以下人数'
FROM C,SC
WHERE SC.CNO = C.CNO AND SCORE < 60
GROUP BY CN

SELECT  CN AS 课程名,COUNT(*) AS '60~70人数'
FROM C,SC
WHERE SC.CNO = C.CNO AND SCORE > 60 AND SCORE < 70
GROUP BY CN

SELECT  CN AS 课程名,COUNT(*) AS '70~80人数'
FROM C,SC
WHERE SC.CNO = C.CNO AND SCORE > 70 AND SCORE < 80
GROUP BY CN

SELECT  CN AS 课程名,COUNT(*) AS '80~90人数'
FROM C,SC
WHERE SC.CNO = C.CNO AND SCORE > 80 AND SCORE < 90
GROUP BY CN

SELECT  CN AS 课程名,COUNT(*) AS '90~100人数'
FROM C,SC
WHERE SC.CNO = C.CNO AND SCORE > 90 AND SCORE < 100
GROUP BY CN
GO

 

create table news(name nameid)

exec sp_addtype idnum,'char(10)','not null'

create table new(tno idnum)

--标量函数，根据学生姓名和课程名查询成绩
if object_id('fun_1','fn') is not null
drop function fun_1
go

CREATE FUNCTION fun_1
    (@sname char(20), @cname char(30))
RETURNS int
AS
BEGIN

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

    RETURN @score

END
GO

declare @x int
exec @x=fun_1 '张彬','计算机基础' 
print '张彬'+'计算机基础'+str(@x)

--内置表值函数，根据学生姓名查询该生所有选课的成绩
if object_id('fun_2','if') is not null
drop function fun_2
go

CREATE FUNCTION fun_2
(    
    @sname char(20)
)
RETURNS TABLE 
AS
RETURN 
(
SELECT cname,score from student,sc,course
    where student.sno=sc.cno and course.cno=sc.cno and sname=@sname

)
GO

select * from fun_2 ('张彬')

--多语句表值函数
if object_id('fun_3','tf') is not null
drop function fun_3
go

CREATE FUNCTION fun_3
(
    @cname char(30)
)
RETURNS 
@new TABLE 
(
    sno char(10),sname char(10),sex char(2),score int
)
AS
BEGIN
    
    insert into @new select sc.sno,sname,sex,score
from student,sc,course
    where student.sno=sc.cno and course.cno=sc.cno and cname=@cname
    RETURN 
END
GO

select * from fun_3('程序设计')

--将‘陈东辉’的‘计算机基础’课程成绩改为77分
begin transaction
update sc set score=77 where sno=
(select sno from student where sname='陈东辉')
and cno=(select cname from course where cname='计算机基础')
commit

--将课程‘数据结构’的课号与‘微机原理’的课号互换
begin transaction
declare @cno1 char(10),@cno2 char(10)

--教师'许永军'退休,由他讲授的2门课程中,课程'微机原理'转给教师'张朋'讲授,'数据库'转给'李英'讲授

--学生'王一山'
begin transaction
use jiaoxuedb
declare @n int,@sno char(10),@cno char(10)
select @sno=sno from student where sname='王一山'
select @cno=cno from course where cname='计算机网络'
select @n=count(*) from sc where cno=@cno
if @n<30
  begin
    insert into sc(sno,cno) values(@sno,@cno)
    commit
    print'OK'
  end
else
  begin 
     rollback transaction
     print 'no'
  end

select * from sc

--
begin transaction
use jiaoxuedb
declare @n int,@cno char(20)
select @cno=cno from course where cname='数据结构'
select @n=count(*) from tc where cno=@cno

 

删除表

use jiaoxuedbx
go
drop table TC
go