Getting Hands-On with Oracle Live SQL: My Journey into Database Fundamentals
I recently dove into Oracle Live SQL to sharpen my database skills, and I wanted to share some of the cool things I learned—especially around clusters, indexing, and querying data efficiently.
🧩Creating a Clustered Setup
I started by experimenting with clusters, which allow related tables to be stored together physically. Here's a snippet of what I tried:
CREATE CLUSTER emp_dept_cluster (
deptno NUMBER(3)
)
SIZE 512;
CREATE INDEX idx_emp_dept_cluster
ON CLUSTER emp_dept_cluster;
Then I created two tables—dept and emp—that share the deptno key and are clustered together:
CREATE TABLE dept (
deptno NUMBER(3),
dname VARCHAR2(14),
loc VARCHAR2(13)
)
CLUSTER emp_dept_cluster (deptno);
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3)
)
CLUSTER emp_dept_cluster (deptno);
Pro tip: If you get ORA-00955: name is already used by an existing object, it means you’ve already created that object—just drop it or rename it before retrying.
🎓Building a Student Table and Querying Data
Next, I created a simple student table and practiced inserting and retrieving data:
CREATE TABLE student (
roll_no NUMBER,
name VARCHAR2(20),
dept VARCHAR2(10),
cgpa NUMBER
);
INSERT INTO student VALUES (101, 'Alice', 'CS', 8.5);
INSERT INTO student VALUES (110, 'Bob', 'IT', 7.8);
-- more inserts...
SELECT * FROM student;
This helped me understand how to structure data and run basic queries.
⚡Indexing for Performance
I also explored how indexes improve query performance:
-- Primary key index is implicit
CREATE TABLE student (
roll_no NUMBER PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(50),
cgpa NUMBER(3,2)
);
-- Explicit index on name
CREATE INDEX idx_name ON student(name);
-- Composite index
CREATE INDEX idx_dept_cgpa ON student(dept, cgpa);
Using these indexes, I ran queries like:
SELECT * FROM student WHERE name = 'Jack';
SELECT * FROM student WHERE dept = 'CSE' AND cgpa = 9.5;
The results were fast and efficient—Oracle Live SQL even shows you which indexes are being used!
🧮Categorizing with CASE Statements
Finally, I played with the CASE clause to categorize products based on price:
SELECT product_id, product_name,
CASE
WHEN list_price < 200 THEN 'Low'
WHEN list_price BETWEEN 200 AND 500 THEN 'Medium'
ELSE 'High'
END AS cost
FROM products
ORDER BY cost;
This is a great way to add logic directly into your queries.
Thanks to @santhoshnc sir for guiding me through indexing and query optimization concepts.
Top comments (0)