Why do we need databases? We are living in the age of data. If you see around, everyone has a mobile phone or multiple devices connected to the internet. People share their pictures, talk to family and friends, purchase things online, and engage on social media. Whether it's ordering food or finding a partner on dating sites, the web has become the means of communication. This has led to an explosion of data on the web, from images to tweets to banking transactions and orders. Now the question really is, where does this data go? How is it stored, and how is it retrieved instantaneously when we visit a website? All these tasks are done quietly by databases in the backend.

The reason we need databases is because of the four operations we need to do on data efficiently, which are store, update, delete, and search. We really need fast search, and as we will see later in this course, this is one of the main reasons for using specialized databases. Also, from a data science perspective, we need data to do any meaningful data science, so we should know how to retrieve this data, manipulate it, and save the process data. In the next video, we will try to understand exactly what is the database. So what exactly is a database? A quick Wikipedia search reveals this: a database is an organized collection of data, generally stored and accessed electronically from a computer system. Where databases are more complex, they are often developed using formal design and modeling techniques. Note the important concepts here: it's a collection of data; it's organized, meaning we can query it efficiently; and it's stored and accessed electronically. In the next video, we'll look at some properties to expect from a good database.

Now let's look at some properties we would require from a good database. First is scalability. Imagine a scenario where, let's say, you come up with a business idea of writing a blog for food recipes. Now initially, there will be a few visitors, maybe 5 to 10 daily, but as you grow, your traffic can potentially reach millions of users a day. Each of these visitors will query your database for loading pages, but your database is the same. You want to handle more and more queries by adding some resources like servers. This property of your database, which allows it to do a growing amount of work by adding resources, is called scalability. In fact, the major portion of time and resources in developing any website goes into making it scalable.

Next property is elasticity. Now let's suppose you figure out that your blog for food recipes has five times more visitors on weekends than on weekdays, since most of your users are working class and they like to explore things in their free time on weekends. Let's say you need 20 servers to handle the weekend traffic, but on weekdays you just need four. You need some way where you can increase or decrease the capacity of your database in an on-demand manner, so your servers match the current traffic as much as possible. This property is called elasticity. With the advent of cloud computing and different providers like AWS, it has become very easy to increase or decrease capacity on demand.

Next property is security and governance. This goes without saying that you need to secure your data and your systems from external threads. Also, all the data resides in the database; it becomes quintessential that the database you use has the security features to protect the data. Also, governance and effective access control of data within the organization are a must.

And the final property is data integrity. Data integrity is basically the assurance of accuracy and consistency of data and is a critical aspect of any system design. There are two classes of databases used in industry: one is relational databases, and the other is NoSQL databases. Relational databases generally have tables in which data is stored and have a fixed number of columns, much like Excel. NoSQL databases, on the other hand, have JSON documents stored in collections where you don't need to follow any schema.

Now let's look at some of the examples of relational and NoSQL databases. So first one is MySQL. MySQL is the most widely used database, followed by Microsoft SQL Server and PostgreSQL. Following the acquisition of MySQL by Oracle, the original developers wanted the software to be free and open source, and so forth did to create MariaDB. MariaDB generally is very compatible with MySQL. SQLite is a file-based database which stores data in a flat file and hence is easy for setup and quick testing and development. Then there is Oracle. Examples of NoSQL databases include MongoDB, Redis, Cassandra, CouchDB, Neo4j, and Apache HBase.

Now let's look at how data is stored in relational databases. Let's say you are developing a job portal and trying to store the data of skills of users. So you'll have a database, user data, and in relational databases you can store data in tables. So you will have tables. You decide to have one table called user details, which contains the data of users like user ID, name, and skills. Then you insert rows into it, or your data into it. So you have the first row which is user ID 1, name is Anand, and skills are machine learning and Python. Then it is the user ID 2, name is Dimple, and skills are marketing and Google Analytics. Note that this looks very similar to Excel, and you can have multiple such tables in the database. Now these tables can be linked to each other via relationships, which we will see in the later part of this course.

We have already seen that relational databases store data in the form of tables, but that is only half of the story. What makes relational databases so useful for multiple modern-day applications is that they are guided by the ACID properties. But before we try to understand what those are, let's first understand about transactions. Any operation on the data in a table in a relational database is called a transaction. For example, if you want to read a record from RDBMS, then that would be a single transaction. Similarly, any other changes that you have made to the table is also called a transaction. Now these transactions maintain ACID properties of RDBMS. ACID stands for atomicity, which means that each transaction executes completely or not at all; consistency, which means that data in the database remains in a consistent state before and after a transaction; isolation, which means that transactions run independent of each other; and finally, durability, which means that any modifications that are made by a transaction persist in the database even in the case of a system failure.

Let's try to understand these with an example. Imagine that we have a flight ticket booking website that allows you to book tickets to your choice of destination, and the website maintains all these bookings and related information on a relational database. Now let's say a person sitting in Brazil wants to book a ticket to Russia on a flight that currently has 200 unreserved seats. Now, in order to do that successfully, they have to go through three steps: the first is to block their seat; then they have to make the payment to the seat; and finally, await for the confirmation message. It is only after all of these steps are successfully performed that the flight seat is booked successfully and the exchange is updated in the database. If, however, there is a system failure or a network failure during any of these steps, then the changes are reverted to the original state, and the user would have to try to book their seat one more time. Therefore, this ability of the relational database to ensure that either each transaction executes completely or not at all is called atomicity.

Now, during the working process, it is to be ensured that the integrity of data is maintained. For example, if the number of unreserved seats go down by one, then the number of reserved seats should also go up by one. Therefore, this ability of the relational database to keep the data in a consistent state before and after any modification made by the transaction is called consistency and prevents the database from entering any incorrect state. Now, at any point of time, there are going to be multiple transactions occurring on the platform. For example, there are going to be multiple users trying to book a ticket on the same flight and will have a separate user ID, and the changes made by the database transaction will only be made against their ID in the database, and so none of the transactions will ever interfere with other transactions making changes in the database. For example, if a user makes a payment for the booking, then the transaction will only change the value against their user ID in the database and not against any other user ID, and so each transaction can occur independently without interfering with any other transactions. This allows the relational database to concurrently handle multiple transactions without affecting the consistency of the database, and this property of relational databases is called isolation.

But let's say the transactions make some modifications to the database, and after they have been successfully executed, the system goes down after making these changes to the database. This would not affect the result of the successfully executed transactions. The database will not change its state, and so durability ensures that any modifications that are made by a transaction to the database persist in the database even in the case of a system failure. And so these are the ACID properties of a relational database that are insured by every transaction that occurs on the database, and these properties make relational databases extremely useful in the financial sector, e-commerce websites, ticket booking websites, or just about any place that needs to ensure the ACID properties.

Let's look at some of the companies using MySQL. In fact, 80 percent of the companies worldwide are using MySQL, and this is a list we have curated for you of the top companies in each sector using MySQL. So you have NASA and Boeing in aerospace; Bank of America and Citibank in financial services; U.S. Navy and United Nations in government; General Electric and Tesla Motors in manufacturing; then you have Booking.com and Lufthansa in travel; BBC and Netflix in media and entertainment; then you have some big names like Apple, Dell, IBM, Microsoft, and technology; and all the internet companies, in fact, including Facebook, Twitter, Google, Amazon, and LinkedIn are using MySQL.

Now let's talk about client-server architecture. The client-server architecture is one of the most used and important software architectures. An example of this in real life is the internet. Let's say you want to know about elections; you go to your browser and Google "elections," and you see a page displaying information about elections. Ever wondered how this happens? There is a server of Google which is waiting for your request. When you Google "elections," the browser creates a request and sends it to the Google server that you search for "elections." Now the Google server creates a response page and sends it back to you. This communication is done via your internet cable, which is the communication network in this case. This is a very general architecture and used in many other situations. For example, your MySQL instance runs a server. When you try to access it via a client, which is MySQL client in this case, it gives you responses based on the commands you issue, and internally it does all the talking with the database.

Now we'll talk about MySQL and MariaDB distributions. If you go to the website of MySQL, you will find there are two major versions of MySQL out there: one is MySQL 5.7, and the other is MySQL 8. MySQL 8 is the newer version, and 5.7 is the older one. The new features introduced in MySQL 8 include easier permission management using roles, better multilingual support, and performance improvements, to name a few. One point to note here is that a lot of companies are still using the older MySQL 5.7 version in their legacy applications, and backing up data from 5.7 and restoring it on 8 is not straightforward, so it is important to know which version your company is using before starting working on it. Also, as discussed earlier, we have MariaDB, which was developed as a fork of MySQL after it was acquired by Oracle. It is mostly compatible with MySQL, and the queries are similar, but if you find something not working in MariaDB, make sure to check the documentation.

Next we'll look into installing MySQL locally on Mac, Linux, and Windows. You can see the video corresponding to the OS of the machine on which you want to install MySQL. Note that you will need admin rights on the machine for the installation.

So hi everyone, in this video we'll learn how to install MySQL on macOS. So you can use the link which is given in the notes. So as you can see here, when you go to this link in the browser, there are a lot of MySQL installers that are given. So we need to focus only on the DMG archive, and as you can see here, there are two: one is ARM, and there is x86. So if you are using the latest Mac laptop having M-type chips, you should use the ARM one. If you are using an older laptop, you should use the x86 one and download it. So I have already downloaded it, so here you can see it. Note that it should be MySQL 8 and above because in the course we will be using it. So once you have downloaded the installer, just double-click on it. It will open the installer, and then just follow the instructions, continue, just accept the agreement, uh, just to agree. So this takes 774 MB of space; make sure you have some space on your machine. Then install now. Here you need to enter the administrator's name and password of your machine. Now click on install software. So as you can see, you have added the username and password; we just hit install software, and it just does its thing. Now it asks for use password encryption, strong, okay. Next, enter a password for the root user. Now this is the most important step because a lot of people enter a password here, then they forget it, and then there is no clear way to actually reset it. Okay, so make sure when you put a password here, you remember it or write it down somewhere; otherwise, you would have to uninstall MySQL and reinstall it; that's the easiest way actually. Now you have to again put the administrator name and password, and so as you can see it is doing the installation. Okay, so now the installation has completed successfully, and it says thank you for installing MySQL server; close the window. Now we want to check if it is installed successfully, so let's go to the terminal. As you can see here that the MySQL server is already installed. Now, in order to check it, you can go into system preferences, and in system preferences, see MySQL and check here whether the instance is running. Here you see the version, it's 8.0.26. Go into the MySQL shell; you need to open the terminal, and in the terminal just go into the directory using the CD command, user local MySQL/bin, and in this you can write ./mysql. So as you can see here, it says access denied for the user. So our user is root, so I'll say -u root -p. Now you need to enter the password which you had noted while installing MySQL, and as you can see here, now you are welcome into the MySQL monitor, and this is the MySQL console.

