Beginner-friendly notes, corrected examples, practical labs, interview preparation, and hands-on exercises for Oracle SQL, SQL*Plus, and PL/SQL.
Start learning · Practice labs · Interview questions · Quick reference · Online practice
Created and maintained by Shubhagaman Singh
Website · Email · GitHub repository
The material began as personal classroom transcripts from 2021. It has been reorganized for learners, corrected where necessary, and enriched with explanations and runnable examples. The original .txt files remain beside the Markdown lessons as a historical archive.
| 📘 Structured learning | 🧪 Hands-on practice | 🧠 Interview preparation | ✅ Corrected examples |
|---|---|---|---|
| Concepts progress from DBMS basics to PL/SQL. | Six classroom-style labs build practical confidence. | Twenty-five DBMS, SQL, and PL/SQL questions have complete answers. | Historical mistakes are explained and replaced with runnable solutions. |
Follow the lessons in order if you are new to relational databases.
| Step | Module | You will learn |
|---|---|---|
| 01 | Database and SQL fundamentals | DBMS concepts, users, SQL categories, DDL, DML, DCL, and TCL |
| 02 | Oracle SQL*Plus workbook | Connecting, creating tables, CRUD operations, transactions, and joins |
| 03 | SQL quick reference | Frequently used Oracle SQL and PL/SQL syntax in one place |
| 04 | Views, constraints, keys, and CTAS | Views, validation, composite keys, and table creation from queries |
| 05 | PL/SQL fundamentals | Blocks, variables, conditions, loops, SQL integration, and exceptions |
| 06 | PL/SQL worked examples | Calculator, even/odd, input handling, and defensive programming |
| 07 | Interview questions with answers | Twenty-five corrected interview-ready questions and answers |
| 08 | Six-day practical lab | A progressive lab covering tables, queries, constraints, and PL/SQL |
Database basics → Oracle SQL → Joins & constraints → Views → PL/SQL → Practice → Interview review
Clone the repository:
git clone git@github.com:ShubhagamanSingh/learning_SQL_Oracle.git
cd learning_SQL_OracleThen begin with Database and SQL fundamentals, or open the practical lab if you prefer learning by doing.
Tip
Open SQL Practice for an interactive browser-based SQL terminal, guided questions, and automatic result checking—no local setup required.
.
├── .github/assets/ # Repository and social-preview artwork
├── Database-textbook/
│ ├── README.md # Topic index
│ ├── Database.md # DBMS and SQL foundations
│ ├── Connection.md # SQL*Plus setup and guided SQL workbook
│ ├── syntax.md # Compact SQL/PLSQL reference
│ ├── VIEW.md # Views, CHECK constraints, keys, CTAS
│ ├── interview.md # 25 answered interview questions
│ └── PLSQL/ # PL/SQL lessons and programs
└── dbtask/
├── README.md # Practical-lab index
├── day1.md ... day6.md # Daily exercises
└── CW_18-12-2021.md # Banking classwork
- Relational-database and DBMS fundamentals
- Oracle data types and table design
CREATE,ALTER,DROP, andTRUNCATEINSERT,SELECT,UPDATE, andDELETE- Filtering, sorting, pattern matching, and aggregate functions
- Primary, foreign, unique, composite, and check constraints
- Inner and outer joins, views, subqueries, and CTAS
- Transactions with
COMMIT,ROLLBACK, and savepoints - PL/SQL blocks, variables, branches, loops, and exceptions
- Common interview questions and practical problem solving
- Oracle Database (the original sessions used Oracle Database 11g Express Edition)
- SQL*Plus, SQLcl, or Oracle SQL Developer
- A practice account with permission to create tables and views
Caution
Do not use SYS, SYSTEM, or the broad DBA role for routine learning exercises. Ask an administrator to create a practice user with only the privileges required for the lesson.
For SQL*Plus examples that use DBMS_OUTPUT, enable output first:
SET SERVEROUTPUT ON;Examples containing &name use SQL*Plus substitution variables and will prompt for a value.
- SQL keywords are uppercase for readability; Oracle treats unquoted identifiers case-insensitively.
VARCHAR2is used rather thanVARCHAR, following common Oracle practice.- Inserts name their target columns whenever that makes the example safer.
COMMITandROLLBACKare shown deliberately so transaction boundaries are visible.- Placeholder forms such as
<table_name>are templates, not literal SQL. - Corrected code is preferred in the Markdown lesson; the adjacent
.txtfile preserves the original classroom record.
Create a disposable practice schema, run one lesson at a time, predict each result before executing it, and then change the sample values. For destructive statements such as DROP and TRUNCATE, confirm that you are connected to the practice schema first.
Use SQL Practice to run queries in your browser and work through interactive questions with automatic result validation. It is especially useful for practicing SELECT, WHERE, sorting, pattern matching, joins, aggregate functions, grouping, and subqueries without configuring a local database.
Recommended workflow: read a topic here → reproduce it in Oracle → reinforce the portable query concepts on SQL Practice.
Important
SQL dialects differ. Treat the site as practice for portable SQL concepts, then verify Oracle-specific syntax in these notes or an Oracle environment. PL/SQL blocks, SQL*Plus commands, Oracle data types, user administration, and some functions may not run unchanged in the online terminal.
The Markdown edition fixes several easy-to-miss issues from the raw notes:
- joins now include the relationship between the tables, avoiding Cartesian products;
- Oracle syntax is used for dates and table renaming;
- composite keys are distinguished from candidate keys;
- the PL/SQL multiplication-table loop checks the counter rather than the input number;
- the maximum-of-three and electricity-bill programs cover all valid cases;
- the Fibonacci program prints exactly the requested number of terms;
- examples explain why repeated transaction runs can produce different results.
- Re-create every table without copying the answer.
- Add
NOT NULL,UNIQUE,CHECK, primary-key, and foreign-key constraints with meaningful names. - Wrap multi-step changes in a transaction and test both
COMMITandROLLBACK. - Rewrite old comma joins with explicit
JOIN ... ONsyntax. - Extend the PL/SQL programs with input validation and exception handling.
Corrections, clearer explanations, additional exercises, and Oracle-version notes are welcome. Before contributing, read CONTRIBUTING.md and follow the Code of Conduct.
- Keep examples beginner-friendly and runnable.
- Explain destructive commands and transaction boundaries.
- Never include credentials, private data, or proprietary course material.
- Report sensitive problems privately according to SECURITY.md.
Copyright © 2021–2026 Shubhagaman Singh. Except where otherwise noted, the original educational material is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International.
You may share and adapt the material for non-commercial purposes when you credit Shubhagaman Singh, link to https://shubhagaman.in, identify changes, and distribute adaptations under the same license.
See the Legal and Policy Index for copyright, terms, privacy, disclaimer, trademark, contribution, conduct, and security information.
These notes are educational examples. Test them in a disposable schema before adapting them to a real system.
Shubhagaman Singh
shubhagaman.in · shubhagamansingh@gmail.com
CC BY-NC-SA 4.0 · Legal policies · Contributing · Security
Copyright © 2021–2026 Shubhagaman Singh