1. What is SQL?

Answer:
SQL (Structured Query Language) is a standard language used to
communicate with relational databases. It is used to create,
retrieve, update, delete, and manage data stored in databases.

Example:

SELECT * FROM Employee;

2. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE TRUNCATE DROP

Deletes selected Deletes the entire

Deletes all rows
rows table

Removes table
WHERE clause can
WHERE cannot be used structure and
be used
data

Can be rolled Can be rolled back Cannot recover

back (before (depending on easily after
COMMIT) DBMS/transaction) commit

Table remains Table remains Table is removed

Example:

DELETE FROM Employee WHERE emp_id=101;

TRUNCATE TABLE Employee;

DROP TABLE Employee;

3. What is a Primary Key?

Answer:
A Primary Key is a column (or combination of columns) that
uniquely identifies each record in a table.

Properties:

 Unique

 Cannot contain NULL values

  Only one Primary Key per table

Example:

CREATE TABLE Employee(

emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(30)
);

4. What is a Foreign Key?

Answer:
A Foreign Key is a column that links one table to another. It
references the Primary Key of another table and maintains
referential integrity.

Example:

FOREIGN KEY(dept_id)
REFERENCES Department(dept_id);

5. What is the difference between WHERE and HAVING?

WHERE HAVING

Filters rows Filters groups

Used before GROUP BY Used after GROUP BY

Cannot use aggregate Can use aggregate

functions functions

Example:

SELECT *
FROM Employee
WHERE salary>30000;

SELECT dept_id,AVG(salary)
FROM Employee
GROUP BY dept_id
HAVING AVG(salary)>40000;

6. What is the difference between GROUP BY and ORDER BY?

GROUP BY ORDER BY

Groups rows Sorts rows

Used with aggregate Used for ascending or

functions descending order

Does not sort

Sorts using ASC or DESC
automatically

Example:

SELECT dept_id,COUNT(*)
FROM Employee
GROUP BY dept_id;

SELECT *
FROM Employee
ORDER BY salary DESC;

7. What are SQL Joins? Explain all types.

Answer:
A JOIN combines rows from two or more tables based on a related
column.

INNER JOIN

Returns only matching records.

SELECT *
FROM Employee e
INNER JOIN Department d
ON e.dept_id=d.dept_id;

LEFT JOIN

Returns all records from the left table and matching records from
the right table.

RIGHT JOIN

Returns all records from the right table and matching records
from the left table.

FULL OUTER JOIN

Returns all matching and non-matching records from both tables.

CROSS JOIN
Returns every combination of rows from both tables.

SELF JOIN

Joins a table with itself.

8. What is a Subquery?

Answer:
A Subquery is a query inside another SQL query. It is executed
first, and its result is used by the outer query.

Example:

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

9. What is the difference between UNION and UNION ALL?

UNION UNION ALL

Keeps
Removes duplicate
duplicate
rows
rows

Slower Faster

Returns unique Returns all

records records

10. What is the difference between CHAR and VARCHAR?

CHAR VARCHAR2 (Oracle)

Fixed length Variable length

Wastes space if data is

Saves space
shorter
CHAR VARCHAR2 (Oracle)

Faster for fixed-size Better for variable-

values length text

Example:

CHAR(10)

stores exactly 10 characters.

VARCHAR2(10)

stores only the entered characters.

11. What are Aggregate Functions?

Answer:
Aggregate functions perform calculations on multiple rows and
return a single result.

Common aggregate functions:

 COUNT()

 SUM()

 AVG()

 MAX()

 MIN()

Example:

SELECT AVG(salary)
FROM Employee;

12. What is the difference between IN and EXISTS?

IN EXISTS

Checks if rows
Compares values
exist

Best for small Better for large

datasets datasets
IN EXISTS

Compares all Stops after first

values match

IN Example:

SELECT *
FROM Employee
WHERE dept_id IN
(1,2,3);

EXISTS Example:

