Oracle SQL查询练习(亲测可用)

)
--SC(S#,C#,score) 成绩表
DROP TABLE Student.SC
CREATE TABLE Student.SC
(
S# VARCHAR2(4) REFERENCES Student(S#),
C# VARCHAR2(3) REFERENCES Course(C#),
FROM Student a
WHERE 60>ALL
(
SELECT b.score
FROM SC b
WHERE a.S#=b.S#
)
SELECT S#,SNAME
FROM STUDENT
WHERE S# NOT IN (SELECT STUDENT.S#
FROM STUDENT,SC
)
--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SELECT a.S#,c.Sname
FROM (SELECT S#,score FROM SC WHERE c#='001') a,(SELECT S#,score FROM SC WHERE c#='002') b,Student c
INSERT INTO SC VALUES('1002','003',46);
INSERT INTO SC VALUES('1002','004',57);
INSERT INTO SC VALUES('1003','001',78);
INSERT INTO SC VALUES('1003','002',79);
FROM SC
GROUP BY S#
HAVING AVG(score)>60
--3、查询所有同学的学号、姓名、选课数、总成绩；
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student
left Outer join SC on Student.S#=SC.S#
SELECT S#,Sname
FROM Student
WHERE S# NOT IN
(
SELECT c.S#
FROM Teacher a
LEFT OUTER JOIN Course b ON a.T#=b.T#
LEFT OUTER JOIN SC c ON b.C#=c.C#
group by Student.S#,Sname
--上下性能比较 0.031<0.047
SELECT b.S#,b.Sname,NVL(a.a1,0),NVL(a.a2,0)
FROM
(
SELECT S#,COUNT(1) AS a1,SUM(score) AS a2
FROM SC
--最好放在Oracle的界面化工具PL/SQL Developer里面查看比较好看和测试
--匿名运行块（若表存在则删除该表的）
DECLARE
numcount NUMBER;
BEGIN
SELECT COUNT(1) INTO numcount FROM user_tables WHERE table_name='STUDENT';
WHERE t.Tname='叶平'
GROUP BY a.S#,a.Sname
HAVING COUNT(1)=
(
SELECT COUNT(1)
FROM Teacher e
INNER JOIN Course f ON e.T#=f.T#
WHERE e.tname='叶平'
WHERE a.S#=b.S# AND a.S#=c.S# AND a.score>b.score
SELECT S#, SNAME
FROM (SELECT STUDENT.S#,
STUDENT.SNAME,
SCORE,
(SELECT SCORE
--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SELECT a.S#,a.Sname
FROM Student a
INNER JOIN SC b ON a.S#=b.S#
INNER JOIN Course c ON b.C#=c.C#
INNER JOIN Teacher d ON c.T#=d.T#
)
--Course(C#,Cname,T#) 课程表
CREATE TABLE Student.Course
(
C# VARCHAR2(3) PRIMARY KEY,
Cname VARCHAR2(20),
T# VARCHAR2(4) REFERENCES Teacher(T#)
INSERT INTO SC VALUES('1003','003',84);
INSERT INTO SC VALUES('1003','004',46);
INSERT INTO SC VALUES('1004','001',57);
INSERT INTO SC VALUES('1004','002',56);
FROM SC SC_2
WHERE SC_2.S# = STUDENT.S#
AND SC_2.C# = '002'
) SCORE2
FROM STUDENT, SC
INSERT INTO Student VALUES('1004','赵六',18,'女');
INSERT INTO Student VALUES('1005','刘七',18,'女');
INSERT INTO Teacher VALUES('2001','叶平');
INSERT INTO Teacher VALUES('2002','赵敏');
） a
LEFT OUTER JOIN Student b ON a.S#=b.S#
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# and SC.C#='001'
and exists( Select * from SC SC_2 WHERE SC_2.C#='002');
INSERT INTO SC VALUES('1001','003',80.5);
INSERT INTO SC VALUES('1001','004',80.5);
INSERT INTO SC VALUES('1002','001',74);
INSERT INTO SC VALUES('1002','002',86);
INSERT INTO Course VALUES('003','英语','2003');
INSERT INTO Course VALUES('004','马克思','2004');
INSERT INTO SC VALUES('1001','001',76.5);
INSERT INTO SC VALUES('1001','002',78.5);
Sname VARCHAR2(20),
Sage NUMBER,
Ssex VARCHAR2(4)
)
--Teacher(T#,Tname) 教师表
CREATE TABLE Student.Teacher
(
T# VARCHAR2(4) PRIMARY KEY,
Tname VARCHAR2(20)
SELECT * FROM SC
--1、查询“001”课程比“002”课程成绩高的所有学生的学号；
SELECT a.S#
FROM (SELECT S#,score FROM SC WHERE c#='001') a,(SELECT S#,score FROM SC WHERE c#='002') b
WHERE STUDENT.S# = SC.S#
AND C# = '001'
) S_2
WHERE SCORE2 < SCORE;
--9、查询所有课程成绩小于60分的同学的学号、姓名；
SELECT DISTINCT a.S#,a.Sname
WHERE a.S#=b.S# AND a.score>b.score
--上下性能比较 0.016<0.031
SELECT a.S#
FROM
(
SELECT S#
,SUM(CASE WHEN C#='001' THEN score ELSE 0 END) AS'2003','周天');