資料庫25個常見查詢

--學生表結構：
create table student(
sno varchar(10) primary key,
sname varchar(20),
sage int,
ssex varchar(5)
);


--教師表結構：
create table teacher(
tno varchar(10) primary key,
tname varchar(20)
);


--課程表結構：
create table course(
cno varchar(10),
cname varchar(20),
tno varchar(20),
constraint pk_course primary key (cno,tno)
);


--成績表結構：
create table sc(
sno varchar(10),
cno varchar(10),
score float,
constraint pk_sc primary key (sno,cno)
);

/*******初始化學生表的資料******/
insert into student values ('s001','張三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吳鵬',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王麗',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','劉玉',21,'男');
insert into student values ('s008','蕭蓉',21,'女');
insert into student values ('s009','陳蕭曉',23,'女');
insert into student values ('s010','陳美',22,'女');

/******************初始化教師表***********************/
insert into teacher values ('t001', '劉陽');
insert into teacher values ('t002', '諶燕');
insert into teacher values ('t003', '胡明星');



/***************初始化課程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');

/***************初始化成績表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');


1、查詢姓“李”的學生的詳細資料
2、查詢20-23歲的男同學有多少個
3、查詢姓“劉”的老師有多少個
4、查詢男生、女生人數（一條SQL）
5、查詢'劉陽'老師所教的課程
6、尋找學員編號為's004','s007','s008'的學生資訊
7、尋找教'Oracle'課程的老師
8、尋找成績低於60分的課程名稱
9、尋找成績良好（分數>80）的學員名稱和課程名稱
10、查詢平均成績大於60 分的同學的學號和平均成績
11、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分
12、查詢同名同姓學生名單，並統計同名人數
13、1981 年出生的學生名單(註：Student 表中Sage 列的類型是number)
14、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列
15、查詢課程名稱為“Oracle”，且分數低於60 的學生姓名和分數
16、查詢所有學生的選課情況；
17、查詢不及格的課程，並按課程號從大到小排列
18、查詢課程編號為c001 且課程成績在80 分以上的學生的學號和姓名；
19、求選了課程的學生人數
20、查詢各個課程及相應的選修人數
21、查詢不同課程成績相同的學生的學號、課程號、學產生績
22、檢索“c004”課程分數小於60，按分數降序排列的同學學號
23、查詢“c001”課程比“c002”課程成績高的所有學生的學號；
24、查詢沒學過“諶燕”老師課的同學的學號、姓名；
25、按各科平均成績從低到高和及格率的百分數從高到低順序

解答：

use test--1、查詢姓“李”的學生的詳細資料select * from  student where sname like '李%' --2、查詢20-23歲的男同學有多少個select count(*) from student where sage>=20 and sage<=23  and ssex='男'--3、查詢姓“劉”的老師有多少個select count(*) from student where sname like '劉%'--4、查詢男生、女生人數（一條SQL）select count(*) from student   group by ssex--5、查詢'劉陽'老師所教的課程select *from  course where tno in(select tno from teacher where tname ='劉陽')--6、尋找學員編號為's004','s007','s008'的學生資訊select * from student  where sno in('s004','s007','s008');--7、尋找教'Oracle'課程的老師select  tname from  teacher where tno in(  select tno from course where cname ='Oracle')--8、尋找成績低於60分的課程名稱select cname from course where  cno in(select cno from sc where score <60)--9、尋找成績良好（分數>80）的學員名稱和課程名稱  select (select sname from student where student.sno=sc.sno)as '姓名' ,        (select cname from course where course.cno=sc.cno)as '課程名稱'   from sc where score>80  --方法二：多表串連   select S.sname,C.cname  from student S,course C,sc      where sc.score>80 and sc.cno=C.cno and sc.sno=S.sno--10、查詢平均成績大於60 分的同學的學號和平均成績select sno , avg(score) as '平均成績' from sc group by sno having avg(score)>60--11、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分select cno ,max(score)as '最高分', min(score) as '最低分' from sc group by cno--12、查詢同名同姓學生名單，並統計同名人數select s1.sname,count(*)as '人數' from student s1,student s2        where s1.sno!=s2.sno and s1.sname=s2.sname      group by s1.sname--13、1991 年出生的學生名單(註：Student 表中Sage 列的類型是number)select sname from student where YEAR(getdate())-sage=1991--14、查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列select cno,AVG(score)as  '平均成績'  from sc group by cno order by AVG(score), cno DESC--15、查詢課程名稱為“Oracle”，且分數低於60 的學生姓名和分數 select (select sname from student where sno=sc.sno) ,score  from sc where cno in(                select cno from course where cname='Oracle')and score > 60--16、查詢所有學生的選課情況；      select (select sname from student where student.sno=sc.sno)as '學生',(select cname from course where course.cno=sc.cno) from sc --17、查詢不及格的課程，並按課程號從大到小排列select cno,score from sc where  score<60 order by cno DESC--18、查詢課程編號為c001 且課程成績在80 分以上的學生的學號和姓名；     select sno, sname from student where sno in(select sno from sc where  cno='c001' and score>80)-- 19、求選了課程的學生人數  select count(*) from student where exists      (select * from sc where sc.sno=student.sno)-- 20、查詢各個課程及相應的選修人數      select cno, count (*)as'人數' from sc group by cno      --21、查詢不同課程成績相同的學生的學號、課程號、學產生績      select S1.sno,S1.cno,S1.score from sc S1,sc S2 where S1.cno!=S2.cno and S1.score=S2.score   --22、檢索“c004”課程分數小於60，按分數降序排列的同學學號     select sno  from sc where score<60 and cno='c004' order by score DESC--23、查詢“c001”課程比“c002”課程成績高的所有學生的學號；       select S1.sno from sc S1,sc S2 where S1.sno=S2.sno and S1.cno='c001'and S2.cno='c002'   --24、查詢沒學過“諶燕”老師課的同學的學號、姓名；      select sno,sname from student where sno not in (               select sno from sc where student.sno=sc.sno and cno in (                select cno from course where tno in(select tno from teacher where tname='諶燕')          )    )   --25、按各科平均成績從低到高 和 及格率的百分數從高到低順序      --isnull(score,0)    --1.各科平均成績      select cno ,AVG(score) as '平均成績'      from sc group by cno order by AVG(score);     --2.及格率的百分數從高到低順序     select  cno,convert(varchar,100*SUM(CASE WHEN  score>=60                      THEN 1 ELSE 0 END)/count(*))+'%' as '及格百分比'      from sc group by cno       order by 100 * SUM(CASE WHEN  score>=60 THEN 1 ELSE 0 END)/count(*) DESC    ---綜合起來      select cno, AVG(score) as '平均成績',                 convert(varchar,100*SUM(CASE WHEN  score>=60                      THEN 1 ELSE 0                     END)/count(*))+'%' as '及格百分比'      from sc group by cno order by AVG(score),           100 * SUM(CASE WHEN  score>=60 THEN 1 ELSE 0 END)/count(*) DESC