clear, step-by-step guide to install Oracle Database on Windows (10, 11, or Server) — works

for Oracle Database 19c and 21c, which are the current major Windows releases.

🧩 Step 1: Check Requirements

✅ System requirements:

 Windows version: 10 Pro/Enterprise (64-bit) or Windows Server 2016/2019/2022

 RAM: Minimum 2 GB (4 GB+ recommended)
 Disk space: ~10 GB free
 Administrator privileges

📦 Step 2: Download Oracle Database

1. Go to the official Oracle Database download page:
👉 [Link]
2. Choose Oracle Database 19c (LTS) or 21c (Innovation release).
3. Under Microsoft Windows x64, click Download.
4. Sign in or create a free Oracle account.
5. The download will be a .zip file, e.g.
6. WINDOWS.X64_193000_db_home.zip

📁 Step 3: Extract Files

 Extract the .zip file to a folder such as:
 C:\Oracle19c
 Make sure the folder path has no spaces.

⚙️Step 4: Run the Installer

1. Open the extracted folder.
2. Right-click [Link] → Run as administrator.
3. Choose installation type:
o Desktop Class (simpler) – for single-user or development systems.
o Server Class – for production or advanced configuration.
4. Follow prompts:
o Oracle base: C:\app\<your_username>
 o Database edition: Enterprise or Standard
o Database name (SID): e.g. ORCL
o Password: Create an admin password

🧠 Step 5: Finish Installation

 The installer will:
o Copy files
o Configure services
o Create the database
 When it finishes, it shows:
 Database Configuration Assistant completed successfully

and gives you:

o Enterprise Manager Express URL

e.g. [Link]
o Database connection string
e.g. localhost:1521/ORCL

🧩 Step 6: Test the Installation

Open SQL*Plus or SQL Developer, then connect:

sqlplus sys as sysdba

When prompted:

Enter password: <your_password>

SQL> SELECT name FROM v$database;

You should see your database name (ORCL).

⚠️Optional: Set Environment Variables

Add this to your Windows PATH:

C:\app\<your_username>\product\19.0.0\dbhome_1\bin
Then you can run sqlplus from any Command Prompt.

🧰 Tools You Might Want

 Oracle SQL Developer:
[Link]
→ GUI for managing your database.
 Oracle Database XE (Express Edition) if you want a lightweight free edition:
[Link]

Would you like me to give you the exact download link and setup instructions for a specific
version (like Oracle 19c or 21c, Standard or XE)?

You said:

SQL beginner oracle

ChatGPT said:

Perfect 👌 — you want to start learning SQL with Oracle Database. Let’s go step by step so you
can learn the essentials and practice right away using Oracle syntax (which has a few
differences from MySQL or PostgreSQL).

🎯 Goal
You’ll learn:

1. Basic SQL commands (SELECT, INSERT, UPDATE, DELETE)

2. How to create tables
3. How to use constraints (PRIMARY KEY, FOREIGN KEY, etc.)
4. How to filter and join data

🧰 Step 1: Set Up Your Practice Environment

Option 1 — Easiest (Recommended)

Install Oracle Database Express Edition (XE) + SQL Developer.

 1. Download Oracle XE (Free)
👉 [Link]
2. Install it using the default settings.
o Database name (SID): XE
o Username: system
o Password: the one you choose
3. Download SQL Developer
👉 [Link]
4. Open SQL Developer → Connect to your XE database:
5. Username: system
6. Password: your_password
7. Hostname: localhost
8. Port: 1521
9. SID: XE

🏗️Step 2: Create Your First Table

Run this SQL in SQL Developer:

CREATE TABLE employees (

emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE DEFAULT SYSDATE,
salary NUMBER(8,2)
);

✅ This creates a table named employees.

🧩 Step 3: Insert Some Data

INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Smith', 5000);

INSERT INTO employees (first_name, last_name, salary)

VALUES ('Maria', 'Gonzalez', 6500);

COMMIT;

🔍 Step 4: Query the Table

SELECT * FROM employees;

You’ll see:
EMP_ID FIRST_NAME LAST_NAME HIRE_DATE SALARY
1 John Smith 06-OCT-25 5000
2 Maria Gonzalez 06-OCT-25 6500

