Learn SQL • Practice SQL • Understand SQL • Solve SQL Problems
A structured Oracle SQL learning, practice, revision, and interview preparation repository.
Crystal-Clear-Oracle-SQL is a structured and comprehensive Oracle SQL learning repository created to provide a clear learning path from database fundamentals to advanced SQL querying, database design, normalization, database objects, best practices, and interview preparation.
The repository is designed with a strong focus on:
- Understanding concepts clearly
- Learning SQL syntax
- Practicing practical queries
- Understanding how queries work
- Solving difficult SQL problems
- Building database design knowledge
- Understanding normalization
- Preparing for SQL interviews
- Developing SQL problem-solving skills
- Writing readable and maintainable SQL
Instead of treating SQL as a collection of memorized queries, this repository focuses on developing the ability to convert a requirement into SQL logic.
Requirement
↓
Understand the Problem
↓
Identify Required Data
↓
Identify Tables
↓
Identify Relationships
↓
Build SQL Logic
↓
Write Query
↓
Execute & Verify
↓
Optimize & Explain
The main objectives of this repository are to:
- Build strong Oracle SQL fundamentals
- Understand database concepts from the beginning
- Learn SQL syntax systematically
- Practice SQL queries extensively
- Understand different SQL operators and functions
- Master
GROUP BYandHAVING - Understand subqueries and nested queries
- Master different types of joins
- Understand SQL command categories
- Learn Oracle database objects
- Understand ER diagrams and ER schemas
- Learn database normalization
- Solve interview-oriented SQL problems
- Develop a systematic approach to difficult SQL queries
- Learn SQL best practices
- Improve SQL explanation skills for technical interviews
The repository begins with the fundamental concepts required to understand databases and SQL.
Topics include:
- Data vs Database
- Management of Facts and Figures
- DBMS
- Databases available in the market
- History of Oracle
- Versions of Oracle
- Introduction to SQL
- Table Structure
- Rules for Storing Data
Understanding data types is essential before creating database tables.
Topics include:
- Data Types Overview
- Numeric Data Types
- Alphanumeric Data Types
DATEData Type
Learn how to retrieve information from database tables using SQL queries.
Topics include:
SELECT- Selecting specific columns
- Selecting multiple columns
- Retrieving complete table data
- Query-based data retrieval
Understanding how Oracle SQL handles case sensitivity.
Topics include:
- SQL keywords
- Table names
- Column names
- Character data
- Case-sensitive comparisons
- Practical examples
Learn how constraints maintain data integrity and enforce rules on database tables.
Topics include:
UNIQUENOT NULLPRIMARY KEYFOREIGN KEYDEFAULTCHECK
Reusable tables and SQL scripts are provided for practical query exercises.
Department-related information.
Employee-related information.
Salary-grade information.
SQL scripts include:
01-DEPT.sql
02-EMP.sql
03-J_GRADE.sql
Recommended execution order:
01-DEPT.sql
↓
02-EMP.sql
↓
03-J_GRADE.sql
Learn how operators are used to build SQL conditions and expressions.
Topics include:
- Arithmetic Operators
- Relational Operators
- Concatenation Operator
BETWEENNOT BETWEENINNOT INIS NULLIS NOT NULLLIKEESCAPEANDOR- Operator Precedence
Learn how to sort query results.
Topics include:
- Ascending order
- Descending order
- Sorting using columns
- Sorting using multiple columns
- Ordering query results
Functions are used to perform operations on individual values or groups of rows.
Topics include:
- Character Functions
- Number Functions
- Date Functions
- Conversion Functions
Topics include aggregate functions such as:
COUNT()SUM()AVG()MAX()MIN()
Learn how to divide rows into groups and perform aggregate calculations for each group.
Topics include:
GROUP BY- Grouping data
- Aggregate functions with
GROUP BY - Department-wise calculations
- Group-based reports
Learn how to filter grouped results.
Topics include:
HAVINGWHEREvsHAVING- Aggregate conditions
- Group filtering
- Practical grouping queries
Learn how one SQL query can be placed inside another SQL query.
Topics include:
- Subqueries
- Inner Queries
- Nested Queries
- Single-row subqueries
- Multi-row subqueries
- Subquery-based filtering
- Complex SQL problem solving
Learn how to retrieve related information from multiple tables.
Topics include:
- Joins Overview
- Inner Join
- Equi Join
- Natural Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Cross Join
- Cartesian Join
- Self Join
- Multiple-table joins
- Join query practice
Understand the major categories of SQL commands.
CREATEALTERTRUNCATEDROP
INSERTUPDATEDELETE
SELECT
GRANTREVOKE
COMMITROLLBACKSAVEPOINT
This section provides additional SQL query practice and helps strengthen query-writing and problem-solving skills.
Learn important Oracle database objects and their practical usage.
Topics include:
- Creating views
- Using views
- Removing views
- Index concepts
- Creating indexes
- Using indexes
- Removing indexes
- Procedure concepts
- Creating procedures
- Executing procedures
- Trigger concepts
- Events and actions
BEFORE INSERTAFTER INSERTBEFORE UPDATEAFTER UPDATEBEFORE DELETEAFTER DELETE:NEW:OLD
- Creating sequences
- Removing sequences
NEXTVALCURRVAL- Using sequences with
INSERT
- Synonym concepts
- Creating synonyms
- Using synonyms
- Removing synonyms
Learn how databases can be represented visually using Entity-Relationship diagrams.
Topics include:
- Entities
- Attributes
- Relationships
- Primary Keys
- Foreign Keys
- Relationship representation
- Database design concepts
Learn how ER concepts are represented as a database schema.
Topics include:
- Entities
- Attributes
- Relationships
- Tables
- Primary Keys
- Foreign Keys
- Database structure
- Schema design
A complete normalization section covering database design from fundamentals through advanced normal forms.
- Definition of normalization
- Need for normalization
- Data redundancy
- Database organization
- Normal forms
- Insert Anomaly
- Update Anomaly
- Delete Anomaly
- Super Key
- Candidate Key
- Primary Key
- Alternate Key
- Composite Key
- Functional Dependency
- Full Functional Dependency
- Partial Dependency
- Transitive Dependency
1NF → Atomic Values
Focus:
- Atomic values
- No repeating groups
- One value per cell
2NF → Remove Partial Dependency
Focus:
- 1NF
- Composite keys
- Partial functional dependency
- Full functional dependency
3NF → Remove Transitive Dependency
Focus:
- 2NF
- Transitive dependency
- Dependency between non-key attributes
BCNF → Every Determinant Must Be a Super Key
Focus:
- Boyce-Codd Normal Form
- Super keys
- Determinants
- Functional dependencies
4NF → Multivalued Dependency
Focus:
- Multivalued dependencies
- Independent multi-valued relationships
- Decomposition
5NF → Join Dependency
Focus:
- Join dependency
- Lossless decomposition
- Project-Join Normal Form
Comparison between:
- Normalization
- Denormalization
- Advantages
- Disadvantages
- Appropriate use cases
A complete progression through:
Unnormalized Form
↓
1NF
↓
2NF
↓
3NF
↓
BCNF
↓
4NF
↓
5NF
Includes the benefits and limitations of normalization and denormalization.
Interview-oriented questions covering:
- Normalization
- Anomalies
- Functional dependencies
- 1NF
- 2NF
- 3NF
- BCNF
- 4NF
- 5NF
- Normalization vs Denormalization
- Scenario-based questions
A final revision of the complete normalization section.
1NF → ATOMIC
2NF → PARTIAL
3NF → TRANSITIVE
BCNF → SUPER KEY
4NF → MULTIVALUED
5NF → JOIN
The final section focuses on writing SQL that is clear, maintainable, understandable, and efficient.
Topics include:
- Meaningful table names
- Descriptive column names
- Readable SQL
- Consistent formatting
- Uppercase SQL keywords
- Avoiding unnecessary
SELECT * - Understanding the purpose of every clause
- Correct handling of
NULL - Correct use of operators
- Proper use of aggregate functions
WHEREvsHAVING- Effective joins
- Appropriate use of subqueries
- Avoiding redundant queries
- Using constraints
- Database normalization
- Query testing
- SQL debugging
- Breaking difficult queries into smaller steps
- Execution-thinking
- Query optimization
- Interview-oriented SQL practices
Database Fundamentals
↓
Oracle SQL Fundamentals
↓
Data Types
↓
Data Retrieval
↓
Case Sensitivity
↓
Constraints
↓
Sample Tables
↓
Operators
↓
ORDER BY
↓
Functions
↓
GROUP BY
↓
HAVING
↓
Subqueries
↓
Joins
↓
SQL Commands
↓
Miscellaneous Queries
↓
Database Objects
↓
ER Diagram
↓
ER Schema
↓
Normalization
↓
Best Practices
↓
Interview Preparation
One of the major goals of this repository is to develop the ability to solve SQL problems independently.
For any SQL problem:
1. Read the Question
↓
2. Understand What Is Required
↓
3. Identify the Expected Output
↓
4. Identify Required Tables
↓
5. Identify Table Relationships
↓
6. Identify Conditions
↓
7. Decide Whether JOIN Is Required
↓
8. Decide Whether GROUP BY Is Required
↓
9. Decide Whether HAVING Is Required
↓
10. Decide Whether a Subquery Is Required
↓
11. Build the Query
↓
12. Execute the Query
↓
13. Check the Output
↓
14. Debug if Necessary
↓
15. Optimize
↓
16. Explain the Logic
Question
↓
Logic
↓
SQL
↓
Output
↓
Explanation
The repository focuses on SQL concepts and practical problems commonly useful for technical interviews.
- Second Highest Salary
- Nth Highest Salary
- Highest Salary in Each Department
- Employees Without Managers
- Duplicate Records
- Remove Duplicate Records
- Top-N Records
- Ranking Employees
- Employees Joined in a Particular Year
- Department-wise Salary Report
- Count Employees Department-wise
- Maximum Salary
- Minimum Salary
- Average Salary
- Employees Above Average Salary
- Employees Working in Multiple Departments
- Multiple Table Joins
- Correlated Subqueries
When explaining a SQL problem:
Understand the Requirement
↓
Identify the Tables
↓
Identify the Relationship
↓
Identify Conditions
↓
Choose the SQL Concept
↓
Write the Query
↓
Explain Each Clause
↓
Explain the Result
A strong SQL interview answer should demonstrate:
- Conceptual understanding
- Query-writing ability
- Logical thinking
- Knowledge of joins
- Knowledge of subqueries
- Understanding of aggregation
- Ability to debug
- Ability to explain the solution
The repository provides reusable SQL scripts for query practice.
DEPT
│
└── DEPT_ID
│
│
↓
EMP
│
└── DEPT_ID
J_GRADE
│
└── Salary Grade Information
| Table | Purpose |
|---|---|
DEPT |
Department information |
EMP |
Employee information |
J_GRADE |
Salary-grade information |
- Oracle SQL Developer
- SQL*Plus
- SQLcl
- Oracle Live SQL
git clone https://github.com/shaikbasha-dev/Crystal-Clear-Oracle-SQL.gitOpen the repository in your preferred development environment or directly through GitHub.
Start from:
01 Introduction
and progress sequentially through the repository.
Navigate to:
06 Tables Used for Queries/
Run:
01-DEPT.sql
02-EMP.sql
03-J_GRADE.sql
For every topic:
Read
↓
Understand
↓
Execute
↓
Modify
↓
Experiment
↓
Solve
↓
Revise
Read the Concept
↓
Understand the Definition
↓
Learn the Rules
↓
Study the Syntax
↓
Study Examples
↓
Execute Queries
↓
Observe Output
↓
Modify Queries
↓
Experiment
↓
Solve Without Assistance
↓
Explain the Solution
↓
Revise
Don't just memorize the query. Understand why the query works.
| Category | Details |
|---|---|
| Main Learning Sections | 20 |
| Markdown Documents | 140 |
| SQL Scripts | 3 |
| Main Practice Tables | 3 |
| Normal Forms | 1NF → 5NF + BCNF |
| SQL Level | Beginner → Advanced |
| Database Objects | 6 Major Categories |
| Interview Preparation | ✅ |
| Database Design | ✅ |
| SQL Best Practices | ✅ |
Students who want to build Oracle SQL knowledge from fundamentals.
Developers who want to strengthen their SQL and database skills.
Candidates preparing for SQL and database interview questions.
Anyone who wants a structured Oracle SQL revision path.
Learners interested in database design, relationships, ER diagrams, and normalization.
This repository brings together:
Concepts
+
Syntax
+
Queries
+
Practice
+
Database Design
+
Normalization
+
Problem Solving
+
Interview Preparation
+
Best Practices
The focus is on developing understanding and problem-solving ability, rather than simply collecting SQL commands.
SQL Fundamentals
│
├── Data Types
├── Data Retrieval
├── Operators
├── ORDER BY
└── Functions
↓
Data Analysis
│
├── GROUP BY
└── HAVING
↓
Advanced Querying
│
├── Subqueries
└── Joins
↓
Database Management
│
├── DDL
├── DML
├── DQL
├── DCL
└── TCL
↓
Database Objects
│
├── Views
├── Indexes
├── Procedures
├── Triggers
├── Sequences
└── Synonyms
↓
Database Design
│
├── ER Diagram
├── ER Schema
├── Keys
└── Relationships
↓
Normalization
│
├── 1NF
├── 2NF
├── 3NF
├── BCNF
├── 4NF
└── 5NF
↓
Interview Preparation
│
├── Difficult Queries
├── Common Patterns
├── Query Explanation
└── Problem Solving
↓
Best Practices
Contributions, suggestions, corrections, and improvements are welcome.
If you find:
- A SQL error
- A documentation issue
- An incorrect explanation
- A missing concept
- A query that can be improved
- A formatting issue
- A useful learning improvement
you can contribute by opening an issue or submitting a pull request.
Fork
↓
Create Branch
↓
Make Changes
↓
Test Changes
↓
Commit
↓
Push
↓
Create Pull Request
If Crystal-Clear-Oracle-SQL helps you learn, practice, revise, or prepare for interviews:
- ⭐ Star the repository
- 🍴 Fork the repository
- 📢 Share it with other learners
- 💬 Provide feedback
- 🤝 Contribute improvements
Your support helps the repository reach more learners and developers.
This project is licensed under the MIT License.
The MIT License allows users to use, copy, modify, merge, publish, distribute, sublicense, and sell copies of the software, subject to the conditions of the license.
See the LICENSE file for the complete license terms.
B.Tech — Electronics & Communication Engineering
Aspiring Java Full Stack Developer
Passionate about software development, programming, databases, and continuous learning.
This repository is part of my technical learning and development journey, with a focus on building strong Oracle SQL and database fundamentals through structured learning, practical queries, problem solving, and interview preparation.
Learn the Concept
↓
Understand the Logic
↓
Write the Query
↓
Execute the Query
↓
Analyze the Result
↓
Modify the Query
↓
Solve New Problems
↓
Explain the Solution
↓
Revise
↓
Improve
Good Queries Create Better Solutions.
☕ Learn SQL Today
💻 Practice Every Day
🧠 Build Strong Problem-Solving Skills
🚀 Build a Better Tomorrow
⭐ Crystal-Clear-Oracle-SQL ⭐
Learn SQL • Practice SQL • Understand SQL • Solve SQL Problems
```