Infosys Oracle Developer Interview Questions and Selection Process

Infosys Oracle Developer Interviews Generally Focus On SQL, PL/SQL, Oracle Database Concepts, Performance Tuning, Stored Procedures, Functions, Packages, Triggers, Cursors, And Database Programming. Candidates May Also Be Asked Coding-Based SQL Problems, Query Optimization Questions, Data Integrity Concepts, And Real-Time Database Scenarios. Recent Candidate Experiences Indicate That Technical Interviews Can Include SQL, DBMS, Coding, Projects, And Core Programming Concepts, Although The Exact Selection Process Can Vary By Role And Hiring Drive.The Selection Process May Include An Online Assessment Or Screening Test, Followed By A Technical Interview And An HR Or Managerial Discussion, Depending On The Position And Hiring Route. For Specialized Roles, Candidates May Face More Advanced Coding And Technical Questions.

1. What Is Oracle Database?

Ans:

Oracle Database Is A Relational Database Management System Used To Store, Manage, And Retrieve Structured Data. It Provides SQL For Data Querying And PL/SQL For Developing Database-Side Programs. Oracle Supports Tables, Views, Indexes, Sequences, Synonyms, Procedures, Functions, And Triggers. It Also Provides Security, Transaction Management, Backup, Recovery, And High Availability Features. Oracle Databases Are Widely Used In Enterprise Applications Because They Can Handle Large Volumes Of Data. An Oracle Developer Commonly Works With SQL, PL/SQL, Database Objects, Performance, And Data Management.

2. What Is SQL?

Ans:

SQL Stands For Structured Query Language And Is Used To Communicate With Relational Databases. It Allows Developers To Retrieve, Insert, Update, And Delete Data Stored In Database Tables. SQL Also Supports Operations Such As Filtering, Sorting, Grouping, Joining, And Aggregating Data. Commands Such As SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, And DROP Are Commonly Used. Oracle Developers Frequently Use SQL To Develop Reports, Validate Data, And Support Application Requirements. Strong SQL Knowledge Is One Of The Most Important Skills Required For An Oracle Developer Role.

3. What Is PL/SQL?

Ans:

PL/SQL Stands For Procedural Language Extension To SQL And Is Oracle’s Procedural Programming Language. It Combines SQL Statements With Programming Features Such As Variables, Conditions, Loops, And Exception Handling. PL/SQL Is Commonly Used To Develop Procedures, Functions, Packages, Triggers, And Database Programs. It Allows Multiple SQL Statements To Be Executed Within A Single Program Block. This Can Improve Application Performance By Reducing Unnecessary Communication Between Applications And Databases. Oracle Developers Use PL/SQL Extensively For Implementing Complex Business Logic Inside The Database

4. What Is The Difference Between SQL And PL/SQL?

Ans:

SQL Is Mainly Used To Perform Data Definition, Data Manipulation, And Data Retrieval Operations. PL/SQL Extends SQL By Adding Procedural Programming Features Such As Loops, Conditions, Variables, And Exceptions. SQL Generally Executes One Statement At A Time, While PL/SQL Can Execute Multiple Statements Within A Block. PL/SQL Is Useful When Complex Business Logic Needs To Be Implemented At The Database Level. SQL Is Commonly Used For Queries, While PL/SQL Is Commonly Used For Procedures, Functions, Packages, And Triggers. Both Technologies Are Important For Oracle Developers And Are Frequently Used Together.

5. What Is A Primary Key?

Ans:

A Primary Key Is A Column Or Combination Of Columns That Uniquely Identifies Each Row In A Table. A Primary Key Does Not Allow Duplicate Values In The Key Columns. Primary Key Columns Also Cannot Contain NULL Values. A Table Can Have Only One Primary Key Constraint, Although It Can Contain Multiple Columns. Primary Keys Help Maintain Entity Integrity And Provide Reliable Row Identification. Oracle Developers Frequently Use Primary Keys When Designing Tables And Creating Relationships Between Tables.

6. What Is A Foreign Key?

Ans:

A Foreign Key Is A Column Or Set Of Columns That References A Primary Key Or Unique Key In Another Table. It Is Used To Establish A Relationship Between Two Tables. Foreign Keys Help Maintain Referential Integrity Between Related Records. For Example, An Employee Table Can Have A Department ID That References A Department Table. Oracle Can Prevent Invalid Child Records When Appropriate Foreign Key Constraints Are Defined. Foreign Keys Are Important For Designing Consistent And Well-Structured Relational Databases.

7. What Is A Unique Constraint?

Ans:

A Unique Constraint Ensures That Duplicate Values Are Not Stored In The Constrained Column Or Column Combination. Unlike A Primary Key, A Table Can Have Multiple Unique Constraints. A Unique Constraint Can Allow NULL Values According To Oracle’s Handling Of NULL And Constraint Semantics. It Is Commonly Applied To Values Such As Email Addresses, Employee Codes, Or Account Numbers. Unique Constraints Help Prevent Duplicate Business Values From Entering A Database. Oracle Developers Use Them To Enforce Data Quality And Business Rules At The Database Level.

8. What Is A NOT NULL Constraint?

Ans:

A NOT NULL Constraint Ensures That A Column Must Contain A Value For Every Inserted Or Updated Row. It Prevents NULL Values From Being Stored In The Constrained Column. The Constraint Is Useful For Mandatory Business Information Such As Employee Names Or Department IDs. It Helps Maintain Data Completeness And Prevents Missing Required Values. NOT NULL Constraints Can Be Defined During Table Creation Or Added Later. Oracle Developers Commonly Use Them Along With Primary Key, Foreign Key, And Other Constraints.

9. What Is A CHECK Constraint?

Ans:

• A CHECK Constraint Restricts Values In A Column According To A Specified Logical Condition. For Example, A Salary Column Can Be Restricted To Values Greater Than Zero. 
• A CHECK Constraint Helps Ensure That Data Follows Defined Business Rules. The Database Evaluates The Condition During Insert And Update Operations. 
• Invalid Values Are Rejected When They Violate The Constraint. Oracle Developers Use CHECK Constraints To Improve Data Accuracy And Reduce Invalid Records.

10. What Is A DEFAULT Value?

Ans:

