查詢實例及其代碼

一、????????????設有一數據庫，包括四個表：學生表（Student）、課程表（Course）、成績表（Score）以及教師信息表（Teacher）。四個表的結構分別如表1-1的表（一）~表（四）所示，數據如表1-2的表（一）~表（四）所示。用SQL語句創建四個表并完成相關題目。

?????????????????表1-1數據庫的表結構?

表（一）Student?(學生表)?????????????????????????

屬性名	數據類型	可否為空	含?義
Sno	Char(3)	否	學號（主碼）
Sname	Char(8)	否	學生姓名
Ssex	Char(2)	否	學生性別
Sbirthday	datetime	可	學生出生年月
Class	Char(5)	可	學生所在班級

表（二）Course（課程表）

屬性名	數據類型	可否為空	含?義
Cno	Char(5)	否	課程號（主碼）
Cname	Varchar(10)	否	課程名稱
Tno	Char(3)	否	教工編號（外碼）

表（三）Score(成績表)

屬性名	數據類型	可否為空	含?義
Sno	Char(3)	否	學號（外碼）
Cno	Char(5)	否	課程號（外碼）
Degree	Decimal(4,1)	可	成績
主碼：Sno+?Cno

表（四）Teacher(教師表)

屬性名	數據類型	可否為空	含?義
Tno	Char(3)	否	教工編號（主碼）
Tname	Char(4)	否	教工姓名
Tsex	Char(2)	否	教工性別
Tbirthday	datetime	可	教工出生年月
Prof	Char(6)	可	職稱
Depart	Varchar(10)	否	教工所在部門

表1-2數據庫中的數據

表（一）Student

Sno	Sname	Ssex	Sbirthday	class
108	曾華	男	1977-09-01	95033
105	匡明	男	1975-10-02	95031
107	王麗	女	1976-01-23	95033
101	李軍	男	1976-02-20	95033
109	王芳	女	1975-02-10	95031
103	陸君	男	1974-06-03	95031

??

表（二）Course

Cno	Cname	Tno
3-105	計算機導論	825
3-245	操作系統	804
6-166	數字電路	856
9-888	高等數學	831

表（三）Score

Sno	Cno	Degree
103	3-245	86
105	3-245	75
109	3-245	68
103	3-105	92
105	3-105	88
109	3-105	76
101	3-105	64
107	3-105	91
108	3-105	78
101	6-166	85
107	6-166	79
108	6-166	81

表（四）Teacher

Tno	Tname	Tsex	Tbirthday	Prof	Depart
804	李誠	男	1958-12-02	副教授	計算機系
856	張旭	男	1969-03-12	講師	電子工程系
825	王萍	女	1972-05-05	助教	計算機系
831	劉冰	女	1977-08-14	助教	電子工程系

表格代碼：

? ? ??

create database 學生選課數據庫
Create table Student
(
Sno Char(3) not null primary key, #學號
Sname Char(8) not null, #學生姓名
Ssex Char(2) not null, #學生姓名
Sbirthday datetime, #學生出生年月
Class Char(5) #學生所在班級
);

Create table Course
(
Cno Char(5) not null primary key, #課程號
Cname Varchar(10) not null, #課程名稱
Tno Char(3) not null #教工編號
);
Create table Score
(
Sno Char(3) not null references Student(Sno) , #學號
Cno Char(5) not null references Course(Cno), #課程號
Degree Decimal(4,1) #成績
);

create table Teacher
(
Tno Char(3) not null primary key, #教工編號
Tname Char(4) not null, #教工姓名
Tsex Char(2) not null, #教工性別
Tbirthday datetime, # 教工出生年月
Prof Char(6), #職稱
Depart Varchar(10) not null #教工所在部門

);

insert into Student values('108','曾華','男','1977-09-01','95033');
insert into Student values('105','匡明','男','1975-10-02','95031');
insert into Student values('107','王麗','女','1976-01-23','95033');
insert into Student values('101','李軍','男','1976-02-20','95033');
insert into Student values('109','王芳','女','1975-02-10','95031');
insert into Student values('103','陸君','男','1974-06-03','95031');

insert into Course values('3-105','計算機導論','825');
insert into Course values('3-245','操作系統','804');
insert into Course values('6-166','數字電路','856');
insert into Course values('9-888','高等數學','831');

insert into Score values('103','3-245','86');
insert into Score values('105','3-245','75');
insert into Score values('109','3-245','68');
insert into Score values('103','3-105','92');
insert into Score values('105','3-105','88');
insert into Score values('109','3-105','76');
insert into Score values('101','3-105','64');
insert into Score values('107','3-105','91');
insert into Score values('108','3-105','78');
insert into Score values('101','6-166','85');
insert into Score values('107','6-166','79');
insert into Score values('108','6-166','81');

