NoSQL Interview Questions and Answers [BEST & NEW]

Last updated on 09th Nov 2021, Blog, Interview Questions

These TypeScript Interview Questions have been designed specially to get you acquainted with the nature of questions you may encounter during your interview for the subject of TypeScript . As per my experience good interviewers hardly plan to ask any particular question during your interview, normally questions start with some basic concept of the subject and later they continue based on further discussion and what you answer.we are going to cover top 100 TypeScript  Interview questions along with their detailed answers. We will be covering TypeScript  scenario based interview questions, TypeScript  interview questions for freshers as well as TypeScript  interview questions and answers for experienced.

1. What is NoSQL?

Ans:

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 in which this data is accessed, and performance and processing needs. Relational databases, on the other hand, were 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.

2.Comparison Between NoSQL & RDBMS ?

Ans:

NoSQL is different from RDBMS in the following manner:

Criteria	NoSQL	RDBMS
Data format	Does not follow any order	Organized and structured
Scalability	Very Good	Average
Querying	Limited as no Join Clause	Using SQL
Storage mechanism	Key-Value Pair, document, column storage, etc.	Data & relationship stored in different tables

3. What are the features of NoSQL?

Ans:

When compared to relational databases, NoSQL databases are more scalable and provide superior performance, and their data model addresses several issues that the relational model is not designed to address :

• Large volumes of structured, semi-structured, and unstructured data.
• Agile sprints, quick iteration, and frequent code pushes.
• Object-oriented programming that is easy to use and flexible.
• Efficient, scale-out architecture instead of expensive, monolithic architecture.

4. Explain the difference between NoSQL v/s Relational database?

Ans:

The history seems to look like this :

Google needs a storage layer for its inverted search index. They figure a traditional RDBMS is not going to cut it. So they implement a NoSQL data store, BigTable on top of their GFS file system. The major part is that thousands of cheap commodity hardware machines provide speed and redundancy. Everyone else realizes what Google just did. Brewer’s CAP theorem is proven. All RDBMS systems of use are CA systems. People begin playing with CP and AP systems as well. K/V stores are vastly simpler, so they are the primary vehicle for the research.

Software-as-a-service systems, in general, do not provide an SQL-like store. Hence, people get more interested in the NoSQL type stores. I think much of the take-off can be related to this history. Scaling Google took some new ideas at Google and everyone else follows suit because this is the only solution they know to the scaling problem right now. Hence, you are willing to rework everything around the distributed database idea of Google because it is the only way to scale beyond a certain size. Get to know more about this NoSQL vs. SQL – What is Better? that can help you grow in your career.

5. How to script NoSQL DB configuration?

Ans:

These are as amenable to scripting as any other UNIX commands and will not be discussed further here.The interactive commands available in java -jar kvstore.jar runadmin, among which are those used to create and execute plans, can be scripted in two ways. You can create a file containing the sequence of commands that you want to run, and run them in a batch using java -jar kvstore.jar runadmin load -file <>.For example, a script file named deploy.kvs could contain commands such as the following :

• configure -name mystore  
• plan deploy-datacenter -name boston -rf 3 -wait
• plan deploy-sn -dcname boston -host localhost -port 5000 -wait
• plan deploy-admin -sn sn1 -port 5001 -wait
• You could execute this script by issuing the command
• java -jar kvstore.jar runadmin -host localhost -port 5000 load -file
• deploy.kv

6. Does NoSQL Database Interact With Oracle Database?

Ans:

NoSQL Database supports retrieving records through the Oracle Database External Table functions. This makes it possible to perform some queries from Oracle Database and retrieve records from NoSQL Database.

7. What is Cassandra Data Model?

Ans:

Cassandra data model consists of four main components:

• Cluster : Made up of multiple nodes and keyspaces
• Keyspace : A namespace to group multiple column families, especially one per partition
• Column : Consisting of a column name, value, and timestamp
• Column Family : Multiple columns with the row key reference

8. What are the advantages and disadvantages of NoSQL?

Ans:

Advantages of NoSQL :

• Using the new node concepts, the database of NoSQL can be expanded to the set limit. Low commodity hardware takes this point as an added advantage.
• NoSQL databases are used to store a massive volume of information. Big data and recently posted user reviews utilize the concept of NoSQL.
• There is no expensive administration required to monitor the database of NoSQL.
• NoSQL can be installed with cheap economy instead of spending money on complicated systems.

Disadvantages of NoSQL :

