数据库概论--SQL语句练习.docxVIP

-1.创建数据库（数据库名为DB+你的学号如:DB123456）create database DB210908000-2.在数据库中创建学生表student(sno,sname,ssex,sage,sdept) sno为主码。create table Student( Sno char(10) primary key, Sname char(20), Ssex char(4), Sage smallint, Sdept char(20)-3.在数据库中创建课程表course(cno,cname,cpno,ccredit)，cno为主码,cpno不设外码。create table Course( Cno char(4) primary key, Cname char(40), Cpno char(4), Ccredit smallint)-4.在数据库中创建选修表SC(sno,cnao,grade),主码是（sno,cno）,sno,cno-均为外码，分别参照student(sno),course(cno)create table SC( Sno char(10) , Cno char(4), Grade smallint, primary key(Sno,Cno), foreign key(Sno) references Student(Sno), foreign key(Cno) references Course(Cno)-5.修改课程表增加课程名称必须取唯一值的约束条件alter table Course add constraint uk_Cname unique(Cname)-6.为学生表的sname建立唯一索引indexsnamecreate unique index indexsname on Student(Sname)-7.为选修表建立唯一性索引indexsnocno,按sno升序和cno降序建立create unique index indexsnocno on SC(Sno asc,Cno desc) -8.删除6题中建立的唯一性索引 drop index indexsname on Student-执行下列语句INSERT INTO StudentVALUES (95001,李勇,男,20,CS);INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)VALUES (95002,刘晨,女,19,IS);INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)VALUES (95003,王敏,女,18,MA)INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)VALUES (95004,张立,男,19,IS);INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)VALUES (95005,张国立,男,19,IS);INSERT INTO CourseVALUES (1,DB,5,4);INSERT INTO CourseVALUES (2,数学,null,5);INSERT INTO CourseVALUES (3,信息系统,1,4);INSERT INTO CourseVALUES (4,操作系统,6,3);INSERT INTO CourseVALUES (5,DB_structure,7,1);INSERT INTO CourseVALUES (6,数据处理,null,2);INSERT INTO CourseVALUES (7,PASCAL语言,6,4);INSERT INTO CourseVALUES (8,DB_ans,7,2);INSERT INTO CourseVALUES (9,DB_des,null,2);INSERT INTO SCVALUES (95001,1,92);INSERT INTO SCVALUES (95002,2,90);INSERT INTO SCVALUES (95001,2,85);INSERT INTO SCVALUES (95001,3,88);INSERT INTO SCVALUES (95002,3,80);insert into SCvalues(95003,3,50)insert into SCvalues(95004,2,40)insert into SCvalues(95004,3,30)-9.修改学生表增加“高考成绩(GKGrade)”列，数据类型为intalter table Student add GKGrade int-10.修改course表，增加（cpno）为外码alter table Courseadd foreign key(Cpno) references Course(Cno)-11.查询所有年龄在19岁以下的学生姓名及其年龄。select Sname,Sagefrom Studentwhere Sage19-12.查询考试成绩有不及格的学生的学号。select Sno,Cno,Gradefrom SCwhere Grade60-13.查询课程名以“数据”或“DB_”开头的所有课程信息select *from Coursewhere Cname like 数据%or Cname like DB_%escape-14.查询先修课程为空的课程号，课程名,先修课程号(cpno)select Cno,Cname,Cpnofrom Coursewhere Cpno is null-15.查询姓张且名字是三个字的学生姓名，年龄select Sname,Sagefrom Student where rtrim(Sname) like 张_-16.查询年龄不在2023岁之间的学生姓名、系别和年龄。select Sname,Sdept,Sagefrom Studentwhere Sage not between 20 and 23-17.查询全体学生情况，查询结果按性别升序排列，同性别学生按年龄降序。select *from Studentorder by Ssex asc ,Sage desc-18.计算95001学生平均成绩和最高分。select AVG(Grade) 平均分, MAX(Grade) 最高分from SCwhere Sno=95001-19.查询学分为3，4，5的课程的课程名，学分select Cname,Ccreditfrom Coursewhere Ccredit in(3,4,5)-20.删除表scdrop table SC-21.查询选修了课程的学生人数select COUNT(distinct Sno)from SC-22.查询该校有哪些系select distinct Sdeptfrom Student 2.将一个新学生元组（200215127,李斯,男,20,CS）添加到student表中。insert into Studentvalues(200215127,李斯,男,20,CS,null)-3.创建查询每个学生选修的总学分的视图，-要求列出学号和总学分:view_sumcredit(sno,sum_credit)create view view_sumcredit(sno,sum_credit)asselect Sno,SUM(Ccredit)from Course,SCwhere Course.Cno=SC.Cnogroup by Sno-4.将所有“数据库”课程的选修成绩置为0分。update SCset Grade=0where 数据库=(select Cnamefrom Coursewhere Course.Cno=SC.Cno)-5.修改课程表增加课程名称必须取唯一值的约束条件。alter table Student add constraint sk_Sname unique(Sname)alter table Course add constraint ck_Cname unique(Cname)-6.为学生表的sname建立唯一索引indexsnamecreate unique index indexsname on Student(Sname)-7.查询所有年龄在20岁以下的学生姓名及其年龄。 select Sname,Sage from Student where Sage 20-8.查询考试成绩有不及格的学生的学号。select Sno,Cno,Gradefrom SCwhere Grade=3select Snofrom SCgroup by Sno having COUNT(*)=(select COUNT(Cno) from Course)-16.查询选修了“数据库”课程的学生的学号和姓名。 select Student.Sno,Sname from Student,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Cname= 数据库-17.查询选修了2号课程且成绩高于此课程平均成绩的学号和成绩。select Sno,Grade,Cnofrom SCwhere Cno=2 and Grade( select AVG(Grade) from SC where Cno=2)-18.删除6题中建立的唯一性索引drop index indexsname on Student-19.在Course表中按课程号升序创建唯一索引Cno_1。create unique index Cno_1 on Course (Cno) -20.从Sc表中删除所有选修“数据库”的记录。delete from SCwhere 数据库=( select Cname from Course where Course.Cno =SC.Cno)-21.查询姓欧阳且名字是三个字的学生姓名，年龄select Sname,Sagefrom Studentwhere rtrim(Sname) like欧阳_-22.建立信息系选修了1号课程且成绩在90分以上的学生的视图v1(Sno,Sname,Grade)create view v1(Sno,Sname,Grade)asselect Student.Sno,Sname,Gradefrom Student,SCwhere Student.Sno=SC.Sno and Cno=1