oracle assignment questions

• Onsite training

3,000,000+ delegates

15,000+ clients

1,000+ locations

• KnowledgePass
• Log a ticket

01344203999 Available 24/7

Top 40 Oracle Interview Questions & Answers

Explore our comprehensive blog on Oracle Interview Questions & Answers. Whether you're a seasoned professional or a beginner, our blog offers in-depth insights to help you ace your Oracle interview. From SQL queries to database architecture, we cover it all. Gain the knowledge and confidence you need to succeed in your Oracle interview.

Exclusive 40% OFF

Training Outcomes Within Your Budget!

We ensure quality, budget-alignment, and timely delivery by our expert instructors.

Share this Resource

• Oracle Database 11g Release 2 DBA - Part I
• Oracle Database 11g Release 2 DBA - Part II
• Redis Cluster Database Training
• Relational Databases & Data Modelling Training
• Introduction to Database Training

According to Glassdoor , a Database Administrator earns an average of around £50,000 per year. With such lucrative pay scales, the interviews are bound to be a test of your technical expertise. Check out these top 40 Oracle Interview Questions and Answers and start preparing for these interview questions to crack your DBA interview. 

Table of Contents  

1) Oracle Interview Questions and Answers 

     a) Basic Oracle Interview Questions 

     b) Intermediate Oracle Interview Questions 

     c) Advanced Oracle Interview Questions 

2) Conclusion 

Oracle Interview Questions and Answers  

Whether you're preparing for basic, intermediate, or advanced queries, this resource equips you with insights to confidently address a range of topics. From ACID properties to Materialised Views, this blog has all the Oracle Interview Questions and Answers you need. Let's delve into this repository of knowledge and elevate your readiness for Oracle interviews through these Oracle Interview Questions.  

Basic Oracle Interview Questions  

1) What is Oracle Database?  

Oracle Database is a Relational Database Management System (RDBMS) that enables efficient storage, retrieval, and management of structured data. 

2) What is SQL?  

Structured Query Language (SQL) is a language the enables communication with and manipulation of databases. 

3) Explain the difference between SQL and PL/SQL.  

SQL (Structured Query Language) is used for querying and manipulating databases, while PL/SQL (Procedural Language/SQL) is Oracle's extension for procedural programming, allowing for more complex data processing within the database. 

4) What is a Primary Key?  

A Primary Key in a database is a unique identifier for each record in a table. It ensures data integrity, facilitates searches, and forms the basis for relational connections. 

5) What are Indexes in Oracle?  

Indexes in Oracle are data structures that enhance database performance by enabling quicker data retrieval. They work like a book's index, allowing faster access to specific data within a table. 

6) What is the difference between a View and a Table?  

In Oracle, a View is a virtual table that displays data from one or more tables based on predefined queries. A Table, on the other hand, is a physical storage structure containing actual data. 

7) How can you prevent SQL injection in Oracle?  

To prevent SQL injection in Oracle, employ parameterised queries and bind variables. This technique ensures input values are treated as data, thwarting malicious attempts to manipulate the SQL query. 

8) What is the purpose of the COMMIT statement?  

The COMMIT statement in Oracle finalises and permanently applies all the changes made within a transaction to the database, ensuring data consistency and durability. 

9) Explain the difference between CHAR and VARCHAR2 data types.  

In Oracle, the CHAR data type stores fixed-length strings, and padding with spaces if needed. VARCHAR2 stores variable-length strings, using only the required space. 

10) What is the purpose of the NVL function?  

The NVL function in Oracle serves to replace null values with a specified alternate value. This ensures smoother data handling and accurate results in queries and calculations. 

11) What is the purpose of the ROLLBACK statement?  

The ROLLBACK statement in Oracle serves to undo any uncommitted changes made during a transaction, restoring the database to its previous consistent state, and ensuring data integrity. 

12) How can you retrieve the current date and time in Oracle?  

To retrieve the current date and time in Oracle, you can use the SQL function ‘SYSDATE’, which provides the system's current date and time information accurately. 

13) Explain the difference between a Left Outer Join and a Right Outer Join.  

A Left Outer Join returns all rows from the left table and matching rows from the right table, while a Right Outer Join returns all rows from the right table and matching rows from the left table. 

14) What is the purpose of the UNIQUE constraint?  

The purpose of the UNIQUE constraint in Oracle is to ensure that the values in a specific column or set of columns are distinctive and different across the table, preventing duplicate entries. 

15) What is the difference between a Transaction and a Session?  

In Oracle, a Transaction is a sequence of database operations treated as a single unit, ensuring data consistency. A Session, on the other hand, is a connection established by a user to interact with the database. Transactions affect data, while Sessions facilitate user interactions  

Intermediate Oracle Interview Questions  

16) What is Normalisation?  

Normalisation is a vital database design process that minimises data redundancy and dependency. By structuring data into separate tables, each with a specific purpose, Normalisation enhances data integrity and simplifies maintenance. This approach optimises database efficiency, ensuring consistent and reliable information storage and retrieval.  

17) Explain the difference between UNION and UNION ALL.  

In the context of SQL queries, the ‘UNION’ operator combines and returns distinct rows from multiple SELECT statements, eliminating duplicates. Conversely, ‘UNION ALL’ also merges results from multiple queries but includes all rows, including duplicates. The choice depends on whether you want distinct results or require all entries, respectively. 

18) What is the purpose of the GROUP BY clause?  

The purpose of the GROUP BY clause in SQL is to categorise and group rows from a table based on specific column values. It enables aggregation functions like COUNT, SUM, AVG, etc., to be applied to these groups, providing summarised information and aiding in data analysis. 

19) What is a Foreign Key?  

A Foreign Key in Relational Databases establishes a link between two tables by referencing the primary key of one table in another. This relationship enforces data integrity and maintains referential integrity, ensuring consistency and accuracy when retrieving and manipulating data across related tables. 

20) What is a Subquery?  

A Subquery, often referred to as a Nested Query, is a SQL query within another query. It's used to retrieve data that will be used in the main query's conditions or calculations. Subqueries enhance query flexibility, enabling data retrieval from multiple tables or applying aggregates for more precise results. 

21) What is the difference between a Candidate Key and a Primary Key?  

In Oracle SQL, a Candidate Key and a Primary Key both hold significant roles, yet with distinctions. While a Candidate Key is a unique identifier for each record within a table, a Primary Key is a chosen Candidate Key, serving as the main identification method for the records, facilitating efficient data organisation and retrieval. 

22) Explain the difference between a Stored Procedure and a Function.  

In database management, a Stored Procedure and a Function serve distinct purposes. A set of SQL statements designed to perform specific tasks, often altering data is known as a stored procedure. On the other hand, a Function returns a single value, derived from computations or transformations, and is commonly used within SQL queries. 

23) What is the difference between a Correlated Subquery and a Non-correlated Subquery?  

In SQL, the distinction between a Correlated Subquery and a Non-correlated Subquery lies in their interaction with the Outer Query. A Correlated Subquery references values from the Outer Query, influencing its execution for each row processed. On the other hand, a Non-correlated Subquery operates independently, obtaining data without relying on the Outer Query's context. 

24) What is a Sequence in Oracle?  

A Sequence in Oracle is a database object used to generate unique numeric values sequentially. It's commonly used to provide primary key values for tables. Sequences ensure that each value is unique and can be incremented or decremented based on defined steps. This enhances data integrity and simplifies data management. 

25) What is a Bitmap Index?  

A Bitmap Index is a data structure in Oracle that efficiently represents binary attributes, like Yes/No or True/False values. It uses bitmaps to index data, where each bit corresponds to a unique attribute value. This indexing method accelerates queries involving multiple attributes and enhances query performance by minimising disk I/O. 

26) Explain the difference between a Database Trigger and a Stored Procedure.  

A Database Trigger and a Stored Procedure are both database objects, but they serve distinct purposes. A Trigger is automatically executed in response to specific events, like data changes, while a stored procedure is a reusable set of SQL statements that can be invoked manually. Triggers are event-driven, whereas Stored Procedures are invoked by application code. 

27) What is the difference between a Synonym and an Alias?  

In the context of databases, a Synonym and an Alias are terms used interchangeably, but they serve distinct purposes. A Synonym is an alternate name for an object, aiding in simplifying complex queries. On the other hand, an Alias is a temporary renaming of a table or column in a query, enhancing readability. 

28) Explain the difference between a Hot Backup and a Cold Backup.  

In Oracle databases, a Hot Backup and a Cold Backup differ in timing and accessibility. A Hot Backup is taken while the database is operational, allowing users to access data. In contrast, a Cold Backup is conducted while the database is offline, ensuring data consistency but temporarily restricting user access. 

29) What is the purpose of the WITH clause (Common Table Expression)?  

The WITH clause, also known as a Common Table Expression (CTE), serves the purpose of enhancing query readability and simplifying complex SQL queries. It allows users to define temporary result sets within a query, making it easier to reference and manipulate data. This aids in breaking down intricate queries into more manageable parts, promoting efficiency and maintainability. 

30) Explain the difference between a Schema and a User in Oracle.  

In Oracle, a Schema is a logical container that holds database objects like tables, views, and procedures. A User, on the other hand, is an account with a unique name and password, allowing access to a specific schema. A User can own multiple Schemas, but a Schema is associated with only one User. 

Unlock the Power of Data Management with Our Oracle Database 12c Administration Course – Join Today!  

Advanced Oracle Interview Questions  

31) What is the difference between a Clustered and a Non-clustered Index?  

A Clustered Index dictates the physical order of rows in a table, reorganising the data to match the Index. In contrast, a Non-clustered Index is a separate structure that references rows' locations without altering the actual data order. While a table can only have one Clustered Index, it can have multiple Non-clustered Indexes. This distinction impacts performance: a Clustered Index is efficient for range queries due to its data order, while Non-clustered Indexes excel in speeding up data retrieval for specific columns. 

32) What is the purpose of the ‘HAVING’ clause?  

The purpose of the ‘HAVING’ clause in SQL, particularly within the context of a query with GROUP BY, is to filter the results after the grouping has been applied. Unlike the ‘WHERE’ clause, which filters rows before grouping, the ‘HAVING’ clause works on grouped data. It allows you to set conditions on aggregated values, enabling you to retrieve specific subsets of grouped data that meet certain criteria. This is particularly useful when you need to perform aggregate functions like ‘SUM’, ‘COUNT’, ‘AVG’, etc., and then filter the results based on those aggregated values. The ‘HAVING’ clause helps to refine results and extract meaningful insights from grouped data. 

33) Explain the ACID properties in the context of Oracle Transactions.  

In Oracle Transactions, the ACID properties play a pivotal role in ensuring the reliability and consistency of data manipulations. ACID, an acronym for Atomicity, Consistency, Isolation, and Durability, outlines a set of fundamental principles that guide the behaviour of Transactions. Atomicity makes sure that a Transaction is treated as a single, indivisible unit of work, ensuring that either all its changes are applied or none at all.  

Consistency ensures that only valid data states are transitioned between Transactions. Isolation maintains the separation of concurrent Transactions to prevent interference. Durability guarantees that committed changes are permanently stored even in the face of system failures, solidifying the integrity of the database. 

34) What is an Execution Plan in Oracle?  

An Execution Plan in Oracle is a strategic roadmap that outlines how the database engine will execute a specific SQL query. It provides a detailed step-by-step guide for the database optimiser on how to retrieve and manipulate data efficiently from tables, indexes, and other database objects.  

The Plan's objective is to optimise query performance by choosing the most efficient access paths, join methods, and sorting techniques. This plan is generated after careful analysis of available statistics, database structure, and optimisation algorithms. By understanding and interpreting the execution plan, Database Administrators and Developers can fine-tune queries to enhance overall system performance. 

35) What are Materialised Views?  

A Materialised View is a precomputed and stored result of a complex query, designed to enhance data retrieval efficiency. Unlike standard views, which execute queries in real-time, Materialised Views store the query results physically in the database. This allows for quicker access to frequently requested information, reducing the need to recompute the same results repeatedly.  

Materialised Views are particularly useful in scenarios involving large datasets or intricate calculations, as they provide a snapshot of data that can be easily accessed without the need to re-run resource-intensive queries. This optimisation contributes to improved performance and response times within the Oracle database system. 

36) Explain the difference between a Full Backup and an Incremental Backup.  

A Full Backup and an Incremental Backup are two distinct strategies used in data backup and recovery processes. A Full Backup involves copying all data, files, and information from a source to a designated backup location, creating a complete snapshot of the entire dataset. On the other hand, an Incremental Backup captures only the changes that have occurred since the last backup, minimising storage requirements and backup time. While a Full Backup ensures a comprehensive restoration, Incremental Backups are more efficient and consume less storage space. 

37) What are Materialised Views?  

In Oracle databases, a Materialised View is a precomputed table that stores the results of a complex query. Unlike regular views that simply provide a virtual representation of data, Materialised Views physically store the data, allowing for quicker data retrieval and improved performance.  

These views are especially useful when dealing with large datasets or complex joins, as they eliminate the need to repeatedly execute resource-intensive queries. By storing the computed data, Materialised Views reduce the workload on the database server and enhance response times, making them a valuable tool for optimising query performance in Oracle database systems. 

38) What is the difference between an ‘INNER JOIN’ and a ‘LEFT JOIN’?  

An ‘INNER JOIN’ retrieves only the matching records from both tables, filtering out non-matching ones. This creates a tighter link between the tables and is useful when you want to focus solely on shared data. On the other hand, a ‘LEFT JOIN’ retrieves all records from the left table and the matching records from the right table. This inclusive approach ensures that even non-matching entries from the left table are displayed, maintaining context and aiding in data analysis. 

39) How can you improve query performance in Oracle?  

To enhance query performance in Oracle, an effective approach is optimising the database schema by employing appropriate indexing techniques. Indexes expedite data retrieval, reducing the need for full-table scans. Moreover, refining SQL queries through intelligent coding, minimising the use of unnecessary joins, and avoiding Wildcard Character usage can significantly boost performance.  

Utilising Materialised Views to precompute query results and leveraging query optimisation tools such as the Oracle Query Optimiser can further optimise Execution Plans. Lastly, maintaining regular database maintenance tasks, like updating statistics and managing fragmentation, ensures consistent and efficient query performance, enhancing the overall system's responsiveness. 

40) Explain the purpose of the Oracle Data Pump utility.  

The Oracle Data Pump utility offers a fast and efficient means of exporting and importing large volumes of data, making it essential for tasks like database migration, system upgrades, and data consolidation. This utility provides enhanced control over the export and import processes, allowing users to specify data subsets, compression options, and parallel execution to optimise performance.  

By streamlining data transfer, the Oracle Data Pump utility empowers administrators to ensure seamless data transitions while minimising downtime and maintaining data integrity. It stands as a pivotal tool in managing Oracle database environments with precision and efficiency.  

Unlock the Power of Data with our Oracle SQL Fundamentals Course – Register Now and Master the Language of Databases!  

Conclusion  

Preparation is the key to success in any Oracle interview. Whether you're facing basic, intermediate, or advanced questions, having a solid understanding of Oracle's database concepts is essential. By mastering these top Oracle Interview Questions and Answers, the readers will give themselves the best chance of landing their dream jobs.  

Elevate your skills with comprehensive Oracle Training and unlock new opportunities in the world of database management.  

Frequently Asked Questions

Upcoming programming & devops resources batches & dates.

Mon 3rd Jun 2024

Mon 2nd Sep 2024

Mon 2nd Dec 2024

Get A Quote

WHO WILL BE FUNDING THE COURSE?

My employer

By submitting your details you agree to be contacted in order to respond to your enquiry

• Business Analysis
• Lean Six Sigma Certification

Share this course

Our biggest spring sale.

We cannot process your enquiry without contacting you, please tick to confirm your consent to us for contacting you about your enquiry.

By submitting your details you agree to be contacted in order to respond to your enquiry.

We may not have the course you’re looking for. If you enquire or give us a call on 01344203999 and speak to our training experts, we may still be able to help with your training requirements.

Or select from our popular topics

• ITIL® Certification
• Scrum Certification
• Change Management Certification
• Business Analysis Courses
• Microsoft Azure Certification
• Microsoft Excel Courses
• Microsoft Project
• Explore more courses

Press esc to close

Fill out your  contact details  below and our training experts will be in touch.

Fill out your   contact details   below

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.

Back to Course Information

Fill out your contact details below so we can get in touch with you regarding your training requirements.

* WHO WILL BE FUNDING THE COURSE?

Preferred Contact Method

No preference

Back to course information

Fill out your  training details  below

Fill out your training details below so we have a better idea of what your training requirements are.

HOW MANY DELEGATES NEED TRAINING?

HOW DO YOU WANT THE COURSE DELIVERED?

Online Instructor-led

Online Self-paced

WHEN WOULD YOU LIKE TO TAKE THIS COURSE?

Next 2 - 4 months

WHAT IS YOUR REASON FOR ENQUIRING?

Looking for some information

Looking for a discount

I want to book but have questions

One of our training experts will be in touch shortly to go overy your training requirements.

Your privacy & cookies!

Like many websites we use cookies. We care about your data and experience, so to give you the best possible experience using our site, we store a very limited amount of your data. Continuing to use this site or clicking “Accept & close” means that you agree to our use of cookies. Learn more about our privacy policy and cookie policy cookie policy .

We use cookies that are essential for our site to work. Please visit our cookie policy for more information. To accept all cookies click 'Accept & close'.

Oracle SQL 2023

Oracle SQL is a database management system that is widely used around the world. It’s best suited for commercial and enterprise applications and is especially popular in large corporations with mission-critical databases. Mastering Oracle SQL will thus undoubtedly lead to exciting chances. Oracle SQL incorporates various modifications to the ANSI/ISO standard SQL language and additional commands provided by Oracle tools and applications. SQL*Plus and Server Manager from Oracle allows you to run any ANSI/ISO standard SQL statement on an Oracle database, as well as additional commands or functions.

Although some Oracle tools and applications reduce or hide the use of SQL, SQL is used for all database operations. Any other means of data access would evade Oracle’s security and potentially jeopardize data security and integrity.

Free Oracle SQL Practice Test Online

The relational database management system is Oracle SQL . It is common in enterprise applications. A database is a collection of structured data that is stored electronically. The database stores the data and provides access, management, and assistance locating essential information. Relational database management (RDBMS) has grown in popularity and efficiency over the previous flat-file paradigm, and RDBMS allows you to eliminate unnecessary data. Oracle SQL is the most well-known relational database technology, accounting for a sizable portion of the market among other relational databases. The following are the important Oracle SQL features:

• Unleash the full scope of query: SQL Analytic

Conformance with standards: ANSI SQL compliance

Indexes, in-memory, partitioning, and optimization all contribute to performance.

Read consistency across multiple versions

Text and spatial

PL/SQL and Java are two procedural extensions.

Oracle SQL Normalization

Normalization is a set of actions to create a database design that enables efficient data access and storage. These methods limit data redundancy and the likelihood of data inconsistency. Normalization also aids in the organization of data in a database. It is a multi-step procedure that converts data into a tabular format and removes duplicate data from relational tables. Normalization organizes a database’s columns and tables to guarantee that database integrity constraints execute their dependencies correctly. It is a method of deconstructing tables used to minimize data redundancy (repetition) and undesired characteristics such as Insertion, Update, and Deletion anomalies. It becomes difficult to manage and update the database without data loss if it is not normalized.

• First Normal Form – Avoids repeated groups by placing them on their table and connecting them with a one-to-many relationship.
• Second Normal Form – Every non-key property must be dependent on the entirety of every candidate key, not simply a portion of a key.
• Third Normal Form – Non-key attributes must only be dependent on candidate keys.
• Fourth Normal Form – Divides independent multi-valued data recorded in a single table into different tables.
• Fifth Normal Form – Distinguishes data redundancy that is not addressed by any of the other normal forms.

Oracle Live SQL

Oracle Live SQL is a new Oracle Database 12c that enables developers to design and run SQL scripts against Oracle databases easily. It is appropriate for fast code demonstrations, debugging, and troubleshooting. In general, it is a more convenient manner for people teaching or presenting SQL, PL/SQL, or another Oracle function to use Oracle. The advantage of Oracle Live SQL is that it works with any database on the platform (not just Oracle), so you have an option. It allows you to see the query’s execution strategy in real-time without waiting for it to complete. You can also see if your indexes are being used correctly and which ones require updating.

Furthermore, Oracle Live SQL supports more complex queries, such as text searches using RegExp or wildcards (*). This new technology enables you to change your live data while querying it. This implies that any changes you make will automatically be reflected in the database.

DATE DIFF in Oracle SQL

The DATEDIFF function calculates the difference between two dates in various units. To determine the difference in days or seconds between two dates or datetimes, use the @DATEDIFF function. The DATEDIFF function receives three parameters: the first is a datepart (which might be a year, quarter, month, day, hour, etc.) and the remaining two dates to compare. The SQL DATEDIFF function returns the number of specified datepart boundaries crossed between the supplied startdate and enddate.

@DATEDIFF (‘difference’, ‘date’, ‘date’)

Cursor Loop Oracle SQL

The cursor FOR LOOP statement opens a cursor and implicitly declares its loop index as a record variable of the row type that a given cursor returns. With each iteration, the cursor FOR LOOP command fetches a row from the result set into the record. The cursor FOR LOOP statement closes the cursor when there are no more rows to fetch. If a statement inside the loop transfers control outside the loop or produces an exception, the cursor also closes.

When all of the records in the cursor have been obtained, the CURSOR FOR LOOP will end. When you wish to fetch and process every entry in a cursor, you will use a CURSOR FOR LOOP.

FOR record_index in cursor_name

   {…statements…}

Parameters:

• record_index – This is the record’s index.
• cursor_name – The name of the cursor from which you want to get records.
• statements – The code instructions to be executed go via the CURSOR FOR LOOP.

Oracle SQL Email Validation

Email validation in SQL can be accomplished in a variety of ways. The best technique to validate an email is to utilize regular expressions. Before Oracle 11G, the developer must construct a SQL function for email validation. Email validation is also possible in SQL servers. SQL Server has a PATINDEX function, similar to the REGEXP function in Oracle. Oracle 11 G is used to write the REGEXP function. Before that, the user must write a function, which must then be used in a procedure or select statement. If the email address is correct, the provided function will return ‘SUCCESS,’ else it will return ‘FAILURE.’

