Keyboard Shortcuts:
← → Navigate | Ctrl+F Search | ? Help
1 / 43
Session 1

Session 1 Agenda

What We Will Cover

Session 1 Topics

  • What is a database and why it's important
  • Types of databases
  • Relational database concepts and ACID properties
  • Database constraints
  • HR schema structure and relationships
  • Basic SQL queries: SELECT, WHERE, pattern matching
  • Advanced WHERE conditions
  • Working with NULL values
  • Sorting and using functions in SQL
  • Hands-on exercises
2 / 43

What is a Database?

Definition

Definition

  • A database is an organized collection of data stored electronically.
  • Managed by a Database Management System (DBMS) for storage, retrieval, and manipulation.
  • Data can include text, numbers, images, videos, and more.
Source: GeeksforGeeks: What is Database?
3 / 43

Types of Databases

Overview of Data Models

Common Database Types

  • Hierarchical: Tree-structured data (e.g., Windows Registry)
  • Network: Complex many-to-many relationships (e.g., IDS)
  • Object-Oriented: Stores objects with methods (e.g., ObjectDB)
  • Relational: Tables with keys and SQL (e.g., Oracle, MySQL)
  • Cloud: Hosted remotely (e.g., AWS Aurora)
  • Centralized: One central location (e.g., mainframe systems)
4 / 43

Hierarchical Databases

Types of Databases

Hierarchical Databases

  • Organize data in a tree-like structure (parent-child).
  • Advantage: Simple and fast for hierarchical data.
  • Disadvantage: Not flexible for complex relationships.
  • Example: IBM Information Management System (IMS), Windows Registry
5 / 43

Network Databases

Types of Databases

Network Databases

  • Child records can have multiple parents (many-to-many).
  • Advantage: Flexible, supports complex relationships.
  • Disadvantage: Complex to design/manage.
  • Example: Integrated Data Store (IDS), TurboIMAGE
6 / 43

Object-Oriented Databases

Types of Databases

Object-Oriented Databases

  • Store data as objects (with attributes and methods).
  • Advantage: Great for complex data (multimedia, CAD).
  • Disadvantage: Requires OOP knowledge, less widely supported.
  • Example: db4o, ObjectDB, Versant Object Database
7 / 43

Relational Databases (RDBMS)

Types of Databases

Relational Databases (RDBMS)

  • Store data in tables (rows/columns), use keys for relationships.
  • Advantage: Structured, easy to use, supports ACID.
  • Disadvantage: Can be hard to scale for huge data.
  • Example: Oracle Database, MySQL, Microsoft SQL Server, PostgreSQL
8 / 43

Cloud Databases

Types of Databases

Cloud Databases

  • Hosted on cloud platforms, accessible over the internet.
  • Advantage: Scalable, easy to manage, pay-as-you-go.
  • Disadvantage: Dependent on internet, security concerns.
  • Example: Amazon Aurora, Google Cloud Spanner, Microsoft Azure SQL Database
9 / 43

Centralized Databases

Types of Databases

Centralized Databases

  • All data stored at a single location/server.
  • Advantage: High security and consistency.
  • Disadvantage: Single point of failure, can be a bottleneck.
  • Example: Mainframe databases used by banks, Library catalog system hosted on a single server
10 / 43

What is an RDBMS?

Relational Database Concepts

What is an RDBMS?

  • An RDBMS (Relational Database Management System) stores data in tables (relations) with rows and columns.
  • Each table has a primary key to uniquely identify rows; tables can be linked using foreign keys.
  • Uses SQL for data access and management.
11 / 43

Key Features of RDBMS

Relational Database Concepts

Key Features

  • ACID compliance (Atomicity, Consistency, Isolation, Durability) for reliable transactions
  • Supports multi-user access and data integrity
  • Enforces schemas and normalization for structured data
  • Examples: Oracle Database, MySQL, SQL Server, IBM Db2