🧠 Step 5: Filtering and Sorting

SELECT first_name, salary
FROM employees
WHERE salary > 5500
ORDER BY salary DESC;

✏️Step 6: Update and Delete

-- Give John a raise
UPDATE employees
SET salary = 6000
WHERE first_name = 'John';

-- Delete Maria
DELETE FROM employees
WHERE first_name = 'Maria';

COMMIT;

🔗 Step 7: Relationships (Example with FOREIGN KEY)

CREATE TABLE departments (
dept_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
dept_name VARCHAR2(50)
);

INSERT INTO departments (dept_name)

VALUES ('Finance'), ('IT'), ('HR');

ALTER TABLE employees

ADD (dept_id NUMBER REFERENCES departments(dept_id));

UPDATE employees SET dept_id = 2 WHERE first_name = 'John';

COMMIT;

🧮 Step 8: JOIN Example

SELECT e.first_name, [Link], d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
📚 Step 9: Learn Step-by-Step
Here’s a good Oracle SQL learning path:

Topic Description
SELECT Retrieve data
WHERE Filter rows
ORDER BY Sort results
INSERT / UPDATE / DELETE Modify data
CREATE TABLE Define structure
PRIMARY / FOREIGN KEYS Relationships
JOINs Combine tables
GROUP BY / HAVING Aggregate data
Subqueries Query inside a query
Views Saved queries
Indexes Speed up queries

💡 Step 10: Practice Resources

 Oracle Live SQL (no install needed):
👉 [Link]
You can practice all SQL commands directly in your browser.
 Tutorials:
o Oracle SQL Tutorial
o SQLZoo (use Oracle mode)

Would you like me to create a step-by-step beginner SQL practice plan (7 days) for Oracle —
with daily exercises and answers?

You said:

SQL oracle intermediate

ChatGPT said:

Excellent 👏 — you’re ready to move from beginner to intermediate Oracle SQL.

At this level, you’ll go beyond basic SELECT and JOIN statements and learn how to analyze,
manipulate, and optimize data using Oracle’s advanced SQL features.
🎯 What You’ll Learn at the Intermediate Level
Here’s a roadmap of essential Oracle SQL intermediate topics:

Topic Description
1. Subqueries Nesting queries inside other queries
2. Joins (INNER, LEFT, RIGHT, FULL) Combining related data across tables
3. Aggregate functions SUM, AVG, COUNT, MAX, MIN, etc.
4. GROUP BY / HAVING Grouping and filtering aggregates
5. CASE expressions Conditional logic inside queries
6. Analytic (Window) functions Ranking, running totals, moving averages
7. Set operators UNION, INTERSECT, MINUS
8. Constraints & sequences Managing data integrity
9. Views Saved query definitions
10. Performance basics Indexes, execution plans, and hints

🧩 1. Subqueries
Example: Find employees who earn more than the average salary
SELECT first_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);

💡 The subquery calculates the average salary; the outer query finds who earns more.

🔗 2. Joins
Example: INNER JOIN
SELECT e.first_name, d.dept_name
FROM employees e
JOIN departments d
ON e.dept_id = d.dept_id;

LEFT OUTER JOIN

SELECT e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id;

📊 3. Aggregate Functions
SELECT dept_id, COUNT(*) AS num_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
ORDER BY avg_salary DESC;

HAVING (filter grouped results)

SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 6000;

⚙️4. CASE Expression (Conditional Logic)

SELECT first_name, salary,
CASE
WHEN salary < 4000 THEN 'Low'
WHEN salary BETWEEN 4000 AND 7000 THEN 'Medium'
ELSE 'High'
END AS salary_level
FROM employees;

🧠 5. Analytic (Window) Functions

Oracle’s analytic functions are extremely powerful — they let you calculate running totals,
ranks, and percentiles without grouping.

Example: Rank employees by salary

SELECT first_name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank_by_salary
FROM employees;

Example: Running total

SELECT first_name, salary,
SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;
🧮 6. Set Operators
SELECT first_name FROM employees
UNION
SELECT manager_name FROM departments;

Other operators:

 UNION ALL → includes duplicates

 INTERSECT → common rows
 MINUS → rows in first query not in second

