Introduction to Oracle

1
SQL Language
Special Issues
2
What Is a View?
ID LAST_NAME FIRST_NAME TITLE
DEPT_ID -- ------------ ----------
-------------------- ------- 1 Velasquez
Carmen President 50 2 Ngao
LaDoris VP, Operations 41 3
Nagayama Midori VP, Sales
31 4 Quick-To-See Mark VP, Finance
10 5 Ropeburn Audry VP,
Administration 50 6 Urguhart Molly
Warehouse Manager 41 7 Menchu
Roberta Warehouse Manager 42 8 Biri
Ben Warehouse Manager 43 9
Catchpole Antoinette Warehouse Manager
44 10 Havel Marta Warehouse Manager
45 11 Magee Colin Sales
Representative 31 12 Giljum Henry
Sales Representative 32 13 Sedeghi
Yasmin Sales Representative 33 14 Nguyen
Mai Sales Representative 34 15
Dumas Andre Sales Representative
35 16 Maduro Elena Stock Clerk
41 17 Smith George Stock Clerk
41 18 Nozaki Akira Stock
Clerk 42 19 Patel Vikram
Stock Clerk 42 20 Newman Chad
Stock Clerk 43 21 Markarian
Alexander Stock Clerk 43 22 Chang
Eddie Stock Clerk 44 23
Patel Radha Stock Clerk
34 24 Dancs Bela Stock Clerk
45 25 Schwartz Sylvie Stock Clerk
45
S_EMP Table
EMPVU45 View
ID LAST_NAME TITLE -- -----------
--------------- 10 Havel Warehouse
Manager 24 Dancs Stock Clerk 25 Schwartz
Stock Clerk
3
Creating a View Example

• Create the EMPVU45 view, which contains the
  employee number, last name, and job title for
  employees in department 45.
• Display data from the view by entering a SELECT
  statement against the view.

SQLgt CREATE VIEW empvu45 2 AS SELECT id,
last_name, title 3 FROM s_emp 4
WHERE dept_id 45 View created.
4
Duplicate Rows

• The default display of queries is all rows
  including duplicate rows.
• Eliminate duplicate rows by using DISTINCT in the
  SELECT clause.

SQLgt SELECT name 2 FROM s_dept
SQLgt SELECT DISTINCT name 2 FROM s_dept
5
DISTINCT with Multiple Columns

• DISTINCT applies to all columns in the SELECT
  list.
• When DISTINCT is applied to multiple columns, the
  result represents the distinct combination of the
  columns.

SQLgt SELECT DISTINCT dept_id, title 2 FROM
s_emp
6
The ORDER BY Clause

• Sort rows with the ORDER BY clause.
• ASC ascending order, default.
• DESC descending order.
• ORDER BY clause is last in SELECT command.

SQLgt SELECT last_name, dept_id, start_date 2
FROM s_emp 3 ORDER BY last_name
7
The ORDER BY Clause

• The default sort order is ascending.
• The sort order can be reversed by using DESC.
• You can sort by expressions or aliases.

SQLgt SELECT last_name EMPLOYEE, start_date 2
FROM s_emp 3 ORDER BY EMPLOYEE DESC

• Null values are displayed
• Last for ascending sequences.
• First for descending sequences.

8
Sorting by Multiple Columns

• You can order by position to save time.

SQLgt SELECT last_name, salary 12 2 FROM
s_emp 3 ORDER BY 2

• You can sort by multiple columns.

SQLgt SELECT last_name, dept_id, salary 2 FROM
s_emp 3 ORDER BY dept_id, salary DESC

• The order of ORDER BY list is order of sort.
• You can sort by a column that is not in the
  SELECT list.

9
Comparison and Logical Operators

• Logical comparison operators
• gt gt lt lt
• SQL comparison operators
• BETWEEN ... AND...
• IN(list)
• LIKE
• IS NULL
• Logical operators
• AND
• OR
• NOT

10
Negating Expressions

• Sometimes it is easier to exclude rows you know
  you
