Complete SQL Concepts for MySQL & Oracle

✅ What is SQL?

SQL (Structured Query Language) is a standard language used to communicate with relational databases. It allows you to store, retrieve, manipulate, and manage data stored in tables.

🔹 1. SQL Language Types

Language Type	Commands
DDL (Def. Data)	CREATE, ALTER, DROP, TRUNCATE
DML (Man. Data)	INSERT, UPDATE, DELETE
DQL (Query)	SELECT
DCL (Control)	GRANT, REVOKE
TCL (Transaction)	COMMIT, ROLLBACK, SAVEPOINT

🔹 2. Create Tables (MySQL vs Oracle)

🐬 MySQL:

CREATE TABLE employee (
  emp_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  age INT,
  department VARCHAR(50),
  salary DECIMAL(10,2)
);

🟠 Oracle:

CREATE TABLE employee (
  emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR2(50),
  age NUMBER,
  department VARCHAR2(50),
  salary NUMBER(10,2)
);

🔹 3. Insert Data

INSERT INTO employee (name, age, department, salary)
VALUES ('John', 30, 'HR', 50000);

🔹 4. Select Data

SELECT name, salary FROM employee WHERE age > 25;

🔹 5. Update and Delete

UPDATE employee SET salary = salary + 5000 WHERE department = 'IT';
DELETE FROM employee WHERE emp_id = 3;

🔹 6. Constraints

A constraint in SQL is a rule enforced on the data in a table to maintain data integrity, accuracy, and validity.

🔹 Types of Constraints

Constraint	Description
NOT NULL	Ensures the column cannot have NULL values
UNIQUE	Ensures all values in a column are different
PRIMARY KEY	Uniquely identifies each record + NOT NULL
FOREIGN KEY	Ensures referential integrity between two tables
CHECK	Validates values based on a condition
DEFAULT	Sets a default value for a column

🔹 1. NOT NULL Constraint

CREATE TABLE employee (
  emp_id INT NOT NULL,
  name VARCHAR(50) NOT NULL
);

💡 emp_id and name must be provided — can’t be left empty.

🔹 2. UNIQUE Constraint

CREATE TABLE users (
  email VARCHAR(100) UNIQUE
);

💡 Prevents duplicate emails.

🔹 3. PRIMARY KEY Constraint

CREATE TABLE student (
  student_id INT PRIMARY KEY,
  name VARCHAR(100)
);

💡 Combines UNIQUE + NOT NULL. Only one primary key per table.

🔹 4. FOREIGN KEY Constraint

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

💡 Ensures customer_id in orders table must exist in customers table.

🔹 5. CHECK Constraint

CREATE TABLE product (
  price DECIMAL(10, 2),
  CHECK (price > 0)
);

💡 Prevents negative price values.

🔹 6. DEFAULT Constraint

CREATE TABLE users (
  status VARCHAR(10) DEFAULT 'active'
);

💡 If no value is provided, 'active' is automatically assigned.

🔍 Example: Combined Constraints

CREATE TABLE employee (
  emp_id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE NOT NULL,
  age INT CHECK (age >= 18),
  status VARCHAR(10) DEFAULT 'active'
);

🔹 7. Joins

What is a JOIN in SQL?

A JOIN in SQL is used to combine rows from two or more tables based on a related column between them — usually a foreign key.

Joins allow you to query across multiple tables as if they were one.

🔹 Example Tables

🧾 employees

emp_id	name	dept_id
1	Alice	10
2	Bob	20
3	Charlie	30

🧾 departments

dept_id	dept_name
10	HR
20	IT
40	Marketing

✅ Types of Joins in SQL

🔸 1. INNER JOIN

Returns rows where there is a match in both tables.

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

🧾 Result:

name	dept_name
Alice	HR
Bob	IT

Charlie is excluded because there is no matching department 30.

🔸 2. LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, and matched rows from the right table.
If no match, NULL is returned for right-side columns.

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

🧾 Result:

name	dept_name
Alice	HR
Bob	IT
Charlie	NULL

🔸 3. RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table, and matched rows from the left.

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

🧾 Result:

name	dept_name
Alice	HR
Bob	IT
NULL	Marketing

🔸 4. FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables. Non-matching rows have NULLs.

SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

🧾 Result:

name	dept_name
Alice	HR
Bob	IT
Charlie	NULL
NULL	Marketing

🟠 Note: MySQL doesn’t support FULL JOIN directly, but you can simulate it using UNION.

🔸 5. CROSS JOIN

Returns Cartesian product — every row from table A paired with every row from table B.

SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

🧾 If employees has 3 rows and departments has 3 → 9 rows total