In this video, we'll learn how to install MySQL server on Ubuntu. The version of Ubuntu I am using is 19.04, so let's get started. Note that you will need an internet connection and admin rights on the machine you wish to install MySQL server on. So let's open the terminal. Now let's update the packages using apt. Put in the admin password of your machine. Next, let's install the MySQL server package. So it says that it can't find the lock; be sure to put in the sudo before it so that you give it the permissions. Press y to see if MySQL is installed correctly; type systemctl status MySQL. As you can see, it says MySQL service Community server, and you can see this green dot, and it's written active running, means it has installed correctly. Just come out of it using :q. To get into MySQL shell, use sudo MySQL. As you can see, we are logged into the MySQL shell. Now you can see the server version, which is 5.7. Use this command instead of the one I show in the videos for going into MySQL shell since I have created those videos on my Mac machine using some other way. For MySQL dump, you can use the command sudo mysqldump. So let's just come out of the shell, and I'll type sudo mysqldump. So this is the command for MySQL dump.

In this video, we'll look at how to install MySQL server on Windows. So first of all, you need to visit the MySQL installer downloader page, the link to which I have shared below this video. Let me open it in my browser. Now I need to download the installer. So note that this is an 8.0 installer, but the similar way you can also install 5.7. So guys, as we can see here, MySQL installer has been downloaded. Let's just start the installer. So this takes us to the installer's window, and just, you know, you can accept the terms; if you want to read it, you can read it as well. Next, next, just take the default settings, everything like next, just do yes, execute, and this will start running the installer. Now as you can see here, the installer is complete, so let's start the MySQL server. Do next, next, just go with the default configurations here. Now you need to set up some root account password; make sure you set a strong password and remember it. So as we can see here, the configuration of MySQL is complete, so just press finish. Again, just do next, next, and the security credentials, just put in. Started. If you want to start the shell again after closing it, you can just go to the start menu and use the command line shell option. Put in your password, and yeah, we are into the MySQL shell now.

So in this module, we'll be working with SQL. We'll look at the most basic queries in SQL. Here specifically, we'll be looking at what SQL is; how do we connect to the MySQL server; how do we create databases in MySQL; how to create a table within a database; we'll look at the basic data types in MySQL; then we'll move on to inserting some records in the table; we'll retrieve those records; we'll be updating those records; deleting them; and then we'll look at how to alter the structure of the table; and finally, how to delete the table. So in short, we'll be covering all aspects of working in SQL. Let's understand what really is SQL. For the user to interact with the database, we need a query language. The query language used for managing all data in a relational database is called structured query language, or SQL, or SQL for short. It was first developed in the 1970s as part of an IBM project. Then it was first standardized by ANSI and ISO in the 1980s. Since then, it has been used widely in all relational databases. There have been various versions of SQL since then, and the latest one is SQL 16, which is the latest version of SQL released in 2016. DBMS vendors make additional changes to it to suit their applications. Here is an example of a SQL command. It consists of a SELECT clause, that is used to retrieve records, the columns that we want to extract from a table, the FROM clause which determines the table we want to retrieve data from, and the table name, and finally we end the command with a semicolon. So as you may have noticed, it is a highly human-friendly language, and therefore anyone, including someone with limited knowledge of programming, can easily work with SQL.

Now SQL is used to perform various operations on a relational database. It has various types of commands for different purposes. One of them is to manipulate the structure of the tables, like creating, altering, or dropping tables. These commands are known as data definition language commands. The other is to manipulate the records in the table, like inserting, updating, or deleting records. These commands are known as data manipulation language commands. SQL is also used to query the data, that is, retrieving the required data from the database. These commands are known as data query language commands. SQL even provides commands to control the access of the database through data control language commands. This prevents unauthorized access to the database. Finally, to control the transactions and ensure consistency of the data, we have the transaction control language commands.

In this video, I will show you how to connect to the MySQL server on a Windows machine. So first of all, go to the search menu and search for services. Thank you. Here, scroll down to MySQL. Here I have MySQL 80 over here and start the service. This is because we need to first of all start the MySQL server on a Windows machine. Now this may already be running on your machine, but if it is not, you can start it like this. Repeat, you can start it like this. So once you have done that, I'll just minimize this. So now I will show you how to connect to the server from your command prompt. So to do that, we need to actually open up the file explorer, explorer, and let me

Show you that quickly. Here, we need to navigate to the uh, this PC directory. Repeat: Here, we need to navigate to the C directory. And inside this, we need to navigate to Program Files, then to MySQL, which I have over here. Then we need to go inside MySQL Server 8.0, and finally inside this bin directory. So the path to this directory can be found from here; just click on top, and you can just copy it to your Flipboard.

Now, just open up the command prompt, and over here we are going to navigate to that bin directory. So just type in CD space and just paste that command; just you can click right-click, and the path will be pasted over here, and just hit Enter. So now that we have navigated inside the bin directory of MySQL, let's now connect to the MySQL shell, which will connect to the MySQL server. The command for that is like this: mysql -u root -p. They need to provide the password for the root user, right? So now you are inside the MySQL shell. Any command that you execute over here will be executed on the MySQL server.

In this video, I will show you how to connect to MySQL server on Linux. So once you open up the command prompt on your Linux machine, just type in sudo mysql -u root -p. So sudo is to allow you to connect to MySQL as an admin. MySQL is the MySQL server command, then -u, then -u is for the username, which is root over here, and then we provide -p, which represents the password for the root. So once I press Enter, it is going to ask me the password for the username, which is root currently. So as you can see here, currently I am getting this error, which is that it cannot connect to the local MySQL server, and this is because the MySQL server isn't currently running on my machine. Let me show you how you can do that first. So just type in sudo systemctl start mysql.

Now you can actually check whether the MySQL server is running on your machine or not using the same command, but this time you just have to replace start with status. So as you can see, the MySQL server is now running on my machine. Now in some Linux machines, this sudo systemctl start mysql command won't work. For those machines, you have to use a different command, which is going to be sudo service mysql, and then you just type in start or status to check whether MySQL server is running or not. So now that the MySQL server is running on my machine, let me again connect to the MySQL server. So again we are going to use sudo mysql -u root -p. Now it is going to ask me for the password for the root user. So as you can see, I'm now inside the MySQL shell. As you can see here, this shell is connected to the MySQL server, which is 8.0.25 version, and it, this is the community version, and as you know, MySQL shell is actually a client, so it is connecting to the MySQL server. Now any command that I run over here in the shell is going to be implemented by the server on the databases. So this was all about connecting to MySQL server on a Linux machine.

Now, in order to actually go into the MySQL shell, you need to open the terminal, and in the terminal just go into the directory using CD command: user/local/mysql/bin, and in this you can write ./mysql. So as you can see here, it says Access denied for user 'localhost'. So our user is root, so I'll say -u root -p. Now you need to enter the password which you had noted while installing MySQL, and as you can see here, now you are welcome into the MySQL monitor, and this is the MySQL console. In this video, we'll talk about exploring and creating databases. So let's start off with our first SQL query.

Now I have MySQL shell which has started here. Let's say we are developing a job portal, and we want to save the data of users, jobs, and their interactions. Here, let's just say our database name what we want is job portal. To create a database, the command is CREATE DATABASE followed by the database name. So what we do is we write CREATE DATABASE, and the database name which is job portal, and end it with a semicolon. It says Query OK, 1 row affected, which means that the job portal database has been created successfully. Some points to note here are: the name is case-sensitive, so job portal with the J capital and a j small are different things. Each command of MySQL ends with a semicolon or \G. Okay.

Now let's verify if the database is created. So we enter the command SHOW DATABASES; what it does is it shows all the databases which are running out there, and then it was a semicolon. So here we can see, along with other databases, we have job portal also there. If we want to work with a particular database, let's say job portal, we enter the command USE the database. So USE job portal; and it says the database has been changed. Since data in MySQL is stored in tables, it is important to learn how to create tables. So in this video, we'll learn how to create tables. Let's continue with our example of creating a job portal, and let's say we want to store data of users in the table. Let's say we want to call the table user_details. Okay. For starters, let's say it has only three fields. The field names I am writing out here which we want: one is, let's say name, which will be the name of the user; this will be our text field, okay. The second field I want is designation, which is the designation of the user, which is again our text field. And the third one is, let's say the salary, okay, which will be an integer field. Something you need to know at this point is that in MySQL, in order to create a table, two things are required: first is the structure, so you need to define the structure of the table first, and the next is to insert data into the table. So let's first define the structure or schema of this table. The command to do so in MySQL is the CREATE TABLE command, which is like this.

So I have opened my MySQL shell. I'll write CREATE TABLE, the name of the table which is user_details here, then I'll open a small parenthesis and close it and the semicolon. In the small parenthesis, I have to basically write the field names and and their types. So for example, the first field name here is name, and the type is TEXT. The second field name is designation, type is TEXT, and the third field name is salary, and the type is INT. Okay. First field name I have written, second is designation, type is TEXT, and the third is salary and type is INT. Okay, and I press Enter. It says Query OK, 0 rows affected, which means it has successfully created the table. In order to verify if the table is created successfully, we can use the SHOW TABLES command, which basically tells you all the tables which are there in the current database, which is job_portal here. So I'll write SHOW TABLES; so as you can see, our table is out there, which is user_details, which is created.