Most Recent Date Oracle SQL

Knowing how to retrieve the current date and time in any language is essential. Until Oracle8i Database, there was only one way to acquire the date and time in PL/SQL: utilize the SYSDATE function. Beginning with Oracle9i Database, you have access to all Table functions, and you must grasp how they work and your options.

Oracle SQL Limit Rows

The optimal strategy for limiting rows in Oracle will consider performance, flexibility, and the database version you are using. If you’re running Oracle 12c, use the FETCH syntax, which was designed specifically for this purpose. If you aren’t using Oracle 12c, we recommend using the AskTom approach, which was advocated by Tom Kyte and is widely utilized by Oracle customers. So that’s how you may build a query in Oracle to limit the number of rows returned.

All database systems do not support the SELECT TOP clause. To pick a limited number of records, MySQL supports the LIMIT clause, whereas Oracle employs FETCH FIRST n ROWS ONLY and ROWNUM. ROWNUM is a query-accessible pseudocolumn (not a real column). The numbers 1, 2, 3, 4,… N will be allocated to ROWNUM, where N is the number of rows in the set ROWNUM is used. A ROWNUM value is not assigned to a row indefinitely (this is a common misconception). A row in a table does not have a number; there is no such thing as row 5 in a table. A ROWNUM value is assigned to a row after it has passed the query’s predicate phase but before it is sorted or aggregated.

Oracle 12 Syntax:

SELECT column_name(s)

FROM table_name

ORDER BY column_name(s)

FETCH FIRST number ROWS ONLY;

Older Oracle Syntax:

WHERE ROWNUM <= number ;

Older Oracle Syntax (with ORDER BY):

FROM ( SELECT column_name(s) FROM table_name ORDER BY column_name(s) )

Oracle SQL Replace New Line Character

Oracle SQL select statement from a table containing newline characters (such as \n, \r, \t) and replace it with a space ” “.  This is simple to accomplish using ASCII codes and the dreaded TRANSLATE() command. This substitutes space for newline, tab, and carriage return. TRANSLATE() is far more efficient than its regex counterpart.

select translate(your_column, chr(10)||chr(11)||chr(13), ‘    ‘)

from your_table;

The REGEXP_REPLACE function in Oracle/PLSQL is an expansion of the REPLACE function. This method, introduced in Oracle 10g, allows you to use regular expression pattern matching to replace a sequence of characters in a string with another set of characters. Oracle’s REGEXP_REPLACE function syntax is as follows:

REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ] )

Count in Oracle SQL

COUNT is an important Numeric/Math function in Oracle. It is used to calculate the count of an expression. The COUNT function is supported in all Oracle/PLSQL versions, including Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, and Oracle 8i. The COUNT() method in Oracle is an aggregate function that returns the number of items in a group.

COUNT( [ALL | DISTINCT | * ] expression)

• If you use DISTINCT, you can only specify the query partition clause of the analytic clause. The order by clause and windowing clause clauses are not permitted.
• COUNT returns the number of rows where expr is not null if you specify it. You can count either all rows or simply distinct expr values.
• Suppose you provide an asterisk (*); this function will return all rows, including duplicates and nulls. COUNT will never return null.

Oracle SQL Question and Answers

There are several ways to remove decimal values in SQL:

• Using the ROUND () function: This function in SQL Server is used to round a particular number to a certain number of decimal places.
• Using the FLOOR () function: Returns the largest integer value less than or equal to a number.
• Using the CAST () function: Explicit conversion must be performed in SQL Server using the Cast or Convert functions.

To access SQL scripts: Sign in to the workspace home page.

•  To display the SQL Scripts page, do one of the following: 
• Click the SQL Workshop icon and click SQL Scripts to go to the SQL Scripts page.
• Click the down arrow to the right of the SQL Workshop icon to display a drop-down menu. Then select the SQL Script menu option.

A structured query language (SQL) is a set of statements that all programs and users use to access data in an Oracle database. Application programs and Oracle tools often allow users to access the database without using SQL directly, but these applications must use SQL when executing their requests.

• From the Oracle SQL Developer menu, go to Tools> Settings.
• In the left pane of the Settings dialog box, select Database> NLS. 
• From the list of NLS parameters, enter DD-MON-RR HH24: MI: SS in the Date Format field.
• Save and close the dialog. That’s it.
• In SQL Developer, click the Reports tab on the left side of the Connection Navigator. … 
• In the Report Navigator, expand Data Dictionary Report.
• Under Data Dictionary, expand Report on Database. 
• Under About Database, click Version Banner.
• Start the comment with a slash and an asterisk (/ *). Continue with the comment text. This text can span multiple lines. .. .. 
• Start the comment with-(two dashes). Continue with the comment text. This text cannot be broken.

This can be easily done using equals to(=), less than(<), and greater than(>) operators.

Use the ALTER TRIGGER statement to recompile a trigger manually.

•  In the terminal window, set the environment variables. Change to the sqldeveloper directory under $ ORACLE_HOME. Start SQL Developer by running the shsqldeveloper.sh command.
• In the Connections navigator, right-click the Connections node and select New Connection.
• Enter the selected connection name, system user name, and password for the SYSTEM user. Select Save Password if you want to save the password for future connections as this user. Accept the default connection type and role. Enter the host name, port, and SID. You can click Test to verify that the connection is working properly. Click Connect. 
• The connection appears on the Connections tab on the left and the SQL Worksheet opens automatically.

To delete a table: In SQL Developer, follow the steps in View Tables to the PURCHASE_ORDERS table in the HR schema. Right-click the PURCHASE_ORDERS table, select Tables, and then select Delete.

The easiest way to escape single quotes in SQL is to use two single quotes.