SELECT *
FROM Department d
WHERE EXISTS
(
SELECT *
FROM Employee e
WHERE d.dept_id=e.dept_id
);

13. What is NULL in SQL?

Answer:
NULL means the value is unknown, missing, or not available. It is
different from zero or an empty string.

Example:

SELECT *
FROM Employee
WHERE dept_id IS NULL;

14. What is the difference between PRIMARY KEY and UNIQUE

KEY?

PRIMARY KEY UNIQUE KEY

Can contain one NULL (Oracle allows multiple

Cannot
NULLs in a unique constraint because NULLs are
contain NULL
treated as unknown)
PRIMARY KEY UNIQUE KEY

Only one per

Multiple UNIQUE constraints allowed
table

Uniquely
Ensures uniqueness but is not the primary
identifies
identifier
records

15. What is a View?

Answer:
A View is a virtual table created using a SQL query. It does not
store data itself; it displays data from one or more tables.

Example:

CREATE VIEW EmpView AS

SELECT emp_name,salary
FROM Employee;

16. What is an Index?

Answer:
An Index improves the speed of data retrieval from a table. It
works like an index in a book, allowing the database to find rows
faster.

Example:

CREATE INDEX idx_name

ON Employee(emp_name);

17. What are Constraints in SQL?

Answer:
Constraints are rules applied to table columns to ensure data
accuracy and integrity.

Types:

 PRIMARY KEY

 FOREIGN KEY

 UNIQUE
  NOT NULL

 CHECK

 DEFAULT (supported in Oracle)

18. How do you find the Nth highest salary?

Answer:
In Oracle, one common approach is using DENSE_RANK().

Example (3rd highest salary):

SELECT salary
FROM
(
SELECT salary,
DENSE_RANK() OVER(ORDER BY salary DESC) rnk
FROM Employee
)
WHERE rnk=3;

19. What is Normalization?

Answer:
Normalization is the process of organizing data to reduce
redundancy and improve data integrity.

Normal Forms:

 1NF: No repeating groups; atomic values.

 2NF: Remove partial dependency.

 3NF: Remove transitive dependency.

 BCNF: Stronger version of 3NF.

Advantages:

 Reduces data duplication

 Improves consistency

 Saves storage

 Simplifies maintenance
20. What are ACID Properties in Databases?

Answer:
ACID properties ensure reliable database transactions.

 A – Atomicity: A transaction is completed entirely or not at

all.

 C – Consistency: The database remains in a valid state

before and after a transaction.

 I – Isolation: Concurrent transactions do not interfere with

each other.

 D – Durability: Once a transaction is committed, the changes

are permanently saved.

1. Create Department Table

CREATE TABLE Department (

dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(30),
location VARCHAR2(30)
);

2. Create Employee Table

CREATE TABLE Employee (

emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(30),
age NUMBER,
salary NUMBER(10,2),
dept_id NUMBER,
city VARCHAR2(30),
CONSTRAINT fk_dept
FOREIGN KEY (dept_id)
REFERENCES Department(dept_id)
);

3. Insert Data

Department

INSERT INTO Department VALUES (1,'HR','Mumbai');

INSERT INTO Department VALUES (2,'IT','Pune');
INSERT INTO Department VALUES (3,'Finance','Delhi');
INSERT INTO Department VALUES (4,'Marketing','Bangalore');

COMMIT;

Employee

INSERT INTO Employee VALUES (101,'Rahul',25,35000,1,'Mumbai');

INSERT INTO Employee VALUES (102,'Priya',28,45000,2,'Pune');
INSERT INTO Employee VALUES (103,'Amit',30,50000,1,'Mumbai');
INSERT INTO Employee VALUES (104,'Neha',27,42000,3,'Delhi');
INSERT INTO Employee VALUES (105,'Karan',35,70000,2,'Pune');
INSERT INTO Employee VALUES (106,'Anjali',24,30000,NULL,'Nagpur');
INSERT INTO Employee VALUES (107,'Rohit',31,65000,4,'Hyderabad');

