SQL Fundamentals

Structured Query Language (SQL) is the standard language for interacting with Oracle Database. Oracle's implementation of SQL follows the ANSI/ISO SQL standard with powerful extensions. This lesson covers the essential SQL statements for querying, inserting, updating, and deleting data.

---

SQL Statement Categories

Category	Statements	Purpose
DML (Data Manipulation Language)	SELECT, INSERT, UPDATE, DELETE, MERGE	Query and modify data
DDL (Data Definition Language)	CREATE, ALTER, DROP, TRUNCATE	Define database objects
DCL (Data Control Language)	GRANT, REVOKE	Control access privileges
TCL (Transaction Control Language)	COMMIT, ROLLBACK, SAVEPOINT	Manage transactions

---

SELECT — Querying Data

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;

Selecting All Columns

SELECT * FROM employees;

Column Aliases

SELECT first_name AS "First Name",
       salary * 12 AS annual_salary
FROM employees;

DISTINCT

SELECT DISTINCT department_id FROM employees;

---

WHERE — Filtering Rows

Comparison Operators

Operator	Meaning
=	Equal to
<> or !=	Not equal to
<, >, <=, >=	Less/greater than
BETWEEN ... AND ...	Range (inclusive)
IN (...)	Match any value in a list
LIKE	Pattern matching (% = any characters, _ = one character)
IS NULL / IS NOT NULL	Check for NULL values

Examples

-- Salary between 5000 and 10000
SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;

-- Department 10, 20, or 30
SELECT * FROM employees WHERE department_id IN (10, 20, 30);

-- Names starting with 'A'
SELECT * FROM employees WHERE first_name LIKE 'A%';

-- Employees with no manager
SELECT * FROM employees WHERE manager_id IS NULL;

Logical Operators

SELECT * FROM employees
WHERE department_id = 50
  AND salary > 5000
  OR job_id = 'IT_PROG';

Tip: Use parentheses to make complex conditions clear: WHERE (dept = 50 AND salary > 5000) OR job_id = 'IT_PROG'

---

ORDER BY — Sorting Results

-- Ascending (default)
SELECT * FROM employees ORDER BY last_name;

-- Descending
SELECT * FROM employees ORDER BY salary DESC;

-- Multiple columns
SELECT * FROM employees ORDER BY department_id, salary DESC;

-- By position
SELECT first_name, salary FROM employees ORDER BY 2 DESC;

---

Aggregate Functions

Function	Description
COUNT(*)	Number of rows
COUNT(column)	Number of non-NULL values
SUM(column)	Total of numeric values
AVG(column)	Average value
MIN(column)	Minimum value
MAX(column)	Maximum value

Examples

-- Count employees
SELECT COUNT(*) FROM employees;

-- Average salary by department
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- Departments with average salary > 8000
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000;

GROUP BY and HAVING

• GROUP BY — groups rows sharing a common value
• HAVING — filters groups (like WHERE but for aggregated data)

---

INSERT — Adding Data

-- Insert a single row
INSERT INTO departments (department_id, department_name, location_id)
VALUES (280, 'Data Science', 1700);

-- Insert from a query
INSERT INTO dept_archive
SELECT * FROM departments WHERE department_id < 100;

---

UPDATE — Modifying Data