Top 10 SQL Interview Queries | Popular SQL Queries for SQL Interview
HTML-код
- Опубликовано: 13 май 2024
- In this video, we look at 10 commonly asked SQL Queries during interviews. These are in my opinion top 10 SQL queries to learn before going to any SQL Interview. These 10 queries will cover most of the important SQL concepts hence if you can solve these 10 queries then you would have a very good understanding of SQL.
Download all 10 SQL query scripts, solutions, and problem statements from my blog below:
techtfq.com/blog/top-10-sql-i...
Check out one of the BEST platforms to learn SQL:
learnsql.com/?ref=thoufiqmoha...
Get yourself a "Certificate of Competency in SQL" for free from below:
learnsql.com/sql-skill-assess...
Timestamp:
00:00 Intro
01:03 Query 1
04:25 Query 2
07:34 LearnSQL
09:34 Get your Free SQL Competence Certificate
10:14 Query 3
12:59 Query 4
16:05 Query 5
19:56 Query 6
23:27 Query 7
26:03 Query 8
27:58 Query 9
31:13 Query 10
More Videos:
Delete Duplicate data (10 ways):
• How to remove Duplicat...
Window function Frame clause Tutorial:
• SQL Window Function | ...
JOINS Tutorial (SELF Join)
• SQL JOINS Tutorial for...
Recursive SQL Tutorial:
• Recursive SQL Queries ...
Subqueries Tutorial:
• Subquery in SQL | Corr...
Thanks for watching!
Thoufiq | techTFQ
Check out the learn SQL platform here:
learnsql.com/?ref=thoufiqmohammed
Get yourself a "Certificate of Competency in SQL" for free from below:
learnsql.com/sql-skill-assessment
Amazing content Thanks man
Thank you sooo much Toufiq for this SQL course! Thanks a lot! 🙏
Your welcome bro
Hi Toufiq! I have gone through SQL course from the beginning to brush up my skills and all the concepts. In the recent interview, I cleared all the technical rounds and I got placed last week with a good hike. Your videos and your teaching helped me a lot.! I am really Happy! Please keep going. God bless you!
Congragulations Malathi.. very happy for you. Super glad to know my content helped :)
Thank you soo much! @@techTFQ
Can you please share the course videos
@@Malathi_Steps hi , could you please some SQL interview questions which was asked in your interview?, as my interview is tomorrow
@@richakapoor445 - Sure. There are mainly these concepts were asked: 1. Finding Duplicates, deleting the duplicates(not all the records, 1 should be there), 2. all types of joins, especially the self join 3. Differences b/w delete, drop, truncate 4. Rank(),Dense Rank(), Row num 5. last 10 records, first 10 records 6. highest salary, lowest salary of an employee in each dept 7. set operators and the difference b/w union and union all 8. data base related questions like: how to test if source DB and Target DB are different 9. questions on like operators ex: name starts with letter 'M' and ends with 'N' etc. 10. all null functions. case statement etc. For me, they asked about API testing as well(their requirement). Hope this helps! All the best!
Your Channel is one the best when it comes to taking SQL to the next level. Really appreciate this content, thank you!
Thank you Toufiq, for question 4 we can do in very simple way instead writing such complex query select least(startingpoint,endingpoint),greatest(startingpoint,endingpoint),distance from distance;
Awesome video!!! I'm using SQL server. What I do to follow along is CREATE TABLE and INSERT INTO the same simple tables/data and try to solve it before watching your solutions.
Some we did exactly the same.... others we went about it a different way. Either way, this is a fantastic video to prep you for SQL Interviews. Thank you!!!!
Dear Toufiq, I would say thank you for your effort. On Question 5 I didn't get the point of joining with the original table, I think it could work as expected with out joining the cte with the original table as follow unless we have some edge condition we should consider.
with recursive cte as
(
select id, item_name, total_count, 1 as level
from travel_items
union all
select cte.id, cte.item_name, cte.total_count -1, level + 1 as level
from cte
where cte.total_count > 1
)
select id, item_name, level
from cte
with recursive cte as
(
select id, item_name, total_count, 1 as lvl from travel_items
union
select id, item_name,total_count, lvl+1 as lvl
from cte where lvl
Thankyou , Its helpful and correct question which are asked in Interviews
Thank you very much TFQ, this was so helpful!
Haven't seen any channel creating this gold level across platform
Thank you:)
Hi Toufiq. Thanks a lot for providing so many useful examples on solving SQL. I have been learning quite a lot from your videos and your SQL / python courses. Really appreciate for these courses and videos that you have done for us. Also, thanks for introducing the learnSQL website to us. I have registered for the life time eLearning also. Also, you are the best teacher I've met on learning SQL. Hope you continue to make these quality and useful video for us. Keep going brother 👍
From Tony
Thank you Tony. Glad it helped :)
After watching your SQL videos I thought my thinking capability increase gradually that where to think about to write a query....😊 Thanks a lot.....
Hi Toufiq!!
All question are so interesting. I just found few things which effects the query.
Q1) The table has only 2 duplicate rows, assume if table has 3 duplicate rows then sol 1 and sol 2 won't work as it will select MAX() and MIN() model_id. Here most accurate sol is sol 3 using ROW_NUMBER().
Q7) In this to write negative amount as (amount$), || won't work in MYSQL instead CONCAT() can be used.
partition by which column?
how can i call dataset in first query as having duplicates ?since there is a primary key id
what the hell are you saying ?? It will work, analyse sol1 and sol2 and then think. Just don't mug up !!
|| also knowns as pipes does work in mysql. You just have to turn it on by doing this: SET SQL_MODE = 'PIPES_AS_CONCAT';
Very informative. Thank you🙏
Always a pleasure to watch your videos Thofik. I m learning a lot from you .
Thanks a lot bro. Truly appreciate it
you are doing so awesome job
Hi Taufiq,
I just solved the problem no . 5 without join.
Let's have a look and lemme know if i am correct.
with recursive cte as
(
select id, item_name, total_count, 1 as level from item
union all
select cte.id, cte.item_name, total_count - 1, level+1 as level from cte
where total_count > 1
)
select cte.id, cte.item_name,level as cnt from cte
order by id asc
Thank you for creating this video. It was excellent. In Question 10, the 3rd union should also consider 'CREATED'. d2.status in ('DELIVERED','CREATED'))
@techTFQ
an easier solve for the issue with min function is this -
select *, min(salary) over(partition by dept order by salary) as min_sal
, max(salary) over(partition by dept order by salary desc) as max_sal
from
table;
This will ensure the frame issue wont come into picture, right?
Thank you , bahut sikhne ko milta he , aur questions late raho 👌👍
Welcome
Nice explanation and it is very useful 👌👌
the perfect video i never know i needed
Thank you:)
Another quality content from Techtfq👌👌👏 Keep going brother🙌
Thanks:)
Hi Toufiq! I have been following all your videos regarding sql but this is can you make more videos, on my sql work bench and interview questions also
Excellent questions Toufiq , covers quite a bit of SQL innards
Thanks:)
Thank you Thoufiq, Really leaned a lot from you.
Glad to hear that
Can you make a video explaining how the joins on different row number ids or less than row numbers id work? I can't fully understand it. Thank you for all the great info!
Good job! Thank you very much!
Please make a video about data execution in sql server, how to identify bad or good ( efficient or inefficient queries) , how the cost of query in percent matters
Thank you very much for such detailed info
Welcome:)
thank you for this video! would you say this level of SQL knowledge is generally tested for data engineer/data scientist roles as opposed to business functions? for example at a company like google or meta. thank you!
Hi, I've been working as a programmer for about two years in a financial company. However, I still struggle with understanding complex queries. While I get the few chance to write queries at work, there are instances where I need to read long queries, sometimes exceeding 500 sentences, due to unexpected circumstances.
I genuinely make an effort to read and comprehend them, but when multiple challenging grammar concepts like outer joins and subqueries are used together, I find it difficult to grasp. In this situation, what should I focus on learning to improve my ability to read complex and lengthy query sentences? I really want to enhance my skills in this area.
Top 👍
Hi @techTFQ! In problem 10, Why are we not considering a case where order status is submitted and created for same user (3rd statement in the union)??
Great Work
How I can select all the row specific column data in one column separate by ,.
Like in a table userdetails has a column fullname.
On 'select fullname from userdetails' I should not get rows.
I should get single column (string) where values are separate by ,
Like: Ram Sing, Shayam, Sita Jha, Gita Migan
Superb !!.. Your content really helps with approach that could be taken. 🙂👍
Glad to hear that
Awesome content as usual!
Appreciate it!
Thanks a lot
Thank you Amazing content
Glad you enjoy it!
Excellent Thoufiq, thanks.
Where can i find these queries for practice ?
Hey Toufeeq!
I have been trying buy your SQL recording from your website but it doesn't allow me to move forward from cart.
Can you fix this?
Hi techtfq, can you please elaborate how the join works internally when 2 or more tables are joined. I am getting confused often when I try to join the third table. Please help me in this with clear picture how the intermediate tables are created and within this already joined two tables are joined with third table. So after joining the first two tables when I write a condition to join third table from which table I should pick the column to join condition.
Nice thofiq
thanks buddy
Hi TFQ, Thanks for these free tutorials. Please do share a collection of medium and tricky SQL queries and solutions so we can practice before an interview.
ruclips.net/video/aE623ff7zkM/видео.html&pp=gAQBiAQB
Thanks for such good explanation on these problems.
I have a question about query5, why do we need join in the recursive part. Will it not work woithout join. e.g. below query worked for me.
WITH RECURSIVE trvl_itm AS (
SELECT id, item_name, count_of, 1 AS level FROM travel_items
UNION ALL
SELECT id, item_name, trvl_itm.count_of-1, LEVEL+ 1 AS level FROM trvl_itm
WHERE trvl_itm.count_of > 1
)
SELECT id, item_name, level FROM trvl_itm
ORDER BY id, level;
Really Really Awesome !!
Thanks a lot!
For query 5, why there's need of join
first base table will work and input for the 2nd query. then why need of join in query 5.
i got solution without join in MS SQL
with cte as
(select id, item_name, total_count from travel_items
union all
select id, item_name, total_count - 1 as total_count from cte
where total_count > 1
)
select * from cte
Hi Taufiq, I had been asked to write sql query to names in alternate rows, say for example a table have "name" colum having 10 rows, data as "abc" 5 rows and "xyz" 5 rows, and I need to display these "abc" and "xyz" in alternate rows as abc xyz abc xyz so on
Can you please provide vedio for performance tuning or SP optimization
Always a privilege for learners to get your video … You are doing a good service 👌God bless your efforts
Thank you:)
These types of sql questions are asked for data engineer/ data analyst roles only or for SDE roles also ?
Hi Toufiq, for Quest #6, part 2 each team plays every other team twice, a much simpler solution would be to do Union ALL of part 1
I.e. every team plays other team once *2 i.e. union ALL of this , right? Let me know if there's some flaw in my thinking?
alternative for Q10:
SELECT CUST_NAME,
CASE WHEN TOT_CNT = DELIVER_CNT THEN 'DELIVERED'
WHEN DELIVER_CNT > 0 THEN 'IN PROGRESS'
WHEN TOT_CNT = submit_cnt THEN 'AWAITING PROGRESS'
ELSE 'AWAITING SUBMISSION'
END STATUS
FROM (
select cust_name,
sum(case when status = 'DELIVERED' then 1 else 0 end ) as deliver_cnt,
sum(case when status = 'SUBMITTED' then 1 else 0 end ) as submit_cnt,
count(order_id) tot_cnt
from cust_orders
group by cust_name
)
Best video, I have faced a few of these exact questions from this video in my interviews. Thank you @techTFQ
Welcome:)
Small improvement for Query 2 Solution:
As per MySQL 8.0 Docs, the frame_clause defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when ORDER BY is not present. And the order of the partitioned rows does not matter for the purpose of the query. Therefore you can remove the ORDER BY clauses for both partitions:
SELECT *,
MAX(salary) OVER(PARTITION BY dept) AS highest_salary,
MIN(salary) OVER(PARTITION BY dept) AS lowest_salary
FROM employee;
i think query 2 output is wrong for IT department as we have to find highest and lowest salary for each department and the query you mentioned is showing other salaries as well
my solution for q2
select * from
(
select *,
max(salary) over(partition by dept rows between unbounded preceding and unbounded following) as "highest_sal",
min(salary) over(partition by dept rows between unbounded preceding and unbounded following) as "lowest_sal"
from employee order by dept,salary desc)x where salary in(highest_sal,lowest_sal);
thanks fir video Do yiu have videos for plsql
select max(salary) , min(salary) , dept from employee group by dept ..... i think this is a better solution for query 2 ......please let me know if any mistake from my end
Hi taufiq! Can you make logical interview questions more?
Can you clarify what you mean by logical questions?
I have reviewed your top 25 SQL questions on the blog and also watched the video on Joins for interviews. The interviewer has been posing more questions of this nature, particularly those based on scenarios.@@techTFQ
Hi Sir, for question number 5, I tried with different solution but with same recursive technique,
solution -->
with recursive cte as (
select id, item, count
from travel_items
union
select id, item, count - 1 as count
from cte
where count > 1
)select id, item from cte order by id
I tried this on mysql, and got expected output, is this correct?
hi toufiq plz do more videos on data analytics and how to do more analytical work with excel.plz plz
Noted
in your vlog employee table creation script is not working for 2019 version ,fyi
in query 2, is it not possible to use group and getting min and max of the salaries?
It seems easier, I could not get why you used partition.
Pls make query tuning videos..
I have a doubt in the 1 query 1 solution what if the color is different but as per your code it will delete that record also right?
Can you make videos on sql developer
Road map to be a sql developer please
Can you pls share create table and data scripts ? not sure if you have already shared somewhere
Are these questions asked in Business Analyst position as well?
For question 4 we can do like this also
with cte as (
select *, row_number() over(order by (select null)) as rn from src_dest_distance)
select source,destination,distance from cte
where rn%20
The solution in the video for question 4 doesn't work if there is only one entry like mumbai to kerala. but this solution works on that criteria.
Absolutely better solution. This query covers all scenarios and also in SQL Server you are not able to write ROW_NUMBER without ORDER BY clause, what he did in his video.
can we get the db data your using in the video?
In the car duplicate question, you haven't mention color but if the color is different then it won't be a duplicate one right..then is it right to delete the record without considering color?
Please clarify
I have same question. Because we consider that the rows are different if they differ in at least one column.
For query 2 the question we have to find min and max salary of evey department then in that case very dept. Will have two rows and the final output will have 8 rows but your output has 10 rows, pls do correct me if i am wrong
Hi Toufiq is there any new session start for python beginners from this December. I'm from india
Probably from Jan
Can I expect this kind of sql questions or more challenging than these with 2 years of DE experience?
For Q4, why can't you just do where source < destination? or swap and distinct.
In question 2, I think there is no need for frame clause?
Hi,can u please tell me the difference between database, data warehouse, data lake and data mart . Please 🙏
you need to add color to your delete queries - it won't work unless you group by ALL fields (excluding the id), otherwise, you'll delete a non-dup record
SQL Interview Questions of the Top Companies
ruclips.net/p/PLqGLh1jt697xtgiGwGUTFpOctT82ANdJZ
Hi Taufiq,
My simpler solution -
with base as(
select cust_name
, count(distinct case when status='DELIVERED' then order_id end) as DELIVERED
, count(distinct case when status='SUBMITTED' then order_id end) as SUBMITTED
, count(distinct case when status='CREATED' then order_id end) as CREATED
, count(distinct order_id) as tot_orders FROM
cust_orders
group by 1)
select cust_name, case
when tot_orders=DELIVERED then 'COMPLETED'
when tot_orders>DELIVERED AND DELIVERED>0 then 'PROGRESS'
when tot_orders=SUBMITTED then 'AWAITING PROGRESS'
ELSE 'AWAITING SUBMISSION' END AS FLAG
FROM BASE;
Easy Query...
SELECT distinct
least(source, destination) AS source,
greatest(source, destination) AS destination,
distance
FROM
src_dest_distance;
in question 3 what if the rows are not arranged car-wise, car1,car2,car3, then the lagging query would be wrong?
is that free coures or with paying
for first question, can we use SELECT DISTINCT* FROM cars
Hello! For the question on ungrouping data, I am unable to execute the recursive cte on SQL Server. I get an error 'The statement terminated. The maximum recursion 100 has been exhausted before statement completion.' Is there another way to do that? Thanks!
Actually, I found it. I was missing the termination condition total_count > 1 and that helped solve the above problem. 🙂
Hi Taufiq When will you start Python Bootcamp let me know pls
Sure Ill be announcing in LinkedIn probably next month
Hi. For the question 1..can someone lemee know if this solution works.
delete from cars where model _id in (select max(model_id) from cars group by model name, brand)
Please Explain query No. 6 in detail.
in question 8. what do you mean employees under Asha. under waht criteria?
why not use cross join for query 6?
select t2.team_name, t1.team_name as enemy_team
from teams as t1
cross join teams as t2
where t1.team_name not like t2.team_name
Alternative Q4 solution:
select distinct substr(src_desc, 1, instr(src_desc, '-', 1, 1) -1 ) as source,
substr(src_desc, instr(src_desc, '-', 1, 1) + 1 ) destination, distance
from (
select destination, source, distance,
case when source > destination then source || '-' || destination else destination || '-' || source end src_desc
from src_dest_distance
)
Below also easy alternatives : select least(startingpoint,endingpoint),greatest(startingpoint,endingpoint),distance from distance; OR select startingpoint, endingpoint, distance from (
select startingpoint, endingpoint, distance,row_number() over (partition by distance order by distance) as rn
from distance
)where rn=1;
Pls let us know when you are going to start Advance SQL Advance level 2024
I am new to SQL so sorry if I am asking anything silly. Will these queries also work same way for the SQL server also?
Yes
@@sravanipattanaik Thanks for replying!
The second solution for first question does not work in case of more than 2 duplicates.
11.00
Q3: if days >= 10 anywhere your code will fail as it's a string and will be ordered 1, 10, 2, 3, 4.
Q4: if not all source and destinations are duplicated you will remove records that only appear once. Use LEFT JOIN to ensure you're not deleting data.
Q5: recursion is very slow, newer SQLs will have a work-around:
SELECT id, item_name
FROM travel_items
CROSS JOIN LATERAL (SELECT EXPLODE(SEQUENCE(1, total_count)) AS num)
ORDER BY id, num
My ans to Q4
-- Assumes the duplicates are immediate to each other.
WITH dups AS (
SELECT *,CASE
WHEN LEAD(destination) OVER(ORDER BY distance) = source
THEN 1 ELSE 0
END AS dup_indic
FROM src_dest_distance
)
SELECT source,destination,distance FROM dups WHERE dup_indic = 1;
Do you have any sql course
I want to learn sql to you.
I have recordings from my previous bootcamp:
techtfq.graphy.com/courses/Recordings-from-SQL-Bootcamp-64f0e1cae4b014d5beccb78e
Query 4 My Solution :select * from src_dest_distance where destination
in (select max(source,destination) from src_dest_distance)
Query 2 can also be written as, a small change
Select *,
max(salary) over (partition by dept order by salary desc ) as highest_salary,
min(salary) over (partition by dept order by salary) as lowest_salary
from employee;
Could you help me I'm struggling joins
Query 4 -
select t2.source,t1.destination,t1.distance from src_dest_distance t1 left join src_dest_distance t2 on t1.source = t2.destination where t2.source IS NOT NULL