SQL practice (2)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

  1 Decode function used to achieve conversion cross table and vertical table

1 SELECT deptno DEPARTMENT_ID, 2 COUNT(*) "Department number", 3 SUM(DECODE(SIGN(sal-9000),-1,0,1)) "High income population(>9000)", 4 SUM(DECODE(SIGN(sal-9000),-1,0,( DECODE(SIGN(sal-12000),1,0,1) ) )) "High income population([9000,12000])", 5 SUM(DECODE(SIGN(sal-6000),-1,0,( DECODE(SIGN(sal-9000),1,0,1) ) )) "High-income([6000,9000])", 6 SUM(DECODE(SIGN(sal-3000),-1,0,( DECODE(SIGN(sal-6000),1,0,1) ) )) "General revenue([3000,6000])", 7 SUM(DECODE(SIGN(sal-3000),-1,1,0)) "Low-income([<3000])" 8 FROM emp 9 GROUP BY deptno;

  The 2.sqlplus environment variable settings:

Spool a.sql will display content into text,

Spool off to close the display settings,

Col empno format A9 set the column width,

col empno heading 'Department number 'to change the column headers,

Col empno justify right sets the column header right aligned,

col sal format $99,The 999 set of data display format

Set echo on set whether to display the contents of the script script execution,

Set newpage n settings page interval n,

Set wrap closes automatically wrap

Set timing on script execution time,

L N display buffer line N content,

A order by Sal desc in the above display command line n after the add command,

c/ename/job/  In the above display line n with job instead of n for ename,

Save a.sql buffer is saved to text

The get a.sql command to load the current buffer,

title 'The employee table 'for the entire table add information,

Set verify off off the old and the new output bind variable values

Only by inputting a bind variable:

select empno,ename,job,&&column_name
from emp
order by &column_name;

Prompt information set custom to bind variables:
accept deptno prompt 'Please enter the department number:'
accept var prompt 'Please enter the password' hide

Predefined values of bound variables:
undefine name

The SQL function

  1.MONTHS_BETWEEN(date1, date2):Judging by month between two date

select months_between(to_date('2013.11.29','yyyy.mm.dd'),sysdate) from dual;


  2.LAST_DAY(date):The function returns contains the date of the last day of the month

select last_day(sysdate) from dual;

30-9 month -13

  3.ROUND(date[,'fmt']):The date D specified in FMT format rounding, FMT as string

select round(sysdate,'mm') from dual;

01-9 month -13

  4.TRUNC(date[, 'fmt']):Returns the specified by the FMT unit of the date of

select trunc(sysdate,'mm') from dual;

01-9 month -13 (due to truncation to September, in front of the DD is automatically filled format)

  5.NEXT_DAY(date, 'char'):Find the next week

select next_day(sysdate,'Monday') from dual;

09-9 month -13

  6.ADD_MONTHS(date, n):For value increasing or decreasing the number of the month from a date

select add_months(sysdate,1) from dual;

06-10 month -13

  7.extract(date_field from [datetime_value]):Find the date field or interval value

select extract(month from sysdate) from dual;



Select initcap(‘hello’) from dual;



Select lower(‘FUN’) from dual;


Select ltrim( ‘xyzadams’,’xyz’) from dual;


  11.Translate(char, from, to)

Select translate(‘jack’,’j’ ,’bk’) from dual;

Back (only replace the corresponding character)

  12.Replace(char, searchstring,[rep string])

Select replace(‘jack and jue’ ,’j’,’bl’) from dual;

Black and blue (the overall replacement)

  13.Instr (string,char, m, n)

Select instr (‘worldwide’,’d’) from dual;

5 (to find the positions of the characters)

  14.Substr (char, m, n)

Select substr(‘abcdefg’,3,2) from dual;

  15.Concat (expr1, expr2)

Select concat (‘Hello’,’ world’) from dual;

Hello world


Select abs(-15) from dual;


Select ceil(44.778) from dual;

45 (returns the smallest integer: greater than he is rounding up)

Select cos(180) from dual;



Select floor(100.2) from dual;

100 (rounded down)

Select power(4,2) from dual;


Select mod(10,3) from dual;


Select round(100.256,2) from dual;

100.26 (this is four to five homes, the default precision to integer)

Select trunc(100.256,2) from dual;


Select sqrt(4) from dual;


Select sign(-30) from dual;


NVL(exp1, exp2) : If Exp1 is null, NVL returns exp2; otherwise returns Exp1
NVL2(exp1,exp2,exp3) :If Exp1 is not null, it returns exp2, if it is, it returns EXP3
NULLIF(exp1,exp2) :If Exp1, exp2 is equal to null is returned, otherwise it returns Exp1

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Alvis at November 29, 2013 - 4:03 PM