SQL Interview Questions and Answers

Answer:

SQL stands for Structured Query Language. It is the standard language for RDBMS and is useful in handling organized data that has entities or variables with relations between them. SQL is used for communicating with databases.

Answer:

Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways.

Answer:

DBMS stands for Database Management System is the software that allows storing, modifying, and retrieving data from a database. And it is a group of programs that act as the interface between data and applications. DBMS supports receiving queries from applications and retrieving data from the database.

RDBMS stands for Relational Database Management System, Like DBMS, RDBMS is also the software that allows storing, modifying, and retrieving data from a database but a RELATIONAL database. In a relational database, the data in the tables have a relationship. Besides, RDBMS is useful when data in tables are being managed securely and consistently.

Answer:

A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called fields while the rows can be referred to as records.

Answer:

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during the creation of the table or after creating using the ALTER TABLE command. The constraints are:

• NOT NULL– Restricts NULL value from being inserted into a column.
• CHECK– Verifies that all values in a field satisfy a condition.
• DEFAULT– Automatically assigns a default value if no value has been specified for the field.
• UNIQUE– Ensures unique values to be inserted into the field.
• INDEX– Indexes a field providing faster retrieval of records.
• PRIMARY KEY– Uniquely identifies each record in a table.
• FOREIGN KEY– Ensures referential integrity for a record in another table.

Answer:

Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle and is a critical aspect of the design, implementation, and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

Answer:

The following are the four significant subsets of the SQL:

• Data definition language (DDL): It defines the data structure that consists of commands like CREATE, ALTER, DROP, etc.
• Data manipulation language (DML): It is used to manipulate existing data in the database. The commands in this category are SELECT, UPDATE, INSERT, etc.
• Data control language (DCL): It controls access to the data stored in the database. The commands in this category include GRANT and REVOKE.
• Transaction Control Language (TCL): It is used to deal with the transaction operations in the database. The commands in this category are COMMIT, ROLLBACK, SET TRANSACTION, SAVEPOINT, etc.

Answer:

Normalization is used to minimize redundancy and dependency by organizing fields and table of a database.

There are some rules of database normalization, which is commonly known as Normal Form, and they are:

• First normal form(1NF)
• Second normal form(2NF)
• Third normal form(3NF)
• Boyce-Codd normal form(BCNF)

Using these steps, the redundancy, anomalies, inconsistency of the data in the database can be removed.

Answer:

An Inconsistent dependency refers to the difficulty of getting relevant data due to a missing or broken path to the data. It leads users to search the data in the wrong table, resulting in an error as an output.

Answer:

If a table is dropped, all things associated with that table are dropped as well. This includes the relationships defined on the table with other tables, access privileges, and grants that the table has, as well as the integrity checks and constraints. To create and use the table again in its original form, all the elements associated with the table need to be redefined.

However, if a table is truncated, there are no such problems as mentioned above. The table retains its original structure.

Answer:

The trigger can be defined as an automatic process that happens when an event occurs in the database server. It helps to maintain the integrity of the table. The trigger is activated when the commands, such as insert, update, and delete, are given.

The syntax used to generate the trigger function is:

CREATE TRIGGER trigger_name

Answer:

Relationships are developed by interlinking the column of one table with the column of another table. There are three different types of relationships, which are as follows:

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

Answer:

Two authentication modes are available in SQL Server. They are:

• Windows Authentication Mode: It allows authentication for Windows but not for SQL Server.
• Mixed Mode: It allows both types of authentication—Windows and SQL Server.

Answer:

A function is an SQL Server database object. It is basically a set of SQL statements that allow input parameters, perform processing, and return results only. A function can only return a single value or table; the ability to insert, update, and delete records in database tables is not available.

Answer:

The primary key and unique key both are essential constraints of the SQL. The main difference among them is that the primary key identifies each record in the table. In contrast, the unique key prevents duplicate entries in a column except for a NULL value.

Answer:

There are four different types of JOINs in SQL, which are as follows:

• Inner Join– Inner join return rows when there is at least one match of rows between the tables.
• Right Join– Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.
• Left Join– Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
• Full Join– Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.

Answer:

Stored Procedure is a function consists of many SQL statement to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.

Answer:

Database Cardinality denotes the uniqueness of values in the tables. It supports optimizing query plans and hence improves query performance. There are three types of database cardinalities in SQL, as given below:

• Higher Cardinality
• Normal Cardinality
• Lower Cardinality

Answer:

Following are the different types of cursors:

• Forward Only: It is known as the firehose cursor that can make only a forward movement. The modification made by the current user and other users is visible while using this cursor. As it is the forward-moving cursor, it fetches rows of the result set from the start to end serially.
• Static: This cursor can move forward and backward on the result set. Here, only the same result set is visible throughout the lifetime of the cursor. In other words, once the cursor is open, it doesn’t show any changes made in the database that is the source for the result set.
• Keyset: This cursor is managed by a set of identifiers known as keys or keysets. Here, the keysets are built by the columns that derive the rows of a result set. When we use this cursor, we can’t view the records created by other users. Similarly, if any user deletes a record, we can’t access that record too.
• Dynamic: Unlike static cursors, once the cursor is open, all the modifications performed in the database are reflected in the result set. The UPDATE, INSERT and DELETE operations made by other users can be viewed while scrolling the cursor.

Answer:

The schema represents the logical structures of data. Using schemas, the database objects can be grouped logically in a database. Schema is useful for segregating database objects based on different applications, controlling access permissions, and managing a database’s security aspects. Simply out, Schemas ensure database security and consistency.

Following are the advantages of Schema in SQL:

• Schemas can be easily transferred.
• You can transfer database objects between schemas.
• It protects database objects and achieves effective access control.