COMMIT;

4. Display Data

SELECT * FROM Employee;

SELECT * FROM Department;

5. WHERE Clause

Salary greater than 40,000

SELECT *
FROM Employee
WHERE salary > 40000;

Employees from Mumbai

SELECT *
FROM Employee
WHERE city='Mumbai';

6. ORDER BY

Highest salary first

SELECT *
FROM Employee
ORDER BY salary DESC;
7. Aggregate Functions

Highest Salary

SELECT MAX(salary)
FROM Employee;

Lowest Salary

SELECT MIN(salary)
FROM Employee;

Average Salary

SELECT AVG(salary)
FROM Employee;

Total Salary

SELECT SUM(salary)
FROM Employee;

Count Employees

SELECT COUNT(*)
FROM Employee;

8. GROUP BY

Department-wise employee count

SELECT dept_id,
COUNT(*)
FROM Employee
GROUP BY dept_id;

Average salary department-wise

SELECT dept_id,
AVG(salary)
FROM Employee
GROUP BY dept_id;

9. HAVING

Departments whose average salary is greater than 40,000

SELECT dept_id,
AVG(salary)
FROM Employee
GROUP BY dept_id
HAVING AVG(salary) > 40000;

10. INNER JOIN

SELECT e.emp_name,
d.dept_name
FROM Employee e
INNER JOIN Department d
ON e.dept_id = d.dept_id;

11. LEFT OUTER JOIN

SELECT e.emp_name,
d.dept_name
FROM Employee e
LEFT OUTER JOIN Department d
ON e.dept_id = d.dept_id;

12. RIGHT OUTER JOIN

SELECT e.emp_name,
d.dept_name
FROM Employee e
RIGHT OUTER JOIN Department d
ON e.dept_id = d.dept_id;

13. FULL OUTER JOIN

SELECT e.emp_name,
d.dept_name
FROM Employee e
FULL OUTER JOIN Department d
ON e.dept_id = d.dept_id;

14. CROSS JOIN

SELECT *
FROM Employee
CROSS JOIN Department;

15. SELF JOIN

SELECT e1.emp_name,
e2.emp_name,
e1.dept_id
FROM Employee e1
JOIN Employee e2
ON e1.dept_id = e2.dept_id
AND e1.emp_id <> e2.emp_id;

16. Subqueries

Highest Salary

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

Second Highest Salary

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

Employees earning above average salary

SELECT *
FROM Employee
WHERE salary >
(
SELECT AVG(salary)
FROM Employee
);

Employees in IT Department

SELECT *
FROM Employee
WHERE dept_id =
(
SELECT dept_id
FROM Department
WHERE dept_name='IT'
);

Departments having employees

SELECT *
FROM Department
WHERE dept_id IN
(
SELECT dept_id
FROM Employee
);

Departments without employees

SELECT *
FROM Department
WHERE dept_id NOT IN
(
SELECT dept_id
FROM Employee
WHERE dept_id IS NOT NULL
);

17. UPDATE

Increase salary by 10%

UPDATE Employee
SET salary = salary * 1.10;
COMMIT;

18. DELETE

Delete employee 107

DELETE FROM Employee

WHERE emp_id = 107;

COMMIT;

19. Oracle SQL*Plus Interview Queries

Find Second Highest Salary

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

Find Third Highest Salary

SELECT salary
FROM
(
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
)
WHERE ROWNUM <= 3
MINUS
SELECT salary
FROM
(
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
)
WHERE ROWNUM <= 2;
Find Duplicate Employee Names

SELECT emp_name,
COUNT(*)
FROM Employee
GROUP BY emp_name
HAVING COUNT(*) > 1;

Find Employees Without Department

SELECT *
FROM Employee
WHERE dept_id IS NULL;

Highest Salary in Each Department

SELECT dept_id,
MAX(salary)
FROM Employee
GROUP BY dept_id;