🧱 7. Views
CREATE OR REPLACE VIEW high_earners AS
SELECT first_name, salary
FROM employees
WHERE salary > 6000;

SELECT * FROM high_earners;

🔒 8. Constraints and Sequences

Create a sequence for generating IDs
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

INSERT INTO employees (emp_id, first_name, last_name, salary)

VALUES (emp_seq.NEXTVAL, 'Lucas', 'Brown', 7200);

⚡ 9. Performance Basics
Create an index
CREATE INDEX idx_employees_salary ON employees(salary);

View execution plan

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE salary > 7000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

🧰 10. Helpful Oracle-Specific Functions
Function Description Example
NVL(expr1, expr2)
Replace NULL with NVL(commission, 0)
a value
DECODE(expr, val1, res1, Conditional logic DECODE(dept_id, 10, 'HR', 20,
val2, res2, default) (older style) 'IT', 'Other')
TO_CHAR(hire_date, 'YYYY-MM-
TO_CHAR(date, format) Format dates DD')

TRUNC(date)
Remove time from TRUNC(SYSDATE)
date
SYSDATE Current date/time SELECT SYSDATE FROM dual;

🧠 Practice Challenges
1. Top 3 Highest Salaries
2. SELECT * FROM (
3. SELECT first_name, salary
4. FROM employees
5. ORDER BY salary DESC
6. )
7. WHERE ROWNUM <= 3;
8. Average Salary per Department (only if more than 5 employees)
9. SELECT dept_id, AVG(salary)
10. FROM employees
11. GROUP BY dept_id
12. HAVING COUNT(*) > 5;
13. Employee with the Earliest Hire Date per Department
14. SELECT dept_id, first_name, hire_date
15. FROM (
16. SELECT dept_id, first_name, hire_date,
17. ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY hire_date)
AS rn
18. FROM employees
19. )
20. WHERE rn = 1;

📚 Resources to Continue Learning

 Oracle Live SQL (practice online):
[Link]
 Oracle SQL Language Reference (official docs):
[Link]
 Tutorials & Exercises:
o Mode SQL Tutorials
o SQLZOO Oracle Mode
Would you like me to create a 7-day intermediate Oracle SQL practice plan (with daily
exercises and solutions)? It’s great for solidifying these skills.

You said:

SQL oracle advanced

ChatGPT said:

Excellent 🚀 — you’re ready for advanced Oracle SQL, where you move from “writing queries”
to designing, optimizing, and engineering data solutions.

This level focuses on Oracle-specific power features: advanced analytic functions, hierarchical
queries, advanced subqueries, performance tuning, materialized views, and PL/SQL integration.

🧭 Overview: What “Advanced Oracle SQL” Covers

Category Key Concepts
Hierarchical queries, correlated subqueries, WITH clause,
🔍 Advanced Querying
pivot/unpivot
📈 Analytics Window functions, ranking, lag/lead, percentiles, moving averages
🧠 Advanced Joins & Sets Self-joins, anti-joins, semi-joins, UNION vs MINUS
🧩 Performance Indexes, query plans, optimizer hints, partitions
🗂️Data Architecture Materialized views, partitions, sequences
💡 PL/SQL Integration Using SQL inside PL/SQL, DML returning, bulk collect
🧮 Advanced Functions Model clause, XML/JSON functions, regex

⚙️1. WITH Clause (Common Table Expressions)

Allows you to structure complex queries cleanly.

WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id
)
SELECT e.first_name, [Link], d.avg_sal
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE [Link] > d.avg_sal;
💡 Great for breaking large queries into readable parts.

🌳 2. Hierarchical Queries (CONNECT BY )

Used for tree-like data (e.g. org charts, category hierarchies).

SELECT employee_id, first_name, manager_id, LEVEL

FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

🔹 LEVEL shows hierarchy depth

🔹 PRIOR defines parent-child relationship

🧠 3. Correlated Subqueries
Each row in the outer query runs the inner query.

SELECT e.first_name, [Link]

FROM employees e
WHERE [Link] > (
SELECT AVG(salary)
FROM employees
WHERE dept_id = e.dept_id
);

💡 Used for comparisons within the same table context.

🔢 4. Analytic (Window) Functions — Advanced