What is ACID?

  • Atomicity: Each transaction is all-or-nothing. If any part fails, the whole transaction is rolled back.
  • Consistency: Transactions bring the database from one valid state to another, maintaining all rules and constraints.
  • Isolation: Transactions are processed independently and transparently—intermediate results are invisible to other transactions.
  • Durability: Once a transaction is committed, its results are permanent, even in the case of a system failure.
12 / 43

How RDBMS Works

Relational Database Concepts

How It Works

  • Data is organized in tables. Each row is a record; each column is a field/attribute.
  • Primary keys uniquely identify rows. Foreign keys link tables together.
  • Constraints ensure data accuracy and relationships.
13 / 43

What are Constraints?

Constraints in Relational Databases

What are Constraints?

  • Rules that enforce the accuracy, consistency, and integrity of data in tables.
  • Prevent invalid data and maintain relationships between tables.
14 / 43

Main Types of Constraints

Constraints in Relational Databases

Main Types of Constraints

  • PRIMARY KEY: Uniquely identifies each row in a table (no duplicates, no NULLs).
  • FOREIGN KEY: Links to another table's primary key (e.g., department_id).
  • NOT NULL: Ensures a column cannot have NULL (empty) values.
  • UNIQUE: Ensures all values in a column are different.
  • CHECK: Ensures values in a column meet a specific condition.
15 / 43

Why Constraints Matter

Constraints in Relational Databases

Why Constraints Matter

  • Guarantee data quality and prevent errors
  • Maintain valid relationships between tables
  • Support reliable business operations and analytics
16 / 43

SQL Command Categories

Understanding Different Types of SQL Commands

DDL - Data Definition Language

  • CREATE: Create tables, databases, indexes
  • ALTER: Modify table structure
  • DROP: Delete database objects
  • TRUNCATE: Remove all data from table
  • RENAME: Rename database objects

DQL - Data Query Language

  • SELECT: Retrieve data from database
  • Used for reading and querying data
  • Most commonly used SQL command
  • Can be combined with other clauses

DML - Data Manipulation Language

  • INSERT: Add new records to tables
  • UPDATE: Modify existing data
  • DELETE: Remove records from tables
  • MERGE: Insert or update based on condition

DCL & TCL - Control Commands

  • DCL: Data Control Language
  • • GRANT: Give permissions
  • • REVOKE: Remove permissions
  • TCL: Transaction Control Language
  • • COMMIT: Save changes
  • • ROLLBACK: Undo changes

Example: Complete SQL Workflow

-- DDL: Create table CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), salary DECIMAL(10,2) ); -- DML: Insert data INSERT INTO employees VALUES (1, 'John', 'Doe', 50000); -- DQL: Query data SELECT * FROM employees WHERE salary > 40000; -- TCL: Commit changes COMMIT;
17 / 43

HR Schema Structure

Understanding Our Practice Database

Table Name Primary Key Description Key Columns
EMPLOYEES employee_id Core employee information first_name, last_name, salary, department_id
DEPARTMENTS department_id Department details department_name, manager_id, location_id
JOBS job_id Job positions job_title, min_salary, max_salary
LOCATIONS location_id Office locations city, state_province, country_id
18 / 43

HR Schema ER Diagram

Complete Database Structure & Relationships

EMPLOYEES employee_id (PK) first_name, last_name email, phone_number hire_date, salary department_id (FK) manager_id (FK) DEPARTMENTS department_id (PK) department_name manager_id (FK) location_id (FK) JOBS job_id (PK) job_title min_salary, max_salary LOCATIONS location_id (PK) street_address city, state_province country_id (FK) COUNTRIES country_id (PK) country_name, region_id REGIONS region_id (PK) region_name JOB_HISTORY employee_id (FK) job_id (FK) start_date, end_date department_id manager_id job_id location_id country_id region_id employee_id job_id

