MS SQL Interview Questions

Introduction To MS SQL Interview Questions And Answers

If you are looking for a job related to MS SQL, you need to prepare for the 2020 MS SQL Interview Questions. Every interview is indeed different as per the different job profiles. Here, we have prepared the important MS SQL Interview Questions and Answers, which will help you succeed in your interview.

This 2020 MS SQL Interview Questions article will present the 10 most important and frequently asked MS SQL interview questions. These interview questions are divided into two parts are as follows:

Part 1 – MS SQL Interview Questions (Basic)

This first part covers basic Interview Questions and Answers.

1. What is SQL? Describe the importance of SQL in RDBMS?

Answer:
SQL is a Structured Query Language. SQL is used to communicate with the database. SQL is the heart of RDBMS (Relational Database Management System). It is the language used to perform all the operations in a relational database.

When you issue a command to the RDBMS is SQL, the RDBMS interpret your command and takes necessary actions.

2. What is the difference between SQL and PL/SQL?

Answer:

SQL	PL/SQL
It is a Structured Query Language.	It is Procedural language, an extension of SQL.
In SQL, you can execute a single command at a time.	In pl/SQL, you can execute multiple lines of code at a time.
In SQL, commands are executed using DDL (Data Definition Language), DML (Data Manipulation Language).	In pl/SQL, you can write multiple code lines that have procedure, function, packages, etc.
SQL commands can be used in pl/SQL.	Pl/SQL cannot use in SQL.
An example of SQL is: – Select * from Table_name where condition	An example of pl/SQL is: – BEGIN dbms_output.put_line (‘HELLO EDUCBA WORLD’); END; /

3. What are the main components of SQL?

Answer:
The main components of SQL are DDL, DML, DCL (Data Control Language), TCL (Transaction Control Language)

Data Definition Language:

Tables are the only way to store data; all the information has to be arranged in the form of tables. Suppose you want to store some information (Name, city) about the company in the database. To store this, you need to create a table; you can create a table using the table command:

Create table company (name char (10), city char (10));

Using DDL, you can also alter or drop objects.

Data Manipulation Language:

DML, as the name suggests, allows you to manipulate data in an existing table. You can do many operations using DML, such as insertion, updating, deletion, on a table.

• Adding a row to a table

Insert into company values (‘XYZ’, ‘Sydney’);

• Updating data in a table

Update company set city = ‘Melbourne’ where name = ‘XYZ’

Data Control Language:

• DCL: This allows you to control access to the data.
• Grant: Grants permission to one or more users to operate.
• Revoke: Withdraw the access permission given by the grant statement.

Transaction Control Language:

TCL includes commit, rollback and save point to data.

Let us move to the next MS SQL Interview Questions.

4. What is the difference between Delete and truncate commands?

Answer:

• DELETE command can be used to delete rows from the particular table, and the WHERE clause can be used for condition. Commit, and Rollback functions can be performed on the delete command after the delete statement.
• TRUNCATE is used to removes all rows from the table. When Truncate operation is used, it cannot be rolled back.

5. Write a SQL query to find the 3rd highest salary from the table without using the TOP/limit keyword?

Answer:

Select salary from EDUCBA_Employee E1 WHERE 2 = (Select count (Distinct (E2. salary))
from EDUCBA_EMPLOYEE E2 where E2. salary > E1. salary

6. How will you perform pattern matching operations in SQL?

Answer: 
LIKE operator is used for pattern matching, and it can be used in two ways -.

• % – It Matches zero or more characters.

Select * from employee where name like ‘X%’

• _(Underscore) – It Matches exactly one character.

Select * from employee where name like ‘XY_’

7. Write a query to get employee names ending with a vowel?

Answer:

Select EMP_ID, EMP_NAME from EDUCBA_EMPLOYEE where EMP_NAME like '%[aeiou]'

8. How will you copy rows from one table to another table?

Answer:
The INSERT command will be used to adding up a row to a table by copying from another table. In this case, a subquery is used in place of the VALUES clause.

Part 2 – MS SQL Interview Questions (Advanced)

Let us now have a look at the advanced Interview Questions and Answers.

9. What is the difference between the ‘WHERE’ clause and the ‘HAVING’ clause?

Answer:
HAVING clause can only be used with the SELECT statement. HAVING clause is used with the GROUP BY clause, and if the GROUP BY clause is not used, then the HAVING clause behaves like a WHERE clause.HAVING Clause is only used with the GROUP BY command, whereas WHERE Clause is applied to each row after FROM clause and before they are going to a part of GROUP BY function in a query.

10. How will you get a first name, salary, and round the salary to thousands?

Answer:

SELECT FIRST_NAME, SALARY, ROUND (SALARY, -3) FROM EDUCBA_EMPLOYEE;

11. Display the first name and experience of the employees?

Answer:

SELECT FIRST_NAME, HIRE_DATE, FLOOR((SYSDATE-HIRE_DATE)/365) FROM EDUCBA_EMPLOYEE;

Let us move to the next MS SQL Interview Questions.

12. Write a query to get the first name and last name after converting the first letter of each name to upper case and the rest to lower case?

Answer:

SELECT INITCAP(FIRST_NAME), INITCAP(LAST_NAME) FROM EDUCBA_EMPLOYEE;

13. Display the length of the first name for employees where the last name contain the character ‘b’ after the 3rd position?

Answer:

SELECT FIRST_NAME, LAST_NAME FROM EDUCBA_EMPLOYEE WHERE INSTR(LAST_NAME,'B') > 3;

14. Change the salary of employee 115 to 8000 if the existing salary is less than 6000?

Answer:

UPDATE EDUCBA_EMPLOYEE SET SALARY = 8000 WHERE EMPLOYEE_ID = 115 AND SALARY < 6000;

Let us move to the next MS SQL Interview Questions.

15. How will you Insert a new employee into employees with all the required details?

Answer:

INSERT INTO EDUCBA_EMPLOYEE (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, SALARY) VALUES (207, 'ANGELA', 'SNYDER','ANGELA','215 253 4737’, 12000);

16.Display employees who joined in the month of May?

Answer:

SELECT * FROM EDUCBA_EMPLOYEE WHERE TO_CHAR (HIRE_DATE, 'MON’) = 'MAY';

17. What is the meaning of “TRIGGER” in SQL?

Answer:
A trigger allows you to execute SQL query when an operation like insert, update or delete commands are executed against a specific table.

Recommended Article

This has been a guide to a List Of MS SQL Interview Questions and Answers so that the candidate can crackdown these MS SQL Interview Questions easily. You may also look at the following articles to learn more –

1. Top 12 SQL Interview Questions
2. NoSQL Interview Questions
3. Cloud Computing Interview Questions
4. Manual Testing Interview Questions