1
00:00.880 --> 00:01.720
Hello all.

2
00:02.640 --> 00:07.360
So welcome back to my YouTube channel to learn SQL.

3
00:08.200 --> 00:13.880
So let's have continuation of our Phangan MNC SQL queries.

4
00:15.080 --> 00:17.760
So this is the first query.

5
00:17.800 --> 00:19.640
They've given us the delivery table.

6
00:20.520 --> 00:28.680
And in the delivery table they've given the delivery ID, customer ID order date and customer preference

7
00:28.680 --> 00:29.600
delivery date.

8
00:31.000 --> 00:33.640
Delivery ID is the primary key of this table.

9
00:34.440 --> 00:41.040
The table holds information about food delivery to customers that makes order ID some date and specify

10
00:41.040 --> 00:42.480
a preferred delivery date.

11
00:45.200 --> 00:48.120
On the same order date or after it.

12
00:48.760 --> 00:57.640
So if the customers prefer delivery date is same as the order date, then the order ID is cold or then

13
00:57.680 --> 00:59.320
the order is cold.

14
00:59.320 --> 01:00.450
called immediate order.

15
01:01.570 --> 01:05.210
Otherwise it is scheduled order.

16
01:06.010 --> 01:12.650
Brighter solution to find the percentage of immediate orders in the table rounded to two decimal places.

17
01:13.570 --> 01:16.650
The result format is in the following example.

18
01:18.410 --> 01:26.570
So here they have given us the delivery table in the delivery table, delivery ID, customer ID, order

19
01:26.570 --> 01:30.450
date and customer preferred delivery date column is given.

20
01:31.170 --> 01:38.170
So here we need to identify the orders whose order date and customer preferred delivery date.

21
01:38.170 --> 01:39.330
Both are same.

22
01:39.970 --> 01:48.730
So if you see over here for the delivery I did two delivery ID two order date and customer preferred

23
01:48.730 --> 01:49.690
delivery date.

24
01:49.730 --> 01:50.970
Both are same right?

25
01:51.690 --> 01:53.810
Same as for the delivery ID.

26
01:55.850 --> 02:04.010
Three And the rest of the delivery are ID, order rate and customer preference.

27
02:04.010 --> 02:06.210
Delivery date is not same.

28
02:06.810 --> 02:14.050
So total two delivery IDs as order date and customer preferred delivery date is same.

29
02:14.850 --> 02:18.490
Out of 123456.

30
02:22.330 --> 02:22.850
Right.

31
02:26.170 --> 02:28.490
So over here.

32
02:31.410 --> 02:37.090
I have created the table delivery table and inserted the data into the same.

33
02:37.690 --> 02:45.850
So let me found where order date and customer preference.

34
02:45.850 --> 02:49.330
Delivery date is same for how many delivery id.

35
02:49.730 --> 02:53.850
So it will be two delivery IDs has the same right.

36
02:54.410 --> 02:57.540
Now let me find the total number of delivery.

37
03:00.020 --> 03:04.300
Or you can say the total number of count in the delivery table.

38
03:04.780 --> 03:08.140
Now we need to create the two different cities.

39
03:08.140 --> 03:16.580
One city is for to count the number of deliveries where order, date and customer preferred delivery

40
03:16.620 --> 03:17.700
date are same.

41
03:18.580 --> 03:25.780
So that will be one city and for one city is for total number of delivery IDs in the entire delivery

42
03:25.780 --> 03:26.340
table.

43
03:27.100 --> 03:28.500
Now we need to divide.

44
03:30.700 --> 03:36.460
This first city value with the second city value.

45
03:37.100 --> 03:38.540
And then we have done it.

46
03:38.780 --> 03:40.020
We have used the cast.

47
03:42.820 --> 03:46.620
Cost function to convert into the decimal value.

48
03:46.860 --> 03:47.300
Right.

