实验一 sql语言及相关操作及答案1.docx

1、实验一 sql语言及相关操作及答案1实验一 SQL语言及相关操作一、 实验目的本实验的目的是使学生掌握如何在SQL Server Management studio中进行查询分析以及对数据库中的表和数据进行操作，加深对SQL和SQL语言相关操作理解。熟练掌握数据查询、表的更新等操作方法。二、 实验时数 4学时三、实验内容：(1)利用SQL语句进行简单查询和连接查询操作。1.列出所有course的课程号、课程名和学分。2.查询所有蒙古族学生的学号、姓名和所在学院。3.在score表中显示平均成绩都高于85分的学生学号、课程号和成绩。 4.查询选修课称号为001或005且平均成绩大于等于75分学生

2、的学号、课程号和成绩。5.查询信息管理学院1991年出生的所有男生的信息。 6.查询所有学生的年龄。 7.查询所有姓王或姓黄的学生的姓名、生日和所在班级名称。8.查询先修课程为”数据结构”的所有课程。9.查询信息管理学院非汉族同学的学号、姓名、性别及民族。 10.查找选修了“操作系统”的学生学号、成绩和姓名。11.查找至少选修了一门其直接先修课编号为004的课程的学生学号和姓名。12.查找至少选修了学号为0800001的学生所选课程的学生学号和姓名。10413.查询出生日期在1991年以后的学生的学号、姓名、籍贯和年龄。14.在student表中查询学生的学号、姓名和平均成绩，并按照平均成绩的

3、降序排列。15.查找了选修了以“C语言程序设计”为其直接先修课的课程的学生学号、课程号和成绩。16.在score表中查询平均成绩大于80的学生的学号、课程号和成绩，并先按照课程号的升序、再按照成绩的降序排列。17.查找信息管理学院学生选课情况，显示学生姓名、课程名和成绩。18.统计student表中的男女学生的人数。19.查询成绩最高分的学生的学号、课程号和相应成绩。 20.查询选课少于3门的学生的学号及其选课的门数。(2) 利用SQL语句进行表的管理等操作。1.给class表中的classname字段添加唯一约束。2.给Student表中的sex字段添加检查约束。 3.建立学生会名单表Stu

