mysql綜合性練習

題目描述

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

• 學生表（student）
• 課程表（course）
• 成績表（score）
• 教師信息表（teacher）

表結構如下：

表一_學生表（student）

表二_課程表（course）

表三_成績表（score）

表四_教師表（teacher）

二、創建資料庫和表

先創建資料庫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列)

 19、查詢"3-105"課程的成績高於學號為109同學這科成績的所有同學記錄

 20、查詢1995年之後出生的所有學生所選學的課程名及成績

 21、查詢與學號108同學同年出生的所有學生的學號,姓名和生日

 22、查詢"張旭"教師任課的學生成績

 23、查詢選修某課程的同學人數多於5人的教師姓名

 24、查詢"95033"班和"95031"班全體學生的記錄

 25、查詢存在有85分以上成績的課程編號

 26、查詢出"電腦系"教師所教課程每一科成績的平均分

 27、查詢"電腦系"與"電子工程系"不同職稱的教師的姓名和職稱

28、查詢所有教師和同學的姓名,性別和生日

 29、查詢所有"女"教師和"女"同學的姓名,性別和生日

 30、查詢所有任課教師的姓名和部門

 31、查詢所有未講課的教師的姓名和部門

 32、查詢student表中不姓"王"的同學記錄

 33、查詢student表中每個學生的姓名和年齡

 34、查詢student表中最大和最小的生日值

 35、查詢student表中的全部記錄,按照班級編號降序排列,班級編號若相同則按照生日降序排列

 36、查詢所有"男"教師及其所上的課程名稱

 37、查詢和"李軍"同性別並同班的同學信息

 38、查詢所有選修"電腦導論"課程的"男"同學的成績表

 39、查詢出同時選擇"3-245"和"3-105"的課程的學生信息

40、查詢既沒有選修"3-245"課程也沒有選修"3-105"課程的學生信息