SMC Corporation Plsql Developer Interview Questions and Answers

Q1. What is the difference between varchar and varchar2?
Ans. 
VARCHAR can store up to 2000 bytes while VARCHAR2 can store up to 4000 bytes.
• VARCHAR can store variable-length character strings up to 2000 bytes.

• VARCHAR2 can store variable-length character strings up to 4000 bytes.

• VARCHAR will occupy space for NULL values while VARCHAR2 will not.

• VARCHAR2 is more efficient in terms of storage and performance compared to VARCHAR.
Answered by AI
Add your answer
Q2. Explain composite indexes
Ans. 
Composite indexes are indexes that are created on multiple columns in a database table.
• Composite indexes improve query performance for queries that involve multiple columns

• They can be created using the CREATE INDEX statement in SQL

• Example: CREATE INDEX idx_name ON table_name (column1, column2)
Answered by AI
Add your answer

Q1. PLSQL basic to advanced.
Add your answer
Q2. Explain collections in details?
Ans. 
Collections in PL/SQL are data structures used to store multiple elements of the same data type.
• Collections can be nested tables, varrays, or associative arrays.

• Nested tables are like one-dimensional arrays with no upper bounds.

• Varrays are like arrays with a maximum size that is specified at declaration.

• Associative arrays are like lookup tables where each element is accessed using a unique key.

• Collections can be used t...read more
Answered by AI
Add your answer
Q3. Bulk collect concept
Ans. 
Bulk collect is a feature in PL/SQL that allows for fetching multiple rows of data at once.
• Improves performance by reducing context switches between the PL/SQL engine and SQL engine

• Reduces network traffic by fetching multiple rows in a single round trip

• Can be used with SELECT INTO or FETCH INTO statements
Answered by AI
Add your answer
Q4. Decode and case
Add your answer
Q5. Tuning of sql and some sql code
Add your answer

Bank product related questions and carrer expericnce .

Q1. What is temp table and temp variable in plsql?
Ans. 
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...read more
Answered by AI
View 5 more answers
Q2. What is mutating table or mutating trigger?
Ans. 
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 mut...read more
Answered by AI
View 4 more answers
Q3. What is autonomous transaction?
Ans. 
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 ...read more
Answered by AI
View 6 more answers
Q4. Cursors definition and types explained
Ans. 
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 compar...read more
Answered by AI
View 1 more answer
Q5. Explain about Triggers ?
Ans. 
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 updat...read more
Answered by AI
View 3 more answers
Q6. Truncate vs delete difference?
Ans. 
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.
Answered by AI
View 2 more answers
Q7. A plsql programme to print 103,99,96...3?
View 7 more answers
Q8. What is procedure in plsql and it's syntax and difference between procedure and function?
Ans. 
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 val...read more
Answered by AI
View 6 more answers

Q1. What are your salary expectations?
Add your answer
Q2. Share details of your previous job.
Add your answer
Q3. Tell me about yourself.
Add your answer
Q4. How do you find if two table having similer data
Ans. 
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...read more
Answered by AI
View 2 more answers
Q5. Difference between having and group by?
Ans. 
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;
Answered by AI
View 1 more answer

Q1. It was good but not selected
Add your answer
Q2. How to delete duplicate records
Ans. 
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.
Answered by AI
Add your answer

Q1. What is collation
Ans. 
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
Answered by AI
View 1 more answer

Q1. SQL queries, joins, views, functions , advanced functions, exceptions.. Lot of Scenario based question
Add your answer
Q2. About resume and past experiences
Add your answer
Q3. Interview happened almost 1hr
Add your answer

Q1. After 30 days.. Do u have any other offer in hand? How much they offered
Add your answer

Q1. Types of partition in sql
Add your answer
Q2. Can we edit data in complex views, will the underlying table be effected?
Add your answer
Q3. How do you do performance tuning?
Add your answer
Q4. Tell about regex
Add your answer

It is little bit easy

2 coding question in python