 Hello there. What you should know before you take the course, you need a basic knowledge of SQL. You also need a basic knowledge of database concepts. Who should take the course? The course is designed for beginner data science analysts, data analysts, beginner data engineer, and beginner data reporting analyst. What you'll learn on the course, the course is a practical course and some of the things you'll learn include setting up the SQL reporting analytic environment. So you'll be installing a MySQL server and setting up what MySQL workbench which will be used for the reporting and analytic queries. You also learn about basic database concepts. You learn about data types using the SQL select statements to retrieve data from a database table. You learn about removing duplicate records, filtering data with various operators, analyzing data using patterns like the like operator using that in combination with wild cards. You learn how to sort the data that you have retrieved from a database table. You learn how to report on various aggregated data. So we'll be using aggregate functions. You also learn how to report on data extracted by joining data from multiple tables. You learn how to group data from a query, course format and duration. The format of the course is video based and the duration is 3.5 hours long. Thank you so much for watching and bye for now. Hello and welcome to this video. I will be giving you a brief introduction to SQL. What is SQL? SQL stands for structured query language. It is also popularly referred to as SQL in the IT industry. So what does SQL do? SQL basically allows you to access and manipulate databases. SQL became a standard of the American National Standards Institute, which is also referred to as ANSI. It became a standard in 1986. Also, it also became an international organization for standardization in 1987, which is referred to as ISO. There are different versions of SQL. For example, you have the Oracle version. You have the MySQL version. You also have the Microsoft SQL Server version. You've got the PostgreSQL versions. You've got side based. So there are different flavors of the SQL language depending on the vendor. One thing to note with the different flavors or versions of SQL is that they have their own propriety extensions in addition to the SQL standard extension. So apart from the SQL standard language, these different versions of SQL also have their own proprietary extensions that they use in addition to the standard SQL. Because SQL belongs to the American National Standards Institute and is also an international organization for standardization, what they do, they help standardize some of the commands that are used in SQL. For example, where we have different versions or different vendors have their own version of SQL, they have to comply with the American National Standards Institute and the International Organization for standardization. They comply in that they all have to support setting commands in the same way. And these commands are select, update, delete, insert and where. So all these commands, when you use them with different versions of flavors of SQL, they must be supported in the same way. That's what this standardization enforces. SQL is very useful. And there's a lot you can do with it. You can retrieve data from a database, you can insert new records in a database, you can update existing records in a database, you can delete records from a database, you can create new databases and tables, you can create stored procedures. Stored procedures basically are SQL scripts that will get executed when setting conditions are met. They're usually stored in the database. Also, you can use SQL to set permissions on various types of database objects, like tables and other related objects. So that's it for this quick introduction to SQL. Thank you for watching and bye for now. Hello and welcome. In this video, I'm going to give you a brief introduction to my SQL. My SQL is also popularly referred to as my SQL in the IT sector. So in this video, I'll be referring to my SQL as my SQL. What is my SQL? My SQL is an open source relational database management system. So it's a software used to manage databases. Open source means that it is open for other developers to contribute to make it better. My SQL can be implemented on a variety of platform. So you can run it on a Windows based computer, a Unix also includes Ubuntu, you can also run it on Mac OS and other related platforms. My SQL is ideal for both small and large applications. My SQL uses standard SQL. My SQL is fast, reliable, and easy to use. My SQL has both free packages that can be used and there are also commercial related packages. My SQL is owned and distributed by Oracle. My SQL is popularly used in combination with the PHP programming languages. Other programming languages can also use MySQL but it is popularly used with the PHP programming language. My SQL like other relational database management system stores its data in a table. Let's take a look at the customers that are currently using My SQL as their database. So this link here will show you the customers using My SQL. So this is My SQL customer page that shows you the various customers using My SQL so they are grouped in different categories. So you've got the telecom sector, you've got the web e-commerce, you've got the technology slash software. So there are different aspects. So you've got the government, you've got healthcare, manufacturing, media, retail. So let's take a look at the media and entertainment. So you've got YouTube, Netflix, Spotify and more. So there are some key heavy players here using My SQL as a database. So feel free to look through this list and that shows you how popular My SQL database is. So that's it for this quick introduction to My SQL. Thank you for watching and bye for now. Hello and welcome in this video we are going to download and install My SQL. We are going to be using an installer to install My SQL and other related software that you would need to work with My SQL. So what is an installer? An installer is basically a piece of software that is used to install software programs on a system. An installer basically makes it easy to install other programs by providing the user with a step by step process also giving them the ability or the options to select what they wish to install. So to download the My SQL installer we need to go to the link I have displayed on the screen. This is the download page to download My SQL installer. So if I scroll down to the bottom of the page when you download the installer and run it it will install all these various software. These are all part of the My SQL catalog. These can be installed individually which takes a lot more time but when you're using the installer it can install all these together for you. So that's the advantage of using the installer it basically simplifies the process. So if I scroll down there are two installer files depending on how you wish to run the installation. If you want to run the installation with an internet connection then you choose this file. If you want to run the installation offline you choose this file which is a much bigger file. So if I scroll down you'll see the two files that we have. For me I'm going to choose the smaller file because I want to run my installation using my internet connection. So I'm going to download that. If you however want to run your installation offline you need to click on this download button which is a much bigger file. So I'm going to go for this one. So I'll click on the download button and it gives me this page to begin your download. It's asking if you can sign up or if you've already got an account with Oracle it's telling you to log in. Well this is optional you don't have to do that. There is an option here to skip that. You can say no thanks just start my download. So I'm going to click this link here and it should begin the download. So that's my download there. I'll just give it a few minutes to complete. So the file has finished. So I'm just going to double click to run the installation. You may get an antivirus pop-up when you try to run the installation basically asking you if it is safe to install the package. So I'm going to double click and I'll just say yes to my antivirus. You can see it's asking if I want to launch and I've said yes. So it's telling me here that there is an upgrade available for my SQL installer. It's saying if I want to go for the upgrade I'll say yes to begin the upgrade of my SQL installer. So you are presented with this page here for license agreement. So click on this checkbox here that says I accept the licensed terms. Feel free to read through that if you wish to do so and click next and then you have to choose the type of setup. There are different types. The default is developer. It tells you what is going to install in this area here. If you click on the different options you can see what it entails. For me I'm going to go for the full option here which will install all including my SQL products and features. So it tells you here what it is going to install. So now that I've got my setup type selected I'll click next and here I'm presented with an option to manually install this Python connector. My SQL basically has different connectors that allows you to connect to the MySQL database. They're basically connectivity drivers for database. So if you're working with Python for example and you want to use MySQL as your database then you will need a connector. I'm going to skip that at the moment because I don't need it. So I'll say next. So what it's doing here is telling me that I have not chosen this option here. So it's asking me do I want to go ahead with the installation despite the fact that I have not chosen that. I'll say yes please go ahead click on that and what it will do it selects all the products that it is going to download and install. You can see here the status is saying ready to download and it's asking you here to click on the execute to install or update these packages. So I'm going to click execute to begin the download and then installation of the packages. So the packages have all been downloaded and installed. You can see the status is complete which means it has successfully installed all these packages. Hence you have the green ticks. So the installer has taken us through the license agreement. We've chosen a setup type. We've done the installation. Now we are going to the next stage which is the product configuration. So click on the next button and here it's telling us it's going to configure these products. Okay. So it says ready to configure. Just click on the next button and we will be giving this option here for group replication. So you have an option here to select this standalone MySQL option. It tells you what the option does and then gives you this option here also as a sandbox. I'm going to choose the default which is standalone. I'm going to click next and then here I have type and networking. So we need to configure what type of SQL server we want to set up. So in the dropdown here there's different options here. We've got the development which is the default. We've got the server computer. So if you have several server applications that will be running on this computer you need to choose that one and then you have the dedicated one which is this one here. So what I'm going to do I'm going to leave it on the default which is a development computer and then for connectivity I need to make sure that the TCP IP is checked because we're going to connect using that and I'm going to leave this port also checked and here we're going to open the windows firewall ports for network access. So I'm going to leave the default settings for the type and networking. So the next click on the next tab to go to the next configuration is asking you for authentication method is saying to use strong password encryption for authentication. This is recommended and then it says use legacy authentication method blah blah blah. So I'm going to go for the recommended option I'll click next and then you need to specify the accounts and the roles. You need to specify a password for my SQL route is very important that you choose a password that you can remember later because this is development doesn't have to be a strong password but choose one that you will remember. So I have entered a password and confirmed it is telling me that my password strength is weak and doesn't really matter because this is just a development machine and then here also is giving an option here to add a user. So if you wanted to add users to your server this you can also do it here says create my SQL user accounts for your users and applications and then you can also assign a rule to the user that consists of a set of privileges. This you can also do at a later time. So for now I'm just going to click next and then here is giving you an option to configure Windows service. So here for the Windows service you have to provide a name is giving you a default name there because this is development server just accept the default you can change that if you wish to do so and then you also have the tick here that says start the my SQL server at system startup. So when you start up the system it will automatically start up the my SQL server. So leave that checked and then he's asking you how you want to run the Windows service. It's giving you a default here of using the standard system account. I will recommend you accept that and just click next and then it's telling you here to click on this execute button to apply all the configuration changes. It tells you giving you what the configuration steps are here. So by applying the execute button it will apply the configuration settings. So I'm going to click on that and you can see it's going through the various steps of applying the configuration. Once the configuration has been applied you will get this little message here saying the configuration for my SQL server was successful. Click on finish to continue. So I'll click finish and you can see it's giving you a next button. We now have to move over to the configuration of the router and the samples and examples. So click on next and then here it says configure my SQL router for inodb cluster. No we're not going to do that because I have not chosen this inodb cluster. I'm going to skip that and just click on finish and you can see here it says initial configuration. So it's still initially configuring the router. The router basically is a lightweight middleware that provides transparent routing between your application and any back end my SQL server. So basically the function of the router is to provide transparent routing between your application and any of the back end servers. So once you've got this button here if it's highlighted it means you can progress. Click next and here you've given the option to connect to the server and that's the server. That's the port. Make sure you take notice of the port in case there are any issues. It's asking for a password. The username to connect to the MySQL server is going to be root. The password will be the password that you would have set during the configuration. So I'm going to enter mine. I've entered mine. Just click on check and if your password is successful you can see here it says connection succeeded. All connection succeeded. Just click next and it says press execute to apply the changes. I'm just going to press execute. So once the configuration operation has completed you will get this finished button highlighted. Just click finish and it tells you about product configuration. The router says initial configuration because we didn't actually configure it as such. Hence it's showing initial configuration. We can click on next and it gives you an option here. It tells you the installation is complete. So we've gone through all the steps using the installer and this is the final step. It tells us the installation is completed. You can copy the log to the clipboard. You can start MySQL workbench after the setup. You can start MySQL shell after the setup. I'm just going to check that. I don't need to do that and I just click finish. So this concludes the download and installation of MySQL. Thanks for watching and bye for now. Hello and welcome. In this video I will be showing you how to download and install MySQL database on macOS. I have got the address displayed on the screen that will take us to the download site. Before you download the MySQL for Mac it's good to check what version of macOS you are running and you can do that just by clicking on the apple icon on the top of your screen on the preferences and just click on about mac. It will bring you this view here and my version is 10.13. So check the version you have got. So this is the download page for MySQL which is MySQL.com. So once you land on this page click on the downloads button and that will show you the different products that you can download. Some of the products are commercial. It tells you there like that is commercial. The enterprise version is commercial. So we want the free version that we don't need to pay for which is this one here. It's called MySQL community edition. So click on that and that will show you the download options. So what we want is the MySQL community server. Current version as of the time I am making this video is 8.0.11. So click on MySQL community server and that will give us some options here. So you can read through if you wish to do so and it tells you installation instructions and stuff. So but what we really want is this. I've already checked the version of Mac I have got. Make sure on the operating system you've got the Mac OS selected and here you can see it says packages for high Sierra 10.13 which is what I got is compatible to this one. So make sure you click on the download that says dmg file which is disk image file. Click on that. Click on download and that will begin the download before the download will begin. You will get a prompt to say begin your download. It's asking you to either login or sign up but you can skip that by clicking on this link that says no thanks just start my download and that will start the download. So you can see the download has start. Okay that's my download there. We'll give it a few minutes to complete. Once the download has completed you can just double click to run the dmg file. So I'm just going to double click and you can see here is opening the MySQL Mac OS dmg file. So this is the package. Just double click on the package to run the installation. So once you double click on the package you can just click continue and click continue again. These are the steps it will take you through to complete the installation. So click continue and click continue again. You can say I agree. Click install and tells you it will take this amount of disk space. So make sure you have enough disk space. You can change the installation location if you wish to do so but I recommend you just accept the default. Click install. It will prompt you for your password. Just enter your password to progress with the installation. Click install and it's doing the installation. We'll just give it a few minutes to complete and it's asking you here to use a strong password. It's giving you guidelines of how to set up your password. Click next then you have to enter a password. The username for you to log into the MySQL is called root. So you need to enter a password that you want to use with it. So make sure you enter a password that will be easy for you to remember. So I'm going to enter my password and there's an option here that is checked. It says start MySQL server once the installation is complete. So if you don't want to start it you can uncheck that. Click finish and it's asking for your password again for your Mac. So this is your Mac password. So enter that in and click okay and you can see it's performing initial database setup. So it's doing the configuration at the moment. We'll give it a few minutes to complete that. All right. So it seems I've done it. It says thank you for installing MySQL server. So you can click on close. It says do you want to move the MySQL community installer to the trash? Yes because we finished it. So we can move to trash. Excellent. So that completes the installation of MySQL on a Mac. To check that you've got MySQL running you can go into your preferences. So just open up your preferences tab and inside the preferences you should see the MySQL icon. So click on that and you can see here it tells you that you have an active instance of MySQL 8.0.1.1 which is currently running. You can see here you've got this box checked which says start MySQL when computer starts up. Okay. So you've got the path here as well to where the installation is held. So that's the instance that you've got configuration settings as well. So you can look at the various configuration settings for MySQL. So that's it for this video on the installation of MySQL on a Mac. Thank you for watching. Bye for now. Hello and welcome to this video. I will be introducing you to MySQL workbench. What is MySQL workbench? MySQL workbench is a comprehensive visual administrative tool. It can be used for database design and modeling. It can be used for SQL development. It can be used for database administration and also to connect to different databases. You can use it to perform database backup and restore. You can also use it for database migration. It is a very powerful tool that has a bunch of integrated tools for administrative purposes. So it's very, very useful for database administrators, for database architects, architects, for developers, programmers, analysts, and anyone who works with database. It's a useful tool that you can use to perform all your database related administrative tasks. It also comes with an editor that you can use to query and manipulate the database. If you installed MySQL using the MySQL installer, it will also install the MySQL workbench. So I'm going to access it, go into my all programs and I'm using a Windows 10 machine. So I'm just going to scroll down and look for MySQL folder. So this is MySQL folder. This is MySQL folder. If I expand that, you should see I have got MySQL workbench, which is this one, version 8.0. So I'm going to click to launch it. So when you first launch the MySQL workbench after the installation, this is what the screen looks like. So you've got different tabs here. You've also got some information in this area that says welcome to MySQL workbench. So it gives you a brief description here, what to expect. Also here in this area here, it tells you you have a local instance of the MySQL router and give you the username to log into a database. And you've got an option to read the blog, discuss on the forums and so on. So these tabs here are quite useful. If you want to script, you can click on the scripting tag. It gives you different options for scripting. It doesn't help menu. Also you can look through. And there is a MySQL workbench forum. There's a blog, different tools, configuration. If you want to set up backup connections, restore connections, if you want to start the shell, if you want to connect to a database, let's try and connect to a database. I'm going to click connect to database and it's going to give me this option. If you've already connected to a database before, it will save the connection there. So I'm going to click on the drop down. I've only got one database, which is local instance. Okay. And then you have different options here. So you can click on the advance if you want to. I would just focus on the default for now tells you the host name is local host, because you're running it locally. And that's the port is running on the username is root. And if you press okay, it should give you an option for your password. So I click okay. And it will give me option to login. So I just need to enter the password I entered during the installation of the MySQL configuration. So I've entered my password. I would not recommend you use this option to save the password in the vault. What that does if you save the password, then it does not prompt you anymore for your password that can be a safety issues in case someone grabs hold of your laptop or your computer then they'll be able to access your database. So I don't recommend you use that option. So I'm going to click okay, to connect me to my database. You can see here I've logged into my database successfully. So it tells me the status of the server. Give me different database options here. So this is just a quick tour of the MySQL workbench. But feel free to go through at your own convenience. And there is a useful help option here. Just search for anything you're not sure of and just experiment by going through the various tabs you have. Also the icons here, if you put your mouse over it, it gives you a description of what each of these little icons do. So that's it for this quick introduction to MySQL workbench. Thank you for watching and bye for now. Anything you need to do on the MySQL database, you're going to be doing it using the MySQL workbench. Bye for now. Hello and welcome to this video. I'm going to introduce you to some basic database concepts. I'm going to start with a database. What is a database? A database is basically a collection of organized information or data that is stored in a table. So let's take a look at an illustration of what a database looks like. So this is a basic illustration of a database. So a database stores information or data in a table. So this is basically a table. And a table consists of columns. The columns are the ones across in blue. And it also consists of rows. These rows are known as records. So each record is unique in a table. So we now know that a database stores information in a table. What is a table? A table basically stores information in rows and columns, just like in an Excel spreadsheet. So if we take a look at the illustration I showed you previously, we can see that this is a database that consists of a table. And the table has information in rows and columns. So these rows are horizontal. Okay, so these are all rows. They are horizontal and they represent unique records in a table. The columns or fields are in blue and they are vertical. So age, city, address, last name and first name can be referred to as columns or fields in a table. While the rows here are referred to as records. So each record should be unique in a table. Next, I want to introduce you to the concept of a relational database. A relational database basically is a database where two or more tables are related. So I'm going to show you a quick illustration. So this is an illustration where we have several tables in the database. So this customer is one table, orders is one table, other items is another table, products is another table. So with a relational database, there must be relationship between two or more tables. If we take a look at the customer's table here, we've got a customer ID, which is a column or a field in the customer's table. We've also got a customer ID here, which is also a field or a column in the orders table. So you can see the dot representing the relationship because there's a customer ID there, and there's a customer ID there. All right, if we look at this order items table on this order table, we've got an order ID, we've got another ID, you can see the dot here representing the relationship. Likewise, we've got a table called products. You can see that we've got a product ID and we've also got a product ID in this order item. So you can see the dot here representing the little relationship between the tables. Next, we're going to look at what a relational database management system is also referred to as RDBMS for short. A relational database management system is basically a software that is used to manage databases. So there are several types of relational database management system, depending on the vendor. Examples include Oracle, Microsoft, SQL Server, MySQL, PostgreSQL, and many others. Next, I'm going to introduce you to the concept of a primary key. A primary key basically is used to uniquely identify each record in a database. Most primary keys must contain unique values. That means a primary key cannot contain null value and null value basically is a value that is not represented is unknown. So there must be a record that identifies each record uniquely in a database table. Also a table can have only one primary key, which can consist of a single or multiple fields. So again, looking at this illustration of a table, we can see here that where we've got PK PK represents a primary key, we're using the custom ID as a primary key to identify each record in the table. In this table, there we're using the order ID as a primary key. The next concept is a foreign key. A foreign key basically is a key that is used to link two tables together. So a foreign key can also be a collection of fields in one table that refers to the primary key in another table. So again, using this illustration here, we've got in this table, for example, we've got custom ID as a primary key, while in this table called orders, we've got customer ID as the foreign key. So the table that contains the foreign key is called the child table. And the table containing the primary key is called the parent table. So in this case, the customer ID here, where we've got the primary key is a parent table, while the customer ID here we've got the foreign key is the child table. The next concept I want to introduce you to is constraints. Constraints basically are used to specify the rules you want your data to follow in a table. And when you are setting up constraints, you can actually implement it at the point of creating the table. So when you're creating a table, you can also implement different types of constraint. An example constraint you could implement, for example, in any of the fields, you could say you don't want the value to be null, null basically means that a value that is not known. See, for example, here, I can specify that the customer last name, I can place a constraint in this field and say the customer last name cannot be null. That means they must provide a name for the customer name without that it will not allow the input for that column. So that's basically what a constraint means. There are different types of constraints you can use to specify different types of rules. You want your data to follow in a table. So any data you input, you want a set of rules for that data to comply with. So that's it for this video. In this video, I introduced you to some key and basic database concepts. Thank you for watching and bye for now. Hello and welcome to this video. I'll be introducing you to schema. What is a schema? A schema basically is a container of database objects and configuration. So a schema is a container of database objects. By database objects, it could include things like database tables, index, views, and stored procedures. Schema basically is a logical way to group objects in the database. For example, you can have a large enterprise application and you can have different schemas for different aspects of that application. For example, you can have an HR related table and you can put that into a HR schema. You can have an accounting related table and put that into the accounting schema and so on. So a schema is very useful. It makes management easy. With schemas, you can apply permissions to individual schemas. And when you apply permissions, the users who have access to that schema will also have access to the objects in the schema. For example, the database tables and so on. A schema can be owned by any user and the ownership is also transferable. Schemas are generally created and also altered in the database. So schema basically can also represent the logical structure. So whereby you have the logical structure of a schema, that will give you the relationship between the tables in the database. When you have a physical schema, basically it relates to the structure and the data that it holds in the database. So I have opened up my SQL workbench and connected to my database. When you install my SQL and my SQL workbench using the MySQL installer, it also installs some sample databases. So you can see here where I've got schemas. I've got three databases listed here. You can see these are all sample schemas. I've got one here called circular. I've got sys and I've got world. So these are all different schemas. So you can easily assign permissions to each of these schemas so that some users can have access to one schema and not the other. So they're very, very useful. They logically can group objects in a table. If I expand this schema, for example, you can see here these are the tables that belongs to this circular schema. If I expand that you can see these are all objects. These are tables, views, stored procedures function. These are all referred to as objects and they are all components of a schema. If I open this schema, again, you have related objects as well. So every schema has its own logical structure and object contained within it. It's really useful to implement schemas because you can isolate different schemas whereby you can give access to certain people and lock it down so that those who are not meant to access certain tables in certain schemas will not have access to it. For example, if I had a finance schema, I could have one for HR. I can have one for maybe something else. So you may when you have the schema structured in that way, you can easily set permissions on the schemas and then lock them down. So basically, schemas are logical or containers that you can use to hold database objects. So that's it for this brief introduction to schemas. Thank you and bye for now. Hello and welcome to this video. I'm going to introduce you to some basic database concepts. I'm going to start with a database. What is a database? A database is basically a collection of organized information or data that is stored in a table. So let's take a look at an illustration of what a database looks like. So this is a basic illustration of a database. So a database stores information or data in a table. So this is basically a table. And a table consists of columns, the columns are the ones across in blue. And it also consists of rows, these rows are known as records. So each record is unique in a table. So we now know that a database stores information in a table. What is a table? A table basically stores information in rows and columns, just like in an Excel spreadsheet. So if we take a look at the illustration I showed you previously, we can see that this is a database that consists of a table. And the table has information in rows and columns. So these rows are horizontal. Okay, so these are all rows, they are horizontal and they represent unique records in a table. The columns or fields are in blue and they are vertical. So age, city, address, last name and first name can be referred to as columns or fields in a table. While the rows here are referred to as records. So each record should be unique in a table. Next, I want to introduce you to the concept of a relational database. A relational database basically is a database where two or more tables are related. So I'm going to show you a quick illustration. So this is an illustration where we have several tables in the database. So this customers is one table, orders is one table, other items is another table, products is another table. So with the relational database, there must be relationship between two or more tables. If we take a look at the customers table here, we've got a customer ID, which is a column or a field in the customers table. We've also got a customer ID here, which is also a field or a column in the orders table. So you can see the dot representing the relationship because there's a customer ID there, and there's a customer ID there. Alright, if we look at this order items table and this order table, we've got an order ID, we've got another ID, you can see the dot here representing the relationship. Likewise, we've got a table called products, you can see that we've got a product ID, and we've also got a product ID in this order item. So you can see the dot here representing the relationship between the tables. Next, we're going to look at what a relational database management system is also referred to as RDBMS for short. A relational database management system is basically a software that is used to manage databases. So there are several types of relational database management system, depending on the vendor. Examples include Oracle, Microsoft, SQL Server, MySQL, PostgreSQL, and many others. Next, I'm going to introduce you to the concept of a primary key. A primary key basically is used to uniquely identify each record in a database. Most primary keys must contain unique values. That means a primary key cannot contain null value and null value basically is a value that is not represented is unknown. So there must be a record that identifies each record uniquely in a database table. Also, a table can have only one primary key, which can consist of a single or multiple fields. So again, looking at this illustration of a table, we can see here that where we've got pk, pk represents a primary key. We're using the custom ID as a primary key to identify each record in the table. In this table, there we're using the order ID as a primary key. The next concept is a foreign key. A foreign key basically is a key that is used to link two tables together. So a foreign key can also be a collection of fields in one table that refers to the primary key in another table. So again, using this illustration here, we've got in this table, for example, we've got custom ID as a primary key. While in this table called orders, we've got custom ID as the foreign key. So the table that contains the foreign key is called the child table. And the table containing the primary key is called the parent table. So in this case, the custom ID here where we've got the primary key is a parent table. While the custom ID here where we've got the foreign key is the child table. The next concept I want to introduce you to is constraints. Constraints basically are used to specify the rules you want your data to follow in a table. And when you are setting up constraints, you can actually implement it at the point of creating the table. So when you're creating a table, you can also implement different types of constraint. An example constraint you could implement, for example, in any of the fields, you could say you don't want the value to be null. Null basically means that a value that is not known. See, for example, here, I can specify that the customer last name, I can place a constraint in this field and say the customer last name cannot be null. That means they must provide a name for the customer name without that it will not allow the input for that column. So that's basically what a constraint means. There are different types of constraints you can use to specify different types of rules. You want your data to follow in a table. So any data you input, you want a set of rules for that data to comply with. So that's it for this video. In this video, I introduced you to some key and basic database concepts. Thank you for watching and bye for now. To retrieve data that is stored in a MySQL database, you have to query the database. And the statement that is used to query a MySQL database is called the select statement. This is going to be the most used statement that you would use when you are working with a MySQL database or any database for that matter. The select statement is very important. It's what you use to interrogate the database to get information out. Obviously, you have to use the select statement with other clauses or other keywords. For example, your select statement must always contain a from the from clause basically is where you specify the table that you want to retrieve the data from. You can also add a where clause. A where clause is basically used to act as a filter so that you only retrieve from the query the information you want. Your select statement can also include operators. Operators could include things like greater than less than or and in between and so on. So you can add all these things to your select statement. But the basic select statement must always have a from. In most cases, it has a where as well because you don't want to put a lot of pressure on the network or the database by using the from by itself. The where clause will filter and limit the records that are returned by the query depending on how you have specified the query. So let's write a very basic select statement. So in my SQL here, you can see the database I am trying to query is this database here called fruit shop. So make sure that you have your schema highlighted because that is the schema that the query will be run against. And to change schemas, you just double click on any schema you want. And that schema will become bold. And then any query you write will be executed against the schema. So the schema I'm working on is this fruit shop. If I expand that it has two tables, one is called fruits, one is called units. So let's write, let's try and get some data from this table. So to begin, you write a select statement, I'll say select. That's the first thing you do. And then you do a space. Next thing you have to specify is the from, you always have to specify the from the from before you select the from when you do a select statement. You can either select individual columns. For example, if I expand this here, and you can see all the columns, I can either select the column I want, or I can use an asterisk. When you use an asterisk, it will return the records from all the columns within that table. So once you specified the column, next thing you need to do, you specify the table where the information is coming from. So you specify that in the from clause. So the from here will be fruits. That's the name of the table. And then you terminate that with a semicolon. Semicolon means it tells the editor that that's the end of the statement. Now to run this, I just click on that to execute. And you can see here, these are all the columns in the table. You can see it has returned all the records from that table. This is okay, because this is a very small table. But if you are working in a large corporation, and you have a big database will tables that have thousands and thousands of records, you don't want to do a star. You don't want to use the star, because that will return the records from all the tables. So it always helps if you know the columns or records you want to query data from. So you could also select individual columns. So if I can do the query again, rather than use the asterisk, I can say select, I'll just choose the column. I've got a fruit underscore ID, I do a comma, and then I'll do a fruit underscore name. So there I've specified the columns I want. And then I can come here and do a from you can have your select statement on one line, or you can split them in multiple lines is up to you. And I'll select from and the name of the table is fruits. So this is one way. And if I run this query here, it will give me the same number of rows, but it will limit the columns, you can see here is always returned the information from two columns based on the column I have supplied in the select statement. Another thing you can do is use the where clause to filter the records that get returned by the select statement. So I could write another query like this, I could say, select star, okay, from any way you see the asterisk, it means it will return all the columns. That means you don't need to specify what column. So I say select star from the table called fruits. And I'm going to add the where clause, which will act as a filter. And say where the unit underscore ID is equals to one. So now if I run this query, I'm just going to highlight that and run this. And you can see here it has returned only the record that matches this criteria. You can see here I've got the unit ID one. So the only two records matches that this is another useful way of querying a table, because it limits the traffic on the network. And your queries get returned fairly quickly, because you're not querying the entire database table, you're just querying the information you want. So the where clause is useful in that it will help reduce the amount of records that gets returned back. So that's it for this video on using the select statement to query a MySQL database. One of the advantages of using a select statement is that it does not change or alter the structure of the database or the database table. A select statement will only ask for information and get the information back, but it cannot alter the structure of a database or a database table. Hello and welcome to this video. The distinct keyword is basically used within an SQL statement to remove duplicate rows from the result set of a query. When you execute some SQL statements, it is possible that the result will contain some duplicates. So to remove them, you need to use or include the distinct keyword in your statement to get rid of them from the result of that query. The distinct keyword does not remove the duplicates from the table. It only removes the duplicate from the result output of the query. Let's consider the following query I have in the query editor. So this query here does not include a distinct keyword. So basically what I'm doing here, selecting the first name from the table, this database called Sakila. And the table I am selecting the columns from is this column called actor. And the column here is first name. So I'm selecting this first name from this table called actor, which belongs to the Sakila schema or database. And this is a condition I'm using the where clause to look for this condition. So it will only return results that match this condition. So what I'm saying here is that go get the first name from this table where the first name is like this. That means any first name that has a pattern that begins with a n, I want all the results. So let me execute that. And then you can see the output. So this is the output. Because here I specify that I want the records for everyone who's name begin with a n. All right, that's what this query is saying. And this is the output. You can see we've got Angela here. And we've got Angela there again. So that is a duplicate. So if I use this query and add a distinct, it will remove one of the instances of Angela. So let's modify this query to include a distinct. All I need to do after the select statement, I just add the keyword distinct. What I'm saying here, I want the first name here to be distinct. So now if I run this query again, by clicking on this, you can see here, I've only got one instance of Angela. So it has removed the duplicate from the result of the query, but not from the actual table itself. You can also use the distinct keyword with the count aggregate function. So for example, you can find out the count of the number of rows that match a setting condition. So I'm just going to exit out of this query window by clicking on the X. So I'm going to modify this query to include a count. So here I'm going to say select, I'll do a space and I'll say count count is an aggregate function. And you have to include aggregate functions in parentheses. So I'm going to wrap this in parenthesis here. All right. So make sure there are no spaces when you when you use the count, you can see here, there's no spaces between the parenthesis. So I'm selecting, I'm doing a count aggregate function on this value here. So it will return a count of any record that matches this condition. So it won't display the actual record is just give me a count. So let me execute this. You can see here is giving me a count of three. So there are three records that matches this condition in the work loss. When you use the distinct keyword, you can only also use it on multiple columns, not just the one column. For example, if I remove the count, I can do a distinct on boot, the first name and the last name. Okay, I'll just make this lowercase last name. And now I'm just going to exit this result. If I run this again, you can see here, I have a much larger output because I've used multiple column. But also you can see that there is no duplicate returned in the result set. So that's basically how you use the distinct keyword with your select statement. So the key function of the distinct keyword is to remove duplicate from the result of the query. Thank you for watching. Bye for now. Hello and welcome to this video. The where clause can be used within an SQL statement to narrow down the result set of a given set criteria. So when you use the where clause, you specify a criteria within the where clause. And that will determine the result, the query returns. Here is a very simple example, we have so far used the where clause during the course. This is just to reiterate the importance of the where clause. So here I'm selecting all the columns from the table called actor from the saquilla schema, where the first name equals to Nick. So this is the condition. It will only return the results that matches this condition. So let me execute. You can see here it's returned three records, because there are three people that have the first name of Nick. You can also use the where clause with an update statement when you're trying to update a record that is already in the database, you have to use the where clause to prevent other records from being updated. Also, when you're trying to delete records, you have to use the where clause very important because the where clause prevents unnecessary deletion of records from a database. Let's take a look at this query here. I'm not going to execute it, but I just want to show you the importance of the where clause. So if we wanted to update a record, for example, here, I wanted to update this record from the actor table, you use the set basically to change the value. So I'm changing the first where the I'm setting the first name to equals to Nikki, where the actor ID is two. So you can see the importance of the where clause, it will only action the update statement where this condition in the where clause is true. So it will only change if you can find an actor ID that matches this value of two, if it finds that it then makes the change and changes the first name to Nikki. So that's basically it on using the where clause. Thanks for watching. Bye for now. Hello, and welcome to this lecture. In this lecture, you will learn how to use the MySQL between operator. The MySQL between operator is used to determine whether a value is in a range of values. Let's have a look at an example and see how this works. I have got my SQL workbench opened. And I have already made a connection to the database. The database and schema I'm going to use to illustrate this example is the Sakela sample database and schema. So this is a Sakela schema. So if you click on this sign here, it will expand and you can see the list of tables. The table I am going to use to illustrate the example is this table here called the film table. And these are the columns. What we are interested in here is just the columns. If I expand that, these are the various columns in this film table. I am going to use three columns. I'm going to use the title, the release year, and the rental duration. So I begin by typing in the select keyword, the intelligence as brought up if you suggestion here. So I just click on that because it is what I'm trying to type keyword. And then do a space followed by the name of the column. So the first one is the title, double click, and he pops it in there. I go comma. Next one is release year. If I double click, it will pop that in there. There you go. And the final one I'm going to use is the rental duration. So double click. So you don't need to put a comma at the last after the last column. Now I've got my column set up. I need to specify the table the data is coming from. And the name of the table is film. So I just type in film there. Again, I'm getting some guides by the intelligence. I just click on that because what I'm looking for is film and it is a table. So double click on that. Next, I need to identify a filter because you use the between operator with the where clause, which acts as a filter. So I type in where another keyword where the I'm going to filter the condition I'm going to use for the filter and the between operator. I'm going to use this column here. So where the rental rental duration is between say three and six. Also, I want to set a limit on the number of rows it returns. I don't want it to return everything. So I'll specify the limit using the limit keyword. And then I specify in number seven. So my query is all set. I just need to highlight and make it look a bit beautiful by going to edit format, beautify query, you can see it looks much more decent. So we are ready. So line six here is the key. This is where we've used the between operator and the between operator is used in combination with the where clause. Where clause acts as a filter filtering based on this condition I have set here, the rental duration has to be between three and seven. So the between acts on a range of values. And the range I've specified here is three and seven. The three and let me change that to six actually, and make that six, three and six. So let me run this. Click on that. And you can see it has executed. And if you look at this window here is called the output window, basically what this window does, it records your action. So it tells me here that I the action I performed was a select statement. And these are the columns are used the table and it returned seven rows. And this is the time it took. There are other options for this output window here. So you can click on the drop down arrow it will tell you there is a text output if you got a text you want to output. Also there's a history output that will output a history of the task or actions you have performed. Okay, so I'll leave it on action output because he tells me the action I have just executed. In this lecture, we learned about the MySQL between operator, which is used to determine if a value is within a range of values. Thank you so much for your time. Try and practice with a few of the options change the values with the examples I have given you and try and play around with it. That's the only way you're going to get used to it. Thanks very much. Take care and bye for now. Hello and welcome to this lecture. In this lecture, you will learn how to use the MySQL in operator to determine if a specified value matches any value in a list or query. The in operator is used to match a specified value in a list or query. The syntax to specify the in operator is within a select statement in the word clause. The word clause is where you attach the in operator, you specify the condition in the word clause, then you attach the in operator. So let's have a look at an example to illustrate how the MySQL in operator works. We're going to use an example here. I am connected to the MySQL workbench and I've connected to the database. The database we will be using is a Sakela database and the schema also the Sakela schema. So these is this Sakela schema. So let's expand that to see the tables. The table I will be using is the film table. And these are the columns from the film table. I'm going to use three columns. I don't want to use all of them. I'm going to use the title, the release here, and the rental duration. So these are the three columns I'm going to use title, release here, and then the rental duration. So let's write the query. So we begin with a select keyword type in select followed by the names of the column. So the first is the title. Notice the intelligence there gives giving me some guide. You separate the columns with a comma comma. The next column is the release here. So I type in release here. Again, the intelligence is giving me a guide. This is a very useful utility. So I just double click. That's what I want. Comma. The next, I want a release duration. So that's my three columns. Next, I specify the table, the table name is called film, which is this table here. Okay, so what I'll do before using the in operator, let's just run this execute this query as it is and see. So if you notice, it returns three columns, release, the title, release here, and then rental duration. Okay, notice that different, because I've set a limit of row here, I can say don't limit, execute, and it will return all of them. So if I scroll, you see there's so many roles there, different values of roles. So so many. Okay, see, because I've said don't limit the roles that are returned. So let's return all the roles from these three columns. Now I want to use the in operator. But I have to use it with the workloads to act as a filter to only return the roles that match the criteria I will specify in the in operator. Oh, this this query is a bit untidy. So let me beautify that go edit, format, beautify. See, it looks a lot neater now. Now we're going to apply the workloads in combination with the in operator. All right, so I type in where see the intelligence is giving me a guide again, so I double click where because that's what I want, where the release duration, this is the condition now, the rental duration is the condition I'm setting, where the rental duration, and then I do a space. And then this is where my in operator kicks in. Notice all the keywords are highlighted in blue in this my SQL workbench. So I specify the in operator then when you use the in operator, you need to use parentheses as well. Because in between here, you specify the criteria that the in operator will work with. So if you notice here, there are different rental duration six, three, seven, five, and so on five scroll through you see there's so many more different types. So I'm going to use, I just want to select two criteria. So I'll say three, comma, and say five, just make it odd numbers. All right, so this is this is what the in operator will look for. If I run this query again, it will fit it will only return the rows where the criteria matches three, where this duration rental duration is three, or five. So let me highlight that beautify the query, edit format, beautify, see the query looks a little bit more decent now. So now we've got our query set. Notice the in operator here is used with a parenthesis because you need to specify the values inside the parenthesis that the in operator will look for. So what it is looking for in this query, it will only return rows that match three and five using the rental duration is the condition. So it will in return, when I execute this query, I only expect to see rows that have a rental duration of three and five. Also, I want to add something extra. I want to limit the rows to see seven. I don't want too many seven. Then I end my query with a semicolon. So let me beautify that again. Right, looks more decent now. All right, let me execute that. Click on that. As you can see, it has only returned rows that match three and five. So that's these are the conditions I have set in the work laws and using the in operator. Okay, so you know, notice how they work together, the in operator and the work laws, when you're using the in operator always know you have to use it with a parenthesis, because you need to specify the numerical values inside the parenthesis. Thank you so much for watching. I hope this lecture on the in operator has been useful. Try and practice using them with various tables and columns. Take care and bye for now. Hello and welcome to this lecture. In this lecture, you will learn how to use the MySQL like operator. The MySQL like operator is used to select data based on patterns. So it will look for specified patterns when searching for data. The like operator is used in combination with the work laws. So when you use the like operator, you specify it after the work laws. The like operator also works with two wild cards. They are the percent and the underscore with the when the like operator is used with a percent, it will search for a specified pattern on any string from zero or more characters. It depends on where you place the percentage within the search criteria. The underscore on the other hand is used to match only a single character pattern. So we're going to have a look at how these work. I have got my SQL world bench opened and I have connected to the database. The database I'll be using and schema is the sequela database and schema. So let's click on this sign here. It expands and give you a list of the various tables. So the table we are going to use is the actor table. If I click on this sign, it will show me the columns. But these are the columns here, where it's got columns. If I click on that, it will expand it. So to begin our type in select again, the intelligence has popped up if you suggestion. So I'll click on that because that is what I want to select. And then I want to specify just two columns, the first name column. So to make it appear here, I just double click on it. And it pops in here. Then you separate columns from each other with a comma. The next is the last name I'll double click. So that's all my columns done. I only want to use two columns from this table. The next line is to identify the table that the data is coming from. The name of the table is actor. I just double click on that. And it pops it in there. Next will be the where clause. We need the where clause to act as a filter. So I'll type in where double click where the condition I'm going to use is the first name. So where the first name, which is this one, say for example, I want to search for the first name that begins with the character D. So the way I would do this with a like operator using a wildcard will be to after the first name, I would say type in like, where first name like. And then because I want to search for where the first name begins with a D, that is the pattern I want the search to look for. So you do a quote, followed by the letter D. And then the percent when this is important when the percent is behind the character. That means it will search for any character that begins with that. So by putting the percentage behind the character, you're saying that look for any pattern that starts with this D. So remember, you also have to enclose it in quotes because you are dealing with a string or text. And so for the computer to understand, you need to enclose it in quotes. So that's the that's the search criteria done. So that is it basically. So let's try and beautify the squaring highlight, go edit format, beautify. So the like operator will search using this percent wildcard for any character that begins with a D. So it will search for all the first name that begins with a D. Let's execute that using this. Let's limit the limited to just 10 rows by clicking on this. So it will only return 10 rows. So execute. As you can see, it has, there you go. So if you look at the pattern here, or the first name DDD, he hasn't returned anything else, apart from those with the first name that begins with a D. We can also do the reverse by changing the position of the percent sign, we can make it in front by placing the percent sign in front. That means it will search for any first name that ends with a D, not begin this time that ends with a D. So let's execute that. So take note of the current output. This is begin with. Now I'm going to do a search that ends with to search for the pattern ending with D. So click to execute. You can see it ends with D you go add, add, Richard. So we've changed the pattern. There is another operator you can use. You can use the not like. So if you want to search for a for a pattern that is not within the data, it will search for. So for example, I want to search for first name, not like this, that doesn't contain D. So I'll do a search. Okay, so it's return everything that does not end with a D. See, so if you look through the output, nothing there ends with a D because I've used the not like. So when you use the not like, it will exclude the criteria from the search result. Another thing you can do is use the double percent. Say if you wanted to search for a pattern and you know that the pattern can either be in between the characters, anywhere within between the characters, so it won't be at the beginning and it won't be at the end, maybe somewhere in the middle. Also, you can use the double percent sign to to search that data out. So let's try that. Say if I wanted to search for a pattern, ER. So any name ending in ER, I wanted to search for that pattern. What I would do I put a percent in front. And a percent at the back. So that means it will pick out that pattern, regardless of where it is. So if it's in the front, it will pick it out. If it's towards the end, it will pick it out. If it's in the middle, it will pick it out. So let's give that a go. So it's going to start for that pattern. Click on execute. As you can see, if you look at the ER, there's the gen ER there. There's the ER there. There's an ER there. There's one there. There's one there. There is one here. There's one there. Okay, there's another there. One there, one there. And there as well. So you can use the double percent to search for the criteria on either site as well. Now let's have a look at how you can use the like operator with the underscore, the wildcard. The underscore is a wildcard. So when you use a like with the underscore wildcard, it will only search for a pattern based on one character. Say if I wanted to, for example, look for anyone whose name, for example, ends with that, you can take out the percent. Okay, so this is a criteria here. I'm looking for just one character, which is the underscore. So anyone whose name matches that criteria, so it could be done, it could be done, anything that ends with the end, it will fish out or return that search data. So let's have a look. So let me execute that. As you can see, it's returned, you only found three. So the missing is you can see the pattern here called done done done, because that's the only single character that you can place in here to match the D and the N. So we have learned how to use a like operator with the knot. Also with wildcards, we use the percent wildcard. We also use the underscore wildcard. Thank you so much for your time. I will encourage you to try and practice with the examples I've shown you, change some of the values and play around with it. That is the best way for you to get the hang of things. Thank you so much always. Bye for now. Hello and welcome to this video. The order by clause can be used within an SQL statement to sort the result set by one or more fields. So you can sort the result of a select statement either in ascending order or descending order. The default if you don't specify an order usually is by ascending order. So let's take a look at this simple query here. We are selecting all the columns from the actor table. This is the sakila schema, the schema here and the table I am referring to is this actor table here. So I'm selecting all the columns from the actor table and I'm ordering when you say order by you mean you're sorting the records by the actor ID, which is this value here. So let me execute. I can see here we have sorted the actor ID is used to sort the records. So when you use the order by and you don't specify a descending or ascending, the default is used by default means it will sort from highest or from lowest to highest. Okay, that's the default. If I specified like the same query here, if we wanted to use descending, for example, if I come here and type in descending, which is d d e s c. And then run the query again. You can see descending does from top to bottom. You can see it starts from 200 and works its way down. If you use ascending is from top bottom to top. So this is ascending. This is the default. So if you don't specify ascending, it will sort the records in ascending order, which is from the lowest to the highest. Okay. While descending sorts the other way around from highest to lowest. I have added another query here in this query here. I am sorting by multiple columns. So I'm applying the order by clause on the first name and the last name. So I've got a where clause here and the condition it will sort anyone who has the first name that starts with a n. So let me run this query. You can see here, we have and the when you don't specify the order by it will sort it in ascending order, which is from lowest to highest. If it's alphabetical, it will be from a to z. So you can see here is sorted it out in first name is sorted. See Angela Angela and so on. And then last name also you can see is sorting it from alphabetically. You can see we've got h we've got w and then we've got a. So if you notice in the sorting of the result, the first name is sorted out first. And then the last name that matches that first name follows. So that's the pattern. If you can you can also specify this to sort in a descending order. So if I come here and just say DSC, you can do it in lower case, it doesn't matter. So if I run this query again, you can see the value will be sorted the other way around. So let me run this. You can see the sorting has changed in the in a reverse order. The only difference between the first and the second statement is that the one I use the descending on the last name field, therefore the result will be ordered by the first name column in ascending order, then by the last name in descending order. This basically is in contracts to the first statement, which orders both columns in ascending order. Note the order of the first two records in each of the examples I showed you, although the records were ordered by the first name, there are two first name records with the same value. That is Angela. This is when the last name descending comes into effect. It results in switching the placement of the two records. You can see here the two Angela here. So where the descending comes in, it switches the placement of the two. So if I take this descending out and then run the query without it, you can see here it has this two Angela, they have switched again because of the way it's ordered it by default. The orders in ascending order. So that's it for this video on using the order by clause. I hope it makes sense. If not, please let me know. Thanks for watching. Bye for now. Hello and welcome to this lecture. In this lecture, I will introduce you to the MySQL limit clause. The MySQL limit clause is used to limit the number of rows returned by a result set when used with a select statement. The syntax for using MySQL limit clause is you specify the select keyword and then the column or columns from indicate where the table is coming from. The limit is usually at the end of the select query. So the statement, the limit, what that does, it limits the number of rows that is returned. So you can specify the number of rows you want, which is usually in numerical value. So the values can be zero or positive integers. So anything from zero and above, you can specify. Let's have a look at an example of how the limit clause works. I've already got my SQL workbench open. And this is my query window. The database I'm using is Secila, which is a sample database. And I'm also using the Secila schema. So the table I want to use to illustrate is the film table, this table here. And it consists of several columns. But I'm going to use just one column, which is the title. I'm going to work on the title column and experiment with that. So I encourage you to type along with me. So I begin by typing in the select keyword. The intelligence has helped me there. And then I need to specify the column, which is title, specify the title. Next would be the from keyword. The table is film. Okay, so that's the name of the table. I'm just going to execute this query here without the limit clause, so that you can see the difference when I use the limit clause. So execute by clicking on this icon there. As you can see, it's giving me several titles. Here, I've used a limit of 10 rows. So if I say no limit, it will give me all the rows from all the titles rows from this table here. So this is without using the limit clause. So now I want to specify using the limit clause. So to specify that, I'll take out the semic column there, and then type in limit. And then you specify the number I want the rows to limit by. So I want to limit it to say four. Then specify semicolon. Let me beautify my query, highlight it, go to edit, format, beautify. I always like the query to look neat, makes it easier to read. So now I've used the limit clause there to indicate that I only want the first four rows returned for the title. So let me execute that. So you can see is returned just for because I've specified a limit of four. So let me change that to seven. So that you can see and execute this again. As you can see is now giving me seven rows. So the main purpose of the limit clause is to specify the rows you want returned by the query. If you notice here also in this drop down, the MySQL workbench has also already done that for us. So if you don't want a limit set, you can use that. If you use any of these, for this query, it will not work, because it will ignore that and stick to this, because I've already specified there. So if I specify 10 rows there, and come here and change that to two, it will ignore this and honor this. So let's try that. See that is completely ignored what I've said there, instead is used what I've specified within the query. So the main purpose of the limit clause is to restrict the rows that are returned in a MySQL query. Try and practice play around with a few of the columns in the table and then change the limits and see how you get on. Thank you so much for your time. Take care and bye for now. Hello and welcome. In this video, we are going to be learning about sub query in MySQL. A sub query is basically a query that is nested inside another query. So it is a query within a query. Sub queries are a powerful concept that allows you to use the results of another query inside a where clause. This basically allows you to pass a dynamic value to the where clause as the value will depend on the result of the query. Sub queries are normally enclosed in parentheses and they can also sometimes be referred to as an inner query or inner select. So I've added a script here to illustrate how sub query works. So basically in this example here, the sub query is enclosed in parentheses. Okay. So what I'm doing here, I'm trying to retrieve the details of all the actors that appeared in the film with a film ID of two. So we can do this because the film underscore actor table contains an actor underscore ID and also a film underscore ID column. So here I'm this script here is going to be run against the circular database, which is a sample database for MySQL. So this here I'm selecting star, which means all the columns from this table called actor. If I expand, you can see all the column. So it has an actor underscore ID, first underscore name, last underscore name, and last underscore update. That's for the actor ID. So if I go to the film underscore actor column table, sorry, so we've got a table called film underscore actor, which is this table. If I expand that expand the column, you can see we've also got an actor underscore ID, a film underscore ID, and the last underscore update. So what this query will do is to retrieve the details of all the actors that appeared in the film with a film ID of two. That's the condition. Okay. And we're able to achieve this because the film underscore actor table contains both the actor underscore ID and the film ID. So I've already showed you see that's the actor ID. And that's the film ID from the film underscore actor table. So let me execute this query by clicking on this. And you can see here, that is the result of the query here. I'm just going to limit it to 10 rows. So that we have less from the output Okay, so I'm just going to collapse this so that we can see the output. So there you go. So this is the result. I'll bring that up a bit so you can see from executing this sub query. So that's it for this video. In this video, I introduce you to the concept of sub query. A sub query is basically a query that is nested inside another query. So it's a query within a query. So that's it for this video. Thank you for watching and bye for now. Hello and welcome. In this video, I'll be showing you an example of a nested sub query nested sub query basically is a nesting of a sub query within another sub query. So you're nesting the sub query inside another sub query. So let's show you an example. Before I show you an example of a nested sub query, I want us to take a look at this sub query first. So this is a basic sub query. And in this example here, I'm using the film underscore ID. This is because the film underscore actor table does not contain the film title. So if we take a look at the film underscore actor table, there's an actor underscore ID, a film underscore ID, but there is no film title. Okay, so what we could do, okay, we could use the film title instead of having to remember the film ID for every film we need to look up. So we can use a sub query that is nested inside another sub query to achieve this. So I'm going to refactor this sub query to a nested sub query that will enable us achieve the title of the film. So this is the query I refactored here. So we have a sub query inside another sub query. So in this example here, I'm using a nested sub query to retrieve the film underscore ID from the film table, where the title equals to ace gold finger. This happens to be the same film as in the previous example that had a film ID of two. So therefore, we are getting the same list of actors as the previous example in the earlier script I showed you. So I'm just going to execute this. You can see here, we're getting exactly the same thing. But we've got the actor ID, the first name, last name, and last underscore opt. So now all we need to do is think of a film title, and we can retrieve its actors. No need to try to remember the film ID. We can use this script to achieve that objective. In this video, I showed you an example of using a nested sub query, which is a query nested inside another sub query. Thank you for watching. Bye for now. Hello and welcome to this video. The char underscore length function is used to return the number of characters in the argument that is passed to the function. The char underscore length and character underscore length function, they both do the same thing. So this is a query here in my editor, I'm selecting the title. Okay, and I'm applying a char underscore length function to this title. So basically what this is doing is going to count the characters in each title. This too will produce the same result because char underscore length or character underscore length, they both do the same thing. So I'm doing that from the film table. So if I, we've got a table here called film. And if I expand the column, you can see here, we've got a column for title. That's what I'm applying this function to. So let me execute this and you can see what it does. There you go. You can see here, it tells you gives the character length of the title. So both the char underscore length function and the character underscore length function, they both do the same thing. You can see they're returning the character length of the title. So that's it for this quick video just to show you how the function called the character length or char rent is used. So basically use that to find the character of a given column or data. Okay. Thanks for watching. Bye for now. Hello and welcome to this video. The concat function allows you to concatenate or join two or more parameters by concat. Basically means you are joining two or more parameters. Concat function returns a string that results from the concatenating parameters. So the parameters themselves don't necessarily need to be strings. For example, if the parameter is numeric, it will be converted into a string as part of the concatenation. All right. So just know that when you are concatting, it will normally return a string. So concatenation is best used with a string, but the parameters don't necessarily have to be strings. When I refer to the word string, I mean text. So here I've got a query that I've already staged. So I'm selecting the first name, the last name from the table called actor. I've got a table here called actor. And these are the columns I'm selecting first name, last name from that table. And I'm doing a concat function on the first underscore name. And the last name notice I've got this single quotes here. This means it will create a space after the concatenation has occurred. What this will do is we join the first name and the last name together and then create a space using this single quotes. So let me execute so you can see what it looks like. So you can see here, we've got the first name, we've got the last name and then we've got a concat operation where it joins the first name and the last name together. We can see there's a space between them. That's achieved because of this single quote here that creates the space. Without the space, the concatenation parameters will be joined together and you won't be able to tell. So the space is important. So you can see the concatenation properly. So that's basically how you use the concat function. Thanks for watching. Bye for now. Hello and welcome to this lecture. In this lecture, you will learn how to use the MySQL union operator. The MySQL union operator is used to combine two or more result sets from multiple select statements into a single result set. There are a few rules that you must follow when using the union operator. When using the union operator, the participating tables from the select statements must have equal number of columns. So if you've got select statements A has got three columns, select statement B must also have three columns. So they must be equal. They must also have matching or convertible data type. For example, you can't have a number in column in select statement A corresponding to a string or date data type in select statement two. So data types must match or they must be convertible. With the union operator by default, it removes duplicates values. Any duplicates will be removed. So when you specify a union operator, you can use it with a distinct. If you use a distinct, it will remove all the duplicates. So when you're using it without a distinct is still the same by default, it eliminates duplicates. If you use the union operator with the all, it will include all duplicates. When you're using the union all, it is a lot faster than when you use the union distinct. So let's have a look at an example how this works. I have got my SQL workbench opened and connected to the database. The database and schema I will be using for this example is the secular database and schema. So let me expand this and the I'm going to use two tables. I'm going to use the actor table and the customer table. So I must make sure that the columns I select must match. When writing or using the union operator, the select statements from both tables should be enclosed inside parentheses to separate them. So I'll start with the first statement. So I do my parentheses. That's the opening one. I'll close it later and then do a select statement. And I'll select two columns from this actor table, the actor ID to a comma. So you always have to separate each column by a comma. The next column I'm going to use is the last name. And then the table, this is the from and the name of the table is actor. Now I can close my parentheses. So that's the first select statement. Then I attach my union operator, a typing union. And then I begin my second select statement with a parenthesis and then a select. This time I'm going to use the customer table. So I'll select the columns. I want the columns has to be two because I specify two and the data types have to match. So select customer ID command. So this customer ID has the same data type as the actor ID. Next I'll select the first name. So double click on that. Next is the table. Name of the table is customer. Then I can close my parentheses. So I've got both my parentheses closed. So this is for the first select statement. I've got a union and then this is the second select statement. Another thing that is useful when using a union is to do the order by the order by is usually used with the last select statement. So I type in the order by and with my sequel you could order by or number. So I could order by column the position of the column in the so that's two comma one. So what this do it will sort the result based on the position of the column in the query. So let me just beautify the query a little bit and go edit format beautify. So it's a bit more spacious now. So that's the first select statement. I've got two columns actor ID last name from this table called actor and then this is the union operator. This is the second select statement customer ID first name to the data types match from customer and then I'm ordering by the position of the column. So if I execute this now and go execute and this is the output. So this is a result of using the union operator which I've used to combine the results from two queries into one query result set. In this lecture we use the my sequel union operator to combine results from multiple tables into a single result set. Try and practice with some of the with the examples I've shown you and see if you can change some of the values to get the hang of things. Thank you so much for your time. Bye for now. Hello and welcome. In this video I'm going to introduce you to aggregate functions. What are aggregate functions aggregate functions are functions that enables you to perform calculations on multiple rows within a single column of a table and returns a single value as the result. So when you use aggregate function it will only result in a single value depending on the type of aggregate function you execute. Aggregate functions allow us to easily produce a summarized data from our data. So depending on your type of business or the type of business requirements you can use different types of aggregate functions to achieve the result you are trying to obtain. MySQL supports the following types of aggregate functions. We have the count, the sum, the evg which stands for average and then we've got the mean and the max. So these are the aggregate functions supported by MySQL. So depending on the type of aggregate function you use it will always give you a single result value. So that's it for this introduction. I'll be covering each of these aggregate functions in separate videos and also provide an example of each. Thanks for watching and bye for now. Hello and welcome to this video. In this video I'll be introducing you to the count aggregate function. The count function basically is used to return the total number of values in the specified field or column and it works on both numeric and non-numeric data. By default all aggregate functions will exclude values that are null before working on the data. A null value basically is a value that has not been represented. That means nothing has been accounted for. There's no value to show for that particular field or row. So anything that is null mean is an unknown value. I have got my SQL workbench opened and these are my schemers and the schemers as highlighted in bold is the schema I'm going to be illustrating an example from. So if I expand this Sakela schema and if we take a look these are all the tables within the schema and the table I want to use for this particular illustration of the count aggregate function is this table here called film. If I expand that and expand the columns you can see all the columns within this table called film. So this is where I'm going to execute my query. So what I want to do first I want to implement a count function that will count all the records from all the columns or fields in this particular table. So to do that I'll do a select okay and then after the select I do the aggregate function which is count and when you use a function inside the parentheses you specify either the column or field you're trying to perform the aggregate function on or you can use a special implementation of count using the asterisks. When you use the asterisks what that will do it will return the counts of all the rows in a specified table and when you're using it with this asterisks it will also consider the nodes and the duplicates okay. So I'm doing that and I'm specifying from the name of the table is called film and I'll terminate that with a semicolon. I'm just going to highlight my query and beautify it by clicking on this symbol here this icon and that will space it out make it look nicer. So now I can execute the query remember the aggregate function will only return a single value so don't expect a lot of rows return just a single value. So I'll execute and you can see here that is the record here let me just expand bring that down a bit this is the output area you can drag it down okay and you can see here it is you can also drag it down here that is giving me a count of a thousand so it's telling me that I've got 1000 rows in this table called film so because I've specified the asterisks it returned the count from every field and all the records so that's what I've got here notice it's returned just a single value. Also when you're using the count you can use a filter you can narrow down the outcome or the final result for example if I wanted to find out I've got a field here or column called rental underscore duration and I've also got one called replacement underscore cost if I for example I want to find out what a specific replacement cost is for say for example if I want to find out the film ID from where the replacement cost is greater than 27 I can do a count statement for that and the way I would do this I can say select count and rather than use the asterisks I will specify a column which is film underscore ID if you look here I've got a film underscore ID so I'm selecting rather than all the column I'm picking a specific column okay and I'm selecting film ID from film I'm going to remove the semicolon because the statement has not ended and I'm going to say where replacement underscore cost is greater than 27 okay I'm just going to highlight this I'm beautify it again all right so this is a new query I'm picking a specific column now so if I execute this you can see there it's giving me a count of 47 so I have 147 film ID with a replacement cost greater than 27 anytime you execute a query in this output area here it also gives you kind of like a snippet of the query you have executed okay and then the result is usually in the middle you can also use a distinct keyword with your aggregate functions to remove duplicate values there are times you may have duplicate values so let me illustrate how that works so I'm going to write a statement here I'm going to do select I'm going to use a column here there is one here called rental underscore durations I'm going to say select rental underscore duration from film film is the name of the table so I'm just going to highlight and just click on this beautify icon so I'm selecting a column rental underscore duration from film so if I execute this you will see some duplicate values here so if I bring that bring this down a bit so you can see and you can see here I've got six I've got six again here that is duplicate I've got six again six again so you can see here is returning the duplicate values in the result now if I want to avoid that I can add a keyword to that which is called distinct so I can do select and then add a distinct keyword okay to that and if I run that it should remove all the duplicate queries so if you see now there is no more duplicate all these are all single values all right so we just wanted to highlight the fact that you can use distinct with your queries to get rid of any duplicates records or rows so that's it for this video on using the count aggregate function thank you for watching and bye for now hello and welcome in this video I will be introducing you to the sum aggregate function the sum aggregate function basically is used to return the sum of all the values in the specified column the sum function only works on numeric fields so any field where you have numeric data you can use the sum aggregate function and when you're using the sum aggregate function null values are excluded from the result if you want to find out for example the total amount of something the sum aggregate function is a good function to use to achieve that I'm going to show you an example of a sum aggregate function I'm using the sakila schema and also using the table called film from the sakila schema if I click on the film table these are all the columns in the table called film so what I want to do basically here I want to find out how much it will cost to for example to replace all the films we have in our database table so I can use a sum aggregate function to determine the results let's just assume all the films in the database in that table got destroyed or something or they disappeared and we wanted to know the replacement cost so to do that we need to use this sum aggregate function so we do a select do a space followed by the name of the function do a sum and then with functions you always have to enclose them in parentheses and inside that parentheses you specify the name of the column that you want the function to act on so it'll be the replacement underscore cost which is this column here highlighted here so I'm selecting this column called replacement cost and I'm going to apply the aggregate function called sum on it and it's going to be from a table called film and I'll terminate that with a semicolon I'm just going to highlight this to beautify it so that it spaces out the statement makes it look more readable so that's basically the query I'm going to execute that and I'm just going to bring the result up a bit so that you can see so you can see the result here so it's giving me this value so this is what it will cost to replace the catalog or films that I have in that database so you can see the sum aggregate function can be a useful function to use depending on the type of result you want from the database table so basically what the function has done here basically is that it gets all the cost of replacing each individual film and then it adds them together to give you this final result so that's it for this video on the sum aggregate function thank you for watching bye for now hello and welcome to this video I will be introducing you to the mean aggregate function the mean aggregate function is basically used to return the smallest value in the specified table column or field so if you have a range of values in a specified column and you wanted to find out the smallest value in that range then the mean aggregate function is ideal for that purpose I have got my SQL workbench opened I'm going to be using the sakila schema and the table I'm going to be using is a table called film and these are the columns within that table so what I want to do in this table in this column here called replacement cost I want to find out what the minimum replacement cost will be for each film so this is how I would write the aggregate function so I'll do a select space followed by the name of the aggregate function which is mean and inside the parentheses I specify the column I want to apply the aggregate function to and it's called replacement underscore cost and I'll specify the table is coming from the name of the table is called film so I'm just going to highlight and beautify this to space the statement out so that's basically it I'm just clicking to execute and it tells me here that the minimum replacement cost value is 9.99 so that's it for this video on using the mean aggregate function thank you and bye for now hello and welcome to this video I will introduce you to the max aggregate function the max aggregate function is basically the opposite of the main aggregate function the max function returns the largest value from the specified table column or field so whereby you have a range of records in a specified column you can use the max aggregate function to find the largest value within that range I have logged into my sequel workbench and I'll be using the sakila schema for this video and the table I will be using is a table called film and these are all the columns within that table so let's say I've got this column here called replacement cost let's say I wanted to find out the maximum value to replace a film the maximum replacement cost for a film so this is the way I would write the query I will do a select followed by the name of the function which is max inside the parentheses I specify the name of the column that I want to apply the aggregate function to so the name of the column is called replacement underscore cost which is this column here and then I specify the table using the from keyword I'll say from film and I'll terminate that with a semicolon I'm going to highlight and click on this beautify icon to space the statement out so that it makes it more readable so I'm just going to execute this by clicking on this and it tells me that the maximum replacement cost is 29.99 so the max aggregate function is a useful function to find out the largest value within a range of columns so within a range of rows or records inside a specified column that's it for this video thank you for watching bye for now hello and welcome in this video I will introduce you to the AVG aggregate function the AVG function is used to return the average of the values in a specified column just like the sum aggregate function it works on numeric data types only also it excludes null values from the result so I have got my SQL workbench open and I'm going to illustrate with an example of using the AVG aggregate function the schema I'm going to use is the highlighted schema which is a kilo any schema that's highlighted is usually the schema that the query will execute against unless you specify inside the query window what schema you want so to begin I'm just going to write a simple select statement so you do select space and then you type the name of the aggregate function so this will be AVG and inside the parenthesis you specify the name of the column you want to apply the aggregate function to the column I'm going to use is a column called replacement underscore cost all right so if I click to expand the schema and the table I am working on is this table here called films and the column I am applying the AVG function to is this column called replacement cost this column here all right so I'm applying the AVG to the column called replacement cost and I need to specify the table it is coming from it's coming from a table called film and I'll end that with a semicolon I'm just going to highlight this to beautify it by clicking on this icon here it spaces it out makes it look more readable so I'm just going to execute the query I click that and you can see the result here this is the result it tells me tells me here that the average replacement cost is nineteen point nine eight all right and here we have a snippet of the query inside the output area so that's basically how you use the AVG aggregate function it's basically used to return an average so if you want to find out an average within the records in a column then you use the AVG function so that's it thank you for watching bye for now hello and welcome to this video the group by clause is used to group the returned record set by one or more columns and when you do that you can specify which columns the result set is grouped by let's run the query I have here inside the editor to take a look at the result set so notice here you can when you run a query you can select if you want the entire records to appear or you can limit for now I'm just limiting it to 10 rows so I'm going to run this by clicking on that and that is the result set so I'm just going to drag this up a bit so you can see most of it so you can see here in this result here that the last name column contains a lot of duplicates you can see allen allen allen allen acroid acroid acroid so what that means is that many actors share the same last name now if we add a group by using the last name column we should get a different output so let me add a group by clause to this query so I'm just going to tap and come down here and I'm going to add a group by so I'm going to say group by and I'm going to use the last name column okay so I'm going to run this query again by clicking on this now you can see there is no more duplicates when I've used the group by okay and that is because it is represented only once in the result set so whereby you have several actors with the same name when you group them it will only show one instance so it groups everything together as one you can also use the count aggregate function with a group by clause so I'm going to modify the query to include the count so I'm going to do select last name I'm going to do a comma here and I'm going to add the count aggregate function and you have to pass in a value for the aggregate function I'm going to I want to return all the columns so I just put an asterisk asterisk means it will return it will perform this aggregate function on all the columns since I've not specified any this asterisk represents all the column so I'm selecting the last name doing an aggregate count on all the columns from the actor table which is this table that belongs to the saquilla schema which is the schema here and I'm going to group by the last name so okay so I execute that and you can see here the output so it says our crowd I've got three people called our crowd Allen I've got three Bailey I've got two and so on so the group by clause is quite useful if you want to separate or group setting related data from a result set so that's it for this video thank you for watching bye for now hello and welcome to this video the having clause can be used as a filter on the group by clause so it is used basically to apply a filter to a group of rows or aggregates this basically is in contrast to the where clause which is applied before the group by clause let's take a look at the query I have in the query editor here in the query here I'm using the having clause to filter the result set to only those records that have a count of greater than three okay this sign here is a greater than sign so I'm selecting the last name and I'm applying the aggregate function count on all the columns and then it is getting this data from the actor table which is this table that belongs to the saquilla table I'm using a group by you always use the having after you've used the group by because it works on the group by so I'm using the group by to group by the last name and then I'm applying the having count having clause and applying another aggregate function so I'm using the aggregate function twice here on the last name and then here I'm applying it on the result of the group by where this condition is true so it will only to filter the result that is returned to only those records that have a count greater than three so let me execute this so you can see so this is basically how the having clause works it works on the group by clause if we did not have the having clause it would have returned all the records regardless of their count so if I run the query again without the group by you will see what I mean so if I take out the group by I'm just going to comment it out that comment it out and I'm going to add the semicolon there now if I run this query again you can see here it returns you know all the records regardless of their counts okay but having the having clause with it puts it in a more structured way let's take a look at this more advanced query I have added on here it's a slightly more advanced example and basically it uses an inner join to retrieve data from two tables as well as a sum aggregate function which is used to add up the totals that each customer has paid and you can see in the query here after the join condition I have used the having clause the having clause here to return only those records where the total amount returned by the sum aggregate function exceeds that is greater than 180 and then I'm also sorting the amount using the I'm using the order by to sort the amount in a descending order when you see dsc it means sort the result set in a descending order using the order by clause so by sorting it this way customers who spend the most is listed at the top all right so it's a very straightforward query it looks more complicated than it is we're basically selecting all these columns from we're selecting the customer underscore id we're using that alias here we see as that means alias so given it an alias customer id this is alias first name alias last name total and we apply some aggregate function here as well from circular this is a schema and the column that we working on is you can see here we've got the customer id so if I collapse this column here we have different columns so let X this is the customer table here so if I expand that expand the column you can see customer id is coming from the customer column and we've also got the first name we've got the last name okay and we are performing a join on the customer id okay and these are the joint conditions that we're using the customer underscore id from one column and we're joining that to the customer underscore id of the same column when you are joining data from the same column you have to use an alias to differentiate them so we're using p dot customer underscore id and c dot customer underscore id to be able to perform the join so let me execute the query by clicking on this and you can see this is the result set so you can see here that the customers who spent the most is listed at the top so that's it for this video on using the having clause thanks for watching bye for now hello and welcome in this video I will show you an example of a MySQL inner join the MySQL inner join is used to return data from multiple tables the participating tables involved in the inner join must have at least one matching column among them the inner join is used when you are interested in returning the records where there is at least one row in both tables that matches the join condition let's take a look at the illustration of two tables I have displayed on the screen as you can see from the illustration you can see that there are two columns that are matching within the two tables so if we take a look at the country table we have the country underscore id which is the primary key in this table but if we take a look at the city table we also have the country underscore id in this case it's the foreign key in the city table if we wanted to select data from the two highlighted fields that is the country and the city we could run a query that looks like this so this is the typical query we would run in order to select data from the country and the city column in the query we are using an inner join and the inner join basically will be used to display a list of cities alongside the country it belongs to so looking at the table you can see that the city info is in a different table to the country info therefore we join the two tables using the country underscore id field so both tables have an identical column called country underscore id so being the common field or column in both tables that is represented in the join condition so inside the inner join we are joining the country table and these are the condition we're joining the country id from the city table to the country id in the country table so let's go to our MySQL workbench and try and see how this translates into a real query so this is my SQL workbench and the schema we're working on is the circular schema which is the sample schema if I open that look at the table here we can see we've got a city table and you can see these are the columns in the city table we've got city underscore id city and then country underscore id and then last underscore update so if I collapse that and open the country table we can see we've got the country id which is identical because we've also got the country id inside the city you see here we've got country id in city and then we've got country id here inside the country table so the country id in the country table is the primary key in the country table while the country underscore id in the city table will be the foreign key so I'm going to just paste the query that I've already staged so this is a query make sure when you're running a query you're running it against the right database you can see this circular is highlighted in bold which means this query here will be executed against the database so this is the query here I'm selecting the city and country column two columns city country from the city table and I'm performing an inner join on the country table this is the on the on basically it's kind of like a glue that joins the data together so this is the join condition we are joining the country underscore id column from the city table to the country underscore id column in the country table so if I execute this query now you can see here now we're able to represent two different type of data in the same output so we've got the city here and then the country tells you city Kabul this country afghanistan and so on okay I've only limited my query output to 10 rows but you can obviously choose whatever rows you want returned if you don't want to limit you just click don't limit and it will return all the records from the table or from this query when the query is executed if you notice in the query we in the qualifying fields here that we have qualified some of the field names with their respective table names so here you see I'm saying country underscore id from city the table is city and then we've got country underscore id and the table name is country if we don't represent it like that because the identical column you will get an error from mysql server when you are qualifying your column names you can use table aliases to shorten the query and save typing so we can modify this query here to include alias so I do select city and then country and where I've got city here I can do a space and type in a so the a now will now become an alias so when I'm doing inner join okay so I'll do country and then I'll specify another alias which would be b and then here rather than say city country and under rather than say city dot country id I can use the alias so I get rid of that and I type in a which is the alias I created here for city and then for here country I can get rid of that and use the b which is the alias I created here so aliases can help make your queries a lot shorter and easier to read so once you define the alias you can use it in the join so that's just another way you can restructure your join query another thing you can do with inner joins you can use a group by clause and also you can use aggregate functions so let's say we wanted to find out how many cities there are in each country we can restructure this query to do that by adding an aggregate function which is a count function and then we can use a group by clause which will enable us to group the city and then tell us how many cities there are in a particular country okay let's restructure this query again so I'm going to select the city the country no actually I'm going to get rid of city because I'm trying to find out how many cities in a country so I want to select just the country and then I do a comma and then I'll add the count aggregate function inside that function I'm going to pass it the city so it's going to count how many cities there are in each country so again I use this alias here from rather than from city I'm going to change that to country because we're trying to find out how many cities in each country so from country and this the alias here I'm going to do an inner join on city so I'm going to change that to city and yep so that'll be the inner join and I'm going to extend the query a little so I'm going to actually leave this here so you're going to be a dot country underscore id it's going to be equals to b dot country underscore id and then what I'm going to do I'm going to add a group by clause I'm going to say group by I'm going to group the result by country so let me execute this query so you can see so you can see what I mean here so if I just expand this area here this area here is basically collapsible so you can drag it so you can see here what I mean it tells you Afghanistan there's one city Algeria there's three and so on this country there's 13 Austria there's three as this place there's two I've only limited my query to 10 so if I for example made it 50 I run this query again you can see I've got a lot more here they've got here see this China here we've got 53 based on the records we have in the sakila schema so this is basically a very useful query that you can use to get specific types of information relating to data from multiple tables so that's it for this video on the mysql inner join thanks for watching bye for now hello and welcome in this video I'll be showing you an example of a mysql left join the left join is used to return data from multiple tables it is called the left join because all the rows from the left table are returned even if there is no matching row in the right table if there's no matching row in the right table this could result in null values that will appear in any columns returned from the right table let's take a look at the table illustration I've got on the screen let's assume we want to return a list of all the customers from the customer table and if the customer shares the same last name with an actor from the actor table we want to be able to display that actor's details also the important thing here is that we would like to display all the customers regardless of whether they share their last name with an actor or not so therefore if a customer doesn't share the same last name as an actor the customer is still listed to achieve that we'll need to write a query like this so we'll do a select using aliases here so we've got c.customer ID c.firstname c.name a.actor the c there stands for customer and then the a is an alias for actor and then we are getting the data from the customer table and we're doing a left join on the actor table and the fields we're joining we're joining the last name from the customer table to the last name of the actor table and then we're going to order the result using the order by class i'm going to order it by the last name so i have got the query listed inside the editor in the myc core workbench so this is a query here and the table we are also using the sakila schema here and we are referencing the customer table which is this table here if i expand you can see the columns here all right and you can see here we've got a last name which is where we are doing the join so we are doing the left join on the actor last name and the last name of the customer so that's the customer and if i click on the actor here you can see inside the columns here we've got a last name column also so we're joining by their last name so let me execute this query just by clicking on this so the query is pretty straightforward we're using the select statement and we're selecting all these columns from the table customer id from customer table first name from customer table last underscore name from customer table and then we've got these three columns from the actor table actor underscore id first underscore name last underscore name and then we're getting the data from the customer table this here is an alias for the customer table and you can see here i've also set aliases for the column names you always set the alias for a table name in the from clause and the alias for the tables name are specified in the select statement as i've done here so we've got the left join again we've got the actor here with an alias a so we're joining on this on here is kind of like a glue that joins the data together and then we're using the order by to filter the data that is returned by the query in the last name order so let me execute this query by clicking on this and then you can see the result in the output so if you notice here we have a lot of null values here and because there is no matching data on the from the right table you can see here null null okay or coming from the right table because there is no matching so we can see we've got a few matches in some but some no matches at all that's why you've got a lot of null values that means null basically it means there is no value represented for that data so you can see here that all the customers are listed even if they don't share a last name with an actor if they don't share the last name with an actor the actor fields are represented by these null you can see here the null values so that's it for this video on the my sequel left join thanks for watching bye for now hello and welcome in this video i'll be showing you an example of a my sequel right join the right join is used to return data from multiple tables in particular the right part basically means that all the rules from the right table will be returned even if there's no matching rule in the left table this could also return null values appearing in any columns returned from the left table let's take a look at this two table illustration so let's assume we want to return a list of all the actors from the actor table and if the actor shares the same last name with a customer from the customer table so we can also display the customer details or information also the important thing here is that we want to display all the actors regardless of whether they share their last name with a customer or not what that means is that if an actor doesn't share the same last name as a customer the actor is still listed we could achieve this with this type of query listed here so we have a select statement where we select the fields from the respective tables and then we have the from field where we get the data from the customer table and then we do a left join on the actor table which is the right table and what we're joining here is the last name from the customer table and the last name from the actors table and we're going to order the data by the last name so I've got the query here inside my SQL workbench so again here we're using the sakila schema and we're using the actor table and also the customers table so this is the actors table this one here and then we have the customer table you can see that we've got the last underscore name here in the customer table and if I expand the actor table we've also got the last underscore name all right so let me execute this and we can see here in the result if I just drag down down a bit you can see we are getting a lot of results here you can see on the left here we're getting a lot of note values this is where there are no matching last name that matches the actors last name okay so that's represented by null values so you can see here that all the actors are listed even if they don't share a last name with the customer if they don't share the last name with the customer the customer fields are represented by null values all these null values instances where it doesn't have a last name that matches the last name of an actor so that's it for this video on the mysql right join thanks for watching bye for now hello and welcome in this video I will be showing you an example of a mysql self join the self join is used to join a table to itself when you're using a join you can join the data from the same table to itself so a self join basically is useful for when you want to combine the records in a table with other records in the same table that matches a setting join condition so let's take a look at this illustration of this table called customer so for example we can join data from this table to itself so we can for example join the last name and the first name from the same table so let's see how we represent that you can represent that with an inner join like this where you select the various fields from the same table notice here because I'm using data from the same table I'm using aliases so I've got the aliases a which represents customer underscore id then first name last name and then I've got an alias b with the same number of fields customer id first underscore name last underscore name so we're basically joining data from the same table to itself okay and we're using an inner join so you can also represent this with a right join and also with a left join so I've got the query inside my editor in my sequel workbench so this is the table we are targeting here the customer table and these are all the columns from the customer table so we've got a customer id first name last name and so on so here I'm using aliases because I'm joining data from the same table as I'm using two sets of aliases so I'm saying the customer underscore id from alias a first underscore name last underscore name and then the second when you're performing a join obviously you have to join the data with certain condition so if they match the condition then the join will take place because I'm joining data from the same table as I'm using these aliases to make it easy so again I'm saying from customer a which is from this customer table with the alias a I'm getting these fields and then I'm doing an inner join with customer b and using the on this on here clause is basically what glues the data together so I'm saying last underscore name from take customer table a and I'm joining that to first underscore name of customer table b so if I execute this query with the inner join you can see here we've got the first name and last name okay and then we've got all the fields that we specified here and we've got the customer id here from the customer table b we've got the first name last name and then we've got the customer id here as well so with the output is represented in the order in which the columns or the fields are listed in the query so that is the full query okay and this is the join and we're joining the first name and the last name together so we have done this using an inner join I'm going to show you another query that we can use to do the same to achieve the same mouse gone but this time using a right join so in the above script here we were able to retrieve all the customers whose last name matches the first name of another customer and we were able to achieve this by assigning aliases to the customer table while performing an inner join on the two aliases the aliases basically allow us to join the table to itself because they give the table two unique names which means that we can query the table as though it wears two different tables the aliases allow us to achieve that self joins are not limited to inner joins you can also use a left join to provide all the records from the left table regardless of whether there is a match on the right table so the previous query I did and this is identical again we're using aliases to make the make it look like we have two separate tables so that we can perform the join you can see here I have used the order by clause to order the output by the customer underscore ID so let me execute this query and then you can see here again customer ID first name last name we've got a null value where there's no match again another null anyway you see null that means there's no match you can also use a right join to provide all the records from the right table regardless of whether there is a match on the left table note that in this query on the right join I have changed the order by clause to use the b dot first name this is purely for demonstration purposes by doing this I was able to get I'll be able to get some matches near at the top of the results and to make it the illustration more meaningful so let me execute this query and then you can see here you say you go you can see some null values right at the top here okay just by changing the way I filtered the result in the output so that's it for this video on using the mysql self