INSERT INTO Teachers
VALUES ("600001", "杨梦", "女", 64, #4/22/1966#, YES, 1660, 210);
DELETE *
FROM teachers
WHERE 年龄<36 and 性别="女";
UPDATE teachers SET 应发工资 = 应发工资*1.2
WHERE year(date())-year(参加工作年月)>=25;
SELECT 教师号, 姓名, 应发工资-扣除工资 AS 实发工资
FROM teachers
WHERE 参加工作年月<#1/1/1991#
ORDER BY 应发工资-扣除工资 DESC;
SELECT count(*) AS 教师人数, avg(应发工资-扣除工资) AS 平均实发工资
FROM teachers;
SELECT 性别, min(应发工资-扣除工资) AS 最低工资, max(应发工资-扣除工资) AS 最高工资, avg(应发工资-扣除工资) AS 平均工资
FROM teachers
GROUP BY 性别;
SELECT 党员,count(*) AS 学生人数,avg(分数) AS 平均成绩
FROM teachers,students
WHERE teachers.教师号=students.教师号
GROUP BY 党员
ORDER BY avg(分数) DESC;
SELECT 党员, count(*) AS 教师人数, avg(年龄) AS 平均年龄
FROM teachers
GROUP BY 党员;
SELECT 学号, students.姓名 AS 学生姓名, teachers.教师号, teachers.姓名 AS 教师姓名, teachers.性别
FROM teachers, students
WHERE teachers.教师号=students.教师号
ORDER BY 学号 DESC;
SELECT teachers.教师号 AS 教师号, teachers.姓名 AS 姓名, min(分数) AS 最低分, max(分数) AS 最高分, avg(分数) AS 平均分
FROM teachers, students
WHERE teachers.教师号=students.教师号
GROUP BY teachers.教师号, teachers.姓名;