1 00:00:00,840 --> 00:00:01,920 Hello there. 2 00:00:01,950 --> 00:00:04,660 Welcome to this video in this video. 3 00:00:04,680 --> 00:00:14,030 I will be introducing you to what views are in Oracle and explain some of the reasons why they are used. 4 00:00:14,250 --> 00:00:18,510 I will also create an example view. 5 00:00:18,510 --> 00:00:20,300 What is his view. 6 00:00:20,700 --> 00:00:31,860 Basically if you is stored secure query or sequal query so we can be a combination of one or more tables. 7 00:00:31,860 --> 00:00:44,700 So a view is a logical representation of one or more tables a view derives data from tables and tables 8 00:00:44,790 --> 00:00:54,590 unknown as base tables so a base table can also be other tables or other views. 9 00:00:54,640 --> 00:01:03,820 Any operations that are performed on views actually do affect the base tables as well. 10 00:01:03,870 --> 00:01:13,800 One of the reasons why we use views we use them to tailor the presentation of data to different type 11 00:01:13,800 --> 00:01:15,270 of users. 12 00:01:15,720 --> 00:01:19,170 So you may have several users within the organizations. 13 00:01:19,200 --> 00:01:28,800 You may want them to see certain part of the data in a table or tables so you can use to section out 14 00:01:29,340 --> 00:01:37,200 the information you want to portray to different type of users views also provide an additional level 15 00:01:37,950 --> 00:01:39,960 of security to the tables. 16 00:01:39,960 --> 00:01:49,770 By restricting access to sit in rows of columns in a table you may have a table that have salary information 17 00:01:50,330 --> 00:02:00,030 that you may not want certain users to see so you can use it view to exclude the salary part of the 18 00:02:00,030 --> 00:02:08,570 information on like a table view is not a located storage space. 19 00:02:08,790 --> 00:02:12,300 Also in view does not contain data. 20 00:02:12,360 --> 00:02:24,530 All it does is display the data in view is also stored in the Oracle database data dictionary. 21 00:02:24,750 --> 00:02:33,510 I have turned toward for a call edit open so we are going to create an example view together. 22 00:02:33,750 --> 00:02:41,400 I am gonna have a look at the Employees Table and see what it looks like at the moment and then from 23 00:02:41,400 --> 00:02:45,650 the date I remember I am going to create a view. 24 00:02:45,870 --> 00:02:47,120 So lets have a look. 25 00:02:47,190 --> 00:02:51,010 The employees table with this select statement. 26 00:02:51,540 --> 00:02:52,650 So let 27 00:02:57,240 --> 00:03:02,010 start start means bring back everything from 28 00:03:04,780 --> 00:03:07,230 employees. 29 00:03:07,380 --> 00:03:12,320 Employees mean the meaning the table and then a call on. 30 00:03:12,330 --> 00:03:19,530 Always remember to end your statements with a semicolon because he separates it from other statements 31 00:03:19,740 --> 00:03:27,430 are right so the school query here return is select which means bring back the start. 32 00:03:27,450 --> 00:03:34,300 Means all data from employees employees is to table. 33 00:03:34,320 --> 00:03:36,000 So if that execute this 34 00:03:39,140 --> 00:03:45,210 source give me a hundred and seven rolls from this table here. 35 00:03:45,420 --> 00:03:48,810 These are the different columns you got. 36 00:03:48,970 --> 00:03:49,800 Employee. 37 00:03:49,810 --> 00:03:53,830 The first name last name got so many. 38 00:03:53,970 --> 00:04:02,310 If you want to find out the structure of the table what this table looks like he can use to describe 39 00:04:02,370 --> 00:04:07,500 common to you describe on the table describe 40 00:04:10,310 --> 00:04:15,140 employees so miquel So this would give it a strong shot. 41 00:04:15,210 --> 00:04:16,630 I run that. 42 00:04:22,110 --> 00:04:24,400 Think about it. 43 00:04:24,400 --> 00:04:27,230 All right so this is the structure of the table. 44 00:04:27,230 --> 00:04:34,020 These are the columns columns of the table and in different aspects of the table. 45 00:04:34,190 --> 00:04:39,450 So if ever you want to find out what the table structure looks like or check the columns you can use 46 00:04:39,450 --> 00:04:42,030 the Describe command to do that. 47 00:04:42,260 --> 00:04:46,830 Okay let's go back to our first query around that again. 48 00:04:46,850 --> 00:04:52,260 All right so I'm going to create a view from this table. 49 00:04:52,290 --> 00:05:01,920 I'm going to restrict some of the data and won't exclude the salary aspect of it be comissioner percent. 50 00:05:01,930 --> 00:05:12,260 And if you or that just to make certain data available only to certain people so to do that you start 51 00:05:12,760 --> 00:05:22,040 to create comite for you to be able to view the account you are using needs to have b Create permission 52 00:05:22,490 --> 00:05:23,440 to do that. 53 00:05:23,690 --> 00:05:27,850 So this account has the permission to do that. 54 00:05:27,850 --> 00:05:32,150 The account I'm using which is here char count. 55 00:05:32,260 --> 00:05:40,250 If you all who haven't got that you need to grant that account permission to do that using the grant 56 00:05:40,370 --> 00:05:41,240 permission. 57 00:05:41,540 --> 00:05:45,820 So I'm just going to create a view it creates 58 00:05:49,590 --> 00:05:52,460 with a word create and then view 59 00:05:55,220 --> 00:05:56,540 and then the view name 60 00:06:00,050 --> 00:06:05,900 the view may well be OnStar has as mean. 61 00:06:05,930 --> 00:06:09,100 This is where you're going to take the information from. 62 00:06:09,110 --> 00:06:12,530 I need to specify where the information is coming from. 63 00:06:12,590 --> 00:06:17,150 So from this so take what's left. 64 00:06:17,450 --> 00:06:22,750 So I'm trying to get the data I want to use for the view. 65 00:06:23,240 --> 00:06:30,710 And I do that using the select statements for selecting employees Heidi. 66 00:06:36,460 --> 00:06:40,310 And then come up you have to come out to separate the columns. 67 00:06:40,540 --> 00:06:42,770 You don't have to use upper case. 68 00:06:42,790 --> 00:06:48,220 I'm just using up a case for simplicity so you can always use lower case if you want to. 69 00:06:48,790 --> 00:06:55,760 And then last name. 70 00:07:02,420 --> 00:07:04,500 And what else do I need. 71 00:07:04,680 --> 00:07:08,440 Limited email address. 72 00:07:14,120 --> 00:07:20,330 Let's say hi a date. 73 00:07:29,590 --> 00:07:30,740 T. 74 00:07:31,330 --> 00:07:36,010 Department Heidi Hyatt date. 75 00:07:36,010 --> 00:07:38,300 No I think I'll leave it like that. 76 00:07:38,930 --> 00:07:40,800 Ah lamb and a phone number. 77 00:07:40,860 --> 00:07:42,090 I mean to phone 78 00:07:45,020 --> 00:07:45,750 the score. 79 00:07:45,780 --> 00:07:56,290 No no it is when you are adding the columns always specify columns the way they are in the table. 80 00:07:56,290 --> 00:07:58,720 For example the phone number Colombia. 81 00:07:58,730 --> 00:08:00,080 That's an on the score. 82 00:08:00,250 --> 00:08:03,800 So I have to specify on the score as well. 83 00:08:04,060 --> 00:08:07,790 So these are the columns I want to show up in my view. 84 00:08:08,140 --> 00:08:13,150 So I need to specify the table they're coming in from from 85 00:08:15,520 --> 00:08:17,620 employees. 86 00:08:18,260 --> 00:08:21,650 All right so my view is good to go. 87 00:08:21,660 --> 00:08:31,770 So we've said create a view and call it all staff as means take the information from this table here. 88 00:08:31,860 --> 00:08:39,450 This cell let me these is what I want you to include in the view and from these employees or the employees 89 00:08:39,460 --> 00:08:42,230 now is the base table. 90 00:08:42,310 --> 00:08:44,440 So if I run this is just great of you. 91 00:08:44,440 --> 00:08:46,940 So I click execute. 92 00:08:47,770 --> 00:08:48,460 Excellent. 93 00:08:48,460 --> 00:08:53,170 You see that there is a view created you created. 94 00:08:53,200 --> 00:08:54,520 So I've got a view. 95 00:08:54,730 --> 00:08:57,490 So if I do a select statement on that. 96 00:08:57,910 --> 00:09:05,380 So I do select a star from start 97 00:09:08,050 --> 00:09:13,550 fire run that it should give me a new view. 98 00:09:14,290 --> 00:09:14,860 Excellent. 99 00:09:14,860 --> 00:09:17,630 You see that so this is the view we've created. 100 00:09:17,950 --> 00:09:26,170 Employee the last name email hire date phone number or is derived from this script here. 101 00:09:26,980 --> 00:09:34,840 And these the details were taken from the employees so the employees is the base table saw with this 102 00:09:34,840 --> 00:09:35,150 view. 103 00:09:35,150 --> 00:09:42,880 Now we can make this data available to certain types of users within the organization and then we can 104 00:09:42,880 --> 00:09:47,880 also tailor a different type of view to a different set of users. 105 00:09:48,070 --> 00:09:52,180 So this is the advantage of using a view. 106 00:09:52,210 --> 00:09:54,830 So we restricted the salary information. 107 00:09:54,850 --> 00:10:01,260 We was present in the base table from this all stuff. 108 00:10:02,050 --> 00:10:07,130 Just a quick summary of what I've covered in this video. 109 00:10:07,440 --> 00:10:09,550 I explained what your views are. 110 00:10:09,550 --> 00:10:18,480 They are stored in school queries the data from the queries are taken from tables. 111 00:10:19,060 --> 00:10:27,790 Could be one or more tables and table the date has taken from there known as base tables view as all 112 00:10:27,790 --> 00:10:33,410 source can be used to customize data to present a different type of user. 113 00:10:34,190 --> 00:10:41,070 They can also be used to restrict access from parts of the data from certain parts of the data to other 114 00:10:41,080 --> 00:10:42,610 people. 115 00:10:42,610 --> 00:10:47,110 The views are stored in the data dictionary. 116 00:10:47,110 --> 00:10:50,620 We also create an example for you. 117 00:10:50,710 --> 00:10:56,520 I hope the information in this video has been helpful. 118 00:10:56,530 --> 00:10:59,930 You can practice creating some views for yourself. 119 00:11:00,250 --> 00:11:01,960 Thank you so much for watching. 120 00:11:01,960 --> 00:11:02,810 Bye for now.