数据中的表格如下:
Employee(EID, Fname, Lname, Address, DOB, Gender, Salary, Bonus, DID)
Department(DID, Name, Mgr_EID, Mgr_Start_Date)
Project(PID, Name, DID)
Work_On(EID, PID, Hours)
PS:DID:Department的ID,DOB:生日。。
SQL 问题:(1)寻找在department中的research department中年龄第二小的。
(2)找出employee里面的名字包含字母"Y"的,并用desending的顺序排列。
(3)找到所有比平均年龄大的employee.
(4) 找出每个department所承担的project的数量,并列出department的名字
(5)找出只承担1个project的employee,并列出名字
(6)找出承担大于1个project的employee,列出名字
(7)找出每个employee所承担的project的数量,每个employee的名字要列出
(8)找出男(male)employee和女(female)employee个承担的project的数量
(9)每一个project的有几个employee在做, 要列出project的名字
各位大侠帮忙看下感激不尽~~~
Employee(EID, Fname, Lname, Address, DOB, Gender, Salary, Bonus, DID)
Department(DID, Name, Mgr_EID, Mgr_Start_Date)
Project(PID, Name, DID)
Work_On(EID, PID, Hours)
PS:DID:Department的ID,DOB:生日。。
SQL 问题:(1)寻找在department中的research department中年龄第二小的。
(2)找出employee里面的名字包含字母"Y"的,并用desending的顺序排列。
(3)找到所有比平均年龄大的employee.
(4) 找出每个department所承担的project的数量,并列出department的名字
(5)找出只承担1个project的employee,并列出名字
(6)找出承担大于1个project的employee,列出名字
(7)找出每个employee所承担的project的数量,每个employee的名字要列出
(8)找出男(male)employee和女(female)employee个承担的project的数量
(9)每一个project的有几个employee在做, 要列出project的名字
各位大侠帮忙看下感激不尽~~~
Employee(EID, Fname, Lname, Address, DOB, Gender, Salary, Bonus, DID)
Department(DID, Name, Mgr_EID, Mgr_Start_Date)
Project(PID, Name, DID)
Work_On(EID, PID, Hours)
PS:DID:Department的ID,DOB:生日。。
SQL 问题:(1)寻找在department中的research department中年龄第二小的。
(2)找出employee里面的名字包含字母"Y"的,并用desending的顺序排列。
(3)找到所有比平均年龄大的employee.
(4) 找出每个department所承担的project的数量,并列出department的名字
(5)找出只承担1个project的employee,并列出名字
(6)找出承担大于1个project的employee,列出名字
(7)找出每个employee所承担的project的数量,每个employee的名字要列出
(8)找出男(male)employee和女(female)employee个承担的project的数量
(9)每一个project的有几个employee在做, 要列出project的名字
各位大侠帮忙看下感激不尽~~~
1 年龄是哪个字段
2、按哪个字段排序,两种查询方式,fname like '%Y%' or lname like '%Y%'
instr(fname,'Y',1,1)>0 or instr(lname,'Y',1,1)>0 3、select a.*
from employee a,(select avg(sysdate-dob) age from employee ) b
where sysdate-dob>b.age4、select
d.DID, d.Name,count(*) num
from Department d,project p
where d.did=p.did
group by d.DID, d.Name这些都是很基础的SQL语句
如5
select
EID, Fname, Lname
from Employee e,project p
where e.did=p.did
group by EID, Fname, Lname
having count(*)=16
select
EID, Fname, Lname
from Employee e,project p
where e.did=p.did
group by EID, Fname, Lname
having count(*)>17
select
EID, Fname, Lname,count(*)
from Employee e,project p
where e.did=p.did
group by EID, Fname, Lname这三个基本上就是一道题
第8题则是换了个分组字段
select
gender,count(*)
from Employee e,project p
where e.did=p.did
group by gender第9题
select
PID, Name,count(*)
from Employee e,project p
where e.did=p.did
group by PID, Name无非就是分组条件变来变去
SQL问题解答----变态的题目,变态的答案:(1)寻找在department中的research department中年龄第二小的。
select min(Mgr_Start_Date) from Department
where DID not in
(select DID from Department where Mgr_Start_Date=select min(Mgr_Start_Date) from Department);
(2)找出employee里面的名字包含字母"Y"的,并用desending的顺序排列。
select * from Employee
where Fname like '%Y%'
order by Fname desc;
(3)找到所有比平均年龄大的employee.
假设年龄只按照年份计算,不考虑月日
select * from Employee A,(select avg(to_number(to_char(sysdate,'yyyy'))-to_number(to_char(DOB,'yyyy'))) avgX from Employee) B
where to_number(to_char(A.DOB,'yyyy'))>avgX;
(4)找出每个department所承担的project的数量,并列出department的名字
select D1.Name,num
from Department D1,
(
select D.DID,count(*) num
from Department D,Project P
where D.DID=P.DID
group by D.DID) X
where D1.DID=X.DID;(5)找出只承担1个project的employee,并列出名字
select E.EID,count(*)
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.EID
having count(*)=1;
(6)找出承担大于1个project的employee,列出名字
select E.EID,count(*)
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.EID
having count(*)>1;
(7)找出每个employee所承担的project的数量,每个employee的名字要列出
select E1.NAME,num
from Employee E1,
(select E.EID,count(*) num
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.EID) X
where E1.EID=X.EID;(8)找出男(male)employee和女(female)employee个承担的project的数量
select E.Gender,count(*)
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.Gender;(9)每一个project的有几个employee在做,要列出project的名字
select P1.NAME,num
from Project P1,
(select P.PID,count(*) num
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by P.PID) X
where P1.P.PID=P.PID;
select Employee.* from Department, Employee where Department.DID=Employee.DID and Department.name = 'research department' and DOB = (select DOB from (select count(1) cnt, rownum, DOB from Department, Employee where Department.DID=Employee.DID and Department.name = 'research department' group by DOB order by DOB asc where rownum <= 2) temp where temp.rownum>1)
(2)找出employee里面的名字包含字母"Y"的,并用desending的顺序排列。
select * from Employee
where Fname like '%Y%'
order by Fname desc;
(3)找到所有比平均年龄大的employee.
假设年龄只按照年份计算,不考虑月日
select * from Employee A,(select avg(extract(year from sysdate)-extract(year from DOB,'yyyy')) avgX from Employee) B
where A.DOB > to_date(to_char(extract(year from sysdate)-B.avgX), 'yyyy');
(4)找出每个department所承担的project的数量,并列出department的名字
select D.DID,D.name, count(*) num
from Department D, Project P where D.DID=P.DID
group by D.DID, D.name) X;(5)找出只承担1个project的employee,并列出名字
select E.EID,count(*)
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.EID
having count(*)=1;
(6)找出承担大于1个project的employee,列出名字
select E.EID,count(*)
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.EID
having count(*)>1;
(7)找出每个employee所承担的project的数量,每个employee的名字要列出
select E1.NAME,num
from Employee E1,
(select E.EID,count(*) num
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.EID) X
where E1.EID=X.EID;
(8)找出男(male)employee和女(female)employee个承担的project的数量
select E.Gender,count(*)
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.Gender;
(9)每一个project的有几个employee在做,要列出project的名字
select P1.NAME,num
from Project P1,
(select P.PID,count(*) num
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by P.PID) X
where P1.P.PID=P.PID;
select Employee.* from Department, Employee where Department.DID=Employee.DID and Department.name = 'research department' and DOB = (select DOB from (select count(1) cnt, rownum, DOB from Department, Employee where Department.DID=Employee.DID and Department.name = 'research department' group by DOB order by DOB asc where rownum <= 2) temp where temp.rownum>1)
(2)找出employee里面的名字包含字母"Y"的,并用desending的顺序排列。
select * from Employee
where Fname like '%Y%'
order by Fname desc;
(3)找到所有比平均年龄大的employee.
假设年龄只按照年份计算,不考虑月日
select * from Employee A,(select avg(extract(year from sysdate)-extract(year from DOB,'yyyy')) avgX from Employee) B
where A.DOB > to_date(to_char(extract(year from sysdate)-B.avgX), 'yyyy');
(4)找出每个department所承担的project的数量,并列出department的名字
select D.DID,D.name, count(*) num
from Department D, Project P where D.DID=P.DID
group by D.DID, D.name) X; (5)找出只承担1个project的employee,并列出名字
select E.EID,count(*)
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.EID
having count(*)=1;
(6)找出承担大于1个project的employee,列出名字
select E.EID,count(*)
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.EID
having count(*)>1;
(7)找出每个employee所承担的project的数量,每个employee的名字要列出
select E1.NAME,num
from Employee E1,
(select E.EID,count(*) num
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.EID) X
where E1.EID=X.EID;
(8)找出男(male)employee和女(female)employee个承担的project的数量
select E.Gender,count(*)
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.Gender;
(9)每一个project的有几个employee在做,要列出project的名字
select P1.NAME,num
from Project P1,
(select P.PID,count(*) num
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by P.PID) X
where P1.P.PID=P.PID; (1)寻找在department中的research department中年龄第二小的。
select Employee.* from Department, Employee where Department.DID=Employee.DID and Department.name = 'research department' and DOB = (select DOB from (select count(1) cnt, rownum, DOB from Department, Employee where Department.DID=Employee.DID and Department.name = 'research department' group by DOB order by DOB asc where rownum <= 2) temp where temp.rownum>1)
(2)找出employee里面的名字包含字母"Y"的,并用desending的顺序排列。
select * from Employee
where Fname like '%Y%'
order by Fname desc;
(3)找到所有比平均年龄大的employee.
假设年龄只按照年份计算,不考虑月日
select * from Employee A,(select avg(extract(year from sysdate)-extract(year from DOB,'yyyy')) avgX from Employee) B
where A.DOB > to_date(to_char(extract(year from sysdate)-B.avgX), 'yyyy');
(4)找出每个department所承担的project的数量,并列出department的名字
select D.DID,D.name, count(*) num
from Department D, Project P where D.DID=P.DID
group by D.DID, D.name) X; (5)找出只承担1个project的employee,并列出名字
select E.EID,count(*)
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.EID
having count(*)=1;
(6)找出承担大于1个project的employee,列出名字
select E.EID,count(*)
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.EID
having count(*)>1;
(7)找出每个employee所承担的project的数量,每个employee的名字要列出
select E1.NAME,num
from Employee E1,
(select E.EID,count(*) num
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.EID) X
where E1.EID=X.EID;
(8)找出男(male)employee和女(female)employee个承担的project的数量
select E.Gender,count(*)
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by E.Gender;
(9)每一个project的有几个employee在做,要列出project的名字
select P1.NAME,num
from Project P1,
(select P.PID,count(*) num
from Employee E,Department D,Project P
where E.EID=D.Mgr_EID
and P.DID=D.DID
group by P.PID) X
where P1.P.PID=P.PID;