All The Useful Advanced SQL Interview Questions & Answers for Developers

Q 1. What are the subsets of SQL?
a. DDL (Data Definition Language): Used to define the data structure it consists of the commands like CREATE, ALTER, DROP, etc. 
b. DML (Data Manipulation language): Used to manipulate already existing data in the database, commands like SELECT, UPDATE, INSERT 
c. DCL (Data Control Language): Used to control access to data in the database, commands like GRANT, REVOKE

Q 2. What is the difference between CHAR and VARCHAR2 datatype in SQL?
CHAR is used to store fixed-length character strings, and VARCHAR2 used to store variable-length character strings

Q 3. How to sort a column using a column alias?
By using the column alias in the ORDER BY instead of where clause for sorting

Q 4. What is the difference between COALESCE() & ISNULL(). ? 
COALESCE() accepts two or more parameters, one can apply 2 or as many parameters but it returns only the first non NULL parameter. 

ISNULL() accepts only 2 parameters. 

The first parameter is checked for a NULL value, if it is NULL then the 2nd parameter is returned, otherwise, it returns the first parameter.

Q 5. What is 'Trigger' in SQL?
A trigger allows you to execute a batch of SQL code when an insert,update or delete command is run against a specific table as Trigger is said to be the set of actions that are performed whenever commands like insert, update or delete are given. 

Q 6. Write a Query to display employee details along with age.
SELECT * DATEDIFF(yy, dob, getdate()) AS 'Age' FROM employee

Q 7. Write a Query to display employee details along with age.
SELECT SUM(salary) FROM employee

Q 8. Write an SQL query to get the third maximum salary of an employee from a table named employee_table.
SELECT TOP 1 salary FROM ( SELECT TOP 3 salary FROM employee_table ORDER BY salary DESC ) AS emp ORDER BY salary ASC; 

Q 9. What are aggregate and scalar functions?
Aggregate functions are used to evaluate mathematical calculation and return single values. This can be calculated from the columns in a table. Scalar functions return a single value based on input value. 

Example -. Aggregate - max(), count - Calculated with respect to numeric. Scalar - UCASE(), NOW() - Calculated with respect to strings.

Q 10. What is a deadlock?
It is an unwanted situation where two or more transactions are waiting indefinitely for one another to release the locks. 

Q 11. Explain left outer join with example.
Left outer join is useful if you want all the records from the left table(first table) and only matching records from 2nd table. The unmatched records are null records. Example: Left outer join with '+' operator Select t1.col1,t2.col2….t 'n'col 'n.'. from table1 t1,table2 t2 where t1.col=t2.col(+);

Q 12. What is SQL injection?
SQL injection is a code injection technique used to hack data-driven applications

Q 13. What is an UNION operator?
The UNION operator combines the results of two or more Select statements by removing duplicate rows. The columns and the data types must be the same in the SELECT statements.

Q 14. Explain SQL Constraints.
Constraints are used to specify the rules of data type in a table. They can be specified while creating and altering the table. The following are the constraints in SQL: NOT NULL CHECK DEFAULT UNIQUE PRIMARY KEY FOREIGN KEY

Q 15. What is ALIAS command?
This command provides another name to a table or a column. It can be used in WHERE clause of a SQL query using the 'as' keyword. 

Q 16. What are Group Functions? Why do we need them?
Group functions work on a set of rows and return a single result per group. The popularly used group functions are AVG, MAX, MIN, SUM, VARIANCE, COUNT

Q 17. How can dynamic SQL be executed?
By executing the query with parameters 
By using EXEC 
By using sp_executesql

Q 18. What is the usage of NVL() function?
This function is used to convert NULL value to the other value.

Q 19. Write a Query to display employee details belongs to ECE department.
SELECT EmpNo, EmpName, Salary FROM employee WHERE deptNo in (select deptNo from dept where deptName = 'ECE')

Q 20. What are the main differences between #temp tables and @table variables and which one is preferred?

1. SQL server can create column statistics on #temp tables. 
2. Indexes can be created on #temp tables 
3. @table variables are stored in memory up to a certain threshold

Q 21. What is CLAUSE?
SQL clause is defined to limit the result set by providing condition to the query. This usually filters some rows from the whole set of records. Example - Query that has WHERE condition

