Top 15 SQL interview questions for 2-5 years experienced

 1. What is the difference between UNION and UNION ALL?

UNION: To select related information from two tables UNION command is used. It is similar to JOIN command.

UNION All: The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It will not remove duplicate rows, instead, it will retrieve all rows from all tables.

2. List the difference between delete and drop

DELETE is a DML(Data Manipulation Language) command, DML command is used to remove tuples/records from a relation/table. 

Whereas DDL (DROP is a Data Definition Language, command and is used to remove named elements of schema like relations/table, constraints or entire schema

	DELETE	DROP
1	DELETE Command, removes some or all tuples/records from a relation/table	DROP Command, removes named elements of schema like relations/table, constraints or entire schema.
2	DELETE is DML command.	DROP is DDL coomand.
3	Where clause can be used to add filtering. Syntax for deleting table: DELETE FROM table_name WHERE condition;	No where clause is needed Syntax: DROP TABLE table_name;
4	Delete command can be rollbacked as it works on data buffer.	Drop command can't be rollbacked as it works directly on data.
5	Table memory space is not free if all records are deleted using Delete Command.	Drop command frees the memory space.

3. what is normalization in sql server.?

Database normalization is the process of restructuring a relational database in accordance with a series of normal forms in order to reduce data redundancy and improve data integrity. It was first proposed by Edgar F. Codd as an integral part of his relational model.

Database Normalization is a process and it should be done for every database design.

The database normalization process is further categorized into the following types:

1. First Normal Form (1 NF)
2. Second Normal Form (2 NF)
3. Third Normal Form (3 NF)

4. Explain different types of joins in SQL server

5. What are the different types of joins in SQL Server?

 Answer: Joins are used to bring data together from different tables based on their database relations. 

Some different types of joins are as below:

1. Inner Join

2. Left outer Join or Left Join

3. Right outer Join or Right Join

4. Full Outer Join or Full Join

5. Cross join

1. Inner join

From both left and right join matching records will be produced

2. Left join

The LEFT JOIN keyword returns all records from the left table (table2), and the matching records from the right table (table1). 

3. Right join

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). 

4. Full join or Full Outer Join

The Full join returns a result set that contains all rows from both left and right tables, with the matching rows from both sides where available.

5. Cross Join

A cross join returns the Cartesian product of rows from the rowsets in the join. In other words, it will combine each row from the first rowset with each row from the second rowset.

Click here to know more about Joins with examples

==================================

6. what is the difference between COMMIT and ROLLBACK?

Every statement between BEGIN and COMMIT becomes persistent to database when the COMMIT is executed. Every statement between BEGIN and ROOLBACK are reverted to the state when the ROLLBACK was executed.

======================================

7. 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.

8. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

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.

9.  Define Constraints?

A constraint is a table column property with performs data validation. Using constraints, you can maintain data integrity by preventing invalid data from being entered.

10. What do you understand by Data integrity?

Data integrity is the consistency and accuracy of the data which is stored in a database.

SQL Constraints

NOT NULL - Surely field gets value for every row

DEFAULT- If u didnt give a value it default value will be given to field.

PRIMARY KEY- not null+unique

FOREIGN KEY- references a column of another table(mostly primary key)

UNIQUE- All the field values must be different, but it allow one null value.

CHECK CONSTRAINT-kinda integrity constraint (specifies a requirement that must be met by each row in database)

11. How many primary keys can exist on a table? 

One

12.  What is a Foreign Key?

An Foreign Key in one table points to a PK in another table

It prevents any actions that would destroy links between tables with the corresponding data values

FK are used to enforce referential integrity

13. Difference between Primary key and Unique key constraints?

1) Unique constraints will allow a null value. If a field is nullable then a unique constraint will allow at most one null value.

2) SQL server allows many unique constraints per table where it allows just primary key per table.

14. Can we apply Unique key constraints on multiple columns?

Yes! Unique key constraints can be applied on a composite of multiple fields to ensure the uniqueness of records.

Example : City + State in the StateList table

15. Explain GROUP BY Clause?

In some cases, you will be required to use the SQL GROUP BY clause with the SQL MAX function.

For example, when you want to return the name of each department and the maximum salary in the department, you could also use the SQL MAX function

SELECT department, MAX(salary) AS "Highest salary"

FROM employees

GROUP BY department;

With Single Expression

The simplest way to use the SQL MAX function would be to return a single field that calculates the MAX value.

For example, you might wish to know the maximum salary of all employees.

SELECT MAX(salary) AS "Highest salary"

FROM employees;

Explain different types of joins in SQL server?

 What are the different types of joins in SQL Server?

 Answer: Joins are used to bring data together from different tables based on their database relations. 

Some different types of joins are as below:

1. Inner Join

2. Left outer Join or Left Join

3. Right outer Join or Right Join

4. Full Outer Join or Full Join

5. Cross join

1. Inner join

In the case of Inner join, from both left and right tables, matching records will be produced

Ex:

SELECT a.col1, b.col1 FROM table1  a INNER JOIN table2 b ON a.Id = b.Id;

2. Left join

The LEFT JOIN keyword returns all records from the left table (table2), and the matching records from the right table (table1). If there is no match in right side able, the result is 0 records.

Ex:

SELECT a.desc, b.area  FROM table1 a LEFT JOIN table2 b

ON a.ColId = b.ColID;

3. Right join

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

Ex:

SELECT column_name(s) FROM table1 RIGHT JOIN table2

ON table1.column_name = table2.column_name;

4. Full join or Full Outer Join

