Database Interview Questions And Answers

 Q #1) What do you understand by ‘Database’?

Answer: Database is an organized collection of related data where the data is stored and organized to serve some specific purpose.

For Example, A librarian maintain a database of all the information related to the books that are available in the library.

Q #2) Define DBMS.

Answer: DBMS stands for Database Management System. It is a collection of application programs which allow the user to organize, restore and retrieve information about data efficiently and as effectively as possible.

Some of the popular DBMS’s are MySql, Oracle, Sybase, etc.

Q #3) Define RDBMS.

Answer: Relational Database Management System(RDBMS) is based on a relational model of data that is stored in databases in separate tables and they are related to the use of a common column. Data can be accessed easily from the relational database using Structured Query Language (SQL).

Q #4) Enlist the advantages of DBMS.

Answer: The advantages of DBMS includes:

• Data is stored in a structured way and hence redundancy is controlled.
• Validates the data entered and provide restrictions on unauthorized access to the database.
• Provides backup and recovery of the data when required.
• It provides multiple user interfaces.

Q #5) What do you understand by Data Redundancy?

Answer: Duplication of data in the database is known as data redundancy. As a result of data redundancy, duplicated data is present at multiple locations, hence it leads to wastage of the storage space and the integrity of the database is destroyed.

Q #6) What are the various types of relationships in Database? Define them.

Answer: There are 3 types of relationships in Database:

• One-to-one: One table has a relationship with another table having the similar kind of column. Each primary key relates to only one or no record in the related table.
• One-to-many: One table has a relationship with another table that has primary and foreign key relations. The primary key table contains only one record that relates to none, one or many records in the related table.
• Many-to-many: Each record in both the tables can relate to many numbers of records in another table.

Q #7) Explain Normalization and De-Normalization.

Answer:

Normalization is the process of removing redundant data from the database by splitting the table in a well-defined manner in order to maintain data integrity. This process saves much of the storage space.

De-normalization is the process of adding up redundant data on the table in order to speed up the complex queries and thus achieve better performance.

Q #8) What are the different types of Normalization?

Answer: Different types of Normalization are:

• First Normal Form (1NF): A relation is said to be in 1NF only when all the entities of the table contain unique or atomic values.
• Second Normal Form (2NF): A relation is said to be in 2NF only if it is in 1NF and all the non-key attribute of the table is fully dependent on the primary key.
• Third Normal Form (3NF): A relation is said to be in 3NF only if it is in 2NF and every non-key attribute of the table is not transitively dependent on the primary key.

Q #9) What is BCNF?

Answer: BCNF is the Boyce Code Normal form. It is the higher version of 3Nf which does not have any multiple overlapping candidate keys.

Q #10) What is SQL?

Answer: Structured Query language, SQL is an ANSI(American National Standard Institute) standard programming language that is designed specifically for storing and managing the data in the relational database management system (RDBMS) using all kinds of data operations.

Q #11) How many SQL statements are used? Define them.

Answer: SQL statements are basically divided into three categories, DDL, DML, and DCL.

They can be defined as:

• Data Definition Language (DDL) commands are used to define the structure that holds the data. These commands are auto-committed i.e. changes done by the DDL commands on the database are saved permanently.
• Data Manipulation Language (DML) commands are used to manipulate the data of the database. These commands are not auto-committed and can be rolled back.
• Data Control Language (DCL) commands are used to control the visibility of the data in the database like revoke access permission for using data in the database.

Q #12) Enlist some commands of DDL, DML, and DCL.

Answer: Data Definition Language (DDL) commands:

• CREATE to create a new table or database.
• ALTER for alteration.
• TRUNCATE to delete data from the table.
• DROP to drop a table.
• RENAME to rename a table.

Data Manipulation Language (DML) commands:

• INSERT to insert a new row.
• UPDATE to update an existing row.
• DELETE to delete a row.
• MERGE for merging two rows or two tables.

Data Control Language (DCL) commands:

• COMMIT to permanently save.
• ROLLBACK to undo the change.
• SAVEPOINT to save temporarily.

Q #13) Define DML Compiler.

Answer: DML compiler translates DML statements in a query language into a low-level instruction and the generated instruction can be understood by Query Evaluation Engine.

Q #14) What is DDL interpreter?

Answer: DDL Interpreter interprets the DDL statements and records the generated statements in the table containing metadata.

Q #15) Enlist the advantages of SQL.

Answer: Advantages of SQL are:

• Simple SQL queries can be used to retrieve a large amount of data from the database very quickly and efficiently.
• SQL is easy to learn and almost every DBMS supports SQL.
• It is easier to manage the database using SQL as no large amount of coding is required.

Q #16) Explain the terms ‘Record’, ‘Field’ and ‘Table’ in terms of database.

Answer:

Record: Record is a collection of values or fields of a specific entity. For Example, An employee, Salary account, etc.

Field: A field refers to an area within a record that is reserved for specific data. For Example, Employee ID.

Table: Table is the collection of records of specific types. For Example, the Employee table is a collection of records related to all the employees.

Q #17) What do you understand by Data Independence? What are its two types?

