Comprehensive Oracle Database and SQL Study Guide
This study guide provides an exhaustive review of Oracle Database development, Structured Query Language (SQL) syntax, data types, and transaction management based on the provided technical documentation.
--------------------------------------------------------------------------------
1. Introduction to SQL and Oracle Database
SQL (Structured Query Language) is a non procedural database computer language designed for managing data in Relational Database Management Systems (RDBMS). Originally developed by IBM in the mid-1970s and incorporated by Oracle in 1979, SQL handles database navigation and task performance automatically. It is used to query, insert, and update data; format and perform calculations on results; and examine table or object definitions.
Categories of SQL Statements
Oracle SQL statements are divided into several functional categories:
Data Manipulation Language (DML): Statements used to retrieve and manipulate data within existing schema objects (e.g., SELECT, INSERT, UPDATE, DELETE, MERGE).
Data Definition Language (DDL): Statements used to create, alter, rename, and drop database objects (e.g., CREATE, ALTER, DROP, RENAME, TRUNCATE).
Transaction Control Language (TCL): Statements used to manage changes made by DML statements (e.g., COMMIT, ROLLBACK, SAVEPOINT).
Data Control Language (DCL): Statements used to control access and secure the database (e.g., GRANT, REVOKE).
--------------------------------------------------------------------------------
2. Oracle Data Types
A data type defines the category of values a column can hold, ensuring data accuracy, storage efficiency, and performance.
Core Data Types
Data Type
Category
Description
NUMBER(p,s)
Numeric
Stores fixed or floating-point numbers with up to 38 digits of precision (p). Scale (s) determines decimal places.
INTEGER
Numeric
Used for whole numbers.
FLOAT
Numeric
Stores approximate numeric values.
CHAR(size)
Character
Fixed-length character strings (maximum 255 characters).
VARCHAR2(size)
Character
Variable-length alphanumeric data (maximum 2,000 characters per some provided sources).
DATE
Date/Time
Stores date and time information (default format DD-MON-YY).
TIMESTAMP
Date/Time
Stores more precise date and time information than the standard DATE type.
CLOB / NCLOB
Large Object
Character Large Object used for large blocks of character data.
BLOB
Large Object
Binary Large Object used to store binary data like images or digitized pictures.
LONG / RAW
Other
LONG stores up to 2 GB of character data; RAW stores variable-length binary data (max 255 bytes).
--------------------------------------------------------------------------------
3. Data Manipulation Language (DML)
DML statements allow users to access and manipulate data in existing tables. The effects of DML statements are not permanent until the transaction is committed.
INSERT: Adds rows to an existing table. Requires values for all NOT NULL columns. Columns omitted from the list default to NULL.
UPDATE: Modifies existing table rows. Can update single or multiple columns and often uses a WHERE clause to target specific rows.
DELETE: Removes rows from a table. If the WHERE clause is omitted, all rows are deleted, but the table structure remains.
MERGE: Synchronizes two tables. It performs an UPDATE if a row exists in both or an INSERT if the row only exists in one.
SELECT: The primary query statement used to retrieve data. It utilizes clauses like WHERE for filtering, ORDER BY for sorting, and GROUP BY for aggregation.
Comparison Operators for Filtering
Operator
Function
=, !=, <>
Equal to, Not equal to
>, >=, <, <=
Greater than, Less than comparisons
BETWEEN...AND
Checks for a range (inclusive)
LIKE
String matching with wildcards % (multiple chars) and _ (one char)
IN, NOT IN
Matches values in a specified list
IS NULL, IS NOT NULL
Checks for the presence or absence of data
--------------------------------------------------------------------------------
4. Data Definition Language (DDL)
DDL statements manage the structure of database objects. Unlike DML, Oracle issues an implicit COMMIT before and after any DDL statement, meaning they cannot be rolled back.
CREATE TABLE: Defines a new table, its columns, and data types.
ALTER TABLE: Used to ADD new columns, MODIFY existing column types/sizes, DROP columns, or RENAME columns.
DROP TABLE: Permanently removes a table and its data from the database.
TRUNCATE TABLE: Removes all data from a table permanently while keeping the structure.
TRUNCATE vs. DELETE
The documentation highlights critical differences between these commands:
Type: TRUNCATE is DDL; DELETE is DML.
Performance: TRUNCATE is faster as it deallocates data pages and resets the "High Water Mark" rather than logging individual row deletions.
Logging: DELETE generates significant undo/redo logs; TRUNCATE generates negligible logs (only page deallocation).
Rollback: DELETE can be rolled back. In Oracle, TRUNCATE cannot be rolled back (though some other vendors like SQL Server allow it within an explicit transaction).
Identity: TRUNCATE typically resets identity/sequence counters to their seed value; DELETE does not.
--------------------------------------------------------------------------------
5. Transactions and Transaction Control
A transaction is a sequence of one or more SQL statements treated as a single unit: either all statements are performed, or none are.
COMMIT: Ends the current transaction, makes changes permanent, releases locks, and erases savepoints.
ROLLBACK: Undoes changes. It can undo the entire transaction or roll back to a specific SAVEPOINT.
SAVEPOINT: Marks a specific point within a transaction to which you can later roll back without losing the entire transaction's progress.
Important Note: If a program terminates abnormally without an explicit commit, Oracle Database automatically rolls back the last uncommitted transaction.
--------------------------------------------------------------------------------
6. Joins and Aggregate Functions
Table Joins
INNER JOIN: Returns rows only when there is a match in both tables.
LEFT OUTER JOIN: Returns all rows from the left table and matching rows from the right.
RIGHT OUTER JOIN: Returns all rows from the right table and matching rows from the left.
FULL OUTER JOIN: Returns all rows when there is a match in either table.
NATURAL JOIN: Automatically joins tables based on columns with matching names and data types.
SELF JOIN: Joins a table to itself (e.g., matching employees to their managers within the same table).
Aggregate Functions
These functions operate on sets of rows to return a single result:
COUNT(): Returns the number of rows or non-null values.
SUM(): Calculates the total of a numeric column.
AVG(): Calculates the mean value.
MIN() / MAX(): Finds the lowest and highest values.
--------------------------------------------------------------------------------
7. Short-Answer Quiz
Instructions: Answer the following questions in 2-3 sentences based on the provided documentation.
What is the primary difference between how a database handles DDL versus DML statements regarding transactions?
Why is the TRUNCATE statement generally faster than the DELETE statement when removing all data from a table?
Explain the purpose of a SAVEPOINT and how it interacts with the ROLLBACK command.
When using the INSERT statement, what happens if you omit a column that allows NULL values from the list_of_columns?
What are the consequences of committing a transaction?
Describe the function of the WHERE clause when used with the UPDATE or DELETE statements.
How does an OUTER JOIN differ from a simple INNER JOIN?
What is a "pseudocolumn" in Oracle, and provide two examples.
Under what specific condition will a TRUNCATE statement fail even if the user has the correct privileges?
Describe the difference between the CHAR and VARCHAR2 data types.
--------------------------------------------------------------------------------
8. Quiz Answer Key
DDL vs. DML Transactions: Oracle issues an implicit COMMIT before and after any DDL statement, making them permanent immediately and impossible to roll back. DML statements, however, require an explicit COMMIT to become permanent and can be undone with a ROLLBACK until that point.
TRUNCATE Speed: TRUNCATE is faster because it removes data by deallocating the data pages and resetting the table's "High Water Mark" rather than logging each individual row deletion. It bypasses the resource-heavy process of checking constraints and generating extensive undo/redo logs required by DELETE.
SAVEPOINT and ROLLBACK: A SAVEPOINT marks a specific point in a transaction that allows a user to perform a partial rollback. By using ROLLBACK TO SAVEPOINT, a user can undo only the changes made after that mark without ending the overall transaction.
Omitting Omit-able Columns: If a column that can be NULL is omitted from the INSERT statement's column list, Oracle defaults the value for that column to NULL. However, all columns defined as NOT NULL must have a valid value provided, or the statement will fail.
Committing Consequences: Once a transaction is committed, all changes become permanent and visible to other database users. The commit erases all transaction savepoints, releases transaction locks, and ensures the changes cannot be undone using a ROLLBACK.
WHERE Clause Function: The WHERE clause filters the rows that the statement affects based on specified conditions. Without a WHERE clause, an UPDATE will change values for every row in the column, and a DELETE will remove every row in the table.
Outer vs. Inner Join: An INNER JOIN only returns rows where there is a match in both joined tables. An OUTER JOIN extends this by returning all rows from at least one of the tables even if no matching row exists in the other table.
Pseudo columns: A pseudocolumn behaves like a table column but is not actually stored in the table; it returns a value based on the context of the query. Examples include SYSDATE, which returns the current system date, and ROWNUM, which indicates the order in which a row was selected.
TRUNCATE Failure Condition: A TRUNCATE statement cannot be applied to a table if it is being referenced by an enabled foreign key from another table. To perform the operation, the foreign key constraint must first be disabled or dropped.
CHAR vs. VARCHAR2: CHAR is a fixed-length data type where the cell always holds the specified number of characters, padding with spaces if necessary. VARCHAR2 is a variable-length data type that only uses the amount of space required by the actual data entered, up to the defined maximum size.
--------------------------------------------------------------------------------
9. Essay Questions
Instructions: Use the provided documentation to formulate comprehensive responses to the following prompts.
Analyze the role of Transaction Control Language (TCL) in maintaining data integrity within a multi-user database environment.
Compare and contrast DROP, TRUNCATE, and DELETE. Discuss the specific scenarios where a developer should choose one over the others.
Explain the importance of selecting appropriate data types during the table creation phase. How does this decision affect storage, performance, and data validation?
Discuss the utility of Joins in relational databases. Use examples from the documentation (such as Employees and Departments) to explain how joining tables provides a more complete view of data.
Describe the various types of SQL functions (Numeric, Character, Date, Conversion, and Aggregate) and explain how they enhance the power of a standard SELECT query.
--------------------------------------------------------------------------------
10. Glossary of Key Terms
Aggregate Function: A function (like SUM or AVG) that performs a calculation on a set of values and returns a single value.
Commit: A command that makes all current transaction changes permanent and visible to others.
Data Definition Language (DDL): SQL commands used to define or modify the database structure (schema).
Data Manipulation Language (DML): SQL commands used for managing and querying data within database objects.
High Water Mark: A pointer in the database that indicates the amount of space used by a table; it is reset by the TRUNCATE command.
Join: An operation that combines rows from two or more tables based on a related column between them.
NULL: A marker used to indicate that a data value does not exist in the database.
Primary Key: A column or set of columns that uniquely identifies each row in a table; it cannot contain NULL values.
Pseudocolumn: A "virtual" column that returns a value but is not stored in the table (e.g., ROW NUM, USER).
Rollback: A command that undoes changes made during the current transaction.
Savepoint: A logical marker within a transaction used to allow partial rollbacks.
Schema Object: A logical structure of data, such as a table, view, sequence, or index.
Tablespace: A logical storage unit in an Oracle database consisting
of one or more physical datafiles.
Transaction: A single logical unit of work consisting of one or more SQL statements that must succeed or fail together.
