12 Query optimization tips for better

performance
Monitoring metrics can be used to evaluate query runtime, detect performance
pitfalls, and show how they can be improved. For example, they include:

 Execution plan: A SQL Server query optimizer executes the query

step by step, scans indexes to retrieve data, and provides a detailed
overview of metrics during query execution.
 Input/Output statistics: Used to identify the number of logical and
physical reading operations during the query execution that helps
users detect cache/memory capacity issues.
 Buffer cache: Used to reduce memory usage on the server.
 Latency: Used to analyze the duration of queries or operations.
 Indexes: Used to accelerate reading operations on the SQL Server.
 Memory-optimized tables: Used to store table data in memory to
make reading and writing operations run faster.
Now, we’ll discuss the best SQL Server performance tuning practices and tips
you may apply when writing SQL queries.

Tip 1: Add missing indexes

Table indexes in databases help retrieve information faster and more efficiently.

In SQL Server, when you execute a query, the optimizer generates an execution
plan. If it detects the missing index that may be created to optimize
performance, the execution plan suggests this in the warning section. With this
suggestion, it informs you which columns the current SQL should be indexed,
and how performance can be improved upon completion.

Let’s run the Query Profiler available in dbForge Studio for SQL Server to see
how it works.
You can also understand which tables need indexes by analyzing graphical
query plans. The thicker the arrow between operators on the query execution
plan is, the more data is passed. Seeing thick arrows you need to think about
adding indexes to the tables being processed to reduce the amount of data
passed through the arrow.
On the execution plan, you might encounter Table Spool (Lazy Spool in our case)
that builds a temporary table in the tempdb and fills it in a lazy manner. Simply
put, the table is filled by reading and storing the data only when individual rows
are required by the parent operator. The Index Spool operator works in a
somehow similar manner — all input rows are scanned and a copy of each row is
placed in a hidden spool file that is stored in the tempdb database and exists
only for the lifetime of the query. After that, an index on the rows is built. Both
Table Spool and Index Spool might require optimization and adding indexes on
the corresponding tables.

Nested Loops might also need your attention. Nested Loops must be indexed, as
they take the first value from the first table and search for a match in the
second table. Without indexes, SQL Server will have to scan and process the
whole table, which can be time-consuming and resource-intensive.

Keep in mind that the missing index does not 100% guarantee better
performance. In SQL Server, you can use the following dynamic management
views to get a deep insight in using indexes based on query execution history:
  sys.dm_db_missing_index_details: Provides information about the
suggested missing index, except for spatial indexes.
 sys.dm_db_missing_index_columns: Returns information about the
table columns that do not contain indexes.
 sys.dm_db_missing_index_group_stats: Returns summary
information about the missing index group, such as query
cost, avg_user_impact (informs you how much performance can be
improved by increasing the missing index), and some other metrics to
measure effectiveness.
 sys.dm_db_missing_index_groups: Provides information about
missing indexes included in a specific index group.

Tip 2: Check for unused indexes

You may encounter a situation where indexes exist but are not being used. One
of the reasons for that might be implicit data type conversion. Let’s consider the
following query:

SELECT *

FROM TestTable

WHERE IntColumn = '1';

When executing this query, SQL Server will perform implicit data type
conversion, i.e. convert int data to varchar and run the comparison only after
that. In this case, indexes won’t be used. How can you avoid this? We
recommend using the CAST() function that converts a value of any type into a
specified datatype. Look at the query below.

SELECT *

FROM TestTable

WHERE IntColumn = CAST(@char AS INT);

Let’s study one more example.

SELECT *

FROM TestTable

WHERE DATEPART(YEAR, SomeMyDate) = '2021';

In this case, implicit data type conversion will take place too, and the indexes
won’t be used. To avoid this, we can optimize the query in the following way:

SELECT *

FROM TestTable

WHERE SomeDate >= '20210101'

AND SomeDate < '20220101'

Filtered indexes can affect performance too. Suppose, we have an index on the
Customer table.

CREATE UNIQUE NONCLUSTERED INDEX IX ON Customer (MembershipCode)

WHERE MembershipCode IS NOT NULL;

The index won’t work for the following query:

SELECT *

FROM Customer

WHERE MembershipCode = '258410';

To make use of the index, you’ll need to optimize the query in the following way:

SELECT *

FROM Customer

WHERE MembershipCode = '258410'

AND MembershipCode IS NOT NULL;

Tip 3: Avoid using multiple OR in the FILTER predicate

When you need to combine two or more conditions, it is recommended to
eliminate the usage of the OR operator or split the query into parts separating
search expressions. SQL Server can not process OR within one operation.
Instead, it evaluates each component of the OR which, in turn, may lead to poor
performance.
Let’s consider the following query.

SELECT *

FROM USER

WHERE Name = @P

OR login = @P;
If we split this query into two SELECT queries and combine them by using the
UNION operator, SQL Server will be able to make use of the indexes, and the
query will be optimized.

SELECT * FROM USER

WHERE Name = @P

UNION

SELECT * FROM USER

WHERE login = @P;

Tip 4: Use wildcards at the end of a phrase only

Wildcards in SQL Server work as a placeholder for words and phrases and can
be added at the beginning/end of them. To make data retrieval faster and more
efficient, you can use wildcards in the SELECT statement at the end of a phrase.
For example:

SELECT

p.BusinessEntityID

,p.FirstName

,p.LastName

,p.Title

FROM Person.Person p
WHERE p.FirstName LIKE 'And%';
As a result, the query will retrieve a list of customers whose First Name matches
the specified condition, i.e. their First Name starts with ‘And’.

However, you might encounter situations where you regularly need to search by
the last symbols of a word, number, or phrase — for example, by the last digits
of a telephone number. In this case, we recommend creating a persisted
computed column and running the REVERSE() function on it for easier back-
searching.

CREATE TABLE dbo.Customer (

id INT IDENTITY PRIMARY KEY

,CardNo VARCHAR(128)

,ReversedCardNo AS REVERSE(CardNo) PERSISTED

)

GO

CREATE INDEX ByReversedCardNo ON dbo.Customer (ReversedCardNo)

GO

CREATE INDEX ByCardNo ON dbo.Customer (CardNo)

GO

INSERT INTO dbo.Customer (CardNo)

SELECT

NEWID()

FROM master.dbo.spt_values sv

SELECT TOP 100

FROM Customer c

--searching for CardNo that end in 510c

SELECT *

FROM dbo.Customer

WHERE CardNo LIKE '%510c'

SELECT

FROM dbo.Customer

WHERE ReversedCardNo LIKE REVERSE('%510c')

Tip 5: Avoid too many JOINs

When you add multiple tables to a query and join them, you may overload the
server. In addition, a large number of tables to retrieve data from may result in
an inefficient execution plan. When generating a plan, the SQL query optimizer
needs to identify how the tables are joined, in which order, and how and when
to apply filters and aggregation.

All SQL experts are aware of the SQL JOINs importance, and understanding how
to use them in queries appropriately is critical. In particular, JOIN elimination is
one of the many techniques to achieve efficient query plans. You can split a
single query into several separate queries which can later be joined, and thus
remove unnecessary joins, subqueries, tables, etc.

Tip 6: Avoid using SELECT DISTINCT

The SQL DISTINCT operator is used to select only unique values of the column
and thus eliminate duplicated values. It has the following syntax:

SELECT DISTINCT column_name FROM table_name;

However, this may require the tool to process large volumes of data and as a
result, make the query run slowly. Generally, it is recommended to avoid using
SELECT DISTINCT and simply execute the SELECT statement but specify
columns.

Another issue is that quite often people build JOINs unnecessarily, and when the
data doubles, they add DISTINCT. This happens mainly in a leader-follower
relation when people do SELECT DISTINCT … FROM LEADER JOIN
FOLLOWER… instead of doing the correct SELECT … FROM LEADER WHERE
EXISTS (SELECT… FROM FOLLOWER).
Tip 7: Use SELECT fields instead of SELECT *
The SELECT statement is used to retrieve data from the database. In the case of
large databases, it is not recommended to retrieve all data because this will
take more resources on querying a huge volume of data.

If we execute the following query, we will retrieve all data from the Users table,
including, for example, users’ avatar pictures. The result table will contain lots of
data and will take too much memory and CPU usage.

SELECT

FROM Users;
Instead, you can specify the exact columns you need to get data from, thus,
saving database resources. In this case, SQL Server will retrieve only the
required data, and the query will have lower cost.

For example:

SELECT

FirstName

,LastName

,Email

,Login

FROM Users;
If you need to retrieve this data regularly, for example, for authentication
purposes, we recommend using covering indexes, the biggest advantage of
which is that they contain all the fields required by query and can significantly
improve query performance and guarantee better results.

CREATE NONCLUSTERED INDEX IDX_Users_Covering ON Users

INCLUDE (FirstName, LastName, Email, Login)

Tip 8: Use TOP to sample query results
The SELECT TOP command is used to set a limit on the number of records to be
returned from the database. To make sure that your query will output the
required result, you can use this command to fetch several rows as a sample.

For example, take the query from the previous section and define the limit of 5
records in the result set.

SELECT TOP 5

p.BusinessEntityID

,p.FirstName

,p.LastName

,p.Title

FROM Person.Person p

WHERE p.FirstName LIKE 'And%';

This query will retrieve only 5 records matching the condition:
Tip 9: Run the query during off-peak hours
Another SQL tuning technique is to schedule the query execution at off-peak
hours, especially if you need to run multiple SELECT queries from large tables or
execute complex queries with nested subqueries, looping queries, etc. If you are
running a heavy query against a database, SQL Server locks the tables you are
working with to prevent concurrent use of resources by different transactions.
That means that other users are not able to work with those tables. Thus,
executing heavy queries at peak times leads not only to server overload but
also to restricting other users’ access to certain amounts of data. One of the
popular mechanisms to avoid this is to use the WITH (NOLOCK) hint. It allows
the user to retrieve the data without being affected by the locks. The biggest
drawback of using WITH (NOLOCK) is that it may result in working with dirty
data. We recommend that users should give preference to snapshot isolation
which helps avoid data locking by using row versioning and guarantees that
each transaction sees a consistent snapshot of the database.

Tip 10: Minimize the usage of any query hint

When you face performance issues, you may use query hints to optimize
queries. They are specified in T-SQL statements and make the optimizer select
the execution plan based on this hint. Usually, query hints include NOLOCK,
Optimize For and Recompile. However, you should carefully consider their usage
because sometimes they may cause more unexpected side effects, undesirable
impacts, or even break business logic when trying to solve the issue. For
example, you write additional code for the hints that can be inapplicable or
obsolete after a while. This means that you should always monitor, manage,
check, and keep hints up to date.

Tip 11: Minimize large write operations

Writing, modifying, deleting, or importing large volumes of data may impact
query performance and even block the table when it requires updating and
manipulating data, adding indexes or check constraints to queries, processing
triggers, etc. In addition, writing a lot of data will increase the size of log files.
Thus, large write operations may not be a huge performance issue, but you
should be aware of their consequences and be prepared in case of unexpected
behavior.

One of the best practices in optimizing SQL Server performance lies in using
filegroups that allow you to spread your data across multiple physical disks.
Thereby multiple write operations can be processed simultaneously and thus
much faster.
Compression and data partitioning can also optimize performance and help
minimize the cost of large write operations.

Tip 12: Create JOINs with INNER JOIN (not WHERE)

The SQL INNER JOIN statement returns all matching rows from joined tables,
while the WHERE clause filters the resulting rows based on the specified
condition. Retrieving data from multiple tables based on the WHERE keyword
condition is called NON-ANSI JOINs while INNER JOIN belongs to ANSI JOINs.

It does not matter for SQL Server how you write the query – using ANSI or NON-
ANSI joins – it’s just much easier to understand and analyze queries written
using ANSI joins. You can clearly see where the JOIN conditions and the WHERE
filters are, whether you missed any JOIN or filter predicates, whether you joined
the required tables, etc.

Let’s see how to optimize a SQL query with INNER JOIN on a particular example.
We are going to retrieve data from the
tables HumanResources.Department and HumanResources.EmployeeDe
partmentHistory where DepartmentIDs are the same. First, execute the
SELECT statement with the INNER JOIN type:

SELECT

d.DepartmentID

,d.Name

,d.GroupName

FROM HumanResources.Department d

INNER JOIN HumanResources.EmployeeDepartmentHistory edh

ON d.DepartmentID = edh.DepartmentID
Then, use the WHERE clause instead of INNER JOIN to join the tables in the
SELECT statement:

SELECT

d.Name
,d.GroupName

,d.DepartmentID

FROM HumanResources.Department d

,HumanResources.EmployeeDepartmentHistory edh

WHERE d.DepartmentID = edh.DepartmentID

Both queries will output the following result:

SQL query optimization best practices

SQL Server performance tuning and SQL query optimization are some of the
main aspects for database developers and administrators. They need to
carefully consider the usage of specific operators, the number of tables on a
query, the size of a query, its execution plan, statistics, resource allocation, and
other performance metrics – all that may improve and tune query performance
or make it worse.

For better query performance, we recommend using tips and techniques

presented in the article, such as running queries at off-peak hours, creating
indexes, retrieving data only for the specific columns, applying the correct filter,
joins, and operators, as well as trying not to overload queries.

In addition, we propose some recommendations which may not directly relate to

coding techniques, but they can still help you write precise and efficient SQL
code.
Use uppercase for keywords

Keywords in SQL are generally case-insensitive. You can use lower case, upper
case, or both mixed across all popular database management systems,
including Microsoft SQL Server. However, it is recommended to use the upper
case for keywords for improved code readability.

Although some developers may find it cumbersome to switch between upper

and lower case while coding, modern SQL code formatting tools provide the
functionality to configure case usage, text coloring, indents, and other options.
These tools can automatically apply the preferable formatting while typing.

Write comments for your SQL code

Commenting on the code is optional, but it is highly recommended. Even if some

code solutions seem obvious at the moment, what happens in a couple of
months when you need to revisit it, especially after writing lots of other code for
different modules or projects? This is especially important for your colleagues
who will have to work with your code.

Another essential point is to review your existing comments whenever you make
changes to your code, ensuring that they remain relevant. It may take time, but
it greatly improves the readability of your code, and your efforts will pay off.

Use a professional SQL code editor

As a developer, you may apply various techniques and customize your

workflows according to your preferences, but creating code manually from
scratch consumes a lot of time and demands exceptional precision. A reliable
and potent SQL editor makes code writing easier and enhances accuracy.

Modern SQL editors offer robust functionality for query development, such as
auto-completion options, libraries of code snippets, syntax validation, and code
formatting. Advanced tools for SQL development allow developers to double the
coding speed twice (at least) and guarantee outstanding code quality.

Conclusion
In the article, we have covered a lot of fine-tuning techniques and tips to
improve performance. We hope that they will work for you and help you avoid
any performance issues that may arise.
Also, we suggest that you should try a free fully-functional 30-day trial version of
dbForge Studio for SQL Server to work with SQL queries effectively.

Basics of Oracle Database Performance

Tuning
Performance tuning in Oracle involves optimizing the performance of
the database by streamlining the SQL statement execution process. To
put it simply, Oracle database performance tuning makes accessing
and changing database information easier, resulting in improved
application operations and query response times.

Oracle tuning is used to troubleshoot performance issues. When a

network user reports slow-loading pages and application delays, it can
often be difficult for DBAs to identify the root cause. Database
bottlenecks can be caused by multiple system elements, including the
computer, the optimizer, and query statement coding. Oracle database
performance issues and solutions become clearer when tuning best
practices are implemented.

To effectively troubleshoot the cause of an issue, DBAs must consider

all the components of their relationship database management system.
This can be a time-consuming and frustrating process, with
comprehensive performance tuning operations touching on every
component of the system from top to bottom. This starts with system-
level tuning.

Without system-level tuning of the objects, instances, and servers in

your database, individual tuning of SQL statements will be largely
obsolete. DBAs should assess the following before tuning individual
SQL statements:

 Instance settings
 Input and output measures
 Optimizer statistics and parameters
If these components aren’t assessed appropriately, SQL tuning may be
reversed by the optimizer later.

Oracle Database Performance Tuning

Best Practices
There are six best practices DBAs should follow when conducting
Oracle database performance tuning:

1. Improving SQL execution efficiency

2. Increasing availability
3. Managing operating system resources
4. Optimizing storage performance
5. Using the query optimizer
6. Using and configuring memory
Best Practice One: Improving SQL Execution Efficiency

Understanding how to deliver SQL execution efficiency is a crucial part

of the system development process. It’s especially important during
the design stage. Every single database connection comes at a cost,
which is why DBAs should always endeavor to use the smallest number
of concurrent database connections as possible without compromising
the system. If you’re using multi-tiered applications, you should ensure
database connections are pooled together.
To reduce parsing activity, it’s important for user connections to be
appropriately maintained. Parsing refers to the process of creating an
execution strategy by analyzing SQL statements. This involves loading
shared structures, in addition to checking syntax and security.

A “hard parse” involves performing all the operations associated with a

parse. Hard parses are resource-intensive and difficult to scale. Soft
parses, on the other hand, require security checks which can
unnecessarily consume your system’s resources.
In other words, parses aren’t ideal, which is why they should be
reduced as much as possible. To do this, developers should design
applications capable of parsing SQL statements a single time, and then
executing them multiple times. You can use cursors to achieve this.
When opening and re-executing cursors, developers should ensure SQL
statements are shared with the shared pool. Bind variables, which
represent query parts, can be used to do this.

Best Practice Two: Increasing Availability

Enterprise applications are expected to offer maximum availability,

with near constant access. To achieve this, safeguards should be
established to protect the application from unanticipated downtime.
Typically, this involves employing architecture with redundant
elements. Oracle provides a blueprint called Maximum Availability
Architecture, which features all the components required to eradicate
data center complexity.

Oracle’s Maximum Availability Architecture uses redundant

components to boost the efficiency of production systems. Real
Application Clusters offer great scalability possibilities for enterprise
applications and protect against potential server failures. The
architecture uses built-in Data Guard technology, which facilitates
effective disaster recovery, along with an Active Data Guard, which
allows certain processes to be offloaded to standby systems. This
includes backup and reporting operations.

Within the Data Guard environment, users can be easily transferred

between standby and production databases. Both the operating
system and database version variations are supported, which means a
standby database can be upgraded, along with any users of the earlier
version.
Best Practice Three: Managing Operating System Resources

There are numerous causes of operating system performance issues.

Many are tied up with scheduling, process, and memory management.
If you’ve tuned the Oracle database instance and find you still need to
boost performance, then you may lower the system time. Ensure
there’s a sufficient amount of swap space, CPU power, and
input/output (I/O) bandwidth.

Many platforms provide resource managers for operating systems,

which can reduce the impact of peak load use patterns. It’s important
to note operating system resource managers aren’t the same as
domains, which can create entirely distinct environments within a
single system. Provided the partitioned memory resource allocation is
performing appropriately, Oracle is able to run within domains, and
other constructs using a less partitioning model. The allocation of
resources within a global pool is usually prioritized by administrators.

If you have nodes with multiple instances and intend on distributing

resources between them, you should assign each instance to a
resource manager group. If you plan on running numerous instances in
the managed entity, the distribution of CPU resources among instances
should be managed by instance caging. When CPU resources are being
managed, the Oracle Database Resource Manager requires a fixed
number of CPU resources for each instance. When instance caging is
used, the Resource Manager assumes the amount of available CPU
resources is equal to the total number of CPUs within the managed
entity.

If you think there might be a CPU issue to address, you’ll need to

identify whether sufficient CPU resources are available. It’s essential to
recognize when too many resources are being consumed by the
system. Oracle features three utilities—Statspack, UTLBSTAT, and
Automatic Workload Repository—which can be useful for capturing
various workload snapshots.

While 90% CPU utilization with 10% waiting time may be acceptable
during peak hours, it’s important to note there’s a significant problem
if your system displays high use during ordinary workload levels.

Best Practice Four: Optimizing Storage Performance

IT management teams will always strive to minimize the need to use
additional storage. If reducing storage costs is a priority, you can do so
by partitioning underneath big databases.

Typically, enterprises employ two storage tiers. One is a high-end

storage array used by mission critial applications. The second is a
lower-cost storage array used by less demanding applications. Most
databases include a combination of active data (such as call records)
and more passive data (like orders taken in the previous year). Active
data might only take up 5% of your database storage.

Sizing your disk storage to match the workload is a key part of

optimizing your storage performance. Random I/O operations are
actuated by deletes, inserts, and updates activated by queries
scanning the table in bulk portions. These random I/O operations are
calculated in I/O operations per second, while sequential I/O operations
are calculated as several megabytes scanned per second (MB/s).
Storage arrays should be sized according to the number of I/O
operations per second they can deliver.

You can work out the total number of I/O operations per second and
MB/s required for your existing databases by checking the workload
repository reports.

Best Practice Five: Using the Query Optimizer

The query optimizer is a built-in software component responsible for

choosing how SQL statements are executed. They can be executed by
a database in multiple ways, including through nested loops, hash
joins, index scans, and full table scans. While examining the execution
plan, the query optimizer can acknowledge numerous factors related
to the conditions and objects within a query. This is a key part of SQL
processing, and when done right, it can significantly reduce execution
time.

There are three main components of a query optimizer:

1. Query transformer: Every query portion is a query block.

The input into the query transformer is a parsed query
defined by a collection of query blocks. The transformer is
responsible for determining whether rewriting the existing
 statements into semantically equivalent statements is
beneficial.
2. Estimator: The estimator examines the cost of the execution
plan. It uses the statistics available to make its calculations.
Ultimately, the estimator can improve accuracy.
3. Plan generator: The plan generator considers a range of
plans for a query block by testing different join methods and
access paths. The plan with the lowest cost is then selected.
Best Practice Six: Using and Configuring Memory

Oracle usually advises users to employ automatic memory

management, but you also have the option of altering memory pools
manually. Information is stored on disk and in memory caches. Memory
access is faster than disk access, which means the disk access can
increase the amount of CPU resources required.

To boost performance, you can configure your system so the data

requests of frequently accessed objects are managed by memory
instead of disk. Keep in mind the aim is to reduce the physical I/O
overhead as much as possible, by optimizing the data retrieval
process.

Oracle’s Automatic Shared Memory Management feature can make

SGA configuration much simpler. To make the most of it, you’ll need to
set your SGA_TARGET initialization parameter to a non-zero value. You
should also set the STATISTICS_LEVEL parameter to TYPICAL and the
SGA_TARGET parameter to the amount of memory you’d like allocated
for the SGA.

Appropriate configuration and use of the buffer cache can also boost
database performance. Oracle will bypass the buffer cache for specific
operations, including parallel reads. However, you can use the buffer
cache by tuning SQL statements for the application. If you’re using
parallel queries, alter your configurations so the buffer cache is used
instead of direct reads being performed into the PGA. If your server has
a large memory capacity, this configuration is especially useful.

You can analyze buffer cache activity by using the Buffer Cache Hit
Ratio or V$DB_CACHE_ADVICE. If you need to use the latter, then the
DB_CACHE_ADVICE parameter should be ON. The buffer cache hit ratio
expresses how often a requested block could be detected in the buffer
cache without the disk being accessed. This ratio should be used to
verify the physical I/O.

The configuration of your redo log buffer is another important element

to consider. Redo data is created when a server process alters data
blocks in the buffer cache. While the log writer writes the redo entries
into a redo log, the new entries can be copied over earlier entries in
the memory because of user processes.

Nine SQL Query Performance Tuning Tips

The above list of Oracle database performance tuning best practices
takes a broad approach and isn’t exhaustive by any means. This
section digs deeper to offer nine tips specific to SQL query
performance tuning.

1. Remember SQL query performance tuning is an ongoing

process, and there’s always something to do. It can feel
never-ending, which is why it’s important to isolate high-
impact SQL statements at the start of the tuning process.
These cost-intensive queries are the ones executed most
regularly and require the most I/O and database activity.
Prioritizing the isolation of these high-impact SQL
statements will offer the greatest performance
improvement returns.
2. Always try to minimize the amount of data to be scanned
during an operation. Many query statements will send a
prompt to the database asking it to perform a full-table
scan. This uses more I/O than is necessary and can slow
down operations. You can streamline the process of data
retrieval by doing the following:
 Add indexes to tables if the data you need to access
comprises less than 5% of the total data stored. Don’t apply
this tip to small tables, which are searched in full more
expediently.
 Avoid including the asterisk symbol (*) in your SELECT
statement queries, unless it is necessary for retrieving data.
Note this symbol will load the system.
 Restrict the size of the data set by using filters in WHERE
clauses.
  In a column-oriented system, only select the columns
needed to perform the query.
 Delete unnecessary tables from query statements.
 Use EXISTS in subqueries. This tells Oracle it can halt the
search, rather than complete a full-table scan by default,
when a match is discovered.
3. Don’t use indexes in tables undergoing a large number of
INSERT or UPDATE operations, as indexes can reduce the
speed of data input. It’s also recommended you drop
indexes when conducting batch updates or insertions.
Instead, re-create the indexes after one batch event, or
avoid indexes on tables regularly experiencing batch loads.
4. Don’t mix data types and avoid converting numbers to
characters.
5. In some cases, it can be easier to create a new field than to
perform a calculation function on a WHERE or JOIN clause.
The new field should contain the calculated value, which the
statement would SELECT instead of making the calculation
itself.
6. Go through your SQL syntax and ensure statements have
been written so they’re aligned with the data structure.
7. By using procedures instead of single statements, you can
institute a protocol. A procedure is a collection of
statements. Using a procedure can reduce the cost of
executing a query occurring regularly.
8. Use global temporary tables where possible. This simplifies
complicated summarization queries and can improve
database performance.
9. Make tuning a routine process. Habitual maintenance can
prevent database performance from deteriorating over
time.

Oracle Database Performance Tuning

Tools
Implementing these Oracle database performance tuning best
practices manually is difficult. Fortunately, there are plenty of Oracle
database performance tuning tools designed to streamline your tuning
operations. These tools can automate database performance tracking
and help you identify the source of performance bottlenecks.
One of the best Oracle database performance tuning tools currently
available is SolarWinds Database Performance Analyzer (DPA).
This tool was designed specifically for Oracle and delivers a range of
highly advanced features through a simple user interface. DPA offers
response-time analytics, which help you correlate activity, SQL
statements, wait events, and numerous other dimensions. With
the ability to drill down into database monitoring data, identifying the
root cause of database slowdowns becomes simple.

This tool helps you understand your Oracle performance by giving you
an at-a-glance view of key dimensions, including waits, applications,
SQL statements, client machines, users, and much more. You can gain
insight into your database performance in a matter of seconds, with
data displayed graphically, in the form of charts and dials.

DPA includes a blocking analysis capability, which allows you to view

your Oracle blocking hierarchy along with the overall impact blocking
has on your database performance. It also features anomaly detection
powered by machine learning. With DPA’s intelligent algorithm, which
improves over time, you can gain a comprehensive understanding of
your Oracle wait behavior.
The query performance analysis capabilities of this tool are highly
advanced. You can conduct SQL and plan analysis, in addition to
correlating query wait time with resource metrics, statistics, blocking
data, and so on. DPA even gives you access to tuning advisors, who
can help you achieve your Oracle database performance tuning goals
and pinpoint areas needing to be prioritized.

With DPA’s management API, you can automate and scale the
management of your database environment. The management REST
API can be used to programmatically manage the ability to license,
register, stop and start monitors, annotate, and more.

One of the reasons DPA is so popular is its agentless architecture

supports collaborations between teams. Developers, DBAs, and
operations teams can use the simple web interface to work together.
With read-only rights, you can restrict access to certain functions, so
DPA is safe to use across multiple teams.

DPA comes with comprehensive alerting and reporting utilities. The

alerts have been designed to prevent your inbox from being
overloaded with irrelevant notifications. You can use pre-built
monitoring alerts or create your own, setting thresholds so you can
choose exactly what behaviors you’d like to be informed on. This keeps
noncritical notifications, or notifications your business doesn’t consider
a priority, to a minimum.

With ample maintenance support, including custom metrics, index

advice, and intuitive dashboard features, DPA is a flexible and cleverly
designed tool. It requires no training or experience to get started and
is highly scalable, making it suitable for small-scale and large-scale
business use.
1) What are the components of physical database structure of
Oracle database?
Components of physical database structure are given below.

o One or more data files.

o Two or more redo log files.
o One or more control files.

2) What are the components of logical database structure in

Oracle database?
Components of logical database structure.

o Tablespaces
o Database's schema objects

3) What is a tablespace?
A database contains Logical Storage Unit called tablespaces. A tablespace is
a set of related logical structures. Actually a tablespace groups related
logical structures together.

4) What is a SYSTEM tablespace and when it is created?

When the database is created in Oracle database system, it automatically
generate a SYSTEM named SYSTEM tablespace. The SYSTEM tablespace
contains data dictionary tables for the entire database.

5) What is an Oracle table?

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

6) In the Oracle version 9.3.0.5.0, what does each number

shows?
Oracle version number refers:

o 9 - Major database release number

o 3 - Database maintenance release number
o 0 - Application server release number
o 5 - Component Specific release number
o 0 - Platform Specific release number

7) What is bulk copy or BCP in Oracle?

Bulk copy or BCP in Oracle, is used to import or export data from tables and
views but it does not copy structure of same data.

The main advantage of BCP is fast mechanism for coping data and you can
also take the backup of data easily.

8) What is the relationship among database, tablespace and data

file?
An Oracle database contains one or more logical storage units called
tablespaces. These tablespaces collectively store whole data of databases
and each tablespace in Oracle database consists of one or more files called
datafiles. These datafiles are physical structure that confirm with the
operating system in which Oracle is running.

9) What is a snapshot in Oracle database?

A snapshot is a replica of a target master table from a single point-in-time. In
simple words you can say, snapshot is a copy of a table on a remote
database.

10) What is the difference between hot backup and cold backup in
Oracle? Tell about their benefits also.
Hot backup (Online Backup): A hot backup is also known as online backup
because it is done while the database is active. Some sites can not shut
down their database while making a backup copy, they are used for 24 hour
a day, 7 days a week.

Cold backup (Offline Backup): A cold backup is also known as offline

backup because it is done while the database has been shutdown using the
SHUTDOWN normal command. If the database is suddenly shutdown with a
uncertain condition it should be restarted with RESTRICT mode and then
shutdown with NORMAL option.

For a complete cold backup the following files must be backed up.

All datafiles, All control files, All online redo log files(optional) and the init.ora
file (you can recreate it manually).

11) How many memory layers are in the Oracle shared pool?
Oracle shared pools contains two layers:

1. library cache
2. data dictionary cache

12) What is save point in Oracle database?

Save points are used to divide a transaction into smaller parts. It allows
rolling back of a transaction. Maximum five save points are allowed. It is
used to save our data, whenever you encounter an error you can roll back
from the point where you save your SAVEPOINT.
13) What is hash cluster in Oracle?
Hash cluster is a technique to store a data in hash table and improve the
performance of data retrieval. Hash function is applied on table row's cluster
key value and store in hash cluster.

14) What are the various Oracle database objects?

Tables: This is a set of elements organized in vertical and horizontal fashion.

Tablespaces: This is a logical storage unit in Oracle.

Views: It is virtual table derived from one or more tables.

Indexes: This is a performance tuning method to process the records.

Synonyms: This is a name for tables.

15) What is the difference between pre-select and pre-query?

A pre-query trigger fire before the query executes and fire once while you try
to query. With the help of this trigger you can modify the where clause part
dynamically.

Pre-select query fires during the execute query and count query processing
after Oracle forms construct the select statement to be issued, but before
the statement is actually issued.

Pre-query trigger fires before Pre-select trigger.

16) What are the different types of modules in Oracle forms?

Following are the different modules in Oracle forms:

o Form module
o Menu module
o Pl/SQL Library module
 o Object Library module

17) What is the usage of ANALYZE command in Oracle?

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

o It is used to identify migrated and chained rows of the table or cluster.

o It is used to validate the structure of the object.
o It helps in collecting the statistics about object used by the optimizer.
They are then stored in the data dictionary.
o It helps in deleting statistics used by object from the data dictionary.

18) Can you create a synonym without having a table?

Yes. We can create a synonym without having a base table.

