Most Frequently Asked SQL Interview Questions And Answers

Most Frequently Asked SQL Interview Questions And Answers

1) What is SQL ?

SQL stands for Structured Query Language. SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database.

2) What’s difference between DBMS and RDBMS ?

DBMS provides a systematic and organized way of storing, managing and retrieving from collection of logically related information. RDBMS also provides what DBMS provides but above that it provides relationship integrity. So in short we can say RDBMS = DBMS + REFERENTIAL INTEGRITY These relations are defined by using “Foreign Keys” in any RDBMS. Almost all DBMS (SQL SERVER, ORACLE) fulfills all the twelve CODD rules and are considered as truly RDBMS.

3) What are DML and DDL statements?

DML stands for Data Manipulation Statements. They update data values in table. 

Below are the most important DDL statements:

-=>SELECT – gets data from a database table

=> UPDATE – updates data in a table

=> DELETE – deletes data from a database table

DDL stands for Data definition Language. They change structure of the database objects like table, index etc. 

Most important DDL statements are as shown below:-

=>CREATE TABLE – creates a new table in the database.

=>ALTER TABLE – changes table structure in database.

=>DROP TABLE – deletes a table from database

=> CREATE INDEX – creates an index

4) How do we select distinct values from a table?

DISTINCT keyword is used to return only distinct values. 

Below is syntax:-

SELECT DISTINCT age FROM Employee

5) What is Like operator for and what are wild cards?

LIKE operator is used to match patterns. A “%” sign is used to define the pattern. 

Below SQL statement will return all words which start with letter “S”:

SELECT * FROM Employee WHERE EmpName LIKE 'S%'

Below SQL statement will return all words which end with letter “S”:

SELECT * FROM Employee WHERE EmpName LIKE '%S'

Below SQL statement will return all words having letter “S” in between:

SELECT * FROM Employee WHERE EmpName LIKE '%S%'

6) Can you explain Insert, Update and Delete query?

Insert statement is used to insert new rows in to table.

Update statement is used to update existing data in the table. 

Delete statement is used to delete a record from the table. 

Below code snippet for Insert, Update and Delete :-

INSERT INTO Employee SET name='rohit',age='24';

UPDATE Employee SET age='25' where name='rohit';

DELETE FROM Employee WHERE name = 'sonia';

7) What is order by clause?

ORDER BY clause helps to sort the data in either ascending order to descending order. Below is the code snippet for ascending order sort:

SELECT name,age FROM Employee ORDER BY age ASC

Below is the code snippet for descending order sort:

SELECT name FROM Employee ORDER BY age DESC

8) What is the SQL ” IN ” clause?

SQL IN operator is used to see if the value exists in a group of values. 

For instance the below SQL checks if the Name is either ‘rohit’ or ‘Anuradha’

SELECT * FROM Employee WHERE name IN ('Rohit','Anuradha')

Also you can specify a not clause with the same.

SELECT * FROM Employee WHERE age NOT IN (17,16)

9) Can you explain the between clause?

Below SQL selects employees born between ’01/01/1975′ AND ’01/01/1978′:

SELECT * FROM Employee WHERE DOB BETWEEN '1975-01-01' AND '2011-09-28'

10) What are different types of joins in SQL?

INNER JOIN:
Inner join shows matches only when they exist in both tables.

SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

LEFT OUTER JOIN:
Left join will display all records in left table of the SQL statement.

SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON
Customers.CustomerID =Orders.CustomerID

RIGHT OUTER JOIN:
Right join will display all records in right table of the SQL statement.

SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON
Customers.CustomerID =Orders.CustomerID

11) What is “CROSS JOIN”? or What is Cartesian product?

“CROSS JOIN” or “CARTESIAN PRODUCT” combines all rows from both tables. Number of rows will be product of the number of rows in each table.

12) How to select the first record in a given set of rows?

Select top 1 * from sales.salesperson

13) Can you explain the SELECT INTO Statement?

SELECT INTO statement is used mostly to create backups. The below SQL backs up the Employee table in to the EmployeeBackUp table. One point to be noted is that the structure of EmployeeBackup and Employee table should be same.

SELECT * INTO EmployeeBackup FROM Employee

14) What is a “trigger”?

Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurrence of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert, update, delete and query statements.

15) What type of wildcards have you used?

Wildcards are special characters that allow matching string without having exact match. In simple word they work like contains or begins with. Wildcard characters are software specific and in SQL Server we have % which represent any groups of characters, _ that represent one character (any) and you also get [] where we can [ab] which means characters with letter a or b in a specific place.

16) What is the difference between clustered and non clustered index in SQL?

The differences between clustered and non clustered index in SQL are:

– Clustered index is used for easy retrieval of data from the database and its faster whereas reading from non clustered is relatively slower.

– Clustered index alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index whereas in a non clustered index, it does not alter the way it was stored but it creates a separate object within a table which points back to the original table rows after searching.

– One table can only have one clustered index whereas it can have many non clustered index.

17) Write a SQL query to find the names of the employees that begin with  'A'?

SELECT * FROM Table_Name Where EmpName like 'A%'

18) Write a SQL query to get the third highest salary of an employee from employee_table?

SELECT TOP 1 salary FROM
( SELECT TOP 3 salary from employee_table ORDER BY salary DESC) AS emp 
ORDER BY salary ASC;

19) What is the difference between DELETE and TRUNCATE commands?

• Delete is a DML command but Truncate is a DDL command
• We can use Where clause in Delete command but not in Truncate command
• Delete statement is used to delete a row from a table but Truncate command is used to remove all the rows from a table
• Delete is slower than Truncate statement. Truncate is faster than Delete statement
• You can rollback data after using DELETE statement. It is not possible to roll back after using TRUNCATE statement

20) Write an SQL query to find the number of employees whose DOB is between 01/01/1990 to 31/12/1999.

SELECT COUNT(*) FROM Employees WHERE DOB BETWEEN '01/01/1990' AND '31/12/1999'

21) Write an SQL query to find the year from Date.

SELECT YEAR(GETDATE()) as "Year";

22) Write SQL Query to delete duplicate rows in a database

DELETE from emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);

23) What is view in SQL?

A view can be defined as a virtual table that contains rows and columns with fields from one or more table. 

Code syntax:

CREATE VIEW view_name AS SELECT column_name FROM table_name WHERE condition

24) What is a cursor?

A cursor is a database object which is used to manipulate data in a row-to-row manner.

Cursor follow steps as given below:

• Declare cursor
• Open cursor
• Retrieve row from cursor
• Process the row
• Close cursor
• Deallocate cursor

25) What is the process of copying data from Table 1 to Table 2?

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ... 
FROM table1 
WHERE condition;

26) What is Auto Increment feature in SQL?

ALTER TABLE Employee ADD (Salary)

USE TestDB 
GO 
SELECT * from sys.Tables
GO

SELECT * from Table1 Except Select * from Table2

SELECT column_name AS new_column_name FROM table_name

SP_RENAME TABLE 'Test1','Test2'