Database Developer Interview Questions

1. What is the difference between PL SQL and SQL?

2. What is SQL and describe types of SQL statements?

SQL - Standard Query Language, communicates with the server to access, manipulate, and control data.

Exploring the Types of SQL Statements

To understand the full capabilities of SQL, it's essential to categorize its statements into distinct types, each designed for specific aspects of database management:

1. Data Retrieval:

• SELECT: The SELECT statement is the key to querying a database, allowing for the retrieval of data from one or more tables. It supports complex operations, including filtering, sorting, and calculating data.

2. Data Manipulation Language (DML):

• INSERT: Adds new records to a table.

• UPDATE: Modifies existing records within a table.

• DELETE: Removes records from a table.

• MERGE: Combines the capabilities of INSERT and UPDATE by either updating existing records or inserting new ones if they do not already exist, based on a given condition.

3. Data Definition Language (DDL):

• CREATE: Creates new database objects, like tables and views.

• ALTER: Changes the structure of an existing database object.

• DROP: Permanently deletes database objects.

• RENAME: Changes the name of a database object.

• TRUNCATE: Removes all records from a table, resetting the table to its initial state.

4. Transaction Control Statements:

• COMMIT: Finalizes the changes made during the current transaction, making them permanent.

• ROLLBACK: Reverts changes made during the current transaction.

• SAVEPOINT: Creates points within a transaction to which one can roll back.

5. Data Control Language (DCL):

• GRANT: Assigns user permissions for database operations.

• REVOKE: Removes previously assigned permissions.

3. What is an alias in SQL Statements?

In SQL, an alias is a user-defined name given to a table or column for the duration of a particular query. It’s like giving a nickname that only applies within that specific conversation. Aliases help SQL queries easier to read and understand, especially when dealing with complex queries involving multiple tables or when the original column names are long or not very descriptive. By default column, alias headings appear in upper case. Enclose the alias in double quotation marks (“ “) to make it case-sensitive.

Example:

SELECT employee_name AS name FROM employee;

here AS is a keyword and "name" is alias

Aliases become even more valuable in queries involving calculations or when joining multiple tables and need to avoid column name conflicts.

4. What is Literal? Give an example of where it can be used.

In SQL, a literal is essentially a specific value that can be typed directly in a query. Literals can be text, numbers, dates, or boolean(true/false). For text and date types, you wrap them in single quotes, like '2023-03-14' for a date or 'John Doe' for text. Numbers don't need quotes.

Example:

If you want to concatenate (link together) the last name of an employee with their job title in a query, you might use literals for the text parts. Here’s how it could look:

SELECT last_name || ' is a ' || job_id AS "Employee Details" FROM employee;

The || symbol in SQL is used for string concatenation, which means it combines two or more strings into a single string.

5. Define the order of Precedence used in executing SQL Statements.

A straightforward breakdown of the order in which SQL operations are evaluated, presented in a simple table format:

6. What are SQL functions? Describe in brief of the types of functions?

SQL functions are essential tools in SQL that perform operations on data and return a result. They can process data values, perform calculations, manipulate text, and more. These functions can be broadly categorized into two types, each serving distinct purposes:

1. Single-Row (Scalar) Functions

Single-row functions operate on individual rows and return a single result for each row processed. These functions can be further divided based on the type of operation they perform:

• Character Functions: process text data. They can take character input and often return values as either characters or numbers, depending on the function used. Here are the types of character functions:

  • Case-Manipulation Functions:

    • LOWER: Converts all characters in a given string to lowercase.

    • UPPER: Converts all characters to uppercase, making it useful for case-insensitive comparisons.

    • INITCAP: Capitalizes the first character of each word in a given string, which can be particularly useful in formatting names or titles.

  • Character-Manipulation Functions:

    • CONCAT: Joins two or more strings into one.

    • SUBSTR (or SUBSTRING): Extracts a substring from a string, starting at a specified position and for a specified length.

    • LENGTH: Returns the number of characters in a string.

    • INSTR: Finds the position of a substring within a string, which can be used for searching within texts.

    • LPAD/RPAD: Adds a specified number of characters to the left or right of a string, padding it to a certain length.

    • TRIM: Removes specified prefixes or suffixes from a string.

    • REPLACE: Replaces occurrences of a specified substring within a string with another substring.