19) What types of joins are used in writing SUBQUERIES?

o Self join
o Outer Join
o Equi-join

20) What is the usage of control file in Oracle?

In Oracle, control file is used for database recovery. The control file is also
used to identify the database and redo log files that must be opened for
database operation to go ahead, whenever an instance of an ORACLE
database begins.
21) What is a synonym?
A synonym is also known as alias for a table, view, sequence or program
unit.

22) What are the different types of synonyms?

There are two types of synonyms or alias:

Private: It can only accessed by the owner.

Public: It can be accessed by any database user.

23) What is the usage of synonyms?

o Synonym can be used to hide the real name and owner of an object.
o It provides public access to an object.
o It also provides location transparency for tables, views or program
units of a remote database.
o It simplifies the SQL statements for database users.

24) How do you store pictures in a database?

Yes, you can store pictures in a database using Long Raw Data type. This
data type is used to store binary data for 2 gigabytes of length. However, the
table can have only one Long Raw data type.

25) What is BLOB data type in Oracle?

BLOB data type is a data type with varying length binary string. It is used to
store two gigabytes memory. For BLOB data type, the length needs to be
specified in bytes.
26) What is the difference between TRANSLATE and REPLACE
in Oracle?
Translate is used to substitute a character by character while Replace is used
to substitute a single character with a word.

27) What are the different types of database objects?

A list of different types of database objects:

o Tables: This is a set of elements organized in vertical and horizontal

fashion.
o Tablespaces: This is a logical storage unit in Oracle.
o Views: It is virtual table derived from one or more tables.
o Indexes: This is a performance tuning method to process the records.
o Synonyms: This is a name for tables.

28) What is the usage of Save Points in Oracle database?

Save Points are used to divide a transaction into smaller phases. It enables
rolling back part of a transaction. There are maximum 5 save points allowed
in Oracle Database. Whenever an error is encountered, it is possible to
rollback from the point where the SAVEPOINT has been saved.

29) What is the difference between post-database commit and

post-form commit?
The post-database commit trigger is executed after Oracle forms issue the
commit to finalized transaction while, the post-form commit is fired during
the post and commit transactions process, after the database commit
occurs.

30) What is Logical backup in Oracle?

Logical backup is used to read a set of database records and writing them
into a file. An Export utility is used to take the backup while an Import utility
is used to recover from the backup.

31) What do you understand by Redo Log file mirroring?

Mirroring is a process of having a copy of Redo log files. It is done by creating
group of log files together. This ensures that LGWR automatically writes
them 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. It
diminishes the performance.

32) What is the meaning of recursive hints in Oracle?

The number of times a dictionary table is repeatedly called by various
processes is known as recursive hint. Recursive hint is occurred because of
the small size of data dictionary cache.

33) What are the limitations of CHECK constraint?

The main limitation of CHECK constraint is that the condition must be a
Boolean expression evaluated using the values in the row being inserted or
updated and can't contain sub queries.

34) What is the use of GRANT option in IMP command?

GRANT is used to import object grants.

35) What is the use of ROWS option in IMP command?

The ROWS option indicates whether the table rows should be imported.
36) What is the use of INDEXES option in IMP command?
The INDEXES option is used to determine whether indexes are imported.

37) What is the use of IGNORE option in IMP command?

The IGNORE option is used to specify how object creation errors should be
handled.

38) What is the use of SHOW option in IMP command?

The SHOW option specifies when the value of show=y, the DDL within the
export file is displayed.

39) What is the use of FILE param in IMP command?

FILE param is used to specify the name of the export file to import. Multiple
files can be listed, separated by commas.

40) How to convert a date to char in Oracle? Give one example.

The to_char() function is used to convert date to character. You can also
specify the format in which you want output.

1. SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'YYYY-MM-

DD') FROM dual;

Or,

1. SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'DD-MM-

YYYY') FROM dual;

41) What are actual and formal parameters?

Actual Parameters: Actual parameters are the variables or expressions
referenced in the parameter list of a subprogram.

Let's see a procedure call which lists two actual parameters named empno
and amt:

1. raise_sal(empno, amt);

Formal Parameters: Formal parameters are variables declared in a

subprogram specification and referenced in the subprogram body.

Following procedure declares two formal parameters named empid and amt:

1. PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary

REAL;

42) What are the extensions used by Oracle reports?

Oracle reports are use to make business enable with the facility to provide
information of all level within or outside in a secure way. Oracle report
uses REP files and RDF file extensions.

43) How to convert a string to a date in Oracle database?

Syntax: to_date (string , format)

Let us take an example :

1. to_date ('2012-12-12', 'YYYY/MM/DD')

It will return December 12, 2012.

44) How do you find current date and time in Oracle?

The SYSDATE() function is used in Oracle to find the current date and time
of operating system on which the database is running.
 1. SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Current_Dat
e" FROM DUAL;

45) What will be the syntax to find current date and time in format
"YYYY-MM-DD"?

1. SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') "Current_Dat

e" FROM DUAL;

PL/SQL Interview Questions

PL/SQL is an advance version of SQL. There are given top list of PL/SQL
interview questions with answer.

1) What is PL/SQL?
PL/SQL stands for procedural language extension to SQL. It supports
procedural features of programming language and SQL both. It was
developed by Oracle Corporation in early of 90's to enhance the capabilities
of SQL.

2) What is the purpose of using PL/SQL?

PL/SQL is an extension of SQL. While SQL is non-procedural, PL/SQL is a
procedural language designed by Oracle. It is invented to overcome the
limitations of SQL.

3) What are the most important characteristics of PL/SQL?

A list of some notable characteristics:

o PL/SQL is a block-structured language.

o It is portable to all environments that support Oracle.
 o PL/SQL is integrated with the Oracle data dictionary.
o Stored procedures help better sharing of application.

4) What is PL/SQL table? Why it is used?

Objects of type tables are called PL/SQL tables that are modeled as database
table. We can also say that PL/SQL tables are a way to providing arrays.
Arrays are like temporary tables in memory that are processed very quickly.
PL/SQL tables are used to move bulk data. They simplifies moving collections
of data.

5) What are the datatypes available in PL/SQL?

There are two types of datatypes in PL/SQL:

1. Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG,

BOOLEAN etc.
2. Composite datatypes Example are RECORD, TABLE etc.

6) What is the basic structure of PL/SQL?

PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program
consists of SQL and PL/SQL statement which form a PL/SQL block.

PL/SQL block contains 3 sections.

1. The Declaration Section (optional)

2. The Execution Section (mandatory)
3. The Exception handling Section (optional)

7) What is the difference between FUNCTION, PROCEDURE

AND PACKAGE in PL/SQL?
Function: The main purpose of a PL/SQL function is generally to compute
and return a single value. A function has a return type in its specification and
must return a value specified in that type.

Procedure: A procedure does not have a return type and should not return
any value but it can have a return statement that simply stops its execution
and returns to the caller. A procedure is used to return multiple values
otherwise it is generally similar to a function.

Package: A package is schema object which groups logically related PL/SQL

types , items and subprograms. You can also say that it is a group of
functions, procedure, variables and record type statement. It provides
modularity, due to this facility it aids application development. It is used to
hide information from unauthorized users.

8) What is exception? What are the types of exceptions?

Exception is an error handling part of PL/SQL. There are two type of
exceptions: pre_defined exception and user_defined exception.

9) How to write a single statement that concatenates the words ?

Hello? and ?World? and assign it in a variable named Greeting?
Greeting := 'Hello' || 'World';

10) Does PL/SQL support CREATE command?

No. PL/SQL doesn't support the data definition commands like CREATE.

11) Write a unique difference between a function and a stored

procedure.
A function returns a value while a stored procedure doesn?t return a value.
12) How exception is different from error?
Whenever an Error occurs Exception arises. Error is a bug whereas exception
is a warning or error condition.

13) What is the main reason behind using an index?

Faster access of data blocks in the table.

14) What are PL/SQL exceptions? Tell me any three.

1. Too_many_rows
2. No_Data_Found
3. Value_error
4. Zero_error etc.

15) How do you declare a user-defined exception?

You can declare the User defined exceptions under the DECLARE section,
with the keyword EXCEPTION.

Syntax:

1. <exception_name> EXCEPTION;

16) What are some predefined exceptions in PL/SQL?

A list of predefined exceptions in PL/SQL:

o DUP_VAL_ON_INDEX
o ZERO_DIVIDE
o NO_DATA_FOUND
o TOO_MANY_ROWS
 o CURSOR_ALREADY_OPEN
o INVALID_NUMBER
o INVALID_CURSOR
o PROGRAM_ERROR
o TIMEOUT _ON_RESOURCE
o STORAGE_ERROR
o LOGON_DENIED
o VALUE_ERROR
o etc.

17) What is a trigger in PL/SQL?

A trigger is a PL/SQL program which is stored in the database. It is executed
immediately before or after the execution of INSERT, UPDATE, and DELETE
commands.

18) What is the maximum number of triggers, you can apply on a

single table?
12 triggers.

19) How many types of triggers exist in PL/SQL?

ADVERTISEMENT

There are 12 types of triggers in PL/SQL that contains the combination of

BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.

o BEFORE ALL ROW INSERT

o AFTER ALL ROW INSERT
o BEFORE INSERT
o AFTER INSERT etc.
20) What is the difference between execution of triggers and
stored procedures?
A trigger is automatically executed without any action required by the user,
while, a stored procedure is explicitly invoked by the user.

21) What happens when a trigger is associated to a view?

When a trigger is associated to a view, the base table triggers are normally
enabled.

22) What is the usage of WHEN clause in trigger?

A WHEN clause specifies the condition that must be true for the trigger to be
triggered.

23) How to disable a trigger name update_salary?

ALTER TRIGGER update_salary DISABLE;

24) Which command is used to delete a trigger?

DROP TRIGGER command.

25) what are the two virtual tables available at the time of
database trigger execution?
Table columns are referred as THEN.column_name and NOW.column_name.

For INSERT related triggers, NOW.column_name values are available only.

For DELETE related triggers, THEN.column_name values are available only.

For UPDATE related triggers, both Table columns are available.

26) What is stored Procedure?

A stored procedure is a sequence of statement or a named PL/SQL block
which performs one or more specific functions. It is similar to a procedure in
other programming languages. It is stored in the database and can be
repeatedly executed. It is stored as schema object. It can be nested, invoked
and parameterized.

27) What are the different schemas objects that can be created
using PL/SQL?

o Stored procedures and functions

o Packages
o Triggers
o Cursors

28) What do you know by PL/SQL Cursors?

Oracle uses workspaces to execute the SQL commands. When Oracle
processes a SQL command, it opens an area in the memory called Private
SQL Area. This area is identified by the cursor. It allows programmers to
name this area and access it?s information.

29) What is the difference between the implicit and explicit

cursors?
Implicit cursor is implicitly declared by Oracle. This is a cursor to all the DDL
and DML commands that return only one row.

ADVERTISEMENT
Explicit cursor is created for queries returning multiple rows.

30) What will you get by the cursor attribute SQL%ROWCOUNT?

The cursor attribute SQL%ROWCOUNT will return the number of rows that
are processed by a SQL statement.

31) What will you get by the cursor attribute SQL%FOUND?

It returns the Boolean value TRUE if at least one row was processed.

32) What will you get by the cursor attribute SQL%NOTFOUND?

It returns the Boolean value TRUE if no rows were processed.

33) What do you understand by PL/SQL packages?

A PL/SQL package can be specified as a file that groups functions, cursors,
stored procedures, and variables in one place.

34) What are the two different parts of the PL/SQL packages?
PL/SQL packages have the following two parts:

Specification part: It specifies the part where the interface to the

application is defined.

Body part: This part specifies where the implementation of the specification
is defined.

35) Which command is used to delete a package?

The DROP PACKAGE command is used to delete a package.

36) How to execute a stored procedure?

There are two way to execute a stored procedure.

From the SQL prompt, write EXECUTE or EXEC followed by procedure_name.

1. EXECUTE or [EXEC] procedure_name;

Simply use the procedure name

1. procedure_name;

37) What are the advantages of stored procedure?

Modularity, extensibility, reusability, Maintainability and one time
compilation.

38) What are the cursor attributes used in PL/SQL?

%ISOPEN: it checks whether the cursor is open or not.

%ROWCOUNT: returns the number of rows affected by DML operations:

INSERT,DELETE,UPDATE,SELECT.

%FOUND: it checks whether cursor has fetched any row. If yes - TRUE.

%NOTFOUND: it checks whether cursor has fetched any row. If no - TRUE.

39) What is the difference between syntax error and runtime

error?
A syntax error can be easily detected by a PL/SQL compiler. For example:
incorrect spelling etc. while, a runtime error is handled with the help of
exception-handling section in a PL/SQL block. For example: SELECT INTO
statement, which does not return any rows.

40) Explain the Commit statement.

Following conditions are true for the Commit statement:

o Other users can see the data changes made by the transaction.
o The locks acquired by the transaction are released.
o The work done by the transaction becomes permanent.

41) Explain the Rollback statement?

The Rollback statement is issued when the transaction ends. Following
conditions are true for a Rollback statement:

o The work done in a transition is undone as if it was never issued.

o All locks acquired by transaction are released.

42) Explain the SAVEPOINT statement.

With SAVEPOINT, only part of transaction can be undone.

43) What is mutating table error?

Mutating table error is occurred when a trigger tries to update a row that it is
currently using. It is fixed by using views or temporary tables.

44) What is consistency?

Consistency simply means that each user sees the consistent view of the
data.
Consider an example: there are two users A and B. A transfers money to B's
account. Here the changes are updated in A's account (debit) but until it will
be updated to B's account (credit), till then other users can't see the debit of
A's account. After the debit of A and credit of B, one can see the updates.
That?s consistency.

45) What is cursor and why it is required?

A cursor is a temporary work area created in a system memory when an
SQL statement is executed.

A cursor contains information on a select statement and the row of data

accessed by it. This temporary work area stores the data retrieved from the
database and manipulate this data. A cursor can hold more than one row,
but can process only one row at a time. Cursor are required to process rows
individually for queries.

46) How many types of cursors are available in PL/SQL?

There are two types of cursors in PL/SQL.

1. Implicit cursor, and

2. explicit cursor

1) What is SQL?
SQL stands for the Structured Query Language. It is the standard language
used to maintain the relational database and perform many different data
manipulation operations on the data. SQL was initially invented in 1970. It is
a database language used for database creation, deletion, fetching and
modifying rows, etc. sometimes, it is pronounced as 'sequel.' We can also
use it to handle organized data comprised of entities (variables) and
relations between different entities of the data.

2) When SQL appeared?

SQL first appeared in 1974. It is one of the most used languages for
maintaining the relational database. In 1986, SQL became the standard of
the American National Standards Institute (ANSI) and ISO (International
Organization for Standardization) in 1987.

3) What are the usages of SQL?

SQL is responsible for maintaining the relational data and the data structures
present in the database. Some of the common usages are given below:

Backward Skip 10sPlay VideoForward Skip 10s

ADVERTISEMENT

o To execute queries against a database

o To retrieve data from a database
o To inserts records in a database
o To updates records in a database
o To delete records from a database
o To create new databases
o To create new tables in a database
o To create views in a database
o To perform complex operations on the database.

4) Does SQL support programming language features?

SQL refers to the Standard Query Language. Therefore, it is true that SQL is a
language but does not actually support the programming language. It is a
common language that doesn't have a loop, conditional statements, and
logical operations. It cannot be used for anything other than data
manipulation. It is a command language to perform database operations. The
primary purpose of SQL is to retrieve, manipulate, update, delete, and
perform complex operations like joins on the data present in the database.

5) What are the subsets of SQL?

The following are the four significant subsets of the SQL:

o Data definition language (DDL): It defines the data structure that consists
of commands like CREATE, ALTER, DROP, etc.
o Data manipulation language (DML): It is used to manipulate existing data
in the database. The commands in this category are SELECT, UPDATE,
INSERT, etc.
o Data control language (DCL): It controls access to the data stored in the
database. The commands in this category include GRANT and REVOKE.
o Transaction Control Language (TCL): It is used to deal with the
transaction operations in the database. The commands in this category are
COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT, etc.

6) What is the purpose of DDL Language?

DDL stands for Data definition language. It is the subset of a database that
defines the data structure of the database when the database is created. For
example, we can use the DDL commands to add, remove, or modify tables.
It consists of the following commands: CREATE, ALTER and DELETE database
objects such as schema, tables, indexes, view, sequence, etc.

Example

1. CREATE TABLE Students

2. (
3. Roll_no INT,
 4. Name VARCHAR(45),
5. Branch VARCHAR(30),
6. );

7) What is the purpose of DML Language?

Data manipulation language makes the user able to retrieve and manipulate
data in a relational database. The DML commands can only perform read-
only operations on data. We can perform the following operations using DDL
language:

o Insert data into the database through the INSERT command.

o Retrieve data from the database through the SELECT command.
o Update data in the database through the UPDATE command.
o Delete data from the database through the DELETE command.

Example

1. INSERT INTO Student VALUES (111, 'George', 'Computer Science')

8) What is the purpose of DCL Language?

Data control language allows users to control access and permission
management to the database. It is the subset of a database, which decides
that what part of the database should be accessed by which user at what
point of time. It includes two commands, GRANT and REVOKE.

GRANT: It enables system administrators to assign privileges and roles to

the specific user accounts to perform specific tasks on the database.

REVOKE: It enables system administrators to revoke privileges and roles

from the user accounts so that they cannot use the previously assigned
permission on the database.

Example

1. GRANT * ON mydb.Student TO javatpoint@localhsot;

9) What are tables and fields in the database?
A table is a set of organized data in the form of rows and columns. It enables
users to store and display records in the structure format. It is similar to
worksheets in the spreadsheet application. Here rows refer to the tuples,
representing the simple data item, and columns are the attribute of the data
items present in a particular row. Columns can categorize as vertical, and
Rows are horizontal.

Fields are the components to provide the structure for the table. It stores the
same category of data in the same data type. A table contains a fixed
number of columns but can have any number of rows known as the record. It
is also called a column in the table of the database. It represents the
attribute or characteristics of the entity in the record.

Example

Table: Student

Field: Stud_rollno, Stud_name, Date of Birth, Branch, etc.

10) What is a primary key?

A primary key is a field or the combination of fields that uniquely identify
each record in the table. It is one of a special kind of unique key. If the
column contains a primary key, it cannot be null or empty. A table can have
duplicate columns, but it cannot have more than one primary key. It always
stores unique values into a column. For example, the ROLL Number can be
treated as the primary key for a student in the university or college.

We can define a primary key into a student table as follows:

1. CREATE TABLE Student (

2. roll_number INT PRIMARY KEY,
3. name VARCHAR(45),
 4. );

To read more information, click here.

11) What is a foreign key?

ADVERTISEMENT

The foreign key is used to link one or more tables together. It is also known
as the referencing key. A foreign key is specified as a key that is related to
the primary key of another table. It means a foreign key field in one table
refers to the primary key field of the other table. It identifies each row of
another table uniquely that maintains the referential integrity. The primary
key-foreign key relationship is a very crucial relationship as it maintains the
ACID properties of the database sometimes. It also prevents actions that
would destroy links between the child and parent tables.

We can define a foreign key into a table as follows:

1. CONSTRAINT constraint_name]
2. FOREIGN KEY [foreign_key_name] (col_name, ...)
3. REFERENCES parent_tbl_name (col_name,...)

To read more information, click here.

12) What is a unique key?

A unique key is a single or combination of fields that ensure all values stores
in the column will be unique. It means a column cannot stores duplicate
values. This key provides uniqueness for the column or set of columns. For
example, the email addresses and roll numbers of student's tables should
be unique. It can accept a null value but only one null value per column. It
ensures the integrity of the column or group of columns to store different
values into a table.

We can define a foreign key into a table as follows:

1. CREATE TABLE table_name(

 2. col1 datatype,
3. col2 datatype UNIQUE,
4. ...
5. );

To read more information, click here.

13) What is the difference between a primary key and a unique

key?
The primary key and unique key both are essential constraints of the SQL.
The main difference among them is that the primary key identifies each
record in the table. In contrast, the unique key prevents duplicate entries in
a column except for a NULL value. The following comparison chart explains it
more clearly:

Primary Key Unique Key

The primary key act as a unique The unique key is also a unique identifier for
identifier for each record in the records when the primary key is not present in the
table. table.

We cannot store NULL values in the We can store NULL value in the unique key column,
primary key column. but only one NULL is allowed.

We cannot change or delete the We can modify the unique key column values.
primary key column values.

To read more information, click here.

14) What is a Database?

A database is an organized collection of data that is structured into tables,
rows, columns, and indexes. It helps the user to find the relevant information
frequently. It is an electronic system that makes data access, data
manipulation, data retrieval, data storing, and data management very easy.
Almost every organization uses the database for storing the data due to its
easily accessible and high operational ease. The database provides perfect
access to data and lets us perform required tasks.

The following are the common features of a database:

o Manages large amounts of data

o Accurate
o Easy to update
o Security
o Data integrity
o Easy to research data

15) What is meant by DBMS?

ADVERTISEMENT

DBMS stands for Database Management System. It is a software program

that primarily functions as an interface between the database and the end-
user. It provides us the power such as managing the data, the database
engine, and the database schema to facilitate the organization and
manipulation of data using a simple query in almost no time. It is like a File
Manager that manages data in a database rather than saving it in file
systems. Without the database management system, it would be far more
difficult for the user to access the database's data.

The following are the components of a DBMS:

o Software
o Data
o Procedures
o Database Languages
o Query Processor
o Database Manager
o Database Engine
o Reporting
16) What are the different types of database management
systems?
The database management systems can be categorized into several types.
Some of the important lists are given below:

o Hierarchical databases (DBMS)

o Network databases (IDMS)
o Relational databases (RDBMS
o Object-oriented databases
o Document databases (Document DB)
o Graph databases
o ER model databases
o NoSQL databases

17) What is RDBMS?

RDBMS stands for Relational Database Management System. It is a database
management system based on a relational model. It facilitates you to
manipulate the data stored in the tables by using relational operators.
RDBMS stores the data into the collection of tables and links those tables
using the relational operators easily whenever required. Examples of
relational database management systems are Microsoft Access, MySQL, SQL
Server, Oracle database, etc.

18) What is Normalization in a Database?

Normalization is used to minimize redundancy and dependency by
organizing fields and table of a database.

There are some rules of database normalization, which is commonly known

as Normal From, and they are:

o First normal form(1NF)

 o Second normal form(2NF)
o Third normal form(3NF)
o Boyce-Codd normal form(BCNF)

Using these steps, the redundancy, anomalies, inconsistency of the data in

the database can be removed.

19) What is the primary use of Normalization?

Normalization is mainly used to add, delete or modify a field that can be
made in a single table. The primary use of Normalization is to remove
redundancy and remove the insert, delete and update distractions.
Normalization breaks the table into small partitions and then links them
using different relationships to avoid the chances of redundancy.

20) What are the disadvantages of not performing database

Normalization?
The major disadvantages are:

The occurrence of redundant terms in the database causes the waste of

space in the disk.

Due to redundant terms, inconsistency may also occur. If any change is

made in the data of one table but not made in the same data of another
table, then inconsistency will occur. This inconsistency will lead to the
maintenance problem and effects the ACID properties as well.

21) What is an inconsistent dependency?

An Inconsistent dependency refers to the difficulty of getting relevant data
due to a missing or broken path to the data. It leads users to search the data
in the wrong table, resulting in an error as an output.

22) What is Denormalization in a Database?

Denormalization is a technique used by database administrators to optimize
the efficiency of their database infrastructure. The denormalization concept
is based on Normalization, which is defined as arranging a database into
tables correctly for a particular purpose. This method allows us to add
redundant data into a normalized database to alleviate issues with database
queries that merge data from several tables into a single table. It adds
redundant terms into the tables to avoid complex joins and many other
complex operations.

Denormalization doesn't mean that normalization will not be done. It is an

optimization strategy that takes place after the normalization process.

23) What are the different types of SQL operators?

Operators are the special keywords or special characters reserved for
performing particular operations. They are also used in SQL queries. We can
primarily use these operators within the WHERE clause of SQL commands.
It's a part of the command to filters data based on the specified condition.
The SQL operators can be categorized into the following types:

o Arithmetic operators: These operators are used to perform mathematical

operations on numerical data. The categories of this operators are addition
(+), subtraction (-), multiplication (*), division (/), remainder/modulus (%),
etc.
o Logical operators: These operators evaluate the expressions and return
their results in True or False. This operator includes ALL, AND, ANY, ISNULL,
EXISTS, BETWEEN, IN, LIKE, NOT, OR, UNIQUE.
o Comparison operators: These operators are used to perform comparisons
of two values and check whether they are the same or not. It includes equal
to (=), not equal to (!= or <>), less than (<), greater than (>), less than or
equal to (<=), greater than or equal to (>=), not less than (!<), not greater
than (!>), etc.
o Bitwise operators: It is used to do bit manipulations between two
expressions of integer type. It first performs conversion of integers into
binary bits and then applied operators such as AND (& symbol), OR (|, ^),
NOT (~), etc.
 o Compound operators: These operators perform operations on a variable
before setting the variable's result to the operation's result. It includes Add
equals (+=), subtract equals (-=), multiply equals (*=), divide equals (/=),
modulo equals (%=), etc.
o String operators: These operators are primarily used to perform
concatenation and pattern matching of strings. It includes + (String
concatenation), += (String concatenation assignment), % (Wildcard), []
(Character(s) matches), [^] (Character(s) not to match), _ (Wildcard match
one character), etc.

24) What is a view in SQL?

A view is a database object that has no values. It is a virtual table that
contains a subset of data within a table. It looks like an actual table
containing rows and columns, but it takes less space because it is not
present physically. It is operated similarly to the base table but does not
contain any data of its own. Its name is always unique. A view can have data
from one or more tables. If any changes occur in the underlying table, the
same changes reflected in the views also.
The primary use of a view is to implement the security mechanism. It is the
searchable object where we can use a query to search the view as we use for
the table. It only shows the data returned by the query that was declared
when the view was created.

We can create a view by using the following syntax:

1. CREATE VIEW view_name AS

2. SELECT column_lists FROM table_name
3. WHERE condition;

25) What is an Index in SQL?

An index is a disc structure associated with a table or view that speeds up
row retrieval. It reduces the cost of the query because the query's high cost
will lead to a fall in its performance. It is used to increase the performance
and allow faster retrieval of records from the table. Indexing reduces the
number of data pages we need to visit to find a particular data page. It also
has a unique value meaning that the index cannot be duplicated. An index
creates an entry for each value which makes it faster to retrieve data.

For example: Suppose we have a book which carries the details of the
countries. If you want to find out information about India, why will you go
through every page of that book? You could directly go to the index. Then
from the index, you can go to that particular page where all the information
about India is given.

26) What are the different types of indexes in SQL?

SQL indexes are nothing more than a technique of minimizing the query's
cost. The higher the query's cost, the worse the query's performance. The
following are the different types of Indexes supported in SQL:

o Unique Index
o Clustered Index
o Non-Clustered Index
o Bit-Map Index
o Normal Index
 o Composite Index
o B-Tree Index
o Function-Based Index

27) What is the unique index?

UNIQUE INDEX is used to enforce the uniqueness of values in single or
multiple columns. We can create more than one unique index in a single
table. For creating a unique index, the user has to check the data in the
column because the unique indexes are used when any column of the table
has unique values. This indexing does not allow the field to have duplicate
values if the column is unique indexed. A unique index can be applied
automatically when a primary key is defined.

We can create it by using the following syntax:

1. CREATE UNIQUE INDEX index_name

2. ON table_name (index_column1, index_column2,...);

Example

1. CREATE TABLE Employee(

2. ID int AUTO_INCREMENT PRIMARY KEY,
3. Name varchar(45),
4. Phone varchar(15),
5. City varchar(25),
6. );

Suppose we want to make a Phone column as a unique index. We can do this

like below:

1. CREATE UNIQUE INDEX index_name_phone ON Employee (Phone);

To read more information, click here.

28) What is clustered index in SQL?

A clustered index is actually a table where the data for the rows are stored. It
determines the order of the table data based on the key values that can sort
in only one direction. Each table can have only one clustered index. It is the
only index, which has been automatically created when the primary key is
generated. If many data modifications needed to be done in the table, then
clustered indexes are preferred.

To read more information, click here.

29) What is the non-clustered index in SQL?

The indexes other than PRIMARY indexes (clustered indexes) are called non-
clustered indexes. We know that clustered indexes are created automatically
when primary keys are generated, and non-clustered indexes are created
when multiple joins conditions and various filters are used in the query. The
non-clustered index and table data are both stored in different places. It
cannot be able to alter the physical order of the table and maintains the
logical order of data.

The purpose of creating a non-clustered index is for searching the data. Its
best example is a book where the content is written in one place, and the
index is at a different place. We can create 0 to 249 non-clustered indexes in
each table. The non-clustered indexing improves the performance of the
queries which use keys without assigning the primary key.

30) What are the differences between SQL, MySQL, and SQL
Server?
The following comparison chart explains their main differences:

SQL MySQL SQL Server

SQL or Structured Query MySQL is the popular SQL Server is an RDBMS

Language is useful for database management database system mainly
managing our relational system used for managing developed for the Windows
databases. It is used to the relational database. It is a system to store, retrieve,
query and operate the fast, scalable, and easy-to- and access data requested
database. use database. by the developer.
 SQL first appeared in 1974. MySQL first appeared on May SQL Server first appeared on
23, 1995. April 24, 1989.

SQL was developed by IBM MySQL was developed by SQL Server was developed
Corporation. Oracle Corporation. by Microsoft Company.

SQL is a query language for MySQL is database software SQL Server is also a
managing databases. that uses SQL language to software that uses SQL
conduct with the database. language to conduct with
the database.

SQL has no variables. MySQL can use variables SQL Server can use
constraints and data types. variables constraints and
data types.

SQL is a programming MySQL is software, so it gets SQL Server is also software,

language, so that it does frequent updation. so it gets frequent updation.
not get any updates. Its
commands are always fixed
and remain the same.

31) What is the difference between SQL and PL/SQL?

The following comparison chart explains their main differences:

SQL PL/SQL

SQL is a database structured query PL/SQL or Procedural Language/Structured Query

language used to communicate with Language is a dialect of SQL used to enhance the
relational databases. It was capabilities of SQL. Oracle Corporation developed it
developed by IBM Corporations and in the early 90's. It uses SQL as its database
first appeared in 1974. language.

SQL is a declarative and data- PL/SQL is a procedural and application-oriented

oriented language. language.

SQL has no variables. PL/SQL can use variables constraints and data
types.
 SQL can execute only a single query PL/SQL can execute a whole block of code at once.
at a time.

SQL query can be embedded in PL/SQL cannot be embedded in SQL as SQL does
PL/SQL. not support any programming language and
keywords.

SQL can directly interact with the PL/SQL cannot directly interact with the database
database server. server.

SQL is like the source of data that we PL/SQL provides a platform where SQL data will be
need to display. shown.

32) Is it possible to sort a column using a column alias?

Yes. We can use the alias method in the ORDER BY instead of the WHERE
clause for sorting a column.

33) What is the difference between clustered and non-clustered

indexes in SQL?
Indexing is a method to get the requested data very fast. There are mainly
two types of indexes in SQL, clustered index and non-clustered index. The
differences between these two indexes are very important from an SQL
performance perspective. The following comparison chart explains their main
differences:

Clustered Index Non-Clustered Index

A clustered index is a table or view where The indexes other than PRIMARY indexes
the data for the rows are stored. In a (clustered indexes) are called non-clustered
relational database, if the table column indexes. It has a structure separate from
contains a primary key, MySQL automatically the data row. The non-clustered indexes
creates a clustered index named PRIMARY. are also known as secondary indexes.

Clustered indexes store the data information Non-clustered indexes stores only the
and the data itself. information, and then it will refer you to the
 data stored in clustered data.

There can only be one clustered index per There can be one or more non-clustered
table. indexes in a table.

A clustered index determines how data is It creates a logical ordering of data rows
stored physically in the table. Therefore, and uses pointers for accessing the physical
reading from a clustered index is faster. data files. Therefore, reading from a
clustered index is slower.