A DEFAULT Value Is Automatically Assigned To A Column When An Insert Statement Does Not Provide A Value For That Column. It Can Be Used For Values Such As Status, Creation Date, Or Default Category. For Example, A Status Column Can Have A Default Value Of ACTIVE. Defaults Help Reduce The Need For Applications To Explicitly Supply Common Values. They Also Provide Consistency When New Records Are Created. Oracle Developers Commonly Use DEFAULT Values For Columns With Predictable Initial Values.

11. What Is A View?

Ans:

A View Is A Logical Table Based On A SQL Query And Does Not Normally Store The Query Result As Physical Data. It Can Present Selected Columns And Rows From One Or More Underlying Tables. Views Can Simplify Complex Queries And Provide An Additional Layer Of Data Security. Users Can Be Given Access To A View Without Giving Direct Access To Certain Underlying Tables. Views Are Frequently Used For Reporting And Presenting Business-Friendly Data Structures. Oracle Developers Create Views To Simplify Data Access And Encapsulate Frequently Used Queries.

12. What Is A Materialized View?

Ans:

A Materialized View Stores The Result Of A Query Physically In The Database. Unlike A Normal View, It Contains Persisted Data That Can Be Refreshed When Required. Materialized Views Can Improve Performance For Complex Queries And Large Reporting Workloads. They Are Particularly Useful When Aggregated Data Needs To Be Accessed Frequently. Refreshes Can Be Configured According To Application And Data Freshness Requirements. Oracle Developers Use Materialized Views To Optimize Reporting And Data Warehouse Workloads.

13. What Is An Index?

Ans:

• An Index Is A Database Object Used To Improve The Speed Of Data Retrieval From A Table. It Provides A More Efficient Access Path For Queries That Search Or Sort Using Indexed Columns. 
• Indexes Can Be Created On One Column Or Multiple Columns Depending On Query Requirements. Although Indexes Improve Reads, They Can Increase Storage Requirements And DML Maintenance Costs. 
• Too Many Or Poorly Designed Indexes Can Reduce Insert, Update, And Delete Performance. Oracle Developers Analyze Query Patterns Before Creating Appropriate Indexes.

14. What Is A Composite Index?¹

Ans:

A Composite Index Is An Index Created On Two Or More Columns Of A Table. It Is Useful When Queries Frequently Filter Or Join Data Using Multiple Columns Together. The Order Of Columns In A Composite Index Is Important For Efficient Query Access. The Leading Column Or Columns Can Influence Whether The Optimizer Can Use The Index Effectively. Composite Indexes Should Be Designed According To Actual Query Patterns And Data Distribution. Oracle Developers Use Them Carefully To Improve Performance Without Creating Unnecessary Indexes.

15. What Is A Sequence In Oracle?

Ans:

A Sequence Is A Database Object Used To Generate Numeric Values Automatically. It Is Commonly Used To Generate Unique Identifiers For Table Records. Sequences Can Be Created With Options Such As START WITH, INCREMENT BY, CACHE, And NOCACHE. The NEXTVAL Pseudocolumn Retrieves The Next Available Sequence Value. The CURRVAL Pseudocolumn Returns The Current Sequence Value In A Session After NEXTVAL Has Been Referenced. Oracle Developers Frequently Use Sequences For Generating Primary Key Values.

16. What Is A Synonym?

Ans:

A Synonym Is A Database Object That Provides An Alternative Name For Another Database Object. It Can Be Created For Tables, Views, Sequences, Procedures, Functions, And Other Supported Objects. Synonyms Can Simplify SQL Statements By Hiding Schema Names Or Long Object Names. Private Synonyms Are Available To Their Owner, While Public Synonyms Can Be Accessible More Broadly. They Can Make Application Queries Easier To Maintain In Certain Database Environments. Oracle Developers Use Synonyms When Object Access And Naming Requirements Make Them Appropriate.

17. What Is A Stored Procedure?

Ans:

A Stored Procedure Is A Named PL/SQL Program Unit Stored And Executed Inside The Oracle Database. It Can Accept Parameters And Perform Multiple SQL And PL/SQL Operations. Procedures Are Commonly Used To Implement Business Processes And Database Operations. They Can Contain Variables, Conditional Statements, Loops, Cursors, And Exception Handling. Stored Procedures Can Reduce Repeated Application-Side Database Logic. Oracle Developers Use Procedures When A Reusable Database Operation Needs To Be Encapsulated.

18. What Is A Function In Oracle?

Ans:

A Function Is A PL/SQL Program Unit That Performs An Operation And Returns A Value. It Can Accept Input Parameters And Use SQL Or PL/SQL Statements To Produce The Result. Functions Are Often Used For Calculations, Data Transformation, Validation, And Reusable Business Logic. A Function Can Sometimes Be Called From SQL When It Meets Oracle’s SQL Execution Requirements. Functions Improve Reusability By Centralizing Repeated Logic In One Database Object. Oracle Developers Use Functions When Returning A Calculated Or Derived Value Is Required.

19. What Is The Difference Between Procedure And Function?

Ans:

Aspect	Procedure	Function
Purpose	Performs A Specific Operation	Performs An Operation And Returns A Value
Return Value	Can Use OUT Or IN OUT Parameters	Must Return A Value Using RETURN
Usage	Commonly Used For Database Operations	Commonly Used For Calculations And Returning Results

20. What Is A Package?

Ans:

• A Package Is A PL/SQL Object That Groups Related Procedures, Functions, Variables, Cursors, And Other Program Elements. It Contains A Specification That Defines Public Elements And A Body That Contains Implementation Details. 
• Packages Improve Code Organization, Reusability, Maintainability, And Encapsulation. They Can Also Maintain Package-Level Variables During A Database Session. 
• Grouping Related Logic Into Packages Makes Large Database Applications Easier To Manage. Oracle Developers Commonly Use Packages For Enterprise-Level PL/SQL Development.

21. What Is A Trigger?

Ans:

A Trigger Is A PL/SQL Program Unit That Automatically Executes When A Specified Database Event Occurs. Triggers Can Respond To Events Such As INSERT, UPDATE, DELETE, Or Certain DDL Operations. They Can Be Defined At Statement Level Or Row Level Depending On The Requirement. Triggers Are Sometimes Used For Auditing, Validation, Or Automatically Maintaining Related Data. However, Excessive Trigger Usage Can Make Application Behavior Difficult To Understand And Maintain. Oracle Developers Should Use Triggers Carefully And Prefer Clear Application Or Database Logic Where Appropriate.

