Session 35 - Subqueries in SQL | DSMP 2023

Session 35 - Subqueries in SQL | DSMP 2023
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
| Time stamp |
-----------------------
*Session Start*
00:00:00 - Session Start
00:03:43 - Agenda of this session

*Subquery*
00:05:35 - What is Subquery?
00:07:48 - Practical Example of Subquery
00:13:52 - Types of Subquery
00:20:15 - Where can subquery be used?
00:22:29 - Doubt clearance

*Independent subquery - Scalar Subquery*
00:24:10 - Details
00:24:53 - Example #1
00:32:25 - Example #2
00:34:26 - Example #3
00:36:13 - Example #4

*Independent subquery - Row Subquery*
00:41:17 - Details
00:43:28 - Example #1
00:46:35 - Example #2
00:52:54 - Example #3
00:57:46 - Doubt clearance

*Independent subquery - Table Subquery*
01:01:11 - Details
01:01:38 - Example #1
01:06:41 - Example #2
01:10:47 - Example #3
01:16:08 - Doubt Clearance

*Correlated Subquery*
01:17:37 - Details
01:18:26 - Example #1
01:27:59 - Example #2
01:38:06 - Doubt clearance

*Usage with SELECT*
01:39:36 - Details
01:40:21 - Example #1
01:43:40 - Example #2

*Usage with FROM*
01:47:05 - Example #1

*Usage with HAVING*
01:51:25 - Example #1

*Subquery in INSERT*
01:56:20 - Example #1

*Subquery in UPDATE*
02:01:40 - Example #1 (Get the query at the last section of this session)

*Subquery in DELETE*
02:08:32 - Example #1

*Doubt clearance*
02:11:45 - Doubt clearance
02:12:32 - Subquery in UPDATE (Resolved)
02:15:24 - Doubt clearance

------------------------------------------------------------------------------------------------------------------------------------------------

Practice Questions
1:53:57 Find all profitable orders
1:58:10 Find the customer who has placed the max number of orders
2:00:20 Which is the most profitable category
2:02:34 Which is the most profitable state
2:05:27 Find all categories with profits higher than 5000
2:06:40 End Session Doubts
#datanalytics #Database #DBMS #SQL #DML #sqlgroupby #groupby

[adityamaurya]

Hands down to best subqueries lecture on Indian youtube

[khemchand]

Very well oriented lecture. Amongst the best lectures available on YT in particular to SQL subqueries.
Very nicely oriented pedagogy to deliver lecture of such high quality.
A lot of thanks Nitish sir, for this amazing video. Couldn't have found a better person to explain these concepts in such a easiest way.

[nikhiljagtap]

If you cant load the dataset, here is what you need to do:

Upload to Excel's power query > clean it(if you want more cleaning)
> Load it to the sheet
> Save as "utf-8 CSV file"

Done, you can now upload into your MySql

[rajanchaurasiya]

Subqueries cannot manipulate their results internally, therefore ORDER BY clause cannot be added to a subquery. You can use an ORDER BY clause in the main SELECT statement (outer query) which will be the last clause.

[ujjwalgupta]

57:13 we can use group by with star and name to get the correct answer:

Select * from movies
Where(star, name) in (select star, name from movies where votes>25000
Group by star, name
Having avg(score)>8.5);

[himalmevada]

2:06:33 SUBQUERY WITH DELETE :
update loyal_customer t1 set money = (select (sum(amount) * 0.1) as "discount" from orders t2 where t1.user_id = t2.user_id group by user_id);

[sampritkundyana]

Upload to Excel's power query > clean it(if you want more cleaning)
> Load it to the sheet
> Save as "utf-8 CSV file"

DROP TABLE IF EXISTS MOVIES;
CREATE TABLE IF NOT EXISTS MOVIES
( NAME VARCHAR,
RATING VARCHAR,
GENRE VARCHAR,
YEAR INT,
RELEASED VARCHAR,
SCORE DECIMAL,
VOTES INT,
DIRECTOR VARCHAR,
WRITER VARCHAR,
STAR VARCHAR,
COUNTRY VARCHAR,
BUDGET INT,
GROSS BIGINT,
COMPANY VARCHAR,
RUNTIME INT
);

SELECT * FROM MOVIES;

[riteshbarthwal]

Thanks for this session Sir, I basically comes from non-technical background but have interest in data analysis. I can say this is the best session available on subqueries over a YouTube.
🙂

[BikashGuptaBG]

Good job bro, really helpful. Come up with more. Keep growing! You deserve even more than this.

[rahulrathore]

kisi ka delete statement mein error aaya that's like 'Error Code: 1093. You can't specify target table 'users' for update in FROM clause'

[abhishekbarua]

Thank you Bhai ..so much for helping 🙂😍😍😍

[animeshbasak]

-- find all movies of all those actors whose filmography
-- avg rating> 8.5( take 25000 votes as cutoff)

select * from movies where star in(
select star from movies
where votes>25000
group by star having avg(score)>8.5);

this code is not running
Error Code: 2013. Lost connection to MySQL server during query
why is it happening

[rameezkhan]

At 2:07:23 we can use co-related subqueries statement in Update Statement

UPDATE loyal_users lu SET money = (SELECT SUM(amount)*0.1 FROM orders o
where o.user_id = lu.user_id)

[sougaaat]

1:33:50 is throwing an error.
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zomato.T1.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[RajanKamble-wpqd]

While using delete statement its showing error code that you can't specify Target table users for update using from clause

[AlgoTribes]

epic request and epic reply on hair cut

[arslanjutt]

1:15:19 ager ham na lagain nested to kam to inner queries he kar dy gi

[ank_kumar]

corelated subquery k jo pehla ques hai wo iss tarah se v solve ho skta
select * from movies
where genre in (select genre from movies
group by genre
having score > avg(score));

haa ye insependent subquery se banaye hai... lekin result same de rha..
pls reply..

[nayansrivastava]

I cant find the cte lecture from your playlist..Did you upload it?

[anjan]

loyal_user dataset is not available in