• Since NoSQL is a new technology of database storage, there are many other systems in the market which are already leading in the market.
• The previously built systems have the support available round the clock, but the NoSQL support system is less comparatively.
• Since NoSQL is a new technology, it is not supporting the old commands that are used in the system.

9. Enlist differences between SQL and NoSQL databases?

Ans:

Differences between SQL and NoSQL databases :

10. What is CAP theorem? How is it applicable to NoSQL systems?

Ans:

The CAP theorem was proposed by Eric Brewer in early 2000. In this, three system attributes have been discussed within the distributed databases. That is :

• Consistency – In this, all the nodes see the same data at the same time.
• Availability – It gives us a guarantee that there will be a response for every request made to the system about whether it was successful or not.
• Partition tolerance – It is the quality of NoSQL database management system which states that the system will work even if a part of the system has failed or is not working.

11. What different types of NoSQL Databases are you aware of?

Ans:

There are 4 most common types of NoSQL databases as below :

• Key-value databases
• Document databases
• Wide-column or Column-family databases
• Graph databases stores

12. What is DocumentDB?

Ans:

DocumentDB or Document database is a completely NoSQL database service that stores the data as schema-free JSON (JavaScript Object Notation) documents.You also need scalability, low cost and fast deployment for your data. In all these scenarios we consider DocumentDB. There are many DocumentDB services as below.

• Microsoft Azure Cosmos DB
• Amazon DocumentDB
• MongoDB

13. What are the major challenges with traditional RDBMS?

Ans:

Following are some of the major challenges with RDBMS systems :

• Not optimized to scale out : RDBMS systems are not optimized for horizontal scaling out.
• Not able to handle unstructured data : RDBMS systems are not able to handle schema-less data (semi-structured or unstructured)
• Costly : There is a high licensing cost for data analysis with RDBMS systems.
• Not able to handle the high velocity of data ingestion : RDBMS systems are designed for steady data retention.

The NoSQL systems evolved to overcome all the above challenges.

14. How Does NoSQL relate to big data?

Ans:

NoSQL databases are designed with “Big Data” needs in mind. Since a fixed schema model does not bind them, this makes them suitable for today’s business needs where there is a large volume of non-uniform data (Big Data).

15. What is Document Database in NoSQL?

Ans:

16. When should I use a NoSQL database instead of a relational database?

Ans:

Relational databases enforces ACID. So, you will have schema based transaction oriented data stores. It’s proven and suitable for 99% of the real world applications. You can practically do anything with relational databases.

But, there are limitations on speed and scaling when it comes to massive high availability data stores. For example, Google and Amazon have terabytes of data stored in big data centers. Querying and inserting is not performant in these scenarios because of the blocking/schema/transaction nature of the RDBMs. That’s the reason they have implemented their own databases (actually, key-value stores) for massive performance gain and scalability.

If you need a NoSQL db you usually know about it, possible reasons are:

• client wants 99.999% availability on a high traffic site.
• your data makes no sense in SQL, you find yourself doing multiple JOIN queries for accessing some piece of information.
• you are breaking the relational model, you have CLOBs that store denormalized data and you generate external indexes to search that data.

17. Explain difference between scaling horizontally and vertically for databases

Ans:

In a database world horizontal-scaling is often based on the partitioning of the data i.e. each node contains only part of the data, in vertical-scaling the data resides on a single node and scaling is done through multi-core i.e. spreading the load between the CPU and RAM resources of that machine.

Good examples of horizontal scaling are Cassandra, MongoDB, Google Cloud Spanner. and a good example of vertical scaling is MySQL – Amazon RDS (The cloud version of MySQL).

• Horizontal scaling means that you scale by adding more machines into your pool of resources whereas.
• Vertical scaling means that you scale by adding more power (CPU, RAM) to an existing machine.

18. When should we embed one document within another in MongoDB?

Ans:

You should consider embedding documents for:

• contains relationships between entities
• One-to-many relationships
• Performance reasons

19. How do I perform the SQL JOIN equivalent in MongoDB?

Ans:

Mongo is not a relational database, and the devs are being careful to recommend specific use cases for $lookup, but at least as of 3.2 doing join is now possible with MongoDB. The new $lookup operator added to the aggregation pipeline is essentially identical to a left outer join :

20. How does column-oriented NoSQL differ from document-oriented?

Ans:

The main difference is that document stores (e.g. MongoDB and CouchDB) allow arbitrarily complex documents, i.e. subdocuments within subdocuments, lists with documents, etc. whereas column stores (e.g. Cassandra and HBase) only allow a fixed format, e.g. strict one-level or two-level dictionaries.

For Example :