• do not want.
• Logical Operators
• ! ltgt
• SQL Operators
• NOT BETWEEN
• NOT IN
• NOT LIKE
• IS NOT NULL

11
BETWEEN and IN SQL Operators

• Use the BETWEEN operator to test for values
  between, and inclusive of, a range of values.

SQLgt SELECT first_name, last_name, start_date 2
FROM s_emp 3 WHERE start_date BETWEEN
'09-may-91' 4 AND '17-jun-91'

• Use IN to test for values in a list.

SQLgt SELECT id, name, region_id 2 FROM
s_dept 3 WHERE region_id IN (1,3)
12
IS NULL SQL Operator

• Test for null values with the IS NULL operator.
• Do not use the operator.

SQLgt SELECT id, name, credit_rating 2 FROM
s_customer 3 WHERE sales_rep_id IS NULL
13
Rules of Precedence
Override rules of precedence by using parentheses.

• Order Evaluated Operator
• 1 All comparison operators.
• 2 AND
• 3 OR

14
Rules of Precedence Examples

• Display information for those employees in
  department 44 who earn 1000 or more, and any
  employees in department 42.

SQLgt SELECT last_name, salary, dept_id 2 FROM
s_emp 3 WHERE salary gt 1000 4 AND dept_id
44 5 OR dept_id 42

• Display information for those employees in
  department 44 or 42 who earn 1000 or more.

SQLgt SELECT last_name, salary, dept_id 2 FROM
s_emp 3 WHERE salary gt 1000 4
AND (dept_id 44 5 OR dept_id 42)
15
Concatenation Operator

• The concatenation operator
• Columns or character strings are put together
  (concatenated).
• The operator is represented by two vertical bars
  .(MS Access uses the operator)
• Creates a resultant string value.

16
Concatenation Operator Example
SQLgt SELECT first_name ' ' last_name 2 ',
' title "Employees" 3 FROM s_emp
Employees ----------------------------------------
---- Carmen Velasquez, President LaDoris Ngao,
VP, Operations Midori Nagayama, VP, Sales Mark
Quick-To-See, VP, Finance Audry Ropeburn, VP,
Administration Molly Urguhart, Warehouse
Manager ...
17
Managing Null Values in expresssions

• NULL is a value that is unavailable, unassigned,
  unknown, or inapplicable.
• NULL is not the same as zero or space.
• Arithmetic expressions containing a null value
  evaluate to NULL.

SQLgt SELECT last_name, title,
2 salarycommission_pct/100 COMM 3 FROM s_emp
18
NVL Function

• Convert NULL to an actual value with NVL.
• Datatypes to use are date, character, and number.
• Datatypes must match.
• NVL (start_date, '01-JAN-95')
• NVL (title, 'No Title Yet')
• NVL (salary, 1000)

SQLgt SELECT last_name, title,
2 salaryNVL(commission_pct,0)/100 COMM 3 FROM
s_emp
19
Group Functions

• Group functions operate on sets of rows to give
  one result per group.
• Group functions appear in both SELECT lists and
  HAVING clauses.
• The GROUP BY clause in the SELECT statement
  divides rows into smaller groups.
• The HAVING clause restricts result groups.

20
GROUP BY and HAVING Clauses in the SELECT
Statement Syntax
SELECT column, group_function FROM table WHERE co
ndition GROUP BY group_by_expression HAVING gr
oup_condition ORDER BY column

• GROUP BY divides rows into smaller groups.
• HAVING further restricts the result groups.

21
Group Functions

• AVG (DISTINCTALLn)
• COUNT (DISTINCTALLexpr)
• MAX (DISTINCTALLexpr)
• MIN (DISTINCTALLexpr)
• STDDEV (DISTINCTALLn)
• SUM (DISTINCTALLn)
• VARIANCE (DISTINCTALLn)

22
Group Functions Example

• You can use AVG and SUM against columns that can
  store numeric data.

