//数据查询

create database JXGL; //创建数据库

use JXGL;//使用数据库

create table S(

Sno char(10) not null unique,

Sname char(20) not null unique,

Ssex char(2),

Sage int,

Sdept char(20));

create table C(

Cno char(2) not  null primary key (Cno),

Cname char(20),

Teacher char(20));

create table Sc(

Sno char(10) not null,

Cno char(2) not null,

Grade smallint);

 

insert into S values(‘200215121′,’李勇’,’男’,20,’CS’);

insert into S values(‘200215122′,’刘晨’,’女’,19,’CS’);

insert into S values(‘200215123′,’王敏’,’女’,18,’MA’);

insert into S values(‘200215124′,’张立’,’男’,19,’IS’);

insert into C values(‘2′,’数学’,’张三’);

insert into C values(‘6′,’数据处理’,’张三’);

insert into C values(‘4′,’操作系统’,’张三’);

insert into C values(‘7′,’PASCAL’  ,’张三’);

insert into C values(‘5′,’数据结构’,’李四’);

insert into C values(‘1′,’数据库’  ,’李四’);

insert into C values(‘3′,’信息系统’,’王五’);

insert into Sc values (‘200215121′,’1’,92);

insert into Sc values(‘200215121′,’2’,85);

insert into Sc values(‘200215121′,’3’,88);

insert into Sc values(‘200215122′,’2’,90);

insert into Sc values(‘200215122′,’3’,80);

insert into Sc values(‘200215122′,’1’,null);

insert into Sc values (‘200215124′,’1’,89);

insert into Sc values(‘200215124′,’5’,90);

insert into Sc Values(‘200215124′,’3’,92);

/**  

S表示学生,它的各属性依次为 学号、姓名、年龄、性别、系;

SC表示成绩,它的各属性依次为 学号、课程号和分数;

C表示课程,它的各属性依次为 课程号、课程名和任课教师。

  S(Sno,Sname,Sage,Ssex,Sdept)

  SC(Sno,Cno,Grade)

  C(Cno,Cname,Teacher)

*/

//1.查询学生选课表中的全部数据

select * from  C;

//2.查询CS系学生的姓名,年龄

select Sname,Sage from S where Sdept=’CS’;

//3.查询成绩在70~80分之间的学生的学号,课程号和成绩

select Sno,Cno,grade from Sc where Grade>=70 and Grade<=80;

//4.查询CS系年龄在18~20之间且性别为”男”的学生的姓名和年龄

select Sname,Sage from S where Ssex=’男’ and Sage between 18 and 20;

alter table S add constraint Ssex check (Ssex in(‘男’,’女’));

select Sname,Sage from S where Ssex not like’女’  and Sage in(18,19,20
);

//5.查询课程号为”C01″的课程的最高分数 select MAX(Grade) from Sc where
Cno=’Co1′;

//6.查询CS系学生的最大年龄和最小年龄

select min(Sage)最小年龄,max(Sage)最大年龄 from S where  Sdept=’CS’;

//7.统计每个系的学生人数

select count(*) 人数 from S where Sdept=’CS’;

//8.统计每门课程的选课人数和考试最高分

select Count(*) 选课人数, max(Grade) 最高分 from Sc,C where
Sc.Cno=C.Cno group by C.Cno ;

//9.查询每个学生的选课门数和考试总成绩,并按选课门数的升序显示结果

select count(Sno) ,sum(Grade)from Sc group by Sno order by count(Sno)
asc;

//10.查询总成绩超过200分的学生,要求列出学号,总成绩

select Sc.Sno 学号,sum(Grade) 总成绩 from Sc group by Sno having
sum(Grade)>=200;


//11.查询选修了”1″的学生的姓名和所在系

select S.Sname,Sdept from S,Sc where Sc.Sno=S.Sno and Cno=’1′;

//12.查询成绩在80分以上的学生的姓名,课程号和成绩,并按成绩的降序排列

select Sname,Sc.Cno,Grade from S,Sc where Grade>80 and Sc.Sno=S.Sno
order by Grade desc;

//13.查询那些课程没有人选修.要求列出课程号和课程名(not in)

select Cno,Cname from C where Cno not in (select Cno from Sc);