Exclude Weekends in Oracle SQL Using to_char(date, `DY’) Function. SELECT * FROM sales_orders WHERE TO_CHAR(order_date, ‘DY’) NOT IN (‘SAT’, ‘SUN’) and trunc(order_date) >= trunc(sysdate) – 30; The statistics output will consist of all days besides Saturdays and Sundays.

In Oracle SQL Developer, click the Tools menu and select the Export Database option. The following window is displayed. Specify Scheme from the Connection drop-down menu and select the check boxes as needed. Also specify the name and location of the export file.

The SQL LIMIT clause limits the number of rows returned by the SELECT statement. For Microsoft databases such as SQL Server and MS Access, you can use the SELECT TOP statement to limit the results. This is equivalent to Microsoft’s own SELECT LIMIT statement.

To execute a query using multiple statements, make sure that each statement is separated by a semicolon. Then set the DSQEC_RUN_MQ global variable to 1 and run the query. If the variable is set to null, all statements after the first semicolon will be ignored.

Open SQL Developer and connect to the Oracle database. Then, in the Connections pane on the left, expand the schema node that runs the stored procedure. Then expand the Procedures node, select the stored procedure you want to run, and right-click.

Right-click within editor to show the pop-up-menu. Select Unwrap or simply press Ctrl-Shift-U to unwrap the code.

Press Control-Shift-L to open the log. This is the message log by default, but when I create the item that is causing the error, I see the compiler log.

Dynamic SQL is a programming technique that allows you to dynamically create SQL statements at run time. The full text of SQL statements can be unknown at compile time, so you can use dynamic SQL to create more versatile and flexible applications.

SQL plan management uses a mechanism called SQL plan baseline. A plan baseline is a set of accepted plans that the optimizer can use for SQL statements. In a typical use case, after verifying that the plan is working properly, the planning baseline in the database simply contains the plan.

SQL_ID is just another hash of the library cache object name. In fact, after 10g, the whole story is as follows. Oracle has MD5 hashed the library cache object name and generated a 128-bit hash value.

The SQL Trace feature and TKPROF are two basic performance diagnostic tools that help you monitor and tune applications running on your Oracle server.

Oracle dba interview questions and answers

This article contains the most commonly asked top 80 oracle dba interview questions and answers

Table of Contents

oracle database administrator interview questions

Question 1.  What is oracle database ?

Answer: Oracle Database is a relational database management system (RDBMS) which is used to store and retrieve the large amounts of data. Oracle Database had physical and logical structures. Logical structures and physical structures are separated from each other

Question 2  What is the difference between Oracle database and Oracle instance?

Answer :Oracle database is the collection of datafiles,redologs and control files while Oracle instance is the SGA ,processes in the Memory.

We can have 1 or more instance serving a oracle database . In Oracle RAC , we have one set of datafiles,control file and redo logs while instance on one ore more boxes accesses the same database

Question 3 What is a Tablespace? Answer:  Oracle use Tablespace for logical data Storage. Physically, data will get stored in Datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles. A tablespace can have objects from different schema’s and a schema can have multiple tablespace . Database creates “SYSTEM tablespace” by default during database creation. It contains read only data dictionary tables which contains the information about the database.

Question 4  What are Datafiles? Answer: The datafiles contain all the database data. The data of logical database structures, such as tables and indexes , is physically stored in the datafiles allocated for a database.

Question 5  what is Control Files? Answer: Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database such as Database name and the Names and locations of datafiles and redo log files.

Question 6  What is Redo Log Files? Answer The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.

Question 7  What is Archive Log Files ? Answer: Oracle automatically archives log files when the database is in ARCHIVELOG mode. This prevents oracle from overwriting the redo log files before they have been safely archived to another location.

Question 8  What is Parameter Files (initSID.ora) Answer: Parameter files contain a list of configuration parameters for that instance and database.

Question 9  What is schema? Answer: A user account and its associated data including tables, Oracle views , indexes, clusters, sequences ,procedures, functions, triggers,packages and database links is known as Oracle schema. System, SCOTT etc are default schema’s. We can create a new Schema/User. But we can’t drop default database schema’s.

Question 10  What is data blocks ? Answer: Data Blocks are the base unit of logical database space. Each data block represents a specific number of bytes of database space on a disk.The data blocks can be 4 K,8 K size depending on the requirement.

Question 11.  What is an Extent ? Answer :Extent is a collection of Continuous data blocks, which is used for storing a specific type of information.

Question 12.  What is a Segment ? Answer: A segment is a collection of extents which is used for storing a specific data structure and resides in the same tablespace.

Question 13.  What is Rollback Segment ? Answer: Database contain one or more Rollback Segments to roll back transactions and data recovery.

Question 14.  What are the different type of Segments ? Answer

Data Segment(for storing User Data), Index Segment (for storing index), Rollback Segment and Temporary Segment.

Question 15. What is archive-log and No archive log mode? Answer: We all know that redo logs stored the redo information and redo log files are in circular fashion.Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The process of turning redo log files into archived redo log files is called archiving.

The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind. No archive log means archive log are not generated and redo are overwritten

Question 16.  What all things are present in the shared pool ?

Answer: The shared pool portion of the SGA contains three major areas: library cache(contains parsed sql statements,cursor information,execution plans) dictionary cache (contains cache -user account information,privileges information,datafile,segment and extent information) buffers for parallel execution messages control structure.

Question 17.  What is hotbackup ? Answer: If the database must be up and running 24 hours a day, seven days a week, then you have no choice but to perform inconsistent backups of the whole database. A backup of online data files is called an online backup. This requires that you run your database in ARCHIVELOG mode.

Question 18.  which views is used to finding the locking in the database? Answer: v$lock, v$session, v$process

Related Article

Oracle table locks

Question 19.  You have many instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?

Answer: There are two ways

Another way is to use

Question 20.  What is Database index ? Answer: A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space.By default, Oracle creates B-tree indexes .

Related article Oracle Indexes Question 21.  What is difference between the hotbackup taken through Oracle RMAN and Manual?

Answer check the below link for Detailed explanation

Mechanism followed by Oracle when we are taking hotbackup

Question 22. How to check Oracle database version?

The below matrix explains the number of the Oracle version

Lets take the example of Oracle version 10.2.0.4.0 10 – Major database release number 2 – Database Maintenance release number 0 – Application server release number 4 – Component Specific release number 0 – Platform specific release number

Question 23 How to find the database/sqlplus version?

select banner from v$version;

Question 24 How to find operating system version?

Question 25 .What is the location of init.ora ?

$RDBMS_ORACLE_HOME/dbs

Question 26 . What is that trace files contains and the utility used to read them?

Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. Tkprof utility is used to convert trace file into readable format.

Question 27 What is the syntax for tkprof?

tkprof explain=apps/ sys=no

Question 28 . What is a database link? How to create it?

If we want to access objects of another database from this database then we need a database link from this database to the other.

1.Login as oracle user

2.sqlplus “/as sysdba”

3. create database link connect to identified by using ”;

create database link MY1_TO_MY2 connect to apps identified by apps using ‘MY2′;

Database link created.

SQL> select name from v$database@ MY1_TO_MY2;

SQL>select db_link from dba_db_links; Add destination database tns entry in tnsnames.ora

Question 29 . How to apply patch to Oracle database Home software?

Answer : Oracle database Home software is patched using OPATCH utility

Applying patch

Question 30 How to find Database version ?

The command returns the release information,

Question 31 How to find opatch Version ?

opatch is utility to apply database patch , In order to find opatch version execute”$ORACLE_HOME/OPatch/opatch version” Even if you simple write $ORACLE_HOME/OPatch/opatch, it will show the opatch version in the start

Question 32 How to find that the database is 64-bit/32-bit?

Question 33 What is top command?

top is a operating system command, it will display top 10 processes which are taking high cpu and memory. 8. What is a patch?Answer A patch can be a solution for a bug/it can be a new feature.

Question 34 Which table you will query to check the tablespace space issues?

bytes column in dba_free_spaces and dba_data_files

Question 35 Which table u will query to check the temp tablespace space issues?

dba_temp_files

Question 36 What is temp tablespace?

Temp tablespace is used by so many application programs for sorting and other stuff.

Question 37 How to find out invalid objects in the apps schema in the database?

Question 38 How you will see hidden files in linux/solaris?

Question 39 How to generate the AWR report

Question 40 How to apply a rdbms patch?

Using opatch

Go the patch directory

cd <patch num>

opatch apply

what is PSU patch and how to verify the PSU update in the Oracle Home

Senior oracle dba interview questions

Question 41 How to kill a database session?

Question 42 How to find opatch is enabled or not for your database?

If Opatch directory exists under RDBMS_ORACLE_HOME.

Question 43 What is the pre-req for applying a rdbms patch?

Inventory should be set in file oraInst.loc @/var/opt/oracle or /etc and Oracle home must be registered in Central inventory

Question 44 What is Inventory?

The oraInventory is the location for the OUI (Oracle Universal Installer)’s book keeping. The inventory stores information about: All Oracle software products installed in all ORACLE_HOMES on a machine Other non-Oracle products, such as the Java Runtime Environment (JRE)

In a R12 Application system ,the RDBMS and iAS ORACLE_HOMEs and 10.1.2 are registered in the oraInventory.

How to run Opatch in non interactive form

Question 45 What are different types of inventories?

The Global inventory (or Central inventory)

The Local inventory (or Home inventory)

Question 46 What is Global inventory or central inventory?

The Global Inventory is the part of the XML inventory that contains the high level list of all oracle products installed on a machine. There should therefore be only one per machine. Its location is defined by the content of oraInst.loc.The Global Inventory records the physical location of Oracle products installed on the machine, such as ORACLE_HOMES (RDBMS and IAS) or JRE. It does not have any information about the detail of patches applied to each ORACLE_HOMEs. The Global Inventory gets updated every time you install or de-install an ORACLE_HOME on the machine, be it through OUI Installer, Rapid Install, or Rapid Clone.

Note: If you need to delete an ORACLE_HOME, you should always do it through the OUI de-installer in order to keep the Global Inventory synchronized.

Question 47 What is local inventory?

There is one Local Inventory per ORACLE_HOME. It is physically located inside the ORACLE_HOME at $ORACLE_HOME/inventory and contains the detail of the patch level for that ORACLE_HOME.The Local Inventory gets updated whenever a patch is applied to the ORACLE_HOME, using OUI.

Question 48 . What is AWR?

AWR is a database utility to gather database and session level performance information.

All about AWR

Question 49 How to enable trace at database level?

set init.ora parameter sql_trace

Question 50 How to enable trace for a session?

Execute the sql query

This will create a trace file at background dump dest directory

Question 51 How to enable trace for other session?

Example To enable trace for sql session with sid 9779

PL/SQL procedure successfully completed.

To disable trace

Question 52.  What is flashback database?

It is New feature in Oracle database post 10.1 onwards. It Uses past block images to back out changes to a oracle database . As the name suggest, we can use this flashback database in previous time

a.During normal database operation, Oracle occasionally logs past block images in flashback logs b.Flashback logs are  written sequentially not archived c. Oracle automatically creates, resizes and deletes flashback logs in the flash recovery area d.  DBA should be aware of flashback logs to monitor performance,to decide how much space to allocate to flash recovery area e. Allows database to be recovered to a previous time to correct problems caused by logical data corruptions,user errors Flashback database explained and limitation

Question 53.  How can you rebuild an index?

Answer: We can rebuild the index using the below command

If it is to be online

If it is to be rebuild offline

You can use parallel to speed up the rebuild

Question 54  What is Branch Block in index ? Answer: Branch block rows hold <separator key,dba> pairs used to guide the B-tree search to a row in a leaf block.

Question 55.  What is Leaf Block in index ? Answer: Leaf block rows hold the <KEY, KEYDATA> pairs stored by the B-tree.

Question 56.  What is High Water Mark in Oracle?

• High water mark in Oracle is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.
• Delete Table in Oracle operation won’t reset HWM.
• Oracle TRUNCATE will reset HWM.
• The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use. The blocks below the HWM level is used blocks, they are already used.

Question 57.  What parameters are used to set parallelism in the database? Answer: Following initialization parameters are required for parallelism setup in database.

Question 58.  What is difference between startup mount and startup nomount?

Answer.   startup mount -mount the control file

startup nomount- does not mount the controlfile

Related Articles

ORA-27154: post/wait create failed during startup Oracle Shutdown steps decoded

Question 59.  What is SCN (System Change Number) ?

Answer The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs. Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file

Question 60. How to find  Last password change of a user?

Oracle DBA questions and answers for experienced professionals

Question 61.  What is library cache lock?

Answer A library cache lock means that a session is waiting to use or change an object definition or to use a SQL statement that another session is loading, changing or parsing, or is waiting to change or parse. This usually indicates that database object definitions are being changed regularly.

Another example is gathering statistics on an object. When statistics are gathered all references to that object in the shared pool or library cache become invalid, requiring a new hard parse for each SQL statement referencing the object. Statistics should only be gathered when there are no active users or system activity is very low.

Question 62. How do we find the blocker for Library cache lock?

Answer: We can run hanganalyze to find the blocking session.

Many times the below query also works in wonderful manner

Question 63.  How to take global hanganalyze dump? Answer

Question 64.  How do you recover the database if you lost one of the controlfile in the database? Answer

Shutdown Instance ( abort )

Change Init.ora file to remove the lost controlfile or copy the existing controlfile to that location startup the database

Check below link for all the useful scenario for recovery

Database recovery various cases and solution

Question 65.  How do you recover the database if you lost all of the controlfile in the database?

a. Shutdown Instance (abort) b.  If the control-file backup is available ,then restore it from backup or if you have got the controlfile information in trace using alter database backup controlfile to trace c. Once the controlfile is created, recover database using backup controlfile. You will need to apply the redo logs to complete the recovery d, alter database open resetlogs;

We can avoid the resetlogs by using the steps below 1. After the database is mounted with restore of controlfile from backup,create a trace of the controlfile using the command below alter database backup controlfile to trace; 2. Now take out the create controlfile statement from the trace. Choose the NORESETLOG portion 3. Recreate the controlfile using the above portion. 4. Do recover database 5. alter database open

Question 66.  If the table is fragmented, how would you rebuild it?

Answer First we need to rebuild the table

Secondly we need to rebuild all its indexes

We can find all the indexes

Finally we should gather the stats on the table

Related article

How to rebuild the table in oracle

Question 67.  What view would you use to determine free space in a tablespace? Answer dba_free_space

Question 68.  How do you switch from an init.ora file to a spfile?

Answer: Create spfile from pfile ; shutdown instance startup

It will start using spfile

Question 69. You are experiencing high “busy buffer waits” . how can you find what’s causing it? Answer Buffer busy wait means that the queries are waiting for the blocks to be read into the db cache.There could be the reason when the block may be busy in the cache and session is waiting for it. It could be undo, data block or segment header wait.

Run the following query to find out the p1,p2 and p3 of a session causing buffer busy wait

After that running the following query to find the segment causing buffer busy wait:-

Question 70.  How to kill the database session? Answer

Question 71. What is Physical Block Corruptions?

This kind of block corruptions are normally reported by Oracle with error ORA-1578 and the detailed corruption description is printed in the alert log.

Corruption Examples are:

Bad header – the beginning of the block (cache header) is corrupt with invalid values The block is Fractured/Incomplete – header and footer of the block do not match The block checksum is invalid The block is misplaced

Question 72. What is Row chaining ?

Row chaining happens when a  row is too large to fit into a single database block.

For example, Suppose you have 4 KB block size for your database,and you need to insert a row of 8 KB into it, Oracle will use 3 blocks and store the row in pieces.

In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment.So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.And Row Chaining happens only when the row is being inserted and whenever it has inserted it cannot be chained.

In most cases chaining is unavoidable, especially when this involves tables with large columns such as LONGS, LOBs, etc. When you have a lot of chained rows in different tables and the average row length of these tables is not that large, then you might consider rebuilding the database with a larger block size. Tables with more than 255 columns can potentially force Chaining

Question 73 . What is Row Migration ?

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the the «forwarding address». So, the original block just has the ROWID of the new block and the entire row is moved.

Question 74. How to find the physical location of the datafiles,redo logs,controlfile?

Question 75 What is the difference between CPU and PSU patching?

The CPU are the Critical Patch Update, we can summarize it as the Security bugs fixes,  The new name for the critical patch updates is security patch update (SPU). the PSU contains the CPU + some technical bugs fixed.

For my point of view, it is better to apply PSU as it contains the CPU and the some others Important bugs fixes).

Question 76 How to verify whether a parameter changed required database bounce or not?

In v$parameter we can find one column ie. ISSYS_MODIFIABLE. This column contains three phases 1) Immediate 2) DEFERRED 3) False

Immediate : We can change the parameter in fly database i.e Dynamic.(only need to change the value no need to bounce) DEFERRED : We can change the parameter in fly database but this will effect after restart the database only.(here we need to edit using spfile and bounce the database) False : Compulsory we need to down the database i.e Static.

Question 77 How do we check if a Database upgrade is successful?

Question 78 How to find the locks and what is the resolution?

we can find general locks with the following query:

If it’s a dead lock, we need to kill that session.

Question 79 How to do database clone?

Read the below link How to clone the database using manual hot backup How to clone the database using manual cold backup

Question 80 . While applying a rdbms patch using opatch you are getting the error, unable to read inventory/inventory is corrupted/ORACLE_HOME is not not registered, what you will do, and how you will apply the patch?

We will check the inventory directory permission, try to apply the patch after giving 777 permissions to that inventory directory.

We can try to create to recreate the central inventory and If still it won’t work we will apply patch with the following command:

Opatch apply no_inventory

Oracle Database 12c Related Interview Questions

oracle 12c new features Understand Oracle Database 12c – Multitenant Architecture 5 Simple (But Important) Things To Remember About Oracle Database 12c views ,parameters and packages Oracle 12c New Features for developers Very useful 10 new things in 12c database

Hope you like this compilation of core oracle dba interview questions and answers.This will be helpful to senior DBA & experienced DBA also. Please do provide the feedback

Recommended  Courses

The following are some of the recommended courses you can buy if you want to get a step further

Given below are the links to some of the courses

Related Articles Unix shell scripting interview questions : compilation of unix shell scripting interview questions for success in any interviews.Examples are given for the command also Oracle interview questions : 49 Oracle Interview questions and answers : Basics , Oracle SQL to help you in interviews Oracle PLSQL interview questions : 25 Oracle PLSQL interview questions with detailed explanation and answer for the success in interview Weblogic Interview questions : Weblogic Interview questions to help you |How to access the admin console|States of the Weblogic Server|multicast and unicast oracle apps dba interview questions : 60 awesome oracle apps dba interview questions.Must read to succeed in interviews and jobs oracle apps technical interview questions and answers : 19 oracle apps technical interview questions and answers to succeed in your career

Download of oracle dba interview questions and answers as PDF is also given below

Related Posts

Service group changes in r12.2.

In previous Oracle E-Business Suite releases, the Application services were categorized into service groups according to the type of service provided. In Oracle E-Business Suite…

How to convert private key to ppk( Putty Format)

Many times, you get the private key from the cloud provider to connect to the Virtual machine. Generally, the keys will be in OpenSSH format.…

Top 9 Useful Oracle Apps Printer queries

Oracle Apps Printer Queries Check which printer,concurrent request is going to print SELECT NUMBER_OF_COPIES ,NLS_LANGUAGE ,NLS_TERRITORY ,PRINTER , PRINT_STYLE ,COMPLETION_TEXT ,OUTPUT_FILE_TYPE , NLS_CODESET ,OUTFILE_NODE_NAME,OUTFILE_NAME FROM…

Step by Step instructions for Oracle Virtual Box Installation

VirtualBox is a powerful x86 and AMD64/Intel64 virtualization product for enterprise as well as home use VirtualBox runs on Windows, Linux, Macintosh, and Solaris hosts…

9 thoughts on “Oracle dba interview questions and answers”

Thanks for sharing.learn more at https://www.janbasktraining.com/blog/top-sql-server-interview-questions-and-answers/

Pingback: Oracle Database Insurance Agent Interview | List of Insurance Firms

Pingback: Database Insurance Agent Interview Questions Answers | List of Insurance Firms

thanks for the information Read More

Great post. Please keep sharing the knowledge.

Thanks for sharing this. you can check the trending Data Fabric tools here

Thanks for sharing this information here.

This blog provides a comprehensive list of Oracle database interview questions, covering topics such as SQL, PL/SQL, database architecture, and performance tuning. It’s a useful resource for anyone preparing for an Oracle database interview or looking to brush up on their knowledge of Oracle database concepts. The article provides clear and concise answers to each question, making it easy to understand and follow. Additionally, it can be beneficial for businesses that use Oracle databases to ensure their staff is knowledgeable and capable of working with the database effectively.

Leave a Comment Cancel Reply

Your email address will not be published. Required fields are marked *

Top 75 Oracle Interview Questions (With Example Answers)

Mike Simpson 0 Comments

By Mike Simpson

When it comes to technology companies, Oracle has some unique claims to fame. After all, it created the world’s first autonomous database , which is a massive feat. It’s that kind of innovation that drives job seekers to this tech giant. That means candidates typically face stiff competition. If you want to stand out, then nailing the Oracle interview questions is a must.

Fortunately, it’s possible to get them right with a bit of preparation. If you’re about to face off against interview questions for Oracle, here’s what you need to know.

How to Answer Oracle Interview Questions

Before we hop into the Oracle interview questions, let’s take a second to talk about how you need to approach them.

As a large organization – with around 133,000 employees worldwide – it’s easy to assume landing a job at Oracle is easy. However, Oracle has the luxury of choice since so many candidates are interested. That’s why, if you apply, you need a solid strategy for answering interview questions for Oracle.

Precisely what that entails depends on the question type. As a technical company, Oracle will ask a lot of knowledge-based, straightforward, and traditional interview questions . With these, you usually present facts, augmenting them with details about your past experience or mentality when possible.

With behavioral and situational questions, you need a different approach. Usually, combining the STAR Method and the Tailoring Method will get you moving in the right direction. Your answers will be thorough, compelling, and relevant, making it easier to stand out.

However, you don’t want to hop right into practicing your answers. Instead, you need to make sure you can address that specific hiring manager’s needs. How do you do that? By diving into some research.

First, you want to take a hard look at the job description. See which skills and what experience Oracle is after. Along with any must-have skills list, seek out clues that are featured in other parts of the description. For example, if they describe the work environment, you can sometimes learn about traits the company wants to find.

Second, check out Oracle’s mission and values statements. Again, these clue you into the type of candidate they want, as professionals who feel a connection to those same points are often better matches for the culture.

You can also check out any Oracle social media profiles. This also includes insights that help you learn about the company’s culture, along with recent achievements that you may want to reference in some of your interview answers.

One thing that’s important to note about the Oracle job interview process is that it’s usually long. Many jobs require three, four, or even five rounds of interviews. While that may feel excessive, Oracle is a world-renown company that gets a slew of applicants.

Since that’s the case, Oracle can typically afford to be a bit picky. Make sure you’re comfortable with powering through a multi-round hiring process before you apply. That way, you’ll have the right mindset to make it to the end and hopefully snag an offer.

Top 5 Oracle Interview Questions

Oracle is an incredibly large company that hires professionals in a wide range of niches. Since that’s the case, you could face different questions depending on the type of job you’re trying to land. After all, they won’t ask a financial professional coding questions, as that’s not relevant to the role.

However, since Oracle is mainly a technology company, we’re going to focus on the technical professions. With that in mind, here’s a look at our top five Oracle interview questions and answers.

1. Tell me about your experience with Oracle.

When you’re interviewing for a company that’s known for specific products or services, there’s a good chance the hiring manager is going to ask about your past experience with the company’s offerings. Ideally, you’ll be able to discuss moments from your work history involving the product or service.

Academic experience is also fine, particularly if you’re looking to land an entry-level opportunity. Just make sure you can discuss a few specifics.

EXAMPLE ANSWER:

“As a recent graduate, most of my experience with Oracle has been academic. Since my major focused on database management and administration, several courses dove into the technology. Additionally, I got hands-on time with Oracle while completing various projects, including group and individual assignments. However, that isn’t my only experience with Oracle. While working as an intern at a local company, I handled database-related tasks regularly. That included updating data, initiating queries, and similar responsibilities.”

2. What’s something unique that you feel you can bring to Oracle?

This is a question designed to learn more about your personality or unconventional experience. Usually, you have two choices.

First, you can provide a trait-heavy answer, highlighting a few standout characteristics that you feel are potentially uncommon at Oracle and discussing how they’ll help you thrive. Second, you can go over an unconventional work, or personal experience that you feel may serve you well.

“One thing that makes me unique is my ability to communicate incredibly complex ideas to those without a technical background. The reason I’m skilled in this arena is that I have experience outside of the tech space. Before I began studying database administration and data management, I explored a wide range of fields. That allows me to relate technical ideas to activities I performed in those other roles, making it easier to find common ground with professionals in other niches. Essentially, I can make connections between the concept I need to describe and something they’re already familiar with, shortening the learning curve dramatically. Since Oracle is a technology company but has relationships with businesses and customers in a wide array of industries, I believe that’s a capability that can serve the company well.”

3. Can you describe aggregate functions in Oracle? What are some common ones?

This question is a knowledge check, allowing the hiring manager to see how much you understand about Oracle in a technical sense. Generally, you’ll simply want to outline what’s requested as a starting point, then give a little extra detail that shows an understanding beyond what’s asked.

“An aggregate function essentially joins values from multiple rows together, creating a single output that serves as a summary. For example, ‘average’ is an aggregate function, allowing you to get the mean value of a column over a select number of rows. ‘Count’ and ‘sum’ are also aggregate functions, giving you the number of non-NULL data points in a column and the sum total of the value of the data points in a column, respectively. Generally, aggregate functions are used in conjunction with GROUP BY or HAVING clauses. That separates the rows into groups or filters them to ensure relevancy, allowing you to limit the data points factored into the calculation.”

4. Tell me how you would store images in an Oracle database.

While this is another knowledge check, how you approach your answer will be a little different. Instead of definitions, you need to outline your general process.

Typically, you won’t need to go keystroke by keystroke when you describe what you would do. Instead, outline your general approach, any constraints, and similar points to round out your response.

“If I needed to store images in an Oracle database, my first step is to determine if a new table is necessary. A table can only have one LONG column, and since images require the LONG RAW datatype, I need to confirm if another LONG column is already in place. Once I know whether a LONG column exists, I can either insert the needed column into an existing table or create the new one. With the latter, I may need to replicate certain data points from the original table and use the INNER JOIN clause to forge a connection, suggesting that the images need to connect to the information contained in another table. At that point, the images can be stored in the appropriate column.”

5. Create a query to learn the average cost of a service listed in a table.

This is closer to a technical interview question, as it asks you to essentially write out a query. If the prompt is presented in this manner, you’ll usually need to provide a few details first. Then, you can create the query.

“If I needed to find the average cost of a service listed in a table, I would use the average function and GROUP BY clause to get the needed information. If the table was called ‘Services’ and the column containing the cost information was called “Price,” the query would be ‘SELECT AVG(Price) FROM Services GROUP BY Price;’ as that should deliver the needed information.”

70 More Oracle Interview Questions

Here are 70 more Oracle interview questions:

• Why do you want to work for Oracle?
• What do you know about our company?
• Why did you choose a career in technology?
• How do you handle pressure or stress while on the job?
• Do you have any coding preferences?
• Tell me about your experience with SQL.
• Oracle was developed in what language?
• What’s a snapshot in Oracle?
• Can you tell me the components of an Oracle physical database structure?
• In regards to the Oracle database, what is a tablespace?
• What are the default tablespaces in Oracle?
• What’s the difference between the varchar and varchar2 data types?
• How do online and offline tablespaces differ?
• What is the RAW datatype?
• Tell me about the memory layers in an Oracle shared pool?
• What is an NVL function for?
• What’s a save point in an Oracle database?
• Give a quick overview of the Oracle database objects.
• What is the purpose of the ANALYZE command in Oracle?
• What are the most common Oracle forms modules?
• Can you describe the logical backup mechanism in Oracle?
• What are synonyms in Oracle databases?
• Tell me about recursive hints.
• What are the limitations of the CHECK constraint?
• Tell me the difference between JVM, JRE, and JDK.
• Describe your experience with REST API.
• How would you use nested tables?
• How are comments represented in Oracle?
• What’s the difference between REPLACE and TRANSLATE?
• How do you display table rows while ensuring there aren’t any duplicates?
• What does the NULL value represent in Oracle?
• What are merge statements used for?
• Give me an example of a USING clause.
• What does a GROUP BY clause do?
• Describe what a subquery is and outline the different types of subqueries in Oracle.
• What is cross join?
• List the temporal datatypes and what they do in Oracle.
• How are privileges created in Oracle?
• Tell me what VArray is.
• What’s the difference between alias and rename?
• Tell me what a View is.
• What are cursor variables? What about cursor attributes?
• What would you do if you needed to delete duplicate rows in a table?
• How are hash clusters used?
• What is the purpose of alerts in Oracle?
• How do you see the current date and time associated with the operating system where a database is running?
• What are actual parameters, and what are they used for?
• How do formal parameters differ from actual parameters?
• Tell me about your experience with the Agile methodology?
• Have you ever led a project? What was that experience like?
• Which part of your professional experience do you feel makes you particularly well suited to this job?
• If you had a conflict with a colleague, how would you work to resolve it?
• What style of manager do you prefer?
• Do you prefer independent work or handling responsibilities with a team?
• If you had to sell Oracle to a CEO, what benefits would you highlight?
• Is there anything you dislike about using Oracle databases? How would you improve the experience to address those issues?
• How does Oracle stand apart from its competitors?
• Do you think there are upcoming opportunities in the market that will help Oracle grow?
• How do you stay on top of technology trends?
• What’s the maximum number of triggers that can be applied to one table?
• How do you typically fit into a team dynamic?
• How do you view the last record added to a table?
• Are you multilingual? If so, are you comfortable speaking with international teams in languages other than English?
• Tell me about your experience with Java. What about Python?
• If you had to convert the numbers on checks to words, how would you create a program to do that?
• How do you respond to constructive criticism?
• How would your past colleagues describe you as a professional? What about your last manager?
• If you didn’t choose a job in technology, what field would you explore instead?
• Do you have plans to further your education?
• If you don’t land this job, would you go after another position at Oracle if one comes available?

5 Good Questions to Ask at the End of an Oracle Interview

When you reach the end of your interview, you’ll typically get a chance to ask the hiring manager a few questions. This is a chance to learn more about the hiring process, role, company culture, and more, so don’t let it pass you by.

While you can ask questions based on points that occur to you during the interview, having a few in your back pocket is also a wise move. If you aren’t sure what to bring up, here are five good questions to ask at the end of an Oracle interview.

• Can you describe a typical day in this role?
• Since Oracle is an ever-evolving technology company, how do you envision this role changing over time?
• How does Oracle use education or training opportunities to ensure its teams are up-to-date?
• What initially attracted you to Oracle? Do you feel that the company met your expectations?
• How supportive is Oracle of employees that want to go back to school while continuing to work?

Putting It All Together

Ultimately, answering interview questions for Oracle can be intimidating, but you can nail them with some preparation. Use all of the tips and information above to your advantage. That way, when you’re tackling the Oracle interview questions, you can be sure to impress.

Co-Founder and CEO of TheInterviewGuys.com. Mike is a job interview and career expert and the head writer at TheInterviewGuys.com.

His advice and insights have been shared and featured by publications such as Forbes , Entrepreneur , CNBC and more as well as educational institutions such as the University of Michigan , Penn State , Northeastern and others.

Learn more about The Interview Guys on our About Us page .

About The Author

Mike simpson.

Co-Founder and CEO of TheInterviewGuys.com. Mike is a job interview and career expert and the head writer at TheInterviewGuys.com. His advice and insights have been shared and featured by publications such as Forbes , Entrepreneur , CNBC and more as well as educational institutions such as the University of Michigan , Penn State , Northeastern and others. Learn more about The Interview Guys on our About Us page .

Copyright © 2024 · TheInterviewguys.com · All Rights Reserved

• Our Products
• Case Studies
• Interview Questions
• Jobs Articles
• Members Login

Introduction to SQL

• What is a Database? Definition, Types and Components
• What is SQL and how to get started with it?
• SQL Basics – One Stop Solution for Beginners
• What are SQL Operators and how do they work?
• Understanding SQL Data Types – All You Need To Know About SQL Data Types
• SQL Tutorial : One Stop Solution to Learn SQL
• DBMS Tutorial : A Complete Crash Course on DBMS
• CREATE TABLE in SQL – Everything You Need To Know About Creating Tables in SQL

What is a Schema in SQL and how to create it?

• What is a Cursor in SQL and how to implement it?
• Top 10 Reasons Why You Should Learn SQL

Learn how to use SQL SELECT with examples

Sql functions: how to write a function in sql, what is sql regex and how to implement it.

• SQL UPDATE : Learn How To Update Values In A Table
• SQL Union – A Comprehensive Guide on the UNION Operator
• Triggers in SQL – Learn With Examples
• INSERT Query SQL – All You Need to Know about the INSERT statement
• How To Use Alter Table Statement In SQL?
• What is Normalization in SQL and what are its types?
• How to perform IF statement in SQL?
• What are SQL constraints and its different types?

Learn How To Use CASE Statement In SQL

• Primary Key In SQL : Everything You Need To Know About Primary Key Operations
• Foreign Key SQL : Everything You Need To Know About Foreign Key Operations
• SQL Commands - A Beginner's Guide To SQL

How to Change Column Name in SQL?

• How to retrieve a set of characters using SUBSTRING in SQL?
• What is the use of SQL GROUP BY statement?
• How To Use ORDER BY Clause In SQL?
• How to use Auto Increment in SQL?
• Everything You Need to Know About LIKE Operator in SQL
• What is an index in SQL?
• Understanding SQL Joins – All You Need To Know About SQL Joins
• Differences Between SQL & NoSQL Databases – MySQL & MongoDB Comparison
• What is Database Testing and How to Perform it?
• SQL Pivot – Know how to convert rows to columns

Introduction To MySQL

• What is MySQL? – An Introduction To Database Management Systems
• How To Install MySQL on Windows 10? – Your One Stop Solution To Install MySQL
• MySQL Tutorial - A Beginner's Guide To Learn MySQL
• MySQL Data Types – An Overview Of The Data Types In MySQL
• How To Use CASE Statement in MySQL?
• DECODE in SQL – Syntax of Oracle Decode Function

What are basic MongoDB commands and how to use them?

• SSIS Tutorial For Beginners: Why, What and How?
• Learn About How To Use SQL Server Management Studio
• SQLite Tutorial: Everything You Need To Know
• What is SQLite browser and how to use it?
• MySQL Workbench Tutorial – A Comprehensive Guide To The RDBMS Tool

Postgre SQL

• PostgreSQL Tutorial For Beginners – All You Need To Know About PostgreSQL
• PL/SQL Tutorial : Everything You Need To Know About PL/SQL
• Learn How To Handle Exceptions In PL/SQL

SQL Interview Questions

• Top 115 SQL Interview Questions and Answers for 2024

Top 50 MySQL Interview Questions You Must Prepare In 2024

• Top 50 DBMS Interview Questions You Need to know in 2024

Top 50 Oracle Interview Questions You Should Master in 2024

Knowledge of SQL is a must because the demand for SQL-expertise is high and is valued in the market. Oracle is a very popular secured database that is widely used across multinational companies. So, this article on Oracle interview questions will cover the most frequently asked interview questions and help you to brush up your knowledge before the interview.

If you are a fresher or an experienced, this is the right platform for you which will help you to start your preparation.

Let’s begin by taking a look at the most frequently asked questions.

Oracle Basic Interview Questions

• PL/SQL Interview Questions

So, let’s begin!

Q1. How will you differentiate between varchar & varchar2 Q2. What are the components of logical database structure in Oracle database?

Upskill for Higher Salary with SQL Certification Training Course

Q3. Describe an Oracle table Q4. Explain the relationship among database, tablespace and data file? Q5. What are the various Oracle database objects? Q6. Explain about the ANALYZE command in Oracle? Q7. What types of joins are used in writing subqueries? Q8. RAW datatype in Oracle Q9. What is the use of Aggregate functions in Oracle? Q10. Explain Temporal data types in Oracle

Q1. How will you differentiate between Varchar & Varchar2?

Both Varchar & Varchar2 are the Oracle data types which are used to store character strings of variable length. To point out the major differences between these,

Q2. What are the components of logical database structure in Oracle database?

The components of the logical database structure in Oracle database are:

Tablespaces : A database mainly contains the Logical Storage Unit called tablespaces . This tablespace is a set of related logical structures. To be precise, tablespace groups are related to logical structures together.

Database schema objects: A schema is a collection of database objects owned by a specific user. The objects include tables, indexes, views, stored procedures, etc. And in Oracle, the user is the account and the schema is the object. It is also possible in the database platforms to have a schema without a user specified.

Q3. Describe an Oracle table

A table is a basic unit of data storage in the Oracle database. A table basically contains all the accessible information of a user in rows and columns.

To create a new table in the database, use the “CREATE TABLE” statement. First, you have to name that table and define its columns and datatype for each column.

CREATE TABLE table_name ( column1 datatype [ NULL | NOT NULL ], column2 datatype [ NULL | NOT NULL ], … column_n datatype [ NULL | NOT NULL ] );

• table_name: This specifies the name of the table that you want to create.
• column..n: It specifies the number of columns which you want to add in the table. Here, every column must have a datatype and should either be defined as “NULL” or “NOT NULL”. If in case, the value is left blank, it is treated as “ NULL ” as default.

Q4. Explain the relationship among database, tablespace and data file?

An Oracle database possesses one or more logical storage units called tablespaces . Each tablespace in Oracle database consists of one or more files called the datafiles. These tablespaces collectively store the entire data of databases. Talking about the datafiles, these are the physical structure that confirms with the operating system as to which Oracle program is running.

Q5. What are the various Oracle database objects?

These are the Oracle Database Objects:

Tables: This is a set of elements organized in a vertical and horizontal manner. Tablespaces: It is a logical storage unit in Oracle. Views: Views are a virtual table derived from one or more tables. Indexes: This is a performance tuning method to process the records. Synonyms: It is a name for tables.

Q6. Explain about the ANALYZE command in Oracle?

This “Analyze” command is used to perform various functions on index, table, or cluster. The following list specifies the usage of ANALYZE command in Oracle:

• Analyze command is used to identify migrated and chained rows of the table or a cluster.
• It is used to validate the structure of an object.
• This helps in collecting the statistics about the object used by the user and are then stored on to the data dictionary.
• It also helps in deleting statistics that are used by an object from the data dictionary.

Q7. What types of joins are used in writing subqueries?

A Join  is used to compare and combine, this means literally join and return specific rows of data from two or more tables in a database.

There are three types of joins in SQL that are used to write the subqueries.

• Self Join : This is a join in which a table is joined with itself, especially when the table has a foreign key which references its own primary key.
• Outer Join: An outer join helps to find and returns matching data and some dissimilar data from tables.
• Equi-join: An equijoin is a join with a join condition containing an equality operator . An equijoin returns only the rows that have equivalent values for the specified columns.

Q8. RAW datatype in Oracle

The RAW datatype in Oracle is used to store variable-length binary data or byte string values . The maximum size for a raw in a given table in 32767 bytes.

You might get confused as to when to use RAW, varchar, and varchar2. Let me point out the major differences between them. PL/SQL does not recognize the data type and hence, it cannot have any conversions when RAW data is transferred to different systems. This data type can only be queried or can be inserted in a table.

Q9. What is the use of Aggregate functions in Oracle?

An aggregate function in Oracle is a function where values of multiple rows or records are joined together to get a single value output. It performs the summary operations on a set of values in order to provide a single value. There are several aggregate functions that you can use in your code to perform calculations. Some common Aggregate functions are:

Q10. Explain Temporal data types in Oracle

Oracle mainly provides these following temporal data types:

• Date Data Type: Different formats of Dates.
• TimeStamp Data Type: Has different formats of Time Stamp .
• Interval Data Type: Interval between dates and time.

Q11. What is a View?

A view is a logical table based on one or more tables or views. A View is also referred as a user-defined database object that is used to store the results of a SQL query, that can be referenced later in the course of time. Views do not store the data physically but as a virtual table, hence it can be referred as a logical table. The corresponding tables upon which the views are signified are called Base Tables and this doesn’t contain data.

Q12. How to store pictures on to the database?

It is possible to store pictures on to the database by using Long Raw Data type. This data type is used to store binary data of length 2GB. Although, the table can have only on Long Raw data type.

Q13. Where do you use DECODE and CASE Statements?

Both these statements Decode and Case will work similar to the if-then-else statement and also they are the alternatives for each of them. These functions are used in Oracle for data value transformation.

Decode function

Select OrderNum, DECODE (Status,’O’, ‘Ordered’,’P’, ‘Packed,’ S’,’ Shipped’, ’A’,’Arrived’) FROM Orders;

Case function

Select OrderNum , Case(When Status=’O’ then ‘Ordered’ When Status =’P’ then Packed When Status=’ S’ then ’Shipped’ else ’Arrived’) end FROM Orders;

Both these commands will display Order Numbers with their respective Statuses like this,

Status O= Ordered Status P= Packed Status S= Shipped Status A= Arrived

Q14. What do you mean by Merge in Oracle and how can you merge two tables?

Merge statement is used to merge the data from two tables subsequently. It selects the data from the source table and then inserts/updates it in the other table based on the condition provided in the query. It is also useful in data warehousing applications. 

Q15. What is the data type of DUAL table?

The Dual table is basically a one-column table that is present in the Oracle database. This table has a single Varchar2(1) column called Dummy which has a value of ‘X’.

Q16. Explain about integrity constraint?

An integrity constraint is actually a declaration that is defined as a business rule for a table column. They are used to ensure accuracy and consistency of data in the database. It can also be called as a declarative way to define a business rule for a table’s column. There are a few types, namely:

• Domain Integrity
• Referential Integrity

Q17. What is SQL and also describe types of SQL statements?

SQL stands for  Structured Query Language . SQL is used to communicate with the server in order to access, manipulate and control data. There are 5 different types of SQL statements available. They are:

• Select: Data Retrieval
• Insert, Update, Delete, Merge : Data Manipulation Language (DML)
• Create, Alter, Drop, Rename, Truncate : Data Definition Language (DDL)
• Commit, Rollback, Savepoint: Transaction Control Statements
• Grant, Revoke : Data Control Language (DCL)

Q18. Briefly explain what is Literal? Give an example where it can be used?

A Literal is a string that contains a character, a number, or a date that is included in the Select list and which is not a column name or a column alias.

Also note that, Date and character literals must be enclosed within single quotes (‘ ‘), whereas you don’t have to do that for the number literals.

For example: Select last_name||’is a’||job_id As “emp details” from employee;

In this case, “is a” is literal.

Q19. How to display row numbers with the records?

In order to display row numbers along with their records numbers you can do this:

This above query will display the row numbers and the field values from the given table.

This query will display row numbers and the field values from the given table.

Q20. What is the difference between SQL and iSQL*Plus?

Q21. What are SQL functions? Describe in brief different types of SQL functions?

SQL Functions are a very powerful feature of SQL. These functions can take arguments but always return some value. There are two distinct types of SQL functions available. They are:

• Single-Row functions:   These functions operate on a single row to give one result per row.

Types of Single-Row functions are:

• Multiple-Row functions:   These functions operate on groups of rows to give one result per group of rows.

Types of Multiple-Row functions:

Q22. Describe different types of General Function used in SQL?

General functions are of following types:

• NVL:   Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null then NVL function return value of exp2.
• NVL2:   If exp1 is not null, nvl2 returns exp2, if exp1 is null, nvl2 returns exp3. The argument exp1 can have any data type. NVL2 (exp1, exp2, exp3)
• NULLIF:   Compares two expressions and returns null if they are equal or the first expression if they are not equal. NULLIF (exp1, exp2)
• COALESCE:   Returns the first non-null expression in the expression list. COALESCE (exp1, exp2… expn). The advantage of the COALESCE function over NVL function is that the COALESCE function can take multiple alternative values.
• Conditional Expressions:   Provide the use of IF-THEN-ELSE logic within a SQL statement. Example: CASE Expression and DECODE Function.

Q23. What is a Sub Query? Describe its Types ?

A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. A subquery can be placed in where having and from clause.

Guidelines for using subqueries:

• You should enclose the sub-queries within parenthesis.
• Place these subqueries on the right side of the comparison condition.
• Use Single-row operators with single-row subqueries.
• Use Multiple-row operators with multiple-row subqueries.

Types of subqueries:

• Single-Row Subquery:   Queries that return only one row from the inner select statement. Single-row comparison operators are: =, >, >=, <, <=, <>
• Multiple-Row Subquery:   These are queries that return more than one row from the inner Select statement. You will also find multiple-column subqueries that return more than one column from the inner select statement. Operators include: IN, ANY, ALL.

Q24. What is the use of Double Ampersand (&&) in SQL Queries? Give an example

You can use && if you want to reuse the variable value without prompting the user each time.

For example: Select empno, ename, &&column_name from employee order by &column_name;

Q25. Describe VArray

VArray is basically an Oracle data type used to have columns containing multivalued attributes and it can hold a bounded array of values. All Varrays consist of contiguous memory locations. The lowest address corresponds to the first element and the highest address to the last element.

Each element in a Varray has an index associated with it. It has a maximum size (max_size) that can be changed dynamically.

Q26. What are the attributes of the Cursor?

Each Cursor in Oracle has a set of attributes that enables an application program to test the state of the Cursor. The attributes can be used to check whether the cursor is opened or closed, found or not found and also find row count.

Q27. Name the various constraints used in Oracle

These are the following constraints used:

• NULL : It is to indicate that a particular column can contain NULL values.
• NOT NULL: It is to indicate that particular column cannot contain NULL values.
• CHECK : Validate that values in the given column to meet the specific criteria.
• DEFAULT : It is to indicate the value is assigned to a default value.

Q28. What is the fastest query method to fetch data from the table?

The fastest query method to fetch data from the table is by using the Row ID. A row can be fetched from a table by using RowID.

Q29. Difference between Cartesian Join and Cross Join?

There are no such differences between these Joins. Cartesian and Cross join are the same.

Cross join gives a cartesian product of two tables i.e., the rows from the first table is multiplied with another table that is called cartesian product.

Cross join without the where clause gives a Cartesian product .

Q30. How does the ON-DELETE-CASCADE statement work?

Using this On Delete Cascade you can automatically delete a record in the child table when the same record is deleted from the parent table. This statement can be used with Foreign Keys as well.

You can add this On Delete Cascade option on an existing table.

Alter Table Child_T1 ADD Constraint Child_Parent_FK References Parent_T1(Column1) ON DELETE CASCADE;

Now let’s move on to the next part of this Oracle Interview Questions article.

Oracle PL/SQL Interview Questions

Q31. What is PL SQL?

PL/SQL is an extension of Structured Query Language (SQL) that is used in Oracle. It combines the data manipulation power of SQL with the processing power of procedural language in order to create super-powerful SQL queries . PL SQL means instructing the compiler what to do through SQL and how to do it through its procedural way.

Q32. Enlist the characteristics of PL/SQL?

There are a lot of characteristics of PL/SQL. Some notable ones among them are:

• PL/SQL is a block-structured language.
• It is portable to all environments that support Oracle.
• PL/SQL is integrated with the Oracle data dictionary.
• Stored procedures help better sharing of application.

Q33. What are the data types available in PL/SQL?

There are two data types available in PL/SQL. They are namely:

• Scalar data types

Example: Char, Varchar, Boolean, etc.

• Composite datatypes

Example: Record, table etc.

Q34. What are the uses of a database trigger

Triggers are the programs which are automatically executed when some events occur:

• Implement complex security authorizations.
• Drive column values.
• Maintain duplicate tables.
• Implement complex business rules.
• Bring transparency in log events.  

Q35. Show how functions and procedures are called in a PL SQL block

A Procedure  can have a return statement to return the control to the calling block, but, it cannot return any values through the return statement. They cannot be called directly from Select statements but they can be called from another block or through EXEC keyword.

The procedure can be called in the following ways: a) CALL <procedure name> direc b) EXCECUTE <procedure name> from calling  environment c) <Procedure name> from other procedures or functions or packages

Functions can be called in the following ways a) Execute<Function name> from calling environment. Always use a variable to get the return value. b) As part of an SQL/PL SQL Expression

Q36. What are the two virtual tables available at the time of database trigger execution?

Columns are referred as Then.column_name and Now.column_name.

• INSERT related triggers, Now.column_name values are available only.
• DELETE related triggers, Then.column_name values are available only.
• UPDATE related triggers, both Table columns are available.

Q37. What are the differences between Primary Key and Unique Key?

Q38. Explain the purpose of %TYPE and %ROWTYPE data types with the example?

%ROWTYPE and %TYPE are the attributes in PL/SQL which can inherit the datatypes of a table that are defined in a database. The main purpose of using these attributes in Oracle is to provide data independence and integrity. Also note that, if any of the datatypes gets changed in the database, PL/SQL code gets updated automatically including the change in the datatypes.

%TYPE:  This is used for declaring a variable that needs to have the same data type as of a table column. %ROWTYPE: This is used to define a complete row of record having a structure similar to the structure of a table.

Q39. Explain the difference between Triggers and Constraints?

Triggers are very different from Constraints in the following ways:

Q40. Exception handling in PL/SQL

When an error occurs in PL/SQL, the corresponding exception is raised. This also means, to handle undesired situations where PL/SQL scripts gets terminated unexpectedly, error-handling code is included in the program. In PL/SQL, all exception handling code is placed in the Exception section.

There are 3 types of Exceptions:

• Predefined Exceptions:   Common errors with predefined names.
• Undefined Exceptions:   Less common errors with no predefined names.
• User-defined Exceptions:   Do not cause runtime error but violate business rules.

Comparison based Interview Questions 

Q41. What is the difference between COUNT (*), COUNT (expression), COUNT (distinct expression)?

COUNT (*): This returns a number of rows in a table including the duplicates rows and the rows containing null values in the columns. COUNT (EXP): This returns the number of non-null values in the column identified by an expression. COUNT (DISTINCT EXP): It returns the number of unique , non-null values in the column identified by an expression.

Q42. Difference between the “VERIFY” and “FEEDBACK” command?

The major differences between Verify and Feedback commands are:

Verify Command: You can use this command to confirm the changes in the SQL statement which can have old and new values that are defined with Set Verify On/OFF. Feedback Command : It displays the number of records that are returned by a query.

Q43. List out the difference between Commit, Rollback, and Savepoint?

The major differences between these are listed below:

• Commit : This ends the current transaction by ensuring that all pending data changes are made permanent.
• Rollback : This ends the current transaction by discarding or deleting all pending data changes.
• Savepoint : It divides a transaction into smaller parts. You can rollback the transaction until you find a particular named savepoint.

Q44. What is the difference between SUBSTR and INSTR?

SUBSTR returns a specific portion of a string whereas INSTR provides the character position in which a pattern is found in a string . SUBSTR returns string whereas INSTR returns numeric values.

Q45. Point out the difference between USER TABLES and DATA DICTIONARY?

User Tables: This is a collection of tables created and maintained by the user. It also contains user information. Data dictionary: This is a collection of tables that are created and maintained by the Oracle Server. It contains database information. All data dictionary tables are owned by the SYS user .

Q46. Major difference between Truncate and Delete?

Q47. Point the difference between TRANSLATE and REPLACE?

Translate is used for character by character substitution whereas Replace is used to substitute a single character with a word.

Q48. What is the difference between $ORACLE_BASE and $ORACLE_HOME?

$Oracle_base is the main or root directory of Oracle whereas Oracle_Home is located beneath the base folder in which all Oracle products reside.

Q49. What do you understand by Redo Log file mirroring?

Mirroring is a process of having a copy of Redo log files. This is done by creating a group of log files altogether. It ensures that the LGWR automatically writes it to all the members of the current on-line redo log group. If the group fails, the database automatically switches over to the next group and it diminishes the performance of the database.

Q50. What is the difference between a hot backup and a cold backup in Oracle? Explain about their benefits as well

Hot backup (Online Backup):   A hot backup is also known as an online backup because it is done while the database is active. Some sites can’t shut down their database while making a backup copy and they are used 24*7. Cold backup (Offline Backup):   A cold backup is also known as an offline backup because it is done while the database has been shut down using the SHUTDOWN command. If the database is suddenly shutdown with an uncertain condition, it should be restarted with RESTRICT mode and then shutdown with the NORMAL option. For a complete cold backup , the corresponding files must be backed up i.e., all datafiles, All control files, All online redo log files and the init.ora file (you can recreate it manually).

I hope this set of Oracle Interview Questions will help you in preparing for your interviews. All the best!

Also, check out our website Edureka , for more exciting technologies and career guide for a noob and professionalists. Edureka is a trusted online learning company with a network of more than 250,000 satisfied learners spread across the globe.If you wish to learn more about SQL, then check out our  SQL Training .

  Got a question for us? Please mention it in the comments section of this “Oracle Interview Questions” article and we will get back to you.

Recommended videos for you

Build application with mongodb, introduction to mongodb, recommended blogs for you, top 50 sql server interview questions you must prepare in 2024, sql views: how to work with views in sql, insert query sql – all you need to know about the insert statement, everything you need to know about mongodb client, mongodb: the database for big data processing, how to create stored procedures in sql, introduction to cassandra architecture, mysql workbench tutorial – a comprehensive guide to the rdbms tool, sql server tutorial – everything you need to master transact-sql, how to install mysql on windows 10 – your one stop solution to install mysql, face off: mongodb vs hbase vs cassandra, choosing the right nosql database, differences between sql & nosql databases – mysql & mongodb comparison, join the discussion cancel reply, trending courses in databases, microsoft sql course.

• 4k Enrolled Learners

SQL Essentials Training

• 12k Enrolled Learners
• Weekend/Weekday

MongoDB Certification Training Course

• 17k Enrolled Learners

MySQL DBA Certification Training

• 7k Enrolled Learners

Apache Cassandra Certification Training

• 13k Enrolled Learners

Teradata Certification Training

• 3k Enrolled Learners

Browse Categories

Subscribe to our newsletter, and get personalized recommendations..

Already have an account? Sign in .

20,00,000 learners love us! Get personalised resources in your inbox.

At least 1 upper-case and 1 lower-case letter

Minimum 8 characters and Maximum 50 characters

We have recieved your contact details.

You will recieve an email from us shortly.

30 Oracle Database Developer Interview Questions and Answers

Common Oracle Database Developer interview questions, how to answer them, and example answers from a certified career coach.

In the vast realm of database development, Oracle stands as a titan with its robust and intricate architecture. As an Oracle Database Developer, you understand that mastering this complex system requires a blend of technical know-how, problem-solving skills, and attention to detail. But before you can showcase your expertise in managing and manipulating data sets in an Oracle environment, you must first conquer the job interview.

To help you succeed in presenting your skills, experience, and problem-solving abilities during your upcoming Oracle Database Developer interview, we’ve assembled a list of potential questions and effective strategies for answering them. Let’s delve into the world of Oracle and prepare you to ace that interview!

1. Can you describe your experience with Oracle Database Development?

The essence of this question lies in the interviewer’s desire to understand your technical skills and hands-on experience. They want to gauge your familiarity with Oracle’s database development tools, your ability to handle complex database tasks, and your problem-solving skills in real-world scenarios. Your answer to this question will highlight your proficiency and readiness to dive into the technical responsibilities this role requires.

Example: “I have extensive experience with Oracle Database Development, including designing and implementing databases. I’ve worked on creating complex PL/SQL modules, tuning SQL statements for performance optimization, and handling database security.

In terms of data modeling, I’m proficient in using tools like ERWin and TOAD Data Modeler. My projects often involved working with large datasets and ensuring efficient data retrieval.

My expertise also extends to Oracle’s development tools such as Oracle Forms and Reports, APEX, and SQL Developer. I’ve used these tools to create user-friendly interfaces and generate comprehensive reports.

Overall, my skills allow me to develop robust, optimized Oracle databases that meet business needs.”

2. How have you utilized PL/SQL in your previous roles?

Hiring managers ask this question to gauge your hands-on experience with PL/SQL, which is a cornerstone of Oracle database development. It helps them understand how well you can leverage this powerful tool to develop robust, high-performing databases and applications. They’re keen to know if you can write, debug, and manage PL/SQL programs to support the company’s database infrastructure and development initiatives.

Example: “In my experience, I have used PL/SQL extensively for various tasks. For instance, I’ve written stored procedures and functions to encapsulate business logic within the database itself, making it easier to manage and maintain.

I also utilized PL/SQL packages to group related procedures and functions together, providing modularity and ease of use.

To handle large volumes of data efficiently, I’ve implemented bulk collect and forall statements, reducing the context switching between SQL and PL/SQL engines significantly.

Moreover, I leveraged exception handling features in PL/SQL to catch and handle errors effectively, ensuring smooth execution of applications.

Overall, these practices enhanced performance and made the code more robust and easy to understand.”

3. What strategies do you use for effective database design?

Being an Oracle Database Developer is all about creating and maintaining efficient databases. The design phase is particularly critical as it determines the structure and function of the database down the line. Therefore, interviewers want to understand your approach to database design, particularly in terms of ensuring data integrity, security, and performance, all of which are key to meeting business needs.

Example: “In designing a database, I start by clearly defining the purpose of the database and understanding its requirements. This involves identifying key entities, relationships among these entities, and their attributes.

I use normalization to eliminate data redundancy and improve data integrity. However, in some cases, I may also use denormalization for performance optimization.

Choosing appropriate data types and constraints is crucial to ensure data accuracy and consistency.

Indexing strategies are also important for improving query performance. But it’s essential to strike a balance as excessive indexing can slow down data modification operations.

Finally, I follow best practices like using consistent naming conventions, documenting the design thoroughly, and regularly reviewing and optimizing the design based on changing business needs or performance metrics.”

4. Can you describe a time when you had to optimize a poorly performing database?

Optimization is a critical skill in database management. Databases can slow down due to a variety of reasons such as bad design, poor indexing, or inefficient queries. It’s essential for a database developer to be able to identify the causes of poor performance and implement solutions. This question is asked to gauge your problem-solving skills and your technical competence in improving the performance of a database.

Example: “At one company, we had a database that was performing poorly due to inefficient indexing. Users were experiencing slow response times and the system was frequently timing out.

I started by analyzing the SQL queries and found several full table scans which were causing performance issues. I then used Oracle’s Explain Plan tool to understand how the queries were being executed.

After identifying the problem areas, I implemented index optimization strategies such as bitmap indexes for low cardinality columns and B-tree indexes for high cardinality columns. This significantly improved query performance.

Post-optimization, I also set up regular monitoring and maintenance tasks to ensure optimal performance in the future. This experience taught me the importance of proactive database management.”

5. How would you handle a situation where you had to recover lost data?

The digital world runs on data, and the loss of it can lead to catastrophic outcomes. As a database developer, you’re the first line of defense in preventing data loss and the last hope in recovering it if something does go awry. Hiring managers want assurance that you not only understand the technical aspects of data recovery but also have the problem-solving skills and poise to handle such high-stress situations.

Example: “In a scenario where data loss occurs, I would first identify the cause of the issue. This could be due to hardware failure, software bugs, or human error.

Once identified, I’d implement the appropriate recovery method. If the lost data is due to accidental deletion, Oracle’s Flashback technology can recover it quickly.

For more severe cases like database corruption, I’d use Recovery Manager (RMAN) for point-in-time recovery. It’s crucial to have regular backups and maintain updated logs to facilitate this process.

Post-recovery, I’d conduct an analysis to prevent future occurrences. This might involve improving backup strategies, updating security measures, or providing additional training to staff.”

6. Describe your experience with database security. What measures have you implemented?

Database security is absolutely paramount in the sphere of Oracle Database Development. As such, hiring managers want to ensure that potential hires are well-versed in implementing robust security measures. From preventing unauthorized access to protecting sensitive information, it’s critical for developers to have a solid grasp on how to safeguard the company’s databases. This not only protects the company’s data but also ensures compliance with various data protection regulations.

Example: “In my experience with database security, I’ve implemented several measures to ensure data integrity and confidentiality. One key strategy was the use of encryption for both data at rest and in transit. This helped protect sensitive information from unauthorized access.

I also utilized strong user authentication methods, limiting privileges based on roles to minimize potential breaches. Regular audits were conducted to monitor any unusual activities or discrepancies.

Furthermore, I ensured regular backups and updates were performed to prevent data loss and maintain system robustness against emerging threats. These strategies are all integral parts of maintaining a secure Oracle database environment.”

7. How proficient are you with Oracle’s built-in packages?

The essence of this question is to assess your technical skills. Oracle’s built-in packages are essential tools for Oracle Database Developers. They demonstrate your ability to perform complex tasks, automate processes, and improve performance. Therefore, your familiarity with these tools is a direct reflection of your competence and efficiency as a developer.

Example: “I have extensive experience with Oracle’s built-in packages. I am proficient in using PL/SQL collections and records, invoking stored procedures and functions, and managing database triggers.

My expertise extends to DBMS_JOB for job scheduling and UTL_FILE for file management. I’ve also utilized DBMS_STATS for gathering optimizer statistics and DBMS_OUTPUT for debugging purposes.

Moreover, I’m well-versed in handling exceptions through the use of PRAGMA EXCEPTION_INIT and SQLCODE.

In terms of performance tuning, I’ve used DBMS_PROFILER and DBMS_HPROF for identifying bottlenecks in PL/SQL code.

Overall, my proficiency with Oracle’s built-in packages allows me to write efficient and optimized database solutions.”

8. What is your process for identifying and fixing bugs in database procedures?

The question is designed to assess your problem-solving skills and attention to detail, two traits which are vital for an Oracle Database Developer. Debugging is a core part of the job, and employers need to know that you can solve issues quickly and effectively to ensure the smooth running of their databases.

Example: “To identify bugs in database procedures, I first replicate the issue in a controlled environment. This helps to understand the exact conditions that cause the bug.

Then, I analyze the procedure code line by line, using tools like EXPLAIN PLAN for performance issues or DBMS_PROFILER for procedural bottlenecks.

Once the problem is identified, I correct it and retest the procedure under various scenarios to ensure the fix works as expected without causing any unforeseen side effects.

After successful testing, I document the changes made, the reasons behind them, and the results of the tests conducted before deploying the fixed procedure into production.”

9. Can you explain how you’ve used Oracle Data Integrator (ODI)?

As an Oracle Database Developer, your experience with tools like Oracle Data Integrator (ODI) is critical. Interviewers want to gauge your hands-on experience and understand how you’ve utilized ODI to solve real-world problems. Your response will help them assess your technical skills, problem-solving abilities, and your understanding of the tool’s capabilities within the context of their business needs.

Example: “In one of my projects, I used Oracle Data Integrator (ODI) to develop and manage a data warehouse. ODI’s E-LT architecture enabled me to extract, load, and transform large volumes of data efficiently.

The declarative design approach allowed me to define rules for data transformation and integration rather than writing detailed code. This significantly reduced development time and improved performance.

I also utilized ODI’s in-built scheduling feature to automate the execution of interfaces and packages, which ensured timely updates to our data warehouse.

Moreover, ODI’s error management framework was instrumental in handling exceptions and ensuring data integrity. It provided comprehensive logs that helped in identifying and rectifying issues promptly.

Overall, ODI proved to be an invaluable tool in managing complex data integration tasks effectively.”

10. Describe a situation where you had to migrate data from one database to another.

Data migration is a common but critical task that Oracle Database Developers often handle. It involves moving data from one system to another, which can be complex and risky. Interviewers want to know how you’ve handled this type of challenge in the past, how you mitigated risks, ensured data integrity, and what tools or methods you used. Your approach to such tasks can reveal your problem-solving skills, attention to detail, and understanding of best practices in database management.

Example: “In one project, I had to migrate data from a MySQL database to an Oracle database. We used the SQL Developer tool for this task.

Firstly, we connected both databases using JDBC drivers. After establishing connections, we started the migration process by selecting the source and target databases.

Next, we mapped datatypes between two databases. This step was crucial as incorrect mapping could lead to data loss or corruption.

Post-mapping, we migrated schema objects like tables, views, stored procedures etc., followed by actual data transfer.

During the process, we faced challenges with date format mismatches and handled it by customizing the date formats in our script.

Finally, after successful migration, we validated the data integrity and consistency. The entire process required careful planning, execution and validation to ensure no data loss occurred.”

11. How do you ensure the scalability of the databases you develop?

This question is asked because scalability is an integral part of database development. As a potential Oracle Database Developer, you will be expected to design databases that can handle an increasing amount of work in a capable manner. Knowing your approach to ensuring scalability demonstrates your foresight, planning, and technical capabilities in the field. Your response will help interviewers gauge your ability to support the company’s growth and adapt to evolving needs.

Example: “To ensure database scalability, I focus on three main aspects:

1. Designing efficient schema: This involves normalizing the data to reduce redundancy and improve data integrity.

2. Indexing: By creating indexes for frequently accessed columns, we can significantly speed up query performance.

3. Partitioning: Large tables can be partitioned into smaller, more manageable pieces, which improves performance and simplifies maintenance tasks.

I also implement monitoring tools to track system performance over time, enabling proactive identification of potential issues before they become critical. Lastly, understanding the application’s requirements is crucial in designing a scalable database; hence, I always work closely with the development team to understand their needs.”

12. How familiar are you with Oracle Exadata? Can you discuss its benefits and drawbacks?

Knowledge of Oracle Exadata is essential because it’s a popular, comprehensive, and integrated computing platform that combines hardware and software to deliver superior performance for Oracle databases. Your ability to discuss its pros and cons not only showcases your technical knowledge, but also indicates your understanding of how these features may impact business operations, thereby demonstrating your strategic thinking ability.

Example: “I am well-versed with Oracle Exadata, a database machine designed to achieve high performance and scalability for Oracle databases.

The key benefits include improved data warehousing capabilities due to its hybrid columnar compression feature, faster processing speed because of the Smart Flash Cache, and enhanced reliability with Real Application Clusters.

However, it also has drawbacks such as high cost which might not be feasible for small businesses. Also, since it’s highly specialized hardware, finding skilled professionals can be challenging. Lastly, migrating existing databases to Exadata may require significant effort and resources.”

13. What is your strategy for handling database backups and recovery?

Knowing how to handle database backups and recovery isn’t just a technical skill—it’s a critical part of safeguarding a company’s valuable data. Any slight mistake can lead to data loss, and in the worst-case scenario, a total business collapse. Therefore, interviewers want to ensure that you have a reliable strategy in place to prevent such catastrophes and that you can recover data swiftly and efficiently when necessary.

Example: “A robust strategy for database backups and recovery is essential to prevent data loss. I adhere to the 3-2-1 rule: three copies of data, stored on two different media, with one backup offsite.

For Oracle databases, I use RMAN (Recovery Manager) for hot and cold backups, leveraging its block-level corruption detection during backup and restore. It’s also useful for incremental backups, reducing storage needs and improving efficiency.

In terms of recovery, I ensure that the database is in ARCHIVELOG mode for Point-In-Time Recovery. Also, Data Guard can be used for disaster recovery and high availability.

Regular testing of backups and recovery procedures is crucial to ensure they work when needed. Automation of these processes where possible minimizes human error.

Data security is paramount; hence encryption and access controls are implemented for both production and backup data.”

14. Can you discuss your experience with Oracle Data Guard?

Oracle Data Guard is a key feature of Oracle Database and plays a critical role in ensuring data protection, availability, and disaster recovery. Therefore, interviewers ask this question to gauge your proficiency with this tool, your ability to manage and troubleshoot it, and your understanding of how it can be effectively utilized in a business context. They are keen to hire someone who can ensure the company’s data is secure and readily available, even in the face of potential disasters.

Example: “I have extensive experience with Oracle Data Guard, primarily in setting up and managing physical standby databases. I’ve used it for high availability, data protection, and disaster recovery solutions.

My work involved configuring Data Guard to ensure zero data loss at failover, performing switchover operations, and troubleshooting errors during these processes.

I also have experience using the Data Guard Broker for easy management of the environment. My knowledge extends to Active Data Guard for real-time querying on the standby database.

In terms of performance tuning, I’ve optimized redo transport services and SQL apply services to reduce latency. Overall, my proficiency with Oracle Data Guard has been instrumental in maintaining business continuity and minimizing downtime in various scenarios.”

15. How have you used Oracle’s Advanced Queuing (AQ) in your previous projects?

Oracle’s Advanced Queuing (AQ) is a critical feature that allows messaging and information sharing between applications. It’s a key aspect of handling complex database tasks. If you’re interviewing for a position as an Oracle Database Developer, your potential employer needs to know that you understand this feature and can use it effectively. Your response will give them a better understanding of your technical skill level and your ability to handle advanced database functions.

Example: “In one of my projects, we used Oracle’s Advanced Queuing (AQ) to handle asynchronous communication between different components of a distributed system. This was critical for ensuring that the components could function independently and reliably.

We also utilized AQ’s propagation feature to send messages across databases in a multi-database environment. This helped us maintain data consistency and integrity.

Furthermore, we leveraged AQ’s ability to prioritize messages based on their importance. This ensured crucial tasks were handled first, improving overall system efficiency.

Overall, using AQ greatly enhanced our project’s reliability and performance.”

16. Can you explain a situation where you used Oracle’s Partitioning feature to improve performance?

This question is a way to evaluate your practical experience and problem-solving skills. As an Oracle Database Developer, you’re expected to have a deep understanding of the tools and features of the Oracle system, including the Partitioning feature. Using this feature properly can drastically improve the performance of a database. Thus, hiring managers want to see if you can apply this skill effectively to enhance system performance and solve real-world business problems.

Example: “In one of my projects, we had a large table with billions of rows that was frequently queried. The queries were usually range-based on the date column and took a lot of time to execute.

To improve performance, I implemented Oracle’s partitioning feature. I partitioned the table based on the date column using range partitioning. This divided the table into smaller, more manageable pieces, each containing data for a specific date range.

Post-partitioning, when a query was executed, Oracle only scanned the relevant partitions instead of the entire table. This significantly reduced the amount of data read from the disk, leading to faster query execution times.

This use of Oracle’s Partitioning not only improved query performance but also made maintenance tasks like backups and updates more efficient.”

17. How do you handle database version control?

Database version control is critical in ensuring the smooth operation and integrity of a database. It prevents conflicts between different versions of data and allows for easy rollback to a previous version if something goes wrong. Interviewers want to gauge your understanding and experience with database version control, as well as your ability to implement it effectively, given its importance in maintaining the database’s reliability and preventing data loss.

Example: “In handling database version control, I use tools like Liquibase or Flyway. These help track, manage and apply changes to the database schema.

The process begins by writing change scripts in SQL or XML format. These scripts are then committed into a version control system alongside application code for synchronization purposes.

During deployment, these tools check the database to identify which change sets need to be applied, ensuring that the database is always synchronized with the application code.

I also ensure best practices such as keeping all changes backward compatible, testing changes on local databases before committing, and having clear documentation of all database changes.”

18. Can you discuss your experience with Oracle’s Real Application Clusters (RAC)?

Oracle’s Real Application Clusters (RAC) is a key component of database management and optimization. It allows multiple computers to run Oracle RDBMS software simultaneously while accessing a single database. Interviewers ask this question to gauge your hands-on experience with RAC, your understanding of its setup, configuration, and management. This reflects your ability to ensure high availability and performance of the company’s database systems.

Example: “I have extensive experience with Oracle’s Real Application Clusters (RAC), particularly in implementing and managing high-availability solutions. I’ve utilized RAC for load balancing, to ensure uninterrupted access during planned outages, and as a failover solution in case of unexpected downtime.

My expertise includes designing databases that leverage RAC features, such as automatic workload management and fast connection failover. I’ve also worked on performance tuning within a RAC environment, which involves optimizing interconnect traffic and managing resources effectively among nodes.

Moreover, I’ve dealt with troubleshooting issues related to node evictions, cluster interconnects, and service-level failures. My experience has taught me the importance of proactive monitoring and diagnostics in maintaining a healthy RAC environment.

In sum, my hands-on experience with RAC spans from implementation and design to maintenance and troubleshooting.”

19. How would you approach a situation where you need to integrate Oracle with non-Oracle databases?

The question is designed to test your problem-solving skills, technical knowledge, and your ability to work outside your comfort zone. As a database developer, you’ll often encounter situations where you need to integrate different systems. Your ability to do this effectively and efficiently, especially with databases that you might not be as familiar with, is critical in ensuring seamless operations and data integrity.

Example: “Integrating Oracle with non-Oracle databases requires a well-planned approach.

First, I would identify the data that needs to be integrated and understand its structure in both databases. This is crucial for mapping data correctly between systems.

Next, using middleware like Oracle GoldenGate or third-party tools such as Informatica can help facilitate the integration process. These tools provide real-time, log-based change data capture, and delivery between heterogeneous systems.

Lastly, it’s important to test the integration thoroughly to ensure data accuracy and integrity. Regular monitoring post-integration is also essential to address any issues promptly.”

20. What is your experience with performance tuning in Oracle?

Performance tuning is an essential part of maintaining an efficient and effective Oracle database. If you’re applying for a role as an Oracle Database Developer, the hiring manager wants to understand your ability to identify potential performance issues and implement solutions. Proving that you have experience with performance tuning can give them confidence that you can help ensure their database runs optimally and meets business needs.

Example: “I have extensive experience with performance tuning in Oracle. I’ve used tools such as Explain Plan and SQL Trace to identify bottlenecks, optimizing SQL statements for better execution plans.

I’m proficient in using AWR/ADDM reports for system-wide analysis. My approach includes adjusting memory allocation for SGA & PGA, managing indexes, partitioning large tables, and utilizing hints where necessary.

In terms of PL/SQL, I’ve optimized code by reducing loops, using bulk collect, and implementing parallel processing when needed.

Understanding the application’s data access pattern is also crucial for effective tuning. Hence, my strategies are always aligned with the specific needs of the application and business requirements.”

21. How have you used Oracle’s Flashback Technology in your past projects?

Flashback Technology is a significant feature of Oracle’s database management system, and it’s used to simplify the process of recovering lost data. By asking this question, hiring managers are looking to assess your practical experience with this feature. They want to gauge your ability to handle data loss scenarios, which can be critical in maintaining the integrity and continuity of business operations.

Example: “In my experience, Oracle’s Flashback Technology has been instrumental in managing and recovering data. In one project, I used the Flashback Query feature to view past states of data, which helped in resolving issues related to incorrect updates or deletions.

Another instance was when we faced a critical situation where a table was accidentally dropped. Using Flashback Drop, I could recover the table quickly without restoring the entire database from backup.

Flashback Versions Query also proved beneficial for tracking changes over time, particularly useful during audit trails.

Overall, Flashback Technology has provided me with efficient tools for data recovery and analysis, enhancing productivity and minimizing potential data loss.”

22. Can you explain your process for conducting Oracle database audits?

The ability to conduct database audits is key to maintaining the integrity of data, ensuring compliance with regulations, and protecting sensitive information. By asking this question, interviewers are trying to gauge your technical skills and your understanding of the auditing process. They want to know if you have the competency to identify potential security risks and vulnerabilities, and if you can come up with measures to address these issues.

Example: “Conducting Oracle database audits involves several key steps. I start by defining the scope of the audit, which includes identifying the data and systems to be audited.

Next, I use Oracle’s built-in auditing tools or third-party software to collect data on user activity, changes made in the database, and any potential security breaches.

I then analyze this data to identify any irregularities or issues that may indicate a problem. This could include unauthorized access attempts, unexpected changes in data, or performance issues.

Finally, I compile my findings into an audit report, detailing any identified issues and recommending solutions for improvement. Regular audits are essential to ensure data integrity, compliance, and optimal performance of the Oracle database.”

23. How proficient are you in using Oracle’s Automatic Storage Management (ASM)?

Oracle’s Automatic Storage Management (ASM) is a vital tool that assists in managing and optimizing database storage. If you are applying for a position as an Oracle Database Developer, your proficiency in using ASM can significantly impact your effectiveness and efficiency on the job. By asking this question, hiring managers are trying to gauge your technical skills and your ability to manage database storage effectively, which is critical for maintaining the performance, reliability, and efficiency of the company’s database systems.

Example: “I am highly proficient in using Oracle’s Automatic Storage Management (ASM). My experience includes managing and troubleshooting ASM instances, disk groups, and files. I have a deep understanding of ASM rebalancing, data redundancy, and striping. I’ve also worked with ASM Fast Mirror Resync to maintain database performance during planned outages. I’m comfortable working with both command-line utilities and GUI for ASM management. In terms of data migration, I’ve used RMAN and Data Pump with ASM to ensure efficient and secure data transfer.”

24. How do you manage code reviews and quality assurance in database development?

Code reviews and quality assurance are key elements of any software development process, and this includes database development. By asking this question, hiring managers are seeking to understand your approach to maintaining high standards, catching errors, and ensuring that your code is robust and efficient. They want to be reassured that you can contribute to a culture of excellence and continuous improvement.

Example: “In database development, code reviews are crucial to ensure accuracy and efficiency. I manage them by using a peer review approach where multiple team members can scrutinize each piece of code for errors or improvements.

For quality assurance, I adhere strictly to the defined coding standards and conventions. Automated testing tools are also utilized to check for any bugs or issues. This way, we not only maintain high-quality code but also improve our productivity by catching errors early in the process.

Furthermore, continuous integration is used to integrate changes frequently. This allows us to detect problems quickly and fix them while they’re still small. It’s a proactive approach that ensures consistent quality throughout the project lifecycle.”

25. Can you discuss a time when you had to troubleshoot a complex database issue?

Troubleshooting is a critical part of any tech role, and especially for a database developer. Your work behind the scenes keeps systems running smoothly, so when something goes wrong, it’s on you to fix it. Whether the issue lies in the database structure, the data itself, or how users are trying to access that data, you need to identify the problem and find a solution quickly and efficiently. Your interviewer wants to hear about your problem-solving skills in action.

Example: “During a major project, we experienced frequent database crashes. I was tasked with troubleshooting the issue.

I started by examining system logs to identify any error messages which led me to suspect an issue with memory allocation. To confirm this, I used Oracle’s Automatic Workload Repository and Active Session History reports.

The data indicated that our database was not optimally configured for the workload type. The shared pool size was too small causing excessive parsing activity, leading to crashes.

I recommended increasing the shared pool size to better accommodate the workload. Post-adjustment, the database stability significantly improved. This experience underscored the importance of proper initial configuration and ongoing monitoring.”

26. How would you handle a situation where you need to upgrade an Oracle database without causing downtime?

The essence of this question lies in your ability to ensure smooth operations despite necessary upgrades or modifications. Data is the lifeblood of a modern business, and the continuous availability of that data is often a key part of a company’s operation. Therefore, your potential employer needs to know that you can manage necessary upgrades or changes without interrupting the flow of business.

Example: “To upgrade an Oracle database without causing downtime, I would use the Oracle Data Guard feature. This allows for a standby database to be created and synchronized with the primary one.

During the upgrade process, we can switch users to the standby database, ensuring continuous service. Once the upgrade is completed on the primary database, we can then switch back.

This method ensures minimal disruption while maintaining data integrity. It also provides a safety net in case of any issues during the upgrade, as the standby database can continue to function independently.”

27. Can you discuss your experience with Oracle’s Job Scheduler?

The Oracle Job Scheduler is a key tool for managing and optimizing database workloads. By asking this question, hiring managers want to gauge your familiarity with this essential feature. They want to understand if you can automate tasks, streamline processes, and ensure that critical operations are executed accurately and on time. It’s a way for them to assess your technical competency and your potential efficiency in the role.

Example: “I’ve extensively used Oracle’s Job Scheduler in managing and scheduling jobs that are critical for business operations. My experience includes creating, running, and monitoring jobs to automate various database tasks.

One of my key projects involved setting up a complex job chain to streamline ETL processes. I utilized the advanced features like prioritizing jobs, enabling/disabling them, and setting notifications upon failure.

Moreover, I have leveraged its ability to schedule PL/SQL anonymous blocks and stored procedures at predefined times or events, which has been instrumental in optimizing system performance.

Overall, Oracle’s Job Scheduler has been an essential tool in ensuring smooth and efficient database operations.”

28. How have you used Oracle’s Materialized Views to improve query performance?

As an Oracle Database Developer, you’re expected to have a deep understanding of how to optimize the performance of Oracle databases. Materialized Views is one of the many tools Oracle provides to improve query performance, and interviewers want to know how you have practically applied this tool in your previous roles. This gives them insight into your technical expertise and problem-solving skills.

Example: “Materialized Views in Oracle are a powerful tool for improving query performance, particularly when dealing with complex and time-consuming queries. I have used them to pre-compute and store aggregated or join results which can significantly reduce the execution time.

For instance, if an application frequently runs a complicated report that involves joining multiple tables and performing aggregations, running this query each time is inefficient. Instead, creating a Materialized View that stores the result of this query allows subsequent requests to simply fetch data from the view instead of executing the entire query again.

Moreover, by using the refresh options available in Oracle, I ensured these views were updated periodically or based on certain events, maintaining data consistency. This approach has led to substantial improvements in response times and overall system performance.”

29. What is your approach to handling database replication?

Hiring managers want to know your technical proficiency and approach to handling database replication, a critical aspect of Oracle database management. Replication can help improve the availability and performance of database systems, and your ability to manage this process efficiently can have a significant impact on business operations. It’s your chance to demonstrate your technical skills, problem-solving abilities, and understanding of key database principles.

Example: “In handling database replication, I prioritize understanding the business needs first. This helps in deciding between various replication methods like snapshot, transactional or merge.

Snapshot replication is ideal for smaller databases with infrequent changes. Transactional replication suits high-volume environments where data consistency is crucial. Merge replication is best when conflict resolution is needed for multiple users updating data simultaneously.

Next, I ensure proper configuration of the publisher, distributor and subscriber servers. Regular monitoring and troubleshooting of these components are also important to maintain smooth operations.

Lastly, I focus on optimizing performance by fine-tuning replication settings as per specific requirements. For instance, adjusting the synchronization frequency can balance between system load and data freshness.”

30. Can you discuss a challenging database development project you’ve worked on and how you tackled it?

As an Oracle Database Developer, your primary role involves problem-solving and dealing with complex database systems. By asking this question, recruiters are looking to assess your problem-solving skills, technical competence, and your ability to handle stress. They want to see if you can take on demanding projects, overcome obstacles, and deliver efficient results, which will ultimately contribute to the overall success of their organization.

Example: “One of the most challenging projects I’ve worked on involved optimizing a complex, slow-performing database. The system was critical for business operations but its performance issues were causing significant delays.

My approach started with an in-depth analysis to identify bottlenecks. Using Oracle’s Automatic Workload Repository and SQL tuning Advisor, I pinpointed inefficient queries that were consuming excessive resources.

I then rewrote these queries and implemented indexing strategies which drastically improved the system’s performance. Furthermore, I partitioned large tables based on usage patterns to enhance data retrieval speed.

This project taught me the importance of continuous monitoring and optimization in maintaining efficient database systems.”

30 Dressmaker Interview Questions and Answers

30 collision repair technician interview questions and answers, you may also be interested in..., 20 most common edi developer interview questions and answers, 30 developmental specialist interview questions and answers, 30 vice president of human resources interview questions and answers, 30 director of player development interview questions and answers.

Top 50 Oracle Interview Questions and Answers (2024)

Oracle SQL Interview Questions for Freshers & Experienced

Here are Oracle interview questions and answers for fresher as well experienced SQL developer candidates to get their dream job.

 1) Difference between varchar and varchar2 data types?

Varchar can store upto 2000 bytes and varchar2 can store upto 4000 bytes. Varchar will occupy space for NULL values and Varchar2 will not occupy any space. Both are differed with respect to space.

Free PDF Download: Oracle Interview Questions & Answers

2) In which language Oracle has been developed?

Oracle has been developed using C Language.

 3) What is RAW datatype?

RAW datatype is used to store values in binary data format. The maximum size for a raw in a table in 32767 bytes.

4) What is the use of NVL function?

The NVL function is used to replace NULL values with another or given value. Example is –

NVL(Value, replace value)

5) Whether any commands are used for Months calculation? If so, What are they?

In Oracle, months_between function is used to find number of months between the given dates. Example is –

Months_between(Date 1, Date 2)

6) What are nested tables?

Nested table is a data type in Oracle which is used to support columns containing multi valued attributes. It also hold entire sub table.

7) What is COALESCE function?

COALESCE function is used to return the value which is set to be not null in the list. If all values in the list are null, then the coalesce function will return NULL.

8) What is BLOB datatype?

A BLOB data type is a varying length binary string which is used to store two gigabytes memory. Length should be specified in Bytes for BLOB.

9) How do we represent comments in Oracle?

Comments in Oracle can be represented in two ways –

• Two dashes(–) before beginning of the line – Single statement
• /*—— */ is used to represent it as comments for block of statement

10) What is DML?

Data Manipulation Language (DML) is used to access and manipulate data in the existing objects.  DML statements are insert, select, update and delete and it won’t implicitly commit the current transaction.

11) What is the difference between TRANSLATE and REPLACE?

Translate is used for character by character substitution and Replace is used substitute a single character with a word.

12) How do we display rows from the table without duplicates?

Duplicate rows can be removed by using the keyword DISTINCT in the select statement.

13) What is the usage of Merge Statement?

Merge statement is used to select rows from one or more data source for updating and insertion into a table or a view. It is used to combine multiple operations.

14) What is NULL value in oracle?

NULL value represents missing or unknown data. This is used as a place holder or represented it in as default entry to indicate that there is no actual data present.

15) What is USING Clause and give example?

The USING clause is used to specify with the column to test for equality when two tables are joined.

[sql]Select * from employee join salary using employee ID[/sql]

Employee tables join with the Salary tables with the Employee ID.

16) What is key preserved table?

A table is set to be key preserved table if every key of the table can also be the key of the result of the join. It guarantees to return only one copy of each row from the base table.

17) What is WITH CHECK OPTION?

The WITH CHECK option clause specifies check level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the sub query.

18) What is the use of Aggregate functions in Oracle?

Aggregate function is a function where values of multiple rows or records are joined together to get a single value output. Common aggregate functions are –

19) What do you mean by GROUP BY Clause?

A GROUP BY clause can be used in select statement where it will collect data across multiple records and group the results by one or more columns.

20) What is a sub query and what are the different types of subqueries?

Sub Query is also called as Nested Query or Inner Query which is used to get data from multiple tables. A sub query is added in the where clause of the main query.

There are two different types of subqueries:

• Correlated sub query

A Correlated sub query cannot be as independent query but can reference column in a table listed in the from list of the outer query.

• Non-Correlated subquery

This can be evaluated as if it were an independent query. Results of the sub query are submitted to the main query or parent query.

21) What is cross join?

Cross join is defined as the Cartesian product of records from the tables present in the join. Cross join will produce result which combines each row from the first table with the each row from the second table.

22) What are temporal data types in Oracle?

Oracle provides following temporal data types:

• Date Data Type – Different formats of Dates
• TimeStamp Data Type – Different formats of Time Stamp
• Interval Data Type – Interval between dates and time

23) How do we create privileges in Oracle?

A privilege is nothing but right to execute an SQL query or to access another user object. Privilege can be given as system privilege or user privilege.

24) What is VArray?

VArray is an oracle data type used to have columns containing multivalued attributes and it can hold bounded array of values.

25) How do we get field details of a table?

Describe <Table_Name> is used to get the field details of a specified table.

26) What is the difference between rename and alias?

Rename is a permanent name given to a table or a column whereas Alias is a temporary name given to a table or column. Rename is nothing but replacement of name and Alias is an alternate name of the table or column.

27) What is a View?

View is a logical table which based on one or more tables or views.  The tables upon which the view is based are called Base Tables and it doesn’t contain data.

28) What is a cursor variable?

A cursor variable is associated with different statements which can hold different values at run time. A cursor variable is a kind of reference type.

29) What are cursor attributes?

Each cursor in Oracle has set of attributes which enables an application program to test the state of the cursor. The attributes can be used to check whether cursor is opened or closed, found or not found and also find row count.

30) What are SET operators?

SET operators are used with two or more queries and those operators are Union, Union All, Intersect and Minus.

31) How can we delete duplicate rows in a table?

Duplicate rows in the table can be deleted by using ROWID.

32) What are the attributes of Cursor?

Attributes of Cursor are

Returns NULL if cursor is open and fetch has not been executed

Returns TRUE if the fetch of cursor is executed successfully.

Returns False if no rows are returned.

Returns False if fetch has been executed

Returns True if no row was returned

Returns true if the cursor is open

Returns false if the cursor is closed

Returns the number of rows fetched. It has to be iterated through entire cursor to give exact real count.

33) Can we store pictures in the database and if so, how it can be done?

Yes, we can store pictures in the database by Long Raw Data type. This datatype is used to store binary data for 2 gigabytes of length. But the table can have only on Long Raw data type.

34) What is an integrity constraint?

An integrity constraint is a declaration defined a business rule for a table column. Integrity constraints are used to ensure accuracy and consistency of data in a database. There are types – Domain Integrity, Referential Integrity and Domain Integrity.

35) What is an ALERT?

An alert is a window which appears in the center of the screen overlaying a portion of the current display.

36) What is hash cluster?

Hash Cluster is a technique used to store the table for faster retrieval. Apply hash value on the table to retrieve the rows from the table.

37) What are the various constraints used in Oracle?

Following are constraints used:

• NULL – It is to indicate that particular column can contain NULL values
• NOT NULL – It is to indicate that particular column cannot contain NULL values
• CHECK – Validate that values in the given column to meet the specific criteria
• DEFAULT – It is to indicate the value is assigned to default value

38) What is difference between SUBSTR and INSTR?

SUBSTR returns specific portion of a string and INSTR provides character position in which a pattern is found in a string.

SUBSTR returns string whereas INSTR returns numeric.

39) What is the parameter mode that can be passed to a procedure?

IN, OUT and INOUT are the modes of parameters that can be passed to a procedure.

40) What are the different Oracle Database objects?

There are different data objects in Oracle –

• Tables – set of elements organized in vertical and horizontal
• Views  – Virtual table derived from one or more tables
• Indexes – Performance tuning method for processing the records
• Synonyms – Alias name for tables
• Sequences – Multiple users generate unique numbers
• Tablespaces – Logical storage unit in Oracle

41) What are the differences between LOV and List Item?

LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column, LOV can have one or more columns.

42) What are privileges and Grants?

Privileges are the rights to execute SQL statements – means Right to connect and connect. Grants are given to the object so that objects can be accessed accordingly. Grants can be provided by the owner or creator of an object.

43) What is the difference between $ORACLE_BASE and $ORACLE_HOME?

Oracle base is the main or root directory of an oracle whereas ORACLE_HOME is located beneath base folder in which all oracle products reside.

44) What is the fastest query method to fetch data from the table?

Row can be fetched from table by using ROWID. Using ROW ID is the fastest query method to fetch data from the table.

45) What is the maximum number of triggers that can be applied to a single table?

12 is the maximum number of triggers that can be applied to a single table.

46) How to display row numbers with the records?

Display row numbers with the records numbers –

This query will display row numbers and the field values from the given table.

47) How can we view last record added to a table?

Last record can be added to a table and this can be done by –

48) What is the data type of DUAL table?

The DUAL table is a one-column table present in oracle database.  The table has a single VARCHAR2(1) column called DUMMY which has a value of ‘X’.

49) What is difference between Cartesian Join and Cross Join?

There are no differences between the join. Cartesian and Cross joins are same. Cross join gives cartesian product of two tables – Rows from first table is multiplied with another table which is called cartesian product.

Cross join without where clause gives Cartesian product.

50) How to display employee records who gets more salary than the average salary in the department?

This can be done by this query –

These interview questions will also help in your viva(orals)

You Might Like:

21 Comments

– There are 3 big files, 1GB (file_a.txt), 10GB (file_b.txt) and 1TB (file_c.txt); – The format of these 3 files: each line with a random string in the file; – There is only 100MB memory could be used, disk usage is not limited; – Assumption: IF AND ONLY IF string A appears within all 3 files, we need to count the total appearing times of this A. Such as, A appears 2 times within file_a.txt, appears 10 times within file_b.txt, appears 100 times within file_c.txt, then we count the total appearing times of A as 2 + 10 + 100 = 112 times.

Question: please write a program to output the strings with TOP 10 and LAST 10 appearing times in descending order. anyone can answer please.

bad question .cannot understand.

SECTION ONE – SCENARIO SchemaName: IssuesTracking Problem Description: You are required to design and implement database for Issues Tracking Software. Software issue tracking is an integral part of any enterprise software development lifecycle. The issue tracking toolkit is responsible to create, store, trace, and manage issues (e.g., software bugs or requests for new features). Each issue is represented by a ticket that must capture the following information • the actual issue • the components or projects effected by the issue • developer/customers who first identified the issue • the developers/managers who are responsible to address the issue • the state of the issue • other related issues (tickets)

Detailed Requirements: Here we outline minimum requirements for an enterprise change management toolkit. You are encouraged to modify, add, and (if clearly justifiable) remove requirements as you deem to be necessary. Ticket – possible attributes: owner, title, description, state (e.g., open, assessing, working, testing, deferred, rejected, closed, etc.), priority (e.g., low, mid, high, urgent), planned completion date, one or more related projects, one or more related tickets, category (task, feature, question, defect, milestone), milestone status, submitter, submission date, escalation person, blog entry (a ticket discussion forum), related resources, and related artifacts (e.g., diagrams and documents to help resolve the problems), work log (number of hours worked on each day which could be different for each day and for each user)

Project – Possible attributes: title, description, planned completion date, actual completion date, project manager, creator, creation date, work log (number of hours worked on each day which could be different for each day and for each user) User – Possible attributes: name, title (e.g., developer, manager, sysadmin), security (username and password) Artifact – Possible attributes: title, description, category, version, size, data Comment – possible attributes: ticket, submitter, submitter date, text -Your role as a Student The goal of this exercise is to provide a practical experience, as a database designer and administrator. Prepare a script and document it. You may actually use tools like SQL Developer in order to implement the database design. Use examples where ever appropriate.

SECTION TWO – QUESTIONS

All questions are compulsory. Answer all questions serially. Make sure, you indicate each question and follow with answers. Use syntax and examples wherever necessary. You are to derive your answers based on the scenario. The numbers at the end of questions indicate full marks. Questions: 1. Create User Schema (IssueTracking) and grant permission to all Objects. [5] 2. Create possible DB table in Oracle that should be represent the given scenarios. [10] 3. Define and explain the relationship among the tables [10] 4. Define the proper table structures (e.g., date column can be date field, Amount data filed should be Numeric data field) [10] 5. Explain the DBs Object Security and System Security. Create DBReader and DBWriter users, DBWriter user should get access on DDL and DML command execution, DBReader user can access only read permission on all tables [4+4+2+6+4] 6. What is locking mechanism? Why is it necessary? Demonstrate the Shared Lock and Exclusive lock situation in any one table [2+4+6+6] 7. What is the Deadlock and how do you manage deadlock in DB system? Create a deadlock situation in ‘Ticket’ table. [5+10] 8. ABC Inc. business losses last week’s data due to system failure, the business Owner doesn’t want to lose the anything for business. Normally, DBA takes daily backup on End of Day. You are the DBA of ABC Inc.; How do you manage data of that week and what would be the best way forward? [10]

Can you help me ?

good Question…

This shouldn’t be too hard. I would loop through the smallest file, since if a string is not in it, then we don’t care if it exists in the other files. For my data structure, I would keep a list that would have a line for each line in the smallest file, and I would write in this line my count. If a line is present multiple times in the file, you can count subsequent occurrences a zeroes, or mark them as X, so you know to not count them twice. Then I read this file to load an array with the top 10 occurrences, I would put the first index in this array, and the count. Then I would use the index to go to the file and read the actual line and display it, along with the count. Then do a similar process for the LAST 10, this should be even easier.

can you write the script instead of verbal english

Thank you very much for giving an opportunity to recap knowledge over Oracle DB

There is a mistake on answer of 50. Given is this Select * from employee where salary>(select avg(salary) from dept, employee where dept.deptno = employee.deptno; Error is last bracket is not given.

Hi, thanks for writing. It is reviewed and updated.

this is a complete knowledge pack instructional training

Select is not a DML command rather it’s a DRL command. Please refer Q10 above.

Yes Damodar u are ri8

Question no 47. to get the last record of table Select * from (select * from employees order by employee_id desc) where rownum<=1;

instead of above query can we use the below one.

select * from employees where rownum<=1 order by employee_id desc;

Thanks, Ank

we can not use , order by clause is processed by sql engine after the result set is processed, so your query takes first record in the table

SQL developer

Good question

45. What is the maximum number of triggers that can be applied to a single table? correct answer:

We can have N number of triggers on a table but the maximum type of triggers on a single table can be 3*2*2=12 that is the division is done as Insert/Update/Delete= 3 Before/After= 2 Row Level/Statement Level=2

If select query return 6 lakhs record out of 10 lakhs record from one table, then optimizer use INDEX scan or full table scan. when index will fail and what is the INDEX maximum percentage to fetch record from table?

Thanks for help by Interview Questions.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

• Python Quiz
• Testing Quiz
• Selenium Quiz
• Python Questions
• Java Questions
• CSharp Questions
• Linux Questions
• WebDev Questions
• Agile Tutorial
• Linux Tutorial
• Python Selenium
• Python Pandas
• Python Quizzes
• Shell Script Quiz
• WebDev Interview
• Python Basic
• Python Examples
• Python Advanced
• General Tech

Top 100 SQL Query Interview Questions for Practice

Hey Friends, we’ve brought you over 100 SQL queries and exercises for practice. The first 50, in this post, are the most frequently asked SQL query interview questions and the remaining 50 are the tricky SQL queries for interview. So, start with the readymade SQL script provided to create the test data. The script includes a sample Worker table, a Bonus, and a Title table with pre-filled data. Just run the SQL script, and you are all set to get started with the SQL queries. Practice with these SQL queries asked in interview questions and get ready for interviews with top IT MNCs like Amazon, Flipkart, Facebook, etc.

Get Started with 100 SQL Query Interview Questions

We recommend that you first try to form queries by yourself rather than just reading them from the post. This tutorial includes SQL scripts to create the test data. So, you can use them to create a test database and tables. Once you have done enough SQL practice, then also check out another post on SQL interview prep for QA and DBAs .

Let’s Prepare Sample Data for SQL Practice

Sample table – worker, sample table – bonus, sample table – title.

To prepare the sample data, you can run the following queries in your database query executor or on the SQL command line. We’ve tested them with the latest version of MySQL Server and MySQL Workbench query browser. You can download these tools and install them to execute the SQL queries. However, these queries will run fine in any online MySQL compiler, so you may use them.

SQL Script to Seed Sample Data.

Once the above SQL runs, you’ll see a result similar to the one attached below.

Practice with SQL Queries Asked in Interview Questions

Below are the 50 commonly asked SQL queries in interviews from various fields. For the second part including 50 tricky SQL queries for interview, read from below.

Must Read: 50 Tricky SQL Queries for Practice Before Your Interview

Q-1. Write an SQL query to fetch “FIRST_NAME” from the Worker table using the alias name <WORKER_NAME>.

The required query is:

Q-2. Write an SQL query to fetch “FIRST_NAME” from the Worker table in upper case.

Q-3. write an sql query to fetch unique values of department from the worker table., q-4. write an sql query to print the first three characters of  first_name from the worker table., q-5. write an sql query to find the position of the alphabet (‘a’) in the first name column ‘amitabh’ from the worker table..

• The INSTR does a case-insensitive search.
• Using the BINARY operator will make INSTR work as the case-sensitive function.

Q-6. Write an SQL query to print the FIRST_NAME from the Worker table after removing white spaces from the right side.

Q-7. write an sql query to print the department from the worker table after removing white spaces from the left side., q-8. write an sql query that fetches the unique values of department from the worker table and prints its length., q-9. write an sql query to print the first_name from the worker table after replacing ‘a’ with ‘a’., q-10. write an sql query to print the first_name and last_name from the worker table into a single column complete_name. a space char should separate them., q-11. write an sql query to print all worker details from the worker table order by first_name ascending., q-12. write an sql query to print all worker details from the worker table order by first_name ascending and department descending., q-13. write an sql query to print details for workers with the first names “vipul” and “satish” from the worker table., q-14. write an sql query to print details of workers excluding first names, “vipul” and “satish” from the worker table., q-15. write an sql query to print details of workers with department name as “admin”., q-16. write an sql query to print details of the workers whose first_name contains ‘a’., q-17. write an sql query to print details of the workers whose first_name ends with ‘a’., q-18. write an sql query to print details of the workers whose first_name ends with ‘h’ and contains six alphabets., q-19. write an sql query to print details of the workers whose salary lies between 100000 and 500000., q-20. write an sql query to print details of the workers who joined in feb 2021., q-21. write an sql query to fetch the count of employees working in the department ‘admin’..

At this point, you have acquired a good understanding of the basics of SQL, let’s move on to some more intermediate-level SQL query interview questions. These questions will require us to use more advanced SQL syntax and concepts, such as GROUP BY, HAVING, and ORDER BY.

Q-22. Write an SQL query to fetch worker names with salaries >= 50000 and <= 100000.

Q-23. write an sql query to fetch the number of workers for each department in descending order., q-24. write an sql query to print details of the workers who are also managers., q-25. write an sql query to fetch duplicate records having matching data in some fields of a table., q-26. write an sql query to show only odd rows from a table., q-27. write an sql query to show only even rows from a table., q-28. write an sql query to clone a new table from another table..

The general query to clone a table with data is:

The general way to clone a table without information is:

An alternate way to clone a table (for MySQL) without data is:

Q-29. Write an SQL query to fetch intersecting records of two tables.

Q-30. write an sql query to show records from one table that another table does not have., q-31. write an sql query to show the current date and time..

The following MySQL query returns the current date:

Whereas the following MySQL query returns the current date and time:

Here is a SQL Server query that returns the current date and time:

Find this Oracle query that also returns the current date and time:

Q-32. Write an SQL query to show the top n (say 10) records of a table.

MySQL query to return the top n records using the LIMIT method:

SQL Server query to return the top n records using the TOP command:

Oracle query to return the top n records with the help of ROWNUM:

Now, that you should have a solid foundation in intermediate SQL, let’s take a look at some more advanced SQL query questions. These questions will require us to use more complex SQL syntax and concepts, such as nested queries, joins, unions, and intersects.

Q-33. Write an SQL query to determine the nth (say n=5) highest salary from a table.

MySQL query to find the nth highest salary:

SQL Server query to find the nth highest salary:

Q-34. Write an SQL query to determine the 5th highest salary without using the TOP or limit method.

The following query is using the correlated subquery to return the 5th highest salary:

Use the following generic method to find the nth highest salary without using TOP or limit.

Q-35. Write an SQL query to fetch the list of employees with the same salary.

Q-36. write an sql query to show the second-highest salary from a table., q-37. write an sql query to show one row twice in the results from a table., q-38. write an sql query to fetch intersecting records of two tables., q-39. write an sql query to fetch the first 50% of records from a table..

Practicing SQL query interview questions is a great way to improve your understanding of the language and become more proficient in using it. However, in addition to improving your technical skills, practicing SQL query questions can also help you advance your career. Many employers are looking for candidates who have strong SQL skills, so being able to demonstrate your proficiency in the language can give you a competitive edge.

Q-40. Write an SQL query to fetch the departments that have less than five people in them.

Q-41. write an sql query to show all departments along with the number of people in there..

The following query returns the expected result:

Q-42. Write an SQL query to show the last record from a table.

The following query will return the last record from the Worker table:

Q-43. Write an SQL query to fetch the first row of a table.

Q-44. write an sql query to fetch the last five records from a table., q-45. write an sql query to print the names of employees having the highest salary in each department., q-46. write an sql query to fetch three max salaries from a table., q-47. write an sql query to fetch three min salaries from a table., q-48. write an sql query to fetch nth max salaries from a table., q-49. write an sql query to fetch departments along with the total salaries paid for each of them., q-50. write an sql query to fetch the names of workers who earn the highest salary..

Must Check: SQL Programming Test with 20+ Basic to Advanced Queries

Summary – Master SQL with Practice Questions.

We hope you enjoyed solving the SQL exercises and learned something new along the way. Stay tuned for our next post, where we’ll bring you even more challenging SQL query interview questions to sharpen your proficiency.

Thanks for reading! We hope you found this tutorial helpful. If you did, please consider sharing it with your friends and colleagues. You can also follow us on our social media platforms for more helpful resources. And if you’re looking for more information on this topic, be sure to check out the “You Might Also Like” section below.

-TechBeamers.

You Might Also Like

How to use union in sql queries, if statement in sql queries: a quick guide, where clause in sql: a practical guide, a beginner’s guide to sql joins, 20 sql tips and tricks for better performance.

Leave a Reply

Your email address will not be published. Required fields are marked *

Top Tutorials

7 Demo Websites to Practice Selenium Automation Testing

SQL Exercises – Complex Queries

15 Java Coding Questions for Testers

30 Python Programming Questions On List, Tuple, and Dictionary

25 SQL Performance Interview Questions and Answers

SQL Joins: 12 Practice Questions with Detailed Answers

• sql practice
• sql-practice-guide

In this article, we dig into our SQL JOINS course and give you 12 join exercises to solve. But don’t worry – all the exercises have solutions and explanations. If you get stuck, help is there! This is, after all, made for practicing and learning. 

SQL joins can be tricky. It’s not just the syntax, but also knowing what joins to use in what scenarios.

Joins are used when combining data from two or more tables in SQL. The tables can be joined in several ways, and, depending on the tables, each way of joining them can result in a completely different result.  There’s no other way to learn this than practice. Yes, you can read explanations and typical uses of SQL joins. That helps, for sure! But practice builds on that through problem-solving and repetition, which makes your knowledge stick. The more you practice, the greater the possibility that the real-life data problems you’ll have to solve will be similar or completely the same as what you’ve already done!

And practice is what we’ll do in this article! We’ll show you exercises for basic and more advanced SQL joins uses. If you like them, you’ll enjoy our SQL JOINs course even more, as all the exercises are taken from there. In total, the course offers you 93 SQL joins exercises. They cover topics ranging from the types of joins in SQL, to filtering data, joining more than two tables, self-joining a table, and using non-equi joins.

OK, so let’s introduce the datasets and start exercising, shall we? Feel free to help yourself with the SQL JOIN Cheat Sheet as you go.

List of Exercises

Here's a list of all exercises in the article:

Exercise 1: List All Books and Their Authors

Exercise 2: list authors and books published after 2005, exercise 3: show books adapted within 4 years and rated lower than the adaptation, exercise 4: show all books and their adaptations (if any), exercise 5: show all books and their movie adaptations, exercise 6: show all books with their reviews (if any), exercise 7: list all the books and all the authors, exercise 8: show products under 150 calories and their department, exercise 9: list all products with their producers, departments, and carbs, exercise 10: show all the products, prices, producers, and departments, exercise 11: list all workers and their direct supervisors, exercise 12: show cars with higher mileage than a specific car.

INNER JOIN is a type of SQL join that returns only the matching rows from the joined tables.

To show you how this works, we’ll use Dataset 1 from the course.

The dataset consists of four tables: author , book , adaptation , and book_review .

The first table shows the author data in the following columns:

• id – The author’s unique ID within the database.
• name – The author’s name.
• birth_year – The year when that author was born.
• death_year – The year when that author died (the field is empty if they are still alive).

Here are the table’s first few rows:

The second table, book ,  shows details about books. The columns are:

• id – The ID of a given book.
• author_id – The ID of the author who wrote that book.
• title – The book’s title.
• publish_year – The year when the book was published.
• publishing_house – The name of the publishing house that printed the book.
• rating – The average rating for the book.

These are the first five rows:

The adaptation table has the following columns:

• book_id – The ID of the adapted book.
• type – The type of adaptation (e.g. movie, game, play, musical).
• title – The name of this adaptation.
• release_year – The year when the adaptation was created.
• rating – The average rating for the adaptation.

Here’s a snapshot of the data from this table:

The final table is book_review . It consists of the following columns:

• book_id - The ID of a reviewed book.
• review - The summary of the review.
• author - The name of the review's author.

Here’s the data:

Exercise: Show the name of each author together with the title of the book they wrote and the year in which that book was published.

Solution explanation: The query selects the name of the author, the book title, and its publishing year. This is data from the two tables: author and book . We are able to access both tables by using INNER JOIN . It returns only rows with matching values (values that satisfy the join condition) from both tables.

We first reference the table author in the FROM clause. Then we add the JOIN clause (which can also be written as INNER JOIN in SQL) and reference the table book .

The tables are joined on the common column. In this case, it's id from the table author and author_id from the table book . We want to join the rows where these columns share the same value. We do that using the ON clause and specifying the column names. We also put the table name before each column so the database knows where to look. That’s primarily because there’s an id column in both tables, but we want the id column only from the author table. By referencing the table name, the database will know from which table we need that column.

Solution output:

Here’s the output snapshot. We got all this data by joining two tables:

Exercise: Show the name of each author together with the title of the book they wrote and the year in which that book was published. Show only books published after 2005.

Solution explanation: This exercise and its solution are almost the same as the previous one. This is reflected by the query selecting the same columns and joining the tables in the same way as earlier.

The difference is that the exercise now asks us to show only books published after 2005. This requires filtering the output; we do that using the WHERE clause.

WHERE is a clause that accepts conditions used to filter out the data. It is written after joining the tables. In our example, we filter by referencing the column publish_year after WHERE and using the comparison operator ‘greater than’ ( > ) to find the years after 2005.

The output shows only one book published after 2005.

Exercise: For each book, show its title, adaptation title, adaptation year, and publication year.

Include only books with a rating lower than the rating of their corresponding adaptation. Additionally, show only those books for which an adaptation was released within four years of the book’s publication.

Rename the title column from the book table to book_title and the title column from the adaptation table to adaptation_title .

Solution explanation: Let’s start explaining the solution from the FROM and JOIN clauses. The columns we need to show are from the tables book and adaptation . We reference the first table in FROM and the second in JOIN .

In the ON clause, we equal the two book ID columns and specify the table of each column. This is the same as earlier, only with different table and column names.

Now, we need to select the required columns. The thing here is there’s a title column in both tables. To avoid ambiguity, a best practice is to reference the table name before each column in the SELECT .

Note: The above is mandatory only for ambiguous columns. However, it’s a good idea to do that with all columns; it improves code readability and the approach remains consistent.

After selecting the columns, we need to rename some of them. We do that using the keyword AS and writing a new column name afterward. That way, one title column becomes book_title , the other becomes adaptation_title . Giving aliases to the column names also helps get rid of ambiguity.

Now we need to filter the output. The first condition is that the adaptation had to be released four years or less after the book. We again use WHERE and simply deduct the book publish year from the adaptation release year. Then we say that the difference has to be less than or equal to ( <= ) 4.

We also need to add the second condition, where the book has a lower rating than the adaptation. It’s simple! The question implies that both the first and the second conditions have to be satisfied. The clue is in AND , a logical operator we use for adding the second condition. Here, it uses the ‘less than’ (< ) operator to compare the two ratings.

The output shows three book–adaptation pairs that satisfy the conditions.

Now that you get the gist of INNER JOIN , let’s move on to LEFT JOIN . It’s a type of outer join that returns all the columns from the left (the first) table and only the matching rows from the right (the second) table. If there is non-matching data, it’s shown as NULL .

You can learn more in our article about LEFT JOIN .

Exercise: Show the title of each book together with the title of its adaptation and the date of the release. Show all books, regardless of whether they had adaptations.

Solution explanation: We first select the required columns from the two tables. Then we join book (the left table) with adaptation (the right table) using LEFT JOIN . You see that the SQL join syntax is the same for INNER JOIN . The only thing that changes is the join keyword.

Note: SQL accepts both LEFT JOIN and LEFT OUTER JOIN . They are the same command.

The output snapshot shows the required data, with some of the data shown as NULL . These are the books without the adaptation.

Exercise: Show all books with their movie adaptations. Select each book's title, the name of its publishing house, the title of its adaptation, and the type of the adaptation. Keep the books with no adaptations in the result.

Solution explanation:

The question asks to show all the rows, even those without any adaptations. It’s possible that there are books without adaptations, so we use LEFT JOIN .

We first select the book title, its publishing house, its adaptation title, and its type.

Then we join book (the left table) with adaptation (the right table) using LEFT JOIN . We join the tables on the book ID. All the books that don’t satisfy the conditions will have NULL s as an adaptation title and type.

We filter data using WHERE . The first condition is that the adaptation type has to be a movie, so we equal the type column with a movie using the equal sign ( = ).  Note: When using text data in the WHERE condition, it must be enclosed in single quotes ( '' ).

The second filtering condition is added using the logical operator OR. It says that the type can also be NULL if it’s not a movie. The exercise asks us to keep books with no adaptations in the results.

Here’s the output snapshot. You can see that it shows only books adapted as movies or not adapted at all.

Where there’s LEFT JOIN , there’s also RIGHT JOIN , right? Despite being the LEFT JOIN's mirror image, it’s still a part of the SQL joins practice.

It’s a type of join that returns all the columns from the right (the second) table and only the matching rows from the left (the first) table. If there is non-matching data, it’s shown as NULL .

Exercise: Join the book_review and book tables using a RIGHT JOIN . Show the title of the book, the corresponding review, and the name of the review's author. Consider all books, even those that weren't reviewed.

We first select the required columns. Then we do as we’re told: join the tables using RIGHT JOIN . We join the tables on the book ID. The table book is the right table; we want all the data from it, regardless of the reviews.

As you can see, the syntax stays the same as in INNER JOIN and LEFT JOIN .

Note: SQL accepts both RIGHT JOIN and RIGHT OUTER JOIN .

The query returns all the book titles, their reviews, and authors. Where there’s no review or author information, a NULL is shown.

Here’s another join type that’s useful in some scenarios: the FULL JOIN . This is a LEFT JOIN and RIGHT JOIN put together. It shows matching rows from both tables, rows that have no match from the left table, and rows that have no match from the right table. In short, it shows all data from both tables.

You can read more about how and when to use FULL JOIN .

Exercise: Display the title of each book along with the name of its author. Show all books, even those without an author. Show all authors, even those who haven't published a book yet. Use a FULL JOIN .

Solution explanation: The question requires showing all books, but also all authors – FULL JOIN is perfect for doing this elegantly.

We select the book title and the author's name. Next, we FULL JOIN the table book with the table author . The joining condition is that the author ID has to be the same in both tables. Again, the syntax is the same as in all the previous join types.

Note: SQL accepts both FULL JOIN and FULL OUTER JOIN.

The output shows all the books and all the authors, whether the authors or books exist in both tables or not.

Joining 3 or More Tables

Yes, SQL joins allow for joining more than two tables. We’ll see how to do that in this part of the SQL joins practice. You can find a more detailed explanation of multiple joins here .

We also need a new dataset, so let’s introduce it.

The first table in the dataset is department . Its columns are:

• id – The unique ID of the department.
• name – The department name, i.e. where a particular type of product is sold.

Here’s the data from the table.

The second table is product , and it consists of the following columns:

• id – The ID of a given product.
• name – The product’s name.
• department_id – The ID of the department where the product is located.
• shelf_id – The ID of the shelf of that department where the product is located.
• producer_id – The ID of the company that manufactures this product.
• price – The product’s price.

Here’s the data snapshot:

The next table is nutrition_data . Its columns and data are given below:

• product_id – The ID of a product.
• calories – The calorific value of that product.
• fat – The amount of fat in that product.
• carbohydrate – The amount of carbohydrates in that product.
• protein – The amount of protein in that product.

The fourth table is named producer . It has the following columns:

• id – The ID of a given food producer.
• name – The name of the producer.

Below is the data from this table:

The last table in the dataset is sales_history . It has the following columns:

• date – The date of sale.
• product_id – The ID of the product sold.
• amount – The amount of that product sold on a particular day.

Here’s the data, too:

Exercise: List all products that have fewer than 150 calories. For each product, show its name (rename the column product ) and the name of the department where it can be found (name the column department ).

Solution explanation: The general principle of how you join the third (fourth, fifth…) table is that you simply add another JOIN . You can see how it’s done in this article explaining multiple joins . We’ll do it the same way here.

We first join the department table with the product table on the department ID using JOIN . But we also need the third table. To get the data from it, we just add another JOIN , which will join the product table with the nutrition_data table. The syntax is the same as with the first join. In this case, the query joins the tables on the product ID.

Then we use WHERE to find products with fewer than 150 calories. We finally select the product and department names and rename the columns as per the exercise instructions.

Note: You probably noticed both selected columns have the same original name. And you also noticed we solved this ambiguity by putting some strange short table names in front of all the columns in the query. These shortened names are table aliases, which you give by simply writing them after the table name in FROM or JOIN . By giving aliases to the tables, you can shorten the tables’ names. Therefore, you don’t have to write their full names (sometimes they can be really long!), but the short aliases instead. This saves time and space.

The output shows a list of the products and the department they belong to. It includes only those products with fewer than 150 calories.

Exercise: For each product, display the:

• Name of the company that produced it (name the column producer_name ).
• Name of the department where the product is located (name it department_name ).
• Product name (name it product_name ).
• Total number of carbohydrates in the product.

Your query should still consider products with no information about producer_id or department_id .

Solution explanation: The query selects the required columns. Then it joins the table product with the table producer on the producer ID using LEFT JOIN . We choose this type of join because we have to include products without producer data.

Then we add another LEFT JOIN . This one adds the department table and joins it with the product table. Again, we choose LEFT JOIN because we need to show products that don’t have a department.

There’s also a third join! We simply add it to the chain of the previous joins. It’s again LEFT JOIN , as we add the nutrition_data table and join it with the product table.

This is an interesting topic to explore, so here’s an article that explains multiple LEFT JOINs to help you with it.

The output shows all the products with their producer and department names and carbohydrate amounts:

If you need more details, please read how to LEFT JOIN multiple tables in SQL .

Exercise: For each product, show its name, price, producer name, and department name.

Alias the columns as product_name , product_price , producer_name , and department_name , respectively. Include all the products, even those without a producer or department. Also, include the producers and departments without a product.

Solution explanation: This exercise requires using FULL JOIN , as we need all the data from the tables we’ll use: product , producer , and department .

The syntax is the same as in the previous examples. We just join the different tables ( product and producer ) on the producer ID and use a different type of join:  FULL JOIN .

The second FULL JOIN joins the product table with the department table.

After selecting the required columns and renaming them, we get the following output.

The solution shows all the data from the selected tables and columns:

A self-join is not a distinct type of SQL JOIN – any join can be used for self-joining a table. It’s simply a join used to join the table with itself. By giving different aliases to the same table, it’s treated as two different tables when self-joined.

For more details, check out our illustrated guide to the SQL self-join .

The dataset for this example consists of only one table: workshop_workers . It has the following columns.

• id – The worker’s ID.
• name – The worker’s first and last name.
• specialization – The worker's specialization.
• master_id – The ID of the worker's supervisor.
• experience – The worker's years of experience.
• project_id – The ID of the project to which the worker is currently assigned.

Exercise: Show all workers' names together with the names of their direct supervisors. Rename the columns  apprentice_name and master_name , respectively. Consider only workers who have a supervisor (i.e. a master).

Solution explanation: Let’s start with explaining the self-join. The general principle is the same as with regular joins. We reference the table in FROM and give it an alias, apprentice . Then we use JOIN and reference the same table in it. This time, we give the table the alias master . We’re basically pretending that one table has the apprentice data and the other has the master data.

The tables are joined on the master ID from the apprentice table and the ID from the master table.

This example is a typical use of a self-join: the table has a column ( master_id ) that references another column from the same table ( id ). Both columns show the worker’s ID. When there’s NULL in master_id , it means that the worker doesn’t have a master. In other words, they are the master.

After self-joining, we simply select the required columns and rename them.

The output shows all the apprentices and their direct supervisors.

Non-Equi Joins

The final topic we’ll tackle in this SQL joins practice are non-equi joins. The joins we used so far are called equi-joins because they use the equality sign ( = ) in the joining condition. Non-equi are all other joins that use any other operators – comparison operators ( < , > , <= , >= , != , <> ), the BETWEEN operator, or any other logical condition – to join tables.

We’ll use the dataset consisting of two tables. The first table is car . Here are its columns:

• id – The car’s ID in the database.
• model – The car’s model.
• brand – The car’s brand.
• original_price – The original price of that car when new.
• mileage – The car’s total mileage.
• prod_year – The car’s production year.

The data looks like this:

The second table is charity_auction with these columns:

• car_id – The car’s ID.
• initial_price – The car’s initial (i.e. starting) price.
• final_price – The actual price when the car was sold.
• buyer_id – The ID of the person who bought the car.

Exercise: Show the model, brand, and final price of each car sold at the auction. Consider only those sold cars that have more mileage than the car with the id = 4 .

Solution explanation: We select the car model, brand, and final price.

In the first JOIN , we join the car table with the charity_auction table. The tables are joined where the car IDs are the same. This is our regular equi JOIN .

We add the second JOIN , which is a self-join. It adds the table car again, so we can filter the data using the non-equi join condition. The condition will return all the cars from the car table and all the cars from the car2 table with the lower mileage. This is a non-equi condition as it uses the ‘greater than’ ( > ) operator. The syntax is the same, but there’s > instead of = this time.

Finally, we need to filter data using WHERE . We’re not interested in comparing the mileage of all cars. We want to show the cars that have a mileage higher than the car with id = 4 . This is what the first filtering condition does.

We add another filtering condition that says the final price shouldn’t be NULL , i.e., the car has to have been sold in the auction.

The result shows two cars:

SQL JOINs Practice Makes Perfect. More Practice? Perfect-er!

Twelve SQL join exercises is a solid amount of practice. Through these exercises, you could learn and practice all the most common join topics that trouble beginner and intermediate users.

Now, you just need to keep going! When you practice even more, you become even perfect-er. So if you liked our exercises, you can get more of the same in our SQL JOINS course or the article about the SQL JOIN interview questions .

Hope you ace all the exercises that await you there!

You may also like

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query

• All Platforms
• First Naukri
• All Companies
• Cognizant GenC
• Cognizant GenC Next
• Cognizant GenC Elevate
• Goldman Sachs
• Infosys SP and DSE
• TCS CodeVita
• TCS Digital
• TCS iON CCQT
• TCS Smart Hiring
• Tech Mahindra
• Zs Associates

• Top 100 Codes
• Learn Python
• Learn Data Structures
• Learn Competitve & Advanced Coding
• Learn Operating System
• Software Engineering
• Online Compiler
• Microsoft Coding Questions
• Amazon Coding Questions

• Learn Logical
• Learn Verbal
• Learn Data Interp.
• Psychometric Test

• All Syllabus
• Cognizant-Off Campus
• L&T Infotech
• Mahindra ComViva
• Reliance Jio
• Wells Fargo
• ZS-Associates

• Interview Preparation
• HR Interview
• Virtual Interview
• Technical Interview
• Group Discussions

• All Interview Exp.
• Accenture ASE
• ZS Associates

• Get OffCampus updates
• On Instagram
• On LinkedIn
• On Telegram
• On Whatsapp
• AMCAT vs CoCubes vs eLitmus vs TCS iON CCQT
• Companies hiring via TCS iON CCQT
• Companies hiring via CoCubes
• Companies hiring via AMCAT
• Companies hiring via eLitmus
• Companies hiring from AMCAT, CoCubes, eLitmus
• Prime Video
• PrepInsta Prime
• The Job Company
• Placement Stats

Notifications Mark All Read

7 Day TCS NQT Crash Course is available at just 1999. Click here to Purchase

• Get Prime

Oracle Coding Questions and Answers

August 12, 2023

Oracle Coding Questions with Solutions

Every year, Oracle searches for engineers to hire through On-Campus and Off-Campus Drives. Oracle Coding Questions and Answers Page will help you to get sample Coding Questions asked in the Online Assessment and Technical Interviews of Oracle.

Sample Oracle Coding Questions and Answers

Question 1:.

Problem Statement  : Ratan is a crazy rich person. And he is blessed with luck, so he always made the best profit possible with the shares he bought. That means he bought a share at a low price and sold it at a high price to maximize his profit. Now you are an income tax officer and you need to calculate the profit he made with the given values of stock prices each day. You have to calculate only the maximum profit Ratan earned. Note that: Ratan never goes into loss.

Example 1 : Price=[1,6,2] Ratan buys it on the first day and sells it on the second. Example 2 : Price=[9,8,6]

The Price always went down, Ratan never bought it.

Input Format: First line with an integer n, denoting the number days with the value of the stack Next n days, telling the price of the stock on that very day.

Output Format: Maximum profit done by Ratan in a single line. Constraints: Number of days <=10^8

Sample Input for Custom Testing:

STDIN 7 1 9 2 11 1 9 2

Sample Output :

Explanation :

The maximum profit possible is when Ratan buys it in 1 rupees and sells it in 11.

Prime Course Trailer

Related banners.

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Question 2:

Problem Description

Question – : There are two banks – Bank A and Bank B. Their interest rates vary. You have received offers from both banks in terms of the annual rate of interest, tenure, and variations of the rate of interest over the entire tenure.You have to choose the offer which costs you least interest and reject the other. Do the computation and make a wise choice.

The loan repayment happens at a monthly frequency and Equated Monthly Installment (EMI) is calculated using the formula given below :

EMI = loanAmount * monthlyInterestRate / ( 1 – 1 / (1 + monthlyInterestRate)^(numberOfYears * 12))

Constraints:

• 1 <= P <= 1000000
• 1 <=T <= 50
• 1<= N1 <= 30
• 1<= N2 <= 30

Input Format:

• First line: P principal (Loan Amount)
• Second line: T Total Tenure (in years).
• Third Line: N1 is the number of slabs of interest rates for a given period by Bank A. First slab starts from the first year and the second slab starts from the end of the first slab and so on.
• Next N1 line will contain the interest rate and their period.
• After N1 lines we will receive N2 viz. the number of slabs offered by the second bank.
• Next N2 lines are the number of slabs of interest rates for a given period by Bank B. The first slab starts from the first year and the second slab starts from the end of the first slab and so on.
• The period and rate will be delimited by single white space.

Output Format: Your decision either Bank A or Bank B.

Explanation:

• Output : Bank B
• Output : Bank A

Question 3 : Network Stream

Problem Statement :

A stream of n data packets arrives at a server. This server can only process packets that are exactly 2^n units long for some non-negative integer value of n (0<=n). All packets are repackaged in order to the 1 largest possible value of 2^n units. The remaining portion of the packet is added to the next arriving packet before it is repackaged. Find the size of the largest repackaged packet in the given stream.

Example arriving Packets = [12, 25, 10, 7, 8] The first packet has 12 units. The maximum value of 2^n that can be made has 2^n = 2^3 = 8 units because the next size up is 2^n = 2^4 = 16 (16 is greater than 12).

12 – 8 = 4 units are added to the next packet. There are 4 + 25 = 29 units to repackage, 2^n = 2^4 = 16 is the new size leaving 9 units (29-16 = 9)

Next packet is 9 + 10 = 29 unists & the maximum units(in 2^n) is 16 leaving 3 units. 3 + 7 = 10 , the max units is 8 Leaving 2 units, and so on. The maximum repackaged size is 16 units.

Returns: Long : the size of the largest packet that is streamed

Constraints : 1<=n<=10^5 1<=arriving Packets[i] size<=10^9

Sample input 0: 5 → number of packets=5 13→ size of packets=[13,25,12,2,8] 25 10 2 8 Sample output 0: 16

Question 4 :Borrow Number

Problem Statement  :

You have two numbers number1 and number2, your job is to check the number of borrow operations needed for subtraction of number1 from number2. If the subtraction is not possible then return the string not possible.

Answer : Not possible

Question 5:

Rahul has an array a , consisting of n integers a 1, a 2, …, an . The boy cannot sit and do nothing, he decided to study an array. Rahul took a piece of paper and wrote out m integers l 1,  l 2, …,  lm (1 ≤  li  ≤  n ). For each number li he wants to know how many distinct numbers are staying on the positions li , li  + 1, …, n . Formally, he want to find the number of distinct numbers among ali ,  ali  + 1, …,  an .?

Rahul wrote out the necessary array elements but the array was so large and the boy was so pressed for time. Help him, find the answer for the described question for each li .

• The first line contains two integers n and m (1 ≤  n ,  m  ≤ 105). The second line contains n integers a 1, a 2, …, an (1 ≤  ai  ≤ 105) — the array elements.
• Next m lines contain integers l 1,  l 2, …,  lm . The i -th line contains integer li (1 ≤  li  ≤  n ).
• Print m lines — on the i -th line print the answer to the number li .
• Input 10 10 1 2 3 4 1 2 3 4 100000 99999 1 2 3 4 5 6 7 8 9 10
• Output 6 6 6 6 6 5 4 3 2 1

Question 6 : Biggest Meatball

Problem Statement – Bhojon is a restaurant company and has started a new wing in a city. They have every type of cook except the meatball artist. They had fired their last cook because the sale of meatballs in their restaurant is really great, and they can’t afford to make meatballs again and again every time their stock gets empty. They have arranged a hiring program, where you can apply with their meatball. They will add the meatball in their seekh (a queue) and everytime they cut the meatball they take it and cut it on the day’s quantity and then re-add the meatball in the seekh. You are the hiring manager there and you are going to say who is gonna be hired.

Day’s quantity means, on that very day the company sells only that kg of meatballs to every packet.

If someone has less than a day’s quantity, it will be counted as a sell.

Function Description:

• Complete the function with the following parameters:

Parameters:

• The ith person whose meat is served at last.
• 1 <= N <= 10^3
• 1 <= D <= 10^3
• 1 <= Array[i] <= 10^3
• First line contains N.
• Second line contains D.
• After that N lines contain The ith person’s meatball weight.

Output Format: The 1 based index of the man whose meatball is served at the last.

Sample Input 1:

Sample Output 1:

The seekh or meatball queue has [7 8 9 3] this distribution. At the first serving they will cut 2 kgs of meatball from the first meatball and add it to the last of the seekh, so after 1st time it is:

Then, it is: [9 3 5 6],  [3 5 6 7], [5 6 7 1], [6 7 1 3], [7 1 3 4], [1 3 4 5], [3 4 5], [4 5 1], [5 1 2], [1 2 3], [2 3], [3], [1], [0]

So the last served meatball belongs to the 3rd person.

Question 7 : Street Light

Street Lights are installed at every position along a 1-D road of length n.

Locations[] (an array) represents the coverage limit of these lights. The ith light has a coverage limit of locations[i] that can range from the position max((i – locations[i]), 1) to min((i + locations[i]), n ) (Closed intervals). Initially all the lights are switched off. Find the minimum number of fountains that must be switched on to cover the road.

locations[] = {0, 2, 13}then

For position 1: locations[1] = 0, max((1 – 0),

1) to mini (1+0), 3) gives range = 1 to 1

For position 2: locations[2] = 2, max((2-2),

1) to min( (2+2), 3) gives range = 1 to 3

For position 3: locations[3] = 1, max( (3-1),

1) to min( (3+1), 3) gives range = 2 to 3

For the entire length of this road to be covered, only the light at position 2 needs to be activated.

Function Description

int: the minimum number of street lights that must be activated

Constraints

• 1<_n<_ 10^5
•  O<_locations[i] <_ mini (n,100) (where 1 <_1<_

► Input Format For Custom Testing

Sample Case 0

Sample Input For Custom Testing

3 ->locations[] size n = 3

1 ->locations[] [1, 1, 1]

1 ->Sample Output

Sample Output

Question 8:

A 10cm x 10cm x 10cm solid cube rests on the ground. It has a beetle on it, as well as some sweet honey spots on the cube’s surface. The beetle begins at a point on the cube’s surface and moves in a clockwise direction along the cube’s surface to the honey spots.

• If it goes from one point on the same face to another (say, X to Y), it goes in an arc of a circle that subtends an angle of 60 degrees at the circle’s center.
• If it travels from one point to another on a different face, it takes the shortest path on the cube’s surface, except that it never travels along its bottom.

The beetle is a Cartesian geometry student who knows the coordinates (x, y, z) of all the points it needs to visit. Its coordinate origin is one of the cube’s corners on the ground, and the z-axis points up. As a result, z=0 is the bottom surface (on which it does not crawl), and z=10 is the top. The beetle keeps track of all distances traveled and rounded the distance to two decimal places when it arrives at the following location so that the final distance is a sum of the rounded distances from spot to spot.

The first line returns an integer N, the total number of points visited by the beetle (including the starting point).

The second line contains 3N non-negative numbers, each with two decimal places. These are to be interpreted as the x, y, and z coordinates of the points the beetle must visit in the given order.

One line containing a number representing the total distance traveled by the beetle to two decimal places. Even if the travel distance is an integer, the output should have two decimal places.

None of the points visited by the beetle are on the bottom face (z=0) or on any of the cube’s edges (the lines where two faces meet)

2<=N<=10

Question 9 : Guess the word

Problem statement :  .

Kochouseph Chittilappilly went to Dhruv Zplanet , a gaming space, with his friends and played a game called “Guess the Word”. Rules of games are – Computer displays some strings on the screen and the player should pick one string / word if this word matches with the random word that the computer picks then the player is declared as Winner. Kochouseph Chittilappilly’s friends played the game and no one won the game. This is Kochouseph Chittilappilly’s turn to play and he decided to must win the game. What he observed from his friend’s game is that the computer is picking up the string whose length is odd and also that should be maximum. Due to system failure computers sometimes cannot generate odd length words. In such cases you will lose the game anyways and it displays “better luck next time”. He needs your help. Check below cases for better understand

Sample input 0: 5 → number of strings Hello Good morning Welcome you Sample output 0: morning

Explanation: Hello → 5 Good → 4 Morning → 7 Welcome → 7 You → 3 First word that is picked by computer is morning

Sample input 1: 3 Go to hell Sample output 1: Better luck next time Explanation:  Here no word with odd length so computer confuses and gives better luck next time

Question 10 : Array Subarray

You are given an array, You have to choose a contiguous subarray of length ‘k’, and find the minimum of that segment, return the maximum of those minimums. Sample input 0 : 1 → Length of segment x =1 5 → size of space n = 5 1 → space = [ 1,2,3,1,2] 2 3 1 2

Sample output : 3 Explanation : The subarrays of size x = 1 are [1],[2],[3],[1], and [2],Because each subarray only contains 1 element, each value is minimal with respect to the subarray it is in. The maximum of these values is 3. Therefore, the answer is 3

FAQs related to Oracle Coding Questions

Question 1: is coding questions asked in oracle recruitment process.

Yes, In Online Assessment and Technical Interviews Oracle asks DSA Based Coding Questions.

Question 2: How long is Oracle recruitment process?

Oracle usually takes 6 – 8 Weeks to complete whole Recruitment Process.

Question 3: What is Oracle interview process?

Before Technical Interview there will be a Group Discussion, conducted by Oracle for those applicants who have successfully passed Online Aptitude & Coding Assessments.

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Checkout list of all the video courses in PrepInsta Prime Subscription

Oracle Fusion HCM Absence Management Interview Questions

Oracle Absence Management (part of Oracle Cloud HCM ) is a highly configurable rule based application that enables you to efficiently manage employee absenteeism globally and locally. Implement your policies and rules consistently while you streamline your absence process administrative framework. Absence Management provides you the ability to reduce effects associated with absenteeism such as: cost, risks, and productivity. IQ Stream Technologies offer Fusion HCM online training for professionals to build new careers and growth in their respective fields. Explore through some of the interview questions below;

• How Absence Components Work Together?

2. What are Absence Components?

There are 6 components in absence management. Absence types, Absence categories, Absence patterns, Absence plans, Absence reasons, and Absence certifications.

3. What is the difference between an absence type and an absence plan?

Absence types: Use the Absence Types task to create absence types Absence plans: Use the Absence Plans task to create absence plans.

Absence types are used to record absence entries whereas absence plans are used to grant and deduct entitlements.

4. What is absence plan?

The absence plan is used to to create absence plans.

5. What are the rules for absence display and processing?

6. What is Assignment Selection Configuration?

Configure absence types for employees with multiple assignments so that managers, administrators, and employees can enter absences that impact either all assignments or a specific assignment. Select the  Allow assignment selection at absence entry  check box in the Type Attributes tab of Create/Edit absence type page to provide this option at absence entry.

What are the absence types in Oracle Fusion HCM?

The absence types in Oracle Fusion HCM can be broadly classified into four different categories, namely Sick Leave, Annual Leave, Vacation Leave, Marriage Leave, Compassionate Leave, Maternity leave, Paternity Leave etc.

How to create a Vacation Absence Type?

• In the Absences work area Tasks panel tab, click  Manage Absence Types .
• Click  Create .
• On the Create Absence Type dialog box, complete the fields, as shown in this table. Use default values for fields unless the steps specify other values.
• Click  OK .
• On the Create Absence Type page, Type Attributes tab, complete the fields, as shown in this table. Use default values for fields unless the steps specify other values.

Click the Display Features tab, In the Dates and Duration section, complete the following fields for the Advanced Absence Entry usage rule, Click Save and Close.

What are the Formulas for Absence Type?

The following table lists the aspects of an absence type for which you can write a formula and identifies the formula type for each.

What are the different plan types available in fusion absence management?

Oracle Fusion Absence Management provides the framework for your company to do just that. With a few clicks of the mouse, you can create plans for occupational sick plans, occupational maternity plans, vacation plans and more.

7. What is Qualified Entitlement Rule?

Configure this rule to show or hide the Qualified Entitlements section on the absence recording pages for qualification plans. When workers schedule an absence related to a qualification absence plan, the Qualified Entitlements section displays payment percentages that apply during the absence period.

8. What is Insufficient Balance Enforcement Rule?

Configure this rule to show or hide the Projected Balances section on the absence recording pages. When workers schedule an absence related to an accrual absence plan, an error message prevents workers from adding an absence if there is insufficient accrual balance.

9. What is Supplemental Details Configuration?

Control the display of additional fields that collect additional information and support absence processing. The fields vary based on the pattern selected.

10. What is Deferred processing in Absence Approval?

An absence that you schedule doesn’t have any impact on accrual balance or entitlements until you confirm the absence. You must run the Evaluate Absences process to confirm deferred absences.

11. Which attribute helps you configure the absence approval flow according to your organization needs?

Additional payload attribute helps you configure the absence approval flow according to your organization needs. This allows more flexibility in routing approvals within your organization.

12. How to set a limit on the duration and occurrence of an absence type?

Occurrence limit rule is used to set a limit on the duration and occurrence of an absence type.

13. How to allow daily absence duration override?

To enable the override daily duration rule:

In the Absences work area Tasks panel tab, click Absence Types.

In the Search Results section, click Create to open the Create Absence Type dialog box.

Select the pattern.

Click Continue to open the Create Absence Type page.

In the Type Attributes tab, enter the name.

Select the legislative data group.

Select the Display Features tab.

In the Dates and Duration section Override daily duration row, select Enabled.

Click Save and Close to return to the Absence Types page.

14. How HCM assignment security works with absences impacting multiple assignments?

Plan Balances

15. How you configure absence types to prevent absences from impacting multiple assignments?

You can use the Allow assignment selection at absence entry and Allow only one assignment per absence check boxes to configure an absence type to ensure that all absences using that type will need to be entered for a specific assignment.

Related Interview Questions:

Oracle Integration Cloud (OIC) Interview Questions

Azure logic apps interview questions.

Oracle HRMS Payroll Management Questions

Oracle SOA Interview Questions

16. How can I configure an absence type to include line managers of relevant assignments for approval?

You can configure approvers from the Absence Record Maintenance section of Create/Edit Absence Type page. If an employee has multiple assignments, you can select the Manager of all relevant assignments option from the approver list. This will send the absence approval request to all the line managers of relevant assignments.

17. How can I create an absence category?

You can create an absence category from the Absence Categories page in the Absences work area. Click Create and enter the details of the absence category like name, legislation and effective end date. You then need to select the absence type you want to associate with the absence category in the Associated Types section. Click Save and Close to complete the process.

18. How can I configure an absence type to show absence reasons?

After you create an absence reason, you need to associate it with an absence type. To do that, go to the Absence Reasons section of the Create/Edit Absence Type page and add the absence reason. When people try to add an absence for that absence type, they can select the reason from the Reason list.

19. How to evaluate absences marked for deferred processing and reevaluate absences that meet the process parameters?

You calculate entitlements for unprocessed absences using the Evaluate Absences process in the Absences work area. Use this process to evaluate absences marked for deferred processing and reevaluate absences that meet the process parameters.

This process runs automatically when you submit an absence. To defer the process, select the rule Deferred processing on initial entry in the Display Features tab of the Create Absence Type page. Decides which absence records are selected for processing;

Evaluate All Absences: Reprocesses all absence records that meet the other filtering options for the process

Evaluate Only Duration Changes: Reprocesses the absence records only if there is a change in absence duration

Evaluate Only Deferred Absences: Reprocesses deferred absences

Evaluate Only Open-ended Absences: Reprocesses open-ended absences that require an extension

Absence schedule process in fusion HCM: How an Individual’s Schedule Is Determined – You can set up an individual’s work time in different ways. So, the process that determines a person’s official schedule for a selected time period uses the individual’s current schedule or work hours. It also considers applicable calendar events, work schedule resource exceptions, and absence entries.

The primary work schedule links to one of these levels. Schedules that the process builds from the work schedule also show assigned calendar events and resource exceptions, as well as applicable absences.

Primary assignment of the person Position Job Department Location Legal Employer Enterprise

Published Schedule (Workforce Management): The process builds the published schedule using the employment work week, primary work schedule, or standard working hours for each person. It can also build it using published schedules from other scheduling applications. The published schedule shows applicable calendar events and absences.

Position synchronization process in Fusion HCM:

Position Synchronization: If position synchronization is enabled, assignments inherit specified values from the associated position.

Synchronized Attributes: You can select any of the following attributes for synchronization when position synchronization is enabled:

Department, Job, Location, Grade, Grade Ladder, Manager, Full Time or Part Time, Regular or Temporary, Assignment Category, FTE and Working Hours, Start Time and End Time, Probation Period, Union, Bargaining Unit and Collective Agreement, Synchronize Mapped Flexfields.

Reference: https://docs.oracle.com/en/cloud/saas/human-resources/21d/faiam/absence-types-reasons-and-categories.html#FAIAM1588520

Related Posts

Oracle fusion cloud technical interview questions, changing work email in oracle fusion hcm, creating fast formulas in oracle fusion hcm, write a comment.

Save my name, email, and website in this browser for the next time I comment.

• Java & Web
• Data Science
• Datawarehousing
• Computer Basics
• Become an instructor

WhatsApp us

Fusion HCM Analytics

• All Categories
• 12.4K Forums
• User Groups
• 1 Announcements
• Find Partners
• For Partners
• Hall of Fame: Spotlights
• Hall of Fame: Monthly Highlights
• Hall of Fame: Leaderboard

Goal Plan assignment

We need to create a DV for tracking the performance goals status, per review period and per business unit. It should display how many distinct employees have created 1 or more performance goals in their goal plan. It should also display how many distinct employees have not created any performance goals.

The FAW subject area 'HCM - Goals and Career Development' contains the created performance goals, however we cannot find the Goal Plan Assignment in FAW. In HCM OTBI, there is a subject area 'Workforce Goals - Goal Plan Assignment Real Time' which help us to know which employees have a goal plan assigned and are expected to create goals. Subtracting the result set of the goal plan assignments and the created goals, gives the population who should but have not created any goals. How could we accomplish this in FAW? 

• Oracle Fusion Analytics
• HCM Analytics Content

Best Answer

Hi @Orange yes, you are right. Currently, the subject area renders only the workers and their goal plans that have at least one goal under them. Does not provide the Goal Plan Assignment details. The subject area can be enhanced to include the goal plan assignment details. I shall keep you posted. You could also post this in the idea lab.

Please refer below

Looks like "Goal Plan assignment" is currently unavailable. Please submit this as an enhancement request in FAW Idea Lab. The Idea Lab is a special channel on Oracle Cloud Services where you can submit and vote on ideas, receive updates on the idea’s status, and collaborate directly with product management.

Also please votes for your idea to prioritize the visibility.

Other comments are welcome.

Hi @Subha_Tripathy-Oracle

Thanks for your response. I had checked the documentation you mentioned and also came to the conclusion that "Goal Plan assignment" is currently not available in FAW. I'm happy to raise an Idea and seek votes, but it's a lengthy process from idea to realization. With my question, I was hoping to hear work-around suggestions. For example, I could try to apply filters in a DV on 'HCM - Workforce Core' to mimic our goal plan eligibility rules, and join that population with 'HCM - Goals and Career Development' but not sure if that will work. I would greatly appreciate other work-around suggestions.

Kind regards 

@Raghavendra Raghunath-Oracle That would be great if the subject area could contain the workers who have the plan but no goals. We ran into the issue of needing to provide separate security to the goal plan subject area for our manager of L&D who should gets full company access in goals but line manager access for the core subject area, so that workaround didn't work for us.

Hi @Leanna Kirkland yes, this is in the roadmap and must be available in the 24R2 release

@Raghavendra Raghunath-Oracle That's great news, thanks Raghu!

• Skip to content
• Accessibility Policy
• Oracle blogs
• Lorem ipsum dolor
• EPM Cloud ,

Frequently Asked Questions (FAQs) covering various topics related to Oracle Enterprise Performance Management (EPM) Cloud

A new “ Frequently Asked Questions (FAQs) " section has been added to the Getting Started with Oracle Enterprise Performance Management Cloud for Administrators Guide.

This section compiles a range of Frequently Asked Questions (FAQs) along with their answers, covering various topics related to Oracle Enterprise Performance Management Cloud , as found in multiple EPM Cloud documentation resources.

Tanya Heise

Sr principal technical support engineer.

Tanya Heise is part of the Oracle Proactive Support team. During my years at Oracle, I have worked in both Technical Support and Consulting Services.

• Analyst Reports
• Cloud Economics
• Corporate Responsibility
• Diversity and Inclusion
• Security Practices
• What is Customer Service?
• What is ERP?
• What is Marketing Automation?
• What is Procurement?
• What is Talent Management?
• What is VM?
• Try Oracle Cloud Free Tier
• Oracle Sustainability
• Oracle COVID-19 Response
• Oracle and SailGP
• Oracle and Premier League
• Oracle and Red Bull Racing Honda
• US Sales 1.800.633.0738
• How can we help?
• Subscribe to Oracle Content
• © 2024 Oracle
• Privacy / Do Not Sell My Info

• Install App

Cloud Platform

For appeals, questions and feedback, please email [email protected]

assignmentDFF inconsistent behaviour with EffectiveDate UPDATE

Hi, I am referencing: https://docs.oracle.com/en/cloud/saas/human-resources/21d/farws/op-workers-workersuniqid-child-workrelationships-periodofserviceid-child-assignments-assignmentsuniqid-child-assignmentsdff-assignmentsdffuniqid-patch.html I can make a PATCH call to an assignment DFF and change a custom field value successfully using this call. However I have inconsistent behaviour with regards to EffectiveStartDate. Example: Assignment Start Date: 2021-12-01 myField: null I make the PATCH UPDATE call with sysdate (2022-01-09) and BODY {"myField":"sometext"} The outcome is Assignment Start Date: 2022-01-09 myField: sometext Yet on another worker assignment I use the same code to make the update and the outcome is: Assignment Start Date: 2021-12-01 myField: sometext Both calls report success 200 OK, yet I have different behaviours with regards to the EffectiveStartDate Essentially the first seems to have performed an UPDATE whilst the second has performed a CORRECTION. Yet both calls a PATCH with RangeMode=UPDATE

Does anyone have any idea why this would be the case? Thanks

• Using Learning

Preactive Statuses for Oracle Learning Assignments

You can track and control people's learning progress with learning assignment statuses. These are the statuses informally categorized as preactive.