These are the most powerful features in Oracle SQL for analytics.

a. Ranking and Percentiles

SELECT first_name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank,
PERCENT_RANK() OVER (ORDER BY salary) AS perc_rank
FROM employees;

b. Lag / Lead
SELECT first_name, hire_date,
LAG(hire_date) OVER (ORDER BY hire_date) AS prev_hire,
LEAD(hire_date) OVER (ORDER BY hire_date) AS next_hire
FROM employees;

c. Moving Average
SELECT dept_id, hire_date, salary,
AVG(salary) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS 2
PRECEDING) AS mov_avg
FROM employees;

🧮 5. PIVOT and UNPIVOT

Transform rows ↔ columns.

PIVOT Example
SELECT * FROM (
SELECT dept_id, job_id, salary FROM employees
)
PIVOT (
AVG(salary)
FOR job_id IN ('IT_PROG' AS IT, 'HR_REP' AS HR, 'SA_REP' AS SALES)
);

UNPIVOT Example
SELECT * FROM monthly_sales
UNPIVOT (
sales_amount FOR month IN (jan AS 'January', feb AS 'February', mar AS
'March')
);

⚡ 6. Performance Tuning Concepts

a. View Execution Plan
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE salary > 7000;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

b. Index Hints
SELECT /*+ INDEX(e idx_employees_salary) */ salary
FROM employees e
WHERE salary > 7000;

c. Partitioning Example
CREATE TABLE sales (
sale_id NUMBER,
sale_date DATE,
amount NUMBER
)
PARTITION BY RANGE (sale_date) (
PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION p2024 VALUES LESS THAN (MAXVALUE)
);

📦 7. Materialized Views
Materialized views store the query results for performance.

CREATE MATERIALIZED VIEW mv_dept_summary

BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept_id;

🧩 8. Advanced Joins
Semi-Join (Exists)
SELECT e.first_name
FROM employees e
WHERE EXISTS (
SELECT 1 FROM departments d
WHERE e.dept_id = d.dept_id
);

Anti-Join (Not Exists)

SELECT e.first_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM departments d
WHERE e.dept_id = d.dept_id
);

🔍 9. REGEXP (Regular Expressions)

Powerful pattern matching in Oracle.

SELECT first_name
FROM employees
WHERE REGEXP_LIKE(first_name, '^[A-M].*');

 REGEXP_REPLACE(col, pattern, replacement)

 REGEXP_SUBSTR(col, pattern)

🧠 10. MODEL Clause (Advanced Analytical Feature)

Lets you perform spreadsheet-like calculations directly in SQL.

SELECT country, year, sales

FROM sales_data
MODEL
PARTITION BY (country)
DIMENSION BY (year)
MEASURES (sales)
RULES (
sales[2025] = sales[2024] * 1.1
);

💡 Predicts 2025 sales by increasing 2024’s value by 10%.

🧰 11. JSON and XML in Oracle SQL

JSON
SELECT JSON_VALUE(json_data, '$.[Link]') AS customer_name
FROM orders;

XML
SELECT EXTRACTVALUE(xml_col, '/Employee/Name') AS emp_name
FROM employees_xml;

🧮 12. DML RETURNING Clause

Return data from DML statements without extra queries.

DECLARE
v_new_id employees.emp_id%TYPE;
BEGIN
INSERT INTO employees (first_name, last_name, salary)
VALUES ('Nora', 'Chen', 8000)
RETURNING emp_id INTO v_new_id;
END;

🧩 13. BULK Operations (PL/SQL Integration)

Efficiently process large datasets.

DECLARE
TYPE t_emp_ids IS TABLE OF employees.emp_id%TYPE;
v_ids t_emp_ids;
BEGIN
SELECT emp_id BULK COLLECT INTO v_ids FROM employees WHERE dept_id = 10;
FORALL i IN v_ids.FIRST .. v_ids.LAST
DELETE FROM employees WHERE emp_id = v_ids(i);
END;

📚 Advanced Learning Resources

Type Resource
💻 Practice Oracle Live SQL
📘 Docs Oracle Database SQL Language Reference
📗 Tuning Oracle Database Performance Tuning Guide
🎓 Free Course Oracle University – SQL Learning Path
🧠 Community AskTom — expert Q&A from Oracle architects

