We cannot imagine any application will work without the support of databases. Databases have traversed a long way from flat files to hierarchical models to RDBMS and now NoSQL based databases. Today Big data and Business Intelligence(BI) work solely on NoSQL databases. This depicts the importance of a database based career in the technical field.
Here we have compiled the most frequently asked interview questions for databases. In this article, we will see the most frequently asked SQL interview questions. Before starting with the SQL we will see the popular RDBMS interview questions first.
RDBMS Interview Questions
1.What are the different features of RDBMS?
Below are the important features of RDBMS which make it reliable and consistent:
- Structured storage: Data is stored in structured data tables which makes data retrieval easier.
- Unique identification: Data is stored in rows and columns, with a unique primary key. This makes it easy to identify and retrieve the data uniquely.
- Indexes: RDBMS supports the indexing of data for quick data retrieval.
- Views: Virtual tables or views help in hiding sensitive data. Different users may have different views as per their role access.
- Concurrent users: Multiple users can have the access to the database and they may access the RDBMS concurrently.
2.What is an E-R model in RDBMS?
The E-R model or Entity-Relationship model is a high-level data model. It defines the relationship between the different entities in RDBMS. There are three components in an E-R model:
- Entity- Represents an object(person, place, class, etc.) It is denoted by a rectangle in the E-R diagram.
- Attributes- Represents the properties of an entity. It is denoted by an eclipse in the E-R diagram.
- Relationship- Represents the relationship between the entities. It is denoted by a diamond in the E-R diagram.
Detailed tutorial here.
3.What are the levels of abstraction in RDBMS?
There are three levels of abstraction in RDBMS:
- Physical level: It is the lowest level of abstraction which defines the actual physical storage of data in the memory. Here we deal with the memory size, data accessing mechanisms, hashing, etc.
- Logical level: It is the middle level of the abstraction in RDBMS. Here we deal with the tables, attributes of the objects, and relationships between the entities.
- View level: This is the highest level of abstraction present in RDBMS. Here we define the interaction of users with the database. Different users have access to different views of the same database according to the requirement.
4.What are ACID properties in DBMS?
The ACID properties in DBMS are its backbone, and these properties provide the necessary rules which the RDBMS transactions should follow.
- Atomicity: Each database transaction is a single unit and it should be processed completely and not partially. Hence, a database transaction should either complete or not execute at all. There is no middle state in the case of RDBMS.
- Consistency: Database maintains correctness and integrity after the execution of a transaction. Hence, only valid data according to the rules and constraints of the database should get written in the database.
- Isolation: Database transactions may execute concurrently without interfering with the other. Additionally, the transactions should not violate the stability and consistency of the database.
- Durability: When a database transaction is executed completely, then in case of system failure, it will maintain the consistency of the database. In such cases, the transactions will remain present in the database.
5. What is normalisation and its different forms?
Normalisation is a process of organising the data in the RDBMS in such a way that it eliminates redundancy in relations and a set of relations. Additionally, when we use an update, insert, or delete on redundant data then it causes abnormalities in the database. Hence, we should reduce the data into normal forms to remove these anomalies.
The most popular normal forms are:
- 1NF- First Normal Form states that every attribute(represented by column) of the database table should be atomic. Therefore, in 1NF the attribute should not contain a composite value.
- 2NF-Second Normal Form states that the table should be in 1NF. In addition, all non-key attributes should be fully functionally dependent on the primary key.
- 3NF- Third Normal Form, states that first, the table should be in 2NF. Secondly, there should be no transition dependency.
- BCNF- Boyce-Codd Normal Form states that first, a table should be in 3NF. Secondly, for every functional dependency A->B, A should be a super key of the table.
6. List and explain the different types of keys in RDBMS?
We use a database key to uniquely identify a row or record in a database table. Where a key can be composed of a single attribute or a set of attributes which help us to uniquely identify a row.
Different types of keys in RDBMS are-
- Super key: It is a group of single/multiple keys to identify a row uniquely. It may also have some attributes which are not needed to uniquely identify rows.
- Candidate key: It is a super key but does not have additional attributes which are not needed to uniquely identify the rows. A table can have multiple candidate keys.
- Primary key: It is selected from the candidate keys of a table. A table can have only one primary key to uniquely identify rows.
- Composite key: Two or more attributes are combined together to uniquely identify the rows. These attributes individually cannot identify rows uniquely.
- Foreign key: It is a column which shows a relationship with the primary key of another table. A table can have multiple foreign keys.
- Alternate key: A key which is not a primary key but can be used to uniquely identify rows in the table. A table can have one or more choices for the primary key and we can choose one out of them as the primary key. Hence, the rest of the keys among the choices are alternate keys.
7. What are the different data models in RDBMS?
The common data models in RDBMS are:
Hierarchical model- Data is present in a tree structure hierarchy. There is a single root and other data are linked to the root node. It maintains a parent-child relationship between the nodes of the tree.
Network model- The network model is an extended version of the hierarchical model where a node can have more than one parent.
Entity-Relationship model- It has an object defined as an entity and the properties defined as attributes. The entities have a relationship between them denoted by a diamond in the diagram.
Relational model- The data is stored in a two-dimensional table with rows and columns.
8. Explain the differences between vertical and horizontal scaling of databases.
Vertical and horizontal scaling are two types of database scaling. Scaling is done when the data load increases on the database and then we need to choose any one of the scaling types.
SQL Interview Questions: Basic
1. What is SQL?
SQL stands for Structured Query Language. It is a standard language to create, manipulate, store, and retrieve data from RDBMS. Through SQL we can perform the below actions on relational databases-
- Create- database, tables, views, stored procedures
- Update- records
- Insert- records
- Select- data from tables
- Set- permissions on tables, procedures, views
2. Explain the difference between a Primary and Unique key in SQL?
3. List the different types of SQL statements?
SQL statements can be divided into five types based on the tasks they perform-
- DDL: Data Definition Language- defines the structure of a database
- DML: Data Manipulation Language- manages data in a database
- DCL: Data Control Language- grant and revoke access
- TCL: Transaction Control Language- applies changes permanently
- DQL: Data Query Language- fetches data from a database
4. What is data independence in SQL? Explain its types.
Data independence is a property that lets us change the schema of a database at one level without causing alterations in the higher level of the schema.
There are two types of data independence, frequently asked in SQL interview questions:
- Physical Data Independence- It means that any change in the physical data storage will not affect the conceptual schema of the database.
Example: changing the location of the database in memory
- Logical Data Independence-It means that any change in the conceptual schema will not affect external views.
Example: Merging two records
Read more about data independence in SQL here.
5. Explain the advantages and disadvantages of views in SQL?
Advantages of views–
- Views can be used to restrict the visibility of data columns to users.
- They do not actually use physical storage to store data. They can be understood as virtual tables.
- Views can provide access permissions to users for executing various commands.
Disadvantages of views–
- When a table is dropped, the view gets deactivated. In such a case, we cannot use the view anymore.
- DML commands cannot be used on views because they are not stored physically in storage.
- Views on large database tables occupy more memory space of the system.
- The execution speed of view is slow because it works on data retrieval queries.
SQL Interview Questions: Intermediate
1. Explain cursors and their types in SQL.
Cursors are temporary work areas in the memory which store the data retrieved from the SQL query for manipulation or traversing through it. They are database objects which store the rows returned by the query and point to a single row at a time.
There are two types of cursors that you should know for SQL interview questions:
- Implicit cursor: These are the default cursors of SQL used for internal processing. They are used whenever a DML command – insert, delete, update, etc. are executed.
- Explicit cursor: These are explicitly created by the user whenever required for fetching data row by row. E.g. creating a SQL block
2. Explain different Joins in SQL.
SQL Joins are used to combine two or more tables based on a related column.
Different types of SQL Joins are:
- Inner Join- Returns all rows from both the tables where the join condition is satisfied.
- Left Join- Returns all rows from the left side table and rows which satisfy the join condition from the right side table.
- Right Join- Returns all rows from the right side table and rows which satisfy the join condition from the left side table.
- Full Outer Join- Returns all rows where the join condition is satisfied from both tables.
3. Explain the importance of database partitioning.
In a database, table, index, or indexed table can be partitioned into smaller pieces called partitions. This process of subdividing is called database partitioning. Importance of database partitioning to know for SQL interview questions practice are:
- Better performance of the queries because only a partition needs to be checked and not the whole table.
- Administrators can manage smaller chunks of data more effectively.
- Maintenance is easier on the small partitions than the whole large table.
- For a database table, if one partition is unavailable due to some issue and the rest of the partitions are available, this will ensure better database availability for query execution.
4. Explain differences between DROP, DELETE, and TRUNCATE commands.
- Drop command in SQL is used to delete the entire schema of the table permanently.
- Delete command in SQL is used to delete the rows of the data table.
- Truncate command in SQL is used to delete the entire table and free up the memory space.
Below table shows the differences between DROP, DELETE and TRUNCATE command in SQL:
5. Explain the difference between WHERE and HAVING clauses.