學生選課資料練習 (續)

(1)建立資料庫

create database mycourse_20170526
character set utf8
collate utf8_general_ci;

(2)選擇資料庫

use mycourse_20170526;

(3)建立學生選課資料表 : 學生資料表、課程資料表、選課表、老師資料表、科系資料表。

學生資料表 student

create table student (
stud_no char(8) not null,
stud_name char(10),
stud_sex char(1),
stud_tel char(12),
dept_no char(1),
primary key(stud_no),
unique (stud_tel));

課程資料表 course

create table course (
course_no char(5) not null,
course_name char(20),
course_credit int default 3,
teacher_no char(5),
course_type char(1),
dept_no char(1),
primary key (course_no));

選課表 student_course

create table student_course (
stud_no char(8),
course_no char(5),
course_score int not null,
primary key (stud_no, course_no),
foreign key (stud_no) references student (stud_no)
on update cascade
on delete cascade,
foreign key (course_no) references course (course_no)
on update cascade
on delete cascade);

老師資料表 teacher

create table teacher (
teacher_no char(5) not null,
teacher_name char(10),
dept_no char(1),
primary key (teacher_no));

科系資料表 dept

create table dept (
dept_no char(1) not null,
dept_name varchar(30),
primary key (dept_no));

(4) 插入資料

學生資料表 student
insert into student
values
('S001', '李大華', 'M', '02123456789','1'),
('S002', '張曉明', 'M', '222222222','1'),
('S003', '王二中', 'M', '333333333','2'),
('S004', '孫維茜', 'F', '444444444','2'),
('S005', '陳雅東', 'M', '555555555','2'),
('S006', 'John', 'M', '6666666','2');

課程資料表 course

insert into course
values
('C001', '資料庫實務', 3, 'T0001','1','2'),
('C002', '程式設計', 2, 'T0002','1','1'),
('C003', '系統分析', 3, 'T0003','1','1'),
('C004', '數位設計', 3, 'T0004','1','1'),
('C005', '系統分析', 2, 'T0001','0','1'),
('C006', '資料結構', 2, 'T0002','0','1'),
('C007', '統計分析', 2, 'T0003','0','2'),
('C008', '計算機概論', 2, '','1','1');

選課表 student_course

insert into student_course
values
('S001', 'C001', 56),
('S001', 'C005', 73),
('S002', 'C002', 92),
('S002', 'C005', 63),
('S003', 'C004', 92),
('S003', 'C005', 70),
('S004', 'C003', 75),
('S004', 'C004', 88),
('S004', 'C005', 68),
('S005', 'C005', 95),
('S006', 'C001', 90),
('', 'C008', 90),
('S002', 'C001', 70),
('S003', 'C001', 65),
('S004', 'C001', 90),
('S005', 'C001', 81);

老師資料表 teacher

insert into teacher
values
('T0001','AAA','1'),
('T0002','BBB','1'),
('T0003','CCC','1'),
('T0004','DDD','2'),
('T0005','EEE','3');

科系資料表 dept

insert into dept
values
('1', '資管系'),
('2', '資工系'),
('3', '企管系');

執行SELECT >>

(1)

select * from student;

(2)

select stud_no, stud_name from student;

(3)

select * from student, student_course;

(4)
select * from student, student_course
where student.stud_no=student_course.stud_no;

(5)
select student.stud_no, student.stud_name, student_course.course_no from student, student_course
where student.stud_no=student_course.stud_no;

(6)
select a.stud_no, a.stud_name, b.course_no from student a, student_course b

where a.stud_no=b.stud_no;

(7) 以下會有錯誤訊息，為何?
select stud_no,avg(course_score) as s from student_course 
group by stud_no 
order by s desc
having s>70;

(8)
select stud_no,avg(course_score) as s from student_course 
group by stud_no 
having s>70 
order by s desc;

(9)
select stud_no,avg(course_score) as s from student_course 
where stud_no>'S002' 
group by stud_no 

having s>70 
order by s desc;

(10)
select * from course 
where course_no not in (select course_no from student_course);

(11) 

select * from teacher as a 

left outer join course as b 

on a.teacher_no=b.teacher_no; 

(12) 

select * from teacher as a 

left outer join course as b 

on a.teacher_no=b.teacher_no 

where b.teacher_no is null; 

(13) 

select a.teacher_no, a.teacher_name from teacher as a 

left outer join course as b 

on a.teacher_no=b.teacher_no 

where b.teacher_no is null; 

(14) 

select * from teacher as a 

right outer join course as b 

on a.teacher_no=b.teacher_no 

(15) 

select * from teacher as a 

right outer join course as b 

on a.teacher_no=b.teacher_no 

where a.teacher_no is null; 

(16) 

select b.course_no, b.course_name from teacher as a 

right outer join course as b 

on a.teacher_no=b.teacher_no 

where a.teacher_no is null; 

(17) 

select stud_no as no, stud_name as name from student 

union 

select teacher_no, teacher_name from teacher; 

(18) 

select course_name from course as c 

where not exists 

(

select * from student as a 

where not exists 

(

select * from student_course as b 

where c.course_no=b.course_no and a.stud_no=b.stud_no

))

除法格式

select 目標屬性 from 目標表格
where not exists
     (select * from 除式表格
      where not exists
     (select * from 被除式表格

      where 目標表格.合併屬性1=被除式表格.合併屬性1
      and 除式表格.合併屬性2=被除式表格.合併屬性2));