TCS Full Stack Developer Interview Questions and Answers for Freshers

Oracle Interview Questions For Data Analysts Are Designed To Evaluate Knowledge Of SQL, Database Concepts, Data Analysis, And Problem-Solving Skills. These Interviews Commonly Cover Topics Such As Tables, Joins, Subqueries, Aggregate Functions, Window Functions, Views, Indexes, NULL Handling, And Data Cleaning. Candidates May Also Be Asked About Data Warehousing, ETL, Query Optimization, Data Validation, And Business Reporting. Practical SQL Problems Are Often Included To Test The Ability To Retrieve, Transform, And Analyze Data Efficiently. This Collection Of Oracle Interview Questions And Answers Helps Freshers And Experienced Data Analysts Prepare For Technical Interviews And Build Strong Oracle Database Skills.

1. What Is Oracle Database?

Ans:

Oracle Database Is A Relational Database Management System Used To Store, Manage, And Retrieve Structured Data Efficiently. It Supports SQL And PL/SQL For Querying, Manipulating, And Processing Data. Data Analysts Use Oracle To Extract Business Data, Perform Calculations, And Generate Analytical Reports. It Provides Features Such As Transactions, Security, Indexing, Data Integrity, Backup, And Recovery. Oracle Is Widely Used In Enterprise Applications, Data Warehousing, And Large-Scale Business Environments.

2. What Is SQL In Oracle?

Ans:

SQL Stands For Structured Query Language And Is Used To Communicate With Oracle Databases. It Allows Users To Retrieve, Insert, Update, And Delete Data Stored In Database Tables. SQL Also Supports Filtering, Sorting, Grouping, Joining, And Aggregating Data For Analysis. Data Analysts Use SQL Queries To Prepare Reports, Calculate Business Metrics, And Extract Useful Insights. Strong SQL Knowledge Is Essential For Performing Data Analysis Using Oracle..

3. What Is A Table In Oracle?

Ans:

A Table Is A Database Object Used To Store Structured Data In Rows And Columns. Each Column Represents A Specific Attribute Such As Customer Name, Salary, Department, Or Date. Each Row Represents An Individual Record Stored In The Table. Oracle Tables Can Store Different Data Types Including Numbers, Characters, Dates, And Timestamps. Data Analysts Frequently Query Tables To Explore, Transform, And Analyze Business Information.

4. What Is A Primary Key?

Ans:

• A Primary Key Is A Column Or Combination Of Columns That Uniquely Identifies Each Record In An Oracle Table. 
• It Does Not Allow Duplicate Values And Normally Does Not Allow NULL Values. A Primary Key Helps Maintain Data Integrity And Ensures That Every Record Can Be Uniquely Identified. 
• It Can Also Be Referenced By A Foreign Key In Another Related Table. Data Analysts Need To Understand Primary Keys When Joining And Analyzing Data From Multiple Tables.

5. 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 Establishes A Relationship Between Two Related Tables In A Database. Foreign Keys Help Maintain Referential Integrity And Prevent Invalid Relationships Between Records. For Example, A Customer ID In An Orders Table Can Reference The Customer ID In A Customers Table. Data Analysts Use Foreign Key Relationships To Join Related Data And Generate Meaningful Reports.

6. What Is The Difference Between WHERE And HAVING?

Ans:

WHERE Is Used To Filter Individual Rows Before Grouping And Aggregation Are Performed. HAVING Is Used To Filter Groups After Aggregate Functions Such As SUM, COUNT, Or AVG Have Been Applied. WHERE Is Generally Used For Conditions On Individual Records, While HAVING Is Used For Conditions On Aggregated Results. For Example, WHERE Can Filter Sales Above A Certain Amount, While HAVING Can Filter Departments With Total Sales Above A Target. Understanding This Difference Helps Analysts Create Accurate SQL Queries.

7. What Is An INNER JOIN?

Ans:

An INNER JOIN Returns Only The Records That Have Matching Values In Both Joined Tables. It Is Commonly Used When Related Information Is Required From Two Or More Tables. For Example, A Customers Table And An Orders Table Can Be Joined Using Customer ID. Records Without A Matching Value In Either Table Are Excluded From The Result. INNER JOIN Is One Of The Most Commonly Used Join Types In Oracle Data Analysis.

8. What Is A LEFT JOIN?

Ans:

A LEFT JOIN Returns All Records From The Left Table And Matching Records From The Right Table. If A Matching Record Does Not Exist In The Right Table, Oracle Returns NULL Values For The Right-Side Columns. It Is Useful When All Records From The Main Table Need To Be Preserved During Analysis. For Example, A LEFT JOIN Can Display All Customers Even If Some Customers Have Never Placed An Order. It Is Frequently Used For Missing-Data Analysis And Data Completeness Checks..

9. What Is A RIGHT JOIN?

Ans:

A RIGHT JOIN Returns All Records From The Right Table And Matching Records From The Left Table. When A Matching Record Is Not Available In The Left Table, Oracle Returns NULL Values For The Left-Side Columns. It Performs The Same General Logic As A LEFT JOIN But Gives Priority To The Right Table. Analysts Can Use It When All Records From The Right-Side Dataset Must Be Preserved. In Practice, Many Queries Can Be Rewritten As LEFT JOINs To Make SQL Easier To Read And Maintain.

10. What Is A FULL OUTER JOIN?

Ans:

• A FULL OUTER JOIN Returns Matching Records As Well As Non-Matching Records From Both Tables. When A Record Does Not Have A Match, NULL Values Are Displayed For Columns From The Other Table. 
• It Is Useful For Comparing Two Complete Datasets And Identifying Missing Records. Data Analysts Can Use FULL OUTER JOINs For Data Reconciliation, Migration Validation, And Source-System Comparisons. T
• his Join Is Particularly Helpful When Records From Both Tables Need To Be Preserved

11. What Is GROUP BY?

Ans:

GROUP BY Is Used To Combine Rows With Similar Values Into Groups For Analytical Calculations. It Is Commonly Used With Aggregate Functions Such As COUNT, SUM, AVG, MIN, And MAX. For Example, Sales Data Can Be Grouped By Department To Calculate Total Sales For Each Department. Every Selected Column That Is Not Aggregated Generally Needs To Be Included In The GROUP BY Clause. GROUP BY Helps Data Analysts Convert Detailed Records Into Useful Summary-Level Reports

