这篇内容展示了SQL语言在数据库管理中的应用，包括教师信息表的数据插入、删除、更新、查询等操作，涉及条件筛选、聚合函数及排序。同时，还涵盖了不同性别的教师工资范围统计以及党员教师的相关信息统计。
这是同济大学练习题答案,全10分的
1、INSERT INTO Teachers (教师号,姓名,性别,年龄,参加工作年月,党员,应发工资,扣除工资)
VALUES ("600001","杨梦","女",64,#4/22/1966#,YES,1660,210)
2、DELETE FROM Teachers WHERE 年龄<36 And 性别='女'
3、UPDATE Teachers SET 应发工资=应发工资*1.2
WHERE Year(Date())-Year(参加工作年月)>25
4、SELECT 教师号, 姓名, 应发工资-扣除工资 AS 实发工资
FROM Teachers
WHERE 参加工作年月
ORDER BY 应发工资-扣除工资 DESC
5、SELECT Count(*) AS 教师人数,
Avg(应发工资-扣除工资) AS 实发工资
FROM Teachers
6、SELECT 性别,
Min(应发工资-扣除工资) AS 最低工资,
Max(应发工资-扣除工资) AS 最高工资,
Avg(应发工资-扣除工资) AS 平均工资
FROM Teachers
GROUP BY 性别
7、SELECT 党员, count(*) as 学生人数, avg(分数) as 平均成绩
from teachers, students
where teachers.教师号=students.教师号
group by 党员
order by avg(分数) DESC
8、SELECT 党员, Count(*) AS 教师人数, Avg(年龄) AS 平均年龄
FROM teachers
GROUP BY 党员
9、SELECT 学号, students.姓名 as 学生姓名, teachers.教师号, teachers.姓名 as 教师姓名, teachers.性别 from teachers, students
where teachers.教师号=students.教师号
order by 学号 DESC
10、SELECT
teachers.教师号, Min(分数) AS 最低分, Max(分数) AS 最高分, Avg(分数) AS 平均分 FROM teachers, students
WHERE teachers.教师号=students.教师号
GROUP BY teachers.教师号
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