Now let's talk about the different data types in MySQL. As we have seen earlier in the video of creating tables, we need to let MySQL know the exact type of each of the columns in order to create a table. Here we'll talk about the different data types which MySQL supports. As you can see here, the most commonly used data types are: first is INT, so you can store any integer valued variable in this field. Note that there is a parenthesis and a number inside it; this is used to specify the expected length of the integer. For example, if you expect the number to be from 1 to 1000 and no more, INT(3) is a good starter. Specifying the exact length helps in decreasing query time. The downside is that if some data comes in which has more length, it will be truncated, so it is necessary to plan it in advance. Next is VARCHAR, which is basically a string with some limit. Here putting the length is necessary. TEXT is used for free flow text; no needs to specify limit here. Next is TIMESTAMP, which is used to store timestamps like when something is created or modified, when someone applied to a job, or when you ordered something on Amazon. So these kind of timestamp you will generally store in this table. Next is ENUM, which is used for categorical variables. Examples are choices. So let's say you want to ask the user, you know, what they want in the food, whether it's veg or non-veg mail. So these are the kind of situations where you give options to the user; those variables which store the values of user input, those are stored in an ENUM data type. The most efficient data type is ENUM in the case of choices, okay. Then there are some other data types like FLOAT or floating point integers, BLOB for storing binary data, and so on. To learn more deeply into the data types, you can see the refer a link out here.

If we want to know the structure of a particular table, there are two commands in MySQL: first is the DESCRIBE command, which is as simple as DESCRIBE table_name, and the other one is SHOW CREATE TABLE command, which looks like this: SHOW CREATE TABLE table_name. Note the differences here. DESCRIBE command just gives the schema or structure of the table. On the other hand, SHOW CREATE TABLE gives the query which one can be used to recreate the table schema. In this video, we'll show you the use of DESCRIBE and SHOW CREATE commands in the MySQL shell. So let's just use this commands on our user_details table. There is also a shorthand for this command where I just write DESC. So as you can see here, we get the structure of the table. So we get the field names, the type, whether it can be NULL or not, whether it is a key or not, and what kind of key, what are the default values, and some extra information. So as you can see here, name is a TEXT field, designation is again a TEXT field, and salary is an INT field of length 11. NULL is allowed in all of them, means all of them are optional. The Key column here is blank for now. We'll look into indexing in a later module where you will understand what keys are and how they help us to retrieve data more efficiently from databases. In essence, keys help us to speed up our SELECT queries. Now let's move on to our next command, which is the SHOW CREATE TABLE command. So let's just do it. Foreign table statement of user_details. You can see here a few more things like engine and character set. You can ignore them for now; just know that engine is the underlying storage update and retrieval powerhouse of MySQL, and there are different ways to do it; one of them is InnoDB, other one is MyISAM. Character set is the character set which helps you in multi-language support. Here it is latin1, and you know there are different character sets. If you have a problem storing language characters like Chinese in your MySQL table, character set may be a problem, so you need to look into that if you want to insert records in a table.

Then you have to use the INSERT command. The syntax for it is very simple: INSERT INTO table_name VALUES, and then you provide the values for all the columns within the parenthesis. This will add a new record to the table with the values for all the columns. If, however, you want to insert a new record with values for only a limited set of columns, then you have to use this command. The only difference is that this time, along with the table name, you also provide the name of the columns where you want to insert the values. If you want to insert records in a table, then you have to use the INSERT command. The syntax for it is very simple: INSERT INTO table_name VALUES, and then you provide the values for all the columns within the parenthesis. This will add a new record to the table with the values for all the columns. If, however, you want to insert a new record with values for only a limited set of columns, then you have to use this command. The only difference is that this time, along with the table name, you also provide the name of the columns where you want to insert the values.

In this video, I will show you how to retrieve the records from a MySQL table. So remember that we were working with the job_portal database, and within that we had the user_details table. We inserted a few records in this table, and let me show you these records. So the command that you have to use is the SELECT command. It is used to retrieve records from a table. After that, you provide the name of the columns that you want to retrieve. A special * character is used to retrieve all the columns from a table, which I have used here. Then we use the FROM keyword, and then the name of the table, which is user_details. So this command is going to retrieve all the records for all the columns from this user_details table. So as you can see, we have three records over here, and we have three columns in this table, so we have records for all of these columns. Now let's say we want to retrieve only the name column and not the designation and salary columns. So again we have to use the SELECT command, but this time we can provide the name of the column that we want to retrieve, which is going to be the name column. Then again we have to use the FROM keyword, and then the name of the table. So as you can see, this time only the name column has been retrieved and not the other two. If we want to retrieve name and designation columns and not the salary column, then again we use the same command as above, but this time we also provide the name of the designation column after a comma, and so we are selecting name and designation columns from the user_details table, and here as you can see we have only these two columns and not the salary column.

Now let's say you want to retrieve a record where the name is Parul. So again you can use the SELECT name command FROM user_details, and since this time we want to filter out a specific record based on a value, we have to use the WHERE clause. So we use WHERE clause over here. Since we want the name to be Parul, we mentioned the name of the column, and then we want the value of a record for this column to be equal to Parul. So as you can see, this is a complete command to retrieve only that record where the name is Parul, and once you press Enter, only the name column for that record where the name is Parul is present over here. Similarly, you can also retrieve that record where the designation is Data Engineer, and this time going by the records in our table, it should return the name Jatin. As you can see, the record Jatin has now been returned to you. So this was all about retrieving uh records from a MySQL table.

In this video, we'll learn about NULL versus NOT NULL. Consider a scenario where you have a registration form for the user. Now some fields are there which you want the user must fill; others are optional. If the user doesn't fill the mandatory fields, the form should not be submitted. Now this is a kind of constraint in which there are some fields which you can allow to be blank; others you cannot. The implementation of this feature at database level is called the NOT NULL constraint. So there is a special value called NULL, all in caps. At the time of creation of database, you can specify which fields you want as mandatory or NOT NULL and which are optional. If you missed it at the time of creation, you can change the behavior by restructuring the table using the ALTER command, as we will see in later videos. If someone uses the INSERT statement in which the mandatory fields specified by NOT NULL constraint are not present, the query will fail, and you can tell the user to fill in the mandatory fields. So in summary, a column which has NOT NULL constraint means it is mandatory to put some value for the column while inserting the row. A column which allows NULL means it's okay to give NULL value, a special value which means blank. The blank is different from empty or zero. This is defined in the structure of the table.

In this video, we're going to see how to deal with NULL values in a MySQL table. So we're going to use the jobs_portal database, and this is our user_details table. Remember that the third record had a NULL value for the salary column. Let's say we only wanted to retrieve those records from this table whose value for the salary column is NULL. So in effect, we want to retrieve only this third record. So we're going to SELECT * FROM user_details, and again we have to use the WHERE clause because we are filtering the record from the table. Then since we are filtering on the salary column value, we have to use the salary column, and now we're saying that wherever the salary is NULL, retrieve those records. So to compare the value of a column to a NULL value, we have to use the IS operator, and after that we use NULL. So we are retrieving all the record values from the user_details table where the value for the salary column is NULL. So once I press Enter, only the third record which has NULL value for the salary column is returned here. If, however, I wanted to retrieve those records whose salary was NOT NULL, so the first two columns where the salary is NOT NULL, I wanted to retrieve those records. So to do that, the command is going to be exactly similar to the one above, but this time instead of IS NULL, we are going to use IS NOT NULL. So we are using negation here, and once I press Enter, all the records whose salary value is NOT NULL is output over here. So here the third value, judging whose salary was NULL, is not returned over here. Now let me also show you how to insert a record that contains a NULL value. So we have to use the INSERT command, then INTO user_details, then the VALUES keyword, and then we provide the values for the record. So let's say we want to insert a record that has the name Andrea; Andrea is a software developer. Let's say there is no value for the salary column for this employee. So we cannot leave this as blank over here. If I do that, it is going to give me an error here, and this is that the column count does not match. So basically, since we are inserting uh the values for this record for all the columns, and I am only providing values for the name and designation column and not the salary column, so I am going to face an error over here. So to overcome this error, for the salary column I am going to enter the NULL value. So now as once I press Enter, it is going to be successfully loaded into the table, and you can view it using the SELECT * command. As you can see for here, the salary is again NULL. So that is how you can deal with NULL values in a MySQL table.

Now let's look at how to update data in a table. The command used is the UPDATE command. The syntax is shown here, which looks like this: UPDATE table_name SET column_name = new_value, and then if you want you can put some condition. So basically what it says is: use the table given by table_name and change the column_name value to the new_value based on certain conditions. Now there are some things to note here: first, updates have to be done one column at a time; you can't update multiple columns in one go. WHERE clause is used to select the rows on which you want to do the updates, and if the WHERE clause is not used, it will set all rows of column_name to new_value. In this video, we'll show you how to use the UPDATE command in MySQL shell. We have our user_details table which looks like this. Now let's say Parul gets promoted to Senior Data Scientist and her salary is raised to 15. We need to update the table to reflect these changes. So I need to change this row, and designation I have to change to Senior Data Scientist and salary to 15. The question is how to do it. As we learned in the last video, update can be only done one column at a time, so we have to do it in two steps. First, we have

To update the designation and then salary, or vice versa, let's start with designation. So we say, "UPDATE user_details SET designation = 'Linear Data Scientists'". And I have to select the rows, so I'll say "WHERE name = 'Parul'". As we can see, it says "Query OK, 1 row affected". Let's see whether it has done it. Now let's update the salary. The query will be similar; instead of designation, we'll have salary, and instead of this, we'll have 15. Now let's see the result. So we have updated both the designation and salary of Parul.

Now this can be done on multiple rows as well; it just depends on your WHERE clause. So instead of "WHERE name = 'Parul'", if I just say "WHERE name = 'Parul' OR name = 'Roshni'", it will do it for both the rows. If I remove the WHERE clause and just say "UPDATE user_details SET salary = 15.", it will change the salary of everybody to 15—all the rows.

Now let's look at how to delete data from a table. The command used is the DELETE command. The syntax is as shown: "DELETE FROM table_name WHERE condition". The things to note here are: all the matching rows corresponding to the WHERE clause will be deleted from the table in one go. If the WHERE clause is not used, all rows will be deleted. So be careful. So it says "3 rows affected". So Vivek has salary greater than or equal to 50, Roshni has salary greater than or equal to 70, and Tiny has salary greater than or equal to 50. So they should be removed. Let's see the table. So as you can see here, all the three rows have been removed. Thank you for watching this video. In the next video, we'll learn about DESCRIBE and CREATE TABLE commands, which will help us know the structure of the table.

