Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Oracle SQL intro #2

Working with dates. Functions
by

Rodion Mironov

on 2 April 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle SQL intro #2

Lesson agenda: DATE literals in Oracle;
DATE-related functions;
other standard SQL functions;
conditional expressions; Introduction to Oracle SQL. Dates and functions. Date literals Option #1: '01-Sep-2012'
string implicitly converted to date;
depends on environment (session parameters);
bad practice; date + N => N days after date;
date + 1/24 => one hour after date (1/(24*60) and so on can be used);
date1 - date2 => difference in days; Date functions SELECT
SYSDATE
,ADD_MONTHS(SYSDATE,3)
,NEXT_DAY(SYSDATE, 'TUESDAY')
,LAST_DAY(SYSDATE)
,ROUND(SYSDATE,'YEAR')
,TRUNC(SYSDATE, 'MONTH')
,EXTRACT(YEAR from SYSDATE)
FROM dual Case transformation functions Character functions Numeric functions NULL-related functions Functions and expressions CASE <expr>
WHEN <comparison expr1> THEN <return expr1>
...
WHEN <comparison exprN> THEN <return exprN>
ELSE <else expr>
END Simple CASE syntax DECODE syntax DECODE(
<expr>
,<comparison expr1>, <return expr1>
...
,<comparison exprN>, <return exprN>
,<else expr>
) ? Decode
or Case
or NVL expressions! function Searched CASE syntax Practice 1.Select ID and FIRST_NAME of all employees with first names starting with "JA". Search should be case-insensitive.

2.For each employee form the string like "Person #<ID> has/hasn't commission";

3.List all "valuable" employees with one query. Employee is “valuable” if both conditions below are true for him:
He is hired before 2007;
He has salary from 7000 to 10000 or his JOB_ID starts with ‘IT’;

4.Find number of days to New Year. Note: no hardcoded dates are allowed;

5.Calculate how many minutes have passed since month start;

6.Find the second Sunday of next year. Note: no hardcoded dates are allowed;

7.For all employees hired in June (any year) print string like 'John Doe was hired on 01.01.2006';

8. list employees full names and their phone numbers in "encoded" format. Format is:
no dots, just numbers;
each digit should be replaced according to the formula F(d) = MOD(d+5,10) . For example, F(1) = 6, F(7) = 2. отпуск !!! :) CASE
WHEN <condition 1> THEN <return expr1>
...
WHEN <condition N> THEN <return exprN>
ELSE <else expr>
END See you again! Useful links: Conversion functions Option #3: TO_DATE('01.01.2012 13:00:01',
'dd.mm.yyyy hh24:mi:ss')
environment-independent;
the most flexible; Option #2: DATE '2012-09-01'
ANSI format only (yyyy-mm-dd);
no time part available;
short and convenient. Date arithmetic select sysdate, sysdate + 1
from dual;

select sysdate, sysdate-10/(24*60*60)
from dual;

select date'2012-12-31'-sysdate
from dual; select
first_name, last_name
,case SUBSTR(phone_number,1,3)
when '650' then 'Department #50'
when '590' then 'Department #90'
else 'Some other department'
end as dep_info
from employees; select
first_name, last_name, salary,
case
when salary < 8000 then 'Low'
when salary between 8000 and 12000 then 'Medium'
when salary > 12000 then 'High'
else 'Null salary!?'
end
from employees; select
first_name, last_name
,decode(SUBSTR(phone_number,1,3)
,'650','Department #50'
,'590','Department #90'
,'Some other department') as dep_info
from employees; select
first_name, last_name, salary
,DECODE(SIGN(salary-8000)
,-1,'Low'
,0,'8000'
,1,'Medium or high'
,'Null salary!?')
from employees; Datetime literals http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#BABGIGCJ Single-row functions http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions002.htm#CJAJHBIA CASE expression http://docs.oracle.com/cd/E11882_01/server.112/e26088/expressions004.htm#i1033392
Full transcript