10 Questions, 10 Minutes – 2/100
This is something which has been on my mind since a long time. We will be picking 10 questions per day and would like to simplify it.
We will make sure that the complete article is covered in 10 minutes by the reader. There will be 100 posts in the coming 3 months.
The articles/questions will revolve around SQL, Statistics, Python/R, MS Excel, Statistical Modelling, and case studies.
The questions will be a mix of these topics to help you prepare for interviews
You can also contribute by framing 10 questions and sending it to contact@thedatamonk.com or messaging me on Linkedin.
The questions will be updated late in the night ~1-2 a.m. and will be posted on Linkedin as well.
Let’s see how many can we solve in the next 100 posts
1/100 – SQL Questions
1. How to find the minimum salary using subquery?
-SELECT *
FROM employee
WHERE salary = (select MIN(salary) from employee);
2. How to find the second minimum salary?
– SELECT *
FROM employee
WHERE salary = (SELECT MIN(salary) FROM employee > SELECT MIN(salary) FROM employee)
Similarly, find the third minimum salary
– SELECT *
FROM employee
WHERE salary = (SELECT MIN(salary) FROM employee > SELECT MIN(salary) FROM employee > SELECT MIN(salary) FROM employee)
3. The above query is too lengthy, write a query to get the third minimum salary with some other method.
– SELECT DISTINCT (salary)
FROM emp e1 where 3 = (SELECT COUNT(DISTINCT salary) FROM emp e2 WHERE e1.sal >= e2.sal);
4. How to get 3 Min salaries?
-SELECT DISTINCT salary FROM emp a WHERE 3 >= (SELECT COUNT(DISTINCT salary) FROM emp b WHERE a.salary >= b.salary);
5. Some basic SQL Select questions
– SELECT 125
125
-SELECT ‘Ankit’+’1’
Ankit1
-SELECT ‘Ankit’+1
Error
– SELECT ‘2’+2
4
-SELECT SUM(‘1’)
1
6. Write a generic method to fetch the nth highest salary without TOP or Limit
–
SELECT Salary
FROM Worker W1
WHERE n-1 = (
SELECT COUNT( DISTINCT ( W2.Salary ) )
FROM Worker W2
WHERE W2.Salary >= W1.Salary
);
7. LAG(): Provides access to a row at a given physical offset that comes before the current row. Use this function in a SELECT statement to compare values in the current row with values in a previous row as
specified by offset. Default offset is 1 if not specified. If Partition By clause is specified then it returns the offset Value in each partition after ordering the partition by Order By Clause.
Basically, lag() is used to create one more column in the table where you can get the previous value of the specified column
Col1 | Col2 | Lag_Col |
a | 10 | Null |
b | 20 | 10 |
c | 30 | 20 |
d | 40 | 30 |
8.One more example
employee_number | last_name | first_name | salary | dept_id |
---|---|---|---|---|
12009 | Sutherland | Barbara | 54000 | 45 |
34974 | Yates | Fred | 80000 | 45 |
34987 | Erickson | Neil | 42000 | 45 |
45001 | Parker | Sally | 57500 | 30 |
75623 | Gates | Steve | 65000 | 30 |
SELECT dept_id, last_name, salary, LAG (salary,1) OVER (ORDER BY salary) AS lower_salary FROM employees;
dept_id | last_name | salary | lower_salary |
---|---|---|---|
45 | Erickson | 42000 | NULL |
45 | Sutherland | 54000 | 42000 |
30 | Parker | 57500 | 54000 |
30 | Gates | 65000 | 57500 |
45 | Yates | 80000 | 65000 |
9. LEAD() – Provides access to a row at a given physical offset that comes after the current row. Use this function in a SELECT statement to compare values in the current row with values in a subsequent row
as specified by offset. Default offset is 1 if not specified. If Partition By clause is specified then it returns the offset Value in each partition after ordering the partition by Order By Clause
10. Which operator is used for Pattern Matching?
LIKE operator is used for pattern matching. It supports below wildcards.
% : Matches any string of zero or more characters.
_ : Matches any single character.
[] : Matches any single character within the specified range ([a-f]) or set ([abcdef]).
[^] : Matches any single character not within the specified range ([^a-f]) or set ([^abcdef])
This was the second set of 10 questions, if you want to learn more about the type of questions asked in different Data Science interviews then do try the below book:-
What do they ask in top Data Science Interviews: 5 Complete Data Science Real Interviews Q and A
What do they ask in Top Data Science Interview Part 2: Amazon, Accenture, Sapient, Deloitte, and BookMyShow
Keep Learning 🙂
The Data Monk