2013年最新完美大学计算机基础access练习题答案(同济大学)

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 参加工作年月<#1/1/1991#
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.教师号