A small thing to note before we go to the next video is: don't use the command without the WHERE clause, specifically the DELETE command. The reason is, if you just write "DELETE FROM user_details", it will delete all the rows because you have not specified the condition on which rows should be deleted. If you want to add, rename, modify, or delete a column from a table, then we use the ALTER command. So let me show you the syntax of these one by one. If you want to add a new column to an existing table, then you can use the ALTER command like this: "ALTER TABLE table_name ADD COLUMN column_name AND its data type". To rename an existing column in a table, we just modify the ALTER command and we use "ALTER TABLE table_name CHANGE COLUMN", and then we provide the old column name, then the new column name, and then its data type. To change the data type of an existing column, we write something like this: "ALTER TABLE table_name MODIFY column_name new_data_type". Finally, to remove a column from a table, we have to make use of DROP with ALTER: "ALTER TABLE table_name DROP COLUMN column_name". In this video, we'll show you how to use the ALTER TABLE command in MySQL shell.

Let's say you want to add another column to the user_details table, which is experience, which is an integer with length 3. We can do it as follows: "ALTER TABLE user_details ADD COLUMN experience INT(3)". Now if we run it, we can see it has run successfully. Let's see what the table structure looks like right now. As we can see here, the experience column has been added. Now you may be wondering, we already have some records in the table, which are Wireless Neha. If I add another column right here of experience, what kind of values will be populated in that column? So let's just have a look. As we can see here, it has put NULL values for those rows.

Now let's say we want to change the name of a particular column. Let's say we want to change experience to EXP. We can use this command. Let's see the change using the DESCRIBE command. So as you can see, that name has been changed from experience to EXP. Suppose we just want to change the data type of a column. Let's say we want to change the data type of EXP from INT(3) to INT(10). The command will look like this: "ALTER TABLE user_details MODIFY COLUMN exp INT(10)". Now let's see the table structure again. As you can see, the data type of experience or EXP here has been changed from INT(3) to INT(10). Now let's say you figure out that the column EXP is no longer required or necessary and you want to delete it. The command to do it is: "ALTER TABLE user_details DROP COLUMN exp". So it has run successfully. Now let's see how the table looks like. So as you can see, the EXP column has gone. In this video, you learned about altering tables: adding, changing, modifying, and deleting columns.

Now we have already seen how to delete records from a table using the DELETE command, but what if we wanted to delete the entire table from the database? That is, what if we wanted to delete all the records from a table and also delete its structure from the database? Well, that is called dropping a table, and the command to do so is the DROP command. Let me show you the syntax for that command. So it's pretty straightforward: "DROP TABLE table_name". Be careful while using this command, as you will lose the entire table data and the table structure after executing this command. In this video, I'm going to show you how to drop a table from MySQL. So as you know, dropping a table means that we are going to delete not only the data from the table but also the table structure. So be very careful when you are implementing such a command in MySQL. So the command is going to be very simple: "DROP TABLE table_name". Since we have been working with the user_details table, I am going to drop this table from the job_portal database. So once I press Enter after this, the table is going to be dropped from the database. So now if I do a "SHOW TABLES" command, it is going to return an empty set over here. So I no longer have the table data as well as the table structure within this database. So that was all about dropping a table from a database. Again, be very careful when you are implementing this command.

In this video, I will show you how to import a local CSV file into MySQL. Let's say we have this CSV file over here, which contains these three columns: name, designation, and salary, and the four records over here. So this is exactly similar to our user_details table. So let's try and import this into MySQL. Here I am already logged into my MySQL shell, and let me use the job_portal database. Now let me show you the user_details table that I already have here. So as you can see, the table is currently empty and it already contains the three columns: name, designation, and salary. So we want to import this CSV file into the user_details table. Now this CSV file is locally stored on my system, so this is going to be a local CSV file, and let me show you how we can import this into MySQL. So the command is going to be very simple: "LOAD DATA LOCAL INFILE". So we are loading the data, and this is a local file because it is residing on the client, which is on my local system. Then "INFILE" is the command to import the file. After that, we provide the path to the file. This is going to be followed by the "INTO TABLE" keywords, and after that, we have to provide the name of the table, which is going to be user_details. Now since this is a CSV file, our columns are going to be separated by a comma, so we're going to mention that. Now there could be a case that some records contain an apostrophe in the value, so we can ignore those using the optionally enclosed option. Since the first line of the CSV is going to contain the name for all the columns, we're going to ignore them as well. So this is the complete command for importing a local CSV file into a MySQL table. Let's hit Enter. This has given us an error which says that loading local data is disabled, and this must be enabled on both the client and the server sides. Now MySQL provides some authentication services which disables loading local data onto the MySQL server, and this is a good thing. So let me show you how we can enable this loading data option from the client side as well as the server side. So first of all, we have to quit out of this MySQL shell. So you can just press Ctrl+Z, or you can write "exit", or you can press ":q". Either way, this is going to exit the MySQL shell. Now we are again going to log into the MySQL shell, but this time along with our usual "mysql -u root -p" command, we also are going to provide an option for the "LOAD DATA LOCAL INFILE" option, which is going to be "--local-infile=1". So using this "local-infile" option and setting it to 1, we are telling the MySQL server to allow the user or the client to load the local file, and let's just hit Enter, and we need to enter the password for the user again. Let's use the job_portal database. Right, so here we allowed the MySQL server to allow local import of the file. However, if you remember, the error told us to turn on the "local-infile" option from the client side as well. So this MySQL is actually our client. So once you're inside here, you can turn on the "local-infile" option. Let me first show you the variable. So you can just type in "SHOW VARIABLES LIKE '%local_infile%'". So as you can see here, it shows that the "local-infile" is currently turned off from the client side. So we can actually turn it on. So the command for it is very simple: just type in "SET GLOBAL local_infile = 1". And this just means that we are setting the "local-infile" variable to 1, and "GLOBAL" means that this option should be remembered for all the sessions between the client and the server. So once you hit Enter, as you can see, the query has been successfully executed. Now we can actually successfully load the local CSV file. So here is the command again. This is the exact same command we used before, and this time once you press Enter, it will say that the query has been successfully executed, and four rows have been imported, and we can check that using the SELECT command. Right, so as you can see, all the records have been successfully imported into the table, and that is how you import a local CSV file into MySQL.

In this video, we'll learn how to get the number of rows in a table. The query is simple; we'll just use the COUNT operator on * wildcard and SELECT to get the count. So I'll write "SELECT COUNT(*) FROM user_details". As you can see here, it has six rows, and it has returned 6. Next, let's say we want to find the distinct values in a particular column. For example, let's say we want to find the distinct values of salary. We just use the DISTINCT keyword. The query will look like this: "SELECT DISTINCT salary FROM user_details". It gives us distinct values of salary. Note that the repeating value is taken only once. Now let's find the number of distinct values of salary. So we use the COUNT operator and say: "SELECT COUNT(DISTINCT salary) FROM user_details". As you can see here, it has counted five values which are distinct.

In this video, we'll learn how to get some aggregate functions like SUM, AVERAGE, and standard deviations of different columns in MySQL. This is very important in analyzing data. Let's say we are interested in the total salary of all users on our portal. The query is pretty simple; we say "SELECT SUM(salary) FROM table_name". As you can see here, it has outputted the sum, which is 240. Now let's say I want to name the sum as "total_salary" in the final results. So I can use the AS alias. I write "SELECT SUM(salary) AS total_salary FROM user_details". As you can see here, it has renamed the output variable. Now let's say I want to know the average salary of all users. I'll write "SELECT AVG(salary) AS average_salary FROM user_details". The average is 40 (240/6, which are the total number of rows). Just average doesn't give me any idea of the distribution of salaries, so I look out for its standard deviation. So I write "SELECT STDDEV(salary) FROM user_details". So it gives us a standard deviation of salaries as 20.

In this video, we'll learn about detecting maximum and minimum values of variables. The queries are very straightforward. For obtaining the maximum, we write "SELECT MAX(salary) FROM user_details". Here it has printed 70, which is the maximum salary, as you can see here in the table. For obtaining the minimum, we write "SELECT MIN(salary) FROM user_details". The minimum is 10, as you can see here.

In this video, we'll learn about IN, OR, and NOT. Also, we'll learn about AND and BETWEEN options of the WHERE clause to slice data. Let's say in our user_details table, we want to know the total salary of the sales department, that is, of the users Sneha and Denny. As you can see from the designation, they are in sales; no one else looks like remotely in sales. So we can do something like this: "SELECT SUM(salary) FROM user_details WHERE name IN ('Sneha', 'Denny')". This is the syntax, and I put a semicolon. So I get the sum as 70, which you can verify here (20 + 50 = 70). Suppose we wanted to find out the total salary of all the users who are in sales or CEO, then the query will be like this. So this query was for sales. Now I just want to add in the CEO thing, so I'll write "OR designation = 'CEO'". So this is the OR operator. So here we can see the salary is 70 + 70, which is 140. Next, let's say we want to find out the total salary of everyone except Denny. So I would do: "SELECT SUM(salary) FROM user_details WHERE name != 'Denny'". The operator looks like this, and I can write "Denny". So the result is 190. You can verify by adding it up. There is another way of doing the same thing, which is similar to the IN operator. I can use something like a NOT IN operator. Here I get the same result. Next, let's say we want to display the names of all users whose salary is between 20 and 50, both inclusive. So we can write "SELECT name FROM user_details WHERE salary >= 20 AND salary <= 50". Note we are using something which is called the AND operator, and we get Shreya, Vivek, Neha, and Denny. So we can check here: Vivek, Neha, and Denny all have their salaries between 20 and 50. Roshni and Parul have either less salary or more salary than this range. Okay, another way to write it will be something like this: "SELECT name FROM user_details WHERE salary BETWEEN 20 AND 50". So this operator is created by MySQL because this kind of query is done a lot. So here you can see the result. Note that most of the queries in SQL are very similar to plain English. So I would like you to practice these queries with me, and you will soon develop an intuition of the query writing skill. Let's say I want to see all the rows in the table user_details. So I can write "SELECT * FROM user_details", and it shows me all the rows. This is all right here since our table only has a few records, but what if our table has thousands or millions of records, as it will be the case in a practical scenario? We can't just dump all the data here. Let's say I want to see just the top two records. What should I do? It's as easy as this. I'll write "SELECT * FROM user_details LIMIT 2". So as you can see, it has printed the top two results. Now let's say I want to see the next two records, that is, I want to skip the first two records and display the record number 3 and 4, which are values corresponding to Vivek and Roshni. So the command looks like this: "SELECT * FROM user_details LIMIT 2 OFFSET 2". Since I want to see only two records and an offset which suggests where to start, I have to offset two rows. So here you can see it has skipped the first two records. Now let's say I want to find the top user based on salary, that is, I want to find the highest-earning user. Here in this small data set, it is easy since you can just eyeball it. So if I look closely at the data, I can figure out that Roshni is earning the highest salary, but if you have a large data set, as we'll see in the project, this becomes almost impossible to do. So we have the ORDER BY command. So let's just try it. So as you can see here, it prints out results in increasing order of the salary from 10 up till 70. Now I want them from highest to lowest salary, so I write this ending. So I have the same query, and I put a small "DESC" at the end. So now it prints the records in decreasing order of the salary, but I want to return the first user, that is, the highest-earning user. So as we have learned earlier, I'll put a "LIMIT 1". So I get "Roshni CEO salary 70" as the highest-earning salary user. If I just want to know the name of the user instead of *, I'll select the name. So this tells me that Roshni is earning the highest salary.