A clustered index always contains an index A non-clustered index always contains an

id of 0. index id>0.

To read more information, click here.

34) What is the SQL query to display the current date?

There is a built-in function in SQL called GetDate(), which is used to return
the current timestamp.

35) Which are joins in SQL? Name the most commonly used SQL
joins?
SQL joins are used to retrieve data from multiple tables into a meaningful
result set. It is performed whenever you need to fetch records from two or
more tables. They are used with SELECT statement and join conditions.

The following are the most commonly used joins in SQL:

o INNER JOIN
o LEFT OUTER JOIN
o RIGHT OUTER JOIN

36) What are the different types of joins in SQL?

Joins are used to merge two tables or retrieve data from tables. It depends
on the relationship between tables. According to the ANSI standard, the
following are the different types of joins used in SQL:

o INNER JOIN
o SELF JOIN
o LEFT OUTER JOIN
o RIGHT OUTER JOIN
o FULL OUTER JOIN
o CROSS JOIN

To read more information, click here.

37) What is INNER JOIN in SQL?

Inner join returns only those records from the tables that match the specified
condition and hides other rows and columns. In simple words, it fetches rows
when there is at least one match of rows between the tables is found. INNER
JOIN keyword joins the matching records from two tables. It is assumed as a
default join, so it is optional to use the INNER keyword with the query.

The below visual representation explain this join more clearly:

The following syntax illustrates the INNER JOIN:

1. SELECT column_lists
2. FROM table1
3. INNER JOIN table2 ON join_condition1
4. INNER JOIN table3 ON join_condition2
5. ...;

To read more information, click here.

38) What is the Right JOIN in SQL?

The Right join is used to retrieve all rows from the right-hand table and only
those rows from the other table that fulfilled the join condition. It returns all
the rows from the right-hand side table even though there are no matches in
the left-hand side table. If it finds unmatched records from the left side table,
it returns a Null value. This join is also known as Right Outer Join.

The below visual representation explain this join more clearly:

The following syntax illustrates the RIGHT JOIN:

1. SELECT colum_lists
2. FROM table1
3. RIGHT JOIN table2
4. ON join_condition;

To read more information, click here.

39) What is Left Join in SQL?

The Left Join is used to fetch all rows from the left-hand table and common
records between the specified tables. It returns all the rows from the left-
hand side table even though there are no matches on the right-hand side
table. If it will not find any matching record from the right side table, then it
returns null. This join can also be called a Left Outer Join.

The following visual representation explains it more clearly:

The following syntax illustrates the RIGHT JOIN:

1. SELECT colum_lists
2. FROM table1
3. LEFT JOIN table2
4. ON join_condition;

To read more information, click here.

40) What is Full Join in SQL?

The Full Join results from a combination of both left and right join that
contains all the records from both tables. It fetches rows when there are
matching rows in any one of the tables. This means it returns all the rows
from the left-hand side table and all the rows from the right-hand side tables.
If a match is not found, it puts NULL value. It is also known as FULL OUTER
JOIN.

The following visual representation explains it more clearly:

The following syntax illustrates the FULL JOIN:

1. SELECT * FROM table1

2. FULL OUTER JOIN table2
3. ON join_condition;

To read more information, click here.

41) What is a "TRIGGER" in SQL?

A trigger is a set of SQL statements that reside in a system catalog. It is a
special type of stored procedure that is invoked automatically in response to
an event. It allows us to execute a batch of code when an insert, update or
delete command is run against a specific table because the trigger is the set
of activated actions whenever DML commands are given to the system.

SQL triggers have two main components one is action, and another is an
event. When certain actions are taken, an event occurs as a result of those
actions.

We use the CREATE TRIGGER statement for creating a trigger in SQL. Here is
the syntax:

1. CREATE TRIGGER trigger_name

2. (AFTER | BEFORE) (INSERT | UPDATE | DELETE)
 3. ON table_name FOR EACH ROW
4. BEGIN
5. --variable declarations
6. --trigger code
7. END;

To read more information, click here.

42) What is self-join and what is the requirement of self-join?

A SELF JOIN is used to join a table with itself. This join can be performed
using table aliases, which allow us to avoid repeating the same table name in
a single sentence. It will throw an error if we use the same table name more
than once in a single query without using table aliases.

A SELF JOIN is required when we want to combine data with other data in the
same table itself. It is often very useful to convert a hierarchical structure to
a flat structure.

The following syntax illustrates the SELF JOIN:

1. SELECT column_lists
2. FROM table1 AS T1, table1 AS T2
3. WHERE join_conditions;

Example

Suppose we have a table 'Student' having the following data:

If we want to get retrieve the student_id and name from the table where
student_id is equal, and course_id is not equal, it can be done by using the
self-join:

1. SELECT s1.student_id, s1.name

 2. FROM student AS s1, student s2
3. WHERE s1.student_id=s2.student_id
4. AND s1.course_id<>s2.course_id;

Here is the result:

To read more information, click here.

43) What are the set operators in SQL?

We use the set operators to merge data from one or more tables of the same
kind. Although the set operators are like SQL joins, there is a significant
distinction. SQL joins combine columns from separate tables, whereas SQL
set operators combine rows from different queries. SQL queries that contain
set operations are called compound queries. The set operators in SQL are
categories into four different types:

A. UNION: It combines two or more results from multiple SELECT queries

into a single result set. It has a default feature to remove the duplicate rows
from the tables. The following syntax illustrates the Union operator:
 1. SELECT columns FROM table1
2. UNION
3. SELECT columns FROM table2;

B. UNION ALL: This operator is similar to the Union operator, but it does not
remove the duplicate rows from the output of the SELECT statements. The
following syntax illustrates the UNION ALL operator:

1. SELECT columns FROM table1

2. UNION ALL
3. SELECT columns FROM table2;

C. INTERSECT: This operator returns the common records from two or more
SELECT statements. It always retrieves unique records and arranges them in
ascending order by default. Here, the number of columns and data types
should be the same. The following syntax illustrates the INTERSECT operator:

1. SELECT columns FROM table1

2. INTERSECT
3. SELECT columns FROM table2;

D. MINUS: This operator returns the records from the first query, which is
not found in the second query. It does not return duplicate values. The
following syntax illustrates the MINUS operator:

1. SELECT columns FROM table1

2. MINUS
3. SELECT columns FROM table2;

To read more information, click here.

44) What is the difference between IN and BETWEEN operators?

The following comparison chart explains their main differences:

BETWEEN Operator IN Operator

 This operator is used to selects the It is a logical operator to determine whether or not
range of data between two values. a specific value exists within a set of values. This
The values can be numbers, text, and operator reduces the use of multiple OR
dates as well. conditions with the query.

It returns records whose column value It compares the specified column's value and
lies in between the defined range. returns the records when the match exists in the
set of values.

The following syntax illustrates this The following syntax illustrates this operator:
operator: SELECT * FROM table_name
SELECT * FROM table_name WHERE column_name IN ('value1','value 2');
WHERE column_name BETWEEN
'value1' AND 'value2';

45) What is a constraint? Tell me about its various levels.

The constraint is used to specify the rule and regulations that allows or
restricts what values/data will be stored in the table. It ensures data
accuracy and integrity inside the table. It enforces us to store valid data and
prevents us from storing irrelevant data. If any interruption occurs between
the constraint and data action, the action is failed. Some of the most
commonly used constraints are NOT NULL, PRIMARY KEY, FOREIGN KEY,
AUTO_INCREMENT, UNIQUE KEY, etc.

The following syntax illustrates us to create a constraint for a table:

1. CREATE TABLE table_name (

2. column1 datatype constraint,
3. column2 datatype constraint,
4. .........
5. );

SQL categories the constraints into two levels:

Column Level Constraints: These constraints are only applied to a single

column and limit the type of data that can be stored in that column.

Table Level Constraints: These constraints are applied to the entire table
and limit the type of data that can be entered.
To read more information, click here.

46) How to write an SQL query to find students' names start with
'A'?
We can write the following query to get the student details whose name
starts with A:

1. SELECT * FROM student WHERE stud_name like 'A%';

Here is the demo example where we have a table named student that
contains two names starting with the 'A' character.

47) Write the SQL query to get the third maximum salary of an
employee from a table named employees.
The following query is the simplest way to get the third maximum salary of
an employee:
 1. SELECT * FROM `employees` ORDER BY `salary` DESC LIMIT 1 OFFSET
2

Here is the demo example that shows how to get the third maximum salary
of an employee.

The following are the alternative way to get the third-highest salary of an
employee:

A. Using LIMIT Keyword

1. SELECT salary FROM employees

2. ORDER BY salary DESC
3. LIMIT 2, 1;

B. Using Subquery

1. SELECT salary
2. FROM
3. (SELECT salary
 4. FROM employees
5. ORDER BY salary DESC
6. LIMIT 3) AS Temp
7. ORDER BY salary LIMIT 1;

C. Using TOP Keyword

1. SELECT TOP 1 salary

2. FROM
3. (SELECT DISTINCT TOP 3 salary
4. FROM employees
5. ORDER BY salary DESC) AS Temp
6. ORDER BY salary ASC;

48) What is the difference between DELETE and TRUNCATE

statements in SQL?
The main difference between them is that the delete statement deletes data
without resetting a table's identity, whereas the truncate command resets a
particular table's identity. The following comparison chart explains it more
clearly:

No DELETE TRUNCATE
.

1) The delete statement removes single The truncate command deletes the whole
or multiple rows from an existing table contents of an existing table without the
depending on the specified condition. table itself. It preserves the table structure
or schema.

2) DELETE is a DML command. TRUNCATE is a DML command.

3) We can use the WHERE clause in the We cannot use the WHERE clause with
DELETE command. TRUNCATE.

4) DELETE statement is used to delete a TRUNCATE statement is used to remove

row from a table. all the rows from a table.
 5) DELETE is slower because it TRUNCATE statement is faster than
maintained the log. DELETE statement as it deletes entire data
at a time without maintaining transaction
logs.

6) You can roll back data after using the It is not possible to roll back after using
DELETE statement. the TRUNCATE statement.

7) DELETE query takes more space. TRUNCATE query occupies less space.

To read more information, click here.

49) What is the ACID property in a database?

The ACID properties are meant for the transaction that goes through a
different group of tasks. A transaction is a single logical order of data. It
provides properties to maintain consistency before and after the transaction
in a database. It also ensures that the data transactions are processed
reliably in a database system.

The ACID property is an acronym for Atomicity, Consistency, Isolation, and

Durability.

Atomicity: It ensures that all statements or operations within the

transaction unit must be executed successfully. If one part of the transaction
fails, the entire transaction fails, and the database state is left unchanged.
Its main features are COMMIT, ROLLBACK, and AUTO-COMMIT.

Consistency: This property ensures that the data must meet all validation
rules. In simple words, we can say that the database changes state only
when a transaction will be committed successfully. It also protects data from
crashes.

Isolation: This property guarantees that the concurrent property of

execution in the transaction unit must be operated independently. It also
ensures that statements are transparent to each other. The main goal of
providing isolation is to control concurrency in a database.

Durability: This property guarantees that once a transaction has been

committed, it persists permanently even if the system crashes, power loss,
or failed.
To read more information, click here.

50) Is a blank space or zero the same as a NULL value?

No. The NULL value is not the same as zero or a blank space. The following
points explain their main differences:

o A NULL value is a value, which is 'unavailable, unassigned, unknown or not

applicable.' It would be used in the absence of any value. We can perform
arithmetic operations on it. On the other hand, zero is a number, and a blank
space is treated as a character.
o The NULL value can be treated as an unknown and missing value, but zero
and blank spaces differ from the NULL value.
o We can compare a blank space or a zero to another blank space or a zero. On
the other hand, one NULL may not be the same as another NULL. NULL
indicates that no data has been provided or that no data exists.

51) What are functions and their usage in SQL?

SQL functions are simple code snippets that are frequently used and re-used
in database systems for data processing and manipulation. Functions are the
measured values. It always performs a specific task. The following rules
should be remembered while creating functions:

o A function should have a name, and the name cannot begin with a special
character such as @, $, #, or other similar characters.
o Functions can only work with the SELECT statements.
o Every time a function is called, it compiles.
o Functions must return value or result.
o Functions are always used with input parameters.

SQL categories the functions into two types:

 o User-Defined Function: Functions created by a user based on their needs
are termed user-defined functions.
o System Defined Function: Functions whose definition is defined by the
system are termed system-defined functions. They are built-in database
functions.

SQL functions are used for the following purposes:

o To perform calculations on data

o To modify individual data items
o To manipulate the output
o To format dates and numbers
o To convert data types

52) What is meant by case manipulation functions? Explains its

different types in SQL.
Case manipulation functions are part of the character functions. It converts
the data from the state in which it is already stored in the table to upper,
lower, or mixed case. The conversion performed by this function can be used
to format the output. We can use it in almost every part of the SQL
statement. Case manipulation functions are mostly used when you need to
search for data, and you don't have any idea that the data you are looking
for is in lower case or upper case.

There are three case manipulation functions in SQL:

LOWER: This function is used to converts a given character into lowercase.

The following example will return the 'STEPHEN' as 'stephen':

1. SELECT LOWER ('STEPHEN') AS Case_Reault FROM dual;

NOTE: Here, 'dual' is a dummy table.

UPPER: This function is used to converts a given character into uppercase.

The following example will return the 'stephen' as 'STEPHEN':

1. SELECT UPPER ('stephen') AS Case_Reault FROM dual;

INITCAP: This function is used to converts given character values to
uppercase for the initials of each word. It means every first letter of the word
is converted into uppercase, and the rest is in lower case. The following
example will return the 'hello stephen' as 'Hello Stephen':

1. SELECT INITCAP ('hello stephen') AS Case_Reault FROM dual;

53) Explain character-manipulation functions? Explains its

different types in SQL.
Character-manipulation functions are used to change, extract, and alter the
character string. When one or more characters and words are passed into
the function, the function will perform its operation on those input strings
and return the result.

The following are the character manipulation functions in SQL:

A) CONCAT: This function is used to join two or more values together. It

always appends the second string into the end of the first string. For
example:

Input: SELECT CONCAT ('Information-', 'technology') FROM DUAL;

Output: Information-technology

B) SUBSTR: It is used to return the portion of the string from a specified

start point to an endpoint. For example:

Input: SELECT SUBSTR ('Database Management System', 9, 11) FROM DUAL;

Output: Management

C) LENGTH: This function returns the string's length in numerical value,

including the blank spaces. For example:

Input: SELECT LENGTH ('Hello Javatpoint') FROM DUAL;

Output: 16

D) INSTR: This function finds the exact numeric position of a specified

character or word in a given string. For example:

Input: SELECT INSTR ('Hello Javatpoint', 'Javatpoint');

Output: 7

E) LPAD: It returns the padding of the left-side character value for right-
justified value. For example:

Input: SELECT LPAD ('200', 6,'*');

Output: ***200

F) RPAD: It returns the padding of the right-side character value for left-
justified value. For example:

Input: SELECT RPAD ('200', 6,'*');

Output: 200***

G) TRIM: This function is used to remove all the defined characters from the
beginning, end, or both. It also trimmed extra spaces. For example:

Input: SELECT TRIM ('A' FROM 'ABCDCBA');

Output: BCDCB

H) REPLACE: This function is used to replace all occurrences of a word or

portion of the string (substring) with the other specified string value. For
example:

Input: SELECT REPLACE ( 'It is the best coffee at the famous coffee shop.',
'coffee', 'tea');

Output: It is the best tea at the famous tea shop.

54) What is the usage of the NVL() function?

The NVL() function is used to convert the NULL value to the other value. The
function returns the value of the second parameter if the first parameter is
NULL. If the first parameter is anything other than NULL, it is left unchanged.
This function is used in Oracle, not in SQL and MySQL. Instead of NVL()
function, MySQL have IFNULL() and SQL Server have ISNULL() function.
55) Which function is used to return remainder in a division
operator in SQL?
The MOD function returns the remainder in a division operation.

56) What are the syntax and use of the COALESCE function?
The COALESCE() function evaluates the arguments in sequence and returns
the first NON-NULL value in a specified number of expressions. If it evaluates
arguments as NULL or not found any NON-NULL value, it returns the NULL
result.

The syntax of COALESCE function is given below:

1. COALESCE (exp1, exp2, .... expn)

Example:

1. SELECT COALESCE(NULL, 'Hello', 'Javatpoint', NULL) AS Result;

This statement will return the following output:

57) How do we use the DISTINCT statement? What is its use?

The DISTINCT keyword is used to ensure that the fetched value always has
unique values. It does not allow to have duplicate values. The DISTINCT
keyword is used with the SELECT statement and retrieves different values
from the table's column. We can use it with the help of the following syntax:

1. SELECT DISTINCT column_lists FROM table_name WHERE [condition];

Suppose we have a table 'customer' containing eight records in which the
name column has some duplicate values.

If we want to get the name column without any duplicate values, the
DISTINCT keyword is required. Executing the below command will return a
name column with unique values.

58) What is the default ordering of data using the ORDER BY

clause? How could it be changed?
The ORDER BY clause is used to sort the table data either in ascending or
descending order. By default, it will sort the table in ascending order. If we
want to change its default behavior, we need to use the DESC keyword after
the column name in the ORDER BY clause.

The syntax to do this is given below:

1. SELECT expressions FROM tables

 2. WHERE conditions
3. ORDER BY expression [ASC | DESC];

We have taken a customer table in the previous example. Now, we will

demonstrate the ORDER BY clause on them as well.

In the below output, we can see that the first query will sort the table data in
ascending order based on the name column. However, if we run the second
query by specifying the DESC keyword, the table's order is changed in
descending order.

59) Is the following query returns the output?

1. SELECT subject_code, AVG (marks)

2. FROM Students
 3. WHERE AVG(marks) > 70
4. GROUP BY subject_code;

Answer: No. The above query does not return the output because we cannot
use the WHERE clause to restrict the groups. We need to use the HAVING
clause instead of the WHERE clause to get the correct output.

60) What is the difference between the WHERE and HAVING

clauses?
The main difference is that the WHERE clause is used to filter records before
any groupings are established, whereas the HAVING clause is used to filter
values from a group. The below comparison chart explains the most common
differences:

WHERE HAVING

This clause is implemented in row operations. This clause is implemented in column

operations.

It does not allow to work with aggregate It can work with aggregate functions.
functions.

This clause can be used with the SELECT, UPDATE, This clause can only be used with the
and DELETE statements. SELECT statement.

To know more differences, click here.

61) How many Aggregate functions are available in SQL?

The aggregate function is used to determine and calculate several values in
a table and return the result as a single number. For example, the average of
all values, the sum of all values, and the maximum and minimum value
among particular groupings of values.

The following syntax illustrates how to use aggregate functions:

 1. function_name (DISTINCT | ALL expression)

SQL provides seven (7) aggregate functions, which are given below:

o AVG(): This function is used to returns the average value from specified
columns.
o COUNT(): This function is used to returns the number of table rows,
including rows with null values.
o MAX(): This function is used to returns the largest value among the group.
o MIN(): This function is used to returns the smallest value among the group.
o SUM(): This function is used to returns the total summed values(non-null) of
the specified column.
o FIRST(): This function is used to returns the first value of an expression.
o LAST(): This function is used to returns the last value of an expression.

62) What is SQL Injection?

SQL injection is a type of vulnerability in website and web app code that
allows attackers to control back-end operations and access, retrieve, and
destroy sensitive data from databases. In this technique, malicious SQL
statements are inserted into a database entry field, and once they are
performed, the database becomes vulnerable to an attacker. This technique
is commonly used to access sensitive data and perform administrative
activities on databases by exploiting data-driven applications. It is also
known as SQLi attack.

Some common examples of SQL injection are:

o Accessing confidential data to modify an SQL query to get desired results.

o UNION attacks to steal data from different database tables.
o Examine the database to extract information regarding the version and
structure of the database.
63) What is the difference between the RANK() and
DENSE_RANK() functions?
The RANK function determines the rank for each row within your ordered
partition in the result set. If the two rows are assigned the same rank, then
the next number in the ranking will be its previous rank plus a number of
duplicate numbers. For example, if we have three records at rank 4, the next
rank listed would be ranked 7.

The DENSE_RANK function assigns a unique rank for each row within a
partition as per the specified column value without any gaps. It always
specifies ranking in consecutive order. If the two rows are assigned the same
rank, this function will assign it with the same rank, and the next rank being
the next sequential number. For example, if we have 3 records at rank 4, the
next rank listed would be ranked 5.

64) Is it possible to implicitly insert a row for the identity column?

Yes. We can implicitly insert a row for the identity column. Here is an
example of doing this:

1. SET IDENTITY_INSERT TABLE1 ON

2. INSERT INTO demo_table1 (id, name, branch)
3. SELECT id, name, branch FROM demo_table2
4. SET IDENTITY_INSERT OFF

65) What are SQL comments?

Comments are explanations or annotations in SQL queries that are readable
by programmers. It's used to make SQL statements easier to understand for
humans. During the parsing of SQL code, it will be ignored. Comments can
be written on a single line or across several lines.

o Single Line Comments: It starts with two consecutive hyphens (--).

o Multi-line Comments: It starts with /* and ends with */.

Advanced SQL MCQ Questions and Answers

This section provides multiple-choice questions and answers based on
advanced query optimization.

1) What type of join do you need when you want to include rows with values
that don't match?

a. Equi-Join

b. Outer Join

c. Natural Join

d. All of the above.

Show Answer Workspace

2) Which of the following option matched a CASE SQL statement?

a. A way to establish an IF-THEN-ELSE in SQL.

b. A way to establish a loop in SQL.

c. A way to establish a data definition in SQL.

d. None of the above.

Show Answer Workspace

3) Which of the following is an illegal data type in SQL?

a. NUMBER

b. CLOB

c. BLOB

d. LINT

Show Answer Workspace

4) The view is updated immediately if the actual relations used in the view
definition change. These views are referred to as _________.
 a. Instant views

b. Instantaneous views

c. Materialized views

d. Materialistic views

Show Answer Workspace

5) The part of SQL that deals with the SQL support constructs are called _______.

a. Persistent Construct Dealer

b. Persistent Supports Centre

c. Primary Storage Medium

d. Persistent Storage Module

Show Answer Workspace

6) Which of the following is true regarding a correlated subquery?

a. Uses the result of an outer query to determine the processing of an inner

query.

b. Uses the result of an inner query to determine the processing of an outer

query.

c. Uses the result of an inner query to determine the processing of an inner

query.

d. Uses the result of an outer query to determine the processing of an outer

query.

Show Answer Workspace

7) Whenever a database is modified, the system executes a statement called

_________.
 a. Function

b. Trigger

c. Package

d. Protocol

Show Answer Workspace

8) A transaction starts when

a. A COMMIT statement is issued

b. A ROLLBACK statement is issued

c. A CREATE statement is used

d. All of the above

Show Answer Workspace

9) Which of the below sequential statements cannot be used in a function?

a. IF

b. WAIT

c. CASE

d. LOOP

Show Answer Workspace

10) In the data type TIME(p), what does p stands for?

a. The amount of delay required to be added to the time

b. The maximum number of allowed hours

c. The number of fractional digits for the seconds

 d. None of the above

Show Answer Workspace

11) Which of the following is a privilege in SQL standard?

a. SELECT

b. INSERT

c. UPDATE

d. All of the above

Show Answer Workspace

12) Which of the following indicates another name for referential integrity
constraints?

a. Functional dependencies

b. Subset dependencies

c. Superset dependencies

d. Primary dependencies

Show Answer Workspace

13) Triggers are stored blocks of code that have to be called in order to operate.

a. TRUE

b. FALSE

Show Answer Workspace

14) Outer join is the same as equi-join, except one of the duplicate columns in
the result table is removed.

a. TRUE
 b. FALSE

Show Answer Workspace

15) A transaction is a collection of closely related update commands that must

all be completed, or none at all, for the database to be valid.

a. TRUE

b. FALSE

Q1. What are the different types of modules

associated with Oracle forms?
The different types of modules associated with Oracle forms include:

 Form module
 Menu module
 Object Library module
 SQL module

Q2. Which are the fundamental types of Joins

used in Oracle to write subqueries?
This is one of the most common Oracle interview questions for
experienced professionals. The main types of Joins that are used to write
subqueries in the Oracle database are:

 Self Join
 Equi Join
 Outer Join
Q3. Explain the use of the Control File in
Oracle.
The Control file in the Oracle database is essentially used to identify log files
that need to be opened for certain database operations to take place. It is
also used for database recovery.

Q4. What are Actual and Formal Parameters?

Expressions and variables referenced in the parameter list of a subprogram
are called Actual Parameters.

Variables that are declared within the body of the subprogram are known as
Formal Parameters.

Q5. Which are the different types of database

objects in Oracle?
There are fundamentally five different types of database objects that
include:

 Tables
 Views Indexes
 Tablespaces
 Indexes
 Synonyms

Sample Oracle Interview Questions for

Experienced Developers
Experienced developers who code front-end, back-end, or cloud applications
are required to have a thorough understanding of the Oracle database and
its features. To give you an understanding of the type of Oracle interview
questions asked at interviews for experienced developers, we’ve compiled
this list of Oracle interview questions for experienced professionals.

i. What do you understand by logical backup in Oracle?

ii. Why are save points used in Oracle?
 iii. Explain the BLOB datatype in Oracle
iv. What do you understand about synonyms in Oracle?
v. Why is the Analyze command used in Oracle?
vi. What do you understand by a Hash Cluster in Oracle?
vii. What are Recursive Hints in Oracle?
viii. What do you understand by Redo Log File Mirroring?
ix. Explain the difference between Translate and Replace commands in
Oracle
x. What do you understand about the Check Constraint in Oracle?
xi. How would you go about storing pictures in Oracle?
xii. Explain the different types of synonyms in Oracle
xiii. Which are the file extensions used by Oracle reports?
xiv. Explain the difference between Post Database Commit and Post Form
Commit
xv. Explain Temporal data types in Oracle
xvi. What is the function of the GRANT option in the IMP command?
xvii. In Oracle, how do you convert a date to a character? Give one specific
example.
xviii. What is the best way to store images in a database?
xix. What is the distinction between a pre-select and a pre-query?
xx. What is the number of memory layers in the Oracle shared pool?
xxi. What is a bulk copy, also known as BCP in Oracle?
xxii. What exactly is a SYSTEM tablespace, and when is one created?

Additional Oracle Interview Questions for

Experienced Developers
In this section, we’ve documented some additional Oracle interview
questions for experienced professionals.

i. What are cursor variables?

ii. What are Set operators, and what are they used for?
iii. What are integrity constraints in Oracle?
 iv. What do you understand about the Raw datatype in Oracle?
v. What are some general functions used in Oracle?
vi. What are database triggers, and why are they used?
vii. Explain the different types of backups in Oracle
viii. Explain Data Normalization
ix. What do you understand about Recovery catalogs?
x. Which are some commonly-used methods to shut down Oracle
databases?

Q1. What do you understand about Indexes in SQL?

Indexes in SQL are used to help retrieve data smoothly and efficiently. There
are three main types of indexes in SQL - Unique, Clustered, and Non-
Clustered. In SQL, tables can have many non-clustered indexes but only one
clustered index.

Q2. What do you do when data gets overloaded in your relational

database?

If data is overloaded, you’re ideally required to apply a soft link. A soft link
lets you store .idb and .frm files and retrieve them whenever necessary.

Q3. What do you understand about auto increment in SQL?

This is one of the most common advanced SQL interview

questions asked in technical interviews. Auto Increment is a command
through which a unique number is generated immediately when a new
record is entered into a table.

Q4. Which command is used to select a unique entry or record in a

table?

The command used to select a unique entry from the table is - “distinct.”

Q5. What are the two types of variables in SQL?

There are mainly two types of variables in SQL - Local and Global. Local
variables are those that exist in a single function. Global variables exist in
multiple functions and can be present throughout the program.

Q6. What do you understand by Recursive Stored Procedure?

The Recursive Stored Procedure is a common procedure through which

developers can use the same code snippet multiple times in a program.
Through this procedure, code snippets call themselves until a specific
boundary condition is reached.

Q7. What is the main use of the NVL() function in SQL?

The NVL() function in SQL is essentially used to replace null entries with
default values. If the first parameter has a null value, the function returns the
next parameter. This is a function that is specific to Oracle databases.

Q8. What is the Unique Constraint in SQL?

The Unique Constraint in SQL ensures that every value in a particular record
is unique. It prevents identical or similar values in a column from appearing
in two different records.
Q9. What are some major differences between SQL and NoSQL?

This is a common SQL interview question asked in technical interviews.

 SQL is a language used to manage data in relational databases, whereas

NoSQL applies to non-relational databases.
 SQL is a language used to store, manage and retrieve structured data,
whereas NoSQL is used for unstructured data.
 NoSQL databases scale horizontally while SQL databases scale vertically.
 SQL databases contain data in the form of tables, while NoSQL databases
contain data in the form of documents, records, key-values, or graphs.

Q10. What do you understand about SQL Sandbox?

SQL Sandbox is an environment within the SQL databases where untested

programs can be tested and run. There are primarily three types of SQL
Sandboxes:

 Sandbox for External Access

 Safe Access Sandbox
 Unsafe Access Sandbox
Additional Advanced SQL Interview
Questions for Practice
In this section, we’ll look at some additional Advanced SQL interview
questions that often appear in technical interviews.

i. Where is the MyISam table stored in the SQL database?

ii. What do you understand about Joins in SQL?
iii. What is the fundamental difference between CVarChar2 and Char datatype in
SQL?
iv. What is the difference between Unique Key and Foreign Key in SQL?
v. What do you understand about Data Integrity, and how do you ensure it in
SQL?
vi. Explain the differences between the different types of Indexes in SQL
vii. Explain the process of Query Optimization in SQL
viii. What are the advantages of Normalization in SQ

Q1. What is SQL injection?

This is one of the commonly asked SQL Query interview questions for
experienced professionals. You must know that SQL injection is a widely used
hacking technique by hackers to steal data from databases and tables. The
hacker would add some malicious code to steal your credentials if you go to
a website and give in your user credentials, inserted in the form of tables. It
is better to keep your credentials secure from SQL injection attacks.
Q2. What are the different types of collation
sensitivity?
You must be well-adept to this SQL Query interview question. There are
four types of collation sensitivity. They are:

Case sensitivity: A and a are treated differently.

Kana sensitivity: Japanese kana characters Katakana and Hiragana are

treated differently.

Width sensitivity: Same character represented in single-byte (half-width) and

double-byte (full-width) are treated differently.

Accent sensitivity: a and á are treated differently.

Q3. Write a query to find out the data between

range?
If you are performing day to day activities, then you need to find out the
data between a particular range. To achieve this, you have to use
between..and operator or greater than and less than operator. For instance,

Query 1: Using between..and operator

Select * from employee where salary between 60000 and 80000;

Query 2: Using operators (greater than and less than)

Select * from employee where salary >= 60000 and salary <= 80000.

Q4. What are the various data types used in

SQL?
Data types is a specific storage format that is used to store column values. A
few common data types in SQL are:

 CHAR (size): Minimum size is ‘1’and Maximum size is ‘2000’

 VARCHAR2(size): Minimum size is ‘1’ and Maximum size is ‘4000’
 LONG: 2GB
  RAW (size): Maximum size is 2000
 BLOB: 4GB
 ROWID: 64 base number system representing the unique address of a
row in the table
 BFILE: 4GB
 CLOB: 4GB
 DATE
 NUMBER(P,S): " Precision" can range from 1 to 38 and the “Scale”
can range from -84 to 127
 LONG RAW: 2GB

Q5. What is the discard file?

The discard file stores reflected records based on when clause conditions
within the control file. This condition must be satisfied in the table clause.
The file extension of the discard file is .dsc. You must specify the discard file
within the control file using the discard file clause.

Q6. What do you understand about database

schema and what does it hold?
If you are preparing for Oracle SQL interview questions, you must
prepare this one thoroughly. Schema is owned by a database user who can
manipulate and create new objects within this schema. It is collections of
database objects and contains DB objects, such as tables, clusters, indexes,
view, stored procs, functions, and more.

Q7. Explain Non Equi Join with an example?

This is one of the most asked SQL Query interview questions for
experienced professionals. You should know when two or more tables are
joined without equal to (=) condition, then that join is referred to as non equi
join. You can use any operator, such as <>, !=, <, >, between. For example,
Select b.Department_ID,b.Department_name from Employee a,Department b
where a.Department_id <> b.Department_ID.

Q8. Where is the MyISAM table stored?

All of the MyISAM tables are stored on disk in three files.
1. The “.frm” file stores the table definition.

2. The data file has a ‘.MYD’ (MYData) extension.

3. The index file has a ‘.MYI’ (MYIndex) extension.

Q1. What are Primary Keys in SQL?

Primary keys in SQL are used to identify records in a table. A primary key can
be a column or a group of columns that uniquely identify each row in a table.
It is important that database tables have primary keys to identify unique
table values.

Q2. What are subqueries in SQL?

This is one of the most common Google database interview
questions asked in technical interviews.

Subqueries in SQL are queries that are nested within any of the four major
SQL statements - SELECT, DELETE, INSERT and UPDATE. Any query nested
within a subquery is also considered a subquery in SQL.

Q3. What are constraints in SQL?

Constraints in SQL are a set of rules that are applied to entries in a table.
These rules prevent certain data types from being included in tables. Some
common SQL constraints include:

 UNIQUE - This constraint is used by the Primary key, and ensures that
entries in a column are unique.
 NOT NULL - This constraint prevents null values from being entered
into a table
 FOREIGN KEY - This constraint is used to identify a unique row in
another table.

Q4. What is Query Optimization in SQL?

Query optimization is the process of choosing the right SQL statements while
writing queries to enable quicker outputs and improve system performance.

Q5. Which are the four major subsets of SQL?

The four major subsets of SQL include:
  Data Definition Language
 Data Manipulation Language
 Data Control Language
 Transaction Control Language

Q1. How would you find duplicate records in

SQL?
You can find duplicate records using the following SQL functions:

 GROUP BY function to group all rows by the column/ columns you

want to check for duplicate values.
 RANK function: It returns a unique rank number for every distinct row
to a specified column value. It starts at 1 for the first row in every
partition, with the same rank for duplicate values, leaving gaps
between the ranks. You can check duplicate values wherever the gaps
appear in the sequence.
Recommended reading: Top Advanced SQL Interview Questions and
Answers.

Q2. What are the various types of SQL Joins?

Joins in SQL are of the following types:

 Right Join: It concatenates all rows from the right table and the
matching left table rows.
 Left Join: It concatenates all rows from the left table and the
corresponding right table rows.
 Inner Join: It selects and returns records with matching values in both
tables.
 Full Join: It returns all records of both tables if there is a matching
record in either left or the right table.
 Self Join: It is a regular join, and it simply joins a table to itself. The
table is treated similarly to two different tables.
 Cartesian Join: It is also called a cross join as it returns the rows in
the first table by multiplying them with the second table rows.
Recommended Reading: Top SQL Joins Interview Questions and
Answers you should practice.

Q3. What is a trigger in SQL?

A trigger in SQL is a type of stored procedure. It automatically runs as an
event occurs in the database server. An SQL server has the following three
types of triggers:

 DML (Data Manipulation Language) triggers: It runs when you try

to modify data through DML events such as Insert, Update, or Delete
statements.
 DDL (Data Definition Language) triggers: It runs in response to
DDL events such as Create, Drop, Alter, Grant, Denay, and Revoke.
 Logon triggers: It runs in response to a LOGON event. It is fired after
completing the authentication phase of logging in but before the user
session gets established.
Read the 50+ SQL Interview Questions and tips on how to crack them.

Q4. What is the difference between static SQL

and dynamic SQL?
The following table enumerates the key differences between static and
dynamic SQL:

Feature Static (Embedded) SQL Dynamic (Interactive) SQL

Access Predetermined how the database Determined at runtime how the

Determination will be accessed database will be accessed

Efficiency Swifter and more efficient Less efficient and swift

SQL statements are compiled at SQL statements are compiled at

Compilation Time
compile time run time

Data Distribution Suitable for uniform data Generally used with non-
 Feature Static (Embedded) SQL Dynamic (Interactive) SQL

Suitability distributions uniformly distributed data

Does not use EXECUTE Uses EXECUTE IMMEDIATE,

Use of EXECUTE
IMMEDIATE, EXECUTE, and EXECUTE, and PREPARE
Statements
PREPARE statements statements

Flexibility Less flexible More flexible

Q5. What is Oracle SQL Developer used for?

Oracle SQL Developer is a free access IDE (Integrated Development
Environment) that allows you to work with SQL in Oracle databases. It is
written in Java. This graphical tool enhances productivity while simplifying
database development tasks. You can use Oracle SQL Developer for
browsing database objects, running SQL statements, editing and debugging
PL/SQL statements, and more.

1. What is normalization? What are the different normalizations?

The process of organizing fields and tables of a database to minimize

redundancy and dependency. It allows you to add, delete or modify fields
that can be incorporated into a single table. The different normalizations are
as follows:
  First Normal Form (1NF): This should remove a table's duplicate
columns. It is for the identification of unique columns and the creation
of tables for the related data.
 Second Normal Form (2NF): A table is in its second normal form if it
meets all requirements of the first normal form and places the subsets
of data in separate tables. The relationships between tables are
created using primary keys.
 Third Normal Form (3NF): The table should be in the second normal
form. There should be no dependency on primary key constraints.
 Fourth Normal Form (4NF): It should meet all the requirements of
the third normal form and should not have multi-valued dependencies.
2. What is denormalization?

Using the denormalization technique, you can access the data from higher to
lower normal forms of the database. As the name suggests, it is a reversal of
normalization, i.e., it introduces redundancy into a table as it incorporates
data from the related tables.

3. What is collation? What are the different types of collation

sensitivity?

It is a set of rules determining how character data can be sorted and

compared. You can use collation to compare A and other language
characters. It depends on the width of the characters. You can use ASCII
values to compare these character data.

The different types of collation sensitivity are as follows:

4. What do you understand about CASE statements?

You can use a CASE statement to check certain conditions and return a value
based on whether the conditions are evaluated as true. CASE allows you to
bring logic, conditions, and order in combination with clauses like WHERE
and ORDER BY.

A CASE expression is different from a CASE statement. An expression

evaluates a list of conditions. It returns one of the multiple possible result
expressions. Its result is a single value, whereas a CASE statement result is
the execution of a sequence of statements.

5. What are some common SQL commands?

Some common commands are as follows:

 UNION: It combines the results of two tables. It also removes duplicate
rows from the tables.
 MINUS: It returns rows from the first query and not from the second
query.
 INTERSECT: It returns rows returned by both the queries.
 DELETE: It removes one or more rows from a table.
 TRUNCATE: It deletes all the rows from the table. Thus, it frees the
space containing the table.
 INSERT: It inserts data into the row of a table.
 UPDATE: It updates or modifies the value of a column in the table.
 ALIAS: It is a name that you can give to a table or column. You can
refer to this name in a WHERE clause to identify the table or column.
 ALTER: It alters the structure of the database. It allows you to add a
column or modify an existing one.
 DROP: It removes tables and databases from RDBMS.
 CREATE: It defines the database structure schema.
 GRANT: It gives the user access privileges to a database.
 REVOKE: It takes back permissions from the user.
  COMMIT: It saves all the transactions to the database.
 ROLLBACK: It allows you to undo transactions that haven't been
saved to the database.
 SAVEPOINT: It allows you to set a savepoint within a transaction.
 SELECT: It selects the attribute as described by the WHERE clause.
6. What is SQL and why is it used?

SQL (Structured Query Language) is a programming language used for

managing and manipulating relational databases. It is used to create,
update, and query the data stored in these databases. SQL is the standard
language for interacting with relational databases and is supported by most
relational database management systems (RDBMS) such as MySQL, Oracle,
SQL Server, and PostgreSQL.

7. What are the main differences between SQL and other

programming languages?

The main differences between SQL and other programming languages are as
follows:
8. What are the main data types in SQL?

In SQL, the main data types are as follows:

 INT (integer)
 FLOAT (floating point number)
 CHAR (fixed-length character string)
 VARCHAR (variable-length character string)
 DATE (date)
 DATETIME (date and time)
 BOOLEAN (true or false)
9. How do you select, insert, update and delete data in SQL?

10. What is the difference between a primary key and a foreign key?

A primary key is a column or set of columns in a table that uniquely identifies

each row in the table. It is used to enforce the integrity of the data in the
table and to create relationships with other tables. A foreign key is a column
or set of columns in a table that references the primary key of another table.
It is used to enforce referential integrity and to create relationships between
tables.

11. How do you create a table and its constraints in SQL?

To create a table and its constraints in SQL, you can use the CREATE TABLE
statement. Here is an example of how to create a table called "orders" with a
primary key and a foreign key:

CREATE TABLE orders (

order_id INT PRIMARY KEY,

customer_id INT,

order_date DATE,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

12. How do you use JOINs in SQL?

JOINs in SQL are used to combine data from two or more tables based on a
related column between them. There are several types of JOINs, including
INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

An example of using an INNER JOIN in SQL would be as follows:

SELECT *

FROM orders

JOIN customers

ON orders.customer_id = customers.customer_id;

13. How do you use aggregate functions in SQL?

Aggregate functions in SQL are used to perform calculations on a set of

values and return a single value. Common aggregate functions include
COUNT, SUM, AVG, MIN, and MAX.

An example of using the SUM aggregate function in SQL would be:

SELECT SUM(price)

FROM products;

14. How do you use subqueries and temporary tables in SQL?

A subquery is a query that is nested inside another query, and it is used to

return a set of results that will be used by the outer query. Subqueries can
be used in various parts of a SQL statement, such as in the SELECT, FROM,
WHERE, and HAVING clauses. Here is an example of how to use a subquery
in the WHERE clause of a SELECT statement:

SELECT * FROM orders

WHERE customer_id IN (SELECT customer_id FROM customers WHERE

country = 'USA');

A temporary table is a table that exists only for the duration of a session or a
transaction and is typically used to store intermediate results for a complex
query. To create a temporary table in SQL, you can use the CREATE
TEMPORARY TABLE statement, like this:

CREATE TEMPORARY TABLE temp_table AS

SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

15. How do you optimize and troubleshoot SQL queries?

To optimize and troubleshoot SQL queries, you can use various techniques
such as:

 Indexing columns that are frequently searched or sorted on

 Profiling the query execution plan to identify slow or inefficient parts of
the query
 Using EXPLAIN or EXPLAIN ANALYZE to understand the query execution
plan
 Using tools such as pgAdmin, MySQL Workbench, or SQL Server
Management Studio to monitor performance metrics such as CPU and
memory usage
 Running the query with different parameters and data to identify any
issues with specific values
 Simplifying the query by breaking it down into smaller queries
16. How do you use indexes and views in SQL?

Indexes in SQL are used to improve the performance of queries by allowing

the database management system (DBMS) to quickly locate and retrieve the
requested data. An index is a separate data structure that is associated with
a table and contains a copy of the data from one or more columns of the
table, along with a pointer to the location of the corresponding rows in the
table.

For example, to create an index on the "customer_id" column of the "orders"

table, you would use the following SQL statement:

CREATE INDEX idx_customer_id ON orders (customer_id);

Views in SQL are virtual tables that are based on the result of a SELECT
statement. They do not store data themselves, but rather provide a way to
access data from one or more tables in a specific way, such as by filtering,
joining, or aggregating the data.

For example, to create a view that shows all orders with a total price greater
than $100, you would use the following SQL statement:

CREATE VIEW high_value_orders AS SELECT * FROM orders WHERE

total_price > 100;
In SQL, NULL values represent missing or unknown data. When working with
NULL values in SQL, it's important to understand the difference between
NULL and an empty string or a zero value.

17. How do you handle NULL values in SQL?

There are several ways to handle NULL values in SQL:

i. Using the IS NULL or IS NOT NULL operators in a WHERE clause to filter

for or exclude NULL values.
SELECT * FROM customers WHERE last_name IS NOT NULL;

ii. Using the COALESCE() or NULLIF() functions to replace NULL values

with a specific value or another expression.
SELECT COALESCE(last_name, 'N/A') AS last_name FROM customers;

iii. Using the NULL value in a comparison operator to include NULL values
in the result set.
SELECT * FROM orders WHERE total_price > 100 OR total_price IS NULL;

It's important to note that when using any comparison operator other than IS
NULL or IS NOT NULL, with a NULL value, it will return false, so you need to
use the IS NULL or IS NOT NULL operator to handle NULL values in the
comparison.

18. How do you implement security in SQL?

Implementing security in SQL involves a combination of several different

techniques, including:

i. Access control: This involves limiting access to the database based on

user credentials and permissions. Users are assigned different roles
and are only granted access to the data they need to perform their job.
ii. Encryption: This involves encrypting sensitive data, such as credit card
numbers, to protect it from unauthorized access.
iii. Auditing: This involves keeping track of all actions performed on the
database, such as who accessed it, when, and what data was
accessed. This allows for the detection of any unauthorized access
attempts.
iv. Input validation: This involves validating user input to prevent SQL
injection attacks, which are a common type of security vulnerability.
 v. Firewall: This involves setting up a firewall to block unauthorized
access to the database from the network

SQL Queries Interview Questions and

Answers for Experienced Professionals
The interviewers might present a SQL query and ask questions on the same.
They can also ask you to write SQL queries. Here are some SQL query
interview questions for experienced professionals.

i. Write an SQL query to display the current date.

To display the current date in SQL, you can use the GETDATE() function:

SELECT GETDATE();

ii. Write an SQL query to verify if the data passed to the query is
of the given format: "DD/MM/YY."
To verify if the data passed to a query is in the format "DD/MM/YY," you can
use the ISDATE() function along with the appropriate format string:

SELECT ISDATE('DD/MM/YY')

This will return 1 if the passed data is in the format "DD/MM/YY" and 0
otherwise.

Please keep in mind that the above query only check the passed string
format and not the actual data, to check if the data passed to the query is in
the format "DD/MM/YY" and is a valid date you can use the following query

SELECT CASE

WHEN ISDATE(your_date_column) = 1 AND your_date_column like

'__/__/__' THEN 'valid date'

ELSE 'invalid date'

END as 'date_status'

iii. Write an SQL query to print the candidate's name, whose birth
date is 08/09/1970 to 30/11/1975.
SELECT name FROM candidates
WHERE birth_date BETWEEN '1970-09-08' AND '1975-11-30';

iv. Write a query to print an employee's name whose name starts

with 'S.'
SELECT name FROM employees

WHERE name LIKE 'S%';

v. Write a query to find an employee whose salary is less than or

equal to 10000.
SELECT * FROM employees WHERE salary <= 10000;

vi. Write a query to find the month from a given date.

SELECT MONTH(date_column) FROM table_name;

vii. Write a query to join three tables containing two NULL values.
To join three tables containing two NULL values, you can use the LEFT JOIN
clause. The LEFT JOIN returns all rows from the left table, and the matched
rows from the right table. If there is no match, NULL values will be returned
for the right table's columns:

SELECT *

FROM table1

LEFT JOIN table2 ON table1.column_name = table2.column_name

LEFT JOIN table3 ON table2.column_name = table3.column_name

viii. Write a query to fetch the first three characters of the

employee name.
To fetch the first three characters of the employee name from a table called
"employee" and a column called "name", you can use the SUBSTRING()
function:

SELECT SUBSTRING(name, 1, 3) as 'name_first_3' FROM employee;

This query will return a new column called "name_first_3" containing the first
three characters of the "name" column for each employee.

ix. Consider the following data table for answering the question
given below:
Answer the following SQL query interview questions with reference
to the above table:

 Write a SQL query to fetch the EmpFname in the upper case.

Use the ALIAS name as EmpName.
SELECT UPPER(EmpFname) AS EmpName FROM Employee;

 Write a SQL query to retrieve the number of employees who

are a part of the HR department.
SELECT COUNT(*) FROM Employee WHERE Department='HR';

 Write a query to fetch the first four characters of EmpLname

whose name starts with 'W.'
SELECT SUBSTR(EmpLname,1,4) FROM Employee WHERE EmpLname LIKE 'W
%';

 Write a SQL query to retrieve the place name, i.e., the string
before brackets, from the Address column.
SELECT SUBSTRING_INDEX(Address,'(',1) as Place FROM Employee;

 Write a SQL query to fetch the names of employees that begin

with 'R.'
SELECT EmpFname FROM Employee WHERE EmpFname LIKE 'R%';

Recommended Reading: SQL query interview questions based on a

sample data table

Oracle PL/SQL Interview Questions for

Experienced Professionals
Oracle created PL/SQL to overcome the disadvantages of SQL. It allows
easier building and handling of critical applications. The following Oracle PL/
SQL interview questions for experienced professionals will help you brush
up on the concepts:

i. What makes PL/SQL a better option than SQL?

PL/SQL (Procedural Language/Structured Query Language) is an extension of
SQL that is used for developing stored procedures, functions, and triggers in
an Oracle database. It provides several features that make it a better option
than plain SQL for certain types of tasks:

 Block structure: PL/SQL allows you to group multiple SQL statements

together into logical blocks, making it easier to organize and manage
your code.
 Control structures: PL/SQL provides various control structures such as
IF-ELSE, LOOP and WHILE etc, which allows you to perform conditional
logic and iteration in your code.
 Error handling: PL/SQL allows you to handle and raise exceptions,
which makes it easier to handle errors and unexpected scenarios.
 Stored procedures and functions: PL/SQL allows you to create and use
stored procedures and functions, which can be called from other
applications or PL/SQL blocks. This can improve performance by
reducing the amount of data that needs to be transferred between the
application and the database.

ii. How would you debug your code in PL/SQL?

To debug your code in PL/SQL, you can use the following methods:
  DBMS_OUTPUT.PUT_LINE : You can use this package to print your
variable values, messages, etc.
 RAISE_APPLICATION_ERROR: This function allows you to raise an error
with a custom error message.
 Using Oracle SQL Developer : It is an Integrated development
environment (IDE) for working with SQL and PL/SQL. It provides
debugging facilities like breakpoints, step-by-step execution and watch
variables.
It's important to note that debugging PL/SQL can be more involved than
debugging other programming languages, and it may require knowledge of
the specific database and tools you are using.

iii. How is the mutating table different from the constraining

table?
A mutating table is a table that is currently being modified by a DML (Data
Manipulation Language) statement, such as an UPDATE or DELETE
statement. This can cause issues when trying to access the same table
within a trigger, as the changes made by the DML statement may not be
visible yet or may not be consistent. To avoid this, a mutating table trigger is
not allowed to read or modify the table that it is associated with.

A constraining table is a table that is being referenced by a foreign key

constraint. This means that the table has a column or set of columns that
reference the primary key of another table. This constraint ensures that the
data in the constraining table is consistent with the data in the referenced
table.

iv. Where are the outcomes of the execution of the DML

statement saved?
The outcome of the execution of a DML statement is saved in the database.
DML statements are used to modify the data in the database, so any
changes made will be persisted in the relevant tables. These changes can be
committed or rolled back, depending on the transaction management in use,
with committed changes being permanent and visible to other sessions, and
rolled-back changes being discarded and not visible to other sessions.

v. What virtual tables are available during the execution of the

database trigger?

 Virtual tables available during the execution of a database trigger

include:
  INSERTED: Contains the new data for any rows affected by an INSERT
or UPDATE statement.
 DELETED: Contains the old data for any rows affected by an UPDATE or
DELETE statement.

v. What is the significance of the SYS.ALL_DEPENDENCIES?

SYS.ALL_DEPENDENCIES is a view in the Oracle database that shows all
dependencies between objects in the database. This view can be used to
determine the dependencies between objects such as tables, views,
procedures, and triggers. The significance of this view is that it can be used
to track dependencies between objects in a database and ensure that
changes to one object do not break any other objects that depend on it. It
can also be used to help identify and resolve issues related to object
invalidation and to help plan and manage upgrades and migrations of the
database.

vii. What is the difference between %TYPE and %ROWTYPE data

types in PL/SQL?
In PL/SQL, %TYPE and %ROWTYPE are used to declare variables.
viii. Write a PL/SQL program to calculate the sum of the digits of a
three-digit number?
DECLARE

num NUMBER := 123; -- Three-digit number

sum NUMBER := 0; -- Variable to store the sum

BEGIN

sum := sum + num MOD 10; -- Add the units digit

num := num DIV 10; -- Remove the units digit

sum := sum + num MOD 10; -- Add the tens digit

num := num DIV 10; -- Remove the tens digit

sum := sum + num MOD 10; -- Add the hundreds digit

DBMS_OUTPUT.PUT_LINE('Sum of digits: ' || sum); -- Print the sum

END;

ix. What are the different ways of commenting in a PL/SQL code?

 There are two ways to comment in PL/SQL code:

 Single-line comments, which begin with two dashes (--) and continue
until the end of the line.
 Multi-line comments, which are enclosed between /* and */

ix. What is exception handling?

Exception handling is a mechanism that allows a program to handle errors
and unexpected conditions in a controlled and predictable manner. It is a
process of dealing with runtime errors that occur during the execution of a
program. PL/SQL provides a rich set of predefined exceptions and also allows
you to define your own exceptions. Exceptions can be handled using the
EXCEPTION block, where you can catch and handle the exceptions that occur
in the EXECUTABLE block. Exception handling allows you to write robust and
fault-tolerant code, making it easier to identify and correct errors and
improve the overall stability of your PL/SQL programs.

xi. What do you understand about INSTEAD OF triggers?

INSTEAD OF triggers are a type of database trigger that are executed in
place of the triggering DML statement, rather than in addition to it. They are
typically used to perform actions that cannot be easily accomplished using
standard DML statements, or to override the default behavior of a view that
cannot be modified directly. They are u

Q1. Write a query to get the last record from a table?

select * from Student where RowID = select max(RowID) from Student;

Q2. Write a query to get the first record from a table?

The first record can be fetched in two ways, one similar to the last record
case, but with min:

select * from Student where RowID = select min(RowID) from Student;

The other method is by printing just one (first) row of the table:

select * from Student where Rownum = 1;

Q3. Write a query to display the first ten records from a table.

select * from Student where Rownum <= 10;

Q4. Create a table with the same structure with data as in the
Student table.

Create table Student2 as Select * from Student;

Q5. Show only common records between two tables.

Select * from Student;

Intersect

Select * from StudentA;

Want to know what SQL interview questions are asked at Facebook? Read
this article.

Complex SQL Interview Questions for

Practice
Now, check out these frequently asked complex SQL interview questions to
gauge your interview preparation:

Complex PL SQL Server Interview Questions

These complex PL SQL server questions regularly feature at tech interviews
for SQL developers:

i. Define and describe the usage of a linked server.

ii. Name and explain the different types of Joins.
iii. Explain the different types of authentication modes.
iv. Which stored procedure would you run when adding a linked server?
v. Where do you think the user names and passwords will be stored in the
SQL server?
vi. How would you add email validation using only one query?
Check out this article for more basic and advanced PL SQL interview
questions.

Complex SQL Queries Interview Questions

Interview questions related to complex SQL queries are quite common at
interviews for developers. These involve questions like “what SQL query
would you use to”:

i. Fetch the number of Weekends in the current month

ii. Get the last 5 Records from the Student table
iii. Get the common records present in two different tables that have no
joining conditions.
iv. Display records 5 to 9 from the Employee table.
v. Display the last record of a table
vi. Display the third-last record of a table
 vii. Convert seconds into time format
viii. Remove duplicate rows from a table
ix. Find the number of duplicate rows
x. Find the fourth-highest score in the Students table using self-join
xi. Show the max and min salary together from the Employees table
xii. Display date in a DD-MM-YYYY table
xiii. Create Employee_C table, which is the exact replica of the Employee
table
xiv. Drop all user tables from Oracle
xv. Calculate the number of rows in a table without using count
xvi. Find repeated characters from your name
xvii. Display department and month-wise maximum salary
xviii. Find the second-highest salary in the Employee table.
xix. Select all the records from the Student table, where the names are
either Anu or Dan.
xx. Select all the records from the Student table where the name is not
Anu and Dan.
xxi. Get Nth Record from the Student table.
xxii. Get the 3 Highest salaries records from the Student table
xxiii. Show Odd rows in the Student table
xxiv. Show Even rows in the Student table
xxv. Get the DDL of a table
xxvi. Get all the records from Employees who have joined in the year 2020.
xxvii. Find the maximum salary of each department.
xxviii. Find all Employees with their managers.
xxix. Display the name of employees who joined in 2020 and have a salary
is greater than 50000.
xxx. Get the first 5 Records from the Student table.
xxxi. Get information of Employees where Employee is not assigned to any
department.
xxxii. Show 1 to 100 Numbers
xxxiii. Find duplicate rows in a table
 xxxiv. Get the previous month’s last day.
xxxv. Display a string vertically.
xxxvi. The marks column in the Student table contains comma-separated
values. How would you calculate the number of those comma-
separated values?
xxxvii. Get the 3rd highest salary using Rank Function.
xxxviii. Create a table with its structure the same as the structure of the
Student table.
xxxix. Display first 25% records from the Student table
xl. Display last 25% records from the Student table
xli. Create a table with the same structure and data as the Student table
xlii. Get only the common records between two tables
xliii. Get unique records from the table without using distinct keywords.
xliv. Find the admission date of the Student in YYYY-DAY-Date format.
xlv. Convert the System time into seconds.
xlvi. Display monthly Salary of Employee given annual salary.
xlvii. Get the first record from the Student table
xlviii. Get the last record from the Student table
To practice more SQL interview questions, read this article.

What do experts say?

Manish Devgan, CPO of the real-time data platform, Hazelcast, says,

“Markets such as retail, e-commerce, and energy are seeing growing interest
in applications where data has to be processed and analyzed in real-time.
The use of SQL within streaming systems opens up a new chapter in the
story of SQL within the data domain.”

FAQs on Complex SQL Interview

Questions
Q1. What is a complex SQL query?

Complex SQL queries are parameter queries that go beyond the standard
SQL usage of SELECT and WHERE and use two or more parameters. They
also often heavily use AND and OR clauses. Complex queries are helpful
because we can make more precise and accurate database searches with
them.

Q2. How many types of SQL are there?

There are five types of SQL commands on the basis of the functionalities
performed by them: DDL(Data Definition Language), DQL(Data Query
Language), DCL(Data Control Language), DML(Data Manipulation Language)
and TCL(Transaction Control Language).

Q3. Give a comparison between PostgreSQL and MongoDB.

PostgreSQL is a SQL database that uses tables with organized rows and
columns for storing data. It is compatible with notions like JOINS and
referential integrity entity-relationship. PostgreSQL uses SQL as the query
language.

On the other hand, MongoDB is a NoSQL database. It is capable of storing

raw data because a schema is not necessary. Data is kept in BSON
documents, and the user can modify the document's structure. The query
language used by MongoDB is JavaScript.

Q4. What is the difference between simple and complex views in

SQL?

Simple views in SQL can contain only one base table. On the other hand,
complex views in SQL have more than one base table. Complex views can
also have a group by clause, join conditions, and order by clause.

Q5. What is a unique key in SQL?

The collection of data or columns in a table that allows us to recognize

records distinctively is known as a unique key in SQL. The unique key
ensures that the columns in the database are all unique. It is equivalent to
the primary key; however, it may accept a null value compared to it.

Q #1) What is Oracle and what are its different editions?

Answer: Oracle is one of the popular databases provided by Oracle
Corporation, which works on relational management concepts, and
hence it is referred to as Oracle RDBMS as well. It is widely used for
online transaction processing, data warehousing, and enterprise grid
computing.
Q #2) How will you identify Oracle Database Software
Release?
Answer: Oracle follows a number of formats for every release.
For Example,
Release 10.1.0.1.1 can be referred to as:
10: Major DB Release Number
1: DB Maintenance Release Number
0: Application Server Release Number
1: Component Specific Release Number
1: Platform Specific Release Number
Q #3) How will you differentiate between VARCHAR &
VARCHAR2?
Answer: Both VARCHAR & VARCHAR2 are Oracle data types that
are used to store character strings of variable length. Their
differences are:
 VARCHAR can store characters up to 2000 bytes while
VARCHAR2 can store up to 4000 bytes.
 VARCHAR will hold the space for characters defined
during declaration even if all of them are not used
whereas VARCHAR2 will release the unused space.
Q #4) What is the difference between TRUNCATE & DELETE
commands?
Answer: Both commands are used to remove data from the
database.
The difference between the two include:
 TRUNCATE is a DDL operation while DELETE is a DML
operation.
 TRUNCATE removes all the rows but leaves the table
structure intact. It can not be rolled back as it issues
COMMIT before and after the command execution while
the DELETE command can be rolled back.
 The TRUNCATE command will free the object storage
space while the DELETE command does not.
 TRUNCATE is faster compared to DELETE.
Q #5) What is meant by RAW datatype?
Answer: RAW datatype is used to store variable-length binary data
or byte strings.
The difference between RAW & VARCHAR2 datatype is that PL/SQL
does not recognize this data type and hence, cannot do any
conversions when RAW data is transferred to different systems. This
data type can only be queried or inserted in a table.

Syntax: RAW (precision)

Q #6) What is meant by Joins? List the types of Joins.
Answer: Joins are used to extract data from multiple tables using
some common columns or conditions.
There are various types of Joins as listed below:
 INNER JOIN
 OUTER JOIN
 CROSS JOINS or CARTESIAN PRODUCT
 EQUI JOIN
 ANTI JOIN
 SEMI JOIN
Q #7) What is the difference between SUBSTR & INSTR
functions?
Answer:
 SUBSTR function returns the sub-part identified by
numeric values from the provided string.
 For Example, [SELECT SUBSTR (‘India is my
country, 1, 4) from dual] will return “Indi”.
 INSTR will return the position number of the sub-string
within the string.
 For Example, [SELECT INSTR (‘India is my
country, ‘a’) from dual] will return 5.
Q #8) How can we find out the duplicate values in an Oracle
table?
Answer: We can use the below example query to fetch the
duplicate records.
1SELECT EMP_NAME, COUNT (EMP_NAME)
2FROM EMP
3GROUP BY EMP_NAME
4HAVING COUNT (EMP_NAME) &amp;gt; 1;
Q #9) How does the ON-DELETE-CASCADE statement work?
Answer: Using ON DELETE CASCADE will automatically delete a
record in the child table when the same is deleted from the parent
table. This statement can be used with Foreign Keys.
We can add the ON DELETE CASCADE option to an existing table
using the below set of commands.

Syntax:
1ALTER TABLE CHILD_T1 ADD CONSTRAINT CHILD_PARENT_FK REFERENCES
2PARENT_T1 (COLUMN1) ON DELETE CASCADE;
Q #10) What is an NVL function? How can it be used?
Answer: NVL is a function that helps the user to substitute value if
null is encountered for an expression.
It can be used as the below syntax.
1NVL (Value_In, Replace_With)
Q #11) What is the difference between a Primary Key & a
Unique Key?
Answer: Primary Key is used to identify each table row uniquely,
while a Unique Key prevents duplicate values in a table column.
Given below are a few differences:
 The primary key can be only one on the table while
unique keys can be multiple.
 The primary key cannot hold a null value at all while the
unique key allows multiple null values.
 The primary key is a clustered index while a unique key is
a non-clustered index.
Q #12) How TRANSLATE command is different from
REPLACE?
Answer: TRANSLATE command translates characters one by one in
the provided string with the substitution character. REPLACE
command will replace a character or a set of characters with a
complete substitution string.
For Example:
1TRANSLATE (‘Missisippi’,’is’,’15) =&amp;gt; M155151pp1
2REPLACE (‘Missisippi’,’is’,’15) =&amp;gt;&nbsp; M15s15ippi
Q #13) How can we find out the current date and time in
Oracle?
Answer: We can find the current date & time using SYSDATE
command in Oracle.
Syntax:
1SELECT SYSDATE into CURRENT_DATE from dual;
Q #14) Why do we use COALESCE function in Oracle?
Answer: COALESCE function is used to return the first non-null
expression from the list of arguments provided in the expression.
There must be a minimum of two arguments in an expression.
Syntax:
1COALESCE (expr 1, expr 2, expr 3…expr n)
Q #15) How will you write a query to get 5th RANK students
from the table STUDENT_REPORT?
Answer: The query will be as follows:
1SELECT TOP 1 RANK
2FROM (SELECT TOP 5 RANK
3FROM STUDENT_REPORT
4ORDER BY RANK DESC) AS STUDENT
5ORDER BY RANK ASC;
Q #16) When do we use the GROUP BY clause in SQL Query?
Answer: GROUP BY clause is used to identify and group the data by
one or more columns in the query results. This clause is often used
with aggregate functions like COUNT, MAX, MIN, SUM, AVG, etc.
Syntax:
1SELECT COLUMN_1, COLUMN_2
2FROM TABLENAME
3WHERE [condition]
4GROUP BY COLUMN_1, COLUMN_2
Q #17) What is the quickest way to fetch the data from a
table?
Answer: The quickest way to fetch the data would be to use ROWID
in the SQL query.
Q #18) Where do we use DECODE and CASE Statements?
Answer: Both DECODE & CASE statements will function like IF-
THEN-ELSE statements and they are the alternatives for each other.
These functions are used in Oracle to transform the data values.
For Example:
DECODE Function
1Select ORDERNUM,
2DECODE (STATUS,'O', ‘ORDERED’,'P', ‘PACKED,’S’,’SHIPPED’,’A’,’ARRIVED’)
3FROM ORDERS;
CASE Function
1Select ORDERNUM
2, CASE (WHEN STATUS ='O' then ‘ORDERED’
3WHEN STATUS ='P' then PACKED
4WHEN STATUS ='S' then ’SHIPPED’
5ELSE ’ARRIVED’) END
6FROM ORDERS;
Both the commands will display order numbers with their
respective status as,
If,
Status O= Ordered
Status P= Packed
Status S= Shipped
Status A= Arrived

Q #19) Why do we need integrity constraints in a database?

Answer: Integrity constraints are required to enforce business rules
so as to maintain the integrity of the database and prevent the
entry of invalid data into the tables. With the help of the below-
mentioned constraints, relationships can be maintained between the
tables.
Various integrity constraints are available which include Primary
Key, Foreign Key, UNIQUE KEY, NOT NULL & CHECK.

Q #20) What do you mean by MERGE in Oracle and how can

we merge two tables?
Answer: The MERGE statement is used to merge the data from two
tables. It selects the data from the source table and inserts/updates
it in the other table based on the condition provided in the MERGE
query.
Syntax:
1
2MERGE INTO TARGET_TABLE_1
3USING SOURCE_TABLE_1
4ON SEARCH_CONDITION
5WHEN MATCHED THEN
6INSERT (COL_1, COL_2…)
7VALUES (VAL_1, VAL_2…)
8WHERE &amp;lt;CONDITION&amp;gt;
9WHEN NOT MATCHED THEN
1UPDATE SET COL_1=VAL_1, COL_2=VAL_2…
0WHEN &amp;lt;CONDITION&amp;gt;
Q #21) What is the use of Aggregate functions in Oracle?
Answer: Aggregate functions perform summary operations on a set
of values to provide a single value. There are several aggregate
functions that we use in our code to perform calculations. These
are:
 AVG
 MIN
 MAX
 COUNT
 SUM
  STDEV
Q #22) What are the set operators UNION, UNION ALL,
MINUS & INTERSECT meant to do?
Answer: The set operator facilitates the user to fetch the data from
two or more than two tables at once if the columns and relative data
types are the same in the source tables.
 UNION operator returns all the rows from both tables
except the duplicate rows.
 UNION ALL returns all the rows from both the tables
along with the duplicate rows.
 MINUS returns rows from the first table, which does not
exist in the second table.
 INTERSECT returns only the common rows in both
tables.
Q #23) Can we convert a date to char in Oracle and if so,
what would be the syntax?
Answer: We can use the TO_CHAR function to do the above
conversion.
Syntax:
1SELECT&nbsp;to_char&nbsp;(to_date&nbsp;('30-01-2018',&nbsp;'DD-MM-YYYY'),
Q #24) What do you mean by a database transaction & what
all TCL statements are available in Oracle?
Answer: Transaction occurs when a set of SQL statements are
executed in one go. To control the execution of these statements,
Oracle has introduced TCL i.e. Transaction Control Statements that
use a set of statements.
The set of statements includes:
 COMMIT: Used to make a transaction permanent.
 ROLLBACK: Used to roll back the state of DB to last the
commit point.
 SAVEPOINT: Helps to specify a transaction point to
which rollback can be done later.
Q #25) What do you understand by a database object? Can
you list a few of them?
Answer: The object used to store the data or references of the data
in a database is known as a database object. The database consists
of various types of DB objects such as tables, views, indexes,
constraints, stored procedures, triggers, etc.
Q #26) What is a nested table and how is it different from a
normal table?
Answer: A nested table is a database collection object, which can
be stored as a column in a table. While creating a normal table, an
entire nested table can be referenced in a single column. Nested
tables have only one column with no restriction of rows.
For Example:
1CREATE&nbsp;TABLE&nbsp;EMP&nbsp;(
2EMP_ID&nbsp;NUMBER,
3EMP_NAME &nbsp;TYPE_NAME)
Here, we are creating a normal table as EMP and referring to a
nested table TYPE_NAME as a column.

Q #27) Can we save images in a database and if yes, how?

Answer: BLOB stands for Binary Large Object, which is a data type
that is generally used to hold images, audio & video files, or some
binary executables. This datatype has the capacity of holding data
up to 4 GB.
Q #28) What do you understand by database schema and
what does it hold?
Answer: Schema is a collection of database objects owned by a
database user who can create or manipulate new objects within this
schema. The schema can contain any DB objects like table, view,
indexes, clusters, stored procs, functions, etc.
Q #29) What is a data dictionary and how can it be created?
Answer: Whenever a new database is created, a database-specific
data dictionary gets created by the system. This dictionary is owned
by the SYS user and maintains all the metadata related to the
database. It has a set of read-only tables and views and it is
physically stored in the SYSTEM tablespace.
Q #30) What is a View and how is it different from a table?
Answer: View is a user-defined database object that is used to
store the results of an SQL query, which can be referenced later.
Views do not store this data physically but as a virtual table, hence
it can be referred to as a logical table.
The view is different from the table:
 A table can hold data but not SQL query results whereas
View can save the query results, which can be used in
another SQL query as a whole.
 The table can be updated or deleted while Views cannot
be done so.
Q #31) What is meant by a deadlock situation?
Answer: A deadlock is a situation when two or more users are
simultaneously waiting for the data, which is locked by each other.
Hence it results in all blocked user sessions.
Q #32) What is meant by an index?
Answer: An index is a schema object, which is created to search
the data efficiently within the table. Indexes are usually created on
certain columns of the table, which are accessed the most. Indexes
can be clustered or non-clustered.
Q#33) What is a ROLE in the Oracle database?
Answer: Giving access to individual objects to individual users is a
tough administrative task. In order to make this job easy, a group of
common privileges is created in a database, which is known as
ROLE. The ROLE, once created can be assigned to or revoked from
the users by using GRANT & REVOKE command.
Syntax:
1CREATE ROLE READ_TABLE_ROLE;
2GRANT SELECT ON EMP TO READ_TABLE_ROLE;
3GRANT READ_TABLE_ROLE TO USER1;
4REVOKE READ_TABLE_ROLE FROM USER1;
Q #34) What are the attributes that are found in a CURSOR?
Answer: A CURSOR has various attributes as mentioned
below:
(i) %FOUND:
 Returns INVALID_CURSOR if the cursor has been declared
but closed.
 Returns NULL if fetch has not happened but the cursor is
open only.
 Returns TRUE, if the rows are fetched successfully and
FALSE if no rows are returned.
(ii) NOT FOUND:
 Returns INVALID_CURSOR if the cursor has been declared
but closed.
 Returns NULL if fetch has not happened but the cursor is
open only.
 Returns FALSE, if rows are fetched successfully and TRUE
if no rows are returned
(iii) %ISOPEN: Returns TRUE, if the cursor is OPEN else FALSE
(iv) %ROWCOUNT: Returns the count of fetched rows.
Q #35) Why do we use %ROWTYPE & %TYPE in PLSQL?
Answer: %ROWTYPE & %TYPE are the attributes in PL/SQL that can
inherit the datatypes of a table defined in a database. The purpose
of using these attributes is to provide data independence and
integrity.
If any of the datatypes or precision gets changed in the database,
PL/SQL code gets updated automatically with the changed data
type.

%TYPE is used for declaring a variable that needs to have the same
data type as of a table column.

While %ROWTYPE will be used to define a complete row of records

having a structure similar to the structure of a table.

Q #36) Why do we create Stored Procedures & Functions in

PL/SQL and how are they different?
Answer: A stored procedure is a set of SQL statements that are
written to perform a specific task. These statements can be saved
as a group in the database with an assigned name and can be
shared with different programs if permissions are there to access
the same.
Functions are again subprograms that are written to perform
specific tasks but there are differences between both of them.

Stored Procedures Functions

Stored Procedures may or may not return a value and can return multiple values as Function will always return
well. value.
Stored Procedures can include DML statements like insert, update & delete. We cannot use DML statem
function.
Stored Procedures can call functions. Functions cannot call stored
Stored Procedures support exception handling using Try/Catch block. Functions does not support T
block.
Q #37) What are the parameters that we can pass through a
stored procedure?
Answer: We can pass IN, OUT & INOUT parameters through a
stored procedure and they should be defined while declaring the
procedure itself.
Q #38) What is a trigger and what are its types?
Answer: A trigger is a stored program that is written in such a way
that it gets executed automatically when some event occurs. This
event can be any DML or a DDL operation.
PL/SQL supports two types of triggers:
 Row Level
  Statement Level
Q #39) How will you distinguish a global variable from a
local variable in PL/SQL?
Answer: Global variable is the one, which is defined at the
beginning of the program and survives until the end. It can be
accessed by any methods or procedures within the program, while
the access to the local variable is limited to the procedure or
method where it is declared.
Q #40) What are the packages in PL SQL?
Answer: A package is a group of related database objects like
stored procs, functions, types, triggers, cursors, etc. that are stored
in the Oracle database. It is a kind of library of related objects which
can be accessed by multiple applications if permitted.
PL/SQL Package structure consists of 2 parts: package specification
& package body.

Q #11) Can you define an ERP System?

Answer: ERP stands for Enterprise Resource Planning. It is a
business software system that allows an organization to automate
and integrate with the various business processes sharing common
data across enterprises.
ERP software typically integrates the different facets of an operation
like product planning, development, and manufacturing, sales,
marketing, etc. using a single database, application, and interface.

Q #12) Explain Oracle Apps architecture.

Answer: Oracle Apps is a multi-tier architecture environment where
the application server serves as an interface between clients and
database servers.
Hence, the components of this architecture are:
 Clients
 Application servers
 Database servers
Clients will initiate the request for an operation to be executed by
the database. The application server will act as an intermediate
layer by sending the client request to the database and providing
the requested data to the client.
Q #13) Can we create tables in the APPS schema?
Answer: No, we cannot create tables in APPS schema and this
schema will only have synonyms.
Q #14) What is a flexfield? List out its types.
Answer: Flexfield is a database field possessing the inbuilt
flexibility in defining reporting structure, which is relevant to specific
organizations. A flexfield is made up of segments or subfields.
There are two types of flex fields:
 Key flexfields: They are used to record key data
elements.
 Descriptive flexfields: They are user-defined flexfields
and can be customized.
Q #15) What are the different components you should
consider while defining responsibility in Oracle Apps?
Answer: A responsibility is a group of components. These are:
 Menu (mandatory): It is an arrangement of forms in a
hierarchical manner.
 Data Group (mandatory): It defines the mapping
between Oracle App products like forms, reports,
concurrent programs, and Oracle database Ids.
 Responsibilities and Request Security Groups
(mandatory): A request security group is the request
group assigned to a responsibility.
 Function and Menu Exclusions
(optional): Responsibility may or may not have an
associated function and menu exclusion rules.
Q #16) What is a Value Set? List its various types.
Answer: As the name itself suggests, the value set is a predefined
list of values used by Oracle for validation. It restricts the end-user
to enter junk data by providing an option to select a value from the
predefined set of values.
Oracle supports eight types of value set validations. These are:
 None (validated minimally)
 Independent
 Dependent
 Table
 Special (advanced)
 Pair (advanced)
 Translated Independent
 Translatable Dependent
Q #17) How can we import the data into a database using
SQL * Loader?
Answer: SQL * Loader is a utility that is used to import the data in
bulk from external files.
Various features of this command-line utility are as shown
below:
 They can perform selective loading.
 They can perform multi-table loads.
 Supports various format files.
This utility can be invoked using a set of commands.

SQLLDR CONTROL=Test.ctl, LOG=Test.log, BAD=Test.bad,

DATA=Test.dat USERID=superuser/XXX, ERRORS=999, LOAD=3000,
DISCARD=toss.dsc, DISCARDMAX=6

Q #18) What do you understand by Concurrent programs?

Answer: An executable file that can execute simultaneously with
the other programs and utilize the hardware capacity to the fullest is
called the concurrent program. Generally, these types of programs
would be long-running and data-intensive. They can be grouped with
reports to form a request group.
Q #19) What does the APPL_TOP directory contain?
Answer: APPL directory stores Oracle e-business suite files and
hence it is commonly known as APPL_TOP directory.
This directory contains the files along with the relative
directories:
 Technology files
 Product files
 Oracle e-business suite environment files
Q #20) What do you understand by a set of books?
Answer: The set of books is a financial reporting entity. It
determines the functional currency, account structure, and calendar
for a company or a group of companies.
There are two types of sets of books.
 Primary: It consists of functional currency
 Secondary: It consists of reporting currency
Oracle Forms Interview Questions
Q #1) What do you understand by Oracle Forms and why are
they required?
Answer: Oracle Forms are the user interfaces that are developed to
present the data to the user. This data can be presented once
retrieved from the Oracle database. If required, forms can be
integrated with web services or Java to follow SOA architecture.
Forms are created at source as .fmb files and later compiled
into .fmx (executable file).
Oracle Forms include:
 Forms Developer: It helps in the development and
compilation of Oracle Forms.
 Forms Services: It is considered for the deployment of
Forms.
Q #2) Explain the different levels at which Oracle Form
Services interact.
Answer: Oracle Form Services is a three-tier application and
hence it will interact at the below levels:
 Client Level
 Server Level
 Database Level
At the client level, HTTP requests will be sent by a client to the
system. This request will be received by the Forms Listener Servlet
at the server and it will initiate the Forms Runtime process. This
process will send the request to the database to retrieve the
information and send it back to the client.

This completes the workflow of user interaction through Oracle

Forms Services.

Q #3) Can we invoke one form from another in a multi-form

application?
Answer: Yes, we can invoke one form from another with the
help of the below built-in functions:
 OPEN_FORM: It opens up the requested form along with
the current form and the user can navigate to both the
forms in the same session.
 NEW_FORM: It will also open up a new form after exiting
from the current form.
  CALL_FORM: It will open the requested form by keeping
the parent form active but hidden. Once exited from the
requested form, control goes back to the parent form.
Q #4) What do you understand by LOV and how can it be
used?
Answer: LOV is a list of values populated in a pop-up window and is
displayed to the end-user for selection. These values can be
assigned and invoked statically or dynamically in LOV.
There is a related property known as ‘LOV for Validation’ which is
used to validate the contents of LOV. If this property is set to true,
the current value of the text item is compared with the values
displayed in the first column of LOV.

If any of the LOV values match the text item, then validation
succeeds and LOV will not be displayed. If the value does not match,
LOV will be displayed and a search will happen based on the text
item.

Q #5) What is a canvas in Oracle Forms?

Answer: A canvas is a layer within a window where visual objects
like interface items or graphics can be placed.
Oracle Forms support four types of canvas as mentioned
below:
 Content canvas (default canvas)
 Tab canvas
 Toolbar canvas
 Stacked canvas
Q #6) In what sequence do triggers get fired by Oracle
Forms?
Answer: Oracle Forms follow the below hierarchy for trigger
execution:
 Pre-form
 Pre-block
 Pre-record
 Pre-text item
 When-new-form-instance
 When-new-block-instance
 When-new-record-instance
 When-new-item-instance
 Post-text_item
  Post-Record
 Post-Block
 Post-Form
Q #7) Explain the Master-Detail relationship with some
examples.
Answer: Master-Detail relationship is the relationship among
different business entities which follows the parent-child relationship
pattern. There will be one parent entity linked to many child entities.
For Example, we can have one master named COMPANY with
different details as DEPARTMENTS (HR, FINANCE, OPERATIONS,
ADMIN, etc.).
This relationship can be implemented with the help of two data
blocks where the first data block represents the master table while
the other represents a detailed table.

Q #8) Name the different triggers associated with Oracle

Forms having a master-detail relationship.
Answer: Enlisted triggers get created during the creation of
the master-detail block:
 ON-CHECK-DELETE-MASTER
 ON-CLEAR-DETAILS
 ON-POPULATE-DETAILS
Q #9) What are the various configuration files that are used
by Oracle Forms?
Answer: The configuration files include:
 default.env
 formsweb.cfg
 ftrace.cfg
 base.htm,basejini.htm & basejpi.htm
Using the above config files, a user can specify different parameters
for the forms as per the requirement.

Q #10) What do we mean by record group in Oracle Forms?

Answer: A record group is a framework of rows and columns within
the Oracle Forms similar to a table in the Oracle database.
Record groups can be:
 Query Record Group
 Non-Query Record Group
 Static Record Group
A query record group is associated with to SELECT statement and
can be created or updated during design or execution. While a non-
query record group is not associated with any query and hence it
can be created or updated during execution only.

The static record group is again not associated with any query and
can be created or updated during the design phase only.

Oracle Reports Interview Questions

Q #11) What is an Oracle Report? List its various types.
Answer: Oracle Report is a tool provided by Oracle Fusion
Middleware, which is used to generate reports based on the data
stored in the Oracle database. It consists of Oracle Reports
Developer, a report designing tool, and Oracle Application Server
Reports Services.
Various types of reports include:
 Tabular
 Master-Detail Reports
 Form Reports
 Form Letter Reports
 Mailing Labels Reports
 Matrix Reports
Q #12) What is an implicit anchor and how is it different
from an explicit anchor in a report builder?
Answer: An anchor is used to determine the position of an object in
horizontal and vertical directions. This position of an object will
always be relative to the position of the other objects, which can be
called parent objects for these child objects.
During runtime, an implicit anchor will be generated by the Oracle
Forms Builder for each layout object, which is not hold an explicit
anchor. The implicit anchor will be created during the runtime of a
report while explicit anchors are created by a user explicitly.

Q #13) Name different triggers supported by Oracle Reports

and their firing order.
Answer: Listed below are the triggers supported by Oracle
Reports:
 Before Parameter Form: Gets fired before the display
of runtime parameter form on the screen.
  After Parameter Form: Gets fired after the display of
runtime parameter form on the screen.
 Before Report: Gets fired before the execution of a
report but after the queries get parsed.
 Between Pages: Gets fired before formatting is done for
every page except the first page.
 After Report: Gets fired either at the exit of the
Previewer or once the report output is shared with the
destination.
Q #14) What is the difference between bind and lexical
parameters?
Answer: Bind parameters are the variables, which can replace a
single value in SQL/PLSQL such as a number, character, string, or
date.
While lexical parameters can replace clauses or multiple values
embedded in SELECT query possibly after SELECT, FROM, WHERE,
GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

Q #15) List the different types of columns in Oracle Reports.

Answer: There are three types of columns in Oracle reports. They
are:
 Formula Columns: Columns that can do user-defined
calculations on values within other columns and return
some value.
 Summary Columns: Columns, can do summary
computations like sum, average, etc. on values placed in
the other columns.
 Placeholder Columns: Column for which data type or
value can be set using PL/SQL.
Q #16) What is a User exit program in Oracle Reports?
Answer: User exit is a program that is written to perform some
relevant action. They can be called from report triggers and once
executed, it gives back the control to Report Builder.
A few of the user exits are listed as shown below:
 FND SRWINIT
 FND SRWEXIT
 FND FORMAT_CURRENCY
 FND FLEXSQL
 FND FLEXIDVAL
Q #17) How can we generate report output in Excel format?
Answer: To get report data in an Excel format, we can use:
 SPOOL Command

 Text_IO Package
 UTL Package
Q #18) What is the difference between flex mode and
confined mode?
Answer: Confined mode, if set restricts the child object within
enclosing parent objects. If not set on, the child objects can move
out of parent objects.
During flex mode, parent objects will adjust their border if the child
object expands or moves. If not set, parent borders stay fixed when
the child objects move.

Q #19) What is a matrix report and how many minimum

groups are required to prepare the same?
Answer: A matrix is a kind of report that looks like an information
grid with one row of labels and one column of columns. At least 4
groups are required in the data model to prepare a matrix report.
One should be a cross-product group, one cell group & at least two
groups should be within a cross-product group.
Q #20) Is it possible to have multiple layouts in a report?
Answer: Yes, it is possible to have multiple layouts. We can use an
additional layout option in the layout editor tool.

Q #1) Differentiate PL/SQL and SQL?

Answer: Difference between SQL and PL/SQL can be categorized as
follows:
SQL PL/SQL
SQL is a natural language which is very useful for PL/SQL is a procedural extension of Oracle - SQL.
interactive processing.
No procedural capabilities like condition testing, PL/SQL supports procedural capabilities as well as high
looping is offered by SQL. features such as conditional statements, looping statemen
All SQL statements are executed by the database server PL/SQL statements send the entire block of statements to
one at a time, thus it is a time-consuming process. server at the same time, thus network traffic is reduced c
No error handling procedures are there in SQL. PL/SQL supports customized error handling.
Q #2) Enlist the characteristics of PL/SQL?
Answer:
Characteristics of PL/SQL are as follows:
  PL/SQL allows access and sharing of the same
subprograms by multiple applications.
 It is known for the portability of code as code can be
executed on any operating system provided that Oracle is
loaded on it.
 With PL/SQL users can write their own customized error
handling routines.
 Improved transaction performance with integration to
Oracle data dictionary.
Q #3) What are the data types available in PL/SQL?
Answer: Data types define the ways to identify the type of data and
their associated operations.
There are 4 types of predefined data types explained as
follows:
 Scalar Data Types: A scalar data type is an atomic data
type that does not have any internal components.
 For example
 CHAR (fixed-length character value range
between 1 and 32,767 characters)
 VARCHAR2 (variable length character value range
between 1 and 32,767 characters)
 NUMBER ( fixed-decimal, floating-decimal or
integer values)
 BOOLEAN ( logical data type for TRUE FALSE or
NULL values)
 DATE (stores date and time information)
 LONG (character data of variable length)
 Composite Data Types: A composite data type is made
up of other data types and internal components that can
be easily used and manipulated. For example, RECORD,
TABLE, and VARRAY.
 Reference Data Types: A reference data type holds
values, called pointers that designate to other program
items or data items. For example, REF CURSOR.
 Large Object Data Types: A Large Object datatype
holds values, called locators, that defines the location of
large objects( such as video clips, graphic image, etc)
stored out of line.
 For example
 BFILE (Binary file)
 BLOB (Binary large object)
  CLOB ( Character large object)
 NCLOB( NCHAR type large object)
Recommended Reading =>> PL SQL Data Types
Q #4) Explain the purpose of %TYPE and %ROWTYPE data
types with the example?
Answer: PL/SQL uses the %TYPE declaration attribute for anchoring.
This attribute provides the datatype of a variable, constant or
column. %TYPE attribute is useful while declaring a variable that has
the same datatype as a table column.
For example, the variable m_empno has the same data type and
size as the column empno in table emp.
1m_empno emp.empno%TYPE;
%ROWTYPE attribute is used to declare a variable to be a record
having the same structure as a row in a table. The row is defined as
a record and its fields have the same names and data types as the
columns in the table or view.

For example:
1dept_rec dept%ROWTYPE;
This declares a record that can store an entire row for the DEPT
table.

Q #5) What do you understand by PL/SQL packages?

Answer: PL/SQL packages are schema objects that group functions,
stored procedures, cursors and variables at one place.
Packages have 2 mandatory parts:
 Package Specifications
 Package body
Q #6) What do you understand by PL/SQL cursors?
Answer: PL/SQL requires a special capability to retrieve and
process more than one row and that resource is known as Cursors. A
cursor is a pointer to the context area, which is an area of memory
containing SQL statements and information for processing the
statements.
PL/SQL Cursor is basically a mechanism under which multiple rows
of the data from the database are selected and then each row is
individually processed inside a program.

Q #7) Explain cursor types.

Answer: There are two types of cursors.
They are explained as follows:
a) Explicit Cursors: For queries that return more than one row, an
explicit cursor is declared and named by a programmer. In order to
use explicit cursor in PL/SQL, 4 steps are followed
 Declare the cursor
Syntax: CURSOR <cursor_name> is
SELECT statement;
Here, <cursor_name> is the name assigned to the cursor and
SELECT statement is the query that returns rows to the cursor active
set.

Open the cursor

Syntax: OPEN <cursor_nam>;
Where, <cursor_name> is the name of the previously defined
cursor.

 Fetch rows from the cursor

Syntax: FETCH <cursor_name> INTO <record_list>;
Here, <cursor_name> refers to the name of the previously defined
cursor from which rows are being fetched.

<record_list> represents the list of variables that will receive the

data being fetched.

 Closing the cursor

Syntax: CLOSE <cursor_name>;
Here, <cursor_name> is the name of the cursor being closed.

b) Implicit cursors: When any SQL statement is executed, PL/SQL

automatically creates a cursor without defining such cursors are
known as implicit cursors.
For the following statements, PL/SQL employs implicit cursors

INSERT

 UPDATE
 DELETE
 SELECT ( queries that return exactly one row)
Q #8) When do we use triggers?
Answer: The word ‘Trigger’ means to activate. In PL/SQL, the
trigger is a stored procedure that defines an action taken by the
database when the database-related event is performed.
Triggers are mainly required for the following purposes:
 To maintain complex integrity constraints
 Auditing table information by recording the changes
 Signaling other program actions when changes are made
to the table
 Enforcing complex business rules
 Preventing invalid transactions
Q #9) Explain the difference in the execution of triggers and
stored procedures?
Answer: A stored procedure is executed explicitly by issuing a
procedure call statement from another block via a procedure call
with arguments.
The trigger is executed implicitly whenever any triggering event like
the occurrence of DML statements happens.

Q #10) Explain the difference between Triggers and

Constraints?
Answer: Triggers are different from constraints in the
following ways:
Triggers Constraints
Only affect those rows added after the trigger is enabled. Affect all rows of the table including tha
when the constraint is enabled.
Triggers are used to implement complex business rules which cannot be Constraints maintain the integrity of the
implemented using integrity constraints.
Q #11) What is a PL/SQL block?
Answer: In PL/SQL, statements are grouped into units called Blocks.
PL/SQL blocks can include constants, variables, SQL statements,
loops, conditional statements, exception handling. Blocks can also
build a procedure, a function or a package.
Broadly, PL/SQL blocks are two types:
(i) Anonymous blocks: PL/SQL blocks without header are known
as anonymous blocks. These blocks do not form the body of a
procedure, function or triggers.
Example:
1DECLARE
2num NUMBER(2);
3sq NUMBER(3);
4BEGIN
5num:= &amp;Number1;
6sq := num*num;
7DBMS_OUTPUT.PUT_LINE(‘Square:’ ||sq);
8END;
(ii) Named blocks: PL/SQL blocks having header or labels are
known as Named blocks. Named blocks can either be subprograms
(procedures, functions, packages) or Triggers.
Recommended Reading =>> PL SQL Procedures And
Functions
Example:
1FUNCTION sqr (num IN NUMBER)
2RETURN NUMBER is sq NUMBER(2);
3BEGIN
4sq:= num*num;
5RETURN sq;
6END;
Q #12) Differentiate between syntax and runtime errors?
Answer:
Syntax errors are the one which can be easily identified by a
PL/SQL compiler. These errors can be a spelling mistake, etc.
Runtime errors are those errors in PL/SQL block for which an
exception handling section is to be included for handling the errors.
These errors can be SELECT INTO statement which does not return
any rows.
Q #13) What are COMMIT, ROLLBACK, and SAVEPOINT?
Answer: COMMIT, SAVEPOINT, and ROLLBACK are three transaction
specifications available in PL/SQL.
COMMIT statement: When DML operation is performed, it only
manipulates data in database buffer and the database remains
unaffected by these changes. To save/store these transaction
changes to the database, we need to COMMIT the transaction.
COMMIT transaction saves all outstanding changes since the last
COMMIT and the following process happens
 Affected rows locks are released
 Transaction marked as complete
 Transaction detail is stored in the data dictionary.
Syntax: COMMIT;
ROLLBACK statement: When we want to undo or erase all the
changes that have occurred in the current transaction so far, we
require to be rolled back of the transaction. In other words,
ROLLBACK erases all outstanding changes since the last COMMIT or
ROLLBACK.
Syntax to rollback a transaction completely:
ROLLBACK;
SAVEPOINT statement: The SAVEPOINT statement gives a name
and marks a point in the processing of the current transaction. The
changes and locks that have occurred before the SAVEPOINT in the
transaction are preserved while those that occur after the
SAVEPOINT are released.
Syntax:
SAVEPOINT <savepoint_name>;
Q #14) What is the mutating table and constraining table?
Answer: A table that is currently being modified by a DML
statement like defining triggers in a table is known as a Mutating
table.
A table that might need to be read from for a referential integrity
constraint is known as constraining table.
Q #15) What are actual parameters and formal parameters?
Answer: The variables or an expression referred to as parameters
that appear in the procedure call statement is known as Actual
parameters.
For example: raise_sal(emp_num, merit+ amount);
Here in the above example, emp_num and amount are the two
actual parameters.

The variables that are declared in the procedure header and are
referenced in the procedure body are called as Formal
parameters.
For example:
PROCEDURE raise_sal( emp_id INTEGER) IS
curr_sal REAL:
………..
BEGIN
SELECT sal INTO cur_sal FROM emp WHERE empno = emp_id;
…….
END raise_sal;
Here in the above example, emp_id acts as a formal parameter.

Q #16) What is the difference between ROLLBACK and

ROLLBACK TO statements?
Answer: The transaction is completely ended after ROLLBACK
statement i.e. ROLLBACK command completely undo a transaction
and release all locks.
On the other hand, a transaction is still active and running after
ROLLBACK TO command as it undo only a part of the transaction up
till the given SAVEPOINT.

Q #17) Write a PL/SQL script to display the following series

of numbers: 99,96,93……9,6,3?
Answer
1
2
3
4SET SERVER OUTPUT ON
DECLARE
5
BEGIN
6
FOR i IN REVERSE 1..99
7LOOP
8IF Mod(i,3) = 0 THEN
9DBMS_OUTPUT.PUT_LINE(i);
1END IF;
0END LOOP;
1END;
1/
Q #18) What are the 3 modes of parameter?
Answer: 3 modes of the parameter are IN, OUT, IN OUT.
These can be explained as follows:
 IN parameters: IN parameters allow you to pass values
to the procedure being called and can be initialized to
default values. IN parameters acts like a constant and
cannot be assigned any value.
 OUT parameters: OUT parameters return value to the
caller and they must be specified. OUT parameters act
like an uninitialized variable and cannot be used in an
expression.
 IN OUT parameters: IN OUT parameters passes initial
values to a procedure and return updated values to the
caller. IN OUT parameters act like an initialized variable
and should be assigned a value.
Q #19) Why is %ISOPEN always false for an implicit cursor?
Answer: An implicit cursor, SQL%ISOPEN attribute is always false
because the implicit cursor is opened for a DML statement and is
closed immediately after the execution of the DML statement.
Q #20) When a DML statement is executed, in which cursor
attributes, the outcome of the statement is saved?
Answer: The outcome of the statement is saved in 4 cursor
attributes.
These are:
 SQL%FOUND
 SQL%NOTFOUND
 SQL%ROWCOUNT
 SQL%ISOPEN
