1. What is the difference between DELETE, TRUNCATE and DROP in SQL?
Ans:
In SQL DELETE can be rolled back and eliminates rows from a table according to a criterion, whereas TRUNCATE removes all rows from a table and cannot be rolled back. DROP deletes the entire table structure, including its data and cannot be rolled back. The choice between these operations depends on the requirement for data removal and the need for rollback capabilities.
2. What are SQL query optimization techniques you have used in your projects?
Ans:
SQL query optimization involves various techniques to improve query performance. Using appropriate indexes, avoiding unnecessary columns in SELECT statements and minimizing the use of subqueries can enhance performance. Additionally, analyzing execution plans and rewriting queries to reduce complexity are effective strategies.
3. What kinds of indexes are there in SQL? When is it inappropriate to use an index?
Ans:
SQL supports several types of indexes, including unique, composite and full-text indexes. Indexes should not be used on columns with low cardinality, such as boolean values, as they do not improve performance and can degrade it. Additionally, indexes should be used judiciously, as they can slow down data modification operations.
4. Explain the concept of joins in SQL.
Ans:
In SQL, joins are used to group rows from two or more tables together according to a common column. The primary types of joins are Each of the following joins: INNER, LEFT, RIGHT, and FULL OUTER serving different purposes in retrieving data. Understanding joins is crucial for querying relational databases effectively.
5. What is a correlated subquery?
Ans:
A correlated subquery is a subquery refers to columns from the outer query. Unlike a regular subquery, which is independent, for every row that the outer query processes, a correlated subquery is examined once. This type of subquery is useful for comparing each row to a set of values.
6. What are cursors in PL/SQL? Explain the different types.
Ans:
Cursors in PL/SQL are pointers to context areas that store the SQL statement and its processing information. There are two types: implicit cursors, which are automatically created by Oracle for SQL DML statements and explicit cursors, which are defined by the programmer for complex queries requiring multiple fetches.
7. What is exception handling in PL/SQL?
Ans:
Exception handling in PL/SQL is a mechanism to handle runtime errors allowing the program to continue execution or fail gracefully It involves using predefined exceptions like no data found or too many rows and user defined exceptions to manage errors effectively.
8. What is performance tuning in SQL?
Ans:
Performance tuning in SQL involves optimizing queries to improve execution speed and resource utilization. Techniques include analyzing and optimizing execution plans, using appropriate indexes and rewriting queries to reduce complexity. Regular monitoring and adjustments are essential for maintaining optimal performance.
9. What is the difference between a procedure and a function in PL/SQL?
Ans:
In PL/SQL, a procedure is a named block of code performs a specific task but does not return a value, whereas A designated chunk of code that carries out a particular purpose is called a function returns a value. SQL expressions can employ functions, but procedures are invoked using the EXECUTE command.
10. What are packages in PL/SQL?
Ans:
Packages in PL/SQL are groups of related procedures, functions, variables and other elements are stored together. They promote modularity, encapsulation and reusability of code. Packages consist of a specification which declares the elements and a body.