Q 22. What is recursive stored procedure?
A stored procedure which calls by itself until it reaches some boundary condition. This recursive function or procedure helps programmers to use the same set of code any number of times.

Q 23. What is a schema?
A schema is a collection of database objects in a database for a particular user/owner. Objects can be tables, views, indices and so on.

Q 24. What does the BCP command do?
The Bulk Copy is a utility or a tool that exports/imports data from a table into a file and vice versa. 

Q 25. What is a Cross Join?
In SQL cross join, a combination of every row from the two tables is included in the result set. This is also called cross product set. For example, if table A has ten rows and table B has 20 rows, the result set will have 10 * 20 = 200 rows provided there is NOWHERE clause in the SQL statement.

Q 26. Which operator is used in query for pattern matching?
LIKE operator is used for pattern matching, and it can be used as- 1. % - Matches zero or more characters. 2. _(Underscore) - Matching exactly one character.

Q 27. Write a SQL query to get the current date.
SELECT CURDATE();

Q 28. State the case maniplation functions in SQL.

1. LOWER: converts all the characters to lowercase.
2. UPPER: converts all the characters to uppercase. 
3. INITCAP: converts initial character of each word to uppercase

Q 29. How to add a column to an existing table?
ALTER TABLE Department ADD (Gender, M,F)

Q 30. Define lock escalation.
A query first takes the lowest level lock possible with the smallest row-level.When too many rows are locked, the lock is escalated to a range or page lock. If too many pages are locked, it may escalate to a table lock. 

Q 31. How to store Videos inside SQL Server table?
By using FILESTREAM datatype, which was introduced in SQL Server 2008.

Q 32. State the order of SQL SELECT?
Order of SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clauses are mandatory.

Q 33. What is the difference between IN and EXISTS?
IN: Works on List result set Doesn't work on subqueries resulting in Virtual tables with multiple columns Compares every value in the result list.

Exists: Works on Virtual tables Is used with co-related queries Exits comparison when match is found

Q 34. How do you copy data from one table to another table?
INSERT INTO table2 (column1, column2, column3, …) SELECT column1, column2, column3, … FROM table1 WHERE condition;

Q 35. List the ACID properties that makes sure that the database transactions are processed
ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. 

Q 36. What will be the output of the following Query, provided the employee table has 10 records? 
BEGIN TRAN TRUNCATE TABLE Employees ROLLBACK SELECT * FROM Employees

This query will return 10 records as TRUNCATE was executed in the transaction. TRUNCATE does not itself keep a log but BEGIN TRANSACTION keeps track of the TRUNCATE command.

Q 37. What do you mean by Stored Procedures? How do we use it?
A stored procedure is a collection of SQL statements that can be used as a function to access the database. We can create these stored procedures earlier before using it and can execute them wherever required by applying some conditional logic to it. Stored procedures are also used to reduce network traffic and improve performance.

Q 38. What does GRANT command do?
This command is used to provide database access to users other than the administrator in SQL privileges.

Q 39. What does First normal form do?
First Normal Form (1NF): It removes all duplicate columns from the table. It creates a table for related data and identifies unique column values.

Q 40. How to add e record to the table?
INSERT syntax is used to add a record in the table. INSERT into table_name VALUES (value1, value2..);

Q 41. What are the different tables present inMySQL?
There are 5 tables present in MYSQL.

1. MyISAM 
2. Heap 
3. Merge 
4. INNO DB 
5. ISAM

Q 42. What is BLOB and TEXT in MySQL?
BLOB stands for large binary object. It is used to hold a variable amount of data. TEXT is a case-insensitive BLOB. TEXT values are non-binary strings (character string). 

Q 43. What is the use of mysql_close()?
Mysql_close() cannot be used to close the persistent connection. Though it can be used to close connection opened by mysql_connect().

Q 44. How do you return a hundred books starting from 25th?
SELECT book_titile FROM books LIMIT 25,100; 

Q 45. How would you select all the users, whose phone number is NULL?
SELECT user_name FROM users WHERE ISNULL(user_phonenumber); 

Q 46. How do you run batch mode in mysql?
SELECT user_name FROM users WHERE ISNULL(user_phonenumber); 

Q 47. Write an SQL query to show the second highest salary from a table.
Select max(Salary) from Worker where Salary not in (Selct max(Salary) from Worker); 

