1. 用子查询实现如下查询： （1）查询选了“C001”课程的学生姓名和所在系。 （2）查询通信工程系成绩80分以上的学生的学号和姓名。 （3）查询计算机系考试成绩最高的学生的姓名。 （4）查询年龄

一、SQL定义语句

--1.  用子查询实现如下查询：

--（）查询选了“C001”课程的学生姓名和所在系。

SELECT Sname,Sdept from SC,Student WHERE SC.Sno=Student.Sno AND Cno='C001'

--（）查询通信工程系成绩分以上的学生的学号和姓名。

SELECT Student.Sno,Sname FROM SC,Student WHERE SC.Sno=Student.Sno AND Sdept='通信工程系' AND Grade>80

--（）查询计算机系考试成绩最高的学生的姓名。

---SELECT  Sname ,Grade FROM SC,Student WHERE Sdept='计算机系' AND SC.Sno=Student.Sno ORDER BY Grade DESC;

SELECT TOP 1 Sname FROM SC,Student WHERE Sdept='计算机系' AND SC.Sno=Student.Sno ORDER BY Grade DESC;

--（）查询年龄最大的男生的姓名、所在系和年龄。

--SELECT  Sname,Sdept,Sage FROM Student WHERE Sex='男' ORDER BY Sage DESC

SELECT TOP 1 Sname,Sdept,Sage FROM Student WHERE Sex='男' ORDER BY Sage DESC

--2.  查询C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩。

SELECT Sno,Grade FROM SC WHERE Cno='C001' AND Grade>(SELECT avg(Grade) FROM SC );

--3.  查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、考试的课程名和考试成绩。

SELECT Sname,Cname,Grade

FROM SC s,Student,Course c

WHERE s.Sno=Student.Sno AND

c.Cno=s.Cno AND

Sdept='计算机系' and

 Grade > (

select AVG(Grade)

from SC,Student

where SC.Sno = Student.Sno AND c.Cno=s.Cno AND

 Sdept = '计算机系'

)

 

select Sname, Cname, Grade

from Student, SC, Course

where Student.Sno = SC.Sno AND

SC.Cno = Course.Cno AND

Sdept = '计算机系' AND

Grade > (

select AVG(Grade)

from SC,Student

where SC.Sno = Student.Sno AND Sdept = '计算机系'

)

--4.  查询VB课程考试成绩高于VB平均成绩的学生姓名和VB成绩。

SELECT Sname,Grade

FROM SC,Student,Course

WHERE SC.Sno=Student.Sno AND

Course.Cname='VB' AND

Course.Cno = SC.Cno AND

Grade > (SELECT avg(Grade) FROM SC,Course WHERE Course.Cname='VB' AND Course.Cno=SC.Cno)

--5.  查询没选VB的学生姓名和所在系。

SELECT Sname,Sdept FROM Student WHERE Sname NOT IN (SELECT SC.Sno FROM SC,Student,Course WHERE Cname='VB' and Course.Cno=SC.Cno AND Student.Sno= SC.Sno )

--6.  查询每个学期学分最高的课程信息，列出开课学期、课程名和学分。

SELECT  Semester,Cname,Credit from Course c1 WHERE Credit  = (SELECT max(Credit) FROM Course c2 WHERE c1.Semester=c2.Semester)

--7.  查询每门课程考试成绩最高的学生信息，列出课程号、学生姓名和最高成绩，结果按课程号升序排序，不包括没考试的课程。

SELECT Course.Cno,Course.Cname,Sname,maxgrade from Course,Student,SC,(SELECT Cno,max(Grade) from SC  GROUP BY Cno) as max_grade(Cno,maxgrade) WHERE max_grade.Cno=Course.Cno AND maxgrade=SC.Grade AND SC.Sno=Student.Sno ORDER BY Course.Cno ASC

--8.  查询计算机系学生姓名、年龄和年龄情况，其中年龄情况为：如果年龄小于，则显示“偏小”；如果年龄在-22，则显示“合适”；

--如果年龄大于，则显示“偏大”。

