SQL Interview Questions

Relational Databases continue to be the most extensively used database management systems. Accessing and analyzing the data is the top most need these days and SQL enables you to do just that. There is a high demand among the employers for individuals who possess SQL skills. It is therefore absolutely essential for you to prepare yourself with the frequently asked SQL Interview questions, if you want to land your dream job! You can access various programming and technical courses on Great learning Academy and get your free certificates.

The Questions are divided into three categories:

Basic SQL Interview Questions

 1.  Write a Query to display the number of employees working in each region? 

SELECT region, COUNT(gender) FROM employee GROUP BY region;

2. What are Nested Triggers?

Triggers may implement DML by using INSERT, UPDATE, and DELETE statements. These triggers that contain DML and find other triggers for data modification are called Nested Triggers.

3. Write SQL query to fetch employee names having a salary greater than or equal to 20000 and less than or equal 10000.

By using BETWEEN in the where clause, we can retrieve the Employee Ids of employees with salary >= 20000and <=10000. SELECT FullName FROM EmployeeDetails WHERE EmpId IN (SELECT EmpId FROM EmployeeSalary WHERE Salary BETWEEN 5000 AND 10000)

4. Given a table Employee having columns empName and empId, what will be the result of the SQL query below? select empName from Employee order by 2 asc;

“Order by 2” is valid when there are at least 2 columns used in SELECT statement. Here this query will throw error because only one column is used in the SELECT statement. 

5. What is OLTP?

OLTP stands for Online Transaction Processing. And is a class of software applications capable of supporting transaction-oriented programs. An essential attribute of an OLTP system is its ability to maintain concurrency. 

6. What is Data Integrity?

Data Integrity is the assurance of accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data. It also defines integrity constraints to enforce business rules on the data when it is entered into an application or a database.

7. What is OLAP?

OLAP stands for Online Analytical Processing. And a class of software programs which are characterized by relatively low frequency of online transactions. Queries are often too complex and involve a bunch of aggregations. 

8. Find the Constraint information from the table?

There are so many times where user needs to find out the specific constraint information of the table. following queries are useful, SELECT * From User_Constraints; SELECT * FROM User_Cons_Columns;

9. Can you get the list of employees with same salary? 

Select distinct e.empid,e.empname,e.salary from employee e, employee e1 where e.salary =e1.salary and e.empid != e1.empid 

10. What is an alternative for TOP clause in SQL?

1. ROWCOUNT function 
2. Set rowcount 3
3. Select * from employee order by empid desc Set rowcount 0 

11. Will following statement give error or 0 as output? SELECT AVG (NULL)

Error. Operand data type NULL is invalid for Avg operator. 

12. What is the Cartesian product of the table?

The output of Cross Join is called a Cartesian product. It returns rows combining each row from the first table with each row of the second table. For Example, if we join two tables having 15 and 20 columns the Cartesian product of two tables will be 15×20=300 rows.

You can refer this SQL beginner’s tutorial if you want to know more about Structure Query Language

Intermediate SQL Interview Questions

13. 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

14. 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

15. How to sort a column using a column alias?

By using the column alias in the ORDER BY instead of where clause for sorting

16. 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.

17. 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. 

18. Write a Query to display employee details along with age?

SELECT * DATEDIFF(yy, dob, getdate()) AS ‘Age’ FROM employee

19. Write a Query to display employee details along with age?

SELECT SUM(salary) FROM employee

20. 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; 

21. 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.

22. What is a deadlock?

It is an unwanted situation where two or more transactions are waiting indefinitely for one another to release the locks. 

23. 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(+);

24. What is SQL injection?

SQL injection is a code injection technique used to hack data-driven applications

25. 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.

26. 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

27. 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. 

28. 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

29. How can dynamic SQL be executed?

a. By executing the query with parameters 

b. By using EXEC 

c. By using sp_executesql

30. What is the usage of NVL() function?

This function is used to convert NULL value to the other value.

31. 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’)

32. 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

33. 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

34. 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.

35. 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.

36. 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. 

37. 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.

38.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.

39. Write a SQL query to get the current date?

SELECT CURDATE();

40. 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

41. How to add a column to an existing table?

ALTER TABLE Department ADD (Gender, M,F)

42. 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. 

43. How to store Videos inside SQL Server table ?

By using FILESTREAM datatype, which was introduced in SQL Server 2008.

44. What is Database Black Box Testing?

This testing involves: 1. Data Mapping 2. Data stored and retrieved 3. Use of Black Box testing techniques such as Equivalence Partitioning and Boundary Value Analysis (BVA)

45. 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.

46. 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

47. 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;

48. 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. 

49. 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.

50. 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.

51. What does GRANT command do?

This command is used to provide database access to users other than the administrator in SQL privileges.

52. What are the different types of SQL sandbox?

There are 3 different types of SQL sandbox: 

1. Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. but cannot have access to the memory as well as cannot create files.

 2. External Access Sandbox: Users can access files without having the right to manipulate the memory allocation.

3. Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.

53. 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.

54. What is Database White box testing?

White box testing includes: Database Consistency and ACID properties Database triggers and logical views Decision Coverage, Condition Coverage, and Statement Coverage Database Tables, Data Model, and Database Schema Referential integrity rules

55. 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..);

56. 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

57. 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). 

58. 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().

59. How do you return a hundred books starting from 25th?

SELECT book_titile FROM books LIMIT 25,100; 

60. How would you select all the users, whose phone number is NULL?

