Oracle row to column, Case usage, table merging __oracle

--Lists the number of employees and departmental numbers for which wages are higher than the average wage in each department, and the
select * from EMP E1 by department number,
(select round avg (SAL), 2) Sal,deptno from EMP Group by Deptno) E2 
where e1.sal>e2.sal and e1.deptno=e2.deptno the order by e1.deptno ASC;

Create Test table

--The row-and-column test
CREATE TABLE test (
ID number primary key,
name VARCHAR2, total number
,
quarter VARCHAR2
)
create sequence Test_seq increment by 1-start with 1;
INSERT into Test (Id,name,total,quarter) VALUES (test_seq.nextval, ' cheese ', 50, ' first quarter ');
INSERT into Test (Id,name,total,quarter) VALUES (test_seq.nextval, ' cheese ', 60, ' second quarter ');
INSERT into Test (Id,name,total,quarter) VALUES (test_seq.nextval, ' beer ', 50, ' second quarter ');
INSERT into Test (Id,name,total,quarter) VALUES (test_seq.nextval, ' beer ', 80, ' quarter Four ');
Commit

SELECT * from Test;

Select name,
sum (decode (quarter, ' first quarter ', total,0)) as first quarter sales,
sum (decode (quarter, ' second quarter ', total,0)) as second quarter sales ,
sum (decode (quarter, ' third quarter ', total,0)) as third-quarter sales,
sum (decode (quarter, ' quarter ', total,0)) as fourth quarter sales from test Group by name;

The use of case

Select Name, (when 
total<60 then ' unqualified ' when
total>=60 and total<80 then ' qualified ' when
total>=80 t Hen ' outstanding ' end
) as rank from test;

Merging of Oracle Tables

CREATE TABLE T1 (
ID number primary key,
name Varchar2 (), age number
);
INSERT into T1 (id,name,age) VALUES (1, ' A ',);
INSERT into T1 (id,name,age) VALUES (2, ' B ',);
INSERT into T1 (id,name,age) VALUES (3, ' C ',);
Commit

CREATE TABLE B1 (
ID number primary key,
name Varchar2 (),
score number
);
INSERT INTO B1 (Id,name,score) VALUES (1, ' A ',);
INSERT INTO B1 (Id,name,score) VALUES (2, ' B ',);
INSERT INTO B1 (Id,name,score) VALUES (3, ' D ', n);
Commit

Merging queries

Select T1.name,t1.age,b1.score from T1,B1

Select B1.name,t1.age,b1.score from T1,b1 
where (t1.name (+) =b1.name)

Final results

Select T1.name,t1.age,b1.score from T1,b1
where (T1.name=b1.name (+)) 
Union
Select B1.name,t1.age, B1.score from T1,b1 
where (t1.name (+) =b1.name)