1.use SCT
select distinct S1.Tname as name1,S2.Tname as name2,(S1.salary-S2.salary) as gzce
from Teacher S1,Teacher S2
where S1.Salary>S2.Salary
2.SELECt distinct Sname,S1.Score,S2.Score,(S1.Score-S2.Score) AS CJC
from Student,SC AS S1,SC AS S2
WHERe Student.S#=S1.S# AND S1.S#=S2.S# AND
S1.C#=‘001’ AND S2.C#=‘002’ AND S1.Score>S2.Score
3.SELECt S1.S# AS XH1,S2.S# AS XH2,(S1.Score-S2.Score) AS CJC
FROM SC AS S1,SC AS S2
WHERe S1.C#=‘001’ AND S2.C#=‘001’
AND S1.Score>S2.Score
4.select Sname,Student.S#
from Student,SC
where Student.S#=SC.S# AND C#=‘001’
5.select S#,AVG(Score) AS AVG
from SC where S# IN
(select S# from SC where Score<60
group by S# having COUNT(*)>= 2)
group by S#
6.select S#
from SC
where Score >= all(select Score from SC where C#=‘001’)
7.select C# from SC,Student
where SC.S#=Student.S# and Sname='张三’and Score <=all
(select Score from SC,Student where SC.S#=Student.S# AND Sname=‘张三’)
8.select Student.Sname from Student
where NOT exists (select * from SC,Teacher,Course
where Student.S#=SC.S# AND SC.C#=Course.C#
AND Course.T#=Teacher.T# and Teacher.Tname=(‘赵三’) )
9.USE SCT SELECt DISTINCT S1.S# FROM SC AS S1
WHERe S#!=‘98030101’ AND NOT EXISTS
(SELECt * FROM SC AS S2 WHERe S#=‘98030101’ AND NOT EXISTS
(SELECt * FROM SC AS S3 WHERe S3.S#=S1.S# AND S3.C#=S2.C#))
10.INSERT INTO SCt(S#,C#,Score)
SELECt S#,C#,Score FROM SC WHERe Score<60
11.DELETe FROM SCt WHERe S# IN
(SELECt S# FROM SCt
GROUP BY S# HAVINg COUNT(*)>=2)
12.UPDATe SC
SET Score=(SELECT AVG(Score) FROM SC WHERe C#=‘001’)
WHERe C#=‘001’ AND S# IN (SELECt Student.S# FROM Student,SC
WHERe Student.S#=SC.S# AND Sname=‘张三’)
