数据库讲解---(SQL语句--练习题讲解)【MySQL版本】

目录

零.前言

一.例题一

1.1查询姓刘的老师所授课程的课程号和课程名

1.2查询年龄大于23岁的男同学的学号和姓名

1.3查询学号为S3的学生所学课程的课程号、课程名和任课教师姓名

1.4查询“张小飞”没有选修的课程号和课程名

1.5查询至少选修了三门课程的学生的学号和姓名

1.6查询全部学生都选修了的课程编号和课程名称

二.例题二

2.1找出价格高于15000元,并且运行速度低于同价位PC的平均运行速度的LAPTOP

 2.2找出生产价格最低的彩色打印机的厂家

2.3计算由厂家“HP”生产的PC和LAPTOP的平均价格

2.4计算各厂商生产的LAPTOP的显示器的平均尺寸

2.5找出每一个生产厂商的PC的最高价格

2.6计算生产打印机的各个厂商所生产的PC的硬盘的平均容量

零.前言

数据库讲解（MySQL版）（超详细）【第一章】-CSDN博客

数据库-ER图教程_e-r图数据库-CSDN博客

数据库讲解（MySQL版）（超详细）【第二章】【上】-CSDN博客

数据库讲解---(SQL语句--表的使用)【MySQL版本】-CSDN博客\

一.例题一

现在有一个名为：“mathdb”的数据库，该库中有三张表：

S(学号,课程号,姓名,成绩)

SC(学号,课程号,成绩)

C(课程号,课程名,任课教师姓名)

(PS：“下划线标注的属性是主键”)

1.1查询姓刘的老师所授课程的课程号和课程名

SELECT 课程号,课程名
FROM C
WHERE 任课教师姓名 = '刘%';

1.2查询年龄大于23岁的男同学的学号和姓名

SELECT 学号,姓名
FROM S
WHERE 年龄 > 23 AND 性别 = '男';

1.3查询学号为S3的学生所学课程的课程号、课程名和任课教师姓名

SELECT SC.课程号,课程名,任课教师姓名
FROM SC,C
WHERE SC.课程号 = C.课程号 AND 学号 = 'S3';

1.4查询“张小飞”没有选修的课程号和课程名

SELECT 课程号,课程名
FROM C
WHERE 课程号 NOT IN (
    SELECT C.课程号
    FROM S,SC,C
    WHERE S.学号 = SC.学号 AND SC.课程号 = C.课程号 AND S.姓名 = '张小飞'
);

1.5查询至少选修了三门课程的学生的学号和姓名

SELECT 学号,姓名
FROM S
WHERE 学号 IN (
    SELECT 学号
    FROM S
    GROUP BY 学号
    HAVING COUNT(*) >= 3
);

1.6查询全部学生都选修了的课程编号和课程名称

看到“全部”字眼，第一反应用到了“除运算”

使用“除运算”我们要先假设两个集合“A”是某个学生选修的所有课程、“B”是所有学生共同选修的课程，因此A集合包含B集合

使用关系代数表示为：“(B-A)是一个空集”，再形象一点就是：“不存在一个集合是(B-A)”

因此：“B-A用到一个差运算，在MySQL中可以使用NOT EXISTS代替，不存在(空集)也用到一个差运算”

SELECT 课程号,课程名称
FROM C
//不存在(空集)所用到的差运算
WHERE NOT EXISTS(
    SELECT *
    FROM S
    //B - A用到的差运算
    WHERE NOT EXISTS (
        SELECT *
        FROM SC
        WHERE SC.课程号 = C.课程号 AND S.学号 = SC.学号
    )
);

二.例题二

设有下列4个表：

PRODUCT(maker,model,type)

PC(model,speed,ram,hd,cd,price)

LAPTOP(model,speed,ram,screen,price)

PRINTER(model,color,type,price)

使用SQL语句完成查询：

2.1找出价格高于15000元,并且运行速度低于同价位PC的平均运行速度的LAPTOP

没啥难的，三个约束条件：“价格高于15000”、“同价位”、“LAPTOP的运行速度与PC的运行速度”在WHERE中摆上即可

SELECT model
FROM LAPTOP,PC
WHERE LAPTOP.price = PC.price AND LAPTOP.price > 15000 AND LAPTOP.speed < PC.speed;

 2.2找出生产价格最低的彩色打印机的厂家

SELECT maker
FROM PRODUCT
WHERE model in (
    SELECT model
    FROM PRINTER
    WHERE color = true AND price == (
        SELECT MIN(price)
        FROM PRINTER
    )
);

2.3计算由厂家“HP”生产的PC和LAPTOP的平均价格

由表中数据可知，PC和LAPTOP是两种属性数据，如果一次查询使用where是无法查到的，因此需要对PC和LAPTOP分别计算再取并集即可

(
    SELECT AVG(price) AS AVG_PC
    FROM PRODUCT,PC
    WHERE maker = 'HP' AND type = 'pc'
    GROUP BY type
)
UNION
(
    SELECT AVG(price) AS AVG_LAPTOP
    FROM PRODUCT,LAPTOP
    WHERE maker = 'HP' AND type = 'laptop'
    GROUP BY type
);

2.4计算各厂商生产的LAPTOP的显示器的平均尺寸

SELECT maker,AVG(SCREEN)
FROM PRODUCT,LAPTOP
WHERE PRODUCT.model = LAPTOP.model
GROUP BY maker;

2.5找出每一个生产厂商的PC的最高价格

SELECT maker,MAX(price)
FROM PRODUCT,PC
WHERE PRODUCT.model = PC.model
GROUP BY maker;

2.6计算生产打印机的各个厂商所生产的PC的硬盘的平均容量

SELECT maker,AVG(cd)
FROM PRODUCT,PC
WHERE type = 'PC' AND maker in (
    SELECT maker
    FROM PRODUCT
    WHERE type = 'printer'
)
GROUP BY maker;