说明
此基础练习的题目难度都比较简单,但是还有一些细节需要把握
此题目的主要目的是熟悉SQL语句的基础查询,关于建表方面的语句了解即可,因为一般开发中所用的都是图形化建表,SQL语句建表所用较少。
虽然这些题目比较简单,但是开始中大多数都是这些简单的查询,所以需要我们必须掌握。
建表要求
一、 设有一数据库,包括四个表:学生表(Student)、课程表(Course)、成绩表(Score)以及教师信息表(Teacher)。四个表的结构分别如表1-1的表(一)-表(四)所示
表1-1数据库的表结构
表(一)Student (学生表)
属性名 |
数据类型 |
可否为空 |
含 义 |
STU_ID |
Char(3) |
否 |
学号(主码) |
STU_NAME |
Char(8) |
否 |
学生姓名 |
STU_SEX |
Char(2) |
否 |
学生性别 |
STU_BIRTH |
datetime |
可 |
学生出生年月 |
CLASSES |
Char(5) |
可 |
学生所在班级 |
表(二)Course(课程表)
属性名 |
数据类型 |
可否为空 |
含 义 |
COU_ID |
Char(5) |
否 |
课程号(主码) |
COU_NAME |
Varchar(10) |
否 |
课程名称 |
TEA_ID |
Char(3) |
否 |
教工编号(外码) |
表(三)Score(成绩表)
属性名 |
数据类型 |
可否为空 |
含 义 |
STU_ID |
Char(3) |
否 |
学号(外码) |
COU_ID |
Char(5) |
否 |
课程号(外码) |
DEGREE |
Decimal(4,1) |
可 |
成绩 |
主码: |
STU_ID+ COU_ID |
|
|
表(四)Teacher(教师表)
属性名 |
数据类型 |
可否为空 |
含 义 |
TEA_ID |
Char(3) |
否 |
教工编号 |
TEA_NAME |
Char(4) |
否 |
教工姓名 |
TEA_SEX |
Char(2) |
否 |
教工性别 |
TEA_BIRTH |
datetime |
可 |
教工出生年月 |
TEA_PROF |
Char(6) |
可 |
职称 |
DEPART |
Varchar(10) |
否 |
教工所在部门 |
数据库中数据
student表中数据(学生表)
![在这里插入图片描述](https://img-blog.csdnimg.cn/e5f013d1ddb742d49ceeff77d371cf5c.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5oif5b6h,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center)
score表中数据(成绩表)
![在这里插入图片描述](https://img-blog.csdnimg.cn/7df317b876ec4a18b8c374da47edc74c.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5oif5b6h,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center)
course表中数据(课程表)
![在这里插入图片描述](https://img-blog.csdnimg.cn/f51ecfb73a4c49e5998af6a14a8efe16.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5oif5b6h,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center)
teacher表中数据(老师表)
![在这里插入图片描述](https://img-blog.csdnimg.cn/18a5e0f7fbef46f2b501b4f7a90fa36f.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5oif5b6h,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center)
建表SQL语句
-- 创建Student表、学生表
CREATE TABLE Student(
STU_ID CHAR(3) NOT NULL PRIMARY KEY,
STU_NAME CHAR(8) NOT NULL,
STU_SEX CHAR(2) NOT NULL,
STU_BIRTH DATETIME NULL,
CLASSES CHAR(5) NULL
)
-- 创建Teacher表,教师表
CREATE TABLE Teacher(
TEA_ID CHAR(3) NOT NULL PRIMARY KEY,
TEA_NAME CHAR(4) NOT NULL,
TEA_SEX CHAR(2) NOT NULL,
TEA_BIRTH DATETIME NULL,
TEA_PROF CHAR(6) NULL,
DEPART VARCHAR(10) NOT NULL
)
-- 创建Course表,课程表
CREATE TABLE Course(
COU_ID CHAR(5) NOT NULL PRIMARY KEY,
COU_NAME VARCHAR(10) NOT NULL,
TEA_ID CHAR(3) NOT NULL,
FOREIGN KEY(TEA_ID) REFERENCES Teacher(TEA_ID)
)
-- 创建Score表,成绩表
CREATE TABLE Score(
STU_ID CHAR(3) NOT NULL,
COU_ID CHAR(5) NOT NULL,
DEGREE DECIMAL(4,1) NULL,
PRIMARY KEY(STU_ID,COU_ID),
FOREIGN KEY(STU_ID) REFERENCES Student(STU_ID),
FOREIGN KEY(COU_ID) REFERENCES Course(COU_ID)
)
SQL查询语句问题
1、 查询Student表中的所有记录的STU_NAME、STU_SEX和CLASSES列。
2、 查询教师所有的单位即不重复的DEPART列。
3、 查询Score表中成绩在60到80之间的所有记录。
4、 查询Score表中成绩为85,86或88的记录。
5、 查询Student表中“95031”班或性别为“女”的同学记录。
6、 以COU_ID升序、DEGREE降序查询Score表的所有记录。
7、 查询“95031”班的学生人数。
8、 查询Score表中的最高分的学生学号和课程号。
9、 查询每门课的平均成绩。
10、查询Score表中至少有5名学生选修的课程的平均分数。
11、查询所有学生的STU_NAME、COU_ID和DEGREE列。
12、查询所有学生的STU_NAME、COU_NAME和DEGREE列。
13、 查询“95033”班学生的平均分。
查询问题解答
-- 1、 查询Student表中的所有记录的STU_NAME、STU_SEX和CLASSES列。
SELECT STU_NAME,STU_SEX,CLASSES
FROM student
-- 2、 查询教师所有的单位即不重复的DEPART列。
SELECT DISTINCT DEPART
FROM teacher
-- 3、 查询Score表中成绩在60到80之间的所有记录。
SELECT *
FROM score
WHERE DEGREE >= 60 AND DEGREE <= 80
-- 4、 查询Score表中成绩为85,86或88的记录。
SELECT *
FROM score
WHERE DEGREE IN ('85','86','88')
-- 5、 查询Student表中“95031”班或性别为“女”的同学记录。
SELECT *
FROM student
WHERE CLASSES = '95031' OR STU_SEX = '女'
-- 6、 以COU_ID升序、DEGREE降序查询Score表的所有记录。
SELECT *
FROM score
ORDER BY COU_ID ASC,DEGREE DESC
-- 7、 查询“95031”班的学生人数。
SELECT COUNT(CLASSES) AS 95031number
FROM student
GROUP BY CLASSES HAVING CLASSES='95031'
-- 8、 查询Score表中的最高分的学生学号和课程号。
SELECT STU_ID,COU_ID
FROM score
ORDER BY DEGREE DESC LIMIT 1
-- 9、 查询每门课的平均成绩。
SELECT c.COU_NAME,C_AVG.DEGREE_AVG
FROM course AS c LEFT JOIN
(SELECT COU_ID,AVG(DEGREE) AS DEGREE_AVG
FROM score
GROUP BY COU_ID) AS C_AVG
ON c.COU_ID=C_AVG.COU_ID
-- 10、查询Score表中至少有5名学生选修的课程的平均分数。
SELECT c.COU_NAME,`COUNT(STU_ID)` AS NUMBER,`AVG(DEGREE)` AS `AVG`
FROM course AS c INNER JOIN(
SELECT COU_ID,COUNT(STU_ID),AVG(DEGREE)
FROM score
GROUP BY COU_ID) AS s_avg
ON c.COU_ID=s_avg.COU_ID AND `COUNT(STU_ID)`>=5
-- 11、查询所有学生的STU_NAME、COU_ID和DEGREE列。
SELECT STU_NAME,sc.COU_ID,DEGREE
FROM student st LEFT JOIN score sc
ON st.STU_ID=sc.STU_ID
-- 12、查询所有学生的STU_NAME、COU_NAME和DEGREE列。
SELECT STU_NAME,c.COU_NAME,DEGREE
FROM student st JOIN score sc JOIN course c
ON st.STU_ID=sc.STU_ID AND c.COU_ID=sc.COU_ID
ORDER BY STU_NAME
-- 13、 查询“95033”班学生的平均分。
SELECT CLASSES,AVG(DEGREE),COU_ID
FROM student st JOIN score sc
ON st.STU_ID=sc.STU_ID
GROUP BY COU_ID,CLASSES
HAVING CLASSES='95033'