22. What Is A Cursor?

Ans:

A Cursor Is A Mechanism Used To Process Query Results In PL/SQL. An Implicit Cursor Is Automatically Created By Oracle For Certain SQL Statements. An Explicit Cursor Is Declared And Controlled By The Developer For Processing Multiple Rows. Explicit Cursors Can Be Opened, Fetched, And Closed Within PL/SQL Programs. Cursor FOR Loops Can Simplify Row-By-Row Processing Without Manually Managing Cursor Operations. Oracle Developers Use Cursors When Set-Based SQL Alone Cannot Conveniently Handle A Required Operation.

23. What Is An Implicit Cursor?

Ans:

An Implicit Cursor Is Automatically Managed By Oracle Whenever A SQL Statement Is Executed In PL/SQL. Developers Do Not Need To Explicitly Declare, Open, Fetch, Or Close It. Attributes Such As SQL%ROWCOUNT And SQL%FOUND Provide Information About The Most Recent SQL Operation. SQL%ROWCOUNT Can Be Used To Determine How Many Rows Were Affected By A DML Statement. Implicit Cursors Are Convenient For Simple SQL Operations. Oracle Developers Commonly Use Them For INSERT, UPDATE, DELETE, And SELECT INTO Statements.

24. What Is An Explicit Cursor?

Ans:

• An Explicit Cursor Is Declared By The Developer To Process A Query That Returns Multiple Rows. It Provides More Control Over Opening, Fetching, Processing, And Closing The Query Result. 
• Explicit Cursors Can Be Declared In The Declaration Section Of A PL/SQL Block Or Program Unit. Rows Can Be Processed One At A Time Through FETCH Operations. 
• Cursor FOR Loops Provide A Simpler Alternative For Many Explicit Cursor Use Cases. They Should Be Used Carefully Because Excessive Row-By-Row Processing Can Reduce Performance.

25. What Is Exception Handling In PL/SQL?

Ans:

Exception Handling Allows PL/SQL Programs To Respond To Runtime Errors In A Controlled Manner. Exceptions Can Be Predefined Oracle Exceptions, User-Defined Exceptions, Or Internally Raised Errors. The EXCEPTION Section Is Used To Define Actions For Handling Specific Errors. Examples Include NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, And OTHERS. Proper Exception Handling Helps Prevent Unexpected Program Termination And Supports Meaningful Error Management. Oracle Developers Use It To Make Database Programs More Reliable And Maintainable.

26. What Is NO_DATA_FOUND?

Ans:

NO_DATA_FOUND Is A Predefined PL/SQL Exception That Occurs When SELECT INTO Finds No Rows. A SELECT INTO Statement Normally Expects Exactly One Row Unless Special Handling Is Used. When No Record Matches The Query Condition, Oracle Raises NO_DATA_FOUND. The Exception Can Be Handled In The EXCEPTION Section Of A PL/SQL Block. Developers Should Decide Whether Missing Data Is Expected Or Represents An Actual Business Error. Proper Handling Prevents Unexpected Failures In Database Applications.

27. What Is TOO_MANY_ROWS?

Ans:

TOO_MANY_ROWS Occurs When A SELECT INTO Statement Returns More Than One Row. A Standard SELECT INTO Operation Expects Exactly One Row. If Multiple Rows Are Returned, Oracle Raises The TOO_MANY_ROWS Exception. The Problem Can Often Be Prevented By Using More Specific Conditions Or Appropriate Aggregation. An Explicit Cursor Can Be Used When Multiple Rows Need To Be Processed. Oracle Developers Should Understand Expected Cardinality Before Using SELECT INTO.

28. What Is A Transaction?

Ans:

• A Transaction Is A Logical Unit Of Database Work That Contains One Or More SQL Operations. A Transaction Can Be Committed To Make Changes Permanent Or Rolled Back To Undo Uncommitted Changes. 
• Transactions Help Maintain Data Consistency When Multiple Related Operations Must Be Treated Together. Oracle Uses Transaction Management To Support Reliable Database Processing. 
• COMMIT, ROLLBACK, And SAVEPOINT Are Important Transaction Control Statements. Oracle Developers Must Manage Transactions Carefully To Avoid Inconsistent Or Unintended Data Changes.

29. What Is COMMIT?

Ans:

COMMIT Permanently Makes The Changes Performed By The Current Transaction Available According To Oracle Transaction Rules. It Ends The Current Transaction And Releases Relevant Transaction Resources And Locks. After A Commit, The Changes Generally Cannot Be Reversed Using ROLLBACK. COMMIT Should Be Used Carefully When Multiple Database Operations Belong To The Same Logical Transaction. Frequent Commits During Large Processing Can Affect Performance And Transaction Management. Oracle Developers Should Commit At Appropriate Business Transaction Boundaries.

30. What Is ROLLBACK?

Ans:

ROLLBACK Undoes Uncommitted Changes Made During The Current Transaction. It Can Be Used When An Error Occurs Or When Changes Should Not Be Saved. ROLLBACK Helps Restore Data To The State At The Start Of The Transaction Or To A SAVEPOINT. Once Changes Have Been Committed, A Normal ROLLBACK Cannot Undo Them. It Is Commonly Used In Exception Handling When A Transaction Cannot Complete Successfully. Oracle Developers Use ROLLBACK To Maintain Transaction Consistency And Prevent Partial Updates.

31. What Is SAVEPOINT?

Ans:

A SAVEPOINT Marks A Specific Point Within A Transaction To Which A Partial Rollback Can Be Performed. It Allows Developers To Undo Some Changes Without Rolling Back The Entire Transaction. The SAVEPOINT Statement Creates A Named Point Within The Current Transaction. ROLLBACK TO SAVEPOINT Can Return The Transaction To That Earlier Point. This Feature Can Be Useful When A Large Transaction Contains Multiple Logical Processing Steps. Oracle Developers Use Savepoints Carefully To Control Complex Transaction Processing.

32. What Is Normalization?

Ans:

Normalization Is A Database Design Technique Used To Reduce Data Redundancy And Improve Data Integrity. It Organizes Data Into Related Tables According To Defined Dependencies And Rules. Common Normal Forms Include First Normal Form, Second Normal Form, And Third Normal Form. Normalization Helps Prevent Insert, Update, And Delete Anomalies. Highly Normalized Designs Can Require More Joins During Query Processing. Oracle Developers Apply Appropriate Normalization Based On Application And Performance Requirements.

33. What Is Denormalization?

Ans:

Denormalization Is The Intentional Introduction Of Redundancy Into A Database Design To Improve Query Performance Or Simplify Access. It Can Reduce The Number Of Joins Required For Frequently Executed Queries. Denormalization May Increase Storage Requirements And Data Maintenance Complexity. It Is Often Considered In Reporting Systems, Data Warehouses, And High-Read Workloads. Any Denormalized Data Must Be Carefully Maintained To Prevent Inconsistencies. Oracle Developers Balance Data Integrity And Query Performance Before Choosing Denormalization.

34. What Is A JOIN?

Ans:

• A JOIN Combines Data From Two Or More Tables Based On A Related Column Or Join Condition. 
• Joins Allow Related Information Stored In Separate Tables To Be Retrieved Together. Common Types Include INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, And FULL OUTER JOIN. T
• he Join Condition Determines How Rows From Different Tables Are Matched. Proper Join Conditions Are Important To Avoid Incorrect Results Or Unnecessary Row Multiplication. Oracle Developers Use Joins Extensively For Business Reports And Application Queries.

35. What Is An INNER JOIN?

Ans:

An INNER JOIN Returns Only The Rows That Have Matching Values In Both Joined Tables. Rows Without A Matching Record In Either Table Are Excluded From The Result. It Is Commonly Used When Only Related Records Are Required. For Example, Employees Can Be Joined With Departments To Return Employees Having Valid Department Matches. Join Conditions Should Usually Use Appropriate Keys Or Related Business Columns. Oracle Developers Frequently Use INNER JOIN For Relational Data Retrieval.

36. What Is A LEFT OUTER JOIN?

Ans:

A LEFT OUTER JOIN Returns All Rows From The Left Table And Matching Rows From The Right Table. When No Matching Right-Side Row Exists, Oracle Returns NULL Values For The Right-Side Columns. It Is Useful When All Records From The Primary Table Must Be Preserved. For Example, All Departments Can Be Listed Even If Some Departments Have No Employees. Correct Placement Of Filtering Conditions Is Important When Using Outer Joins. Oracle Developers Commonly Use LEFT JOIN For Reporting And Missing-Relationship Analysis.

37. What Is A SELF JOIN?

Ans:

A SELF JOIN Is A Join Where A Table Is Joined With Itself. It Is Useful When Rows Within The Same Table Have A Relationship With Other Rows In That Table. An Employee Table Can Be Self-Joined To Represent Employee And Manager Relationships. Table Aliases Are Required To Distinguish The Different Logical Instances Of The Same Table. The Join Condition Connects The Related Rows Within The Table. Oracle Developers Use Self Joins For Hierarchical Or Parent-Child Relationships.

38. What Is A CROSS JOIN?

Ans:

A CROSS JOIN Produces The Cartesian Product Of Two Tables. Every Row From The First Table Is Combined With Every Row From The Second Table. If One Table Contains Five Rows And Another Contains Four Rows, The Result Can Contain Twenty Rows. Cross Joins Can Be Useful For Generating Combinations Or Certain Analytical Datasets. They Can Also Create Very Large Results When Used Unintentionally. Oracle Developers Should Use CROSS JOIN Only When A Cartesian Product Is Actually Required.

39. What Is A Subquery?

Ans:

A Subquery Is A Query Nested Inside Another SQL Statement. It Can Appear In SELECT, FROM, WHERE, HAVING, Or Other Supported SQL Clauses. Subqueries Can Be Used To Compare Values, Filter Records, Or Generate Intermediate Result Sets. They Can Be Correlated Or Non-Correlated Depending On Their Relationship With The Outer Query. Complex Subqueries Should Be Reviewed For Readability And Performance. Oracle Developers Use Subqueries To Solve Many Multi-Step Data Retrieval Problems.

40. What Is A Correlated Subquery?

Ans:

• A Correlated Subquery References A Column From The Outer Query. Because Of This Dependency, The Subquery Is Conceptually Evaluated In Relation To Each Outer Row. It Can Be Useful For Comparing Each Record With Related Data. 
• For Example, It Can Identify Employees Whose Salary Is Greater Than The Average Salary In Their Department. Correlated Subqueries Can Sometimes Be More Expensive Than Equivalent Join Or Analytic Solutions. 
• Oracle Developers Should Consider Performance When Using Correlated Subqueries On Large Tables.

41. What Is GROUP BY?

Ans:

GROUP BY Combines Rows With The Same Values In One Or More Columns Into Groups. It Is Commonly Used With Aggregate Functions Such As SUM, COUNT, AVG, MIN, And MAX. For Example, Sales Can Be Grouped By Department To Calculate Department-Level Revenue. Every Selected Non-Aggregated Column Generally Needs To Be Included In The GROUP BY Clause. GROUP BY Helps Convert Detailed Transaction Data Into Summary Information. Oracle Developers Frequently Use It For Reports, Dashboards, And Analytical Queries.

42. What Is HAVING?

Ans:

HAVING Is Used To Filter Groups Created By The GROUP BY Clause. WHERE Filters Individual Rows Before Grouping, While HAVING Filters The Resulting Groups. For Example, HAVING COUNT(*) > 10 Can Return Departments Having More Than Ten Employees. HAVING Is Commonly Used With Aggregate Functions. Using WHERE For Row-Level Conditions Before GROUP BY Can Often Reduce Processing Work. Oracle Developers Use WHERE And HAVING Together When Both Row And Group Filtering Are Required.

43. What Is The Difference Between WHERE And HAVING?<table

Ans:

Aspect	WHERE	HAVING
Purpose	Filters Individual Rows Before Grouping.	Filters Groups After Grouping.
Usage	Commonly Used With Individual Column Conditions.	Commonly Used With Aggregate Functions Such As COUNT, SUM, And AVG.
Execution	Applied Before GROUP BY And Aggregation.	Applied After GROUP BY And Aggregation.