SQLgt SELECT AVG(salary), MAX(salary),
2 MIN(salary), SUM(salary) 3 FROM s_emp 4
WHERE UPPER(title) LIKE 'SALES'

• You can use MAX and MIN for any datatype.

SQLgt SELECT MIN(last_name), MAX(last_name) 2
FROM s_emp
23
The GROUP BY Clause Example

• All columns in the SELECT list that are not in
  group functions must be in the GROUP BY clause.
• The GROUP BY column does not have to be in the
  SELECT clause.
• The results are more meaningful if the GROUP BY
  column is in the SELECT clause.

SQLgt SELECT title, MAX(salary) 2 FROM s_emp
3 GROUP BY title
24
Display Specific Groups by Using the HAVING Clause
SQLgt COLUMN ANNUAL SALARY FORMAT
99,999.99SQLgt SELECT title, 12 AVG(salary)
ANNUAL SALARY, 2 COUNT() NUMBER OF
EMPLOYEES 3 FROM s_emp 4 GROUP BY title
5 HAVING COUNT() gt 2
HAVING clause (Restrict Groups)
TITLE ANNUAL SALARY NUMBER OF EMPLOYEES----------
---------- -------------- -------------------Sale
s Representative 17,712.00 5Stock
Clerk 11,388.00 10Warehouse Manager 14,776.80 5

Display specific groups of job titles as
restricted in the HAVING clause.
25
Joining Tables - What Is a Join?

• A join is used to query data from more than one
  table.
• Rows are joined using common values, typically
  primary and foreign key values.
• Join methods
• Equijoin
• Non-equijoin
• Outer join
• Self join
• Set operators

26
Relations Between Tables
S_EMP Table ID LAST_NAME DEPT_ID --
--------------- ------- 1 Velasquez 50 2
Ngao 41 3 Nagayama 31 4 Quick-To-See 10 5
Ropeburn 50 6 Urguhart 41 7 Menchu 42 8
Biri 43 9 Catchpole 44 10 Havel 45 11
Magee 31 12 Giljum 32 13 Sedeghi 33 14
Nguyen 34 15 Dumas 35 16 Maduro 41
S_DEPT Table ID NAME REGION_ID --
--------------- --------- 30 Finance
1 31 Sales 1 32 Sales
2 43 Operations 3 50
Administration 1
S_REGION Table ID NAME -- ---------------------
1 North America 2 South America 3 Africa /
Middle East 4 Asia 5 Europe
27
Cartesian Product

• A Cartesian product is formed when
• A join condition is omitted.
• A join condition is invalid.
• All rows in the first table are joined to all
  rows in the second table.
• To avoid a Cartesian product, always include a
  valid join condition in a WHERE clause.

28
Simple Join Query Syntax
SELECT table.column, table.column FROM table1,
table2 WHERE table1.column1 table2.column2

• Write the join condition in the WHERE clause.
• Precede each column name with the table name for
  clarity.
• Column names must be prefixed with the table name
  when the same column name appears in more than
  one table.

29
Equijoin Columns must match
S_EMP
S_DEPT
LAST_NAME DEPT_ID ID NAME--------- ------- -- --
-------------Velasquez 50 50 AdministrationNgao
41 41 OperationsNagayama 31 31 SalesRopeburn 50
50 AdministrationUrguhart 41 41 OperationsMenchu
42 42 OperationsBiri 43 43 OperationsHavel 45 4
5 Operations... ...
30
Equijoin Example

• Equijoins are most often used when a column in
  one table corresponds directly to a column in
  another table.
• The join condition includes the equal ()
  operator.
• Alternative (new) syntax

SQLgt SELECT e.ename, e.job, e.sal,
d.dname 2 FROM emp e, dept d 3 WHERE
e.deptno d.deptno
SQLgt SELECT e.ename, e.job, e.sal,
d.dname 2 FROM emp e INNER JOIN dept d 3 ON
e.deptno d.deptno
31
Qualifying Ambiguous Column Names

• Use table prefixes to qualify column names that
  are in multiple tables.
