Introduction to Oracle

1
SQL Language
Selected Overview
2
Creating Tables Syntax
CREATE TABLE schema.table (column datatype
DEFAULT expr column_constraint, ...
table_constraint)

• You must have specific privileges
• CREATE TABLE
• A storage area

3
Constraint Syntax

• Column-constraint level
• Table-constraint level

column CONSTRAINT constraint_name
constraint_type,
column,... CONSTRAINT constraint_name
constraint_type (column, ...),
4
The NOT NULL Constraint

• Ensures that null values are not permitted for
  the column
• Is defined at the column-constraint level
• Example

CREATE TABLE friend... phone VARCHAR2(15) NOT
NULL,... last_name VARCHAR2(25) CONSTRAINT
friend_last_name_nn NOT NULL,...
5
The UNIQUE Constraint

• Designates a column or combination of columns so
  that no two rows in the table can have the same
  value for this key
• Allows null values if the UNIQUE key is based on
  a single column
• Is defined at either the table or
  column-constraint level
• Automatically creates a UNIQUE index

... phone VARCHAR2(10) CONSTRAINT
s_emp_phone_uk UNIQUE,...
6
The PRIMARY KEY Constraint

• Creates a primary key for the table only one
  primary key is allowed for each table
• Enforces uniqueness of columns
• Does not allow null values in any part of the
  primary key
• Is defined at either the table or column
  constraint level
• Automatically creates a UNIQUE index

... id NUMBER(7) CONSTRAINT s_emp_id_pk
PRIMARY KEY,...
7
The FOREIGN KEY Constraint

• Designates a column or combination of columns as
  a foreign key
• Establishes a relationship between the primary or
  unique key in the same table or between tables
• Is defined at either the table or column
  constraint level
• Must match an existing value in the parent table
  or be NULL

... dept_id NUMBER(7) CONSTRAINT
s_emp_dept_id_fk REFERENCES s_dept(id),...
8
FOREIGN KEY Constraint Keywords

• FOREIGN KEY
• Defines the column in the child table at the
  table constraint level
• REFERENCES
• Identifies the table and column in the parent
  table
• ON DELETE CASCADE
• Allows deletion in the parent table and deletion
  of the dependent rows in the child table

9
Create Table Example

• SQLgt CREATE TABLE s_dept 2 (id NUMBER(7)
  3 CONSTRAINT s_dept_id_pk PRIMARY KEY, 4
  name VARCHAR2(25) 5 CONSTRAINT
  s_dept_name_nn NOT NULL, 6 region_id NUMBER(7)
  7 CONSTRAINT s_dept_region_id_fk
  REFERENCES 8 s_region (id), 9
  CONSTRAINT s_dept_name_region_id_uk UNIQUE 10
  (name, region_id))

10
Create Table Example

• SQLgt CREATE TABLE s_emp 2 (id NUMBER(7) 3
  CONSTRAINT s_emp_id_pk PRIMARY KEY, 4
  last_name VARCHAR2(25) 5 CONSTRAINT
  s_emp_last_name_nn NOT NULL,
• 6 first_name VARCHAR2(25), 7
  userid VARCHAR2(8) 8 CONSTRAINT
  s_emp_userid_nn NOT NULL 9 CONSTRAINT
  s_emp_userid_uk UNIQUE, 10 start_date DATE
  DEFAULT SYSDATE, 11 comments VARCHAR2(25), 12
  manager_id NUMBER(7), 13 title VARCHAR2(25),
  14 dept_id NUMBER(7) 15 CONSTRAINT
  s_emp_dept_id_fk REFERENCES 16 s_dept
  (id), 17 salary NUMBER(11,2), 18
  commission_pct NUMBER(4,2) 19 CONSTRAINT
  s_emp_commission_pct_ck CHECK 20
  (commission_pct IN(10,12.5,15,17.5,20)))

11
Adding a Constraint Example

• Add a foreign key constraint to the S_EMP table
  indicating that a manager must already exist as a
  valid employee in the S_EMP table.

SQLgt ALTER TABLE s_emp 2 ADD
CONSTRAINT s_emp_manager_id_fk 3 FOREIGN KEY
(manager_id) 4 REFERENCES s_emp(id) Table
altered.
12
Basic Select Statements Syntax
SELECT DISTINCT ,column alias,.... FROM tab
le WHERE condition(s)

• SELECT identifies what columns
• FROM identifies which table
• WHERE limits what rows

13
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
14
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)
15
LIKE SQL Operator

• You can use the LIKE operator to perform wildcard
  searches of valid search string values.
• Search conditions can contain either literal
  characters or numbers.
• "" denotes none or many characters.
• "_" denotes one character.

SQLgt SELECT last_name 2 FROM s_emp 3
WHERE last_name LIKE 'M'
16
LIKE SQL Operator

• The LIKE operator can be used as a shortcut for
  some BETWEEN comparisons.
• You can combine pattern matching characters.
• You can use the ESCAPE identifier to search for
  "" or "_".

SQLgt SELECT last_name, start_date 2 FROM
s_emp 3 WHERE start_date LIKE '91'
SQLgt SELECT last_name 2 FROM s_emp 3
WHERE last_name LIKE '_a'
17
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
18
Two Types of SQL Functions

• Single row functions
• Character
• Number
• Date
• Conversion
• Multiple row functions
• Group

FUNCTION
SINGLE ROW
MULTI ROW
19
Single Row Functions Syntax

• Single row functions
• Manipulate data items.
• Accept arguments and return one value.
• Act on each row returned.
• Return one result per row.
• Modify the datatype.
• Can be nested.
• Syntax

function_name (columnexpression, arg1,
arg2,...)
20
Character Functions

• LOWER Converts to lowercase
• UPPER Converts to uppercase
• INITCAP Converts to initial capitalization
• CONCAT Concatenates values
• SUBSTR Returns substring
• LENGTH Returns number of characters
• NVL Converts a null value

21
Number Functions

• ROUND Rounds value to specified decimal
• TRUNC Truncates value to specified decimal
• MOD Returns remainder of division

22
Date Functions

• MONTHS_BETWEEN Number of months between two
  dates
• ADD_MONTHS Add calendar months to date
• NEXT_DAY Next day of the date specified
• LAST_DAY Last day of the month
• ROUND Round to date at midnight
• TRUNC Remove time portion from date

23
Managing Null Values

• 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
24
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
25
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.

26
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.

27
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 data type.

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

• All columns in the SELECT list that are not in
  group functions must be in the GROUP BY clause.

SQLgt SELECT title, MAX(salary) 2 FROM s_emp
3 GROUP BY title
29
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.
30
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

31
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
32
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.

33
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.

34
Equijoin Example
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... ...
35
Outer Joins 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
36
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
37
What Is a Subquery?
A subsequery is a SELECT statement embedded in a
clause of another SQL statement.
SELECT Syntax
Main Query
SELECT... FROM... WHERE...
Subquery
SELECT Syntax
(SELECT... FROM... WHERE...)
38
Subqueries Syntax
SELECT select_list FROM table WHERE expr
operator (SELECT select_list FROM table)

• The subquery executes once before the main query.
• The result of the subquery is used by the main
  outer query.

39
How Are Nested Subqueries Processed?

• 1. Nested SELECT statement is executed first.
• 2. Result is passed into condition of main query.

Nested Query
Main Query
SELECT last_name, title FROM s_emp WHERE dept_id

SELECT dept_id FROM s_emp WHERE last_name'Biri'
43
40
Multiple Row Subqueries Example

• A multiple row subquery returns many rows.
• You must use a multiple row operator in the WHERE
  clause, for example the IN operator.

SQLgt SELECT last_name, first_name, title 2
FROM s_emp 3 WHERE dept_in IN 4 (SELECT ID
5 FROM s_dept 6 WHERE name
'Finance' 7 OR region_id 2)
41
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

42
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...)
43
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.
44
Updating Rows in a Table Syntax

• Modify existing rows with the UPDATE command.

UPDATE table SET column value , column
value WHERE condition
45
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.
46
Creating a View Example

• Create the EMPVU45 view, which contains the
  employee number, last name, and job title for
  employees in department 45.
• Describe the structure of the view by using the
  SQLPlus DESCRIBE command.
• 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.
47
SQL Language
End of overview