A document-oriented database (like MongoDB) inserts whole documents (typically JSON), whereas in Cassandra (column-oriented db) you can address individual columns or supercolumns, and update these individually, i.e. they work at a different level of granularity. Each column has its own separate timestamp/version (used to reconcile updates across the distributed cluster).

21. What is Sharding in MongoDB?

Ans:

Sharding is a method for storing data across multiple machines. MongoDB uses sharding to support deployments with very large data sets and high throughput operations.

22. When should I use a NoSQL database instead of a relational database?

Ans:

Relational databases enforces ACID. So, you will have schema based transaction oriented data stores. It’s proven and suitable for 99% of the real world applications. You can practically do anything with relational databases.

But, there are limitations on speed and scaling when it comes to massive high availability data stores. For example, Google and Amazon have terabytes of data stored in big data centers. Querying and inserting is not performant in these scenarios because of the blocking/schema/transaction nature of the RDBMs.

If you need a NoSQL db you usually know about it, possible reasons are :

• Client wants 99.999% availability on a high traffic site.
• Your data makes no sense in SQL, you find yourself doing multiple JOIN queries for accessing some piece of information.
• You are breaking the relational model, you have CLOBs that store denormalized data and you generate external indexes to search that data.

23. What Are The Various Categories On Nosql?

Ans:

The various categories on NOSQL :

• Key­Value Store Database
• Column Family Database
• Document Store Database
• Graph Database
• Multivalue Database
• Object Database
• Tripple Store Database
• Tuple Store Database
• Tabular Database

24. How to script NoSQL DB configuration?

Ans:

You may find that you want to build the same NoSQL DB configuration repeatedly for testing purposes. The Admin CLI commands can be scripted in several ways.Many uses of the Admin CLI are simple commands, such as java -jar kvstore.jar makebootconfig to initially configure a StorageNode, shown above.

These are as amenable to scripting as any other UNIX commands and will not be discussed further here.The interactive commands available in java -jar kvstore.jar runadmin, among which are those used to create and execute plans, can be scripted in two ways.

25. List the many types of NoSQL data stores

Ans:

The four types of no SQL data storage are commonly accessible or divided into four categories. They are as follows :

• Key-value store – A key-value store is a basic data storage key system that uses a key to access various values.
• Column family store – It’s a sparse metric system with column family storage. The keys are columns and rows.
• Graph store – The graph store is utilized for dealing with situations that need a lot of relationships.
• Document store – document stores are used to store hierarchical data structures in the database directly.

26. What exactly is the goal of NoSQL?

Ans:

Non-structured data can be stored in NoSQL databases. The NoSQL data format is a novel way to store big datasets. When it comes to storing textual data, NoSQL is a viable alternative to SQL databases.

27. What Are The Pros And Cons Of Graph Database?

Ans:

• Pros :

Graph databases appear to be tailor-made for networking applications. The prototypical example is a social community, in which nodes constitute users who have various forms of relationships to every other. Modeling this type of facts using any of the other patterns is often a tough fit, but a graph database might take delivery of it with savor. They are also perfect suits for an item-oriented machine.

• Cons :

Because of the high diploma of interconnectedness among nodes, graph databases are usually now not appropriate for community partitioning. Graph databases don’t scale out well.

28. What is NoSQL Database Architecture ?

Ans:

29. Explain How Cassandra Writes?

Ans:

Cassandra writes first to a commit log on disk for sturdiness then commits to an in­reminiscence shape known as a memtable. A write is a success as soon as each commits are complete. Writes are batched in memory and written to disk in a desk shape referred to as an SSTable (looked after string desk). Memtables and SSTables are created in line with column own family. With this layout Cassandra has minimum disk I/O and offers excessive pace write performance because the commit log is append­best and Cassandra doesn’t are trying to find on writes. In the occasion of a fault whilst writing to the SSTable Cassandra can truely replay the commit log.

30. What Is The Key Difference Between Replication And Sharding?

Ans:

Replication takes the identical data and copies it over multiple nodes. Sharding puts specific statistics on different nodes Sharding is in particular treasured for overall performance due to the fact it can enhance each examination and write overall performance. Using replication, particularly with caching, can greatly enhance examine performance however does little for packages that have a whole lot of writes. Sharding provides a manner to horizontally scale

31. Explain About Cassandra NoSQL?

Ans:

• Cassandra is an open supply scalable and extraordinarily available “NoSQL” dispensed database management machine from Apache. Cassandra claims to offer fault-tolerant linear scalability and not use a single factor of failure. Cassandra sits within the Column­Family NoSQL camp.
• The Cassandra statistics version is designed for large-scale dispensed statistics and trades ACID-compliant records practices for overall performance and availability. Cassandra is optimized for terribly speedy and particularly to be had writes. Cassandra is written in Java and may run on an extensive array of working systems and platforms.

32. Explain Cassandra Data Model?

Ans:

The Cassandra facts version has the 4 most important ideas which can be a cluster, keyspace, column, column own family. Clusters include many nodes (machines) and may incorporate more than one keyspace. A keyspace is a namespace to institution a couple of column families, normally one in keeping with the application. A column includes a call, fee, and timestamp. A column’s own family includes a couple of columns referenced by using a row key.

33. Explain How Cassandra Writes?

Ans:

• Cassandra writes first to a commit log on disk for sturdiness then commits to an in­reminiscence shape known as a memorable. A write is a success as soon as each commit is complete. Writes are batched in memory and written to disk in a desk shape referred to as an SSTable (looked after string desk). Memtables and SSTables are created in line with the column’s own family.
• With this layout, Cassandra has minimum disk I/O and offers excessive pace write performance because the commit log is append­best and Cassandra doesn’t are trying to find on writes. In the occasion of a fault whilst writing to the SSTable Cassandra can truly replay the commit log.

34. Understanding the difference between Azure Cosmos DB NoSQL and Relational database?

Ans:

35. What Is Flume?

Ans:

• Flume is an open-source software program developed with the aid of the Cloud era that acts as a provider for aggregating and moving big quantities of records around a Hadoop cluster because the records are produced or shortly thereafter. Its number one use case is the gathering of log documents from all of the machines in a cluster to persist them in a centralized keep together with HDFS.
• In Flume, we create record flows by means of building up chains of logical nodes and connecting them to resources and sinks. For example, say we desire to move facts from an Apache get admission to log into HDFS. You create a source using tailing get admission to log and use a logical node to course this to an HDFS sink.

36. What Is Bigsql?

Ans:

Big Data is the fruit of numerous studies and development initiatives at IBM. So IBM has taken the paintings from those diverse initiatives and released them as an era preview known as Big SQL.

• IBM claims that Big SQL offers sturdy SQL assist for the Hadoop atmosphere :
• It has a scalable structure
• It helps SQL and facts sorts to be had in SQL ’92, plus it has some extra capabilities
• It supports JDBC and ODBC patron drivers
• It has efficient dealing with of “factor queries”
• Big SQL is based totally on a multi­threaded architecture, so it’s good for performance and the scalability in a Big SQL surroundings basically depends at the Hadoop cluster itself this is its size and scheduling policies.

37. How Big Sql Works?

Ans:

The Big SQL engine analyzes incoming queries. It separates portions to execute at the server versus the portions to be carried out by using the cluster. It rewrites queries if essential for stepped forward overall performance? determines the correct garage take care of for data? produces the execution plan and executes and coordinates the question.

IBM architected Big SQL with the goal that existing queries ought to run with no or few modifications and that queries should be performed as effectively as the chosen storage mechanisms permit. And in preference to constructing a separate question execution infrastructure, they made Big SQL rely a good deal on Hive, a lot of the facts manipulation language, the facts definition language syntax, and the overall standards of Big SQL are similar to Hive. And Big SQL shares catalogs with Hive thru the Hive metastore. Hence everyone can query every different’s table.

38. What Is “polyglot Persistence” In Nosql?

Ans:

Neal Ford coined the time period polyglot programming, to express the concept that applications have to be written in a mix of languages to take advantage of the reality that different languages are suitable for tackling one of a kind problems. Complex packages integrate specific types of troubles, so choosing the right language for each task may be greater effective than seeking to fit all components into a single language.

Similarly, when working on an e­trade business hassle, the usage of a facts store for the purchasing cart which is exceptionally available and may scale is crucial, but the same records keep can not help you find products offered by way of the customers’ buddies—which is a totally exceptional question. We use the time period polyglot persistence to define this hybrid method to staying power.

39. What is a trigger?

Ans:

Triggers are stored programs that get automatically executed when an event such as INSERT, DELETE, UPDATE(DML) statement occurs. Triggers can also be evoked in response to Data definition statements(DDL) and database operations, for example, SERVER ERROR, LOGON.

40. What are the subsets of SQL?

Ans:

The following are the subsets of SQL :