Q #21) What are the ways of commenting in a PL/SQL code?
Answer: Comments are the text which is included with the code to
enhance readability and for the understanding of the reader. These
codes are never executed.
There are two ways to comment in PL/SQL:
1) Single line comment: This comment starts with double –.
Example:
DECLARE
num NUMBER(2); — it is a local variable.
BEGIN
2) Multi-line comment: This comment starts with /* and ends with
*/.
Example:
BEGIN
num := &p_num; /* This is a host variable used in program
body */
……….
END
Q #22) What do you understand by Exception handling in
PL/SQL?
Answer: When an error occurs in PL/SQL, the exception is raised. In
other words, to handle undesired situations where PL/SQL scripts
terminated unexpectedly, an error handling code is included in the
program. In PL/SQL, all exception handling code is placed in an
EXCEPTION section.
There are 3 types of EXCEPTION:
 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.
Q #23) Enlist some predefined exceptions?
Answer:
Some of the predefined exceptions are:
 NO_DATA_FOUND: Single row SELECT statement where
no data is returned.
 TOO_MANY_ROWS: Single row SELECT statement where
more than one rows are returned.
 INVALID_CURSOR: Illegal cursor operation occurred.
 ZERO_DIVIDE: Attempted to divide by zero.
Q #24) What are PL/SQL cursor exceptions?
Answer:
The exceptions related to PL/SQL cursors are:
 CURSOR_ALREADY_OPEN
 INVALID_CURSOR
Q #25) Explain the difference between cursor declared in
procedures and cursors declared in the package
specification?
Answer: The cursor declared in the procedure is treated as local
and thus cannot be accessed by other procedures.
The cursor declared in the package specification is treated as global
and thus can be accessed by other procedures.

Q #26) What are INSTEAD OF triggers?

Answer: The INSTEAD OF triggers are the triggers written especially
for modifying views, which cannot be directly modified through SQL
DML statements.
Q #27) What are expressions?
Answer: Expressions are represented by a sequence of literals and
variables that are separated by operators. In PL/SQL, operations are
used to manipulate, compare and calculate some data. An
expression is a composition of ‘Operators’ and ‘Operands’.
 Operands: These are an argument to the operators.
Operands can be a variable, function call or constant.
 Operators: These specify the actions to be performed on
operators. Example: ‘+’, ‘*’, etc.
Q #28) List different type of expressions with the example.
Answer: Expressions can be as mentioned below:
 Numeric or Arithmetic expressions : Example: 20*
10+ 15
 Boolean expressions: Example: ‘spot’ LIKE ‘sp%t’
 String expressions: Example: LENGTH (‘NEW YORK’||
‘NY’)
  Date expressions: Example: SYSDATE>TO_DATE(’15-
NOV-16’, “dd-mm-yy”)
Q #29) Write a program that shows the usage of the WHILE
loop to calculate the average of user entered numbers and
entry of more numbers are stopped by entering number 0?
Answer
1
2
3
4
5
6
7
8
9DECLARE
1n NUMBER;
0avg NUMBER :=0 ;
1sum NUMBER :=0 ;
1count NUMBER :=0 ;
1BEGIN
n := &amp;enter_a_number;
2
WHILE(n&lt;&gt;0)
1
LOOP
3count := count+1;
1sum := sum+n;
4n := &amp;enter_a_number;
1END LOOP;
5avg := sum/count;
1DBMS_OUTPUT.PUT_LINE(‘the average is’||avg);
6END;
Q #30) What do you understand by PL/SQL Records?
Answer: A PL/SQL records can be referred as a collection of values
or say, a group of multiple pieces of information, each of which is of
simpler types and can be related to one another as fields.
There are three types of records supported in PL/SQL:
 Table based records
 Programmer based records
 Cursor based records

Q #1) What is SQL?

Answer: Structured Query Language SQL is a database tool that is
used to create and access the database to support software
applications.
Q #2) What are tables in SQL?
Answer: The table is a collection of records and information in a
single view.
Q #3) What are the different types of statements supported
by SQL?
Answer:

There are 3 types of SQL statements:

a) DDL (Data Definition Language): It is used to define the
database structure such as tables. It includes three statements such
as CREATE, ALTER, and DROP.
Also read =>> MySQL Create Table Tutorial
Some of the DDL Commands are listed below:
CREATE: It is used for creating the table.
1CREATE TABLE table_name
2column_name1 data_type(size),
3column_name2 data_type(size),
4column_name3 data_type(size),
ALTER: The ALTER table is used for modifying the existing table
object in the database.
1ALTER TABLE table_name
2 ADD column_name datatype
OR

1ALTER TABLE table_name

2DROP COLUMN column_name
b) DML (Data Manipulation Language): These statements are
used to manipulate the data in records. Commonly used DML
statements are INSERT, UPDATE, and DELETE.
The SELECT statement is used as a partial DML statement, used to
select all or relevant records in the table.
c) DCL (Data Control Language): These statements are used to
set privileges such as GRANT and REVOKE database access
permission to the specific user.
Q #4) How do we use the DISTINCT statement? What is its
use?
Answer: The DISTINCT statement is used with the SELECT
statement. If the record contains duplicate values then the DISTINCT
statement is used to select different values among duplicate
records.
Syntax:
1SELECT DISTINCT column_name(s)
2 FROM table_name;
Q #5) What are the different Clauses used in SQL?
Answer:

WHERE Clause: This clause is used to define the condition, and

extract and display only those records which fulfill the given
condition.
Syntax:
1SELECT column_name(s)
2 FROM table_name
3 WHERE condition;
GROUP BY Clause: It is used with the SELECT statement to group
the result of the executed query using the value specified in it. It
matches the value with the column name in tables and groups the
end result accordingly.
Further reading => MySQL Group By Tutorial
Syntax:
1SELECT column_name(s)
2 FROM table_name
3 GROUP BY column_name;
HAVING clause: This clause is used in association with the GROUP
BY clause. It is applied to each group of results or the entire result
as a single group. It is much similar to the WHERE clause but the
only difference is you cannot use it without the GROUP BY clause
Syntax:
1SELECT column_name(s)
2 FROM table_name
3 GROUP BY column_name
4 HAVING condition;
ORDER BY clause: This clause is used to define the order of the
query output either in ascending (ASC) or in descending (DESC).
Ascending (ASC) is set as the default one but descending (DESC) is
set explicitly.
Syntax:
1SELECT column_name(s)
2 FROM table_name
3 WHERE condition
4 ORDER BY column_name ASC|DESC;
USING clause: USING clause comes in use while working with SQL
JOIN. It is used to check equality based on columns when tables are
joined. It can be used instead of the ON clause in JOIN.
Syntax:
1SELECT column_name(s)
2 FROM table_name
3 JOIN table_name
4 USING (column_name);
Q #6) Why do we use SQL constraints? Which constraints we
can use while creating a database in SQL?
Answer: Constraints are used to set the rules for all records in the
table. If any constraints get violated then it can abort the action that
caused it.
Constraints are defined while creating the database itself with the
CREATE TABLE statement or even after the table is created once
with the ALTER TABLE statement.

There are 5 major constraints used in SQL, such as

 NOT NULL: That indicates that the column must have
some value and cannot be left NULL.
  UNIQUE: This constraint is used to ensure that each row
and column has a unique value and no value is being
repeated in any other row or column.
 PRIMARY KEY: This constraint is used in association with
NOT NULL and UNIQUE constraints such as on one or the
combination of more than one column to identify the
particular record with a unique identity.
 FOREIGN KEY: It is used to ensure the referential
integrity of data in the table. It matches the value in one
table with another using the PRIMARY KEY.
 CHECK: It ensures whether the value in columns fulfills
the specified condition.
Suggested Reading => Boost Your Career with these SQL
Certifications
Q #7) What are different JOINS used in SQL?
Answer:

4 major types of Joins are used while working on multiple tables in

SQL databases:

INNER JOIN: It is also known as SIMPLE JOIN which returns all rows
from BOTH tables when it has at least one matching column.
Syntax:
1SELECT column_name(s)
2 FROM table_name1&amp;nbsp;
3 INNER JOIN table_name2
4 ON column_name1=column_name2;
For Example,
In this example, we have a table Employee with the following data:
The second table’s name is Joining.

Enter the following SQL statement:

1SELECT Employee.Emp_id, Joining.Joining_Date
2 FROM Employee
3 INNER JOIN Joining
4 ON Employee.Emp_id = Joining.Emp_id
5 ORDER BY Employee.Emp_id;
There will be 4 records selected. Results are:

Employee and Orders tables have a matching customer_id value.

LEFT JOIN (LEFT OUTER JOIN): This join returns all rows from the
LEFT table and its matched rows from a RIGHT table.
Syntax:
1SELECT column_name(s)
2 FROM table_name1
3 LEFT JOIN table_name2
4 ON column_name1=column_name2;
For Example,
In this example, we have a table Employee with the following data:
The second table’s name is Joining.

Enter the following SQL statement:

1SELECT Employee.Emp_id, Joining.Joining_Date
2FROM Employee
3LEFT OUTER JOIN Joining
4ON Employee.Emp_id = Joining.Emp_id
5ORDER BY Employee.Emp_id;
There will be 4 records selected. You will see the following
results:

RIGHT JOIN (RIGHT OUTER JOIN): This joins returns all rows from
the RIGHT table and its matched rows from the LEFT table.
Syntax:
1SELECT column_name(s)
2FROM table_name1
3RIGHT JOIN table_name2
4ON column_name1=column_name2;
For Example,
In this example, we have a table Employee with the following data:
The second table’s name is Joining.

Enter the following SQL statement:

1SELECT Employee.Emp_id, Joining.Joining_Date FROM Employee
2RIGHT JOIN Joining
3ON Employee.Emp_id = Joining.Emp_id
4ORDER BY Employee.Emp_id;
Output:
Emp_id Joining_Date
E0012 2016/04/18
E0013 2016/04/19
E0014 2016/05/01
FULL JOIN (FULL OUTER JOIN): This joins returns all results when
there is a match either in the RIGHT table or in the LEFT table.
Syntax:
1SELECT column_name(s)
2 FROM table_name1
3 FULL OUTER JOIN table_name2
4 ON column_name1=column_name2;
For Example,
In this example, we have a table Employee with the following data:

The second table’s name is Joining.

Enter the following SQL statement:
1SELECT Employee.Emp_id, Joining.Joining_Date
2FROM Employee
3FULL OUTER JOIN Joining
4ON Employee.Emp_id = Joining.Emp_id
5ORDER BY Employee.Emp_id;
There will be 8 records selected. These are the results that you
should see.

Also, Read => MySQL Join Tutorial

Q #8) What are transactions and their controls?
Answer: A transaction can be defined as the sequence task that is
performed on databases in a logical manner to gain certain results.
Operations like Creating, updating, and deleting records performed
in the database come from transactions.
In simple words, we can say that a transaction means a group of
SQL queries executed on database records.

There are 4 transaction controls such as

  COMMIT: It is used to save all changes made through the
transaction.
 ROLLBACK: It is used to roll back the transaction. All
changes made by the transaction are reverted back and
the database remains as before.
 SET TRANSACTION: Set the name of the transaction.
 SAVEPOINT: It is used to set the point where the
transaction is to be rolled back.
Q #9) What are the properties of the transaction?
Answer: Properties of the transaction are known as ACID
properties. These are:
 Atomicity: Ensures the completeness of all transactions
performed. Checks whether every transaction is
completed successfully or not. If not, then the transaction
is aborted at the failure point and the previous
transaction is rolled back to its initial state as changes are
undone.
 Consistency: Ensures that all changes made through
successful transactions are reflected properly on the
database.
 Isolation: Ensures that all transactions are performed
independently and changes made by one transaction are
not reflected on others.
 Durability: Ensures that the changes made in the
database with committed transactions persist as it is even
after a system failure.
Q #10) How many Aggregate functions are available in SQL?
Answer: SQL Aggregate functions determine and calculate values
from multiple columns in a table and return a single value.
There are 7 aggregate functions in SQL:
 AVG(): Returns the average value from specified
columns.
 COUNT(): Returns the number of table rows.
 MAX(): Returns the largest value among the records.
 MIN(): Returns the smallest value among the records.
 SUM(): Returns the sum of specified column values.
 FIRST(): Returns the first value.
 LAST(): Returns last value.
Q #11) What are Scalar functions in SQL?
Answer: Scalar functions are used to return a single value based on
the input values.
Scalar Functions are as follows:
 UCASE(): Converts the specified field in the upper case.
 LCASE(): Converts the specified field in lowercase.
 MID(): Extracts and returns characters from the text
field.
 FORMAT(): Specifies the display format.
 LEN(): Specifies the length of the text field.
 ROUND(): Rounds up the decimal field value to a
number.
Q #12) What are triggers?
Answer: Triggers in SQL is kind of stored procedures used to create
a response to a specific action performed on the table such as
INSERT, UPDATE or DELETE. You can invoke triggers explicitly on the
table in the database.
Action and Event are two main components of SQL triggers. When
certain actions are performed, the event occurs in response to that
action.

Syntax:
1CREATE TRIGGER name {BEFORE|AFTER} (event [OR..]}
2ON table_name [FOR [EACH] {ROW|STATEMENT}]
3EXECUTE PROCEDURE functionname {arguments}
Q #13) What is View in SQL?
Answer: A View can be defined as a virtual table that contains rows
and columns with fields from one or more tables.
Syntax:
1CREATE VIEW view_name AS
2SELECT column_name(s)
3FROM table_name
4WHERE condition
Q #14) How we can update the view?
Answer: SQL CREATE and REPLACE can be used for updating the
view.
Execute the below query to update the created view.

Syntax:
1CREATE OR REPLACE VIEW view_name AS
2 SELECT column_name(s)
3 FROM table_name
4 WHERE condition
Q #15) Explain the working of SQL Privileges.
Answer: SQL GRANT and REVOKE commands are used to
implement privileges in SQL multiple user environments. The
administrator of the database can grant or revoke privileges to or
from users of database objects by using commands like SELECT,
INSERT, UPDATE, DELETE, ALL, etc.
GRANT Command: This command is used to provide database
access to users other than the administrator.
Syntax:
1GRANT privilege_name
2 ON object_name
3 TO {user_name|PUBLIC|role_name}
4 [WITH GRANT OPTION];
In the above syntax, the GRANT option indicates that the user can
grant access to another user too.

REVOKE command: This command is used to provide database

deny or remove access to database objects.
Syntax:
1REVOKE privilege_name
2 ON object_name
3 FROM {user_name|PUBLIC|role_name};
Q #16) How many types of Privileges are available in SQL?
Answer: There are two types of privileges used in SQL, such
as
 System privilege: System privilege deals with the
object of a particular type and provides users the right to
perform one or more actions on it. These actions include
performing administrative tasks, ALTER ANY INDEX,
ALTER ANY CACHE GROUP creates/ALTER/DELETE TABLE,
CREATE/ALTER/DELETE VIEW, etc.
 Object privilege: This allows us to perform actions on
an object or object of another user(s) viz. table, view,
indexes, etc. Some of the object privileges are EXECUTE,
INSERT, UPDATE, DELETE, SELECT, FLUSH, LOAD, INDEX,
REFERENCES, etc.
Q #17) What is SQL Injection?
Answer: SQL Injection is a type of database attack technique where
malicious SQL statements are inserted into an entry field of the
database in a way that once it is executed, the database is exposed
to an attacker for the attack. This technique is usually used for
attacking data-driven applications to have access to sensitive data
and perform administrative tasks on databases.
For Example,
1SELECT column_name(s) FROM table_name WHERE condition;
Q #18) What is SQL Sandbox in SQL Server?
Answer: SQL Sandbox is a safe place in the SQL server
environment where untrusted scripts are executed. There are 3
types of SQL sandbox:
 Safe Access Sandbox: Here a user can perform SQL
operations such as creating stored procedures, triggers,
etc. but cannot have access to the memory as well as
cannot create files.
 External Access Sandbox: Users can access files
without having the right to manipulate the memory
allocation.
 Unsafe Access Sandbox: This contains untrusted codes
where a user can have access to memory.
Q #19) What is the difference between SQL and PL/SQL?
Answer: SQL is a Structured Query Language to create and access
databases whereas PL/SQL comes with procedural concepts of
programming languages.
Q #20) What is the difference between SQL and MySQL?
Answer: SQL is a Structured Query Language that is used for
manipulating and accessing the relational database. On the other
hand, MySQL itself is a relational database that uses SQL as the
standard database language.
Q #21) What is the use of the NVL function?
Answer: The NVL function is used to convert the null value to its
actual value.
Q #22) What is the Cartesian product of the table?
Answer: The output of Cross Join is called a Cartesian product. It
returns rows combining each row from the first table with each row
of the second table. For Example, if we join two tables having 15
and 20 columns the Cartesian product of two tables will be
15×20=300 rows.
Q #23) What do you mean by Subquery?
Answer: Query within another query is called as Subquery. A
subquery is called an inner query which returns output that is to be
used by another query.
Q #24) How many row comparison operators are used while
working with a subquery?
Answer: There are 3-row comparison operators that are used in
subqueries such as IN, ANY, and ALL.
Q #25) What is the difference between clustered and non-
clustered indexes?
Answer: The differences between the two are as follows:
 One table can have only one clustered index but multiple
non-clustered indexes.
 Clustered indexes can be read rapidly rather than non-
clustered indexes.
 Clustered indexes store data physically in the table or
view whereas, non-clustered indexes do not store data in
the table as it has a separate structure from the data row.
Q #26) What is the difference between DELETE and
TRUNCATE?
Answer: The differences are:
 The basic difference in both is DELETE command is the
DML command and the TRUNCATE command is DDL.
 DELETE command is used to delete a specific row from
the table whereas the TRUNCATE command is used to
remove all rows from the table.
 We can use the DELETE command with the WHERE clause
but cannot use the TRUNCATE command with it.
Q #27) What is the difference between DROP and
TRUNCATE?
Answer: TRUNCATE removes all rows from the table which cannot
be retrieved back, DROP removes the entire table from the database
and it also cannot be retrieved back.
Q #28) How to write a query to show the details of a student
from the Students table whose
name start with K?
Answer: Query:
1SELECT * FROM Student WHERE Student_Name like ‘K%’;
Here ‘like’ operator is used to perform pattern matching.

Q #29) What is the difference between Nested Subquery and

Correlated Subquery?
Answer: Subquery within another subquery is called Nested
Subquery. If the output of a subquery depends on column values of
the parent query table then the query is called Correlated Subquery.
1SELECT adminid(SELEC Firstname+' '+Lastname&amp;nbsp;&amp;nbsp;FROM Employ
2 empid=emp. adminid)AS EmpAdminId FROM Employee;
The result of the query is the details of an employee from the
Employee table.

Q #30) What is Normalization? How many Normalization

forms are there?
Answer: Normalization is used to organize the data in such a
manner that data redundancy will never occur in the database and
avoid insert, update and delete anomalies.
There are 5 forms of Normalization:
 First Normal Form (1NF): It removes all duplicate
columns from the table. It creates a table for related data
and identifies unique column values.
 First Normal Form (2NF): Follows 1NF and creates and
places data subsets in an individual table and defines the
relationship between tables using the primary key.
 Third Normal Form (3NF): Follows 2NF and removes
those columns which are not related through the primary
key.
 Fourth Normal Form (4NF): Follows 3NF and does not
define multi-valued dependencies. 4NF is also known as
BCNF.
Q #31) What is a Relationship? How many types of
Relationships are there?
Answer: The relationship can be defined as the connection between
more than one table in the database.
There are 4 types of relationships:
 One-to-One Relationship
 Many to One Relationship
 Many to Many Relationship
 One to Many Relationship
Q #32) What do you mean by Stored Procedures? How do we
use it?
Answer: A stored procedure is a collection of SQL statements that
can be used as a function to access the database. We can create
these stored procedures earlier before using it and can execute
them wherever required by applying some conditional logic to them.
Stored procedures are also used to reduce network traffic and
improve performance.
Syntax:
1CREATE Procedure Procedure_Name
2 (
3 //Parameters
4 )
5 AS
6 BEGIN
7 SQL statements in stored procedures to update/retrieve records
8 END
Q #33) State some properties of Relational databases.
Answer: Properties are as follows:
 In relational databases, each column should have a
unique name.
 The sequence of rows and columns in relational
databases is insignificant.
 All values are atomic and each row is unique.
Q #34) What are Nested Triggers?
Answer: Triggers may implement data modification logic by using
INSERT, UPDATE, and DELETE statements. These triggers that
contain data modification logic and find other triggers for data
modification are called Nested Triggers.
Q #35) What is a Cursor?
Answer: A cursor is a database object which is used to manipulate
data in a row-to-row manner.
The cursor follows steps given below:
 Declare Cursor
 Open Cursor
 Retrieve row from the Cursor
 Process the row
 Close Cursor
 Deallocate Cursor
Q #36) What is Collation?
Answer: Collation is a set of rules that check how the data is sorted
by comparing it. Such as character data is stored using the correct
character sequence along with case sensitivity, type, and accent.
Q #37) What do we need to check in Database Testing?
Answer: In Database testing, the following thing is required
to be tested:
 Database connectivity
 Constraint check
 Required application field and its size
 Data Retrieval and processing with DML operations
 Stored Procedures
 Functional flow
Q #38) What is Database White Box Testing?
Answer: Database White Box testing involves:
 Database Consistency and ACID properties
 Database triggers and logical views
 Decision Coverage, Condition Coverage, and Statement
Coverage
 Database Tables, Data Model, and Database Schema
 Referential integrity rules
Q #39) What is Database Black Box Testing?
Answer: Database Black Box testing involves:
 Data Mapping
 Data stored and retrieved
 Use of Black Box testing techniques such as Equivalence
Partitioning and Boundary Value Analysis (BVA)
Q #40) What are Indexes in SQL?
Answer: The index can be defined as the way to retrieve data more
quickly. We can define indexes using CREATE statements.
Syntax:
1CREATE INDEX index_name
2 ON table_name (column_name)
Further, we can also create a Unique Index using the following
syntax:

1CREATE UNIQUE INDEX index_name

2 ON table_name (column_name)

UPDATE: We have added a few more short questions for practice.

Q #41) What does SQL stand for?
Answer: SQL stands for Structured Query Language.
Q #42) How to select all records from the table?
Answer: To select all the records from the table we need to use the
following syntax:
1Select * from table_name;
Q #43) Define join and name different types of joins.
Answer: Join keyword is used to fetch data from two or more
related tables. It returns rows where there is at least one match in
both the tables included in the join. Read more here.
Type of joins are:
1. Right join
2. Outer join
3. Full join
4. Cross join
5. Self join.
Q #44) What is the syntax to add a record to a table?
Answer: To add a record in a table INSERT syntax is used.
For Example,
1INSERT into table_name VALUES (value1, value2..);
Q #45) How do you add a column to a table?
Answer: To add another column to the table, use the following
command:
1ALTER TABLE table_name ADD (column_name);
Recommended reading =>> How to add a column to a
table in MySQL
Q #46) Define the SQL DELETE statement.
Answer: DELETE is used to delete a row or rows from a table based
on the specified condition.
The basic syntax is as follows:
1DELETE FROM table_name
2WHERE &amp;lt;Condition&amp;gt;
Q #47) Define COMMIT?
Answer: COMMIT saves all changes made by DML statements.
Q #48) What is the Primary key?
Answer: A Primary key is a column whose values uniquely identify
every row in a table. Primary key values can never be reused.
Q #49) What are Foreign keys?
Answer: When a table’s primary key field is added to related tables
in order to create the common field which relates the two tables, it
is called a foreign key in other tables. Foreign key constraints
enforce referential integrity.
Q #50) What is CHECK Constraint?
Answer: A CHECK constraint is used to limit the values or type of
data that can be stored in a column. They are used to enforce
domain integrity.
Q #51) Is it possible for a table to have more than one
foreign key?
Answer: Yes, a table can have many foreign keys but only one
primary key.
Q #52) What are the possible values for the BOOLEAN data
field?
Answer: For a BOOLEAN data field, two values are possible: -1(true)
and 0(false).
Q #53) What is a stored procedure?
Answer: A stored procedure is a set of SQL queries that can take
input and send back output.
Q #54) What is identity in SQL?
Answer: An identity column where SQL automatically generates
numeric values. We can define a start and increment value of the
identity column.
Q #55) What is Normalization?
Answer: The process of table design to minimize data redundancy
is called normalization. We need to divide a database into two or
more tables and define the relationship between them.
Q #56) What is a Trigger?
Answer: The Trigger allows us to execute a batch of SQL code when
a tabled event occurs (INSERT, UPDATE or DELETE commands are
executed against a specific table).
Q #57) How to select random rows from a table?
Answer: Using a SAMPLE clause we can select random rows.
For Example,
1SELECT * FROM table_name SAMPLE(10);
Q #58) Which TCP/IP port does SQL Server run?
Answer: By default SQL Server runs on port 1433.
Q #59) Write a SQL SELECT query that only returns each
name only once from a table.
Answer: To get the result as each name only once, we need to use
the DISTINCT keyword.
1SELECT DISTINCT name FROM table_name;
Q #60) Explain DML and DDL.
Answer: DML stands for Data Manipulation Language. INSERT,
UPDATE and DELETE are DML statements.
DDL stands for Data Definition Language. CREATE, ALTER, DROP,
RENAME are DDL statements.

Q #61) Can we rename a column in the output of the SQL

query?
Answer: Yes, using the following syntax we can do this.
1SELECT column_name AS new_name FROM table_name;
Q #62) Give the order of SQL SELECT.
Answer: The order of SQL SELECT clauses is: SELECT, FROM,
WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM
clauses are mandatory.
Q #63) Suppose a Student column has two columns, Name
and Marks. How to get names and marks of the top three
students.
Answer: SELECT Name, Marks FROM Student s1 where 3 <=
(SELECT COUNT(*) FROM Students s2 WHERE s1.marks = s2.marks)
Q #64) What are SQL comments?
Answer: SQL comments can be inserted by adding two consecutive
hyphens (–).
Q #65) Difference between TRUNCATE, DELETE, and DROP
commands?
Answer:
 DELETE removes some or all rows from a table based on
the condition. It can be rolled back.
 TRUNCATE removes ALL rows from a table by de-
allocating the memory pages. The operation cannot be
rolled back
 DROP command removes a table from the database
completely.
Q #66) What are the properties of a transaction?
Answer: Generally, these properties are referred to as ACID
properties. They are:
1. Atomicity
2. Consistency
3. Isolation
4. Durability.
Q #67) What do you mean by ROWID?
Answer: It’s an 18-character long pseudo-column attached to each
row of a table.
Q #68) Define UNION, MINUS, UNION ALL, INTERSECT?
Answer:
 MINUS – returns all distinct rows selected by the first
query but not by the second.
 UNION – returns all distinct rows selected by either query
 UNION ALL – returns all rows selected by either query,
including all duplicates.
 INTERSECT – returns all distinct rows selected by both
queries.
Q #69) What is a transaction?
Answer: A transaction is a sequence of code that runs against a
database. It takes the database from one consistent state to
another.
Q #70) What is the difference between UNIQUE and PRIMARY
KEY constraints?
Answer: The differences are as follows:
 A table can have only one PRIMARY KEY whereas there
can be any number of UNIQUE keys.
 The primary key cannot contain Null values whereas the
Unique key can contain Null values.
Q #71) What is a composite primary key?
Answer: The primary key created on more than one column is
called the composite primary key.
Q #72) What is an Index?
Answer: An Index is a special structure associated with a table to
speed up the performance of queries. The index can be created on
one or more columns of a table.
Q #73) What is the Subquery?
Answer: A Subquery is a subset of select statements whose return
values are used in filtering conditions of the main query.
Q #74) What do you mean by query optimization?
Answer: Query optimization is a process in which a database
system compares different query strategies and selects the query
with the least cost.
Q #75) What is Collation?
Answer: Set of rules that define how data is stored, how case
sensitivity and Kana character can be treated etc.
Q #76) What is Referential Integrity?
Answer: Set of rules that restrict the values of one or more columns
of the tables based on the values of the primary key or unique key
of the referenced table.
Q #77) What is the Case function?
Answer: Case facilitates the if-then-else type of logic in SQL. It
evaluates a list of conditions and returns one of the multiple
possible result expressions.
Q #78) Define a temp table.
Answer: A temp table is a temporary storage structure to store the
data temporarily.
Q #79) How can we avoid duplicating records in a query?
Answer: By using the DISTINCT keyword, duplication of records in a
query can be avoided.
Q #80) Explain the difference between Rename and Alias.
Answer: Rename is a permanent name given to a table or column
whereas Alias is a temporary name given to a table or column.
Q #81) What is a View?
Answer: A view is a virtual table that contains data from one or
more tables. Views restrict data access to the table by selecting only
required values and making complex queries easy.
Q #82) What are the advantages of Views?
Answer: Advantages of Views are:
 Views restrict access to the data because the view can
display selective columns from the table.
 Views can be used to make simple queries to retrieve the
results of complicated queries. For Example, views can
be used to query information from multiple tables without
the user knowing.
Q #83) List the various privileges that a user can grant to
another user.
Answer: SELECT, CONNECT, RESOURCES.
Q #84) What is schema?
Answer: A schema is a collection of database objects of a User.
Q #85) What is a Table?
Answer: A table is the basic unit of data storage in the database
management system. Table data is stored in rows and columns.
Q #86) Does View contain Data?
Answer: No, Views are virtual structures.
Q #87) Can a View be based on another View?
Answer: Yes, A View is based on another View.
Q #88) What is the difference between the HAVING clause
and WHERE clause?
Answer: Both specify a search condition but the Having clause is
used only with the SELECT statement and typically used with the
GROUP BY clause.
If the GROUP BY clause is not used then Having behaved like the
WHERE clause only.
Q #89) What is the difference between Local and Global
temporary tables?
Answer: If defined inside a compound statement a local temporary
table exists only for the duration of that statement but a global
temporary table exists permanently in the DB but its rows disappear
when the connection is closed.
Q #90) What is CTE?
Answer: A CTE or common table expression is an expression that
contains a temporary result set which is defined in a SQL statement.

1) What is the difference between PL SQL and SQL?

Comparison SQL PL/SQL

Execution Single command at a time Block of code

Application created by data

Application Source of data to be displayed
acquired by SQL

DDL and DML based queries and Includes procedures, function

Structures include
commands etc

Recommended Performing CRUD operations on Creating applications to disp

while data data obtained using SQL

Compatibility with
SQL can be embedded into PL/SQL PL/SQL cant be embedded in
each other

2) What is SQL and also describe types of SQL statements?

SQL stands for Structured Query Language. SQL is a language used to

communicate with the server to access, manipulate, and control data.

There are 5 different types of SQL statements.

1. Data Retrieval: SELECT

2. Data Manipulation Language (DML): INSERT, UPDATE, DELETE, MERGE

3. Data Definition Language

(DDL): CREATE, ALTER, DROP, RENAME, TRUNCATE.

4. Transaction Control Statements: COMMIT, ROLLBACK, SAVEPOINT

5. Data Manipulation Language (DCL): GRANT, REVOKE

3) What is an alias in SQL statements?

Alias is a user-defined alternative name given to the column or table. By default

column, alias headings appear in upper case. Enclose the alias in double
quotation marks (“ “) to make it case-sensitive. “ AS” Keyword before the alias
name makes the SELECT clause easier to read.

For example Select emp_name AS name from employee; (Here AS is a

keyword and “name” is an alias).

If you want to enrich your career and become a Professional in Oracle PL SQL, then enroll in "Oracl
SQL Training" - This course will help you to achieve excellence in this domain.

4) What is a Literal? Give an example of where it can be used?

A Literal is a string that can contain a character, a number, or a date that is

included in the SELECT list and that is not a column name or a column alias.
Date and character literals must be enclosed within single quotation marks (‘ ‘),
number literals need not.

For exp: Select last_name||’ is a’||job_id As “emp details” from the employee;
(Here “is a” is a literal).

5) What is the difference between SQL and iSQL*Plus?

SQL iSQL*Plus
Is a Language Is an Environment

Character and date column headings are left-

justified and number column headings are Default heading justification is in the Centr
right-justified.

Cannot be Abbreviated (short forms) Can be Abbreviated

Has a dash (-) as a continuation character i

Does not have a continuation character
command is longer than one line

Use Functions to perform some formatting Use commands to format data