insert into Teacher values('804','李誠','男','1958-12-02','副教授','計算機系');
insert into Teacher values('856','張旭','男','1969-03-12','講師','電子工程系');
insert into Teacher values('825','王萍','女','1972-05-05','助教','計算機系');
insert into Teacher values('831','劉冰','女','1977-08-14','助教','電子工程系');

?

?

1、?查詢Student表中的所有記錄的Sname、Ssex和Class列。

select?Sname,Ssex,Class?from?Student?

2、?查詢教師所有的單位即不重復的Depart列。

select??Depart?from?Teacher

3、?查詢Student表的所有記錄。

select?*?from?Student

4、?查詢Score表中成績在60到80之間的所有記錄。

select?*?from?Score?where?Degree?between?60?and?80

5、?查詢Score表中成績為85，86或88的記錄。

select?*?from?Score?where?Degree='85'??or??Degree=?'86'??or???Degree='88'

Select*?from?Score?where?Degree?in(85,86,88)

6、?查詢Student表中“95031”班或性別為“女”的同學記錄。

select?*?from?Student?where?Class='95031'?or?Ssex='女'

7、?以Class降序查詢Student表的所有記錄。

select?*?from?Student?order?by?Class?desc

8、?以Cno升序、Degree降序查詢Score表的所有記錄。

select?*?from?Score?order?by?Cno?asc,?Degree?desc

9、?查詢“95031”班的學生人數。

select?count(Class)?from?Student?where?Class='95031'

10、??查詢Score表中的最高分的學生學號和課程號。（子查詢或者排序）

select?*?from?Score?order?by?Degree?desc?排序

select?Sno,Cno?from?Score?where?Degree=?(?select?max(Degree)?from?Score)

11、?查詢每門課的平均成績。

select?avg(Degree)?from?Score?group?by?Cno

12、?查詢Score表中至少有5名學生選修的并以3開頭的課程的平均分數。

select?avg(Degree)?from?Score?group?by?Cno?having?count(*)>=5?and?cno?like?'3%'

13、?查詢分數大于70，小于90的Sno列。

select?Sno?from?Score?where?Degree?between?70?and?90

14、?查詢所有學生的Sname、Cno和Degree列。

select?Student.Sname,?Score.Cno,Score.Degree?from?Student,Score?where?Student.Sno?=?Score.Sno?

15、?查詢所有學生的Sno、Cname和Degree列。

select?Score.Sno,Course.Cname,Score.Degree?from?Course,Score?where?Course.Cno?=?Score.Cno?

16、?查詢所有學生的Sname、Cname和Degree列。

select?Student.Sname,Course.Cname,Score.Degree?from?Course,Score,Student?where?Course.Cno?=?Score.Cno?and?Score.Sno?=?Student.Sno

17、??查詢“95033”班學生的平均分。

select?avg(Degree)?from?Score,Student?where?Score.Sno?=?Student.Sno?and?Student.class?=?'95033'

18、?假設使用如下命令建立了一個grade表：

create?table?grade(low??int(3),upp??int(3),rank??char(1))

insert?into?grade?values(90,100,’A’)

insert?into?grade?values(80,89,’B’)

insert?into?grade?values(70,79,’C’)

insert?into?grade?values(60,69,’D’)

insert?into?grade?values(0,59,’E’)

現查詢所有同學的Sno、Cno和rank列。

select?Score.Sno,Score.Cno,grade.rank?from?score,grade?where?Score.Degree?between?grade.low?and?grade.upp

19、??查詢選修“3-105”課程的成績高于“109”號同學成績的所有同學的記錄。

select?*?from?score?where?degree?>(select?degree?from?score?where?sno=?'109'?and?cno?='3-105')?and?cno?='3-105'

20、查詢score中選學多門課程的同學中分數為非最高分成績的記錄。

21、?查詢成績高于學號為“109”、課程號為“3-105”的成績的所有記錄。

22、查詢和學號為108的同學同年出生的所有學生的Sno、Sname和Sbirthday列。

select?sno,sname,sbirthday?from?student?where?year(sbirthday)=(select?year(sbirthday)?from?student?where?sno?='108')

23、查詢“張旭“教師任課的學生成績。

select?*?from?score?where?cno?=(select?cno?from?course?where?tno?=(select?tno?from?teacher?where?tname='張旭'))

24、查詢選修某課程的同學人數多于5人的教師姓名。

select?tname?from??teacher?where?tno?=(select?tno?from?course?where?cno=(select?cno?from?score?group?by?cno?having?count(*)>5))

25、查詢95033班和95031班全體學生的記錄。

select?*?from?student?where?class?in('95033','95031')

26、??查詢存在有85分以上成績的課程Cno.

select??distinct?cno?from?score?where?degree>85