Answer: Data Independence refers to the ability to modify the schema definition in one level in such a way that it does not affect the schema definition in the next higher level.

The 2 types of Data Independence are:

• Physical Data Independence: It modifies the schema at the physical level without affecting the schema at the conceptual level.
• Logical Data Independence: It modifies the schema at the conceptual level without affecting or causing changes in the schema at the view level.

Q #18) Define the relationship between ‘View’ and ‘Data Independence’.

Answer: View is a virtual table that does not have its data on its own rather the data is defined from one or more underlying base tables.

Views account for logical data independence as the growth and restructuring of base tables are not reflected in views.

Q #19) What are the advantages and disadvantages of views in the database?

Answer: Advantages of Views:

• As there is no physical location where the data in the view is stored, it generates output without wasting resources.
• Data access is restricted as it does not allow commands like insertion, updation, and deletion.

Disadvantages of Views:

• The view becomes irrelevant if we drop a table related to that view.
• Much memory space is occupied when the view is created for large tables.

Q #20) What do you understand by Functional dependency?

Answer: A relation is said to be in functional dependency when one attribute uniquely defines another attribute.

For Example, R is a Relation, X and Y are two attributes. T1 and T2 are two tuples. Then,

T1[X]=T2[X] and T1[Y]=T2[Y]

Means, the value of component X uniquely define the value of component Y.

Also, X->Y means Y is functionally dependent on X.

Q #21) When is functional dependency said to be the fully functional dependent?

Answer: To fulfill the criteria of fully functional dependency, the relation must meet the requirement of functional dependency.

A functional dependency ‘A’ and ‘B’ are said to be fully functional dependent when removal of any attribute say ‘X’ from ‘A’ means the dependency does not hold anymore.

Q #22) What do you understand by the E-R model?

Answer: E-R model is an Entity-Relationship model which defines the conceptual view of the database.

The E-R model basically shows the real-world entities and their association/relations. Entities here represent the set of attributes in the database.

Q #23) Define Entity, Entity type, and Entity set.

Answer:

Entity can be anything, be it a place, class or object which has an independent existence in the real world.

Entity Type represents a set of entities that have similar attributes.

Entity Set in the database represents a collection of entities having a particular entity type.

Q #24) Define a Weak Entity set.

Answer: Weak Entity set is the one whose primary key comprises its partial key as well as the primary key of its parent entity. This is the case because the entity set may not have sufficient attributes to form a primary key.

Q #25) Explain the terms ‘Attribute’ and ‘Relations’

Answer:

Attribute is described as the properties or characteristics of an entity. For Example, Employee ID, Employee Name, Age, etc., can be attributes of the entity Employee.

Relation is a two-dimensional table containing a number of rows and columns where every row represents a record of the relation. Here, rows are also known as ‘Tuples’ and columns are known as ‘Attributes’.

Q #26) What are VDL and SDL?

Answer: VDL is View Definition Language which represents user views and their mapping to the conceptual schema.

SDL is Storage Definition Language which specifies the mapping between two schemas.

Q #27) Define Cursor and its types.

Answer: Cursor is a temporary work area that stores the data, as well as the result set, occurred after manipulation of data retrieved. A cursor can hold only one row at a time.

The 2 types of Cursor are:

• Implicit cursors are declared automatically when DML statements like INSERT, UPDATE, DELETE is executed.
• Explicit cursors have to be declared when SELECT statements that are returning more than one row are executed.

Q #28) What is the Database transaction?

Answer: Sequence of operation performed which changes the consistent state of the database to another is known as the database transaction. After the completion of the transaction, either the successful completion is reflected in the system or the transaction fails and no change is reflected.

Q #29) Define Database Lock and its types.

Answer: Database lock basically signifies the transaction about the current status of the data item i.e. whether that data is being used by other transactions or not at the present point of time.

There are two types of Database lock: Shared Lock and Exclusive Lock.

Q #30) What is Data Warehousing?

Answer: The storage as well as access to data, that is being derived from the transactions and other sources, from a central location in order to perform the analysis is called Data Warehousing.

Q #31) What do you understand by Join?

Answer: Join is the process of deriving the relationship between different tables by combining columns from one or more tables having common values in each. When a table joins with itself, it is known as Self Join.

Q #32) What do you understand by Index hunting?

Answer: Index hunting is the process of boosting the collection of indexes which helps in improving the query performance as well as the speed of the database.

Q #33) How to improve query performance using Index hunting?

Answer: Index hunting help in improving query performance by:

• Using a query optimizer to coordinate queries with the workload.
• Observing the performance and effect of index and query distribution.

Q #34) Differentiate between ‘Cluster’ and ‘Non-cluster’ index.

Answer: Clustered index alters the table and re-order the way in which the records are stored in the table. Data retrieval is made faster by using the clustered index.

A Non-clustered index does alter the records that are stored in the table but creates a completely different object within the table.

Q #35) What are the disadvantages of a Query?

Answer: Disadvantages of a Query are:

• Indexes are not present.
• Stored procedures are excessively compiled.
• Difficulty in interfacing.

Q #36) What do you understand by Fragmentation?

Answer: Fragmentation is a feature that controls the logical data units, also known as fragments that are stored at different sites of a distributed database system.

Q #37) Define Join types.

Answer: Given below are the types of Join, which are explained with respect to the tables as an Example.

employee table:

employee_info table:

a) Inner JOIN: Inner JOIN is also known as a simple JOIN. This SQL query returns results from both the tables having a common value in rows.

SQL Query:

SELECT * from employee, employee_info WHERE employee.EmpID = employee_info.EmpID ;

Result:

b) Natural JOIN: This is a type of Inner JOIN that returns results from both the tables having the same data values in the columns of both the tables to be joined.

SQL Query:

SELECT * from employee NATURAL JOIN employee_info;

Result:

c) Cross JOIN: Cross JOIN returns the result as all the records where each row from the first table is combined with each row of the second table.

SQL Query:

SELECT * from employee CROSS JOIN employee_info;

Result:

Let us do some modification in the above tables to understand Right JOIN, Left JOIN, and Full JOIN.

employee table:

employee_info table:

a) Right JOIN: Right JOIN is also known as Right Outer JOIN. This returns all the rows as a result from the right table even if the JOIN condition does not match any records in the left table.

SQL Query:

SELECT * from employee RIGHT OUTER JOIN employee_info on (employee.EmpID = employee_info.EmpID);

Result:

b) Left JOIN: Left JOIN is also known as Left Outer JOIN. This returns all the rows as a result of the left table even if the JOIN condition does not match any records in the right table. This is exactly the opposite of Right JOIN.

SQL Query:

SELECT * from employee LEFT OUTER JOIN employee_info on (employee.EmpID = employee_info.EmpID);

Result:

c) Outer/Full JOIN: Full JOIN return results in combining the result of both the Left JOIN and Right JOIN.

SQL Query:

SELECT * from employee FULL OUTER JOIN employee_info on (employee.EmpID = employee_info.EmpID);

Result:

Q #38) What do you understand by ‘Atomicity’ and ‘Aggregation’?

Answer: Atomicity is the condition where either all the actions of the transaction are performed or none. This means, when there is an incomplete transaction, the database management system itself will undo the effects done by the incomplete transaction.

Aggregation is the concept of expressing the relationship with the collection of entities and their relationships.

Q #39) Define Phantom deadlock.

Answer: Phantom deadlock detection is the condition where the deadlock does not actually exist but due to a delay in propagating local information, deadlock detection algorithms identify the deadlocks.

Q #40) Define checkpoint.

Answer: Checkpoint declares a point before which all the logs are stored permanently in the storage disk and is the inconsistent state. In the case of crashes, the amount of work and time is saved as the system can restart from the checkpoint.

Q #41) What is Database partitioning?

Answer: Database partitioning is the process of partitioning tables, indexes into smaller pieces in order to manage and access the data at a finer level.

This process of partitioning reduces the cost of storing a large amount of data as well as enhances the performance and manageability.

Q #42) Explain the importance of Database partitioning.

Answer: The importance of Database partitioning are:

• Improves query performance and manageability.
• Simplifies common administration tasks.
• Acts as a key tool for building systems with extremely high availability requirements.
• Allows accessing a large part of a single partition.

Q #43) Explain the Data Dictionary.

Answer: Data dictionary is a set of information describing the content and structure of the tables and database objects. The job of the information stored in the data dictionary is to control, manipulate and access the relationship between database elements.

Q #44) Explain the Primary Key and Composite Key.

Answer: Primary Key is that column of the table whose every row data is uniquely identified. Every row in the table must have a primary key and no two rows can have the same primary key. Primary key value can never be null nor can it be modified or updated.

Composite Key is a form of the candidate key where a set of columns will uniquely identify every row in the table.

Q #45) What do you understand by the Unique key?

Answer: A Unique key is the same as the primary key whose every row data is uniquely identified with a difference of null value i.e. Unique key allows one value as a NULL value.

Q #46) What do you understand by Database Triggers?

Answer: A set of commands that automatically get executed when an event like Before Insert, After Insert, On Update, On Delete of row occurs in a table is called as Database trigger.

Q #47) Define Stored procedures.

Answer: A Stored procedure is a collection of pre-compiled SQL Queries, which when executed denotes a program taking input, process and gives the output.

Q #48) What do you understand by B-Trees?

Answer: B-Tree represents the data structure in the form of a tree for external memory that reads and writes large blocks of data. It is commonly used in databases and file systems where all the insertions, deletions, sorting, etc., are done in logarithmic time.

Q #49) Name the different data models that are available for database systems.

Answer: Different data models are:

• Relational model
• Network model
• Hierarchical model

Q #50) Differentiate between ‘DELETE’, ‘TRUNCATE’ and ‘DROP’ commands.

Answer: After the execution of ‘DELETE’ operation, COMMIT and ROLLBACK statements can be performed to retrieve the lost data.

After the execution of ‘TRUNCATE’ operation, COMMIT, and ROLLBACK statements cannot be performed to retrieve the lost data.

‘DROP’ command is used to drop the table or key like the primary key/foreign key.

