【数据库】实验二 数据库SQL语言增、删、改、查功能

1.查询选了1号课且选了2号课的学生的班号、学号

SELECT FIR.sclass,FIR.sno
From  SC FIR INNER JOIN SC SEC 
ON (FIR.sclass=SEC.sclass 
AND FIR.sno=SEC.sno)
WHERE FIR.cno=1 AND SEC.cno=2;

2.查询选了1号课但不选2号课的学生的班号、学号

SELECT sclass,sno
From  SC 
WHERE cno=1
EXCEPT
SELECT sclass,sno
From  SC 
WHERE cno=2

3.查询1班平均分在85分以上的同学班号、学号、姓名、性别、系、各科课程号及成绩

SELECT Student.sclass,Student.sno,
       sname,ssex,Sdept,cno,grade
FROM Student INNER JOIN SC 
ON (Student.sclass=SC.sclass 
AND Student.sno=SC.sno)
WHERE Student.sclass=1
AND Student.sno IN(SELECT sno
FROM SC
WHERE sclass=1
GROUP BY sno
HAVING AVG(grade)>85);

4.查询至少选了1班2号同学所选课的所有班号、学号及同学姓名

SELECT DISTINCT Student.sclass,Student.sno,sname
FROM Student INNER JOIN SC SCX
ON (Student.sclass=SCX.sclass 
AND Student.sno=SCX.sno)
WHERE NOT EXISTS 
(SELECT *
FROM SC SCY
WHERE SCY.sclass=1 AND SCY.sno=2 AND
NOT EXISTS
(SELECT *
FROM SC SCZ
WHERE SCZ.cno=SCY.cno AND SCZ.sclass=SCX.sclass
AND SCZ.sno=SCX.sno )
);

5.查询不选1号课的学生班号及学号

SELECT DISTINCT sclass,sno
FROM Student
EXCEPT
SELECT DISTINCT sclass,sno
FROM SC
WHERE cno=1;

6.查询选2号课的学生名字及相应2号课成绩，按成绩从高到低排序

SELECT DISTINCT sname,grade
FROM Student INNER JOIN SC SCX
ON (Student.sclass=SCX.sclass 
AND Student.sno=SCX.sno)
WHERE cno=2
ORDER BY grade DESC

7.统计学生选修课程的班号、学号及总学分

SELECT sclass,sno,SUM(ccredit) sum
FROM SC INNER JOIN Course 
ON SC.cno=Course.cno
GROUP BY sclass,sno

8.统计1班选修3号课的学号及平均分

SELECT sno,AVG(grade) AVG
FROM SC
WHERE sno IN
( SELECT sno
FROM SC
WHERE cno=3 AND sclass=1)
AND sclass=1
GROUP BY sclass,sno

9.把个人信息及选课信息插入到Student和SC 表及新增加一门“无机化学”课程信息

INSERT INTO Student
(sage,sclass,Sdept,sname,sno,ssex)
VALUES (18,2,'XX','小明',8,'男');
INSERT INTO SC
(sclass,sno,cno,grade)
VALUES (2,8,1,100);
INSERT INTO Course
(cno,cname)
VALUES (10,'无机化学');
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM SC;

10.删除选修3号课的所有选课信息并显示删除后的结果

DELETE FROM SC
WHERE cno=3;
SELECT * FROM SC;

11.把选修1号课的所有男同学年龄增加1岁并显示最终学生Student信息

UPDATE Student
SET sage=sage+1
WHERE ssex='男' 
AND EXISTS
(SELECT *
FROM SC
WHERE cno=1 AND Student.sclass=SC.sclass AND Student.sno=SC.sno); 
SELECT * FROM Student

12.把每个选课人的学号、班号及平均成绩插入到一个新表中。

create table SN(
sno nchar(10),
sclass nchar(10),
avggrade float
PRIMARY KEY(sclass,sno),
FOREIGN KEY(sclass,sno) REFERENCES Student(sclass,sno)
);
INSERT INTO SN select sno,sclass,AVG(grade)
from SC
GROUP BY sno,sclass;
select *
from sn;

视图SQL语言功能

1.使用企业管理器创建视图：在ST库中以“student”表为基础，建立信息系学生的视图V_ISStudent

2 .使用SQL语句创建视图：

建立一个每个学生的学号、班号、姓名、选修的课名及成绩的视图S_C_GRADE；

create view S_C_GRADE
as
select student.sno,student.sage,student.sname,cname,SC.grade
from student,SC,Course
Where student.sclass = sc.sclass AND student.sno = SC.sno AND SC.cno = Course.cno

建立信息系建立信息系选修了1号课程且成绩在90分以上的学生的视图V_IS_Score

create view V_IS_Score
as
select s.sclass, s.sno, sname,cname,grade
from student s,sc,course c
where s.sclass=sc. sclass and s.sno=sc.sno and sc.cno= c.cno
and s.sno in
(
select s.sno
FROM sc sc1
where s.sclass=sc1.sclass and s.sno=sc1.sno
and sc1.cno=1 and sc1.grade>=90
)

将各系学生人数，平均年龄定义为视图V_NUM_AVG。

create view V_NUM_AVG(sdept,sum,avg)
as
select sdept, count (*), avg (sage)
from student
group by sdept

3.查询以上所建的视图结果

4.查询选修了1号课程的信息系学生

select *
from course c,s_c_grade s
where s.cname=c.cname and c.cno=1

5.在信息系学生的视图中找出年龄小于20岁的学生

select *
from v_ISStudent
where sage<20

6.将信息系学生视图V_ISStudent中学号一班2号的学生姓名改为“刘辰”

update V_ISStudent
set sname= '刘辰'
where sclass=1 and sno=2;

select *
from V_ISStudent

7.用SQL语句删除视图S_C_GRADE

drop view S_C_GRADE