20+ Vestas Interview Questions and Answers

A procedure in PL/SQL is a named block of code that can be called and executed multiple times.

• Syntax: CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode1] datatype1 [, parameter2 [mode2] datatype2]...)] IS

• Difference between procedure and function: Procedures do not return a value, while functions return a value.

• Procedures are used to perform an action, while functions are used to calculate and return a value.

• Procedures can have OUT or IN OUT parameters to pass va...read more

Temp table is a table created temporarily in memory. Temp variable is a variable that holds temporary data.

• Temp table is used to store data temporarily during a session

• Temp variable is used to hold temporary data that is not needed after a certain point

• Temp table and variable are created using the 'CREATE GLOBAL TEMPORARY' and 'DECLARE' statements respectively

• Example: CREATE GLOBAL TEMPORARY TABLE temp_table (id NUMBER, name VARCHAR2(50));

• Example: DECLARE temp_var VARCHAR2(50...read more

PL/SQL program to print numbers in descending order from 103 to 3

• Use a loop to iterate from 103 to 3

• Print each number in the loop

• Decrement the loop counter by 3 in each iteration

A mutating table or mutating trigger occurs when a trigger tries to update a table that is currently being modified.

• Mutating table occurs when a trigger references the table that is being modified.

• It can happen when a trigger is fired by an INSERT, UPDATE, or DELETE statement on the table.

• This can lead to unpredictable results or errors, such as ORA-04091: table is mutating, trigger/function may not see it.

• To avoid mutating table errors, use row-level triggers instead of stat...read more

To find if two tables have similar data, compare the records in both tables using a join or a subquery.

• Use a join operation to compare the records in both tables based on a common column.

• If the tables have a primary key, you can join them on that key to check for similar data.

• Alternatively, you can use a subquery to compare the data in both tables and check for matching records.

• Consider using the MINUS operator to find the differences between the two tables.

• You can also use t...read more

Autonomous transaction is a separate transaction initiated by a parent transaction.

• It allows a subtransaction to commit or rollback independently of the parent transaction.

• It is useful for logging or auditing purposes.

• It can be created using the PRAGMA AUTONOMOUS_TRANSACTION statement.

• Example: A parent transaction updates a table, while an autonomous transaction logs the changes made.

• Example: An autonomous transaction sends an email notification after a parent transaction com...read more

HAVING is used to filter groups while GROUP BY is used to group rows based on a column.

• HAVING is used with GROUP BY to filter groups based on a condition

• GROUP BY is used to group rows based on a column

• HAVING is used after GROUP BY in a query

• GROUP BY is used before HAVING in a query

• Example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000;

Truncate removes all data, delete removes selected data.

• Truncate is faster than delete as it doesn't log individual row deletions.

• Truncate cannot be rolled back, delete can be.

• Truncate resets identity columns, delete doesn't.

• Truncate doesn't fire triggers, delete does.

• Truncate is a DDL operation, delete is a DML operation.

SQL optimization, normalization, and pragma usage are important concepts in PL/SQL development.

• SQL optimization involves improving the performance of SQL queries by analyzing and modifying the query structure, indexes, and data access patterns.

• Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

• Pragma is a compiler directive that provides additional information to the compiler to optimize code performance.

• Examples of p...read more

Triggers are database objects that automatically execute in response to certain events.

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

• They can be defined to execute before or after an event, such as a row being inserted, updated, or deleted.

• Triggers can also be nested, meaning one trigger can execute another trigger.

• Examples of triggers include automatically updating a timestamp when a row is modified, or preventing a de...read more

Cursors are used to retrieve and manipulate data from a database in PL/SQL.

• Cursors are like pointers to a result set, allowing us to fetch and process rows one by one.

• There are two types of cursors: implicit and explicit.

• Implicit cursors are automatically created by Oracle when executing a SQL statement.

• Explicit cursors are declared and used by the programmer.

• Explicit cursors provide more control and flexibility compared to implicit cursors.

• Cursors can be used to fetch data f...read more

A cursor is a database object used to retrieve data from a result set one row at a time.

• Types of cursors: Implicit, Explicit, Ref, and Dynamic

• Implicit cursor is used for single row queries

• Explicit cursor is used for multi-row queries

• Ref cursor is used to point to a cursor variable

• Dynamic cursor is used to execute dynamic SQL statements

Explanation of triggers and their types in PL/SQL

• Triggers are database objects that are automatically executed in response to certain events

• Types of triggers include DML, DDL, and system triggers

• Syntax for creating a trigger: CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table_name [FOR EACH ROW] [WHEN condition] BEGIN ... END;

A temporary variable used to store data during program execution.

• Temp variables are used to hold data temporarily during program execution.

• They are typically used in loops or conditional statements.

• Once the program execution is complete, the temp variable is no longer needed.

• Example: int temp = 0; for(int i=0; i<10; i++) { temp += i; }

• In this example, the temp variable is used to store the sum of the numbers 0-9.

To delete duplicate records in PL/SQL, use a combination of SELECT DISTINCT and DELETE statements.

• Identify duplicate records using SELECT DISTINCT with a COUNT(*) function.

• Use a DELETE statement with a subquery to remove duplicate records.

• Consider creating a temporary table to store unique records before deleting duplicates.

I have a strong ability to learn new software quickly.

• I am a self-motivated learner and enjoy exploring new technologies.

• I am comfortable with online tutorials, documentation, and experimenting with new software.

• I have experience learning new software such as Oracle SQL Developer, Toad, and PL/SQL Developer.

• I am able to adapt to new software quickly and efficiently.

Table size can be obtained by querying the data dictionary views in SQL.

• Use the query 'SELECT table_name, round((num_rows * avg_row_len)/1024/1024,2) as size_mb FROM all_tables WHERE table_name = 'YOUR_TABLE_NAME';'

• Alternatively, you can use the query 'SELECT segment_name, bytes/1024/1024 as size_mb FROM user_segments WHERE segment_name = 'YOUR_TABLE_NAME';'

Collation is the set of rules determining how data is sorted and compared in a database.

• Collation defines the order in which characters are sorted and compared in a database

• It includes rules for comparing characters with diacritics, case sensitivity, and special characters

• Different collations can affect sorting order and comparison results

Delete removes specific rows from a table, drop removes the entire table, truncate removes all rows from a table.

• Delete is a DML command used to remove specific rows from a table based on a condition.

• Drop is a DDL command used to remove an entire table along with its structure and data.

• Truncate is a DDL command used to remove all rows from a table but keeps the table structure intact.

• Delete can be rolled back, drop cannot be rolled back, truncate cannot be rolled back.

• Example...read more