19-1 Exam Objectives for Oracle Database SQL Certified Associate
Key Concepts
1. SQL Data Definition Language (DDL)
DDL commands are used to define the database structure. This includes creating, altering, and dropping tables, indexes, and other database objects.
Example:
Creating a table:
CREATE TABLE employees (
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
hire_date DATE
);
2. SQL Data Manipulation Language (DML)
DML commands are used to manipulate data within the database. This includes inserting, updating, and deleting data.
Example:
Inserting data into a table:
INSERT INTO employees (employee_id, first_name, last_name, hire_date)
VALUES (101, 'John', 'Doe', TO_DATE('2023-01-15', 'YYYY-MM-DD'));
3. SQL Data Control Language (DCL)
DCL commands are used to control access to the database. This includes granting and revoking privileges.
Example:
Granting privileges:
GRANT SELECT, INSERT ON employees TO hr_user;
4. SQL Transaction Control Language (TCL)
TCL commands are used to manage transactions within the database. This includes committing, rolling back, and saving transactions.
Example:
Committing a transaction:
COMMIT;
5. SQL Queries
SQL queries are used to retrieve data from the database. This includes SELECT statements with various clauses such as WHERE, GROUP BY, and ORDER BY.
Example:
Selecting data from a table:
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date > TO_DATE('2022-01-01', 'YYYY-MM-DD')
ORDER BY hire_date DESC;
6. Joins
Joins are used to combine rows from two or more tables based on a related column between them. This includes INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Example:
Using an INNER JOIN:
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
7. Subqueries
Subqueries are queries nested within another query. They can be used in SELECT, FROM, WHERE, and HAVING clauses.
Example:
Using a subquery in a WHERE clause:
SELECT first_name, last_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
8. Set Operators
Set operators are used to combine the results of two or more SELECT statements. This includes UNION, UNION ALL, INTERSECT, and MINUS.
Example:
Using UNION:
SELECT employee_id FROM employees
UNION
SELECT employee_id FROM retired_employees;
9. Aggregate Functions
Aggregate functions perform a calculation on a set of values and return a single value. This includes functions like COUNT, SUM, AVG, MIN, and MAX.
Example:
Using SUM:
SELECT SUM(salary) AS total_salary
FROM employees;
10. Grouping and Filtering
Grouping and filtering are used to organize and filter data. This includes GROUP BY and HAVING clauses.
Example:
Using GROUP BY and HAVING:
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;
11. Data Types
Data types define the type of data that can be stored in a column. This includes numeric, character, date, and large object (LOB) types.
Example:
Defining data types in a table:
CREATE TABLE products (
product_id NUMBER(6),
product_name VARCHAR2(50),
price NUMBER(8,2),
release_date DATE
);
12. Constraints
Constraints are used to enforce rules on the data in a table. This includes NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints.
Example:
Adding a PRIMARY KEY constraint:
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);
13. Indexes
Indexes are used to improve the performance of data retrieval operations. They can be created on one or more columns of a table.
Example:
Creating an index:
CREATE INDEX idx_last_name
ON employees (last_name);
14. Views
Views are virtual tables based on the result-set of an SQL statement. They can simplify complex queries and control access to data.
Example:
Creating a view:
CREATE VIEW employee_details AS
SELECT employee_id, first_name, last_name, hire_date
FROM employees;
15. Sequences
Sequences are used to generate unique numeric values. They are often used to create primary key values.
Example:
Creating a sequence:
CREATE SEQUENCE employee_seq
START WITH 100
INCREMENT BY 1;
16. Synonyms
Synonyms are alternative names for database objects. They can simplify object access and provide security by hiding object names and owners.
Example:
Creating a synonym:
CREATE SYNONYM emp
FOR employees;
17. PL/SQL Basics
PL/SQL is Oracle's procedural language extension to SQL. It allows for the creation of stored procedures, functions, triggers, and packages.
Example:
Creating a PL/SQL block:
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT first_name || ' ' || last_name INTO v_name FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;
18. Triggers
Triggers are PL/SQL blocks that are automatically executed in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table.
Example:
Creating a trigger:
CREATE OR REPLACE TRIGGER check_salary_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 1000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary too low');
END IF;
END;
19. Data Dictionary Views
Data dictionary views provide information about the database objects and their definitions. They are essential for database administration and monitoring.
Example:
Querying the data dictionary:
SELECT table_name, column_name, data_type
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';