6) Define the order of Precedence used in executing SQL statements?

Order Evaluated Operator

1 Arithmetic operators (*, /, +, -)

2 Concatenation operators (||)

3 Comparison conditions

4 Is[NOT] NULL, LIKE, [NOT] IN

5 [NOT] BETWEEN

6 NOT Logical condition

7 AND logical condition

8 OR logical condition

7) What are SQL functions? Describe in brief different types of SQL functions?

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

1) Single-Row functions: These functions operate on a single row to give

one result per row.

Types of Single-Row functions:

1. Character
2. Number
3. Date
4. Conversion
5. General

2) Multiple-Row functions: These functions operate on groups of rows to

give one result per group of rows.

Types of Multiple-Row functions:

1. AVG
2. COUNT
3. MAX
4. MIN
5. SUM
6. STDDEV
7. VARIANCE
8) Explain the character, number, and date function in detail?

Character functions: accept character input and return both character and
number values. Types of character function are:

 Case-Manipulation Functions: LOWER, UPPER, INITCAP

 Character-Manipulation Functions: CONCAT, SUBSTR, LENGTH,
INSTR, LPAD/RPAD, TRIM, REPLACE

Number Functions: accept Numeric input and return numeric values. Number
Functions are: ROUND, TRUNC, and MOD

Date Functions: operates on values of the Date data type. (All date functions
return a value of DATE data type except the MONTHS_BETWEEN Function, which
returns a number. Date Functions are MONTHS_BETWEEN, ADD_MONTHS,
NEXT_DAY, LAST_DAY, ROUND, TRUNC.

9) What is a Dual Table?

The dual table is owned by the user SYS and can be accessed by all users. It
contains one columnDummy and one row with the value X. The Dual Table is
useful when you want to return a value only once. The value can be a constant,
pseudocolumn, or expression that is not derived from a table with user data.

10) Explain the Conversion function in detail?

Conversion Functions convert a value from one data type to another. Conversion
functions are of two types:

Implicit Data type conversion:

1. VARCHAR2 or CHAR To NUMBER, DATE

2. NUMBER To VARCHAR2
3. DATE To VARCHAR2

Explicit data type conversion:

1. TO_NUMBER
2. TO_CHAR
3. TO_DATE

TO_NUMBER function is used to convert a Character string to Number format.

TO_NUMBER function use fx modifier. Format: TO_NUMBER ( char[, ‘
format_model’] ). fx modifier specifies the exact matching for the character
argument and number format model of the TO_NUMBER function.
TO_CHAR function is used to convert NUMBER or DATE data type to
CHARACTER format. TO_CHAR Function uses fm element to remove padded
blanks or suppress leading zeros. TO_CHAR Function formats:TO_CHAR (date,
‘format_model’).The format model must be enclosed in single quotation marks
and is case sensitive.

For exp: Select TO_CHAR (hire date, ‘MM/YY’) from the employee.

TO_DATE function is used to convert a Character string to date format.

TO_DATE function use fx modifier which specifies the exact matching for the
character argument and date format model of TO_DATE function. TO_DATE
function format: TO_DATE ( char[, ‘ format_model’] ).

For exp: Select TO_DATE (‘May 24, 2007’,’ mon dd RR’) from dual;

Read these latest SQL Interview Questions and Answers that help you grab hig
paying jobs

11) Describe different types of General Function used in SQL?

General functions are of the following types:

NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If exp1 is null
then the NVL function returns the 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
the 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.

12) What is the difference between COUNT (*), COUNT (expression), COUNT
(distinct expression)? (Where expression is any column name of Table)?

 COUNT (*): Returns a number of rows in a table including duplicates

rows and rows containing null values in any of the columns.
 COUNT (EXP): Returns the number of non-null values in the column
identified by expression.
  COUNT (DISTINCT EXP): Returns the number of unique, non-null
values in the column identified by expression.

13) 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:

1. Enclose sub queries within parenthesis

2. Place subqueries on the right side of the comparison condition.
3. Use Single-row operators with single-row subqueries and 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: Queries that return more than one row from
the inner Select statement. There are also multiple-column subqueries
that return more than one column from the inner select statement.
Operators include: IN, ANY, ALL.

14) What is the difference between ANY and ALL operators?

ANY Operator compares value to each value returned by the subquery. ANY
operator has a synonym SOME operator.

 > ANY means more than the minimum.

 < ANY means less than the maximum
 = ANY is equivalent to IN operator.

ALL Operator compares value to every value returned by the subquery.

 > ALL means more than the maximum

 < ALL means less than the minimum
 <> ALL is equivalent to NOT IN condition.

15) What is a MERGE statement?

The MERGE statement inserts or updates rows in one table, using data from
another table. It is useful in data warehousing applications.

16) What is the difference between the “VERIFY” and the “FEEDBACK” command?
  VERIFY Command: Use VERIFY Command to confirm the changes in the
SQL statement (Old and New values). Defined with SET VERIFY ON/OFF.
 Feedback Command: Displays the number of records returned by a
query.

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

Use “&&” if you want to reuse the variable value without prompting the user
each time.

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

&column_name;

18) What are Joins and how many types of Joins are there?

Joins are used to retrieve data from more than one table.

There are 5 different types of joins.

Oracle 8i and Prior SQL: 1999 (9i)

Equi Join Natural/Inner Join

Outer Join Left Outer/ Right Outer/ Full Outer Join

Self Join Join ON

Non-Equi Join Join USING

Cartesian Product Cross Join

19) Explain all Joins used in Oracle 8i?

Cartesian Join: When a Join condition is invalid or omitted completely, the

result is a Cartesian product, in which all combinations of rows are displayed. To
avoid a Cartesian product, always include a valid join condition in a “where”
clause. To Join ‘N’ tables together, you need a minimum of N-1 Join conditions.

For exp: to join four tables, a minimum of three joins is required. This rule may
not apply if the table has a concatenated primary key, in which case more than
one column is required to uniquely identify each row.

Equi Join: This type of Join involves primary and foreign key relations. Equi Join
is also called Simple or Inner Joins.

Non-Equi Joins A Non-Equi Join condition containing something other than an

equality operator. The relationship is obtained using an operator other than an
equal operator (=). The conditions such as <= and >= can be used, but
BETWEEN is the simplest to represent Non-Equi Joins.

Outer Joins: Outer Join is used to fetch rows that do not meet the join
condition. The outer join operator is the plus sign (+), and it is placed on the
side of the join that is deficient in information. The Outer Join operator can
appear on only one side of the expression, the side that has information
missing. It returns those rows from one table that has no direct match in the
other table. A condition involving an Outer Join cannot use IN and OR operators.

Self Join: Joining a table to itself.

20) Explain all Joins used in Oracle 9i and later release?

Cross Join:

The Cross Join clause produces the cross-product of two tables. This is the same
as a Cartesian product between the two tables.

Natural Joins:

This is used to join two tables automatically based on the columns which have
matching data types and names, using the keyword NATURAL JOIN. It is equal to
the Equi-Join. If the columns have the same names but different data types, then
the Natural Join syntax causes an error.

Join with the USING clause:

If several columns have the same names but the data types do not match, then
the NATURAL JOIN clause can be modified with the USING clause to specify the
columns that should be used for an equi Join. Use the USING clause to match
only one column when more than one column matches. Do not use a table name
or alias in the referenced columns. The NATURAL JOIN clause and USING clause
are mutually exclusive.

For ex: Select a.city, b.dept_name from loc a Join dept b USING (loc_id) where
loc_id=10;

Joins with the ON clause:

Use the ON clause to specify a join condition. The ON clause makes the code
easy to understand. ON clause is equals to Self Joins. The ON clause can also be
used to join columns that have different names.

Left/ Right/ Full Outer Joins:

Left Outer Join displays all rows from the table that is Left to the LEFT OUTER
JOIN clause, right outer join displays all rows from the table that is right to the
RIGHT OUTER JOIN clause, and full outer join displays all rows from both the
tables either left or right to the FULL OUTER JOIN clause.

Read these latest SQL Interview Questions and Answers for Experienced that h
you grab high-paying jobs

PlSQL Developer Interview Questions

21) What is the difference between Entity, Attribute, and Tuple?

Entity: A significant thing about which some information is required. For

exp: EMPLOYEE (table). Attribute: Something that describes the entity. For
exp: empno, emp name, emp address (columns). Tuple: A row in a relation is
called Tuple.

22) What is a Transaction? Describe common errors that can occur while executing
any Transaction?

Transaction consists of a collection of DML statements that forms a logical unit

of work.

The common errors that can occur while executing any transaction are:

The violation of constraints.

1. Data type mismatch.

2. Value too wide to fit in the column.
3. The system crashes or the Server gets down.
4. The session Killed.
5. Locking takes place. Etc.

23) What is locking in SQL? Describe its types?

Locking prevents destructive interaction between concurrent transactions. Locks

held until Commit or Rollback. Types of locking are:

 Implicit Locking: This occurs for all SQL statements except SELECT.
 Explicit Locking: This can be done by the user manually.

Further, there are two locking methods:

1. Exclusive: Locks out other users

2. Share: Allows other users to access
24) What is the difference between Commit, Rollback, and Savepoint?

 COMMIT: Ends the current transaction by making all pending data

changes permanent.
 ROLLBACK: Ends the current transaction by discarding all pending data
changes.
 SAVEPOINT: Divides a transaction into smaller parts. You can roll back
the transaction to a particular named savepoint.

25) What are the advantages of COMMIT and ROLLBACK statements?

Advantages of COMMIT and ROLLBACK statements are:

 Ensure data consistency

 Can preview data changes before making changes permanent.
 Group logically related operations.

26) Describe naming rules for creating a Table?

Naming rules to be considered for creating a table is:

1. The table name must begin with a letter,

2. The table name can be 1-30 characters long,
3. Table name can contain only A-Z, a-z, 0-9,_, $, #.
4. The table name cannot duplicate the name of another object owned by
the same user.
5. The table name cannot be an oracle server reserved word.

27) What is a DEFAULT option in a table?

A column can be given a default value by using the DEFAULT option. This option
prevents null values from entering the column if a row is inserted without a
value for that column. The DEFAULT value can be a literal, an expression, or a
SQL function such as SYSDATE and USER but the value cannot be the name of
another column or a pseudo column such as NEXTVAL or CURRVAL.

28) What is the difference between USER TABLES and DATA DICTIONARY?

 USER TABLES: This is a collection of tables created and maintained by

the user. Contain USER information.
 DATA DICTIONARY: This is a collection of tables created and
maintained by the Oracle Server. It contains database information. All
data dictionary tables are owned by the SYS user.

29) Describe a few Data Types used in SQL?

Data Types is a specific storage format used to store column values. Few data
types used in SQL are:

1. VARCHAR2(size): Minimum size is ‘1’ and Maximum size is ‘4000’

2. CHAR(size): Minimum size is ‘1’and Maximum size is ‘2000’
3. NUMBER(P,S): " Precision" can range from 1 to 38 and the “Scale” can
range from -84 to 127.
4. DATE
5. LONG: 2GB
6. CLOB: 4GB
7. RAW (size): Maximum size is 2000
8. LONG RAW: 2GB
9. BLOB: 4GB
10. BFILE: 4GB
11. ROWID: A 64 base number system representing the unique
address of a row in the table.

30) In what scenario you can modify a column in a table?

During modifying a column:

1. You can increase the width or precision of a numeric column.

2. You can increase the width of numeric or character columns.
3. You can decrease the width of a column only if the column contains null
values or if the table has no rows.
4. You can change the data type only if the column contains null values.
5. You can convert a CHAR column to the VARCHAR2 data type or convert a
VARCHAR2 column to the CHAR data type only if the column contains null
values or if you do not change the size.

31) Describe a few restrictions on using the “LONG” data type?

A LONG column is not copied when a table is created using a subquery. A LONG
column cannot be included in a GROUP BY or an ORDER BY clause. Only one
LONG column can be used per table. No constraint can be defined on a LONG
column.

32) What is a SET UNUSED option?

SET UNUSED option marks one or more columns as unused so that they can be
dropped when the demand on system resources is lower. Unused columns are
treated as if they were dropped, even though their column data remains in the
table’s rows. After a column has been marked as unused, you have no access to
that column.

A select * query will not retrieve data from unused columns. In addition, the
names and types of columns marked unused will not be displayed during a
DESCRIBE, and you can add to the table a new column with the same name as
an unused column. The SET UNUSED information is stored in the
USER_UNUSED_COL_TABS dictionary view.

33) What is the difference between Truncate and Delete?

The main difference between Truncate and Delete is as below:

SQL Truncate SQL Delete

TRUNCATE DELETE

Removes all rows from a table and releases Removes all rows from a table but does not
storage space used by that table. release storage space used by that table.

TRUNCATE Command is faster. DELETE command is slower.

Is a DDL statement and cannot be Rollback.

Is a DDL statement and can be Rollback.

Database Triggers do not fire on TRUNCATE. Database Triggers fire on DELETE.

34) What is the main difference between CHAR and VARCHAR2?

CHAR pads blank spaces to a maximum length, whereas VARCHAR2 does not
pad blank spaces.

35) What are Constraints? How many types of constraints are there?

Constraints are used to prevent invalid data entry or deletion if there are
dependencies. Constraints enforce rules at the table level. Constraints can be
created either at the same time as the table is created or after the table has
been created. Constraints can be defined at the column or table level.
Constraint defined for a specific table can be viewed by looking at the USER-
CONSTRAINTS data dictionary table. You can define any constraint at the table
level except NOT NULL which is defined only at the column level. There are 5
types of constraints:

 Not Null Constraint

 Unique Key Constraint
 Primary Key Constraint
 Foreign Key Constraint
 Check Key Constraint.

36) Describe types of Constraints in brief?

  NOT NULL: NOT NULL Constraint ensures that the column contains no
null values.
 UNIQUE KEY: UNIQUE Key Constraint ensures that every value in a
column or set of columns must be unique, that is, no two rows of a table
can have duplicate values in a specified column or set of columns. If the
UNIQUE constraint comprises more than one column, that group of
columns is called a Composite Unique Key. There can be more than one
Unique key on a table. Unique Key Constraint allows the input of Null
values. Unique Key automatically creates an index on the column it is
created.
 PRIMARY KEY: Uniquely identifies each row in the Table. Only one
PRIMARY KEY can be created for each table but can have several UNIQUE
constraints. PRIMARY KEY ensures that no column can contain a NULL
value. A Unique Index is automatically created for a PRIMARY KEY column.
PRIMARY KEY is called a Parent key.
 FOREIGN KEY: This is also called Referential Integrity Constraint.
FOREIGN KEY is one in which a column or set of columns take references
of the Primary/Unique key of the same or another table. FOREIGN KEY is
called a child key. A FOREIGN KEY value must match an existing value in
the parent table or be null.
 CHECK KEY: Defines a condition that each row must satisfy. A single
column can have multiple CHECK Constraints. During CHECK constraint
following expressions is not allowed:

1. References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM Pseudo

columns.

2. Calls to SYSDATE, UID, USER, and USERENV Functions

37) What is the main difference between Unique Key and Primary Key?

The main difference between Unique Key and Primary Key is:

Unique Key Primary Key

A table can have more than one Unique Key. A table can have only one Primary Key.

The primary key column cannot store NULL

The unique key column can store NULL values.
values.

Uniquely identify each value in a column. Uniquely identify each row in a table.

38) What is the difference between ON DELETE CASCADE and ON DELETE SET
NULL?

ON DELETE CASCADE Indicates that when the row in the parent table is deleted,
the dependent rows in the child table will also be deleted. ON DELETE SET NULL
Covert foreign key values to null when the parent value is removed. Without the
ON DELETE CASCADE or the ON DELETE SET NULL options, the row in the parent
table cannot be deleted if it is referenced in the child table.

39) What is a Candidate Key?

The columns in a table that can act as a Primary Key are called Candidate Key.

40) What are Views and why they are used?

A View logically represents subsets of data from one or more tables. A View is a
logical table based on a table or another view. A View contains no data of its
own but is like a window through which data from tables can be viewed or
changed. The tables on which a view is based are called Base Tables. The View
is stored as a SELECT statement in the data dictionary. View definitions can be
retrieved from the data dictionary table: USER_VIEWS.

Views are used:

 To restrict data access

 To make complex queries easy
 To provide data Independence
 Views provide groups of users to access data according to their
requirements.

Pl SQL Interview Questions For Experienced

41) What is the difference between Simple and Complex Views?

The main differences between the two views are:

Simple View Complex View

Derives data from many tables. Contain
Derives data from only one table.
functions or groups of data.

Derives data from many tables. Contain

Contains no functions or group of data
functions or groups of data.

Does not always allow DML operations thro

Can perform DML operations through the view.
the view

42) What are the restrictions of DML operations on Views?

Few restrictions of DML operations on Views are:

You cannot DELETE a row if the View contains the following:

1. Group Functions
2. A Group By clause
3. The Distinct Keyword
4. The Pseudo column ROWNUM Keyword.

You cannot MODIFY data in a View if it contains the following:

1. Group Functions
2. A Group By clause
3. The Distinct Keyword
4. The Pseudo column ROWNUM Keyword.
5. Columns defined by expressions (Ex; Salary * 12)

You cannot INSERT data through a view if it contains the following:

43) What is PL/SQL, Why do we need PL/SQL instead of SQL, Describe your
experience working with PLSQL and What are the difficulties faced while working
with PL SQL and How did you overcome them?

1. PL/SQL is a procedural language extension with SQL Language.

2. Oracle introduced PL/SQL
3. It is a combination of SQL and Procedural Statements and used for
creating applications.
4. Basically, PL/SQL is a block-structured programming language whenever
we are submitting PL/SQL
5. Blocks then all SQL statements are executing separately by using SQL
engine and also all procedure statements are executed separately.
6. Explain your current and previous projects along with your roles and
responsibilities, mention some of the challenging difficulties you've faced
in your project while working with PL/SQL.

44) What are the different functionalities of a Trigger?

Trigger is also the same as stored procedure & also it will automatically be
invoked whenever DML operation performed against table or view.

There are two types of triggers supported by PL/SQL

1. Statement Level Trigger.

2. Row Level Trigger

Statement Level Trigger: In a statement-level trigger, the trigger body is

executed only once for the DML statement.
Row Level Trigger: In a row-level trigger, the trigger body is executed for
each row DML statement. It is the reason, we are employing each row clause
and internally stored DML transaction in trigger specification, these qualifiers:
old, new, are also called records type variables.
These qualifiers are used in trigger specification & trigger body.

Syntax
old.column_name

Syntax
new column_name

When we use these qualifiers in trigger specification then we are not allowed to
use “:” in form of the names of the qualifiers.

45) Write a PL/SQL Program that raises a user-defined exception on Thursday?

declare
a exception
begin
If to_char(sysdate, ‘DY)=’THU’
then
raise a;
end if;
exception
when a then
dbms_output.put_line(‘my exception raised on thursday’);
end
;

Output: my exception raised on Thursday

46) Write a PL/SQL program to retrieve the emp table and then display the salary?
declare
v_sal number(10);
begin select max(sal)intr v_sal;
from emp;
dbms_output.put_line(v.sal);
end;
/
(or)
declare
A number(10);
B number(10);
C number(10);
begin
a:=70;
b:=30;
c:=greatest+(a,b);
dbms_output.put_line(c);
end;
/

Output:70

47) Write a PL/SQL cursor program that is used to calculate total salary from emp
table without using sum() function?
Declare
cursor c1 is select sal from emp;
v_sal number(10);
n.number(10):=0;
begin
open c1;
loop
fetch c1 into v_sal;
exit when c1%not found;
n:=n+v_sal;
end loop;
dbms_output.put_line(‘tool salary is’||’ ‘ ||n);
close c1;
end;
/

Output: total salary is: 36975

48) Write a PL/SQL cursor program to display all employee names and their salary
from the emp table by using % not found attributes?
Declare
Cursor c1 is select ename, sal from emp;
v_ename varchar2(10);
v_sal number(10);
begin
open c1;
loop
fetch c1 into v_ename, v_sal;
exist when c1 % notfound;
dbms_output.put_line(v_name ||’ ‘||v_sal);
end loop;
close c1;
end;
/

49) What is a Mutating Trigger?

 Into a row-level trigger based on a table, the trigger body cannot read
data from the same table and also we cannot perform DML operation on
the same table.
 If we are trying this oracle server returns mutating error oracle-4091:
table is mutating.
 This error is called a mutating error, and this trigger is called a mutating
trigger, and the table is called a mutating table.
 Mutating errors are not occurred in statement-level trigger because
through this statement-level trigger when we are performing DML
operations automatically data committed into the database, whereas in
the row-level trigger when we are performing transaction data is not
committed and also again we are reading this data from the same table
then only mutating errors is occurred.

50) What are Triggering Events (or) Trigger Predicate Clauses?

If we want to perform multiple operations in different tables then we must use

triggering events within the trigger body. These are inserting, updating, deleting
clauses. These clauses are used in the statement, row-level triggers. These
triggers are also called trigger predicate clauses.

→ Explore Oracle PL SQL Sample Resumes Download & Edit, Get Noticed by To
Employers!

51) What is the Discard File?

  This file extension is .dsc

 Discard file we must specify within the control file by using the discard file
clause.

 The discard file also stores reflected records based on when clause
condition within the control file. This condition must be satisfied in the
table clause.

52) What is REF CURSOR (or) CURSOR VARIABLE (or) DYNAMIC CURSOR?

Oracle 7.2 introduced ref cursor, This is a user-defined type that is used to
process multiple records and also this is a record by record process.

In static cursor database servers execute only one select statement at a time for
a single active set area wherein ref cursor database servers execute a number
of select statements dynamically for a single active set area that's why those
cursors are also called a dynamical cursor.

Generally, we are not allowed to pass static cursor as parameters to use

subprograms whereas we can also pass ref cursor as a parameter to the
subprograms because basically precursor is a user-defined type in oracle we can
also pass all user-defined type as a parameter to the subprograms.

Generally, the static cursor does not return multiple records into the client
application whereas the ref cursor is allowed to return multiple records into the
client application (Java, .Net, PHP, VB, C++).

This is a user-defined type so we are creating it in 2 steps process i.e first we

are creating a type then only we are creating a variable from that type that’s
why this is also called a cursor variable.

53) What are The Types of Ref Cursors?

In all databases having 2 ref cursors.

1. Strong ref cursor

2. Weak ref cursor

A strong ref cursor is a ref cursor that has a return type, whereas a weak ref
cursor has no return type.

Syntax:
Type typename is ref cursor return record type data type;
Variable Name typename

Syntax
Type typename is ref cursor
Variable Name typename;

In the Weak ref cursor, we must specify a select statement by using open for
clause this clause is used in the executable section of the PL/SQL block.

Syntax:
Open ref cursor varname for SELECT * FROM table_name condition;

54) What is the Difference Between the trim, delete collection methods?
SQL> declare
type t1 is table of number(10);
v_t t1;=t1(10,20,30,40,50,60);

beign

v_t.trim(2);
dbms_output.put_line(‘after deleting last two elements’);

for i in v_t.first.. V_t.last

loop
dbms_output.put_line(v_t(i));
End loop;

vt.delete(2);
dbms_output.put_line(‘after deleting second element;);

for i in v_t.first..v_t.last
loop

If v_t.exists(i) then
dbms_output.put_line(v_t(i));

end if;
end loop;
end;
/
55) What are Overloading Procedures?

Overload refers to the same name that can be used for a different purpose, in
oracle we can also implement an overloading procedure through the package.
Overloading procedure having the same name with different types or different
numbers of parameters.

56) What are the Global Variables?

In oracle, we are declaring global variables in Package Specification only.

57) What is Forward Declaration?

In oracle declaring procedures within the package body are called forward
declaring generally before we are calling private procedures into public
procedure first we must implement private procedure within body otherwise use
a forward declaration within the package body.

58) What is Invalid_number, Value_Error?

In oracle when we try to convert “string type to number type” or” data string
into data type” then the oracle server returns two types of errors.

1. Invalid_number
2. Value_error (or) numeric_error

Invalid_number:

When PL/SQL block has a SQL statement and also those SQL statements try to
convert string type to number type or data string into data type then oracle
server returns an error: ora-1722-Invalid Number

For handling this error oracle provides number exception Invalid_number

exception name.

Example:
begin
Insert
intoemp(empno, ename, sal)
values(1,’gokul’, ‘abc’)
exception when invalid_number then dbms_output.put_line(‘insert proper data
only’);
end;/

value_error:
Whenever PL/SQL block having procedural statements and also those
statements find to convert string type to number type then oracle servers return
an error: ora-6502: numeric or value error: character to a number conversion
error
For handling, this error oracle provided exception value_error exception name

Example:
begin
declare z number(10);
begin
z:= ‘&x’ + ‘&y’;
dbms_output.put_line(z);
exception when value_error then dbms_output.put_line(‘enter numeric data
value for x & y only’);
end;/

Output:
Enter value for x:3
Enter value for y:2
z:=5

Enter value for x:a

Enter value for y:b

Error:enter numeric data value for x & y only.

59) What is Flashback Query?

 Flashback query is handled by the Database Administrator the only

flashback queries along to allow the content of the table to be retrieved
with reference to the specific point of time by using as of clause that is
flashback queries retrieves accidental data after committing the
transaction also.
 Flashback queries generally use undo file that is flashback queries
retrieve old data before committing the transaction oracle to provide two
methods for flashback queries

Method1: using the timestamp

Method2: using scn number

60) Explain what PL/SQL package consists of?

PL/SQL consists of two major parts, they are package specification and package
body.

1. Package specification: it acts as a public interface for your application

which includes procedures, types, etc.
2. Package Body: It contains the code required to implement the Package
Specification

61) Explain what the benefits of PL/SQL Packages are?

These are the benefits of PL/SQL Packages

 We can store functions and procedures in a single unit called a package.

 Packages provide security to grant privileges.
 Functions and procedures, within the package, shares a common variable
among them.
 Packages support even if the functions are overloaded.
 Packages enhance the performance even when multiple objects loaded
into memory.

62) Explain different methods to trace the PL/SQL code?

Tracing code is a necessary technique to test the performance of the code

during runtime. We have different methods in PL/SQL to trace the code, which
are,

 DBMS_ TRACE
 DBMS_ APPLICATION_INFO
 Tkproof utilities and trcsess
 DBMS_SESSION and DBMS_MONITOR

63) What does it mean by PL/SQL Cursors?

In PL/SQL to retrieve and process more, it requires a special resource, and that
resource is known as Cursor. A cursor is defined as a pointer to the context area.
The context area is an area of memory that contains information and SQL
statements for processing the statements.

64) What is the difference between Implicit and Explicit Cursors?

An implicit cursor used in PL/SQL to declare, all SQL data manipulation

statements. An implicit cursor is used to declare SQL statements such as open,
close, fetch, etc.

An explicit cursor is a cursor and which is explicitly designed to select the

statement with the help of a cursor. This explicit cursor is used to execute the
multirow select function. An explicit function is used PL/SQL to execute tasks
such as update, insert, delete, etc.

65) What is a trigger?

It is a program in PL/SQL, stored in the database, and executed instantly before

or after the UPDATE, INSERT and DELETE commands.

66) What are the uses of database triggers?

Triggers are programs that are automatically fired or executed when some
events happen and are used for:

 To implement complex security authorizations.

 To drive column values.
 To maintain duplicate tables.
 To implement complex business rules.
 To bring transparency in log events.

67) Name the two exceptions in PL/SQL?

Error handling part of PL/SQL is called an exception. We have two types of

exceptions, and they are User-defined and predefined.

Related article: Error Handling in SQL Server

68) Which command is used to delete the package?

To delete the ‘Package’ in PL/SQL we use the DROP PACKAGE command.

69) what is the process for PL/SQL compilation?

The compilation process consists of syntax check, bind, and p-code generation.
It checks the errors in PL/SQL code while compiling. Once all errors are
corrected, a storage address allocated to a variable that stores this data. This
process is called binding. P-Code consists of a list of rules for the PL/SQL engine.
It is stored in the database and triggered when the next time it is used.
1. Explain the difference between RANK and DENSE_RANK functions in
Oracle SQL.

RANK and DENSE_RANK both assign rankings to result rows.

With RANK, when two or more rows have the same values, they’ll be
assigned the same rank, and the subsequent rank will be skipped.

Meanwhile, DENSE_RANK provides a consecutive ranking and doesn’t leave

gaps in ranking even when duplicate values exist.

2. What is the purpose of the UNION operator in Oracle SQL?

The UNION operator combines the results of two or more SELECT queries into
a single result set – as if it came from a single query. It merges the rows from
different queries, removes duplicate rows, and presents a unified result.

3. Name one advantage of using indexes in a database.

Indexes improve query performance through quicker data retrieval by

reducing the need for full table scans.

4. Differentiate between the WHERE clause and the HAVING clause in

Oracle SQL.

The WHERE clause filters rows before grouping – that is, before they’re
included in the result set. Filtering is also based on certain conditions.

The HAVING clause, on the other hand, filters data post-grouping – meaning
after aggregation.

5. How does the Oracle Query Optimizer determine an execution plan for a
query?

It uses heuristics or rules of thumb and statistics to decide on the most

efficient execution plan based on available indexes, table size, and query
complexity.
6. What is the key difference between ROW-level and STATEMENT-level
triggers in Oracle?

ROW-level triggers fire once for each affected row, therefore allowing row-
specific actions.

STATEMENT-level triggers are executed only once for the entire statement.
This is regardless of the number of affected rows and is more suitable for
actions that don't depend on individual rows.

7. What do the COMMIT and ROLLBACK statements in Oracle SQL do?

The COMMIT statement saves all the changes made in a transaction to the
database, making them permanent. The ROLLBACK statement undoes the
changes in the transaction and reverts the database to its pre-transaction
state.

8. What are some advantages of using bind variables in Oracle SQL?

Bind variables improve performance through caching and reusing, reducing

the need for parsing. Bind variables also protect against SQL injection
attacks, require minimal maintenance, and reduce memory usage.

9. Differentiate between VARCHAR and VARCHAR2 data types.

Both VARCHAR and VARCHAR2 are used to store variable-length character

strings. VARCHAR is a standard SQL data type which works across different
relational database systems. Whereas, VARCHAR2 is specific to Oracle.

VARCHAR 2 has several advantages. It is more storage efficient and, unlike

VARCHAR, it does not store trailing spaces at the end of a string so avoids
potential unexpected results when comparing strings. However, VARCHAR2
might not be supported non-Oracle database systems.
10. How would you explain database roles and privileges in Oracle SQL
security? How do you grant and revoke privileges to users and roles in
Oracle?

Database roles are named groups of related privileges. They allow for
assigning multiple privileges to a role and granting or revoking the role to
users, simplifying security management. The GRANT statement is used to
grant, and the REVOKE statement is used to revoke privileges.

Oracle SQL practical application questions

These questions aim to test how candidates can apply their Oracle SQL
knowledge to perform real, practical tasks. You can use these 10 example
questions to assess how they write SQL queries, retrieve and manipulate
data, and use Oracle SQL features.

While we provide sample answers to each question below, there are often
multiple ways to write a query to achieve the desired outcome. A candidate's
answers can depend on table structure, data availability, and specific user
requirements.

11. Write an Oracle SQL query to find the average salary of employees
within each department.

SELECT department_id, AVG(salary) AS average_salary

FROM employees

GROUP BY department_id;

12. Write an Oracle SQL query to find employees who earn more than their
managers.

SELECT emp.*

FROM Employee emp

INNER JOIN Employee mgr ON emp.manager_id = mgr.employee_id

WHERE emp.salary > mgr.salary;

13. How would you update the status column of the orders table to set all
orders with a total amount greater than 1,000 to High Value?

UPDATE orders

SET status = 'High Value'

WHERE total_amount > 1000;

14. Write an Oracle SQL query to get the date and time of the last 10
logins for a specific user.

SELECT login_time

FROM UserLogins

WHERE user_id = 'specific_user_id'

ORDER BY login_time DESC

FETCH FIRST 10 ROWS ONLY;

15. Retrieve the top five highest-rated products based on customer

reviews from the product_reviews table.

SELECT product_id, product_name, AVG(review_rating) AS average_rating

FROM product_reviews

GROUP BY product_id, product_name

