Interview Questions on Oracle Sql for Freshers | 0 to 1 Years of Experience

Chapter 1 — Introduction to Oracle SQL

Overview of RDBMS

Question 1:
What is a Relational Database Management System (RDBMS)?
Answer: An RDBMS is a type of database management system that stores data in tables with rows and columns. It allows users to perform operations like querying, updating, and managing data while maintaining relationships between tables.

Question 2:
What are the main features of an RDBMS?
Answer: Key features include data integrity, data security, scalability, normalization, and support for SQL queries.

Question 3:
How does an RDBMS ensure data integrity?
Answer: Data integrity is ensured through constraints such as primary keys, foreign keys, unique constraints, and check constraints.

Question 4:
What are the advantages of using an RDBMS?
Answer: Advantages include efficient data retrieval, flexibility in query execution, support for multi-user environments, and data consistency.

Question 5:
Name some popular RDBMS software.
Answer: Popular RDBMS software includes Oracle Database, MySQL, PostgreSQL, SQL Server, and SQLite.

Oracle Database Architecture

Question 6:
What is Oracle Database?
Answer: Oracle Database is a multi-model database management system developed by Oracle Corporation that supports SQL and PL/SQL for managing relational and non-relational data.

Question 7:
What are the main components of Oracle Database architecture?
Answer: The main components include the Instance (SGA and background processes) and the Database (datafiles, control files, and redo log files).

Question 8:
What is the System Global Area (SGA)?
Answer: SGA is a shared memory area used by the Oracle instance to store data and control information for database operations.

Question 9:
What is the role of background processes in Oracle Database?
Answer: Background processes handle tasks like writing data to disk (DBWn), maintaining redo logs (LGWR), and recovering the database in case of failure (SMON, PMON).

Question 10:
What is the difference between an Oracle instance and an Oracle database?
Answer: The instance is the memory structures and processes running the database, while the database is the physical storage structures (datafiles, control files).

SQL vs PL/SQL

Question 11:
What is the difference between SQL and PL/SQL?
Answer: SQL is a declarative language used for querying and manipulating data, while PL/SQL is a procedural extension of SQL used for writing programs with loops, conditions, and exception handling.

Question 12:
Can SQL execute procedural logic?
Answer: No, SQL cannot execute procedural logic like loops or conditions. For that, PL/SQL is used.

Question 13:
Is PL/SQL platform-independent?
Answer: Yes, PL/SQL is platform-independent as it runs within the Oracle Database.

Question 14:
What are some common uses of PL/SQL?
Answer: PL/SQL is used for writing stored procedures, functions, triggers, and managing complex business logic within the database.

Question 15:
Can SQL and PL/SQL be used together?
Answer: Yes, PL/SQL can include SQL statements to perform operations on the database.

Tools (SQL*Plus, SQL Developer)

Question 16:
What is SQL*Plus?
Answer: SQL*Plus is a command-line tool provided by Oracle for executing SQL and PL/SQL commands.

Question 17:
What is Oracle SQL Developer?
Answer: Oracle SQL Developer is a graphical tool for database development, offering a user-friendly interface for writing and executing SQL/PLSQL and managing database objects.

Question 18:
What is the difference between SQL*Plus and SQL Developer?
Answer: SQL*Plus is a command-line interface, while SQL Developer is a graphical interface with additional features like debugging and data modeling.

Question 19:
Can SQL Developer be used to connect to other databases?
Answer: Yes, SQL Developer supports connections to databases like MySQL, PostgreSQL, and SQL Server with proper configurations.

Question 20:
What are the advantages of using SQL Developer over SQL*Plus?
Answer: SQL Developer provides a better user interface, debugging tools, integrated version control, and reporting features.

Chapter 2 — Basic SQL Statements

SELECT, FROM, WHERE

Question 21:
What is the purpose of the SELECT statement in SQL?
Answer: The SELECT statement is used to retrieve data from one or more tables in a database.

Question 22:
What is the syntax of a basic SELECT statement?
Answer:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Question 23:
What is the FROM clause used for?
Answer: The FROM clause specifies the table(s) from which the data is to be retrieved.

Question 24:
What is the WHERE clause used for?
Answer: The WHERE clause is used to filter rows based on specified conditions.

