Quick review of SQL

1
Quick review of SQL

• And conversion to Oracle SQL

2
Assuming

• You know how to model data using
• An ER diagram
• A class diagram
• You know how to translate entity-relationship
  models into
• Tables
• With constraints

3
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 SQL2003, but is not completely
  compliant.
• It also has extensions, that are not compliant,
  so are not portable to non-Oracle databases.

4
DML

• SELECT
• INSERT
• UPDATE
• DELETE

5
DDL

• CREATE
• ALTER
• DROP
• Any object in the database.
• TRUNCATE
• Deletes all rows in a table.

6
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

7
Data Control Language

• These control user access to an Oracle database.
• These instructions include
• GRANT
• REVOKE
• SET ROLE

8
Exercises Lab 1, Week 1

• Learn the datatypes that are used in Oracle 10g.
• Retrieve all columns and rows
• Retrieve specific columns
• Use SQLPlus 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.

9
Exercises Lab 2, Week 1

• Building WHERE clause conditions
• with relational operators
• With subqueries
• Building composite WHERE Clause conditions with
  logical operators
• Grouping records in a querys result set.
• Rolling up groups into supergroups.
• Ordering records in a querys result set
• Building an inner join of two tables
• Building an outer join of two tables.
• Inserting new rows in a table
• Deleting rows from a table.

10
Lab 1 Week 2

• Committing and Rolling back transactions
• Writing and storing scripts.

11
Lab 2 Week 2

• Data Definition Language
• DCL

12
References

• Hands-on Oracle Database 10g Express Edition for
  Windows, Bobrowski, S., Oracle Press, McGraw
  Hill / Osborne, 2006.
• Oracle Database 10g A Beginners guide,
  Abramson, I., Abbey, M., Corey, M., Oracle Press,
  McGraw Hill / Osborne, 2004.