• Number Functions: Number functions operate on numeric data and return numeric values, facilitating mathematical operations directly within SQL queries. Examples include:

  • ROUND: Rounds a number to a specified decimal place, which is useful for financial calculations requiring rounding to cents.

  • TRUNC (or TRUNCATE): Truncates a number to a specified number of decimal places without rounding.

  • MOD: Returns the remainder of a division operation, helpful in identifying even or odd numbers or in loop iterations.

• Date Functions: Date functions allow for the manipulation and interrogation of data in date format, providing flexibility in handling dates:

  • MONTHS_BETWEEN: Calculates the number of months between two dates, returning a numeric value. It’s valuable in calculating age, tenure, or intervals.

  • ADD_MONTHS: Adds a specified number of months to a date, useful in projecting future dates.

  • NEXT_DAY: Finds the date of the next specified day of the week following a given date, which can be used in scheduling.

  • LAST_DAY: Returns the last day of the month for a given date, useful in monthly closing operations.

  • ROUND and TRUNC (for dates): These functions work on dates to round or truncate them to the nearest specified unit (day, month, year), aiding in date comparisons or aggregations.

• Conversion Functions: Convert a value from one data type to another. TO_CHAR() converts a number or date to a string, while TO_NUMBER() converting a string to a number.

• General Functions: Include various utility functions like NVL() that allows substituting a value when a null value is encountered.

2. Multiple-Row (Aggregate) Functions

Multiple-row functions perform operations on a set of rows to return a single result representing the set. These functions are crucial for summarizing data:

• AVG(): Calculates the average value of a specified column over a set of rows.

• COUNT(): Returns the number of rows in a set, optionally counting only non-null values.

• MAX() and MIN(): Determine the maximum and minimum values in a set of rows, respectively.

• SUM(): Adds up the values in a column for a set of rows.

• STDDEV() and VARIANCE(): Compute the standard deviation and variance, respectively, indicating how much the values in a set differ from the average.

7. What is a Dual Table?

The dual table is owned by the user SYS and can be accessed by all users. It contains one column Dummy and one row with the value X. The Dual Table is useful when you want to return a value only once. The value can be a constant, pseudocolumn, or expression that is not derived from a table with user data.

8. What are the different types of database normalization? and why is normalization important?

There are three types of database normalization: 1NF (First Normal Form), 2NF (Second Normal Form), and 3NF (Third Normal Form). Normalization is important because it helps eliminate data redundancy and improves data integrity. It reduces data anomalies and ensures that each piece of data is stored in only one place, making it easier to maintain and update the database.

9. Explain the concept of ACID properties in a database system.

ACID properties stand for Atomicity, Consistency, Isolation, and Durability. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. Consistency guarantees that a transaction brings the database from one valid state to another. Isolation ensures that concurrent transactions do not interfere with each other. Durability ensures that once a transaction is committed, its changes are permanent and survive any system failure.

10. How to ensure the security of data within a database?

To ensure the security of data within a database, I implement various measures such as restricting access through user roles and permissions, encrypting sensitive data, implementing strong password policies, and regularly auditing and monitoring database activity. I also ensure that database backups are in place to protect against data loss.

11. Explain the difference between a primary key and a foreign key in a database.

A primary key is a unique identifier for a record in a table and ensures that each record is uniquely identifiable. A foreign key is a column in a table that refers to the primary key of another table, creating a relationship between the two tables. The primary key is used to enforce data integrity and maintain the referential integrity between related tables.

12. In what scenarios would you choose NoSQL over SQL?

NoSQL databases are suitable for scenarios where scalability, flexibility, and fast data retrieval are more important than strict data consistency. NoSQL databases are often chosen for applications with high write loads, large amounts of unstructured data, and the need for horizontal scalability.

13. What is database indexing, and why is it important?

Database indexing is the process of creating data structures, known as indexes, to improve the speed of data retrieval operations. Indexing is important because it allows the database to quickly locate and access specific data without scanning the entire table. It can significantly improve query performance, especially for large tables.

13. Difference between DECODE and CASE

DECODE: Decode is a function in SQL. It's a way of converting a written code into understandable language.

CASE: Case is the statement in SQL. It's a way of responding to the occurrence of a value or what action to be performed when a particular value occurs.

Differences: