31 - One liner question and answer for technical interview

 

Q1. What is the Definition of a DBMS?

A Database Management System (DBMS) is a computer program that controls the creation, maintenance, and use of databases. It functions as an interface between user applications and data stored in files or on other computers connected to it over network links.

Q2. What does RDBMS stand for?

RDBMS, or Relational Database Management System, is a system that stores data in collections of tables related by common fields between columns. It also enables operators to manipulate the stored information within those tables.

Q3. What language is used to communicate with the Database?

SQL stands for Structured Query Language, and it is a standard computer language that allows users to send commands or query statements in order to retrieve stored data from relational databases and manipulate them as needed.

Q4. Explain what a database contains?

A database typically consists of an organized collection of records that can be queried by one or more software applications in order to search out specific information inside tables such as customer transactions, products stock inventories, etc., which are created when organizing information by related fields.

Q5. What are Tables & Fields?

A Table is an arrangement of data with labeled columns and rows. The number of Columns, or ‘Fields’ are fixed while the number of Rows can vary depending on the amount of information being presented.

Q6. Explain the term Primary Key?

A Primary Key is a special type of Unique Key that consists of multiple fields combined together to uniquely identify each row in a database. This has an inherent NOT NULL constraint, meaning the values for this key cannot be set as NULL.

Q7. How do Unique Keys differentiate themselves from Primary Keys?

A Unique Key constraint ensures that every record in a database has a distinct value or combination of values. This means that different records cannot have the same value in the column(s) on which the unique key is defined, nor can they be left blank (null).

A Primary Key is also subject to this rule but it additionally guarantees only one occurrence of each row. All other columns may not contain duplicate values and null entries are forbidden. Whereas there can be multiple Unique Keys per table, there should always be just one single Primary Key per table.

Q8. What is the purpose of a Foreign Key?

A Foreign Key is used to link one table with another related table and establish a relationship between them by referencing the primary key from one table in the other.

Q9. Could you explain the use of JOINs?

JOINs are used to combine data from two or more tables based on a common field between them. For instance, an INNER JOIN can be employed when you want to retrieve records that exist in both tables A and B.

Q10. What does Normalization involve?

Normalization involves organizing database fields and structures into efficient forms so as to reduce redundancy while maintaining or increasing efficiency when Adding, Deleting, or Modifying field values within single Tables.

Q11. What is DeNormalization?

DeNormalization is a technique used to access the data from higher to lower normal forms of the database. It involves introducing redundancy into a table by incorporating data from related tables in order to speed up queries and simplify processes such as joining tables or aggregating results.

Q12. What is meant by “view” in databases?

A ‘view’ is a virtual representation of data from one or more tables, taking up less storage space than the original table. It contains only a subset of information and does not physically exist in memory. The content included depends on the relationship between the different elements involved.

Q13. Explain Indexes in databases?

By using an index, the retrieval of records from a table can be sped up. This is done by creating entries for each value which makes data access quicker.

Q14. What is a Cursor?

A Cursor is a control structure that provides navigation over the records in a database table. It can be thought of as pointing to one row within a set of rows and allows operations such as retrieval, addition, or removal of database records. Cursors are extremely useful for traversing through data sets efficiently.

Q15. What is a database relationship and what are the types?

A database relationship refers to the connection between two or more tables in a database. There are four common types of relationships: One-to-One, One-to-Many, Many-to-One, and Self Referencing Relationships.

Q16. What is meant by “Query” in a database?

A ‘query’ is a code used to obtain data from the database. Queries can be written in such a way that they will produce results that meet specific expectations. In simpler terms, it is an inquiry made of the database.

Q17. Name two types of Subqueries?

Two types of Subqueries are Correlated Subquery and Non-Correlated Subquery. A Correlated Subquery is dependent on the main query, referring to columns in tables listed in the FROM clause. On the other hand, a Non-Correlated Subquery can be regarded as an independent entity that provides values that are then substituted into the main query’s expression.