4、dentUnion，包含三个字段：编号SID（char(2)），姓名studnetName (varchar(20)，职务post(char(15)。4.为StudentUnion表插入两条数据（1,李小勇,学生会主席）, （2,吴敏,文艺部长）5.在上题中建立的表StudentUnion中增加一个字段“电话(char(8)”，为电话列添加检查约束，要求每个新加入或修改的电话号码为8位数字。6.为StudentUnion表增加一个字段“地址（varchar(50)）”。7.在表StudentUnion中的地址字段添加一个默认约束，默认值为“独墅湖高教区仁爱路1号”。8.把字段职务post的数据

5、类型改为varchar(15)。9.将计算机科学与技术07-01班的男同学的成绩减去5分。10.将学号为0800008同学的籍贯修改为厦门。四、 实验目的请各位同学结合实验内容，写实验的总结，可以写在实验中、学习中遇到的问题，如何解决的，最后学到了哪些知识。每个同学的总结应该都是不同的，如果发现有雷同的实验报告，都将本次实验成绩作为不及格记录。完成本次实验后，请根据要求完成实验报告，实验报告要求每一题都配合都准确的截图，截图的大小不能超过实验报告纸的范围且不能把实验报告的头部内容给遮挡住。-(1)利用SQL语句进行简单查询和连接查询操作。-1.列出所有course的课程号、课程名和学分。sel

6、ect courseno,coursename,credithourfrom course;-2.查询所有蒙古族学生的学号、姓名和所在学院。select studentNo,studentName,institutefrom Student,Classwhere student.classNo=Class.classNo;-3.在score表中显示平均成绩都高于85分的学生学号、课程号和成绩。select *from Scorewhere studentNo in( select studentno from Score group by studentNo having AVG(score)

7、85);-4. 查询选修课称号为001或005且平均成绩大于等于75分学生的学号、课程号和成绩。select *from Scorewhere courseNo in (001,005)and studentNo in( select studentNo from Score group by studentNohaving AVG(score)=75);-5.查询信息管理学院年出生的所有男生的信息。select *from Student,Classwhere institute=信息管理学院and sex=男 and YEAR(birthday)=1991;-6.查询所有学生的年龄。sel

8、ect studentno,YEAR(GETDATE()-YEAR(birthday) as agefrom Student-7.查询所有姓王或姓黄的学生的姓名、生日和所在班级名称。select studentName,birthday,classnamefrom Student,Classwhere Student.classNo=Class.classNoand (studentName like 王% or studentName like 黄%);-8.查询先修课程为”数据结构”的所有课程。select a.courseno,a.coursenamefrom Course a,Cour

9、se bwhere a.priorCourse=b.courseNoand b.courseName=数据结构;-9.查询信息管理学院非汉族同学的学号、姓名、性别及民族。select studentNo,studentName,sex,nationfrom Student,Classwhere Student.classNo=Class.classNoand institute=信息管理学院and nation !=汉族;-10.查找选修了“操作系统”的学生学号、成绩和姓名。select student.studentNo,studentname,Scorefrom Student,cours

10、e,Scorewhere Student.studentNo=score.studentNoand Course.courseNo=score.courseNoand courseName=操作系统;-11.查找至少选修了一门其直接先修课编号为004的课程的学生学号和姓名。select score.studentno,studentnamefrom Student,Course,Scorewhere Student.studentNo=score.studentNoand score.courseNo=Course.courseNoand priorCourse=004;-12.查找至少选修了

11、学号为0800001的学生所选课程的学生学号和姓名。select score.studentno,studentnamefrom Score,studentwhere score.studentNo=Student.studentNo and courseNo=all(select coursenofrom Scorewhere studentNo=0800001)-13.查询出生日期在年以后的学生的学号、姓名、籍贯和年龄。select studentno,studentName,native,YEAR(GETDATE()-YEAR(birthday) as agefrom Student w

12、here YEAR(birthday)1991;-14.在student表中查询学生的学号、姓名和平均成绩，并按照平均成绩的降序排列。select score.studentno,studentName,AVG(score) as avgscorefrom Student,Scorewhere Student.studentNo=score.studentNogroup by score.studentNo,studentNameorder by avgscore desc; -15.查找了选修了以“C语言程序设计”为其直接先修课的课程的学生学号、课程号和成绩。select student.s

13、tudentNo,score.courseNo,scorefrom student,score,Coursewhere student.studentNo=score.studentNoand score.courseNo=Course.courseNoand Course.courseNo=( select a.courseNo from Course a,Course b where a.priorCourse=b.courseNo and b.courseName=C语言程序设计);-16.在score表中查询平均成绩大于80的学生的学号、课程号和成绩，并先按照课程号的升序、再按照成绩的

14、降序排列。select * from Scorewhere studentNo in ( select studentNo from Score group by studentNo having AVG(score)80 )order by courseNo,score desc;-17.查找信息管理学院学生选课情况，显示学生姓名、课程名和成绩。select studentName,courseName,scorefrom Student,Course,Score,classwhere Student.studentNo=score.studentNoand Course.courseNo=

15、score.courseNoand Student.classNo=Class.classNoand institute=信息管理学院;-18.统计student表中的男女学生的人数。select sex,count(*) as 人数from Studentgroup by sex-19.查询成绩最高分的学生的学号、课程号和相应成绩。select studentNo,courseno,scorefrom Score where score =( select MAX(score) from Score); -20.查询选课少于门的学生的学号及其选课的门数。select studentno,CO

16、UNT(*) as 门数from Scoregroup by studentnohaving count(*)3-(2) 利用SQL语句进行表的管理等操作。-1.给class表中的classname字段添加唯一约束。alter table class add constraint uniqueClassname unique(classname);-2.给Student表中的sex字段添加检查约束。alter table student add constraint chkSex check (sex in (男,女);-3.建立学生会名单表StudentUnion，包含三个字段：编号SID（

17、char(2)），姓名studnetName (varchar(20)，职务post(char(15)。create table studentUnion(sid char(2) primary key,studentname varchar(20),post char(15);-4.为StudentUnion表插入两条数据（,李小勇,学生会主席）, （,吴敏,文艺部长）insert into studentUnionvalues(1,李小勇,学生会主席);insert into studentUnionvalues(2,吴敏,文艺部长);-5.在上题中建立的表StudentUnion中增加一

18、个字段“电话(char(8)”，为电话列添加检查约束，要求每个新加入或修改的电话号码为位数字。alter table studentUnion add telephone char(8);-6.为StudentUnion表增加一个字段“地址（varchar(50)）”。alter table studentUnion add address varchar(50);-7.在表StudentUnion中给地址字段添加一个默认约束，默认值为“独墅湖高教区仁爱路号”。alter table studentUnion add constraint defaultAdd default 独墅湖高教区仁爱

19、路号 for address; -8.把字段职务post的数据类型改为varchar(15)。alter table studentUnion alter column post varchar(15);-9.将计算机科学与技术-01班的男同学的成绩减去分。update Scoreset score=score-5from scorewhere studentNo in ( select studentNo from Class,Student where class.classNo=Student.classNo and className=计算机科学与技术-01班 and sex=男);-10.将学号为同学的籍贯修改为厦门。update studentset native=厦门where studentNo=0800008;