学生系

 

/*----系表----*/

create table department

(

 dShortName varchar(5)not null, --系的简称,  主键

 dName varchar(20) not null     --系的名称

)

 

/*----信息表----*/

create table student

(

 sNO char(15)not null ,         --学生学号,  主键

 sName varchar(20) not null,   --学生姓名

 Age int not null,             --年龄

 Sex char(2),

 department varchar(5)not null, --系的简称,  外键引用department.dShortName

)

 

/*----课程表----*/

create table subject

(

 uID int not null identity(1,1),   --课程号,   主键

 uName varchar(20),  --课程名称

)

 

/*----选修课程表----*/

create table Mark

(

msNo char(15) not null, --学生学号,    外键引用student.sNO

muID int not null,      --选修课程号,  外键引用subject.uID

mark float              --成绩

)

 

 

 

 

--1.查询每个学生及其选修课程的情况

 select 姓名=sName,选修课程=uName from student  join mark on student.sno=Mark.msNO join subject on Mark.muid=subject.Uid

--或

 select sname,uname from student ,subject where uid in (select muid from mark where msno=sno)order by sname

 

--2.查询年龄在-23岁之间的学生姓名，系别和年龄

  select 学生姓名=sName,系别=dName,年龄=age  from student join department on student.department=department.dShortName where age between 20 and 23

 

--3.查询信息系（is）,数学系（MA）和计算机科学系（cs）学生的姓名和性别

select 姓名=sName,性别=Sex ,系名=dName from  student inner join department

 on dshortName =department where department  in (select dshortName from department where  dName in ('信息系','数学系','计算机科学系'))

--或

select sname,sex from student where department='is' or department='cs' or department='ma'

 

--4.查询所有不姓刘的学生的姓名

  select 姓名=sname from student where sname not like '刘%'

 

--5.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列

select * from student order by department,age desc

 

--6.查询选修号课程的学生最高分数

select  最高分数=max(mark) from mark where muid=1

select 课程= uname ,最高分数=(select max(mark) from Mark where muid=1)from subject where uid=1

--或

select 名字=(select sName from student where sNo=(select msNo from Mark where Mark=(select max(mark) from Mark where muid=1))),

      课程= uname,

      最高分数=(select max(mark) from Mark where muid=1)from subject where uid=1

 

--7.查询选修了门以上课程的学生学号

select 学号=msno,选修课程的次数=count(msno) from Mark group by msno having count(msno)>=3

--或

select 学号=sno,姓名=sname from student where sno in(select msno from mark group by msno having count(msno)>=3)

 

 

--9.以student表为主体列出每个学生的基本情况及其选课情况

select  UName , MsNO  into newstudent from subject ,Mark where MUId=UId

select sNO, sName, age ,department, Uname from student right join newstudent on sNO=MsNO

--或

select sno,sname,age,sex,mark,uname from student  s join mark on s.sno=mark.msno join subject on mark.muid=subject.uid

--或

select 学号=sno,姓名=sname,性别=sex,课程=uname from student,subject where subject.uid in (select muid from mark where msno=sno)

 

--10.查询选修号课程且成绩有分及分以上的所有学生

select * from student where sno in (select msno from mark where muid=2 and mark>=90)

--或

select *,score=(select mark from mark where mark>=90 and muid=2) from student where sno in (select msno from mark where muid=2 and mark>=90)

 

--11.查询每个学生的学号，姓名，选修的课程名及成绩

select 学号=sno, 姓名=sName,选修课程=uName,成绩=mark from student  join mark on student.sno=Mark.msNO join subject on Mark.muid=subject.Uid

 

--12.查询与"刘晨"在同一个系学习的学生

select 同一个系的同学有=sname from student where department in (select department from student where sname='刘晨') and sname<>'刘晨'

 

--13.查询选修了课程名为"音乐"的学生学号和姓名

select 学号=sno ,姓名=sname from student where sno in(select msno from mark where muid in (select uid from subject where uname='音乐'))

 

--14.查询所有选修了号课程的学生姓名

 select 姓名=sname from student where sno in(select msno from Mark where muid=1)   

 

--15.将学生s25108的年龄改为岁

  update student set age=7 where sno='s25108'

 

--16.查询选修了全部课程的学生姓名

select 姓名=sname from student where sno in(select msno from mark group by msno having count(*)=(select count(*) from subject))

select sname,sno,sex from student where sno in(select msno from mark group by msno having count(msno)=(select count(*) from subject))

--或

select sname,sno from student join mark on mark.msno =student.sno group by sname,sno having count(sname)=(select count(*) from subject)

 

--17.查询至少选修了,学生s25105选修的全部课程的学生号码

 

select msno from mark where

muid in (select muid from mark where msno='s25105') and msno<>'s25105'

group by msno having count(*)>=(select count(*)

from mark where msno='s25105' group by msno)

 

 

select* from department

select* from subject

select* from student

select* from Mark

 

 

select * from mark where msno='s25105'

 

select MsNO from Mark where MUId  in (select MUId from Mark where  MsNo='s25105' )and MsNO<>'s25105'

 

select sno from student where sno in (select msno from mark where muid in (select muid from mark where msno='s25105' ) and msno<>'s25105')

 

select msno from mark where muid in (select count(muid) from mark where msno='s25105') and msno<>'s25105'

 

 

 

 

--18.查询选修了课程或者选修了课程的学生的学号

select 学号=msno from mark where muid=1 or muid=2 group by msno

--或

select 姓名=sname,学号=sno from student  where sno  in(select msno from mark where muid=1 or muid=2 group by msno)