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