1 00:00:00,400 --> 00:00:07,500 Well, this time we are going to talk about database, but queries. 2 00:00:07,500 --> 00:00:13,439 One important thing about queries is that you can have queries of different types. 3 00:00:13,439 --> 00:00:23,600 You can have queries that do something, such as delete some records in a table, or update 4 00:00:23,600 --> 00:00:34,200 some dates in a table, some registers and records, but in this level, the only type 5 00:00:34,200 --> 00:00:41,460 of queries that we are going to study is selection queries. 6 00:00:41,460 --> 00:00:45,780 In any case, selection queries can be also complicated. 7 00:00:45,780 --> 00:00:55,299 First of all, as I click in Queries, we have three possibilities to create a query. 8 00:00:55,299 --> 00:01:04,799 One is in the side-by view, another is using a system, and finally using SQL. 9 00:01:04,799 --> 00:01:05,799 What is SQL? 10 00:01:05,799 --> 00:01:16,299 Well, SQL is a language that lets you to manage all the database. 11 00:01:16,299 --> 00:01:22,219 You have a database, you can program this database using this type of language, but 12 00:01:22,219 --> 00:01:27,120 But obviously it's a programming language so it's complicated, it's not easy. 13 00:01:27,120 --> 00:01:32,900 And in this level it's not necessary that you reach this level. 14 00:01:32,900 --> 00:01:41,879 You can do it in the first year of your degree or even in the university, but not at this 15 00:01:41,879 --> 00:01:42,879 level. 16 00:01:42,879 --> 00:01:50,819 But it's enough that you know that they exist, it exists, it's enough now. 17 00:01:50,819 --> 00:01:55,459 Well in any case we are going to begin using the assistant. 18 00:01:55,459 --> 00:02:05,109 So click in the assistant, and here you have, it's a formular indeed, it's very easy to 19 00:02:05,109 --> 00:02:06,109 use. 20 00:02:06,109 --> 00:02:13,509 Firstly, what you must choose is which table you are going to do the query. 21 00:02:13,509 --> 00:02:15,490 For example, clients. 22 00:02:15,490 --> 00:02:20,750 So you have all the fields that client has. 23 00:02:20,750 --> 00:02:29,430 You must choose, you can choose all of them if you want, or just a few of them, it's your 24 00:02:29,430 --> 00:02:30,430 choice obviously. 25 00:02:30,430 --> 00:02:45,080 Well, in this case, for me, it's enough with a client code, Enterprise, for example, Townhouse 26 00:02:45,080 --> 00:02:49,629 City. 27 00:02:49,629 --> 00:03:05,520 Then click on next. Here you can order, taking into consideration the order in one file, for example, this file. 28 00:03:05,520 --> 00:03:16,520 If you order in this file, the record has been ordered in this file. 29 00:03:16,520 --> 00:03:21,520 or ascending or descending, in ascending or descending mode. 30 00:03:21,520 --> 00:03:33,889 Then, well, sometimes, very often, we usually add a criteria to do the selection of the record. 31 00:03:33,889 --> 00:03:45,889 So, for example, the first thing that you should do is select in which file do you want to add a criteria. 32 00:03:45,889 --> 00:03:50,889 For example, town. 33 00:03:50,889 --> 00:03:59,889 And then here you can add a value for this town, for this field, 34 00:03:59,889 --> 00:04:04,889 that lets you to create the selection, for example, Madrid. 35 00:04:04,889 --> 00:04:15,919 Come finish. 36 00:04:15,919 --> 00:04:18,920 Finish your query. 37 00:04:18,920 --> 00:04:26,990 And here is the query, just only two records. 38 00:04:26,990 --> 00:04:27,990 But what happened? 39 00:04:27,990 --> 00:04:32,870 It's more or less the same as you use a filter to do this. 40 00:04:32,870 --> 00:04:41,529 It's not so important, it's not, indeed it's not, it's nothing that the query is added 41 00:04:41,529 --> 00:04:45,769 to what we have already can do. 42 00:04:45,769 --> 00:04:55,649 Well we are going to edit it and you can see the design view. 43 00:04:55,649 --> 00:05:03,589 What happens is that if you look at the design view you can add more than one criteria. 44 00:05:03,589 --> 00:05:14,230 And you can also add this criteria using an or all operation, logical operation. 45 00:05:14,230 --> 00:05:24,470 So it is true that with this first query you haven't done so much, but it is also true 46 00:05:24,470 --> 00:05:29,019 that it is a very very easy query. 47 00:05:29,019 --> 00:05:40,000 So what I want to prove, for example, if I want to do a query in which I have involved 48 00:05:40,000 --> 00:05:46,939 More than one table is what you must do because if you use the assistant, it's not possible. 49 00:05:46,939 --> 00:05:48,480 There's only one table. 50 00:05:48,480 --> 00:05:52,740 You can join or you can use more than one table. 51 00:05:52,740 --> 00:06:01,800 So I have to use another type of query also, and obviously, what happened? 52 00:06:01,800 --> 00:06:07,439 Well, we are going to use design view query. 53 00:06:07,439 --> 00:06:16,639 view query as you can see you have all the tables so for example you can add it 54 00:06:16,639 --> 00:06:48,860 these two tables and as you can see here you can add these two tables you can do 55 00:06:48,860 --> 00:07:02,480 a query with two tables if they are related. So maybe it's important to 56 00:07:02,480 --> 00:07:11,000 revise the context of relations before doing any query in which you 57 00:07:11,000 --> 00:07:21,290 involve more than one table. So we are going to leave this query and we are 58 00:07:21,290 --> 00:07:29,910 going to go back to tables. Well, if you click in tools you have relationships. 59 00:07:29,910 --> 00:07:41,000 Well, here in relationships you can see that we have a line that correspond the field 60 00:07:41,000 --> 00:07:48,319 that has in common to table. For example, table clients and table pedidos have in 61 00:07:48,319 --> 00:07:59,180 common a client code and table pedidos and table products have in common a client code and you have 62 00:07:59,180 --> 00:08:08,509 a line what does it mean well we are going to explain it I'm going to delete firstly and I'm 63 00:08:08,509 --> 00:08:25,889 going to explain what is this from the beginning what happened is you have tables that have some 64 00:08:25,889 --> 00:08:38,909 field in common you can relate it this table. It means that if you have 65 00:08:38,909 --> 00:08:45,210 one table you can know the fields that correspond in other tables and are 66 00:08:45,210 --> 00:08:57,480 more or less the same. So for example you are in tables and now I'm going to 67 00:08:57,480 --> 00:09:11,370 select in the rubber tools. Tools, relationship. Clients, this one, and also 68 00:09:11,370 --> 00:09:52,059 products. So we have the three tables. What happened? Clients and this other 69 00:09:52,059 --> 00:10:08,740 table have one field in common which is this one, client code. So I can 70 00:10:08,740 --> 00:10:34,789 You can create a new relation between clients and this other table in client code. 71 00:10:34,789 --> 00:10:48,529 This type of relationship means that each register can have a relationship with so many 72 00:10:48,529 --> 00:10:51,370 registers in the other table. 73 00:10:51,370 --> 00:11:08,279 You can also create another relationship between this table, which is pedidos and products. 74 00:11:08,279 --> 00:11:09,740 What is products? 75 00:11:09,740 --> 00:11:22,940 In products you have the product code, and in pedidos you have the product code. 76 00:11:22,940 --> 00:11:28,539 So you also have the relation between these tables. 77 00:11:28,539 --> 00:11:38,200 So now we have relationships between different tables and it lets you to create missing queries 78 00:11:38,200 --> 00:11:51,100 is the advantage because obviously it doesn't matter that you have to create any relationship. 79 00:11:51,100 --> 00:12:06,950 If you open clients, you have the same. 80 00:12:06,950 --> 00:12:15,070 If it were access, you can see the difference between the tables in which you have any relationship 81 00:12:15,070 --> 00:12:17,509 and you have relationship. 82 00:12:17,509 --> 00:12:25,750 But in this way, sorry, but it's not possible to show anything that shows the relationship. 83 00:12:25,750 --> 00:12:33,769 You can't see the relationship, it's nothing, it's any action in which you can show it. 84 00:12:33,769 --> 00:12:46,139 But it is true that when you create a query in DesignVue and you, for example, select 85 00:12:46,139 --> 00:12:57,940 these two tables, they show you the relationship between these two tables. 86 00:12:57,940 --> 00:13:23,049 So now, we are going to choose this code, also for example the product code, also for 87 00:13:23,049 --> 00:13:42,000 example the price, and maybe for example this one. 88 00:13:42,000 --> 00:13:51,840 If you don't give any value to do the selection, you have a selection indeed, because you select 89 00:13:51,840 --> 00:13:55,340 some fields and you miss two tables. 90 00:13:55,340 --> 00:14:14,799 So even if you added any criteria or not, you have done a query selection. 91 00:14:14,799 --> 00:14:19,570 And the result is this. 92 00:14:19,570 --> 00:14:27,690 But obviously, what is interesting, if you added some criteria, for example, we are going 93 00:14:27,690 --> 00:14:44,080 to add it that just only the register in which the price is more than or less than 15. 94 00:14:44,080 --> 00:14:58,320 So we edit this one, and here our criteria is less than 15, yes? 95 00:14:58,320 --> 00:15:16,600 So if I save this as selection tool, the result is this. 96 00:15:16,600 --> 00:15:25,620 So you have selected just only the registers in which the price is less than 15. 97 00:15:25,620 --> 00:15:36,940 You for example now can do a different selection because you can select that at the same time 98 00:15:36,940 --> 00:15:44,909 this price is less than 15 and you have sent it. 99 00:15:44,909 --> 00:16:08,960 So in this case, if here in the same file you added null, because this field is binariful, 100 00:16:08,960 --> 00:16:20,120 so it can only have two values, null or not null, null means that you have send it. 101 00:16:20,120 --> 00:16:40,070 So now I can save as query three, and you have the selection. 102 00:16:40,070 --> 00:16:48,169 In this case, the condition of the value is at the same time. 103 00:16:48,169 --> 00:17:28,940 If you don't do it in this way, what happens is it's the same as this, sorry. 104 00:17:28,940 --> 00:17:38,119 So it's better if you do the 10S one, I don't know, maybe it's not good, yes it's good. 105 00:17:38,119 --> 00:17:53,930 So it's the same, it's the same as do in this way or do in another way, in this other way. 106 00:17:53,930 --> 00:18:14,980 Instead here, u is empty or null, it means that this condition of this condition. 107 00:18:14,980 --> 00:18:37,059 So if you save as selection 3, again, what you save is more or less the same as selection 108 00:18:37,059 --> 00:18:39,819 2. 109 00:18:39,819 --> 00:18:49,680 This and this are the same. 110 00:18:49,680 --> 00:19:27,250 So as a consequence of this, but this is different, sorry, this query is different, yes? 111 00:19:27,250 --> 00:19:34,750 So I think it's clear that the differences between one of those. 112 00:19:34,750 --> 00:20:02,220 For example, another one, clients and products, no products, no. 113 00:20:02,220 --> 00:20:32,769 So for example, client code, town, for example here, town, enterprise, units, and send it, 114 00:20:32,769 --> 00:20:58,829 We can, for example, choose less than 10, and locate it, for example, Madrid. 115 00:20:58,829 --> 00:21:15,539 If you do it this way, both criteria must be at the same time. 116 00:21:15,539 --> 00:21:26,720 So, click in 45, and there's nothing. 117 00:21:26,720 --> 00:21:27,720 Nothing. 118 00:21:27,720 --> 00:21:28,720 Why? 119 00:21:28,720 --> 00:22:07,660 possible? Well, maybe we're going to edit it. We're going to delete this one. So you have this register 120 00:22:09,339 --> 00:22:17,420 less units than 10 and there is anything of these from Madrid, so it's logical. 121 00:22:18,299 --> 00:22:25,420 We're going to sell us for example Alicante or Seville or Seville. Alicante. La Coruña. 122 00:22:25,420 --> 00:22:34,630 We're going to select La Coruña. 123 00:22:34,630 --> 00:22:35,630 It's here. 124 00:22:35,630 --> 00:22:54,160 You select. 125 00:22:54,160 --> 00:23:00,269 The result of this query is this. 126 00:23:00,269 --> 00:23:02,869 It's okay. 127 00:23:02,869 --> 00:23:12,589 If you just choose with OR, you have the same, the same of the other query. 128 00:23:12,589 --> 00:23:18,230 So that's the reason why sometimes it's not necessary OR. 129 00:23:18,230 --> 00:23:29,410 Or it's not a good logical operation to create a good selection query. 130 00:23:29,410 --> 00:23:36,990 But yes, because it's another filter, it's about a filter. 131 00:23:36,990 --> 00:23:43,430 Well it's more or less what you must do about queries.