49
03:48.180 --> 03:51.660
So this two divided by six will be our answer.

50
03:52.500 --> 03:55.420
And then at the end we are doing multiply by 100.

51
03:55.420 --> 03:55.580
Ready.

52
03:55.980 --> 04:03.980
And again we are doing casting to two decimal places only, so answer will be 33.33.

53
04:05.500 --> 04:07.740
That will be our final answer.

54
04:08.500 --> 04:11.060
Now let me jump to the next question.

55
04:17.260 --> 04:24.020
So here they have given us the users table in the users table account and name column is given.

56
04:24.500 --> 04:26.700
Account is a primary key for this table.

57
04:26.740 --> 04:31.580
Each row of this table contains the account number of each user in the bank.

58
04:32.260 --> 04:38.700
There will be no two users having the same name in the table, and another table is transactions table.

59
04:39.220 --> 04:46.740
And again in the transactions table, account column is given which is common column between users table

60
04:46.740 --> 04:53.620
and transactions table and other columns are transaction ID account amount and transaction own.

61
04:56.140 --> 04:58.500
Transaction ID is the primary key for this table.

62
04:58.500 --> 05:04.380
In each row of this table contains all changes made to all accounts.

63
05:06.220 --> 05:07.820
Now what is the requirement?

64
05:08.140 --> 05:14.420
Write a solution to report the name and balance of the users with the balance higher than 10,000.

65
05:15.700 --> 05:22.500
The balance of an account is equal to the sum of amount of all transactions involving that account.

66
05:26.620 --> 05:35.060
So here you if you see the users table and transactions table, I have already created both the tables

67
05:35.060 --> 05:36.940
and inserted the data into the same.

68
05:39.180 --> 05:48.020
Now we need to find identify those accounts whose balance is greater than 10,000.

69
05:50.380 --> 05:52.540
So if you see over here.

70
05:55.750 --> 05:57.230
For, uh, allies.

71
05:57.230 --> 05:57.510
Right?

72
05:57.550 --> 05:59.470
For allies, 9001.

73
06:00.110 --> 06:02.550
So 14,000, -3000.

74
06:02.990 --> 06:07.870
It will be 11,000 right now for two, it is 1000.

75
06:08.470 --> 06:12.350
And for three, six plus six, 12,000, -4000.

76
06:12.790 --> 06:14.110
So it will be 8000.

77
06:14.150 --> 06:15.870
So allies will be our answer.

78
06:15.870 --> 06:16.350
Right.

79
06:17.750 --> 06:20.510
Which balance is greater than 10,000.

80
06:22.910 --> 06:25.470
So how we can identify this thing.

81
06:27.030 --> 06:30.670
So I have created both the tables and inserted the data into the same.

82
06:31.910 --> 06:38.510
Now we need to do join between users table and transactions table.

83
06:38.510 --> 06:44.230
And we need to do some of amount with the group by name.

84
06:45.670 --> 06:52.960
And now whenever the sum of amount is greater than 10,000 we need to retrieve that Value, right?

85
06:55.560 --> 07:02.920
So if you see over here only for allies, 11,000 is given that is greater than 10,000.

86
07:03.520 --> 07:06.280
So this should be our output right.

87
07:08.360 --> 07:09.600
So thank you so much.

88
07:09.600 --> 07:11.920
Let me jump to the next question.

89
07:14.720 --> 07:18.600
Here they have given us the actor director table.

90
07:19.320 --> 07:23.960
And in the in that actor ID director ID and timestamp column is given.

91
07:24.840 --> 07:27.600
Timestamp is the primary key for this table.

92
07:28.480 --> 07:28.880
Right?

93
07:28.880 --> 07:30.960
Solution to find all the pairs.

94
07:31.320 --> 07:39.080
All the pairs means actor id and director ID combination where the actor has cooperate, cooperated

95
07:39.080 --> 07:41.320
with the director at least three times.

