Ans. SQL, or Structured Query Language, is a standardized programming language used for managing and manipulating relational databases. It allows users to perform various operations such as querying data, updating records, inserting new data, and deleting existing data. SQL is widely used in database management systems like Oracle, MySQL, PostgreSQL, and Microsoft SQL Server. It provides a powerful way to interact with databases and is essential for data analysis and application development.

Ans. A relational database is a type of database that stores data in structured formats using rows and columns. Each table in a relational database represents a specific entity, and relationships between tables are established through foreign keys. This structure allows for efficient data retrieval and manipulation, ensuring data integrity and reducing redundancy. Relational databases use a schema to define the structure of the data, which helps maintain consistency and organization.

Ans. A primary key is a unique identifier for a record in a table, ensuring that no two rows have the same value in that column. A foreign key, on the other hand, is a field (or collection of fields) in one table that uniquely identifies a row of another table, establishing a relationship between the two tables. The primary key enforces entity integrity, while the foreign key enforces referential integrity between the tables.

Ans. The different types of joins in SQL include:

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table. If there is no match, NULL values are returned for columns from the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table. If there is no match, NULL values are returned for columns from the left table.
• FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table records. If there is no match, NULL values are returned for non-matching rows from both tables.
• CROSS JOIN: Returns the Cartesian product of the two tables, meaning every row from the first table is combined with every row from the second table.

Ans. Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller ones and defining relationships between them, typically through the use of foreign keys. The main goals of normalization are to eliminate duplicate data and ensure that data dependencies make sense.

Ans. Denormalization is the process of combining tables to improve read performance, often at the cost of increased redundancy. It is used in scenarios where read operations are more frequent than write operations, allowing for faster data retrieval.

Ans. A stored procedure is a set of SQL statements that can be stored in the database and executed as a single unit. It can perform operations such as modifying data and returning results, and it can accept parameters. Stored procedures are used for data validation, encapsulation, and reusability.

Ans. A function is a stored program in SQL that returns a single value. Functions are often used for calculations or data transformations. Unlike stored procedures, functions are required to return a value and can be used in SQL expressions, such as SELECT statements.

Ans. An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage and maintenance time. Indexes are created on columns that are frequently used in queries to allow for faster access to data.

Ans. ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are processed reliably:

• Atomicity: Ensures that all operations in a transaction are completed successfully or none at all.
• Consistency: Ensures that a transaction takes the database from one valid state to another valid state.
• Isolation: Ensures that the operations of a transaction are isolated from other transactions.
• Durability: Ensures that once a transaction is committed, it cannot be undone, even in the event of a system failure.

Ans. A trigger is a database object that is automatically executed or fired when certain events occur in a database. Triggers are often used to enforce business rules, validate data, or maintain audit logs. They can be set to fire after INSERT, UPDATE, or DELETE operations.

Ans. A view is a virtual table in SQL that consists of a stored query. It is used to simplify complex queries by representing them as a table. Views can be used to restrict access to certain columns or rows of data, and they can encapsulate complex joins or aggregations.

Ans. A schema is the structure that defines the organization of a database. It includes definitions of tables, views, indexes, and relationships between tables. It provides a blueprint for how the data is stored and accessed.

Ans. A subquery is a query embedded within another query. It is used to return results that are used by the outer query. Subqueries can be used in SELECT, INSERT, UPDATE, or DELETE statements.

Ans. The difference between UNION and UNION ALL is:

• UNION: Combines the result sets of two queries and removes duplicates.
• UNION ALL: Combines the result sets of two queries but does not remove duplicates.

Ans. The difference between DELETE and TRUNCATE is:

• DELETE: Removes rows from a table one by one, and it can be rolled back.
• TRUNCATE: Removes all rows from a table but cannot be rolled back. It is faster than DELETE as it does not log individual row deletions.

Ans. A composite key is a combination of two or more columns in a table that together uniquely identify a record. It is used when a single column is not sufficient to uniquely identify a record in a table.

Ans. Aggregate functions are functions that operate on a set of rows and return a single value. Common aggregate functions include:

