Oracle SQL Test 1

Oracle SQL Test 1 SQL code www.2cto.com id sname smoney sprovince 1 zhangsan 2098 A 2 lisi 3000 B 3 wangwu 6789 C 4 liumazi 4587 C 5 dongjiu 3298 B 6 shiga 4567 A id: contract id sname: Name smoney: Performance sprovince: Region create table test (sid NUMBER, sname VARCHAR2 (20), smoney NUMBER, sprovince CHAR (1) insert into test VALUES (1, 'hangsan', 2098, 'A'); insert into test VALUES (2, 'lisi', 3000, 'B'); INSERT INTO test VALUES (3, 'hangw ', 6789, 'C'); insert into test VALUES (4, 'liumazi', 4587, 'C'); insert into test VALUES (5, 'dongjiu', 3298, 'B'); INSERT INTO test VALUES (6, 'shiga ', 4567, 'A'); COMMIT; -- show the name of the Contract id whose performance is greater than the average value of the same region. SELECT t. * FROM test t, (select avg (smoney) smoney, sprovince FROM test group by sprovince) t2 WHERE t. smoney> t2.smoney AND t. sprovince = t2.sprovince or SELECT t. * FROM test t WHERE t. smoney> (select avg (smoney) FROM test t2 WHERE t. sprovince = t2.sprovince group by t2.sprovince) -- inserts the average performance region of the same region into the new TABLE (the new TABLE only contains two fields: average performance region) create table test_temp as select avg (t. smoney) smoney, t. sprovince FROM test t group by t. sprovince