MySQL數(shù)據(jù)庫系統(tǒng)與應(yīng)用 習(xí)題7

·PAGE80··PAGE81·習(xí)題71．員工工資管理數(shù)據(jù)庫salary_management中3個(gè)表的記錄分別見表7-5、表7-6和表7-7。表7-5department表中的記錄部門編號部門名負(fù)責(zé)人人數(shù)11人力部劉一521研發(fā)部李四6031市場部周八4041財(cái)務(wù)部吳九3表7-6employee表中的記錄部門編號員工編號姓名性別生日入職時(shí)間離職時(shí)間在職狀態(tài)111132劉一女1998-10-232021-04-10在職111152陳二女1995-09-122020-05-15在職111163張三男1992-12-252021-08-012022-04-30離職212151李四男1990-07-082020-11-01在職212175王五男1992-05-072021-03-01在職212192趙六女1998-06-152021-05-012022-03-31離職313121孫七女1996-10-142020-08-10在職313122周八女1998-09-202021-04-01在職414125吳九女1997-12-282022-06-19在職414128鄭十女1991-11-192022-08-01在職表7-7salary表中的記錄部門編號員工編號姓名年月標(biāo)識基本工資績效工資補(bǔ)貼扣款應(yīng)發(fā)工資社保代扣實(shí)發(fā)工資111132劉一2022-05-01300040002000-500111152陳二2022-05-01300040001000-500111163張三2022-05-01300040001000-500212151李四2022-05-01400050003000-500212175王五2022-05-01400050002000-500212192趙六2022-05-01400050001000-500313121孫七2022-05-01500060002000-500313122周八2022-05-01500060002000-500414125吳九2022-05-01600070003000-500414128鄭十2022-05-01600070002000-500111132劉一2022-06-01300040003000-500111152陳二2022-06-01300040001000-500212151李四2022-06-01400050002000-500212175王五2022-06-01400050002000-500313121孫七2022-06-01500060003000-600313122周八2022-06-01500060002000-600414125吳九2022-06-01600070003000-800414128鄭十2022-06-01600070002000-800用SQL語句插入表中所有記錄。USEsalary_management;INSERTINTOdepartment(部門編號,部門名,負(fù)責(zé)人,人數(shù))VALUES('11','人力部','劉一',5),('21','研發(fā)部','李四',60),('31','市場部','周八',40),('41','財(cái)務(wù)部','吳九',3);INSERTINTOemployee(部門編號,員工編號,姓名,性別,生日,入職時(shí)間,離職時(shí)間,在職狀態(tài))VALUES('11','1132','劉一','女','1998-10-23','2021-04-10',NULL,'在職'),('11','1152','陳二','女','1995-09-12','2020-05-15',NULL,'在職'),('11','1163','張三','男','1992-12-25','2021-08-01','2022-04-30','離職'),('21','2151','李四','男','1990-07-08','2020-11-01',NULL,'在職'),('21','2175','王五','男','1992-05-07','2021-03-01',NULL,'在職'),('21','2192','趙六','女','1998-06-15','2021-05-01','2022-03-31','離職'),('31','3121','孫七','女','1996-10-14','2020-08-10',NULL,'在職'),('31','3122','周八','女','1998-09-20','2021-04-01',NULL,'在職'),('41','4125','吳九','女','1997-12-28','2022-06-19',NULL,'在職'),('41','4128','鄭十','女','1991-11-19','2022-08-01',NULL,'在職');INSERTINTOsalary(部門編號,員工編號,姓名,年月標(biāo)識,基本工資,績效工資,補(bǔ)貼,扣款)VALUES('11','1132','劉一','2022-05-01','3000','4000','2000','-500'),('11','1152','陳二','2022-05-01','3000','4000','1000','-500'),('11','1163','張三','2022-05-01','3000','4000','1000','-500'),('21','2151','李四','2022-05-01','4000','5000','3000','-500'),('21','2175','王五','2022-05-01','4000','5000','2000','-500'),('21','2192','趙六','2022-05-01','4000','5000','1000','-500'),('31','3121','孫七','2022-05-01','5000','6000','2000','-500'),('31','3122','周八','2022-05-01','5000','6000','2000','-500'),('41','4125','吳九','2022-05-01','6000','7000','3000','-500'),('41','4128','鄭十','2022-05-01','6000','7000','2000','-500'),('11','1132','劉一','2022-06-01','3000','4000','3000','-500'),('11','1152','陳二','2022-06-01','3000','4000','1000','-500'),('21','2151','李四','2022-06-01','4000','5000','2000','-500'),('21','2175','王五','2022-06-01','4000','5000','2000','-500'),('31','3121','孫七','2022-06-01','5000','6000','3000','-600'),('31','3122','周八','2022-06-01','5000','6000','2000','-600'),('41','4125','吳九','2022-06-01','6000','7000','3000','-800'),('41','4128','鄭十','2022-06-01','6000','7000','2000','-800');2．在salary表中，計(jì)算年月標(biāo)識為2022-05-01的所有記錄，計(jì)算方法如下：應(yīng)發(fā)工資=基本工資+績效工資+補(bǔ)貼-扣款假設(shè)：