• DDL(Data Definition Language): Includes SQL commands like CREATE, ALTER, and DELETE.
• DML(Data Manipulation Language): Accesses and manipulates data Uses INSERT, UPDATE commands.
• DCL(Data Control Language): Controls access to the database. Uses commands like GRANT and REVOKE.

41. What is a Cross Join?

Ans:

In a SQL cross join, a combination of every row from the two tables is included in the result set. This is also called Cross Product Join. For example, if table A has ten rows and table B has 20 rows, the result set will have 10 * 20 = 200 rows provided there is NOWHERE clause in the SQL statement.

42. What are the scalar functions in NoSQL? Give an example?

Ans:

Scalar Functions are used to return a single value based on the input values. Scalar Functions are as follows :

• UCASE(): Converts the specified field in upper case
• LCASE(): Converts the specified field in lower case

43. What is a set-based solution?

Ans:

Cursors operate on individual rows, and in the case of a set, it works on a resultant set of data, which could be a table/view or a join of both. The resultant set is an output of a SQL query.

44. What is de-normalization, and when do you go for it?

Ans:

De-normalization is a technique sometimes used to improve performance so the table design allows redundant data to avoid complex joins. If the application involves heavy read operations, then de-normalization is used at the expense of the write operations performance.

45. What is a transaction log?

Ans:

A log is an audit trail file where the history of actions executed by the DBMS is stored.

46. What are Object Privileges?

Ans:

An object-level privilege is a permission granted to a database user account or role to perform some action on a database object. These object privileges include SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX on tables, and so on.

The following examples are object privileges that can be granted to users :

• SELECT ON hr.employees TO myuser
• INSERT ON hr.employees TO myuser

47. What is data integrity?

Ans:

Data integrity defines the accuracy, consistency, and reliability of data that is stored in the database.

There are four kinds of data integrity :

• Row integrity
• Column integrity
• Referential integrity
• User-defined integrity

48. How is data in a NoSQL database stored?

Ans:

It depends on the type of NoSQL database is used. Each database stores the data using different methods like some use column store, some graphs, and some documents, etc. Below is the explanation of each database and how they store the data.

• Graph stores : It stores the data in the form of graphs.
• Key-value stores : In this, each item stored with an attribute name along with its value. For example Riak, Berkeley, etc.
• Wide Column stores : Data is stored in the columns instead of rows. Examples of such data stores are Cassandra and HBase.

49. NoSQL Developer Skills

Ans:

Here are some features to watch out for when looking for an experienced NoSQL developer :

• Should be able to install, configure, upgrade, and patch database software (MySQL cluster, Cassandra, MySQL, and Couchbase)
• Should be able to handle operations for both production and development databases
• Should be able to troubleshoot NoSQL problems and performance delays
• Should be able to make and configure monitors to set the health of servers and the databases
• Should be able to develop, enforce, manage, and automate the backup and recovery architecture as per requirements

50. Explain recursive stored procedure?

Ans:

Stored procedure calling itself until it reaches some boundary condition is a recursive stored procedure. It enables the programmers to use a set of code n number of times.

51. What is the stored procedure?

Ans:

It is a function consisting of many SQL statements to access the database system. Several SQL statements are consolidated into a stored procedure and are executed wherever and whenever required.

52. What is ALIAS command?

Ans:

This command provides another name to a table or a column. It can be used in WHERE clause of a SQL query using the as keyword.

Example :

• SELECT S.StudentID, E.Result from student S, Exam as E where S.StudentID = E.StudentID

53. What are STUFF and REPLACE functions?

Ans:

STUFF : overwriteS existing character or inserts a string into another string.

Syntax :



REPLACE : replaces the existing characters of all the occurrences.

Syntax :

54. How can dynamic SQL be executed?

Ans:

It can be executed by the following ways :

• By executing the query with parameters.
• By using EXEC
• By using sp_executesql

55. What is the TRUNCATE command? How is it different from the DELETE command?

Ans:

DELETE	TRUNCATE
DML command	DDL command
We can use WHERE	clause We cannot use WHERE clause
Deletes a row from the table.	Deletes all rows from the table.
We can rollback.	We cannot rollback.

56. What are the different case manipulation functions in SQL?

Ans:

• LOWER : Converts all the characters to lowercase.
• UPPER : Converts all the characters to uppercase.
• INITCAP : Converts initial character of each word to uppercase

57. What are the character manipulation functions?

Ans:

Character manipulation functions alter, extract and change the character string.

58. What are the different operators in SQL?

Ans:

• Arithmetic
• Comparison
• Logical

59. What is the MERGE statement?

Ans:

The statement enables conditional updates or inserts into the table. It updates the row if it exists or inserts the row if it does not exist.