Q18. What is a Stored Procedure?

A Stored Procedure consists of several SQL statements combined into one entity so they could be run when necessary. It’s used for accessing databases oftentimes making operations more efficient than regular SQL commands would do separately each time running them independently.

Q19. What is a database Trigger?

A database Trigger is an automated procedure that runs in response to certain events on a particular table or view within a database. Triggers are used mainly to maintain the integrity of data by changing, restricting, or validating data entered into the tables and can also be used for other tasks such as auditing changes made by users.

Q20. Distinguish Between DELETE & TRUNCATE Commands?

The DELETE command removes records from a table one row at a time. It’s often used with a WHERE clause to limit the number of rows affected. The TRUNCATE command quickly deletes all records in a table without logging individual row removals. It also resets auto-increment values back to their starting value (usually 1).

Intermediate Level RDBMS Interview Questions

If you already have a more extensive understanding of RDBMS and are looking for intermediate roles, or if you’re looking to test your expertise, an intermediate-level interview may include moderately difficult questions. These questions can help evaluate how familiar you are with core concepts and how adeptly you apply them in various scenarios. Here is a selection of intermediate-level RDBMS interview questions along with detailed answers:

Q21. What is the difference between Local and Global variables?

Local variables are used inside functions while global variables can be accessed throughout the entire program. Local variable names cannot conflict with those of global scope, and their values exist only when a function is executing. Global variables remain in memory for as long as the script runs and they must be declared outside any particular function to retain their value between calls.

Q22. What does a Constraint do?

A Constraint is used to limit or restrict the data type of the table. It can be applied when creating or altering a database table, and there are several types of constraints available such as NOT NULL, CHECK, DEFAULT, UNIQUE, PRIMARY KEY, and FOREIGN KEY.

Q23. What is Data Integrity?

Data Integrity refers to the accuracy and consistency of data stored in a database. It also includes defining integrity constraints that enforce certain business rules on incoming data when it enters an application or database.

Q24. What does Auto Increment do?

The Auto-increment keyword enables the generation of a unique number each time a new record is added to the table. The AUTO INCREMENT keyword can be utilized in Oracle while the IDENTITY keyword is available for use in SQL SERVER.

Q25. How does Cluster Index differ from Non-Cluster Indexes?

Clustered index reorganizes the physical order of data in a database table, allowing for faster retrieval as records are stored by their clustered column value. Nonclustered indexes create an entirely separate object within the same table and merely point back to the original rows after searching; they do not change how it was originally stored.

Q26. Can you explain what a data warehouse is exactly?

A data warehouse is a central repository of data from multiple sources that have been consolidated, transformed, and made available for mining and online processing. Data Marts are subsets of the data warehouse’s data.

Q27. Tell me more about Self-Join?

A Self-Join is a type of query used for comparing data from the exact same table. It employs aliases to distinguish between rows and can yield various insights on both related/unrelated records within databases.

Q28. What does the Cross Join entail?

The Cross Join, also known as the Cartesian product, is a type of query in which every row from one table is combined with every row from another table. This results in an output containing all possible combinations between both tables. However, if a WHERE clause filters out certain rows then the result will be similar to that of an INNER JOIN operation.

Q29. Could you explain what User Defined Functions do?

User Defined Functions or UDFs exist to store logic that may be reused whenever needed instead of having users write identical phrases iteratively every time. These tools ultimately speed up code execution while cutting down network traffic & enhancing overall security standards.

Q30. Can you briefly explain Collation?

Collation is the set of rules governing how character data can be sorted and compared. ASCII values are usually used to compare these characters based on their numerical value.

Q31. What are the advantages and disadvantages of Stored Procedures?

Advantages include supporting motioned modular programming helping to execute tasks more quickly, reducing network traffic & providing extra security for your data. The disadvantage is that stored procedures can only be executed in databases consuming larger amounts of memory on these servers.