Key Relationships

  • EMPLOYEES → DEPARTMENTS: Each employee belongs to one department
  • EMPLOYEES → EMPLOYEES: Self-reference for manager hierarchy
  • DEPARTMENTS → LOCATIONS: Each department has one location
  • LOCATIONS → COUNTRIES: Each location is in one country
  • COUNTRIES → REGIONS: Each country belongs to one region
  • JOB_HISTORY: Tracks employee job changes over time

Data Types & Constraints

  • Primary Keys: employee_id, department_id, job_id, location_id, country_id, region_id
  • Foreign Keys: department_id, manager_id, location_id, country_id, region_id
  • Unique Constraints: email, phone_number
  • Check Constraints: salary > 0, hire_date validation
  • NOT NULL: employee_id, first_name, last_name, hire_date

Sample Data Insights

  • ~107 employees across multiple departments
  • 27 departments in various locations worldwide
  • 19 job titles with different salary ranges
  • 23 locations in 14 countries across 4 regions
  • Hierarchical structure: Employees can manage other employees
  • Historical tracking: Job history maintains employment records
19 / 43

Basic SELECT Statements

Your First SQL Queries

Basic Syntax

SELECT column1, column2, ...
FROM table_name;

Select All Columns

SELECT *
FROM employees;

Select Specific Columns

SELECT first_name, last_name, salary
FROM employees;

Using Column Aliases

SELECT first_name AS "First Name",
salary AS "Monthly Salary"
FROM employees;
20 / 43

WHERE Clause

Filtering Your Data

Basic WHERE Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Comparison Operators

  • = Equal to
  • != or <> Not equal
  • >, <, >=, <= Comparisons

Examples

-- Find employees with salary greater than 10000
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 10000;

-- Find employees in department 50
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id = 50;
21 / 43

Pattern Matching with LIKE

Finding Patterns in Your Data

Wildcards

  • % - Matches any sequence of characters
  • _ - Matches any single character

Examples

-- Names starting with 'J'
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'J%';

-- Second letter is 'o'
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE '_o%';
22 / 43

Advanced WHERE Conditions

Complex Filtering

IN and BETWEEN

-- IN operator
SELECT first_name, department_id
FROM employees
WHERE department_id IN (10, 20, 30);

-- BETWEEN operator
SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 5000 AND 10000;

Logical Operators

-- AND operator
SELECT first_name, salary
FROM employees
WHERE salary > 8000
AND department_id = 60;

-- OR operator
SELECT first_name, job_id
FROM employees
WHERE job_id = 'IT_PROG'
OR job_id = 'SA_REP';
23 / 43

Working with NULL Values

Handling Missing Data

IS NULL

-- Find employees with no commission
SELECT first_name, last_name, commission_pct
FROM employees
WHERE commission_pct IS NULL;

IS NOT NULL

-- Find employees with commission
SELECT first_name, last_name, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

💡 Important Note

Never use = NULL or != NULL. Always use IS NULL or IS NOT NULL when checking for NULL values.

24 / 43

Sorting Data with ORDER BY

Organizing Your Results

Basic Sorting

-- Ascending order (default)
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary;

-- Descending order
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

Multiple Column Sorting

-- Sort by department, then by salary
SELECT first_name, last_name, salary, department_id
FROM employees
ORDER BY department_id, salary DESC;
25 / 43

Built-in Functions

Transforming Your Data

String Functions

-- Convert case
SELECT UPPER(first_name), LOWER(last_name)
FROM employees;

-- String length
SELECT first_name, LENGTH(first_name)
FROM employees;

Numeric Functions

-- Round numbers
SELECT salary, ROUND(salary/12, 2) as monthly
FROM employees;

-- Truncate numbers
SELECT salary, TRUNC(salary/12, 2) as monthly
FROM employees;
26 / 43
Session 2

Session 2 Agenda

What We Will Cover

Session 2 Topics

  • Aggregate functions and GROUP BY
  • JOIN operations
  • Subqueries for complex data retrieval
  • Data manipulation: INSERT, UPDATE, DELETE
  • Practice exercises