• Improve performance by using table prefixes.
• Distinguish columns that have identical names but
  reside in different tables by using column
  aliases.

32
Non-Equijoins Example

• Non-equijoins result when no column in one table
  corresponds directly to a column in the second
  table.
• The join condition contains an operator other
  than equal ().

SQLgt SELECT e.ename, e.job, e.sal,
s.grade 2 FROM emp e, salgrade s 3 WHERE
e.sal BETWEEN s.losal AND s.hisal
33
Outer Joins Old Syntax

• Use an outer join to see rows that do not
  normally meet the join condition.
• Outer join operator is the plus sign ().
• Place the operator on the side of the join where
  there is no value to join to.

SELECT table.column, table.column FROM table1,
table2 WHERE table1.column() table2.column
34
Outer Joins New Syntax

• Use LEFT JOIN or RIGHT JOIN operators instead of
  INNER JOIN.
• LEFT JOIN returns all rows in first table.
• RIGHT JOIN returns all rows in second table.

SELECT table.column, table.column FROM table1
LEFT JOIN table2 ON table1.column
table2.column
SELECT table.column, table.column FROM table1
RIGHT JOIN table2 ON table1.column
table2.column
35
Self Joins Example

• Join rows in a table to rows in the same table by
  using a self join.
• Simulate two tables in the FROM clause by
  creating two aliases for the table.

SQLgt SELECT worker.last_name' works for ' 2
manager.last_name 3 FROM s_emp worker, s_emp
manager 4 WHERE worker.manager_id manager.id
36
Data ManipulationStatements
Description Adds a new row to the table. Modifies
existing rows in the table. Removes existing rows
from the table.

• Statement
• INSERT
• UPDATE
• DELETE

37
Inserting New Rows into a Table Syntax

• Add new rows to a table by using the INSERT
  command.
• Only one row is inserted at a time with this
  syntax.

INSERT INTO table (column , column...) VALUES
(value , value...)
38
Inserting New Rows Example

• Insert a new row containing values for each
  column.
• Optionally list the columns in the INSERT clause.
• List values in the default order of the columns
  in the table.
• Enclose character and date values within single
  quotation marks.

SQLgt INSERT INTO s_dept 2 VALUES (11,
'Finance', 2) 1 row created.
39
Copying Rows from Another Table

• Write your INSERT command with a subquery.
• Do not use the VALUES clause.
• Match the number of columns in the INSERT clause
  to those in the subquery.

SQLgt INSERT INTO history(id, last_name, salary,
2 title, start_date) 3 SELECT id,
last_name, salary, 4 title, start_date
5 FROM s_emp 6 WHERE start_date lt
'01-JAN-94' 10 rows created.
40
Updating Rows in a Table Syntax

• Modify existing rows with the UPDATE command.

UPDATE table SET column value , column
value WHERE condition
41
Updating Rows Examples

• Transfer employee number 2 to department 10.

SQLgt UPDATE s_emp 2 SET dept_id 10 3
WHERE id 2 1 row updated.

• Transfer employee number 1 to department 32 and
  change the salary to 2550.

SQLgt UPDATE s_emp 2 SET dept_id 32,
salary 2550 3 WHERE id 1 1 row updated.
42
Updating All Rows in the Table

• All rows in the table will be updated if you do
  not add the WHERE clause.

SQLgt UPDATE s_emp 2 SET commission_pct
10 25 rows updated.
43
Deleting Rows from a Table Syntax

• Remove existing rows by using the DELETE command.

DELETE FROM table WHERE condition

• Remove all information about employees who
  started after January 1, 1996.

SQLgt DELETE FROM s_emp 2 WHERE start_date gt
3 TO_DATE('01.01.1996', 'DD.MM.YYYY') 1
row deleted.
44
Deleting Rows Example

• Delete all the rows in the table by excluding the
  WHERE clause.

SQLgt DELETE FROM test 25,000 rows deleted.

• Confirm the deletions.

SQLgt SELECT 2 FROM test no rows selected