60.Distinguish between BETWEEN and IN conditional operators.

Ans:

61. Why is NoSQL document-oriented data model an attractive choice?

Ans:

As data-sensitive organizations have faced many data-related problems, so a new solution has been introduced and the answer is NoSQL Document Databases. It is attractive to the users because it keeps data in documents rather than rows and columns. Some minimum standard format rules must be followed by these documents. The format choices could be JSOS, XML, YAML, etc. The priority is always JSON for NoSQL databases due to its better compatibility and readability.

62. What is the primary key?

Ans:

A primary key constraint uniquely identifies each row/record in a database table. Primary keys must contain unique values. Null value and duplicate values are not allowed to be entered in the primary key column. A table can have only one primary key. It can consist of single or multiple fields.

63. What are the features of MySQL?

Ans:

Here are some of the important features of MySQL :

• It is reliable and easy to use
• It supports standard SQL (Structured Query Language)
• MySQL is secure as it consists of a data security layer that protects sensitive data from unauthorized users
• MySQL has a flexible structure and supports a large number of embedded applications
• It is one of the very fast database languages

64. Explain the different types of indexes in SQL?

Ans:

There are three types of indexes in SQL :

• Unique Index – It does not allow a field to have duplicate values if the column is unique indexed.
• Clustered Index – This index defines the order in which data is physically stored in a table. It reorders the physical order of the table and searches based on key values. There can be only one clustered index per table./li>
• Non-Clustered Index – It does not sort the physical order of the table and maintains a logical order of the data. Each table can have more than one non-clustered index./li>

65. What is a subquery in SQL? What are the different types of a subquery?

Ans:

A subquery is a query within another query. When there is a query within a query, the outer query is called the main query, while the inner query is called a subquery. There are two types of a subquery :

• Correlated subquery : It obtains values from its outer query before it executes. When the subquery returns, it passes its results to the outer query.
• Non-Correlated subquery : It executes independently of the outer query. The subquery executes first and then passes its results to the outer query. Both inner and outer queries can run separately.

66. What is collation sensitivity?

Ans:

Collation sensitivity defines the rules to sort and compare the strings of character data, based on correct character sequence, case-sensitivity, character width, and accent marks, among others.

67. Can you name different types of MySQL commands?

Ans:

SQL commands are divided into the following

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)

68. What is Database Relationship?

Ans:

A Database Relationship is defined as the connection between two relational database tables. The primary table has a foreign key that references the primary key of another table. There are three types of Database Relationship :

• One-to-one
• One-to-many
• Many-to-many

69. Mention the command used to get back the privileges offered by the GRANT command?

Ans:

REVOKE command is used to get back the privileges offered by the GRANT command.

70. What are the differences between the ‘WHERE’ Clause and the ‘HAVING’ Clause?

Ans:

Below are the major differences between the ‘WHERE’ Clause and the ‘HAVING’ Clause :

WHERE Clause	HAVING Clause
It performs filtration on individual rows based on the specified condition.	HAVING clause performs filtration on groups based on the specified condition.
It can be used without GROUP BY Clause.	It is always used with the GROUP BY Clause.
WHERE Clause is applied in row operations.	HAVING is applied in column operations.
We cannot use the WHERE clause with aggregate functions.	This clause works with aggregate functions.
WHERE comes before GROUP BY HAVING comes after GROUP BY.	HAVING comes after GROUP BY.
This clause acts as a pre-filter.	HAVING clause acts as a post-filter.
WHERE Clause can be used with SELECT, INSERT, UPDATE, and DELETE statement.	This Clause can only be used with SELECT statement.

71. How to create a table in SQL?

Ans:

• CREATE TABLE table_name (
• column1 datatype,
• column2 datatype,
• column3 datatype,
• ….
• );

72. How to delete a table in SQL?

Ans:

There are two ways to delete a table from sql: DROP and TRUNCATE. The DROP TABLE command is used to completely delete the table from the database. This is the command :

DROP TABLE table_name;

The above command will completely delete all the data present in the table along with the table itself.

73. What is Normalization in SQL?

Ans:

Normalization is used to decompose a larger, complex table into simple and smaller ones. This helps us in removing all the redundant data.

Generally, in a table, we will have a lot of redundant information which is not required, so it is better to divide this complex table into multiple smaller tables which contains only unique information.

74. How to change a table name in SQL?

Ans:

We will start off by giving the keywords ALTER TABLE, then we will follow it up by giving the original name of the table, after that, we will give in the keywords RENAME TO and finally, we will give the new table name.

