第1个回答 推荐于2016-08-20
1、查询“001”课程比“002”课程成绩高的所有学生的学号；
SELECT t1.SID
FROM (SELECT Sid, Score FROM SC WHERE cid = '001') t1
(SELECT Sid, Score FROM SC WHERE cid = '002') t2
WHERE t1.sid = t2.sid AND score > t2.score
2、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT sid, AVG(score) FROM SC GROUP BY sid HAVING AVG(SCORE) > 60
3、查询所有同学的学号、姓名、选课数、总成绩；
SELECT stu.SID, stu.SNAME, COUNT(SC.cid), SUM(SC.score)
FROM Student stu LEFT JOIN SC ON stu.SID = SC.SID
GROUP BY stu.SID, stu.SNAME
4、查询姓“李”的老师的个数；
SELECT COUNT(*) FROM Teacher WHERE Tname LIKE '李%'
5、查询没学过“叶平”老师课的同学的学号、姓名；
SELECT stu.sid, stu.name
FROM SC, student stu
WHERE SC.CID NOT IN (SELECT C.CID FROM COURSE c, Teacher t WHERE c.Tid = t.ti)
AND sc.sid = stu.sid
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
SELECT stu.SID, stu.name
FROM SC t, student stu
WHERE cid ='001'
AND EXITS (SELECT 1 FROM SC WHERE cid = '002' AND sid = t.sid)
and t.sid = stu.sid
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SELECT SC.SID, stu.name
FROM SC T, Student
WHERE CID = '002'
AND EXISTS (SELECT 1 FROM SC WHERE CID = '001' AND T.score > score)
AND student.sid = sc.sid
9、查询所有课程成绩小于60分的同学的学号、姓名；
SELECT Sid, Sname
FROM student WHERE SID NOT IN (SELECT SID FROM SC WHERE score > 60)
10、查询没有学全所有课的同学的学号、姓名；
SELECT SID, SNAME
FROM Student
WHERE SID IN (SELECT SID
FROM SC GROUP BY SC HAVING COUNT(*) < (SELECT COUNT(Canme) FROM Course))
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
SELECT Student.SID, Student.Sname
FROM SC, Student
WHERE SC.sid = Student.Sid
AND SC.CID IN (SELECT CID FROM SC WHERE SID = '1001')
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；
SELECT Sid, Sname
FROM student WHERE SID IN
(SELECT SID
FROM SC
WHERE CID IN (SELECT CID FROM SC WHERE SID = '001')
AND SID <> '001'
HAVING COUNT(CID) >= (SELECT COUNT(CID) FROM SC WHERE SID = '001')
GROUP BY SID)
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
UPDATE TABLE SC t SET score = (SELECT AVG(SCORE) FROM SC WHERE Cid = t.Cid)
WHERE CID IN (SELECT CID FROM Course c, Teacher t WHERE t.ti = c.Tid AND Tname = '叶平')
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
select sname, sid
FROM student
WHERE sid IN(SELECT SID FROM SC
WHERE SID <> '1002'
AND CID IN (SELECT CID FROM SC WHERE SID ='1002'
GROUP BY SID
HAVING COUNT(*) = (SELECT COUNT(*) FROM SC WHERE SID = '1002') )
15、删除学习“叶平”老师课的SC表记录；
DELETE FROM SC WHERE Cid IN (SELECT cid FROM Course c, Teacher t WHERE c.Tid = t.Ti AND t.Tname = '叶平')
16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、
号课的平均成绩；
INSERT INTO SC(SID, Cid, score)
SELECT t1.sid, t2.average
FROM (SELECT SID FROM SC WHERE CID <> '003') t1,
(SELECT AVG(SCORE) AS average FROM SC WHERE CID = 2) t2
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，
按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
不明
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT CID, MAX(SCORE), MIN(SCORE)
FROM SC
GROUP BY CID本回答被提问者和网友采纳
第2个回答 2010-12-14
create database school
go
use school
go
create table Student --学生表
(
Sid int identity(1001,1) primary key,
Sname varchar(20),
Sage int,
Ssex varchar(2)
)
go
create table Teacher--教师表
(
Tid int identity primary key,
Tname varchar(20)
)
go
create table Course--课程表
(
Cid int primary key,
Cname varchar(20),
Tid int foreign key references Teacher(Tid)
)
go
create table SC-- 成绩表
(
SCid int identity primary key,
Sid int foreign key references student(sid),
Cid int foreign key references Course(Cid),
score float
)
go
--------------------------学生表 测试数据----------------------------
go
insert into Student values('maple',21,'男')
insert into Student values('lili',22,'女')
insert into Student values('lucy',20,'女')
insert into Student values('driver',23,'男')
insert into Student values('map',19,'女')
go
select * from Student
go
--------------------------教师表 测试数据----------------------------
insert into Teacher values('叶平')
insert into Teacher values('李邱')
insert into Teacher values('刘诗')
go
select * from Teacher
go
--------------------------课程表 测试数据----------------------------
insert into Course values(001,'数据库',1)
insert into Course values(002,'英语',2)
insert into Course values(003,'企业管理',3)
go
select * from Course
--------------------------成绩表 测试数据----------------------------
insert into SC values(1001,1,75)
insert into SC values(1001,2,69)
insert into SC values(1001,3,56)
insert into SC values(1002,1,74)
insert into SC values(1002,2,62)
insert into SC values(1002,3,81)
insert into SC values(1003,1,73)
insert into SC values(1003,2,67)
insert into SC values(1003,3,59)
insert into SC values(1004,1,70)
insert into SC values(1004,2,65)
insert into SC values(1004,3,62)
go
select * from sc
go
--1、查询“001”课程比“002”课程成绩高的所有学生的学号；
select t1.sid as '学号' from (select * from sc where cid = 1) as t1 join (select * from sc where cid = 2)as t2 on t1.sid=t2.sid where t1.score>t2.score
go
--2、查询平均成绩大于60分的同学的学号和平均成绩；
select sid as '学号',convert(decimal(4,2),avg(score)) as '平均成绩' from sc group by sid having avg(score)>60
go
--3、查询所有同学的学号、姓名、选课数、总成绩；
select t1.sid as '学号',sname as '姓名',cid as '选课数',score as '总成绩' from (select sid,sname from Student)as t1 join (select sid,count(cid) as cid,sum(score) as score from sc group by sid)as t2 on t1.sid=t2.sid
go
--4、查询姓“李”的老师的个数；
select count(*) as '姓“李”的老师的个数' from Teacher where tname like '李%'
go
--5、查询没学过“叶平”老师课的同学的学号、姓名；
select * from Student where sid not in (select sid from sc where cid in (select cid from Course where tid =(select tid from Teacher where tname='叶平')))
go
--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select t3.sid as '学号',t3.sname as '姓名' from student as t3 join (select t1.sid from (select * from sc where cid =1)as t1 join (select * from sc where cid =2)as t2 on t1.sid=t2.sid) as t4 on t3.sid=t4.sid
go
--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select t3.sid as '学号',t3.sname as '姓名' from student as t3 join (select t1.sid from (select * from sc where cid=1) as t1 join (select * from sc where cid=2)as t2 on t1.sid=t2.sid where t1.score<t2.score)as t4 on t3.sid=t4.sid
go
--9、查询所有课程成绩小于60分的同学的学号、姓名；
select sid as '学号',sname as '姓名' from Student where sid in (select sid from sc where score < 60)
go
--10、查询没有学全所有课的同学的学号、姓名；
select sid as '学号',sname as '姓名' from Student where sid in(select sid from sc group by sid having count(*) <(select count(*) as sumc from Course))
go
--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select sid as '学号',sname as '姓名' from Student where sid in (select sid from sc where sid != 1001 and cid in (select cid from Course where cid in (select cid from sc where sid = 1001)) group by sid)
go
--12、查询至少学过学号为“1001”同学所有一门课的其他同学学号和姓名；
select t3.sid as '学号' ,t3.sname as '姓名',t4.cname as '科目' from Student as t3 join(select sid,cname from sc,(select * from Course where cid in (select cid from sc where sid = 1001))as t1 where sid != 1001 and sc.cid =t1.cid) as t4 on t3.sid=t4.sid
go
--13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
update sc set score = (select avg(score)as avgy from sc where cid = (select cid from Teacher where tname='叶平')) where cid =(select cid from Course where tid = (select tid from Teacher where tname='叶平'))
go
--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
select student.sid as '学号',sname as '姓名' from Student where sid in (select sid from (select cid from sc where sid = 1002) as t1 join (select * from sc where sid != 1002) as t2 on t1.cid=t2.cid group by sid having count(*)=(select count(*) from sc where sid = 1002))
go
--15、删除学习“叶平”老师课的SC表记录；
delete from sc where cid in(select cid from Course where tid=(select tid from Teacher where tname ='叶平'))
go
--16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“1003”课程的同学学号、2号课的平均成绩；
declare @score int
select @score = avg(score) from sc where cid =2
insert into SC values(1005,2,@score)
go
--17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
select isnull(t3.sid,1005)as '学号',isnull(sqls,0)as '数据库',isnull(english,0)as '英语',manager as '企业管理' from (select t1.sid,sqls,english from (select sid, isnull(avg(score),0)as sqls from sc where cid=1 and sid in (select sid from Student) group by sid)as t1,(select sid, avg(score)as english from sc where cid=3 and sid in (select sid from Student) group by sid)as t2) as t3 right join(select sid, avg(score)as manager from sc where cid=2 and sid in (select sid from Student) group by sid) as t4 on t3.sid=t4.sid
go
--18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cid as '课程ID', max(score)as '最高分',min(score)as '最低分' from sc where cid in (select cid from Course) group by cid
go