Q #51) Based on the given table, solve the following queries.

Employee table

a) Write the SELECT command to display the details of the employee with empid as 1004.

SQL Query:

SELECT empId, empName, Age, Address from Employee WHERE empId = 1004;

Result:

b) Write the SELECT command to display all the records of table Employees.

SQL Query:

SELECT * from Employee;

Result:

c) Write the SELECT command to display all the records of the employee whose name starts with the character ‘R’.

SQL Query:

SELECT * from Employee WHERE empName LIKE ‘R%’;

Result:

d) Write a SELECT command to display id, age and name of the employees with their age in both ascending and descending order.

SQL Query:

SELECT empId, empName, Age from Employee  ORDER BY Age;

Result:

SELECT empId, empName, Age from Employee  ORDER BY Age Desc;

Result:

e) Write the SELECT command to calculate the total amount of salary on each employee from the below Emp table.

Emp table:

SQL Query:

SELECT empName, SUM(Salary) from Emp GROUP BY empName;

Result:

Best SQL Server Interview Questions

Let’s start.

Q #1) Which TCP/IP port does SQL Server run on?

Answer: By default SQL Server runs on port 1433.

Q #2) What is the difference between clustered and non-clustered index?

Answer:  A clustered index is an index that rearranges the table in the order of the index itself. Its leaf nodes contain data pages. A table can have only one clustered index.

A non-clustered index is an index that does not re-arrange the table in the order of the index itself. Its leaf nodes contain index rows instead of data pages. A table can have many non-clustered indexes.

Q #3) List the different index configurations possible for a table?

Answer: A table can have one of the following index configurations:

• No indexes
• A clustered index
• A clustered index and many non-clustered indexes
• A non-clustered index
• Many non-clustered indexes

Q #4) What is the recovery model? List the types of recovery models available in SQL Server?

Answer: The recovery model tells SQL Server what data should be kept in the transaction log file and for how long. A database can have only one recovery model. It also tells SQL server which backup is possible in a particular selected recovery model.

There are three types of recovery models:

• Full
• Simple
• Bulk-Logged

Q #5) What are the different backups available in SQL Server?

Answer:  Different possible backups are:

• Full backup
• Differential Backup
• Transactional Log Backup
• Copy Only Backup
• File and Filegroup backup

Q #6) What is a Full Backup?

Answer: A full backup is the most common type of backup in SQL Server. This is the complete backup of the database. It also contains part of the transaction log so that it can be recovered.

Q #7) What is OLTP?

Answer: OLTP means Online Transaction Processing which follows rules of data normalization to ensure data integrity. Using these rules, complex information is broken down into a most simple structure.

Q #8) What is RDBMS?

Answer:  RDBMS or Relational Database Management Systems are database management systems that maintain data in the form of tables. We can create relationships between the tables. An RDBMS can recombine the data items from different files, providing powerful tools for data usage.

Q #9) What are the properties of the Relational tables?

Answer: Relational tables have six properties:

• Values are atomic.
• Column values are of the same kind.
• Each row is unique.
• The sequence of columns is insignificant.
• The sequence of rows is insignificant.
• Each column must have a unique name.

Q #10) What’s the difference between a primary key and a unique key?

Answer: The differences between the primary key and a unique key are:

• The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. They create a clustered index on the column and cannot be null.
• A Unique key is a column whose values also uniquely identify every row in a table but they create a non-clustered index by default and it allows one NULL only.

Q #11) When is the UPDATE_STATISTICS command used?

Answer: As the name implies UPDATE_STATISTICS command updates the statistics used by the index to make the search easier.

Q #12) What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Answer:  The differences between HAVING CLAUSE and WHERE CLAUSE is:

• Both specify a search condition but the HAVING clause is used only with the SELECT statement and typically used with GROUP BY clause.
• If the GROUP BY clause is not used, then the HAVING clause behaves like a WHERE clause only.

Q #13) What is Mirroring?

Answer: Mirroring is a high availability solution. It is designed to maintain a hot standby server which is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from the principal server to a secondary server which keeps a secondary server up to date with the principal server.

Q #14) What are the advantages of the Mirroring?

Answer: Advantages of Mirroring are:

• It is more robust and efficient than Log shipping.
• It has an automatic failover mechanism.
• The secondary server is synced with the primary in near real-time.

Q #15) What is Log Shipping?

Answer: Log shipping is nothing but the automation of backup and restores the database from one server to another standalone standby server. This is one of the disaster recovery solutions. If one server fails for some reason we will have the same data available on the standby server.

Q #16) What are the advantages of Log shipping?

Answer: Advantages of Log Shipping includes:

• Easy to set up.
• The secondary database can be used as a read-only purpose.
• Multiple secondary standby servers are possible
• Low maintenance.

Q #17) Can we take the full database backup in Log shipping?

Answer: Yes, we can take the full database backup. It won’t affect the log shipping.

Q #18) What is an execution plan?

Answer: An execution plan is a graphical or textual way of showing how the SQL server breaks down a query to get the required result. It helps a user to determine why queries are taking more time to execute and based on the investigation user can update their queries for the maximum result.

