必威体育精装版数据库题目+答案练习1汇编.doc

-- 1.建一个员工信息表employee, 表中 id （员工代码)、sex (员工性别)、name(姓名）、 -- departmentid (部门代码) 、address (地址)，birthdate (生日)、postcode (邮编) 、 -- salary〔薪水)、workdate (入职日期) 、remark （备注信息），其中 postcode 、 -- remark 可以为空，薪水需为numher 类型.，生日、入职日期为 date 类型。以员工代码 -- 作为主键 CREATE TABLE employee (id int(4) NOT NULL PRIMARY KEY, sex CHAR(2) DEFAULT 男, eNAME VARCHAR(8) NOT NULL, departmentid INT(5) NOT NULL, address VARCHAR(50) NOT NULL, birthdate TIMESTAMP NOT NULL, postcode INT(6), salary int(6) NOT NULL, workdate TIMESTAMP NOT NULL, remark VARCHAR(20) ) SELECT*FROM employee; //2.插入两条记录，id分别为0023, 1023，其余信息自己编造。 INSERT INTO employee VALUES(0023,男,白字画,12218,蜀山上清宫,1978-12-15,285800,50000,1980-10-10,此人为仙人); INSERT INTO employee VALUES(1023,女,花千骨,12218,蜀山上清宫,1988-10-25,285800,8000,1989-11-11,此人为散仙), (1,男,李逍遥,12216,芙蓉镇,1981-08-15,285600,80000,1981-07-21,此人为散仙), (2,女,赵灵儿,12218,芙蓉镇,1982-11-21,285800,10600,1982-05-23,此人为散仙), (3,女,小龙女,12218,芙蓉镇,1983-12-13,285600,5000,1983-02-21,此人为散仙), (4,女,雪女,12216,九幽,1980-11-01,285800,6600,1984-09-18,此人为散仙), (5,女,月儿,12216,九幽,1982-05-05,285800,5800,1985-06-25,此人为散仙), (6,男,天明,12218,九幽,1983-05-08,285600,8200,1986-11-16,此人为散仙), (7,男,葛聂,12215,太平宫,1984-09-18,285800,6000,1987-11-18,此人为散仙), (8,男,小明,12218,太平宫,1985-09-23,285700,8000,1988-10-19,此人为散仙), (9,男,大胖,12215,太平宫,1980-11-22,265000,8000,1989-10-23,此人为散仙) INSERT INTO employee VALUES(10,男,灰太狼,12218,太平宫,1982-03-27,287800,3500,1995-07-22,此人为散仙), (11,男,熊大,12216,蜀山上清宫,1981-02-20,285800,2800,1996-06-28,此人为散仙), (12,男,熊二,12215,蜀山上清宫,1983-02-06,285900,3800,1990-12-05,此人为散仙); //3.查询员工总数、薪水总额。 SELECT COUNT(*)AS 员工总数 FROM employee; SELECT SUM(salary)AS 薪水总额 FROM employee; //4.查询出各部门的最小年龄、最大年龄。 SELECT MAX(birthdate)AS 最小年龄,MIN(birthdate) AS 最大年龄 FROM employee; //5.统计每个部门的男女人数，按照部门代码排序。group by在order by前 中间没符号 SELECT sex,COUNT(sex) FROM employee GROUP BY sex; SELECT departmentid FROM employee ORDER BY departmentid; SELECT sex,COUNT(sex) FROM employee GROUP BY sex; SELECT departmentid,sex,COUNT(sex) FROM employee GROUP BY depa