SQL Interview Questions and Answers Part-II(SQL Basic Understanding)

Hello and Welcome to the article on SQL Interview Questions ad Answers Part-II(SQL Basic Understanding). If you directly landed on this article while looking for interview questions on SQL and Database I will Suggest checking the first article in this series where I tried to explain and answer on the Database link as follow SQL Interview Questions and Answers(Database)

preparing for an upcoming?SQL Interview, you’re at the right place! This blog on SQL Interview Questions has been divided into various sections. Whether you’re a fresher, a developer, or an experienced working professional, there are SQL Interview Questions to meet your needs and help you ace your interview!

Q) What is SQL?

• ?SQL stands for Structured Query Language.
• ?SQL lets you access and manipulates databases.
• SQL is the language to retrieve, update, modify delete or insert any new information in the database.
• ?SQL is the standard language for Relational Database systems.
• All the Relational Database Management systems (RDBMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.

Q) What Can SQL do?

• Allows users to access data in the relational database management systems.
• Allow users to describe the data.
• Allows users to define the data in a database and manipulate that data.
• Allows users to create and drop databases and tables.
• Allows users to retrieve, insert Update, delete, and create new tables.
• Allows users to create views, stored procedures, and functions in a database.
• Allows users to set permissions on tables, procedures and views
• ?SQL can execute queries against a database.
• SQL can set permissions on tables, procedures and views.

SQL is a Standard-BUT......

• Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.
• However, to be compliant with the ANSI standard, they all support at least the major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.

Q) What is SQL Process?

When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and the SQL engine figures out how to interpret the task.

There are various components included in this process.

These components are-

• Query Dispatcher.
• Optimization Engines
• Classic Query Engine
• SQL Query Engine, etc.

A classic query engine handles all the non-SQL queries, but a SQL query engine won't handle logical files.

Q) What are ACID Properties in DBMS?

Q) Explain Atomicity?

Atomicity means either the entire transaction takes place at once or doesn’t happen at all. There is no midway i.e. transactions do not occur partially. Each transaction is considered as one unit and either runs to completion or is not executed at all. It involves the following two operations.

Abort: If a transaction aborts, changes made to the database are not visible.

Commit: If a transaction commits, changes made are visible.

Atomicity is also known as the ‘ALL or Nothing Rule’.

Q) Explain Consistency?

This means that integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to the correctness of a database. Referring to the example above.

Q) Explain Isolation?

This property ensures that multiple transactions can occur concurrently without leading to the inconsistency of the database state. Transactions occur independently without interference. Changes occurring in a particular transaction will be visible to any other transaction until the particular change in that transaction is written to memory or has been committed. This property ensures that the execution of transactions concurrently will result in a start that is equivalent to a state achieved these were executed serially in some order.

Lets X=500, Y=500

Consider two transactions T and T’’.

Suppose T has been executed till reading (Y) and then T” starts.?As a result, interleaving of operations takes place due to which T” reads the correct value of X but the incorrect value of Y and sum compute by

T”: (X+Y=50,000+500=50,500)

Is thus not consistence with the sum at the end of the transaction:

T: (X+Y=50,000+450=50,450)

This results in database inconsistency, due to a loss of 50 units. Hence, transactions must take place in isolation and changes should be visible only after they have been made to the main memory.

Q) What is Durability?

This property ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if a system failure occurs.

These updates now become permanent and are in non-volatile memory. The effects of the transaction thus are never lost.

The ACID properties, In totality, provides a mechanism to ensure the correctness and consistency of a database in a way such that each transaction is a group of operations that acts as a single unit, produces consistent results, acts in isolation from other operations and updates that it makes are durably stored.

Q) What is the ER Model?

• Entity Relational(ER) Model is a high-level conceptual data model diagram.
• ER modelling helps you to analyze data requirements systematically to produce a well-designed database.
• The Entity-Relation model represents real-world entities and the relationship between them.
• It is considered a best practice to complete ER modelling before implementing your database.
• ER modelling helps you to analyze data requirements systematically to produce a well-designed database. So it is considered a best practice to complete ER modelling before implementing your database.
• ENTITY-RELATIONSHIP DIAGRAM(EDR) displays the relationship of entity sets stored in a database. In other words, we can say that ER diagram helps you to explain the logical structure of databases.
• At first look, an ER diagram looks very similar to the flowchart. However, ER Diagram includes many specialized symbols, and its meaning makes this model unique.
• The purpose of ER Diagram is to represent the entity framework infrastructure.

Facts about ER Diagram Model

• ER model allows you to draw Database Design.
• It is an easy-to-use graphical tool for modelling data.
• Widely used in Database Design.
• It is a GUI representation of the logical structure of a Database.
• It helps you to identify the entities which exist in the system and the relationship between those entities.

Q) Q) What are the different components of ER Diagram

• Entities: in a database could be a single person, place, or thing about which data can be stored.
• Attributes(columns): is a piece of information which determines the properties of a field or tag in a database or a string of characters in a display.
• Relationships: provides useful information that could not be discerned with just the entity types. Relationships are meaningful associations between or among entities like assigning, associating, or tracking.

Q) What are the best practices for developing an effective ER Diagram

• Eliminate any redundant entities or relationship
• You need to make sure that all your entities are relationships are properly labelled
• There may be various valid approaches to an ER diagram, you need to make sure that the ER diagram supports all the data you need to store.
• You should assure that each entity only appears as a single time in the ER diagram.
• Name every relationship, entity, and attribute represented on your diagram.
• Never connect relationships to each other
• You should use colours to highlight the important portion of the ER diagram

?Q) Explain Database Normalization?

• Database normalization is the process of efficiently organizing data in a database. There are two reasons for this normalization process
• Eliminating redundant data, for example, storing the same data in more than one table.
• Ensuring data dependencies make sense.
• ?Both these reasons are worthy goals as they reduce the amount of space a database consumes and ensures that data is logically stored.
• Normalization consists of a series of guidelines that help guide you in creating a good database structure.
• Normalization guidelines are divided into normal forms; think of a form as the format or the way a database structure, so that it complies with the rules of the first normal form, then the second normal form and finally the third normal form.

Q) What is MySQL?

• MySQL is an open-source SQL database, which is developed by a Swedish company-MySQL LAB.
• MySQL is supporting many different platforms including Microsoft windows, the major Linux distributions, UNIX, and Mac OS X.
• MySQL has a free and paid version, depending on its usage(non-commercial/commercial) and features. MySQL comes with a very fast, multi-threaded, multi-user and robust SQL database server.

Q) What are the advantages of MySQL?

• High Performance
• ?High Availability
• ???Scalability and Flexibility Run anything
• ???Robust Transactional Support.
• ???Web and Data Warehouse Strengths.
• ???Strong Data Protection.
• ???Comprehensive Application Development.
• ???Management Ease.
• ???Open Source Freedom and 24X7 Support.
• ???Lowest Total Cost of Ownership.

For this article, we will stop here in the next article we are going to learn about the MySql database and its installation.

Show your support by Like, Share and Comment on the article link to the previous article is as follows: SQL Interview Questions and Answers(Database)