MySQL练习题~45道

_sunShine女孩

已于 2022-04-12 18:08:15 修改

阅读量2.1k

点赞数 3

分类专栏：软件测试文章标签： sql mysql

于 2022-04-12 18:01:44 首次发布

本文链接：https://blog.csdn.net/MYgirlHHH/article/details/124081377

版权

本文提供了一组关于MySQL的练习题目，涵盖了基础查询、联查、聚合函数等多个方面，包括查询特定列、去重、排序、条件筛选等内容。通过这些题目，可以提升对SQL操作的理解和应用能力。

摘要由CSDN通过智能技术生成

创建表并添加数据

-- 经典SQL练习题

CREATE TABLE STUDENT8
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5));

CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL);

CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL);

CREATE TABLE TEACHER
(TNO VARCHAR(3) NOT NULL,
TNAME VARCHAR(4) NOT NULL,
TSEX VARCHAR(2) NOT NULL, 
TBIRTHDAY DATETIME NOT NULL,
PROF VARCHAR(6),
DEPART VARCHAR(10) NOT NULL);

INSERT INTO STUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES(108,'曾华','男' ,'1977-09-01',95033);
INSERT INTO STUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES(105,'匡明','男' ,'1975-10-02',95031);
INSERT INTO STUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES(107 ,'王丽','女','1976-01-23',95033);
INSERT INTO STUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES(101,'李军','男','1976-02-20',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES(109 ,'王芳','女','1975-02-10',95031);
INSERT INTO STUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(103 ,'陆君','男','1974-06-03',95031);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105','计算机导论',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245','操作系统' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166','数据电路' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888','高等数学',100);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
INSERT INTO sCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',