In this video, we'll learn about filtering patterns using the LIKE operator and wildcards. Let's say we wanted to get data of all the users from the user_details table with the name starting with "S". Now this is easy here since it is a toy example, so I can figure out who all users have a name starting with "S", and Sneha, and then I can get the data of them, but in a table having millions of rows, you cannot do this manually. The clause used to do this is called the LIKE clause. The query to get all user data whose name starts with the character "S" looks like this. So it's similar to our original query. Now I need to put a filter where name is LIKE "S%", here "S%" is a regular expression which means anything starting with "S". Here "%" is a wildcard, which means any number of characters. So basically it means search for names which match a string which looks like "S" followed by any number of characters, and I put a semicolon and run it. As you can see here, it gives us the expected data of Shreya and Sneha. Let's see if we want the sum of salaries of all users whose name ends with the character "a". What the query will look like. So I want the salary sum of all the users. This will be the query. Now I put a filter like where the name has to start with an "a". So there are no users whose name starts with "a", so here it has returned NULL. Now if I want to find the sum of salaries of all users whose name ends with "a", I'll put "%" and then "a". So here I can find the sum of salary is 60. So let's just see if it works well. So Shreya's name ends with "a", Neha's name also ends with "a", so the salary sum is 40 + 20, which is 60. So we have verified that it works fine. Now let's say we want to find out the name of all the users which have the character "i" somewhere in their name. The query will look like this: "SELECT name FROM user_details WHERE name LIKE '%i%'".

As we can see here, it has output Vivek, Roshni, and Danny. Note that these are the only two people whose names have an "i" in them, so you can verify this on your own.

Next, let's say we want to find out the names of all the users whose third character in their name is "r". So let's just see our table first. Third character is R; who are those users? One is Parul, other Shreya. I can't see any other user right now, so let's just write the query. So I'll write: `SELECT name FROM user_details WHERE name LIKE` and I need something to tell my SQL that I am looking for the third character. So the wildcard used here is underscore, which means one character; percentage means any number of characters. So I'll put one character, two characters—two underscores, then an R, and then there can be anything. And then run the query. So we get Parul and Shreya, whose third character in the name is "r".

There are a lot of regular expressions in MySQL and ways to manipulate them, which can help you filter data based on a column starting with, ending with, or matching a pattern. We have covered the most important ones here. If you want to learn more, you can see the reference in the link in this video.

We'll learn about groupings and the use of the `GROUP BY` command. This is one of the most powerful features of MySQL, which allows us to roll up the data. The most useful application of the `GROUP BY` statement is finding distributions of a categorical variable. We don't have a categorical variable in our database table yet, so let's just add a department variable which has department information of the users. So in order to do so, we use the `ALTER` command. So I can write: `ALTER TABLE user_details ADD COLUMN` [Music] `department`, and let's keep it `VARCHAR(255)`. We can also use an `ENUM` here since it's a categorical variable and we'll have a limited set of values, but for sake of simplicity, I am just using `VARCHAR`. So it says query successful. Now let's just see the table. So as you can see here, it is all blanks here. So now just let's just fill up the values. So Parul is a data scientist, so we'll keep her in the data science department. Shreya, Neha, and Danny will be in sales and marketing, and let's put Vivek and Roshni in strategy. So the commands will look like this: [Music] So I am setting `Department = 'data science'` `WHERE name = 'Parul'`. One row affected. Okay, let's just check working correctly. [Music] `Department = 'strategy'` for Vivek and Roshni, Head of Product and CEO. I'll put `WHERE name`. I can just use the `IN` clause. We wait for Roshni. [Music] Two rows affected. Okay, so it has done the thing here. Now I need to put Shreya, Neha, and Danny in sales and marketing. [Music] `'sales and marketing'` [Music] `Danny`. Three rows affected. Great! So we have put the department column and filled the values corresponding to the users, and we have a categorical variable called department now, which has three categories right now: sales and marketing, data science, and strategy.

Now suppose we want to see the frequency distribution of Department; that is, we want to see how many users are in which department. So for this case, I would expect a result like: there are three people in sales and marketing, two in strategy, and one in data science. [Music] The query to do so is: `SELECT COUNT(*) , Department` [Music] `FROM the table` [Music] `GROUP BY Department` [Music] So note the new clause we have introduced here, which is the `GROUP BY` clause, which helps you roll up data. So what this query is basically saying is: I want the count of all departments from the `user_details` table. Now press enter. As you can see here, data science has one person, which is Parul; sales and marketing has three people, which are Sneha, Shreya, and Danny; and strategy has two people, which are Vivek and Roshni.

Now let's say we want to know the total salary in each department. In order to do so, everything will be the same; instead of `COUNT`, I am really interested in total salary. So I'll write `SUM(salary)`, maybe give it a name of `total_salary`. [Music] So as you can see here, it gives us the department-wise total salary. For data science, it's just 10 because there is only one person, Parul. For sales and marketing, there are three people: 40 + 20 = 60, and 50 = 110. For strategy, there are two people, and the salary adds up to 120.

Now let's say we want the department having total salary greater than 100. So in this case, there are three departments; we just want to find out which all departments have a salary greater than 100. We want to put a filter. We now can use a new clause called the `HAVING` clause to do so. It helps us filter data coming from the output `GROUP BY` command. So what I'll write is: this will be the same query; I just put another clause saying `HAVING total_salary > 100`. So notice what I am doing here: I am trying to filter the result coming out of this query. So as you can see here, there are only two departments left: sales and marketing and strategy, which have a total salary greater than 100.

Next, let's say I want to sort this final result in descending order of total salary. So I can use the `ORDER BY` clause. So what I will do is: I'll have the same query, write `ORDER BY` [Music] `total_salary`, and I want a descending sort. So you can see we have got the departments in decreasing order of salary, and only those departments are displayed whose total salary is greater than 100.

Next, I want, along with total salary, the average salary also. As a column in this table—I am talking about this table—I also want a column of average salary because I want to do an analysis. So what I'll do is: here I'll add another column; I'll say `AVG(salary) AS avg_salary` [Music] Next, we have already seen that average is not a very important predictor of the distribution, so we may also need standard deviation to have a little bit more understanding of the distribution of salaries. So let's just put standard deviation also in as a column. So I write `STDDEV(salary) AS standard_deviation`.

Note that the complexity of the query has kept on increasing, and this is a fairly complex query. It is having aggregations, so you are having `SUM(salary)`; then you are renaming variables using the `AS` clause; you are using some mathematical functions like `SUM`, average, standard deviation; then you are rolling up data using the `GROUP BY` command; filtering the rolled-up data using `HAVING`; and finally sorting and displaying the data. We can figure some insights from data using this final table. For example, the average salary of the strategy department is greater than that of sales and marketing, but it has a lower standard deviation, which means that salaries in the strategy department are very close to each other and close to their mean, which is 60, whereas in the sales and marketing department, it's kind of more cluttered out, but it is not very different as the standard deviation is 10 and 12. I can further filter this query by using the `WHERE` clause if I just want to focus on, say, strategy like this. So this is the entire query right now, and let's say I just want to focus on strategy. So I need some way to filter this final result. So I'll write `WHERE Department = 'strategy'`. So here you can see I have just filtered out the strategy row out of the result.

Note that this is a fairly complex query we have written, and the order is important. First, we have the `SELECT`; then we have all the items we want to select. Now there may be some aggregations, some functions which you want to do out here. You can also give aliases to these items. Then you have to give the name of the table from which you want to select the data, which in this case is `user_details`. Next, we have the filtering based on the `WHERE` clause. Note that the `WHERE` clause should come before the `GROUP BY` or `HAVING`. After the `WHERE` clause, we have the rolling up of data based on `GROUP BY`, and finally we have the `HAVING` clause, and after that you will have the sorting by `ORDER BY`, and whether you want an ascending sort or a descending sort. Thank you for watching this video. Hope you enjoyed writing some complex queries here. Practice more such queries on your machine.

