Fun SQL and fun SQL

Fun SQL and fun SQL

1. Create a 3*3 addition table

SQL> select a||'+'||b||'='||(a+b) from (select rownum a from all_objects where rownum<4), (select rownum b from all_objects where rownum<4);A||'+'||B||'='||(A+B)------------------------------------------------------------------------------------------------------------------------1+1=21+2=31+3=42+1=32+2=42+3=53+1=43+2=53+3=69 rows selected.

2. Create a 5*5 multiplication table

with multiplier as (select rownum n from dual connect by rownum<6)select a.n||'*'||b.n||'='||(a.n*b.n) from multiplier a, multiplier b

3. Use dual tables instead of connect by to construct 1 to 128

with a as (select 1 from dual union all select 1 from dual)select rownum from a,a,a,a,a,a,a

4. There are several cows and geese on the side of the pond. Xiaohua sees 15 heads and 42 legs in total. How many cows and geese have?

with a as (select 1 from dual union all select 1 from dual),b as (select rownum n from a,a,a,a)select x.n num_of_bull, y.n num_of_goose from b x, b y where x.n*4+y.n*2=42 and x.n+y.n=15

5. buy chicken and rabbit for a hundred dollars: three old hens, four hens, five rabbits, two white rabbits, and four white rabbits. A total of 100 animals are required to be bought, in addition, there are no less than 240 entries and no more than 320 entries. If you spend 100 yuan to buy these animals, you must buy at least one for each type of animals and the money is just enough to output all possible information.

with t as (select 1 from dual union all select 1 from dual),t1 as (select rownum n from t,t,t,t,t)select a.n lmj,5*b.n xmj,c.n dbt,4*d.n xbt from t1 a,t1 b,t1 c,t1 d where 3*a.n+b.n*4+c.n*2+d.n*3=100 and a.n+5*b.n+c.n+4*d.n=100 and (2*a.n+10*b.n+4*c.n+16*d.n between 240 and 320) and a.n<>0 and b.n<>0 and c.n<>0 and d.n<>0;

6. Each employee's salary (SAL) corresponds to a salary level (GRADE field in the SALGRADE table). which salary level has the most employees? Output the salary level information. This question needs to be answered in three different ways.

Method 1:

select * from salgrade where grade=(select grade from (select s.grade,count(*) from emp e,salgrade s where e.sal between s.losal and s.hisal group by s.grade order by 2 desc) where rownum=1);

Method 2:

with t as (select s.grade,count(*) num from emp e,salgrade s where e.sal between s.losal and s.hisal group by s.grade),t1 as (select max(num) maxnum from t)select s.* from salgrade s,t,t1 where s.grade=t.grade and t.num=t1.maxnum;

Method 3:

select * from salgrade where exists (select 1 from (select grade from (select s.grade,count(*) from emp e,salgrade s where e.sal between s.losal and s.hisal group by s.grade order by 2 desc) where rownum=1) s where s.grade=salgrade.grade);