• COUNT: Returns the number of rows in a set.
• SUM: Returns the sum of a numeric column.
• AVG: Returns the average value of a numeric column.
• MIN: Returns the smallest value in a column.
• MAX: Returns the largest value in a column.

Ans. A cascade delete is a type of referential action in a foreign key relationship where, if a record in the parent table is deleted, the corresponding records in the child table are automatically deleted as well.

Ans. A database transaction is a sequence of one or more SQL operations that are executed as a single unit. A transaction ensures that all operations are completed successfully; otherwise, none of the changes are committed to the database, ensuring consistency.

Ans. A clustered index determines the physical order of data in a table. A table can only have one clustered index because the data rows can only be sorted in one way. It is typically created on the primary key of the table.

Ans. A non-clustered index is a separate structure from the data table that provides a quick way to look up data. It does not alter the physical order of the data but instead stores pointers to the data rows.

Ans. A data dictionary is a set of tables that stores metadata about the database. It includes information about tables, columns, data types, indexes, constraints, and relationships in the database.

Ans. SQL injection is a security vulnerability that allows an attacker to interfere with the SQL queries an application makes to its database. It occurs when user input is incorrectly filtered for string literal escape characters embedded in SQL queries.

Ans. A composite index is an index that is created on multiple columns of a table. It is used to speed up queries that filter or sort on more than one column.

Ans. The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, often used with aggregate functions like COUNT, SUM, AVG, etc.

Ans. The HAVING clause is used to filter records after they have been grouped by the GROUP BY clause. It is used to apply a condition to groups, unlike the WHERE clause which filters individual rows before grouping.

Ans. A database trigger is a special kind of stored procedure that is automatically invoked in response to a specific event in the database, such as an INSERT, UPDATE, or DELETE operation on a table.

Ans. A CTE (Common Table Expression) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It is used to simplify complex joins and subqueries and improve query readability.

Ans. A LEFT JOIN returns all records from the left table and matching records from the right table, while a RIGHT JOIN returns all records from the right table and matching records from the left table. If there is no match, NULL values are returned for non-matching rows.

Ans. PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural extension to SQL. It combines SQL's data manipulation power with procedural constructs like loops, conditions, and exception handling, enabling more complex and powerful database programming.

Ans. Features of PL/SQL include procedural constructs (loops, conditions), error handling using exceptions, support for variables and constants, modularity through procedures and functions, tight integration with SQL, and improved performance through block processing.

Ans. A PL/SQL block is the basic unit of a PL/SQL program. It consists of three parts: the declaration section, executable section, and exception-handling section. Each block can be anonymous or named (procedure/function).

Ans. The types of PL/SQL blocks are:

• Anonymous Blocks
• Named Blocks (Procedures, Functions)
• Packages
• Triggers

Ans. A cursor is a pointer that allows row-by-row processing of query results. There are two types: implicit cursors (automatically managed by PL/SQL) and explicit cursors (defined and controlled by the programmer).

Ans. Implicit cursors are automatically created for single SQL statements like SELECT INTO, while explicit cursors are defined for queries that return multiple rows, allowing more control over processing.

Ans. A parameter in PL/SQL is used to pass values into and out of procedures and functions. Parameters can be IN (input only), OUT (output only), or IN OUT (both input and output).

Ans. An exception is a runtime error that interrupts the normal flow of execution. PL/SQL provides predefined exceptions (like NO_DATA_FOUND) and allows user-defined exceptions for custom error handling.

Ans. Exceptions are handled using the EXCEPTION block within a PL/SQL program. Specific exceptions can be caught and handled using WHEN clauses, and an OTHERS clause can catch all unhandled exceptions.

Ans. A stored procedure is a named PL/SQL block stored in the database that performs a specific task. It can take input/output parameters and be called multiple times, promoting code reuse and modular programming.

Ans. A function is similar to a procedure but must return a single value using the RETURN statement. Functions can be used in SQL expressions, unlike procedures.

Ans. A package is a collection of related procedures, functions, variables, and other PL/SQL constructs grouped together. It consists of a specification (declaration) and a body (implementation).

Ans. Benefits include modular code organization, easier maintenance, encapsulation of logic, performance improvement through pre-compilation, and support for global variables.