The Full join returns a result set that contains all rows from both left and right tables, with the matching rows from both sides where available. In case there is no match, the missing side will have NULL values.

Ex:

SELECT a.Id,a.Name,b.DepName

FROM table1 a FULL JOIN table2 b ON a.id= b.Id;

5. Cross Join

A cross join returns the Cartesian product of rows from the rowsets in the join. In other words, it will combine each row from the first rowset with each row from the second rowset.

It produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.

Ex: SELECT *  From table1 CROSS JOIN table2

Arithmetic overflow error converting expression to data type int Error(Msg 8115) in SQL Server

SQL Error:-Arithmetic overflow error converting expression to data type int Error(Msg 8115) in SQL Server

SQL Error No:-Msg 8115

To recreate this issue, I have created a test table testOverflow.

create table testOverflow(

column1 int,

column2 int)

=> To test if the normal insert works, I am inserting some random numbers into the table. and the insert is successful

Insert into testOverflow values(1111111,2222222)

=> Now I am increasing the length of column2 values as shown in the below query,

Insert into test11 values(1111111,22222222222)

and with this, overflow error occurs.

Error message:

Msg 8115, Level 16, State 2, Line 9

Arithmetic overflow error converting expression to data type int.

The statement has been terminated.

=> For this solution would be either change the datatype of column2 to hold a larger value.

Ex:Change Int to BigInt or change the value of the column to a smaller value to hold within Int limits

How to create a database in sql server with database name and path as input variables

How to create a database in sql server with database name and path as input variables

=>Below stored procedure example illustrates how to create a new database.

=> and adds database name and path as an input variable.

Create proc createTestDatabase(@databasename as varchar(120),@drivename as varchar(500))

as

Begin

Declare  @stmt as nvarchar(max);

IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @databasename))

BEGIN

EXEC('DROP DATABASE ' + @databasename);

END

Set @stmt='

CREATE DATABASE ' + @databasename +  

    ' ON (NAME = N''' + @databasename + '''' +

', filename = N''' + @drivename  + '\' + @databasename + '.mdf' + '''' + ', SIZE = 1024MB, FILEGROWTH = 256MB)' +

' LOG ON (NAME = N''' + @databasename +  '_log' + '''' +

 ', FILENAME = N''' + @drivename + '\' + @databasename + '_log.ldf' + '''' + ', SIZE = 512MB, FILEGROWTH = 125MB)'

Select @stmt 

Execute (@stmt);

End

How to execute the above stored proc:

Declare @databasename as varchar(150);

Declare @drivepath as varchar(500);

Set @databasename  = 'testDatabase11';

Set @drivepath= 'D:\mcj';

Exec createTestDatabase @databasename, @drivepath;

How do you set database ONLINE and OFFLINE in sql server?

SQL Server database can be made OFFLINE by running the below SQL statement. 

If there are any critical or important transactions running then it's recommended to let them complete first and then run the below queries down.

USE [master];

GO

--Disconnect all existing sessions.

ALTER DATABASE SampleDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

--Change database into OFFLINE mode.

ALTER DATABASE SampleDB SET OFFLINE

Now your database has moved to offline mode.

=>If incase set single user doesn't work, use the below deadlock priority high command

SET DEADLOCK_PRIORITY HIGH

GO

ALTER DATABASE SampleDB SET MULTI_USER WITH ROLLBACK IMMEDIATE

GO

How do we bring back our database online again?

Once we are done with updating the physical file names, the next step is to bring the database ONLINE.

Run the below SQL statement to bring the target database ONLINE. 

ALTER DATABASE SampleDB SET ONLINE

Go

ALTER DATABASE SampleDB SET MULTI_USER

Go 

SQL Server- Query to restore database with its file groups and log files

 -- > create a sample database

USE master

GO

 

CREATE DATABASE SampleDB

ON PRIMARY

  (NAME = SampleDB_file1,

    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\data\SampleDB_1.mdf',

          SIZE = 100MB,         

          FILEGROWTH = 10%),

 FILEGROUP SampleDB_MemoryOptimized_filegroup CONTAINS MEMORY_OPTIMIZED_DATA

  ( NAME = SampleDB_MemoryOptimized,

    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\data\SampleDB_MemoryOptimized')

 LOG ON

  ( NAME = SampleDB_log_file1,

    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\data\SampleDB_1.ldf',

          SIZE = 100MB,         

          FILEGROWTH = 10%)

GO

SQL Server Database master keys and certificates for database encryption

 

SQL Server – Database master key and Certificates

Database Master Key and Password Protected Certificates

Things to remember about master Keys

-->Each sql server instance can have only 1 service master key and each database can have only 1 database master key

-- > we cannot rename master key as we want to...

--> this will be listed under master database

Syntax to check if the master key exists or not, if Not then create master key: 

IF (select Count(*) from master.sys.symmetric_keys where name like '%DatabaseMasterKey%') = 0

BEGIN

CREATE MASTER KEY  ENCRYPTION BY PASSWORD = 'test1234pwd'

END

Note: you need to be sysadmin or login with sa credentials to execute these queries

Once you created the master key you can view them under the security section of master database.

Why do we need master keys:

Certificates created with the CREATE CERTIFICATE statement are protected by the database master key. To be able to execute that statement, the database master key has to exist. If the database master key does not exist or if it cannot be opened, the CREATE CERTIFICATE statement will fail.

Alternatively, if you do not want the private key to be protected by the database master key, you can provide a password using the ENCRYPTION BY PASSWORD clause:

 ENCRYPTION BY PASSWORD = '**********'   WITH SUBJECT = ' SQLserverpwd112233';