Question 25:
Can a SELECT statement work without a WHERE clause?
Answer: Yes, if the WHERE clause is omitted, the SELECT statement retrieves all rows from the specified table(s).

Filtering with Operators

Question 26:
What is the purpose of operators in SQL?
Answer: Operators are used in SQL to specify conditions in the WHERE clause. They include comparison, logical, and arithmetic operators.

Question 27:
Give an example of a comparison operator.
Answer: Examples include =, <, >, <=, >=, <>.

Question 28:
What is the BETWEEN operator?
Answer: The BETWEEN operator is used to filter values within a range.

SELECT * FROM employees WHERE salary BETWEEN 3000 AND 5000;

Question 29:
What is the IN operator?
Answer: The IN operator checks if a value matches any value in a list.

SELECT * FROM employees WHERE department_id IN (10, 20, 30);

Question 30:
What does the LIKE operator do?
Answer: The LIKE operator is used to search for patterns in text. Wildcards % (multiple characters) and _ (single character) are used.

Sorting Results with ORDER BY

Question 31:
What is the purpose of the ORDER BY clause?
Answer: The ORDER BY clause is used to sort the result set in ascending (ASC) or descending (DESC) order.

Question 32:
What is the default sorting order in the ORDER BY clause?
Answer: The default sorting order is ascending (ASC).

Question 33:
How can you sort by multiple columns?
Answer: By listing the columns separated by commas in the ORDER BY clause.

SELECT * FROM employees ORDER BY department_id, salary DESC;

Question 34:
Can ORDER BY be used with expressions?
Answer: Yes, you can sort by expressions or computed columns.

SELECT salary + bonus AS total FROM employees ORDER BY total DESC;

Question 35:
How do you order rows based on a column alias?
Answer: Use the alias defined in the SELECT statement.

SELECT salary + bonus AS total FROM employees ORDER BY total DESC;

---

Chapter 3 — Data Manipulation Language (DML)

INSERT, UPDATE, DELETE

Question 36:
What is the purpose of the INSERT statement?
Answer: The INSERT statement is used to add new rows to a table.

Question 37:
What is the basic syntax of the INSERT statement?
Answer:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Question 38:
Can you insert rows without specifying column names?
Answer: Yes, but you must provide values for all columns in the table.

INSERT INTO table_name VALUES (value1, value2, ...);

Question 39:
What is the purpose of the UPDATE statement?
Answer: The UPDATE statement modifies existing rows in a table based on specified conditions.

Question 40:
What is the syntax of the UPDATE statement?
Answer:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Question 41:
What is the DELETE statement used for?
Answer: The DELETE statement removes rows from a table based on specified conditions.

Question 42:
What is the syntax of the DELETE statement?
Answer:

DELETE FROM table_name WHERE condition;

Question 43:
What happens if you omit the WHERE clause in a DELETE statement?
Answer: Omitting the WHERE clause deletes all rows from the table. Use caution when performing this action.

Question 44:
Can the DELETE statement be rolled back?
Answer: Yes, if the changes are not committed, they can be rolled back.

Question 45:
How is the DELETE statement different from the TRUNCATE command?
Answer: DELETE is a DML command and can be rolled back, while TRUNCATE is a DDL command that removes all rows and cannot be rolled back.

Transactions: COMMIT, ROLLBACK, SAVEPOINT

Question 46:
What is a transaction in SQL?
Answer: A transaction is a sequence of operations performed as a single unit of work. It ensures data integrity by following ACID properties.

Question 47:
What does the COMMIT statement do?
Answer: The COMMIT statement permanently saves all changes made during the current transaction.

Question 48:
What does the ROLLBACK statement do?
Answer: The ROLLBACK statement undoes all changes made during the current transaction.

Question 49:
What is the purpose of the SAVEPOINT command?
Answer: SAVEPOINT creates a marker within a transaction to which changes can be rolled back.

Question 50:
Give an example of using SAVEPOINT.
Answer:

SAVEPOINT savepoint1;
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
ROLLBACK TO savepoint1;

---

Chapter 4 — Data Definition Language (DDL)

CREATE, ALTER, DROP

Question 51:
What is the purpose of the CREATE statement?
Answer: The CREATE statement is used to create database objects such as tables, views, indexes, or sequences.