In this video, I am going to show you how to export data from a MySQL table to a local CSV file. So we have been working with this `user_details` table, and these are all the records. Now let's say we want to export this into a CSV file. So the command for it is going to be very simple. We have to use the `SELECT` command over here, and since we are exporting all the columns, we have to use `SELECT *`, then the `INTO` keyword, and then we have to use the `OUTFILE` keyword over here. Remember that when we were importing data from an external CSV into a MySQL table, we used the `INFILE` keyword instead. Now we provide the path where we want to save the CSV file. So as you can see, I am exporting this as `user_details_output.csv`. And once you have done that, you have to provide the `COLUMNS TERMINATED BY` option like you did for the `INFILE` command, and since this is a CSV file, that is going to be a comma. `LINES ENCLOSED BY` option, then to determine new lines in the CSV, we have to use the backslash n character. So you have the `LINES TERMINATED BY` option over here, and this is going to be backslash n, and finally the table from which we are exporting this data. So this is going to be `user_details`, right? So this is the complete command, and once you have done that, just hit enter. So as you can see, uh, we have encountered an error over here, which says that the MySQL server is running with the `secure_file_priv` option, and so it cannot execute this statement. And this is again a security feature of MySQL, and by default it does not allow you to export data from MySQL into any other directory but only a specific directory. And let me show you where that directory is located. So type in the command with me: `SHOW VARIABLES LIKE 'secure_file_priv'`. So this is the path to the directory where you can successfully export data from MySQL, and we are going to use this directory. So just copy the command from above and just change the path of the directory. So here I'm going to use this directory. As you can see here, just copy and paste that directory path and follow it by the name of the CSV file that you want to output. Once you've done that, just hit enter, and now as you can see, the file has been successfully exported, and you can visit this directory and view the file over there.

In this video, we'll learn how to back up data from MySQL into .SQL files. So I have this MySQL shell running. Let's see the databases. Okay, so I use a particular database, `job_portal`. So there is one table out here which is `user_details`. Okay, and let's say I want to back up this entire database of `job_portal`. So the command used is something called as `mysqldump`. What we'll do is we'll just come out of MySQL. Okay, I pressed Ctrl+D, and I'll write `mysqldump` with the same options as you write for MySQL. And now you have to put the name of the database you want to back up. So in our case, it's `job_portal`. So then this greater-than sign, which means redirect everything to something else. The something else here is the SQL file. So I'll write `job_portal.sql`. Press enter. It will ask for a password; put in the MySQL password, and it's done. If I see, I have got the `job_portal.sql` file here, which is the backup of the entire database. Okay. If I want to back up all the databases, I won't put this `job_portal` also; all right, and this command will back up all databases. If I want to just back up one table of a database, I'll write the database name here and then the name of the table, okay, and it will back up only one table of the database. So now let's see how the SQL file looks like. I'll just open the file. So it says `mysqldump 10.13`, gives the version of MySQL, and then there are some commented lines. Looks like commented, but note that there is this exclamation mark out here. So these are the kind of options which run depending on your machine and your MySQL configuration. Then it creates the tables. Note that we have already seen these kinds of commands like `CREATE TABLE`, and then it will dump data using `INSERT` statements. So you can see here all our data is dumped into the `user_details` table using an `INSERT` statement, and then there are some other checks which help in integrity checking of the database. Let me come out of this file. So that's how you back up databases and tables from MySQL.

In this video, we'll learn how to restore databases. Now the point of taking backups is really that you can restore it at some point in the future. The reason we take backups are basically two: first, if somebody, say, accidentally deletes some tables or databases, you will have a copy; second, if you want to move data from one server to another, you have to take a backup on one server, copy the file to another server, and restore the backup file on the second server. So let's see how restore is done. In the last video, we had already created an SQL file which was the backup of our `job_portal` database. So let's see what databases I have right now. I still have the `job_portal` database. Let's just see what's inside the database. I'll see the tables. So it still has the `user_details` table, and let's see what kind of data is there in this table. As you can see, there are six records here, and all the data is currently there. Now what if, you know, we just accidentally delete this database? So let's just do it. So the command used is called `DROP DATABASE`, followed by the database name, which here is `job_portal`. Now note that please exercise extreme caution while using this command because it will delete the database; it will delete all the tables inside the database; it will delete all the data inside all the tables. So here we run it; query okay, one row affected. Now if I say `SHOW DATABASES`, it's gone; there is no `job_portal` any longer here. If I try to use the `job_portal` database, it says unknown database. So it's gone; everything is gone. Now let's try to restore this using the backup image we took last time. Before that, I need to go out of MySQL. I put a Ctrl+D. Now I am at the terminal prompt here. I can see the file `job_portal.sql`, which is kind of my savior right now. The command to restore is like this: `mysql -u root -p`, then this redirection operator, and `job_portal.sql`. But if you run it at this moment, it will give an error. The reason is this command expects you to create a blank database, and then it will run and restore all the tables and data in that blank database. So let's just run it anyway. It gives an error: no database selected. So we know what to do; we need to create a blank database named `job_portal`. So again I go into MySQL, create a database which is blank, okay, come out of MySQL, and then I have to run this command here. Note that you have to put the name of the database where MySQL wants you to execute this query. Now I put in the password, and it's done. Let's just look whether it has done something successfully or it was just a fluke. Now we are logged into MySQL. I say `SHOW DATABASES`; I have the `job_portal` database here. I use it; database changed. I want to see the tables. So there is the `user_details` table. [Music] Let's see if it has actually restored some data into the table. Great! So it has restored all the data I had in the table. So this is the way you actually restore databases from their SQL files.

In this module, we do a descriptive analytics of the FIFA 19 players data set. We'll go step by step, as is done on the job by a real data scientist, and I'll walk you through the thought process of how data is understood, transformed into a useful format, and how we get answers and insights from our data. You can download the data for this project from the link below this video. The data set is the FIFA 19 players data set, which is also in the open domain. I have taken a subset of it and cleaned it for you to get started quickly. The topics we'll be covering in this module are as follows: First, data eyeballing. Here we take a closer look at the data to get an intuition of what it contains. Second, data dictionary. Here we'll understand what a data dictionary is and look at the data dictionary of the FIFA 19 players data set. Next, we look at some key questions we need to find answers from.

data. Then we'll analyze the data and create the table structure to store it in MySQL. Next we'll load our data into a MySQL table. Next we'll do data analysis, where we start with simple queries first—that is, we'll try to answer the simple questions we had talked about in the earlier video first—and finally we'll analyze the data to come up with the answers of complex queries, also learning some Advanced queries in this process.

Now I assume you have downloaded the data set of FIFA 19, which is the CSV file. The first thing we need to do is data eyeballing, which means we need to look at the data manually and see what's in it. So I have just opened the data set in Excel, and let's see what it looks like. In this case, the data set contains less than a million rows, so we can view it in Excel. IF the data set were bigger, we would have used some shell commands like head, tail, or cut, etc., to take a sample of data for the purpose of eyeballing.

The data seems to be a player-level data. So as you can see here, there are different players, and each row consists of data about a player. The data contains different attributes of the player, like his ID, age, nationality, overall rating, potential rating, the club he belongs to, the overall market value of the player, the wage he is getting, the preferred food, whether it's left or right, the jersey number he generally wears, the date he joined the club, his height and weight, and the penalties.

Now let's just see how big the data is in terms of rows and columns. So if I just want to see the number of rows, since I am using Excel, I can just—15 columns are there—and for the number of rows, I can just go to the bottom and see. So there are close to 16,000 rows and 15 columns.

So now let's put a filter on the columns to see what kind of values they contain. So I'll go to data, filter, and it puts on a filter. In Mac, you can do using data, filter. In Windows, you can directly use the data tab or use Ctrl+Shift+L. Note that in Mac, sometimes the data tab filter does not work properly; sometimes it does, sometimes it doesn't. So you have to be careful about that.

So now let's look at one variable at a time. So first is ID. So ID, if I see its value, starts from 16, goes all the way up to 246,000. It's some kind of an integer. The next one is name. So you have all sorts of names here, with some unique code characters also out there. The next is age. So the age ranges starts from 16, 17, 18, goes all the way up to 45; all are whole numbers, as you can see.

So why I'm telling it's a whole number or it's an integer or it's a Unicode because you will need them to ascertain the data types of the table structure we want to put the data in. So as we can see here, it's a diverse age group, from a teenager all the way up to, you know, 45 years old. The next one is nationality. Taj from Afghanistan and Albanian goes all the way up to United States, Zambia, and Zimbabwe. Then his overall ratings. So somebody—the minimum rating is 46, goes all the way up to 94. Similarly, potential rating, also an integer, from 48 to 95, two digits. Then we have Club. So there are all sorts of Club here, with some like very—some random names out here—or some of the clubs.

Next is value. Value is the perceived market value of the player, and it ranges from 100,000—sorry, it's ten thousand—all the way up to 118 million. So we can see there's a lot of variance in value, but it's expected, as there are some celebrity players and there are a lot of unknown players. Next one is wage, which starts from thousand Euros, goes all the way up to 565,000 Euros. Here also you can see there's a lot of variance. So maybe some new players are known—getting thousand, two thousand Euros—and then there are some players getting more than 500K.

The next one is preferred foot. So here we can see it's a categorical variable, which is either left or right. So this is the foot from which the footballer generally plays. Next is the jersey number. So people are wearing different kinds of jerseys; number starts from one all the way up to 99. The next column is joined, which is a date column, and it is a date when the player joined the club. So as you can see here, it's most recently—there are some players from 2018—and it goes all the way up to three decades back, like 1991. The next one is height, with the shortest player being 5'1" and going all the way up to the tallest one who is like six foot nine. The next variable is weight. So there are some like very light players—110 pounds—goes all the way up to 243 pounds—some bulky players out there, maybe some Defenders—and finally we have penalties, and penalties goes from 5 to 92, and it's also an integer.

In order to do analysis and come up with meaningful insights, it's very essential to have a feeling of the data; that's the reason why we do data eyeballing. And I would say this is the most ignored step by data scientists, but I think it's really important, as it gives you some intuition into what the data contains. To download the data set, then play around with it. If you know some Advanced Excel, try to use some pivot tables, get to know some more meaning from the data, and get to know some insights from the data.

Now let's try to understand what is the meaning of data dictionary. Data dictionary, in very simple words, is the information which helps us understand the contents, structure, and format of data. It describes what each variable means in the data set. So let's look at the data dictionary of our FIFA 19 data set. The description of variables are as follows: First variable is ID, which is a unique identification number for the player. Next is the name of the player. Then we have age, which is the age of the player. Then we have nationality, which is basically the player's nationality. Then we have overall, which is overall rating of the player; potential, which means the potential rating of the player. Then we have Club, which signifies the player belongs to this particular Club. Then we have value, which means the current market value of the player in euros. Then we have the wage, which is his current wage in euros. Then we have preferred put, which is the preferred foot for playing, whether it's left foot or right foot. Then we have the jersey number; joining date, when the player joined the particular Club; then we have the height of the player in feets and inches; then we have his weight in pounds; and finally we have penalties, which is a rating on a spoil of 100 of how good the player is on penalties. Also, it's given that the data set contains information of one player per row, which means each row contains information about only one player, and you can know the number of players by just counting the number of rows.

