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 #1

Intro, simple SELECT statement, NULL and ternary logic
by

Rodion Mironov

on 1 April 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Oracle SQL intro #1

World Takeover Plan Introduction to
Oracle SQL. Part 1. Course agenda:
Introduction and basic SELECT statement;
Functions and conditional expressions;
ordering data and Top-N queries;
grouping data;
joining tables;
subqueries;
set operators;
DML statements;
transactions;
Oracle Data Dictionary and CREATE TABLE statement;
integrity constraints;
views, sequences and synonyms;
indexes. SQL ANSI standard Proprietary implementations and extensions Oracle DML DDL TCL DCL DB2 (IBM) MS SQL MySQL etc. + Procedural
extentions = PL/SQL 1977 1979 1982 2000 2009 Company
founded Oracle version 2 Ported to PC Oracle 9i release Oracle 11g R2 release SELECT SELECT first_name, last_name, job_id
FROM employees SELECT *
FROM employees DUAL - system table to select "from nowhere"
has 1 row and 1 column -- SQL statement may include
SELECT 1
FROM /* two types of comments*/ dual SELECT
first_name || ' ' || last_name || '''s salary is:' Full_Name
,salary*12 as "Salary per Year"
FROM employees Expressions include arithmetics (+,-,/,*)
"||" means concatenation
String 'literals' in single quotes
Column aliases SELECT *|{[DISTINCT] column|expression [[as] alias],...}
FROM table; WHERE SELECT
last_name
,salary
,commission_pct
,salary*commission_pct
FROM employees NULL is special value meaning "unknown" or "inapplicable"
Any arithmetic operation with NULL results in NULL
Use NVL function to substitute NULL with some value
Empty string ('') is equivalent to NULL Pattern-matching condition:
'_' - any one character
'%' - any string, including empty string
use 'escape' to search for '_' or '%' SELECT last_name, job_id
FROM employees
WHERE job_id in ('AD_PRES','AD_VP') IN condition Standard HR schema Our environment:
11g R2;
PL/SQL developer;
Standard HR schema. Practice 1.Select all information from REGIONS table;

2.Find all unique employees’ last names;

3.Find all departments with names starting with "IT";

4.Find full names and salaries of employees who yearn from 8000 to 12000;

5.Find all employees phone numbers that contain substring '123' anywhere in the number;

6.For each employee calculate gross income (salary + commission_pct*salary) and form the string like "<Name> <Surname> earns <sum> USD";

7.Evaluate this logical condition: "not (1=2 or 'dumy' = null) ". It’s not necessary to write the query, but you can use query to check your answer;

8.Evaluate this logical condition: "1 > null and 0 <> 0". It’s not necessary to write the query, but you can use query to check your answer. SELECT DISTINCT job_id
FROM employees SELECT sysdate
FROM dual SELECT *|{[DISTINCT] column|expression [[as] alias],...}
FROM table
[WHERE condition(s)]; SELECT last_name
FROM employees
WHERE employee_id = 100 Comparison operators: =, <>, <, >, >=, <= SELECT first_name, last_name, salary
FROM employees
WHERE last_name = 'King' and not first_name = 'Janette'
or salary > 15000 Standard logical operators: and, or, not
All string comparisons are case-sensitive SELECT first_name, last_name, salary
FROM employees
WHERE salary between 15000 and 30000 [Range] condition SELECT first_name, last_name, job_id
FROM employees
WHERE last_name like 'K%'
and first_name not like '_a%'
and job_id like 'AD\_%' escape '\' See you again! Documentation: http://www.oracle.com/technetwork/database/enterprise-edition/documentation/index.html Useful links: SQL Reference: http://docs.oracle.com/cd/E11882_01/server.112/e26088/toc.htm 2 Day Developer Guide: http://docs.oracle.com/cd/E11882_01/appdev.112/e10766/toc.htm Concepts: http://docs.oracle.com/cd/E11882_01/server.112/e25789/toc.htm Error messages: http://docs.oracle.com/cd/E11882_01/server.112/e17766/toc.htm http://asktom.oracle.com www.sql.ru PL/SQL Developer -> Tools -> Preferences Your domain name here No error means success! Lesson agenda: why are we here and who are all these people ? course overview; SQL, ANSI SQL, PL/SQL; tools and environment, test DB connection; the simplest SQL statement; conditions and expressions; HR schema description; NULLs and ternary logic; Operator precedence:
unary +, -
*, /
||, binary +, - Operator precedence:
unary +, -
*, /
||, binary +, - Conditions precedence:
operators first;
=, !=, <, >, <=, >=
IS [NOT] NULL, LIKE, [NOT] BETWEEN, [NOT] IN, EXISTS
NOT
AND
OR select 'Price and VAT are ' || 100+20 || ' UAH'
from dual; select 'Price and VAT are ' || (100+20) || ' UAH'
from dual; Operators are executed from left to right for equal precedence SELECT last_name
FROM employees
WHERE commission_pct is null
and manager_id is not null NULL NULL conditions:
Oracle uses ternary logic: each logical expression can be true, false or null
any "stanard" (=, >,...) comparition with null is null
use "is [not] null" to check for nulls AND TRUE FALSE NULL TRUE FALSE NULL TRUE FALSE FALSE FALSE OR TRUE FALSE NULL TRUE FALSE NULL TRUE FALSE TRUE TRUE select
q'!It's just another text literal's syntax!' ||
q'{ which allows any text delimeter your like!}'
from dual
Full transcript