Question 52:
What is the syntax for creating a table?
Answer:

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

Question 53:
How do you create a table with a primary key?
Answer:

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    salary NUMBER
);

Question 54:
What is the purpose of the ALTER statement?
Answer: The ALTER statement is used to modify the structure of an existing database object.

Question 55:
What are common uses of the ALTER statement?
Answer: Common uses include adding, modifying, or dropping columns in a table.

ALTER TABLE employees ADD phone_number VARCHAR2(15);
ALTER TABLE employees MODIFY salary NUMBER(10, 2);
ALTER TABLE employees DROP COLUMN phone_number;

Question 56:
What does the DROP statement do?
Answer: The DROP statement is used to remove a database object permanently.

DROP TABLE employees;

Question 57:
Can you recover a table after using the DROP statement?
Answer: Once a table is dropped, it cannot be recovered unless the database has flashback features enabled.

Question 58:
What is the difference between DROP and TRUNCATE?
Answer: DROP removes the table and its structure, while TRUNCATE removes only the data but keeps the structure intact.

Question 59:
Can the ALTER statement be used to rename a table?
Answer: Yes, you can rename a table using the RENAME command:

ALTER TABLE old_table_name RENAME TO new_table_name;

Question 60:
What happens when you drop a table with a foreign key dependency?
Answer: You must first drop or disable the dependent foreign key constraints before dropping the table.

Data Types and Constraints

Question 61:
What are the commonly used data types in Oracle SQL?
Answer: Common data types include:

• CHAR(size): Fixed-length character data.
• VARCHAR2(size): Variable-length character data.
• NUMBER(p,s): Numeric data with precision and scale.
• DATE: Date and time values.
• CLOB/BLOB: Large character or binary data.

Question 62:
What is a constraint in SQL?
Answer: Constraints enforce rules on table columns to ensure data integrity.

Question 63:
What are the types of constraints in Oracle SQL?
Answer: Types include:

• PRIMARY KEY: Ensures unique, non-null values.
• FOREIGN KEY: Maintains referential integrity.
• UNIQUE: Ensures all values in a column are unique.
• NOT NULL: Prevents null values in a column.
• CHECK: Ensures data satisfies specific conditions.

Question 64:
How do you add a NOT NULL constraint to a column?
Answer:

ALTER TABLE employees MODIFY name VARCHAR2(50) NOT NULL;

Question 65:
Can a column have multiple constraints?
Answer: Yes, for example, a column can have both NOT NULL and UNIQUE constraints.

Tables, Views, and Sequences

Question 66:
What is a table in Oracle SQL?
Answer: A table is a database object used to store data in rows and columns.

Question 67:
What is a view in Oracle SQL?
Answer: A view is a virtual table based on the result of a SELECT query. It does not store data but provides a way to simplify complex queries.

Question 68:
What is the syntax for creating a view?
Answer:

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

Question 69:
Can views be updated?
Answer: Yes, but only if the view is based on a single table and does not use certain features like aggregations or DISTINCT.

Question 70:
What is a sequence in Oracle SQL?
Answer: A sequence generates unique numeric values, typically used for primary keys.

Question 71:
How do you create a sequence?
Answer:

CREATE SEQUENCE seq_name
START WITH 1
INCREMENT BY 1;

Question 72:
How do you use a sequence to insert values?
Answer:

INSERT INTO employees (employee_id, name)
VALUES (seq_name.NEXTVAL, 'John Doe');

Question 73:
Can a sequence have a maximum value?
Answer: Yes, you can define a maximum value using the MAXVALUE option.

CREATE SEQUENCE seq_name MAXVALUE 1000;

Question 74:
What is the difference between CURRVAL and NEXTVAL?
Answer: CURRVAL returns the current value of a sequence, while NEXTVAL increments the sequence and returns the new value.

Question 75:
How do you drop a sequence?
Answer:

DROP SEQUENCE seq_name;

---

Chapter 5 — Functions and Operators

Single-Row Functions (String, Numeric, Date)

Question 76:
What are single-row functions in Oracle SQL?
Answer: Single-row functions operate on a single row of data and return a single result for each row.