In this video, we learned what is a data dictionary. In order to find insights from data, two things are important: first one is asking the right questions, and second one is getting the answers to those questions from the data at hand. Now asking the right questions—very important—and I've seen in my experience lot of people, including lot of data scientists, spending even months and getting nothing from the data, and the reason being they didn't ask the right questions in the first place. Now how to ask the right questions, and or how to figure out what questions are really important, really depends on the business goal you have at hand, and it also depends on your own intuition about the business, the Judgment you have. You will talk to different stakeholders, you will do some structured problem solving, and trying to figure out what questions, if I ask, will have the most impact on business. So we have created an entire course for it, because this is some topic which is really important and it's very vast. The name of the course is Structure Thinking; it's an excellent course. So if you want to Deep dive into how to frame questions—the right questions from data—you can take a look at this course. And in this module, we'll be focusing on the second question, which is: we have already some questions which we have framed from structured thinking. Now how do we get the answers to those questions from our data?

So let's see the kind of questions we want to get answered from this data, which is our FIFA 19 data set. So some questions are basic. So first question is like: how many players are there in the data set, right? Then you have: how many nationalities do these players belong to, or which nationality has the highest number of players? What are top three nationalities by the number of players? So basically I am trying to explore the data set; I am trying to see how many players are there; what's the distribution of nationalities out there. Then there are some questions uh related to the wages of players. For example: What's the total wage of the player? So I'm trying to figure out how the wage is distributed. So what's the average, what's the standard deviation, maybe you know what's it—the highest and lowest wage given to a particular player. Then let's look at the ratings. So which player has the best overall rating? Which one has the worst overall rating? Then we have the clubs. So as you know, there are different clubs, and each Club has some players in it. So I want to figure out which is the best club. Okay, so let's say this is the question. Now in order to figure out which is the best club—club in itself does not have some scorecard in our data set—what we know is what players belong to the club. Though I can maybe look at the ratings of the those players and try to figure out based on some aggregate functions like, you know, sum of ratings of all the players which belong to the club, based on that I can figure out which club is better, or maybe I can take an average of the ratings. So here we are trying to figure out which the best club is based on highest overall rating or highest average of overall rating. Then maybe you can talk about top five clubs. So I am interested in what are the top five clubs, and again based on the ratings as we discussed.

Next question is: I want to figure out what is the distribution of players who prefer left foot versus right foot. Always jersey number is the luckiest. Now these kind of questions, where you are asking which jersey number is the luckiest—what is lucky, how do you define lucky—that's really important. I mean, lucky can be defined as the players who wear this jersey have the highest wage, or maybe the players who wear this jersey have the highest market value, or they have the highest rating. So definition of the problem is really important. Next is: what is the frequency distribution of nationalities among Players whose club name starts with them? Now this is a question which I have just inserted in—in here—so that you get a good knowledge of MySQL. You won't really get such kind of question in practice, because you won't be interested in, you know, who the club name starts with them; I want the frequency distribution and stuff like that. But there may be other situations in which you may find this useful. So I have put this question. The next one is: how many players have joined their respective clubs in the date range 20 May 2018 to 10th April 2019, both included? So now a lot of times it happens that you have dates in the database, and you are trying to figure out some timeline related thing. So here I am trying to filter out all the players who have joined the respective clubs in this particular day train. The next is: how many players have joined their respective clubs day twice? So this is a distribution I want to see—on each date how many players have joined their clubs. So this kind of distribution is—they are in practice a lot of times if you think so. For example, in our website also we track what are the daily user signups, or how much daily revenue we are getting. In some other website or in some other business you may have things like: what is the daily cost we are incurring, things like that. So there is a distribution with respect to time. So on a timeline you are trying to map some variable and see how the graph looks like. The next thing is: how many players have joined their clubs yearly? So instead of saying date wise, I want to say see the yearly numbers. So for example, how many players have joined their clubs in 2016, or in 2017, 18, 19. So year-wise I need that table.

So as you can see here, we have various queries, and we want answers to them in the next few videos. You will see how to get answers to all of them using MySQL. But in order to do so, the data we have currently is in CSV. Okay, so we need to get the data in MySQL first. In this video, we'll learn how to analyze data and create a table structure for our data for the project.

Now as we have seen earlier, in order to store this data in MySQL table, we need to assign each of these fields or columns—as you can see here, like each ID, nationality—a data type. This is done based on some judgment and eyeballing. Now we'll look one by one through the variables and try to figure out what data types they may have. The first one is ID. So as we can see here, ID is a unique number which is an integer. So we can keep ID as an integer, and we can keep its length as 11 for now, because as we can see here, ID does not range more than 11. We can also skip the length if we are not sure of the Len. The next one is name. As we can see here, name is the name of the player. So we expect it to be a text field, but you generally don't have like very long names. So let's just keep it at varchar 255 for now. If there are some exceptionally longer name of players, it will give us warnings at the time of insertion, and we can change it then. The next variable is age. Now age, we can assume between 0 and 200 safely. Then set some human and not some very super god kind of a person. Just as a fun fact, the oldest person to ever live was Jeanne Calment of France, who lived for a very long 122 years. Now coming back to age, we can take it as an integer of length 3, because we don't expect it to get more than say 999. The next one is nationality. Nationality is just the name of a country. So again we can use varchar 255. Next is overall rating. So it's given in the data dictionary that it is an integer. The next one is potential value, and potential value or potential rating, we can take it as an integer since also it is given in the data dictionary. The next one is club; the club—club which a player belongs to—so we can safely take it as varchar 255, since it's a string field. The next one is value. Value is the perceived market value of the player. So as you can see here, it ranges from 10,000 Euros goes all the way up to 118 million Euros. So we can safely assume it as an integer. Similarly, wage—wage is the wage given to the player—ranges from a thousand Euros all the way up to 565,000 Euros. So again we can keep it as an integer of length 11. The next one is preferred foot. Now this is the preferred put of the player for playing. This can be either left or right, or maybe blank if you don't know about it. So these are basically a character field. So we can keep it as an enum with two choices: either left or right. Note that this kind of data types put a constraint on what is inserted. So if particular row you insert has your preferred foot which is a—let's say 20—so MySQL will give an error; it has to be between left and right, since it's an enum. The next one is jersey number. So jersey number is just the name of the Jersey, which we have seen in data eyeballing; it's just an integer between 1 and 99. So we can keep it as int(11). Now it's date—join date—joined is just a date field. So we keep it as date. Next is height. Looks like a character; if you see it, the length is pretty much short. So we can keep it between length 10. Next is the weight and pounds of the player; ranges from 110 to 243. We can keep it as an integer. And finally there is penalties, which you can also keep it as an integer of length 3. So penalties are between 5 and 92, so you can also use int here, and we are just being conservative by giving it one extra slot.

Now when to be conservative and when to take risks is an important question. If you are too conservative, and if you are constrained the data types too much, there will be some records which won't be inserted. On the other hand, if you take too much risk, so you can always say, you know, ID I'll keep as int(200) or even for height I'll keep varchar(255). The problem then is when my sequence stores data, it expects your data type, because it reserves some space on the hard disk. So if you are reserving a lot of space, first of all you are utilizing the space a lot less, and secondly, at the time of querying it will slow down. So as a rule of thumb, the longer the data type, the slower the query time. So it's a trade-off basically.

Now we have already decided all the data types of our variables. Now let's go ahead by creating a table structure. And before creating a table structure, we need to create a database. So let's just create a database. So I have written the query here; it's create database, and I'll keep name as FIFA 19 for now. I'll just go in terminal. So I had already done it, so I'll just drop it for you guys. So as you can see here, there is no database name FIFA 19 as of now. Now I created—create database FIFA 19—so it's successful. Now let's see—show databases—so we have our create database. So we have our FIFA 19 database; it's great. Now next step is creating the table structure. I have written the query here in order for Speed, and I can just copy paste it from here. So it looks like—create table players—and then these are the variables and their corresponding data types—like pretty simple query. I'll copy it, go to my terminal and paste it, press enter. It says—no database selected—so we'll select our database and we'll run our query again. So now we can see here it says—query okay—which means that the query was successful. Let's just see the structure of the players table we just created. So as we can see here, it shows the structure with the fields, the data types, and then there are some other columns. So there is some key column here which is used for indexing, which I'll come in a bit, and some extra information, and then there is whether this field can be null or not. So right now I am allowing every field to be null; everything is optional. You can change it if you want to. So for now, just know that you can do indexing in MySQL, and there are two things to remember: first is it has to be done on one column at a time, and second is the query to search becomes fast on whichever column you have indexed. So for example, if you do indexing on the ID field, so you can query like—select star from players where ID is greater than 20—it will be very quick, because it won't scan the entire table; it will just scan some certain sections of a database structure. We'll come to indexing in a later module, but I am explaining you because indexing is really used a lot of times in databases, and it's very difficult to even think about a database which does not have indexing. So the important thing is: if I have indexing on name, I would be doing very fast queries on name, okay, like selecting name is equal to Anna and or doing some regular expression based search on name, but it won't give me fast queries on age or nationality. So indexing is done one column at a time. The next thing is: why do we do indexing? Because we need fast search.

Now watch the trade-off: I can just do indexing on all the columns. Who cares? The problem is this: the more you do indexing, the more your insert time increases, and you don't want that. The other thing is, the more you do indexing, the more space it takes on your hard disk and RAM. So this is a trade-off between your hard disk, your RAM, and your insertion speed, which you want compared to the kind of querying time you really want. So we'll look deeper into indexing and how it works in the next few modules.