27 / 43

Aggregate Functions

Summarizing Your Data

Function Description Example
COUNT(*) Count all rows SELECT COUNT(*)
FROM employees
SUM() Sum of values SELECT SUM(salary)
FROM employees
AVG() Average of values SELECT AVG(salary)
FROM employees
MAX() Maximum value SELECT MAX(salary)
FROM employees
MIN() Minimum value SELECT MIN(salary)
FROM employees
28 / 43

How Tables Relate: Keys & Foreign Keys

Keys and Foreign Keys

Primary and Foreign Keys

  • Primary Key: Unique ID for each row (e.g., employee_id)
  • Foreign Key: Links to another table's primary key (e.g., department_id)
  • Why: They allow JOIN operations and maintain data integrity
  • Example: An employee belongs to a department — relationship via department_id
29 / 43

JOIN Operations

Combining Data from Multiple Tables

INNER JOIN (WHERE Clause)

-- Employees with department names
SELECT e.first_name, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

LEFT JOIN (Outer Join with (+))

-- All departments with employee count
SELECT d.department_name, COUNT(e.employee_id)
FROM departments d, employees e
WHERE d.department_id = e.department_id(+)
GROUP BY d.department_name;
30 / 43

Multiple Table JOINs

Connecting Multiple Data Sources

Multiple Table JOINs (WHERE Clause)

-- Employees with department and location info
SELECT e.first_name, e.last_name,
d.department_name, l.city, l.country_id
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
31 / 43

Subqueries

Queries within Queries

Single-Row Subqueries

-- Employee with highest salary
SELECT first_name, last_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

-- Employees earning more than average
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Multiple-Row Subqueries

-- Employees in departments located in the US
SELECT first_name, last_name, department_id
FROM employees
WHERE department_id IN (
SELECT d.department_id
FROM departments d, locations l
WHERE d.location_id = l.location_id
AND l.country_id = 'US'
);

Correlated & EXISTS Subqueries

-- Employees earning more than average in their department
SELECT first_name, last_name, salary, department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);

-- Employees who are also managers
SELECT first_name, last_name
FROM employees e
WHERE EXISTS (
SELECT 1
FROM employees
WHERE manager_id = e.employee_id
);
32 / 43

Data Manipulation (DML)

INSERT, UPDATE, DELETE, COMMIT, ROLLBACK

INSERT & UPDATE

-- Insert new employee
INSERT INTO employees (
employee_id, first_name, last_name, email,
hire_date, job_id, salary, department_id
) VALUES (
207, 'John', 'Doe', 'JDOE',
SYSDATE, 'IT_PROG', 6000, 60
);

-- Update employee salary
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 60;

DELETE, COMMIT, ROLLBACK

-- Delete specific employee
DELETE FROM employees
WHERE employee_id = 207;

-- Save changes
COMMIT;

-- Undo changes
ROLLBACK;

UPDATE with Subquery

-- Update with subquery
UPDATE employees
SET salary = (
SELECT AVG(salary)
FROM employees
WHERE department_id = 50
)
WHERE employee_id = 207;
33 / 43

Practice Exercises

Session 2 Challenges

Try These!

  • Find the average salary for each department
  • List all employees with their department names and cities
  • Find employees who earn more than the average salary
  • Display the top 5 highest-paid employees
  • Create a report showing department name, employee count, and average salary
34 / 43

Real-World SQL

Applying SQL in the Workplace

Example Scenarios

  • Payroll report: calculate total salary by department
  • Sales report: join products and orders tables
  • HR report: list employees earning above average salary
  • Support report: find unresolved tickets by technician
35 / 43

Key Takeaways

What You've Learned

Session 1 Summary

  • Basic SELECT statements with column selection and aliases
  • WHERE clause for filtering with various operators
  • Pattern matching with LIKE
  • Sorting data with ORDER BY
  • Basic built-in functions