Query Analyzer has an option, called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on, it will display a query execution plan in a separate window when the query is run again.

Q #19) What is the Stored Procedure?

Answer: A stored procedure is a set of SQL queries that can take input and send back output. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

Q #20) List the advantages of using Stored Procedures?

Answer: Advantages of using Stored procedures are:

• Stored procedure boosts application performance.
• Stored procedure execution plans can be reused as they cached in SQL Server’s memory which reduces server overhead.
• They can be reused.
• It can encapsulate logic. You can change the stored procedure code without affecting clients.
• They provide better security for your data.

Q #21) What is identity in SQL?

Answer: An identity column in the SQL automatically generates numeric values. We can be defined as a start and increment value of the identity column. Identity columns do not need to be indexed.

Q #22) What are the common performance issues in SQL Server?

Answer: Following are the common performance issues:

• Deadlocks
• Blocking
• Missing and unused indexes.
• I/O bottlenecks
• Poor Query plans
• Fragmentation

Q #23) List the various tools available for performance tuning?

Answer: Various tools available for performance tuning are:

• Dynamic Management Views
• SQL Server Profiler
• Server Side Traces
• Windows Performance monitor.
• Query Plans
• Tuning advisor

Q #24) What is a performance monitor?

Answer: Windows performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of the SQL server also.
Some useful counters are – Disks, Memory, Processors, Network, etc.

Q #25) What are 3 ways to get a count of the number of records in a table?

Answer:

SELECT * FROM table_Name; SELECT COUNT(*) FROM table_Name; SELECT rows FROM indexes WHERE id = OBJECT_ID(tableName) AND indid< 2;

Q #26) Can we rename a column in the output of the SQL query?

Answer: Yes, by using the following syntax we can do this.

SELECT column_name AS new_name FROM table_name;

Q #27) What is the difference between a Local and a Global temporary table?

Answer: If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.

Q #28) What is the SQL Profiler?

Answer: SQL Profiler provides a graphical representation of events in an instance of SQL Server for monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to captures the specific data we want.

Q #29) What do you mean by authentication modes in SQL Server?

Answer: There are two authentication modes in SQL Server.

• Windows mode
• Mixed Mode – SQL and Windows.

Q #30) How can we check the SQL Server version?

Answer: By running the following command:

SELECT @@Version

Q #31) Is it possible to call a stored procedure within a stored procedure?

Answer: Yes, we can call a stored procedure within a stored procedure. It is called the recursion property of the SQL server and these types of stored procedures are called nested stored procedures.

Q #32) What is the SQL Server Agent?

Answer: SQL Server agent allows us to schedule the jobs and scripts. It helps in implementing the day to day DBA tasks by automatically executing them on a scheduled basis.

Q #33) What is the PRIMARY KEY?

Answer: The primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused.

Q #34) What is a UNIQUE KEY constraint?

Answer: A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

Q #35) What is FOREIGN KEY

Answer: When a one table’s primary key field is added to related tables to create the common field which relates the two tables, it called a foreign key in other tables.

Foreign Key constraints enforce referential integrity.

Q #36) What is a CHECK Constraint?

Answer: A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.

Q #37) What are a Scheduled Jobs?

Answer: The scheduled job allows a user to run the scripts or SQL commands automatically on a scheduled basis. The user can determine the order in which command executes and the best time to run the job to avoid the load on the system.

Q #38) What is a heap?

Answer: A heap is a table that does not contain any clustered index or non-clustered index.

Q #39) What is BCP?

Answer: BCP or Bulk Copy is a tool by which we can copy a large amount of data to tables and views. BCP does not copy the structures the same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

Q #40) What is Normalization?

Answer: The process of table design to minimize the data redundancy is called normalization. We need to divide a database into two or more tables and define relationships between them. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.

Q #41) List the different normalization forms?

Answer: Different normalization forms are:

• 1NF (Eliminate Repeating Groups): Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
• 2NF (Eliminate Redundant Data): If an attribute depends on only part of a multi-valued key, remove it to a separate table.
• 3NF (Eliminate Columns Not Dependent On Key): If attributes do not contribute to the description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
• BCNF (Boyce-Codd Normal Form): If there are non-trivial dependencies between candidate key attributes, separate them into distinct tables.
• 4NF (Isolate Independent Multiple Relationships): No table may contain two or more 1:n or n:m relationships that are not directly related.
• 5NF (Isolate Semantically Related Multiple Relationships): There may be practical constraints on information that justifies separating logically related many-to-many relationships.
• ONF (Optimal Normal Form): A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
• DKNF (Domain-Key Normal Form): A model free from all modification is said to be in DKNF.

Q #42) What is De-normalization?

Answer: De-normalization is the process of adding redundant data to a database to enhance the performance of it. It is a technique to move from higher to lower normal forms of database modeling to speed up database access.

Q #43) What is a Trigger and types of a trigger?

Answer: The trigger allows us to execute a batch of SQL code when table event occurs (INSERT, UPDATE or DELETE command executed against a specific table). Triggers are stored in and managed by DBMS. It can also execute a stored procedure.