12. What Are Aggregate Functions In Oracle?

Ans:

Aggregate Functions Perform Calculations On Multiple Rows And Return A Single Summary Result For Each Group. Common Oracle Aggregate Functions Include COUNT, SUM, AVG, MIN, And MAX. COUNT Can Be Used To Determine The Number Of Records, While SUM Can Calculate Total Sales Or Revenue. AVG, MIN, And MAX Can Be Used To Analyze Average, Lowest, And Highest Values. These Functions Are Essential For Creating Business Reports, Dashboards, And Analytical Summaries.

13. What Is COUNT(*)?

Ans:

COUNT() Is An Oracle SQL Function Used To Count The Number Of Rows Returned By A Query. It Counts Every Row, Including Rows Where Individual Columns Contain NULL Values. It Can Be Used To Count Customers, Employees, Orders, Transactions, Or Other Records. COUNT() Can Also Be Combined With GROUP BY To Calculate Record Counts For Different Categories. It Is Frequently Used In Data Analysis, Reporting, And Data Quality Validation..

14. What Is The Difference Between COUNT(*) And COUNT(Column)?

Ans:

Aspect	COUNT(*)	COUNT(Column)
Purpose	Counts All Rows In The Result Set	Counts Only Non-NULL Values In The Specified Column
NULL Handling	Includes Rows Even When Columns Contain NULL	Excludes Rows Where The Specified Column Is NULL
Usage	Commonly Used To Count Total Records	Used To Count Records With Available Values In A Column
Components	Includes servers, databases, middleware	Specific processes on application servers

15. What Is NULL In Oracle?

Ans:

NULL Represents A Missing, Unknown, Or Undefined Value In An Oracle Database. NULL Is Not The Same As Zero, And It Requires Special Handling In Comparisons And Calculations. Conditions Such As = NULL Do Not Correctly Identify NULL Values, So IS NULL And IS NOT NULL Should Be Used. Functions Such As NVL Can Replace NULL Values With Suitable Alternatives. Proper NULL Handling Is Important For Maintaining Accurate Data Analysis And Reporting.

16. What Is NVL?

Ans:

NVL Is An Oracle Function Used To Replace A NULL Value With A Specified Alternative Value. For Example, NVL(Salary,0) Can Replace A Missing Salary With Zero For A Calculation. It Is Particularly Useful When NULL Values Could Cause Calculations Or Reports To Produce Unexpected Results. Analysts Can Use NVL To Handle Missing Values During Data Transformation And Reporting. The Replacement Value Should Always Match The Business Meaning Of The Data.

17. What Is NVL2?

Ans:

NVL2 Is An Oracle Function That Checks Whether An Expression Contains A NULL Or Non-NULL Value. It Returns One Value When The Expression Is Not NULL And Another Value When The Expression Is NULL. For Example, NVL2(Commission,’Available’,’Not Available’) Can Categorize Records Based On Commission Availability. It Is Useful For Creating Conditional Categories And Derived Columns. Data Analysts Can Use NVL2 To Simplify Certain Data Transformation And Reporting Tasks.

18. What Is CASE Statement In Oracle?

Ans:

• CASE Is A Conditional Expression Used To Return Different Results Based On Specified Conditions. It Works Similar To IF-THEN-ELSE Logic And Can Evaluate Multiple Conditions In A SQL Query. 
• Analysts Can Use CASE To Categorize Customers, Employees, Sales Amounts, Scores, Or Other Business Values. 
• It Can Also Be Used To Create Calculated Columns And Conditional Aggregations. CASE Is A Powerful Tool For Data Transformation And Business Rule Implementation.

19. What Is DISTINCT?

Ans:

DISTINCT Is Used To Remove Duplicate Rows From The Result Of A SELECT Query. It Is Commonly Used When Analysts Need A List Of Unique Values Such As Departments, Cities, Product Categories, Or Customer IDs. When DISTINCT Is Applied To Multiple Columns, Oracle Considers The Combination Of Those Columns For Uniqueness. It Can Help Simplify Results And Support Data Exploration. However, DISTINCT May Require Additional Processing On Very Large Datasets.

20. What Is ORDER BY?

Ans:

ORDER BY Is Used To Sort The Results Of An Oracle SQL Query Based On One Or More Columns. Data Can Be Sorted In Ascending Order Using ASC Or Descending Order Using DESC. For Example, Sales Can Be Sorted From Highest To Lowest To Identify Top-Performing Products. Multiple Columns Can Also Be Included To Apply Secondary And Additional Sorting Rules. ORDER BY Helps Data Analysts Organize Query Results For Easier Interpretation And Reporting.

21. What Is A Subquery?

Ans:

A Subquery Is A SQL Query Written Inside Another SQL Query To Perform Additional Data Retrieval Or Calculation. It Can Be Used In Clauses Such As SELECT, FROM, WHERE, And HAVING Depending On The Requirement. Subqueries Help Break Complex Analytical Problems Into Smaller And More Manageable Logical Steps. For Example, A Subquery Can Calculate The Average Salary Before The Outer Query Identifies Employees Earning Above The Average. Data Analysts Use Subqueries To Filter, Compare, Transform, And Analyze Data Efficiently.

22. What Is A Correlated Subquery?

Ans:

• A Correlated Subquery Is A Subquery That Depends On Values From The Outer Query. Unlike A Regular Subquery, It References A Column From The Outer Query And Is Evaluated In Relation To The Current Outer Row.
•  It Can Be Useful For Performing Row-Level Comparisons And Finding Records That Meet Conditions Related To Their Group. 
• For Example, It Can Be Used To Find Employees Whose Salary Is Higher Than The Average Salary Of Their Department. Correlated Subqueries Can Be Powerful But May Require Performance Optimization On Large Datasets.

23. What Is A View In Oracle?

Ans:

A View Is A Virtual Database Object Created From A SQL Query And Used To Present Data From One Or More Tables. It Generally Stores The Query Definition Rather Than Maintaining A Separate Physical Copy Of The Underlying Data. Views Can Simplify Complex Queries, Improve Data Access Control, And Provide Consistent Business Logic. Data Analysts Can Query A View In Much The Same Way As A Regular Table. Views Are Commonly Used For Reporting, Data Abstraction, And Standardized Analytical Queries

24. What Is A Materialized View?

Ans:

• A Materialized View Is A Database Object That Physically Stores The Result Of A Query. Unlike A Regular View, It Contains Stored Query Results And Can Therefore Improve Performance For Frequently Executed Complex Analytical Queries. 
• The Materialized View Must Be Refreshed To Reflect Changes Made In The Underlying Source Data. Oracle Supports Different Refresh Strategies Depending On Data And Business Requirements. 
• Materialized Views Are Commonly Used In Data Warehousing, Reporting, And Performance-Sensitive Analytical Applications

25. What Is An Index In Oracle?

Ans:

An Index Is A Database Object That Helps Oracle Locate And Retrieve Rows More Efficiently. Indexes Are Commonly Created On Columns Frequently Used In WHERE Conditions, JOIN Conditions, Or Certain ORDER BY Operations. A Properly Designed Index Can Significantly Improve Query Performance, Especially When Working With Large Tables. However, Too Many Indexes Can Increase Storage Requirements And Add Overhead To INSERT, UPDATE, And DELETE Operations. Data Analysts Should Understand Indexes When Investigating Slow SQL Queries And Working With Database Administrators.

26. What Is A Composite Index?

Ans:

A Composite Index Is An Index Created Using Two Or More Columns From The Same Table. It Can Improve Query Performance When SQL Statements Frequently filter Or Join Data Using Multiple Columns Together. The Order Of Columns In The Index Is Important Because Oracle Uses The Index Based On Its Defined Column Structure And Query Conditions. Proper Composite Index Design Depends On Query Patterns, Column Selectivity, And Data Distribution. Data Analysts Can Work With Database Administrators To Identify Columns That May Benefit From Composite Indexing..

27. What Is A Sequence In Oracle?

Ans:

A Sequence Is An Oracle Database Object Used To Generate Numeric Values Automatically And Sequentially. It Is Commonly Used To Generate Unique Identifiers For Records In Tables. A Sequence Can Be Configured With Options Such As START WITH, INCREMENT BY, MINVALUE, MAXVALUE, And CACHE. The NEXTVAL Pseudocolumn Generates The Next Sequence Value, While CURRVAL Returns The Current Value In The Session. Sequences Are Useful In Enterprise Applications Where Automatically Generated Identifiers Are Required.

28. What Is A Schema In Oracle?

Ans:

A Schema Is A Collection Of Database Objects Owned By A Particular Database User. It Can Contain Tables, Views, Indexes, Procedures, Functions, Sequences, And Other Objects. Schemas Help Organize Database Objects And Control Access To Different Data Sources. Different Applications, Departments, Or Development Environments Can Use Separate Schemas To Maintain Logical Separation. Data Analysts Need To Understand Schemas To Locate Tables, Understand Data Ownership, And Access Relevant Business Information.

29. What Is A Transaction?

Ans:

A Transaction Is A Logical Unit Of One Or More Database Operations That Are Treated As A Single Unit Of Work. It Can Include Operations Such As INSERT, UPDATE, And DELETE That Modify Database Data. COMMIT Permanently Saves The Changes Made During A Transaction, While ROLLBACK Reverses Changes That Have Not Yet Been Committed. Transactions Help Maintain Data Consistency And Protect Against Incomplete Or Accidental Updates. Data Analysts Should Understand Transactions When Working With SQL Scripts That Modify Data.

30. What Is COMMIT?

Ans:

COMMIT Is A Transaction Control Statement Used To Permanently Save Changes Made To The Database During A Transaction. It Is Commonly Used After INSERT, UPDATE, Or DELETE Operations When The Changes Have Been Verified And Should Be Stored Permanently. After A COMMIT, The Changes Cannot Normally Be Reversed Using A Standard ROLLBACK. Analysts Should Use COMMIT Carefully When Working With Production Data To Avoid Accidental Changes. Read-Only SELECT Queries Generally Do Not Require COMMIT..

31. What Is ROLLBACK?

Ans:

• ROLLBACK Is A Transaction Control Statement Used To Reverse Changes That Have Not Yet Been Committed. It Is Particularly Useful When An INSERT, UPDATE, Or DELETE Operation Produces An Unexpected Or Incorrect Result. 
• ROLLBACK Helps Protect Data By Allowing Uncommitted Changes To Be Discarded. Once A Transaction Has Been Committed, A Normal ROLLBACK Cannot Be Used To Undo Those Changes. 
• Data Analysts Should Understand ROLLBACK Before Executing Data Modification Queries In Shared Or Production Environments.

32. What Is A Stored Procedure?

Ans:

A Stored Procedure Is A Named Program Unit Stored In The Oracle Database That Can Execute SQL And PL/SQL Statements. It Can Accept Input Or Output Parameters And Perform A Series Of Defined Operations. Stored Procedures Are Useful For Reusing Business Logic And Automating Complex Data Processing Tasks. They Can Help Centralize Database Operations And Reduce Repeated SQL Code In Applications. Data Analysts May Encounter Stored Procedures When Working With Enterprise Reporting, ETL Processes, And Business Data Systems.

33. What Is A Function In Oracle?

Ans:

A Function Is A Stored Program Unit That Performs A Specific Operation And Returns A Value To The Calling Program Or Query. It Can Accept Parameters And Contain SQL Or PL/SQL Statements To perform Calculations Or Transformations. Functions Are Useful For Reusing Common Business Calculations And Data Processing Logic. Some Oracle Functions Can Be Called Directly Within SQL Statements To Transform Or Analyze Data. Data Analysts May Use Built-In Or User-Defined Functions During Data Preparation And Reporting.

34. What Is PL/SQL?

Ans:

PL/SQL Stands For Procedural Language/SQL And Is Oracle’s Procedural Extension To SQL. It Adds Programming Features Such As Variables, Conditions, Loops, Exception Handling, Procedures, And Functions To Standard SQL. PL/SQL Allows Complex Data Processing And Business Logic To Be Executed Within The Oracle Database. It Is Commonly Used For Stored Procedures, Functions, Triggers, Database Automation, And ETL Operations. Basic PL/SQL Knowledge Can Be Valuable For Data Analysts Working With Complex Oracle Environments.

35. What Is A Cursor?

Ans:

A Cursor Is A Mechanism Used To Process Query Results In Oracle, Particularly Within PL/SQL Programs. It Allows A Program To Access And Process Rows Returned By A SQL Query. Cursors Can Be Implicitly Managed By Oracle Or Explicitly Declared When More Control Over Row Processing Is Required. They Are Useful When Individual Rows Must Be Processed According To Specific Procedural Logic. However, For Large Analytical Datasets, Set-Based SQL Is Usually More Efficient Than Row-By-Row Cursor Processing.

36. What Is A Common Table Expression?

Ans:

A Common Table Expression, Or CTE, Is A Temporary Named Result Set Defined Using The WITH Clause In A SQL Query. It Allows Complex Queries To Be Divided Into Smaller Logical Sections That Are Easier To Read And Understand. Multiple CTEs Can Be Combined To Perform Sequential Filtering, Aggregation, Transformation, And Analysis. CTEs Are Especially Useful When The Same Intermediate Result Needs To Be Referenced Within A Query. Data Analysts Use CTEs To Make Complex Analytical SQL More Organized, Maintainable, And Readable.

37. What Is A Window Function?

Ans:

A Window Function Performs Calculations Across A Set Of Related Rows Without Combining Those Rows Into A Single Result Row. Common Oracle Window Functions Include ROW_NUMBER, RANK, DENSE_RANK, LAG, And LEAD. They Can Be Used To Calculate Rankings, Running Totals, Moving Averages, And Comparisons Between Current And Previous Records. PARTITION BY Can Divide Data Into Groups While ORDER BY Determines The Sequence Used For The Calculation. Window Functions Are Extremely Useful For Advanced Data Analysis And Reporting.

38. What Is ROW_NUMBER()?

Ans:

ROW_NUMBER() Is A Window Function That Assigns A Unique Sequential Number To Each Row In A Result Set Or Within A Partition. The ORDER BY Clause Determines The Sequence In Which The Numbers Are Assigned. PARTITION BY Can Be Used To Restart The Numbering For Each Group, Such As Each Department Or Customer. It Is Commonly Used To Identify The Latest Record, Select The Top Record Per Group, Or Remove Duplicate Records. Data Analysts Frequently Use ROW_NUMBER() For Data Cleaning, Ranking, And Record Selection

39. What Is RANK()?

Ans:

• RANK() Is A Window Function Used To Assign Ranking Numbers To Rows Based On The Values Specified In The ORDER BY Clause. 
• Rows Having The Same Value Receive The Same Rank, Which Means Ties Are Given Equal Positions. When A Tie Occurs, The Following Rank Number Is Skipped; For Example, Two Records Ranked First Can Cause The Next Record To Receive Rank Three. 
• RANK() Is Useful For Ranking Employees, Products, Salespeople, Or Other Business Metrics. It Is Commonly Used In Performance Analysis And Top-N Reporting.

40. What Is DENSE_RANK()?

Ans:

• DENSE_RANK() Is A Window Function That Assigns The Same Rank To Equal Values Without Skipping The Next Ranking Number. For Example, If Two Employees Receive Rank One, The Next Employee Receives Rank Two Rather Than Rank Three. 
• This Is The Main Difference Between DENSE_RANK() And RANK(), Which Skips Ranking Numbers After Ties. 
• DENSE_RANK() Is Useful When Continuous Ranking Categories Are Required In Sales, Salary, Performance, And Top-N Analysis. Data Analysts Frequently Use It When Duplicate Values Should Share A Rank Without Creating Gaps.

41. What Is LAG()?

Ans:

LAG() Is An Oracle Window Function That Returns A Value From A Previous Row Based On A Specified Ordering. It Allows Data Analysts To Compare The Current Record With An Earlier Record Without Performing A Self-Join. For Example, Monthly Sales Can Be Compared With The Previous Month’s Sales To Calculate Growth Or Decline. LAG() Is Commonly Used For Trend Analysis, Period-over-Period Comparisons, And Sequential Data Analysis. It Is A Powerful Analytical Function For Working With Time-Series And Historical Data.

42. What Is LEAD()?

Ans:

LEAD() Is An Oracle Window Function That Returns A Value From A Following Row In An Ordered Result Set. It Allows Analysts To Compare The Current Record With A Future Record Without Using A Separate Self-Join. For Example, A Transaction Date Can Be Compared With The Next Transaction Date To Analyze Customer Activity. LEAD() Is Useful For Time-Series Analysis, Event Sequences, Forecast Comparisons, And Process Analysis. It Is Commonly Used Together With LAG() For Understanding Changes Across Sequential Records.s.

43. What Is UNION?

Ans:

UNION Is A SQL Set Operator Used To Combine The Results Of Two Or More Compatible SELECT Queries Into A Single Result Set. It Automatically Removes Duplicate Rows From The Combined Results. The SELECT Statements Must Have The Same Number Of Corresponding Columns With Compatible Data Types. UNION Is Useful When Similar Data Needs To Be Combined From Different Tables, Queries, Or Sources. When Duplicate Rows Need To Be Preserved, UNION ALL Should Be Used Instead.

44. What Is UNION ALL?

Ans:

• UNION ALL Combines The Results Of Two Or More Compatible SELECT Queries Without Removing Duplicate Rows. Since Oracle Does Not Need To Perform Duplicate Elimination, UNION ALL Can Often Execute Faster Than UNION. 
• It Is Useful When Combining Similar Datasets Where Duplicate Records Are Valid, Expected, Or Already Controlled. 
• Data Analysts Frequently Use UNION ALL To Append Data From Multiple Tables Or Periods. The Queries Must Still Return The Same Number Of Compatible Columns In The Corresponding Positions.

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

Ans:

DELETE Removes Individual Rows From A Table And Can Use A WHERE Condition To Control Which Records Are Deleted. TRUNCATE Removes All Rows From A Table And Is Designed To Quickly Empty The Table Without Removing The Table Structure. DROP Removes The Database Object Itself, Such As A Table, Along With Its Definition And Associated Data. These Commands Have Different Transaction, Recovery, And Dependency Behaviors In Oracle. Data Analysts Should Use Them Carefully, Especially When Working With Production Or Shared Database Environments.

46. What Is Data Cleaning In Oracle?

Ans:

Data Cleaning Is The Process Of Identifying And Correcting Missing, Duplicate, Incorrect, Inconsistent, Or Invalid Data. Oracle SQL Provides Functions Such As TRIM, REPLACE, UPPER, LOWER, NVL, And CASE To Transform And Standardize Data. Analysts Can Use SQL Queries To Detect NULL Values, Duplicate Records, Invalid Formats, And Unexpected Values. Cleaning Rules Should Be Based On Business Requirements And Clearly Defined Data Quality Standards. High-Quality Clean Data Improves The Accuracy And Reliability Of Reports And Business Decisions.

47. How Can Duplicate Records Be Identified?

Ans:

Duplicate Records Can Be Identified By Determining Which Columns Or Combination Of Columns Should Uniquely Identify A Record. GROUP BY Can Be Used With COUNT(*) To Find Groups Having More Than One Occurrence. ROW_NUMBER() Can Then Help Assign Numbers To Duplicate Rows And Identify Which Records Should Be Retained Or Investigated. The Definition Of A Duplicate Should Always Be Based On Business Rules Rather Than Assuming That Identical Rows Are Automatically Errors. Analysts Should Validate Duplicate Results Before Removing Or Modifying Any Records.

48. How Can NULL Values Be Identified?

Ans:

NULL Values Can Be Identified In Oracle Using The IS NULL Condition. For Example, WHERE Salary IS NULL Returns Records Where The Salary Column Does Not Contain A Value. The IS NOT NULL Condition Can Be Used To Find Records Where A Value Exists. Functions Such As NVL And CASE Can Help Handle NULL Values During Calculations, Categorization, And Reporting. Identifying And Understanding NULL Values Is An Important Part Of Data Quality Analysis.

49. How Can The Second Highest Salary Be Found?

Ans:

The Second Highest Salary Can Be Found Using DENSE_RANK(), RANK(), Or A Suitable Subquery Depending On The Requirement. DENSE_RANK() Can Rank Salaries In Descending Order And Assign Rank Two To The Second Highest Distinct Salary. This Approach Correctly Handles Situations Where Multiple Employees Have The Same Salary. If Exactly The Second Row Is Required Rather Than The Second Distinct Salary, ROW_NUMBER() May Be More Appropriate. Analysts Should Clarify The Business Requirement Before Choosing The Ranking Method.

50. How Can The Top Five Salaries Be Found?

Ans:

• The Top Five Salaries Can Be Identified Using ORDER BY, ROW_NUMBER(), RANK(), DENSE_RANK(), Or Oracle’s Row-Limiting Features. ROW_NUMBER() Can Be Used When Exactly Five Individual Records Are Required, Even When Salary Values Are Duplicated. 
• DENSE_RANK() Or RANK() Can Be Used When Employees With Equal Salaries Should Receive The Same Ranking. 
• The Appropriate Method Depends On Whether The Requirement Is Based On Rows Or Distinct Salary Values. Ranking Functions Provide Flexible Solutions For Top-N Analysis And Business Reporting.

51. What Is Data Aggregation?

Ans:

Data Aggregation Is The Process Of Combining Detailed Records Into Summary-Level Information. Common Aggregations Include Total Sales, Average Revenue, Record Count, Minimum Value, And Maximum Value. Oracle SQL Uses Aggregate Functions Such As SUM(), AVG(), COUNT(), MIN(), And MAX() To Perform These Calculations. GROUP BY Is Commonly Used To Create Summaries For Categories Such As Products, Departments, Regions, Or Months. Data Aggregation Helps Analysts Convert Large Datasets Into Meaningful Business Metrics For Reports And Dashboards.

52. What Is Data Normalization?

Ans:

Data Normalization Is A Database Design Process Used To Organize Data And Reduce Unnecessary Duplication. It Divides Data Into Related Tables And Establishes Appropriate Relationships Between Them. Common Normalization Levels Include First Normal Form, Second Normal Form, And Third Normal Form. Proper Normalization Helps Improve Data Consistency And Reduce Insert, Update, And Delete Anomalies. Data Analysts Should Understand Normalization Because It Helps Explain Why Enterprise Databases Often Store Related Information In Multiple Tables.

53. What Is Denormalization?

Ans:

Denormalization Is The Intentional Combination Or Duplication Of Data To Improve Query Performance Or Simplify Data Analysis. It Is Commonly Used In Data Warehouses And Analytical Systems Where Fast Reporting Is More Important Than Strict Normalization. Denormalized Structures Can Reduce The Number Of Joins Required When Retrieving Frequently Used Business Data. However, They Can Increase Storage Requirements And Create Additional Data Maintenance Challenges. The Decision To Denormalize Should Depend On Performance Requirements, Query Patterns, And Business Needs.

54. What Is A Data Warehouse?

Ans:

A Data Warehouse Is A Centralized Data Storage System Designed Primarily For Reporting, Business Intelligence, And Analytical Processing. It Commonly Integrates Data From Multiple Operational Systems And Stores Historical Information For Long-Term Analysis. Data Warehouses Support Activities Such As Aggregation, Trend Analysis, Reporting, And Performance Measurement. They Are Generally Optimized For Analytical Queries Rather Than Frequent Transaction Processing. Oracle Can Be Used As A Database Platform For Enterprise Data Warehousing And Analytical Workloads.

55. What Is ETL?

Ans:

ETL Stands For Extract, Transform, And Load And Describes A Common Process For Moving And Preparing Data For Analysis. Extract Involves Collecting Data From Sources Such As Databases, Applications, Files, Or APIs. Transform Involves Cleaning, Validating, Converting, Standardizing, And Applying Business Rules To The Extracted Data. Load Places The Transformed Data Into A Target Database, Data Warehouse, Or Analytical System. Data Analysts Often Work With ETL Pipelines To Ensure That Reporting Data Is Complete, Consistent, And Reliable.