Session 2 Summary

  • Aggregate functions for data analysis
  • GROUP BY and HAVING for grouped data
  • JOIN operations for multi-table queries
  • Subqueries for complex data retrieval
  • Basic DML operations
36 / 43

Common SQL Mistakes

Learn from Errors

Frequent Pitfalls

  • Forgetting GROUP BY with aggregates
  • Using = NULL instead of IS NULL
  • Mixing non-aggregated columns with aggregates
  • Missing aliases in multi-table joins
  • Not testing on small datasets first
37 / 43

Best Practices & Next Steps

Continue Your SQL Journey

Best Practices

  • Always use table aliases in multi-table queries
  • Use meaningful column aliases for better readability
  • Test queries with small datasets first
  • Use COMMIT/ROLLBACK appropriately with DML operations
  • Format your SQL code for better readability

Next Steps

  • Master PL/SQL for stored procedures and triggers
  • Explore analytical functions (e.g., RANK, LEAD, LAG)
  • Learn about query tuning and execution plans
  • Practice on SQLZoo or Mode Analytics
  • Consider Oracle SQL Certification (1Z0-071)
38 / 43

Window Functions

Advanced Analytical Functions

ROW_NUMBER()

-- Rank employees by salary
SELECT first_name, last_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

RANK() and DENSE_RANK()

-- Rank with ties
SELECT first_name, salary,
RANK() OVER (ORDER BY salary DESC) as rank,
DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

LAG() and LEAD()

-- Compare with previous/next salary
SELECT first_name, salary,
LAG(salary) OVER (ORDER BY salary) as prev_salary,
LEAD(salary) OVER (ORDER BY salary) as next_salary
FROM employees;
39 / 43

Performance & Indexes

Optimizing Your Queries

Understanding Indexes

  • Indexes speed up data retrieval
  • Automatically created on PRIMARY KEY
  • Consider indexes on frequently searched columns
  • Too many indexes slow down INSERT/UPDATE

Query Optimization Tips

  • Use specific columns instead of SELECT *
  • Avoid functions on indexed columns
  • Use appropriate WHERE conditions
  • Limit result sets with FETCH FIRST
40 / 43

SQL Injection Prevention

Security Best Practices

❌ Vulnerable Code

-- DON'T DO THIS
SELECT *
FROM employees
WHERE last_name = '" + userInput + "';

✅ Secure Code

-- Use parameterized queries
SELECT *
FROM employees
WHERE last_name = ?;

Security Best Practices

  • Always use parameterized queries
  • Validate and sanitize user input
  • Use least privilege database accounts
  • Regular security audits
41 / 43

Real-World Case Studies

Business Applications

E-commerce Analytics

-- Top selling products
SELECT p.product_name,
COUNT(o.order_id) as total_orders,
SUM(oi.quantity * oi.unit_price) as revenue
FROM products p, order_items oi, orders o
WHERE p.product_id = oi.product_id
AND oi.order_id = o.order_id
GROUP BY p.product_id, p.product_name
ORDER BY revenue DESC;

Customer Segmentation

-- High-value customers
SELECT customer_id,
SUM(order_total) as total_spent,
CASE
WHEN SUM(order_total) > 10000 THEN 'Premium'
WHEN SUM(order_total) > 5000 THEN 'Gold'
ELSE 'Standard'
END as segment
FROM orders
GROUP BY customer_id;
42 / 43

Troubleshooting Guide

Common Oracle SQL Errors

Error Cause Solution
ORA-00942 Table or view does not exist Check table name spelling and schema
ORA-00904 Invalid identifier Verify column names and aliases
ORA-00937 Not a single-group function Add missing GROUP BY clause
ORA-02291 Integrity constraint violated Check foreign key relationships
43 / 43

Knowledge Check

Test Your Understanding

What SQL clause is used to filter data?
WHERE
SELECT
FROM
ORDER BY
Which operator should you use to check for NULL values?
= NULL
IS NULL
== NULL
NULL()