Transcript Quick review of SQL

Quick review of SQL
And conversion to Oracle SQL
Assuming…
• You know how to model data using
– An ER diagram
– A class diagram
• You know how to translate entityrelationship models into:
– Tables
– With constraints
SQL
• Structured Query Language
• Made up of the following components:
–
–
–
–
Data Manipulation Language (DML)
Data Definition Language (DDL)
Transaction control
Data Control Language (DCL)
• Oracle SQL complies with the core ANSI/ISO
standard for SQL:2003, but is not completely
compliant.
• It also has extensions, that are not compliant, so
are not portable to non-Oracle databases.
DML
•
•
•
•
SELECT
INSERT
UPDATE
DELETE
DDL
• CREATE
• ALTER
• DROP
– Any object in the database.
• TRUNCATE
– Deletes all rows in a table.
Transaction control
• A database transaction is a unit of work
that leaves the database in a consistent
state.
• To ensure consistency, ongoing work can
be committed, rolled back to a checkpoint,
or rolled back completely.
• COMMIT
• ROLLBACK
Data Control Language
• These control user access to an Oracle
database.
• These instructions include:
– GRANT
– REVOKE
– SET ROLE…
Exercises Lab 1, Week 1
•
•
•
•
•
•
•
•
Learn the datatypes that are used in Oracle 10g.
Retrieve all columns and rows
Retrieve specific columns
Use SQL*Plus DESCRIBE command
Specify an alias for a column
Learn the SQL built-in functions provided by Oracle.
Learn about the DUAL table.
Build SELECT clause expressions
– with the Concatenation String operator
– with arithmetic operators
– with SQL built-in functions.
• Work with NULLs in a SELECT clause expression
• Implement conditional log in SELECT clause expressions.
• Retrieve specific rows from tables.
E
x
e
r
c
i
s
e
s
L
a
b
2
,
W
e
e
k
1
•
–
Building WHERE clause conditions
with relational operators
–
•
With subqueries
Building composite WHERE Clause conditions with
logical operators
L
•
C
•
W
o
m
r
i
m
t
i
n
i
g
t
t
a
i
n
n
g
d
a
s
n
t
d
o
R
r
i
n
o
g
l
l
s
i
n
c
a
g
r
b
b
i
p
1
a
t
s
c
.
k
W
t
r
e
a
n
s
e
a
k
c
t
2
i
o
n
s
Lab 2 Week 2
• Data Definition Language
• DCL
References
‘HanGs-on Oracle Database 10g Express
EGition for WinGows’, Bobrowski, S.,
Oracle Press, McGraw Hill / Osborne,
2006.
‘Oracle Database 10g A Beginner’s guiGe’,
Abramson, I., Abbey, M., Corey, M., Oracle
Press, McGraw Hill / Osborne, 2004.