For example, if we want to change the “employee” table to “employee_information”, this will be the command :

• ALTER TABLE employee
• RENAME TO employee_information;

75. How to find duplicate records in SQL?

Ans:

There are multiple ways to find duplicate records in SQL. Let’s see how can we find duplicate records using groupby :

• SELECT
• x,
• y,
• COUNT(*) occurrences
• FROM z1
• GROUP BY
• x,
• y
• HAVING
• COUNT(*) > 1;

76. What are the benefits of TypeScript?

Ans:

Cursors in SQL are used to store database tables. There are two types of cursors :

1. Implicit Cursor

2. Explicit Cursor

Implicit Cursor :

These implicit cursors are default cursors which are automatically created. A user cannot create an implicit cursor.

Explicit Cursor :

Explicit cursors are user-defined cursors. This is the syntax to create explicit cursor :

DECLARE cursor_name CURSOR FOR SELECT * FROM table_name

We start off by giving by keyword DECLARE, then we give the name of the cursor, after that we give the keywords CURSOR FOR SELECT * FROM, finally, we give in the name of the table.

77. How to change column data-type in SQL?

Ans:

We can change the data-type of the column using the alter table. This will be the command :

• ALTER TABLE table_name
• MODIFY COLUMN column_name datatype;

We start off by giving the keywords ALTER TABLE, then we will give in the name of the table. After that, we will give in the keywords MODIFY COLUMN. Going ahead, we will give in the name of the column for which we would want to change the datatype and finally we will give in the data type to which we would want to change.

78. How can dynamic SQL be executed?

Ans:

• By executing the query with parameters
• By using EXEC
• By using sp_executesql

79. What are the main differences between #temp tables and @table variables and which one is preferred ?

Ans:

1. SQL server can create column statistics on #temp tables.

2. Indexes can be created on #temp tables

3. @table variables are stored in memory up to a certain threshold

80. What are the cons of a traditional RDBS over NoSQL systems?

Ans:

• The object-relational mapping layer can be complex.
• Entity-relationship modeling must be completed before testing begins, which slows development.
• RDBMSs don’t scale out when joins are required.
• Sharding over many servers can be done but requires application code and will be operationally inefficient.
• Full-text search requires third-party tools.
• function NoReturnType(): void {}

81. How to create a stored procedure in SQL Server

Ans:

A Stored Procedure is nothing but a frequently used SQL query. Queries such as a SELECT query, which would often be used to retrieve a set of information many times within a database, can be saved as a Stored Procedure. The Stored Procedure, when called, executes the SQL query save within the Stored Procedure.

Syntax to create a Stored Proc :

82. How to connect SQL Server management studio to the local database

Ans:

• Launch the SQL Server Management Studio from the START menu.
• In the dialog box shown below, select the Server Type as Database Engine and Server Name as the name of your laptop/ desktop system and click on the Connect button.
• Select the Authentication as ‘Windows Authentication.
• A secure connection would be established, and the list of the available Databases will be loaded in the Object Explorer window pane.

83. What is the case when in SQL Server?

Ans:

Case When statements in SQL are used to run through many conditions and to return a value when one such condition is met. If none of the conditions is met in the When statements, then the value mentioned in the Else statement is returned.

Syntax :

84. What is 32 bit nuances?

Ans:

There is extra memory mapped file activity with journaling. This will further constrain the limited db size of 32 bit builds. Thus, for now journaling by default is disabled on 32 bit systems.

85. How long does replica set failover take?

Ans:

It may take 10-30 seconds for the primary to be declared down by the other members and a new primary elected. During this window of time, the cluster is down for “primary” operations – that is, writes and strong consistent reads. However, you may execute eventually consistent queries to secondaries at any time (in slaveOk mode), including during this window.

86. How to find server name in SQL Server

Ans:

Run the query SELECT @@version; to find the version and name of the SQL Server you are using.

87. How to install SQL Server management studio ?

Ans:

Launch Google and in the Search toolbar, type in SQL Server Management Studio’ download. Go to the routed website and click on the link to download. Once the download is complete, open the .exe file to install the content of the file. Once the installation is complete, refresh or restart the system, as required.

Alternatively, once SQL Server is installed and launched, it will prompt the user with an option to launch SQ Server Management Studio.

88. How to connect SQL Server management studio to the local database

Ans:

• Launch the SQL Server Management Studio from the START menu.
• In the dialog box shown below, select the Server Type as Database Engine and Server Name as the name of your laptop/ desktop system and click on the Connect button.
• Select the Authentication as ‘Windows Authentication.
• A secure connection would be established, and the list of the available Databases will be loaded in the Object Explorer window pane.