27、查詢出“計算機系“教師所教課程的成績表。

select?degree?from?score?where?cno?in?(select?cno?from?course?where?tno?in(select?tno?from?teacher?where?depart='計算機系'))

28、查詢“計算機系”與“電子工程系“不同職稱的教師的Tname和Prof。

select?tname,prof?from?teacher?where?prof?not?in(Select?prof?from?teacher?where?depart=’計算機系’?and?prof?in(select?prof?from?teacher?where?depart=’電子工程系’))

29、查詢選修編號為“3-105“課程且成績至少高于選修編號為“3-245”的同學的Cno、Sno和Degree,并按Degree從高到低次序排序。

select?Cno,Sno,Degree?from?Score?where?Cno='3-105'?and?Degree>any?(select?Degree?from?Score?where?Cno='3-245')order?by?Degree?desc;

??#?any?任何一個值

30、查詢選修編號為“3-105”且成績高于選修編號為“3-245”課程的同學的Cno、Sno和Degree.

select?Cno,Sno,Degree?from?Score?where?Cno='3-105'?and?Degree>(select?max(Degree)?from?Score?where?Cno='3-245')?order?by?Degree?desc

?

select?Cno,Sno,Degree?from?Score?where?Cno='3-105'?and?Degree>all(select?Degree?from?Score?where?Cno='3-245')??#all?所有值

?

31、?查詢所有教師和同學的name、sex和birthday.

select?Tname?as?name,Tsex?as?sex,Tbirthday?as?birthday?from?Teacher?union?select?Sname,Ssex,Sbirthday?from?Student

?

32、查詢所有“女”教師和“女”同學的name、sex和birthday.

select?Tname,Tsex,Tbirthday?from?Teacher?where?Tsex='女'?union?select?Sname,Ssex,Sbirthday?from?Student?where?Ssex='女'

?

33、?查詢成績比該課程平均成績低的同學的成績表。

select?*?from?Score?a?where?a.Degree<(select?avg(Degree)?from?Score?b?where?b.Cno=a.Cno)

?

34、查詢所有任課教師的Tname和Depart.

select?Tname,Depart?from?Teacher?where?Tno?in(select?Tno?from?Course?where?Teacher.Tno=Course.Tno)

?

select?Tname,Depart?from?Teacher?where?exists(select?Tno?from?Course?where?Teacher.Tno=Course.Tno)??#通常用exists里面select后面跟*

?

35?、?查詢所有未講課的教師的Tname和Depart.?

?

select?Tname,Depart?from?Teacher?where?Tno?not?in(select?Tno?from?Course?where?Teacher.Tno=Course.Tno)

?

select?Tname,Depart?from?Teacher?where?not?exists(select?Tno?from?Course?where?Teacher.Tno=Course.Tno)???#通常用exists里面select后面跟*

?

36、查詢至少有2名男生的班號。

select?class?from?Student?where?Ssex='男'?group?by?class?having?count(Student.Ssex)>=2

?

select?class?from?Student?where?Ssex='男'?group?by?class?having?count(*)>=2???

?

37、查詢Student表中不姓“王”的同學記錄。

select?*?from?Student?where?Sname?not?like?'王%'?

?

38、查詢Student表中每個學生的姓名和年齡。

select?Sname?as?姓名,year(now())-year(Sbirthday)?as?年齡?from?Student

?

39、查詢Student表中最大和最小的Sbirthday日期值。

select?max(Sbirthday),min(Sbirthday)?from?Student?

?

40、以班號和年齡從大到小的順序查詢Student表中的全部記錄。

select?*?from?Student?order?by?class?desc,Sbirthday?desc

?

41、查詢“男”教師及其所上的課程。

select?Teacher.Tname,Teacher.Tsex,Course.Cname?from?Teacher,Course?where?Teacher.Tsex='男'?and?Teacher.Tno=Course.Tno

?

42、查詢最高分同學的Sno、Cno和Degree列。

select?*?from?Score?where?Degree=(select?max(Degree)?from?Score)

?

43、查詢和“李軍”同性別的所有同學的Sname.

select?Sname?from?Student?where?Ssex=(select?Ssex?from?Student?where?Sname='李軍')

?

44、查詢和“李軍”同性別并同班的同學Sname.

select?Sname?from?Student?where?Ssex=(select?Ssex?from?Student?where?Sname='李軍')?and?class=(select?class?from?Student?where?Sname='李軍')

45、查詢所有選修“計算機導論”課程的“男”同學的成績表。

select?*?from?Score

where?Cno?in?(select?Cno?from?Course?where?Cname='計算機導論')?and?Sno?in?(select?Sno?from?Student?where?Ssex='男')

#滿足一個條件可以用‘=’，滿足多個條件用‘in’，所以最好都用‘in’??