3 types of triggers that are available in the SQL Server are as follows:

• DML Triggers: DML or Data Manipulation Language triggers are invoked whenever any of the DML commands like INSERT, DELETE or UPDATE happens on the table or the view.
• DDL Triggers: DDL or Data Definition Language triggers are invoked whenever any changes occur in the definition of any of the database objects instead of actual data. These are very helpful to control the production and development of database environments.
• Logon Triggers: These are very special triggers that fire in case of the logon event of the SQL Server. This is fired before the setup of a user session in the SQL Server.

Q #44) What is the Subquery?

Answer: A Subquery is a subset of SELECT statements, whose return values are used in filtering conditions of the main query. It can occur in a SELECT clause, FROM clause and WHERE clause. It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.

Types of Sub-query:

• Single-row sub-query: The subquery returns only one row
• Multiple-row sub-query: The subquery returns multiple rows
• Multiple column sub-query: The subquery returns multiple columns

Q #45) What is a Linked Server?

Answer: Linked Server is a concept by which we can connect another SQL server to a Group and query both the SQL Servers database using T-SQL Statements sp_addlinkedsrvloginisssed to add link server.

Q #46) What is Collation?

Answer: Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types, and character width.

Q #47) What is View?

Answer: A view is a virtual table that contains data from one or more tables. Views restrict data access of the table by selecting only required values and make complex queries easy.

Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database

Q #48) Where SQL server usernames and passwords are stored in a SQL server?

Answer: They get stored in System Catalog Views sys.server_principals and sys.sql_logins.

Q #49) What are the properties of a transaction?

Answer: Generally, these properties are referred to as ACID properties.

They are:

• Atomicity
• Consistency
• Isolation
• Durability

Q #50) Define UNION, UNION ALL, MINUS, INTERSECT?

Answer: 

• UNION – returns all distinct rows selected by either query.
• UNION ALL – returns all rows selected by either query, including all duplicates.
• MINUS – returns all distinct rows selected by the first query but not by the second.
• INTERSECT – returns all distinct rows selected by both queries.

Q #51) What is SQL Server used for?

Answer: SQL Server is one of the very popular Relational Database Management Systems. This is a product from Microsoft to store and manage the information in the database.

Q #52) Which language is supported by SQL Server?

Answer: SQL Server is based upon the implementation of the SQL also known as Structured Query Language to work with the data inside the database.

Q #53) Which is the latest version of SQL Server and when it is released?

Answer: SQL Server 2019 is the latest version of SQL Server that is available in the market and Microsoft launched this on November 4th, 2019 with the support of the Linux O/S.

Q #54) What are the various editions of SQL Server 2019 that are available in the market?

Answer: SQL Server 2019 is available in 5 editions. These are as follows:

• Enterprise: This delivers comprehensive high-end datacenter capabilities with blazing-fast performance, unlimited virtualization, and end-to-end business intelligence for mission-critical workloads and end-user access to data insights.
• Standard: This delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premises and cloud-enabling effective database management.
• Web: This edition is a low total-cost-of-ownership option for Web hosters and Web VAPs to provide scalability, affordability, and manageability capabilities for small to large-scale Web properties.
• Express: Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications.
• Developer: This edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server.

Q #55) What are functions in the SQL Server?

Answer: Functions are the sequence of the statements which accept inputs, process the inputs to perform some specific task and then provide the outputs. Functions should have some meaningful name but these should not start with a special character such as %,#,@, etc.

Q #56) What is a User-Defined function in the SQL Server and what is its advantage?

Answer: User-Defined Function is a function that can be written as per the needs of the user by implementing your logic. The biggest advantage of this function is that the user is not limited to pre-defined functions and can simplify the complex code of pre-defined function by writing a simple code as per the requirement.

This returns Scalar value or a table.

Q #57) Explain the creation and execution of a user-defined function in the SQL Server?

Answer: A User-Defined function can be created in the following way:

CREATE Function fun1(@num int) returns table as return SELECT * from employee WHERE empid=@num;

This function can be executed as follows:

SELECT * from fun1(12);

So, in the above case, a function with the name of ‘fun1’ is created to fetch employee details of an employee having empid=12.

 Q #58) What are the Pre-Defined functions in the SQL Server?

Answer: These are built-in functions of the SQL Server like String functions which are provided by SQL Server like ASCII, CHAR, LEFT, etc. string functions.

Q #59) Why are Views required in the SQL Server or any other database?

Answer: Views are very beneficial because of the following reasons:

• Views are required to hide the complexity that is involved in the database schema and also to customize the data for a particular set of users.
• Views provide a mechanism to control access to particular rows and columns.
• These help in aggregating the data to improve the performance of the database.

Q #60) What is TCL in SQL Server?

Answer: TCL is Transaction Control Language Commands which are used to manage the transactions in the SQL Server.

Q #61) Which TCL Commands are available on the SQL Server?

Answer: There are 3 TCL Commands in the SQL Server. These are as follows:

• Commit: This command is used to save the transaction permanently in the database.
• Rollback: This is used to roll back the changes that are done i.e. to restore the database in the last committed state.
• Save Tran: This is used for saving the transaction to provide the convenience that the transaction can be rolled back to the point wherever required.