Q 48.  Write an SQL query to fetch three max salaries from a table.
SELECT distinct Salary from worker a WHERE 3 >= (SELECT count(distinct Salary) from worker b WHERE a.salary <= b.Salary) order by a.Salary desc;

Q 49. What is the difference between NOW() and CURRENT_DATE()?
NOW () command is used to show current year,month,date with hours,minutes and seconds. CURRENT_DATE() shows current year,month and date only.

Q 50. How can we convert between Unix & MySQL timestamps?
UNIX_TIMESTAMP is the command which converts from MySQL timestamp to Unix timestamp FROM_UNIXTIME is the command which converts from Unix timestamp to MySQL timestamp.

Q 51. What are the nonstandard string types?
Following are Non-Standard string types: 

1. TINYTEXT 
2. TEXT 
3. MEDIUMTEXT 
4. LONGTEXT

Q 52. What is the group by clause used for?
The group by clause combines all those records that have identical values in a particular field or any group of fields.

Q 53. How do you get the last id without the max function?
SELECT ID from table order by ID desc limit 1

Q 54. Write a SQL query to fetch only even rows from the table.
Using the same Row_Number() and checking that the remainder when divided by 2 is 0- SELECT E.EmpId, E.Project, E.Salary FROM ( SELECT *, Row_Number() OVER(ORDER BY EmpId) AS RowNumber FROM EmployeeSalary ) E WHERE E.RowNumber % 2 = 0

Q 55. Write a SQL query to create a new table with data and structure copied from another table.
Using SELECT INTO command- SELECT * INTO newTable FROM EmployeeDetails; 

Q 56. What are the different types of Collation Sensitivity?
Case sensitivity: A and a are treated differently. Accent sensitivity: a and á are treated differently. Kana sensitivity: Japanese kana characters Hiragana and Katakana are treated differently. Width sensitivity: Same character represented in single-byte (half-width) and double-byte (full-width) are treated differently.

Q 57. Write a query to find out the data between range.
In day to day activities the user needs to find out the data between some range. To achieve this user needs to use Between..and operator or Greater than and less than operator. 

Query 1 : Using Between..and operator
Select * from Employee where salary between 25000 and 50000;

Query 2 : Using operators (Greater than and less than)
Select * from Employee where salary >= 25000 and salary <= 50000;

Q 58. How to calculate the number of rows in a table without using the count function?
There are so many system tables which are very important .Using the system table user can count the number of rows in the table.following query is helpful in that case, Select table_name, num_rows from user_tables where table_name='Employee';

Q 59. What is wrong with the following query? SELECT empName FROM employee WHERE salary <> 6000
The following query will not fetch a record with the salary of 6000 but also will skip the record with NULL. 

Q 60. Will the following statements execute? if yes what will be output? SELECT NULL+1 SELECT NULL+'
Yes, no error. The output will be NULL. Perform any operation on NULL will get the NULL result.

Q 61. SQL vs. PL/SQL

Basis For Comparison    SQL  -  PL/SQL
Basic    In SQL you can execute a single query or a command at a time.    In PL/SQL you can execute a block of code at a time.
Full form    Structured Query Language    Procedural Language, an extension of SQL.
Purpose    It is like a source of data that is to be displayed.    It is a language that creates an application that display's data acquired by SQL.
Writes    In SQL you can write queries and command using DDL, DML statements.    In PL/SQL you can write block of code that has procedures, functions, packages or variables, etc.
Use    Using SQL, you can retrieve, modify, add, delete, or manipulate the data in the database.    Using PL/SQL, you can create applications or server pages that display the information obtained from SQL in a proper format.
Embed    You can embed SQL statement in PL/SQL.    You can not embed PL/SQL in SQL

Q 62. SQL having vs where
Where Clause   Vs Having Clause

1    The WHERE clause specifies the criteria which individual records must meet to be selected by a query. It can be used without the GROUP by clause    The HAVING clause cannot be used without the GROUP BY clause
2    The WHERE clause selects rows before grouping    The HAVING clause selects rows after grouping
3    The WHERE clause cannot contain aggregate functions    The HAVING clause can contain aggregate functions
4    WHERE clause is used to impose a condition on SELECT statement as well as single row function and is used before GROUP BY clause    HAVING clause is used to impose a condition on GROUP Function and is used after GROUP BY clause in the query
5    SELECT Column,AVG(Column_nmae)FROM Table_name WHERE Column > value GROUP BY Column_nmae    SELECT Columnq, AVG(Coulmn_nmae)FROM Table_name WHERE Column > value GROUP BY Column_nmae Having column_name>or<value

