数据库期末考试题

Salary ON Employees.编号 = Salary.编号 WHERE (Salary.收入< 2500) 3. SELECT Employees.* FROM Employees INNER JOIN

Departments ON Employees.部门号 = Departments.部门号 WHERE (Employees.出生日期<= (SELECT MIN(出生日期) FROM Employees INNER JOIN

Departments ON Employees.部门号 = Departments.部门号

WHERE (Departments.部门名 = '研发部'))) AND (Departments.部门名 = '财务部') 4. SELECT AVG(Salary.收入) AS 平均收入 FROM Employees INNER JOIN

Departments ON Employees.部门号 = Departments.部门号 INNER JOIN Salary ON Employees.编号 = Salary.编号 WHERE (Departments.部门名 = '财务部')

5. SELECT MAX(Salary.收入) AS 最高收入, MIN(Salary.收入) AS 最低收入 FROM Employees INNER JOIN

Departments ON Employees.部门号 = Departments.部门号 INNER JOIN Salary ON Employees.编号 = Salary.编号 WHERE (Departments.部门名 = '财务部')

6. SELECT AVG(Salary.收入-Salary.支出) AS 实际平均收入 FROM Employees INNER JOIN

Departments ON Employees.部门号 = Departments.部门号 INNER JOIN Salary ON Employees.编号 = Salary.编号 WHERE (Departments.部门名 = '财务部') 7. SELECT COUNT(Employees.编号) AS 总人数 FROM Employees INNER JOIN

Departments ON Employees.部门号 = Departments.部门号 WHERE (Departments.部门名 = '财务部') 8. SELECT COUNT(Employees.编号) AS 总人数 FROM Employees INNER JOIN

Departments ON Employees.部门号 = Departments.部门号 INNER JOIN Salary ON Employees.编号 = Salary.编号

WHERE (Departments.部门名 = '财务部') AND (Salary.收入> 2500) 9. SELECT Employees.*, Salary.收入 FROM Employees INNER JOIN

Salary ON Employees.编号 = Salary.编号 ORDER BY Salary.收入DESC

10. SELECT Departments.部门名, AVG(Salary.收入) AS 平均, MAX(Salary.收入) AS 最高,

MIN(Salary.收入) AS 最低, COUNT(*) AS 人数 FROM Employees INNER JOIN

Salary ON Employees.编号 = Salary.编号 INNER JOIN Departments ON Employees.部门号 = Departments.部门号 GROUP BY Departments.部门名 ORDER BY COUNT(*)

四、程序改错

建立jbqk数据表结构并满足完整性的要求是：学号必须输入，且为主键；“姓名”必须输入；“性别”必须输入“男”或“女”； 出生日期为1985-2009之间，所属单位为“管理 学院”和“能源学院”；专业的默认值为“信息管理”；各门课程的成绩必须在0-100之间。建立该数据表结构 的其T-SQL语句如下，修改其中的错误。

CREATE DATABASE JBQK --FOUND ERROR 1--

(学号 char (8) PK_xh NOT NULL PRIMARY KEY, --FOUND ERROR 2-- 姓名nchar (4) IS NULL , --FOUND ERROR 3--

性别nchar (1) CONSTRAINT chk_xb 性别=?男女?, --FOUND ERROR 4-- 出生日期 datetime CONSTRAINT CK_rq check(出生日期 between 1985 and 2008) , --FOUND ERROR 5--

所属单位 char (16) in(?管理学院?,?能源学院?), --FOUND ERROR 6-- 专业char (20) default ?信息管理? for 专业, --FOUND ERROR 7-- 数学decimal(4, 1) CHECK (数学 = [0-100]) , --FOUND ERROR 8-- 语文decimal(4, 1) CHECK (语文 Not NULL) , --FOUND ERROR 9-- 英语decimal(4, 1) CHECK (0<=英语 <= 100) , --FOUND ERROR 10-- 备注text )