Chapter 1 – 250 Most Asked SQL Interview Questions
250 Most Asked SQL Interview Questions
We are a group of 30+ Analytics professionals with an average experience of 6 years and we have curated a set of 250 questions that will first take you from a beginner level to a moderate+ level and will then help you to solve the tricky and complex interview questions.
Our panel works with the best of the product-based companies like Google, Amazon, OLA,OYO, Flipkart, Myntra, Rapido, Uber, Microsoft, etc. and the below questions are a result of the collaboration between all of us.
Link to the solution to all the questions given below with a detailed explanation
250 Most Asked SQL Interview Questions
Solve all the questions given below, and make sure to take notes while solving these questions. In case you are stuck on some questions or want to quicken your progress, then we will suggest you either Google the questions or get the book from our e-shop place and download the PDF from Here.
1. What is SQL?
2. Who maintains the database, queries, website, frontend, backend, and other services in an application?
3. What is a database?
4. What is DBMS?
5. What is RDBMS?
6. What is the difference between DBMS and RDBMS?
7. What are the examples of DBMS and RDBMS?
8. What is the difference between SQL and MySQL?
9. What are Tables and Fields?
10. What is the basic syntax of SQL?
11. Write any query to show how the code is written in SQL?
12. What are the Constraints in SQL?
13. What is the SELECT command?
14. Explain the use of the FROM clause?
15. What and how is * used in SQL?
16. What is the use of the WHERE clause in SQL?
17. What is the use of the ORDER BY clause in SQL?
18. What is the use of the GROUP BY clause in SQL?
19. Write a SQL query to get the average marks scored by each class.
20. Can we use the WHERE command with an aggregate function?
21. What is the use of the HAVING clause in SQL?
22. What is the use of the LIMIT clause in SQL?
23. Use the LIKE clause to filter all the products that start with a and end with e
24. What are wildcards in SQL?
25. One more important interview question on this line is when you are asked to pick the product name iphone but the iphone can be written as
26. How to create a table with a single field as primary key?
27. How to create a table with multiple fields as primary key?
28. What is a Primary Key?
29. What is a UNIQUE constraint?
30. What is a Foreign Key?
31. What is a Join?
32. What are the different types of joins?
33. What is an inner join? Give its syntax.
34. What is Left Join? Give its syntax.
35. What is Right Join? Give its syntax.
36. What is Full Join? Give its syntax
37. What is self-join?
38. In an EmployeeDetails table, we have 3 columns:-
Emp_id, Emp_Name, and Mgr_id
The Manager id is nothing but the employee id of some employee. Example
1, A, 2
2, B, 3
3, C, 4
B is the manager of A and C is the manager of B.
Output required
| Employee Name | Manager Name |
| A | B |
| B | C |
| C | D |
Write a code in SQL to get the desired output.
39. Now in the above example, we have used Inner join as a part of the self join, the above will query will work fine for all the employees who have a Manager, but the Managing Director, CEO’s, etc. won’t necessarily have a Manager. What part of the query will you change to make sure all the employees of the company are present in the output?
40. What is a Cross-Join?
41. What is an Index?
42. We have two columns(Revenue and Cost Price) in a
table like below, get me the Profit column
43. How do the SQL commands flow at the back end?
44. Write a SQL query to get the second highest query using subquery.
45. Write a SQL query to find all the student names Nitin in a table
select name from student where lower(name) like ‘%nitin%’
46. Write a query to get all the student with name length 10, starting with K and ending with z.
47. Write a SQL query to get the second highest query using Ranking
48. Can you use HAVING command without any aggregate function in SQL?
49. You have data on people have applied for a lottery ticket. The data consists of their name and ticket number. You have to choose winners by selecting the people present in the alternate rows (the first winner starting from row number 3). Write a query to make things easy to select the winners.
50. We have the following values
10000
10000
20000
30000
30000
30000
What would be the result of row number, rank, and dense rank ?
51. Find all the students who either are male or live in Mumbai ( have Mumbai as a part of their address).
52. Suppose there are two columns in employee table i.e. emp id and email
get all the unique domains like gmail.com, yahoo.com, outlook.com, etc.
53. Can you join two table without any common column?
54 to 58. Give the output for the following
SELECT ‘NITIN’+1
SELECT ‘NITIN’+’1’
SELECT(SELECT ‘NITIN’)
SELECT ‘1’+1
SELECT 1+’1’
59. select case when null=null then ‘Amit’ else ‘Rahul’ end from dual.
What will be the output of the above query?
60. What is the difference between COUNT(*) and COUNT(ColName)?
61. Help me create a table with all the employee Names and Manager Names.
62. Write a query for collecting the names of children who pursuing their graduation in their residential city.
(Hint- Look for the common city name in the columns of ADDRESS and COLLEGE)
63. What is indexing in SQL?
64. List the different types of relationships in SQL.
65. Demonstrate how to write a query to show details of an HR whose name starts with M.
66. What is OLTP?
67. What are the differences between OLTP and OLAP?
68. What is the usage of the NVL() function?
69. Explain character-manipulation functions? Explains its different types in SQL.
70. Get the number of duplicate names and their frequency
Table – Employee
Name
Nitin
Amit
Gaurav
Nitin
Amit
Output
Nitin – 2
Amit – 2
Gaurav – 1
71. Write the SQL query to get the third maximum salary of an employee from a table named employees.
72. Define records and fields in a table
73. What is the use of FETCH command?
74. What are UNION, MINUS and INTERSECT commands?
75. How to fetch alternate records(even rows) from a table?
76. You always have a big data i.e. millions of rows in your tables, how would you partition it for optimum performance?
77. Suppose in class, you have 3n boys and 2n girls with their names tabulated along with their weight and gender. Write a SQL query to separate students alphabetically who are over-weight (55kg for girls, 75kg for boys)
78. What is indexing in SQL? Explain with proper example.
79. Arrange the employees with respect to their Joining date, most experienced employee coming on the top followed by others.
80. What is Cursor? How to use a Cursor?
81. List the different types of relationships in SQL.
82. How to create a temp table in SQL Server?
83. Write an SQL Query find number of employees whose DOB is between 01/07/1965 to 31/12/1975. Collect the data separately for different gender.
Use column name as sex, DOB table name Employees.
84. How can you create an empty table from an existing table? Write the steps and explain the working.
85. What is the use of IFNULL and ISNULL in SQL?
86. You have got some data in the Table 1 and Table 2
Write a SQL query to create a Table 3 that contains the following columns- Id, First_Name, Last_Name, Salary
87. NoSQL vs SQL
88. What is the difference between NOW() and CURRENT_DATE()?
89. Extract the information about all department managers who were hired between the 1st of January 2020 and the 1st of January 2021.
90. If a table contains duplicate rows, does a query result display the duplicate values by default? How can you eliminate duplicate rows from a query result?
91. How to create a stored procedure using SQL Server?
92. Which join is used to join a table with itself
93. What is the difference between CHAR and VARCHAR2 datatype in SQL?
94. List the total numbers of products of each brand(Take the name of the table as well as the column name by yourself)
95. Does the virtual table created occupy space for the operation to joining with itself?
96. What are Constraints?
97. What is the difference between DELETE and TRUNCATE statements?
98. What is CROSS JOIN UNNEST in Presto ?
99. What is LATERAL VIEW explode in Hive?
100-103.Suppose there are two tables, X and Y, X has just one column A and Y has B.
These are the two tables
Questions – How many rows will be populated if you do
-X left join Y
-X inner join Y
-X cross join Y
-X right join Y
104.
In the table below, some duplicate records might be present by mistake. Sort out a way to locate them and find a way to delete them.
105. What is the difference between clustered and non-clustered index in SQL?
106. Pivot a table in SQL without using pivot function
Suppose there are two columns
Age Name
25 Nitin
30 Amit
27 Rishab
29 Ankush
Convert into
Name Nitin. Amit. Rishab. Ankush
Age. 25. 30. 27. 29
107. What is the function of OFFSET command? You might know the definition but create a scenario where you have to make use of it. Also, can the same process be done with any other method?
108. Get all employee detail from EmployeeDetail table whose
“FirstName” not start with any single character between ‘a-p’
109. What do you understand by query optimization?
110. Important conditions for joining two tables on a key?
111. What are Entities and Relationships?
112. Assume the name of table and columns
There are two tables with a common column. Which one will take more processing time
-Outer Join
– Full Outer Join
– Cartesian Join
113. What is NTILE with syntax? With example and use case
114. What is Normalization and what are the advantages of it?
115. Explain different types of Normalization.
116. Find the Nth largest salary from employee table.
117. How the triggers will execute if two or more triggers?
118. Get all the employee detail from EmployeeDetail table whose “FirstName” starts with A and contain 5 letters
119. Could you tell output or result of following SQL statements?
(Hint- In some cases, there may be an error. So, try to locate them and answer accordingly)
select 5
select ‘5’
select count (‘5’)
select count (5)
select count (*)
120. How do you maintain database integrity where deletions from one table will automatically cause deletions in another table?
121. Find the number of people who are from Delhi and have arrived in Patna in the last 7 days
122. How do you handle ties in dense ranking?
123. Does the data stored in the stored procedure increase access time or execution time?
124. How many Aggregate functions are available in SQL?
125. When do we use Coalesce() function?
126. You have a table with the following 3 columns
We want to know the number of customer who have their first two orders in the last 180 days. In the above example 123 will qualify but 456 won’t qualify
127. What is a RANK() function?
128. How do we use the DISTINCT statement? What is its use?
129. What is the ACID property in a database?
130. What are the different types of a subquery?
131. Write an SQL query to fetch the current date-time from the system.
132 to 137
There are two tables:
132. Write a query to fetch top N records using the TOP/LIMIT, ordered by ConsultationFees.
133.Write a SQL query to create a table where the structure is copied from other table.
134. Write a query to fetch even and odd rows from a table.
135. Write an SQL query to fetch duplicate records from Patients, without considering the primary key.
136. Write a query to fetch the number of patients whose weight is greater than 68.
137. Write a query to retrieve the list of patients from the same state.
138. Write a SQL query to fetch consultation fees – wise count and sort them in descending order.
139. Write a SQL query to retrieve patient details from the Patients table who have a weight in the PatientsCheckup table.
140. Write a SQL query to retrieve the last 2 records from the Patients table.
141. Write a SQL query to find all the patients who joined in the year 2022.
142. Write a SQL query to fetch 50% records from the PatientsCheckup table.
143. Write a query to update the patient names by removing the leading and trailing spaces.
144. Write a query to add email validation to your database.
145. Write a query to find all patient names whose name:
Begin with A
Ends with S and contains 3 alphabets
Staying in the state Telangana
146. Write a SQL query to fetch details of all patients excluding patients with name “Sheela” and “Anay”.
147. Write a query to fetch the total count of occurrences of a particular character – ‘x’ in the PatientName.
148. Write a query to retrieve the first three characters of PatientName from the Patients table.
149. Write a query to combine Address and state into a new column – NewAddress.
150. Write a query to fetch PatientIDs which are present in:
- Both tables
- One of the table.
151. Write a query to find the number of patients whose RegDate is between 01/04/2021 to 31/12/2022 and are grouped according to state.
152. Write a query to fetch all records from the Patients table; ordered by PatientName in ascending order, State in descending order.
153. How can you create an empty table from an existing table? Write the steps and explain the working.
154. For what purpose we are using view? How is it created to get the job done?
155. Query the list of CITY names from STATION that does not start with vowels and do not end with vowels. Your result cannot contain duplicates.
156. State the differences between views and tables.
157. What is the difference between BETWEEN and IN operators in SQL?
158. What are the different types of a subquery?
159. Name the operator which is used in the query for pattern matching?
160. Write a SQL query to find the 10th highest employee salary from an Employee table
161. Given a table dbo.users where the column user_id is a unique numeric identifier, how can you efficiently select the first 100 odd user_id values from the table?
162. What will be the output of the below query, given an Employee table having 10 records?
BEGIN TRAN
TRUNCATE TABLE Employees
ROLLBACK
SELECT * FROM Employees
163. How do you get the last id without the max function?
164. What is the difference between IN and EXISTS?
165. Which is better CTE or temp table?
166. Why are CTEs slower than temp tables?
167. Write an SQL query to fetch the EmpIds that are present in EmployeeDetails but not in EmployeeSalary.
168. Write an SQL query to fetch the position of a given character(s) in a field.
169. Write a query to fetch only the first name(string before space) from the FullName column of the EmployeeDetails table.
170. Write an SQL query to find the count of the total occurrences of a particular character – ‘n’ in the FullName field.
171. Write an SQL query to fetch employee names having a salary greater than or equal to 5000 and less than or equal to 10000.
172. Write an SQL query to fetch duplicate records from EmployeeDetails (without considering the primary key – EmpId).
173. What is the difference between COALESCE() & ISNULL()?
174. Write SQL Query to find duplicate rows in a database? and then write SQL query to delete them?
175. There is a table which contains two columns Student and Marks, you need to find all the students, whose marks are greater than average marks i.e. list of above-average students.
176. How to Show the Max marks and min marks together from student table?
177. Give the order of SQL SELECT.
178. What do we need to check in Database Testing?
179. How many types of Privileges are available in SQL?
180. What are the types of transaction levels in SQL SERVER?
181. What is log shipping?
182. What is a T-SQL?
183. What is query optimization?
184. What is the purpose of Query Optimization?
185. What are the metrics for analyzing query performance for SQL Query Optimization?
186. What is the difference between cross join and natural join?
187. What is SQL Injection?
188. What is BLOB and TEXT in MySQL?
189. Explain database white box testing and black box testing.
190. Explain the difference between OLTP and OLAP.
191. What is a stored procedure? Give an example.
192. What are the types of views in SQL?
193. Mention different types of replication in SQL Server?
194. What is a data warehouse?
195. What do you mean by data integrity?
196. What is a No SQL database? What are some examples of NO SQL database?
197. What is a Relational Database? What are some examples of Relational databases?
198. Which type of data is stored in NO SQL and Relational Databases?
199. NO SQL is a vertically scalable or horizontally scalable database? What about Relational Database? How does this scaling make database life easier?
200. When should we use NO SQL and where should we use Relational Database?
201. Disadvantage of Temporary keyword in Presto?
202. What is row number in SQL?
203. How is row number different from rank and dense rank?
204. What is the syntax for calculating row number in SQL?
205. Can you give an example of when you would use row number?
206. How can you use row number to group data?
207. How do you reset the row number to 1 for each group in SQL?
208. Can you explain the performance implications of using row numbers?
209. How would you handle NULL values when using row number?
210. How can you use row number to perform pagination in SQL?
211. How can you use row number to delete duplicate rows in SQL?
212. What is dense rank in SQL?
213. How is dense rank different from rank and row number?
214. What is the syntax for calculating dense rank in SQL?
215. Can you give an example of when you would use dense rank?
216. How do you handle ties in dense ranking?
217. Can you explain the difference between dense rank and percent rank?
218. How can you use dense rank to group data?
219. How do you calculate dense rank for a subset of data in SQL?
220. Can you explain the performance implications of using dense rank?
221. How would you handle NULL values when using dense rank?
222. What is the LIKE command in SQL?
223. How does the LIKE command work?
224. What are the wildcards used in the LIKE command?
225. Can you give an example of using the LIKE command?
226. How can you use the LIKE command with multiple patterns?
227. How can you use the LIKE command to search for data that contains special characters?
228. Can you use the LIKE command with a NOT operator to exclude specific patterns?
229. How can you use the LIKE command to search for data that starts with or ends with a specific pattern?
230. How can you improve the performance of a LIKE command query?
231. Can you use the LIKE command with parameters in a stored procedure?
232. What is a regular expression and why would you use it in SQL?
233. How do you use the REGEXP_LIKE function in SQL? Can you provide an example of when you might use this function?
234. How do you use the REGEXP_REPLACE function in SQL? Can you provide an example of when you might use this function?
235. How do you use character classes in regular expressions? Can you provide an example of when you might use a character class in SQL?
236. How do you use quantifiers in regular expressions? Can you provide an example of when you might use a quantifier in SQL?
237. How do you use anchors in regular expressions? Can you provide an example of when you might use an anchor in SQL?
238. How do you use alternation in regular expressions? Can you provide an example of when you might use alternation in SQL?
239. How do you escape special characters in regular expressions? Can you provide an example of when you might need to escape a special character in SQL?
240. How do you use regular expressions to validate input in SQL? Can you provide an example of how you might validate an email address or a phone number using regular expressions in SQL?
241. How can you use regular expressions to extract data from a string in SQL? Can you provide an example of how you might extract specific values from a string using regular expressions in SQL?
242. What is JSON?
243. How does Presto handle JSON data?
244. What is the syntax for querying JSON data in Presto?
245. How does Presto handle nested JSON data?
246. Can you join JSON data with other tables in Presto?
247. How does Presto handle JSON arrays?
248. How can you flatten nested JSON data in Presto?
249. How can you convert JSON data to a relational format in Presto?
250. How can you use the json_table() function in Presto?
251. How can you use the json_arrayagg() function in Presto?
The Data Monk Product and Services
- Youtube Channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
Link – The Data Monk Youtube Channel - Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
Link – The Data Monk website - E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions
Link – The Data E-shop Page - Mock Interviews
Book a slot on Top Mate - Career Guidance/Mentorship
Book a slot on Top Mate - Resume-making and review
Book a slot on Top Mate
The Data Monk e-book Bundle
1.For Fresher to 7 Years of Experience
2000+ interview questions on 12 ML Algorithm,AWS, PCA, Data Preprocessing, Python, Numpy, Pandas, and 100s of case studies
2. For Fresher to 1-3 Years of Experience
Crack any analytics or data science interview with our 1400+ interview questions which focus on multiple domains i.e. SQL, R, Python, Machine Learning, Statistics, and Visualization
3.For 2-5 Years of Experience
1200+ Interview Questions on all the important Machine Learning algorithms (including complete Python code) Ada Boost, CNN, ANN, Forecasting (ARIMA, SARIMA, ARIMAX), Clustering, LSTM, SVM, Linear Regression, Logistic Regression, Sentiment Analysis, NLP, K-Mean
