Introduction to Oracle SQL
Contents
1. Relational Databases and ER Models
2. SQL
3. The HR Database Schema in Oracle
4. Introducing SELECT Statement
The WHERE Clause
Sorting with ORDER BY
Selecting Data From Multiple Tables
Contents (2)
5. Selecting Data From Multiple Tables
Natural Joins
Join with USING Clause
Inner Joins with ON Clause
Left, Right and Full Outer Joins
Cross Joins
6. Nested SELECT Statements
7. Aggregating Data
Group Functions and GROUP BY
Contents (3)
8.
Oracle SQL Functions
9.
Oracle Data Types
10. Data Definition Language (DDL)
11. Inserting Data
12. Updating Data
13. Deleting Data
Relational Databases
Short Overview
Relational Database Concepts
The relational model consists of the following:
Collection of tables (called relations)
Set of operators to act on the relations
Data integrity for accuracy and consistency
Definition of a Database
A relational database is a collection of
relations (two-dimensional tables)
Database
Table Name: EMPLOYEES
Table Name: DEPARTMENTS
EMPLO
YEE_ID
FIRST_
NAME
LAST_NAME
EMAIL
DEPARTM
ENT_ID
DEPARTMENT_
NAME
MANAGER_ID
100
Svetlin
Nakov
XXX
10
IT
200
101
Rosen
Spasov
YYY
20
Sales
201
102
Danail
Alexiev
ZZZ
50
Finances
124
Entity Relationship Model
Create an entity relationship diagram from
business specifications or narratives
EMPLOYEE
#*
number
*
name
o
job title
assigned to
DEPARTMENT
#*
number
*
name
composed of o
location
Scenario
. . . Assign one or more employees to a
department . . .
. . . Some departments do not yet have
assigned employees . . .
Relating Multiple Tables
Each row of data in a table is uniquely
identified by a primary key (PK)
You can logically relate data from multiple
tables using foreign keys (FK)
Table Name: EMPLOYEES
Table Name: DEPARTMENTS
EMPLOY
EE_ID
FIRST_
NAME
LAST_NAME
DEPARTM
ENT_ID
DEPARTM
ENT_ID
DEPARTMENT_NAME
100
Svetlin
Nakov
80
10
IT
101
Rosen
Spasov
50
20
Sales
102
Danail
Alexiev
90
50
Finances
Primary key
Foreign key
Primary key
Database Terminology
Table Name: EMPLOYEES
Foreign key
column
Field
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
SALARY
DEPART
MENT_ID
100
Mihail
Stoynov
24000
80
101
Miroslav
Nachev
17000
50
102
Danail
Alexiev
103
Radoslav
Ivanov
9000
60
104
Rosen
Spasov
6000
90
Primary key
column
Row
Column
90
Null value
Relational Databases
A relational database:
Can be accessed and modified by
executing Structured Query Language
(SQL) statements
Uses a set of operations to extract
subset of the data
Contains a collection of tables
Relationships are defined between the
tables
Communicating with a DB
SQL statement is
entered
SELECT
department_name
FROM departments
SQL statement is
sent to the database
Database
DEPARTMENT_NAME
IT
Sales
Finances
The result is returned
(usually as a table)
The Structured Query
Language (SQL)
Introduction
What is SQL?
Structured Query Language (SQL)
Declarative language for query and
manipulation of relational data
SQL consists of:
Data Manipulation Language (DML)
SELECT, INSERT, UPDATE, DELETE
Data Definition Language (DDL)
CREATE, DROP, ALTER
GRANT, REVOKE
Entity Relationship (E/R)
Diagrams
The HR Database Schema in Oracle Express
The HR Database Schema in
Oracle 10g
LOCATIONS
PK
I1
I3
FK1,I2
STREET_ADDRESS
POSTAL_CODE
CITY
STATE_PROVINCE
COUNTRY_ID
EMPLOYEES
DEPARTMENTS
LOCATION_ID
PK
DEPARTMENT_ID
PK
EMPLOYEE_ID
FK1
FK2,I1
DEPARTMENT_NAME
MANAGER_ID
LOCATION_ID
I4
I4
U1
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
SALARY
COMMISSION_PCT
MANAGER_ID
DEPARTMENT_ID
FK3,I2
COUNTRIES
PK
COUNTRY_ID
FK1
COUNTRY_NAME
REGION_ID
JOB_HISTORY
PK,FK2,I2
PK
EMPLOYEE_ID
START_DATE
FK3,I3
FK1,I1
END_DATE
JOB_ID
DEPARTMENT_ID
FK2,I3
FK1,I1
JOBS
PK
JOB_ID
REGIONS
PK
REGION_ID
REGION_NAME
JOB_TITLE
MIN_SALARY
MAX_SALARY
SQL Language
Introducing SELECT Statement
Capabilities of SQL SELECT
Projection
Selection
Take some of the columns
Take some of the rows
Table 1
Table 1
Join
Combine
tables by
some
column
Table 1
Table 2
Basic SELECT Statement
SELECT *|{[DISTINCT] column|expression
[alias],...}
FROM table
SELECT identifies what columns
FROM identifies which table
SELECT Example
Selecting all departments
SELECT * FROM DEPARTMENTS
DEPARTM
ENT_ID
DEPARTMENT_NAME
MANAGER_ID
LOCATION_ID
10
Administration
200
1700
20
Marketing
201
1800
50
Shipping
124
1900
Selecting specific columns
SELECT
DEPARTMENT_ID,
LOCATION_ID
FROM DEPARTMENTS
DEPARTMENT_ID
LOCATION_ID
10
1700
20
1800
50
1900
Arithmetic Operations
Arithmetic operators are available:
+, -, *, /
Example:
SELECT LAST_NAME, SALARY, SALARY + 300
FROM EMPLOYEES
LAST_NAME
SALARY
SALARY + 300
King
24000
24300
Kochhar
17000
17300
De Haan
17000
17300
The null Value
A null is a value that is unavailable,
unassigned, unknown, or inapplicable
Not the same as zero or a blank space
Arithmetic expressions containing a null
value are evaluated to null
SELECT LAST_NAME, MANAGER_ID FROM EMPLOYEES
LAST_NAME
MANAGER_ID
King
(null)
Kochhar
100
De Haan
100
NULL is displayed as
empty space or as (null)
Column Alias
Renames a column heading
Useful with calculations
Immediately follows the column name
There is an optional AS keyword
Double quotation marks if contains spaces
SELECT LAST_NAME "Name", 12*SALARY AS
"Annual Salary" FROM EMPLOYEES
Name
Annual Salary
King
288000
Kochhar
204000
Concatenation Operator
Concatenates columns or character strings to
other columns
Is represented by two vertical bars (||)
Creates a resultant column that is a character
expression
SELECT LAST_NAME || JOB_ID AS "Employees"
FROM EMPLOYEES
Employees
KingAD_PRES
KochharAD_VP
De HaanAD_VP
Literal Character Strings
A literal is a character, a number, or a date
included in the SELECT list
Date and character literal values must be enclosed
within single quotation marks
Each character string is output once for each row
returned
SELECT LAST_NAME || ' is a ' || JOB_ID AS
"Employee Details" FROM EMPLOYEES
Employees
King is a AD_PRES
Kochhar is a AD_VP
De Haan is a AD_VP
Removing Duplicate Rows
The default display of queries is all rows,
including duplicate rows
DEPARTMENT_ID
90
SELECT DEPARTMENT_ID
FROM EMPLOYEES
90
60
...
Eliminate duplicate rows by using the
DISTINCT keyword in the SELECT clause
SELECT
DISTINCT DEPARTMENT_ID
FROM EMPLOYEES
DEPARTMENT_ID
90
60
...
UNION and INTERSECT
UNION combines the results from several
SELECT statements
The columns count and types should match
NAME
SELECT FIRST_NAME AS NAME
FROM EMPLOYEES
UNION
SELECT LAST_NAME AS NAME
FROM EMPLOYEES
Abel
Adam
Alana
Alberto
...
INTERSECT makes logical intersection of given
sets of records
Limiting the Rows Selected
Restrict the rows returned by using the WHERE
clause:
SELECT LAST_NAME,
DEPARTMENT_ID FROM
EMPLOYEES WHERE
DEPARTMENT_ID = 90
LAST_NAME
DEPARTMENT_ID
King
90
Kochhar
90
De Haan
90
More examples:
SELECT FIRST_NAME, LAST_NAME, JOB_ID FROM
EMPLOYEES WHERE LAST_NAME = 'Whalen'
SELECT LAST_NAME, SALARY FROM EMPLOYEES
WHERE SALARY <= 3000
Other Comparison Conditions
Using BETWEEN operator to specify a range:
SELECT LAST_NAME, SALARY FROM EMPLOYEES
WHERE SALARY BETWEEN 2500 AND 3000
Using IN / NOT IN operators to specify a set of
values:
SELECT FIRST_NAME, LAST_NAME, MANAGER_ID FROM
EMPLOYEES WHERE MANAGER_ID IN (100, 101, 201)
Using LIKE operator to specify a pattern:
SELECT FIRST_NAME FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'S%'
Other Comparison Conditions (2)
Checking for NULL value:
SELECT LAST_NAME, MANAGER_ID FROM EMPLOYEES
WHERE MANAGER_ID IS NULL
Note: COLUMN=NULL is always false!
Using OR and AND operators:
SELECT LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES
WHERE SALARY >= 1000 AND JOB_ID LIKE '%MAN%'
SELECT LAST_NAME FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
OR LAST_NAME LIKE '%S%'
Sorting with ORDER BY
Sort rows with the ORDER BY clause
ASC: ascending order, default
DESC: descending order
SELECT LAST_NAME,
HIRE_DATE FROM EMPLOYEES
ORDER BY HIRE_DATE
SELECT LAST_NAME,
HIRE_DATE FROM EMPLOYEES
ORDER BY HIRE_DATE DESC
LAST_NAME
HIRE_DATE
King
17-JUN-87
Whalen
17-SEP-87
Kochhar
21-SEP-89
LAST_NAME
HIRE_DATE
Zlotkey
29-JAN-00
Mourgos
16-NOV-99
Grant
24-MAY-99
SQL Language
Selecting Data From Multiple Tables
Data from Multiple Tables
Sometimes you need data from more than
one table:
LAST_NAME
DEPART
MENT_ID
DEPARTM DEPARTMENT_NAME
ENT_ID
King
90
90
Executive
Kochhar
90
20
Marketing
Fay
20
10
Administration
LAST_NAME
DEPARTMENT_NAME
King
Executive
Fay
Marketing
Kochhar
Executive
Cartesian Product
This will produce Cartesian product:
SELECT LAST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
The result:
LAST_NAME
DEPARTMENT_NAME
King
Executive
King
Marketing
King
Administration
Kochhar
Executive
Kochhar
Marketing
..
..
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
Types of Joins
Natural joins
Join with USING clause
Inner joins with ON clause
Left, right and full outer joins
Cross joins
Natural Join
The NATURAL JOIN combines the rows from
two tables that have equal values in all
matched by name columns
SELECT DEPARTMENT_ID, DEPARTMENT_NAME,
LOCATION_ID, CITY
FROM DEPARTMENTS NATURAL JOIN LOCATIONS
DEPARTM
ENT_ID
DEPARTMENT_NAME
LOCATION_ID
CITY
60
IT
1400
Southlake
50
Shipping
1500
San Francisco
10
Administration
1700
Seattle
90
Executive
1700
Seattle
...
...
...
...
Join with USING Clause
If several columns have the same names we can
limit the NATURAL JOIN to only one of them by
the USING clause:
SELECT E.EMPLOYEE_ID, E.LAST_NAME,
D.LOCATION_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
USING (DEPARTMENT_ID)
EMPLOYEE_ID
LAST_NAME
LOCATION_ID
DEPARTMENT_NAME
102
De Haan
1700
Executive
103
Hunold
1400
IT
104
Ernst
1400
IT
...
...
...
...
Inner Join with ON Clause
To specify arbitrary conditions or specify
columns to join, the ON clause is used
Such JOIN is called also INNER JOIN
SELECT E.EMPLOYEE_ID, E.LAST_NAME,
E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.LOCATION_ID
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
EMPLOYEE_ID
LAST_NAME
DEPARTM DEPARTM LOCATION_ID
ENT_ID
ENT_ID
200
Whalen
10
10
1700
201
Hartstein
20
20
1800
202
Fay
20
20
1800
INNER vs. OUTER Joins
The join of two tables returning only matched
rows is an inner join
A join between two tables that returns the
results of the inner join as well as unmatched
rows from the left (or right) table is a left (or
right) outer join
A join between two tables that returns the
results of an inner join as well as the results
of a left and right join is a full outer join
INNER JOIN
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS
MANAGER_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.EMPLOYEE_ID=D.MANAGER_ID
MANAGER_NAME
DEPARTMENT_ID
DEPARTMENT_ NAME
Jennifer Whalen
10
Administration
Michael Hartstein
20
Marketing
Den Raphaely
30
Purchasing
Susan Mavris
40
Human Resources
Adam Fripp
50
Shipping
Alexander Hunold
60
IT
Hermann Baer
70
Public Relations
...
...
...
LEFT OUTER JOIN
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS
MANAGER_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
ON E.EMPLOYEE_ID=D.MANAGER_ID
MANAGER_NAME
DEPARTMENT_ID
DEPARTMENT_ NAME
Jennifer Whalen
10
Administration
Michael Hartstein
20
Marketing
Den Raphaely
30
Purchasing
Clara Vishney
(null)
(null)
Jason Mallin
(null)
(null)
Hazel Philtanker
(null)
(null)
Nanette Cambrault
(null)
(null)
...
...
...
RIGHT OUTER JOIN
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS
MANAGER_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D
ON E.EMPLOYEE_ID=D.MANAGER_ID
MANAGER_NAME
DEPARTMENT_ID
DEPARTMENT_ NAME
Jennifer Whalen
10
Administration
Michael Hartstein
20
Marketing
Den Raphaely
30
Purchasing
(null)
120
Treasury
(null)
130
Corporate Tax
(null)
140
Control And Credit
(null)
150
Shareholder Services
...
...
...
FULL OUTER JOIN
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS
MANAGER_NAME, D.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS D
ON E.EMPLOYEE_ID=D.MANAGER_ID
MANAGER_NAME
DEPARTMENT_ID
DEPARTMENT_ NAME
Jennifer Whalen
10
Administration
Michael Hartstein
20
Marketing
...
...
...
Clara Vishney
(null)
(null)
Jason Mallin
(null)
(null)
...
...
...
(null)
150
Shareholder Services
...
...
...
Three-Way Joins
A three-way join is a join of three tables
SELECT E.EMPLOYEE_ID, CITY, DEPARTMENT_NAME
FROM EMPLOYEES E
JOIN DEPARTMENTS D
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
JOIN LOCATIONS L
ON D.LOCATION_ID = L.LOCATION_ID
EMPLOYEE_ID
CITY
DEPARTMENT_ NAME
103
Southlake
IT
104
Southlake
IT
124
San Francisco
Administration
...
...
...
Cross Join
The CROSS JOIN clause produces the crossproduct of two tables
Same as a Cartesian product
Not often used
SELECT LAST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES CROSS JOIN DEPARTMENTS
LAST_NAME
DEPARTMENT_NAME
King
Executive
King
Marketing
King
Administration
Kochhar
Executive
..
..
Additional Conditions
You can apply additional conditions in the
WHERE clause:
SELECT E.EMPLOYEE_ID,
E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME,
E.MANAGER_ID, E.DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D ON
(E.DEPARTMENT_ID = D.DEPARTMENT_ID)
WHERE E.MANAGER_ID = 149
EMPLO
YEE_ID
NAME
MANAG
ER_ID
DEPARTME DEPARTMENT_NAME
NT_ID
174
Ellen Abel
149
80
Sales
175
Alyssa Hutton
149
80
Sales
...
...
...
...
...
SQL Language
Nested SELECT Statements
Nested SELECT Statements
SELECT statements can be nested in the
where clause
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY =
(SELECT MAX(SALARY) FROM EMPLOYEES)
SELECT FIRST_NAME, LAST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID FROM DEPARTMENTS
WHERE DEPARTMENT_NAME='Accounting')
Note: Always prefer joins to nested SELECT
statements (better performance)
Using the EXISTS operator
Using the EXISTS operator in SELECT
statements
Find all employees that have worked in the
past in the department #110
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES E
WHERE EXISTS
(SELECT EMPLOYEE_ID FROM JOB_HISTORY JH
WHERE DEPARTMENT_ID = 110 AND
JH.EMPLOYEE_ID=E.EMPLOYEE_ID)
SQL Language
Aggregating Data
Group Functions
Group functions operate on sets of rows to
give one result per group
EMPLOYEE_ID
SALARY
100
24000
101
17000
102
17000
103
9000
104
6000
...
...
MAX(SALARY)
24000
Group Functions in SQL
COUNT(*) count of the selected rows
SUM(column) sum of the values in given
column from the selected rows
AVG(column) average of the values in
given column
MAX(column) the maximal value in given
column
MIN(column) the minimal value in given
column
AVG() and SUM() Functions
You can use AVG() and SUM() for numeric
data types
SELECT AVG(SALARY), MAX(SALARY),
MIN(SALARY), SUM(SALARY)
FROM EMPLOYEES
WHERE JOB_ID LIKE '%REP%'
AVG(SALARY)
MAX(SALARY)
MIN(SALARY)
SUM(SALARY)
8272.72
11500
6000
273000
MIN() and MAX() Functions
You can use MIN() and MAX() for any data
type (number, date, varchar, ...)
SELECT MIN(HIRE_DATE), MAX(HIRE_DATE)
FROM EMPLOYEES
MIN(HIRE_DATE)
MAX(HIRE_DATE)
17-JUN-1987
29-JAN-00
Displaying the first and last employee's name
in alphabetical order:
SELECT MIN(LAST_NAME), MAX(LAST_NAME)
FROM EMPLOYEES
The COUNT() Function
COUNT(*) returns the number of rows in the
result table
SELECT COUNT(*) FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
COUNT(*)
5
COUNT(expr) returns the number of rows with
non-null values for the expr
SELECT COUNT(COMMISSION_PCT)
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
COUNT(COMMI
SION_PCT)
3
Group Functions and Nulls
Group functions ignore null values in the
column
SELECT AVG(COMMISSION_PCT) FROM EMPLOYEES
AVG(COMMISSION_PCT)
.2229
If each null value in COMMISSION_PCT is
considered as 0 and is included in the
calculation, the result will be 0.0425
SQL Language
Group Functions and the
GROUP BY Statement
Creating Groups of Data
EMPLOYEES
DEPARTMENT_ID
SALARY
50
3100
50
3000
50
2600
50
11300
SUM(SALARY)
2600
DEPART
MENT_ID
20
4400
50
11300
20
13000
20
23400
20
6000
40
16500
40
6500
110
20300
40
10000
...
...
110
12000
110
8300
...
...
23400
16500
20300
The GROUP BY Statement
We can divide rows in a table into smaller
groups by using the GROUP BY clause
The syntax:
SELECT
FROM
[WHERE
[GROUP
[ORDER
<columns>, <group_function(column)>
<table>
<condition>]
BY <group_by_expression>]
BY <columns>
The <group_by_expression> is a list of
columns
The GROUP BY Statement
Example of grouping data:
SELECT DEPARTMENT_ID, SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
DEPARTMENT_ID
SUM(SALARY)
100
51600
30
24900
(null)
7000
...
...
The GROUP BY column does not have to be in
the SELECT list
Grouping by Several Columns
EMPLOYEES
DEPART
MENT_ID
JOB_ID
SALARY
20
AD_ASST
4400
20
MK_MAN
13000
20
MK_MAN
12000
30
PU_CLERK
2500
30
PU_CLERK
2500
30
PU_CLERK
2500
30
PU_MAN
11000
30
PU_MAN
11500
30
PU_MAN
10000
30
PU_MAN
11000
...
...
...
4400
EMPLOYEES
25000
DPT_ID
JOB_ID
SUM(SA
LARY)
20
AD_ASST
4400
20
MK_MAN
25000
30
PU_CLERK
7500
30
PU_MAN
43500
...
...
...
7500
43500
Grouping by Several Columns
Example
Example of grouping data by several
columns:
SELECT DEPARTMENT_ID, JOB_ID,
COUNT(EMPLOYEE_ID), SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID, JOB_ID
ORDER BY SUM(SALARY) DESC
DEPARTMENT_ID
JOB_ID
COUNT(
EMPLOYEE_ID)
SUM(SALARY)
80
SA_REP
29
243500
50
SH_CLERK
20
64300
80
SA_MAN
61000
...
...
...
...
Illegal Queries
This SELECT statement is illegal
SELECT DEPARTMENT_ID, COUNT(LAST_NAME)
FROM EMPLOYEES
Can not combine columns with groups
functions unless when using GROUP BY
This SELECT statement is also illegal
SELECT DEPARTMENT_ID, AVG(SALARY)
FROM EMPLOYEES
WHERE AVG(SALARY) > 8000
GROUP BY DEPARTMENT_ID;
Can not use WHERE for group functions
Using GROUP BY with HAVING
Clause
HAVING works like WHERE but is used for the
grouping functions
SELECT DEPARTMENT_ID,
COUNT(EMPLOYEE_ID), AVG(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(EMPLOYEE_ID) BETWEEN 3 AND 6
DEPARTMENT_ID
COUNT(EMPLOYEE_ID)
AVG(SALARY)
100
8600
30
4150
90
19333.33
60
5760
Using Grouping Functions and
Table Joins
We can apply grouping function from joined
tables
SELECT COUNT(*) AS EMPS, DEPARTMENT_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
WHERE
HIRE_DATE BETWEEN '1991-1-1' AND '1997-12-31'
GROUP BY DEPARTMENT_NAME
HAVING COUNT(*) > 5
ORDER BY EMPS DESC
EMPS
DEPARTMENT_NAME
19
Shipping
15
Sales
Oracle Data Types
Overview
Oracle Data Types
NUMBER integer number (up to 38 digits)
NUMBER(p, s) integer/real number of given
precision p and scale s
NUMBER(10, 2) fixed point real number
VARCHAR2(size) string of variable length up
to given size (locale specific)
VARCHAR2(50) string of length up to 50
Oracle Data Types (2)
DATE date between Jan 1, 4712 BC and
Dec 31, 9999 AD
TIMESTAMP date and time (year, month, day,
hour, minute, and seconds)
Precision can be defined
BLOB binary large data object, RAW data (up
to 128 TB)
Can contain photos, videos, etc.
CLOB, NCLOB character large data object (up
to 128 TB)
SQL Language
Data Definition Language (DDL)
Data Definition Language
Types of commands
Defining / editing objects
CREATE
ALTER
DROP
Creating Objects
CREATE / CREATE OR REPLACE commands
CREATE TABLE <name> (<fields
definitions>)
CREATE SEQUENCE <name>
CREATE VIEW <name> AS <select>
CREATE TABLE PERSONS (
PERSON_ID INTEGER NOT NULL,
NAME NVARCHAR2(50) NOT NULL,
CONSTRAINT PERSON_PK PRIMARY KEY(PERSON_ID)
)
CREATE OR REPLACE VIEW PERSONS_TOP_10 AS
SELECT NAME FROM PERSONS WHERE ROWNUM <= 10
Modifying Objects
ALTER command
ALTER TABLE <name> <command>
ALTER
-- Add a foreign key constraint TOWN --> COUNTIRY
ALTER TABLE TOWN
ADD CONSTRAINT TOWN_COUNTRY_FK
FOREIGN KEY (COUNTRY_ID)
REFERENCES COUNTRY(ID) ENABLE
-- Add column COMMENT to the table PERSON
ALTER TABLE PERSONS ADD ("COMMENT" VARCHAR2(800))
-- Remove column COMMENT from the table PERSON
ALTER TABLE PERSONS DROP COLUMN "COMMENT"
Deleting Objects
DROP command
DROP TABLE <name>
DROP SEQUENCE <name>
DROP TRIGGER <name>
DROP INDEX <name>
DROP SEQUENCE SEQ_PERSON
DROP CONSTRAINT TRG_PERSON_INSERT
DROP TABLE PERSONS
SQL Language
Inserting Data in the Tables
Inserting Data
INSERT command
INSERT INTO <table> VALUES (<values>)
INSERT INTO <table>(<columns>) VALUES
(<values>)
INSERT INTO <table> SELECT <values>
INSERT INTO COUNTRY
VALUES ('1', 'Bulgaria', 'Sofia')
INSERT INTO COUNTRY(NAME, CAPITAL)
VALUES ('Bulgaria', 'Sofia')
INSERT INTO COUNTRY(COUNTRY_ID, NAME, CAPITAL)
SELECT NULL, COUNTRY, CAPITAL FROM CAPITALS
SQL Language
Updating Data in the Tables
Updating Data
UPDATE command
UPDATE <table> SET <column=expression>
WHERE <condition>
Note: Don't forget the WHERE clause!
UPDATE PERSONS
SET NAME = 'Updated Name'
WHERE PERSON_ID = 1
UPDATE EMPLOYEES
SET SALARY = SALARY * 1.10
WHERE DEPARTMENT_ID = 3
Updating Joined Tables
Updating joined tables is done by nested
SELECT
UPDATE
(SELECT SALARY
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE D.NAME = 'Accounting')
SET SALARY = SALARY * 1.10
SQL Language
Deleting Data from the Tables
Deleting Data
Deleting rows from a table
DELETE FROM <table> WHERE
<condition>
DELETE FROM PERSONS WHERE PERSON_ID = 1
DELETE FROM PERSONS WHERE NAME LIKE 'S%'
Note: Dont forget the WHERE clause!
Delete all rows from a table at once
TRUNCATE TABLE <table>
TRUNCATE TABLE PERSONS
Problems
1. What is SQL? What is DML? What is DDL? Recite the most
important SQL commands.
2. What is PL/SQL?
3. Start Oracle SQL Developer and connect to the database. Use
the HR user. Examine the major tables in the HR schema.
4. Write a SQL query to find all information about all department.
5. Write a SQL query to find all department names.
6. Write a SQL query to find the salary of each employee by
month, by day and hour. Consider that one month has 20
workdays and each workday has 8 work hours.
Problems (2)
7.
Write a SQL query to find the email addresses of each
employee. Consider that the mail domain is
mail.somecompany.com. Emails should look like
"bernst@mail.somecompany.com". The produced column
should be names "Full Email Address".
8.
Write a SQL query to find all different salaries that are paid to
the employees.
9.
Write a SQL query to find all information about the employees
whose position is "AC_MGR" (Accounting Manager).
10. Write a SQL query to find the names of all employees whose
first name starts with "Sa".
Problems (3)
11. Write a SQL query to find the names of all employees whose
last name contains the character sequence "ei".
12. Write a SQL query to find the names of all employees whose
salary is in the range [3000...5000].
13. Write a SQL query to find the names of all employees whose
salary is 2500, 4000 or 5000.
14. Write a SQL query to find all locations that has no state or
post code defined.
15. Write a SQL query to find all employees that are paid more
than 10000. Order them in decreasing order by salary.
Problems (4)
16. Write a SQL query to find to top 5 best paid employees.
17. Write a SQL query to find all departments and the town of their
location. Use natural join.
18. Write a SQL query to find all departments and the town of their
location. Use join with USING clause.
19. Write a SQL query to find all departments and the town of their
location. Use inner join with ON clause.
20. Write a SQL query to find all the locations and the
departments for each location along with the locations that do
not have department. User right outer join. Rewrite the query
to use left outer join.
Problems (5)
21. Write a SQL query to find the manager of each department.
22. Write a SQL query to find the location of each department
manager.
23. Write a SQL query to find the names of all employees from the
departments "Sales" and "Finance" whose hire year is
between 1995 and 2000.
24. Write a SQL query to find the names and salaries of the
employees that take the minimal salary in the company. Use
nested SELECT statement.
25. Write a SQL query to find the names and salaries of the
employees that take a salary that is up to 10% higher than the
minimal salary for the company.
Problems (6)
26. Write a SQL query to find the average salary in the "Sales"
department.
27. Write a SQL query to find the number of employees in the
"Sales" department.
28. Write a SQL query to find the number of all locations where
the company has an office.
29. Write a SQL query to find the number of all departments that
has manager.
30. Write a SQL query to find the number of all departments that
has no manager.
31. Write a SQL query to find all departments and the average
salary for each of them.
Problems (7)
32. Write a SQL query to find the count of all employees in each
department and for each manager.
33. Write a SQL query to find all managers that have exactly 5
employees. Display their names and the name and location of
their department.
34. Write a SQL query to find all departments along with their
managers. For departments that do not have manager display
"(no manager)".
35. Write a SQL query to find the names of all employees whose
last name is exactly 5 characters long.
36. Write a SQL query to print the current date and time in the
format "day.month.year hour:minutes:seconds".
Problems (8)
37. Write a SQL statement to create a table USERS. Users should
have username, password, full name and last login time.
Choose appropriate data types for the fields of the table.
Define a primary key column with a primary key constraint.
Define a sequence for populating the primary key. Define a
trigger to update the primary key column value before
inserting a record.
38. Write a SQL statement to create a view that displays the users
from the USERS table that have been in the system today. Test
if the view works correctly.
Problems (9)
39. Write a SQL statement to create a table GROUPS. Groups
should have unique name (use unique constraint). Define
primary key and a sequence and a trigger for populating it.
40. Write a SQL statement to add a column GROUP_ID to the table
USERS. Fill some data in this new column and as well in the
GROUPS table. Write a SQL statement to add a foreign key
constraint between tables USERS and GROUPS.
41. Write SQL statements to insert several records in the USERS
and GROUPS tables.
Problems (10)
42. Write SQL statements to insert in the USER table the names of
all employees from the EMPLOYEES table. Combine the first
and last names as a full name. For user name use the email
column from EMPLOYEES. Use blank password.
43. Write a SQL statement that changes the password to NULL for
all USERS that have not been in the system since 10.03.2006.
44. Write a SQL statement that deletes all users without
passwords (NULL or empty password).
Homework
1. Write a SQL query to display the average employee salary by
country.
2. Write a SQL query to display the average employee salary by
region.
3. Write a SQL query to display the country of each employee
along with his name and department city.
4. Write a SQL query to display the country where maximal
number of employees work.
5. Write a SQL query to display the number of managers for each
region and each country.
6. Define table WORKHOURS to store work reports for each
employee (date, task, hours, comments). Don't forget to define
automatically populated primary key (primary key constraint +
sequence + trigger).
Homework (2)
7.
Define foreign key between the tables WORKHOURS and
EMPLOYEE. Add additional column in the employee table if
needed.
8.
Write several SQL statements to fill some data in the
WORKHOURS table.
9.
Write a SQL query to find all the average work hours per week
for each country.
10. Write a SQL query to find all the departments where some
employee worked overtime (over 8 hours/day) during the last
week.
11. Write a SQL query to find all employees that have worked 3 or
more days overtime in the last week. Display their name,
location department and country.
Questions
&
Answers