44. What Is DISTINCT?

Ans:

DISTINCT Removes Duplicate Rows From The Result Of A SELECT Query. It Returns Only Unique Combinations Of The Selected Columns. For Example, SELECT DISTINCT DEPARTMENT_ID Can Return Each Department ID Only Once. DISTINCT Can Be Useful For Data Exploration, Validation, And Generating Unique Lists. However, Using DISTINCT Unnecessarily Can Add Sorting Or Other Processing Overhead. Oracle Developers Should Use DISTINCT When Duplicate Elimination Is Actually Required.

45. What Is UNION?

Ans:

UNION Combines The Results Of Two Compatible SELECT Statements And Removes Duplicate Rows. Both Queries Must Return The Same Number Of Columns With Compatible Data Types In Corresponding Positions. The Result Contains Distinct Rows From Both Query Results. UNION Can Be Useful When Data From Different Queries Needs To Be Combined Into One Result Set. UNION ALL Can Be Used When Duplicate Rows Should Be Preserved And Extra Duplicate Elimination Is Unnecessary. Oracle Developers Choose Between UNION And UNION ALL Based On Business And Performance Requirements

46. What Is UNION ALL?

Ans:

UNION ALL Combines The Results Of Two Or More Compatible SELECT Statements Without Removing Duplicate Rows. It Usually Requires Less Processing Than UNION Because Duplicate Elimination Is Not Performed. It Is Useful When Every Row From Each Query Result Must Be Preserved. The SELECT Statements Must Have Compatible Numbers And Types Of Columns. UNION ALL Is Often Preferred In Data Processing When Duplicate Removal Is Not Required. Oracle Developers Use It Frequently In ETL, Reporting, And Data Consolidation Queries.

47. What Is CASE Expression?

Ans:

• CASE Is A SQL Expression Used To Implement Conditional Logic Within A Query. It Can Return Different Values Depending On Whether Specified Conditions Are Satisfied. 
• CASE Can Be Used In SELECT, ORDER BY, GROUP BY, And Other SQL Contexts Where Expressions Are Supported. For Example, Salary Values Can Be Classified Into Low, Medium, And High Categories. 
• It Helps Transform Raw Data Into Business-Friendly Results Without Changing Stored Data. Oracle Developers Frequently Use CASE For Reporting And Data Transformation.

48. What Is NVL?

Ans:

NVL Is An Oracle SQL Function Used To Replace A NULL Value With A Specified Alternative Value. It Accepts Two Arguments And Returns The Second Argument When The First Argument Is NULL. For Example, NVL(COMMISSION, 0) Can Display Zero When Commission Is NULL. NVL Is Commonly Used In Reports And Calculations Where Missing Values Need A Default Representation. The Replacement Value Should Be Compatible With The Data Type Of The First Expression. Oracle Developers Use NVL To Handle NULL Values In Oracle-Specific SQL Statements.

49. What Is COALESCE?

Ans:

COALESCE Returns The First Non-NULL Expression From A List Of Expressions. It Provides A Flexible Way To Select An Available Value When Multiple Possible Sources Exist. For Example, COALESCE(MOBILE, PHONE, EMAIL) Can Return The First Available Contact Value. It Can Be Used In SELECT Statements, Calculations, And Data Transformation Logic. COALESCE Is Based On Standard SQL And Is More General Than Oracle’s Two-Argument NVL Function. Oracle Developers Use It When Multiple Potential Values Need To Be Evaluated For NULL Handling.

50. What Is NULL In Oracle?

Ans:

NULL Represents The Absence Of A Value Or An Unknown Value In Oracle Database. NULL Is Not The Same As Zero, An Empty String In General SQL Semantics, Or A Normal Text Value. Arithmetic Operations Involving NULL Usually Produce NULL Results. Special Functions And Conditions Such As NVL And IS NULL Are Used To Handle NULL Values. The Equality Operator Cannot Be Used To Test Whether A Value Is NULL. Oracle Developers Must Handle NULL Carefully To Avoid Incorrect Query Results And Calculations.

51. What Is The Difference Between DELETE, TRUNCATE, And DROP?

Ans:

• DELETE Removes Selected Rows From A Table And Can Use A WHERE Clause To Control Which Rows Are Deleted. TRUNCATE Removes All Rows From A Table And Is A DDL Operation With Different Transaction Behavior From DELETE. DROP Removes The Database Object Itself, Such As A Table, Along With Its Definition And Associated Data. 
• DELETE Can Be Rolled Back Before A Commit Under Appropriate Transaction Conditions, While TRUNCATE Has Different DDL Semantics. 
• TRUNCATE Is Generally Faster For Removing All Rows From A Table Than Row-By-Row DELETE. Oracle Developers Choose The Command According To Whether Rows Or The Entire Database Object Must Be Removed.

52. What Is DDL?

Ans:

DDL Stands For Data Definition Language And Is Used To Define Or Modify Database Structures. Common DDL Statements Include CREATE, ALTER, DROP, TRUNCATE, And RENAME. DDL Commands Primarily Operate On Database Objects Such As Tables, Views, Indexes, And Sequences. Oracle Treats DDL With Transaction Semantics That Include Implicit Commits Around DDL Operations. DDL Is Mainly Used During Database Design, Deployment, And Structural Changes. Oracle Developers Need Strong DDL Knowledge To Create And Maintain Database Objects.

53. What Is DML?

Ans:

DML Stands For Data Manipulation Language And Is Used To Add, Modify, Or Remove Data. Common DML Statements Include INSERT, UPDATE, DELETE, And MERGE. DML Operations Participate In Transactions And Can Normally Be Committed Or Rolled Back. Developers Use DML To Maintain Records Stored In Database Tables. Careful WHERE Conditions Are Important For UPDATE And DELETE Operations. Oracle Developers Frequently Use DML In Applications, Procedures, ETL Processes, And Data Maintenance Tasks.

54. What Is DCL?

Ans:

DCL Stands For Data Control Language And Is Used To Manage Database Access And Privileges. Common DCL Statements Include GRANT And REVOKE. GRANT Provides Specific Privileges To Users Or Roles. REVOKE Removes Previously Granted Privileges. DCL Helps Control Which Users Can Access Or Modify Database Objects. Oracle Developers Work With Database Administrators To Ensure Appropriate Access Control And Security.