Q 63. When to use NoSQL vs SQL

SQL Databases   Vs    NoSQL Databases
Data Storage Model       Tables with fixed rows and columns    - Document: JSON documents, Key-value: key-value pairs, Wide-column: tables with rows and dynamic columns, Graph: nodes and edges
Development History    Developed in the 1970s with a focus on reducing data duplication -    Developed in the late 2000s with a focus on scaling and allowing for rapid application change driven by agile and DevOps practices.
Examples    Oracle, MySQL, Microsoft SQL Server, and PostgreSQL    - Document: MongoDB and CouchDB, Key-value: Redis and DynamoDB, Wide-column: Cassandra and HBase, Graph: Neo4j and Amazon Neptune
Primary Purpose    General purpose    - Document: general purpose, Key-value: large amounts of data with simple lookup queries, Wide-column: large amounts of data with predictable query patterns, Graph: analyzing and traversing relationships between connected data
Schemas    Rigid  - Flexible
Scaling    Vertical (scale-up with a larger server) -     Horizontal (scale-out across commodity servers)
Multi-Record ACID Transactions    Supported    - Most do not support multi-record ACID transactions. However, some - like MongoDB - do.
Joins    Typically required - Typically not required
Data to Object Mapping    Requires ORM (object-relational mapping)    - Many do not require ORMs. MongoDB documents map directly to data structures in most popular programming languages.

Q 64. SQL vs TSQL
SQL -     TSQL
A domain-specific language used in programming and designed for managing data held in a Relational Database Management System    - Microsoft's proprietary version of SQL for its SQL Server RDBMS
Stands for Structured Query Language     - Stands for Transact Structured Query Language
Query Language to Manage Data in an RDBMS    - An Extension of SQL That is Used on MS SQL Server
Developed by IDM     - Developed by Microsoft
It is Possible to Embed SQL into TSQL  -    It is Not Possible to Embed TSQL into SQL
Helps to Process and Analyze the Data Using Simple Queries -     Helps to Add Business Logic into an Application

Q 65. MySQL vs SQL Server

SQL Server -    MySQL Server
Relational Database Management System - Open-source Relational Database Management System
Developed by Microsoft     - Developed by Oracle
Available in Multiple Languages    - Only Available in English
Supports Windows, Linux and Containers    Supports Windows, Linux and Mac
Commercial    - Open-Source
Programmed in C++     - Programmed in C and C++
Compatible with Kubernetes, Apache Spark and Hadoop Distributed File System    Has Difficulty Operating with Kubernetes, Apache Spark and Hadoop Distributed File System

Q 66. MongoDB vs SQL

MongoDB    - MySQL

When you need high availability of data with automatic, fast, and instant data recovery -    If you're just starting and your database is not going to scale much, MYSQL will help you in easy and low-maintenance setup
In future, if you're going to grow big as MongoDB has in-built sharding solution    - If you want high performance on a limited budget
If you have an unstable schema and you want to reduce your schema migration cost -    If you have fixed schema and data structure isn't going to change over time like WikiPedia
If you don't have a database administrator -    If high transaction rate I going to be your requirement
If most of the services are cloud-based, MongoDB is best suitable for you -     If data security is the topmost priority, MySQL is most suited DBMS

Q 67. How to find duplicate records in SQL?
There are multiple ways to find duplicate records in SQL. Let's see how can we find duplicate records using groupby:

SELECT 
   x, 
   y, 
   COUNT(*) occurrences
FROM z1
GROUP BY
   x, 
   y
HAVING 
   COUNT(*) > 1;

We can also find duplicates in the table using rank:

SELECT * FROM ( SELECT eid, ename, eage, Row_Number() OVER(PARTITION BY ename, eage ORDER By ename) AS Rank FROM employees ) AS X WHERE Rank>1

Q 68. What is Case WHEN in SQL?
If you have knowledge about other programming languages, then you'd have learnt about if-else statements. You can consider Case WHEN to be analogous to that.

In Case WHEN, there will be multiple conditions and we will choose something on the basis of these conditions.

