KILLER Microsoft Access in STEP by STEP - Temporarily FREE | Fahmi Eshaq | Skillshare

KILLER Microsoft Access in STEP by STEP - Temporarily FREE

Fahmi Eshaq, Database Developer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
14 Lessons (3h 15m)
    • 1. Promo Video

    • 2. 1. What is an Access Table?

    • 3. 2. What is a Single Primary Key?

    • 4. 3. How to Add and Manage Columns?

    • 5. 4. What is a Composite Primary Key?

    • 6. 5. How to Import Excel into Access?

    • 7. 6. Let's Take a Quick Tour in Access

    • 8. 7. What is a Query Design?

    • 9. 8. Database Relationships - Part 1

    • 10. 9. Database Relationships - Part 2

    • 11. 10. What is SQL?

    • 12. 11. What is a Calculated Column?

    • 13. 12. How to UPDATE, DELETE, and INSERT Data?

    • 14. 13. The End


About This Class

I'm giving you a free course TEMPORARILY for GENUINE positive review. If you've concerns or negative experience, please contact me first. JOIN NOW before its too late.

Learn from the best and grow your skills and income.

This Microsoft Access course is fast-paced and it'll teach you in BABY STEPS what you need to get started in Microsoft Access.


Short and sweet.

No prior experience required.

- You must have Microsoft Access (any version) installed in your machine.

- This course is easy and effective

You'll stand out amongst your peers with your productivity. MS Access is very powerful;

Learn Microsoft Access once and use it unlimited number of times. Once you learn Microsoft Access, it's hard to unlearn it.

- You will gain a valuable skill and strengthen your resume, salary, and better chances of promotions.


Join now!

What are the requirements?

  • Make sure any version of Microsoft Access is Installed in Your PC


What am I going to get from this course?

  • SQL
  • Query Design
  • Tables and Data Types

What is the target audience?

  • Professionals who use Microsoft Access in the workplace
  • College Students
  • Anyone who is interested in databases and SQL