🔸 6. SELF JOIN

A table joined to itself (useful for hierarchies like employee-manager).

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.emp_id;

🔹 8. Functions

Category	Examples
Aggregate	SUM(), AVG(), COUNT()
String	CONCAT(), UPPER(), SUBSTR()
Date	NOW() (MySQL), SYSDATE (Oracle)
Numeric	ROUND(), FLOOR(), CEIL()

🔹 9. Group By and Having

SELECT department, COUNT(*) 
FROM employee 
GROUP BY department 
HAVING COUNT(*) > 2;

🔹 10. Subqueries

Correlated Subquery:

It returns the names of employees whose salary is greater than the average salary in their department.

SELECT name 
FROM employee e 
WHERE salary > (SELECT AVG(salary) FROM employee WHERE department = e.department);

🔹 11. Views

A View is a virtual table in SQL that is based on a SELECT query. It does not store data physically, but provides a way to simplify and reuse complex queries.

syntax:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

//example
CREATE VIEW high_paid AS
SELECT name, salary FROM employee WHERE salary > 50000;

//query a view
SELECT * FROM high_paid;

🔹 Why Use Views?

Purpose	Benefit
Simplify complex SQL	Hide joins and aggregations
Security	Expose only required columns
Reusability	Centralized logic
Readability	Cleaner application code
Logical data independence	Query structure, not physical

-- Update View
CREATE OR REPLACE VIEW high_paid AS
SELECT name, salary FROM employee WHERE salary > 55000;

-- Drop View
DROP VIEW high_paid;

✅ What is a B-Tree in SQL?

A B-Tree (Balanced Tree) is the default data structure used by most relational database systems (MySQL, Oracle, PostgreSQL, SQL Server) to implement indexes.

B-Tree is short for Balanced Tree — a self-balancing tree data structure that keeps data sorted and allows fast search, insert, delete, and range queries in logarithmic time O(log n)

🔹 12. Indexes

An Index in SQL is a performance-tuning feature used to speed up the retrieval of data from a table. It works like the index in a book — instead of scanning every page, you jump directly to the needed information.

Without index: Full table scan 🔍
With index: Direct access 🔑

🔹 How an Index Works

Internally, indexes use data structures like:

• B-Tree (Balanced Tree) → most common
• Hash Index → used for exact match lookups
• Bitmap Index → used in OLAP systems (Oracle)

-- Basic index create index
CREATE INDEX idx_employee_name ON employee(name);

-- Composite index (multiple columns)
CREATE INDEX idx_dept_salary ON employee(department, salary);

-- Drop an index
DROP INDEX idx_employee_name;

-- In Oracle
DROP INDEX idx_employee_name;

-- In MySQL (using ALTER)
ALTER TABLE employee DROP INDEX idx_employee_name;

🔹 Types of Indexes

Type	Description
Single-column Index	Index on one column
Composite Index	Index on two or more columns
Unique Index	Prevents duplicate values
Primary Key Index	Automatically created on PK
Full-Text Index	Used for text search
Bitmap Index	Efficient for low-cardinality data (Oracle)
Clustered Index	Sorts the physical table data (e.g., in SQL Server)
Non-Clustered Index	Maintains a separate structure to point to the rows

🎯 Interview Questions

Question	Expected Answer
What is an index in SQL?	Data structure that improves search speed
What is the difference between clustered and non-clustered index?	Clustered changes row order, non-clustered does not
Can you index multiple columns?	Yes (composite index)
Do indexes affect data insertion speed?	Yes, they slow it down
What happens if a table has too many indexes?	Query optimizer might get confused and DML will slow down

🔹 13. Transactions

A Transaction in SQL is a sequence of one or more SQL operations that are executed as a single logical unit of work. A transaction ensures data integrity and consistency, especially in multi-step or concurrent operations.

A transaction allows multiple operations to either all succeed (COMMIT) or all fail and undo (ROLLBACK) — atomic behavior.

🔐 The ACID Properties of Transactions

Property	Description
A – Atomicity	All operations in a transaction are treated as one unit. If one fails, all are rolled back.
C – Consistency	Data must be valid according to all defined rules, before and after the transaction.
I – Isolation	Each transaction occurs independently of others (prevents dirty reads, etc.).
D – Durability	Once a transaction is committed, changes are permanent—even if system crashes.

START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE acc_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE acc_id = 2;

COMMIT;

✅ Both updates will be saved together.
❌ If anything fails, you can ROLLBACK and restore original state.

//rollback
START TRANSACTION;

UPDATE employee SET salary = salary + 1000 WHERE emp_id = 101;