55. What Is TCL?

Ans:

TCL Stands For Transaction Control Language And Is Used To Manage Database Transactions. Important TCL Commands Include COMMIT, ROLLBACK, And SAVEPOINT. COMMIT Makes Appropriate Transaction Changes Permanent, While ROLLBACK Reverses Uncommitted Changes. SAVEPOINT Allows A Partial Rollback To A Previously Defined Point. TCL Is Important When Multiple Database Operations Must Be Controlled As A Logical Unit. Oracle Developers Use Transaction Control To Maintain Consistency And Reliability In Database Applications.

56. What Is MERGE In Oracle?

Ans:

MERGE Is A SQL Statement Used To Perform Conditional INSERT And UPDATE Operations In One Statement. It Compares Source Data With Target Data Using A Specified Matching Condition. When A Match Exists, The MERGE Statement Can Update The Target Record. When No Match Exists, It Can Insert A New Record Into The Target Table. MERGE Is Commonly Used In ETL Processes, Data Synchronization, And Upsert Operations. Oracle Developers Use MERGE To Simplify Certain Data Integration And Maintenance Tasks.

57. What Is An Upsert?

Ans:

• An Upsert Is A Data Operation That Updates An Existing Record Or Inserts A New Record When No Matching Record Exists. 
• It Combines The Concept Of UPDATE And INSERT Into One Business Operation. Oracle’s MERGE Statement Is A Common Way To Implement Upsert Logic. Upserts Are Frequently Used During Data Loading And Synchronization Processes. 
• A Reliable Matching Condition Is Required To Determine Whether A Record Already Exists. Oracle Developers Use Upserts To Keep Target Tables Synchronized With Source Data.

58. What Is An Analytic Function?

Ans:

An Analytic Function Performs A Calculation Across A Set Of Rows Related To The Current Row Without Collapsing The Rows Into One Result Per Group. Common Analytic Functions Include ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, SUM, And AVG. The OVER Clause Defines The Window Or Partition Used For The Calculation. Analytic Functions Are Extremely Useful For Ranking, Running Totals, Comparisons, And Trend Analysis. They Often Provide More Efficient And Readable Solutions Than Complex Self Joins. Oracle Developers Frequently Use Analytic Functions In Advanced Reporting And Data Analysis.

59. What Is ROW_NUMBER?

Ans:

ROW_NUMBER Assigns A Unique Sequential Number To Each Row Within A Result Set Or Partition. The Ordering Of Rows Is Defined Using The ORDER BY Clause Inside The OVER Clause. PARTITION BY Can Be Used To Restart Numbering For Each Logical Group. It Is Commonly Used To Identify The First Or Latest Record Within Each Group. For Example, ROW_NUMBER Can Help Select The Highest-Paid Employee From Each Department. Oracle Developers Frequently Use ROW_NUMBER In Deduplication And Top-N Query Requirements.

60. What Is RANK?

Ans:

RANK Assigns Ranking Numbers To Rows Based On Their Ordered Values. Rows With Equal Values Receive The Same Rank. When Ties Occur, The Next Rank Can Have Gaps Because Multiple Rows Share The Same Ranking Position. For Example, If Two Employees Share Rank One, The Next Employee Can Receive Rank Three. RANK Is Useful For Competition-Style Ranking And Comparative Analysis. Oracle Developers Use It When Tied Values Should Receive The Same Position.

61. What Is DENSE_RANK?

Ans:

DENSE_RANK Assigns The Same Rank To Equal Values Without Leaving Gaps After Ties. For Example, Two Employees Can Share Rank One And The Next Employee Receives Rank Two. It Is Implemented As An Analytic Function Using The OVER Clause. DENSE_RANK Is Useful When Ranking Values While Maintaining Continuous Ranking Numbers. It Can Be Used To Identify Top Salaries, Sales Results, Or Other Business Metrics. Oracle Developers Choose DENSE_RANK When Ranking Gaps Are Not Required After Ties.

62. What Is LAG Function?

Ans:

LAG Is An Analytic Function Used To Access A Value From A Previous Row In The Result Set. It Allows A Current Row To Be Compared With An Earlier Row Without A Self Join. For Example, Monthly Sales Can Be Compared With The Sales Value From The Previous Month. The ORDER BY Clause Determines The Logical Sequence Of Rows. LAG Is Useful For Calculating Differences, Growth Rates, And Historical Comparisons. Oracle Developers Frequently Use LAG In Time-Series And Business Reporting Queries.

63. What Is LEAD Function?

Ans:

• LEAD Is An Analytic Function Used To Access A Value From A Following Row In The Result Set. It Allows Current Data To Be Compared With A Future Row Without Creating A Self Join.
•  For Example, A Transaction Can Be Compared With The Next Transaction For The Same Customer. The ORDER BY Clause Determines The Logical Order Used To Identify The Following Row. 
• LEAD Is Useful For Forecasting Comparisons, Event Sequences, And Interval Calculations. Oracle Developers Use LEAD For Advanced Analytical Queries And Reporting.

64. What Is A CTE?

Ans:

A Common Table Expression, Or CTE, Is A Temporary Named Result Set Defined Using The WITH Clause. It Exists For The Duration Of The SQL Statement In Which It Is Defined. CTEs Can Make Complex Queries Easier To Read By Dividing Logic Into Logical Sections. They Can Also Support Recursive Querying In Appropriate Oracle Versions And Syntax. A CTE Can Be Referenced By The Main Query And Sometimes By Other CTEs In The Same Statement. Oracle Developers Use CTEs To Improve Query Organization And Maintainability

65. What Is The WITH Clause?

Ans:

The WITH Clause Allows A Query To Define One Or More Common Table Expressions BeforeMain SELECT Statement. It Helps Break Complex SQL Logic Into Smaller Named Query Components. Each CTE Can Represent An Intermediate Dataset Used By The Final Query. The Approach Can Improve Readability And Make Multi-Step Queries Easier To Maintain. The Optimizer Determines The Most Appropriate Execution Strategy For The Query. Oracle Developers Frequently Use WITH Clauses In Complex Reporting And Analytical SQL.

66. What Is Query Optimization?

Ans:

