来来来 给你
1.SELECT a.S#
FROM (SELECT s#, score FROM SC WHERE C# = '001') a,
(SELECT s#, score FROM SC WHERE C# = '002') b
WHERE a.score > b.score
AND a.s# = b.s#;
2.SELECT S#, avg(score) 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#
GROUP BY Student.S#, Sname
4.SELECT count(distinct(Tname)) FROM Teacher WHERE Tname like '李%';5.SELECT Student.S#, Student.Sname
FROM Student
WHERE S# not in (SELECT distinct (SC.S#)
FROM SC, Course, Teacher
WHERE SC.C# = Course.C#
AND Teacher.T# = Course.T#
AND Teacher.Tname = '叶平');
6.SELECT Student.S#, Student.Sname
FROM Student, SC
WHERE Student.S# = SC.S#
AND SC.C# = '001'
and exists (SELECT *
FROM SC as SC_2
WHERE SC_2.S# = SC.S#
AND SC_2.C# = '002');
7.SELECT S#, Sname
FROM Student
WHERE S# in (SELECT S#
FROM SC, Course, Teacher
WHERE SC.C# = Course.C#
AND Teacher.T# = Course.T#
AND Teacher.Tname = '叶平'
GROUP BY S#
having count(SC.C#) = (SELECT count(C#)
FROM Course, Teacher
WHERE Teacher.T# = Course.T#
AND Tname = '叶平'));
8.SELECT S#, Sname
FROM Student
WHERE S# not in (SELECT Student.S#
FROM Student, SC
WHERE S.S# = SC.S#
AND score > 60);
9.SELECT Student.S#, Student.Sname
FROM Student, SC
WHERE Student.S# = SC.S#
GROUP BY Student.S#, Student.Sname
having count(C#) < (SELECT count(C#) FROM Course);10.SELECT S#, Sname
FROM Student, SC
WHERE Student.S# = SC.S#
AND C# in SELECT C# FROM SC WHERE S# = '1001';
11.Delete FROM course, Teacher
WHERE Course.C# = SC.C#
AND Course.T# = Teacher.T#
AND Tname = '叶平';
12.SELECT L.C# As 课程ID, L.score AS 最高分, R.score AS 最低分
FROM SC L, SC AS R
WHERE L.C# = R.C#
AND L.score = (SELECT MAX(IL.score)
FROM SC AS IL, Student AS IM
WHERE L.C# = IL.C#
AND IM.S# = IL.S#
GROUP BY IL.C#)
AND R.Score = (SELECT MIN(IR.score)
FROM SC AS IR
WHERE R.C# = IR.C#
GROUP BY IR.C#);13.SELECT 1 + (SELECT COUNT(distinct 平均成绩)
FROM (SELECT S#, AVG(score) AS 平均成绩 FROM SC GROUP BY S#) AS T1
WHERE 平均成绩 > T2.平均成绩) as 名次,
S# as 学生学号,
平均成绩
FROM (SELECT S#, AVG(score) 平均成绩 FROM SC GROUP BY S#) AS T2
ORDER BY 平均成绩 desc;14.SELECT t1.S# as 学生ID, t1.C# as 课程ID, Score as 分数
FROM SC t1
WHERE score IN
(SELECT TOP 3 score FROM SC WHERE t1.C# = C# ORDER BY score DESC)
ORDER BY t1.C#;
1.SELECT a.S#
FROM (SELECT s#, score FROM SC WHERE C# = '001') a,
(SELECT s#, score FROM SC WHERE C# = '002') b
WHERE a.score > b.score
AND a.s# = b.s#;
2.SELECT S#, avg(score) 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#
GROUP BY Student.S#, Sname
4.SELECT count(distinct(Tname)) FROM Teacher WHERE Tname like '李%';5.SELECT Student.S#, Student.Sname
FROM Student
WHERE S# not in (SELECT distinct (SC.S#)
FROM SC, Course, Teacher
WHERE SC.C# = Course.C#
AND Teacher.T# = Course.T#
AND Teacher.Tname = '叶平');
6.SELECT Student.S#, Student.Sname
FROM Student, SC
WHERE Student.S# = SC.S#
AND SC.C# = '001'
and exists (SELECT *
FROM SC as SC_2
WHERE SC_2.S# = SC.S#
AND SC_2.C# = '002');
7.SELECT S#, Sname
FROM Student
WHERE S# in (SELECT S#
FROM SC, Course, Teacher
WHERE SC.C# = Course.C#
AND Teacher.T# = Course.T#
AND Teacher.Tname = '叶平'
GROUP BY S#
having count(SC.C#) = (SELECT count(C#)
FROM Course, Teacher
WHERE Teacher.T# = Course.T#
AND Tname = '叶平'));
8.SELECT S#, Sname
FROM Student
WHERE S# not in (SELECT Student.S#
FROM Student, SC
WHERE S.S# = SC.S#
AND score > 60);
9.SELECT Student.S#, Student.Sname
FROM Student, SC
WHERE Student.S# = SC.S#
GROUP BY Student.S#, Student.Sname
having count(C#) < (SELECT count(C#) FROM Course);10.SELECT S#, Sname
FROM Student, SC
WHERE Student.S# = SC.S#
AND C# in SELECT C# FROM SC WHERE S# = '1001';
11.Delete FROM course, Teacher
WHERE Course.C# = SC.C#
AND Course.T# = Teacher.T#
AND Tname = '叶平';
12.SELECT L.C# As 课程ID, L.score AS 最高分, R.score AS 最低分
FROM SC L, SC AS R
WHERE L.C# = R.C#
AND L.score = (SELECT MAX(IL.score)
FROM SC AS IL, Student AS IM
WHERE L.C# = IL.C#
AND IM.S# = IL.S#
GROUP BY IL.C#)
AND R.Score = (SELECT MIN(IR.score)
FROM SC AS IR
WHERE R.C# = IR.C#
GROUP BY IR.C#);13.SELECT 1 + (SELECT COUNT(distinct 平均成绩)
FROM (SELECT S#, AVG(score) AS 平均成绩 FROM SC GROUP BY S#) AS T1
WHERE 平均成绩 > T2.平均成绩) as 名次,
S# as 学生学号,
平均成绩
FROM (SELECT S#, AVG(score) 平均成绩 FROM SC GROUP BY S#) AS T2
ORDER BY 平均成绩 desc;14.SELECT t1.S# as 学生ID, t1.C# as 课程ID, Score as 分数
FROM SC t1
WHERE score IN
(SELECT TOP 3 score FROM SC WHERE t1.C# = C# ORDER BY score DESC)
ORDER BY t1.C#;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货