89. What is cte in SQL Server?

Ans:

CTEs are Common Table Expressions that are used to create temporary result tables from which data can be retrieved/ used. The standard syntax for a CTE with a SELECT statement is :

• WITH RESULT AS
• (SELECT COL1, COL2, COL3
• FROM EMPLOYEE)
• SELECT COL1, COL2 FROM RESULT
• CTEs can be used with Insert, Update or Delete statements as well.

90. Difference between RDBMS and NoSQL databases

Ans:

Let’s consider an example of storing information about a user and their hobbies. We need to store a user’s first name, last name, cell phone number, city, and hobbies.

In a relational database, we’d likely create two tables: one for Users and one for Hobbies.

91. Explain NoSQL database misconceptions

Ans:

Over the years, many misconceptions about NoSQL databases have spread throughout the developer community. In this section, we’ll discuss two of the most common misconceptions :

• Relationship data is best suited for relational databases.
• NoSQL databases don’t support ACID transactions.

92. What is eventual consistency?

Ans:

Eventual consistency is a property of distributed databases. Eventual consistency ensures that when an update is made to the database, eventually all nodes in the distributed database will reflect that update.

93. What language is used to query NoSQL?

Ans:

NoSQL databases span a variety of types and implementations. As a result, NoSQL databases can be queried using a variety of query languages and APIs. MongoDB, the world’s most popular NoSQL database, can be queried using the MongoDB Query Language (MQL).

94. Does NoSQL have schema?

Ans:

NoSQL databases typically have flexible schemas. Note that some NoSQL databases like MongoDB also have support for schema validation, so developers can lock down their schemas as much or as little as they’d like when they are ready.

95. What data model does NoSQL use?

Ans:

NoSQL databases fall into four main categories or types. One thing they have in common is that they do not use the rigid tabular row-and-column data model that traditional relational databases (sometimes called SQL databases) use.

Instead, NoSQL databases have a data model that reflects their particular category. Document databases can store a great deal of information in a single document and can nest documents. Key-value stores have a simple data model, just as their name implies. Wide column stores feature more variation in data types and the number of columns in use than row-oriented relational databases. Graph databases have data models based on graph theory, with data models made up of nodes and edges that relate those nodes

96. Is it Difficult to change tables and relationships.

Ans:

Alteration of the relationships between tables or addition of a new table could affect the existing relations. This means changing the schema.

Change of the schema would be like eliminating the existing one and devising a new schema.

Addition of a new functionality would need all the elements to support the new structure. Change is inevitable.

Example : Each extra column needs all the prior rows to have values for that column. Whereas in Cassandra (a NoSQL database), you can add a column to specific row partitions.

97. Explain RDBMS ACID properties of the database ?

Ans:

The ACID properties of a database are Atomicity, Consistency, Isolation and Durability. ‌

• Atomicity : An “all or nothing” approach. If any statement in the transaction fails, the entire transaction is rolled back.
• Consistency : The transaction must meet all protocols defined by the system. No half completed transactions.
• Isolation : No transaction has access to any other transaction that is in an intermediate or unfinished state. Each transaction is independent.
• Durability : Ensures that once a transaction commits to the database, it is preserved through the use of backups and transaction logs.

98. How does a columnar database store data?

Ans:

99. Why not use a spreadsheet?

Ans:

• It’s very convenient to use Excel for data analysis if you only need to handle thousands of rows and tables. But once you get to about 10,000 rows, you’ll need a database system to deal with gigantic datasets. (Excel can quickly freeze up.)
• If you are working for a company that has tens of thousands of Excel spreadsheets – on different computers, say – and the staff and customers need to see data in real-time, it’s difficult to harmonize your data, especially if many people are working on the same project.
• Spreadsheets are not ideal for working with multiple datasets in tandem.

100. What’s so special about NoSQL?

Ans:

NoSQL databases are a speedier alternative because, for one, you don’t need to join tables in NoSQL. Every piece of data is stored in a JSON format. And the limitation of relational databases is that each item can only contain one attribute. To illustrate this point, look at the above Fruit table and notice how each column is dedicated to just one measure or attribute. Thanks to javascript, NoSQL allows you to store data in a nested fashion.

NoSQL is also simple to use. You don’t have to deal with the “mismatch” between rows and columns. For example, storing all the reviewer’s information in one document as opposed to having to join countless tables. In that way, you write less code which, hopefully, means fewer errors.