Ans. A trigger is a PL/SQL block that is automatically executed in response to specific database events such as INSERT, UPDATE, or DELETE operations on a table or view.

Ans. The key difference is that a function must return a value, while a procedure does not. Functions can be used in SQL statements, whereas procedures cannot be used in SELECT queries.

Ans. A record is a composite data type in PL/SQL that allows grouping of related data items of different types into a single unit. It’s similar to a struct in C or an object in OOP languages.

Ans. A constant is declared using the CONSTANT keyword. Example:
salary CONSTANT NUMBER := 5000;

Ans. The %TYPE attribute is used to declare a variable with the same data type as that of a column or another variable, ensuring consistency and reducing maintenance.

Ans. The %ROWTYPE attribute allows declaration of a record that represents an entire row of a table or cursor, matching its structure dynamically.

Ans. Autonomous transactions are independent transactions started from within another transaction. They can commit or roll back without affecting the main transaction.

Ans. PL/SQL code can be debugged using tools like DBMS_OUTPUT.PUT_LINE for tracing, or advanced tools like Oracle SQL Developer, TOAD, or using logging mechanisms and breakpoints.

Ans. Dynamic SQL allows the execution of SQL statements that are constructed at runtime. It is useful when the exact structure of the query is not known until runtime.

Ans. IN parameters pass values into a procedure/function, OUT parameters return values to the caller, and IN OUT parameters do both – receive input and return output.

Ans. The scope of a variable refers to the region of the code where the variable can be accessed. In PL/SQL, variables declared in a block are local to that block and its sub-blocks.

Ans. Performance can be improved using bulk processing (BULK COLLECT, FORALL), optimizing SQL queries, using appropriate indexing, avoiding unnecessary computations, and using packages.

Ans. BULK COLLECT is used to retrieve multiple rows with a single fetch, improving performance by reducing context switching between SQL and PL/SQL engines.

Ans. FORALL is a PL/SQL feature that allows bulk DML operations. It improves performance by minimizing context switches during operations like INSERT, UPDATE, or DELETE.

Ans. CHAR is a fixed-length data type, padding unused space with blanks, while VARCHAR2 is variable-length, storing only the actual characters. VARCHAR2 is more efficient for variable-length data.

Ans. SAVEPOINT sets a point within a transaction to which you can later roll back, allowing partial rollbacks without affecting the entire transaction.

Ans. ROLLBACK undoes all changes in the current transaction, while ROLLBACK TO SAVEPOINT undoes changes only up to a previously set savepoint, preserving earlier work.

Ans. A collection is a data structure that holds multiple elements of the same type. PL/SQL supports three types: associative arrays, nested tables, and varrays.

Ans. An associative array (formerly known as PL/SQL table) is a set of key-value pairs where keys are unique and can be integers or strings. It is used for temporary in-memory data storage.

Ans. A nested table is a collection type that can be stored in the database as a column of a table. It allows dynamic resizing and can be manipulated with SQL operations.

Ans. A VARRAY (variable-size array) is a collection type with a fixed maximum size. It is stored as a single object in the database and used when the number of elements is known in advance.

Ans. A cursor FOR loop automatically handles cursor open, fetch, and close. Syntax:
FOR record IN cursor_name LOOP ... END LOOP;

Ans. A REF CURSOR is a cursor variable that allows dynamic query execution and returning result sets from procedures/functions to the client or calling programs.

Ans. User-defined exceptions are custom error handlers declared in the DECLARE section and raised using the RAISE statement to handle specific business logic errors.

Ans. PRAGMA EXCEPTION_INIT associates an exception name with an Oracle error number, allowing you to handle specific errors with a named exception.

Ans. DELETE is DML and can be rolled back; it allows WHERE clauses. TRUNCATE is DDL, cannot be rolled back, and removes all rows quickly without logging individual row deletions.

Ans. Local variables are declared within a PL/SQL block or subprogram and accessible only there. Global variables are declared in packages and can be accessed across procedures/functions.

Ans. SYSDATE returns the current date and time with second precision. SYSTIMESTAMP returns the current date and time with fractional seconds and time zone information.

Ans. RAISE is used to re-raise or throw predefined/user-defined exceptions. RAISE_APPLICATION_ERROR is used to generate custom error messages with user-defined error numbers.

