-- student 學員資料表
-- course 學員各科成績
select *from course
select * from student
--** 單純 23 學員中 每人考三科, 有一學員 3科都沒考(23號) , 沒登記分數 非登記成 零分
--** 難一點則 10號學員只考兩科 這時 平均分數 就會有爭議
--ch3
--(done)查出分數(score) 大於90分以上的學員(不管那一科)
select id,name,score
from course
where score >90
select course .id,course.name ,course.score
from course
where course.score >90
--(done)查出課程名稱(name) 為 JAVA 且分數(score) 介於85分及90分之間的學員
select id,name,score
from course
where name = 'Java' and (score between 85 and 90)
select*
from course
where course .name ='java'
and course .score between 85 and 90
--(done)列出學員各科分數,從高到低排序(不管那一科)
--注意要降序
select id,name,score
from course
order by score desc
select *
from course
order by course .score desc
--(done)列出學員各科分數,先依課程名稱(name)排序, 相同名稱再依分數從高到低排列
select *
from course
order by name ,score desc
select *
from course
order by course .name , course .score desc
--ch4
--(done)查出 平均 的分數是幾分 (不管那一科)
select avg(score)
from course
select AVG(score ) as 平均
from course
--(done)依各科(name)分組, 列出各科(name)最高 的分數是幾分
select name,max(score)
from course
group by name
select name as 科目, max(score ) as 最高分
from course
group by name
--(done)依學號(id)分組, 列出各人總分
select id,sum(score)總分
from course
group by id
select id as 學號, sum(score )as 總分
from course
group by id
--(done)依學號(id)分組, 列出各人總分 大於 250 以上者 (列出學號,總分)
select id,sum(score)
from course
group by id
having sum(score)>250
select id as 學號, sum(score )as 總分
from course
group by id
having sum(score )>250
--ch5
--列出學員姓名及各科成績
select c.id,s.name,c.name,c.score
from course c join student s
on c.id = s.id
--列出學員姓名及各科成績 (未參加考試的學員也要列出)
select c.id,s.name,c.name,c.score
from course c full join student s
on c.id = s.id
--ch6
--依學號(id)分組, 計算學員總分最高者 (含學號,總分)
select top 1 s.id,s.name,sum(c.score)總分
from course c join student s
on c.id = s.id
group by s.id,s.name
order by sum(c.score)desc
--查出學員任一科分數大於總平均的有那些 (含學號,個人分數)
select s.id,s.name,c.score
from course c join student s
on c.id = s.id
where score > (select avg(score)
from course)
--查出學員任一科分數大於總平均的有那些 (含學號,個人分數,平均分數)
select s.id,s.name,c.score,(select avg(score)from course)平均
from course c join student s
on c.id = s.id
where score > (select avg(score)from course)
--進階
--ch45
--列出學員姓名及總分
select s.name,sum(score)
from course c join student s
on c.id = s.id
group by s.name
--列出學員學號,姓名及總分
select s.id,s.name,sum(score)
from course c join student s
on c.id = s.id
group by s.id,s.name
--查出學員總分數最高 (含姓名,總分)
select top 1 s.name,sum(score)
from course c join student s
on c.id = s.id
group by s.id,s.name
order by sum(score)desc
--查出學員總分數最高 (含學號,姓名,總分)
select top 1 s.id,s.name,sum(score)
from course c join student s
on c.id = s.id
group by s.id,s.name
order by sum(score)desc
--ch456
--查出學員任一科分數大於總平均的有那些 (含姓名,個人分數)
select avg(score)
from course
select c.id,s.name,c.name,c.score,(select avg(score) from course)total
from course c join student s
on c.id = s.id
where c.score>(select avg(score) from course)
order by c.id
--查出學員各科分數大於該科平均的有那些 (含姓名,個人分數)
select s.id,s.name,c.name,score,(select avg(score)
from course
where name = c.name)avgs
from course c join student s
on c.id = s.id
where c.score>(select avg(score)avgs
from course
where name = c.name)
select name,avg(score)
from course
group by name
--ch8
--PIVOT 將學員各科成績轉成 PIVOT欄位 (含學號,姓名,各科分數 3個欄位)
select s.id,s.name,c.Java,c.[T-SQL],c.電概
from student s left join (select *from course
pivot (sum(score) for name in ([Java],[電概],[T-SQL]))pio)c
on s.id = c.id
order by s.id
select *
from course
pivot (sum(score) for name in ([Java],[電概],[T-SQL]))pio
use DB01 --若 定序非 Chinese_Taiwan 則另建 DB
go
drop table course;
drop table student;
go
create table student
( id int PRIMARY KEY, --學號
name nchar(20), --姓名
bdate date,
tel char(20)
);
create table course
( id int , --學號
name char(20), --課名
score int, --分數
FOREIGN KEY(id) REFERENCES student(id)
);
go
insert into student values ( 1, 'a','1983-04-21','092055');
insert into student values ( 2, 'b','1983-09-23','092055');
insert into student values ( 3, 'c' ,'1983-08-25','092055');
insert into student values ( 4, 'd','1983-07-27','0920553');
insert into student values ( 5, 'e','1983-05-29','09206789');
insert into student values ( 6,' f','1983-06-22','09206789');
insert into student values ( 7, 'g','1983-03-24','09556786');
insert into student values ( 8, 'h','1983-01-26','092055');
insert into student values ( 9, 'i','1983-02-28','09206789');
insert into student values (10, 'j','1983-11-20','0920582');
insert into student values (11, 'k','1983-10-21','09205589');
insert into student values (12, l','1983-04-23','092055');
insert into student values (13, 'm','1983-05-25','092055');
insert into student values (14, 'n','1983-07-23','0920555');
insert into student values (15, 'o','1983-02-27','09203');
insert into student values (16, 'p','1983-04-23','092059');
insert into student values (17, 'q','1983-06-29','092081');
insert into student values (18, 'r','1983-04-22','092050');
insert into student values (19, 's','1983-08-24','092059');
insert into student values (20, 't','1983-04-26','087');
insert into student values (21, 'u','1983-09-28','0920559');
insert into student values (22, 'v','1983-04-20','09205589');
insert into student values (23, 'w','1983-05-25','0988');
go
select name, id, tel from student order by name
insert into course values ( 1,'JAVA',85);
insert into course values ( 2,'JAVA',86);
insert into course values ( 3,'JAVA',80);
insert into course values ( 4,'JAVA',85);
insert into course values ( 5,'JAVA',75);
insert into course values ( 6,'JAVA',70);
insert into course values ( 7,'JAVA',92);
insert into course values ( 8,'JAVA',85);
insert into course values ( 9,'JAVA',83);
insert into course values (11,'JAVA',87);
insert into course values (12,'JAVA',85);
insert into course values (13,'JAVA',86);
insert into course values (14,'JAVA',85);
insert into course values (15,'JAVA',84);
insert into course values (16,'JAVA',75);
insert into course values (17,'JAVA',81);
insert into course values (18,'JAVA',85);
insert into course values (19,'JAVA',79);
insert into course values (20,'JAVA',85);
insert into course values (21,'JAVA',78);
insert into course values (22,'JAVA',85);
go
insert into course values ( 1,'電概',81);
insert into course values ( 2,'電概',80);
insert into course values ( 3,'電概',82);
insert into course values ( 4,'電概',84);
insert into course values ( 5,'電概',64);
insert into course values ( 6,'電概',83);
insert into course values ( 7,'電概',85);
insert into course values ( 8,'電概',87);
insert into course values ( 9,'電概',86);
insert into course values (10,'電概',85);
insert into course values (11,'電概',82);
insert into course values (12,'電概',81);
insert into course values (13,'電概',89);
insert into course values (14,'電概',85);
insert into course values (15,'電概',88);
insert into course values (16,'電概',87);
insert into course values (17,'電概',86);
insert into course values (18,'電概',85);
insert into course values (19,'電概',84);
insert into course values (20,'電概',82);
insert into course values (21,'電概',85);
insert into course values (22,'電概',81);
go
insert into course values ( 1,'T-SQL',85);
insert into course values ( 2,'T-SQL',85);
insert into course values ( 3,'T-SQL',80);
insert into course values ( 4,'T-SQL',85);
insert into course values ( 5,'T-SQL',85);
insert into course values ( 6,'T-SQL',83);
insert into course values ( 7,'T-SQL',85);
insert into course values ( 8,'T-SQL',82);
insert into course values ( 9,'T-SQL',91);
insert into course values (10,'T-SQL',85);
insert into course values (11,'T-SQL',85);
insert into course values (12,'T-SQL',87);
insert into course values (13,'T-SQL',85);
insert into course values (14,'T-SQL',85);
insert into course values (15,'T-SQL',86);
insert into course values (16,'T-SQL',85);
insert into course values (17,'T-SQL',85);
insert into course values (18,'T-SQL',89);
insert into course values (19,'T-SQL',85);
insert into course values (20,'T-SQL',88);
insert into course values (21,'T-SQL',85);
insert into course values (22,'T-SQL',84);
go
留言列表
【前端網頁開發技術】 (4)
