mysql綜合性練習-有解無憂

題目描述

設定有一個資料庫，里面有4張表：

學生表（student）
課程表（course）
成績表（score）
教師資訊表（teacher）

表結構如下：

表一_學生表（student）

屬性名	資料型別	含義
sno	int	學號(主鍵)
sname	varchar	學生姓名
ssex	varchar	學生性別
sbirthday	date	學生出生年月
classid	varchar	學生所在班級

表二_課程表（course）

屬性名	資料型別	含義
cno	varchar	課程號(主鍵)
cname	varchar	課程名稱
tno	int	教師編號(外鍵)

表三_成績表（score）

屬性名	資料型別	含義
sno	int	學號(外鍵)
cno	varchar	課程號(外鍵)
sdegree	float	成績

表四_教師表（teacher）

屬性名	資料型別	含義
tno	int	編號（主鍵）
tname	varchar	教師姓名
tsex	varchar	教師性別
tbirthday	date	教師出生年月
professional	varchar	職稱
department	varchar	教師所在部門

二、創建資料庫和表

先創建資料庫mysql_test，并且設定這個資料庫的編碼字符集為utf-8，

學生表：

create table student(
    sno  int(10),
    sname varchar(10),
    ssex varchar(10),
    sbirthday date,
    classid varchar(10)
);

alter table student add primary key(sno);

教師表：

create table teacher(
    tno int(10),
    tname varchar(12),
    tsex varchar(10),
    tbirthday date,
    professional varchar(20),
    department varchar(20)
);

alter table teacher add primary key(tno);

課程表：

create table course(
    cno varchar(10),
    cname varchar(20),
    tno int(10)
);

alter table course add primary key(cno);
alter table course add constraint fk_course_teacher foreign key(tno) references teacher(tno);

成績表：

create table score(
    sno int(10),
    cno varchar(10),
    sdegree float(5)
);

alter table score add constraint fk_score_student foreign key(sno) references student(sno);
alter table score add constraint fk_score_course foreign key(cno) references course(cno);

三、添加資料

insert into student (sno,sname,ssex,sbirthday,classid) 
values(108,'曾華','男','1996-9-1','95033'),(105,'匡明','男','1995-10-2','95031'),
(107,'王麗','女','1996-1-23','95033'),(101,'李軍','男','1996-2-20','95033'),
(109,'王芳','女','1995-2-10','95031'),(103,'陸君','男','1994-6-3','95031');


insert into teacher(tno,tname,tsex,tbirthday,professional,department) 
values(804,'李誠','男','1978-12-2','副教授','計算機系'),
(856,'張旭','男','1979-3-12','講師','電子工程系'),
(825,'王萍','女','1982-5-5','助教','計算機系'),
(831,'劉冰','女','1984-8-14','助教','電子工程系');


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


insert into score(sno,cno,sdegree) values
(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);

學生表：

教師表：

課程表：

成績表：

四、SQL題目

1、查詢所有學生的姓名,性別及班級編號

2、查詢教師所有的部門名稱(即不重復的DEPART列)

3、查詢student表的所有記錄

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

5、查詢score表中成績為85,86或88的記錄

6、查詢student表中班級編號為"95031"或性別為"女"的同學記錄

7、查詢student表的所有記錄,以班級編號降序排列

8、查詢score表的所有記錄,先按照cno升序排列,若相同則按照sdegree降序排列

9、查詢班級編號為"95031"班級的學生人數

10、查詢score表中的最高分的學生學號和課程號

11、查詢編號為"3-105"的課程平均分

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

13、查詢每科成績均在70到90之間的學生編號

注意：having 后面只能跟聚合函式，如avg、min、max、sum等聚合函式

14、查詢所有學生的姓名,所選課程編號和成績

15、查詢所有學生的學號,所選課程名和成績

16、查詢所有學生的姓名,所選課程名和成績

17、查詢"95033"班同學所選每個課程的平均分

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

CREATE TABLE GRADE(
LOW INT(3),
UPP INT(3),
RANK VARCHAR(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');

– 現查詢所有同學的學號,所選課程號和等級(RANK列)