Learn all the frequently asked interview questions on SQL
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=> INSERT INTO – inserts new data into 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=> DROP INDEX – deletes 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 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
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
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?
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.
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:
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 table1WHERE condition;
26) What is Auto Increment feature in SQL?
Auto increment allows the user to create a unique number to be generated whenever a new record is inserted in the table. IDENTITY keyword can be used in SQL SERVER for auto incrementing.
27) Which query operators in SQL is used for pattern matching?
LIKE operator is used for pattern matching, and it can be used as :-
% – Matches zero or more characters
_ – Matching exactly one character
28) What are the constraints available in SQL?
Constraints in SQL are – Primary Key, Foreign Key, Unique Key, Not Null, Check and Index
29) What is the difference between Primary and Unique key?
30) What are the aggregate functions in SQL?
31) How to add a column ‘Salary’ to a table Employee?
ALTER TABLE Employee ADD (Salary)
32) How to get list of all tables from a database?
SELECT * from sys.Tables
33) How to fetch values from Table1 that are not in Table2 without using NOT keyword?
By using the except keyword
SELECT * from Table1 Except Select * from Table2
34) How to rename a column in the output of SQL query?
SELECT column_name AS new_column_name FROM table_name
35) How to rename a table?
SP_RENAME TABLE 'Test1','Test2'
Full Stack Test Automation Expert | Selenium Framework Developer | Certified Tosca Automation Specialist | Postman | DevOps | AWS | IC Agile Certified | Trainer | Youtuber | Blogger|