1
00:00:00,750 --> 00:00:02,670
Some queries with insert statement.

2
00:00:03,890 --> 00:00:10,520
All right, so let's have a look at the sub queries that can be used with insert statements.

3
00:00:11,800 --> 00:00:19,570
So the insert statement, it uses the data returned from the sub query and then inserts it into another

4
00:00:19,570 --> 00:00:20,010
table.

5
00:00:21,180 --> 00:00:27,540
The selected data in the query can be modified with any of the characters, dates or no functions.

6
00:00:28,980 --> 00:00:34,050
The data coming from the subquery will be used in the insert statement.

7
00:00:36,200 --> 00:00:42,650
So here I have created a state province test table in the person schema.

8
00:00:43,720 --> 00:00:46,340
So let's retrieve all the data from this table.

9
00:00:51,600 --> 00:00:58,080
In this example, we are going to insert the data from the state province table to the state province

10
00:00:58,080 --> 00:01:00,390
test table with the sub query.

11
00:01:07,480 --> 00:01:11,050
And the query will retrieve state province code.

12
00:01:13,360 --> 00:01:14,170
Country code.

13
00:01:15,850 --> 00:01:26,110
Is only state province, flag name, territory, ID, modified date and send the data in these columns

14
00:01:26,230 --> 00:01:32,710
to the insert statement, then the insert statement will insert these records into the state province

15
00:01:32,710 --> 00:01:33,370
test table.

16
00:01:35,260 --> 00:01:38,770
Now, he didn't specify this state province I.D..

17
00:01:39,680 --> 00:01:43,130
Because this field has an auto increment feature.

18
00:01:44,350 --> 00:01:46,930
And we can see this in the table design window.

19
00:01:51,960 --> 00:01:58,620
So just click on the state province I.D. and then find the identity specification from the column properties.

20
00:01:59,620 --> 00:02:02,140
Now, the value of it is yes.

21
00:02:03,940 --> 00:02:10,000
And that means this property is active and this field has auto increment one by one.

22
00:02:11,910 --> 00:02:17,440
That means because of this feature, when a new record is inserted into the table, this value will

23
00:02:17,440 --> 00:02:19,020
automatically increase, right?

24
00:02:19,980 --> 00:02:25,680
So we will not be allowed to specify a value for this field in the insert statement, even if we wanted

25
00:02:25,680 --> 00:02:25,890
to.

26
00:02:28,980 --> 00:02:37,680
And that, my friends, is why we didn't specify these state province Widefield, so before we execute

27
00:02:37,680 --> 00:02:38,490
this query.

28
00:02:39,930 --> 00:02:42,720
Let's retrieve all the data from the state province table.

29
00:02:44,880 --> 00:02:47,900
And here are 181 rows in this table.

30
00:02:49,360 --> 00:02:56,530
And all these rows will be inserted into the state province test table as soon as the query of the insert

31
00:02:56,530 --> 00:02:57,850
statement has been executed.

32
00:03:00,730 --> 00:03:06,480
And it looks like it's been completed successfully and 181 rows have been affected.

33
00:03:08,130 --> 00:03:11,970
Now, let's retrieve data from the state province test.

34
00:03:14,020 --> 00:03:20,680
Great, all the data has been inserted from the state province table into the state province test.

35
00:03:21,070 --> 00:03:21,670
Well done.

36
00:03:23,450 --> 00:03:25,910
Some queries with updates statement.

37
00:03:27,660 --> 00:03:33,540
Now, the subquery can be used in conjunction with the update statement, either single or multiple

38
00:03:33,540 --> 00:03:38,610
columns in a table can be updated when using a sub query with the update statement.

39
00:03:40,840 --> 00:03:46,990
So in this example, we're going to update the territory idea of Canada in the state province test table

40
00:03:47,350 --> 00:03:52,090
with the sub query and the Territory ID will be set to 99.

41
00:03:58,630 --> 00:04:06,160
Now we must specify a condition to update a specific row, otherwise all tables will be updated.

42
00:04:09,200 --> 00:04:11,630
The country region code equals CI A.

43
00:04:12,960 --> 00:04:18,450
And if we execute the query, we will update the data that we want to, but in this example, we're

44
00:04:18,450 --> 00:04:22,230
going to update it with some query and a different table.

45
00:04:22,920 --> 00:04:29,130
So let's use a sub query instead of specifying a value for country region code.

46
00:04:32,500 --> 00:04:39,280
And the outer query is asking for their country region code because of this reason, the sub query must

47
00:04:39,280 --> 00:04:44,050
return the country region code from the state province table.

48
00:04:44,260 --> 00:04:44,610
Right.

49
00:04:45,700 --> 00:04:50,320
And the condition must be country region code equals CIA.

50
00:04:52,860 --> 00:04:56,010
Now, make sure before executing the query.

51
00:04:56,940 --> 00:05:02,670
Because if we exclude the subquery, only the code seet is going to be listed, right?

52
00:05:03,480 --> 00:05:03,810
All right.

53
00:05:03,820 --> 00:05:04,560
So that's good.

54
00:05:04,710 --> 00:05:06,090
There are 13 rows.

55
00:05:07,940 --> 00:05:11,750
And it looks like it is ready to execute, so let's.

56
00:05:13,160 --> 00:05:16,010
Yeah, let's execute the query to update the data.

57
00:05:18,550 --> 00:05:21,310
And here there are 13 rows that have been affected.

58
00:05:24,450 --> 00:05:30,990
Now, if we select all data from the state province test table, we're going to see that all data within

59
00:05:30,990 --> 00:05:34,230
the CIA code has been updated to 99.

60
00:05:39,170 --> 00:05:41,660
Sub queries with the lead statement.

61
00:05:43,210 --> 00:05:47,830
So the subquery can be used in conjunction with a delete statement, just like with any of the other

62
00:05:47,830 --> 00:05:50,260
statements that we've mentioned before.

63
00:05:51,450 --> 00:05:57,900
The sub query of the leading data from the table is very similar to the sub query to update data from

64
00:05:57,900 --> 00:05:58,350
the table.

65
00:06:00,720 --> 00:06:06,960
So in this example, we're going to delete data from the state province test table with a sub query.

66
00:06:09,910 --> 00:06:16,380
If a where clause is not specified for deleting statement, all data from the table will be deleted.

67
00:06:16,390 --> 00:06:20,530
So just make sure to clearly specify the data that you want to delete.

68
00:06:23,590 --> 00:06:29,800
And we will delete a specific row and these are going to be in the sub queries.

69
00:06:31,030 --> 00:06:38,200
We will get data from the state province table, and the commission part is very important because each

70
00:06:38,200 --> 00:06:40,510
row in the same query will be deleted, so.

71
00:06:41,710 --> 00:06:47,800
Yeah, we're going to delete rows, that is country region code equals s.A.

72
00:06:51,120 --> 00:06:54,540
And let's check the subquery, see if it returns Rosett, you want.

73
00:06:55,640 --> 00:07:01,310
Execute the subquery and sure, so here are the 13 rows that have been listed.

74
00:07:02,580 --> 00:07:03,270
So that's good.

75
00:07:03,330 --> 00:07:04,600
Everything looks OK, right?

76
00:07:05,990 --> 00:07:10,550
So now we can execute the query and delete these rows.

77
00:07:11,790 --> 00:07:13,830
The query has been successfully executed.

78
00:07:16,920 --> 00:07:20,580
And now if we select all data from the state province test.

79
00:07:21,540 --> 00:07:25,140
You'll notice that the specified 13 rows have been deleted.