雲想衣裳花想容，春風拂檻露華濃。 若非群玉山頭見，會向瑤臺月下逢。 現在有一教學管理系統，具體的關係模式如下： Student (no, name, sex, birthday, class) Teacher (no, name, sex, birthday, prof, depart) Cours ...
雲想衣裳花想容，春風拂檻露華濃。
若非群玉山頭見，會向瑤臺月下逢。
現在有一教學管理系統，具體的關係模式如下：
Student (no, name, sex, birthday, class)
Teacher (no, name, sex, birthday, prof, depart)
Course (cno, cname, tno)
Score (no, cno, degree)
其中表中包含如下數據：
Course表：
Score表：
Student表：
Teacher表：
根據上面描述完成下麵問題：
（註意：註意保存腳本，尤其是DDL和DML，以便進行數據還原）
DDL
1.寫出上述表的建表語句。
命令：
create table Student (no int, name varchar(30), sex char(2), birthday datetime, class int);
create table Teacher (no int, name varchar(30), sex char(2), birthday datetime, prof varchar(20), depart varchar(30));
create table Course (cno varchar(10), cname varchar(30), tno int);
create table Score (no int, cno varchar(10), degree float);
DML
2.給出相應的INSERT語句來完成題中給出數據的插入。
命令：
insert into Student values(5001,'李勇','男','1987-07-22 00:00:00.000',95001);
insert into Student values(5002,'劉晨','女','1987-11-15 00:00:00.000',95002);
insert into Student values(5003,'王敏','女','1987-10-5 00:00:00.000',95001);
insert into Student values(5004,'李好尚','男','1987-9-25 00:00:00.000',95003);
insert into Student values(5005,'李軍','男','1987-7-17 00:00:00.000',95004);
insert into Student values(5006,'範新位','女','1987-6-18 00:00:00.000',95005);
insert into Student values(5007,'張霞東','女','1987-8-29 00:00:00.000',95006);
insert into Student values(5008,'趙薇','男','1987-6-15 00:00:00.000',95007);
insert into Student values(5009,'錢民將','女','1987-6-23 00:00:00.000',95008);
insert into Student values(5010,'孫儷','女','1987-9-24 00:00:00.000',95002);
insert into Student values(108,'趙里','男','1987-6-15 00:00:00.000',95007);
insert into Student values(109,'丘處機','男','1987-6-23 00:00:00.000',95008);
insert into Student values(107,'楊康','男','1987-9-24 00:00:00.000',95001);
insert into Teacher values('1','李衛','男','1957-11-5','教授','電子工程');
insert into Teacher values('2','劉備','男','1967-10-9','副教授','math');
insert into Teacher values('3','關羽','男','1977-9-20','講師','cs');
insert into Teacher values('4','李修','男','1957-6-25','教授','elec');
insert into Teacher values('5','諸葛亮','男','1977-6-15','教授','電腦系');
insert into Teacher values('6','殷素素','女','1967-1-5','副教授','cs');
insert into Teacher values('7','周芷若','女','1947-2-23','教授','cs');
insert into Teacher values('8','趙雲','男','1980-6-15','副教授','電腦系');
insert into Teacher values('9','張敏','女','1985-5-5','助教','cs');
insert into Teacher values('10','黃蓉','女','1967-3-22','副教授','cs');
insert into Teacher values('11','張三','男','1967-3-22','副教授','cs');
insert into Course values('3-101','資料庫',1);
insert into Course values('5-102','數學',3);
insert into Course values('3-103','信息系統',4);
insert into Course values('3-104','操作系統',6);
insert into Course values('3-105','數據結構',4);
insert into Course values('3-106','數據處理',5);
insert into Course values('4-107','Pascal語言',5);
insert into Course values('4-108','c++',7);
insert into Course values('4-109','Java',8);
insert into Course values('3-245','數據挖掘',10);
insert into Course values('3-111','軟體工程',11);
insert into Score values(5001,'3-105',69);
insert into Score values(5001,'5-102',55);
insert into Score values(5003,'4-108',85);
insert into Score values(5004,'3-105',77);
insert into Score values(5005,'3-245',100);
insert into Score values(5006,'3-105',53);
insert into Score values(5003,'4-109',45);
insert into Score values(5008,'3-105',98);
insert into Score values(5004,'4-109',68);
insert into Score values(5010,'3-105',88);
insert into Score values(5003,'3-105',98);
insert into Score values(5005,'4-109',68);
insert into Score values(5002,'3-105',88);
insert into Score values(107,'3-105',98);
insert into Score values(108,'4-109',68);
insert into Score values(109,'3-105',88);
insert into Score values(109,'4-109',80);
insert into Score values(107,'3-111',88);
insert into Score values(5003,'3-111',80);
單表查詢
3.以class降序輸出student的所有記錄（student表全部屬性）
命令：
select * from Student order by class desc;
4.列出教師所在的單位depart（不重覆）。
命令：
select count(depart) from Teacher;
5.列出student表中所有記錄的name、sex和class列
命令：
select name, sex, class from Student;
6.輸出student中不姓王的同學的姓名。
命令：
select name from Student where name not like '王%';
7.輸出成績為85或86或88或在60-80之間的記錄（no,cno,degree）
命令：
select no, cno, degree from Score where degree in(85, 86, 88) or degree between 60 and 80;
8.輸出班級為95001或性別為‘女’ 的同學（student表全部屬性）
命令：
select * from Student where class = 95001 or sex = '女';
9.以cno升序、degree降序輸出score的所有記錄。（score表全部屬性）
命令：
select * from Score order by cno asc, degree desc;
10.輸出男生人數及這些男生分佈在多少個班級中
命令：
select count(*), count(distinct class) from Student where sex = '男';
11.列出存在有85分以上成績的課程編號。
命令：
select cno from Score where degree > 85 group by cno;
12.輸出95001班級的學生人數
命令：
select count(*) from Student where class = 95001;
13.輸出‘3-105’號課程的平均分
命令：
select avg(degree) from Score where cno = '3-105';
14.輸出student中最大和最小的birthday日期值
命令：
select max(birthday), min(birthday) from Student;
15.顯示95001和95004班全體學生的全部個人信息（不包括選課）。（student表全部屬性）
命令：
select * from Student where class = 95001 or class = 95004;
聚合查詢
16.輸出至少有5個同學選修的並以3開頭的課程的課程號，課程平均分，課程最高分，課程最低分。
命令：select cno, avg(degree), max(degree), min(degree) from Score where cno like '3%' group by cno having count(*) >= 5;
17.輸出所選修課程中最低分大於70分且最高分小於90分的學生學號及學生姓名
命令：
select no,name from Student where no in(select no from Score group by no having min(degree) > 70 and max(degree) < 90);
18.顯示所教課程選修人數多於5人的教師姓名
命令：
select name from Teacher where no in(select tno from
Course where cno in (select cno from score
group by cno having count(cno) > 5));
19.輸出’95001’班級所選課程的課程號和平均分
命令：select cno, avg(degree) from Score where no in (select no from Student where class = 95001) group by cno;
20.輸出至少有兩名男同學的班級編號。
命令：select class from Student where sex = '男' group by class having count(*) >= 2;
多表查詢
21.列出與108號同學同年出生的所有學生的學號、姓名和生日
命令：select a.no, a.name, a.birthday from Student a, Student b where b.no = 108 and year(b.birthday) = year(a.birthday)
22.列出存在有85分以上成績的課程名稱
命令：
select cname from Course where cno in (select cno from Score where degree > 85 group by cno);
select cname from Course, (select cno from Score where Score.degree > 85 group by cno) t where Course.cno = t.cno;
23.列出“電腦系”教師所教課程的成績表（課程編號，課程名，學生名，成績）。
命令：
select Score.cno, cname, Student.name, degree from Teacher, Course, Student, Score where Teacher.depart = '電腦系' and Teacher.no = Course.tno and Course.cno = Score.cno and Student.no = Score.no;
24.列出所有可能的“電腦系”與“電子工程系”不同職稱的教師配對信息，要求輸出每個老師的姓名（name）和（職稱）
命令：select a.name, a.prof, b.name, b.prof from Teacher a, Teacher b where a.depart in ('電腦系', '電子工程') and b.depart in('電腦系', '電子工程') and a.prof != b.prof and a.depart != b.depart;
25.列出所有處於不同班級中，但具有相同生日的學生，要求輸出每個學生的學號和姓名。（提示：使用datediff函數，具體用法可以參考：http://hcmfys.javaeye.com/blog/588844）
命令：select a.no, a.name, b.no, b.name from Student a, Student b where a.class != b.class and datediff(day, a.birthday, b.birthday) = 0;
26.顯示‘張三’教師任課的學生姓名，課程名，成績
命令：select Student.name, cname, degree from Teacher, Student, Course, Score where Teacher.name = '張三' and Teacher.no = Course.tno and Course.cno = Score.cno and Student.no = Score.no;
27.列出所講課已被選修的教師的姓名和系別
命令：select distinct name, depart from Teacher, Course, Score where Teacher.no = Course.tno and Course.cno = Score.cno;
28.輸出所有學生的name、no和degree。（degree為空的不輸出和為空的輸出兩種情況）。
命令：select name, Student.no, degree from Student, Score where Student.no = Score.no;
select name, Student.no, degree from Student left join Score on Student.no = Score.no;
29.列出所有任課教師的name和depart。（從課程選修和任課兩個角度考慮）
命令：select distinct name, depart from Teacher, Course where Teacher.no = Course.tno;
select distinct name, depart from Teacher, Course, Score where Teacher.no = Course.tno and Score.cno = Course.cno;
30.輸出男教師所上課程名稱。
命令：
select cname from Teacher, Course where Teacher.sex = '男' and Course.tno = Teacher.no;
31.出與“李軍”同性別的所有同學的name。
命令：
select a.name from Student a, Student b where a.sex = b.sex and b.name = '李軍';
32.輸出選修“數據結構”課程的男同學的成績。
命令：
select degree from Score, Course, Student where Score.cno = Course.cno and Course.cname = '數據結構' and Student.no = Score.no and Student.sex = '男';
33.列出選修編號為‘3-105’課程且該門課程成績比課程‘3-111’的最高分要高的cno,no和degree。
命令：select Score.cno, no, degree from Score, (select cno, max(degree) maxdegree from Score group by cno) t where Score.cno = '3-105' and t.cno = '3-111' and Score.degree > t.maxdegree;
子查詢
34.輸出score中成績最高的學號和課程號
命令：select no, cno from Score where degree = (select max(degree) from Score);
35.輸出選修3-105課程，其成績高於109號同學在此課程所得成績的所有同學的學號，姓名
命令：select a.no, Student.name from Score a, Score b, Student where a.cno = '3-105' and b.cno = '3-105'and b.no = 109 and a.degree > b.degree and a.no = Student.no;
36.列出成績比該課程平均成績低的同學的學號，成績和該門課的平均成績
命令：
select no, degree, avg_degree from Score, (select cno, avg(degree) avg_degree from Score group by cno) t where Score.degree < t.avg_degree and Score.cno = t.cno;
37.列出沒有實際授課的教師的姓名和系別
命令：select distinct name, depart from Teacher where no not in (select tno from Course, Score where Course.cno = Score.cno);
38.列出選修了編號為‘3-105’課程且其成績高於‘4-109’課程最高成績的同學的 課程編號，學號和成績
命令：
select cno, no, degree from Score where degree > (select max(degree) from Score where cno = '4-109') and cno = '3-105';
39.**列出符合下述條件的所有可能的同學配對（sno1,sname1,sno2,sname2,difference）。其中要求學號為sno1的sname1同學的所學課程的平均分大於學號為sno2的sname2同學的所學課程平均分，兩個同學的課程平均分的差值difference為（sno1同學平均分-sno2同學平均分）
命令：
select a.no, a.name, b.no, b.name, c.avgdegree - d.avgdegree difference from Student a, Student b, (select no, avg(degree) avgdegree from Score group by no) c,(select no, avg(degree) avgdegree from Score group by no) d where a.no = c.no and b.no = d.no and c.avgdegree > d.avgdegree;