Ans. A PL/SQL table is an older term for an associative array—a single-dimensional, unbounded, and sparse collection used for temporary in-memory data storage.

Ans. Triggers are used for enforcing business rules, auditing, data validation, and automatically updating related data in response to DML events on tables or views.

Ans. Compound triggers are special triggers that allow combining multiple timing points (BEFORE/AFTER INSERT/UPDATE/DELETE) in a single trigger body, useful for row-level operations requiring shared state.

Ans. CONTINUE skips the remaining statements in the current iteration of a loop and proceeds to the next iteration, similar to continue in other programming languages.

Ans. EXIT terminates a loop or block early, while GOTO transfers control to a labeled statement unconditionally, which can reduce readability and is generally discouraged.

Ans. Use NVL, NVL2, COALESCE, and CASE expressions to handle NULL values effectively. PL/SQL also supports IS NULL and IS NOT NULL conditions.

Ans. INSTEAD OF triggers are used on views to define DML behavior. AFTER triggers are used on tables to execute logic after the DML operation completes.

Ans. DBMS_SQL is a PL/SQL package that allows dynamic SQL execution at runtime, including parsing, binding, and executing SQL statements where the structure is unknown at compile time.

Ans. Bulk binding allows PL/SQL to transfer data between SQL and PL/SQL in batches using the BULK COLLECT and FORALL statements, improving performance by reducing context switches.

Ans. FORALL is used for bulk DML operations in PL/SQL. It executes DML statements like INSERT, UPDATE, or DELETE multiple times with different values efficiently using collections.

Ans. BULK COLLECT retrieves multiple rows from a query into a collection in a single context switch, improving performance for large data retrieval operations.

Ans. A package is a schema object that groups logically related PL/SQL types, variables, procedures, and functions into a single unit. It has a specification and a body.

Ans. The package specification declares the public elements (procedures, functions, constants), while the package body contains their implementations. The body is optional if only declarations exist.

Ans. The initialization section in a package body runs only once per session when the package is first referenced. It's used to initialize variables or perform startup actions.

Ans. Errors can be tracked using the EXCEPTION section with SQLCODE and SQLERRM functions, and by logging errors using custom logging tables or DBMS_OUTPUT/DBMS_APPLICATION_INFO.

Ans. DBMS_OUTPUT.PUT_LINE is used for displaying output from PL/SQL blocks, commonly used for debugging and displaying intermediate values in anonymous blocks or procedures.

Ans. AUTONOMOUS_TRANSACTION allows a procedure, function, or trigger to execute independently of the main transaction, enabling commits or rollbacks without affecting the calling transaction.

Ans. A cursor variable (REF CURSOR) is a pointer to a query result set. It allows passing query results between procedures/functions or to clients dynamically.

Ans. Implicit cursors are automatically created by Oracle for DML and SELECT INTO statements. Explicit cursors are defined by the user to control query processing explicitly.

Ans. A mutating table error occurs when a row-level trigger tries to read or modify the same table that fired the trigger, violating Oracle's read-consistency rules.

Ans. Avoid using row-level triggers for querying or modifying the table. Use compound triggers or statement-level triggers, or store intermediate data in collections or temporary tables.

Ans. A stored procedure is a PL/SQL subprogram stored in the database and can be called by name to perform a specific task. It may or may not return a value.

Ans. A stored function is similar to a procedure but always returns a single value and can be used in SQL expressions.

Ans. Bind variables are placeholders in SQL statements that are replaced with actual values at runtime. They improve performance and prevent SQL injection.

Ans. You can schedule PL/SQL procedures using the DBMS_SCHEDULER or DBMS_JOB packages to automate execution at specified intervals or times.

Ans. DBMS_JOB is the older job scheduling utility. DBMS_SCHEDULER offers more advanced and flexible scheduling options like calendaring, job chaining, and resource management.

Ans. Dynamic SQL allows SQL statements to be constructed and executed at runtime using EXECUTE IMMEDIATE or DBMS_SQL. It supports flexible query building.

Ans. PL/SQL code is compiled into bytecode that is stored in the database. Compilation includes syntax checking, semantic analysis, and code generation.