Top N Analysis. Functions --ROWNUM pseudocolumn. row number of rows of resultset dynamically assigned by each query. -- i.e. not the "row number" of row in the table (there is no such row numbers). --use to limit number of rows returned: SELECT * FROM employees WHERE Rownum<=10; --careful: Order By applies to the returned rows only! SELECT * FROM employees WHERE Rownum<=10 ORDER BY hire_date; --this is not 10 longest employees, it's the first ten rows sorted --a subquery is needed. "top N reporting/analysis". SELECT * FROM (SELECT * FROM employees ORDER BY employee_id) WHERE ROWNUM<=10; --the outer query is just the row-limiting operation, the inner query does the "real" query. --five most recently hired employees: SELECT * FROM (SELECT hire_date FROM employees ORDER BY hire_date DESC) WHERE ROWNUM<=5; --five most recently hired manager employees: SELECT * FROM (SELECT hire_date,last_name FROM employees WHERE job_id LIKE '%MAN%' ORDER BY hire_date DESC) WHERE ROWNUM<=5; --some numeric functions: Sqrt Power Mod Round trig exp log Round(num,places) SELECT Round(1234.5678,4),Round(1234.5678,3),Round(1234.5678,2),Round(1234.5678,1),Round(1234.5678,0),Round(1234.5678,-1),Round(1234.5678,-2),Round(1234.5678,-3),Round(1234.5678,-4),Round(1234.5678,-5) FROM dual; --some Date functions --what time is it now? SELECT Sysdate,Systimestamp,Localtimestamp,Current_date from Dual; --hired within the last year: SELECT * FROM employees WHERE hire_date>=Sysdate-365; --Extract part_of_date from date SELECT Extract(Year From hire_Date) FROM employees; --also Day Month Hour Minute Second timezone SELECT last_name,hire_date FROM employees WHERE Extract(Year From hire_date)=2000; --change output format SELECT hire_date,To_char(hire_date,'DAY DD MONTH YYYY HH24:MI:SS') FROM employees; --many formatting options... SELECT hire_date,To_char(hire_date,'DS') FROM employees; SELECT hire_date,To_char(hire_date,'DL') FROM employees; SELECT birth,Round(birth,'MONTH'),Trunc(birth,'MONTH') FROM pet; --also DAY YEAR --change default format ALTER SESSION SET nls_date_format = 'DD-MON-YYYY'; ALTER SESSION SET nls_date_format = 'DD MONTH YYYY'; ALTER SESSION SET nls_date_format = 'Dy Mon DD HH24:MI:SS YYYY'; --the last day of the month SELECT hire_date,Last_day(hire_date) FROM employees; SELECT hire_date,Last_day(hire_date)+1 FROM employees; --elapsed time between two dates SELECT hire_date,Months_between(Sysdate,hire_date) FROM employees; --add months to a date SELECT hire_date,Add_months(hire_date,15) FROM employees; --inserting Sysdate includes time, so simple Date column compare won't --equal. Trunc(Sysdate) truncates to begining of day --can insert dates in ANSI format yyyy-mm-dd with DATE Insert Into pet Values('Blue','Steve','dog','f',DATE '2001-11-23',null,24,99); -- create table x(ts timestamp); insert into x values(systimestamp); --some string functions --length of a string SELECT last_name,Length(last_name) FROM employees; SELECT Max(Length(last_name)) FROM employees; --also "length" (# of display characters) of numbers and dates! --to all uppercase, all lowercase, initial capital SELECT last_name,Upper(last_name),Lower(last_name),Initcap(last_name) FROM employees; --search for position of embedded string: Instr(src,srch[,start]) SELECT last_name,Instr(last_name,'son') FROM employees WHERE last_name LIKE '%son%'; --extract a substring: Substr(src,start[,len]) --from 3rd char to end of string: SELECT last_name,Substr(last_name,3) FROM employees; --2 chars starting at 3rd char SELECT last_name,Substr(last_name,3,2) FROM employees; --last char of string SELECT last_name,Substr(last_name,Length(last_name)) FROM employees; --if constraint name: table_column_name_constraintType --extract the 3 components: column_name with or without _ is complicated... ??? --padding SELECT Rpad(last_name,20,'.'),Lpad(salary,10,'*') FROM employees; --Nvl(column,subsitute value for Null SELECT last_name,salary,Nvl(commission_pct,0) FROM employees; --Nvl2(column,subsitute value for not Null,substitute value for Null) SELECT last_name,salary,Nvl2(commission_pct,commission_pct*100,0) FROM employees; -- || concatenation operator SELECT last_name||', '||first_name AS "Employee name" FROM employees;