10+ MPC Motors Interview Questions and Answers

Using GROUP BY clause with aggregate functions can obtain distinct values without using the DISTINCT keyword.

• Use GROUP BY clause with aggregate functions like COUNT, SUM, AVG, etc.

• Example: SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

Exception handling is a mechanism to handle errors or unexpected events in a program.

• Syntax: BEGIN -- code block EXCEPTION -- exception handling block END;

• Exceptions can be predefined or user-defined

• Common predefined exceptions include NO_DATA_FOUND, TOO_MANY_ROWS, etc.

• User-defined exceptions can be declared using the EXCEPTION keyword

SQL is a query language used to interact with databases, while PL/SQL is a procedural language extension for SQL.

• SQL is used for querying and manipulating data in databases.

• PL/SQL is used for writing procedural code like loops, conditions, and functions.

• SQL statements are executed one at a time, while PL/SQL blocks can contain multiple statements.

• SQL is declarative, while PL/SQL is procedural.

• Example: SQL - SELECT * FROM employees; PL/SQL - DECLARE x NUMBER := 10; BEGIN DBMS_...read more

In performance tuning, init refers to the initialization parameter file used by Oracle Database to configure various settings.

• init is a text-based configuration file that contains parameters to optimize the performance of Oracle Database.

• It is used to set parameters such as memory allocation, parallel processing, and resource utilization.

• By tuning the init file, developers can improve the overall performance of the database.

• Example: adjusting the buffer cache size in the init...read more

Named and unnamed exceptions in PL/SQL with examples

• Named exceptions are user-defined exceptions with specific names like 'custom_exception'

• Unnamed exceptions are predefined exceptions like 'NO_DATA_FOUND' or 'TOO_MANY_ROWS'

• Named exceptions can be raised using RAISE statement with the exception name

• Unnamed exceptions are raised automatically by the system in case of errors

• Example of named exception: DECLARE custom_exception EXCEPTION; RAISE custom_exception;

• Example of unnamed...read more

Execution plan is a roadmap created by the database optimizer to determine the most efficient way to execute a query.

• Execution plan shows the steps the database will take to execute a query.

• Query optimization involves finding the most efficient way to execute a query.

• Optimization techniques include index usage, join methods, and access paths.

• Understanding execution plans helps in tuning queries for better performance.

Use a self-join query to delete duplicates from the same table.

• Use a self-join query to identify the duplicate records based on a unique identifier column.

• Delete the duplicate records using the DELETE statement with the self-join condition.

A cursor is a pointer to a result set for a SQL query. It allows you to iterate through the rows of the result set.

• Cursors are used in PL/SQL to process individual rows returned by a query.

• Attributes of a cursor include %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN.

• Example: OPEN cursor_name; FETCH cursor_name INTO variable; CLOSE cursor_name;

Triggers are PL/SQL blocks that are automatically executed when certain events occur on a table.

• Triggers can be used to enforce business rules, audit changes, or maintain data integrity.

• Syntax: CREATE OR REPLACE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW BEGIN -- trigger logic here END;

SQL is a structured query language used to communicate with databases. SQL extensions add additional functionality to the language.

• SQL is a standard language used to interact with databases

• SQL extensions like PL/SQL add procedural programming capabilities to SQL

• PL/SQL is an extension of SQL used in Oracle databases

Query to display 5th max salary in a table

• Use the RANK() function to assign a rank to each salary

• Filter the results to only show the row with rank 5

• Order the salaries in descending order to get the 5th max salary

Performance tuning is the process of optimizing the speed and efficiency of a system or application.

• Identifying and resolving bottlenecks in the system

• Optimizing SQL queries and database design

• Improving code efficiency and reducing resource usage

• Monitoring system performance and making adjustments as needed

Procedure is used to perform an action, while function returns a value.

• Procedure does not return a value, while function does

• Functions can be used in SQL queries, procedures cannot

• Functions can be called from SQL statements, procedures cannot

Packages and types are two important components in PL/SQL used for organizing and defining reusable code and data structures.

• Packages are containers for related procedures, functions, variables, and other PL/SQL constructs.

• Types are user-defined data structures that can be used to define new data types in PL/SQL.

• Packages can contain types as well as other PL/SQL constructs, providing a way to organize and encapsulate related code and data.

• Types can be used to define custom da...read more

Merge statement in PL/SQL combines insert and update operations based on a condition.

• Use MERGE INTO statement followed by target table name

• Specify USING clause with source table or subquery

• Define ON condition to match rows for update or insert

• Use WHEN MATCHED THEN UPDATE SET for update operation

• Use WHEN NOT MATCHED THEN INSERT VALUES for insert operation

SQL is a query language used to interact with databases, while PL/SQL is a procedural language extension for SQL.

• SQL is used for querying and manipulating data in databases.

• PL/SQL is used for writing procedural code like loops, conditions, and functions.

• SQL statements are executed one at a time, while PL/SQL blocks can contain multiple statements.

• PL/SQL can be used to create stored procedures, functions, and triggers in the database.

• SQL is declarative, while PL/SQL is procedu...read more