Top 50 Oracle SQL Developer Interview Questions and Answers by IT Trainings Institute
Introduction
So, let’s dive into this comprehensive collection of Oracle SQL Developer Technical Interview Questions and Answers, carefully categorized by IT Trainings Institute to support your interview preparation journey:
Oracle SQL Developer Interview Questions and Answers for Freshers
1. What is Oracle SQL Developer?
Answer:
Oracle SQL Developer is a free, integrated development environment (IDE) provided by Oracle for working with SQL, PL/SQL, databases, and reports. It helps in:
Writing and running SQL queries
Managing database objects
Debugging PL/SQL code
Importing/exporting data
Running reports and scripts
2. What are the features of Oracle SQL Developer?
Answer:
Key features include:
SQL worksheet for running queries
Database browser to view tables, views, indexes, etc.
PL/SQL editor with debugging tools
Data import/export from Excel, CSV, etc.
ER diagrams generation
Version control integration
3. What is the difference between SQL and PL/SQL?
| SQL | PL/SQL |
|---|---|
| Structured Query Language | Procedural Language extension for SQL |
| Executes one statement at a time | Executes a block of code (procedure/function) |
| Mainly used for data manipulation | Used for business logic, loops, conditions |
| No error handling | Has built-in error handling (EXCEPTION) |
4. How do you connect to a database in Oracle SQL Developer?
Answer:
Open SQL Developer.
Click the green + (New Connection) icon.
Enter:
Connection Name
Username & Password
Hostname, Port (default: 1521)
SID or Service Name
Click Test to check connection.
Click Connect to open the session.
5. What is a Primary Key?
Answer:
A Primary Key is a column or combination of columns that uniquely identifies each row in a table. It:
Cannot be NULL
Must be unique
Only one primary key allowed per table
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
name VARCHAR2(100)
);
Learn via our Course
Level Up Your Database Skills with Expert Oracle SQL Developer Training in Chandigarh & Mohali!
6. What is a Foreign Key?
Answer:
A Foreign Key is a column that creates a link between two tables. It refers to the primary key of another table to enforce referential integrity.
7. What is a JOIN? List types of joins.
Answer:
A JOIN is used to combine rows from two or more tables based on a related column.
Types of JOINS:
INNER JOIN – Matching rows only
LEFT JOIN – All rows from the left table, matching from right
RIGHT JOIN – All rows from the right table
FULL JOIN – All rows from both tables
CROSS JOIN – Cartesian product
8. What are Views in Oracle?
Answer:
A View is a virtual table based on the result of a query. It does not store data itself.
CREATE VIEW emp_view AS
SELECT emp_id, name
FROM employees
WHERE status = 'ACTIVE';
9. What is a Cursor?
Answer:
A Cursor is a pointer to the result set of a SQL query. It is used in PL/SQL to process rows one at a time.
Types:
Implicit Cursor – Automatically used by Oracle
Explicit Cursor – Declared and controlled by the programmer
10. What is a sequence in Oracle?
Answer:
A Sequence is used to generate unique numeric values, typically for primary keys.
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;
11. What is NULL in SQL?
Answer:
NULL represents the absence of a value, an unknown value, or an inapplicable value. It’s important to remember that NULL is not the same as zero (0) or an empty string (”). When you perform comparisons involving NULL, you must use special operators like IS NULL or IS NOT NULL, rather than standard comparison operators (=, !=).
To find employees whose commission is not yet set:
```sql
SELECT employee_id, first_name
FROM employees
WHERE commission_pct IS NULL;
```
12. What is the difference between DELETE, TRUNCATE, and DROP?
Answer:
These commands are all used to remove data or objects, but they operate at different levels and have distinct characteristics:
- DELETE: This is a DML (Data Manipulation Language) command used to remove specific rows from a table based on a WHERE clause. If no WHERE clause is specified, all rows are deleted. DELETE operations are recorded in the undo segments, can be rolled back, and fire triggers. It’s generally slower for large tables because it logs each row deletion.
DELETE FROM employees WHERE employee_id = 100; -- Deletes specific row
DELETE FROM employees; -- Deletes all rows, but logs each one
- TRUNCATE: This is a DDL (Data Definition Language) command that removes all rows from a table quickly by deallocating the space occupied by the table data. TRUNCATE operations cannot be rolled back and do not fire triggers. It’s much faster than DELETE for removing all rows from a table.
TRUNCATE TABLE employees; -- Deletes all rows rapidly and cannot be undone
- DROP: This is a DDL command used to remove an entire database object (like a table, index, or view) from the database. When you DROP a table, its structure and all its data are permanently removed. DROP operations cannot be rolled back.
DROP TABLE employees; -- Permanently removes the employees table
13. What is an INDEX in Oracle?
Answer:
An INDEX is a schema object that can significantly speed up the retrieval of rows from a table. It’s similar to an index in a book, allowing the database to quickly locate specific data without scanning the entire table. Indexes are created on one or more columns of a table. While they improve query performance, they can slightly slow down DML operations (INSERT, UPDATE, DELETE) because the index itself also needs to be updated.
```sql
CREATE INDEX idx_employee_lastname
ON employees (last_name); -- Creates an index on the last_name column
```
14. What is a Subquery?
Answer:
A Subquery (also known as an inner query or nested query) is a SELECT statement embedded within another SQL query. The inner query executes first, and its result is then used by the outer query. Subqueries can be used in SELECT, FROM, WHERE, and HAVING clauses.
To find employees who earn more than the average salary:
```sql
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees); -- Subquery finds average salary
```
15. What is a Stored Procedure?
Answer:
A Stored Procedure is a named PL/SQL block of code that performs a specific task. It is compiled and stored in the database, offering several advantages: reusability, improved performance (as it’s pre-compiled), and reduced network traffic (only the procedure call is sent, not the entire SQL code). Procedures can accept input parameters and return output parameters.
```sql
CREATE OR REPLACE PROCEDURE update_employee_salary (
p_employee_id IN NUMBER,
p_new_salary IN NUMBER
)
AS
BEGIN
UPDATE employees
SET salary = p_new_salary
WHERE employee_id = p_employee_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error updating salary.');
END;
/
```
16. What is a Function in PL/SQL?
Answer:
A Function in PL/SQL is similar to a stored procedure but has a key difference: it must return a single value. Functions are typically used to perform calculations and can be called directly within SQL queries (e.g., in the SELECT list or WHERE clause).
```sql
CREATE OR REPLACE FUNCTION get_annual_salary (
p_employee_id IN NUMBER
)
RETURN NUMBER
IS
v_salary NUMBER;
BEGIN
SELECT salary * 12 INTO v_salary
FROM employees
WHERE employee_id = p_employee_id;
RETURN v_salary;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
/
```
17. What is a Trigger?
Answer:
A Trigger is a special type of PL/SQL block that automatically executes (or “fires”) in response to a specific event on a table or view. These events are typically DML operations (INSERT, UPDATE, DELETE) but can also be DDL events or database system events. Triggers are often used for auditing, enforcing complex business rules, or maintaining data integrity.
```sql
CREATE OR REPLACE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
-- Automatically set hire date to current date if not provided
IF :NEW.hire_date IS NULL THEN
:NEW.hire_date := SYSDATE;
END IF;
END;
/
```
18. What is a Synonym?
Answer:
A Synonym is an alias or alternative name for a database object (such as a table, view, sequence, procedure, or function). It provides a way to simplify object access, especially in multi-schema environments, by allowing users to refer to an object without needing to specify its owner or schema name. Synonyms can be private (visible only to the creator) or public (visible to all users).
```sql
-- Create a public synonym for the employees table
CREATE PUBLIC SYNONYM emp_data FOR hr.employees;
-- Now users can query it without specifying the schema 'hr'
SELECT * FROM emp_data;
```
19. What is a Sequence in Oracle? How is it used?
Answer:
A Sequence is a database object used to generate unique numeric values, typically for primary key columns. They ensure that each new record gets a unique ID without requiring manual management or potential concurrency issues.
```sql
-- Create a sequence starting at 100, incrementing by 1
CREATE SEQUENCE employee_id_seq
START WITH 100
INCREMENT BY 1
NOCACHE; -- NOCACHE means values are not pre-allocated in memory, ensuring strict ordering
-- Use the sequence to insert a new employee
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (employee_id_seq.NEXTVAL, 'Jane', 'Doe', 'JDOE', SYSDATE, 'IT_PROG', 6000);
-- Get the current value for the session (useful after an insert)
SELECT employee_id_seq.CURRVAL FROM dual;
```
20. Explain COMMIT and ROLLBACK in SQL.
Answer:
These are TCL (Transaction Control Language) commands used to manage database transactions:
- COMMIT: This command saves all pending changes made during a transaction (like INSERT, UPDATE, DELETE statements) permanently to the database. Once committed, the changes are visible to other users and cannot be undone by ROLLBACK.
- ROLLBACK: This command undoes all pending changes made since the last COMMIT or ROLLBACK, reverting the database to its state before the transaction began. This is useful for discarding incorrect operations or handling errors.
```sql
-- Start a transaction implicitly
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (1001, 'Test', 'User', 'TUSER', SYSDATE, 'SA_REP', 5000);
-- The change is only temporary so far
ROLLBACK; -- Undoes the insert, the row is gone
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (1002, 'Another', 'User', 'AUSER', SYSDATE, 'SA_REP', 5500);
COMMIT; -- Saves the insert permanently
```
21. What is DDL, DML, DCL, and TCL?
Answer:
These are categories of SQL commands:
- DDL (Data Definition Language): Used for defining and modifying the structure of database objects. Commands include CREATE, ALTER, DROP, TRUNCATE, RENAME.
- DML (Data Manipulation Language): Used for manipulating data within database objects. Commands include SELECT, INSERT, UPDATE, DELETE, MERGE.
- DCL (Data Control Language): Used for controlling access to data and managing user permissions. Commands include GRANT, REVOKE.
- TCL (Transaction Control Language): Used for managing transactions and ensuring data consistency. Commands include COMMIT, ROLLBACK, SAVEPOINT.
22. How do you handle errors in PL/SQL?
Answer:
Errors in PL/SQL are handled using the EXCEPTION block. This block is placed at the end of a PL/SQL block (anonymous block, procedure, function, or trigger) and allows you to define specific actions to take when an error occurs. You can handle predefined exceptions (like NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX) or create user-defined exceptions.
```sql
DECLARE
v_employee_name VARCHAR2(100);
BEGIN
SELECT first_name INTO v_employee_name
FROM employees
WHERE employee_id = 9999; -- This ID likely doesn't exist
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Error: Employee ID not found.');
WHEN OTHERS THEN -- Catch any other unexpected errors
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/
```
23. What is ROWNUM in Oracle?
Answer:
ROWNUM is a pseudocolumn that Oracle automatically assigns to each row retrieved by a query, indicating the order in which that row was fetched. It’s crucial to understand that ROWNUM is assigned before the ORDER BY clause is applied. This means if you use ROWNUM with ORDER BY, you often need to use a subquery to ensure correct results.
24. What is the purpose of the GROUP BY clause?
Answer:
The GROUP BY clause is used with aggregate functions (COUNT(), SUM(), AVG(), MIN(), MAX()) to group rows that have the same values in specified columns into a set of summary rows. Instead of returning a result for each individual row, it returns a single summary row for each group.
To find the number of employees in each department:
```sql
SELECT department_id, COUNT(employee_id) AS total_employees
FROM employees
GROUP BY department_id;
```
25. What is the difference between WHERE and HAVING clauses?
Answer:
Both clauses are used for filtering, but they operate at different stages of query processing:
- WHERE clause: Filters individual rows before any grouping occurs. It operates on columns that are not part of an aggregate function.
- HAVING clause: Filters groups after the GROUP BY clause has been applied and aggregate functions have been calculated. It operates on the results of aggregate functions.
```sql
-- WHERE clause: Filter rows before grouping
SELECT department_id, COUNT(employee_id)
FROM employees
WHERE salary > 5000 -- Filters individual employees with salary > 5000
GROUP BY department_id;
-- HAVING clause: Filter groups after aggregation
SELECT department_id, COUNT(employee_id) AS total_employees
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 5; -- Filters groups where the count of employees is > 5
```
26. What is a VIEW in Oracle? What are its advantages?
Answer:
A VIEW is a virtual table based on the result-set of a SQL query. It does not store data itself; instead, it’s essentially a stored query that can be treated like a table. When you query a view, Oracle executes the underlying query to retrieve the data.
Advantages:
- Security: You can grant users access to specific columns or rows through a view without giving them direct access to the underlying tables, thus restricting sensitive data.
- Simplicity: Views can simplify complex queries by pre-joining multiple tables or applying filters. Users can query a single view instead of writing complex joins repeatedly.
- Data Independence: If the underlying table structure changes (e.g., adding a column), the view definition might need to be updated, but applications using the view might not be affected if the view’s output remains consistent.
```sql
CREATE VIEW active_employees_view AS
SELECT employee_id, first_name, last_name, email, phone_number
FROM employees
WHERE status = 'ACTIVE';
-- Now, you can query the view as if it were a table
SELECT first_name, last_name FROM active_employees_view;
```
27. What is a SAVEPOINT?
Answer:
A SAVEPOINT is a marker within a transaction that allows you to roll back to a specific point without rolling back the entire transaction. This is useful when you want to undo only a portion of the operations performed since the last COMMIT or the beginning of the transaction.
```sql
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (1003, 'Mark', 'Smith', 'MSMITH', SYSDATE, 'SA_REP', 4000);
SAVEPOINT sp_after_insert_mark; -- Set a savepoint here
UPDATE employees SET salary = 4500 WHERE employee_id = 1003;
ROLLBACK TO SAVEPOINT sp_after_insert_mark; -- Undoes only the UPDATE, the INSERT remains
-- If you then do a COMMIT, the INSERT will be saved, but the UPDATE will be undone.
COMMIT;
```
28. What are IN and OUT parameters in PL/SQL procedures/functions?
Answer:
These specify the mode of parameters in PL/SQL subprograms (procedures and functions):
- IN parameter: Used to pass values into the procedure or function. It acts as a constant within the subprogram, meaning its value cannot be modified. It’s the default mode if not specified.
- OUT parameter: Used to return values out of the procedure or function to the calling environment. Its initial value inside the subprogram is NULL, and any value assigned to it will be passed back to the caller.
- IN OUT parameter: Used to pass values into the subprogram and also return modified values out of it. The parameter’s initial value is passed in, and its final value (after modification) is passed back.
CREATE OR REPLACE PROCEDURE calculate_bonus (
p_employee_id IN NUMBER, -- IN parameter: input ID
p_bonus OUT NUMBER, -- OUT parameter: calculated bonus will be returned
p_message IN OUT VARCHAR2 -- IN OUT: message passed in, can be modified and returned
)
AS
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary
FROM employees
WHERE employee_id = p_employee_id;
p_bonus := v_salary * 0.10; -- Calculate 10% bonus
p_message := p_message || ' - Bonus calculated for employee ' || p_employee_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_bonus := 0;
p_message := 'Error: Employee not found. ' || p_message;
END;
/
29. What is a CASE statement in SQL? Give an example.
Answer:
A CASE statement in SQL allows you to implement conditional logic within a query. It evaluates a list of conditions and returns a value based on the first condition that evaluates to true. If no conditions are met, it returns the value in the ELSE clause (if provided), otherwise it returns NULL. It’s similar to if-then-else constructs in procedural programming languages.
To classify employees based on their salary into different "salary grades":
SELECT
employee_id,
first_name,
salary,
CASE
WHEN salary < 5000 THEN 'Grade A'
WHEN salary BETWEEN 5000 AND 10000 THEN 'Grade B'
WHEN salary > 10000 THEN 'Grade C'
ELSE 'Unknown Grade' -- Optional ELSE clause
END AS salary_grade
FROM
employees;
30. How do you create a table in Oracle SQL Developer?
Answer:
You can create a table in Oracle SQL Developer using two primary methods:
- Using SQL Worksheet (SQL Commands): You write and execute the CREATE TABLE DDL statement directly in the SQL Worksheet. This is the most common and flexible method for developers.
CREATE TABLE departments (
department_id NUMBER(4) PRIMARY KEY,
department_name VARCHAR2(30) NOT NULL,
location_id NUMBER(4)
);
2. Using the GUI (Graphical User Interface):
- In the Connections navigator, expand your database connection.
- Right-click on the “Tables” node.
- Select “New Table…”.
- A wizard will appear where you can enter the table name, add columns, specify data types, constraints (like Primary Key, Not Null), and other properties using a graphical interface.
- Once all details are entered, click the “OK” or “Apply” button to generate and execute the CREATE TABLE statement in the background.
Oracle SQL Developer Interview Questions and Answers for Experienced
31. Core SQL and Relational Database Concepts
Answer:
Both store variable-length character strings. VARCHAR2 is Oracle’s proprietary data type and is generally preferred as VARCHAR is reserved for future ANSI standard compatibility and might have different behavior. VARCHAR2 does not store trailing spaces at the end of a string, avoiding potential unexpected results when comparing strings, making it more storage efficient.Both store variable-length character strings. VARCHAR2 is Oracle’s proprietary data type and is generally preferred as VARCHAR is reserved for future ANSI standard compatibility and might have different behavior. VARCHAR2 does not store trailing spaces at the end of a string, avoiding potential unexpected results when comparing strings, making it more storage efficient.
32.Explain the different types of JOIN operations in SQL and when you would use each.
Answer:
INNER JOIN: Returns rows
when there is a match in both tables based on the join condition. Used when you only want to see related data from both tables.
- Left-outer: Returns all rows from the left table, and the matching rows from the right table. If there’s no match in the right table, values are returned for right table columns. Used when you want to retrieve all records from one table and their corresponding matches from another.
- Right outer: Returns all rows from the right table, and the matching rows from the left table. If there’s no match in the left table, Null values are returned for left table columns. Used less frequently, but similar to LEFT Join with tables swapped.
33.What are UNION, UNION ALL, INTERSECT, and MINUS operators? Explain their differences.
Answer:
These are set operators used to combine the results of two or more select statements.
UNION: Combines result sets and removes duplicate rows.
Union all: Combines result sets and retains all rows, including duplicates. Generally faster than as it doesn’t involve duplicate removal.
- Interepect: Returns only the rows that are present in both result sets.
- In some databases): Returns rows from the first result set that are not present in the second result set.
34.Explain the concept of Normalization in database design. What are its different forms (1NF, 2NF, 3NF, BCNF) and why is it important?
Answer:
- Normalization is the process of organizing the columns and tables of a relational database to minimize data redundancy and improve data integrity.
- 1NF (First Normal Form): Atomic values, no repeating groups.
- 2NF (Second Normal Form): 1NF + all non-key attributes are fully functionally dependent on the primary key.
- 3NF (Third Normal Form): 2NF + no transitive dependencies (non-key attributes are not dependent on other non-key attributes).
- BCNF (Boyce-Codd Normal Form): A stricter version of 3NF, where every determinant is a candidate key.
- Importance: Reduces data redundancy, improves data integrity, makes the database more flexible and easier to maintain, and optimizes data insertion, update, and deletion.
35.What is Denormalization? When and why would you consider using it?
Answer:
- Denormalization is the process of intentionally introducing redundancy into a database by combining tables or adding duplicate data.
- When to use: Primarily for performance optimization in read-heavy applications (e.g., data warehousing, reporting) where joining multiple normalized tables frequently becomes a bottleneck.
- Why: Can significantly improve query performance by reducing the need for complex joins, but it increases data redundancy and can make data maintenance more challenging.
36.Explain the WHERE clause versus the HAVING clause.
- Answer:
- Where clause: Filters individual rows before they are grouped. It cannot filter on aggregate functions.
- Having clause: Filters groups of rows after they have been grouped by the clause. It can filter on aggregate functions.
- Example:SELECT department, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY department HAVING COUNT(*) > 5;
37.What are Subqueries? What are correlated subqueries and how do they differ from regular subqueries?
Answer:
- Subquery (Nested Query): A query embedded within another SQL query. It executes first and its result is used by the outer query.
- Correlated Subquery: A subquery that references columns from the outer query. It executes once for each row processed by the outer query. This makes them less efficient than non-correlated subqueries but sometimes necessary for complex filtering or calculations based on related data. Regular subqueries execute independently of the outer query.
38.Explain ROWNUM in Oracle. How does it work and what are its limitations?
- Answer:
- ROUMN is a pseudo-column in Oracle that assigns a sequential number to each row returned by a query, starting with 1.
- How it works: It’s assigned before the ODER BY clause is applied. This means if you order by a column, the ROUMN might not reflect the desired ranking based on that order.
- Limitations:
- You cannot directly use
- SELECT department, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY department HAVING COUNT(*) > 5; effectively without a subquery, because
- ROUMN is assigned dynamically as rows are fetched. For example, will never return any rows because the first row is evaluated, then the second (ROWNUM=2), and so on. If isn’t 1, it’s skipped.
39.What is PL/SQL? What are its advantages over plain SQL?
- Answer:
- PL/SQL (Procedural Language/SQL) is Oracle’s procedural extension to SQL. It combines the data manipulation capabilities of SQL with the procedural programming features of a language like Ada or Pascal.
- Advantages:
- Procedural Capabilities: Allows for variables, conditional statements
- (IF/THEN/ELSE), loops (LOOP, WHILE, FOR), ,and error handling.
- Improved Performance: PL/SQL blocks are sent to the database as a single unit, reducing network traffic and improving performance by minimizing context switching between SQL and the application.
- Modularity and Reusability: Supports procedures, functions, packages, and triggers for better code organization, reusability, and maintainability.
40. Explain the concept of CURSOR in PL/SQL. Differentiate between Implicit and Explicit Cursors.
- Answer:
- A Cursor is a pointer or a handle to the context area where Oracle stores information about a SQL statement and its result set. It allows row-by-row processing of the result set.
- Implicit Cursor: Automatically created and managed by Oracle for all DML statements (IF/THEN/ELSE), loops (LOOP, WHILE, FOR), statements that return a single row. You don’t explicitly declare or open them, but you can access their attributes.
41.What are PACKAGES in PL/SQL? What are their benefits?
- Answer:
- A Pacakage is a schema object that groups logically related PL/SQL types, variables, cursors, procedures, and functions. It consists of two parts:
- Package Specification: Declares the public items (procedures, functions, variables, etc.) that are accessible from outside the package.
- Package Body: Contains the implementation details of the items declared in the specification, as well as private items.
- Benefits:
- Modularity and Encapsulation: Organizes code, hides implementation details.
- Reusability: Package elements can be called from different PL/SQL blocks.
42.Differentiate between RANK(), DENSE_RANK(), ROW_NUMBER(), and NTILE() analytic functions.
Answer:
- Row Number(): Assigns a unique, sequential integer to each row within its partition, starting from 1. If rows have the same value, they get different row numbers.
Rank: Assigns the same rank to rows with identical values, and then skips subsequent rank numbers. For example, if two rows are ranked 1, the next distinct rank will be 3 (skipping 2).- Dense Rank : Assigns the same rank to rows with identical values but does not skip rank numbers. If two rows are ranked 1, the next distinct rank will be 2.
- Nettile(N)
: Divides the rows in an ordered partition into.
43.Explain the difference between WHERE and HAVING clauses.
- Answer:
- A materialized view (MV) is a database object that contains the results of a query. Unlike a regular view, which is a virtual table, an MV stores the data physically on disk. It’s essentially a pre-computed or cached version of a query result.
- Benefits:
- Improved Query Performance: Queries against an MV are often much faster than queries against the underlying base tables, especially for complex aggregations or joins, as the data is pre-calculated.
- Reduced Resource Consumption: Less CPU and I/O are consumed on the base tables for frequently executed queries.
- Data Summarization/Warehousing: Ideal for data warehousing environments where historical or aggregated data is frequently accessed.
44.What are the different types of SQL Joins? Provide scenarios for when to use each.
Answer:
- Where clause filters individual rows before any grouping or aggregation occurs. It operates on columns in the base tables.
- Having clause filters groups after aggregation (i.e., after the clause). It operates on aggregated results.
- Example: To find departments with an average salary greater than $50,000, you’d use Having . To find employees with a salary greater than $50,000, you’d use.
45.Explain the concept of UNION vs. UNION ALL.
Answer:
Both combine the result sets of two or more Select statements.
- Union: Combines results and removes duplicate rows from the final result set. It implicitly performs a Distinct operation. This can be slower due to the sorting and de-duplication.
- Union All
: Combines results and retains all rows, including duplicates. It is generally faster than Union because it doesn’t need to perform a operation. Use Union All when you know there are no duplicates or when you want to include duplicates.
46.What are Common Table Expressions (CTEs) and when would you use them?
Answer:
CTEs are temporary, named result sets that you can reference within a single SQL statement (e.g.,
SELECT, INSERT, UPDATE, DELETE). They are defined using the WITH clause.
- Advantages:
- Readability: Break down complex, multi-step queries into smaller, more manageable logical blocks.
- Recursion: Enable recursive queries (e.g., traversing hierarchical data like organizational charts or bill of materials).
- Reusability: A CTE can be referenced multiple times within the same query, avoiding repetitive code.
47.How do you handle NULL values in SQL? Discuss functions related to NULLs.
- Answer:
- A materialized view (MV) is a database object that contains the results of a query. Unlike a regular view, which is a virtual table, an MV stores the data physically on disk. It’s essentially a pre-computed or cached version of a query result.
- Benefits:
- Improved Query Performance: Queries against an MV are often much faster than queries against the underlying base tables, especially for complex aggregations or joins, as the data is pre-calculated.
- Reduced Resource Consumption: Less CPU and I/O are consumed on the base tables for frequently executed queries.
- Data Summarization/Warehousing: Ideal for data warehousing environments where historical or aggregated data is frequently accesse
48.What is a materialized view? What are its benefits and drawbacks?
Answer:
ACID is an acronym that defines a set of properties that guarantee valid transactions.
- Atomicity: A transaction is treated as a single, indivisible unit of work. Either all of its operations are completed successfully, or none of them are. If any part of the transaction fails, the entire transaction is rolled back.
- Consistency: A transaction brings the database from one valid state to another valid state. It ensures that all data integrity rules (constraints, triggers, etc.) are maintained throughout the transaction.
- Isolation: Concurrent transactions execute independently of each other. The intermediate state of one transaction is not visible to other transactions until the first transaction is committed. This prevents interference and ensures data accuracy in multi-user environments.
- Atomicity: A transaction is treated as a single, indivisible unit of work. Either all of its operations are completed successfully, or none of them are. If any part of the transaction fails, the entire transaction is rolled back.
49.Explain the ACID properties in the context of database transactions.
Answer:
ACID is an acronym that defines a set of properties that guarantee valid transactions.
- Atomicity: A transaction is treated as a single, indivisible unit of work. Either all of its operations are completed successfully, or none of them are. If any part of the transaction fails, the entire transaction is rolled back.
- Consistency: A transaction brings the database from one valid state to another valid state. It ensures that all data integrity rules (constraints, triggers, etc.) are maintained throughout the transaction.
50.What is the difference between SQL and PL/SQL? What are the advantages of PL/SQL?
Answer:
SQL (Structured Query Language): A declarative language used for managing and manipulating data in relational databases. It primarily focuses on what data to retrieve or modify.
- PL/SQL (Procedural Language/SQL): An extension of SQL that adds procedural programming capabilities (variables, control structures, error handling, etc.). It focuses on how to process the data.
- Advantages of PL/SQL:
- Procedural Capabilities: Allows for complex logic, conditional statements loops and modular programming.
- Error Handling: Provides robust exception handling mechanisms