96
07:42.240 --> 07:44.560
Return the result table in any order.

97
07:45.440 --> 07:48.400
The result format is in the following example.

98
07:50.960 --> 07:53.440
So here, actor director table is given.

99
07:54.840 --> 08:02.760
Now we need to identify the combination of actor ID and director ID is repeating at least three times.

100
08:03.000 --> 08:07.000
So if you see over here, here 111 is given.

101
08:07.000 --> 08:07.440
Right.

102
08:08.720 --> 08:10.440
Then again 111 is given.

103
08:11.520 --> 08:13.200
And again 111 is given.

104
08:13.480 --> 08:21.760
So actor ID one and director id one combination is repeating three times, but there is no other place.

105
08:22.280 --> 08:27.000
Actor ID and director ID combination is repeating three times or more.

106
08:27.800 --> 08:30.040
So this will be our output, right?

107
08:31.040 --> 08:32.880
So I have created the table.

108
08:33.360 --> 08:36.640
Now this is the very simple guys.

109
08:37.360 --> 08:43.280
We need to use group by group by an indie group by actor and director ID.

110
08:43.760 --> 08:48.720
And we need to count where this combination is repeating three or more times.

111
08:49.360 --> 08:51.480
So whenever we are using the group by.

112
08:53.840 --> 09:03.440
Clause at that time, we need to use the having clause with the group by to find the combination is

113
09:03.440 --> 09:09.440
repeating how many times or more than three times, or more than two times like that.

114
09:10.000 --> 09:13.400
So here we our requirement is three or more times.

115
09:13.400 --> 09:17.520
So I have used having count of one greater than equal to three.

116
09:19.440 --> 09:21.800
So this will be our output right.

117
09:25.280 --> 09:28.760
Now let me jump to the next question.

118
09:32.000 --> 09:35.080
So this is the cust in this customers table.

119
09:35.080 --> 09:38.240
They have given us customer ID name and country column is given.

120
09:38.880 --> 09:41.440
Customer ID is a primary key for this table.

121
09:42.000 --> 09:45.360
This table contains information about the customers in the company.

122
09:46.600 --> 09:48.970
And another table is given is products product stable.

123
09:49.770 --> 09:54.010
So in the product stable, product ID description and price column is given.

124
09:54.530 --> 09:57.290
Product ID is the primary key for this table.

125
09:58.330 --> 10:00.850
And one more table is given in the orders table.

126
10:00.850 --> 10:06.250
In the orders table, order ID, customer ID, product ID, order rate and quantity is given.

127
10:06.570 --> 10:10.930
So customer ID is common between customers table and orders.

128
10:10.930 --> 10:16.410
Table and product ID is common between product table and orders table over here.

129
10:20.250 --> 10:28.970
So write a SQL query to report the customer ID in the customer name of customers who have spent at least

130
10:29.010 --> 10:35.330
$1 in each month of the June and July 2020.

131
10:36.050 --> 10:38.290
Return the result table in any order.

132
10:39.330 --> 10:40.010
So here.

133
10:42.370 --> 10:47.490
I have created all these three table customer table, products Table and Orders table and inserted the

134
10:47.490 --> 10:48.530
data into the same.

135
10:49.170 --> 10:49.810
Now here.

136
10:53.730 --> 10:54.530
We need to.

137
10:56.770 --> 11:01.010
Do join between customers table, orders table and products table.

138
11:01.010 --> 11:03.770
And also we need to identify.

139
11:07.210 --> 11:15.850
The spent of amount in the year 2020 and month of July as well as month of June.

140
11:16.370 --> 11:17.410
So month of July.

141
11:17.450 --> 11:27.650
I have encapsulated into one city and then I have multiplied the price multiplied by quantity, and

142
11:27.650 --> 11:38.610
then sum of this value greater than or equal to 100, right in the month of July and year of 2020.

143
11:38.650 --> 11:39.050
Right.