Query Optimization Is The Process Of Improving A SQL Statement So That It Uses Database Resources Efficiently. It Can Involve Better Joins, Appropriate Indexes, Efficient Filtering, And Reduced Unnecessary Data Processing. Oracle’s Cost-Based Optimizer Selects An Execution Plan Based On Available Statistics And Other Information. Developers Can Analyze Execution Plans To Identify Expensive Operations. Optimization Should Focus On Actual Performance Bottlenecks Rather Than Unnecessary Query Changes. Oracle Developers Need Optimization Skills For Maintaining Responsive Enterprise Applications.

67. What Is An Execution Plan?

Ans:

An Execution Plan Describes How Oracle Intends To Execute A SQL Statement. It Can Show Operations Such As Table Access, Index Scans, Joins, Sorting, And Aggregation. Developers Can Use EXPLAIN PLAN And Other Performance Tools To Investigate Query Behavior. The Plan Helps Identify Expensive Operations And Potential Optimization Opportunities. Actual Runtime Behavior Can Differ From An Estimated Plan, So Real Execution Statistics Can Also Be Important. Oracle Developers Use Execution Plans To Diagnose And Improve SQL Performance.

68. What Is A Full Table Scan?

Ans:

A Full Table Scan Reads The Rows Of A Table To Evaluate A Query Condition. It Can Be Appropriate When A Large Percentage Of The Table’s Rows Must Be Retrieved. For Small Tables, A Full Table Scan May Also Be More Efficient Than Using An Index. For Highly Selective Queries On Large Tables, An Appropriate Index May Provide A Better Access Path. The Correct Choice Depends On Data Volume, Selectivity, Statistics, And Query Requirements. Oracle Developers Analyze Execution Plans Before Assuming That A Full Table Scan Is Always Bad.

69. What Is Index Scan?

Ans:

• An Index Scan Allows Oracle To Access Data Through An Index Rather Than Reading The Entire Table Directly. Different Index Access Methods Can Be Chosen Depending On The Query And Index Structure. 
• Indexes Can Be Effective When A Query Selects A Relatively Small Portion Of A Large Table. The Optimizer Determines Whether An Index Provides A Cost-Effective Access Path.
•  Poorly Designed Indexes May Not Improve Performance And Can Increase DML Overhead. Oracle Developers Analyze Query Patterns And Execution Plans Before Creating Indexes.

70. What Is The Cost-Based Optimizer?

Ans:

The Cost-Based Optimizer, Commonly Called CBO, Chooses An Execution Plan Based On Estimated Resource Costs. It Considers Information Such As Table Statistics, Index Statistics, Data Distribution, And Query Structure. The Optimizer Compares Possible Execution Strategies And Selects A Plan It Estimates To Be Efficient. Accurate And Current Statistics Can Help The Optimizer Make Better Decisions. Developers Can Investigate Execution Plans When Queries Perform Poorly. Understanding CBO Is Important For Advanced Oracle SQL Performance Tuning.

71. What Are Database Statistics?

Ans:

Database Statistics Describe Characteristics Of Tables, Columns, Indexes, And Data Distribution. Oracle Uses These Statistics To Help The Optimizer Estimate Cardinality And Choose Execution Plans. Statistics Can Include Information Such As Row Counts, Data Distribution, And Index Characteristics. Stale Or Inaccurate Statistics Can Contribute To Poor Execution Plan Choices. Oracle Provides Mechanisms For Gathering And Maintaining Optimizer Statistics. Oracle Developers Should Understand Statistics When Investigating Unexpected Query Performance.

72. What Is Partitioning?

Ans:

Partitioning Divides A Large Table Or Index Into Smaller Manageable Pieces Called Partitions. Partitioning Can Improve Manageability And Performance For Certain Large-Data Workloads. Common Partitioning Methods Include Range, List, Hash, And Composite Partitioning. Partition Pruning Can Allow Oracle To Access Only Relevant Partitions For Some Queries. Partitioning Design Should Match Data Characteristics And Common Query Patterns. Oracle Developers Working With Large Enterprise Tables Often Need To Understand Partitioning Concepts.

73. What Is Range Partitioning?

Ans:

Range Partitioning Divides Data Into Partitions According To Ranges Of Values. Date Columns Are Commonly Used For Range Partitioning In Transaction And Historical Data. For Example, Data Can Be Partitioned By Month Or Year Based On A Transaction Date. Queries Filtering On The Partition Key May Benefit From Partition Pruning. Range Partitioning Can Also Simplify Management Of Historical Data. Oracle Developers Use It When Data Naturally Follows An Ordered Range-Based Pattern.

74. What Is Data Integrity?

Ans:

• Data Integrity Means Maintaining Accuracy, Consistency, And Reliability Of Data Stored In The Database. Constraints Such As PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, And NOT NULL Help Enforce Integrity. 
• Transactions Also Help Maintain Consistency When Multiple Related Operations Are Performed. Application Validation Can Complement Database-Level Integrity Rules. 
• Strong Integrity Prevents Invalid, Duplicate, Or Inconsistent Information From Entering The System. Oracle Developers Must Consider Data Integrity During Database Design And Application Development.

75. What Is Referential Integrity?

Ans:

Referential Integrity Ensures That Relationships Between Related Tables Remain Valid. A Foreign Key Constraint Is Commonly Used To Enforce Referential Integrity. It Can Prevent A Child Record From Referencing A Nonexistent Parent Record. It Can Also Control What Happens When Referenced Parent Data Is Updated Or Deleted. Referential Integrity Helps Maintain Reliable Relationships Across Database Tables. Oracle Developers Use Foreign Keys And Appropriate Constraint Rules To Maintain Relationship Consistency.

76. What Is Dynamic SQL?

Ans:

Dynamic SQL Is SQL That Is Constructed And Executed At Runtime Rather Than Being Fully Fixed In The Program Code. Oracle PL/SQL Provides EXECUTE IMMEDIATE For Many Dynamic SQL Requirements. Dynamic SQL Can Be Useful When Table Names, Conditions, Or SQL Structures Are Determined At Runtime. It Should Be Designed Carefully To Avoid SQL Injection And Other Security Problems. Bind Variables Should Be Used Whenever Possible For Dynamic Values. Oracle Developers Use Dynamic SQL When Static SQL Cannot Satisfy The Required Runtime Flexibility.

77. What Is EXECUTE IMMEDIATE?

