Complete MySQL For Beginners

1. Introduction to The Course: Okay, everyone, before we go ahead and start this course, we need to illustrate the point that we're going to discuss in this course. The first steps that we're going to do is to understand the idea off relation of the debates. Then we'll see how to install the my sequel that's him attacked, arrested that we have to go through force. Then we're going to get familiar with the Mice Equal Work Bench, which is the graphical user interface that we will be using to manipulate the my sequel database engine. Then we'll be creating also my sequel instances on some of the basic or the famous cloud service providers like the eight Every House and Microsoft Other. And there is also one of the important lecture that we're going to cover. Then it will be time to work with scheme our users and heavens. I'm going to see how to grate on drop database that they were going to see how to create users and gives them certain privileges on certain tables. There will great tables. Tables are considered to be the heart off a relational database. Everything is around the table. Idea data is stored in the tables going to perform select statements on tables going to before different operations on tape. So that's why it's considered and the heart off the database. We will also see the primary key and foreign key, and this is also one of the main concepts and relational databases. And, of course, in my sequel, we don't understand what is a primary and what it's a foreign, and once the difference between them and how to use them in different ways, we're going to create primary keys using my ex together work bench what? You're going to be much easier. I don't going to see how to great. The King is using the sequel script, which is also very important here to learn. We're also going to see how to implement data using the Maya secured workbench, and we're also going to you this inboard feature two great tables without us typing any code. We also going to export some data out of the database. That's also very good feature that the bicycle is offering us. We're also going to generate the are the or the entity relationship diagram, and this is one of the most important concepts and relations debated the RD When you're designing a large database, the already will help you track the different relations between table because it's going to be a mess. Then we will see how to select statements, how to retrieve data. The select statement is probably the most common used statements, and all of the statements is we're going to discuss and we need to master this statement. We're going to see how to add a wear clothes, how to order by columns, going to see how do you the separators, armed or not? Is no. And many others. We're going to see how to you the lychee Ward, which is very powerful. It's magical, actually. I'm going to use how to you the wild cards with the like they were going to go to more advanced it select statement. Such add the distinct which we're going to use to select the unique values on Lee off the database records that we have going to see the end and in between union and Exists, and the amazing and powerful cases statement on the little complicated sub queries. Not only this, we're going to discuss also the aggregate functions on D d m. L don't understand. What the DEA Ella, We're going to use e built in functions line the minimum maximum count on average, we're going to use grew by and having, And these two points in here are going to take your skills to next level. You're my sequel is clearly going to be performing amazing reports using these two points in here these functions and the group buying heavy there, we're going to see how to glow the table. I'm going to use the insert, delete off date statements to manipulate the data in order, debates all of these concepts and they are going to be with enough exercises so that you can practice these concerts until you master them. Before you go ahead and saw this. I want you to be fully prepared on. If you have a problem, please let me know about it. I will try to answer as soon as possible. So that would be at let to go ahead and start mastering my sequel 2. What is Relational Databases and MySQL: Let's start with what is relational databases. Someone is my Estivill. A relational database simply is. A collection of data items was pre defined relationships between them. These items are organized as sets tables with columns and grows. Tables are used to hold information about the objects to be represented into database. As you know that we haven't plea Jennifer, for example, you know, for how the first name last name course on the first date building number he's living in ST City. A job, a company, Hard eight and the job. It's health has some information, the job name, the minimum salary in maximum salary and other elements that we can have. If you think of them, there is some kind of relation. But when all of this information so we can say there is a group of data or a table for the information about Jennifer by the first name last name and the birth date, when we get at some of the elements like the man for number, national will be, I mean, even the others. We could have another thing before the addresses, which will recall all the addresses where Jennifer is living the building, the Street the city because these items can a chain. It's Lincoln, with the first people off the first name and last name using the I. D. This I d can be a Social Security number or random generated number. We can have another thing before the jobs, the job, the company, the hard eight and we can't have another table for the information about the job itself, containing information like the minimum salary for the job, the maximum salary requirements and many others, what is Stu As yellow or structured query language, it's pronounced s feel or sequel is the primary interface used to communicate with religion of the debases SQL, the standard language for religion in the debases management system, such how the post agree sequel, My Scale sequel server, Oracle on Maria DB and many others. Sequel statements are used to perform tasks such as a day data under the base or retrieve data from the database. There is many other statements, like the select lead concerned, create drop and many others. Little leaks be exploring a lot of disk amand this and the upcoming lectures 3. Install MySQL: Now let's see. How can we stole the money to kill? You need to go to the euro in here. They've thought bicycle that comes last their loads or simply driving Google bicycle that lived and go to the first link, which would be my scale dullards. You can go load the bigot additions off the my scale, but we're going to go for the community addition. This is a free addition off the my scale. Choose the community server, Go down and choose your operating system. There is bunch of doing here. I'm going to choose one is what I'm going to press this like on old this puzzling. Here, go to the load page, go down and choose from these two options. This is the smaller What? I'm going to use this one. The web, as you can see near my skill in a stolen with community on my scale Is Stoller community going to choose one and alerted. I have the really deluded in here, so I'm going to choose it. When now is in, a stroller will be lunched. Here it is. I'm going to leave it for the developer defaulted Going to install a couple of things that I need. The bicycle service, of course, is the most important one on the by scale workbench. This is the graphical user interface that we will be using to interact with the Maya secure database itself. Go ahead and press next. If you'd like to check these requirement, As you can see, the following products have filled where the installment will attend US. Resolve. It's love, actually important. Leave it for now. First. Next. Yes. Okay. If you have something like that, just leave it. What a board and we can fix it later. Here is what's happening. It's being installed, Rick. Now here it is all the features that we're going to install on the percentage off each one . Here it is installing the collectors here. As you can see, this is the files that we can use to collect. As you can see the different If you reason he don't let T shirt of Immediate Net or Java or python. It's being loaded as well on the shell, the abilities the work bends. But they mentioned this is the year Do e the graphical user interface that we will be using that like it except very powerful. Okay, I will leave it. Andi, go ahead. Also as well. Relax until all the installations are done. Okay. Now the manipulation is done. Let's go ahead on this. Next. Thanks. Go ahead and choose down the loan. Last fields over. Go ahead and leave all of this option. The routing here. This will be like the administrator user to configure. Do almost everything to the fine scale. Little babies. Go ahead and choose. He just went for it. Wine will be weak. I know it is that it's not that important for me right now because I'm just using this as training. It's a lot of actual server. If you're in a real environment, you need to make this for us. Apart from passport, leave old this as it is, click next. Next execute. It's finished. Quick finish. Thanks. Leave. All of this has there's just click next on. We need to give the passport for the world. You that that just choosing like yes, As you can see here, Connection succeeded. Old connection. It succeeded Terror. Go ahead. Look like ex queued. I know it's some pouring configuration will be done next. Ah, now we're done installation complete. We can either start the mice. He'll workman's all the show. I'm going to choose. Start by. It's good work. Binge it to display the workbench right now. Here it is. Here where the application is we're going to use to do almost every sing to the mire. Skill, Better base. Next, we're going to explore some of the features off the port bench to you in the next Lichter. 4. MySQL Workbench: will come back in the sector. We're going to explore some of the features of the Maya sickle or Pence and the previous lecture We have seen how to install the vice a gale. Andi, my physical work Pinch. As you can see in here, the workman should the official graphical user interface tool for them like a sickle, it allows you to design, create and priority database. Scheme is work with the debates, object on insert as well as designed and run sequel queries. And I was all of this and even more so. Don't Harry. Now let's see how to connect to the Maya. Secure has again seen here There is this beef old my secure connection. It's named local instance My skill. Roder. Let's go ahead and click it. You can see some of the information here. Double click on that and it's asking me for the passport. I'm going to give it a password. Here it is on. We are connected. Come regulations, cause they some of the main features in here from the first in here service status. You can see the server states from here. As you can see, it's dream on the play Michael in here, it means it's running. You can find some information about the name death Dash courses that VM This is actually my computer name. All the server name. If your local server. And here the port, the virgin that were using the by a scale community server on some other information. Actually, where it is, install the freeze pays. If we go down, you can see here all the connection that are connecting to my SQL server right now here, the users and privileges. From here, we can manage to add users or remove users, gives them certain permissions or removed from the certain permission as we're going to see and the upcoming lecture. As you can see here, the rude This are the default that are already in its told with my a c l. We didn't create any of these. Let's go take a look out the debt export weekend off course. One of the amazing features with the my A scab work bend that we can export data to different heart. You can export it or access database or an excel file. It's very flexible or we can do the opposite. We can import data from these types here. The any storms, you can stop the server. As you can see, it's running now. If you like to do some mental earns or any thing you gonna stop it, we can bring it offline. Bring it offline. Will let stop it. But it will make it offline. It will make the connection to With unavailable. Can go in here for the performance. You're gonna check this nice dashboard in here? Nothing about the efficiency about the server and the collection that's receiving and sending Really nice information you can see. Now let's go to the important part in here. You can find the scheme, it the databases, you confined psychedelia or don't know how to pronounce it. Actually, this is E database, and it's already with us from the sequel server. You can see the database in here. The actor, the Padres, the category, the city, the country, the customer every opened this, for example. You can go ahead and right click and select Rose. And here it is. There is some information in here. Make a Jennifer. This is all ready for us so that we can make relations, have items. Remove. I'm so you can practice sequel queries on them. You don't need to design any of these. Of course they are available. They have pilled end tables, allow you and I'm going to explore some of these features. Laters. You can see. Here is also the world ID database. He opened the tables. We have the citizen countries. We can open the country. Go ahead and right click. Select grows on here. You don't mind all of this cold? We're going to master off this coda. Select Insert that just breaking the eyes with the Maya scale workbench. So don't find any code or anything that you don't understand. We're going to go through all of this features one by one. Are you can see Here is some data. Actually, that's a really cool data for all the countries as you can see and go down for on the continent in the region, the population that all of this information I've ever for us, probably my Esequiel built ended the bases when we installed it. Think it's very nice. This was just an introduction to the workbench. I hope it break the eyes. Was that go ahead, Blais, with this application for a while. Get used with Next We're going to see how to install. Why? Scale only cloud. See you in the next lecture. 5. MySQL Database on Microsoft Azure: Okay, now let's see. How can we create my sick a little? Based on Microsoft phrasing to go This thinking here other dot Microsoft did come. It's free, of course. Go ahead and press start free again. Start free the minors off the other works as a following. It's gonna give you will. Vermont's is off free services and it will give you $200 to spend on minors off as well. Let on any other thing you cannot spend. Of course, this to hundreds on Amazon or you only pay or any other website on Lee on the service off the other only for 30 days. Go ahead, click storage three. Go ahead. And it just a basic sign up on maybe the interesting work only that will ask you about your visa corridor must afford on. That will charge you only $1. Only this and as we're going, if you need to use the services, you will be paying. But don't be worried. Will it be using any hate services and discourse? So go ahead and create a Marxist. I can't if you don't have one and creating mikes off as her account as well, I'll be waiting in here to see how can we create the Maya scale? The debates on the other. Okay, I hope the steps off easy now let's go ahead to this Euro port. Older hazard up. Come. You should see something like this when you add this your own here. So this is mainly the dashboard and the Jui that you will be able to great resource and manage any resource that you're going to create Frieze We can go ahead and change it seem I don't People like to change the scene so we can choose from this colors. You can make it warrant or plaque High contrast seem I'm just just leave it well, the folds Congo have changed. Have you want that dashboard itself is irritable As you can see in here can edit dashboard Start editing your dashboard on the left. In here Here is some of the services of the mattress Soft, other They are not all of the service. If you would like to see all of the services, you can come in here. Andi, choose old services Down here is older services Good thing raised by the genera storage Web databases. It's a trip. You go ahead on a did the dashboard, and you can as you continue gonna resize your my toes. You can add even items from here and the dashboard. Maybe the marketplace. I don't know. Just go ahead and claim Is that it's fun to play with it. Okay, Now, let's go ahead and try to create the my SQL database. I'm gonna choose. Create a resource from here. I'm going down to the databases. Go ahead and choose hearings. Actually, it's in front of a other database for my skill. Go ahead and choose it. Now go ahead and give the server name I'm gonna call with Maybe I didn't give it any name. Your name may be true in my name. Why so kill server? It's valid. The subscription and you can see here it's a free trial. We'll be charging anything. The resource group actually is a collection of resource. Is the chair the same West cycle permission and what it is? We're going to create a lot of free sources right now. Sir. Go ahead and create a new one. Maybe name it. Uh, three Source. When that's what the logon name that will be using. We can name a truth or you can name it anywhere. No, we're gonna named Groot. Actually. Gotta be named all of this names, as you can see in here, Rude guest public ethnic administrator goanna choose maybe again your name or any names that you would like. So it's not slept Abel's or us to name it route. Ok, that's very important when I give it my name. True the password, please Remember this password. But forget it, Onda location that December will be located and we can go ahead. Leave it as west. Your where we are. Just leave it as it is. No use the 5.7 Virgin. This is the last one available for me. The pricing tier. This is very important. We can come in here and you're the payeasy We can see here. This is the the pricing unit. As you can see, it's calculating for me how much money I'll be banging as you can see in here. But I'm just gonna leave it the default again. Don't worry. You let be charging anything will be deleting all of this. Resource is once we're finished and we already have $200 when we created this account. Go ahead And trees Okay, Now commute. You convince this to the dashboard if you like. I'm gonna choose it. Click it. Create validating Depends on your Internet connections. Here it is. And it's been to my dashboard. As I told it, they're blowing Microsoft of the debates. Were Maya scale going and click it? It will be deploying duration Will seconds. I don't know if will be finished doing that. World seconds were not well, that's actually the world's second. That's already taking. OK, I will just leave it until it's finished on. I'll be back. Okay, Now the database is created successfully. As you can see, the status is succeeded in near took three minutes and 19 seconds, which is very good. Like sink on as you continue the notifications, deployments exceeded and we can go to the resource Also, you should see something like this you have in your credit remaining. You should see something around $200. Now let's go to the They're to be itself here it is close unification on here with the server name, and we can connect with using the my secure workbench. We're going to see this in a bet against either Logan. Name your It is here with the name that I specified at on the server in England and the Virgin on and almost everything you can see here as you will be adding, I thing, you'll see the resource utilization. I'm gonna see here the security and we will go to the stab. I will tell you why in a bit. The connection string. If you're going to connect to the Internet or the J. D. B C or the No Js or the BBC or which we price on ruby or whipped up, that's actually very nice to have. You can just come in here on could be the connection String off the other night, for example. Now, let's go ahead and see How can we connect to the database from the sequel work for the my sequel Workings. So go ahead and call me the server name from here. Are we creating in your connection? Here it is. This is the collection name. This is not the server name on the cloud can name it. And even with eggs, even anything, I'm gonna give it a name. Color My skill enemies. The host name. Actually this I'd be actually here. This is a local. When you see this, this mean that this is the local machine that you're working in. So here it is. I covered the server. Name Andi in here. As you remember, we didn't use the route. We use the love, Euller. Go ahead. Take it from here. You know your friend with the Basque word press on this bothering year store in Volt on. Go ahead and given the passport that we've created Okay on. Let's test the collection. It's going to be successfully made. Do you guys feel connection? What a step. If you have an error in here, you're gonna come in here just like your connection security and add your ivy address. If it's that, how did go ahead and add it? Because I have my baby editing here. This mean that it's allowing me to access the database. If I deleted like Ivy address. It is updating connection. It's abated. Some of the leading it to see what's going to happen if I try to connect to the other database. All the my SQL database on the other. So good. I had. I try to connect now. As you can see, failed to clinic client was Ivy. Address is not allowed to connect to this eyes Field server. Let's go ahead and add it again. Safe. We'll forget to safe. Could work. Here it is. It's working. Go ahead on. Okay, on. We have a connection. Double click on it on. You should be able to see the cloud that I basically created through. The more Vince here it is. And it doesn't have anything. Actually, there is no tables. This is just the default tables. We can come, of course, and create tables or drop tables and work with it. But that would be it sooner. So we hope you enjoy Head has. It's actually interesting at late for me. I hope you did. See you Indian expected 6. MySQL MySQL Database on AWS: would come back. Now let's see how to create a my SQL database on AWS or the Amazon Web services. First, I need to go to this Your own Here eight of lee s dot amazon dot com. From here, you will be able to create your free account. AWS will give you 12 months for free. It's only on a selection item that will be seeing how to choose on Lee the freed here that eight of us giving us what race you need to create an account. So go ahead and click. Create a free account going to ask you about your email address, your password, some basic information that it will charge you Onley one bowler. And then, if you like to use any of the AWS services, you will be begging as you're going. So go ahead and create your AWS account on. I'll be waiting for you. Okay. I hope the steps for easy and fast if you created you account you'll have access to this council from here with all of this services. But the one that will be interested in for the moment is the relational database service. Go ahead and click it here it is. So I'll be killing it That I would like to create it started, Like to create a new in distance. It's telling me to choose which did a vase engine that I would like to use. I'm going to choose my ass, Akio, go ahead and choose next and there is an option here. Actually, it's very interesting. Onley enable option for eligible for RDS free tier years. So chicken on you will be only choosing the free tier so that you don't find Miss Take. Choose something that's not free. Go ahead and choose it on here The my scale community additions that we minutes told in the previous pictures on here the virgins and lost you the last one here, the freak here that will be using the innocents or the configuration off the database server that they're abusing. It will be on Lee giving us one CPU on one gates ramp. You would like to increase. Of course, I will be baying money. So that's the one only available for the freakier. As you can see here, the Amazon rds three tier avoids a single. This is the name off the here that will be using Bebe Doc Tito Micro instance. That's giving you old Su 20 gigs of storage. That's actually nice as a story here it is really the storage. Now let's go ahead and give, for instance, name. Let's go ahead and maybe give it any name. My girl. A devious Go ahead and use the name things. My name. Create a password. This user name passport will be using it to access this server of this database. Click Knicks. Just leave all of this as it is. And if the public X ability is said to know, please check. Yes, so the freakin accent from outside. If you'd like to create a database option, give the debates the name. If no database name is specify it, then no initial myself feel that the base will be created. So if you didn't create well, added the name here, there would be no databases available. If you like to. Great, Maybe can Please will be empty, of course, but we'll be there. Go ahead, Andres. Some options for the backup for the security. Just leave it as it is going to play a lot with it, every or if your life to go play with it. It's It's safe. The hump Orient lesson will happen. Go ahead. I am choose or late Large db instance. I want to take a couple of minutes. Here it is. Your DB instance is being created. Your insistence may take human. It's too long. So I'll be posing the video until the database instances created. Okay, the database now was ready on Dawn Lee Cloud. Here it is. It's available And hear the name My physical A deadly s. You can find old of this information in here from the instances if you go back to the services. If you like to see how to go to this screen from the database Gana, choose relational database services and from here, click on DB instances. As you can see, I have one off 40 on here. The database. Go ahead and click on it and hear that the glass or the tear that I'm using, which is the T two microbe which had the free tier on this enemy. It's available. Go ahead, click on the name of the database instance, and that's going to give you a lot of information. The Cebu legalization, the connections, the free storage is pees on lots of useful information. Actually, go ahead and navigate through it. Andi In here, you can find here the Thean Bond. It's a server name can take this copy could be it, because we will be accessing. And here's old the information you can go ahead on, of course, Modify anything or you can even lead it when you are finished, where you're done with an insistence you can just chocolate on from the instance actions. You can go ahead and stop, it reported, but you can delete it or because not sure it's like a packet. Go ahead. Now, after we've taken the name from the end point in here, go to bicycle workbench on creating you connection again. The connection name is not the because it's the connection name. It's not the name off the database server. All the innocents off the database. Okay, I'm gonna call it eight of Leah's My skill database. The host name is the database name or the instance Name loan at the end point? No, that would be replacing in here. When you find this, I be This here means local 1 to 70 or the wanted at the local machine. Go ahead and change it. And if you think route Remember I entered my name. Go have found until you the name that you used. Go ahead and give it the password. It is. The collection should be working fun. Here it is. It's fine. I had include okay, Double click collect. Here it is. If you remember, we created a database cold in please. Here it is. It's empty. This no table, no views, no sort of procedures from any other thing. But it is created for us. It was doing anything on. We can use the database on the cloud on Amazon Web services from the my SQL or a pinch. I think this is very interesting. You can go ahead, use the Aniston's a local oldest one. As long as it's free, it's It's the same. It's up to you. So that would be it. Next. We have something different to you in the next picture. 7. Create and Drop Databases: Okay, This picture, we going to see how can we create under grope databases? But very slick me define? What is it that debates simply database is an organized collections data. It's a collection of tables, queries, functions, views and elements. Are we going to explain all of them on more all of the social media and whipped signs that we're using daily, like Facebook, Twitter, instagram, YouTube? All of this whip science and serves is they're all using databases one way or another database or another, but they're using database to contain information. Let's go ahead and see how can we create the database in my skill. So I'm going to connect to my local little base. When we look a server, it is it's connected to create a year database or a schema in my SQL. It's easier called a schema. As you can see in here, we have stories King has created for us by default. When we was told, the Maya secure L or can be called a database, so to create and you one simply we can type a code in here and the volcanic. It's very simple. Code the s again cold. It's very user friendly hasn't gone to see or you can use the my secure workbench we're going to do, boss, is it so I can come in here simply untied? Create schema and I can go ahead on time. But maybe it's going to be human. Resource is it's going to be a schemer over database for the human resources we present here is skewed. As you can see your the feedback creates schema human resources that successfully, if we go to the schemer area in here and right click yes to refresh it for official on. Here it is. It's green it. It's as simple as this, and he re confined tables. It's empty. Of course. There's no tables, no views, no store procedures or functions. But it's there. This is one of the ways I can come in here even much, much easier and choose if I press right. Greg have this medio to rate scheming. How will I have to do is choosing you names. Or maybe I'm going to call this test. Go ahead and click. Apply owned. Okay, it's showing me the code here. It has reviewed the SPL, a script to be applied on database And, as you can see it just transform it. What I did using my A scale workbench into a code here to straight schemer. Good on the flight. Um, finish on. Here it is. We have the database. See? It's very easy. Most ways using SQL Code or the Wizard from the bicycle workbench. So how can I drop this scheme? Our table? Simply. I can come in here and there, please. Curie eight. I was drunk, skewed to read is It's helping me. It's done. It's removed. Go ahead, Andrew. That's run on again. The river. Very easy to use. I guess that's it. Do you in the next picture? 8. Create Users: will come back in this lecture. We going to see how can we create users and gives them certain permissions? Uncertain scheme as or databases. So in here before we go ahead and connect, I would like to take a look at this. I'm going to choose right quick and choose edit collection. So before we discuss this, but I would like to revise it with you quickly. Sarah. The connection name. I can go ahead and change. It can make it any name. I want the connection message. Leave it. That is DCP over. I p the host name. You can choose local host. Where you gonna change it? To this idea they showed you before? Which in the local Liberia? The baby off. You're the local machine and this is the board probably will not see this number in here. You should see 3306 I have 60 I changed it. Just leave it as it is. Don't change it unless you it change it intentionally. Okay, so this is the users will be creating users like this route users. The road is created by default. When we were installing the Maya secure out. But We're going to be a users like the route. So I need to go back and connect using route. But I need to be an administrator to be able, haven't administered, but have sufficient permissions to create other users. Okay, so right now I'm going to the management from here asserted option, which is users and privileges from here. I can go and create new accounts so I can come in here, assembly and on account. Andi, I can go ahead and sell the information off this current or this user. So in coming here and maybe name it world user. I mean, why the wall of the database or the scheme awarded that we have in here? I'm going to give access to this user toe on Lee. This database, not the secular, the base in love, this cyst. That a waste? Of course I'm not any other debates if we have of the races, Okay, Go ahead and give it a password. Gonna keep it very short and simple. It's week. It's OK. The account limits. This is very interesting. I can sit the limits of this account or this user. The 1st 1 is a maximum query the numbers were isn't account can execute within one hour? So if I said that this user can weary my database 10 times Onley in one hour, if he's going to do it for the Elevens time, will it be able to do it? Okay, we can totally control it. The Mac Tim obeyed that he can use. We're going to see that weaken, choose or make updates. Statements to update data in our scheme is on our database. The connections. I can also limit the connections to idiota base. The concurrent connection is a simultaneous connections going to the server. I can also limit this so it's very powerful and very flexible and very easy to make administrative roles and inscrutable. I can go ahead and give it at B B A. For example. As you can see, here is the role that's developed for me right now the db a grand Z, right? So before all the tasks, So if I choose db A, he will be the same as the root and this rolls in here they are going through with server love bread database. So if I choose that he will be a maintenance admin maintenance and rolled ramps writes, needed to maintain the server. It will be giving this Praxis through our the database all the data basis, not only the world, it all together with any other debates that I have. Okay. So I can go ahead and choose maybe a security hat mean And that I sing in here is that the description of each role is in front of it. Say, I would like to see what is a DB designer. It's telling me rights to create and reverse engineer any database schema. As you can see backup admin from its name. It's very easy. I guess minimal rights needed to back up any database. So I'm gonna leave this as details and we're going to play with this. I'm going to choose from the schema privileges. So what I'm going to choose here is first at entry. There's going to be really interesting. So which is like the schemer and I'm going to choose my database, which is wallet. Go ahead and click OK. And from here, as you can see, I can give it a variety of options. I can go ahead and make this user able to select and we're going to explore all of this statements. Remember when I told you about the SQL or the sequel statements? This is some off the sequel statements and here to get permissions toe this user. If I want him to select, insert or delete or of Israel select only this elect statement that he will be only able to like the food description Here. Select privilege enables you to select rose from tables and in it of its we read about the insert Insert privilege enables you to insert or to be inserted into tables in the database of eight enables you to be abated in tables in database. I can even make it able to create. So this is talking about the data, says the rows in the table. But this is here is talking about creating new objects, for example, well altering the object or creating a views or dropping something. You see, it's very flexible. How can we do this? So what you do is click apply, and now the world user is created with the specific privileges on permissions that we gave him. Let's go ahead on the clothes of controlling to connect to my SQL using the new user wrote , Well, this we want to create a new collection. We're gonna call this toyland user connection again leaves a TCP over a being as it is here . The host name is local, and this is the full support. So you should have. You must have this number 3306 But I change it. This sport, for some reasons on I'm going to change it. But you don't need to change it. Now I'm going to change his user. Look, going to connect using the route I'm going to collect using this users that have just created. And I'm going to test the collections and going to work unless I give the password. My week password. Here it is. Okay. On here it is successfully made my secure collections. Okay. And here it is. We can see that we have a new connection in here. DoubleClick, Elect, We should see that we have access. And we can only see the schemer off the world in here, You see? Very interesting. If I go back and try to connect using the route or the administrator do it is a administrator user. Have sufficient provision. Okay? probably a give it the best word wrong. Yes, that's correct. Here it is. You see, now my scale to the base has three different schemes. But when I connected using the world user, I only have access and can view one that always, which is the world database. If I would like to remove this user, I can simply come in here and choose it. Of course, I can go ahead and edit any off the permissions and privileges I gave to him. You can go ahead and remove creative. You drop. Let's actually just when we remove the create or alter and I'm going to leave the select going to click apply, going to blow this, going to connect again using the world accuser. No, I can see on open the data if I, Chris right click and select grows computer data. And here and then we were going to discuss a lot about tables and Golan's and 80 times Don't worry about this to suggest administration. So if I wanted to create a new table right click on the tables, choose create table. I'm gonna just leave it as a Tina's and click apply so great table world. New Table World is the schemer or the database? Go ahead and play and error. If I even tried to give it some columns in here, maybe this would be just leaving as it is. And I'm just adding columns in here to make sure that we've created the table successfully and see if the permissions is enough to create something owner. Now here it is. I've created one colon. Did you have a primary king? And again, we're going to explain this in detail. Don't worry about this. Let's see if this is going to be executed on. And I would have seen when we're recreating that the basis my skilled workmanship is to win me the SQL code before executing it so that I can see if I can edit it. It's really nice feature granted, click of lying on, and still it's not going to work. There was an error. While buying the sequel script to the data base, you see, create command denied. See the conveyed that we didn't give. So the world user is denied because he doesn't have the sufficient permissions. Let's go ahead. I'm giving the create commission. Actually, let's close older connections on create again on them going toe looking again, using the route to give the world user with permission. The enough permission and privileges is that he can create objects inside the database. So I'm going through the world, and here it is gonna have on click on, Create, Lie, go back again connect Using the world user I'm going to the world of database right click on the tables tray table. Going to leave it as it is. That column also, even as it is just going to click Lie Yes, on. Here it is. It's executed successfully. Easy Now he has enough permissions created on X created without any problems on here. The new table. I know it's empathy doesn't have 80 information or any useful information, but I was just administrating users accounts on permissions. So I hope it was on on and easy and see you in the next picture 9. Exercise Create Database and User: Okay, time for a small exercise. I need you to go ahead and create database called Human Resource Is Ankara users that have access to the human resources database that you've created and give it enough permissions to create objects inside off this database. So I'm going to oppose a video and tell you financially exercise. Okay? How much easy? Let's go ahead. I am do together first. I need to collect as a route. You're gonna get the That's where force they can save the password and the Volt. But I'm going to type it. So go to users and privileges. But first, we need to create it at the base force. Go ahead. Red Lake. You could created using the code or using the wizard in here. This schema is going to be called human. Resource is just go ahead and click. Apply. Um, it's created. Here is the code three Easy. Okay, it's created. Here it is. He won re sources. We need to go ahead to his users and privileges on that account. Medical if it or user HR status for human resource is I'm gonna give it a password. I'm gonna go to the compliments my going to change anything. India compliments all the administrative roles, the scheme and privileges. I'm gonna give it a entry. Go ahead and sell like the schema Human resource. Is that speeded? Basil? The schema gonna give it the right to select. Insert, update, delete, execute, show view, Create alter with you Great routine. Older event. Drip and trickle. That's enough now, and I'm going to explain all of this. I'm selecting here in the upcoming lectures, so don't worry about this. Just given the sufficient permissions, madam, click apply. Go ahead and try this user. We haven't creating your connection. I'm just testing. This is the human resources user connection. No, Maybe that's a long name. You can make a charter if you want. This is the as we agreed. This is the local change. This please don't change. This is the custom port that I have the one that you should have. 3306 As I told you before, I'm just having a different port. No, I haven't change user going to true the it's or user. Oh, I had a distant connection on any of the password. Okay? Yes. Successfully go ahead and create it connected. Andi, it's working. And here is our database. So the tip for this exercise See you in the next picture. 10. Database Tables: when compact and structure, we're going to discuss database tables first, let's de Flore in what is a database table? A relational database consists of many components I didn't go to see. But the most important one is the table Without the table, there is no need only use for the database to exist. That of a stable is where all the data in the database is store. So think of it would be hard off the data base. So briefly here is e simple example Off the table here, we can see there is a column. First name is a column. Last name, city and country and each off these in year our cold rose. So this is the first true containing James Hudson from Barcelona, Spain, and the tutor. So I can see that this is a table of four rows on four colors on sports. They can keep adding data in here rows and columns if necessary. That's we're going to great in the Mayas healed server Right now. Let's go ahead and do such thing. Okay, I have the my scale work Bench opened in here. I'm going to do this post ways. The SQL Code on using the workbench. So right now I'm going to do the 1st 1 which is going to be the SQL code. So what I'm going to do right now with start typing some SQL code to great table, that's going to be straightforward. The SQL code is very complicated. Not like programming language. So it's going to be pretty much easy, I guess. So, let's go ahead and try to create our first table. So I'm going for is to kill it. To use the human resource is database. Maybe he defends to call. So if I would like to make the fund, zoom in to make you see pitter or you would like to secret or you can just go ahead and on your keyboard, Click on control. Bless, But all minus as you can see, it's thank you. Zoom in or zoom out using the plus minus over the scroll. The scroll in your house to this is suitable. Legace. So I'm using this. That the base I'm going to great create. Hey, Well, I'm gonna call this table persons scoring to contain some information about persons. So this is how you create people, create table on the name of the table, The tip, But yet done yet, Teoh, in here. I'm going to open it currencies and close it. And between this prince season here, I'm going to define the structure off my table. So I'm going to use the person I d. This is the first colon on going to great person name, So there will be an i D. On the name on. May be a city on the country. It's dipped. No, we need to specify the date attire off each column. So what did this cold I'm going to store is going to be members text dates. So I need to tell the database the type off the information over the data that's going to be stored in each column. So it's going to be symbol. I'm going to choose. I'm going to discuss data types in depth story and in here, and it is an abbreviation for injured. So this here is going to store in your numbers like 12345 answered, right There is other types just wore floating point numbers 1.2 10.5 on this numbers, and we're going to discuss this later the person named Mike and David using a character that's he get a type twos tour takes where I can use text where I can use my favorite. And I'm going to tell you what the difference between the three of them later is Vora chore or variable characters on my need to specify the linens off this field. So I'm going to choose. Maybe it's going to be 100 100 letter. So the city also is going to be variable character. I'm going to change this later when using foreign keys and primary. He's but right now let's leave it as variable character. Maybe 50 Country also can be variable character 100 maybe. Okay, that's it. But when you're done with the SQL code in the mire sick, ill work pension here, we need to add this special symbol, which is the semi colon meet at a after I finished each statement to Bradley. So as you can see now, this is done on. This is done and they forget one in here. We need to remove it because that would be an error. So if I am going to execute this from here right now, As you can see, my tables are empty. If I refresh this, there is no people in my database, so I'm going to execute it. Here it is. You have mistaken with the name. Yes, that's correct. A human. The sources he the sources. Guess that's great. It's executed on Yes, it's them as you contain here, it successfully created. You see, the Greenmarket here means that everything is OK. Go ahead and finish it. And as you can see now I have e new people. As you can see here the column. They have the person i d stipend, a person named variable character City country. Also variable character and the lens of each one. Open it. I can come in here and see all the information on the columns were discussing data. This table is empty. There is no data inside of it on Lee. The structure of the table. What if I wanted to modify this cable in here, for example? Maybe I would like to add some. Call them to the table so I can come in here simply and stored on. It's very straightforward ulcer. So to Andy Cole them I would like to add maybe the the year of purse off this person. Each person, sir. Well, I'm going to do is Helen that I would like to alter. And as I'm typing as you can see, my scale work, which is helping me telling me if there is a key word available What I'm writing. As you can see now, I have the elder guests they would like to you. This altar is used to modify anything older. What is you're going to alter its asking me? I'm going to alter the table. And this table was called persons. So that's the first part. Now let's go ahead. What are you going to do with this table? What are you going to ultra? I'm going to add he called him. As you can see, it's very systematic in here. And Colin? Andi, I'm gonna give it a name. Maybe the name should The You're, well, purse. And it's going to need a time. So that's it. So what I did in here is set it to older, this table persons, and then add and you cola cold. This is the name of the column. And is that the type off this goal? Go ahead and execute this. I'm selecting this only because if I run this right now from here, it's going to execute all the codes in here and they don't want it to happen. But I would like to have it is to execute on Lee this two lines. Let's read the description in the Potter in here. Execute the selected portion off the script or everything if there is nothing is selected. So if I didn't select anything to going to execute everything and that's not what we want, So what I'm going to use is these two lens only go ahead and executed. Do you remember what he didn't have? A year of purse? Coolum, as you can see from here. So I'm going to refresh this in here. Um, as you can see that I have and you column Cold year of purse of time and it's learned, contained the year of purse of each person. So that's how to on e cooler. Let's go ahead and maybe try something different. Maybe try to drop a column I would like to political don't want. Maybe the city the country is enough for me. So I would like to drop the city. Great. So let's go ahead and do this. All they're going to do again is older adultery meat. He will called persons. And what are you going to do with this table? I'm going to drum. He called him. And this Coghlan's Gold City. Never get the same colon. Go ahead and execute only these two lines from here. Um, that's against you. Hear, if you have something wrong with your code or rescript in here, it's going to kill you that something's wrong, as we have seen with the database name in here. But as you can see now, we have this green correct mark mean that everything is all right. Let's go ahead on the fresh friends. And if you go to the person now, we don't see the city because it was deleted it. Let's go ahead and trying one more saying, Maybe I would like to modify data type off some colon that I have so again, I'm going to choose alter table persons. That's the only cable that we have somewhere working with it. Later, we're going to add more tables at make relations. And don't Harry. Now what are you going to do? I'm going to modify. We call them. Maybe I'm going to change the country. Maybe increase it. So I'm going to change the country. The color country on I'm gonna make it may be very well, Director 250. So right now I'm going to change. Of course, I can go ahead and change the type. I can make it. And if I want and it want complain about this, I'm going to change. And first time got Excuse this. Let's check it again. Fresh on, as you can see now the country is this type. And that's not what I was going to do. That I'm just showing you that I can change it any detail. So right now I'm going to get it back To are able corrector 250. Go ahead and execute it again. Get fresh on. Here it is. Now it's converted to variable. Director 200. Christie. So this is how you can create he cable using he SQL script. I hope it was pretty easy for your Let's go ahead and try the easy way using the work pinch . Do we are Okay. So before you go on and do this using the interface of the workbench. I'm going to drop the table so it's a drop of people will delete it simply can come in here and fill it. Drop table on the name of stable. Now this table is going to be greeted. So go ahead and execute this. And yes, as you can see now drop table persons is executed without an improvement and now my tables are empty. There is no tables in the human resources that debates. Let's go ahead. Undo with using T workbench. So right, click on the tables, Create table on here. The interface. It's pretty easy. First, I'm going to specify name, maybe persons again because we deleted it. So if you have, of course the same name, it's not going to work. But we deleted it would rubbed it. So now it's okay to name it. Go ahead, then leave. Commencing this table should contain information about persons like and make him go ahead and specify. Okay, now it z Coghlan's part army to specify, and he just double click and you will have the call of name give the first Colin version. I d Andi, I'm going to leave it as can seen here. This icon appeared in here This mean that this is a primary cape. But before the workbench assigned the 1st 1 type them to this attribute in here with this primary key, we're going to discuss primary. He's later. So don't worry. We're going to discuss also about the constraints in now the unique seven where he just remove it. Right now we're just leave. It doesn't matter actual. Now let's go to the next column is going to be cold Person name. It's time variable. Correct that I can go ahead and increase it. Maybe 100. And if we drop this drop down in here, I can find all the different data types allowing for me the work benches that lowering me to add, can't you the decimal double slowed character? Even Jason. If here for me there were the JavaScript mutations, it's only it's also available. So I'm going to have another column. Call it again. City. I believe it is. It is. You change it. Actually, there were characters. Live 100 go to the country on. That's it. I can keep adding, as you can see in here without any problems after doing this in here. Using this really lies on using interface. You can come in here and press supply. Why, as observers going forward? Okay, it's it's creating the cool dress, as you can see in here. Just there is some code or scripted, even discuss the primary key. And now we're going to discuss Take. So don't worry, but it's showing you the code that's going to apply for the script. It's going to apply. Resupply finished on. We should find the persons in here as you can see, the same as we've we wrote in here, only different that this is easier on fast. So you need to learn how to write the skill code or the script. That's that's really important. But in a riel war, listen, air you would like to use professionals uses interface directly because it saves time and effort using this interface in here. And if I would like to modifying any of this tables, I can, of course, go back Andi, modify it or even drop the table. If I don't want to, I would like to leave. It just drove it. It's now asking me, Would you like to review the execute code or drove it. Now, let's go ahead and review the SQL Code growth table. Is that the human resources? This is the database or the Scheman. Well, here in the people. Okay, execute it on now. We have no tables in our data bits. So that's it about tables. See you in the next picture. 11. Data Types: Welcome back. Everyone in the structure. We're going to discuss more about the two types and see, how can we fill our tables with data? So in here, I'm going to create a new table using the workbench. I'm gonna give it the same name that we're using persons on. I'm going to start that Columns are the first to Gholam is going to be the I. D. Person i d. This is the idea for the person that's going to be used five injured. That's fine on the second color is going to be the person named You're gonna go ahead and make it the first name that's up to you. Now we have the variable character, and I have seen it on he have the character to the time we go down. You can find that there is a corrector data diverts going store also characters. So what is the difference? The difference between be corrected and the variable correct. That is that the character is fixed. Glimpse when the variable from its name, it's a variable Lindfield. This means that the character always takes the same amount of space regardless of what you going to store was it variable is different, depending on what you're going to store. So I think that the variable character is 19. 9% is suitable for your cases. So I'm going to use the variable character goingto heard. Maybe the country again. Variable correct. They can go ahead and change the limit off the letters to make it longer. If I warmed, go ahead. Maybe, let's Adlai different, that is, I thought I'm going to use the salary, the salary for each person, the salary I'm going to choose either the type called Dissemble Visible actually takes to M . Butts. So I have to specify the linens off the member, maybe eight. And they have secreted by with the cover really comma separated by comma, you have to add the limbs of the numbers after the bond. So I would like to display to members after the point, or I can go ahead and displaced three, and I'm going to see this so I'm going to choose. Dismal ain't onto on going to choose maybe the date off purse, so I'm going to choose eating a type called Date. That's gun store date, as you can see in here and maybe we're going to check if this person is in service. Not so this guy was going to be in service. I'm just asking if this person and services is the type of the column and this column is going toe hold either one zero. True of holes. This person is in service. Yes, which is one or true 00 which is fulls. So I'm going to choose a different eye, which is going to be tough time. But as you can see in here, I didn't choose it. You can just keep it as it is. Okay, now, I don't need this column. It's created by mistake. I can just right click on delete. Select. So now my table is ready. I can go ahead on the blindness on and here is the script is going to be created. The personality of type end person named Variable kept their country variable character salary off this little as you can see, eight. Um, to the date is the verses they the in service is go ahead and click. Lie on. The table is created. To start filling your table was data. You can go ahead, right click and select Cruz And from here here is the dated right now it's actually it's empty. I can go ahead on start filling this and I'm going to show you this right now. But as you contain here there is a result Grip. This is different types to display your data in the tables. I can go ahead and choose for editor, but right now there is nothing cause it's empty. I can choose feel cops. So go ahead, choose whatever makes you happy. So I'm going to add and you and here you can go ahead and choose and certain you were going to start typing in here. So the person i d. This is the key, or this is the idea of the personal going. Discuss primary keys on ideas later than 40. So I'm going to head one you could land at whatever. So maybe at 10 the person names going to the rim using my name. The country you know I'm using. Okay. You can go ahead and type anything that you would like this celery. Maybe you can go ahead and choose for me to 100 dealers. $250 on. If you remember, we sit it to accept two members after the point. So if I add three, it's going to accept or four it's going to accept. But when I'm inserting this into the table, it's going to agreement or actually round it. So the date that they've actually is accepted in this format so I can go ahead and at the year, maybe the divers is to cells and just just, of course, it dummy dated. That's not correct information, maybe one on one. So it takes the year dash months on day, they on service. I can go ahead on have that using the crew keyword in here in capital letters. Go ahead and click applying. Here it is. Here is the insert. We're going to discuss insert statements later, but it converts anything I do to e sequel description here, so I'm going to click, apply and finish around. Here it is. We have our data. It's inserted. Who added to the column the problem? If I go through the form editor, I can go ahead on from this in here. Navigate through my records, but I have only one or go to the field types time they can see in here the field types off the so I can go ahead and on the colon, but on with record. But before we go ahead, Did you know this it if I told you it rounded the number. I added 1234 But now I can see only wills, as you can see, because it accepts only two visits. What we wanted to make it longer can come in here again to the design of the table in here had click on this icon. Actually this Michael, go ahead and change the salary through eight. Leave the eight actually make it nine increased a little. And this would be street go ahead on blind changes on its done. Now it should, except anything's problem or this three digits. Here it is. It are the zero bytes on. So let's go ahead and trying to either the record, maybe one I want to choose. Maybe Miller. The country is Germany. The salary aren't can make it again. I'm going to use four numbers and going to round it. So it's going to be 567 Elstree isn't going to increase the seven. Just press on. Did a purse. I can go ahead. I'm just at any data like out in service, I'm going to add. Lets me try it with small letters. If I'm going to try this with mortars, I'm going to click on a lie. See? Was going to happen. It's going to put an error in here, as you can see, because, as I told you, doesn't accept the small letters I need to give it in. Give me the letters. Let's go ahead and click. Apply. Finish on. Here it is. Miller has been saved them. It went above this record because of the person I d is born and the Sistan. So it's sorting by the person I'd even going to see about sorting and all this other stuff . So don't worry about this. So this is simply how you can add data to tables using different data types. So that's it on to you in the next lecture 12. Primary Keys: Welcome back, everyone. In this lecture, we're going to discuss Primary Case, one of the very important concepts in databases. What is a primary care? We can define a primary key as a special relation. Aled Database Table Cola. So it's column or and sometimes we're going to see this can't be multiple columns that is uniquely identify all the table records. There's two main features about the primary cane. They are unique value for it. Roseau. They cannot be implicated. Andi cannot contain no. The primary sequences is critical to inefficient relational databases. It z main idea behind the relational databases Use primary. He's to connect all the tables together as we going to see almost all of us. The was primary case in our daily lives, knowing that we deal with them. For example, oldest citizens number what nationality you have. He wants to have some sort of a unique number to identify you. You have a passport number that's also a primary that's unique and cannot be no car licenses by counts in the ideas in a company. All of this are examples of primary case, so let's go ahead and see how can we define primary case and my skin. So here I'm going to open new query here. It is going to make it this larger. I'm going to choose to use the human resources database. I'm going to tell it to grate on your table cold in, please. I'm them going to this one. The primary key for the employees can be the employee number all the employee I D, for example. So I'm going to choose the I. D. And it's of type and on to define this golem in here as a primary key. Simply, it's Ray Straightforward time primary kick that sent you can go ahead and continue having the other column so I can come in here until it in P name, for example, can be very well. Character can be maybe 200 like a women here saying Maybe some tree. Maybe also very well, Correct her 200. Okay, let's go ahead and run. Thus, if we run this right now, we should see that we have a new table with the primary key defund already in here. As you can see here, this underlining here as you can see this abbreviation Beaky, which means primary. There's other ways to create the primary keys. Let's go ahead and drop the cable going to you. The key wardrobe table. But he's go. I will execute this Now the table is dropped. Them going to use a different way to create primary keys inside of a tingle. But first, let me show you the stricken here. I can't come in here and leave this code either his but I can commend it so I can come in here and type this year on. As you can see, it turned out great. This mean that it's not going to execute this in here. So anything that take this two dashes in here not going to be executed. So this is called commence. So you can commend you Go to keep it in front of here if your life to keep it. But we're going to be executed. Okay? This can be helpful. Also, when you're leaving, commence for yourself. So I can come in here and leave Comet. Maybe creating the stable for this specific purpose you see for you, please, for yourself. When you go in time and come back to work with the table so you can just leave commence for yourself. Okay? Go ahead and see the other way. This was off topic, but it's good to know that you have this option. So let's go ahead on again. Create small capital. Doesn't matter. Actually create table. These on them going to at the empty idea again. And And I'm not going to time anything here. I'm just driving the name off the call. Um, and the time you're going to choose and be name variable character, maybe 200. Don't forget the comma country variable character 200 also on. And that letter Now we're done with creating the columns, but there is something missing. The primary key primary key is some sort of constraints. I'm going to discuss constrained next so I can come in here until the my a. C l that I would like to have the constraint, but you can see in here is a key word. I'm gonna call it, for example BK, maybe embody. I'm gonna tell her that it's going to be a constraint of type primary key on. It needs to say or to give it the colon itself right now. I don't have a goal. Um, I have a constrained. I have a name for the constraint. I have a time for the constraint, which is the primary key. Could I can they find other constraints? Right now it needs name, and the name of the column is the idea, and that's it. Let's go ahead on try to skewed this or you can run because the other callers the other co . Would. Actually This year the script in here is committed. Let's go ahead and execute this honor. It is. It should be created without a problem. It is on the columns as you can see her. It be I d is primary. He, As you can see, I would see easy for you. It's very straightforward. There is a scores the other way the easy way using T bicycle workbench. When we curated the tables, I could come in here, of course, found this continuing here. It's really creating the primary key when you're adding your columns in here. It's assuming that the first column should be the primary Kate. As you can see, I can check from here or from here. There is other constraints that we can add, like the national, they me many others, and we're going to discuss this in the next election 13. Constraints: we'll come back and it's actually going to discuss constraints. Constraints are used to limit the data entered to my cable to make it Ba Hayes in some way . So sometimes I can make it order increment specific colon. Sometime. I would like to accept only specific data into my columns using constraints and we have seen previously how to use Primary Key and Prime Nike is some sort of constraints. So we're going to discuss more about constraints in this agent. So in here, I'm going to create a new table called Persons, and I'm going to specify some constraints in this table. So I'm going to great table seen this many times Christians on, and I'm going to give it some columns. So the first column should be person i d. Off type. And this is primary key is my primary Que Onda? We have seen this before, but let me add some you constraints or he So I would like this cold in here. The primary key, the end or this number to be photo in current it. I would like this to increment without me doing anything. So how can I do so? It's think it's very easy. All you have to specify a stellar that this is going to be an Oto increment. Call them. That's it. Go to the next column person name and the person is going to be tribe variable character. 200 maybe 50 on But I would like to do with this person name is that it's mandatory. It's have to be filled. Has to be filled. You see in some website that you have to specially fly some attributes or fields and some other are not. So this one is going to be word. And to make this happen, Teoh make that it is forced me to enter the person named I have to specify this keyword not know very easy. So now it's checking that this person name isn't let little. Each time I'm saving and you record into my database, let's go ahead. Maybe I have the address of the address. There is nothing specific about the address. Maybe 300. Done. That's it. It's not going to be in a no or primary key order increments or anything. These air have the option to have it or it bummed out. Okay, so it's option. I'm going to add a new column here. Maybe the passport number and the passport number is going to be of time variable characters because it contains some letters. Hey, maybe you c b Andi. Let me give you a tip in here. If you're going to create any number that you don't create or before calculation on it in the future on your application on your queries. Don't use numbers. What does mean? So if I have a telephone number or in mobile number or any other fields that is going to steal, Remember? But I'm going to perform any calculations on this number. Make it as a variable character, a character or text, cause you're not going to use the are somatic operations on it. So it's better to use the string operations with this feel. Okay, I'm going to add the passport number three minutes 20 on going to add an attribute in here . If you think about it, the that's board number is a unique number. It should look not be duplicated. No other person should have the same number as your passport number. It's only one, so I have to tell it that this is going to be how unique. As you can see in here, it's going to be I unique, Colon, So no one can have you passport. Okay, so I can come here in the country. Variable Director, it's going to be maybe 100 on. There is nothing special about the country. Maybe the salary. And there is something special about the salary, of course. Going to be five decimal. The lengths to if you remember, dismal takes too inputs in here. This is the lens, and this is bean levels off the point or the members after the guy. So I'm going to specify that if the user didn't enter a salary for the person, there is going to be at the full value and this default values going to be maybe two sounds 2000 infancy in colors. Yours any. But we use the key word default. It peopled. I'm the number. So I'm going to run this Here it is should be created. The problem in this buy your fresh high should have the person's table in here, as you can see. So let's go ahead and test our conditions. So I'm going to select crows on going to add some years, I'm going to specify person ideas. One, I'm going to trees. Maybe Veronica like the name and the address. Go ahead and tell him anything. Maybe 200 Church. Three street, Despite anything on the passport number going toe and maybe a 123 down the country of granite intrusions Gamble UK. And I'm going to had any salary to see if the condition in here that the fold 2000 going to be applied or not. So I'm going to click Couple. I am here on here. These equal script apply on Here it is it. How did the 27 without me doing anything that's gonna have interest? Two things are going to test the auto increment in here. I'm not going to specify any numbers in here. It's going to go to increment pie itself on. I'm going to leave the person named Empathy. And if you remember, we have in not local a strange or condition. That's chicken, that the person names that no one. I'm going to specify the address Diner sleep 100 street and neighbourhoods, for example. On the password, I'm going also to chewed the password to be the same to see if it's checking it correctly. This condition on the country is going to be UK again on the country is going to be accepted without problem because I didn't specify the country. Call them to be unique, for example, And go ahead on specifically the salary. Maybe both 1000 on going to click a black. Let's go ahead and check coming here. And there is two problems. The 1st 1 is that if you reading here, as you can see, there is new the full value for the persons. Take a look in here, so let's go ahead and give it e value. So I'm gonna give Veronica value. I'm gonna give not Veronica. Actually really get the first record. I'm going to add value, which is the person named for the second record. Maybe James have a click applying. Yes, Um, we still have a problem because the passport number is duplicated, As you can see in here. Duplicated interest, Kate, because we give the same passport number four compressions, which is working very fine. Now I'm going to change maybe the sea make it futurism on. Let's go ahead and click. Apply. I remember that We didn't specify the person I d and here it is. As you can see, it's executed successfully and here it is. It added. Three. It's also implemented on the salaries 400 level. Probably responsive. Find it and it accepted the name on the passport number and the countries. There is no problem with it, so it accepted it and it's duplicated zoning problem. So that's it about constraints to you in the next. 14. Exercise Create Tables Employees and Jobs: Okay, Time for a small exercise in this exercise. We're going to practice what we have learned in this chapter. I need you to go ahead and create two tables. First Wallace in, please. We're going to create it in a different way. We've created before, but we're going to recreate it using different techniques, and we're going to raid on a table. Cold jobs. So what is the structure? All the columns off each table to the in, please table should contain in the I D. Which is the primary key off this table. Have seen how to create this first on last name, which cannot be empty gender, which can be male or female on also gonna be empty country Did a purse mobile number email address on remarks on There is no special constraints or conditions about these fields. So this for the first table, what about the drops jobs going to contain Job I D, which can be duplicated and cannot be empty, which means that it's going to be primary key. That's look definition for primary keys. Capital cannot be empty. So this is the column that's going toe Hold the title off a dog the minimum salary and it falls going to these resells on 3000. Any currency. Okay, job, maximum salary. And through the maximum salary for each job is going to be maybe 10,000. The last feel is going to be different. It's going to be is managerial or not? Tow this mean that this field is going to be like a flight? Yes. Oh, no. A true or falls. Which is that reminding that each dog is a managerial job owner. So that's what I would like you to go ahead on. Do its go ahead on trying to do it. I'm gonna lose a video here until you go ahead. Finish it. Um, when you're done, let's go back and try to do it together. Okay? Go ahead. Do it. Okay. I hope it's easy. Let's go ahead. I'm trying to do it together. So the first thing that we're going to years oak do is use the mean Zuman. Okay, That's good. I hope so. I'm going to you the human resources there to base and Colon to made a statement. Andi, in case that you have ready and a table called in, please. I'm going to drop its a drug table, probably just in case that we have it already. The cynical also determinate this statement. Let's go ahead and start creating our first table, which it and please containing the fields that we mentioned, sir. Where to use? Great table. He's open the currencies and close them. You're going to use in P i d will be an end on going to be the primary key off the table on . Then we have the first name is going to be a tax, so it should be variable character. Maybe it's 100 on this should not be notice must contain editor. Then we have the last name young to be variable character. Also again 100 100 in the normally must give it a number even the value actually and the gender and gender. We can make it bet which contains through a false one or zero. But I'm a very to make invariable director to contain the whole dicks, male and female, maybe out can some side. Some whip sites actually let you not to do so. Sometimes you'll see that I prefer not to choose so I can add other options. Other than man or female in cave that I need it. So Spirit directed variable character. I'm going to make it for able corrected, maybe 15. For now it's going to be No, it must be. Give him. Okay, then we have the country variable character, maybe 150 in 200 hundreds. Good. There is nothing special about the country going to go to the next one. Login data burst. Going to be a date. Also, it's nothing special about this. The more my number. And I told you before, if you are not going to before some calculation on a number, it's better that you do with at that still going to use variable corrector. Also one benefit of this that we can add dashes or currencies through the final number of tapes. Sometimes in some countries we have dashes or currencies. Maybe you 101 100 is too long to be 25 25. Okay, now let's do the thing Meal address. Very role corrector maybe 50 on the final one remarks. This should contain any other information other than the previous column. So maybe we missed some information and maybe would like to add some remarks about this, and please, or you can go ahead and add it in this column, and I would like to make it a little bit lower. So maybe it's 500. We are done with. Please go. But forget this even column. Go ahead and create the table trade able jobs that job jobs go ahead during the 1st 1 job, I d. Is going to be an end to go into the also primary key, which, like going to be duplicated arms going to be a unique value again. Jump title, talking of each job or the job name. Variable character. What's going to be Maybe the trip title 200? Well, going to be No, no must be given And the job minimum salary is going to be dissimilar because I can add, uh, numbers after the pointing. Gonna make it maybe nine. Enter on in this in here. If you remember from the it's like that, we need to add the fold value. Remember, also from the previous lectures, Toe added. If old value all you have to do is specify the default. Keyword these research Yes, job. Thanks, Mom. Salary. This is the next field. Also dismal nine into the fold is maybe 10,000 Go. I don't choose your own preferences if you'd like. Okay, let's go ahead. And through the last Coolum, which is, and a jury away. I was going to be I'm using the is with the bit. Also, this is e better practice because this is going to be a question under the answer. Is this joke in managerial? Yes. No, No. One. I was here, so it's better to leave it of this is managerial. And also, when you're reading tables over the data, you know that this is off type bit without you going to the structure of the table set. So it's good naming convention to leave it as it is. But you can go ahead on and make any other name off you on the full value of this Going to be false. Um, that's it. We are done. Let's go ahead and execute all of this in here. So I had an executed. Of course, I can just leave it on executed and you're it is all of this drop successfully created the increased able successfully under job stable successfully. So I had a refresher, as you can see, were the dogs to go ahead and open it. And here it is, the job. And here is the primary key on the capital, which is not null. And the minimum salary which contained the fold of 3000 on the maximum salary which contains 10 7 100 I don't edit it in here. Is that a problem going Apply? Yes. Apply this. It's edited. Ready on. If we change the IMF easing here it is your older fields the first name. It's not Mullah's. You can see from here the last name. The gender have a so that for all this exercise it was form and easy and see you in the next picture. 15. Foreign Keys: we'll come back. Everyone in this lecture we're going to discuss during case. What is it for inky? We can define a Frankie as one or more colons in a table that refers to the primary key in another table. To do one job is to link these two tables so we can say that Frankie's actually are used to link two or more tables together. I'm going to use them as our database that human resources is growing. And what we going to do in this lecture is going to create new to table. The 1st 1 is countries on the 2nd 1 Yes, it is. We're going to grade them using the SQL script, and we will see how to great for in case using SQL script. Then we're going to create another new table called departments, using the my ass again work pens, and we're going to link all of this tables together. So let's go ahead. I'm trying to do this. So first, let's go ahead and create our table. I'm going to create first let me you the human sources, Freese, sources that base on, and I'm going to create a new table countries what I'm creating this table. If we think a look through the in peace table in here, I have the country column here. The country is actually variable tactic. It would be better if I have a separate table for the country's so that I can change them. I can. At the capital I can on the names, in English or in any other languages. I'm like an link this Golovin here that I'm going to create the primary key off the countries that I'm going to create in a bit. So the in police table we're going to see we're going to see this, Sir. Don't worry. If you don't get it in here, I'm going to grade the country already on them going to maybe make the sea Kevin feeling here, and I'm going to I'm going to grade this at the primary key, but I'm going to create it in a different way. So just leave it as it is just an end and I'm going to use the colon cold country name variable. Correct. Maybe 150. Ongoing being No. I've seen all of this before. I'm going to use all of the colon cold abbreviation This is the elevation 40 come too easy . The innovation like you as the year K So I can go ahead and added, this is an extra information that I can add it in here there rather than adding it in the employees table, which will make no sense. See, to a federal character. I can go ahead of making maybe street on this. Give me. No, you can not working out. It's okay on going to the capital of the four school of It's going to be variable character , maybe. What arm? That's it for this people. But I didn't specify the primary key. I'm going to do this using a different technique. So I am going to say that I'm going to use a primary key on them going to feel it that the colon is country. I d has symbol of this. So this is a different way to create primary keys, as you can see. So let's go ahead and run this countries. Heidi, reading this. Okay? I have it ready in here. Sir. I'm going to drive it. We'll grab it. It Okay, let's go ahead and create it on. Here it is. It's created that you will was created successfully. Sometimes this happens if you really have it. So Okay, let's go ahead. And now, Right now, I didn't do any foreign key work there in the next table that I'm going to great, which is the city's. I'm going to great my first foreign key, then going to great right now. The second table, which is cities and I'm going toe, had the foreign king in the stable. So I'm going to create people. Sit is on them going to add The first column is going to be city idea itself. Time and on the city name variable corrected its Mykola maybe 150 then no, on in here. I'm going to tell it that there will be another color for the country idea of time. And And this column here is going to link to the stable, and we're going to see how can we do this? So if there's 10 cities, for example, inside of use air or you create any other country, I can go ahead and give it the same I D number off the USA over the UK or any other number to perform the link on they can go ahead and extract all the information from this table off this country we're going to fill and we're going to do this and the upcoming lectures. We're going to fill data and our table. You're going to see this better, so don't work on in here. I need to specify for T Primary key, which is going to be primary key, is going to be the city I d. Andi on the foreign key and here the new port. I'm going to add foreign key. I'm going to tell it what in the colon that's going to be the foreign key or act at the foreign key? It's going to be the country. I'd be this one in here on going to tell it. It's going to be references is a key word in the skill, and I'm going to tell it. Go to be references to the country's table. As you can see it's table and hear this table on. They need also to specify the goal until the columns going to be I d. So what is done in here? So let's revise it again. I'm feeling it that I'm going to add a new column or constrain at the foreign key on this foreign. He's going to be the country I d, which in this field in here on this field is going toe relate reference to this table. Countries in here do this colon country idee in here from this table. Let's go ahead. I hope it's easy. It's go ahead. I'm tryingto run this, Okay, it's created. Let's go ahead and fresh out of the base Under it is it's great it successfully as you can see on here as you can see that there is e bold, which means that this is a key or foreign K. If we open the foreign keys and here we can see that there is one created the target is countries of the country ideas going to the country i d. And if we open the table itself here, I can see the the country I D in here is a different color. If Iger to this tab from here on bottom foreign keys, I can see that there is a name for the foreign key. It's a photo generated on the reference A table is human resources that that a base on the country's It's why Chris on this reference a table. I can see that it's link it to the country idea, as we specified in the scale, a script in here. So I hope that easy. Let's go ahead and create on the table called the Apartments, which going to contain the departments for the company. All the humor sources that abate the time creating. So let's go ahead on trade going, going to great it. You think the work then So great table. Going to use the name department i d. It's going to be an end and primary key, of course, on I'm using Post Way so that you get familiar with how it's done using the SQL script on the workbench. So I'm going to add column deport. My name is going to be variable electric and you make it 100. That's it on. They would like to make it not know that this water this one leave this one in here. Yes, go ahead and apply this on. Don't forget to give it a name. Of course. Apartments. Well, it's going to be created around. That's great. Do you read? It's the department's re good. Now we need to do well lasting. We need to go to the inn. Please table The one that we've created in the exercise before. Here it is. And we need to modify the country to link it to the new table that we've created right now . It's already if variable director, there is no relation. Offer keys? No, nothing. So I need to go ahead on do this and made the employees also I should get spread breaking out. You don't need to modify this, so let's go ahead and try to do this. So here I have the employees. It will open. I'm going to change the country. I d going to make it the country to your country. I d. And I'm going to make the variable right now. If I try to perform the relations between the country are the in here and the country I d in the country's table. It's not going to work because they are different data time. This one in here is of type enger on this year off type variable character. So I need to change this. Make it an end. Andi, if you lie this Okay, now it's a stipend. If I go do the M Please. Let's refresh on databases, as you can see right now are databases is growing and we're going to get more information, more detail. As we go into we go do the country I d. Here it is now it's an empty match. The time I can go ahead and added, But before we go ahead and add it, I need also to create the court. Will are being, and I'm going toe link this also to the department i d table that I've just created. Okay, I know that's all hot of worried, but I know that you're getting it. It's all about relations. This is his relational databases. So let's go ahead and make it also e inger, um, sections Apply this. Why, and that's it. Now we need to go to this having here foreign keys on. We start adding the religion. So the 1st 1 going through the country and the so I can come in here and give it the name country already maybe foreign key one. It was just a random name. Just maybe an abbreviation for the relation. Are we going to link it? Do the human resources countries and we're going to you, the country i d. And we're going to references as you can see in here. See the differences Kohler going to be the country I d in this table, This drop down list in here as from this table. So if I change it, it's going to be changed if I change it to any other country. But it will be pathological. It would be a logical if I have it the cities, for example, because I'm going to leave the country. So the country I d. In my column and please going to be link it to the country I d in the country stable to go ahead and click. Apply? Yes, yes work. Let's go ahead on at the department I d. It said only one. It's going to be apartment ready for in K. Anyone belong to to you that's going to be the department's reforms. A table going to go to the department, call them in way table in here, and I'm going to choose the department i D. And if you check the code that's going to do the same at from Strange foreign key. The difference is the same that we were doing. Okay, it's go ahead. I'm close. All of this going to a fish money database or tables on. If I open the MPs right now, check that everything is working. I think when seeing here the pole between that. This is e foreign key, the country I D and the department Already difficult ID relations. Oh, the foreign keys. I can see the department's is going to be. Link it to the department. I d. On the country's is linked to the country's country I D. So I hope that was easy. I had fun. I don't Maybe for the first time it's going to be a little bit complicated, but you're going to get used to it as we going. We're going to add more tables on more relation on. I hope it's getting easier was time. So that's it on to you in the next lecture 16. Exercise Create Tables Previous Experience: Now it's time for a exercising exercise regard to create a new table called previous experience. This table should contain any previous experience any employee had in the past the key elements on the columns of this table to contain these information. The story day the M date company named on the city on the city is going to be link ID Do. This city is people that I've created earlier. So you might have a problem in this structure in here. So I need you to sink about how we're going to solve it together. Don't work but you to go ahead right now on trying to create it on your okay, I got with this easy. Let's go ahead. I'm trying to do together. So I'm going to create this table using the workbench on to choose create table. Give it the name previous score experience. It's not experience. It's three minutes. Okay, Now I'm going to add the colon. The first column, of course. The employee I d. I need this in here so that I can link it. Oh, no. This record belongs to which simply so I need this column first. The employee, Heidi, is going to be set as primary key that I need the second column to be the star date going to be time they There is also another time called they time. If you've notice it, they time simply storing the date on the time, the difference in the date and the time that the data is storing Onley that date without the time. And in this case, we need the date on Lean at the daytime. Not need the time that he was hired home. Okay, on the and they also have the time date on in here. I'm gonna chew the company name and go ahead and make it whatever character is good. Maybe just crazy once maybe 150. What's good? Um, here, the city year. We're going to have the city I d. That's going to be a stipend. We need this one is created. Boyens take was deleted. Now I need to link this city idea in here to the city. I d in the status table. Let's go to before in Keast have it's gonna have another name for this key. I'm gonna choose. For example, cities score Kerinchi one conference table is going to be the cities on the colon is going to be the city in the table cities Okay, done this before. Let's go back on a lie. What is done on here? It iss It's created successfully, but we have one little problem the innocent. If you take a look, let's see the table that we've created here it is straight. It successfully here is empty i d It's primary key And here is all fields the story date and a the company name and the city already other foreign G without any problem. But we have a problem with this structure that each in pre with the structure will be allowed to have Onley one record because the employee I d is one in here on. We agreed before that the primary key is not duplicated so I cannot duplicate another value . You cannot say that one for example, innately one is working for X company Then everyone was working also in the past for a company. Why company, for example? It's not going toe. Let me do this if you think about it. So how can we sold this? Remember when we discount the primary kids, we said that the primary keys can be one or multiple. Coghlan's. So in this case in here, we never Coolum for the primary key toe worry correctly. So I'm going to juice. I can make it these torrid eight if I want, but I don't prefer it to be the day I'm going to adhere. E simple column called Serial on the Syria is going to be used to live event again. That's created behind steak. Go ahead on Move it. Okay, on what I'm going to do is I'm going to take this up in here on both of them. I was going to be the primary key. Will it work? Yes, it can work there. Any problem And this is called composite key. It's not only one column that the king I haven't applied. Why on now? If we take a look to our table, we have to Coghlan's as a primary key. Really interesting. If I open the people, I'm entering the data. Now I have the MP one, for example of two or 3 100 they have a serial so that I can say that maybe he worked for this company and this company and this company using the serial on this can be increment that they want 12345 And we're going to see this indie peel when we add the date to order debates. So that's it about this exercise. See you in the next lecture. 17. Create table using Table Data Import Wizard: Welcome back, everyone in this picture we going to do something special. We're going to create a table using the table data import with it. So it's going to create a table on fill it with data arrest. So in here, I have an excel file containing the status I d. On the status name. I would like to a blow this file as a table into my database containing the different status that all of us can have Can be a single, engaged, married, an insider. I can grade Andi even add more to make it. I'm known, for example, or any other thing that you can go ahead and that. But first, we need to convert this except while into a cease feet. So I'm going to just fall safe as no matter what Virgin of Excel you have. You still have the option to safe it as he sees V. Okay, so here it is. Sees b comma delimited. Yes. I'm going store eating here. Yes. Okay. Yes, on its No. Now in the database, I can come in here and use the cable data import Whether then I'm going to choose the status data next every time we choose Use existing table. This time we're going to choose Create new table on Dhere the name of the people. But I don't want to have it. A space. Maybe I could make it like this. Or a lab that underscore what's next here. The day down here in the corn was going to be created. And here is the data talk Best frequency. I can go ahead and choose. So the state aside is going to be an aunt and the status name is going to be text on. Once the table was created, I can go ahead and change the date the time. So next. Next. Next on, uh, here, the magic. If we refresh whole database, we have a new table called status data. And if we open it, we can find that there is the Islay on the status name. Was that asked doing anything? I didn't drive Nicole or using the worst work pins to trade a table. It's created automatically and filled with data really, really interesting to you in the next 18. Import Data Into Database: come back. Everyone in the sexual regard had some data to our database. All they've been doing in the previous lectures where defining the structure off database. But we didn't add any data, so our tables are empty, but we're going to add some data using the import data withered off the work bench to How can we do this? First? I have to really be doing first the department's countries on jobs. I cannot do the sickest first over the M please, because if you remember, if I would like to add some data in here, there is some foreign keys in here, so it's going to search for a country I d. There is regulation or restrictions because we don't have a countries. Right now. It's empty if we choose it. Selected empties. There is no data in here. As you can see, it's empty, so I have to fill the sub table. If I can name it this way, which is the Portman's countries and jobs before I start adding the in please on the city's Also, the city is being link it to the country, so I cannot fill the city's without filling the parent, which is the country idee? Okay, it's gonna have on. See, how can we do this? Okay, I'm going to choose from here. Cable department import with going to choose the countries data. I have two options to import the data into my database. Sees we from at all the Jason from a I'm going to chew that cts V I can call we having created using the Excel, as I have done here, have Excel files on I just to the format all they sell fire to B C. C. Varies, go ahead and choose next on in here. I have two options. So, like the destination tables, I can go ahead and create a new table based on the structure off the C S V file. If I opened the CSB file in here, for example, the country's can see that it has the same structure off my data base. I have the country already in country name deprivation on the capital. So this is the same structure. So it's going to create, and you table based on the structure off this sees be fun. But that's not the case on one of the amazing options in here that I can drop the table if exists, So if the table is different or contained some data, it's going to drop it. Then Greek create at using the table data import. You're not going to do anything. It's going to do it for you. But that's not the case. We're going to use an existing table. The table was already there, but I need to fill it with data and in here have a list for the available tables, and I'm going to choose the one that I want, which is countries grant and click next. And from here I'm choosing the source. Coolum. We call them from the Excel, far all this easily fall and the destination color they are matching without any problem going to choose next. Now it's telling me that it's going to perform two tasks. Prepare import and important farmers. Good. Go ahead and finish it on. If I chewed the data was the lady. They end the table on the country's. I can see that I have here the data indices refile imported into mighty. Very interesting. Let's go ahead on do two more. I'm going to choose in here. The Boardman's Kerry next again. Using extinct table departments makes everything's good. The department's going to the department. The names going to the name? Yes, okay, it imported a trick. It's as you contain here. Okay, if I check the departments, I can see that he raise my data that Britain, the seas, before again the production research on development pertains, saying marketing. They have just injured that they are just the mediator for trust used later in the upcoming lectures when we are using on selecting data from the table, So we're going to use them later. Again, let's do the last one. For now, people data, I'm going to choose the jobs. Also, I have set of jobs in here using this thing table dumps, makes and years, as you can see, that source Colon have the minimum salary. Coghlan's called minimum salary in the database called Job Minimum Sellers. So it's mourn enough to know that this column is going to this colon. But I can go ahead and change it if I want, but it would be logically wrong. And then here also the is managerial. There is a space in here it new or figure it out that it's going to is managerial without the space in the destination or and the debits had his next Yes, 10 regards imported by go to check the drops in here. Here I can find that here is a set of jobs with minimal around maximum salary already added to my people. So it's very interesting. Next, we're going to see how can we have the cities on increase to you? NZ next lecture. 19. Exercise Import Data (Employees and Cities): and the structure. We have a new exercise. I need you to go ahead on the complete. The import that we were doing to our databases. I don't like you to go ahead and do this to tables in, please. On the city's you will find the sees. The files for the increase on cities in your resource is go ahead on. Try to add them to our human who chooses database. Okay, I have It was easy. Let's go ahead and try to do it together. So in here, I'm going to click on Able Data. Import with it. I'm going to choose the data. It's really selected. I'm going selected again. Here it is. Next on, I'm going to use an existing table going to choose Talese. Chris. Next. The reason seems, God, go ahead on and it's next. Next. So that's finished on here it is. All of this record has Bean imported his own problem. If we check it right now, we will see a lot of data here. It is really interesting. This is, of course, dominated. Does not hold a citizen's adjust the sample data so you can see that here we have an idea for the city name for it on the country. I d If we take a look here Old New York, Los Anglos Chicago used in All of them are taking this number one in the country already. If we check the countries, we will find that one is use eight. Here it is United State of America. If we go down, take for example number, Do you hear it? Is frank for it. Colon sn stood guard. It's Germany. You're taking number two, as you can see in here. So it's doing very fine. And later we'll see. How can we display the name rather than number one or two using joins on views? Okay, Now let's go ahead on complete our exercise. I'm going to import the in, please. Right. Click in table the to import with a New Jersey, please. Later, through the existing table, using please thing seems very good. Yes, I had mixed next Johnny finish. We check the in, please find that we have some bigger units and begin also there is eat countries for the gym plea and the department. If we go, for example, apartment number three chicken is going to be the processing altitude. So I hope this was fun to you in the next lecture 20. Exercise Primary and Foreign Keys For Status Table: Okay, time for another small exercise. I would like you to go ahead and created primary and foreign keys for the status table that we've just created. Using the table data import with it. So the primary he's going to be in the state of stable itself on the floor in key is going to be in the in. Please. Table toe. Link them, please. To the status table. Was the video go ahead on trying to do on your own? Okay, I have we've seizing. Let's go ahead and try to do it together. So the first thing I'm going to do is to set the primary key for the state of stable. And I'm going to remove the data in here that the school day they don't like this name so I can go out and simply change it on the comply. It's going to be changed. Yes, change it. So let's go ahead. And the first thing, which is the status I d make it a primary game is to click on this attribute in here. Um, click apply. It's going to be turned it into primary key, as we can see in here. So the 1st 1 is done and the names change it successfully. But go ahead and fly to add the state Assaidi through the people in peace. Open it. Glad and everything here going to be the It is Heidi. It's going to be wound on first ports done flight, we evident we need to define their relation in here and going to foreign keys. I'm going toe the lane for this foreign key status. We're in key going to chew the reference a table, which is status that he has changed from status and school data to status on Lee. Going to choose status I D and going through the state Assaidi Colon client on its done. If I chicken now called this chicken here down, that's refreshing. First, here it is. A state society now is a Frankie. It's unfold as we can see. It's of Type M. It's link it to the status table in here. So that's it for now to you in the next picture 21. Export Data out of Database: Welcome back, everyone in this lecture, we're going to export data out of parliament database we have seen previously how to import data. A lot of times in this time we're going to explore the data. So I'm going to choose the cities, for example to get it out. I know that I'm importing the city is already but assuming that I have a different data and it's that important and I would like to get it out off my database. So all I'm going to do is right. Click and choose table data export with Choose it from here are being asked to choose which Coghlan's that I'm going to export. I'm going to the city unity on the name without the country. I d. From here on selecting all the entries, all the records, I can go ahead from the road offset to choose way to start on the count. How many Rosa Kohler Likely export. I'm gonna leave it to all ranchers next in here. I'm going to specify they find past. I'm going to export it in here. This folder is empty right now. As you can see, I'm going to name it List of six second grand and change it if I want on. I'm being asked truths easy. Or Jason, I'm gonna leave it to cease V on. This is very important. Contributing here, the fields greater. And you have an option to choose. Semi colon, colon, comma or tap. I'm going too choosy, comma. And I'm going to press next. Next, Next on. Finished on the phone. You have been created. Here it is if we open it list of cities. Here it is. Here is the city oddity. And the city name was on the country. Heidi. Very easy to use. A very, very good feature. See you in the next lecture. 22. Entity Relationship Diagram (ERD): Welcome back and this lecture. We're going to discuss something really important and very powerful. It's cold entity relationship diagram or commonly known as e r. D. Why would they need this? E R D If you think if it As our databases growing, for example, are small little bit, the human resource is you cannot keep track off the relationship between the tables. Which columns which primary keys Frankie's, what is connecting toe work? So it's going to be, Ah, hustle when you have a lot of tables. So in the situations he already comes in handy. So let's define the already What is the into the relationship? It's a visual form off the relational databases. It shows entities for the tables in the database, on the relationships between tables. Within this database, it's essential to have an entity relationship that it shows three basic elements tables, the attributes or the columns on the relation trips between them. So let's go ahead and see How can we generate this BRD diagram in work bench. So here, going to connect to the before one the password. Okay, here is our human resources, the debates I'm going to choose from here from that. The base actually reverse engineer. Well, I think liquid it It's going to ask you for the credentials, these enormous passwords. OK, give it again for its next on here. I'm going to select that. The reason Want to choose our the debate? The human resource is click next. Thanks. On the really important ways he'll table object. Okay, go ahead and import all of them on and in seconds. We have this four or there's no You see, this is all tables. This is the e R. D diagrams. It's very beautiful. It shows the tables as we agreed on the columns with the times on the relationship between each tables very powerful, so I can go ahead and organize it. This table, we don't need it. Really? The person's because we were just this thing. It we were creating our first. They basically which shows. But we don't need it right now, actually, so I'm going to work with the other. So in here I have the three stable. As you can see in here, I'm gonna take it up here. So I have the employee I d. You've type and I have the first name very well, director. The last name variable director, the dinner country on. You can see that if there is a relation between anything that's going to show up in here. So, for example, if there is a relation between them, please that we really made on the country, that's going to tell me that there is a relation on when I over over this lining here, it's going to tell me that it z country I d in here around the country, 19 here, as you can see, really, really easy to see now the relationship that's going to the database and we have also another relation between the countries on the city's If you remember. So we have here a country I d around the country I d in the cities. So now I can see the relation trips between this tables in a visual form. And if you think of it, here is we created this job table, but it doesn't have in relation right now. So now I can figure out that there is no relation means of jobs and any other table that maybe I forgot to make relation. Maybe that I need to make it later, sir, it's very powerful, even the state of the newly created table it has relation with over in here from the status I d to status society in here on the department's. So it's very powerful way to visually see the relation between the tables as you're building more tables in the future, so that would be it on. See you in the next picture. 23. Select Statement: Welcome back, everyone. In this lecture, we're going to discuss one of the most important statement, which is the select statement. Probably. It's the most famous and the most important statement which would allow you to retrieve data from a database old from the tables. Let's go ahead and take a look at it. So here is a very basic select statement. As you can see, I'm selecting data from the M. Please table. Let's take a look and see how it is constructed. As you can see, here is select key word. And here the science gold asterisk on you can find it on your keyboard on number eight. Great. And we have from on the table name. So here I'm specifying what I'm selecting this mean here this Astra's mean here that I'm selecting all the columns. Let the employee I d. Or the first name on Lino all the columns, without specifying names and And I bet we're going to select the specific columns on this from here is going to tell the database engine from which table I'm going to treat. They turn here the table may I can go ahead and change us making cities for example, and they can run this as you can see in here, you can go back to him, please, and run this again as against Hughes. Older date. So let's take a look. How to select Specific column. Maybe I would like to select be the I. D. First name and last name on. Lee said I'm going to type select, and they're not going to use this sign here. The asterisk. No, I'm going to like simply I d That's the first column that I'm going to select. First name on last name. And as you can see, the are separated by commas. Okay, Now we have the from No, please, to tell the intimates that I would like to select from this stable. Let's go ahead and try to run this. Um, let's take a look. Here it is. We have the MP i d. For his name, unless name on Lee. And they can go ahead and keep adding I can come in here until it, for example, maybe the date of verse. Okay, well, I'm going to run this on right now. The dental burr is going to be displayed in here. Let's run this on, as you can see here it. So it's very flexible, actually, and very straightforward. Let's go ahead and try something different. If I have a lot of data in my table like a thousands frequents, that's going to be a lot and probably going to do this. I'm going to select only a portion off the data if our uses elect statement like this and they have 1000 Africans. And I told you it's going to be a headache for my database engine. So it's but a favorable that we only selected portion without any filtration. Later, we're going to discuss the wear clothes and how to select based on a specific condition. But if I'm selecting generic data, I would like to limit it to a specific number. So let's try this. Going to again select old from and please on them going to talk about this here, limit five. This here mean that I'm going to get all the records from the employee stable. This Astra's means all and here the table and I'm going to limit the result off this query . The only five records. Let's go ahead and run this. This is what I mean, it's only five rows of five figures that I can see now. If I increase this to seven, maybe it's going to be seven. As you can see, if I even choose one, it's going to be one as you can see in here, so it's very powerful. When you have thousands frequency, it's going to really matter. Okay, we'll make a difference on you can also use it with this. So if I take this is a copy, I'm going to type it in here. I'm like I'm coming here until it limit three. This means that I'm going to like only three. I run this as you can see. Here it is Onley three off these selected colors and if I remove one of them, we're again going to work without a problem. Okay, As you can see, it's very powerful. So that's the very pay six off the select statement. We're just scratching the surface off the select statement on the upcoming lectures. We're going to discuss more and more about the select statement because there is a lot to discuss about this leg statements, but we're going to take it 1.1. See you in the next lecture 24. Exercise Select Statement: Okay. How about a small exercise? I know that we didn't learn a lot about the select statement. Yeah, but let's go ahead and just this small knowledge. So I need you to go ahead and select some data from Shaquille. A database. I need to go ahead and select the title. The description. The release year rating. Linds from table called Fell and Secura Database. Go ahead, bozo Video, try to do this exercise. Okay. Welcome back, everyone. I hope that you did it without a problem. Let's go ahead and do it together. So I'm going to tell her that I'm going to you. The security of the base. This is a separate statement. That's why I'm having the semi colon here on. I'm going to select the Tyco. The description. The release year. You rating, Linds from film table. That's good. And just this. Yes, here it is. Here, the data from this table. And as you can see right now, you're limited to tempt. I can go ahead and change the limits from here. I'm going to choose. Don't limit. So I'm going to run this. As you can see now here is old data and this, Abel. You like to take a look at the structure of the table. You can go ahead to the security to base tables Gold way down to film to the base from table. Actually, here it is. On here is a different columns. If you'd like to take a look at this colors. Okay. It was easy. Let's go ahead on and see something different. See you in the next lecture. 25. Where: the wear gloves wear clothes is used a lot of times with a select statement where is used to filter records or rose best and a specific conditions. Let's take a look at this table. That's table here. This is some of the signs that we can use with the where, and you're the sign, which is the equal. We can say some condition is equal to worm for a specific name or specific country or specific gender, as we going to see even not equal this sign here mean that it's not equal to a specific value. Also, we have the greater san lists and British that are equal. Lizano equal. We have also is a bit win, which is going to select between arrange values or like like it's very powerful. I'm going to see how to use it later in also services and a list off values. This is some of the signs or keywords that we can use with where witness elect statement. Let's go ahead and see How do you this in mice again? So I'm going to normally select the records from the empty stable. I'm going to run the square here is here is all the records without a problem, I can go ahead and select, for example, the females on Lee or the males. So how can I do this going to having here where, as you can see, it turns blue? It's a keyword, and we need to specify that Coolum, which is the gender. This is a Colin that I'm going to filter my query based on. And I'm going to tell it that I would like to select the females. We need to arm a single coating here because this is a string. If I remove the single coats from here, the my SQL database will sink that this air, the colon or key word and it's not going to work as you can see. No, I haven't error unknown colon female in the wear clothes, so we need to tell it that this e data in the table it's not a colon, it's another value. Let's go ahead and run this on. Here it is. Now we have only the fee males. As you can see, I can go ahead and change this to B man. So I'm going to get the males only as you can see in here. Very straightforward. We can also year this. I'm going to copy this here on going to select the females. But I'm not going to you The equal I'm going to use if you remember, this sign here means not equals. I'm going to year this. This means that I'm going to use all the columns from the M. Please table on. I'm going to set the gender knot to equal male. So it's the opposite off male with the female. Obviously I'm going to run this on. Here it is. We have the females. If either the female in here, this mean that I'm going to get the opposite or not equal to female. So I'm going to get all the males have that you get. We can also use it with other I'm going to take this is a car without a column. So I'm going to, for example, select the country I d. I'm going to get all the employees from the country number one now we're not filtering based on the gender were filtering based on the country. I d. We have John here. Will friend Noah are from country number one, no matter what the gender is we're going to filter based on the country. Here it is. So we have. I only forget Jessica. Sorry for this. Discuss. So we have four and police from country number. Well, I can go ahead and change it to be number two. You can see we have the employees from number two on. They can go ahead. Also am Jozy not equal going to you? That so that we have all the employees. That's not equal to two. So I have one and 6345 But I don't have to. I can go Heaven filter one will remove one from the query. As you can see, I have 23456 without the one or the country number one and please, that you get this so I can even combine different. Where is by this? I can go ahead. Until that I would like to select the employees from the country Number one on the gender equal men. So I'm going to get all the males from country number one If you remember the where four. And please one female and three males. Let's run it. I'm running it without this other condition on Lee the can Freedom one. So I'm going to get all the employees from Henry. Number one. We have four records. John Wilfred. No one under scare. One female and three males. If I added the male condition to this. So now Jessica is going to be filtered out. Let's run this on. We have on Lee Street. As you can see, if I reversed this Teoh be female is going to work the same on them going to get only the female, which is disc in the country. I d number What? I can go ahead also. And you the date. Do you the date? Actually, it's very interesting. I can come here until the select all from I m please. Where come grd? Well, one. That's abba seem cooler in here. I'm going to get all the employees from number one and I would like to filter based on the date of births over the date would be maybe the employees from months marsh. Okay, so how can I do this? Going to hurt this condition? Andi, if I added deal, be equal three. This doesn't make any sense. If I run this right now, I'm going to get me anything. Actually, there is no at a diverse equal street. There is a months equals research in here, and we're going to discuss this later in detail. But just to give you ah, sneak peek for the function that is used with the date. This is a date. If you remember, the little person is off type date. We can use this here till it life to get the months off. This This is one of the function that the my SQL database engine is giving us that I can get the months out of this year or the year or the day or any part of the date using this basic keywords in here months. So I'm going to filter now. The country I d. Number one On the months off, the data burst Equal Street. Let's take a look at this. Should have one Empoli, which is John in here? Which is the data burst off. John is street. If I change it just to be five and run it again. Now we have Well, Fred, which is the data burst off will, for it is five. If I choose something that doesn't exist in country number one. I'm going to get no results. Run this right now. Residencies. There is no result. I can go ahead and change this to be a day or a year. Let's go out and dried up. Let me first check the data. So we have 1980 Tuesday. I'm going to select the please base the own the year, which is 1982 going to change this month in here to be year. I'm going to add this member 1982. I'm going to wellness. I run this right now we have John. Here it is. I can go ahead, of course, and remove this condition and filter based on the year on Lee off the data burst without this condition in here to make it more results to get me more results. So I'm going to remove this in here, and I'm going to select on the year. Let's check if there is other in please Born on the same year. Asked. There is another warm It's Emily. As you can see in here, it's country number six on it's also 1990 A. I can go ahead also and get the greater center equal 19 82. If I run this now, it's going to get me old employees from starting from 1982 on and till we reach this year. And it is. We have 1990 1986 1982 and 1980 to 2 times. So that's again also very basics off the wear clothes. We're going to see more examples on MAWR scenarios that we go in through the course. This is just e introduction to the wear clothes that you get it on. That's it. See you in the next lecture. 26. Exercise Where: Okay. How about Small Exercising about the wear close? I needed to go ahead and select again. Data from secular needed to go ahead and select all the columns from the film table and select all the movies or the film's really girl or equal 90 minutes. Both of you go ahead trying to do this exercise. Okay? I hope that you didn't have that problem. Let's go ahead and try to do this together. So I'm going to use Secura. I'm going to time Select asterisk or all the columns from Secura film Different the database. I'm here the table and I'm going to add the wear clothes suit Where lens Really? There are equal 90. Let's go ahead and run this query. Andi, Here it is. It's a little bit small phones, but as you can see here in the lengths now we have all the movies with the lens over or equal. Of course, 90 minutes, as you can see. Okay, but with the exercise, let's go ahead and continue discussing more about the select statements. See you in the next life 27. Order by: we have seen how to you this A late statement we have seen how to you the where I don't think he would. That is used usually with the select and the where is called older by. So what exactly is ordered by What about is used to sort the data retrieved from the database in ascending or descending order by default, it is in ascending order. Let's go ahead and see how to you this order like you work. So I'm going to select from the empty stable. If you take a look at the data in here, they are not sorted based on a specific order. If you sing, this year was taken. It's not being sorted if I would like to sorted by the country old data burgers or alphabetically by the first name or any other colon. If I have different table, I can go ahead simply a new this very powerful keyword order. Beiber. Don't forget to move, the cynical from here added at the end of the statement order by, but it's still not yet complete. We need to have a call them so that the my SQL database engine can no, which call him that I would like to sort it with. So I'm going to choose first name. I would like to sort it by the name. As you can see right now is not being sorted by the name I'm going to have to execute. So as you can see the first name here, the order has changed based on the first name. If I change it to something different maybe like the gender has, it comes easy and now and messy and not in a specific order. Because we're based. We are ordering based on the first name going to execute this on here did the first. The females are the first on then the males. I can go ahead and reverse its using the ski word descending. And they told you in the presentation that bitey fold ends ordering it in an ascending way . So right now I'm re voicing this operation on them doing it descending. Wait, Refresh on. Here it is. Now we have the male on female there. Of course, sorting it alphabetically on this is the sending through. The M should come after the f. Okay? You haven't to the country already. You don't look all of this country already again. It's going to start from one, 23456 Senator, if I add the descending, it's going to reverse the operation. Okay, I can go ahead and add another call on two sorted west so I can go ahead and sorted by the can t I d on the name, the first name, for example. Let's go ahead and do this. To do this or you have to do is just add Ikuma First name. Let's refresh on. Here it is. We have the country i d This is the soared calling them one And then we have the first name . So we have the country I d than the first name. As you can see, that's one of the ways I can go ahead and reverse this to make it for example country I d in a descending way. Then the first names on that we have number six. We have Emily and John, then number five. We have Ali and Meyer. I can go ahead also end at a descending in here, sir. Right now, everything would be ordered in a descending way. The country, as you can see now, we have John, Emily, Maya and Ali the opposite off the last equerry over the last result, I can go ahead and add he surgical or, if I want, maybe the date of birth. But right now it's not going to be much for difference because they are not a lot of data. And you Okay, so that's how to you the order by order by the column that you would like. And if you like to add a descending, because by the fold it's an ascending order. It's very easy to use on the are used a lot the select Where on the order by so that sent and see you in the next lecture. 28. Exercise Order By: Okay. How about a small exercise about the order? Boy, What I need you to do in this exercise is to select older columns from the film in tequila database sort violence and a descending way. And then why the title? Go ahead. Well, the video trying to finish this exercise. Okay, I was seizing. Let's go ahead. I'm trying to do together. So I'm going to use skewer and I'm going to select old from Phil and I'm going toe order by lens descending to order it a similar way on then the title. Okay, let's run this truly it's running old. Yes, he lives. Now we have all the data Frawley film table, As you can see here and the ordered by the lengths as you continue in a descending way. This is the longest period as you can see 185 from her 84 it's counting down goes this the sending keyword in here and then we have the title. If we go all the way up to 185 we can see that the title is being sorted. As you can see here the A then the the title is in here at Chicago, Nords Control and high value terror has been so you can go ahead and reverse it, See if it's working. I'm going to add a descending in the title. Then I would have to change it. Okay, As you can see, still, we have the 150 85 heard 85 we're still have the title. But it is Reverse it right now. Okay, But you get this. And it was easy. I'm trying with this is small exercising to emphasize on this very important statements. So I hope that you get it on. That's it. You get the next lecture. 29. Exercise And or: Okay, everyone hold that would exist on us and this exercise I needed to go ahead. And so, like the title description rating blends from the film table or schema and the scale a database select any film with the lands between 90 and 95 or they're waiting is G. I need you to stay focused in here on order them by the rating on the lens and in descending order to descending order at the lens. Only another writing. Okay, Post a video trying to read the exercise carefully and try to apply. Okay, I have. It's easy. Let's go ahead and try to do it to give it. So I'm going first to use the Secure A database, and I'm going to select the title description rating on the left that the column depth we need just like from secura dot fill now to the wear conditions. So where the linens greater than or equal 90 on they need to on the parent sees. And here this is the story and then continue my condition on the lens. Liz Ann or equal 95. I'm glowed the Parenti. So that's the first condition that's going to get us the films between 90 and 95. Then we have another condition for the rating on Lee not related to the lens or the rating equal. You see then to the order by board order, Buy rating, Have the lens in descending order. Let's run this query. I'm here It is here The rating first of the gene before any other rating, as you can see, endure, ordered or sorted by the lens as you can see in here. And this condition isn't applying to this here because we have the G. No matter what the Lin says, as you can see this lenses out of the syringe in here. But when the G the rating G will end, then we're going to stick to this condition. That's gold away, down along the way down. The rate is now the G has ended. And as you can see the BG for example, now we stick to this condition. Here you're dis condition is applied to all of the other ratings. Good. Now that's clear for you. Okay, that's it for now and to you in the next lecture 30. Like and Wildcards: Welcome back, everyone to a new lecture and this lecture. We're going to discuss one of the most amazing and powerful features off my sequel, which is the like on wild cars, like actually deter minds, whether a specific character string matches a specified pattern, so you're searching was like for patterns, not actual text, as we're going to see. A pattern can include a regular characters or wild card characters, which is a very powerful feature in here. A wild character is used to substitute any other characters in a string. We have two different options in here. The underscore allows you too much on a single character. So if you don't remember what is this character is, or if your life to get oldie characters you can use for one single character you. Can you this underscore as we're going to see it or you can simply use the percentage allows you to match any string off any lens, including the zero lens, which is very magical, as we're going to see. Let's go ahead and see how this works. So in here I'm going to select all from complete stable, as we have seen many times before, Let's display data first. You can see in here so I can go ahead, for example, and search for John. But maybe I would like to display all of the Jones or any other name that may contain the first letters. Jan O. For example, Let me show you what I'm talking about. I can come in here and say where? First name like and then I will say, John and I'm going to complete it. I'm going to hit Underscore two times. So what I just did in here that I told you my sequel is the first name. Like as you can see in here, it's going to be something like J and O on to more characters that I don't remember. Let's go ahead and refresh this. Now I get John, as you can see, if I removed one from here, no impaling. Do you mind sticking of the debates that it's only one letter missing? I am. That's wrong. It's not actually wrong, but it's not existing in my database. If I run this right now, nothing is going to be displayed because I don't have John without the H, for example, that's actually not very magical. What is really magical about this is that I can use the percentage sign, which is very powerful. So I'm telling my second right now is that I remember that there is a J ended. Oh, and I don't remember the letters. Maybe it's 123 maybe it zero. So if I read this right now, I'm going to get John, as you can see. And if I remove even yo on, run again. Now I have even Jessica because just get older starts with Jake. That's pretty interesting, I believe. So, if I change it, this Owen s so I'm going to get all the in please. With s with the first name starts with s to be specific. If I run this right now, as you can see, I have Sergey and Sofia pretty interesting. But what if I wanted to enhance this even more and get all the employees that contains an s so I can do something like this? I had the percent of design here or the one chord in here, So I'm telling my skill that no matter what the letters before, the yes is over, sure the is Actually there is an s in the middle or in the start or the end. Just go ahead and search, Friend. Let's run this query and sees a result. Our serves. As you can see, we have now. Sophie, answer. Eat like before. But we have Jessica because Jessica has to s is in the metal in here, which is pretty interesting. If I search for the A, I'm going to execute this. Wow. We have a lot of results in here. Sofia. There is an A at the end. Noah, there is eight at the certain place. Charring fled amid all of them containing a If I remove this one here, this means that I'm going to start with an A. And then, no matter what the wrist off the word or the name is Andi, you're going to get me on Lee Ali. So that's the only name that starts with an A. Okay, I know it can be a little bit confusing at the first, but it's really magical and very powerful and searching patterns. If you like to search for specific pattern, as I am doing here, As you can see, only Ali starts with an A no other names and if I removed it from here. So now I'm getting old names that contains away anywhere, but not the beginning. Okay, I know that this is a little bit confusing, but it's very barbell. I you this here a lot. The two wild cards, the percentage in the front on in the end, on what I need to search is in between. That's very common and very powerful for my vineyard. Okay, that's it about be like and wild cards. Let's test your skills in the upcoming lecture. 31. Exercise Like and Wildcards: Okay, everyone held out and you exercise in this exercise, I need you to go ahead and select the film I d. The title description. Special features from the film and secure database select any movie that consent is off two words. The first board start with an eight on the second board starts with C. As you can see, we can use alike and the one card search for this better. Both the video read this exercise carefully. I'm trying to apply. Okay, everyone, it's easy. Let's go ahead and try to do this together. So I'm going first to use It's a killer and I'm going to select Phil I d. The title description on this special features color from Phil. Let's test this willing to one this, OK, it's working for him, but we need to add the wear clothes, so I'm going to add where the title like you're the Tricky Board Later, a and the first letter of the first word. Then, no matter what comes after it, then a space. And this is very important this space on the second word, which is going to start with C on, no matter what the characters that followed the seat. Let's execute this on. Here it is. We have these movies we have allowed in Calendar Alien Center, American Circus and etcetera. Eight. First word C. And the second word. Okay, that you enjoyed this on. See you in the next lecture. 32. Distinct: Welcome back. Everyone in this lecture we're going to discuss and you keep worId cold. Union Union of Raider is used to combine the result from two more queries or select statements. If I have to separate where is was the result from the select statement, I can confine them in tow. One result. Pretty interesting. Each select statement within union must have the same number of columns. That's obvious. The columns must also have a similar data type. You can go ahead and complain, select statement with three columns to a select statement or agree with 10 columns. For example. If my going to matched have to be logical, let's go ahead and see this in action. So and you don't going to select. I'm going to use a killer again and they're going to It's like, Oh, from customer Customer is a table and Secura, let's take a look at it first. Here it is. As you can see, there is the customer i d. The store I d. First name, last name email and the doctor I'm going to out of wearing here, where the first name like going to get all the name that starts with the name. We're going to order this order by first. Me? This is a vory basic select statement. I'm going to run this and there's a concern. Here I get only the names or the customer was their name starts with an A, as you can see on a need any Antoni Antonio Averil edited. Okay, I'm going Toe Animal Select statement. I'm going to let again from customers. I'm going to act as if customer. I'm going to act as if these are two separate select statements from two different tables where maybe this tour Heidi equal to and I'm going to order by the first minute. Thank So that's a different query. It's again selecting from this. So as you can see, there is some customers. The first name starts with an eight as the first statement in here, but there is other customers. As you can see, there is with sea. There is was D we have down you? Yes, Daryn Dura Ella. That's obvious, because I'm not filtering based on the name filtering based on this tour. So these are two different select statements are acting again as if they are from a different tables from a different database. Even you can compare it from different databases or even from different servers. But just to keep it simple, we're going to act as these are from two different tables. The first name that has risen and the store number two. What if I want to complain? Z's too. If I run both of them right now, I'm not going to get both of them. This is going to be executed. Them this is going to be executed and I'm going to have the second result. What if I wanted to have them in one result, A one result grid in here. All you have to do is at this magic you ward union that that, yes, that's it. Nothing more that you need to add. But as you can see here, there is an error because I cannot go ahead and add an order by and the cynical. And here I need to remove this and wonders Remove the errors gun. And now this is a complete select statement. Was union without a problem? Let's run it and see the result. As you can see in here here, the names as you can see, we have values Adam and hear from store number one Adam from the first select statement. Because in the second statement or the second select statement, I have only store number two. I know it's a little bit confusing, but I need you to stay focused with me. We have Alan also from Stone and one that's coming from the first select statement. Also Albert, Alice, Alicia, Alan and the teacher and then after the customers with the A starts with the A Listers. Now we have Vicky, Ben Hess, Betty Beverly that's coming from this here. That's pretty interesting. I hope it's not that complicated for you. I would like to show you something. There is an Adam in here, coming from this result on and an Adam and here or Arun from the first select statement and then Aaron or a general idea. Maybe there is. I record coming from this here and a record coming from this here, too. I can go ahead and ab Oh, and if I run this right now, it's going to be displayed the same as here. I have to. Arun's in here, as you can see to errands. One is coming from the select number one and one is coming from select number two. Pretty weird if I removed the Erin, it's complaining the columns. It's combining the rose that they are similar. If there is a doublet getting here, Union Onley without union always going to compiegne them into one and this is very logical . But if you add the old G ward, it's not going to be combined. As you can see in here, I have to add Ran to Arun Good the exist in this select statement and in this select statement, let me show you out drinking here. We don't know for sure from which record this is coming from this area of this Erin, we don't you know for sure. We're just giving. I can tell you attract in here to know which records coming from. I can come in here and start adding columns after the asterisk and a coma, and they can go ahead and add columns. I can come in here on Ada's drink. I'm going to go this or at a value result from first query or for a statement. Great. Well, I'm going to take this of the copy. Yesterday was the time in here. I'm going to add it on at the second. So what? I just did it, I added. And you call him that didn't exist. Actually, in the database, I can go ahead and do this. That's a trick. Let's run a seat, actually. So if I run this, as you can see in here results from the first statement, it's only because of this here. I'm going to Adam rename it. That said the name of the column based on the added texting here. But it actually doesn't really matter. What is mattering is what's inside of it. As you can see in here, I can see that now. This Erin And here is from the first statement. This one here is coming from the statement. The second Arun is coming from the second statement, as you can see, and if I go all the way down, I can go ahead and take a look at it. As you can see. Second, now all of the other first name. That's not starting with an A. As you can see in here, coming from the second statement, that's pretty needs and pretty interesting to change the title of the column. I can go ahead and ab as stay Grote. I'm going to run this. As you can see now, the statement This is a result coming from 2nd 1st 1st 1st 2nd And you can go ahead and do this. Where's whatever colon that you want? You invented this without being actually into the debates this has created on the fly. If I can say this, Okay. I hope that's really I love that complicated for you. If you still have a problem going heaven ask me. Excuse me. If sometimes I answer a little bit late. I got a lot of messages, so forgive me on this, okay? I hope that you get this on and that's it to you in the next lecture. 33. In: in the very short keyword in my sequel, but in my opinion, it's very helpful. Let's go, Adam. See how to you the in Cuba or so If I told you that I would like you to go ahead on select all the data from the M, please stable where the country is one or two or six. Let's try to do it together. And so far, if you're following the scores on your not really an expert in my sequel, you're going to do this. It's a link all from Okay is don't forget to use human sources just to make sure that we're using this data base where the country already equal one or the country 20 equal to or the country 20 equal. Six. That's probably what you're going to do on. That's totally fine. It's not wrong at all. I refresh this right now. As you can see in here, I can see that I have these options on these results. Entry number 12 and six. That's fine. But what if I want to do select another country? I'm going to keep adding these countries in here. It's not applicable. It's not. That's not that flexible. So here, in key word comes in handy. So I'm going to perform the same result off the squaring here. Just in a different way. Here. I'm going to select old from in, please. Where? Kmt rd on them go into you. The key word in I'm going to open currencies uncertain, adding the value that I need 12 and six We colon and drink this. Okay, we have the same result. We just didn't have the older right order by Jayanti. Okay, so we're going to get the same result from this gray just in a shorter Andi. Fast, direct way using the end. Pretty interesting. If I change this to B, maybe five. If Brenda's right now, then I have number five as against you want to and five. Pretty interesting. I believe I'm helpful. So let's try something different. I can even get the opposite of these in. Please. I can come in here and they're my sequel. Not in these values. So right now I'm going to get all the different countries which is 34 and six one. This as you can see now I have 34 and six really interesting and very flexible and working was not without a problem. Go ahead and do this with the city's. For example, I'm going to select oh from cities to disable. Let's take a look at what is data develop breast. We have New York's. You can go so I can come in here until that I need where the city name in what I can come in here of. Start adding, for example, you your maybe Erlynne. Let's try this for now. So right now, I should have only these two cities, as you can see here New York on Berlin and keep adding the city's if I want. If I haven't application or working in a website, I can go ahead and provide this dynamically from the application through the query off the my sequel in Here. Also just work without a problem with the Not if I reverse it this I'm going to get all the cities without these two cities and remember that New York is Number one City I d. One Andi. I'm not going to get City I d. No. One. That's the end. It's very powerful, in my opinion, as I told you to very short, but it's very powerful, so I have that you get it on to you in the next lecture 34. Between: Welcome back, everyone. In this lecture, we have the queen keyword. Let's go ahead and see how to use. It was my sequel. I'm going to use the secular database. This time. I'm going to select old from the film how I'm going to take a look at the values in here. What I would like to do is select all the films over the movies between the lens off 90 on day 95. We have done this before, earlier in the previous section. Using is greater zone on the Lesa, but using the between we're going to do this much, much easier. So I'm going to doing here. Is that aware? Let's definitely call him that. I'm going to have the condition on bet Ween. As you can see on you're gonna need to add to values 90 and 95. I'm going to order like we re in here. Let's run this. You can see we were able to get the results between 90 and 95 in a blink of an eye, just like there's no need to use resign or less out or anything like just you. The between Andi, it's going to get the values from this to this branded ursine that you passed to the my sequel that said about the between on and soon the next lecture. 35. Exercise Between and In: Okay. How about a small exercise in this exercise? I need you to guard. Had I'm trying to select all the columns from the film table that we've used plenty of times and secular, of course. Um, the lens between 90 and 100. And the rating is our or G. Read this carefully on go. I hadn't tried to apply this exercise. Okay, I have. It's easy. Let's go have and sold this to give it. So I'm going to use tequila to make sure that I'm using the correct database. Well, I'm going to select whom from Phil. We're going to at the condition where ladies between 90 on 100 that the first condition on the second condition is on rating in, or this is the string or characters. Or we need to add this single coat in here in the beginning and in the end and the semi colon, and you need to order by order by something like the rating. Okay, let's try to test this. Okay? As you can see here in the let's, it's sorted. First. By the rating, we have the d and we have the lens between our value that we specify it and then we have the are only the R and D is seen here on on Lee The lens between 90 and 100 very powerful took a word that we can use the between. Andi, that you enjoyed this and to you in the next lecture. 36. Union: Welcome back. Everyone in this lecture we're going to discuss and you keep worId cold. Union Union of Raider is used to combine the result from two more queries or select statements. If I have to separate where is was the result from the select statement, I can confine them in tow. One result. Pretty interesting. Each select statement within union must have the same number of columns. That's obvious. The columns must also have a similar data type. You can go ahead and complain. Select statement with three columns to a select statement directly with 10 columns, for example. It's not going to matched. Have to be logical. Let's go ahead and see this in action so and you don't going to select. I'm going to use a killer again and they're going to It's like, Oh, from customer Customer is a table and tequila. Let's take a look at it first. It is, As you can see, there is the customer i d. The store I d. First name, last name email and the doctor I'm going to out of wearing here, where the first name like going to get all the name that starts with the name we're going to order this order by first. Me? This is a vory basic select statement. I'm going to run this and there's a concern here. I get only the names or the customer was their name starts with an A, as you can see on a need. Any Antoni Antonio Averil. Relatively. Okay, I'm going Toe Animal Select statement. I'm going to let again from customers. I'm going to act as if customer. I'm going to act as if these are two separate select statements from two different tables where maybe this tour Heidi equal to and I'm going to order by the first minute. Thank So that's a different query. It's against electing from this. So as you can see, there is some customers. The first name starts with an eight as the first statement in here, but there is other customers. As you can see, there is with sea. There is was D we have down you? Yes, Daryn Dura Ella. That's obvious, because I'm not filtering based on the name filtering based on this tour. So these are two different select statements. I'm acting again as if they are from a different tables from a different database. Even you can compile from different databases or even from different servers. But just to keep it simple, we're going to act as these are from two different tables. The first name that ties was an AM the store number two. What if I want to complain? Z's, too. If I run both of them right now, I'm not going to get both of them. This is going to be executed. Then this is going to be executed, and I'm going to have the second result. What if I wanted to have them in one result? A one result grid in here. All you have to do is at this magic you ward union. That's that. Yes, that's it. Nothing more that you need to add. But as you can see here, there is an error because I cannot go ahead and add an order by and the cynical in here. I need to remove this and wonders removed the errors gun, and now this is a complete select statement. Was union without a problem? Let's run it and see the result. As you can see in here here, the names as you can see we have values, Adam and hear from store number one Adam from the first select statement. Because in the second statement or the second select statement, I have only store number True. I know it's a little bit confusing, but I need you to stay focused with me. We have Alan also from Stone and one that's coming from the first select statement. Also Albert, Alice, Alicia, Alan and the teacher and then after the customers with the A starts with the A Listers. Now we have Vicky, Ben Hess, Betty Beverly that's coming from this here. That's pretty interesting. I hope it's not that complicated for you. I would like to show you something. There is an Adam in here, coming from this result on and an Adam and here or Arun from the first select statement and then Aaron or a general idea. Maybe there is. I record coming from this here and a record coming from this here, too. I can go ahead and ab Oh, and if I run this right now, it's going to be displayed the same as here. I have to. Arun's in here, as you can see to errands. One is coming from the select number one and one is coming from select number two. Pretty weird if I removed the Erin, it's complaining. The columns. It's combining the rose that they are similar. If there is a doublet getting here Union Onley without union always going to compiegne them into one and this is very logical. But if you add the old G ward, it's not going to be combined. As you can see in here, I have to add Ran to Arun Good. The exist in this select statement and in this select statement, let me show you out drinking here. We don't know for sure from which record this is coming from this area of this Erin, we don't you know for sure. We're just getting I can tell you attract in here to know which records coming from. I can come in here and start adding columns after the asterisk Atacama on. They can go ahead and add columns. I can come in here and adus drink. I'm going to go this or at a value result from first query or for a statement. Great. Well, I'm going to take this of the copy. Yesterday was the time in here. I'm going to add it on at the second. So what? I just did it, I added. And you call him that didn't exist. Actually, in the database, I can go ahead and do this. That's a trick. Let's run a seat, actually. So if I run this, as you can see here results from the first statement, it's only because of this here I can go, Adam rename it. That said the name of the column based on the added texting here. But it actually doesn't really matter. What is mattering is what's inside of it. As you can see in here, I can see that now. This Erin And here is from the first statement. This one here is coming from the statement. The second Arun is coming from the second statement, as you can see, and if I go all the way down, I can go ahead and take a look at it. As you can see. Second, now all of the other first name. That's not starting with an A. As you can see in here, coming from the second statement, that's pretty needs and pretty interesting to change the title of the column. I can go ahead and ab as stay Grote. I'm going to run this as you can see now, the statement This is a result coming from 2nd 1st 1st 1st 2nd And it can go ahead and do this. Where's whatever colon that you want? You invented this without being actually into the debates this has created on the fly. If I can say this, Okay, I hope that's really I love that complicated for you. If you still have a problem going heaven ask me. Excuse me if sometimes I answer a little bit late. I got a lot of messages, so forgive me on this, okay? I hope that you get this on and that's it to you in the next lecture. 37. Case Statement: we'll come back. Everyone in this next, we're going to discuss one of the most important statements in my sequel. It is the case statement. Katie Statement. Exactly. Waits a list of conditions on return. One of the multiple possible result expressions. It's like if you're asking about something, if you're familiar with programming, it's like F and out. You're asking about something when this happened. Then do that when there's them. Do that. This is the same pics of the cases statement. We have to drive a case. Well, the case in here to keyword. Then each condition we're going to add We're going to you. This went and the expression. For example, when the first name equal John or Maria When the country I d equal one. When the salary equal 5000 something like this, then do this, etcetera. When a condition then do that we're going to see this in detail. Then at the end we can use this is option of this is mean. This parent sees and hero packets mean that this is optional. We can use else if you're running out of conditions and he would like to include all of the possibilities you go ahead and add Els and at the condition when none of these wins happen , then this condition is going to happen the same as if and else and programming on. Then we have to end the case with end key Ward in here. Let's go ahead and see it and a life example better. So what I'm going to do in here, they're going to use the human resource is again. I will select old from please Let's run both of them, actually, Thank as you can see in here, we have this country. The country right now is in numbers. I would like to display a colon for the country name, which is going to be much better looking than this 16 to Street. There's other ways to do this. We're going to explore this letter, working with joins. We're going to create joins between tables, for example, disabled employees on the city's to display the extra information that we need and get information from post able. But right now to explore the principal off case, and when we're going to do this, I'm going to ask if the country ID equals Sexton. This country one than this country to do in that country under two through. Let's go ahead and take a look at the city's for the country's actually select old from countries. Let's take a look at this so we have no one United States number two German. Let's go ahead and do number one and two first. Okay, so what? I'm going to do it. I'm going to leave all the columns. We're going to take a comma and start adding and you call it I'm going to I didn't hear the case as we agreed. Then I'm going to add the winds that I told you we have to add a case on. You can go ahead on time. The end in here and all the options are going to be in between the case and the first option of the first case when country I d equal one xem, as we have seen in the presentation at this. So what is happening here that I'm telling the my sequel when you find a country equal one , then type of use in a new coal, of course. And here the new column that I have ever It's after the come. Okay, I'm going to run this only let's run this as you can see in here, actually, the Colin name as kind of master go to fix this letter. Was magic unseen here all the m please. With the country I d number one you can find that we have a use in here. You say use a You can go ahead and change. It may be to United States. If you look do this on, it's going to change without a problem. As you can see in here, United States, United States to fix this problem We have seen this before, can come in here after the end and say as country name. So this is going to create an alias for renamed this Colon because this column actually doesn't exist. It's created a number for you here, so I'm going to run. This ran out after you renamed it. That's one on here, this country name and we confined. Let's go ahead and add the older boy so that you can see this better order by country. I d the gam. Let's run this. Okay. As you can see here, the four columns or the four m please from the United States, where you can find the countries one in all of these four columns. Okay, let's go ahead and add another one to What I'm going to do is I'm going to type when country are the equal to Zen. Number two is going to be Germany, I believe. Yes, let's go ahead and execute this again. Now the employees from country number two we have Len and Sophia is going to have Germany and the country name Colon in here. So let's run this on here. It is very interesting. You can go ahead and keep adding the countries. So I believe Number Street is China. Look, four is Russia. Let's go ahead and run all of this right now. We should have now more Kinter names for more, please. As you can see in here now we have in please from country number four. Now we have Russia on this column and three China and the others are no in here because I didn't have specifying any values for them. As you can see in here, I can come in here and keep adding them the number foreign from number six. Or you can use an else if I would like to. This is the country that I'm searching for. For example, I can come in here and the start else at the presentation, not available info, for example. Well, to go ahead and run this on this enough available M four is going to be displayed for all the other countries number five and number six. As you can see in here, it's pretty powerful, very amazing statement to use. I don't does look a little bit complicated, but when you start working, that's going to be very easy for you. That's Grant and try it with Are the golden. We can go ahead and do it with the departments. We also have numbers for the department's Let's take a look at the departments. So, like gold from departments. What's look up this table? So we have number one is production. Let's go ahead and do it. Maybe from number one or number two, research and development and production. So I'm going to create another columns. I'm going to take a common here, going to add a case ongoing. It's maybe make this large so that you can see much better. Okay, that's better. So here is my statement. As you can see, I can go ahead and close it. Um, now, having and you won. And as you can see here, I have my case in here. I can go ahead and close it on old. My sequel script has been hidden inside or collapsing in here on day here in the corner, which mean that I'm going to start a new cooler. So I'm going to add case we're going to add Wen the court mint rd equal. Well, then display production. You can go ahead. Also undo calculation if you would like and come in here and multiply. If there is a salary Colin, we can go ahead and multiply the salary by a number. But right now, we don't have this case. We're going to explore this later, so I'm going to time. Grow induction looks forget toe at the end. Head into here. Yes, that's that's fine. I'm going to take this copy paste and I'm going to change the rt to number two and number two those the research hand developed men. Well, im going that also the else Well, im going toe not available Apartment, for example of course I have the information. I can go ahead and displayed, but let's act as if we don't have it. Okay? How about renaming the calypso? I'm going to rename this as the apartment name Andi. That's it. Let's go ahead and run all of this. Here, Execute on. Here it is. As you can see now, it's kind of small. But now we have the production. It's department number one in here and in here. And we have the research on development only. And here on all of the other departments, not available department in here. This is the text because I did not have the condition or the win for this Department Department numbers 3456 on it. I hope that you get the idea. It's one of my Krishnan favorites. It's very useful, very powerful feature in my sequel. OK, now that you got it on to you in the next lecture 38. Exercise CASE Statement: Okay. Time for an exit. Sorry about the case. A statement, but I need you to do in this exercise is to display the months names based on the data burst cooler in the M. Please table off each Empoli and name the new column. Months name using. Of course. The case statement. Boza Video. Read this carefully. Go ahead and watch the previous lecture if you need to. Andi sold this exercise, okay? It was easy. Let's go ahead and try to do it to get so first thing I'm going to do is to you. The human resource is to make sure that I'm using the correct database. I'm going to select all from MPs and of course, you can go ahead and select specific columns if you'd like. But right now I'm going to use all of them. I'm going to take a calmer on start adding a case and on in between I'm going to time I when I'm going to subtract or get the months out off the data burst column equal one. Then January. So what I just did in here it that I get the months from this column here, which they did reverse the date type. Call them in here on. I asked my sequel if this month's equal one. Then time or write in the new column January, we're going to do the same or Number two February them three. There won't be Marsh. I'm actually going to do this for the rest of demand since I'm going to Bose a video a little until I finish. This is that it's not pouring for you. Okay, I added all of the monsters from January to this hammer, and everything's would be far. Let's just go ahead and continue having a name as months named with the name of the colon, and I'm going to order by the months on to do this, I can go ahead on time. Months on end you be, and it doesn't matter to use it, and capital letters like this or small letter is going to work without evil. OK, let's go ahead and run this right now, but we just need to remove this from here to here. The order should be at the end like this. Let's go ahead and run. All of this actually don't have to select that, So I'm going to run it on here, then you call them, as you can see, all of the employees from once one have a month's named January. Then we have three Mars for Enbrel, May 5 on August, September, October, November and December on. That's that. Everything seems working very fine, right? Have that. You learn something. It's not the best way, of course, to do this. But this is just a practice for the case statement says that you can practice very good, so that that ons you in the next picture. 39. Exercise Union Operator: Okay, How about a small? It's designed about union operator, but I need you to do is go ahead and select all the columns from the employees table. And human resource is go ahead and select all the columns from this half table and secular . That's a new table that we didn't use earlier on. I need you to go ahead using the union operator and complain these two different tables in one result. Think about it on. Tried to apply. Okay, It was easy. Let's go ahead and try to do to get it. I'm going first. Do select all from human resource is dot com fees. I'm going to you the full passing here that I can select from two different databases. Is that a problem on going to? So? Like, I'm going to display the results from the two tables first. Oh, from skill adult staff. Six. You this. As you can see, I have two records in here. Mike and John. And as you can see here in the structure off this table the staff table. Steph, I d. First name, last name, address picture, email store. I d. Active user name password on last update. And if you remember, this is totally different than the structure off in police table. That's a lot of the same structure as this. So how it is going to work? Let's try to add the union. Move this from here. Let's take a look at what is going to have it on. That's it. Both off The two tables has been added, but they match the Coghlan's from this table in here. Then peace table. This is the first table, and this is the structure of the M. Please, we have a P I. D. First name, last name gender country T in this table, Not this one, as you can see in here. And if we go the way down confined that we have Mike on John from staff i d. The matching problems has been already sitting here like the first name and the last name. It's really the problem. But as you can see the country, I d. It's no a year because there is no data in here. And if I reverse it this right now, I'm going to take this to be this table here. I just want to be here. What? I just did in here is that I made the staff table and tequila the main table in here by putting it in the first, and I'm going to run this now. Noted this structure in here, it's going to change. You can see now I have the address of the picture of the email. Andi, it's it changing because I change it the main or the primary table in here. And as you can see, the email has been changed. Now I have the email from this table in here on these data coming from the date of bursts colon in here. But I would like to list Great that doesn't care about the name. Just cared about the order off the columns, this field in here, the email address and the date of birth in the in police table. That's why, as you can see here, the email address that's coming from the staff on the data burst coming from the employees . OK, that's the main point that I wanted to on the street. And of course, that's not correct. Deaths mystery. That's not really clear for you or anyone working with user. You have to avoid this. You have to make the match in the time and in the same information they have. Each column should have the same information that I'm at the first name and last name can find that this is workings of the problem. This three columns in here. I hope that you get the idea. And you became familiar with Union. It's very powerful. So the tech armed to you in the next lecture? 40. EXISTS: Welcome back, everyone in this lecture we're going to discuss on your keyword in my secret cold exists exists. Condition is used in combination with sub query going to see what is this? A query? If the sub quer return is at least one grow, then this condition is going to work. Oh, this statement is going to be true. Can be used with select and certain of data will lead statement Let's take a look at this example in here. Here we have the select golden one from table one, for example. Where exists and hear this A query sub query is a query within a very in here. This is all one query, and this is a query inside of the grief. That's why it's called this sub square. So if this wary this separates Aquarian here, select Astra's from table to is valid or true or returning at least one grow this here. This statement is going to execute their This is elect statement. It's going to be executed. If it's an update bleed insert. It's going to be executed as well, and we're going to explore all of these statements later, and it's going to make more sense. When you're abating, something go inserting something. But right now we're going to test it with in normal select statement. Let's go ahead and take a look at these exists in action. So what I'm going to do in here is about to use human. Three sources and I'm going to select old from countries were going to execute this. That's very obvious. So here's all the countries that a problem let's try to use exist. So what I would like to do in here is I would like to get the results from the country's over this select statement if a condition happens, so I'm going to time. Where exists, as you can see turns blue. It's a keyword. I'm going to open a currencies and the close them. I'm going to select. Go ahead and select old or one column. If you would like one long select all from M please. Where? Department equal. Okay, so what I just did in here This is East separate query or the sub query. As you can see, it's getting me some information when the apartment equals two on them, asking if this here it ins any room even one row than this here is going to execute. So if I run this right now, not going to do much because I have one mistaking here which it Portman. There is nothing cold department. I think the i d. Right now it's going to get me all the data because this condition now is true or returning at least one row. So this statement here or the select statement is going to execute. If I change this to something like maybe pretty, we don't have the Boardman number 20 in this table. And please table toe. Nothing is going to happen. It's going to be empty. As you can see in here, it's totally empty. If I returned back, Teoh, maybe number five there is Department number five now the condition mystery. I know it's somehow like this example. It's hard for you to imagine how would we use it? But imagine if you do not update or an insert, you would like to abate something. If a condition is true, if there is some sort of the value, we're going to discuss this. We're going to discuss the update insert and the late statement. But for now, this is how to you the exists. And next we're going to discuss the amazing sub query, which is very, very powerful. See you in the next lecture. 41. Subquery: Okay, everyone in this lecture, we're going to discuss one of the Greek important concepts in my sequel and in all of the relation of the debases and sequel databases. It's called Sub Query. So what is some query query commonly known also as inner query or nested query? He's a query that is nested inside another statement. South Korea is used to return date as it will be used in the main query as a condition to restrict the data to be retrieved from the main quake. I know this does seem complicated, but it's simply some query inside a query that's working as a condition. If this sub query it's true, it's going to achieve some data as a condition of the wear clothes in the main query. Let's go ahead and see. That's going to be much easier when we take a look at it. Okay, we might need to first fell some data in here in the previous experience, so I'm going to fill this previous experience table, and you might need to do this as well. Okay, if you remember how to do this, we did it earlier already, but we're going to do this again. So we go into, like, right, click on the stable previous experience. Right now it's empty. I'm going to fill it with data from an Excel sheet. Choose table data import withered in here on As you can see in here, I have already this file previous experience that see as the UN you should find it and the resources as well. Like next on, I'm going to use an existing table, look to raid a new table, and this is the table that I'm going to use previous experience. It's already there who's older, Coghlan's, and here the data Next might need Do show lugs in here to monitor if everything's going very well or there's a problem. OK, so I'm going to click next on. Everything seems very good just and I'm going to select this, Okay, As you can see now, the table is failed with some dummy data in here that I'm going to use. So let's go back to what we were doing. We were discussing these query. What I'm going to doing here is that some employees in here have some experience. Some have been loosed, hands are not old in. Please have some experience. Maybe they're fresh or they don't have some recorded experience. So I would like to get the in, please, with experience, but from the employee stable. Let me show you what I'm talking about. I'm going to select old from a piece from here. Well, let's run this on hearing the data usual later. That's all the employees that I have in my table right now. Some of them has experience and some not. So how can I get all of these information? The first name, last name, gender, the country, the numbers. All of this while checking you, they have experience a lot, and they can know this information from this table. So this is where the sub query comes in handy. I can come in here and do this, and they need you to stay very focused with me. It's gonna look a little bit complicated, but trust me, it's not that complicated. And it's very powerful. So I'm going to type where empty I d in. I'm then going to right myself. Query. Select the employee I d. You need to make like each other. It doesn't matter again. This capital and small doesn't really matter. Where is my sequel? Run? Previous experience. Run this and see the result. Wow, that's really interesting. Doubts of the employees was experienced in here. Employee number 236 on 11. They are already appearing in here. How did this work? And what is exactly at these lines in here? So let's take it one by one. So where we really have seen this plenty of times. This is the colon that I'm going to compare it with the sub query. As you can see in here, it must be in here on direct. I kind of coming here and compare this employee I d in here with the Embry name, for example. That's not going to work. These two must match employee with employee I. D. Candiotti was country idea first name, for example, And first name. So what I'm doing here is I'm asking if the employee I d existed in this select statement. So whatever comes from here, it's going to be checked with this. It's something like I did this one, if you number one, for example, two and three. But I did not do it in a static way. This is very static I did in a dynamic way. I had to ask my sequel. Get me older and please was experienced these numbers 23 11 and six. This is he in, please, with experience. Previous experience. Um, booted in here that you get what I'm doing in here or what the query is doing in here. Let's try something different. Let's try to get the M please. Without experience. Also based on the previous experienced able to again, I'm going to select old from him. Please. Where will be I d Now? This time I'm going to choose in. I'm going to choose Not in. I'm going to before McGuinness up Query Get me all the M, please. The idea, of course, from the previous experience. So get me old Ian, please. With experience or records in the previous experience table and get me the opposite of them . Not in here. So if I have here number 236 and 11 get me The Impreza is not who are not in this list. Let's try this So I'm going to get older and please other than these two Then please, without experience. As you can see here it is 1457 on all of them, please That have no records in here in this cable. Okay, If I removed, for example, one MP from here of you Number six, which is working for Amazon, they have to remove this. I'm sorry. Let's go back first. Do the first. A statement that's getting me the employees was experience I have here. Noah Burner number six. He has experienced Alison. If, for some reason it was removed, I'm going to remove it. They can delete a blind. We're going to learn more in the upcoming elections about the date statements. What were you about? This? Okay, it's lead it. It does look exist in this name of anymore. If I run this in here again as I told you, it's very dynamic. No, no, uh is not in the list off the infuse. He has experience in this select statement. Because when the my sequel went on and get me the employees with experience know always let their employees number six was not there any more. Zap City Bower off sub query. It's very dynamic, and you can create really complex conditions. As you can see in here, I hope that you really got the idea about the sub query. I know sometimes it's hard for some people, but if you still having problems with that for the first time, don't worry. That's normal. I'm going to write an article and give you more examples about the subway, and you still have problems you can go ahead on. Let me over this and we can figure it out together. That set next. We're going to have a exercise about this query. Serve that you can practice more about the quake. See you in the next election. 42. Exercise Subquery: Okay, How about a small quick exercised about the sub queries? But I need you to do this exercise is to get this reasoning that our employees have experience in starting from 2015. Read this carefully and tried to apply using a foresees a query. Okay, What I'm going to do first is I'm going to Celek the city I d Begin the ideas of the cities that our employees have experienced in starting from 2052. So, like the city I d. From able previous experience where year over the start date Daphne Koller and the previous experience. If we check in here, we can find that we have this color start date greater that equal 2015 on. That's it. I got the cities, all the employees, the city's then please have experience in from 2015. If I didn't apply this condition, I would have much more than this. Not much more. It's two more. It's the double. Almost so that's the sub query that the inner query. Now I need to get the information from the city stable. I'm going to select all from the cities that all of the cities. As you can see here, the city's name on the country and I'm going to add a condition where City I d in going toe open parents season here. I'm going to add the sub query on the close It. I hope this is an easy way for you to figure out how to get it up. Let's run this to get the full data. And here it is. The two cities are Hamburg on you. You. So that's how I saw the exercise so easily, starting from the inner, very oldest of going to the outer query or the main query, which is easy and fun. See you in the next lecture. 43. Aggregate Functions Min and Max: So let's start discussing the arrogant functions by using two main important actually functions in my sequel there, the minimum and the maximum an aggregate function before miscalculation on a set of values on returns to seeing the value minimum, for example, returns the smallest value of a specific column from a set of values. So what is happening or what the minimum is doing, for example, and the maximum it takes a set of values in a column and gives you, for example, the minimum the maximum gives you the maximum. We're going to see others like the average the count on more. So let's go ahead and see how to use these amazing functions in my sequel. Okay, let me first used human resource is database and I'm going to use and this lecture a table cold jobs. If you go to the tables in here, you can find that we have a table called Jobs. Let's select data from jobs. Yes, you. This on here the data from the stable. As you can see it, has a couple of Kohler's do dope I d job title, the job minimum salary and the maximum salad. So what is happening with the stable in that eat job. The accountant, for example, Exclusion software developer. There is a minimum salary and the maximum salary. If I would like to get some kind of special information like, what did the maximum value of the maximum salary in the school of the maximum salary? What is the maximum off the backs of salary? What is the maximum off the minimum salary over the minimum off the minimum salary, etcetera. You can use it. Very good. Very amazing function with numbers, salaries, degrees. Very amazing. Let's go ahead and start using the maximum. I'm going to get the maximum value and this column in here, The jump minimum Salish. I'm going to type like using aggregate functions or function in my sequel. We're going to tired the name of function, which is Max, and I'm going to open a parent sees. As you can see, this is a format off this function. I'm going to click on it or open the currencies without problem. As you can see now, I have parents season here, and I need to type inside of it the colon that I need to use on the column that I am seeking to perform this function on it is called Don't minimum salary okay on them going to have the tables from jumps. Normally, I'm going to run this. I run this, I have 6000 and that's true. If I take a look at the jobs in here table, the data itself, I can find that here is old values and hear the Maxwell very it's yes, it's a senior software developer. It's 6000 and by the way, you can use the work. Then just click on the colon that you'd like to short, and it's going to sort it for you looking here at the casino. It's sorting in ascending way, and if I click again, it's going to be sorted in a descending way. M as you can see 6000 and the maximum in this gold them in here and one thing that I need to show you. Then right now, this name here it's gold maximum job, minimum salary. It's not very nice friendly, so I'm going to rename it as maximum minimum salary, and it does look a bit confusing, but dance is the maximum of the minimum salary. Okay, let's go ahead on the form in Different where I am going to get the minimum salary for the job. Minimum salary. So select again many men you can use it and small letters or capitals out of problem. Going to get a job, then, um, cellar again. This is the people I'm going to rename this cola minimum minimum salary from tips. Let's run this and we should have two cells on south 100 that's the minimum. Let's go ahead and do the same for the other column we have out of the colon cold. Maximum salary. I'm going to do this. Select maximum job maximum celery as maximum celery, a type maximum maximum salary because that is the maximum off the maximum salary cola from jobs. Let's run this on. Here it is. It's 9000. Take a look at the table again to make sure that this information is correct and it must be correct. I'm going toe sort it using the workbench. And yes, that's 9000. Let's do one more thing to get the minimum maximum salary, which is 4004. This column, as we can see in here, that's the minimum to go ahead and do it. I'm going to take. This is a copy. I'm going to change the function to minimum. Andi, rename it just time minimum. Okay, I'm going to run this on. Here it is. We have 4000. As you can see in here, it's really amazing function. That's very basics off. Using these functions, there is more, much more better scenarios. You're going to need to use these functions in your career. Where's mine? Sequel? We're going to explore some of these scenarios later. And, of course, we're going to perform a couple of exercises. Teoh, test your skills with this amazing functions. So that several No, it is just scratching the surface off the aggregate functions. Andi, see you in the next lecture. 44. Aggregate Functions Count: okay in this Actually going to discuss one more aggregate functions? Cold count count is very common and very powerful. It returns. The number of items found in a group can be based in the condition owner. So assembly, it just counts the stuff. And you can add the condition toe with Ola. Let's go ahead and see this. So I'm going to get old. The data from simply stable. He's Yes. Execute this. How many rows are in here? Work? Pinch. My sequel workbench is actually helping here and telling you that you have 14 Rose. But But if I wanted to get this information into my application, I can go ahead and get this. This is only available for me in the work bench. How can I count it? There's plenty of ways. One of them in the count. I can come in here and start typing this so like again. It's the function on going to that parent sees. And what are you going to time in here? I've been coming here and tell her that I would like to count the first name or any other cold living here. We're going to rename it number off rose records from companies that's execute this on. We should have 14. Yes, he had 14 years. Now we have this as a data. I can take it in my application. Or if I'm doing a whole pile ab, I can go ahead. And you this query to get the count number all the rose, I will check that you can use. If this is not a logical for you, you just need the count. No matter what the colon is, you can come in here and have asterisk. This is not the Astros. The same Abdi columns in here. It's going to act differently if I run this right now, it's going to get me 14 as well. So I don't have to mention the name off any column in the table. But I cannot leave it empty. That's not allowed. As you can see, there is an error in here. If I try to run this right now, it's not going to work. There is an error in here. You have an error, so I must give it some kind of a column on. If I don't want to give it a column, you can go ahead and anti start for the asterisk in here. I can come in here, for example, and count old to the number of cities so select oh, from cities. That's excuse this and I have 145 and that's true as well. I can come in here also on count the country I d, for example, and other condition so I can come in here. Let's execute this statement. The select statement for them Please. I would like to get the please in country the count or the number of records off the employees in country number one on day two. So I need to add a condition. So let's go ahead and try to do this. I'm going to select count on this time. I'm going to be more specific. I'm going to count the country I d. From in, please. And I'm going to other where conditioning here, where the country I D in one or two. That's execute this. Andi, The database engine is standing under the or six. If we count them, they're going to be six. Let's try to do this manually to make sure that this is correct, and this must be correct. That's not going to be incorrect just to show you that this is correct. Order by country. I d going to run this. And as you can see, we have 12345 and six. Sat six columns for country number two on country number one employees in here. If I tried something different, like three on and four, for example, it's going to be four as again. Caesar's foreign fees in here two in number three to number four. Let's go out and try to do this as well. So three and four on Execute on. We should have four, and that's correct. On Defy added, For example, there is a country like one is going to be one has four, and these two has force. That's going to be eight. Yes, that's a That's totally correct. You can go ahead also. Andi, this is very flexible and very careful. Of course, you can go ahead and rename it right now I don't have, and then you can go ahead and type as over a froze or any other name that you prefer so I can do something very amazing. I can count the countries that is thinking rose on Lee. Can you believe this? I can come in here. I'm count the distinctive drugs only. So I'm going to select Count Onder than adding the country I d on Lee. I'm going to add the distinct. The work has been seen here on them going to tell it from in, please. How many rows should I get of the number of froze? Should I get I run this? I should get six am That's true. There is six different countries that I have in this column in here. The are the one can you number 12 3456 If I change it, for example, John M. Lee. I'm going to make them one on one that I'm going to apply to us. Yes, a blind us. Okay, foolish. Now I'm going to get Can you give it's life? Yes, that's correct. I'm going to get five because one country was removed, which is country of six. If a grand us, I'm going to get fired. I have five different countries and this table in here or with this count message in here. If I removed, it stinks. I'm going to get 14 okay? Because I have 14 rose, but that stink is going to be different. There is much more to do with the count. This is just the basics. You can go ahead on creatively. Great reports and queries based on the count later going toe have some more exercises about the aggregate functions. Okay, I hope that you enjoy this on that set you in the next lecture. 45. Exercise Create Table Budget with Some Queries: Okay, time for a exist on this and this exercise, we're going to great. And you table called 100. I'm going to apply some queries using the aggregate functions. What I need you to do in this exercise is go ahead and create e table called budget. Less stable is going to consist of attributes from an excel sheet that you're going to find in the resource. Is there the year allocated budget on remaining budget on one final attributes in the remarks, After you successfully import the stable into your database, go ahead and select the maximum budget off all years. This exception contents some may mediate other than going to use, So I haven't select the maximum budget of all years. So, like the minimum budget off the last five years can't hold a valid records with actual remaining budget, actual remaining budget. I'm not going to explain anymore. Go ahead. Try to figure this out yourself, and we're going to sold us together in a bit. What was the video? Try to do it. Okay, I got that. You did it successfully. If you're having a problem regarding to solve it together, so the first thing that I'm going to do is to import the new table for the data. So I'm going to hit quickly, choose table data and both withered. Andi, I have here the farm. It's called Project. Look. Sees V Next. We've done this plenty of times. I'm going to hit, create new table, and I'm going to name it Budget. And here is old columns. I'm going to change the data time throughout the year. I'm going to leave it as an end The allocated budget. I'm going to choose it to be double on the remaining budget work. Choose to be double and the remarks. I'm going to leave it as a text of that problem. Okay, you haven't next. Thanks. Lugs? Yes, that has been imported without a problem. Okay, the first thing that I'm going to do it, I'm going to select from this new creative people to I'm going to select all from budget, and they should have some results. And here it is. We have the year allocated budget, remaining budget and remarks. And here is a couple off data dummy data. Of course, if I had a fresh my tables in here, I should find that I have a new table cold budget, as you can see in here. Okay, so that's the first part off the exercise. Now the second seem to do is to select the maximum allocated budget From here. I would like to select the maximum from this column. Let's go ahead and try to do this or select the function regard to use cold Maximum on the colon is a loop Que Did budget going to rename this as makes move located Budget room. But it without running this now Which number is the highest? I believe it's this bun three million czar. Believe the three minutes or we should have three million's. Yes, we have stream. It ends at the maximum off. All the numbers in this column be allocated budget. The next one is to get the remaining budget. What year is great? Azam themselves on 15. So I'm going to do this as well. I'll go get the minimum. Actually, excuse me. The minimum remaining budget So select minimum meaning project to rename this as any mom remaining budget from much it when we go to add wear clothes. And here So where The year Raiders and 2050. I can go ahead and make it like this. Or making great is that are equal 2016. So it's up to you here. A choice. Okay. Going to execute this query on? Here it is. It's one million. And if we revised the data and the budget, that's totally correct. That's the number that we need. That the minimum number from this too. This in here. Thank do something. 15 is not. Include. If we included it, it's going to be this number believed it's 250,000. Let's run this. Yes, but that's not being requirement. Okay, now that's done. Now, the last Bart is to select the actual the count, the actual remaining budget. So if we take a look at the cable in here, we can find that there is some records with zero in here. So we need to exclude this from this here. We need to count this. So if these are, I believe 10 rows. If we excluded this, we should have nine. What's going to try to do this? So I'm going to select this time. I'm going to do the count remaining budget from budget. We're going to add a wear clothes in here. Where the remaining budget not equal, dear. It's trying to run this have on air in here. Okay? I have this name wrong here. I'm going to fix it. Yes, let's run this on. Here it is. We have nine because we excluded there. Zero By having this condition, the remaining budget look equal to Okay. I hope that you enjoyed this exercise at that. You have learned something and you're familiar with the aggregate functions and force importing tables into your database any time. So that's that ons you in the next lecture. 46. Aggregate Functions Avg and Sum: two more aggregate functions that I would like to mention in here is the submission on the average some mission or the son returns the total submission off a specific numeric column . Very basic and very straightforward. The average, on the other hand, is some mission off all the numbers divided by how many numbers there are the same ad, the average in the mass. Okay, let's go ahead and see how to work with them in my seat. So I'm going to do basic select from the budget table, display the data on what I'm going to do, and I'm going to get the submission or the total off all of these numbers. I'd like to get the indicated, but it for my company, for example, for all of the years from 2010 to 2019. What I'm going to do is I'm going to buy. So, like some the function on we need to add appearances in here on between. This parent sees I'm going to type the column that I'm going to perform the submission on which the located budget going to name it as total allocated budget. Okay, let's go ahead on continue our statement. From which table? We're going to do this from the budget table on. I'm going to leave it like this. I'm going to run and I'm going to have a huge number. Here is it's about 24 million's adapt is the total submission off all of these numbers. If you count them, you're going to find them that they are 20 for Okay, lets go. Hand in hand is a little What if I wanted to get specific years, maybe from due South and 10 11 and 12 only. And that's easy for us to count even in here. So I'm going to type. Where is you have seen before? Year in 10 2007. 11 2012 Let's run this and we have this 6,000,500 that's correct. If we count the numbers and here they're going to be equal the 2000 there's these numbers. Okay, I can go ahead and use only the between can go Heaven. You this This is the same statement I'm going to use between two south in on bond. 200 1000. Okay, so all I just changed in here is and the statement I was using the M with its submission and then the statement. I'm using the between Windows submission. If I run this, I'm going to have the same result, which was 26 million's actually in 500. Okay, we can also use the average to use the average we're going to use. So, like average function, we're going to Time t remaining budget from, But it what's run us? And it's telling me that the average off the budget amusing and all of the years over the remaining was this number. I believe that's about one million go ahead and changes to the allocated budget as well. But I'm going to get the average off the budget that the company has said for each year's about two million. So you're very useful function, especially of doing something about the mass or financial reports. They are very, very so that's it. Andi, to you in the next lecture 47. Group By: There is a key war that is used a lot with the angry it functions that's going to enhance and take your where he levels to a next level. It's called Grew by fruit. Buy Clothes can be used in a select statement to collect data from the records on the grove . The results by one omo columns. So I can, for example, grow the in please from the same country from the same departments and get the number of them or before any other angry it functions on this road. Oldest slice. Let's go ahead and see this in action. So I'm going for us to get older data from and please But I'm going to achieve in here or try to achieve in that make me first at an order. Boy country I d. I would like to get as you can see in here we have four or five actually in please. I added two more in the previous Section two country number one. So I'm going to get these six in please. I would like to get country number one 16 please. Country number 2 to 1 place country number 3 to 1, please 4 to 5 to the count of employees in H Country. Think about how are you going to do this with the scale that you have learned so far? There is nothing you can do with in a direct way. There is no diners way to do it, but with the group buying can go ahead and do it very easily, sir, How to use it or how to create this group? I So I'm going to select. I'm going to need to get the can t I d. I want you to imagine when me I would have a country already column in here and I would have the number in please for each record or for each country. So that's the call them the one that I need the country ality. I'd like to get the count off the employee I d. I would like to count the in peace. I'm going to rename this as Total Are these their country? You can go ahead and use the shorter names, and there's but I would like to keep things descriptive as much of the camp from him. Please. That's the table using. Now let's start adding the group buys a group by country idee there. What I just did here is I added this new line and this new line is going to group buys going to put each similar country into one group on in here. I'm going to perform. We count on the empty I t. That's run their son Cesar results coming to run this. And as you can see, here it is country someone, we have six. Can't you? Number two, We have to. And jittery. So if I tried, for example, to change some of the employees in here, I'm going to start Emily too. Six. Country number six on may be Noah as well. And I'm going to hit a blind Yes of Linus, please. On if I run my grew by query again, I should have different results. Now I have four employees and containable one and Kenji number six is back to to increase very powerful featured have on and most of these airports. Are you going to, he asked. The create and reward scenarios are going to be something like this. Stick great portions. When you see charts for eight countries over cities, it's going to be something like this. So Let's try something different. What if I wanted to get only a specific countries? I can come in here and do this. I can come in here until it would like to perform this four country number 13 and six to I'm going to add Where De country Friday in one, two and six Ministry. And if I run this right now, it's going also to work Fine. That problem showing the only countries that I have selected, which is also great purple so I can use a select statement with an aggregate functions like the count from, of course, the table using a wear clothes on and using the group by all together. Okay, let's try this with movie. The in, please. I can't do this with them, please, from the department So I can come in here and say that each of this department, how many employees are in. So let's go ahead and try to do this again together. So I'm going to select apartment I d. Let's see the department that I would like to display, and I'd like to count the one please for the empty rd. Actually, I'm going to name this as total b there deep part of from appears. I'm going to you the wear clothes. I'm going to create the group by the department by the That's the colon that I would like to grow Good bye in the first, query it with the country out life to count or trade the group based on the country. But this time our life to create the group based on the department's I'm going to have execute and see the results. It's some kind of a Cirque has been here to one and fun to then we have a department of in 32 and it's OK, and of course you can go ahead and use where you would like to do it. We can hear this. Also. It's going to be much, much useful with a table like this. It is if I selected older data from the cities, you remember the city's table. It contains lots of fluffs off citizen here. Can you count the number of cities in the country? I d number one, which is used eight on go ahead and gold away and don't things that you can figure it out by the code in here because this code and here is going to be only valid if it's sequential and it's counting only for the 1st 1 if I change it and then we are in Germany. As we can see, we have mention we have Cullen. So if I go and start from one anymore, as you can see it starting from 41 it keeps updating if we go to another country like China in here or Russians don't sing. That you counted from here is a good idea. It's not good. So I'm going to curate and you a query, getting me the country on the and telling me how many cities in H country Let's go ahead and do this. I'm going to select 20 heinie. We're going to count the city Name this column in here, going to name this as don't know, cities, hair crunchy or in country. And I'm going to perform the group by the country ideas, a group, all of this by each country or by this column. So let's round us, and we're going to have an amazing results. Here it is country number one. You have 40 cities to we have 49 3 1958 and etcetera. Really, really amazing and very powerful keyword. Using only this group by You're going to use it much and going to get amazing results. Was it? I hope that you did like this group by and that you really learned something. Next, we're going to have something different to discuss. Do you in the next? 48. Having: the normal where close doesn't work with angry functions. The work in a single rose that a group of froze. That's why they have to come up with a new thing that ports with the aggregate functions on this thing is having having is typically used was a group buy clothes You can look use it with any other thing other than the group by having works as I told you underwear. But with aggregate functions, let's go ahead and think I look at how to use you having so I'm going to great a basic statement in here. Select country I d Count City name going to name this as total cities and country from cities I'm going to group by the country. I d going to run this to seize a result. As you can see in here, I have a problem. What? This problem Probably I typed something wrong. There is a missing wine here and added Yes. So if I run this right now to work, um, as you can see, I have the can genome 1 40 cities to 48 we've done this before. But what if I wanted to get on Lee the countries with cities larder than 20. How can I do this? I cannot come in here on saying this must be done after the group by. I cannot come in here and at aware It's like going to work. I can come in here and at aware politically. Where the count off the city Name column greater than 20. As you can see, my sequel is giving me an error in here. Where is not a valid em, but at this position, it's not right. Houston. Hubert, If I replaced the where was having, it's going to work. If I run this viola now I have the country's was cities loaders and 20. If I changed, just attempt to go into display more countries. You're six. If I choose 40 I'm going to get on Lee one, which is country number two. Because that's louder than 40. There, that's how to ut having I can go ahead and also you the order by. Go ahead and order by and out account city name going to get this back to 10. I don't want to refresh on here it is. They are sorted based on the count, as you can see here something I can go ahead also and using something to use it into the something way. So that's how you be having. And that's why we need toe having to filter after we created a group by on a column that doesn't exist, like the count off the cities in here help get it. And it was easy for your If you get a problem you have sent to me, I will try to answer as fast as possible. That's it for now and see you in the next licked. 49. Exercise Aggregate Functions: Okay. How about exercise and this exercise? I need you to do a couple of things using the security based on the film table. Select any film that contains letter X Group thumbs by the rating Get on Lee. The higher than five order the query by the total of films and a descending way. Read this carefully going to need to figure things on your own. I'm going to solder together. Don't for it. But just to give yourself the chance to solve it. Okay, beat this carefully trying to apply this exercise. Okay, I hope that you did very good with this existence. If you still having a problem, we're going to do this together. So the first thing that I'm going to do is to select the rating on count the title. And of course, don't forget to use secure the database, and I'm going to be named this as so it'll fills from film. I'm going to at the where title like picks. Okay, so right now what I'm just doing is I'm getting the title or any title that contains the letter X. Okay. Then on the grooving buy rating, then I will be asking if this count off title reader than foreign then displayed greater than five in this plate, all I'm going toe order by the count off the title in a descending order. How about this? Let's run this. I want to run. It'll actually. Okay, let's run this. Um, here it is. We have the are which contains eight films on BG Dash 13 which contains six. Move. If I remove this condition in here, I'm going to have all of the ratings as you can see in here. But this having all this filter Onley selected the higher than five. Okay, I hope this was an easy exercise for your Ah, we're start to build things up. As you can see, we're using multiple statements, right? This lack the where the group, by having the order by That's how you're going to create your queries in the future. So that's it for now. And see you in the next lecture 50. DML and DDL: Welcome back, everyone. Before you go ahead and start the section. Eat, Identify. Two concerts probably will hear the block. They are the D mail on the TV. Help what? This term knowledge is every basic. The A Merrill is an abbreviation of data manipulation language. It's used to retrieve a storm. But if I had the lead insert and we're going to see all of this in this section on updated data ended the debates. I'm not saying this. Elect statement on the next picture, we're going to see the insert of the update on that lead. This is cold. All of these commands on statements, the email, all data manipulation language. On the other hand, we have The DTL is an abbreviation off the date of the submission language, and I have seen it before. It is used to create and modify the structure of the debates. Object ended at the base, such as to create with things that create how to create tables and create databases out of altar, out of drama. These two I I wanted to clarify before that we continue that when you hear this technology is you don't get down like what is with the email over the l simply These two lines here define what is an email on BBL. So it's it's with them. Next, we're going to start seeing more of the data manipulation language on. We will see how to insert the lead on update data to you in the next. 51. Insert Into Statement: Okay. Finally, it's time to discuss the insert into a statement. What are the most important statements in my sequel? Insert into statements is used to insert new records in a table. This is the official UN correct way to add records into in relation to the base, such as Morris equal. There's plenty of ways to do this. Here is two of them. The 1st 1 is to you. The syntax insert into table name called 123 than values. Then start adding the value corresponding to these columns. The second way is to time insert into the table name without the columns name. Start having the value off the same order. The table has bean structure. Let's go ahead and see them in action. So I'm going to use a human resource. Is little base going to select old from countries? I would like to add two more, for example, So let's go ahead and try to do this. I'm going to time insert into on going toe on the table name, which is the country's in this case, ongoing specified. The columns are going to the country I d. I'm going to tired country name, maybe make this a small doesn't make a difference. But just to keep things look good, country abbreviation. Then we have the capital. Okay, then we'll start adding the values. So the values one coming here and make it like this so that it's easy for you to understand what's going on. So the first thing is the idea. I'm going to add it manually. Each number seven. I can go ahead and make this a dramatic, but right now I'm going to stick with the manual values. I'm going at seven. You can go ahead and add eight of your Americo 10 bodyguard and got had a news six, for example, because six is already taken. So you cannot use the numbers that's already taken at the primary key. As we have this custom and the primary key lecture of this course gonna go back to the lecture. If you have forgotten about this rules, so then we need the name. The country names I'm going to type, for example, is pain. Then we need an abbreviation for Spain. It's going to be yes, that I'm going to you the capital, which is Madrid. Very beautiful country. A command that you go there if you have a so let's just desk right now. So I'm going to select this and I'm going to execute it. Ondas he concedes. There isn't error in here because I lifted with six and six is already there, as you can see, if you can see the air in here. Dublin Kit entreat six for the key private because six is already taken. So I'm going to choose seven again, and now it's going to war. Well, it worked, but we didn't. You select. So I'm going to select the table again on here. Is he you come true, which is Spain that we've added in here? I tried, Toe added again, It's not going to let me because I'm again using country number seven. As you can see, I have an error. Who's seven? It's taken. If I tried something like eight, what do you think wouldn't allow me to concerned or not? Yes, it's going to allow me to insert, even if this is the same information. Same country, recreation, same capital. But my sequel doesn't understand this. My sequel of Relational debates understand there is e primary key that you have to follow. That's the only thing that e database would understand. So that's going to work without the problem. If I run my table again, I will have to records with the same name as you can see. Seven and eight, Spain and Spain on. I'm going to remove one of them. Lead this okay, whether we're going to see how to you, the elite states. But right now, let's continue adding for that, we're going to see how to delete in a fishing weight. Okay, let's now try to add another country or another record using a different way, which is the insert into without the name off the columns. So I'm going to insert into and then the going specified these names in here. I'm going to give the values directly to my sequel. Sir. Countries, I'm going to give it the values on and eight England, maybe go ahead and choose any countries that you are comfortable. Where's I'm just doing this? Have the testing, of course on London. So I did not specify the Coghlan's as in here are just time the values that's got tested to see if this is going to work, not on it worked. As you can see from down here, let's just or select and you table, um, we're going to find that we have number eight, which is England. He end on the capital. Is London. Very interesting, I believe. Let's drive one more thing. Can I insert only part off the columns? For example, the country I D and the country name? Yes, you can do it. You can even insert Onley. One column in this, which is the primary t the primary key in the only mandatory K o column that you need. You don't need to answer the country name country of aviation capital, only the country I d. You cannot leave it. No, this is one of the properties off the primary, as we have discussed earlier. So let's try to insert using this technique. So I'm going to insert into countries. I'm going to time country I D than the country name only. Remove this. Selected that I'm going to type values that I'm going to time than you record I D, which is going to be nine, for example, that I'm going to have a country maybe Brazil, Brazil on debt said, Let's start to run this. As you can see, I have only two columns. That's fine on to em. But all day, the injury in here. So let's excuse this. Let's run this. See if Brazil has been safe. Yes, here it is. Number nine Brazell. And there is no data in the country, a privation, all the capital because we did not supply it to the insert statement when we were inserting into this table. Let's try something different also. Let's try to leave the country I d. As this empty and I'm going to maybe add Buick, for example, I'm going to hit, execute on That's not going to execute, because off this air field country, I d doesn't have a default value. This is a primary thing, and this cannot be No, but I can come in here and say the country I d. Only I'm going to insert only one colon, which is a country I d. The primary key. This is what matter to database. So I'm going toe 10. You think this is going to work? We're not. So let's run this and see if it's going to work on. And no, it's not going to work if I had this. I haven't entering here. Field Country name doesn't have a default value because when we were creating this table, we added the condition or constraint. If you remember that the country name cannot be No. If we go back to the structure of the table countries, here it is. We can find that the country name column has this not null condition and go ahead and I'm a trick. This a blind for life to make it work. Finish, Let's go back. Try to insert this cola for this record and was only one cola hit a blind on. Now it's working one row affected. As you can see in here on this, I run my select statement off this table. I can find that I have I record in here that contains 10. But there is no country name, no country observation, no capital. But the database has the most important Coolum for it which the country i d, which is number. So with that said about the insert statement so far, we're going to use it in a regular paises. When working was religion into the bases and my sequel, Of course I hope that you had the chance to understand the basics of insert statement using these examples. Next, we're going to test your insert statements. Skills get pretty. 52. Exercise Insert Into Statement: Okay, time for exercise and this exercise. I need you to go ahead under. Insert into the department's table to lower records the 1st 1 to the communication on, then training. Then go to the jump, stable and insert again. Two more records. Mechanical engineer with salary between 5000 and 8000 then another record truck driver with salary between 3000 and 5000. Read this carefully, trying to apply this exercise. Okay, it was easy. Let's go ahead and try to do it together. So the first thing that I'm going to do is to get the data from the departments. Take a look at the table, so select all from departments. Let's run this. And as you can see here we have about eight records on. We're going to insert two more, So I'm going to hit answer into apartments a long time. Values going to you. This technique with inserting them telly communication had execute on. We should have a new department called the Communication With the Key Nine. As you can see in here, let's go ahead and do the next one. I'm going to take this copy, paste it in here that I'm going to change the values come going to be training. Let's go ahead and ask you this. Then, in the select statement on, we have the Next division, which is number 10 training. So the first part off the exercise has Bean done the problem. Let's go to the job science of select All Again jobs. Take a look at the Haven there. We're going to need to add two more in here as well, so I'm going to time insert into dopes when the values it's going to be number 14 going to tie the first dubbing here. But I'm going to add the name of the job Chemical engineer. Then the next column is going to be the minimum salary on the maximum salary. So it's going to between 5000 to a Salome's that I'm going to add in here. The last column, which is the is managerial. I'm going to add zero in here that I'm going to execute this statement on it worked the problem. Let's run the table again. You can see that we haven't you job called mechanical engineer with the value that we have specified. Let's take this is a copy pasted in here going to change the values. 15 on the jobs. Going to be graver truck drivers. A cordial you sound into five sounder on. Let's select it executed. Run the select statement off the job. Stable. Um, we should have it Here. It is a truck driver with the value that we've given do the truck driver. So that's it about this exercise I have. It was good for your aunt to you in the next. 53. Cloning a Table: Welcome back, everyone in this lecture, we're going to see how to gloat a table or copy a table or created tables from another table that really exists. Let's go ahead and see this. So what I'm going to do is I'm going to curate the same table on the MP stable. I'm going to select the data first from simply stable and I'm going to create the copy or clone from this table, the same structure, the same data using very simple command. Let's go out and see this. So I'm going to type create table that we have seen earlier in this course. I'm going to hit the name off the new table. I'm going to name it you in police, for example, and then going to toy as seven. I'll be adding or selecting the data or the structure that I need the new table to look alike. So I'm going to type so like Astra's from Increase as simple as that. So what I just did and here is I told my sequel that I'm going to create a new table. It's going to be as this structure and here the employees a structure. Let's go ahead and run this and see the result. But I need you to not at that right now. I don't have a new please table. I have only the in. Please going to run this reference minded bases. Andi related I haven't you table cold. Um, please. With the same structure. If I take a look at this, A structure in here, The same ad the in, please. Wellness. Very amazing. Let's go ahead and select old from you. Underscore of these had execute on. You're going to see that this is the data from the new employees stable in here. It's the same out of the employees, the same column, the same data. Nothing is different. It's cloning. It's identical to the original table in here. I'm going to create a new table with the same structure. But on Lee for some of the employees in here, I can have a condition. What? I'm creating this table or cloning this table. So I'm going to create table. I'm going to name this Use a M. Please. You say is the employees number one in here? So I'm going to then my sequel as select from Kampeas where Country I d equal one. Can I put a condition while creating this or to make on Lee Specific and please, Based on a condition or even the limit? I can use the limit in here. I can go ahead a limit. The number of employees taken number one for Sorry if, as we have seen the limit before to be copied, enter than you haben. Yes, of course you can do this. Let's go ahead and have a semi colon and run this query. Okay? And you table should have created I'm going to refresh this on. We have you say employees and I'm going to select this table. Select all from use a please. I'm have execute on. Here it is. We have the same structure. But on Lee, the country i d number one m please. Which are the using? Employees in here were copied through the new table with the same structure. But can I copy only specific columns? Still, you can do this. It's gonna try this with country number to, for example, or a specific department I'm gonna actually to do with with the country again. So I'm going to create table from going to name it. Germany. Germany is number two in my database in here, please, that I'm going to hit as so like we're going to select Astra's this time going to select specific columns of impunity. This is the first column that I'm going to need in the first name in the last name. Then the country I d. Then maybe department fighting. Okay, that's the column that I'm going to clone on Lee, and I'm going to hit a condition or other condition where the country are the equal to You can go. Haven't AB any condition that you like based on the gender, the date, the email address department, the status, any condition. But I'm going to stick with the country in here so that it's very obvious. Let's go ahead and ground this query. Okay, I have an air in here. I'm missing something, which is the original table from employees. My sequel didn't know from where it's going to get the structure off the new table from where this feels air coming from. It's from the employee stable, the original one. Let's go ahead and run this again. It's normal. From time to time, you're gonna have some problems, But you're gonna have to get familiar with these terms of errors so that you don't panic and have to sold them quickly. Let's execute. And as you can see and hear it, house room successfully, I'm going to select from the new table. Select Oh, from Germany. Who? Please. Um, let's see. The results on here is a result. We have two empties on Lee from Germany and we had Onley in the new table. The maybe i d first name last name country rd department are the the colon that we specifying when we were cloning this table. I think that this is a very powerful option in my sequel the debates Very easy to use. Trading tables, new tables With this technique, I hope that you like this technique and you enjoy this lecture on and that would be it. So you in the next one 54. Delete Statement: and is that you're going to see how to appropriately lead wriggled from a database using the lead statement. So as we have seen in the previous lecture, we've created E couple off tables and here like that Germany, and please use am please using the cloning technique in my sequel to select All from Use a employee, Stephen here, the in peace from you say that we've grated and the previous lecture. What I would like to do now is delete this simply, for example, this John. This is specific. John the employee i d Number two. There's plenty of ways to bleed the record, but you have to delete it mainly by the primary Keep. This is the key to his how we're going to ensure this is how we go into great relations. Is it the main important column in all of these columns? This doesn't mean that you cannot delete based on other conditions, like a country I d over gender, For example, let's go ahead and try to do all of these, so I'm going to first delete from you say hippies. So to delete statement, we have to we have to time delete from then the name of the table. If I trying to do this right now, my bicycle is going to delete all of the records. So to prevent this from happening, I need the condition where MP I'd be equal to. So right now. Sequel service going to oblige the lead statement. Do this condition or this empty in here, which is John or the employee number two? Let's try to run this. Remember that we have here, John P number two the man Run this query. Let's again run the select statement to see the table. Andi John has been deleted. As you can see, we have now MP everyone three and nine without the second John in here because it was the leader. I can go ahead and lead based on the department. I d, for example. So I'm going to delete the impede in department number six. So that's good. And do this lead from a right now we have only one MP, which is Jessica Papi. I d number one. But if we have multiple it, please be going to delete them with probably my skin is going to delete them without a problem, is going to try to apply this condition to any number of from please. So where? Department by the equal Going to six. And Gunness, Andi, Jessica, His gun. There is no just you can't anymore. We have to appease on Lee. Let's try to select all from Germany, please. This table had to. It has two records in here. If I try to a blind the lead front Germany, and he's without adding a condition. Now both of these triggers is going to be deleted. My eyes here is going to beat the whole table, and it's not going to drop the table. There is a difference between the bleed or deleting the data or the content off the table on dropping the table. Leading the data mean that the table still exists, but it's empty. Dropping the table is going to drop the table on the data on. You cannot no longer you the stable toe. Right now I'm cleaning or clearing the data in this table because I'm not adding any condition. That's right. Let's run the select statement again. I'm here to be. Table has been cleared. Now it's empathy. It does still exist in here as you can see and hear the structure of red, but it's empty When were saying before we end this lecture. Select old from use a use a M Please. I can go ahead peas. I can go ahead on and lead by the name. For example. They can come in here and help bicycle that I would like to lean from Use a him please way The first name equal Don't or even I can use a like if I would want to do this Let's try to excuse this. Defines the left again. You see the use. But if I select right now, if I selected the you say in please table, I can find that I have only one simply left on John has been treated based on this condition. Lead statement can be very dangerous. You have to be very careful when using the elite statement on taking a look at the condition that you are applying to elite statement. So that would be it for now. And see you in the next lecture 55. Update Statement: Welcome back. Everyone in this lecture going discussed one more essential statement that we need to learn in my sequel. Which 80 updates statements. This statement is used to modify the existing records and table from its name its updating the data that you already have, sir, Along with the insert on Italy Update, a Dysart barred off triangle to these essential statements. So this is the Semtex to use an update statement. We don't have the update keyword than the table name that we're going to update. Then we're going to set the column. That is the column that I'm going to update set this new value in this column set a new value and etcetera, separated by commas in here that I can go ahead and add aware condition or not. Add a week condition. It's the same at the insert. But if I didn't add aware condition, it's going to be applying this update. All of the fields. Let's go ahead and see this in action. So let's first, like the Dayton simply so select coal from M. P's own life to take a look at dorm or them free number one be on the equal one Let's execute this. What if I wanted to update one of the columns in here? One of the data. Maybe I would like to change the email address off John or this empty in here. Let's go ahead and do this. So I'm going to type at the Santic that we have seen in representation. Update the table name, which had the m please, then said word which feel are going to update it? A. The e Men. I'm going to sit it to a new value. Maybe. Gene. All right, some email provider. Don't go. Go ahead and type any email that you would like on if I leave it like deaths. Right now, it's going to be applying this email toe all of the employees because I did not add a condition or aware in here. Now my insecurities going to sing that I need to update all of the fields in this table, the email field. And that's not what I want to do. That's a mistake. You have also to be aware of this, so I'm going to other wear clothes where every I, the equal one you can select the road that you would like to evade by then P i D. For example, in here, the primary k o the name or the gender or the data purse or any other field that you would like to select it. Where's but right now I'm preferring to select it. Use ings. Primary key, sir. Let's go ahead and run this query. Andi, remember that now? John had an email address called John at my email that come. But just before we were that we need to change. It's not email it email address that that the correct call of name because the email only it's not valid. It's not the column name to that. The coloring. Let's go ahead and run this armed. It is affected when were affected in the problem that select again from them. Please, on here it is. As you can see now, the Colin off the empty address for John has been a change. John at some email provider dot com. I could go ahead and change it based on any other column. So let's go ahead and try to change it. Based on the last name, I'm going to hit last name Equal going to time. The last name off John in here. So right now I'm dating this. Based on the last name, I'm going to change this. Maybe John. Doctor, this name, uh, my Maillet Semi colon on. Let's run this. I run this right now. I want to be affected without a problem. That's against, like, the table. And as you can see, the email address has changed to John Stallings at my email. Got based on the last name. If your honor, that we have to Jones in here. If I run this select statement again, we can find that we have in free number one and empty number two parts of them are cold. Jon. If I trying to change the email address, for example, based on the first name on Lee, it's going to update these two fields together because John or the first name is not unique in here. If I liked updated for every number one or this John Jurist Helling's, I need to add some unique color like the last name or the empty I D. In the scenario, of course, let's get him trying to update the email address again. But selecting the first name with John, which is going toe affect this toe Jones in here. So I'm going to take this as a copy just to make it less pouring. So updated. M please. Email address. John Stallions email that come from Sam on them. Going to change the condition is going to be the first name on them going to time. Don, what's going on like this? And I needed to take carefully in here that the second John. The email address is James at my gmail dot com. Oh, my email that called Majima. Let's excuse this. Let's run the table again, Okay? As you can see in here, both of Jones in here has changed it to Jonah's tellings, and that's totally un correct. You need to be very carefully. Maybe you're reward scenario you life to update the salary for some kind of on simply. And he didn't have the right condition for this. Simply you're going to change it for the wrong in please. And that's of course, a problem. Let's go ahead and try to update in other column. Maybe I would like to add interim marks in here. Some remarks aren't going to add update in please. So remarks equal use a completes. I'm going to add this text in here, you say and please Indiana marks field in here. So I'm going to obey them. Please said the remarks column in here to use a m please, where we need to have a condition. Can you figure out what this condition is? Country I the equal one. So right now it's going to apply or add this text in here and the remarks for Onley the employees off number one, which is use a employees. That's glad and execute this. Let's go back. Select all from the peas on as you can see in here now, employee I d. Has been a change. It toe one. It was empty. I d number one is from country number one. If I run without the condition of the MP so that I get all the data from the table, I can find that all the m please with the country and the number one as in hearing Jessica Will Fred on the two johns. The remarks has been updated for them. Okay, so that's basically how you work with the update statements. Date Statement, as I told you, is one of the most important statements and relational databases, such as my sequel. You're going to use it frequently. How did you understand this on? That's it for now. See you in the next lecture. 56. Exercise Update Statement: time for an exercise. In this exercise, I need to go ahead and trying to update the job. Minimum salary to 5000 on the maximum salary to 8000 for only two jobs. Software developer on the Blanding Engineer, We're going to give you any more information. Go ahead and try to think about this and try to apply. Okay, everyone, let's go ahead and try to do to get it. So the first thing that I'm going to do is this alike. The data are the jobs. And as you can see in here, the software developer job has different value than we're going to update it on the job. I d for the software developer is street on the planning engineer 13. I'm going to update it or adding the condition by the job. I do. You can go ahead and do it based on the job title, but I'm going to you the idea. So I'm going to time update jumps. So your job minimum salary to Southland. Come on, job maximum salary to 8000 or are the most important part. Where? Where the job I d equal three that we get this record or a job by the equal. 13. Not on you. Don't do the end in here of Davis or excuse us as against eat Tool, Rose. Effective. That selected job. I'm here today. The software developer. Now it's 57 on and 87 on Also the Banning engineer. 5000 and 8000. Okay, that what? The exercise to you in the next lecture? 57. Exercise Update Status of Employees: we have one more exercise ability of date statements and this exercise. I needed to go out and update this peters of em, please. On before nine and 14 to married. Update the status with Aaron, please. Number 10 and 13 2 divers and the rest of them, please to single. Go ahead. Think about this. I'm trying to apply. Okay, let's go ahead and try to sold us together through. The first thing that I'm going to do is to select all the data from their pleas on from the status. Let's excuse me. One within, please. Okay, so we're going to update first. All of the employees, As you can see, the status idea right now is almost set amount. So I'm going to a day old estate is for old empties toe one that I'm going to filter out the M, please. Number 49 and 14 10 and 13 to a different status idee. Okay, lets start boy updating the whole table to date. Please said status are the equal one. This is going to update the whole table only for this column, of course. So that's execute this if we run now. But this select statement. Now all the status has been set to one. Let's go have them now. Of the second board said Status 92. So to get the number, we need to run this selection in here. I already know that number one was the single. So right now I'm going to choose merit, which is Number Street. So I'm going to sit in the street where the three p are the in of this for nine and 40 at the same column on that's excuse this. But again, on the select statement off the police table on here it is all the number that the specified has changed now on them before nine changer to street, as you can see in here on 14. Great. Let's do the last one to take. This is a copy pasted in here. Run the Status Select statement that we know which one we're going to select. And right now we're going to select number five, which is divorcing. The force is going to be a blind, too, in green over 10 on day 13 of this one. Execute this around the select statement off the employees to make sure that everything has changed correctly. Andi, I believe it's down here. Yes, that's 10. Now it has changed too far. As you can see in here and again, 13 has changed too. G I D number 13. The status I d has changed to fight. So that's it about the exercise. I hope that you are getting more familiar with the update statement because you're going to need it in the future in many occasions. So that would be it. See you in the next lecture. 58. Final Lecture: Okay, everyone we reached at the end of this course. Let it the final picture. I hope that you learned some useful information throughout the course is there is a problem with some of the concepts. You can go back and watch it. If you still have a probably we can go ahead and send me on that. I would be gladly assisting you throughout understanding or fixing your problem. And excuse me if I take some time to answer, I get a lot of messages. I hope that you understand this. One more saying, guys, if you have the time to review the course, please go ahead and do so. I'll be appreciating this so that that handsy you in the upcoming courses.