📚 Oracle SQL Syllabus

🧪 Oracle SQL Lab Tutorial Syllabus

Objective: This lab-based Oracle SQL tutorial is designed to build a strong practical foundation in SQL programming using Oracle Database. Each lab includes step-by-step exercises, real-world scenarios, and SQL scripts for active learning.

🔍 Lab 1: Introduction & Basic Queries

• Explore Oracle SQL*Plus
• SELECT – Retrieve data from a table
• Practice: Query employees and departments
• Analytical SQL Questions for Employees & Departments

🔃 Lab 2: Sorting and Filtering Data

• ORDER BY, WHERE, DISTINCT
• AND, OR, IN, BETWEEN, LIKE, IS NULL
• Practice: Filter employees by salary, department

🔗 Lab 3: Joining Tables

• INNER, LEFT, RIGHT, FULL OUTER JOIN
• CROSS JOIN, SELF JOIN
• Practice: Combine employee and department data

📊 Lab 4: Grouping and Aggregation

• GROUP BY, HAVING
• CUBE, ROLLUP, GROUPING SETS
• Practice: Salary summaries, department counts

🔁 Lab 5: Subqueries

• Sub Query Basics
• Simple and Correlated Subqueries
• EXISTS, NOT EXISTS, ANY, ALL
• Practice: Find high earners per department
• Practice Problems

➕ Lab 6: Set Operations

• UNION, INTERSECT, MINUS
• Practice: Combine and compare queries

🔄 Lab 7: Pivoting Data

• PIVOT, UNPIVOT
• Practice: Crosstab salary reports

🎚 Lab 8: Limiting Rows

• FETCH FIRST, OFFSET
• Practice: Top earners, latest hires

🏗 Lab 9: Creating and Modifying Tables

• CREATE, ALTER TABLE
• Virtual Columns
• Practice: Create custom project table

📁 Lab 10: Data Types

• NUMBER, CHAR, VARCHAR2, DATE, TIMESTAMP
• NCHAR, NVARCHAR2, INTERVAL
• Practice: Define appropriate types

🔐 Lab 11: Constraints

• PRIMARY KEY, FOREIGN KEY
• NOT NULL, UNIQUE, CHECK, DEFAULT
• Practice: Create relational schema

✏️ Lab 12: Modifying Data

• INSERT, INSERT ALL, UPDATE, DELETE
• MERGE for UPSERT
• Practice: Populate employee/project data

⏳ Lab 13: Temporary Tables

• Global and Private Temporary Tables
• Practice: Use for session-specific data

👁 Lab 14: Views

• CREATE, DROP, Updatable Views
• WITH CHECK OPTION, Inline Views
• Practice: Build report views

⚡ Lab 15: Indexes

• CREATE, DROP, Unique, Function-based, Bitmap Index
• Practice: Improve query performance

📛 Lab 16: Synonyms

• CREATE, DROP SYNONYM
• Practice: Use synonyms for object access

🔢 Lab 17: Sequences

• CREATE, ALTER, DROP SEQUENCE
• Practice: Use sequences for IDs

🔢 SQL & PL/SQL CONCEPTUAL EXERCISE

• Constraints in DBMS
• SQL DDL and DML Explained Simply with Examples
• Key Responsibilities of a Database Administrator (DBA)
• Data Integrity vs. Data Inconsistency in DBMS
• DBMS Layered Architecture Made Easy
• Employee DBMS: Keys & Queries Explained Simply
• Library Database Design & Table Modification
• Library Database Design Explained
• SQL to Relational Algebra
• Relational Algebra: Schema Design
• Reducing Data Redundancy for Better Database Integrity
• Concurrency Issues in Retail: Managing with Oracle
• Analyze DBMS Schema of a Bank Management System
• SQL Queries for University Department Data
• SQL Operations on Student Table
• Designing Employee Table with SQL Constraints
• Sub Query Basics : Oracle DBMS
• Sub Query Advanced : Oracle DBMS
• Properties of DBMS : Atomic Stage
• DBMS Constraint : Primary & Foreign Key
• Oracle PL SQL Basic Example Practice
• Stored Procedure for Order Management
• PL SQL Cursor for Information Retrieval
• Database Normalization & ER Diagram
• Sub Query with RDBMS
• Trigger in Oracle Database
• Ensuring Atomicity in E-commerce
• Sub Query for Customer & Order Management
• Normalization of a Database Table
• Database Trigger for Audit Table
• Oracle Explicit Cursor
• ER Diagram & DBMS: Design, Consistency, Atomicity, Integrity

SQL exercises, PL SQL examples, Oracle DBMS practice, SQL queries tutorial, Database normalization, DBMS constraints, relational algebra, SQL DDL vs DML, Sub query examples, Oracle triggers, stored procedures, database administrator responsibilities, ER diagram design, data redundancy, atomicity consistency integrity in DBMS

Tags: SQL, PL/SQL, Oracle DBMS, Database Design, Relational Algebra, SQL Practice, Stored Procedure, Trigger, ER Diagram, Constraints

📛 PL SQL LAB Based Exercise

Section 1: SQL Subquery & Join Questions (30 Questions)

• Overview of Subqueries
  • Single-row vs Multi-row Subqueries
  • Correlated vs Non-Correlated Subqueries
• Overview of SQL Joins
  • INNER, LEFT, RIGHT, FULL OUTER, CROSS
• Combining Subqueries with Joins
• Lab Questions 1–30
  • Problem Statements with Sample Tables
  • SQL Query Solutions
  • Step-by-Step Explanation and Output

Section 2: PL/SQL Trigger Lab Exam (30 Questions)

• Introduction to PL/SQL Triggers
• Types of Triggers: BEFORE, AFTER, INSTEAD OF
• Row-level vs Statement-level Triggers
• Trigger Syntax and Examples
• Lab Questions 1–30
  • Problem Statement
  • Sample Table Schema
  • Step-by-Step Solution
  • Explanation

Section 3: PL/SQL Stored Procedure

• Q31 – Safe Delete Product with Exception Handling
  • Problem Description
  • Sample Table Schema
  • Stored Procedure Syntax
  • Exception Handling Techniques (WHEN OTHERS, NO_DATA_FOUND, etc.)
  • Step-by-Step Execution
  • Output and Explanation

Section 4: PL/SQL Cursor Lab Exam (30 Questions)

• Introduction to Cursors
• Implicit vs Explicit Cursors
• Cursor Attributes (%ROWCOUNT, %FOUND, %NOTFOUND, %ISOPEN)
• Cursor Loops and FOR Loops
• Lab Questions 1–30
  • Sample Database Schema for Practice
  • Problem Statements
  • Cursor Implementation Solutions
  • Step-by-Step Explanations

Section 5: Additional Resources

• Best Practices for PL/SQL and SQL Queries
• Common Errors and Debugging Tips
• Recommended References and Tutorials

🧰 Tools Required

• Oracle Database (XE or Standard)
• SQL Developer or compatible tool
• Sample schema: HR, SCOTT, or custom dataset

✅ Learning Outcomes

• Master Oracle SQL fundamentals
• Write optimized SQL queries
• Design and manage Oracle database objects

oracle sql tutorial, oracle database lab, sql practice exercises, oracle lab manual, sql join practice, oracle sql queries, group by sql oracle, subquery oracle, pivot sql, create table sql oracle, oracle views, temporary tables oracle, sql developer lab, dml oracle, ddl commands, oracle index types, sql sequences oracle, sql training beginner, oracle sql beginner, advanced sql oracle, online sql tutorial oracle, sql lab syllabus, sql lab exercises, sql lab topics