SQL/RDBMS is one of the most widely used databases to date, and therefore SQL skills are important in most work roles.
In this SQL interview questions article, I will share with you some of the most frequently asked questions on SQL (Structured Query Language). This article is the perfect guide for you to learn all concepts related to SQL, Oracle, MS SQL Server, and MySQL databases. In this topic, all the examples are related to SQL Server.
I am keeping 10 questions and answers in each part, So Please read all 10 parts of this SQL Interview Questions article and get ready to crack the SQL interview for beginners or experience level.
SQL Interview Questions & Answer – Best TOP 10 (Day – 1)
Contents
What is the difference between UNION and UNION ALL?
UNION:-
- UNION is always used to select related information from two tables.
- UNION is used to select distinct values from two tables.
- UNION selects only a unique row from all queries.
- UNION is a bit slower as it refines the values to be returned.
UNION All:-
- UNION All is used to select all values (i.e. select duplicate records) from the tables.
- UNION All select all rows from all queries.
- UNION All is comparatively faster as it just has to present all the values, regardless of clone values.
Before start to check SQL Interview Questions & Answer topic, Please understand the related chapter
Introduction to Structured Query Language
What are the differences between Stored Procedure and the dynamic SQL?
A Stored Procedure is a set of statements that are stored in a compiled form.
Dynamic SQL is a set of statements that are dynamically constructed at runtime and it will not be stored in a Database and it simply executes during run time.
What is the difference between Inner Join and Outer Join?
Inner Join:-
- It will retains always common data between two or more two tables.
- If both tables have common records then only it will return the data otherwise null value will be return.
- It can be applicable for Equi join.
Outer Join:-
- It will retain the rows of either table or both tables.
- An outer join will return all the rows whether they are match-based or not matched-based on the join predicate. The variable is dependent on only the first table
Left Outer Join
Right Outer Join
Full Outer Join
SQL Interview Questions & Answer topic, Please understand JOIN in SQL with Top 10 Example
What is the difference between Left Outer Join and Right Outer Join?
Left Outer Join/Left Join: Return all rows from the left table, and the matched rows from the right table.
Right Outer Join/Right Join: Return all rows from the right table and the matched rows from the left table.
Please refer to the above image (SQL Interview Questions -3) for more understanding.
What is the difference between JOIN and UNION?
SQL JOIN allows retrieving the records on other tables based on the given conditions between two tables. Please refer to SQL Interview Questions -1 and 3
For example:-
Retrieve the employee’s records from employee table, whose employees are getting more than 10,000 salaries.
UNION is always used to select related information from two tables.
UNION does not require any condition for joining.
SELECT * FROM employee1;
UNION
SELECT * FROM employee2;
What is DISTINCT Clause ?
The DISTINCT clause is used to remove the duplicate records from the dataset and to display only the unique values.
Select distinct * from employee1;
How data can be copied from one table to another table?
The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match.
We can achieve the result in the below ways.
INSERT INTO SELECT TABLE1
SELECT INTO TABLE2
INSERT INTO SELECT:-
Those require that data types in source and target tables match.
The existing records in the target table are unaffected
The target table is already created.
Emaple:-
Write a query to copy the records from tbl_Emp1 to tbl_emp2 and employee’s salary should not be less than 1000?
INSERT INTO tbl_Emp2
SELECT * FROM tbl_Emp1
WHERE salary>=1000;
SELECT INTO:-
The SELECT INTO statement copies data from one table into a new table.
Emaple:-
Write a query to create a new table “tbl_emp2” and copy the records from tbl_Emp1 whose employee’s salary should not be less than 1000 ?
SELECT * INTO tbl_Emp2
FROM tbl_Emp1
WHERE salary>=1000;
What is the difference between COMMIT and ROLLBACK?
Every statement between BEGIN and COMMIT becomes persistent to the database when the COMMIT is executed.
Every statement between BEGIN and ROOLBACK is reverted to the original state when the ROLLBACK was executed.
What is Cross Join?
A cross joins that does not have a WHERE clause produces the Cartesian product of the tables involved in the join.
The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
SQL Interview Questions & Answer topic, Please understand JOIN in SQL with Top 10 Example
What is a stored procedure?
A Stored Procedure is a collection or a group of precompiled T-SQL statements is stored together in the database.
It will reduce the network traffic because of the precompilation. Stored Procedures are created to perform one or more DML operations on the Database. It is nothing but the group of SQL statements that accepts some input in the form of parameters and performs some task and may or may not returns a value.