Would you like me to build a 10-day “Advanced Oracle SQL Mastery Plan” (with daily
hands-on challenges and query solutions)?
It’s great for progressing from intermediate to expert with real-world Oracle datasets.

You said:

production case student to practice

ChatGPT said:

Perfect — you want a realistic, production-style Oracle SQL case study to practice your skills
like a real data analyst or database developer.

Here’s a full “Student Information System” case study designed for intermediate to
advanced SQL learners.
It includes realistic data, business questions, and advanced Oracle SQL challenges — just like
you’d face in a real project.

🎓 Oracle SQL Production Case: Student

Information System

🧱 1. Business Context
You’re working for a university’s IT department, which uses an Oracle Database to manage:

 Students
 Courses
 Instructors
 Enrollments
 Departments

Your job is to query, analyze, and optimize the data to support reports for academic and
administrative teams.

🗃️2. Database Schema

1️⃣ STUDENTS
CREATE TABLE students (
student_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
gender CHAR(1),
dob DATE,
department_id NUMBER,
admission_year NUMBER
);

2️⃣ COURSES
CREATE TABLE courses (
course_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
course_name VARCHAR2(100),
credits NUMBER(2),
 department_id NUMBER
);

3️⃣ INSTRUCTORS
CREATE TABLE instructors (
instructor_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE,
department_id NUMBER
);

4️⃣ DEPARTMENTS
CREATE TABLE departments (
department_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
dept_name VARCHAR2(100)
);

5️⃣ ENROLLMENTS
CREATE TABLE enrollments (
enrollment_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
student_id NUMBER REFERENCES students(student_id),
course_id NUMBER REFERENCES courses(course_id),
instructor_id NUMBER REFERENCES instructors(instructor_id),
semester VARCHAR2(10),
grade CHAR(2)
);

🧩 3. Sample Data
You can insert this to start:

INSERT INTO departments (dept_name)

VALUES ('Computer Science'), ('Mathematics'), ('Physics'), ('Business');

INSERT INTO courses (course_name, credits, department_id)

VALUES
('Database Systems', 3, 1),
('Linear Algebra', 4, 2),
('Quantum Mechanics', 4, 3),
('Marketing 101', 3, 4),
('Algorithms', 4, 1);

INSERT INTO instructors (first_name, last_name, hire_date, department_id)

VALUES
('Alice', 'Brown', DATE '2015-08-01', 1),
('Bob', 'Green', DATE '2018-02-15', 2),
('Carol', 'White', DATE '2010-06-10', 3),
 ('David', 'Black', DATE '2012-09-25', 4);

INSERT INTO students (first_name, last_name, gender, dob, department_id,

admission_year)
VALUES
('John', 'Doe', 'M', DATE '2002-04-15', 1, 2020),
('Maria', 'Lopez', 'F', DATE '2003-07-20', 1, 2021),
('Liam', 'Nguyen', 'M', DATE '2001-11-03', 2, 2019),
('Sophia', 'Kim', 'F', DATE '2002-01-30', 3, 2020),
('Ethan', 'Patel', 'M', DATE '2001-09-10', 4, 2019);

INSERT INTO enrollments (student_id, course_id, instructor_id, semester,

grade)
VALUES
(1, 1, 1, '2023A', 'A'),
(1, 5, 1, '2023B', 'B'),
(2, 1, 1, '2023A', 'A'),
(3, 2, 2, '2023A', 'C'),
(4, 3, 3, '2023B', 'B'),
(5, 4, 4, '2023A', 'A');
COMMIT;

📊 4. Practice Scenarios & Queries

Here are real-world-style SQL challenges organized by difficulty.

🧩 Basic (Warm-Up)

1. List all students with their department names.

2. SELECT s.first_name, s.last_name, d.dept_name
3. FROM students s
4. JOIN departments d ON s.department_id = d.department_id;
5. Find all courses offered by the Computer Science department.
6. SELECT course_name
7. FROM courses c
8. JOIN departments d ON c.department_id = d.department_id
9. WHERE d.dept_name = 'Computer Science';

⚙️Intermediate

3. Show the average grade per course (convert grades to numeric).