Now let's load the data from CSV into our players table. Let's just use the `LOAD DATA LOCAL INFILE` command to load data into our players table. The command looks like this: `LOAD DATA LOCAL INFILE`, then you have the complete name of the file. To note that in Windows, you will have a different structure to this path, maybe `C drive/something`, `INTO TABLE players`, `COLUMNS TERMINATED BY` this comma. So this is a CSV; here the delimiter is a comma, so I have put a comma. If the delimiter in your initial file is something else, you have to put that thing out here. And then there's the syntax of optionally `ENCLOSED BY` and `IGNORING 1 LINES`, because we need to skip the header. Let's just run this command. It says "Query OK", means it's successful, which is great. Also, there are no warnings.

Note that when you are importing a file as CSV into SQL, noting the warnings is important, as some data may be truncated or changed in the process, given that we have constrained the fields in our table definition using data types. So let's say if you have written some wrong data types and the data comes in which is different, you'll get a warning, and you need to see the warnings. So to see the warnings, just print warnings, and after that you will see "Show warnings enabled", and then you will run this command. Also, I have seen a lot of people, including myself, facing problems sometimes importing a date field in MySQL. For successfully importing a date field in MySQL, it should be formatted like this: so it should be like `20190102`. So what I mean is, it should be first YY, then there are the two digits for the month, and then the two for date. So the format is like YYYY, then MM, then DD, and everything should be separated by a dash, which is also the format in FIFA 19.CSV. So if you see the format, not in the Excel thing, but outside, like if you do a head of it, you will see this format. If you change it to another format in Excel, let's say if you go in Excel and change the format to "I want to see the long date with the wetness day 20 January 2019," this kind of thing, so MySQL won't be able to load it. So this is an important point to note.

So great, we have imported the data into our table successfully. Let's just see a few records. So I'll see `SELECT * FROM players LIMIT 10`, and I do a `/g`. So `/g` what it does is it gives records one by one. So here we can see—so this is the 10th row, this is the ninth row—so it just looks pretty. If I just remove the `/g`, you will have something like this.

In this video, we'll try to answer some basic questions from a FIFA 19 data set. So let's get started. So the first question is: how many players are there in the data set? Pretty simple question. Since the data set contains only one player information per row, all we need to do is account stuff. So as you can see here, there are 16,643 players in the data set. The next question is: how many nationalities do these players belong to? Again, a very simple question. Here we want to know the number of nationalities in the data set. So I write… So as you can see here, there are 161 distinct nationalities in the data set.

The next question is: what is the total wage given to all the players, what's the average, and standard deviation? So the query looks like this: [Music] `SELECT SUM(wage) AS total_wage, AVG(wage), STDDEV(wage)`… So as you can see here, the total wage is close to 160 million euros, the average wage is 9000 euros, the standard deviation of wage is close to 22,000. Which means that there is a lot of spread in the database, and a lot of players either have very high or very low wages.

The next question is: which nationality has the highest number of players? What are the top three nationalities by the number of players, and how many players they have? So here we are interested in the frequency distribution of nationalities. So to order to find a frequency distribution, the query is simple: I write `SELECT COUNT(*) AS freq, nationality FROM players GROUP BY nationality`. So this basically gives me the nationalities and how many players they have. So, for example, Spain has 974 players, and the 10 kids Nevers has only three players in the data set. In order to find the top three nationalities, we need to sort it by frequency in the decreasing order, take the top three. So as you can see here, England, Germany, and Spain are the top three nationalities with 1475, 1151, and 974 players respectively.

The next question is: which player has the highest wage, and who has the lowest? For getting the highest wage, this query is simple: we can write `SELECT MAX(wage) FROM players`. So the maximum wage in the data set is 565,000. But I am really interested in the name of the player and not the wage itself. So I can write `SELECT name FROM players WHERE wage =` this thing, I put 565,000. So as you can see here, Messi is the highest-earning player. What if I want to do this in only one query instead of using these two queries? In order to do so, there is a concept called subquery, which is a very simple concept which looks like a query within a query. So what I can do is I can write something like this: `SELECT name FROM players WHERE wage = (SELECT MAX(wage) FROM players)`. So what this query does is it selects the name from players where wage equals, and then evaluates the maximum wage from all the players, puts it in there, and then does the entire evaluation of the full query. Here again, you can see Messi comes on top. Similarly, for getting the name of the lowest-earning player, we can write, instead of `MAX`, I'll just put `MIN`. So in fact, there are a lot of players, more than 4,000, who have the lowest value wage, and the wage, if you want to find what wage they are living on, it's `MIN(wage) FROM players`. So it's just 1000 euros.

The next question is: I want to figure out the player having the best overall rating. So I want the name of the player: `SELECT name FROM players WHERE overall_rating = (SELECT MAX(overall_rating) FROM players)`. As you can see here, Messi and Cristiano Ronaldo have the highest overall rating.

The next question is: I want to know the best club based on overall ratings of its players. Now, best can either be the highest total rating or the highest average ratings of its player. So let's first look at the highest total thing. In order to do so, we need to first find the distribution of overall ratings for clubs. So I am just adding up the ratings of all the players for a particular club and grouping it by all the clubs and then sorting it by the total rating. So here you can see—so for example, Dairy City Club, if you add up the ratings of all the players, it comes out to be 945—and I want to see the top three clubs, I'll put a `LIMIT 3`. So as you can see here, Real Madrid, FC Barcelona, and Manchester United are the top clubs based on total ratings of their players. If I were interested in the average ratings of their players and describe that as a best club, we would have some other clubs which would be at the top. So this is one thing you really need to understand that in statistics, how you define the problem defines your answers. So as you can see here, completely three different clouds, Juventus, Napoleon, enter, are the best clubs based on average overall rating of their players. So in statistics or in data science, it's really important to know the assumptions you are taking because that will change the results drastically.

The next question is: what are the top five clubs based on overall ratings of the players and their corresponding averages? So instead of three, I need to have the five clubs, and also I need to give them the averages. So here, instead of `total_rating`, I can just title as `total_rating`, but it actually is `average_rating`, so it's better to just keep `average_rating` out here. If you have changed it at one place, you have to change it as in the `ORDER BY` clause also. So here, as you can see, the top gloves based on average rating are Juventus, Napoli, Inter, Real Madrid, and FC Barcelona.

In this video, we'll try to answer some advanced questions using data. So let's get started. So the first question is: what is the distribution of players whose preferred foot is left versus right? So I just need the distribution of preferred foot. The query looks like this: [Music]… and I can use a shortcut. So basically, I use one to `GROUP BY` the second, which is `preferred_foot`, and I want to `ORDER BY` the frequency, so one take a decreasing sort. So these shortcuts are very useful if you want to write the query fast. So in the `GROUP BY` and `ORDER BY` clauses, you can use the numeric positions of the variables. `GROUP BY 2` means `GROUP BY preferred_foot`, and `ORDER BY 1` it places the first argument, which is frequency here. So as you can see here, right is the preferred foot of most of the players, 12,000 something, and left is just of 3.8 thousand.

The next question is: which jersey number is the luckiest? Now, in order to know which jersey is the luckiest one, one has to know what exactly lucky means. So is it the players who wear this number get the highest average wage, or whether they have the highest total wage, or whether the players who wear it have the highest rating or the highest pursuit market value? As you can see here, being lucky is a subjective thing, and the answer to this question will also be subjective. But wait, we need to find an answer because as a data scientist, we can't say, you know, things are subjective, so we have no answer. So in such cases, what you do is assume something and try to solve the problem at hand. Also, you make these assumptions and the reasons you take these assumptions known to all the stakeholders, so they are on the same page. For example, let's assume that by lucky we mean that the players who wear this number have the highest total wage in order. Now, to find the luckiest jersey, we can write… So I am grouping by the jersey number, figuring out the total wage of all the players wearing that jersey number. So this gives us results for all the jerseys. Now I need to order it by the total wage in a decreasing manner, and I need to see the top jersey. So as you can see here, jersey number 10 is the luckiest jersey.

Moving on to our next question, we have: what is the frequency distribution of nationalities among players whose club name starts with "m"? So we can write something like this… So I am taking a frequency distribution where club name has some pattern, and I need to roll up by nationalities. So as you can see here, Spain has 30 players whose club name starts with "m", Switzerland has only three players whose club name starts with "m", and United States has 21.

The next question is: how many players have joined the respective clubs in the date range 20 May 2018 to 10 April 2019, both inclusive? Now, these kinds of filtering based on timeline of date are very, very useful because they are used a lot. So let's see what the queries look like. It's kind of simple. So I just want to filter the players and figure out the count where the date is in a particular range. So `date_joined` is in `2018-05-20`, both are inclusive, so I'll put an equals sign here. Also, you can also use the `BETWEEN` clause instead of this `AND` thing. So as you can see here, there are 4500 players who have joined the respective clubs between these these date range.

Now let's say I want to see the distribution of players as to how many players have joined their respective clubs day-wise or maybe year-wise. So this is some kind of thing which comes up regularly in practice. So let's say if I am having a portal, I want to see my registered users and their distributions daily. So how many daily users are coming on my website and getting registered? So the query looks like this… So the simple query would have been `SELECT * FROM players`, but I need to see it as a distribution of `joined`, and I need to roll up by the date. So as you can see here, this gives us a lot of the 0 0 0 0 things. The reason is MySQL stores some dates also as a date time inside. So in order to remove this thing and get a better picture… So this is the distribution. So for example, on 1st December 2018, 16 players joined their respective clubs; on 23rd November 2018, five people joined their respective clubs. Now there are two or three things to note here that one is the dates they are stored in a timestamp kind of manner internally sometimes, so you may need to use this `DATE` function. The other thing is the grouping can also be done by `date_joined` instead of just `joined`, so that will give you the same result.

The next question is: instead of daily, I want to see what is the yearly trend; how many people have joined the respective clubs year-on-year basis? So instead of `DATE`, I can use the `YEAR` function. What this does is it takes a particular date, converts it into year. So as you can see here, in 1991, only one person joined their club, and in 2018, there were 6566 people who have joined their club.

So hope you liked this module and now have a deeper insight into how to analyze data in the real world. Till now we have been focused on analyzing data from a single table. In the next module, we'll see how to get data from multiple tables and analyze it. Thank you for watching this module, and see you in the next module, which is on getting data from multiple tables.

Get Our Mobile App

SQL Full Course - in 3 hours | SQL Tutorial for Beginners | Free Certification 2023

Transcription