Q #62) What are the 2 types of classifications of constraints in the SQL Server?

Answer: Constraints are classified into the following 2 types in the SQL Server:

• Column Types Constraints: These constraints are applied to the columns of a table in the SQL Server. The definition of these can be given at the time of the creation of a table in the database.
• Table Types Constraints: These constraints are applied on a table and these are defined after the creation of a table is complete. Alter command is used to apply the table type constraint.

Q #63) How is table type constraint applied to a table?

Answer: Table Type Constraint is applied in the following way:

Alter Table Name of the Constraint

Alter Table Constraint_1

Q #64) What are the different types of Columns Types Constraints in the SQL Server?

Answer: SQL Server provides 6 types of Constraints. These are as follows:

1. Not Null Constraint: This puts a constraint that the value of a column cannot be null.
2. Check Constraint: This puts a constraint by checking some particular condition before inserting data in the table.
3. Default Constraint: This constraint provides some default value that can be inserted in the column if no value is specified for that column.
4. Unique Constraint: This puts a constraint that each row of a particular column must have a unique value. More than one unique constraint can be applied to a single table.
5. Primary Key Constraint: This puts a constraint to have a primary key in the table to identify each row of a table uniquely. This cannot be null or duplicate data.
6. Foreign Key Constraint: This puts a constraint that the foreign key should be there. A Primary key in one table is the foreign key of another table. Foreign Key is used to create a relation between 2 or more tables.

Q #65) What command is used to delete a table from the database in the SQL Server and how?

Answer: DELETE Command is used to delete any table from the database in the SQL Server.

Syntax: DELETE Name of the table

Example: If the name of a table is “employee” then DELETE command to delete this table can be written as

DELETE employee;

Q #66) Why is replication required on the SQL Server?

Answer: Replication is the mechanism that is used to synchronize the data among the multiple servers with the help of a replica set.

This is mainly used to increase the capacity of reading and to provide an option to its users to select among various servers to perform the read/write operations.

Q #67) What command is used to create a database in the SQL Server and how?

Answer: CREATEDATABASE Command is used to create any database in the SQL Server.

Syntax: CREATEDATABASE Name of the Database

Example: If the name of a database is “employee” then create command to create this database that can be written as CREATEDATABASE employee.

Q #68) What function does a database engine serve in the SQL Server?

Answer: Database Engine is a type of service in the SQL Server which starts as soon as the Operating System starts. This may run by default depending upon the settings in the O/S.

Q #69) What are the advantages of having an index on the SQL Server?

Answer: The index has the following advantages:

• Index supports the mechanism of having faster data retrieval from the database.
• This forms a data structure in a way that helps in minimizing data comparisons.
• This improves the performance of the retrieval of the data from the database.

Q #70) 12 Query optimization tips for better performance?

Answer: https://blog.devart.com/how-to-optimize-sql-query.html

• Tip 1: Add missing indexes
• Tip 2: Check for unused indexes
• Tip 3: Avoid using multiple OR in the FILTER predicate
• Tip 4: Use wildcards at the end of a phrase only
• Tip 5: Avoid too many JOINs
• Tip 6: Avoid using SELECT DISTINCT
• Tip 7: Use SELECT fields instead of SELECT *
• Tip 8: Use TOP to sample query results
• Tip 9: Run the query during off-peak hours
• Tip 10: Minimize the usage of any query hint
• Tip 11: Minimize large write operations
• Tip 12: Create joins with INNER JOIN (not WHERE)
.

Difference Between SQL And NoSQL

SQL	NoSQL
SQL databases are mainly relational database (RDBMS).	NoSQL databases are mainly non-relational or distributed databases.
An aged technology.	Relatively young technology.
SQL databases are table based in the form of row & columns and must strictly adhere to standard schema definitions. They are a better option for applications which need multi-row transactions.	NoSQL databases can be based on documents, key-value pairs, graphs or columns and they don’t have to stick to standard schema definitions.
They have a well-designed pre-defined schema for structured data.	They have the dynamic schema for unstructured data. Data can be flexibly stored without having a pre-defined structure.
SQL databases favors normalized schema.	NoSQL databases favors de-normalized schema.
Costly to scale.	Cheaper to scale when compared to relational databases.
SQL databases are vertically scalable. They can be scaled by increasing the hardware capacity (CPU, RAM, SSD, etc.) on a single server.	NoSQL databases are horizontally scalable. They can be scaled by adding more servers to the infrastructure to manage large load and lessen the heap.
They are a good fit for complex queries as SQL has a standard interface for handling queries. The syntax of SQL queries is fixed.	Not a good fit for complex queries as there is no standard interface in NoSQL for handling queries. The queries in NoSQL are not as powerful as SQL queries. It is called as UnQL, and the syntax for using the Unstructured query language will vary from syntax to syntax.
SQL databases do not suit well for hierarchical data storage.	NoSQL databases suit best for hierarchical data storage as it follows the key-value pair method for storing the data.
From a commercial perspective, SQL databases are generally classified as open source or closed source.	They are classified on the basis of the way they store data as key-value store, document store, graph store, column store, and XML store.
SQL databases properly follow ACID properties (Atomicity, Consistency, Isolation & Durability).	NoSQL databases properly follow Brewers CAP theorem (Consistency, Availability, and Partition tolerance).
Adding new data in SQL database requires some changes to be made like backfilling data, altering schemas.	New data can be easily inserted in NoSQL databases as it does not require any prior steps.
Excellent vendor support and community support is available for all SQL databases.	Only limited community support is available for NoSQL databases.
Best fit for high transaction-based applications.	You can use NoSQL for heavy transactional purpose. However, it is not the best fit for this.
Not suitable for hierarchical data storage.	Suitable for hierarchical data storage and storing large data sets (E.g. Big Data).
Example of SQL databases: MySQL, Oracle, MS-SQL, SQLite.	Examples of NoSQL databases: MongoDB, Apache CouchDB, Redis, HBase.