-- Error occurs here
UPDATE employee SET salary = 'abc' WHERE emp_id = 102;

ROLLBACK;

🔹 Using SAVEPOINT (Optional)

START TRANSACTION;

UPDATE employee SET salary = 60000 WHERE emp_id = 1;
SAVEPOINT after_first;

UPDATE employee SET salary = 0 WHERE emp_id = 2; -- Mistake

ROLLBACK TO after_first;

COMMIT;

✅ The mistake is undone, but the first update is preserved.

🧠 Interview Questions on Transactions

Question	Expected Answer
What is a transaction?	A logical unit of work with ACID properties
What is atomicity in SQL?	All or nothing execution of operations
What happens if COMMIT is not issued?	Changes are not saved
What is a dirty read?	Reading uncommitted data
Can you rollback part of a transaction?	Yes, using SAVEPOINT

🔹 14. PL/SQL (Oracle only)

✅ This is a PL/SQL anonymous block in Oracle, which declares a variable, selects a value into it, and prints the result.

DECLARE
  v_name employee.name%TYPE;
BEGIN
  SELECT name INTO v_name FROM employee WHERE emp_id = 1;
  DBMS_OUTPUT.PUT_LINE(v_name);
END;

🔹 15. Stored Procedures

What is a Stored Procedure in SQL?

A Stored Procedure is a named block of SQL statements stored in the database that can be called and executed repeatedly. It’s like a function that performs a task — such as inserting, updating, or processing data.

🔁 Instead of writing the same SQL logic every time, you store it once and reuse it.

🔹 Benefits of Stored Procedures

Feature	Benefit
💡 Reusability	Write once, use many times
🔒 Security	Can restrict access to underlying tables
⚡ Performance	Compiled once, runs faster
📦 Encapsulation	Keeps business logic inside DB
🧪 Easy Maintenance	Central place to change logic

MySQL:

DELIMITER //

CREATE PROCEDURE GetEmp(IN eid INT)
BEGIN
  SELECT * FROM employee WHERE emp_id = eid;
END //

DELIMITER ;

🔍 Explanation (MySQL):

• DELIMITER // is used to define a custom delimiter so the semicolon (;) inside the procedure doesn’t confuse the SQL engine.
• CREATE PROCEDURE GetEmp(IN eid INT) defines a stored procedure with one input parameter eid.
• The SELECT * retrieves employee details for the given emp_id.

// call procedure in mysql
CALL GetEmp(1);

Oracle:

CREATE OR REPLACE PROCEDURE GetEmp(eid IN NUMBER) AS
BEGIN
  FOR rec IN (SELECT * FROM employee WHERE emp_id = eid) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.name);
  END LOOP;
END;

🔍 Explanation (Oracle):

• CREATE OR REPLACE PROCEDURE defines or replaces an existing stored procedure.
• eid IN NUMBER is an input parameter of type NUMBER.
• A cursor FOR loop iterates over results of the SELECT.
• DBMS_OUTPUT.PUT_LINE(rec.name) prints the employee name.

✅ Call the Procedure in Oracle:

BEGIN
  GetEmp(1);
END;

🔹 16. Triggers

A Trigger is a stored program in a database that automatically executes (or “fires”) in response to certain events on a table or view — like INSERT, UPDATE, or DELETE.

Unlike procedures, triggers are not called manually. They run automatically when an event occurs.

Example Explained: oracle

CREATE OR REPLACE TRIGGER before_insert_emp
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
  :NEW.salary := NVL(:NEW.salary, 30000);
END;

🔍 What this trigger does:

Logic: If the salary is NULL, it automatically sets it to 30000 using NVL().

Event: Fires before an INSERT is made into the employee table.

Row-level: Applies to each row being inserted.

🔹 17. Common Table Expressions (CTE)

A Common Table Expression (CTE) is a temporary named result set defined within a query. It makes complex queries more readable, especially those with subqueries, recursion, or multi-step logic.

Think of a CTE as a temporary view that exists only for the duration of the query.

🔹 Syntax

WITH cte_name AS (
    SELECT ... -- any valid query
)
SELECT * FROM cte_name;

✅ Simple Example

Task: Get employees with salary > 50000

WITH high_salary AS (
    SELECT emp_id, name, salary
    FROM employee
    WHERE salary > 50000
)
SELECT * FROM high_salary;

✅ Why Use a CTE?

Benefit	Description
💡 Readability	Break complex queries into logical parts
🔁 Reusability	Can reference the same result multiple times
📦 Modularity	Use CTEs to build multi-step queries
🔁 Supports recursion	Perfect for hierarchical or tree-structured data

🔹 18. Window Functions

✅ What are Window Functions in SQL?

Window Functions (also called Analytic Functions) perform calculations across a set of rows that are related to the current row, without collapsing the rows into a single result (unlike GROUP BY).

➕ They are like aggregate functions but preserve row-level details.

🔹 Syntax:

function_name([arguments]) OVER (
    [PARTITION BY column]
    [ORDER BY column]
    [ROWS BETWEEN ...]
)

✅ Example Table: employee

emp_id	name	department	salary
1	Alice	HR	40000
2	Bob	HR	50000
3	Carol	IT	60000
4	Dave	IT	70000
5	Erin	IT	65000

🔹 1. ROW_NUMBER() — Unique Row Number Per Partition

SELECT name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employee;

🧾 Result:

name	department	row_num
Bob	HR	1
Alice	HR	2
Dave	IT	1
Erin	IT	2
Carol	IT	3

🔹 2. RANK() and DENSE_RANK()

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employee;

• RANK skips numbers on ties.
• DENSE_RANK doesn’t skip.

🔹 3. SUM() as a Window Function

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employee;

📊 Result: Each row gets the department total salary.

🔹 4. AVG(), MIN(), MAX() — Per Partition

SELECT name, salary,
       AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employee;

🔹 5. LAG() / LEAD() — Access Previous/Next Row

SELECT name, salary,
       LAG(salary) OVER (ORDER BY salary) AS prev_salary,
       LEAD(salary) OVER (ORDER BY salary) AS next_salary
FROM employee;

👈 LAG() = previous row
👉 LEAD() = next row

🔹 6. NTILE(n) — Bucket/Quartile Assignment

SELECT name, salary,
       NTILE(3) OVER (ORDER BY salary) AS salary_band
FROM employee;

👑 Divides rows into 3 equal groups (tiles)

🔹 7. FIRST_VALUE() and LAST_VALUE()

SELECT name, department, salary,
       FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS top_salary
FROM employee;

🔹 Window vs Aggregate Functions

Feature	Aggregate Function (GROUP BY)	Window Function (OVER)
Output Rows	Reduces rows	Keeps all rows
Grouping	Required	Optional (PARTITION BY)
Use Case	Total/average per group	Rankings, running totals, etc

🔹 19. Differences Between MySQL and Oracle

Feature	MySQL	Oracle
Auto-increment	AUTO_INCREMENT	SEQUENCE or IDENTITY
String type	VARCHAR	VARCHAR2
Date function	NOW()	SYSDATE
Limit rows	LIMIT	ROWNUM, FETCH FIRST
Procedural extension	Not built-in (PL/SQL lite)	Full PL/SQL suppor

🔸 20. A. PL/SQL Programming Basics (Oracle Only)

PL/SQL = SQL + Procedural Logic (variables, conditions, loops)

📌 1. Hello World Block

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
END;

Note: You must enable output using:

SET SERVEROUTPUT ON;

📌 2. Variable Declaration and Assignment

DECLARE
  v_name VARCHAR2(50);
BEGIN
  v_name := 'John';
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;

📌 3. IF-ELSE Condition

DECLARE
  v_salary NUMBER := 60000;
BEGIN
  IF v_salary > 50000 THEN
    DBMS_OUTPUT.PUT_LINE('High Earner');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Low Earner');
  END IF;
END;

📌 4. FOR Loop (1 to 5)

BEGIN
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
  END LOOP;
END;

📌 5. Cursors (To fetch multiple rows)

DECLARE
  CURSOR emp_cursor IS SELECT name, salary FROM employee;
  v_name employee.name%TYPE;
  v_salary employee.salary%TYPE;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO v_name, v_salary;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name || ' - ' || v_salary);
  END LOOP;
  CLOSE emp_cursor;
END;

🔍 B. Performance Tuning Tips for SQL Queries

⚡ 1. Use Proper Indexing

• Index columns in WHERE, JOIN, and ORDER BY.
• Avoid indexing columns with low selectivity like gender.

⚡ 2. Avoid SELECT * in production

• Always use specific column names to reduce I/O.

⚡ 3. Use EXISTS over IN for correlated subqueries

-- Preferred for large datasets
SELECT name FROM employee e
WHERE EXISTS (
  SELECT 1 FROM department d WHERE d.dept_id = e.department
);

⚡ 4. Use CTEs instead of nested subqueries

WITH high_salary AS (
  SELECT * FROM employee WHERE salary > 60000
)
SELECT name FROM high_salary WHERE age < 35;

📊 C. Real-Life SQL Business Scenarios

🧾 Scenario 1: HRMS – Get employees who didn’t take leave this year

SELECT e.name
FROM employee e
LEFT JOIN leave_record l ON e.emp_id = l.emp_id AND EXTRACT(YEAR FROM l.leave_date) = 2025
WHERE l.emp_id IS NULL;

🧾 Scenario 2: Sales – Monthly sales report with total per rep

SELECT rep_id, TO_CHAR(sale_date, 'YYYY-MM') AS month, SUM(amount) AS total_sales
FROM sales
GROUP BY rep_id, TO_CHAR(sale_date, 'YYYY-MM');

🧾 Scenario 3: Inventory – Products that were never sold

SELECT p.product_id, p.name
FROM product p
LEFT JOIN order_details o ON p.product_id = o.product_id
WHERE o.product_id IS NULL;

🧾 Scenario 4: Banking – Calculate interest based on balance slabs

SELECT account_id, balance,
       CASE
         WHEN balance < 10000 THEN balance * 0.03
         WHEN balance BETWEEN 10000 AND 50000 THEN balance * 0.04
         ELSE balance * 0.05
       END AS interest
FROM accounts;

🧾 Scenario 5: Mark duplicate emails in user table

SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

🔹Interview Questions and Answers

Q1. Difference between DELETE, TRUNCATE, DROP?

Command	Removes Data	Removes Structure	Rollback	Speed
DELETE	✅	❌	✅	Slow
TRUNCATE	✅ (All Rows)	❌	❌	Fast
DROP	✅	✅	❌	Fast

Q2. IN vs EXISTS

• IN: Checks if a value exists in a list or subquery result.
• EXISTS: Checks if subquery returns any rows (often faster with correlated subqueries).

Q3. UNION vs UNION ALL

• UNION: Removes duplicates
• UNION ALL: Keeps all (faster)

Q4. What is a correlated subquery?

A subquery that uses a value from the outer query.

SELECT name 
FROM employee e 
WHERE salary > (SELECT AVG(salary) FROM employee WHERE department = e.department);

Q5. What is the use of indexes?

Improve query performance on large datasets.

Q6. ACID properties?

Property	Description
Atomicity	All or none
Consistency	Maintain valid state
Isolation	Transactions independent
Durability	Changes persist after commit

Q7. How to fetch top 5 highest salaries?

MySQL:

SELECT * FROM employee ORDER BY salary DESC LIMIT 5;

Oracle:

SELECT * FROM (
  SELECT * FROM employee ORDER BY salary DESC
) WHERE ROWNUM <= 5;

Q8. Difference: VARCHAR vs CHAR

Type	Fixed/Variable	Padding
CHAR	Fixed length	Padded
VARCHAR	Variable length	Not padded

🧪 Sample Tables

Check below queries

🧾 employee

emp_id	name	age	department	salary
1	John	30	HR	50000.00
2	Alice	28	IT	65000.00
3	David	35	IT	70000.00
4	Maria	40	HR	60000.00
5	Steve	25	Sales	45000.00

🧾 department

dept_id	dept_name
HR	HR
IT	IT
Sales	Sales

📌 SQL Query Examples

Q9. 📋 Get all employees from IT department

SELECT * FROM employee WHERE department = 'IT';

Q10. 💰 Get employees with salary > 60000

SELECT name, salary FROM employee WHERE salary > 60000;

Q11. 👥 Count employees in each department

SELECT department, COUNT(*) AS emp_count
FROM employee
GROUP BY department;

Q12. 🏅 Get highest salary employee

SELECT * FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee);

//Skips 0 rows and returns 1 row, i.e., the top-most (highest salary) row
Select * from employee order by salary desc limit 0, 1;  

//LIMIT 1 → returns only the first row, which is the employee with the highest salary
Select * from employee order by salary desc limit 1;   //same

Q13. 🧩 Join employee with department table

SELECT e.name, d.dept_name
FROM employee e
JOIN department d ON e.department = d.dept_id;         // INNER JOIN same as JOIN

Q14. 🏁 Top 2 salaried employees

✅ MySQL:

SELECT * FROM employee ORDER BY salary DESC LIMIT 2;

✅ Oracle:

SELECT * FROM (
  SELECT * FROM employee ORDER BY salary DESC
) WHERE ROWNUM <= 2;

Q15. 🔍 Employees earning above department average

SELECT name, department, salary
FROM employee e
WHERE salary > (
  SELECT AVG(salary)
  FROM employee
  WHERE department = e.department
);

Q16. 📊 Rank employees by salary (Window Function)

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employee;

name	salary	rank
Carol	70000	1
Alice	60000	2
Bob	50000	3
Dave	50000	3
Erin	40000	5

Q17. What is a primary key and can it be null?