/*

//14.要求用子查询  

(1).查询选修了课程”2″的学生的姓名和所在系。

  (2).查询IS系成绩在80分以上的学生的学号,姓名。

*/

select Sname,Sdept from S where Sno in (select  Sno from Sc where
Cno=’2′);

select Sno,Sname from S where Sdept =’IS’ and Sno in (select Sno from Sc
where Grade>=80);

//15.求数学系学生的学号和姓名

select Sno,Sname from S where Sdept =’MA’;

//16.求选修了课程的学生学号

select distinct Sno from Sc where Cno in (select Cno from C);

//17.求选修了数学课的学生学号和成绩,并要求对查询结果按成绩降序排列,如果成绩相同则按学号升序排列

select S.Sno,Grade from S,Sc,C where Cname=’数学’ and C.Cno=Sc.Cno and
S.Sno=Sc.Sno order by Grade desc,S.Sno asc;

//18.求选修数学课其且成绩在80-90之间的学生学号和成绩,并将成绩乘以系数0.8输出。

select S.Sno,Grade,Grade*0.8 from S,Sc,c where Cname=’数学’ and Grade
between 80 and 90 and C.Cno=Sc.Cno and Sc.Sno=S.Sno;

//19.求CS系或IS系并且姓刘的学生的信息。

select * from S where Sdept in(‘CS’,’IS’) and Sname like ‘刘%’;

//20.求缺少了成绩的学生的学号和课程号(is null)。

select S.Sno,Cno from S,Sc where Grade is null and Sc.Sno=S.sno;

//21 .查询每个学生的情况以及他(她)所选修的课程。

select S.Sno,Sname,Ssex,Sage,Sdept,Cname,Grade from S,C,Sc where
S.Sno=Sc.Sno and C.Cno=Sc.Cno;

//22.求学生的学号、姓名、选修的课程名及成绩

select S.Sno,Sname,C.Cno,Grade from S,Sc,C where S.Sno=Sc.Sno and
Sc.Cno=C.Cno;

//23.求选修数学课且成绩为90分以上的学生学号、姓名、及成绩。

select S.Sno,Sname,Grade from S,Sc,C where Grade>=90 and Cname=’数学’
and S.Sno=Sc.Sno and Sc.Cno=C.cno;

//24.查询选修课程包含王一老师所授课程的学生学号

select S.Sno from S,C,Sc where Teacher like ‘王%’ and S.Sno=Sc.Sno and
Sc.Cno=C.Cno;

//25.查询王一老师所授课程的课程号和课程名。

select C.Cno,Cname from C where Teacher like ‘王%’;

//26.查询学号为’200215121’学生所学课程的课程名与任课教师。

select Cname,Teacher from S,C,Sc where S.Sno=’200215121′ and
S.Sno=Sc.Sno and Sc.Cno=C.Cno;

//27.查询至少选修王一老师所授课程中一门课程的女学生姓名(=any)。

select Sname from S,Sc,C where Teacher like ‘王%’ and Ssex=’女’and 
S.Sno=Sc.Sno and Sc.Cno= C.Cno;

select Sname from S,Sc where Ssex=’女’ and S.Sno=Sc.Sno and Sc.Cno=any(
select Cno from C where Teacher like ‘王%’);

//28.查询姓张同学没有选修的课程的课程号(not int)。

 select Cno from C where Cno not in (Select Cno from Sc,S where
Sc.Sno=S.Sno and Sname like ‘张%’);

//29.查询至少选修两门课程的学生学号。

select Sno from Sc  group by Sno having Count(cno)>2;

//30.查询全部学生都选项修的课程的课程号与课程名。

select C.Cno,Cname from Sc,C where (select distinct count(Sno) from Sc
group by Sno)=any(select count(Cno) from Sc )and Sc.Cno=C.Cno
;(这条语句错误) select Cno,Cname from C where Cno in (Select Sc.Cno from
Sc,c where Sc.Sno=C.Cno group by Sc.Cno having count(*)=(select
count(*) from S));

select cno,cname from c where cno in(select sc.cno from sc,c where
sc.cno=c.cno group by sc.cno having count(*)=(select count(*) from s);

 

希望可以和大家多交流交流!!

相关文章