Here is the syntax for CASE WHEN:

CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   WHEN conditionN THEN resultN
   ELSE result
END;
We start off by giving the CASE keyword, then we follow it up by giving multiple WHEN, THEN statements.

Q 69. How to find 2nd highest salary in SQL?
Below is the syntax to find 2nd highest salary in SQL:

SELECT name, MAX(salary)
 FROM employees
WHERE salary < (SELECT MAX(salary)
                FROM employees);

Q 70. How to delete duplicate rows in SQL.
There are multiple ways to delete duplicate records in SQL.

Below is the code to delete duplicate records using rank:

alter table emp add  sid int identity(1,1)

   delete e
   from  emp e
   inner join
   (select *,
   RANK() OVER ( PARTITION BY eid,ename ORDER BY id DESC )rank
   From emp )T on e.sid=t.sid
   where e.Rank>1

   alter table emp 
   drop  column sno

Below is the syntax to delete duplicate records using groupby and min:

alter table emp add  sno int identity(1,1)
    
        delete E from emp E
        left join
        (select min(sno) sno From emp group by empid,ename ) T on E.sno=T.sno
        where T.sno is null

        alter table emp 
        drop  column sno    

Q 71. What is cursor in SQL?
Cursors in SQL are used to store database tables. There are two types of cursors:

Implicit Cursor
Explicit Cursor
Implicit Cursor:

These implicit cursors are default cursors which are automatically created. A user cannot create an implicit cursor.

Explicit Cursor:
Explicit cursors are user-defined cursors. This is the syntax to create explicit cursor:

DECLARE cursor_name CURSOR FOR SELECT * FROM table_name

We start off by giving by keyword DECLARE, then we give the name of the cursor, after that we give the keywords CURSOR FOR SELECT * FROM, finally, we give in the name of the table.

Q 72. How to create a stored procedure using SQL Server?
If you have worked with other languages, then you would know about the concept of Functions. You can consider stored procedures in SQL to be analogous to functions in other languages. This means that we can store a SQL statement as a stored procedure and this stored procedure can be invoked whenever we want.

This is the syntax to create a stored procedure:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
We start off by giving the keywords CREATE PROCEDURE, then we go ahead and give the name of this stored procedure. After that, we give the AS keyword and follow it up with the SQL query, which we want as a stored procedure. Finally, we give the GO keyword.

Once, we create the stored procedure, we can invoke it this way:

EXEC procedure_name;
We will give in the keyword EXEC and then give the name of the stored procedure.

Let's look at an example of a stored procedure:

CREATE PROCEDURE employee_location @location nvarchar(20)
AS
SELECT * FROM employees WHERE location = @location
GO;
In the above command, we are creating a stored procedure which will help us to extract all the employees who belong to a particular location.

EXEC employee_location @location = 'Boston';
With this, we are extracting all the employees who belong to Boston.

Q 73. How to create an index in SQL?
We can create an index using this command:

CREATE INDEX index_name
ON table_name (column1, column2, column3 ...);
We start off by giving the keywords CREATE INDEX and then we will follow it up with the name of the index, after that we will give the ON keyword. Then, we will give the name of the table on which we would want to create this index. Finally, in parenthesis, we will list out all the columns which will have the index. Let's look at an example:

CREATE INDEX salary
ON Employees (Salary);
In the above example, we are creating an index called a salary on top of the 'Salary' column of the 'Employees' table.

Now, let's see how can we create a unique index:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2,column3 ...);
We start off with the keywords CREATE UNIQUE INDEX, then give in the name of the index, after that, we will give the ON keyword and follow it up with the name of the table. Finally, in parenthesis, we will give the list of the columns which on which we would want this unique index.

Q 74. How to change column data-type in SQL?
We can change the data-type of the column using the alter table. This will be the command:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
We start off by giving the keywords ALTER TABLE, then we will give in the name of the table. After that, we will give in the keywords MODIFY COLUMN. Going ahead, we will give in the name of the column for which we would want to change the datatype and finally we will give in the data type to which we would want to change.

Q 75. What is the difference between SQL and NoSQL databases?
SQL stands for structured query language and is majorly used to query data from relational databases. When we talk about a SQL database, it will be a relational database. 

But when it comes to NoSQL database, we will be working with non-relational databases.