select Sname , Sage,case when Sage <18 then '偏小'when Sage between 18 and 22 then '合适'when Sage >22 then '偏大'end '年龄情况' from Student where Sdept = '计算机系'

--9.  统计每门课程的选课人数，包括有人选的课程和没有人选的课程，列出课程号，选课人数及选课情况，其中选课情况为：

--如果此门课程的选课人数超过人，则显示“人多”；如果此门课程的选课人数在～，则显示“一般”；如果此门课程的选课人数在～

--0，则显示“人少”；如果此门课程没有人选，则显示“无人选”。

SELECT Cno,count(*) '选课人数', CASE WHEN count(*)>100 THEN '人多' when count(*) BETWEEN 40 AND 100 THEN '一般' WHEN count(*) BETWEEN 1 AND 40 THEN '人少' end '无人选' from SC group by Cno;

--10.  查询计算机系选了VB课程的学生姓名、所在系和考试成绩，并将结果保存到新表VB_Grade中。

--Select …into VB_Grade

SELECT Sname,Sdept,Grade into VB_Grade from SC, Student,Course WHERE SC.Sno=Student.Sno AND Course.Cno=SC.Cno AND Cname='VB'

--11.  统计每个系的女生人数，并将结果保存到新表Girls中。

SELECT Sdept,count(*)  FROM Student  where Sex='女'  GROUP BY Sdept ;

--12.  创建一个新表，表名为test，其结构为（COL1, COL 2, COL 3），其中，

--COL1：整型，允许空值。

--COL2：普通编码定长字符型，长度为，不允许空值。

--COL3：普通编码定长字符型，长度为，允许空值。

--试写出按行插入如下数据的语句（空白处表示是空值）。

create table test

(

COL1 int NULL,

COL2 char(10) NOT NULL,

COL3 char(10) NULL

)

insert into test values

(NULL, 'B1', NULL),

(1, 'B2','C2'),

(2, 'B3', NULL);

 

--13.利用第题建立的VB_Grade表，将信息管理系选了VB课程的学生姓名、所在系和考试成绩插入到VB_Grade表中。

insert into VB_Grade(Sname, Sdept, Grade)

select Sname, Sdept, Grade

from Student, SC, Course

where Student.Sno = SC.Sno AND SC.Cno = Course.Cno

AND Sdept = '信息管理系' AND Cname = 'VB' ;

 

--14.  将所有选修C001课程的学生的成绩加分。

UPDATE SC SET Grade=Grade+10 WHERE Cno='C001'

--15.  将计算机系所有学生的“计算机文化学”的考试成绩加分。

UPDATE SC SET Grade=Grade+10 WHERE Sno IN (SELECT Sno from Student where Sdept='计算机系' ) AND Cno IN (SELECT Cno from Course WHERE Cname='计算机文化学')

 

--16.  修改“VB”课程的考试成绩，如果是通信工程系的学生，则增加分；如果是信息管理系的学生则增加分，其他系的学生不加分。

--提示：根据系别判断加分，case when

UPDATE SC SET Grade= Grade+ CASE Sdept WHEN '通信工程系' THEN 10 else 0 end FROM Student,SC,Course WHERE SC.Sno=Student.Sno AND Course.Cno=SC.Cno AND Cname='VB'

 

--17.  删除成绩小于分的学生的选课记录。

 

DELETE FROM SC WHERE  Grade<50;

--18.  删除计算机系VB考试成绩不及格学生的VB选课记录。

DELETE FROM SC

FROM SC,Student,Course WHERE SC.Sno=Student.Sno

AND Course.Cno=Course.Cno AND Sdept='计算机系' AND

Cname='VB' AND Grade<60

 

 

--19.  删除“VB”考试成绩最低的学生的VB修课记录。

DELETE FROM SC FROM SC,Course

 

WHERE SC.Cno=Course.Cno AND Cname='VB'

AND Grade=(SELECT MIN(Grade) FROM SC,Course WHERE SC.Cno=Course.Cno AND Cname='VB')

 

 

--20.  删除没人选的课程的基本信息。

DELETE  FROM  Course FROM Course left  join SC on Course.Cno=SC.Cno WHERE  SC.Cno  IS  NULL