Oracle on-machine operation problem Finishing

Each topic contains some knowledge points, carefully do the problem slowly understand it!

To find out the number of records per table in the current user mode, take the Scott user as an example, the results should be as follows:
DEPT ........ ................... 4
Emp................................... 14
BONUS ........ ................. 0
Salgrade ......... ............. 5

Declare    type Tab_name is Table of VARCHAR2 (+) index by Binary_integer;    T_name Tab_name;    Coun number;    STR varchar (+); Begin    Select table_name Bulk collect into T_name from User_tables;    For I in T_name.first. T_name.last loop        str: = ' SELECT count (*) from ' | | t_name (i);        Execute immediate str into coun;        Dbms_output.put_line (T_name (i) | | ... ' | | Coun);    End Loop;end;

A CC table data is as follows:

C1 C2

--------------

1 West

1 Ann

1 of

2 days

2 Gas

3 good

„„

Converted to

1 of Xian

2 Weather

3 good

Requirements: Can not change the table structure and data content, only in the end through the Select Show this query results

With temp as (select C1,c2,rownum C3 from cc) Select Listagg (C2, ") within group (order by C3) from temp GROUP by C1

Use an SQL statement to query the top three data for each department in the Scott.emp table, showing the following results:
DEPTNO SAL1 SAL2 SAL3
------ ---------- ---------- -------------------------------------
10 5000 2450 1300
20 3000 2975 1100
30 2850 1600 1500
Then, the statement is:

Select Deptno,max (Decode (rn,1,sal)) Sal1,max (decode (rn,2,sal)) Sal2,max (decode (rn,3,sal)) Sal3 from  (select Deptno,sal,row_number () over (partition by deptno ORDER BY Sal Desc nulls last) rnfrom EMP) where rn<=3 GROUP by Deptno

Table NBA records the name and year of the NBA (team VARCHAR2, y number (4)) Championship team:
TEAM Y
-------------------- ------------------------------
Piston 1990
Bull 1991
Bull 1992
Bull 1993
Rocket 1994
Rocket 1995
Bull 1996
Bull 1997
Bull 1998
Spurs 1999
Lakers 2000
Lakers 2001
Lakers 2002
Spurs 2003
Piston 2004
Spurs 2005
Heat 2006
Spurs 2007
Celtic 2008
Lakers 2009
Lakers 2010
Please write out an SQL statement to find out which of the successive champions have been won during this period, and the result of the successive years is as follows:
TEAM B E
-------------------- ---------- --------------------------------
Bull 1991 1993
Rockets 1994 1995
Bull 1996 1998
Lakers 2000 2002
Lakers 2009 2010

Query the current employee's previous employee and the last employee's salary variance with the current employee:

Select Ename,hiredate,sal,deptno,sal-pre_sal,sal-nex_sal from (select Ename,hiredate,sal,deptno,lag (sal,1,0) over ( ORDER by HireDate) Pre_sal,lead (sal,1,0) through (order by HireDate) Nex_sal from EMP)

Table B

C1 C2

------------   ------

2005-01-01 on 1

2005-01-01 on 3

2005-01-02 on 5

The result of processing the data requires:

2005-01-01 on 4

2005-01-02 on 5

Total 9 complete with one sentence of sql:

Select NVL (c1, ' total '), sum (C2) from B Group Rollup (C1)

There is a table A (x number), y number (20))

Insert 1-Beginning 10 million consecutive records into the table with the fastest and most efficient SQL

DECLARE    num number;begin    INSERT into a select Rownum,rownum from dual connect by level < = 100000;    For i in 1..10 loop        select Max (x) to Num from A;        Insert into a select Rownum+num,rownum+num from dual          connect by Level < = 100000*i  ;   end loop; end;

　　

　　

　　

　　

　　

　　

Oracle on-machine operation problem Finishing