//數據查詢

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);

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