Ans:

EXECUTE IMMEDIATE Is A PL/SQL Statement Used To Execute Dynamic SQL At Runtime. It Can Execute Dynamically Constructed DML, DDL, And Other Supported SQL Statements. Bind Variables Can Be Supplied Through The USING Clause For Dynamic Values. Dynamic SQL Requires Careful Construction To Prevent Syntax Errors And Security Vulnerabilities. It Is Particularly Useful When Object Names Or SQL Structures Cannot Be Known During Compilation. Oracle Developers Use EXECUTE IMMEDIATE For Flexible Database Operations That Require Runtime SQL Construction.

78. What Is SQL Injection?

Ans:

SQL Injection Is A Security Vulnerability That Occurs When Untrusted Input Is Improperly Included In SQL Statements. An Attacker May Manipulate Input To Change The Intended Meaning Of A Database Query. It Can Potentially Expose, Modify, Or Delete Unauthorized Data. Using Bind Variables And Proper Input Handling Is A Major Defense Against SQL Injection. Dynamic SQL Should Never Be Constructed By Blindly Concatenating Untrusted User Input. Oracle Developers Must Follow Secure Coding Practices When Building Database Applications.

79. What Are Bind Variables?

Ans:

• Bind Variables Are Placeholders Used To Supply Values To SQL Statements Without Embedding The Values Directly Into The SQL Text. They Can Improve Security By Reducing SQL Injection Risks In Appropriate Application Designs. 
• They Can Also Improve Performance By Allowing Oracle To Reuse SQL Statements More Effectively. Bind Variables Are Especially Important For Applications Executing Similar Queries With Different Input Values. 
• They Can Be Used With Static SQL And Dynamic SQL According To The Programming Context. Oracle Developers Commonly Use Bind Variables For Secure And Efficient Database Programming.

80. What Is Database Locking?

Ans:

Database Locking Controls Concurrent Access To Data When Multiple Sessions Attempt To Modify Or Access Related Records. Locks Help Prevent Conflicting Changes And Maintain Data Consistency. Oracle Uses Its Concurrency Control Mechanisms To Manage Transactions And Row-Level Changes. Long-Running Transactions Can Hold Locks And Potentially Cause Other Sessions To Wait. Developers Should Avoid Unnecessarily Long Transactions And Commit At Appropriate Boundaries. Oracle Developers Need Locking Knowledge To Diagnose Blocking And Concurrency Problems.

81. What Is A Deadlock?

Ans:

A Deadlock Occurs When Two Or More Transactions Wait For Resources Held By Each Other. For Example, One Transaction May Hold A Lock Needed By Another While Waiting For A Lock Held By That Transaction. Oracle Detects Deadlocks And Terminates One Of The Involved Statements Or Transactions To Break The Cycle. Proper Transaction Ordering Can Reduce The Risk Of Deadlocks. Shorter Transactions And Consistent Resource Access Patterns Can Also Help Prevent Them. Oracle Developers Should Analyze Application Logic When Deadlocks Occur Frequently.

82. What Is A Database Schema?

Ans:

A Database Schema Is A Logical Collection Of Database Objects Owned Or Associated With A Database User. Objects Can Include Tables, Views, Indexes, Procedures, Functions, Packages, And Other Structures. A Schema Provides A Namespace For Organizing And Accessing Database Objects. Different Schemas Can Contain Objects With The Same Names Without Direct Naming Conflicts. Permissions Control How Other Users Can Access Objects In A Schema. Oracle Developers Frequently Work With Multiple Schemas In Enterprise Database Environments.

83. What Is A Database Instance?

Ans:

• An Oracle Database Instance Consists Primarily Of Memory Structures And Background Processes That Manage Database Operations. The Instance Works With The Physical Database Files To Provide Database Services. 
• Memory Areas Such As The System Global Area Support SQL Processing And Data Management. Background Processes Perform Tasks Such As Writing Data, Managing Logs, And Recovery. 
• Understanding The Difference Between An Instance And The Database Files Is Important In Oracle Architecture. Oracle Developers May Encounter These Concepts When Working With Database Administrators And Production Systems.

84. What Is SGA?

Ans:

SGA Stands For System Global Area And Is A Shared Memory Area Used By An Oracle Instance. It Contains Various Memory Structures Required For Database Processing. Important Components Include The Database Buffer Cache, Shared Pool, And Redo Log Buffer. The Shared Pool Stores Information Such As Parsed SQL And Other Shared Metadata. The Database Buffer Cache Stores Copies Of Data Blocks Read From Data Files. Oracle Developers Should Understand SGA Basics When Investigating Database Performance And Architecture.

85. How Can The Second Highest Salary Be Found In Oracle?

Ans:

The Second Highest Salary Can Be Found Using A Subquery, DENSE_RANK, Or MAX With A Subquery. One Simple Approach Is To Select The Maximum Salary That Is Less Than The Overall Maximum Salary.

86. How Can Duplicate Records Be Identified In Oracle?

Ans:

Duplicate Records Can Be Identified By Grouping Records According To The Columns That Define Uniqueness. The COUNT Function Can Then Be Used To Find Groups Having More Than One Record.

87. How Can Duplicate Records Be Removed Using ROW_NUMBER?

Ans:

Duplicate Records Can Be Removed By Assigning A Sequential Number To Rows Within Each Duplicate Group. The ROW_NUMBER Analytic Function Can Identify The First Record As Number One And Additional Duplicate Records With Higher Numbers

88. How Can The Highest Salary In Each Department Be Found?

Ans:

The Highest Salary In Each Department Can Be Found Using The MAX Aggregate Function With GROUP BY. The Department ID Is Used To Create Separate Groups And MAX(SALARY) Returns The Highest Salary Within Each Group

89. How Can Employees Earning More Than The Average Salary Be Found?

Ans:

Employees Earning More Than The Average Salary Can Be Found By Comparing Each Employee’s Salary With The Overall Average Salary.

90. How Can The Top Three Salaries Be Found In Oracle?

Ans:

The Top Three Salaries Can Be Retrieved Using DENSE_RANK, RANK, Or FETCH Clauses Depending On The Requirement. DENSE_RANK Is Useful When Distinct Salary Values Are Required And Employees With The Same Salary Should Receive The Same Rank