华工数据库实验题.docVIP

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_DUP_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 HEIGHT1.8 （2）查询计算机系秋季所开课程的课程号和学分数； 答： select CNO,CREDIT from Courses where SEMESTER=秋季 （3）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩； 答： select s.SNAME,SC.CNO,c.CREDIT,SC.GRADE from students s inner join SC on sc.SNO=s.SNO inner join Courses c on sc.CNO=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 sc.cno like EE% （5）查询每位学生已选修课程的门数和总平均成绩； 答： select count(c.CNO) as 课程门数,avg(SC.GRADE) as 总平均成绩from students s inner join SC on sc.SNO=s.SNO inner join Courses c on sc.CNO=c.CNO group by s.SNO （6）查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩； 答： select cname,count(cno),max(grade),min(grade