SELECT user_name FROM users WHERE ISNULL(user_phonenumber); 

61. How do you run batch mode in mysql?

SELECT user_name FROM users WHERE ISNULL(user_phonenumber); 

62. 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); 

63.  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;

64. 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.

65. What is ISAM?

ISAM is abbreviated as Indexed Sequential Access Method.It was developed by IBM to store and retrieve data on secondary storage systems like tapes

66. 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.

67. What are the nonstandard string types?

Following are Non-Standard string types: 

1. TINYTEXT 

2. TEXT 

3. MEDIUMTEXT 

4. LONGTEXT

68. 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.

69. How do you get the last id without the max function?

SELECT ID from table order by ID desc limit 1

70. 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

71. 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; 

72. 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.

73. 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;

74. 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’;

75. 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. 

76. Will the following statements execute? if yes what will be output? SELECT NULL+1 SELECT NULL+’1′

Yes, no error. The output will be NULL. Perform any operation on NULL will get the NULL result.

Advanced SQL Interview Questions

77. If we drop a table, does it also drop related objects like constraints, indexes, columns, default, views and sorted procedures?

Yes, SQL server drops all related objects, which exists inside a table like constraints, indexex, columns, defaults etc. But dropping a table will not drop views and sorted procedures as they exist outside the table. 

78. Can we disable a trigger? If yes, How?

Yes, we can disable a single trigger on the database by using “DISABLE TRIGGER triggerName ON<> We also have an option to disable all the trigger by using, “DISABLE Trigger ALL ON ALL SERVER”

79. What is a Live Lock?

A live lock is one where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keep interferring. A live lock also occurs when read transactions create a table or page. 

80. How to fetch alternate records from a table?

Records can be fetched for both Odd and Even row numbers- To display even numbers-. Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=0 To display odd numbers-. Select employeeId from (Select rowno, employeeId from employee) where mod(rowno,2)=1

81. Define COMMIT and give an example?

When a COMMIT is uded in a transaction all chnages made in the transaction are written into the database permanently. Example: BEGIN TRANSACTION; DELETE FROM HR.JobCandidate WHERE JobCandidateID = 20; COMMIT TRANSACTION; The above example deletes a job candidate in a SQL server.

82. Can you join table by itself? 

A table can be joined to itself using self join, when you want to create a result set that joins records in a table with other records in the same table.

83. Explain Equi join with example

When two or more tables has been joined using equal to operator then this category is called as equi join. Just we need to concentrate on condition is equal to(=) between the columns in the table. Example: Select a.Employee_name,b.Department_name from Employee a,Employee b where a.Department_ID=b.Department_ID

84. How do we avoid getting duplicate entries in a query?

The SELECT DISTINCT is used to get distinct data from tables using a query. The below SQL query selects only the DISTINCT values from the “Country” column in the “Customers” table: SELECT DISTINCT Country FROM Customers;

85. What is a Subquery?

A SubQuery is a SQL query nested into a larger query. Example: SELECT employeeID, firstName, lastName FROM employees WHERE departmentID IN (SELECT departmentID FROM departments WHERE locationID = 2000) ORDER BY firstName, lastName; 

86. How can you create an empty table from an existing table?

Lets take an example: Select * into studentcopy from student where 1=2 Here, we are copying student table to another table with the same structure with no rows copied.

87. Write a Query to display odd records from student table?

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS RowID FROM student) WHERE row_id %2!=0

88. Explain Right Outer Join with Example?

This join is usable, when user wants all the records from Right table (Second table) and only equal or matching records from First or left table. The unmatched records are considered as null records. Example: Select t1.col1,t2.col2….t ‘n’col ‘n.’. from table1 t1,table2 t2 where t1.col(+)=t2.col;

89. Explain Non Equi Join with example?

1. When two or more tables are joining without equal to condition then that join is known as Non Equi Join. Any operator can be used here that is <>,!=,<,>,Between. Example: Select b.Department_ID,b.Department_name from Employee a,Department b where a.Department_id <> b.Department_ID;

90. How can you delete duplicate records in a table with no primary key?

By using the SET ROWCOUNT command. It limits the number of records affected by a command. Let’s take an example, if you have 2 duplicate rows, you would SET ROWCOUNT 1, execute DELETE command and then SET ROWCOUNT 0

91. Difference between NVL and NVL2 functions?

Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null. With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1. With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.

92. What is the difference between clustered and non-clustered indexes?

1.Clustered indexes can be read rapidly rather than non-clustered indexes. 

2. Clustered indexes store data physically in the table or view whereas, non-clustered indexes do not store data in the table as it has separate structure from the data row.

93. What does this query says? GRANT privilege_name ON object_name TO {user_name|PUBLIC|role_name} [WITH GRANT OPTION];

The given syntax indicates that the user can grant access to another user too.

94. Where MyISAM table is stored?

Each MyISAM table is stored on disk in three files. 

1. The “.frm” file stores the table definition. 

2. The data file has a ‘.MYD’ (MYData) extension. 

3. The index file has a ‘.MYI’ (MYIndex) extension. 

95. What does myisamchk do?

It compress the MyISAM tables, which reduces their disk or memory usage.

This brings us to the end of the SQL Interview questions. Glad to see you are now better equipped to face an interview. 

Wondering where to learn the highly coveted in demand skills for free? Check out the courses on Great Learning Academy. Enroll to any course, learn the in demand skill and get your free certificate. Hurry!

1

LEAVE A REPLY

Please enter your comment!
Please enter your name here

seventeen + twenty =