SQL Interview Questions and Answers

What is Data?

Data can be facts related to any object, in consideration.

For Example, your name, age, height, weight etc. are some data related to you. A picture, image, file can also be considered as Data. This data could be random, but not organized.

What is Database?

As we already know that data could be random. But a database is an organized set of data that can be easily managed and accessed.

Data in a database is organized, and it makes data management easily.

What is Database Management System?

Database Management System(DBMS) is a collection of programs which enables its users to access database, manipulate data, and help in representation of data. It also helps control access to the database by various users.

For example, an online telephone directory would use a database management system to store data pertaining to people, phone numbers and other contact details.

Your electricity service provider obviously using a DBMS to manage billing, client related issues, and to handle fault etc.

What is SQL?

SQL stands for Structured Query Language and is a language for writing schemas, store, manipulate and retrieve data from database.

SQL was developed by IBM in around the 1970s, and is a standardized ANSI (American National Standards Institute) query language.

SQL is further divided into four sub languages

1-DDL(Data Definition Language)

2-DML(Data Manipulation Language)

3-DCL(Data Control Language)

4-TCL(Transaction Control Language)

What is SQL Server?

SQL Server also known as Relational Database Management System(RDBMS). It is an application software to store and manipulate the data. It provides features and functionalities to users to properly interact with the database and can perform all the database operations efficiently.

SQL Server was developed by Microsoft in the year 1989 for commercial purposes.

SQL Server is a platform-dependent Relational Database Management System.

SQL Server available in both command-line and GUI(Graphical User Interface) for providing better user operations.

What are authentication modes in SQL Server?

There are two types of authentication modes in SQL Server.

Windows Authentication
Mixed Mode Authentication

During SQL Server installation, there are two modes of authentication, where one is Windows Authentication and the Other is Mixed Mode Authentication.

When we connect SQL Server with Windows Authentication, then SQL Server does not ask for the password, because the authentication is confirmed by the windows’ login.

When we connect the SQL Server instance by SQL Server Authentication, then it asks for the password which we provided during installation after choosing Mixed Mode Authentication.

During installation when we choose Windows Authentication then we cannot connect to SQL Server instance by SQL Server Authentication, but if we choose Mixed Mode Authentication then we can connect to SQL Server instance by both(Windows Authentication and SQL Server Authentication) authentication modes.

What is the Syntax to create database?

CREATE DATABASE DatabaseName;

What is the Syntax to create table?

CREATE TABLE TableName
(
    Column1 datatype,
    Column2 datatype,
    Column3 datatype
);

Which statement is used to insert records into a table?

INSERT INTO statement is used to insert records into SQL Server table.

What is primary key constraint?

PRIMARY KEY constraint is used to enforce uniqueness of rows. It uniquely identifies each record in a table. It does not allow NULL value. A table can have only one primary key but can consist of one or more columns.

What is foreign key constraint?

The Foreign Key in a table is referenced as the primary key in another table. A foreign key can consist of one or more fields and is referenced with the primary key on another table.

A table with foreign key is called a child table and the table with primary key which is referenced by foreign key is called as the parent table.

What is not null constraint?

A NOT NULL constraint on a column does not allow a null value to be inserted in a column.

What is default constraint?

A DEFAULT constraint is used to set a default value for a column. The default value is automatically inserted when no value is specified while inserting new records.

What is check constraint?

A CHECK constraint is used to allow certain values for a column. For example, If you want to apply check constraint on age column of tblPerson where person’s age less than 18 year is not allowed.

What is a unique key constraint?

A UNIQUE Key constraint enforces the uniqueness of a column. A unique key column allows only one NULL value. A primary key column does not allow a NULL value to enter, while UNIQUE key constraint allow one NULL value.

Which statement is used to fetch records from the table?

The SELECT statement is used to fetch records from the table.

Why we use WHERE clause?

WHERE clause is used to filter data from one or more tables based on certain condition.

For example, If we have a table tblEmployee, and we want to select only the female employees records, then we filter those records by using a WHERE clause.

How you differentiate WHERE and HAVING clause?

WHERE clause is used to filter the rows from the table, while HAVING clause is used to filter the grouped data.

WHERE clause is used before the GROUP BY clause, whereas HAVING clause is used after the GROUP BY clause.

The WHERE clause can be used with SELECT, INSERT, UPDATE and DELETE statements while HAVING clause is used only with the SELECT statement.

HAVING clause is slower than the WHERE clause because in HAVING clause first the rows are grouped, and then those groups are filtered, whereas in WHERE clause the rows are filtered.

Explain DELETE and TRUNCATE statements with major differences.

DELETE statement is a Data Manipulation Language command, whereas TRUNCATE is a Data Definition Language command.

We can use the WHERE clause while deleting the rows from the table, while we cannot use the WHERE clause while truncating the table.

DELETE is slower than TRUNCATE statement because in DELETE each and every record is locked and then deleted, while in TRUNCATE statement the whole table is locked and all the records gets deleted.

If there is an IDENTITY column in the table, then DELETE does not reset the identity column, while TRUNCATE reset the identity column.

If data is deleted using DELETE, the data can be rolled back, and if truncation occurred using TRUNCATE, then data cannot be rolled back.

What are UNION and UNION ALL operators in SQL?

UNION and UNION ALL operators are used to combine the result set of two or more SELECT statements. The data types, number of columns and their order should be the same.

UNION operator combine the result set of two or more SELECT statements, excluding the duplicate records. UNION operator also sort the result set.

UNION ALL operator combine the result set of two or more SELECT statements, including the duplicate records. UNION ALL does not sort the result set, it combines the result of second query after the result set of first query.

What are INTERSECT and EXCEPT in SQL?

INTERSECT is used to combine the common rows from two SQL SELECT statements. The retrieved rows are distinct rows. i.e. the duplicate rows are eliminated. Simply, it is just like the intersection of two sets.

EXCEPT command is used to get unique records from the result set of left query which are not present in result set of right query.

Can you explain Joins and their types in SQL?

When there is a need of data in a single result set from different tables, we use joins.

Types of Joins are explained below.

INNER-JOIN
OUTER-JOIN
- LEFT-JOIN/LEFT-OUTER-JOIN
- RIGHT-JOIN/RIGHT-OUTER-JOIN
- FULL-JOIN/FULL-OUTER-JOIN
CROSS-JOIN
SELF-JOIN

What is stored procedure in SQL?

A Stored Procedure consists of a single or set of statements and are stored in a database as an object. A stored procedure can be cashed and reused. From the security point of view, a stored procedure is also good by hiding all the column and details from the user and giving access only for little information.

To read more about stored procedures with examples, CLICK HERE

What is view in SQL?

A view is just like a save SQL query. It is also called as virtual table in SQL. A view can be used as a mechanism to implement column and row level security. It is also used to hide the detailed data and present the aggregated data.

What is trigger in SQL?

A trigger is a special type of stored procedure which automatically fires when an event occur like INSERT, UPDATE, and DELETE etc.

What are DML triggers in SQL?

DML trigger gets fired when any of the DML event (INSERT, UPDATE, DELETE) occurred.

DML triggers are further classified into two categories.

After triggers/FOR triggers

After triggers, gets fired after the triggering action. These triggers (INSERT, UPDATE, and DELETE) causes an after trigger to fire after the respective statements complete execution.

2. Instead of triggers

Instead of triggers, gets fired instead of the triggering action. These triggers (INSERT, UPDATE, and DELETE) causes an instead of trigger to fire instead of the respective statement execution.