Answer: A primary key uniquely identifies a record and cannot be NULL or duplicate.

Q18. Can a table have multiple foreign keys?

Answer: Yes. A table can have multiple foreign keys referencing different parent tables.

Q19. What’s the difference between WHERE and HAVING?

Clause	Used For	Example Usage
WHERE	Filter before group	WHERE age > 30
HAVING	Filter after group	HAVING COUNT(*) > 1

Q20. How to get nth highest salary?

✅ MySQL:

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET N-1;

//example 3rd highest
SELECT salary FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 2; -- 3rd highest

✅ Oracle:

SELECT * FROM (
  SELECT salary, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employee
) WHERE rnk = 3;

Q21. Difference between CHAR and VARCHAR?

Feature	CHAR	VARCHAR
Length	Fixed	Variable
Storage	Padded	As entered
Performance	Faster reads	Efficient storage

Q22. What is normalization?

Normalization is the process of organizing data in a database to reduce data redundancy and improve data integrity. It involves breaking a database into multiple related tables and defining relationships between them.

🔁 Normalization Forms (NF)

Normal Form	Rule
1NF (First Normal Form)	All columns must contain atomic (indivisible) values and no repeating groups.
2NF (Second Normal Form)	Must be in 1NF and every non-key column must be fully dependent on the entire primary key.
3NF (Third Normal Form)	Must be in 2NF and no transitive dependencies (non-key column depends on another non-key column).
BCNF (Boyce-Codd Normal Form)	Stricter version of 3NF — every determinant must be a candidate key.
4NF/5NF	Deals with multi-valued dependencies and join dependencies (used in very advanced cases).

Q23. Difference between DELETE, TRUNCATE, DROP?

Operation	Removes Data	Rollback	Structure
DELETE	✅ Yes	✅ Yes	❌ No
TRUNCATE	✅ All Rows	❌ No	❌ No
DROP	✅ All Rows	❌ No	✅ Yes

Q24. How to prevent SQL Injection?

• Use Prepared Statements
• Validate and escape user input
• Avoid dynamic SQL

Example (MySQL/Java):

PreparedStatement stmt = con.prepareStatement("SELECT * FROM user WHERE email = ?");
stmt.setString(1, email);

Q25. What is a CTE (Common Table Expression)?

CTE allows you to define a temporary result set.

WITH HighSalaries AS (
  SELECT * FROM employee WHERE salary > 60000
)
SELECT * FROM HighSalaries WHERE age < 35;

Q26. What are window functions?

They perform calculations across a set of rows related to the current row.

SELECT name, salary, RANK() OVER (ORDER BY salary DESC)
FROM employee;

📌 27. 🧓 Get average salary by department (rounded)

SELECT department, ROUND(AVG(salary), 2) AS avg_salary
FROM employee
GROUP BY department;

📌 28. 🕵️ Find employees with same salary

SELECT e1.name, e2.name, e1.salary
FROM employee e1
JOIN employee e2 ON e1.salary = e2.salary AND e1.emp_id <> e2.emp_id;

📌 29. 🧑‍💻 Find departments with more than 1 employee

SELECT department, COUNT(*) as total
FROM employee
GROUP BY department
HAVING COUNT(*) > 1;

📌 30. 🧮 Calculate salary percent of each employee vs total

SELECT name, salary,
       ROUND(salary / (SELECT SUM(salary) FROM employee) * 100, 2) AS percent_of_total
FROM employee;

📌 31. 📍 Get second highest salary (Generic Oracle/MySQL)

SELECT MAX(salary) 
FROM employee 
WHERE salary < (SELECT MAX(salary) FROM employee);

✅ Alternative using RANK() in Oracle/MySQL 8+:

SELECT name, salary
FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
  FROM employee
) ranked
WHERE rnk = 2;

📌 32. 🛑 Find employees who are not in any department (Assume department_id in both tables)

SELECT name 
FROM employee 
WHERE department NOT IN (SELECT dept_id FROM department);

✅ Use LEFT JOIN version to avoid issues with NULLs:

SELECT e.name
FROM employee e
LEFT JOIN department d ON e.department = d.dept_id
WHERE d.dept_id IS NULL;

📌 33. 📋 Employee list sorted by department name and then salary

SELECT e.name, e.salary, d.dept_name
FROM employee e
JOIN department d ON e.department = d.dept_id
ORDER BY d.dept_name ASC, e.salary DESC;

Q34. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

Function	Behavior with Ties
RANK()	Skips ranks (1, 2, 2, 4…)
DENSE_RANK()	No gaps (1, 2, 2, 3…)
ROW_NUMBER()	Unique numbers (1, 2, 3…)