ORDER BY average_rating DESC

FETCH FIRST 5 ROWS ONLY;

16. Calculate the total revenue generated by each customer in the last
three months.

SELECT customer_id, SUM(revenue) AS total_revenue

FROM sales

WHERE transaction_date >= TRUNC(SYSDATE) - INTERVAL '3' MONTH

GROUP BY customer_id;

17. Calculate the percentage of total sales each product contributes to the
overall revenue.

SELECT product_id, SUM(total_amount) / (SELECT SUM(total_amount) FROM

sales) * 100 AS percentage_contribution

FROM sales

GROUP BY product_id;

18. Write an Oracle SQL query to find the names of employees not
assigned to any project.

SELECT employee_name

FROM employees

WHERE employee_id NOT IN (SELECT DISTINCT employee_id FROM projects);

19. Write an Oracle SQL query to find the five most common names in the
Employee table.

SELECT name, COUNT(*) AS name_count

FROM Employee

GROUP BY name
ORDER BY name_count DESC

FETCH FIRST 5 ROWS ONLY;

20. Write an Oracle SQL query to ensure only users with the manager role
can insert rows into the performance_reviews table.

CREATE OR REPLACE TRIGGER enforce_manager_insert

BEFORE INSERT ON performance_reviews

FOR EACH ROW

DECLARE

BEGIN

IF NOT (IS_ROLE_ENABLED('manager')) THEN

RAISE_APPLICATION_ERROR(-20001, 'Only users with the "manager" role

can insert into this table.');

END IF;

END;

Oracle SQL scenario-based questions

Scenario-based questions present candidates with complex case studies to

solve. These require candidates to analyze the scenario, design a suitable
SQL solution, and implement it.

This is a great way to see candidates’ Oracle SQL knowledge, problem-

solving, and critical thinking skills in action.

Below are 10 scenario-based questions you can use in your Oracle SQL
interviews.
21. You have an Employees table with columns for employee names and
their respective managers. How will you find the longest chain of
reporting for each employee?

WITH RECURSIVE ReportingChain AS (

SELECT employee_id, manager_id, employee_name, 1 AS chain_length

FROM Employees

WHERE manager_id IS NOT NULL

UNION ALL

SELECT e.employee_id, e.manager_id, e.employee_name, rc.chain_length

+1

FROM Employees e

INNER JOIN ReportingChain rc ON e.manager_id = rc.employee_id

SELECT employee_id, employee_name, MAX(chain_length) AS longest_chain

FROM ReportingChain

GROUP BY employee_id, employee_name;

22. Imagine that you have a students table with the columns student_id,
student_name, and birthdate. Write an Oracle SQL query to find each
student's age (in years) as of today.

SELECT student_id, student_name,

EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM birthdate) AS

age

FROM students;
23. In a database containing information about books and authors, write
an SQL query to identify the author with the most published books.

SELECT author_id, author_name, COUNT(book_id) AS total_books

FROM Authors

JOIN Books ON Authors.author_id = Books.author_id

GROUP BY author_id, author_name

ORDER BY total_books DESC

FETCH FIRST 1 ROWS ONLY;

24. Imagine you have an Inventory table

with product_id and quantity columns. Write an Oracle SQL query to find
the products that have experienced an increase in quantity compared to
the previous month.

SELECT product_id

FROM (

SELECT product_id, quantity, LAG(quantity) OVER (ORDER BY month) AS

prev_quantity

FROM Inventory

WHERE quantity > prev_quantity;

25. Case Study: Sales Analysis System. The dataset contains information
about sales transactions in a company. The "sales" table includes the
following columns:

 transaction_id: Unique identifier for each transaction.

 customer_id: Unique identifier for each customer.
  product_id: Unique identifier for each product sold.
 transaction_date: The date when the transaction occurred.
 quantity: The quantity of the product sold in the transaction.
 unit_price: The price of one unit of the product.

You’re tasked with finding the top 5 customers who made the highest total
purchase amount in the last quarter (last three months) and displaying their
names and total purchase amounts. Write an Oracle SQL query to retrieve
this information.

WITH LastQuarterSales AS (

SELECT customer_id, SUM(quantity * unit_price) AS

total_purchase_amount

FROM sales

WHERE transaction_date >= TRUNC(SYSDATE) - INTERVAL '3' MONTH

GROUP BY customer_id

SELECT c.customer_id, c.customer_name, lqs.total_purchase_amount

FROM LastQuarterSales lqs

JOIN customers c ON lqs.customer_id = c.customer_id

ORDER BY lqs.total_purchase_amount DESC

FETCH FIRST 5 ROWS ONLY;

26-30. Case Study: Employee Performance Evaluation System.

The dataset contains information about employees' performance evaluations

in a company. The "employees" table includes the following columns:

 employee_id: Unique identifier for each employee.

 employee_name: The name of the employee.
  department: The department to which the employee belongs (e.g., HR,
Finance, Sales).
 rating: The employee's performance rating on a scale of 1 to 5 (5 being
the highest).
 years_of_experience: The number of years of experience of the
employee.
 salary: The salary of the employee.
 manager_id: The ID of the employee's manager.

26. Imagine you’re an HR manager and want to get an overview of the

average performance rating for each department. Write an Oracle SQL
query to retrieve the department and the average performance rating for
each department.

SELECT department, AVG(rating) AS avg_rating

FROM employees

GROUP BY department;

27. Say you’re preparing a report for the management to identify

employees who have shown consistently high performance. Write an
Oracle SQL query to retrieve the names and performance ratings of
employees with a rating of 5 in all their performance evaluations.

SELECT employee_name

FROM employees

WHERE rating = 5

GROUP BY employee_name

HAVING COUNT(*) = (SELECT COUNT(*) FROM employees);

28. Imagine you want to identify employees who are eligible for
promotions based on their years of experience and current salary. Write
an Oracle SQL query to retrieve the employee ID, name, department, and
salary of employees who have more than 5 years of experience and earn
more than $95,000 per year.

SELECT employee_id, employee_name, department, salary

FROM employees

WHERE years_of_experience > 5 AND salary > 95000;

29. Consider a scenario where the company plans to give a salary raise to
employees with a performance rating of 4 or 5. Write an Oracle SQL query
to update eligible employees' salaries by 8%.

UPDATE employees

SET salary = salary * 1.08

WHERE rating IN (4, 5);

30. You want to create a list of managers and the number of employees
reporting to them. Write an Oracle SQL query to retrieve managers’
names and the count of employees reporting to each manager.

SELECT m.employee_name AS manager_name, COUNT(e.employee_id) AS

num_employees

FROM employees e

JOIN employees m ON e.manager_id = m.employee_id

GROUP BY m.employee_name;

How to add Oracle SQL questions to your hiring

campaign
Although an interview is a great way to assess traits and skills, it shouldn’t
be the only tool you rely on when hiring Oracle SQL developers.

The most effective hiring decisions are made using a multi-measure

technique that involves personality tests, cognitive ability assessments, job-
specific skills tests, and more.

With TestGorilla, you can access an extensive library of pre-employment

tests and create tailored assessments for your Oracle SQL hiring campaign.
Consider including:

 Personality tests such as the Enneagram, DISC, or the Big 5

(Ocean) to get a better understanding of their behavioral attributes
 Curated assessments with custom questions that are relevant to
your company
 TestGorilla’s Culture add test to evaluate whether a candidate will
fit into your company’s culture and working practices
 Role-specific skills tests such as:
o PostgreSQL test
o SQL coding test — entry level
o SQL coding test — intermediate level
o Microsoft SQL server test
o SQL server management studio test

Combining these techniques will help make your hiring process airtight and
increase your chances of finding and hiring a suitable candidate for your
Oracle SQL role.

1. Why do you want to work at this company?

2. What are your expectations for this position?
3. What are your biggest strengths?
4. Name one of your professional weaknesses.
5. How do your values align with our company values?
6. Why are you leaving your current role?
 7. How do you manage your time?
8. What are your qualifications for this position?
9. How would you describe yourself?
10. Do you prefer to work individually or in a team setting?
11. What are your goals for this position?
12. Where do you see yourself in five years?
13. Discuss the highlights of your resume.
14. What are your salary expectations?
15. How do you prioritize work?
16. How did you find this position opening?

Related: Top 20 Interview Questions (With Sample Answers)

Oracle interview questions about experience and

background
Questions about your work experience or educational background allow
interviewers to understand your professional ethics and knowledge. Interviewers
might ask questions to understand how much you know about Oracle processes
and how you conduct your work, especially if Oracle has been an important part of
your previous roles. These questions can highlight your expertise in certain Oracle
systems or database roles. You may receive experience and background questions
like:17. What is Oracle?18. How familiar are you with Oracle platforms and
programs?19. What is your current work day like?20. What are the key roles of
database management and development?21. What's your specialization with
Oracle technologies?22. How do you make sure your work is accurate?23. What
editions of Oracle and database types have you used in past roles?24. What Oracle
terminology do you know?25. How did you develop your skills in Oracle?26. How
would you describe your Oracle training?27. What industry certifications do you
have?28. How do you resolve conflicts with fellow employees?29. How do you
prioritize your work tasks and projects?30. How did your education prepare you for
your career?31. What do you do when you encounter a problem at work?32. Have
you ever led a team? How did you do it?33. How do you maintain and improve your
Oracle skills?Related: 16 Computer Science Certifications To Help Launch Your
IT Career
In-depth Oracle interview questions
In-depth questions feature specific situations or industry terminology to test your
skills and understanding of particular Oracle functions. These questions may relate
to specific job responsibilities and roles. Interviewers may provide situational and
follow-up questions to see how quickly you can develop a response or solution.
Here are some potential in-depth questions:34. When would you use data
manipulation language?35. What is the database writer process?36. Why would
you replace a procedure rather than recreate one?37. Describe the ANALYZE
command.38. What's the difference between a normal table and a nested table?39.
How would you create a database dictionary?40. How do you maintain your
database?41. How would you store values in a binary data format?42. What coding
languages do you use with Oracle?43. What data types would you use for specific
memory sizes?44. How would you evaluate different types of sub-queries?45. How
would you explain Oracle to a non-expert?46. What are Oracle's most important
functions?47. If you could improve Oracle's processes, what would you change?48.
Have you ever had to train someone in Oracle? How did you do it?Related: 25
Client Management Software Programs
Do you need help with your resume?
YesNo

Oracle interview questions with sample answers

The following Oracle interview questions feature example answers and tips for
crafting responses that show your qualifications:

49. How do you keep your industry and Oracle-specific knowledge

updated?

An interviewer may ask you this question to understand your willingness to learn
and your process of maintaining your knowledge. Keeping your industry
knowledge current allows you to understand the latest developments in Oracle
technologies and work processes to produce accurate results. When answering
this question, consider your learning habits and work or personal processes that
help you gain insight into industry updates.Example: "Through my experience
working with Oracle technologies, patch updates occur every three months for Oracle
products that address code or security vulnerabilities. I check for security alerts and
Oracle bulletins regularly to ensure that I apply new updates when they become
available. Checking Oracle support resources, I learn various techniques for using
company products to automate my work processes and maintain accurate and
thorough work."Related: How To Demonstrate Your Willingness To Learn at
Work

50. Why are password files necessary?

Interviewers may ask this question to determine your understanding of password

file uses and their significance within your industry. Password files store Oracle
database user information and access levels, allowing users to have different
privileges and functions within the database. You can answer this question by
defining password files and explaining how you might use them in your work.
When you do this, try to use specific examples and personal experiences to
highlight your familiarity with the topic.Example: "Password files store database
user log-in credentials, restrictions and access information so authorized users can
retrieve or store their data. These files monitor and verify log-in activity to keep
sensitive information secure and prevent potential data breaches. When necessary, I
can search through the database password files to identify how many users have
account access and what system privileges they have through coding queries. These
searches allow me to modify data easily for security purposes."Related: 35 Oracle
DBA Interview Questions (With Sample Answers)

51. What's your process for updating Oracle indexes?

You may receive this question to test your index usage knowledge and
understanding of basic Oracle processes. Oracle indexes allow users to quickly
retrieve data and update it automatically when data table information changes. To
communicate your understanding of Oracle indexes, you can explain how Oracle
updates without manual input and why you'd use an Oracle index.Example: "As
the Oracle indexes distribute data from table inputs automatically, I monitor data table
changes to ensure that the Oracle systems perform their functions properly. I reference
changes with redo log files to identify discrepancies and execute the necessary
solutions. To prevent database instance failures, I create my scripts with at least three
redo log groups to maintain accuracy."Related: How To Become a Database
Specialist (Plus Salary and Skills)

52. What is an Oracle database?

Interviewers may ask this question to see how brief or in-depth your response is.
An Oracle database is a management system for data storage and retrieval, but it
also consists of various components that allow users to have certain data access,
modifications or functions. You can show your expertise in Oracle databases by
providing an in-depth definition that includes the different components of an
Oracle database and its functions. Keep your answer concise by only including the
most relevant information.Example: "Oracle databases store data for company
personnel to manage and retrieve so they can execute their responsibilities. These
databases have three main files that allow users to manage their information. There
are the data files that hold all existing data in the database, the control files that create
the structure for data storage locations and redo log files that document changes in
case of potential errors."Related: What Are the Different Types of Database
Management?

53. When would you initiate a restricted session in Oracle?

An interviewer can ask this question to understand if you can complete restricted
sessions in Oracle and why you might choose to perform this task. Restricting
sessions in Oracle allows you to limit the access of other users in the database to
conduct routine maintenance or update tasks that may receive interference from
outside factors. Answer this question by providing an example of why you've
opened restricted sessions in Oracle and how you've completed it.Example: "I
perform routine database assessments to optimize company system processes, and I
conduct restricted sessions so that I don't interrupt any users currently using the
database. This prevents potential errors on those users' end, along with my work tasks.
To do this, I run the programming language command to alter the system and enable
a restricted session."

54. What's a SELECT statement?

A hiring manager may ask this question to determine if you understand the
method for retrieving data from defined conditions. They can also assess your
knowledge of how an SQL query defines specific conditions. In your answer,
provide a brief definition and how you can apply it in your work.Example: "A
SELECT statement is a command that retrieves a set of distinct values from an entire
database table or a certain column. An SQL query defines the conditions for a SELECT
statement to follow. Traditionally, a SELECT clause and a FROM clause go together to
form complete SQL queries."
55. What's the difference between a cold backup and a hot backup?

Your answer to this question can help a hiring manager assess your
understanding of data preservation in Oracle. In your response, provide a brief
definition of each. As you're describing each term, you can state its unique
benefits.Example: "A cold backup occurs when the database is offline. During a cold
backup, the data continues to save itself even after a user has issued the SHUTDOWN
command. A hot backup occurs when users are online and using the database actively.
This kind of backup is especially useful for systems that require a constant operation."

56. What's the purpose of RAW datatype?

A hiring manager may ask this question to determine how well you understand
data within Oracle that's not necessarily available for interpretation. In your
response, you can provide a brief definition of RAW datatype and explain why it's
useful.Example: "Users implement RAW datatype to represent byte strings or binary
data that Oracle doesn't have to interpret. For example, a user may implement RAW
datatype when storing character sequences for graphics."Please note that none of the
companies mentioned in this article are affiliated with Indeed.

What is cardinality in a database?

Cardinality is a mathematical term that refers to the number of elements in a given
set. Database administrators may use cardinality to count tables and values. In a
database, cardinality usually represents the relationship between the data in two
different tables by highlighting how many times a specific entity occurs in
comparison to another. For example, the database of an auto repair shop may
show that a mechanic works with multiple customers every day. This means that
the relationship between the mechanic entity and the customer entity is one
mechanic to many customers.In this example, note that each customer has exactly
one vehicle that they bring to the auto repair shop during their visit. This means
the relationship between the customer entity and the car entity is a one-to-one
relationship. Using cardinality can help database administrators automatically
establish these relationships in a software program or database. This can make it
easy for users to see the correlation between mechanics, customers and cars
when searching for specific data or files.Related: What Are the Different Types
of Database Management?

Why's cardinality important in databases?

Cardinality is important in databases because it creates links from one table or
entity to another in a structured manner. This has a significant impact on the
query execution plan, which is a sequence of steps users can take to search for
and access data within a database system. Having a well-structured query
execution plan can make it easier for users to locate the data they need quickly.
Database administrators can apply cardinality to databases for various reasons,
but businesses typically use the cardinality model to analyze information about
their customers or inventory numbers.For example, an online retailer may have a
database table that lists each one of its unique customers. It may also have
another database table that lists all the purchases customers have made from its
store. Since it's likely that each customer purchased multiple items from the store,
the database administrator may represent this pattern by using a one-to-many
cardinality relationship that links each customer in the first table to all the
purchases they made in the second table.Related: Learn About Being a
Database Administrator

Types of cardinality in databases

There are three types of cardinality that may apply to a database. These three
types are one-to-one relationships, one-to-many relationships and many-to-many
relationships. Here are definitions and examples for each type of cardinality:

One-to-one relationship

A one-to-one (1:1) relationship describes a situation where one occurrence of an

entity relates to exactly one occurrence of another entity. You might see this type
of cardinality in a database if you're working with one row in a specific table that
relates to one row in a different table. Most database administrators agree that
this is the least common type of relationship that occurs in databases.Example: A
school may use cardinality in its student database to show a one-to-one relationship
between each student and their student ID number. The school only assigns one ID
number per student. As a result, using this modeling concept can help faculty members
look up a student's ID number quickly if they need to create a replacement card or use
it to access a specific file.Related: A Guide to Data Classification (With Types and
Examples)

One-to-many relationship

A one-to-many (1:N) relationship describes a situation where one occurrence in an

entity relates to many occurrences in another entity. You might see this type of
cardinality in a database if you're working with one row in a specific table that
relates to multiple rows in a different table. The one-to-many relationship is the
most common type of relationship, as you can use it to store data in any relational
database.Example: An online food delivery service may create a data table to store all
its customer ID numbers. It may also create another table for unique order ID
numbers. Each order ID number must correlate with the specific customer ID number
of the person who placed the order. Many order ID numbers may relate back to a
single customer ID number. A database developer can use cardinality to establish this
as a one-to-many relationship to make it easy for other team members to connect each
customer with all of the orders they've placed.Related: Data Entry Skills: Definition
and 6 Steps To Improve Yours

Many-to-many relationship

A many-to-many (M:N) relationship describes a situation where multiple

occurrences in one entity relate to multiple occurrences in another entity. You
might see this type of cardinality in a database if you're working with several rows
in a specific table that relate to several rows in another table. Many-to-many
relationships can be confusing to study, so some database administrators can opt
to divide a many-to-many relationship into several one-to-many
relationships.Example: A book retailer may use the many-to-many relationship model
to manage its online database. It might include a list of book titles in one table and a
list of author names in another table. Many of the authors may have multiple books
and some of the authors may have even co-written books together. This means that a
single author may have a relationship with more than one book and a single book may
have a relationship with more than one author. By using the many-to-many
relationship model, the retailer can quickly assess which authors and books belong
together.Related: 6 Methods of Data Collection (With Types and Examples)
Get interview-ready with tips from Indeed
Prepare for interviews with practice questions and tips
Frequently asked questions about cardinality in
databases
Here are some answers to frequently asked questions about cardinality in
databases:

How's cardinality different from modality?

While cardinality and modality are both modeling concepts that professionals use
in database design to analyze entities and their relationships with each other,
there are some key differences between these two methods. Cardinality measures
the maximum number of associations between two different table rows or
columns. Alternatively, modality represents whether a relationship between two or
more entities exists at all. In other words, modality focuses on the minimum
number of associations, whether a relationship is mandatory and if the
relationship is null.Related: What Is Data Modeling?

What's an entity-relationship (ER) diagram?

An ER diagram visually represents the cardinality that a specific database contains.

An ER diagram often appears as a flowchart that shows how different entities in a
database relate to one another. A database analyst may use an ER diagram to
design, upgrade or troubleshoot a relational database system. ER diagrams are
beneficial because they can help database analysts and system users quickly
identify where the data's location and how it links together.Related: 28 Flowchart
Symbols and Their Meanings

What's the difference between high and low cardinality?

High cardinality describes a data set that has a large number of unique values or
entities. This represents a significant level of diversity and very little repetition. For
example, a data set that lists the name of each unique customer would have high
cardinality because the names are likely to vary.Low cardinality refers to a data set
that has a large quantity of the same values or entities. In a low cardinality data
set, many of the same entities repeat themselves and there's less variety. For
example, a data set that lists the category of each product for a small retail store
may have low cardinality because there are only a few categories that are likely to
repeat.

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,

Varchar Varchar2
Can store characters up to 2000 Can store characters up to 4000
bytes bytes.
It will hold the space for characters
defined during declaration even if all It will release the unused space
of them are not used
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 ]
);

Here,

 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:

 Average
 Count
 Sum
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.

Example:

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;
MySQL DBA Certification Training

Explore Curriculum

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’.

SQL Interview Questions

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
 Domain 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:

1. Select: Data Retrieval

2. Insert, Update, Delete, Merge: Data Manipulation Language (DML)
3. Create, Alter, Drop, Rename, Truncate: Data Definition Language (DDL)
4. Commit, Rollback, Savepoint: Transaction Control Statements
5. 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:

1Select rownum <fieldnames> from table;

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?

SQL iSQL*Plus
It is a language It is an environment
 Character and date columns heading
Default heading justification is in
are left-justified and number column
Centre
headings are right-justified
Cannot be Abbreviated (short forms) Can be Abbreviated
Has a dash (-) as a continuation
Does not have a continuation
character if the command is longer
character
than one line
Use Functions to perform some
Use commands to format data
formatting
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:

1. Character
2. Number
3. Date
4. Conversion
5. General

 Multiple-Row functions: These functions operate on groups of rows

to give one result per group of rows.

Types of Multiple-Row functions:

1. avg
2. count
3. max
4. min
5. sum
6. stddev
7. variance

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

General functions are of following types:

 1. NVL: Converts a null value to an actual value. NVL (exp1, exp2) .If
exp1 is null then NVL function return value of exp2.
2. 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)
3. NULLIF: Compares two expressions and returns null if they are equal
or the first expression if they are not equal. NULLIF (exp1, exp2)
4. 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.
5. 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:

1. You should enclose the sub-queries within parenthesis.

2. Place these subqueries on the right side of the comparison condition.
3. Use Single-row operators with single-row subqueries.
4. Use Multiple-row operators with multiple-row subqueries.

Types of subqueries:

1. Single-Row Subquery: Queries that return only one row from the
inner select statement. Single-row comparison operators are: =, >,
>=, <, <=, <>
2. 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?

Databases Training
SQL CERTIFICATION COURSE
SQL Certification Course
Reviews
5(1600)

SQL ESSENTIALS TRAINING

SQL Essentials Training
Reviews
5(4550)

MONGODB CERTIFICATION TRAINING COURSE

MongoDB Certification Training Course
Reviews
4(6800)

MYSQL DBA CERTIFICATION TRAINING

MySQL DBA Certification Training
Reviews
5(2800)

TERADATA CERTIFICATION TRAINING

Teradata Certification Training
Reviews
5(1200)

APACHE CASSANDRA CERTIFICATION TRAINING

Apache Cassandra Certification Training
Reviews
5(5150)

Next
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.

Syntax:

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?

Unique key Primary key

A table can have more than one A table can have only one Primary
 Unique Key Key
A unique key column can store NULL A primary key column cannot store
values NULL values
Uniquely identify each value in a
Uniquely identify each row in a table
column
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:

Triggers Constraints
Affect all rows of the table including
Only affect those rows added after
that already exist when the
the trigger is enabled
constraint is enabled
Triggers are used to implement
complex business rules which cannot Constraints maintain the integrity of
be implemented using integrity the database
constraints
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?
MySQL DBA Certification Training

Weekday / Weekend BatchesSee Batch Details

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?

Truncate Delete
Removes all rows from a table and Removes all rows from a table but
releases storage space used by that does not release storage space used
table by that table
This command is faster This command is slower
It is a DDL statement and cannot be It is a DDL statement and can be
Rollback Rollback
Database Triggers do not fire on
Database Triggers fire on DELETE
TRUNCATE
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).

1. What are schema objects?

Schema objects are tables, indexes, databases, views, sequences,

synonyms, triggers, functions, procedures, and packages.

2. What are the components of physical database structure in the

Oracle database?

The components of the physical database structure are:

 Greater than equal to two redo log files.

 Greater than equal to one control file.

 Greater than equal to one data file.

3. What are the components of logical database structure in Oracle

database?
The two main components of logical database structure in
the Oracle database are:

 Tablespaces

 Database's schema objects

A tablespace is the logical storage unit of an Oracle database. It is a set of

related logical structures. The default tablespaces are:

 SYSTEM and SYSAUX tablespaces

 USERS tablespace

 UNDOTBS1 tablespace

 TEMP tablespace

4. Differentiate between Varchar and varchar2.

Varchar and Varchar2 are data types in Oracle to store character strings of
varying lengths. While Varchar can store characters up to 2000 bytes,
Varchar2 can store up to 4000 bytes. Varchar will hold space for all
characters defined during declaration. Varchar2 will free up the space for
characters that were unused.

5. What is an Oracle table?

A table is the basic unit of data storage in the Oracle database. Data is
stored in rows and columns.

“CREATE TABLE” statement is used to create a new table.

6. What is a nested table?

Nested table is a data type in Oracle used to hold columns for storing multi-
valued attributes. A nested table can hold an entire sub table.

7. How are comments represented in Oracle?

We can represent comments in Oracle in the following two ways:

Two dashes (–) before the line starts – Single statement

For block of statement, we can use /*—— */ to represent it as comments

8. What is the relationship between database, tablespace and data

file?

An Oracle database can contain one or more tablespaces or logical storage

units. These tablespaces collectively store all the data in a database. Each
tablespace consists of one or more files called data files. The data files are
physical structures conforming to the operating system in which Oracle is
running.

Become a Software Development Professional



Full Stack Java Developer

 Kickstart Full Stack Java Developer career with industry-aligned curriculum by experts
 Hands-on practice through 20+ projects, assessments, and tests

6 months

View Program

Full Stack Web Developer - MEAN Stack

 Comprehensive Blended Learning program
 8X higher interaction in live online classes conducted by industry experts
 11 months

View Program

Not sure what you’re looking for?View all Related Programs

9. What are database objects in Oracle?

Various Oracle database objects are:

Tables – set of elements arranged in vertical and horizontal manner

Tablespaces – logical storage unit

Views – Virtual table derived from one or more tables.

Indexes – performance tuning method for record processing.

Synonyms – name for tables

10. Explain the ANALYZE command in Oracle.

The “ANALYZE” command allows the user to perform various functions on

index, table or cluster. It helps:

To identify migrated and chained rows of the table or cluster.

To validate structure of an object

To collect statistics about objects used by user, which are then stored onto
the data dictionary

To delete statistics used by an object from the data dictionary

11. What types of Joins are used in SUBQUERIES?

A join is used to compare and combine specific rows of data from two or
more tables in a database. The various joins are:

Self Join – to join table with itself

Outer Join – to join matching data and some dissimilar data from more than
one table

Equi-join – with a join condition that returns only rows with equivalent values

12. What is DML?

We use Data Manipulation Language or DML to access and handle data in the
existing objects. The DML statements are select, insert, update, and delete.

RAW datatype in Oracle

The RAW datatype stores variable-length binary data or byte string values.
The maximum size for a raw in a table is 32767 bytes.

13. What are Aggregate functions in Oracle?

In an aggregate function, multiple rows or records are combined, and

operations performed on a set of values to get a single value as output.
Common aggregate functions include:

Sum

Count

Average
14. What are Temporal data types?

Date Datatype

Time Stamp Datatype

Interval Datatype

15. What is a View?

View is a logical table based on one or more tables or views. A view does not
store data physically. Tables upon which views are based are called Base
tables.

16. How are pictures stored onto a database?

Long Raw Data type can be used to store pictures onto a database. Binary
data of length 2GB can be stored using this datatype.

17. Where do you use DECODE and CASE statements?

These statements function like the if-then-else statement and are used in
Oracle for data value transformation.

18. What is MERGE in Oracle, and how can you merge two tables?

Merge statement allows merging data from two tables subsequently. It

selects data from the source table and inserts/updates the data in the
destination table as per the condition specified in the query. Merge
command is useful in Data warehousing applications.

19. What is NULL value in Oracle?

NULL value indicates missing or unknown data.

20. When is a SYSTEM tablespace created?

A SYSTEM tablespace is automatically generated when any database is

created in the Oracle database system. It contains the data dictionary tables
for the entire database and always remains online.

21. What does each number represent in the Oracle version

9.3.0.5.0?

Oracle version number represents the following:

 9 - Major database release number

 3 - Database maintenance release number

 0 - Application server release number

 5 - Component Specific release number

 0 - Platform Specific release number

22. What is a bulk copy in Oracle?

Bulk copy or BCP is for importing and exporting data from tables and views.
It doesn't copy the structure of the same data. It has a quick mechanism for
copying data and allows the users to easily take the data backup.

Intermediate Oracle Interview Questions

23. What is Integrity Constraint?

Integrity constraint is a declaration to define a business rule for a table
column. It ensures data accuracy and consistency. Common types are:

Domain Integrity

Referential Integrity

24. What is SQL?

SQL or Structured Query Language is used to communicate with the server

for accessing, manipulating, and controlling data.

25. What are the different types of SQL statements?

The five types of SQL statements are:

Data Definition Language (DDL): CREATE, DROP, RENAME, ALTER, TRUNCATE

Data Manipulation Language (DML): INSERT, DELETE, UPDATE, MERGE

Data Control Language (DCL): GRANT, REVOKE

Data Retrieval: SELECT

Transaction Control Statements: COMMIT, SAVEPOINT, ROLLBACK

26. What is PL/SQL?

PL/SQL is an extension of SQL, which is used in Oracle. PL/SQL combines

SQL’s data manipulation power with the processing power of procedural
language and creates extra-powerful SQL queries. It uses SQL to instruct the
compiler what to do and a procedural way to instruct how to do it.
27. What is a Literal?

A Literal is a string consisting of a character, a number, or a date that is

included in the Select list but which is not a column name or alias.

28. Difference between SQL and iSQL*Plus?

SQL is a language, but iSQL*Plus is an environment. iSQL*Plus is a command

line tool that allows typing SQL commands to be executed directly against
the Oracle database. While SQL uses Functions to perform certain
formatting, iSQL*Plus uses commands to format data.

29. What are SQL functions?

SQL functions can take arguments but always return a certain value. The 2
types of SQL functions are:

Single-row functions like character, number, date, conversion, and general.

Multiple-row functions like avg, count, sum, max, min, stddev, variance.

Types of SQL General functions are:

NVL

NVL2

NULLIF

COALESCE

Conditional Expressions
30. What is a Sub Query?

A SELECT statement embedded within a clause of another SELECT statement

is called a subquery. It can be placed in WHERE, HAVING, and FROM clause.

Become a Software Development Professional



Full Stack Java Developer

 Kickstart Full Stack Java Developer career with industry-aligned curriculum by experts
 Hands-on practice through 20+ projects, assessments, and tests

6 months

View Program

Full Stack Web Developer - MEAN Stack

 Comprehensive Blended Learning program
 8X higher interaction in live online classes conducted by industry experts

11 months

View Program

Not sure what you’re looking for?View all Related Programs

31. What is VArray?

VArray (variable sized array) is an Oracle datatype used to hold columns

containing multi-valued attributes. It can hold a bounded array of values.

Advanced Oracle Interview Questions

32. What is the fastest query method to fetch data from a table?
Using the Row ID is the fastest query method to fetch data from a table.

33. What is the difference between hot backup and cold backup?

Hot backup is also called online backup, as it is done when the database is
active.

Cold backup is also called offline backup, as it is done when the database
has been shutdown.

34. What are the uses of a database trigger?

Triggers are programs that get automatically executed in response to some

events. For example, a trigger can be invoked when a row is inserted into a
table. It helps maintain data integrity.

35. What is the difference between TRANSLATE and REPLACE?

TRANSLATE is used to substitute a character by a character, while REPLACE

is used to substitute a character with a word.

36. What is Cross Join?

Cross join is the Cartesian product of records from the tables that are there
in the join