56. What Is Data Validation?

Ans:

• Data Validation Is The Process Of Checking Whether Data Meets Defined Quality, Structural, And Business Rules. Validation Can Check Data Types, Required Fields, Ranges, Uniqueness, Relationships, Formats, And Acceptable Values. 
• Oracle SQL Can Be Used To Create Validation Queries That Identify Missing, Invalid, Duplicate, Or Inconsistent Records. 
• Validation Should Be Performed Before Important Reports, Dashboards, Or Analytical Models Are Produced. Strong Data Validation Improves Confidence In The Accuracy And Reliability Of Business Information.

57. How Can Query Performance Be Improved?

Ans:

Oracle Query Performance Can Be Improved By Selecting Only Required Columns, Applying Appropriate Filters, And Avoiding Unnecessary Processing. Suitable Indexes Can Improve Queries That Frequently Filter, Join, Or Search Using Specific Columns. Complex Joins, Repeated Calculations, Unnecessary DISTINCT Operations, And Inefficient Subqueries Should Also Be Reviewed. EXPLAIN PLAN Can Help Identify Expensive Operations And Potential Performance Bottlenecks. Queries Should Be Tested With Realistic Data Volumes Before Being Used In Production Environments.

58. What Is EXPLAIN PLAN?

Ans:

EXPLAIN PLAN Is An Oracle Feature Used To Show How The Database Optimizer Intends To Execute A SQL Query. It Provides Information About Operations Such As Table Access, Index Usage, Join Methods, Sorting, And Filtering. Analysts And Database Professionals Can Use The Execution Plan To Investigate Slow Queries And Identify Expensive Operations. Reviewing The Plan Can Help Determine Whether Indexes, Query Rewriting, Or Other Optimization Techniques May Be Required. It Is An Important Tool For Understanding And Improving SQL Query Performance.

59. What Is A Partitioned Table?

Ans:

A Partitioned Table Is A Large Table That Is Divided Into Smaller Logical Sections Called Partitions. Partitions Can Be Created Based On Values Such As Dates, Regions, Categories, Or Other Suitable Columns. Oracle Can Sometimes Access Only The Relevant Partitions Instead Of Scanning The Entire Table, Which Can Improve Query Performance. Partitioning Can Also Make Large Datasets Easier To Manage, Archive, And Maintain. It Is Commonly Used In Large Enterprise Databases And Data Warehousing Environments

60. What Is Date Handling In Oracle?

Ans:

Date Handling In Oracle Involves Working With DATE, TIMESTAMP, And Related Data Types To Perform Time-Based Analysis. Oracle Provides Functions Such As SYSDATE, ADD_MONTHS, MONTHS_BETWEEN, TRUNC, EXTRACT, And TO_DATE For Date Manipulation. Analysts Use These Functions To Calculate Durations, Group Records By Month Or Year, And Analyze Trends Over Time. Correct Date Conversion And Formatting Are Important To Prevent Incorrect Analytical Results. Special Attention Should Also Be Given To Time Zones And Timestamp Requirements When Working With Global Data.

61. What Is SYSDATE?

Ans:

SYSDATE Is An Oracle SQL Function That Returns The Current Date And Time Of The Database Server. It Is Commonly Used In Queries That Require The Current Date For Calculations, Filtering, Or Reporting. Data Analysts Can Use SYSDATE To Calculate Record Age, Identify Recent Transactions, Or Determine Reporting Periods. The Returned Value Depends On The Database Server Environment Rather Than The Time Set On The User’s Local Device. Therefore, SYSDATE Should Be Used With An Understanding Of The Database Server’s Date And Time Settings.

62. What Is TRUNC For Dates?

Ans:

TRUNC Can Be Used With Dates To Remove The Time Portion Or Truncate A Date To A Specific Period. For Example, TRUNC(date_value) Returns The Date With The Time Portion Set To Midnight. TRUNC(date_value,’MM’) Can Be Used To Represent The Beginning Of The Month Containing The Date. Analysts Frequently Use TRUNC To Group Transactions By Day, Month, Quarter, Or Year. It Is Particularly Useful In Time-Based Reporting And Trend Analysis.

63. What Is TO_DATE?

Ans:

• TO_DATE Is An Oracle Function Used To Convert Character Data Into An Oracle DATE Value. It Uses A Format Model To Specify How The Input Text Should Be Interpreted As A Date. 
• For Example, A Character Value Containing Day, Month, And Year Information Can Be Converted Into A Proper DATE Value. 
• The Format Model Should Match The Structure Of The Input Data To Avoid Conversion Errors Or Incorrect Results. Data Analysts Commonly Use TO_DATE When Cleaning And Standardizing Date Information.

64. What Is TO_CHAR?

Ans:

TO_CHAR Is An Oracle Function Used To Convert Date Or Numeric Values Into Character Representations Using A Specified Format. It Is Commonly Used To Display Dates In Business-Friendly Formats Such As Month-Year Labels. For Example, A Date Can Be Converted Into A Text Value Representing The Month And Year For Reporting. TO_CHAR Can Also Be Used To Format Numbers For Presentation Purposes. Analysts Should Generally Keep Display Formatting Separate From The Underlying Data Type When Performing Calculations.

65. What Is String Manipulation In Oracle?

Ans:

String Manipulation In Oracle Refers To The Process Of Cleaning, Extracting, Combining, Formatting, Or Transforming Text Values. Oracle Provides Functions Such As SUBSTR, LENGTH, TRIM, REPLACE, UPPER, LOWER, And CONCAT For Working With Character Data. These Functions Can Be Used To Standardize Names, Product Codes, Addresses, And Other Text Fields. Multiple String Functions Can Be Combined To Handle Complex Data Cleaning Requirements. String Manipulation Is An Important Part Of Data Preparation And Transformation.

66. What Is REGEXP_LIKE?

Ans:

REGEXP_LIKE Is An Oracle Condition Used To Check Whether A Character Value Matches A Specified Regular Expression Pattern. It Can Be Used To Identify Text Values That Follow Particular Formatting Or Character Rules. For Example, Analysts Can Use It To Detect Unexpected Characters In Product Codes, Email-Like Values, Or Identifier Fields. Regular Expressions Provide More Flexible Pattern Matching Than Basic String Functions. However, Complex Regular Expression Conditions Should Be Used Carefully Because They Can Affect Query Performance On Large Datasets.

67. What Is A CLOB?

Ans:

CLOB Stands For Character Large Object And Is An Oracle Data Type Designed To Store Large Amounts Of Character Data. It Can Store Text That Is Much Larger Than The Typical Size Used For Standard Character Columns. CLOBs May Be Used For Documents, Long Descriptions, Notes, Articles, Or Other Large Text Content. Analysts May Need Specialized Functions And Techniques To Search, Compare, Or Process CLOB Values. Understanding CLOBs Is Useful When Working With Large Text-Based Enterprise Datasets.

68. What Is VARCHAR2?

Ans:

VARCHAR2 Is An Oracle Data Type Used To Store Variable-Length Character Data. It Stores Text Values Without Requiring Every Record To Occupy The Maximum Defined Length. VARCHAR2 Is Commonly Used For Names, Codes, Descriptions, Addresses, And Other Text-Based Attributes. The Maximum Size Available Depends On The Oracle Database Configuration And Usage Context. Data Analysts Frequently Encounter VARCHAR2 Columns When Exploring And Querying Oracle Tables..

69. What Is The Difference Between VARCHAR2 And CHAR?

Ans:

Aspect	VARCHAR2	CHAR
Storage	Stores Variable-Length Character Data.	Stores Fixed-Length Character Data.
Length	Uses The Actual Length Of The Stored Value	Pads Values To The Defined Column Length.
Usage	Suitable For Names, Descriptions, And Variable-Length Text.	Suitable For Fixed-Length Codes And Identifiers

70. What Is A Data Dictionary In Oracle?

Ans:

The Oracle Data Dictionary Is A Collection Of Metadata That Contains Information About Database Objects And Their Properties. It Can Provide Details About Tables, Columns, Users, Constraints, Indexes, Views, And Other Database Components. Data Dictionary Views Such As USER_TABLES And USER_TAB_COLUMNS Help Analysts Understand The Structure Of Available Data. These Views Provide Metadata Rather Than The Actual Business Records Stored In Tables. The Data Dictionary Is Especially Useful When Analysts Work With Large Or Unfamiliar Oracle Databases.

71. What Is USER_TABLES?

Ans:

USER_TABLES Is An Oracle Data Dictionary View That Provides Information About Tables Owned By The Current Database User. It Can Be Used To Identify Tables Available Within The User’s Schema. Data Analysts Can Query USER_TABLES When Exploring An Unfamiliar Database Environment And Determining Which Tables May Contain Relevant Information. The View Provides Metadata About Tables Rather Than The Actual Business Data Stored In Them. Similar Data Dictionary Views Can Be Used When Information About Other Users’ Tables Is Required And Accessible.

72. What Is USER_TAB_COLUMNS?

Ans:

USER_TAB_COLUMNS Is An Oracle Data Dictionary View That Provides Metadata About Columns In Tables Owned By The Current User. It Includes Information Such As Table Name, Column Name, Data Type, And Column Length. Analysts Can Use It To Understand Table Structures Before Writing SQL Queries Or Building Analytical Reports. It Is Particularly Helpful When Database Documentation Is Limited Or When The Structure Of An Unfamiliar Table Needs To Be Explored. Metadata Queries Can Make Data Discovery And SQL Development More Efficient.

73. How Can An Oracle Data Analyst Find Missing Data?

Ans:

Missing Data Can Be Identified By Checking Important Or Required Columns For NULL Values. Analysts Can Use COUNT, SUM, CASE, And Conditional Expressions To Calculate The Number And Percentage Of Missing Records. Missing Values Can Also Be Compared Across Departments, Dates, Products, Customers, Or Source Systems To Identify Patterns. Business Rules Should Determine Whether A Missing Value Is Acceptable, Expected, Or A Data Quality Problem. The Results Can Help Improve Data Quality And Increase The Accuracy Of Reports And Analytical Results.

74. How Can Outliers Be Identified Using Oracle SQL?

Ans:

• Outliers Can Be Identified Using Statistical Measures Such As Average, Standard Deviation, Percentiles, And Quartiles. Analysts Can Calculate Summary Statistics And Compare Individual Values Against Defined Statistical Or Business Thresholds. 
• Business Rules Can Also Identify Values That Are Unusually High Or Low For A Specific Product, Region, Customer, Or Time Period.
•  Outliers Should Be Investigated Before They Are Automatically Removed From The Dataset. An Extreme Value May Represent A Data Error Or A Genuine And Important Business Event.

75. How Can Monthly Sales Be Calculated In Oracle?

Ans:

Monthly Sales Can Be Calculated By Grouping Transaction Records According To The Month Of The Transaction Date. TRUNC(date_column,’MM’) Can Be Used To Represent The Beginning Of Each Month And Create Consistent Monthly Groups. The SUM() Function Can Then Be Applied To The Sales Amount To Calculate Total Sales For Each Month. Additional Columns Such As Region Or Product Can Be Included To Produce More Detailed Monthly Analysis. This Approach Is Commonly Used For Sales Trends, Management Reports, And Business Performance Analysis.

76. How Can Month-Over-Month Growth Be Calculated?

Ans:

Month-Over-Month Growth Measures The Change In A Metric Between The Current Month And The Previous Month. The LAG() Window Function Can Retrieve The Previous Month’s Value Within An Ordered Set Of Monthly Results. The Difference Between Current And Previous Values Can Then Be Divided By The Previous Value And Multiplied By 100 To Calculate Percentage Growth. NULL Or Zero Previous Values Should Be Handled Carefully To Avoid Incorrect Results Or Division-By-Zero Errors. This Metric Is Commonly Used To Analyze Revenue, Sales, Customers, Orders, And Other Business Trends..