1. Promo Video: lo ladies and gentleman's. Okay, I'm gonna teach you maker Soft Axis. So the question is, what is access? Access is just a tool that helps you store data. And then once you store data, you start managing and processing data. That's it. It's very simple, Very easy. This course it's perfect for those who want to learn access and become productive in a very short time. I'm teaching you all the fundamentals that you need to be able to grow in in using duality access databases. So you're here for a reason whether you come from a workplace and you're pretty much knew access and want to learn about it Or you come from a college university where you have some assignments or you're here for personal interest in databases doesn't matter as long as you want to learn access. Microsoft access, this course is for you. I teach you and in a very easy manner in a in a simple baby, step by step, and I get to the point quickly. You know, I just get to the point and teach you what you need to start, you know, just running with it. So joined the course now and let's get started. Thank you 2. 1. What is an Access Table?: Now, the first thing you have to do is install access. Or I would assume you have access installed with Microsoft office already. If you don't, then please go and install access. Dizzy Matic. It could be accessed 10 4016. Whatever. OK, so but try to choose anything beyond 2010. So to a 2010 and beyond. So once you install access, then just right click here. Okay? And choose. Ah ah, you and then select Microsoft Access database. Okay. Ah, you can name this anything you want. Okay? Whatever. You know, you could put your name here, so I'll just type sample Devi, which is a short picture of data bees open access now, and simply to start creating a database, you need to create a table because all data are stored in a form of tables. Okay, Simple. Just go to create, okay? And then press table. Good. So each table consists off rows and columns. So, uh, let's start anything here, okay? Just type with me. Your name for me? A sock. You can't help your name. He could put in your age. Or you can put in your That's a driver lines number, you know, 4444 or whatever. And you can put here your age, you know, 55 or whatever you know, And then you can put your Ah, let me think about it. Your address, you know, 123 Main ST Fine. And then you go to the second role. Now, this is called a record. Each row is called the record. Now you go to the second row and populate these fields again. Like Jack. I don't know, Mike. And then any, uh let's say you put your driver lines is here and then the ages 30 for addresses. Ah, could be empty. Okay. And keep doing that. So now we have two records, and each record has an I d. On ideas and a unique number. It's a It's a value. Okay, that allows you to uniquely identify this record. So if I tell you, OK, give me the record. Ah, information off I d. Number six otoko Flynt. Let's look idea number 60 this one. Okay. What do you want? I want to know the record information. So you would say OK, this guy's name is Jack. My kids dance number is if this this is this. OK, so we identify records through on I D column. Anyway. So, uh, the columns should be named something descriptive because we just created a table. So we don't really have names. What he could do, you could save the table. Just first, save and name that table. Let's name the stable. Uh, give it a name that represents the kind of records you're inserting here. So, what kind of Frackers do we have here? It could be customers. Could be employees. You know what I'll just say we're having? We're putting employees information, short type employees. Good. Uh oh. Employees say it. So this is the table, OK, you can close the table now and then double click it. See, we have information saved here. So employees Ah, let's rename these fields just right. Click the table and then type in. Ah, the name. So that was first name. That was last name, right? And then what? It was 3rd 1 uh, driver lines his age and address. Sorry to close this right click. Ok, and go to design view if you right click and go to design view. It allows you to change the structure off the database information. So that's like the structure of the data bees. The structure here has 123456 It has six columns. Okay? And since you need to change that structure, you have to right click press design view. Let's not. But in the driver licenses, driver Lyons is okay. Oops. Oh, my God. I cannot spell that. I can believe it anyway. And then Ah, here. Age and then address. Okay, fine. So don't worry about that. We're gonna talk about it later. Save this. Close it, Deborah. Secular gain looks good. It makes sense now. Okay, So ah, let me just go back again. Let's delete a column. I don't need idea, you know, because I d was a default column that God created. And I don't want that. I want something else. So just right. Click here and ah, you know what? Let's just we don't want it at all. So right. Click and press delete, Rose. It means that we're gonna delete that. Okay, column. So say yes. It's Oscar. Do you want to delete it? Yes. Groups, it's a Hey, listen. There is Ah ah Primary key. Now, what is the primary key? Let's stop here. Okay? I'm gonna in the next video. I'm gonna talk about primary keys. 3. 2. What is a Single Primary Key?: Okay, We're back to the primary key. Think. OK, So as I told you, I d here represents a unique value. Okay. Which allows you to uniquely identify each record and that column. A column that has a unique value which allows you I didn't You, which allows you to uniquely identify each record is called the primary key. OK, that Feagles cannot be duplicated. It cannot. It must be unique all the time. And it cannot be empty. Okay, A primary kin primary key cannot be duplicate and cannot be empty. For example, if you look at address addresses, a column Okay, uh, here we have it empty. We don't have any problem. That's good. You know, it's fine. You could save the database or save the table physical table so you could say the table without having any address. It's fine, but you wouldn't be able to do it with the i d. Because ideas of primary key. Now let's come down here and press. Uh, I don't know. Saga. Ah, Davis. Any number you can put like however Okay. South streets. Okay. Oops. What did you notice? You notice that this number got populated automatically? We did not really fill in anything. Ah, here. This number got incremental automatically. Let's remove this. Okay. The seafood can remove it here. Or what's going on? You can't see. I'll tell you why. No. Save this. Okay. And now up in the design view. Good. And if you look at the data type, it tells you auto number adult. I basically it just tells you what kind of data or value we're going to have in this column or field. OK, so if you and the first name we gave it a short text, it means we're gonna have bunch of text there. A text can have. Can can have on alpha numeric value. You could have, like, letters and numbers and special characters. This is a text. If you look a driver, lines is an age. We have number. It means the data type. The kind of values you can put in these two fields are only numbers. You cannot put anything else. Okay, You gonna protect so Levy, uh, and you can see that data type is alter number. Auto number is a built in data type in access that say is okay and make sure like access would get involved and and give you Ah, around them. Unique number. Okay, so access. If any field has a data type of auto number, then an auto number will be generated by access. Like what we saw with I D. We just populated this, okay? And this and number seven showed up by itself. Now let me show you. Now, let's put let's change that driver licenses. Remember Robert Lance has had a number, right? So let's put a name. Let's put any name here. Let's put characters. Okay. See, it doesn't allow you to proceed because it say's the value you enter. Does not match the number data type in this column. Your value should match the data type, which is any number. Okay. Ah, and that's what's so called Data integrity. This what? This What databases give you this? What access database gives you okay? It gives you data. Integrity's allows you to control the quality off your data. Allows you to, uh, uh, maintain good data all the times. So, um, what I want to do is I say I wanna delete this. I d right. So let's go back. You can not delete a primary key. Like, if you want to delete this, you cannot delete it. Why? Because we have a primary key. So what would you do is you have to remove officially. You have to do it that way. You remove the primary key first, okay? And then you delete a column. Got it. So let me do that. That's what we deleted it. So now we only have of course, you have to save the table before viewing the data. So now we don't have any I d and that's it. In the next video, I'm going to add a new field. 4. 3. How to Add and Manage Columns?: let's create a column. So I would just right click this. Okay, go to design, view and type in. Ah, ah, and certain Rose. You can, like, add a column here. Okay, let's name it, for example, employees, and let's give it a number, okay? Or you could basically just delete this. We don't need it here. I wanted to shop in the first column. So right collecting the first column and type in certain Rose and then type in employee I d . We want to have employees as a number, so choose number. Yeah. Good. Save it. Close it. Double click again. And now you can insert any number you want. OK? So you can put four. You can keep it empty. You can put two. Okay, fine. So in this case, employee is not a primary key. We have to. We want to assign it as a Primerica. How do you assign it? It's simple. Just go to the column that you need and right click impress primary key. Oops. We got it. So now go back to the table. You have to save it. It says hopes don't No, no, no, no, no, no, no, no. You got a problem here. Why? It doesn't allow you to have the primary key because we have one empty field, remember? Ah, primary key must not be empty. Let's look at the other again. Save it. It says index or primary key. OK, index or primary key? Primary key. You know, indexes like a primary key. So Ah, these are synonyms to make it easy. These are synonyms. So index or primary key cannot contain a null value and no value in the database. Language means empty note means empty, empty value. Okay, I press okay. And nothing was saved. That's fine. Press escape. Or does it matter? You know, just we will not save anything. Just canceled. Come back here. That empty field caused the problem. So we have to put any number here. Let's put in on a date. Okay. Now close this right click again. Price. Primary key. Good. Now let's save that. See, we don't have any problem now because all the fields got populated. Plus plus, all the fields are unique. 20 to 44. 44 88. If you change this to 22 again, it says you. No, no, no, no, no. you gives you an error. Why? Because you're not allowed to have duplicate values in a primary key. OK, so let's put any unique number. Let's put 10 in the next video. I'm going to talk about a composite prime. Your key. OK, see you there. 5. 4. What is a Composite Primary Key?: Hello. Let's discuss of our composite primary key. So let's just remove the primary care from right click Press this bomb. No primary keys anymore, Okay? And you know what? Let's just delete employee I d Boom. That's it. It's over. Oh, it says they didn't. It's fine. You can delete it. Okay, so ah, here. A composite primary key is when you have to. Ah, fields that uniquely identify each record in case of her employee I d. That was a single column or single field. But what if we want, like, toe identify driver line says an age and address based on first name and last name only? Okay, assuming that first name and last name all the time will be unique. So what do you do? You would simply come here, highlight both columns. Okay, so I simply press this press the first government and press shift key present shift, and then select the second column. Okay, now, right. Click. Okay. And price, primary key. So what do you see is two keys and that means both of these fields. Now, our primary keys Okay, so close this. Save it. Where get so far we have a unique combination. Off first name last names. What if I come here and type Saw Davis again, Okay. And put any number put another age because a single company can have multiple people with the same first name and last name. Right? So this could happen. So this assumed this is different person. It's just sharing the same name of somebody else. Wait, Let me just put in the address or whatever. Okay? So if you try to save the table, it it doesn't allow you. It says no, no, no, because it says, ah, you violated the primary key by trying to introduce duplicate value introduced duplicate first name and last name. That's why I saw a good idea for a company. Okay, to assign primary keys based on first name and last name, because so many people out there have person in the last name. Okay, If somebody calls me and say you know what if Saleh Cordesman say, can you please ah, remind me off, You know, can you double check if the address in your system is old or new and then I tell her. Okay, what's your first name and last name? Assuming that this is the only way. Like for me to recognize her, Assume that we don't have an employee. I d So tell our give me your first name. Last name. And when she gives me the first name last name somehow You know what's gonna happen? I'm gonna see two records. Hopes U S. C two records, this one and this one. Okay, so you'll be confused. Like, which one should I choose? That's why companies prefer to give you employee. I, d York or our businesses prefer to give your client number because it it guarantees that's gonna be unique. Okay. Anyway, back to the topic. To resolve this. Just put any other names, like, I don't know. So I don't know. Whatever. You know, Michael, we're fine. Good. So that's the concept off a composite primary key. See you in the next video. 6. 5. How to Import Excel into Access?: I'm gonna show you how to import on Excel sheet into access now. Not necessarily. That every time you're going to create a database, okay, a table here yourself from scratch? No. Sometimes you have the data ready in excel cheat, and you just want to import it into access. So I found a good example online. Ah, and ah, the example simply has ah, list of orders. I attached this actually into ah, the ah into the work into the attachment off this video so you can download it and try it. So once you open the excel sheet, you can see there are so many tabs here. I'm not really interested in any of these staffs except sales orders. So delete those taps just right. Click. Do you eat? Okay, delete. And we want to import sales orders into excel into access, save this file and close it. So, in access to important excel sheet, go to the external dal atop and then choose excel this part under a that's within import and length section. Okay. And then go to the, uh, excel sheet that you want to import press selected and shoes open, and then make sure this is selected. Import the source data into a new table. Okay, and now it's gonna tell you. Listen, look. The sexual See this exception? It has so many. Has these columns? 1234 Okay, whatever. Uh, seven columns and you have all of these records. It will ask you here. You know, make sure that the first row is the header. Oh, yeah. The first road is the header. So pressed next and then in here as you import access gives you the option to decide whether you want to change the data type for each column. So far, No, just keep them as is. Don't change anything. As you can see, also, access anticipates that this field is off date. Ah, date type. So it's selected for you automatically. You can change it to text if you want, but let's keep the risers press next. Uh, access asks you, Do you want to add a primary key or not like, you know, what would I want to add? Anything. We don't want to add primary key, So just choose no primary keys, or you can basically select this one, choose my own primary key and decide Which column should be the primary key in our case. We don't want any primary key, okay? And then go next and it will tell you name the table. As I said earlier, make sure the table is named destructively. In this case, cells orders Maxence. So we will keep it. As is press finish. It will say the import is done successfully. Just close. Now, double click the table. And as you see we have or the records here. So if you want to know how many workers do you have? Just come in the bottom and press this. We have 43 records. That's it. In the next video, I'm gonna, like, go through some, uh, go through some features in axes. Like I'm gonna walk you through a few of these things. Ah, see you there and have a good day. Oh, and I forgot. See their woman. Have a good day to 7. 6. Let's Take a Quick Tour in Access: Okay. Okay. Okay. Now, let's go back. So, yeah, What you could do is you can actually create a copy of a table. Just right. Click press copy and then right click again and press paste. And then, of course, you have to give a table a name, a unique name different than this one. You cannot have duplicate table names here, so let's just name it. Sales order. I don't know. Version two, Okay. And that it asks you, Do you want to have your copy? Every single thing, like the structure in the data or just the structure. So, uh, or you want to upend this to another existing table? Just let's choose now. Structure and data. If you develop like, you're gonna get exact same replica off the stable now, right click again. Press copy. And then right, click and press paste. Now, let's just select structure only to see what what we're gonna get. Okay, so if you click this, you get nothing. You just get the structure off the table. Which is the column? Names off course. And the data types. OK, that's the structure. If you go to the ah structure, let's just go through some off the data types. So you have, like, short text, which takes up to I don't know how many characters, maybe 200 characters or fouled and something like this. Okay. And then you have long text, which takes big, big characters, like, I think, 5000 characters, plus or more Okay, number. It just takes numbers, dates time, by the way, if you want to, like, choose, like, type in an address or something off a small text, like just shoes, short texts. But if you want to make sure like you wanna have a field that will hold, like articles or long descriptions, then choose long text. Okay, to be safe. So daytime, if you just wanna, uh if the field will have date times only currency, it will give you a number with currency. Auto number. It's just like a It would make the field have auto incremental numbers. OK? Yes. No, it's a matter off. Yes or no? So if you choose yes. No. Here it will. Uh, you know, it will give you the option off. Yes and no, that's it. You cannot have anything else. So one would you use a Yes, No. Let's say you have a field called Is customer. Okay? And then I would choose, like, Yes. No, It's like a flag whenever you have something. A flag, yes or no. You know, you would choose that data type. So is customer, for example. Let me just say this. Okay, so it's customer alto. Let's say you populate that with bunch of whom mentioned here. Okay? And then you can mention if this is a customer, not you. Get it? So, anyway, I know this doesn't make sense here, but I just wanted to deliver the concept. Uh, and then you have, ah, hyperlinks attachment calculated. These are not commonly used. Ah, I mean, the most commonly used data types. I spare my experience, you know, in my workplace, different workplaces, short text, long text number, date time, currency or a number years? No, these are the common ones. Okay, So just close everything. Let's right. Click on the sales orders version to select design viewer gain. Now, if you look at the bottom whenever each use field again, you're going to find some field properties at the bottom. So let's choose region, for example, like it tells you the number off texts that you may have in the region Field is 255. And that's the maximum number off characters that is allowed okay for the short text. And there are bunch off self descriptive. Ah, properties here, for example, require, you know, Do you want this feel to be mandatory or not? So it has. No, That means you can have. No, it means you can have. Ah, you can have no values. Here, let me show you. Let me show you. So scroll down to the bottom. Okay. Off cells order version two. And let's just insert a new fear. A new record. So select any dating want. Okay, type in. I don't know Western. And then the name is James Ardan. Units on five units. The cost is $5. The total is 25 for example. Okay, good. So you can save this. Fine. Now close the sales orders. Version two and go to the ah properties off. I would say region. Okay, press region and then scroll down. Change required to Yes. Save this. Okay. And then close everything. Now open the table again. Scroll to the bottom where we inserted the record A removed Western prying off to save the table. So we remove that. And then once you save the table, were like we're not This access is not supposed to allow us to save that. You know what? Because in the properties we selected that region must, you know it's required. Was likely that regions should be fulfilled. So why access allows us to save this? Then let's double check. Maybe we did something wrong. Just go to design few again. Select region required. Yes. Okay, let's look at the bottom here. Allow zero length. Oh, so there's another property that say's should you allow nothing. See, we got off. These are like, Ah, they conflict each other. OK, so we at some point you say yes, you know, make sure that this field is required. We shouldn't have anything empty and the same time we say, Yeah, it's fine. It can have zero left like you could have nothing in that field. So to avoid that conflict, let's just choose this to know, OK, save it. Oops. Says there is a problem. It says that you try to change the property, not to allow zero lef. Okay, life of characters, but your table has no like region field in one. Off the record, one of the records has the region field off. Nothing, you know, Zero. So it cannot really say that, you know? Forget about it. Just make sure that this selected Yes, and this is selected. Yes, it's fine. Go back hoops. Go back to your record and type in something like Wester. Whatever. Okay, save it. Now, I want you to go back to the properties and choose Choose Region, of course, and then choose their lengths to know. Save this. Okay. Now go back to the records. Scroll to the bottom. Let's remove Western now, okay? And see if we are allowed to save or not. It says you must enter value. You must enter value here in the sales blah, blah, blah, duct region field. So it refers to the table and first dot column knee. So it tells you because we have multiple tables here. So how would you know all of them have regions, right? So this aero tells you No, no, no. We have problem with this region in that database or that table story that table so press Okay. See, you're not allowed to say of anything now, so we have to type in something so you can type, you know, Eastern whatever. Now, let's go back to the properties, okay? To the design view of this table. And look at the default value. Let's just type in, you know. Ah, default value type unknown. Just type anything you want. OK, Save. Now go back to the table to the records. Double click this. Go to the bottom. Okay. Chosen a date like, let's add a new record now. And what do you notice here? Unknown. Good. Fulfilled immediately. So even though he type anything here, you're always guarantee that you're gonna end up with a value with the default value here. Okay, Got it. You can delete it. If you want. You can delete it. You can change it. Okay, But of course, you cannot say that because we have region field as required. Okay, so let's just dive it back. Unknown. As I said, this is this course touches the more supported things in access. I'm not going to go through every single thing. I'm just teaching you what you need to be productive in the workplace. Okay, Like what? What I'm teaching you now is good enough for you to start going. Do something with your task or job assignment or whatever you want to do. Now let's Ah export. Okay, let's export a table. So go to external data, OK? And then go to this section. What it say's export? Do you see that press? Like if you want exported to excel or sees re it's up to you. We can export it to any format you like. The most common one that gets used a lot is excel So you export the table into excel. Just press this okay? Press the location you want explore too Like let's say here, let's name it, you know, exported for anything you want to name it, OK, and then And these are just Jardins it tells you if you want to export it with the formatting like the number for mountings and all of this, you know these are like whistles on. So it's up to you. We won't export it into the excel X XLs X press. Ok, that's it. It got exported in the desktop And this is our exported excel sheet. You can double click this and use it in any way you like. Access database has a limit size. Okay, It's has a limit off two gigabyte. So whenever your fire, your access data vase, okay, ups, whatever. Here one of her that's reaches, like to g B. So far, I have one and be only so if it reaches two gigabyte, that's it. You cannot really do any much like you either have to reduce the size of it by deleting some records and delayed him some data. You know, this is the limitation off acts it Regis to G B. And then he cannot do much that you have to do something else like you have to remove some data one way. One way for you to resolve and minimize the size off your access databases is to go to the database tools, tap and press compact and repaired Halabi's. This helps you reduce the size. So let me let let's go to the actual access icon. If you look closely here now, we have the size of 488 kilobytes. Okay, We used to have 1.24 and B, but now we have for 88 kb. So decided. Introduced immensely. Okay, this is it for now. Now, in the next video, I'm going to get into quickies. How can we, uh, you know, right? Some queries and pull the data that we're looking for. We don't want to see every single thing here. Okay, so see you in the next video. 8. 7. What is a Query Design?: Let's talk about Queary design. What is quickly design. So, first of all, a quickie is a way for you to process their a way for you to retrieve data from a table because a typical table would have hundreds off thousands off records. Okay. And you cannot go through them manually. So let's say your manager comes here and say, you know what? Uh, can you please give me all orders within the region off west? Can you do that? So, what did you do? Like naturally, Like, be conventional boring ways to go through here and sell counting. OK, I'll give you all the orders. West boom. And then you could be that. Okay, you can copy a record and put it in excel sheet and then moved to the second west in the third. And just headache. Okay, I'll show you a better way. So a professional way. And this is powerful. Guys, girls, this is powerful. This is powerful. Okay, So create clearly designed. Simple. Now it will say, hold on, hold on was going on. Can you please select one table? Because we have so many tables here, right? Three tables in a typical real environment and access. Like you're gonna have tons of tables. You could have 10. 20 problem. 50 tables there. Okay, So, uh, and and database a database is a collection of table. That's it. A database is a collection of tables, so we have to select one table here. So let's select sales orders. We want to deal with cells orders Good. And then closed. You could select more than one table, but for now, let's just select one table. OK? Close this. So, uh, let's decide what kind of field we want to return or show. So I wanna show. Ah, Order. Date. I want to show ah rep Name and I want to show a total. Okay. And region good. So this basically tells you we are retrieving the following fields ordered a trap. Total region from this stable sales orders, all of them from sales old. It's OK, and it stays, and it stays here up, sort. So you have to like it's optional. You may want to sort by total in descending order, so we want to show the highest totals to the lowest total. Okay, so press and select descendant, that's it. So let's see what we're gonna get. Okay, keep these as show. Show me is Show me the columns. You may want to list everything here and then you just decide what to show. What not to show. So if we remove this, you wouldn't see rap anymore anyway, for now, just keep everything show and go to run. Oh, well, while we were look, so we got all the columns that we need and they are sorted by total from top to bottom. That's great. So that's good. Now go back. OK, go back. Right. Click select design view. Okay. And remove this one of the show's remove it and go to run bomb. We don't see it anymore. So the column is headed. The column is hitting. Okay. Hoops were gettable destined. What about that? So OK, go back here, make sure everything is show. And, uh but we don't want to show everything to be honest. Oops. I'm going to set that to descending again so we wouldn't want to show everything. We just want to show orders off a specific region. So simply come to this part, which where it says criteria, they go here and tightened like, let's say we want to show all the orders associated to West Region type west, OK? Or just type quest, you know, as this. And then press run. See what we got? We just got what we needed, that's all. What if he wanted to show West hopes go to design view. What if you wanted to show west and central or sorry? West or central? So go to the second column and type in center here. Type in Ah, East, for example. I'll just keep it central. Okay. So you could add as many criteria as you want. Press run. So you get west and you get central. If you look at the drop down box. Yes, we have both of these. So this is how you retrieve data Hopes again, Again and again. We'll go back to design view. Okay, so we just retrieve data through a bunch of criterias. So what he could do is you could save that. Just press save it will ask you. You want to save it? Like what? You know. Just name it. For example, West Central orders. Yep. So you can close this now. So every time you want to see all orders associated to West Central. You can come here and double click this. That's it. You see it here? Okay, Now let's go back and create another design view, right? Like us or not, Right click. I mean, press this go to create created design. You don't even have to press that anyway. Anyway, you know, just go to create Selectric, really design and select sales orders again. Close this. If you notice here, you find this option, which is sales orders Got star. That means show me all Collins from this table. So if you press run, you see all the columns here again, you can always add crazier year like west again. Okay. And if you do run, so basically, you can close this now. You don't have to save it. I want you to go and delete the copied columns, Lead this one and delete this one. Now, I want you to also go to the equity design again. Let's try something else. So add the sales order stable here. And let's add, for example, or the date okay. And add, uh, total and rough name, but we want to make sure we only grab totals that are beyond $500. Okay, So type larger than sign space and then put 500. That's it. So we pressed. Run. We got all the orders that are beyond 500. Now go back to design view. We want to get all orders. Less than 100 for example. $100. We got all orders. Less than $100. Okay. And ah, you can do that with, like, equal. So we're gonna get all orders. That's equal 200. Exactly. Nothing. So not we don't have any order. That's $100. Okay, It's either less or more so hopes again, Again and again. Let's just go to the design of you, OK? So you can do, like, more than or equal 1000. Let's see how many orders we have. Okay, so we got these, and you can have Oh, my God. Oh, my God. Every single time, a keep pressing secret view. I don't get it. You know, just let's go to the let's go to the design view, okay? And then he can do less than or equal. So you have equal. These are comparison operators, Okay? Called comparison operators, you can do equal above less less than or equal, Larger than or equal. Simple. Okay. And you also have a sign called Not Equal so not equal. You can write it like this explanation mark equal, and then put the number that you want to put here. So anything that's not equal 189.45 or you can do it that way. That means give me all orders that are not equal 189 or five. So oppressed design groups. Okay, if you run, if you're on it, it tells you hopes I cannot do anything because the table is open. So if you have an open table here, you cannot really. If you have the table in a design, really gonna do anything, you have to close this first and then run the query again. So you'd go to design, always go to design press run. So you got everything OK except one record. That's that has won 89 or five. If you look here, we have wonderful 1 89 or five. But you do not. It will not show up there so it doesn't show up. So if you press run, if you notice that we have 42 records. So there's one record missing. And that record is the one with the 189. Because in the sales orders we have how many records We have 43 records. So what was missing? What? What? What did what record did you filter out? Best record were filtered. We felt it out. This record okay, from this greedy Got it. So go back to designed to you. We have another operator called in. Okay, It's easy. Very easy. Just come here typing in, okay, Space up in a bracket and then put Let's pick some numbers. So let's spec, Let's 1 89 known a list like this 1999 99 or whatever. Okay, comma and their space. And then let's spec Another number 63. 68. That's good. And the third number? I don't know. That's 479 or four. Okay, so this basically tells us, Give me all records, okay. With the total number off either this this or this. Okay, so if you press run design, go to design, make sure go there, and then press run hopes. So we got old record with the numbers that we put in the end. Close. So we have, like, four ah records within the data bees that have these totals. Okay, so it looks like we got two orders with the same amount from two different people in different at different dates or in different dates. Okay, so remember, we got four records, so let's go back to the design view off the Kwik E and type in something else. Let's add a key work, Okay? Not called. Not it means give me everything in that table. That's not this That has that has a total. That's not this this or this. Okay, so press run. So not in isn't a like this. This is also key word. Like you could have end or not in. Okay, these are reserved keywords. Run. Okay, so we got everything, but we got back what? 39 records. So three records are missing. Sorry. Four records are missing because sales orders have a total of 43 records. Okay? And we got 43 records and we just got 39 records because we excluded all the orders that have Ah, those totals. Okay, let's go back to the sales orders. Design view. Let's just look, uh, let's say rap name. Okay, Brooke read has a field, Okay? I mean, has ah property called required, which is no. Okay, that's good. So I just wanted to make sure rap has you know, the option off. No, it's not required because we want to go and delete some off the reps. So double flick this table. Now, let's delete some off the reps. Like, delete this guy. Okay? Delete. Ah, Jones. Delete Morgan. Okay, so we have three records without traps. That's fine. Save it. Close the table. So delete all the criteria is I want you now to go under up column, okay. And type n type and is No, I'll explain. Door. Just type in is no. Okay, Null means empty is means yes is like I wanna have all records with wraps that are empty. Brooke, I wanna have all orders that do not have any reps. Okay, Press run bomb. We've got three records. We don't know their reps. So if you go back again and again anyway, go back If you do is not okay. It's case insensitive, so it doesn't matter if you have it. Upper case. Lower case. it doesn't matter. So if you put the word not here is not know, it means Give me all reps that are not empty run. So if you search for empty wraps, you will not find them. So we excluded the three. Empty the three record Your is with the empty wraps. Okay, We only got back 41 records. So I mean, 40 records, not 41 40 records. Okay, so why didn't we say equal? Null, Right? Who could do that? The thing is, the in the case of surgeon or filtering null values is a little bit different. It's not like a number equal 100 Remember are equal. Whatever. No. When it comes to Nol, if you're looking for now, all you have to put is okay. And if you want to look for records with that don't have no fields. It's fine it can use. You cannot use, like, not sign, remember? No, you cannot use that. And you cannot use that because those tour means not does not equal. Okay. For the case of an all you have to say is not okay. So you want to grab all records with empty wraps? Just look for is null. That means we're rap is no. You won't have all records with wraps. Not know. Just do is not no simple man. Very easy. Very easy. Ladies and gentlemen, it's what If you want to add multiple conditions, Okay, let's say we just want to grab all representatives that done that are not empty. Okay, All records with wraps that are not empty. And they should have to be in a region called I don't know. Sinful. Okay, so the these are multiple conditions, so we have to conditions of this moment. Press run. Okay, so we got all the reps that's not empty. And with the region central only Got it. If you go back and change this to West, then you get only west. You get it. So ah, that's how you can have multiple conditions. And it's fine. It's fine. Like to have Ah ah. For example, the same column repeated multiple times. So let's delete region. Okay, delete this. Now we wanna show all records. Okay. Would wraps not empty. And also want to make sure the raps Paul wraps start to the letter J. So there's a new key word could like okay called like so type. Like like allows you to surge a pattern so tight like and then put a single column a single quotation and type J Capital J, for example, or lower J. And then But remember, whatever you put in between here, between these single quotations, okay, is case sensitive. So we're gonna look for all wraps that starts with the letter J. Capital J. And then it doesn't matter what comes next. So I just put star and then closed it. Single quotation press run. I see. So we got So we got all the records with wraps that starts with the letter J, but way Wait a second, wait a second. We have another feel called expert, you know, whatever that field is. A created column that's feel basically is a reflection off the spot. Because now we have two reps, right? And the only reason we added the second rap because of the second condition. But in reality, we do not want to show that Rep we already we are already showing this, right, right. So just hide this one. We don't want to show it. We just put up here for the sake off added and a new condition. Okay, so press run again. That's it. So we got all wrapped starts with J. Now let's go back. Let's put something else like put, uh, let's say J M and then star search hopes so we don't have a rap that starts with the three letters off J A. M. But let's say that, OK, we're just looking for wraps that that's that. Have names starting with J or N okay, and does it matter what comes next? So just put Star Jail and Star Search. We only got Jones. Got it, But we want to get, uh, the totals. That's over 300. So larger than 300. So we got all orders beyond $300. What about what If we want to find orders between 103 100 let's Let's see, how did S o type the keyword? This is another key word called between, okay, between you put the range off numbers you want, like, between 100 at 300. So between is a key word and and is a key word. So they all come together like between you put a number, the lower number, and then and a bigger number. So this will give us all totals or frappes that hat off. Perhaps that starts with the letter jail. And okay, all numbers between 103 100. So run. I see. So we only got door startles between 100 fire and 300 or the five hundreds were Garnett and three hundreds and beyond, like, beyond 300 is going to so designed. So I'm gonna introduce another key were called Not we used it before. So here Would like would like You can type Not okay. Not like Jones. So we want to grab everything that's or apps that are that Do not start with J o N Delete this part. Okay, the between one. Just take it out from now. Press run. So all off the wraps here, If you look closely, they do not start with the letter J o n. You can double check here yourself. You will not find jr okay. And you can do the same with the between. So between 103 100 you can put the ward Not Okay. So let's just remove the Not like now. Okay. To avoid confusion removed This So here I'm looking for all orders that are not between 103 100 spread execute. So if you look at the prices, you know you will not find anything between 103 100 by the way, the 103 100 are inclusive. So if you have 100 if you have 100 here, okay, in the total, it will show up in the search results up. Sorry. Well, it will not show up. It will be excluded too. Okay, if you use. Okay, let me let me show you. I want to just explain that. Okay, Let's go back to total orders and change that 189 or 5 200 Okay, Good. Save it. Close that. So now we want to find all orders between 103 100 design. See, we got back the 100 because 100 is inclusive. And if you do not between 103 100 run, you will not have the 100 here. Got it? Because 103 100 are inclusive. Okay, so I want you now to delete everything here. OK? Just, like, deletes everything. And, uh, let's say we wanna choose. Uh, we want to see how many. What are the regions in the table. I want to see a set off unique values off regions. Because if you're not as if you're not just at the table, the region gets repeated multiple times, you know, based on the number of orders we have cetera, I just want to know uniquely how many regions we have or what are the regions that we have . I don't want to keep scrolling down and counting. What are the unique regions, you know? So I would come here to quickly, uh, and then select any cell and come to the unique values press? Yes. Okay, First of all, do that and then choose region. Okay? And then, uh, just make sure this shows up show press, run that design front. So what we got back is three records, which tells us we have in sales orders. We have three regions Central. Where? East West. You can do that with ah ah raps. Okay, So if you put trap here and then run, these are all directs that we have. Let's choose a combination off for absent regions. So we want to see how many reps who who are the wraps that work in what type of regions. Okay, so simply just run this. Oops, Sorry. Let's go back. OK, Go back. Select one of the empty cells here. Make sure region is selected okay? And go to unique values by now. He should have its city as yes or just said it. If it's not said ts insanity, it's OK anyway, so run. So this tells you that we have these representatives work and central region and those ones work in east and those ones work in west. Those two are rep and region are a set off unique combination. OK, so if you go back to design, view and select one here or anywhere and then come to the sheet properties changing values to know and then run, you get 43 records, which is the same count as the sales orders. But in our case, we don't really want We just want to know the you know, which wraps work in which region. That's it. Okay. And that's why we ended up selecting the unique values to Yes, you know, I end up selecting, setting unique values to Yes. Okay. Uh and that's pretty much it. I have nothing else to say at this moment, but in the next video, we're going to talk about multiple tables. So what if we have multiple tables in the query design? How, you know, how can we use that? Okay, see you in the next video. 9. 8. Database Relationships - Part 1: Hello. We've got to talk about table joints. So first of all, go to create and let's create a table. Choose table design. Okay? This is just a different way to create table. You could choose a table or as we discussed earlier or choose table designed for now, we're gonna choose table design and let's put here customer I d Okay, And and give it a number. Oops. Come on. But customer name first name, first name and ah customer Last name guess Tumor D o b date of birth. Customer region. That means ah, where the customer lives with our this customers in the north. East, west, south. So now let's change the data types for each field. So customer first name is short text correct. Correct date of birth should be date. Ah, time and customer region Short text. Oh, and one more thing. Let's just put field cold is active to help us know whether the customer is active or not. And press Yes. No good a type Orpheus. No. Save the stable and name it customer. It asks us to define a primary key. And yes, we do want primary key. So we forgot to do that. Customer Eddie is our primary key. Save it. Nam Nam, it customer. Okay, let's just populate some records here, so I'm gonna give it a fake i d of 100 customer name would be Jack. Last name Astros. Ah, date of birth. It could be anything. You can just select today's date and then change the month to I mean the year to something else like 1955 customer region. You can put anything. I would just make it easy. I would say north, for example, and is active. It is active. Okay, if if this is not checked, it means the customers not active. So let's just keep repeating this for a few more customers. So we added these customers. Okay. Ah, and save the table. Now you can close the table. Let's create another table called products. So again, go to create able design product. I d. Okay, it's a number. And then product name, product description. And this is that. Let's say the table and name it product. Now let's go and populate the table. Of course, it's better to assign a primary key in a table. So let's assign a primary key hopes we don't want that primary key. When I signed the primary key on the product, I d Okay, just remove the primary key. Delete the i D. Column that axis created for us and right click here and select Primary Key. Okay, now let's populate that product table, so just give it a number. Like, I don't know, I would say 3000 OK? And ah, but any product, name and description, I just added three products 3000 4000 5000 protein shade, right him and be energy. Drink OK in. Let's add one more column called Unit Price. So, uh, you know what? Let me just add of value here, So let's give that 1999. Let's give that, Uh uh, you know, $15 let's strip that 20. Okay, now, since we added a new column this got named by the four Field one. Let's go and rename that to unit unit products. Ah, go to design for you. Change field 12 Unit price. Okay, let's create third table court supplier type and supplier I D. Number. Okay. And then supplier name, Supplier, phone number. Ok, so, uh, let's said this to primary key and save that to supplier. Let's populate supplier now. Supplier Name would be Ah, I don't let me think. Okay. Hello? Fee Food Inc. And the phone number is 444444 The supply of number two is, I would say Ah, you know supplements provider incorporation. OK, another number. The 3rd 1 is eat. Oops, each well, carp. So we have a customer table, We have product and we have supplier. OK, forget about this. This was from the last video, so I don't know, just put X here. Okay. Anyway, so customer supplier and product a supplier supplies product, OK, And the product is getting supplied by one supplier again. According to the business rules are according to whatever company you work for. Let's just assume that each product has a supplier that we buy from, okay? And And we can only have one product or or a supplier can provide us one product. You cannot have two products. I mean, you cannot have one product like this coming from two suppliers. Okay, so in our case now we have to know like this product comes from which supplier and this one came from my supplier. This record worship life. We can't have, like protein shake this exact product. I d coming from two different or three different suppliers. We must identify. Which supplier do we get this product from? Okay, let's look a supplier table. We have three suppliers here. You can add 4th 1 Let's just add fourth ones. Name it. Organics. Ah, well, organics drunk, I guess. Hopes just getting any name from two from the hit or organics farm. Okay. And give it a phone number, or you don't have to give it home number. It's fine. Okay. So save this. So now we have products and we have suppliers, so we have to know which product comforts were supplier to do. That is. Let's add a new column in the product table called supply or I D. Okay, let's do it. And but it has number. Save it. Okay, So hoops eso not here. So let's guess, you know, the pretty she ate comes from who? Let's say it comes from the supplier. I d number 10. So just get this number, Okay, But it here. What about, um, this one? Probably the 4000. Ah, let's say also 10. You know, we could have multiple suppliers. I mean, we can We could have single supplier, providing us multiple different products. You get it. And since we're this supply providers protein shake that no other supply can provide it to us unless we change the supplier. Let's say you take 30 and but it here if you want. OK, then. In this case, that supply would be the one who is providing us protein shake. Anyway, uh, just put it back to 10. Okay. And 3rd 1 is it comes from supplier. Let's say 40. Oh, so you 30. Good. So it's great if I want to know which supplier we get this from. I just look at the supply already. 10. And then I would go to the supplier table and look for 10. Okay, then Healthy Food Inc is the one that provide us this product. Uh, so whenever you have a table, make sure each table represents it stands alone like it stands alone. It represents itself. Make sure each column in the stable represents the table name. So if we say supplier okay, then we have to make sure the values in this table are related to supplier. Somehow. When we say customer, we have to make sure the fields here are related to customer. You get it? I'm not. I don't have any product description here. Do I have one? No. They have product name here. No. Okay, because this is a customer table, so I'm just hot. I just have customers from Asian. Same thing with the product. I just have products. Information. Okay, if you look at here from product to your price, so you may ask, why did we add supply are dealing? Because supplier is not really a product, you know? So supplier, does it look like it's related to the product somehow like Okay, these ones are fine, but this one looks different. Okay. Why did you put supply already? Here is to belt relationship. This is called relation Relational databases Relation table. So we're building a relationship between supplier and product. And how do you build the relationship? Is by having commonalities between two tables. What's the common between supplier and product? What's the common field here? Okay, Supplier supplier is the common field between both of them, and that's how you build some sort of relationships between tables. Okay, Now, why did you will put supply rd here? And why didn't we put product product? I d here in the supplier table because it depends on the relationship here. Okay. The relationship between supplier and product is called one too many. One too many. That means one suppliers provides you many products. One supplier which supplier 10 for example provides you many parts. It provides your protein shake and it provides you vitamin B. Okay. However, a product No, this down a product belongs to one and only one supplier. Okay, that concept is called one is called one too many. And when you create tables, you have to define that relationship between these between different tables. So supplier on product is one to many relationship. And since the supplier is the one that provides many products, then you take the supply i d. And put it in the product table. Okay? And then you mention which supplier belongs to which product At each brother again. Each product belongs to one and one supplier only. You cannot have like protein shake that belongs to ah supply. I d 10 and 20 and 30. Nano doesn't You must have one value here on Lee. And since we brought out that field supply, I d. Okay, somehow from supplier Table that feels in product that field supply already within the product table OK is called for in key. If you look at the product, this is the primary key right for the product table. What about supply already? Know it's not a primary key here. This is called for in key. However, supply I D in that supplier table is a primary key, okay, but in the product table is considered as a foreign key, and we put that here to build the relationship between these two product and supplier. The data type between the Dow is half of supply already in product table should match the data type of supplier. So the data type here is number. You have to go to supplier design view and make sure that if this is a number, okay, as you see supplier Sorry, the supplier primary key and the supplier table is a number. So we have to make sure the foreign key here that we created is also number. That's it simple. Let's go to the query design and see How can we use product and supplier here? So select product in select select product and then press control and select supplier Press ad. Close. So we have two tables here and you see there's a line in between. That line basically represents the relationship because axis smart so access realized hopes . It looks like we have that primary key here. Supply I d. As a foreign key Impala table. So there's a common fields there's This is a common field between two tables, so they're gonna be a relationship here. That's why access built a line between boy tables. See, start from here and connected to here. Got it? So let's find out. So I want to see the list of products with the supplier name beside it. I'm not really much interested in supply. I d. Okay, so let's just go to the field section and listen the product name, product description, and then, ah, if you can see the last here, see you have products star and then all the product fields associate ID to the product and then you have supplier and then you have supply like supplier star and the supplier and all the fields associated a supplier. It looks like all of these columns come from one table. You get it because the relationship can kind of the relationship kinds off brings these two tables together, so now he can view all the columns beside each other. So now I want to select. I want to see the product name, product description, and I want to know the product. Is the supplier name of that product Simple. I would just go to supplier dot supplier name. That's it. You got it. And then press run. That's it. So I had the product name her description. Supplier name. It looks like it came from one table. If you look here, you wouldn't know. You would think, Oh, this came from one table, right? No, it's not. These two came from the product table, and that's a player. Man came from a supplier table, but because of the benefit of the relationship that we did okay, it allowed us to have it to show the table in such a way nicely laid out in an easier way without having the I DS and headaches, you know, because people don't recognize numbers like supplier I d. They recognize supplier names. We wouldn't have the ability to do this if we just had product table by itself. So if you go here and press select supplier and pressed leads on your keyboard, so now we only have a product table. Right? So if you look at the drop down list, you don't have supplier name anymore. We just have supply already, but this is not useful. You know, I don't want to see the idea. I want to see the name and that we're As I said, you have to introduce the second table, which has the relationship with product anyway, So I would put back the supplier. Okay, given that we already put the foreign key and primary key earlier, and you would just select now the supplier name or here you can do it. I think you could delete that. No. Anyway, you get it. So you just put supplier name. That's it. I keep repeating this concert because it's very, very important in the databases industry is very important. You'll be doing it a lot a lot, especially when you did with create ease. You will always join tables, so you have to understand that concept. Now you may ask, Why didn't we just go to product table and put the supply of name directly? Let me show you. Let's just create a copy off that product table, OK? And yeah, make sure its structure and data is there. Good. Now come here. Right click. You know what? Let's just close all tables first and up in the copy. Off product. Change this to supply of name. Change it to change the data. Type two short text. Okay, Now, let's just put any supplier name healthy. Whatever. Ah, healthy Forward bank. Ah, I don't know. As you know, I don't even remember the 2nd 1 Well, it's healthy for doing too, right. 3rd 1 as Ah, eat. Well, hopes. Got it. This looks easier now. At least we have one table with all the information. We don't need to do the joints. Well, this is not efficient in the real world. You know why? Because people remember that you're gonna have a front end software. You're gonna have a front end application where people will be inserting information there . Okay. And as long as you don't want anyone to keep in certain supplier name for the product manually. Because if you type Healthy Food Inc here for this one correctly the next time, the other person might type it like this. Okay, Like, they have to know, Like, this product comes from where? So they have to ask around and so on. And then once they found out that comes from Healthy Food Inc. They may type it like this food and car operation. Okay, so this is this leads to data inconsistency. Okay? And that's a problem. That's a problem. So after some time, because everyone has been entering supplier name man Willie every single time. So look, look, look here. So even though party she a camp from the same supplier, we and we inserted the names differently now, okay, it's not consistent anymore. Now let's go to the clearly design and start searching the ah ah, let's list. Say we want to look for all products products that come from the supplier name healthy food in Corporate Inc. Okay, let's let's say this so ah, just go to create pretty design. Select products. Good. So let's just list the product name. Ah, oops. Not product. Sorry. You have to insert the copy off a product copy off a product table. Good. And then select product name, but in Ah, supplier name. Okay, show. That's fine. But you know what? We just need to look for the for the products that are associated to Ah ah, Healthy Food Inc. So I would come here in the criteria and type in equal put in devil quotation because whenever you have a short text or long text, make sure your content is with them. Double quotations. Okay, but if you have a number, it's okay. You don't have to put it in double quotation. You just put it as is like number. Got it. So if you have a text like short text value, put it in double quotation. So anyway, so put equal. And then let's search for all the products associated to healthy. Ah, food and cooperation Search. Run. Okay, so I suppose we know now we just got one record. So we got eyes. Tell two people A We just go to one product called Proceed Shake from Healthy Food Inc. But is this true? No. If you look at the table, we have another product, right? Him and be comes from the same company sent from the same supplier. But the problem is, it's been a little bit differently, and that's where the problem is. So this is why didn't show up so we could have got wrong. We did not get complete information because of the data and consistency that happened here . So we try to minimize this problem by by doing the relationship between product and supplier. It's instead of type and everything. Every time you type the supplier name, Man Willie, you end up type in the supplier. I d only like 10 10 10 10. Okay, And then this table refers to the spire table and gives you the actual name. If you look here, we only named it once. Healthy Food Inc. That's it. Nothing else. So and we keep referring, We keep referring to the supplier in the product table with one number only. As you see in the product table, I see 10 10 10 and that leads to consistency again. If you go here, let's go to the copy off product table. Okay, this one. Let's just put dot because some people type Inc like this, and it's actually written like this ANC dot OK, now go back to the quickie to the design view and just searched again. Remember, we write it as healthy food bank without the dot nothing comes back. So a single dot didn't give us anything back now, see, But in fact, we have two products here associate to the supplier. Now, I want you to close this close everything okay? And go to ah, supplier table now and ah, no, don't go to supplier table. Hold a second. Let's just create another creative design. Choose a product supplier. Okay, at it's ah, list the product name. Let's lest the product on description. Let's less the supplier name and run. Good. So we want to find out the ah ah Healthy Food Inc only products associated to that supplier , Healthy Food Inc. So I would go to the supplier name and type healthy food, Thank with dot and then run it. See, we got all the parts associated to the supplier. Uh, and I want to show you how consistent it is. So let's go to the supplier table. Remember this one? Let's change the supplier. The name, the name off the supplier instead of Healthy Foods Inc. less type in carp corporation. Good. Save it. Close it, uh, go to design view. So now if you search for Healthy Food Inc. You will not get anything here. Just take this out. It doesn't matter. Just take this out and run it. See what happens? The name off the supplier here go changed too. So we just changed it once in the supplier table. That's it. And what happened? It reflects the change reflected in the whole table. Now, the change reflected in the results as as a whole. So you didn't have to go and change the supplier name for each product or added the supplier name for each product. I just added it. That supplier and the actual table, OK, just edited it once here. And it got reflected toe all products. So I hope that concept is very clear to you. If such clear, Watch it again. It's very easy, by the way, once you get it, that's it. It's hard to forget it. So, uh, in the next video, I'm going to continue with the relationships. We're going to talk about other aspects off relation ships. Thank you for watching this video. I'll see you in the next one 10. 9. Database Relationships - Part 2: in this video. I'm going to talk about many to many relationships. We're going to look into two tables. Customer. Okay, customer and product. A customer can order one or many products, and a product could be ordered by zero or many customers. Okay, a customer like you cannot have a customer. If that customer did not order anything, you know, it would be a customer. So a customer can only become a customer once the customer orders one. At least one product. Okay. One or many products. However, we could have a product here that's not getting ordered by anyone. Okay, so a product can be ordered or a product has zero to many relationship. So a product can be ordered by zero. No one, literally like you could have ragging on orders or many customers or many customers actually came by the product. Okay, so this is, like, many to many relationship. Okay, Customer, you can order one or many products and a product could be ordered by zero or many customers . Got it. Okay, So, uh, let's build that kind off orders. So, to build a many to many relationship, you have to create 1/3 table which will help us know which customer bought which product. Okay. And will help us North. Which product was bought by which customer? So go to Ah, create design. Sorry. I mean, table design and and type in first of all, order number. Okay. And And said that to, let's say or toe a number. Okay. And then you have What now, since this is this is so called a joint table where we will limit older honestly, like the order table will have a combination off customer and products. So let's put customer I d here customer I d and number and then put product i d number. Okay. And then put order dates. Select date time for the order date. And let's put quantity. I would rather put quantity before order date, so insert rose quantity. Uh, number. Okay. So we gotta have a primary key for the stable, which is customer. Oh, sorry. Order number. Save the stable. Name it. Ah, order. Okay, Now let's assume Jack ordered to products ordered. Ah, pretty shake and vitamin B. So come to the order table. Type in the idea of Jack, which is 100. I think the product I D that he ordered is 3000. And what else for 1000. Okay, so but 100 4000 the quantity for 3000. Let's say people like two quantities. The other one is one. And the older dates I don't know. It could be anything you know October 29 and the other one is November 1st. Okay, so it looks like this stable has a bunch of numbers. Only nothing girls, because it's a joint table It it's called a week table because it's dependent on so many other tables. It's dependent on the perk table, and it's dependent on the customer table because without customer and product, we would not have that third table, which is gold order. Okay, it's only belt because we have customer and we have parked. And this is kind of the order table allows us to believe that many to many relationship customer has many products. Circus might be about to products. Okay, great. Now if you look at the product table, we have a serial prior called up excess energy drink, which is a product of 85 5 but no one ordered see, and it does. That's fine because an order because the product has what relationship? Zero to many. So in this case, the 5000 block I. D 5000 doesn't have any order at this moment. Okay? Its has zero customer. Okay. Ah, so let's speak another customer, Sarah, with the idea off 200. Let's put it here. Hopes and Saara orders oil ordered. Let's assume she ordered three. Okay, She ordered any protein shake with a quantity off on a 10. And the data is whatever 30th got it. So as you can see, that same product got ordered, which is 3000 got ordered by multiple customers. 102 100 and a single customer, for example 100 can order many parts. That customer ordered 3000 and 4000. Ok, now let's go to the creative design. Save that stable. Go to ah created design, but an order table and then close groups. So what do you see here? We get the order number and you can get the customer, not I d. You can get the product. I d they quantity the order dates. But this information Scott of you is list to me. What's the point of relationships then? You know the purpose here here is to grab the details off the customer details of approach . So go back to designed to you again. Let's add right click impress Ah, short table. Let's add customer press control and then select products. So now we have three day roads. Okay? And you can see the relationship between them. Access is smart to handle that relationships for you See, order is in between customer and product. If you don't have product you know of customer, you will not have that table. So let's be more descriptive now. Okay? We want to have order number. Oh, you know what? Let's just lead everything here first lead that so we wanna have So you have actors to all the columns. To be honest, all customer columns, all order columns, all product columns Because off the relationships that we built so now are interested to show order number I would like to show Ah custom or oops. Let me just widen this little bit because I can I want to see the full name We want to show the customer first name, customer, last name. Why did this? And ah, we want to show the product name ropes up. So here's the product name. We want to show a product description. Okay? And now let's ah, view that. See, it's it's displayed in an easy, nice way as if it's came from own table. But in fact, these things come from different tables. The order number came from the order table. The customer name, first name and last name. Oops. Sorry. The customer first name and last name came from the customer table. The product, name and description came from the product table. It looks nice. Now I know that Jack has two orders one and two. And those two orders, uh, has a protein shake. And but vitamin B. So if you go back to the order list hopes So sorry. If you go back to the design view off that equity we want to be, we wanna have order dates because we want to see how Maney like, when were these orders made? I want to see the quantities. Of course. I want to see how many got ordered. Now we can view that again. See, it's very nice, easy and what it could do now you could always filter things here, like what we say before in the earlier video where you can put a different kind of filters like equal, you know, uh, certain name or you put like to search a search specific name. Remember, you can Ah, just all of or all kind of filtration. But you may say that. Okay, I would like to see the supplier name too, because I just want to display which, like, the supplier of each product easy. You can come to the design. Ah, view Right. Click press short tables and select supplier table. OK, Done. That's it. So we selected supplier table and remember, rebuilt the relationship between supplier and product. Right? So what do you have to? What you have to do now is you already since you built, you are introduced That table here, the supplier table. You will have access to the column off supplier. So scroll down. But supplier name, That's it. Front. So now we know the product name and the supplier that provided us the product name. So in this video, we talked about the many to many relationship. Okay, uh, in the next video, we're gonna talk about ah equities 11. 10. What is SQL?: Okay, let's talk about clearly design. No, we're not gonna talk about clearly design. We're gonna talk about clearly code, okay? And this part is very important. It will definitely help you understanding databases or understanding databases in its in a different perspective. Gay. So first of all, let's just go to the clearly design as usual and select one table Select. Let's say customer because we love customers, Okay. And then press run e, I think shows up hopes. Let's just select bunch of columns here. I d customer first name, customer, last name. Okay. And, ah, one looks good. So we got these values back. What I want you to do now, right click and select SQL View. So what is sq out SQL or it's pronounced as sequel Sand for structured query language. It's a language where you start writing code to tell the database to give you a bunch of information according to what you want, you know, So whatever we did in the query design can be done through writing codes. Okay, The critic design was the user friendly way to do it, but you could do it like through writing codes. And that's called SQL or sequel and it's been used a lot like this is like, You know, if you use databases a lot, you'll be This will be like it should come. It should be a second nature to you. Okay, But the end result is to get what you want back. So if he could do it through a query design great. If you could do it through here. Great. OK, but I'm gonna give you a hint on this or on overview off that. So when we, uh, requested those three fields through the clearly design what we did basically, we tell we told the databases Hey, I want OK, select. Select what? Select. It's a key word. It's a reserved keyword. So whenever you want to select columns from a table, you have to type select, okay? And then you list all the columns you need so from a specific table. So, in our case, we wanted to select bunch of columns from From is a key word. It is a reserved keyword. Okay, so you select bunch of columns from what? From a table. So we have to define the table first like OK, select from what customer okay. And then what? Watch what? Which columns do we need? So we need customer I d we selected first name, remember? And the creative design and we selected customer last name. That's good. And that we like access prefixes each column name with the table name. So customer dot customer dot customer dot Because access tells you that. Okay, this column customer i d came from the customer table Customer First name came from the customer Tehzeeb customer last and given the customer table because there are cases when he started joining tables together. Okay, you may have same columns and different tables. So actress wants to recognize which column came from which table, right? So in this case, it's fine because we only reading from one table. You don't even have to perfects anything. You can just do this, remove customer dots, okay, and just run that. See, we got the same result that we got from the clearly design. So go back to the sequel view again, and this is called a statement. A statement is one code that does something more specifically and you end a statement, a sequel statement with semi colons. So if you find a semi colons. It means that's the end of the statement. Now, access will not give you an error if you remove the Simic on. Okay. Access smart enough to understand. Okay, this is the end of the statement. Nothing else happens, but you can always put a semicolon too. Yeah, to just confirm that. Okay, this is the end of it. I have nothing else. So run. You see that you want to get now? Uh, there's something called Alias. Alias would be, uh, to help. Helps to beautify. Helps to beautify the field names. So the actual field name for the customer ideas called customer I d. Right. Like there's no space or anything like that. If you want to give it a different name used as Okay, I then tight, like customer number like this. If you want to do it that way Customer first name, you can type as and then Ah, first space name for the last name. You can type as last space name. It's more friendly. Okay, Run Z customer number. First name, last name, but that does not change the actual column name in the table. If you look at the table itself. You still have the same table. You're the same column names, you know, this doesn't change. Okay, so what? We change here? We just changed those columns temporarily for that equity for that specific request that we wanted for that query only. Okay, so this is called an alias. And we put this square brackets because it wanted to put spaces in between the columns. You have to put brackets like square brackets here. Okay? You can like if you remove brackets, the court will fail because there's a space here. So if you're on this, it says boobs, You know, there's misspelled or missing or punctuation, whatever. There's something incorrect because of that space. If you remove space like if you just have an alias without spaces, then you don't need square brackets. Okay, You can just run that like this. Fine. So let's close this. It's fine. You don't have to save anything now. It's OK. Close that or, uh, got to create a design again. Choose customer. And ah, I want you to select everything all over again. Like select customer I D. Customer. First name, customer lost named, and I want you to now select like specific customer ready. Okay, select but an equal and then just put 100. You know, Let's see what we get. Okay? We just got one customer whose ideas 100. Now I want you to view the creating. This is a good way to experiment with SQL queries. Okay, is to go to the design view and, you know, trying to experiment different things. But in different conditions, you get it, do some bunch of joins and that few the creating and see how sequel gives you the code. And then you can experiment with the court. You look at the court and try to make sense of it. So in our case, we added What the condition, Right under customer I. D. So we just wanted to grab customer I D 100. So if you look at the sequel view, you'll find a wear clothes. So whatever we had before, plus a new line called Where? Where Close. Where is a reserved keyword Whenever you see something after where it means we want to filter things now, OK, we don't want to grab everything from customer table anymore, like we want to have some restrictions here. So where What? I don't know what I don't like about axes. Sequel about sequel and access is it adds lots of brackets, sometimes unnecessarily. Okay, I don't believe these brackets are necessary, to be honest, but if I was typing the sequel might myself. I wouldn't put these. And this is sort of brackets at this moment. So where, in short, it tells us, Look for customer. I d That's equal. 200. That's it. Remove all the brackets here. Okay. Easy. And just And the statement with semicolon run this that we get same results. Got it. Now, let's go back to design view and add something else. Like, uh, you know, customers like, Okay, I want to have customers that have the letter off em. So I guess my first name like I really don't care like the you know about. I really don't care how the name starts or ends. I just need to find customers that have the letter m in their names. First name. Okay. That's how we did it. Like em. Okay, start em. Star star means give me everything. Okay? Everything. Anything before m and start after Emma has given me everything after I am. So run. Okay, We got to customers mike and Norman because each of them have has the letter off em. So let's look at the sequel view, Sam. Thanks. See? Select a bunch of columns from customer. Where? What? Where? This remove the extra brackets. I don't want that. So we're customer. First name like em. Okay. And now we When I make sure we want to grab a customer whose first name is like M and ah, their idea is, you know, off 300. So criteria equal 300. Let's see what we get. So we got Mike only okay, Because Mike has letter am and has the custom idea of 300. Ah, but Norman even though, has letter M. But she doesn't have the idea of 300. She has 400. That's right. She didn't show up. So let's look at the court now. Sequel view. How does it look like? So in default oration, we have two conditions. We have customers. Vehicle 300 I guess my first name like them. Okay, These are two conditions. It's an and relationship like both of them. Where can I get a customer that has both conditions met. Okay, but if you choose are or will give you either are we'll give you either a customer whose idea is ah is 300. If you died in or or the customer that starts with the letter or has a letter Am Okay, if you're on this, you get both of them. Let me show you an example. Another example, with or so and sit of customer. 8300. Let's just 500. So we're saying give me a customer. I d whose ideas 100? Okay, if he found that customer, give it to me. Oh, give me a customer whose first name has the letter. I m so either or I don't care. So if you're on that, you get three of them. So you get the customer whose ideas 100 Okay. And oh, I mean or you get the customers plus, Okay, you get the customer whose ideas 100 also. You get customers who whose first letter has and as m somehow. Okay, Like Mike and Norman. Why did we get Jack? Jack doesn't have the letter am there because we in the condition we have 100 right customer we requested either are like it doesn't matter where flexible so secretly say okay , if I find all of these conditions, I bring it and bring you all the records with those two conditions. If I couldn't find one of them, it's OK. You know, I'm gonna give you whatever I find from these two. Okay, so, uh, sequel fund both conditions records with both conditions. That why you got everything like you got does hopes he got those three records. Let's go to customer table, OK? And select and change 100 to, like 600. Just change it. OK, now go back to quickly too. Got to create a design hoops sequel. Views are a secret view. We will not change anything here, so it's still wear sticking to our condition, give its customers hundreds or has letter am Run this you don't we want to go to now. Mike and Norman, we didn't get Jack anymore because Jack is not 100 anymore, you know is this condition is not met Like the condition where customer I d is 100 is not met anymore. If you go to customer, we don't have customer ideas 100. However, the second condition met in all witches has m. So that's why they got back. Mike and Norman only got it. But in case off and relationship Okay, let's just put. And now I just want to make sure that you understand this concept. Okay? And press run, you get nothing, because on relationship enforces that all conditions must be met again. All condition is met. Sense custom. I D 100 is not Matt. Okay, that's it. Even if that just can't second condition is met. You will get nothing, because must condition. All conditions must be met. So, uh, so if you go back to customer Oops and change AC 200. Okay, again, I've sent out again because I want you to make sure you understand that city, uh, was just understand the concept. You can apply it in so many ways. You just have to understand that. Okay? If you understand one example, the rest will be easy. Can do it over and over. And so many other tables. So many other examples yourself. So So I'm saying give me a customer ideas 101st name has the letter m. So even though remember, Jack is 100 now, right? Fine. Let's now run this again. You get nothing. Oh, my God. I think I mentioned that earlier. Why? Because we don't have those conditions. Those both conditions are not met. We don't have a customer who's 100. Okay with the letter. Um, so because the customers 100 doesn't have letter am like his name doesn't have a letter. AM his name has the letter, like, original. I'm here, so nothing met. You know, if you put just end like Jack, I don't know. That's just a jam. Okay, that's crazy. Just for John and run that created again. You get jammed back because you have the customer visits ideas 100 their first name has letter and and their first name has the letter M somehow. That's it. Uh, you can always built a lot of conditions here. Okay, A lot of conditions. You can like the unlimited number of conditions you can have here. Okay, now, let's just look at the ah sequel for joints. How does the secret court looks like when you join tables? Now, let's go to the created design, but in product supplier ad Okay. And select product I D. Product name product description Select. Also supplier name, supplier phone number. Good. And ah, just run the code. Good. So we want to see the cord that created that table. That I mean, that data in a nice way. It looks like it came from one table. Anyway, right, click. Go to sequel view. Ah, uh, what is this? I'm getting scared, Nam, and this looks like it's getting complicated. Anyway. You know what would make it easy? Okay. I'll make it very easy. Better? It's not complicated at all. Once you get it. This is it. It's like driving a car. Once you learn it, it's hard to unlearn it, so ah, just get it once, do some exercises. Its six in your mind. This is it. Okay, So first of all, we have to tables, right? Public and supplier. So an easy way to do it is first for list all the columns that you need from both tables. So in our case, we wanted product I d from the product table. And then we wanted product name from the product table. Of course, we wanted the product description and we wanted also a supply of name from the supplier table and wanted supplier form from the supplier table. Ok, uh, remember, the prefix here is important. Okay? Because if you look at the code if, like, a table in the supplier, we have supplier. Sorry. In the product, we have supply ready to write. And in this supplier, we have supplies ready to. So if you come here in the creaky and just type supplier I d. Oops. Here, the order, The order of the columns doesn't matter. You can order it in any way you like. So a supply I d Okay. And run that, Uh oh. You get error. It is. Hey, hold on. Hold on. The specified field supplier I d okay. Could refer to more than one table listed in the from close off your sequel statement. Okay, so I actually say is generally hold a hug. I know that supplied he comes from product and comes from supplier. Which one do you want? To be honest, it doesn't matter in this case, because both of them are the same anyway. So he just put product dot okay? And or you could put supplier dot Let's see puts apply a dot and then run. You get 10 10 30. Fine. or you could put product dot flying same 10 10. 30. So it doesn't matter. Okay? And that's where having having prefixes is important. Now, if you remove product the prefixes from here, you're not gonna get any error. Why? So let me just remove all the prefixes from the other columns, but he cannot remove it from here. Okay, Because access will be confused. Like Okay, sobriety confirm comes from which table. However, product I d protect me and proud description only exist in the product table on display. Their names of life only existence Supplier table. So axis clever to decide that. Okay, I know where this comes from, okay? These, like, survived name is sent via phone, etcetera. Because because those fields only exists in the supply on those fields. The product fields, one existing product. Okay, so if you're on that, you still get everything back, as is, you know, without a problem. So whenever you have common columns between tables, Okay. Whenever you have Carmen columns between table just like the supply i d. Between product and yeah, just like the supply already in within product table, you have to prefix it with a table name. Okay? Have to prefix it is very important. Now that's it. We're done for this. Now, let's go over next. Okay, so we listed all the columns that we need and then what? I want you to let now you have to list or the tables that you need. So we need product table, right? And to make it easy just expressed space and put the other table that you want in our case supplier. Good. And then we have to decide, Like, what do we do between these stables? Right. We said join. Correct. So just put in type in joint. So we're joining. Were joining Product supplier. Joining what? Like Okay, we're joining. Based on what? Based on the common columns between them, which is supply. Ready. Right. So I would say join product enjoying product and supply on based on. Okay, this is a key word. This is a key word. Reserved Keyword. Okay. On what? So I would say on product dart supplier, I d. Okay, join both tables based on the supplier. I d. That's in the product table. Equal supplier. I d in the supplier table. Okay. And then there are different kinds of joins here. Okay, there's inner join, and there's outer join. So for now, to make it easy, just type in inner join. Okay, close this. Let's run that And that's it. We got the results that were looking for, right? So this is how you do joints, So inner join Basically, basically say's. Ah, give me all the records. Okay. All the suppliers i d that exist in product i d. Which have imagined in supply charity, or give me all the supply I d or give me or the supply information that have a match. And i d with the product foreign key, which is supply already. Let me re explain that, you know, it's a little bit confusing. It's easy. Very simple registry. Explain it. So enter join. We're saying give me all the records, Okay? Give me all the records for these two tables. Okay? Give me all the records. As long as that supply I d exist in the supplier table. So when you're on the quickie, but you get you get everything in the product table, would its supplier idea right? And the description. Good. Ah, but what if we type here 60? Okay, 60 doesn't really exist in the supplier. I d correct it doesn't. We only have 10 2030 40. Fine. So now when you do the inner, join the inner join, guess what you're gonna get you just going to get let me go to sequel view and rerun the clothes to records only. You only got two products that's associated with this associated with the supplier that exist in supplier I d. Correct. But the third product didn't show up, okay, because supplier i d 60 doesn't really exist in the supplier. I d. That's why it did not show up. So we're missing a product in this joint for that freezing. So the inner joint, the inner joined. I found a good explanation. I found a good explanation in the W three schools. I'm gonna give you the link later. Okay? So select old Rose from both tables as long as there is a match between the columns, okay. And since ah, uh, this product has an i d supply RG that does not have imagined supplier table. That product did not show up here. Okay? It didn't show up. As you see, if you want to show the list off products that if you also want to show the list off products that did not have a matching record with supplier. Okay, in this case, if you want to show, ah, that you simply go to the code, okay? And tighten left to join. Think about it like this Left joint says, Give me all the products that I have in the table. Okay? Whether we have imagined the supplier or we don't. So if we have a match with the supplier Great, give it to me. Give me all the products that have a match with the supplier I d. And also give me the projects that do not have any matches. It's fine. So if you're on that code, we just changed this from inner to left. What do you get? We got the two records which have a match with the supplier, right to products with the supplier description, etcetera. And we have also the product the dead and have a supplier name because you know the supply i d 60 doesn't exist in this by a table anyway. But we had it showing here because of the left joint. So now at least we know, OK? Based on that result. Ah, this product doesn't have a supplier. How come? You know, And then we can start fixing in and search, and it's in Why? And and, you know, we had just this and fix it. Right? So that's the benefit off the left joint. Now there's another joint called right. Join. So left joint. Okay, we'll focus on the left side of the table. Will give you everything. Uh, off the will give you everything for the left side. Off for the left yet for the column on the left side. Good. But if you type right join Okay, you will get first of all, all the match and records between product and supplier. This number one you're gonna get us for sure. Plus, you're going to get all the suppliers that do not have, uh uh that do not have ah products. Okay, so we have the supplier here less dead, but we don't really We didn't really order whatever product a product from them or because their new supplier yes or whatever. Okay. Oh, So, uh, so once we did right join. Let's see. What What's gonna happen? Run this So we got to products, which is great. Okay? And we got those two products with a supplier I d. Because we have a match and record between both of them. So those two products again have imagine supplier I d. Okay, But guess what. We have other suppliers, like 2030 40 which don't have any products here yet. Right? So what we got? We got those suppliers. So we got this. This and this even though they don't have products. Why? Because we did the bright join, right? Join may basically refers to the right hand side table, sir. Gives me It gives you everything. When the right 10 start table. You may now ask yourself, you know why? Why? Let's let's just run this first. Why the supply? I d doesn't show up for the supplier names, those three suppliers names. I'll tell you why. Because if you look at the code, we're getting the supplier. I d from the product table. Ok, and so far, if you look at the park table, we only have two suppliers ideas Here. 10 10 the we only have two matching supply authorities. Okay. 60 doesn't exist in the supplier table. right, So we only have 10. 10. Good. That's why we only see, uh, two tens. And we don't see the other ones because this supply ity comes from the supply or table, Remember? Sorry. This supplier, he comes from the product table. It comes from the product table. So let's go to the queen and change that. Let's type in supplier because I want to make sure the supplier I d comes from the supplier table. OK, not product table. So run this. Get it? So now we have or the supply our I ds here. Got it. Now you can actually go and start investigating. Why don't we have products for these suppliers? And how come, like, you know and the company would start investigating. How come we have ah supplies? We did an order from what happened to those products That why it's empty? Did we delete them by mistake? What happened to the system? And you can end up and fix things up because probably you're not supposed to have a supplier without product, right? So let's go back and change the joint to inner join again in our joint, okay? And run this or before you on that. Let's just go to ah ah, The product list and change 60 to 30. There is one concept here. You could actually add a constraint on the supply. Our i d. So it doesn't allow you to add any number you like. Okay, this is just called referential integrity. Okay, You can impose that somehow. He wanted so refreshing. Integrity means you're informing the table. Hey, please don't allow as don't allow us to put any supply i d. That doesn't exist in the supplier table. So in this case, we have Ah, that's just put back 30. Okay, I'm gonna talk with you about the referential integrity later on. But you know what? Refresh, integrity, something. It's a I mean, we can talk about it later, okay? And we for now, just put it 10 10. 30 design and run. Good. So let's add conditions now. Right? Let's just see how it's gonna look like once you had conditions, so I'm looking for some. I'm looking for records. We're ah with supplier or supplier 300. No, Sorry. With was supply attempt brunt. Good. So we're getting two products. Look at the code. See the wear clothes comes after the joint. It's not before you first joined the table, join the tables and then add the wear clothes. The were close always comes at the end. Okay. And here we're just filtering supply. I d equal time. Now let's do some sorting with that designed for you. And then let's soared by on Ah, product. I d ok. Ah so sorted in a descending order and then run this okay? And look at the coat. So we have another line called order by as a roll of film Order by always. Come last. Okay. Nothing comes after order by as a rule of thumb, whenever you write codes, no matter how many Koji have here, Okay, order by always comes at the end and it's optional. If you type order by, you have to mention the column that you want sort by and the kind off sorting, ascending or descending. Okay, so you join you filter out two things and you add the order by order buys optional. They were closer is also option. But most of the time you use a lot to forecloses. Anyway, you have to have conditions and so on because most of the time. You don't want to grab everything from a table. Let's talk about referential integrity to enforce referential integrity. You have to man will create the relationship. So first, uh, what we did Thea created design. We just selected the tables we want and the relationships is getting built. Right? Groups by access, but not Ah, what do you have to do is click on that link. Okay. Make sure it's it's it's bold id and then double click it. So you're gonna get on option where it tells you. Okay, how do you want to join? You wanna do the inner join? You want to do the right join or the left joint? OK, It's just a different in nice way to say okay, here. It gives you the explanations, but basically, number one is the inner join. Okay, that's fine. And here it tells you how you wanna join. Like it's already been pre built for you, where the left table is product in the left column that we wanna use to join the other table supply already right, and the same with the right table supplier and then the column that we want to use to do the joints supply already. So this is the common common between both of the tables? That's fine. But here doesn't heart have on option for the referential integrity. So to do the prevention integrity, let's just recreate the relationship, that same relationship. Just press new now. Okay? Now it tells you. Okay, What kind of relation do you want? You know, we say the same. Like we want the left table as a product and we want to select the supplier i d. As a column that we wanna join with the supplier table. And of course, we have to choose the supplier idea. This is the common column. Now you press next. Okay, so once you press OK, it looks like Ah, that's weird. Okay, let me check here because I was expecting to find the option of referential integrity, so I'll have to do it in a different way. Okay, let's just delete that and ah, you can go here where we're living. Just remember. Ah, just cancel this quickie and then go to a database stool, Select relationships. Okay. Ah, and then choose product supplier. Highlight both of them at Okay, that's good. So since we chose relationships. We have now to do it manually. Okay, If so now, uh, right Click here. Oops. So they don't write like I usually do. Relationships and write codes. And Oracle? I don't do it in access, so Ah, an access. You know, it uses lots off. Ah graphical user interface sometimes. And I usually do it through court sequel Corden Oracle. But anyway, it once you find out here, it's easy. So press edit relationships and then choose the table you want. Oops. I mean, create new first. Of course, you have to create a new relationship. So its product on then you choose supply I d like we discussed earlier. And then supplier supplier I d. Ok, okay, that's good. Now choose enforce referential integrity and that's it for us. Create. Now you have the relationship between both tables and ah, close everything. Let's go to ah product now. Okay, so nothing changed. Really Go in here. You have supplier, I d know. What about the plus? Now forget about it and we have products. I mean product table. So even though the columns match right between supplier and product But now we added a feature called, as I said, referential integrity where you cannot really add any I d here anymore. If you put 500 you're gonna get an ER. It says Excuse me. 500 doesn't exist in the supplier table anymore. You know, it's not there anyway, so it tells you please change it to something that exists in the supplier table. OK, so this allows you to control the quality of the data that you are uncertain. You insert garbage, you just insert what you can insert. Okay? And that's ah, good and important. So? So if you go to the supplier since we built some what off the relationship manually, we enforced the integrity. The table of supplier became a little bit more friendly. Now you know you can You can easily put plus and see what kind of products under the supplier. Okay. And what kind of products under the supplier. So this supplier 20 doesn't have any products. What kind of flowers? Under the supplier. Okay, this is an extra feature that access gives you but the official way of finding out things finding out. Dad, I finding out how many products associated with the supplier, etcetera has to be done officially, honestly, through queries. Okay. Through clearly design and sequel codes that we discussed earlier. That was a professional. Right? Okay, this this is just a helper. Sometimes you want to find something quickly. Here helps you find out. OK, but this. Ah, but the Creeley approaches the professional way of doing it in the correct way of doing it . Ah, and last but not least, uh, the inner joint. The energy is called inner join. But what about left and right? Join the left and right joint are called, uh, outer joints. So whenever you hear the word outer, Okay. Outer air. Absurd. O U T E r outer joins. It needs either left or right so it could be left out. Or joint or right? Outer join. Okay, this is just for your information. And one more thing. There's a relationship called 1 to 1 relationship. It's not commonly used, but I'll just mention that to you. Okay. So far, we talked about one to many relationships, many to many relationships. And these are the common ones, and you'll be using it all the time. Okay. Understanding the concept will help you also analyze tables and see what's going on. Okay, so 1 to 1 relationship is when you have 1 to 1 relationship. Like Okay, we have customer idea, right? 1st 1st name, last name, customer or date of birth, etcetera. But sometimes you have sensitive information. 12. 11. What is a Calculated Column?: lo were almost reaching the end of the course in, uh, I want to talk about calculated column. A calculated column, as is used when you already have columns that allows you to create 1/3 column. Okay, out of the blue. What does that mean? Okay, I'll tell you something. Go to customer table and look at the date of birth. Like through the date of birth. Who can know the age? We don't really have to put an age column here. You don't have because a changes. So if you put age and put number Okay, it's good this year. Okay, Let's just go age number and then hopes. Sorry. Okay, so in age, you put like 44. Whatever. You know, 50 52 50 50 What? 54. 54. Ok, so 51. 54. Uh, what happens next year? Are you going to go back to the database and keep changing that, you know, every single year? Let me. Come on. If you have thousands of customers, you're not gonna do that's what every single customer, it's gonna be tiring, you know, every year, just going change ages, that's all. You know, it's horrible. okay. And so what we do is, uh we just leverage the date of birth field. We used the date of birth. Feel too make up ages. So age is the age keeps changing according to whatever did for if you have so let me show you something. Ah, let's go to the query design. Let me close everything first, create a design. Oops. And then put in customer. And now, but customer name lost name. Ah, we don't want really date of birth. But how can we put the age then? Oh, so this is where the calculated column come comes into place to define the age of the person from the date of birth. You just take the date of birth. Okay, That's really take today's date. So there's a way to get today's date in access easily. You can just type date, okay. And then ah, open a bracketed closer practice. This gives you today's date. Okay. Minus what? Minus the customer. D o b date of birth. Ok, so and then this is the formula hopes. Don't worry about e expression one. Don't worry about expression one. Just delete it now. I don't want you to be confused. Open a circle bracket like before date and closes certain. Or come on and close the circuit. Brockett after customer dio be okay. Good. And then, like we're adding some calculations Divide by the number of days in the years 3 65 Good. So run the coat. Okay, so you got a customer name, customer, last name and he got the age off each customer Got it. So it's rounded. It's a Sorry it zin decimals weaken basically remove the decimals. Come here. I want you to remove expert Russian one. I'll explain it later. Okay. So, Teoh, remove the decimals. You have. I think it to use a trunk. Trunk function trunk means truncate Any decimals that come out off the calculation. Any decimals within the number, the number that you have. So I just enclosed the whole formula in brackets. Okay, So trunk up in the bracket and then close the bracket of the foreign here after 3 65 Now, on this hoops. Oh, it looks like we have two minute brackets here or what? Let me Jack drank. Maybe drunk is not axis friendly. Yeah, I think after you something else. Really? So to, uh, to remove decimals. What you could do is round, for example. There are so many built in functions in access where you can round remove destined worlds. You can floor a number you can you know, you so many things. You can define the number of decimals you want to display in a number, so these are called built and function like crowned, truncate, etcetera. So to make this simple, it just lets surround it. Okay. And, uh, I'll show you like you have to enclose that every function comes with two brackets up women in brackets and clothes and brackets, and you have to put a lot of a code, isn't between whatever card you have in between the brackets around up in a bracket and then go to the end and close the practice. Okay? And then run the code, See? And he got these numbers around it. The ages so but here it chose you as expression one because we added a calculated column. This is not a belt and column that week is not. The column is not part of the table. You get it. It's not part of the structure or the table design, so we have to give it a meaningful name. Expression one is just a default name given by access. Let's name that age. Okay, so now it makes sense. Got it. So no matter how many, no matter how days change etcetera, you know your age will be changing with the years automatically based on the calculation that we did. Okay. Ah, that's what so called calculated column. Ah, to know, built in functions within Ah, within ah access Go to this link. OK? And basically, you know, go through these different functions. There are so many functions with you can learn as you go as you develop and work with access, then you will find what what punches you need. Okay, according to whatever you want to Dio So just come through this to get an idea. What kind of options do you have here? Okay. Ah, Now let's find let's do another calculated column. Okay, Lets just have another example. So let's create the clearly design. I want to join the, uh order with the cast temps cast Timur compressed control order and ah, well product. Ok, good. So now let's just list in the customer name Oh, a customer I d And then customer. First name, customer, last name hopes, no customer. Last name. And then we want to list the or their number, for example. And ah, the product name and ah, oops. The quantity that's been ordered for this order. Ah ah! And then the units price. Okay, got it. So let's just run this and see what happens. Good. So we know that this overdue was ordered with this customer. He ordered to, ah, protein shakes in the products unit prices in 1999. Let's calculate the total. Now it's pointless to add a total field. Okay, it doesn't make sense because because the quantity can change. Okay, the regular price can change in the product section. You know, today's 1999 tomorrow it could be like I don't know, you know, $10. So so either our It's whenever we have, like, unit price and quantity, we would use calculated column, a calculated column. Okay. Whenever you have a field that's dependent, it's one of whenever you have a field. Yes, which will have values that are dependent on another columns. Okay, then probably you need to use a calculator So in the age section, we have well, in the customer's table. We have age, Okay, but age is dependent On what? The date of birth. Okay, we can recalculate age. So you don't need a judge because ages cut off dependent on the customer date of birth. Okay, same thing with this. A total is basically quantity times unit price. That's it. So total in it of itself is dependent on to other columns in the table, which is quantity in unit price. And this is how we define okay? We don't need really a total column in the design in the table itself in the order table. No, What we need is we just, uh you know, Total would be calculated column. Not because it's dependent on to other columns in the table. OK, now let's go back to ah, see designed for you and add another ah field called quantity Times unit price. Okay. And, uh, that's it from Got it. Can you see that we multiply this by this? You get that good. Let's give it a minute. Let's give it a meaningful name. Okay? Instead of going to design view, I mean, I could do it from here. I just want to show you the sequel cord now. Good. Or, you know, I'd Let's just do it three designed to you quickly. Ah, design view. It's Ah, cool quantity. Well, I mean told you have to think about it right away. So here. Okay, that's it. Very easy. Very simple. I'm gonna show you now how we did. For example, where did the customer age? Uh, what I can show you. Ah, the ah hopes. The secret view. Okay, uh, wait his secret. So, uh, so as you can see in this equal view, we have, like, multiple columns go joint. So we have customer joined to order. You get it. So you have You can have. This is a good point. You can have multiple tables, but you can join multiple tables. Okay. Not just to table even the court, like you saw that. So here we have customer order and product and we joined them together based on the common fields, like you see here. So it's common sense. So we joined customer with order based on customer I d. And we joined the order and product based on the product idea and then we listed all the columns that we need, but we need it. Okay, so far, so good. So until here, this is a calculated column. This is how it's written in the coat. You type in the column that you need quantity and you put the ah mathematic art America operation, which is times you can put. Plus, you can put mine as you can, but whatever. You know, this just put times unit price and give it an alias. Don't keep it empty like this. You could Okay, if you do that, I think we have Ah, yeah. Forget about it. Let me live in. Just go to design view again. Okay? You know why? Because I changed two things here. Like I deleted some brackets and that what caused the problem? Let me just posed. That's why I don't like, you know, right in lots of inner joins in access because it's a bit more complicated than you know, auricular sequel server because access demands for some reason, some weird brackets here, the in the front of it, and it doesn't make sense. Okay, anyway, so let me just pose that and put in the proper bracket Okay. Anyway, I put bad I put back the bracket, which I had to put it before customer And here. Anyway. So now if you're on that, we removed the alias. Okay. On this, you see, expression 1007 doesn't make sense. You have to give it a meaningful name, which is an alias you just put as gay. And they put total. Okay, So this is it about calculated columns? Ah, in the next video, we're going to talk about inserts, updates, deletes. And this is about it looking forward to see you in the next video. 13. 12. How to UPDATE, DELETE, and INSERT Data?: I'm going to show you how to update record, how to delete and how to insert a record into a table. So, uh, let's just go to the creative design and choose customer. Okay? Now, what we want to do is select update good, and then choose which feel you want update. Let's say one update region. OK, that's fine. And we want to update region To what, like, let's look at the customer table. So, so far, we have Northwest, west, south. Let's update east of data to east. Just run that. Okay? Designed. Run. Oh, uh, it's a dating four records. Let's pray. Let's press. Yes, this is dangerous, You know that. You want to show you. Oops. We screwed everything now. So all customers exist in the same region. No, this is not true. It's supposed to be northwest, west, south or north, you know? So, uh, that's a problem that you may face Where, uh, updates. So you have to make sure to restrict What do you want to update? You know, we just want to update a customer region for the customer. 400 only. Okay, not everyone. And we have to mention that and the creative design. So let me just close this and close. That said you want to save it. No, we don't want to save anything. Okay. It looks like screw things up already. So let's just rename things, OK? Go to the customer region and name anything like north? Uh, I don't know. West West. Ah, And then you know what? Self good. Close this. So let's go back to the query design again and do the same thing at customer. Okay? Make sure you select update and then select the customer region. Select what you want. Update. Now let's decide. You know, we want to update customer ideas. Region. So from south to east, I would come here and type east. Okay, update to east and then in the criteria select. Ah, Where? Guest Timur dot Okay, customer dot Customer, I d equal 400. Got it. You can do that. Designed Run. Uh oh. What's going on? Days dal. A type mismatch. We have customer idea, right? And customer I d is a number. So we shouldn't enclose guest Tim or I d in a devil quotation because an American value should be just listed as is okay one of you put something in between quotations. You're telling the you're telling axes that this value is a text or short text, which is not like because customer it is a number. So you don't enclose it with develop rotation, a single quotation. Just keep it as is and press run. What's going on? So these are the kind of urge that you would be getting sometimes, you know. So in my case, you know, in this case, you know, you can simply simply just press run. Of course, you get gala tight mismatching criteria expression. I'm sure that type is not mismatched because we provided the number. Eyes is without double quotation to the customer I d and get some ideas. A number and re provided is a number. I think his actress keeps putting it in devil quotation. So what's the problem here? You know one easy way to do it, especially if you're very familiar with the query. Later on. Once you get familiar, query is gonna be very easy for you to no problem. Just go to sequel view was he was going on. So first of all, let me explain how the creator looks like for the update statement. Okay. First, you just type in, like update, and then you choose the column that you want Update one update customer. Okay, that's good. That's it. And then what? We want to set which value? Like what? Do what column do? Wanna change? So you put the keyword set. Okay? Now choose the the column that the column name that you want. So, customer region. Okay. And then what? What value do we want in this case? Won't east. Okay, good. Then if if you just run that, you know it's gonna get everything to east. That's why it's very dangerous. So what you have to do is make sure with date he put in where close. Ok, where clothes were. What you have to define like only update east off the customer. I d 100. So I want you to forget about that. Okay. So you're type customer. I d equal. What? 400? Okay, run this. This is how do you update? Ah, quit table. Very easy. Very simple. I like to do it through the quickie faster for me, but it's really up to you. Okay, So update customer and then you just run that it say's You're gonna be 81 record. Fine. I did it. Good. So let's go to the customer. See, Customer 400 has east Now, that's fine. Now let's go back to design view. Uh oh. See what happened? Think change now. So it's like advisers Ah ah, relationship here If you write the code and you see how it looks like in creative design advice versa. Circles like what we did before was kind of fraud. You know, we're supposed to do with this way in creative design where you put the customer. Of course, you put the customer region customer table and he put the value you want. And what if you want to filter? You have to make sure the the column that you want a filter isn't selected here and it's on like place. You don't put it here. OK, put it here. The only thing is, you keep that No, you keep up to date as empty because we just want to filter out the criteria, which is customer I d 400. We don't want to put a sign any value to the customer. I d OK, so if you like, let's say you put 500 here. No, no, no. Let's 1000. OK, guess what's gonna happen if you look at the code you're gonna find the code will assign a new customer i d. So it will look for the customer 8400. It will update its region and will update. Also discussed a variety from 402,400 will not exist anymore. Once you run the update, you get it. So in our case, we don't want update the customer idea. So we keep this. We keep this empty and just put the right here. They cried here. Yeah, well, we want to filter on or based on. Okay, you can fix her with so many other criterias you can like filter. Based on customer I d 400. And you know, the customer has to be active. Okay, so the status here would be either one or zero. So if the customer is active and 400 updates region Okay, one, it's true. Sermons falls. Force me is wrong, you know? Anyway, so if you look at the code secret view so we have two conditions. It says that if the customers 400 and he is active or she is active than update its region . Otherwise, don't okay if you're on that. That's true because the customer is active anyway, right? And 400. So if removed, if you remove the customer here. So if he said, is active to know it's not active, you remove the activity and change that. Let's say to any. So we're saying if the customer is 400 the customer is active, change that tune. Not available. Run this, it says. Ah, I'm not dealing anything. Why? Because we have a customer. 400 as you see. But the customer 400 is not active, is not active. And the cold was looking for the what? The second condition, too is was looking for the 400. That's actor. Okay, that's why nothing got updated. Got it. So now let's move to the other part, which is insert one way easy way to insert a record is just to come to the table and do it , you know. But this is not how you do it all the time or professionally. Do it. This is not what you do. What you do is you do it through quickly design you do it through. Ah ah! Sequel cord. Okay, this is how you professionally unofficially updated databases. So let's go through the query design again. Close customer first. So go to the creedy design. And to be honest, I don't know how to do the insert in the creative design. I know how to do it through sequel court. Let's just do it through sequel and see how it looks like and they clearly designed Okay. Ah, so just let's say we want to add, like, add records into customer table. So right, click Select and Select View. I mean, select sequel view, type and insert. Oh, okay. Insert into and then typing customer and then up in a bracket and list all the fields that you wanna concert, which is customer I d. We want to insert customer. I d. We want to insert customer first name. You have to explicitly mention the field names that you want. Insert first name, customer, last name. And do you know what we gonna insert is Act okay is act. That's it. So Okay, Now, how can we added value into that customer? So what? You have to do is stop in values, OK, up in a bracket and then closer. Brighter. Okay, so insert into is a key word. Values is a keyword or reserved keywords. This is how you insert into table. He taught concert into with any tapping the table. Name up in a bracket in list all the columns of names that you have to see that you want to insert into and then type in values in that structure. You know, Tabin values up in the bracket. Now list the record that you want to put in, So I wanna insert customer 601st. Name would be like James. Last name would be Ah ah, Davis. Okay. Ah is active. Set it to true and put in a semi column because all of this is a one statement. Okay, this is one statement you can add new space of new ah line if you want. Okay, So if you run, it says, Hey, we're gonna penned one roll into the customer table. Say yes. I want that. So go to the customer table. You know what? Why? It's not their hopes. You know what? Let's just double click the customer table again. Yeah, it's here, So Ah, that you see? And this is empty. This is empty because we did not provide anything. And his activist checked Mark because Because we provided it as proof. Okay. And, ah, this is how you insert a record. Let's see how this looks like in the query design. I myself don't know. Honestly, I usually write secret codes raw secret codes to do some database operations. But the design of you want to get to your Stewart that makes you faster and fast, fast in and doing things. This based on what? Based on what I saw from my coworkers or some other some co workers. Okay, Design view. It looks like this. Okay, I okay, it looks like this. If you want to insert into a customer table, then you have to do it in that way. Okay? If you want to insert it through the clearly design than you would do it in that way. Now let's move to the next step, which is? Deletes a record. It's very simple, by the way, but it's very dangerous. If you misuse it, I'll show you how so. Let's go. And you know what? Before deleting anything. Let's create a copy of the customer table. Right click. Copy. Right. Click paced. Okay. Okay. So let's now, uh, delayed something from the customer table. I would type and delete. This is how you first, this is how you This is the approach you type in the late 80 litres. A reserved cure, you know, and then delete what delete from customer. So it's a delete from is a key word. From what? Fridge table customer. Okay, then what? You know, if you do the great customer, let me just see. Okay? Delete from customer. Run. Oh, my God. It wants to delete every single thing in customer tables. Very dangerous. You know, That's why I backed up the copy off. Back to customers. Copy. Because if I pressed yes. Guess what? Everything would be deleted. I'm gonna do this. Uh, okay. This is very good thing. I like that. Okay. Anyway, so first of all, it's told you it's gonna delete what all record six records from the customer table. And then you pressed Yes. It says, you know what? There's a problem here. You cannot daily to records because there are some of these records are locked. Okay, Are associated with another table. Just press no. For now. Okay. Press? No. So what do they mean by delete toe? They cannot delete two records. Uh, go to, ah, customer table. Okay. And go to also Ah, order. Got it. So, first of all, there's a lady a relationship between guess Timur table and ordered a but right, So we have a customer. I d here in the order table. And this customer I d is linked to the customer. See? So if we delete all customers by mistake, what would happen to the customer number that's in the older table? You get it like this custom. I d will not have something to reference to because we delete it cause all customers. And that's why access somehow showed you an error and say no, no, no, no. You cannot delete two records here from customer table. What do they mean? Baidu records. You cannot delete customer ready 100 in 200. Why? Because they exist in the order table. Customer. I D 100 consumer, 8200. That's why. Okay, Unless you go and delete those customers, then you'll be able to delete everything customer table without a problem. Okay, so let's go back. Delete from customer. So, uh, did it from customer where we have to define like you don't In reality, don't delete everything from a table. OK? You have to decide what you want to delete. So I want to delete customer 600 were whom? We just add it because realized this is another customer. We thought it's a customer. It ended up being an employee or something. Yeah, whatever. So so did it from customer. Where? Customer I d equal What? 600? Okay, and I'm not make sense. So go design Delete. It is its candidate. One customer. Fine. Go ahead. Deleted. So if you go to the customer table see, it's not there anymore. Close it and open it again. You don't have the customer animal, which is fine. This is This is what we wanted. Okay, let's look at this in the design view how it's gonna look like I also do the deletes from the query, not the done exam you. But if you can do it through the Zambia Great. You know, design view. So it looks like casting where I d It looks very similar, Teoh Update somehow. So to do it through design view, of course, you need to select the delete first. OK, you select the delete option and then you will get this and you start populating the fields according to what you want. So we wanna delete from the table customer. We look for the field customer I d. And ah with the i d 600. So we're gonna delete the customer with 600. Okay. This how you do it? Ah. Anyway, let let me just let us just go crazy and treat everything from the customer table. Run. Yes. When I delete for record say, Listen, do you want to proceed anyway? S idea? Fine. Let's proceed. Screw it. So what happened is we proceeded. 22 records get deleted. Let me just close this and open it again. But to did not get deleted because of whatever in the order table. Got it. So what I'm gonna do is I'm gonna create a copy of the order table. I just want to assure you paste it, Just create a copy first. Okay, Now let's go to the order table and delete, uh, doors customers like we don't want anything in order to highlight everything. This is a quick way to do it and press delete. Okay, just for the sake off this video. But in reality wouldn't do that. You do it through the court, acquitted design or sequel court. So we don't have anything in the order table. That's fine. Okay, so now let's go to the query that we have and delete from customer. It says you want to delete to say yes. It says, No, you cannot delete what strong? You know what's going on. I'm going crazy now, See? So now, Because I know customers were here and I deleted them. So what else could be Oh, okay. I remember. I remember. What? The customer Private. Okay, what's going on? There is a relationship between customer and customer private, so if you deletes, Okay, if you delete an order. Sorry. If you delete a customer okay. From this customer table. What about the customer? Private? You know, they're somehow related. What's gonna happen to this? That's what. And that's the benefits of referential integrity. It doesn't allow you to do some stupid mistakes. OK, it comes. It's kinds off protects you but protects your data and maintains your data integrity. It reminds you when you do things wrong. Sometimes you know. And that's the beauty of referential integrity. And that's why you use databases to maintain, store and manage databases in a proper way to go that I can put back all the customers into their own tables again. Let me just close everything. So all you have to do is up in the Ah, sorry. Up in the, uh, a copy of the customers, take a copy of this. Go to customer table and paste it there. I already did that, you know, away from the video. But you can do that now, okay? And then and I'm game. Bitch would work. Fine. OK? And then for the copy of the customer, OK, you would go back here and then you would just paste it, okay. And ah ah, Same thing with a copy of the orders. You would just take this, remember, because we deleted everything. I would just take the copy and paste it here. Got it. So and now it's up to you. If you want to keep the copies or delete them, I want to just delete the copper is from here. You cannot delete the table. If the tab is open, you have to close the tab. That off the table that you want to delete. So I'll just delete all the copies that I have. These are this is just, you know, I'm just cleaning things up here. Now, that's all. Whenever you have a quickie, whenever you're right to create through the creative design or or you just type in equity, you can save it. Once you save it, it will look like this. Okay, you will see it under queries. Lest you can name the created anything descriptive in our case, the screen. He gives you all the West Central orders from the excels orders. So if you double click it, give it back to you. You see, you can easily like, save these creators and then right click, choose design view, and then and then right click and select select sequel view to see what's the code behind this creepy You know, you can keep saving. The screen is for future of friends and for, you know, just Teddy or to review it back and future, and quickly you know you can just you your code and that's it to remember a few things. Okay, Uh, honestly, where that's it. That's the end of the course. By the way, I'm going to just shoot another video to give you an overview and references that you can use to learn mawr and proceed with your learning journey. It's very I was very happy to teach your guys and see in the next final video. 14. 13. The End: Okay. Congratulations for making it up to the end of the course. You're doing a great great job. I hope you learned a lot. I hope you enjoyed the course. Okay. What you learned is the core fundamentals that you need to be productive in the workplace. I gave you the jests, okay? And and you can take that fundamentals and just run with it and, you know, build your skills on that. Okay? Ah, the I strongly suggest you to keep this as a reference, if you have, you know, you want to learn a bit. Mawr, Uh, you have a question about how to do a few things. You can definitely come to the source. It gives you many things that it will benefit you. Okay. And and luckily, what I told is, most of it are here. And what I told you is the fundamentals that you can build your skills on quickly. Okay. Ah, there are so many futures, Maddox. Things, you know, function built in functions, etcetera. You will not use them every single day, every single time. You'll just use them according to whatever you want to do. And this is something that you will be just learning as you go, you know. And the good thing is what you learned. And the scores is honestly applicable to other database management systems, which, in other words, it's applicable to other platforms like Oracle, Teradata, Secret Server, especially the sequel code. Part of it. Okay, that concept of inner joint left joint right joint the filters were closed. The wear clothes filters like and equal, not equal, You know, bigger than less than the concept of calculated columns. The primary key foreign key. All of these are Dell abyss, strong database fundamentals. You know, if you understand it, well, you can grow very fast in that industry or, you know, in your job you just have to build on it. And you know what? It it. Soon it becomes a second nature to you. The more you do it, the more it becomes second nature to you and this reference kinds off really somewhat inclusive. It's it's very good. Helpful. Uh, so I hope, guys, you enjoyed the course. And you know what? I have some few courses in the pipeline. I'm planning to ah ah, that produce, whether it be for databases or whether it before software development or Web development. Overall, I'm looking forward to share more off the knowledge, and I'll accord also guys to having you in the coming courses. I'm open for questions. There's no stupid question. You can always ask. Okay. And as long as they get identification, I'm gonna respond back as much as I can. So there are times when I don't get notifications from you to me for some reason why get questions and I just find them a chance when I log in. So, uh, if I find your question, you don't I will answer. Okay, guys, I look forward to seeing you in the upcoming courses. Have a good day.