Question 77:
What is the purpose of the UPPER function?
Answer: The UPPER function converts all characters in a string to uppercase.

SELECT UPPER(name) FROM employees;

Question 78:
What is the LOWER function used for?
Answer: The LOWER function converts all characters in a string to lowercase.

SELECT LOWER(name) FROM employees;

Question 79:
How do you concatenate two strings in Oracle SQL?
Answer: You can use the || operator to concatenate strings.

SELECT first_name || ' ' || last_name AS full_name FROM employees;

Question 80:
What is the LENGTH function?
Answer: The LENGTH function returns the number of characters in a string.

SELECT LENGTH(name) FROM employees;

Question 81:
What is the ROUND function used for in numeric data?
Answer: The ROUND function is used to round numeric values to a specified number of decimal places.

SELECT ROUND(salary, 2) FROM employees;

Question 82:
What is the MOD function?
Answer: The MOD function returns the remainder of a division.

SELECT MOD(10, 3) FROM dual;  -- Returns 1

Question 83:
How do you get the current date and time in Oracle SQL?
Answer: The SYSDATE function returns the current date and time.

SELECT SYSDATE FROM dual;

Question 84:
What is the TO_CHAR function used for?
Answer: The TO_CHAR function is used to convert dates or numbers to a formatted string.

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;

Question 85:
What is the TO_DATE function?
Answer: The TO_DATE function is used to convert a string to a date value.

SELECT TO_DATE('2024-12-01', 'YYYY-MM-DD') FROM dual;

Aggregate Functions (SUM, AVG, COUNT)

Question 86:
What is an aggregate function in SQL?
Answer: An aggregate function performs a calculation on a set of values and returns a single result.

Question 87:
What does the SUM function do?
Answer: The SUM function calculates the total of a numeric column.

SELECT SUM(salary) FROM employees;

Question 88:
What is the AVG function used for?
Answer: The AVG function calculates the average value of a numeric column.

SELECT AVG(salary) FROM employees;

Question 89:
How does the COUNT function work?
Answer: The COUNT function returns the number of rows that match a specified condition.

SELECT COUNT(*) FROM employees WHERE department_id = 10;

Question 90:
What is the difference between COUNT(*) and COUNT(column_name)?
Answer: COUNT(*) counts all rows, including those with NULL values, while COUNT(column_name) counts non-NULL values in a specified column.

Question 91:
What is the MIN function used for?
Answer: The MIN function returns the smallest value in a column.

SELECT MIN(salary) FROM employees;

Question 92:
What is the MAX function used for?
Answer: The MAX function returns the largest value in a column.

SELECT MAX(salary) FROM employees;

Question 93:
Can aggregate functions be used with GROUP BY?
Answer: Yes, aggregate functions are often used with GROUP BY to perform calculations on each group of rows.

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

NULL Handling

Question 94:
What is NULL in SQL?
Answer: NULL represents a missing or unknown value in a database.

Question 95:
How do you check for NULL values in a column?
Answer: You can use the IS NULL or IS NOT NULL condition.

SELECT * FROM employees WHERE salary IS NULL;

Question 96:
What is the COALESCE function?
Answer: The COALESCE function returns the first non-NULL value in a list of expressions.

SELECT COALESCE(phone_number, 'N/A') FROM employees;

Question 97:
What is the NVL function used for?
Answer: The NVL function replaces NULL with a specified value.

SELECT NVL(salary, 0) FROM employees;

Question 98:
What is the NULLIF function?
Answer: The NULLIF function returns NULL if two expressions are equal, otherwise it returns the first expression.

SELECT NULLIF(salary, 0) FROM employees;

Question 99:
How does the ISNULL function differ from NVL?
Answer: The ISNULL function is a SQL Server function, while NVL is used in Oracle. Both functions handle NULL values by replacing them with a specified value.

Question 100:
What is the purpose of the CASE expression?
Answer: The CASE expression allows conditional logic to be applied in SQL queries, similar to an IF-THEN-ELSE statement.

SELECT name, 
       CASE 
           WHEN salary > 5000 THEN 'High'
           WHEN salary BETWEEN 3000 AND 5000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_level
FROM employees;

---

These are 100 essential interview questions and answers for beginners learning Oracle SQL.