实验12＿解答.txt

这个是数据库教程复习课件。里面有教你怎么应用好SQL语言和基本的数据库原理

实验一：

创建STUDENTS数据库：
1.1.1　STUDENTS表（！注意：主键及其它约束定义）
CREATE TABLE dbo.Students
	(
	SNO char(7) NOT NULL,
	SNAME varchar(8) NOT NULL,
	SEX char(2) NOT NULL,
	BDATE datetime NOT NULL,
	HEIGHT decimal(5, 2) NULL,
	DEPARTMENT varchar(10) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Students ADD CONSTRAINT
	DF_Students_HEIGHT DEFAULT 000.00 FOR HEIGHT
GO
ALTER TABLE dbo.Students ADD CONSTRAINT
	PK_Students PRIMARY KEY CLUSTERED 
	(
	SNO
	) ON [PRIMARY]

GO
1.1。1　创建COURSES表：（！注意：主键及其它约束定义）
CREATE TABLE dbo.COURSES
	(
	CNO char(6) NOT NULL,
	LHOUR smallint NOT NULL,
	CREDIT decimal(1, 0) NOT NULL,
	SEMESTER char(2) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.COURSES ADD CONSTRAINT
	PK_COURSES PRIMARY KEY CLUSTERED 
	(
	CNO
	) ON [PRIMARY]

GO
1.1。3　创建SC表（！！注意：主键、外键(删除限制）及其它约束定义）
CREATE TABLE dbo.SC
	(
	SNO char(7) NOT NULL,
	CNO char(6) NOT NULL,
	GRADE decimal(4, 1) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.SC ADD CONSTRAINT
	PK_SC PRIMARY KEY CLUSTERED 
	(
	SNO,
	CNO
	) ON [PRIMARY]

GO
ALTER TABLE dbo.SC ADD CONSTRAINT
	FK_SC_Students FOREIGN KEY
	(
	SNO
	) REFERENCES dbo.Students
	(
	SNO
	)
	
GO
ALTER TABLE dbo.SC ADD CONSTRAINT
	FK_SC_COURSES FOREIGN KEY
	(
	CNO
	) REFERENCES dbo.COURSES
	(
	CNO
	)
GO
COMMIT
1.1.2　插入数据项（略）

1.2 SQL作业：
课后习题2：
(1)
select sno,sname from student where height>1.80


(2)
select cno,credit from course where semester='秋' and cno like 'CS%';


(3)
select sname,c.cno,credit,grade from student s,course c,sc
where s.sno=sc.sno and c.cno=sc.cno and c.cno like 'CS%' and sex='男'and semester='秋'and grade is not null

(4)
select sname from student s,sc
where s.sno=sc.sno and cno like 'EE%' and sex='女' 
or:
select sname from student s,sc
where s.sno=sc.sno and cno like 'EE%' and sex='女' 
group by s.sno,sname having count(cno)>=1


(5)
select sno,count(cno),avg(grade) from sc
where grade is not null 
group by sno


(6)
select cno,count(sno),max(grade),min(grade),avg(grade) from sc
where grade is not null 
group by cno


(7)
select s.sno,sname from student s,sc
where s.sno=sc.sno and s.sno not in(select sno from sc where grade is null)
 and grade is not null 
group by s.sno,sname having min(grade)>=80


(8)
select sname,sc.cno,credit from student s,course c,sc
where s.sno=sc.sno and c.cno=sc.cno and grade is null


(9)
select sname from student s,course c,sc
where s.sno=sc.sno and sc.cno=c.cno and credit>=3 and grade<70
or:
select sname from student s,course c,sc
where s.sno=sc.sno and sc.cno=c.cno and credit>=3 
group by sc.sno,sname having min(grade)<70

(10)

select sname,avg(grade),sum(credit) from student s,course c,sc
where s.sno=sc.sno and sc.cno=c.cno and year(bdate) between 1974 and 1976
 group by sc.sno,sname

习题3：
delete from Students
	where SNO LIKE '91%' 
delete from SC
	where SNO LIKE '91%'

习题4： 
INSERT INTO Students(SNO,SNAME,SEX,BDATE,HEIGHT)
　VALUES ('9409101','何平','女','1977-03-02',1.62) 
INSERT INTO Students(SNO,SNAME,SEX,BDATE,HEIGHT)
　VALUES ('9408130','向阳','男','1976-12-11',1.75) 

习题5：
UPDATE Courses
　SET CREDIT=3,LHOUR=60
　WHERE CNAME='CS-221'


补充题：
1.3.1　统计各系的男生和女生的人数
select department,sex,count(*) from s 
group by department,sex

1.3.2　列出学习过‘编译原理’，‘数据库’或‘体系结构’课程，且这些课程的成绩之一在90分以上的学生的名字
select sname from s
where sno in(select sno from sc,c where c.cno=sc.cno and cname in ('编译'，'数据库'，'体系结构') 
and grade>=90) 

or
select sname from s
where 90<=some(select grade from sc,c where s.sno=sc.sno and  c.cno=sc.cno and cname in ('编译'，'数据库'，'体系结构')) 


1.3.3　列出未修选‘电子技术’课程，但选修了‘数字电路’或‘数字逻辑’课程的学生数
select count(distinct sno)
from sc,course
where sc.cno=couse.cno and (cname='数字电路' or cname='数字逻辑') and 
	sno not in (select sno from sc sc1,course c1 where sc1.cno=c1.cno and cname='电子技术')  

1.3.4
按课程排序列出每门课程的所有学生的成绩，如果没有学生选修，也需要列出，学生情况用空值表示。
（！！注意：外连接的用法）
select c.cname,sno,grade from sc,courses c 
where c.cno*=sc.cno
order by c.cno


1.3.5
列出平均成绩最高的学生名字和成绩。
select sname,avg(sc.grade)  from students s,sc,courses c 
where s.sno=sc.sno and c.cno=sc.cno
group by s.sno,s.sname
having avg(sc.grade)>=all(select avg(sc1.grade) from students s1,sc sc1,courses c1 
where s1.sno=sc1.sno and c1.cno=sc1.cno
group by s1.sno)


1.4　选做题：
列出有资格选修数据库课程的所有学生。
(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)
创建表CP用来存放某课程的先修课（注意：外键定义）

CREATE TABLE dbo.CP
	(
	CNO char(7) NOT NULL,
	CPNO char(7) NOT NULL
	)  ON [PRIMARY]
GO

ALTER TABLE dbo.CP ADD CONSTRAINT
	FK_CP_COURSES FOREIGN KEY
	(
	CNO
	) REFERENCES dbo.COURSES
	(
	CNO
	)
GO
ALTER TABLE dbo.CP ADD CONSTRAINT
	FK_CP_COURSES1 FOREIGN KEY
	(
	CPNO
	) REFERENCES dbo.COURSES
	(
	CNO
	)
GO

SELECT SNAME FROM STUDENTS S 
WHERE NOT EXISTS(SELECT * FROM COURSES C,CP
        WHERE C.CNO=CP.CNO AND C.CNAME='数据库' 
AND NOT EXISTS(SELECT * FROM SC WHERE S.SNO=SC.SNO AND CP.CPNO=SC.CNO AND SC.GRADE>=60))


实验二：

存储过程（SQL Server)

(1)
CREATE PROCEDURE Add_Student 
@SNO CHAR(6),@SNAME VARCHAR(8),@SEX CHAR(2),@BIRTHDAY DATETIME,@HEIGHT DECIMAL(5,2),@DEPT VARCHAR(8)
AS
INSERT INTO Students values (@SNO,@SNAME,@SEX,@BIRTHDAY, @HEIGHT, @DEPT)
print '成功插入一条学生记录'
GO

EXEC ADD_STUDENT '9132021','李强','男','1975-02-03',1.77,'EE'
Go

(2) CREATE PROCEDURE Upd_Grade
@SNO CHAR(6),@CNO CHAR(6),@GRADE DECIMAL(4,1)
AS
UPDATE SC SET GRADE=@GRADE WHERE SNO=@SNO AND CNO=@CNO
print '成功修改'+@SNO+'学生成绩记录'
SELECT * FROM SC WHERE SNO=@SNO

GO

EXEC Upd_grade '9132021','cs-120',60
Go

(3)
CREATE PROCEDURE Disp_Student
@SNO CHAR(7),@SUM_CREDIT REAL output,@AVG_GRADE REAL output
AS
SELECT S.*,C.CNAME,GRADE FROM STUDENTS S,SC,COURSES C 
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND S.SNO=@SNO
SELECT @SUM_CREDIT=SUM(CREDIT),@AVG_GRADE=AVG(GRADE) FROM STUDENTS S,SC,COURSES C 
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO AND S.SNO=@SNO
PRINT '学分总计：'+CONVERT(VARCHAR(10),@SUM_CREDIT)
PRINT '平均分：'+CONVERT(VARCHAR(10),@AVG_GRADE)
GO

DECLARE @sum real,@avg real
EXEC Disp_Student '9309119',@sum,@avg
GO

(4)
CREATE PROCEDURE Cal_GPA
@SNO CHAR(7),@GPA REAL output
AS
DECLARE CUR_GRADE CURSOR FOR 
	SELECT CREDIT,GRADE FROM SC,COURSES C 
             WHERE SC.CNO=C.CNO AND SC.SNO=@SNO
DECLARE @GRADE REAL,@CREDIT REAL,@SUMCREDIT REAL
SET @GPA=0
SET @SUMCREDIT=0
OPEN CUR_GRADE
FETCH NEXT FROM CUR_GRADE INTO @CREDIT, @GRADE
WHILE @@FETCH_STATUS=0
BEGIN
    IF @GRADE>=85 
       SET @GPA=@GPA+@CREDIT*4
    ELSE
       IF @GRADE>=75
           SET @GPA=@GPA+@CREDIT*3
       ELSE
           IF @GRADE>=60
               SET @GPA=@GPA+@CREDIT*2
           ELSE
               SET @GPA=@GPA+@CREDIT
      SET @SUMCREDIT=@SUMCREDIT+@CREDIT
      FETCH NEXT FROM CUR_GRADE INTO @CREDIT, @GRADE
END
CLOSE CUR_GRADE
DEALLOCATE CUR_GRADE
PRINT @GPA
PRINT @SUMCREDIT
IF @SUMCREDIT>0 SET @GPA=@GPA/@SUMCREDIT
PRINT @SNO+'学生的GPA为：'+CONVERT(VARCHAR(10),@GPA)
GO

DECLARE @SUM REAL
EXEC CAL_GPA '9209120',@SUM
GO