sql 实验.docx

实验二 SQL语言的基本操作【实验目的】掌握利用SQL语句完成各种查询操作的能力。【实验学时】10学时【实验类型】综合型【实验环境】SQL Server2005【实验人数】1人/组【实验内容及要求】用SQL语句和企业管理器建立如下的表结构并输入数据 给定表结构如下：学生表：student（主键Sno）学号Sno姓名Sname性别Ssex年龄Sage所在系Sdept95001 李勇 男 20 CS 95002 刘晨 女 21 IS 95003 王敏 女 18 MA 95004 张力 男 19 IS 课程表：Course（主键Cno）课程号Cno课程名Cname先行课Cpno学分Ccredit1 数据库 5 4 2 数学 2 3 信息系统 1 4 4 操作系统 6 3 5 数据结构 7 4 6 数据处理 2 7 PASCAL语言 6 4 选课表：SC（主键Sno,Cno，外部键Sno，Cno）学号Sno课程表Cno成绩Grade950011929500128595001388950022909500238595003359用SQL语句完成一下的要求：create database informationcreate table Student(Sno varchar(10) primary key ,Sname nvarchar(10) ,Ssex nvarchar(2) ,Sage int ,Sdept varchar(10) )insert into Student values(95001,李勇,男,20,CS)insert into Student values(95002,刘晨,女,21,IS)insert into Student values(95003,王敏,女,18,MA)insert into Student values(95004,张力,男,19,IS)create table Course(Cno varchar(10) primary key ,Cname nvarchar(10) ,Cpno nvarchar(10) ,Ccredit int )insert into Course values(1,数据库,5,4)insert into Course values(2,数学,2)insert into Course values(3,信息系统,1,4)insert into Course values(4,操作系统,6,3)insert into Course values(5,数据结构,7,4)insert into Course values(6,数据处理,2)insert into Course values(7,PASCAL,6,4)create table SC(Sno varchar(10) foreign key references Student(Sno),Cno varchar(10)foreign key references Course(Cno),primary key(Sno,Cno) ,-constraint PK_T primary key(Sno,Cno),Grade int )insert into SC values (95001,1,92)insert into SC values (95001,2,85)insert into SC values (95001,3,88)insert into SC values (95002,2,90)insert into SC values (95002,3,85)insert into SC values (95003,3,59)-select * from SC-select * from Course-select * from Student-1.查询信息系（IS）的所有学生信息select * from Student where Sdept=ISselect * from Student,Course,SC where Student.Sdept =ISand Student.Sno=SC.Sno and SC.Cno=Co-2.查询选修了“数学”课的所有学生名单select Sname 姓名 from Student,SC,Course where Came=数学and Co=SC.Cno and SC.Sno= Student.Sno -3.查询至少选修了一门其直接先行课为号课程的学生的姓名。select Snamefrom student,SC,Coursewhere Course.Cpno=5 and SC.Cno=Course.Cno and SC.Sno=student.Sno-4.查询全体学生的姓名和出生年份。select Sname 姓名,2012-Sage 出生年月 from Student-5.查询所有姓王的学生。select * from student where Sname like 王%-6.查询选修了号课程的学生姓名及成绩，并按成绩降序排序。select s.Sname 姓名,sc.Grade 成绩 from Student s,SC sc where sc.Cno=3 and sc.Sno = s.Sno order by sc.Grade desc-7.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。select * from Student order by Sdept asc, Sage desc-8.计算号课程的平均成绩。select AVG(Grade) 平均成绩 from SC where Cno=2-9.查询选修了号课程的学生的最高成绩。select MAX(Grade) 最高分 from SC where Cno=2-10.求各个课程号及相应的选课人数。select Sno 课程号,COUNT(Sno) 人数 from SC group by Sno-11.查询至少选修了门课程以上的学生学号。select Sno 学号 from SC group by Sno having COUNT(Sno)=3-12.查询“数据库”的间接先行课。select cname 先行课 from Course where cno=(select cpno from Course where cname=数据库)-13.查询平均成绩最高的学生的学号和姓名。-？select DISTINCT top 1 AVG(c.Grade) 平均分,c.Sno,s.Sname from Student s,SC c where s.Sno=c.Sno group by c.Sno,s.Sname-14.查询数学成绩最高的学生的学号和姓名。select DISTINCT top 1 s.Sno 学号,s.Sname 姓名,sc.Grade 分数 from Student s,Course c,SC sc where ame=数学 and o=sc.Cno and sc.Sno=s.Snoorder by sc.Grade desc-15.查询出成绩最低学号最大的学生学号。select top 1 s.Sno from Student s, SC sc where s.Sno=sc.Sno order by sc.Grade asc ,s.Sno desc-16.查询成绩高于学生平均成绩的记录。select AVG(SC.Grade) from SC,Student where SC.Sno=Student.Snoselect s.Sname,sc.Sno,sc.Grade 成绩 from SC sc ,Student swhere sc.Grade=(select AVG(SC.Grade) from SC,Student where SC.Sno=Student.Sno ) and sc.Sno=s.Sno -17.查询至少选修了号课程和号课程的学生学号。select DISTINCT Sno from SC where Sno in (select Sno from SC where Cno= 1 and Sno in (select Sno from SC where Cno=3) -18.查询只选修了号课程和号课程的学生学号。select Sno from SC where Sno in(select Sno from SC where Cno= 1 and Sno in (select Sno from SC where Cno=3) group by Sno having COUNT(Cno)=2 -19.查询没有选修号课程的学生姓名。select DISTINCT Student.Sname from SC,Student where SC.Sno not in(select SC.Sno from SC where SC.Cno=1) and SC.Sno=Student.Sno-20.查询选修了全部课程的学生姓名。select DISTINCT Student.Sname from SC,Student where SC.Sno in(select SC.Sno from SC where SC.Cno=1 and SC.Sno in( select SC.Sno from SC where SC.Cno= 3) and SC.Sno in (select SC.Sno from SC where SC.Cno=3) )and SC.Sno=Student.Snoselect DISTINCT s.Sname from SC,Student s where SC.Sno=s.Sno group by s.Sname having COUNT(SC.Cno)=3-21.查询至少选修了所选修的全部课程的学生学号。select DISTINCT Sno from SC where Sno in (select Sno from SC where Cno in (select Cno from SC where Sno=95002) group by Sno having COUNT(Cno)=2 -22.查询没有不及格课程的学生的学号和姓名。select DISTINCT SC.Sno 学号,s.Sname 姓名 from SC ,Student s where SC.Sno not in (select SC.Sno from SC where SC.Grade60) and SC.Sno=s.Sno-23.查询没有不及格学生的课程的课程号和课程名。select SC.Cno,c.Cname from SC,Course c where SC.Sno not