D.D.Chopra & Co Plsql Developer Interview Questions and Answers

Q1. EXPLAIN ABOUT UR PRJECT
Ans. 
Developed a PL/SQL project for managing inventory and sales data in a retail store.
• Designed database tables to store product information, sales transactions, and customer details.

• Implemented PL/SQL procedures and functions for adding, updating, and querying data.

• Created triggers to enforce business rules and maintain data integrity.

• Developed reports using PL/SQL queries to analyze sales performance and inventory levels
Answered by AI
Add your answer
Q2. How do you work on performance optimization
Ans. 
I work on performance optimization by analyzing query execution plans, indexing, and code refactoring.
• Analyzing query execution plans to identify bottlenecks

• Creating appropriate indexes to improve query performance

• Refactoring code to optimize resource usage

• Using tools like Explain Plan and SQL Tuning Advisor
Answered by AI
Add your answer

Q1. Factorial of a number
Ans. 
Factorial of a number is the product of all positive integers up to that number.
• To calculate factorial of a number, multiply all positive integers up to that number.

• Factorial of 0 is 1.

• Example: Factorial of 5 is 5*4*3*2*1 = 120.
Answered by AI
Add your answer
Q2. Sql queries including 2nd highest salary and table joins
Add your answer

Q1. Sql question and table joins
Add your answer
Q2. Modelling of schema
Ans. 
Modelling of schema involves designing the structure of a database to organize and represent data.
• Identify entities and their relationships

• Define attributes for each entity

• Establish primary and foreign keys

• Normalize the schema to reduce redundancy

• Consider performance and scalability
Answered by AI
Add your answer

Q1. What is view? Can we update in view?
Ans. 
A view is a virtual table created by a query. It does not store data itself but displays data from one or more tables. Views can be used for querying data as if it were a table.
• Views are created using a SELECT statement with optional joins, WHERE clauses, etc.

• Views can simplify complex queries by pre-defining joins and filters.

• Views do not store data themselves, they display data from underlying tables.

• Views can be upd...read more
Answered by AI
Add your answer
Q2. What is synonym?
Ans. 
A synonym is an alternative name for a table, view, sequence, or other database object.
• Synonyms can be used to provide a different name for an object, making it easier to reference.

• They can be created to reference objects in other schemas or databases.

• Synonyms can be public (accessible to all users) or private (accessible only to the creator).
Answered by AI
Add your answer
Q3. What is sequence
Ans. 
A sequence is a database object in Oracle that generates unique numbers in a specified order.
• Used to generate unique numbers for primary key columns

• Can be used in INSERT statements to automatically generate values

• Can be defined to increment by a specific value and start at a specific number
Answered by AI
Add your answer
Q4. What is procedures?
Ans. 
Procedures are named PL/SQL blocks that perform a specific task. They can accept parameters and return values.
• Procedures are stored in the database and can be called by other PL/SQL blocks or applications.

• They can accept input parameters to perform operations and can also return output values.

• Procedures help in modularizing code and promoting reusability.

• Example: CREATE PROCEDURE calculate_salary (emp_id IN NUMBER) IS
Answered by AI
Add your answer
Q5. What is difference between procedure and functions
Ans. 
Procedures do not return any value while functions return a value.
• Procedures are used to perform an action, while functions are used to calculate and return a value.

• Procedures do not have a return statement, while functions must have a return statement.

• Functions can be called from SQL queries, while procedures cannot be called directly in SQL queries.
Answered by AI
Add your answer
Q6. What is partitions?
Ans. 
Partitions in PL/SQL are used to divide a table into smaller, more manageable pieces.
• Partitions help improve query performance by allowing data to be stored in separate segments based on a specified criteria.

• Common partitioning methods include range, hash, list, and composite partitioning.

• Example: Partitioning a sales table by month can improve query performance when searching for sales data within a specific time fram
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. 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. 1.How to search a text in all database objects- search in all_source table 2.difference between procedure & function 3.difference between case & decode 4.write a query to delete duplicates from table 5.whi...read more
Add your answer

Q1. Waiting for round 1 results
Add your answer

It is little bit easy

2 coding question in python

Q1. Window Functions in SQL
Ans. 
Window functions in SQL are used to perform calculations across a set of table rows related to the current row.
• Window functions are used to calculate values based on a set of rows related to the current row.

• They allow you to perform calculations without grouping the rows into a single output row.

• Examples of window functions include ROW_NUMBER(), RANK(), and LAG().
Answered by AI
Add your answer

Q1. Uses of Packages in PL/SQL
Ans. 
Packages in PL/SQL are used to group related procedures, functions, variables, and other PL/SQL constructs together for better organization and security.
• Packages help in modularizing code for easier maintenance and debugging

• They provide encapsulation and hide implementation details

• Packages can contain multiple procedures, functions, variables, cursors, and exceptions

• They improve performance by reducing network traffic

• E...read more
Answered by AI
Add your answer

Q1. Procedures, functions, cursors, triggers
Add your answer
Q2. Tables, views, synonyms, sequences, constraints
Add your answer
Q3. Packages, having clause, group by clause
Add your answer
Q4. Rowid, rownumber, bulk bind, bulk exception, bulk collect, parameters
Add your answer
Q5. Predefined exception , nonpredefined exception, raise, raise application error
Add your answer
Q6. Truncate, delete, case, decode
Add your answer
Q7. Order by clause, advantages of views
Ans. 
Order by clause is used to sort the result set. Views provide security, abstraction and simplify complex queries.
• Order by clause sorts the result set based on specified column(s)

• Views provide a layer of abstraction and simplify complex queries

• Views can be used to provide security by limiting access to certain columns or rows

• Views can improve performance by precomputing complex queries

• Example: SELECT * FROM employees_vi...read more
Answered by AI
Add your answer
Q8. Max, greatest, on delete cascade
Add your answer
Q9. On delete set null, unique constraints
Ans. 
On delete set null is used to set foreign key values to null when referenced rows are deleted. Unique constraints ensure uniqueness of values.
• On delete set null is a referential action that can be specified when creating a foreign key constraint.

• When a referenced row is deleted, the foreign key value in the referencing table is set to null.

• Unique constraints ensure that no two rows have the same value in the specified ...read more
Answered by AI
Add your answer