同济大学计算机基础数据库答案

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 Year(参加工作年月)<=1990
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 Teachers.党员, Count(Students.学号) AS 学生人数, Avg(Students.分数) AS 平均成绩
FROM Students INNER JOIN Teachers ON Students.教师号=Teachers.教师号
GROUP BY Teachers.党员
ORDER BY Avg(Students.分数) DESC


8.

SELECT Teachers.党员,Count(Teachers.教师号) AS 教师人数, Avg(Teachers.年龄) AS 平均年龄
FROM Teachers
GROUP BY Teachers.党员

9.

SELECT Students.学号, Students.姓名 AS 学生姓名, Teachers.教师号, Teachers.姓名 AS 教师姓名, Teachers.性别
FROM Students INNER JOIN Teachers ON Students.教师号 = Teachers.教师号
ORDER BY Students.学号 DESC

10.

SELECT Teachers.教师号,Teachers.姓名,First(Teachers.教师号) AS 教师号,First(Teachers.姓名) AS 姓名, Min(students.分数) AS 最低分,Max(Students.分数) AS 最高分,Avg(Students.分数) AS 平均分
FROM Students INNER JOIN Teachers ON Students.教师号=Teachers.教师号
GROUP BY Teachers.教师号