When to Use NoSQL?

Given below are the use cases where you should prefer using NoSQL databases:

• To handle a huge volume of structured, semi-structured and unstructured data.
• Where there is a need to follow modern software development practices like Agile Scrum and if you need to deliver prototypes or fast applications.
• If you prefer object-oriented programming.
• If your relational database is not capable enough to scale up to your traffic at an acceptable cost.
• If you want to have an efficient, scale-out architecture in place of an expensive and monolithic architecture.
• If you have local data transactions that need not be very durable.
• If you are going with schema-less data and want to include new fields without any ceremony.
• When your priority is easy scalability and availability.

When to Avoid NoSQL?

Enlisted below are some pointers that would guide you on when to avoid NoSQL.

• If you are required to perform complex and dynamic querying and reporting, then you should avoid using NoSQL as it has a limited query functionality. For such requirements, you should prefer SQL only.
• NoSQL also lacks in the ability to perform dynamic operations. It can’t guarantee ACID properties. In such cases like financial transactions, etc., you may go with SQL databases.
• You should also avoid NoSQL if your application needs run-time flexibility.
• If consistency is a must and if there aren’t going to be any large-scale changes in terms of the data volume, then going with the SQL database is a better option.

One should also keep in mind that NoSQL databases won’t support structured query language. The querying language may vary from one database to another.

SQL Pros:

• It is highly suitable for relational databases.
• Has a predefined schema which is helpful in many cases.
• Normalization can be greatly used here, thus it also helps in removing redundancy and organizing data in a better way.
• Transactions in SQL databases are ACID compliant, thereby guarantees security and stability.
• Follows well-defined standards like ISI and ANSI which are accepted worldwide.
• Code-free.
• Unbeatable speed in retrieving database records with great ease.
• Uses single standardized language i.e SQL across different RDBMS.

SQL Cons:

• The process of interfacing is complex.
• As SQL is an object, it occupies space.
• Handling Big data is very costly as you will have to increase the hardware for scaling.
• When a table is dropped, the view becomes inactive.

NoSQL Pros:

• Capable of handling big data.
• As it is schema-less and table free, it offers a high level of flexibility with data models.
• It is a low-cost database and the open source NoSQL databases provide very affordable solutions to small enterprises.
• Easier and low-cost scalability. You don’t need to increase the hardware for scaling. You just need to add more servers to the pool as NoSQL is schema-free and built on distributed systems.
• Detailed database modeling is not required here. Hence it saves time and effort.

NoSQL Cons:

• The benefits of NoSQL come at the cost of relaxing ACID properties. NoSQL offers only eventual consistency.
• Relatively less community support.
• Lacks standardization, unlike SQL, which in turn creates some issues during migration.
• Inter-operability is also a concern in the case of NoSQL databases.

Types of NoSQL Databases:

1) Column: Wide column stores and arranges the data tables as columns rather than as rows.

They can query a large volume of data very quickly than the traditional databases. They can be employed for recommendation engines, catalogs, fraud detection, etc.

Examples: Cassandra, HBase, Google BigTable, Scylla, Vertica, etc.

2) Document: Document databases, aka document stores and keeps the semi-structured data along with its description in the document format.

Each document has a unique key through which it is addressed. They are helpful for content management and mobile application data handling. They are widely used along with JSON and JavaScript. Document databases also offer an API and query language through which the documents can be fetched based on their contents.

Examples: Apache, MongoDB, MarkLogic, CouchDB, BaseX, IBM Domino, etc.

3) Key-value: Key-value databases have their data model based on an associative array (map or a dictionary) in which the data has represented a collection of key-value pairs. They are highly suitable for session management and caching in web applications.

Examples: Aerospike, Berkeley DB, Apache ignites, Dynamo, Redis, Riak, ZooKeeper, etc.

4) Graph: In graph stores, data is organized as nodes and edges.

You can think of a node as a record and edge as a relationship between the records in the relational database. This model supports a richer representation of data relationships. They are useful for customer relationship Management systems, road maps, reservation systems, etc.

Examples: AllegroGraph, InfiniteGraph, MarkLogic, Neo4j, IBM graph, Titan, etc.