数据分析笔试-sql

题目说明及要求：                                                                            

以下是模似数据库里的表单信息，请根据要求写出SQL语句                                                                           

                     

表1：职工信息                  

表结构如下：                    

表名：Employee                

                    

职工ID   职工姓名       入职年份       部门ID

A1   B1   2000       C1

A2   B2   1998       C2

A3   B3   1999       C1

A4   B4   2001       C4

表2：部门信息           

表结构如下：             

表名：Dept         

             

部门ID   部门名称       部门经理

C1   D1   E1

C2   D2   E2

C3   D3   E3

C4   D4   E4

表3：职工职责表       

表结构如下：             

表名：Emp          

             

职工ID   岗位ID   岗位名称

A1   G1   Gn1

A1   G2   Gn1

A2   G1   Gn1

A3   G1   Gn1

A3   G5   Gn5

A4   G1   Gn1

1. 查询入职年份在2000年及以后的职工

SELECT * FROM Employee e WHERE e.入职年份>2000;                                                                       

                                                                            

2. 查询出部门G1的所有职工信息，需要所有的职工信息和部门信息                                                                      

SELECT e.EID,e.Ename,e.`year`,e.DID,d.Dname,d.Dma FROM e, d WHERE e.DID = d.DID AND e.DID = 'G1';                                                                         

                                                                            

3.查询出与A1不是一个岗位的所有职工的职工信息                                                                           

SELECT e.DID,e.EID,e.Ename,e.`year`,d.Dma,d.Dname,emp.Gnanme,emp.GID                                                                          

FROM e,d,emp                                                                           

WHERE e.DID=d.DID AND e.EID = emp.EID AND emp.GID NOT IN (SELECT emp.GID                                                                            

FROM e LEFT JOIN emp ON e.EID = emp.EID                                                                        

WHERE e.EID = 'A1');