77. What Is Data Profiling?

Ans:

Data Profiling Is The Process Of Examining A Dataset To Understand Its Structure, Characteristics, And Overall Quality. It Can Include Checking Row Counts, NULL Values, Distinct Values, Duplicate Records, Data Types, Minimum And Maximum Values, And Data Distributions. Oracle SQL Can Be Used To Generate Many Of These Data Profiling Metrics Efficiently. Profiling Helps Analysts Understand The Data Before Performing Detailed Analysis Or Building Reports. It Is An Important First Step In Data Cleaning, Data Quality Assessment, And Reporting Projects.

78. How Would An Oracle Data Analyst Handle A Slow Query?

Ans:

A Slow Oracle Query Should First Be Investigated To Identify The Actual Cause Of The Performance Problem. EXPLAIN PLAN Can Be Used To Examine Operations Such As Full Table Scans, Expensive Joins, Sorting, And Index Usage. The Query Structure, Filters, Joins, Selected Columns, Data Volume, And Available Indexes Should Then Be Reviewed. Performance Improvements Should Be Tested Carefully After Each Significant Change To Ensure That Results Remain Correct. The Final Solution Should Balance Query Performance, Accuracy, Maintainability, And Available Database Resources..

79. How Would Oracle Be Used For Business Reporting?

Ans:

Oracle Can Store And Provide Access To Large Volumes Of Structured Business Data Required For Reporting And Analysis. SQL Queries Can Extract Important Metrics Such As Revenue, Customers, Orders, Costs, Employee Performance, And Product Results. Aggregate Functions, GROUP BY, Joins, And Window Functions Can Transform Detailed Records Into Meaningful Analytical Results. Views And Materialized Views Can Provide Reusable Data Structures For Frequently Used Reports. The Results Can Then Be Connected To Business Intelligence Tools And Dashboards For Business Decision-Making.

80. How Can An Oracle Data Analyst Ensure Data Accuracy?

Ans:

• Data Accuracy Can Be Improved Through Data Validation, Reconciliation, Duplicate Checks, NULL Analysis, And Consistency Testing. 
• Source Data Should Be Compared With Defined Business Rules, Reference Values, And Trusted Source Systems To Confirm That The Information Is Correct. SQL Queries Can Help Identify Invalid, Missing, Duplicate, Or Inconsistent Records Before They Affect Reports. 
• Important Business Metrics Should Be Cross-Checked Against Reliable Reports Or Expected Results. Documented Validation Procedures Help Maintain Consistent, Reliable, And Trustworthy Analytical Data.

81. What Is The Difference Between WHERE And ON In Oracle Joins?

Ans:

• The ON Clause Defines The Matching Condition Between Tables During A JOIN Operation. The WHERE Clause Filters The Rows Returned By The Query After The Join Conditions Are Applied. 
• For INNER JOINs, The Difference May Not Always Change The Final Result, But It Can Be Important For OUTER JOINs. 
• Analysts Should Place Join Conditions In ON And Result-Filtering Conditions In WHERE For Better Query Clarity. Understanding This Difference Helps Prevent Unexpected Results When Working With Multiple Related Table

82. What Is The Difference Between INNER JOIN And OUTER JOIN?

Ans:

An INNER JOIN Returns Only The Records That Have Matching Values In Both Tables. An OUTER JOIN Can Also Return Non-Matching Records From One Or Both Tables Depending On Whether It Is LEFT, RIGHT, Or FULL OUTER JOIN. For Example, A LEFT JOIN Can Return All Customers Even When Some Customers Have No Matching Orders. Outer Joins Are Particularly Useful For Finding Missing Or Unmatched Data. Data Analysts Frequently Use These Joins For Data Comparison, Reconciliation, And Completeness Analysis.

83. What Is COALESCE In Oracle?

Ans:

COALESCE Is A SQL Function That Returns The First Non-NULL Value From A List Of Expressions. It Can Be Used To Provide A Fallback Value When The Preferred Column Contains NULL. For Example, COALESCE(Phone, Email, ‘Not Available’) Can Return The First Available Contact Value. It Is Useful For Handling Missing Data And Creating More Complete Analytical Results. Data Analysts Can Use COALESCE In Data Cleaning, Reporting, And Conditional Data Transformation.

84. What Is The Difference Between NVL And COALESCE?

Ans:

NVL Is An Oracle-Specific Function That Accepts Two Expressions And Returns The Replacement Value When The First Expression Is NULL. COALESCE Can Accept Multiple Expressions And Returns The First Non-NULL Value From The List. NVL Is Simple And Commonly Used For Basic NULL Replacement In Oracle Queries. COALESCE Provides Greater Flexibility When Several Alternative Values Need To Be Checked. Data Analysts Can Choose Between Them Based On The Complexity And Portability Requirements Of The SQL Query.

85. Write A Query To Find The Second Highest Salary.

Ans:

This query first finds the highest salary using MAX(). The outer query then finds the maximum salary that is lower than the highest salary. This is a commonly asked SQL coding question for Data Analyst interviews.

86. Write A Query To Find Employees Who Earn More Than The Average Salary.

Ans:

The subquery calculates the average salary of all employees. The main query compares each employee’s salary with that average. Only employees earning more than the average salary are returned.

87. Write A Query To Find The Highest Salary In Each Department.

Ans:

The GROUP BY clause groups employees based on their department. The MAX() function then identifies the highest salary within each department. This query is useful for department-level salary analysis and reporting.

88. Write A Query To Find Duplicate Employee Records.

Ans:

The query groups records using employee name and email. The COUNT() function counts how many times each combination appears. The HAVING clause returns only combinations that occur more than once.

89. Write A Query To Find Employees Who Joined In The Current Year.

Ans:

EXTRACT(YEAR FROM JOIN_DATE) retrieves the year from the joining date. SYSDATE provides the current database server date. The query therefore returns employees whose joining year matches the current year.

90. Write A Query To Calculate The Total Salary For Each Department.

Ans:

The GROUP BY clause creates a separate group for each department. The SUM() function calculates the total salary for every department. This type of query is commonly used for salary analysis, budgeting, and departmental reporting.

.

.