数据库第二次作业

《数据库第二次作业——上机实验题》

实验一：交互式S Q L的使用

实验要求：

1,创建Student数据库，包括Students,Courses,SC表，表结构如下：

Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)

Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)

SC(SNO,CNO,GRADE)

(注：下划线表示主键，斜体表示外键)，并插入一定数据。

答：

create table Students

(

SNO varchar(100) primary key,

SNAME varchar(100) null,

SEX varchar(100) null,

BDATE datetime null,

HEIGHT decimal null,

DEPARTMENT varchar(100) null

)

go

create table Courses

(

CNO varchar(100) primary key,

CNAME varchar(100) null,

LHOUR int null,

CREDIT int null,

SEMESTER varchar(100) null

)

go

CREATE TABLE [dbo].[SC](

[SNO] varchar(100) NOT NULL,

[CNO] varchar(100) NOT NULL,

[GRADE] [int] NULL,

CONSTRAINT [PK_SC] PRIMARY KEY CLUSTERED

(

[SNO] ASC,

[CNO] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SC] WITH CHECK ADD CONSTRAINT [FK_SC_Courses]

FOREIGN KEY([CNO])

REFERENCES [dbo].[Courses]([CNO])

GO

ALTER TABLE [dbo].[SC] CHECK CONSTRAINT [FK_SC_Courses]

GO

ALTER TABLE [dbo].[SC] WITH CHECK ADD CONSTRAINT [FK_SC_Students]

FOREIGN KEY([SNO])

REFERENCES [dbo].[Students] ([SNO])

GO

ALTER TABLE [dbo].[SC] CHECK CONSTRAINT [FK_SC_Students]

2．完成如下的查询要求及更新的要求。

（1）查询身高大于1.80m的男生的学号和姓名；

答:

select SNO,SNAME from Students where HEIGHT>1.8

（2）查询计算机系秋季所开课程的课程号和学分数；

答：

select CNO,CREDIT from Courses where SEMESTER=’秋季’

（3）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；

答：

select s.SNAME,O,c.CREDIT,SC.GRADE from students s

inner join SC on sc.SNO=s.SNO

inner jpin Courses c on O=c,CNO

where s.DEPARTMENT=’计算机系’and s.SEX=’男’and c.SEMESTER=’秋季’

（4）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头）；

答：

select distinct s.sname from Students s,sc where s.sno=sc.sno and s.sex=’女’and o like ’EE%’（5）查询每位学生已选修课程的门数和总平均成绩；

答：

select count(O) as 课程门数,avg(SC.GRADE) as 总平均成绩from students s

inner join SC on sc.SNO=s.SNO

inner join Courses c on O=O

group by s.SNO

（6）查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩；

答：

select cname,count(cno),max(grade),min(grade),avg(grade)

from students natural join sc natural join courses

group by chane;

（7）查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列；

答：

select sname,sno from students natural join (select sno.min(grade)as mini from sc group by sno)where mini>80 order by(sno);

（8）查询缺成绩的学生的姓名，缺成绩的课程号及其学分数；

答：

select sname,o,credit

from students,courses,sc

where students.sno=sc.sno and o=o and grade is null;

（9）查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名；

答：

select sname from students,courses,sc

where students.sno=sc.sno and o=sc.cho and credit>=3 and grade<70;

（10）查询1984年~1986年出生的学生的姓名,总平均成绩及已修学分数。

答：

select sname,avg(grade),sum(credit)