✅ Example:

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC),
       DENSE_RANK() OVER (ORDER BY salary DESC),
       ROW_NUMBER() OVER (ORDER BY salary DESC)
FROM employee;

Q35. What are indexes? When should you not use them?

Indexes improve SELECT query performance.

⚠️ Avoid indexes when:

• On small tables
• Columns with low selectivity (e.g., gender)
• Frequent INSERT/UPDATE/DELETE – index maintenance cost

Q36. What is the difference between EXISTS and IN?

• IN checks against a fixed list or subquery
• EXISTS checks row existence (typically faster in correlated subqueries)

✅ Example:

-- Using IN
SELECT name FROM employee WHERE department IN (SELECT dept_id FROM department);

-- Using EXISTS
SELECT name FROM employee e
WHERE EXISTS (
  SELECT 1 FROM department d WHERE d.dept_id = e.department
);

Q37. Explain MERGE in Oracle (UPSERT)

MERGE INTO employee e
USING temp_emp t
ON (e.emp_id = t.emp_id)
WHEN MATCHED THEN
  UPDATE SET e.salary = t.salary
WHEN NOT MATCHED THEN
  INSERT (emp_id, name, department, salary)
  VALUES (t.emp_id, t.name, t.department, t.salary);

🚀 Oracle vs MySQL Quick Reference Table

Feature	Oracle	MySQL
Procedural SQL	Full PL/SQL	Limited
Sequence Generator	SEQUENCE / IDENTITY	AUTO_INCREMENT
CTE Support	✅ (WITH)	✅ (MySQL 8+)
Recursive Query	✅	✅ (MySQL 8+)
Triggers	Powerful & flexible	Limited
Window Functions	✅	✅ (MySQL 8+)
Query Limiting	ROWNUM / FETCH FIRST	LIMIT

🔸 Q38: Find employees who earn more than the average salary of their department.

SELECT name, salary, department
FROM employee e
WHERE salary > (
  SELECT AVG(salary)
  FROM employee
  WHERE department = e.department
);

🔸 Q39: Get employees with duplicate salaries.

SELECT salary, COUNT(*) 
FROM employee 
GROUP BY salary 
HAVING COUNT(*) > 1;

🔸 Q40: Write a query to fetch department-wise highest paid employee.

SELECT name, salary, department
FROM (
  SELECT name, salary, department,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employee
) t
WHERE rnk = 1;

❓ Advanced SQL Interview Questions

Q41. What is a materialized view in Oracle?

Answer: It stores the result of a query physically on disk and can be refreshed.

CREATE MATERIALIZED VIEW emp_summary
AS SELECT department, AVG(salary) FROM employee GROUP BY department;

Q42. Explain EXPLAIN PLAN

Used to check the execution path of a query for performance tuning.

EXPLAIN PLAN FOR
SELECT * FROM employee WHERE department = 'IT';

Then:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Q43. Difference between PROCEDURE and FUNCTION in PL/SQL

Feature	PROCEDURE	FUNCTION
Returns value	❌ (can use OUT param)	✅ Yes (must return value)
Call in SQL	❌ (Only from PL/SQL)	✅ (From SQL directly)
Use case	Tasks, insert/update	Calculations, return scalar

Q44. Recursive CTE example (MySQL 8+/Oracle)

Find factorial using recursive CTE:

WITH RECURSIVE factorial(n, fact) AS (
  SELECT 1, 1
  UNION ALL
  SELECT n+1, (n+1)*fact FROM factorial WHERE n < 5
)
SELECT * FROM factorial;

Q45. How do you debug PL/SQL?

• Use DBMS_OUTPUT.PUT_LINE for step-wise debugging.
• Use tools like Oracle SQL Developer with breakpoints.
• Use exception blocks:

BEGIN
  -- your code
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Q46. write procedure to update salary?

🛠 Procedure: Update Salary By Emp ID

CREATE OR REPLACE PROCEDURE update_salary (
  p_emp_id IN employee.emp_id%TYPE,
  p_increment IN NUMBER
) AS
BEGIN
  UPDATE employee
  SET salary = salary + p_increment
  WHERE emp_id = p_emp_id;
END;


//You can call it using an anonymous PL/SQL block like this:
BEGIN
  update_salary(101, 5000);  -- Increase salary of employee with emp_id = 101 by 5000
END;

🔹 p_emp_id IN employee.emp_id%TYPE

• p_emp_id: Name of the input parameter (you can name it anything, p_ is just a convention meaning “parameter”)
• IN: This is an input parameter (you pass a value when calling the procedure)
• employee.emp_id%TYPE: This tells Oracle to use the same data type as the emp_id column from the employee table.

