奇偶数、课程成绩 Decode、when case

conn hr/hr

 

1、查询经理
select distinct a.LAST_NAME ||' ' || a.FIRST_NAME 职工姓名,b.LAST_NAME ||' ' || b.FIRST_NAME 汇报经理
from  employees a,employees b
where a.MANAGER_ID=b.EMPLOYEE_ID(+)
order by 汇报经理
/

2、偶数
select a.id,b.id
from test a,test b
where b.id-a.id=(select count(*) from test)/2
/

 

 

3、奇数
select a.id,b.id
from test a,test b
where a.id=b.id(+)-((select count(*) from test)+1)/2 and a.id<=((select count(*) from test)+1)/2
/

注：计算部分应放在操作符右边，有利于性能

 

 

4、查询高于平均工资

select e.LAST_NAME ||' ' || e.FIRST_NAME,d.DEPARTMENT_ID,d.sal_avg
from employees e,(select DEPARTMENT_ID,avg(SALARY) sal_avg from employees group by DEPARTMENT_ID) d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID and e.SALARY>sal_avg
/

 

5、查询课程成绩

 

添加数据

CREATE TABLE SCOTT.student ("STUDENT" VARCHAR2(10) NOT NULL,
    "SUBJECT" VARCHAR2(10) NOT NULL, "GRADE" NUMBER(10) NOT
    NULL);
 
INSERT INTO "SCOTT"."STUDENT" ("STUDENT" ,"SUBJECT" ,"GRADE" )
    VALUES ('student1' ,'语文' ,80  );
INSERT INTO "SCOTT"."STUDENT" ("STUDENT" ,"SUBJECT" ,"GRADE" ) VALUES ('student1' ,'数学' ,70  );
INSERT INTO "SCOTT"."STUDENT" ("STUDENT" ,"SUBJECT" ,"GRADE" ) VALUES ('student1' ,'英语' ,60  );
INSERT INTO "SCOTT"."STUDENT" ("STUDENT" ,"SUBJECT" ,"GRADE" ) VALUES ('student2' ,'语文' ,90  );
INSERT INTO "SCOTT"."STUDENT" ("STUDENT" ,"SUBJECT" ,"GRADE" ) VALUES ('student2' ,'数学' ,80  );
INSERT INTO "SCOTT"."STUDENT" ("STUDENT" ,"SUBJECT" ,"GRADE" ) VALUES ('student2' ,'英语' ,100  );
commit;

 

 

Decode用法

select distinct STUDENT,sum(decode(SUBJECT,'语文', GRADE,0)) 语文,sum(decode(SUBJECT,'数学', GRADE,0)) 数学,sum(decode(SUBJECT,'英语', GRADE,0)) 英语
from  student
group by STUDENT
/

 

 

when case用法

select STUDENT,
sum(case SUBJECT when '语文' then GRADE
                     else 0
                            end) 语文,
sum(case SUBJECT when '数学' then GRADE
                     else 0
                            end) 数学,
sum(case SUBJECT when '英语' then GRADE
                     else 0
                            end) 英语
from  student
group by student
/

 

 

 

 

5月12日实验