4. SELECT c.course_name,
5. ROUND(AVG(
6. CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2 ELSE
0 END
7. ), 2) AS avg_grade
8. FROM enrollments e
9. JOIN courses c ON e.course_id = c.course_id
 10. GROUP BY c.course_name;
11. Find students who took more than one course in the same semester.
12. SELECT student_id, semester, COUNT(*) AS num_courses
13. FROM enrollments
14. GROUP BY student_id, semester
15. HAVING COUNT(*) > 1;
16. Get the top 3 students by average GPA.
17. SELECT * FROM (
18. SELECT s.first_name, s.last_name,
19. AVG(CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN
2 ELSE 0 END) AS gpa
20. FROM enrollments e
21. JOIN students s ON e.student_id = s.student_id
22. GROUP BY s.first_name, s.last_name
23. ORDER BY gpa DESC
24. )
25. WHERE ROWNUM <= 3;

🧠 Advanced (Production-Level)

6. List each instructor with the number of distinct students they’ve taught.
7. SELECT i.first_name, i.last_name, COUNT(DISTINCT e.student_id) AS
num_students
8. FROM enrollments e
9. JOIN instructors i ON e.instructor_id = i.instructor_id
10. GROUP BY i.first_name, i.last_name;
11. Use an analytic function to rank students by GPA within their department.
12. SELECT s.first_name, s.last_name, d.dept_name,
13. ROUND(AVG(CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C'
THEN 2 ELSE 0 END),2) AS gpa,
14. RANK() OVER (PARTITION BY d.dept_name ORDER BY AVG(CASE grade
WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2 ELSE 0 END) DESC) AS
dept_rank
15. FROM enrollments e
16. JOIN students s ON e.student_id = s.student_id
17. JOIN departments d ON s.department_id = d.department_id
18. GROUP BY s.first_name, s.last_name, d.dept_name;
19. Identify “top-performing” instructors whose students have an average GPA > 3.5.
20. SELECT i.first_name, i.last_name,
21. ROUND(AVG(CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C'
THEN 2 ELSE 0 END), 2) AS avg_gpa
22. FROM enrollments e
23. JOIN instructors i ON e.instructor_id = i.instructor_id
24. GROUP BY i.first_name, i.last_name
25. HAVING AVG(CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2
ELSE 0 END) > 3.5;
26. Use a PIVOT query to display average GPA per semester per department.
27. SELECT * FROM (
28. SELECT d.dept_name,
29. [Link],
30. CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2
ELSE 0 END AS grade_points
31. FROM enrollments e
 32. JOIN students s ON e.student_id = s.student_id
33. JOIN departments d ON s.department_id = d.department_id
34. )
35. PIVOT (
36. AVG(grade_points)
37. FOR semester IN ('2023A' AS SEM_A, '2023B' AS SEM_B)
38. )
39. ORDER BY dept_name;
40. Build a materialized view for the academic dashboard.

CREATE MATERIALIZED VIEW mv_student_performance

BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT s.student_id,
s.first_name,
s.last_name,
ROUND(AVG(CASE grade WHEN 'A' THEN 4 WHEN 'B' THEN 3 WHEN 'C' THEN 2
ELSE 0 END), 2) AS gpa
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
GROUP BY s.student_id, s.first_name, s.last_name;

🧮 Bonus Challenge
Use a hierarchical query to show department → courses → instructors:

SELECT dept_name, course_name, instructor_name

FROM (
SELECT d.dept_name, c.course_name,
i.first_name || ' ' || i.last_name AS instructor_name,
ROWNUM AS rn
FROM departments d
JOIN courses c ON d.department_id = c.department_id
JOIN instructors i ON d.department_id = i.department_id
)
CONNECT BY PRIOR rn + 1 = rn
START WITH rn = 1;

🎯 Outcome
By completing this case:

 You’ll master Oracle joins, subqueries, analytics, pivots, and views.

 You’ll practice reporting, ranking, and performance-style SQL.
 You’ll have a reusable test dataset for your resume or interview portfolio.
Would you like me to create a guided 7-day practice plan for this Student Information System
case — with daily SQL goals, data challenges, and expected results?
(It’s like a mini bootcamp using this production dataset.)