c3期末卷

查询年龄在25至30岁之间的男员工的姓名和住址。 SELECT y_name,y_address FROM bumen,yg WHERE bumen.b_id=yg.b_id AND y_sex=’男’ AND y_age BETWEEN 25 AND 302、查询财务部所有40岁以下男员工的所有信息 SELECT * FROM bumen,yg WHERE bumen.b_id=yg.b_id ANDb_name='财务部' AND y_age<=40 AND y_sex=’男’3、查询人事部年龄最大的女员工姓名 Select * from yg where y_age=(Select max（y_age） from yg，bumen where bumen.b_id=yg.b_idAnd y_sex=’女’ AND b_name=’人事部’ )And y_sex=’女’ AND b_id=(Select b_id from bumen where b_name=’人事部’)4、2号新到一名员工，已知姓名，性别，年龄，将此员工加入到员工表 INSERT INTO yg (y_name,y_sex,y_age)VALUES('小灰灰','男',13) 5、在员工表中，将人事部年龄大于30岁的女同事，调到后勤部UPDATE yg SET b_id=(SELECT b_id FROM bumen WHERE b_name='后勤部')WHERE y_id IN(SELECT * FROM (SELECT y_id FROM yg WHERE b_id=(SELECT b_id FROM bumen WHERE b_name='人事部')AND y_age>30) xx)AND y_sex='女'6：查询每个部门年龄最大的员工，显示部门名字和年龄。SELECT b_name,y_age FROM bumen,yg WHERE bumen.b_id=yg.b_id GROUP BY b_name HAVING MAX(y_age)7：查询每个部门各有多少人，显示部门名字和人数，按人数倒序，如果人数相同，按部门编号正序。SELECT b_name,COUNT(*) FROM bumen,yg WHERE bumen.b_id=yg.b_id GROUP BY bumen.b_idORDER BY COUNT(*)DESC,bumen.b_id ASC8：将张三的的名字改为李四，并调到财务部。UPDATE yg SET y_name='李四',b_id=(SELECT b_id FROM bumen WHERE b_name='财务部')WHERE y_name='张三'9：将后勤部年龄大于60岁的员工删除。DELETE FROM yg WHERE y_age>60 AND b_id IN(SELECT b_id FROM bumen WHERE b_name='后勤部')10：查询财务部年龄不在20-30之间的男生信息。SELECT * FROM bumen,yg WHERE bumen.b_id=yg.b_idAND b_name='财务部' AND y_sex=’男’AND y_age NOT BETWEEN 20 AND 30