Chapter 3 - Databases

Frequently asked DBMS, SQL, noSQL Interview Questions and answers. Record, Table, Transactions, Locks, Normalization, Foreign Key, Primary Key, Constraints, SQL Commands, Pattern Matching, SQL Joins, Views, Stored procedure, but also NoSQL. This chapter covers basic and more advanced database questions.

General

What is DBMS?

The database management system is a collection of programs that enables user to store, retrieve, update and delete information from a database

What is RDBMS?

Relational Database Management system (RDBMS). It is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed or reassembled in many different ways without having to reorganize the database tables. Data from relational database can be accessed using an API, Structured Query Language (SQL).

What is SQL?

Structured Query Language(SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard.

What is DDL?

DDL stands for Data Definition Language and is used to define the structure that holds the data. For example, Create, Alter, Drop and Truncate table.

What is DML?

DML stands for Data Manipulation Language and is used for manipulation of the data itself. Typical operations are Insert, Delete, Update and retrieving the data from the table. The Select statement is considered as a limited version of the DML, since it can't change the data in the database. But it can perform operations on data retrieved from the DBMS, before the results are returned to the calling function.

What is DCL?

DCL stands for Data Control Language and is used to control the visibility of data like granting database access and set privileges to create tables, etc. Example - Grant, Revoke access permission to the user to access data in the database.

What are the advantages of SQL?

SQL is not a proprietary language used by specific database vendors. Almost every major DBMS supports SQL, so learning this one language will enable programmers to interact with any database like ORACLE, MySQL etc.

SQL is easy to learn. The statements are all made of descriptive keywords.

SQL allow performing very complex and sophisticated database operations.

What is a field in a database?

A field is an area within a record reserved for a specific piece of data.

Examples : Employee Name, Employee ID, etc.

What is a Record in a database?

A record, or row, is the collection of values / fields of a specific entity: i.e. an Employee, Salary etc.

What is a Table in a database?

A table is a collection of records of a specific type. For example, employee table, salary table etc.

Transactions

What is a database transaction?

Database transaction takes a set of database records from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.

What are properties of a transaction?

Expect this SQL Interview Questions as a part of any interview involving databases, irrespective of your experience. Properties of the transaction can be summarized as the ACID properties belows.

Atomicity

A transaction consists of many steps. When all the steps in a transaction get completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.

Consistency

Consistency, states that data cannot be written that would violate the database’s own rules for valid data. If a certain transaction occurs that attempts to introduce inconsistent data, the entire transaction is rolled back and an error returned.

A simple rule of consistency may state that the Gender column of a database may only have the values ‘Male’, 'Female’ or ‘Unknown’. If a user attempts to enter something else, say ‘Hermaphrodite’ then a database consistency rule kicks in and disallows the entry of such a value.

Isolation

Every transaction should operate as if it is the only transaction in the system. Transaction isolation is an important part of any transactional system. It deals with consistency and completeness of data retrieved by queries unaffecting a user data by other user actions. A database acquires locks on data to maintain a high level of isolation.

Durability

Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis. Durability guarantees that transactions that have committed will survive permanently. For example, if a flight booking reports that a seat has successfully been booked, then the seat will remain booked even if the system is restarted, or crashes.

Keys and Constraints

What is a primary key?

A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. To define a field as primary key, the following conditions must be met:

• No two rows can have the same primary key value.
• Every row must have a primary key value.
• The primary key field cannot be null.
• Value in a primary key column can never be modified or updated, if any foreign key refers to that primary key.

What is a Composite Key?

A Composite key is a type of key, which represents a set of columns whose values uniquely identify every row in a table. For example - if "Employee_ID" and "Employee Name" in a table is combined to uniquely identify a row its called a Composite key.

What is a Composite Primary Key?

A Composite primary key is a set of columns whose values uniquely identify every row in a table where a table having a composite primary key will be indexed based on the columns specified in the primary key. This key will be referred in Foreign Key tables. For example - if the combined effect of columns, "Employee_ID" and "Employee Name" in a table is required to uniquely identify a row, its called a Composite Primary Key. In this case, both the columns will be represented as primary key.

What is a Foreign Key?

When a table's primary key field is added to a related "many" table in order to create the common field which relates the two tables, it is called a foreign key in those other tables. For example, the salary of an employee is stored in salary table. The relation is established via foreign key column “Employee_ID_Ref” which refers “Employee_ID” field in the Employee table.

Insert, Update and Delete

Define the SQL Insert Statement?

SQL INSERT statement is used to add rows to a table. The SQL query starts with the INSERT INTO statement followed by the table name and values command, followed by the values that need to be inserted into the table.

Example of insert statement:

INSERT INTO table_name

(column1, column2, column3, .. .)

VALUES

(value1, value2, value3, .. .);

The insert can be used in several ways:

• To insert a single complete row.
• To insert a single partial row.

Define SQL Update Statement?

SQL Update is used to update data in a row or set of rows specified in the filter condition. The basic format of an SQL UPDATE statement is, UPDATE command followed by the table name to be updated and SET command followed by column names and their new values followed by filter condition that determines which rows should be updated.

Example of update statement:

UPDATE table_name

SET column1 = value1,

column2 = value2,

.. .

WHERE condition;

Define SQL Delete Statement?

SQL Delete is used to delete a row or set of rows specified in the filter condition. The basic format of an SQL DELETE statement is, DELETE FROM command followed by the table name followed by filter condition that determines which rows should be deleted.

Example of delete statement:

DELETE FROM table_name

WHERE condition;

How are wild cards used in SQL statements?

SQL Like operator is used for pattern matching. The LIKE command takes more time to process. So before using it as an operator, consider suggestions given below on when and where to use wild card search.

• Do not overuse wild cards. If another search operator will do, use it instead.
• When you do use wild cards, try not to use them at the beginning of the search pattern, unless absolutely necessary. Search patterns that begin with wild cards are the slowest to process.
• Pay careful attention to the placement of the wild card symbols. If they are misplaced, the query might not return the data you intended.

Joins, Views and Clauses

Define Join and explain different types of joins?

Frequently asked SQL interview questions are on Joins. In order to avoid data duplication, data is stored in related tables. One of the type of JOIN command is used to fetch data from related tables. Joining return rows when there is at least one match in both tables. Commonly used types of joins are covered below.

Right Join Return all rows from the right table, even if there are no matches in the left table.

Left Join Return all rows from the left table, even if there are no matches in the right table.

Inner Join Return rows when there is a match on both tables.

What is Self-Join?

Self-join is when a query joins a table to itself. Aliases should be used for the same table comparison.

What is Cross Join?

Cross Join will return all records where each row from the first table is combined with each row from the second table.

What is the difference between the WHERE clause and the HAVING clause?

WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.

What is a View?

Views are virtual tables. Unlike tables that contain data, views rather contain queries that dynamically retrieve data when used.

What are the advantages and disadvantages using Views?

Advantages

• Views don't store data in a physical location.
• The view can be used to hide some of the columns from the table.
• Views can provide Access Restriction, since data insertion, update and deletion is not possible with the view.

Disadvantages

• When a table is dropped or altered, associated views become irrelevant or need updated as well.
• Complexity where performance suffers because views may be based upon multiple tables. In these cases, queries based upon such views will experience increases in execution time because the database’s query processor has to translate these queries in order to query the actual tables of the database from which the columns of the view are derived.

What is a Stored Procedure?

A stored procedure is a function which contains a collection of SQL Queries. The procedure can take inputs, process them and send back output.

What are the advantages of a Stored Procedure?

Stored Procedures are precomplied and stored in the database. This enables the database to execute the queries faster. Since many queries can be included in a stored procedure, round trip time to execute multiple queries from source code to database and back is avoided.

Also, multiple applications can invoke the stored procedure, this can avoid SQL duplication and maintenance across multiple separate consuming applications.

NoSQL

If you are looking for a full stack job, chances are NoSQL knowledge will be very nice to have. You need to prepare for NoSQL Interview Questions. Although every interview is different, the following should give you the necessary to demonstrate understanding of modern database technologies.

What is NoSQL?

NoSQL encompasses a wide variety of different database technologies that were developed in response to a rise in the volume of data stored about users, objects, and products. The frequency at which such large data is accessed impacts performance hence processing have been revisited to suit big data needs. Relational databases were simply not designed to cope with the scale and agility challenges that face modern applications, nor were they built to take advantage of the cheap storage and processing power available today.

What are common features offered by NoSQL Databases?

1. Flexibility - NoSQL offers flexibility to store structured, semi-structured or unstructured data.
2. Dynamic Schemas - Schema definition is not required, this solves the problem to modify the schema where a table is already present with huge datasets and new columns need to be added to the same table.
3. Sharding - Sharding means partitioning data into smaller databases to have faster access to data. This feature is present in most NoSQL databases which allows fetching data from servers in faster time.
4. Generic - Can be customized by the user as per the need.
5. Scaling - Scales out horizontally, thus cheaper to manage.

Differences with Relational DB?

RDBMS over NoSQL

• Better for relational data that is structured and organized.
• Organize data through normalization
• Use Structured query language(SQL) which is easy to learn
• Maintains Data Integrity
• Data and its relationships are stored in separate tables
• ACID compliance, i.e. either all the transactions are committed or none.
• Scale up / Vertical Scaling

NoSQL over RDBMS

• Better or easier for unstructured and unpredictable data
• No predefined schema
• Usually Cheaper to manage
• Higher performance, availability, and scalability for Big Data
• Scale-out / Horizontal Scaling

What are the different types of NoSQL databases?

Four different types of NoSQL databases

1. Key-value stores - The simplest, where every item in the database is stored as an attribute name (or key) together with its value. Riak, Voldemort, and Redis are the most well-known in this category.
2. Wide-column stores - It stores the data together as columns instead of rows and is optimized for queries over large datasets. The most popular are Cassandra and HBase.
3. Document databases - It pairs each key with a complex data structure known as a document. Documents can contain many different key-value pairs, or key-array pairs, or even nested documents. MongoDB is the most popular of these databases.
4. Graph databases - They are used to store information about networks, such as social connections. Examples are Neo4J and HyperGraphDB.

Do you know what is BASE?

The CAP theorem states that distributed systems cannot achieve all three properties at the same time:

• Consistency
• Availability
• Partition tolerance

The BASE (Basically Available Soft state Eventual consistency) system gives up on consistency while maintaining the other two. The BASE system works well despite physical network partitions and always allow a client with reading and write availability.