SQL练习题集：涵盖E-R图、查询与数据库设计

"这些练习题主要涉及SQL语言的基础操作，包括E-R图的理解，表的主键识别，以及各种查询语句的编写。通过解决这些问题，可以加深对SQL语法的理解和应用能力。" 一、学生和课程关系的问题： 1. E-R图表示：学生实体与课程实体之间存在一对多的关系，即一个学生可以选修多门课程，一门课程可以被多个学生选修。学习实体作为联系实体，连接学生和课程，包含学号和课程ID作为外键，以及成绩信息。 2. 主键字段：学生表的主键是学号，课程表的主键是课程ID，学习表的主键是组合键（学号，课程ID）。 3. SQL语句示例： - (1) 查出所有男同学的基本信息：`SELECT * FROM 学生 WHERE 性别 = '男'` - (2) 查出学生的学号，姓名，班级，课程名称，成绩：`SELECT 学生.学号, 学生.姓名, 学生.班级, 课程.课程名称, 学习.成绩 FROM 学生 JOIN 学习 ON 学生.学号 = 学习.学号 JOIN 课程 ON 学习.课程ID = 课程.课程ID` - (3) 查出成绩表中成绩大于平均成绩的信息：需要先计算平均成绩，然后查询：`SELECT * FROM 学习 WHERE 成绩 > (SELECT AVG(成绩) FROM 学习)` - (4) 查出民族为汉族或者回族的学生的所有基本信息：`SELECT * FROM 学生 WHERE 民族 IN ('汉族', '回族')` - (5) 查出成绩在70-80分之间的学生姓名，课程名称和成绩：`SELECT 学生.姓名, 课程.课程名称, 学习.成绩 FROM 学生 JOIN 学习 ON 学生.学号 = 学习.学号 JOIN 课程 ON 学习.课程ID = 课程.课程ID WHERE 学习.成绩 BETWEEN 70 AND 80` 二、职工社团数据库问题： 1. 定义表及关系： - 职工表：`CREATE TABLE 职工 (职工号 INT PRIMARY KEY, 姓名 VARCHAR(50), 年龄 INT, 性别 CHAR(1))` - 社会团体表：`CREATE TABLE 社会团体 (编号 INT PRIMARY KEY, 名称 VARCHAR(100), 负责人 INT, 活动地点 VARCHAR(200), FOREIGN KEY (负责人) REFERENCES 职工(职工号))` - 参加表：`CREATE TABLE 参加 (职工号 INT, 编号 INT, 参加日期 DATE, PRIMARY KEY (职工号, 编号), FOREIGN KEY (职工号) REFERENCES 职工(职工号), FOREIGN KEY (编号) REFERENCES 社会团体(编号))` 2. 视图创建： - 社团负责人：`CREATE VIEW 社团负责人 AS SELECT 编号, 名称, 负责人, 姓名, 性别 FROM 社会团体 JOIN 职工 ON 社会团体.负责人 = 职工.职工号` - 参加人情况：`CREATE VIEW 参加人情况 AS SELECT 职工号, 姓名, 社团编号, 社团名称, 参加日期 FROM 职工 JOIN 参加 ON 职工.职工号 = 参加.职工号 JOIN 社会团体 ON 参加.编号 = 社会团体.编号` 3. 查询语句： - 查找参加唱歌队或篮球队的职工号和姓名：`SELECT 职工号, 姓名 FROM 职工 JOIN 参加 ON 职工.职工号 = 参加.职工号 JOIN 社会团体 ON 参加.编号 = 社会团体.编号 WHERE 社会团体.名称 IN ('唱歌队', '篮球队')` - 查找没有参加任何社会团体的职工情况：`SELECT * FROM 职工 WHERE 职工号 NOT IN (SELECT 职工号 FROM 参加)` - 查找参加了全部社会团体的职工情况：这个查询较为复杂，因为需要知道所有的社会团体编号，所以实际操作可能需要更具体的数据库信息 - 查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号：`SELECT DISTINCT 职工号 FROM 参加 WHERE 编号 IN (SELECT 编号 FROM 参加 WHERE 职工号 = '1001')` - 求每个社会团体的参加人数：`SELECT 社会团体.编号, 社会团体.名称, COUNT(*) as 参加人数 FROM 社会团体 JOIN 参加 ON 社会团体.编号 = 参加.编号 GROUP BY 社会团体.编号, 社会团体.名称` - 求参加人数最多的社会团体的名称和参加人数：`SELECT 社会团体.名称, MAX(参加人数) FROM (SELECT 社会团体.名称, COUNT(*) as 参加人数 FROM 社会团体 JOIN 参加 ON 社会团体.编号 = 参加.编号 GROUP BY 社会团体.编号, 社会团体.名称) AS 社团计数 GROUP BY 社会团体.名称 ORDER BY 参加人数 DESC LIMIT 1` - 求参加人数超过100人的社会团体的名称和负责人：`SELECT 社会团体.名称, 负责人 FROM 社会团体 WHERE 社会团体编号 IN (SELECT 编号 FROM 参加 GROUP BY 编号 HAVING COUNT(*) > 100)` - 把权限赋予用户李平：`GRANT SELECT, INSERT, DELETE ON 社会团体, 参加 TO '李平'; GRANT GRANT OPTION ON 社会团体, 参加 TO '李平';` 三、SQL2000, flash, net字段设置为浮点型： 1. 创建student和computer表： - `CREATE TABLE student (name VARCHAR(50), number INT, sex CHAR(1));` - `CREATE TABLE computer (name VARCHAR(50), number INT, sex CHAR(1), SQL2000 FLOAT, flash FLOAT, net FLOAT);` 2. 查询语句： - 输出所有男生的成绩：`SELECT name AS SQL数据库, flash AS 网络动画, net AS 计算机网络 FROM computer WHERE sex = '男'` - 输出所有SQL成绩在90以上的记录：`SELECT * FROM computer WHERE SQL2000 >= 90`