Top SQL and PL/SQL Interview Questions on Indexing and Query Optimization
Focus your interview prep on key areas like indexing and query optimization with this collection of frequently asked SQL and PL/SQL interview questions and answers. #SQL #PLSQL #InterviewQuestions #Indexing #QueryOptimization #Database
Frequently Asked SQL and PL/SQL Interview Questions
This section covers common interview questions related to SQL (Structured Query Language) and PL/SQL (Procedural Language/SQL), focusing on database concepts, indexing, and query optimization.
SQL and PL/SQL Interview Questions and Answers
| 1) What is SQL? | SQL (Structured Query Language) is used to communicate with databases. It's used to retrieve, insert, update, and manage data in relational database management systems (RDBMS). |
| 2) What is the origin of CSS? | SQL's origins are in the 1970s; it's evolved over time. |
| 3) What are the different variations of SQL? | Many database systems have their own versions or dialects of SQL (e.g., MySQL's SQL, PostgreSQL's SQL, SQL Server's T-SQL, Oracle's PL/SQL). |
| 4) How to integrate CSS into a webpage? | There are three primary methods for applying CSS styles to HTML elements: Inline styles (within HTML elements), internal stylesheets (within the `<style>` tag in the document's ``), and external stylesheets (linking to a `.css` file using a `` tag). |
| 5) Advantages of using CSS? | Improved website performance, consistent site-wide styling, simpler updates, better accessibility, and clean separation of content (HTML) and style (CSS). |
| 6) Limitations of CSS? | Limited ability to select parent elements based on child properties, restricted control over positioning, lack of dynamic pseudo-class control, and limitations in how it targets and styles specific text ranges. |
| 7) What are CSS frameworks? | Pre-built libraries that simplify and standardize web page styling (e.g., Bootstrap, Tailwind CSS). |
| 8) Why are background and color separate properties? | For better readability and because `color` is an inherited property, while `background` is not. |
| 9) What is an embedded stylesheet? | A stylesheet defined within the `<style>` tag of an HTML document. |
| 10) Advantages of embedded stylesheets? | They are specific to that page; they don't require an additional HTTP request for the stylesheet. |
| 11) What is a CSS selector? | A pattern that matches HTML elements to apply specific styles. |
| 12) Basic CSS components? | Selectors, properties, and values. |
| 13) Purpose of CSS `opacity`? | Sets the transparency level of an element. |
| 14) What is the universal selector? | The asterisk (`*`) selects all HTML elements. |
| 15) Selecting all paragraph elements? | Use the `p` tag as a selector (e.g., `p { color: blue; }`). |
| 16) Purpose of the `%` unit in CSS? | Represents a percentage value relative to the parent element's size. |
| 17) Property for setting background color? | `background-color`. |
| 18) Property for controlling background image repetition? | `background-repeat`. |
| 19) Property for controlling background image position? | `background-position`. |
| 20) Property for controlling background image scrolling? | `background-attachment`. |
| 21) What is a ruleset in CSS? | A ruleset consists of a selector and a declaration block. |
| 22) Class selectors vs. ID selectors? | Class selectors target elements with a specific class; ID selectors target a single element with a unique ID. |
| 23) Advantages of external stylesheets? | Reusability of styles across pages, easier maintenance, improved organization. |
| 24) Inline vs. embedded vs. external stylesheets? | Inline: Styles within an HTML element. Embedded: Styles within the HTML `<style>` tag. External: Styles in a separate `.css` file. |
| 25) What is RWD (Responsive Web Design)? | Creating websites that adapt to different screen sizes and devices. |
| 26) Benefits of CSS sprites? | Reduces HTTP requests, improving page load times. |
| 27) Logical vs. presentational tags? | Logical tags describe the meaning of content; presentational tags describe its appearance. |
| 28) What is the CSS box model? | Defines the structure of an element (content, padding, border, margin). |
| 29) What is the CSS `float` property? | Allows elements to "float" to the left or right, causing text to wrap around them. |
| 30) How to restore default CSS property values? | Use the `initial` keyword. |
| What is a view in SQL? | A stored query that simplifies data retrieval; acts like a virtual table. |
| What is an index in SQL? | A data structure that speeds up data retrieval (like a book index). |
| Types of SQL indexes? | Unique, clustered, non-clustered, bitmap, composite, B-tree, function-based, and others. |
| What is a unique index? | Ensures uniqueness of values in a column (or set of columns). |
| What is a clustered index? | Determines the physical order of data in a table; one per table. |
| What is a non-clustered index? | Does not affect the physical storage order but improves search speed. |
| SQL, MySQL, and SQL Server differences? | SQL is a language. MySQL and SQL Server are database systems that use SQL. |
| SQL vs. PL/SQL differences? | SQL is a declarative query language; PL/SQL is a procedural language extending SQL's functionality. |
| Sorting with column aliases? | Yes, use the alias in the `ORDER BY` clause. |
| Clustered vs. non-clustered indexes? | Clustered indexes define the physical storage order; non-clustered indexes create a separate index structure. |
| Displaying the current date in SQL? | Use a built-in function like `GETDATE()` (SQL Server), `CURDATE()` (MySQL), or `NOW()` (PostgreSQL). |
| What is the `HAVING` clause? | Filters groups of rows based on aggregate functions (used with `GROUP BY`). |
| What is the `SELECT DISTINCT` clause? | Retrieves only unique rows from a table. |
| What is a self-join? | Joining a table to itself to compare rows within the same table. |
| What is `SERIAL` in PostgreSQL? | Creates auto-incrementing columns. |
Frequently Asked SQL and Database Interview Questions
This section covers common interview questions related to SQL (Structured Query Language) and database concepts. It's designed to help both beginners and experienced database developers prepare for technical interviews.
SQL and Database Interview Questions and Answers
| 25) What is an index in SQL? | A database structure that speeds up data retrieval by creating an index for each value. Indexes improve query performance but add overhead when updating the database. Indexes cannot have duplicate values. |
| 26) Types of SQL indexes? | Unique, clustered, non-clustered, bitmap, composite, B-tree, function-based indexes (and others depending on the specific database system). |
| 27) What is a unique index? | Ensures that all values in a column (or set of columns) are unique. It's often created automatically for primary keys. |
| 28) What is a clustered index? | Defines the physical order of rows in a table based on key values. A table can have only one clustered index. |
| 29) What is a non-clustered index? | Creates a separate index structure (not affecting physical storage order); improves search speed for specific columns. |
| 30) SQL, MySQL, SQL Server differences? | SQL is a language; MySQL and SQL Server are database systems that implement SQL (with their own variations). |
| 31) SQL vs. PL/SQL? | SQL is declarative and data-oriented; PL/SQL is procedural and application-oriented. |
| 32) Sorting with column aliases? | Yes, use the alias in the `ORDER BY` clause. |
| 33) Clustered vs. non-clustered indexes? | Clustered indexes define the physical storage order; non-clustered indexes create a separate index structure. |
| 34) Displaying the current date in SQL? | Use database-specific functions (e.g., `GETDATE()` in SQL Server, `CURDATE()` in MySQL, `NOW()` in PostgreSQL). |
| 35) What is the `CREATE VIEW` statement? | Creates a stored query that acts like a virtual table. |
| 36) What is the `HAVING` clause? | Filters groups of rows created by `GROUP BY`, often used with aggregate functions. |
| 37) What is the `SELECT DISTINCT` clause? | Retrieves only unique rows. |
| 38) What is a self-join? | Joining a table to itself to relate rows within the same table. |
| 39) What is `SERIAL` in PostgreSQL? | Creates auto-incrementing columns. |
| 40) What is the `COMMIT` statement? | Saves database changes permanently. |
| 41) What is the `ROLLBACK` statement? | Undoes database changes. |
| 42) What is a `SAVEPOINT` statement? | Creates a point within a transaction that allows you to partially rollback changes. |
| 43) What is a mutating table error? | Occurs when a trigger modifies a table that is currently being accessed by the trigger itself. |
| 44) What is data consistency? | Ensures all users see the same, accurate data. |
| 45) What is a cursor in PL/SQL? | A temporary work area to process data from a query row-by-row. |
| 46) Types of cursors in PL/SQL? | Implicit and explicit cursors. |
| 47) What is an upsert operation? | Combines `INSERT` and `UPDATE` functionality. |
| 48) `ON DELETE` actions in PostgreSQL? | `NO ACTION`, `RESTRICT`, `SET NULL`, `SET DEFAULT`, `CASCADE`. |
| 49) What are ACID properties? | Atomicity, Consistency, Isolation, Durability—guaranteeing reliable transactions. |
| 50) NULL vs. blank space or zero? | `NULL` represents missing or unknown data; blank space and zero are actual values. |
| 51) What are SQL functions? | Reusable code blocks for data manipulation (user-defined and system-defined). |
| 52) Case manipulation functions in SQL? | `LOWER()`, `UPPER()`, `INITCAP()`. |
SQL Interview Questions and Answers
| 25) What is an SQL index? | An index is a database structure that speeds up data retrieval by creating a separate lookup table. It's like an index in a book; it points directly to the data you're looking for, making it much faster than searching through the entire dataset. |
| 26) Types of SQL indexes? | Common types include unique, clustered, non-clustered, bitmap, composite, B-tree, and function-based indexes (specific types may vary by database system). |
| 27) What is a unique index? | Ensures that all values in one or more columns are unique. Often used for primary keys. |
| 28) What is a clustered index? | Defines the physical storage order of data rows in a table. A table can only have one clustered index. |
| 29) What is a non-clustered index? | A separate index structure that doesn't change the physical storage but speeds up searches. |
| 30) SQL, MySQL, SQL Server differences? | SQL is the language; MySQL and SQL Server are different database management systems that use SQL (with their own variations and features). |
| 31) SQL vs. PL/SQL? | SQL is declarative (focuses on *what* data to retrieve); PL/SQL is procedural (focuses on *how* to process data using programming constructs). |
| 32) Sorting with column aliases? | Yes, use the alias in the `ORDER BY` clause. |
| 33) Clustered vs. non-clustered indexes (again)? | Clustered indexes determine the physical order of data; non-clustered indexes create a separate index structure. |
| 34) SQL query for the current date? | Use a database-specific function (e.g., `GETDATE()` in SQL Server, `CURDATE()` in MySQL, `NOW()` in PostgreSQL). |
| 35) What is the `CREATE VIEW` statement? | Creates a stored query that acts like a virtual table, simplifying data access. |
| 36) What is the `HAVING` clause? | Filters groups of rows after aggregation (used with `GROUP BY`). |
| 37) What is `SELECT DISTINCT`? | Retrieves only unique rows, eliminating duplicates. |
| 38) What is a self-join? | Joining a table to itself to find relationships within the same table. |
| 39) What is `SERIAL` in PostgreSQL? | Creates auto-incrementing integer columns. |
| 40) What is the `COMMIT` statement? | Saves database changes permanently. |
| 41) What is the `ROLLBACK` statement? | Undoes database changes made within a transaction. |
| 42) What is a `SAVEPOINT` statement? | Creates a point within a transaction for partial rollbacks. |
| 43) What is a mutating table error? | Occurs when a trigger tries to modify the same table it's currently using. |
| 44) What is data consistency? | Ensures all users see the same accurate data, even during concurrent transactions. |
| 45) What is a cursor in PL/SQL? | A temporary work area used to process query results row by row. |
| 46) Types of cursors in PL/SQL? | Implicit and explicit cursors. |
| 47) What is an upsert operation? | Combines `INSERT` and `UPDATE` operations. |
| 48) `ON DELETE` actions in PostgreSQL? | `NO ACTION`, `RESTRICT`, `SET NULL`, `SET DEFAULT`, `CASCADE`. |
| 49) What are ACID properties? | Atomicity, Consistency, Isolation, Durability—guaranteeing reliable transactions. |
| 50) `NULL` vs. blank space or zero? | `NULL` means missing or unknown data; blank and zero are actual values. |
| 51) What are SQL functions? | Reusable code blocks for data manipulation (user-defined and built-in). |
| 52) Case manipulation functions? | `LOWER()`, `UPPER()`, `INITCAP()`. |
| 53) Character manipulation functions? | `CONCAT()`, `SUBSTR()`, `LENGTH()`, `INSTR()`, `LPAD()`, `RPAD()`, `TRIM()`, `REPLACE()`. |
| 54) What is the `NVL()` function? | Replaces `NULL` values with a specified value (Oracle-specific). |
| 55) Function to get the remainder in SQL? | `MOD()` function. |
| 56) Syntax and use of `COALESCE()`? | Returns the first non-null value from a list of expressions. |
| 57) How to use `DISTINCT`? | Retrieves only unique rows from a query's result set. |
| 58) Default ordering of `ORDER BY`? | Ascending (`ASC`); use `DESC` for descending. |
| 59) Why is `WHERE AVG(marks) > 70` incorrect? | You cannot use aggregate functions (`AVG()`) in the `WHERE` clause; use `HAVING` instead. |
| 60) `WHERE` vs. `HAVING`? | `WHERE` filters rows before grouping; `HAVING` filters groups after grouping. |
| 61) Aggregate functions in SQL? | `AVG()`, `COUNT()`, `MAX()`, `MIN()`, `SUM()`, `FIRST()`, `LAST()` |
| 62) What is SQL injection? | A security vulnerability allowing attackers to inject malicious SQL code into database queries. |
| 63) `RANK()` vs. `DENSE_RANK()`? | `RANK()` assigns ranks with gaps for ties; `DENSE_RANK()` assigns consecutive ranks without gaps. |