144
11:39.450 --> 11:51.020
So if you see over here customer ID one name is Winston Has done 100 amount of total spend in the month

145
11:51.020 --> 11:53.940
of July right now.

146
11:53.980 --> 11:54.700
Same thing.

147
11:54.700 --> 11:56.780
We need to check for the month of June.

148
11:58.900 --> 12:07.980
So again, if you see over here, month of June is three total customers has done more than $100 of

149
12:08.540 --> 12:09.420
expenses.

150
12:10.460 --> 12:12.380
Now what we need to do.

151
12:14.740 --> 12:15.940
We need to do the.

152
12:19.180 --> 12:19.700
I have.

153
12:25.420 --> 12:27.380
Let me jump to the question again.

154
12:29.100 --> 12:35.500
Write an SQL query to report the customer ID and customer name of the customers who have spent at least

155
12:35.540 --> 12:39.260
$100 in each month of June and July.

156
12:40.260 --> 12:45.710
So we need common data between both of these tables, right?

157
12:46.710 --> 12:49.510
So from the July, if you see over here.

158
12:53.190 --> 12:56.670
Only customer ID one has spent this much of amount.

159
12:57.150 --> 13:07.870
But now if you see for the result set of the June month then customer ID one, two and three, all these

160
13:07.870 --> 13:10.910
three has spent 100 or more dollars, right?

161
13:11.630 --> 13:19.950
Now if you do the join between these two then again customer ID one and Winston will come because he

162
13:19.990 --> 13:29.590
is only the common customers who have spent more than $100 in June as well as July month.

163
13:29.950 --> 13:30.470
Right?

164
13:31.470 --> 13:33.590
So this will be our output.

165
13:34.550 --> 13:35.070
Now.

166
13:38.990 --> 13:41.150
Let me jump to the next question.

167
13:42.630 --> 13:49.870
Here they have given us the daily sales tables, date ID, make ID, lead ID and partner ID right?

168
13:50.630 --> 13:52.510
There is no primary key for this table.

169
13:52.510 --> 13:54.070
It may contain duplicates.

170
13:54.790 --> 13:59.110
This table contains the date and the name of the product sold, and the IDs of the lead and partner

171
13:59.110 --> 14:01.030
ID was sold to.

172
14:01.910 --> 14:09.870
The name consists of only lowercase English letters for each date, id and make name.

173
14:10.430 --> 14:16.470
Find the number of distinct lead IDs and distinct partner IDs.

174
14:19.390 --> 14:27.270
So over here for the Toyota, how many distinct unique lead IDs are there?

175
14:27.310 --> 14:29.990
One, two two LED IDs are there.

176
14:29.990 --> 14:30.430
Right.

177
14:31.790 --> 14:36.070
For the Toyota on date eight and again for the date seven.

178
14:37.670 --> 14:41.270
If you see there is only one distinct lead ID are there.

179
14:41.960 --> 14:46.360
Same as for the partner ID for the Toyota one, two and three.

180
14:46.880 --> 14:52.320
Three unique partners are there one, zero and two and four and four seven.

181
14:52.720 --> 14:55.080
It is two distinct unique partners are there.

182
14:56.920 --> 15:00.000
So I have created the table and inserted the data into the same.

183
15:00.360 --> 15:09.160
Here we need to do use date ID as well as make name and then count of distinct lead ID and count of

184
15:09.200 --> 15:10.480
distinct partner ID.

185
15:10.480 --> 15:19.400
And we need to do group by date ID and make ID so this will be our result set right.

186
15:19.440 --> 15:21.400
So thank you so much everyone.

187
15:23.160 --> 15:27.720
We will come again with the next few questions in the next video.

188
15:29.480 --> 15:37.600
Please kindly do like if you really like this video and do comment and subscribe and share as much as

189
15:37.600 --> 15:38.280
possible.

190
15:39.000 --> 15:39.800
Thank you.