Q47. 📥 Function: Get Employee Bonus Based on Salary

CREATE OR REPLACE FUNCTION get_bonus (
  p_salary IN NUMBER
) RETURN NUMBER IS
  v_bonus NUMBER;
BEGIN
  IF p_salary < 40000 THEN
    v_bonus := p_salary * 0.1;
  ELSIF p_salary BETWEEN 40000 AND 70000 THEN
    v_bonus := p_salary * 0.15;
  ELSE
    v_bonus := p_salary * 0.2;
  END IF;
  RETURN v_bonus;
END;

Call this:

SELECT name, salary, get_bonus(salary) AS bonus FROM employee;

🔸 SQL MCQs for Interview

Q48. What is the output of: SELECT COUNT(NULL)?

• A) 0 ✅
• B) NULL
• C) Error

COUNT ignores NULLs.

Q49. Which command permanently removes a table?

• A) DELETE
• B) TRUNCATE
• C) DROP ✅
• D) ERASE

Q50. Which JOIN returns all records from left table even if there is no match?

• A) INNER JOIN
• B) RIGHT JOIN
• C) LEFT JOIN ✅
• D) CROSS JOIN

Q51. Can a PRIMARY KEY be NULL?

• A) Yes
• B) No ✅

Q51. Which is faster for large datasets: EXISTS or IN?

• A) IN
• B) EXISTS ✅
• C) BOTH
• D) Neither

Q52. What will this return?

SELECT 10/0 FROM dual;

• A) NULL
• B) 0
• C) ERROR ✅

Division by zero = error

Q53. What does ROWNUM do in Oracle?

• A) Returns row count
• B) Limits number of rows ✅
• C) Orders rows
• D) Shows row index

Example: Get top 3 rows

SELECT * FROM employee
WHERE ROWNUM <= 3;

Q54. Can a foreign key reference a unique column?

• A) No
• B) Yes ✅

Q55. Which clause is used after GROUP BY?

• A) WHERE
• B) HAVING ✅
• C) ORDER BY
• D) LIMIT

Q56. Which of these is true for UNION?

• A) Keeps duplicates
• B) Removes duplicates ✅
• C) Faster than UNION ALL
• D) Both A & C

Q57. Which of these can be used inside SELECT?

• A) Procedure
• B) Function ✅
• C) Trigger
• D) Cursor

Q58. Which of the following ensures referential integrity?

• A) PRIMARY KEY
• B) UNIQUE
• C) FOREIGN KEY ✅
• D) CHECK

Q59. Which of the following statements about TRUNCATE is false?

• A) It removes all rows
• B) It can be rolled back ✅
• C) It is faster than DELETE
• D) It resets identity in MySQL (InnoDB)

Q60. What is the default sorting order of ORDER BY?

• A) DESC
• B) ASC ✅
• C) None
• D) Random

Q61. What is the purpose of EXPLAIN?

• A) Describe a table
• B) Explain a procedure
• C) Show query plan ✅
• D) Debug PL/SQL

Q62. How to Delete All Duplicate Rows from a Table (Keep Only One)

emp_id	name	salary
1	Alice	50000
2	Bob	60000
3	Alice	50000
4	Carol	55000
5	Bob	60000

Duplicates: rows for Alice and Bob (same name & salary).

1. For Oracle

Using ROWID to delete duplicates and keep one:

DELETE FROM employee e
WHERE ROWID > (
  SELECT MIN(ROWID)
  FROM employee
  WHERE e.name = name AND e.salary = salary
);

🔍 How It Works:

• For each duplicate group (same name & salary), keep the row with the lowest ROWID
• Delete all others

2. For MySQL (Using DELETE JOIN)

DELETE e1 FROM employee e1
JOIN employee e2
ON e1.name = e2.name AND e1.salary = e2.salary
WHERE e1.emp_id > e2.emp_id;

💡 Explanation:

• Compares each row with every other row having same name and salary
• Deletes the one with higher emp_id

Q63. Types (Storage Engines) in MySQL ?

Engine	Transactions	Row Locking	Foreign Keys	Use Case

InnoDB

✅ Yes

✅ Yes

✅ Yes

OLTP, modern apps

MyISAM

❌ No

❌ No

❌ No

Reporting, legacy

MEMORY

❌ No

✅ Yes

❌ No

Temp data, fast cache

CSV

❌ No

❌ No

❌ No

Data exchange

ARCHIVE

❌ No

❌ No

❌ No

Logs, large historical

BLACKHOLE

❌ No

❌ No

❌ No

Triggers, replication

Example : create table

CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(100)
) ENGINE = InnoDB;