SQL Tutorial For Beginners | MySQL Tutorial

SQL is a very important skill if you want to build your career as a data professional or a software engineer and I'm glad to announce that with this video we are beginning SQL tutorial Series so we'll cover all the basic concepts we'll also have exercises as well now these videos are basically taken from my premium SQL course on core Basics dot IO which has received tremendous reviews so far so I'm going to take all the free videos from that course and publish them as YouTube videos and once again there will be an exercises so let's begin with the installation of MySQL we will install MySQL on Windows the first thing I will do is type add remove program and remove any pre-existing installation of MySQL so when I look at this I find bunch of MySQL components which are installed on my machine so I will uninstall them one by one if you don't see any of these components then don't worry you don't have to do anything after I uninstall everything when I type MySQL nothing is showing up once again if you don't have this don't worry I will now go to my dear friend Google and say how do I download MySQL on my machine you will click on the first link scroll down MySQL Community downloads scroll down MySQL installer for Windows and I'm going to download this particular Big File 431 megabyte so when you click on it it will start downloading you can say no thanks just start my download okay and it's gonna take some time so make sure you wait till it is downloaded so the file is downloaded here you can just double click to start installation yes now here see you can select default installation but that will require you to install Microsoft Visual Studio and all kind of components it can get confusing for some people therefore I will use custom you will say next and you will install the server okay MySQL server so by clicking on Green Arrow you can move it to the product to be installed in the application you will use MySQL workbench which is an interface where you can run MySQL queries I don't need Visual Studio MySQL shell can be useful even if you don't install it I think it's okay you don't need router at least for this course then in the connector you need only.net connector okay and this is required if you're using bi tools such as power bi if you're not going to use power bi I think you can skip that as well you don't need to install other connectors and in the documentation I will just install all the samples and the documentation so these are the components that you're going to install click next see here in my case I had some old files which exists but if you don't have old files it you will not see this message just say next execute and it will start installing these components one by one after waiting for few minutes all the components are installed I will just click next next and it is configuring the development computer the local computer with this port say next next and in the root password you can just say root root so root R double o t r double o t so I am keeping my root user which is the main user of this MySQL installation to be root root next execute finish next and here you can root root right that was the password if you give a different password then give that check connection successful execute finish next finish so now see it launched something called MySQL workbench so here we'll be writing the SQL queries exploring database importing database all kind of things and now we are done with the installation part to install MySQL on Mac or Linux you can simply go to YouTube and search for MySQL install Linux or Mac and you will find so many videos you can follow one of those videos and install the latest version of my SQL I am going to use 8.0.30 version of MySQL throughout this course so you can use the same version or any version that comes later on any latest version installing this softwares nowadays is pretty easy pretty much next next next next and if you want to build a career in data industry honestly installing software is something that you need to learn if you are facing some problem let's say during installation you get an error just copy paste that in Google you will find some answer on stack Overflow or some other website so I'm hoping you can install MySQL on your own on Linux or Mac and if you still have a question we have a Discord server so you can find the link below this video you can go to the Discord server and ask the question but once again doing things on your own and trying to figure out your problems on your own is a very nice skill to have and installing a software is something that even a high school student can do it easily once MySQL is installed you can launch MySQL workbench by going to start option and typing it MySQL workbench this is the tool that we will be using for writing our SQL queries for making updates and so on by default you see this local instance but just in case if you're not seeing this particular thing here you can click on plus icon type in local instance SQL course whatever and just say test connection and it will ask for a password the username and password both are same root root sit says successfully made a connection and when you hit OK you'll see this entry but see since I am seeing this already I don't need it so I'm just gonna delete this and I will click here and again if you ask for a password root root and just hit that save in Vault option now this is how this tool looks on the left hand side you see these three entries and these are databases that comes with the default MySQL installation if you look at this database it has tables related to movies but we are going to use our own movies data set we will not be using this so you can ignore all these databases and we're going to now import our movies data set so how does that data set look below this video there is a download button if you click on it you will be able to download two files first file is this movies underscore db.xlsx file and the second one is dot SQL file okay so we are going to import this dot SQL file into our MySQL so I will go let's see let's explore all these different options so in the server there is an option called Data import where you can say import from self contain file click on these three dots go to downloads wherever you have downloaded that file click on this and say open and let's look at all the options that we have okay this looks good click on start import that single file contain our entire movies data set and this green bar shows that the database is imported correctly you can hit refresh icon and see here you see movies database now this database has Stables view stored procedure functions so what are the tables it has actors financials languages Etc now You Must Be Wondering okay what exactly is a table what is a database let me explain you using the other file that we downloaded which is the XLS x file so let's open that file by double clicking on it and this is how that file looks now this file has multiple sheets correct see and the first one is movies and you can see it's a pretty simple database if you are watching movies you will immediately get an idea that okay there is a movie title industry which year it was released in and so on so here in if you have worked with Excel files before Excel file can have multiple sheets and every sheet will have a tabular data and when I say tabular data it means rows and columns okay so see there are these many columns title industry release it these are all called columns and these individual enter is all are called rows so our database is exactly like this it's a tabular data structure where one table means one sheet so movies is one table financials is another table actors languages so there are total one two three four five tables right now watch our database actors Financial languages movies and actors we also have Pi tables now you look at our movies Table and there you see these three icons when you click on the third icon it will show you the records in that table okay so it ran this select query and it showed you all the record and if you look at these records they look exactly like this because I have imported data from this file only and we'll look into how to create the the database but in the initial uh few videos we will be mainly focusing on querying the data because as a data analyst or data scientist most of the time 80 90 of the time you are mainly querying the data you're not updating or creating tables things like that so we are focusing more on the query part and here as you see that you have one two three four five tables okay in the Excel file also you had this file five tables and in within a table all these entries that you have those are called rows and these are called columns so you can see when I run this select query you saw different rows and these are different columns so movies is a table movie actor is a table Financial is a table there are total five table and that makes up a movies DB which is a movies database so database is a collection of table a table is nothing but a tabular data which means rows and columns folks it is as simple as that if you use Excel you will immediately get it I know you all are smart enough now you might be seeing uh smaller fonts here so if you want to increase the fonts what you can do is edit preferences and in that you can go to fonts and colors and here you can increase the font size so SQL editor is basically this thing this thing that you are seeing here is SQL editor results grid is the results that you are seeing here okay and you can just increase the fonts to whatever size 18 20 24 whatever okay you can also do control scroll up and that will also increase the font size now you already got a taste of the first query your first query ever which is a select query and when you say select star from movie Devi dot movies it will return all the records all right in the next video we are going to dig further into this select query in the last video we saw our first SQL query which is Select star from moviesdb.movies star here means all the columns let's say you are just interested in title and Industry column you can do that by typing in the name of those columns separated by comma and when you run this it will show you only two columns when you work in Industry you will see tables which will have 30 40 columns so if you need only two columns it is recommended you retrieve that much because that can help with the performance the syntax here is you will write select column names then from and then the name of the table now we are writing movies DB because we have multiple databases movies World Etc if you want to remove this and just write movies what you need to do is one of the following options you can do use movies DB if you do that what this use Clause will do is it will tell SQL that this is my default database so whatever table I am referring to I mean this particular database now another way of doing this if you don't want to use this is you can say right click and set this as a default schema and you will see that this becomes bold which means it will now use this particular database as my default but I want movies DB as a default database so you can right click and click on set as default schema and in that case when you do this you can run this queries easily you don't have to say movies DB dot movies now let's go back to our original query which is Select star and you will notice that we are seeing two types of columns here one is text column which is title industry Studio the other is numerical columns release 0 IMDb language ID Etc numeric means these are numbers in this particular video we are going to query our database using text query which means let's say I want to get all the movies from Bollywood industry you can use something called where Clause so you will say where industry is equal to Bollywood in double quotes and when you run this it is showing you only Bollywood movies now see only Bollywood movies and in the bottom output panel it will show you the last queries result statistics so he here it says it returned 18 rows and it took this much time to fetch that data so that is the purpose of this bottom panel that you see below the result script right so to understand the anatomy of this tool there is an SQL query editor there is results grid and there is this output panel you can do the similar thing in regular Excel file by doing your regular data filter so in in Excel if you go to data and let's say if you type in filter here I can set those filters I can say Bollywood okay and it will return me 18 of 39 records see total 18 records and here also if you count this these will be total 18. now by the way there is something called count so if you want to return the count of Bollywood movies you can say count star and it will tell you the count see 18 so there is 18 Bollywood movies how many Hollywood movies so so I'm here comparing what you do in Excel with SQL so you're getting an idea this is very similar so in Excel when I say filter by Hollywood and I see total 21 out of 39 records I will do this here Hollywood nc21 hooray correct and you might have noticed that so let's let's print all the columns see all Hollywood movies now you notice that here this is a case insensitive search me means in the database my Edge is capital but in my query I am seeing I'm saying at small and it still works because it doesn't matter what is the case it's gonna always work let's say you want to figure out how many Unique Industries are there in my database for that you can use this Clause called distinct okay so select distinct industry from movies and when you run this it will say Bollywood and Hollywood are Unique Industries in my database after this thing you need to specify the column for which you want to get unique values the same thing you can see in Excel by the top filter see here the list that you're seeing is exactly the distinct query result now let's say you're working in this company called IMDb you might be aware about IMDb website which is the movies website okay and this website has this advanced search option where you can search movies by their title rating and so on I want to know all the Thor movies which are released so far I can type in in title Thor and I can search and I will see all those amazing Thor movies imagine you are working in IMDb as a database engineer or a data analyst and when on the website some user is making this query from website it will make a call to a backend that backend could be written in Java C plus plus python hotel in whatever that language is and when in backend you know that user is querying all the movies which has Thor in the title somewhere it will make an SQL query into a database and it will retrieve all these records see these are all the records which a school query can retrieve and the SQL query will look something like this you will say select star from movies all the columns where title is like Thor and you say percentage percentage percentage percentage means before Thor there could be any string and after that there could be any string in the title if Thor appears somewhere in the string give me those movies and you will see that I get these three movies okay and this is called a wild card search now since all movies are starting from Thor even if you don't give this initial percentage it's still gonna work because this means all the movies that starts with Thor followed by any character any number of time percentage means that now let's say you want to see all the movies for Captain America and if you search for America like this you won't see any movie because usually Captain America movie starts with Captain and then America so you can do something like this see now you are saying all the Captain America movies this is amazing right like now you you have been using IMDb website and now you're getting a feel of what's going on inside and that makes you realize how powerful SQL is okay let's print uh the original query here again now in our data set you will see that some values for Studio are blank and I want to print all those movies which doesn't have Studio this could happen because of data collection error we have some issues in the database this is perfectly fine you will always see a real databases having null values data quality issues and so on and as a data analyst you might want to figure out which movies have Studio set to empty and the empty string you can set it by double quote and see I have three movies for which I don't have any Studios I want to share a quick tip on how you can save these queries for your future reference first of all you can have multiple queries in this same SQL editor and you can execute them one by one by highlighting them with your left Mouse click after that if you want to save these queries for future reference you click on the save icon you can just say SQL course just give some name right this is chapter 2 chapter 3 whatever and you save it and when you save it uh next time let's say I want to open it let's say I closed it I closed everything next time when you want to open it you can just go to mySQL workbench click on this icon and go to that folder where you saved it and say queries chapter SQL course chapter two this this was a file we saved last time and this way you can retrieve those queries and you can again select and execute that you can follow this guideline throughout the course whatever you want to save you can save it in dot SQL file or you can create a Google sheet document and just kind of keep your notes there so that was a quick tutorial on how you can do text query using SQL throughout the course you are going to see a lot of exercises so folks make sure you work on exercises because learning SQL is like learning cooking if you just watch 10 videos on YouTube where people are making paneer tikka masala you're not going to learn politika Masala right you have to take the pan cut paneer cubes and start trying it out that is the only way you can learn cooking similarly say you want to build this bright future as a data analyst data scientist you have to practice and you can see SQL is actually not hard all right once again I am on IMDb website and let's say you want to see all the movies which has rating greater than 9 or equal to nine you can go here and search and it will show you all the amazing movies greater 9 rating means these movies must be good how do we do exactly same thing in our mySQL database so what we are going to do here is again we will write where clause and we will say IMDb rating greater than equal to 9 this is how you specify that and when you run this query it will show you the four movies which has greater than nine uh rating including nine if you don't do this this means greater than 9 it will not include these two movies which has nine point zero rating and you will see only two movies in the search result you can do similar thing in an Excel file by going to data clicking on a filter on IMDb rating say numeric filter greater than or equal to 9 and you get this if you just do greater than 9 you get only two movies correct so you will see a lot of similarity between these Excel operations and MySQL here you can use the other operators as well such as less than this is less than less than equal to nine so let's say all the movies which has less than 5 rating these movies are something you don't want to watch you can get it using this operator now let's say I want to get all the movies between 6 and 8 rating one way I can do that is I would say any rating that is greater than 6 and so I can use this and operator I will say and you know maximum rating is let's say eight when you run this see you see all the movies between 6 and 8 rating so you can use this and operator but the better way is to use between operator where you will say I am DB rating between 6 and 8. and that will give you the same result but this is little easier to read this includes both six and eight now we looked at and operator there is another operator called or and it is used in scenarios where let's say you want to retrieve all the movies in the year 2022 19 and 18. so how do you do that you will say IMDb rating is equal to 2022 oh sorry release here actually release your 2022 this shows you only 2022 but let's say you want to get more so and some people the way they write SQL queries is by using this type of format okay so it's up to you how do you want to do this part or you can say release year is 2019. and it shows 2019 and 2002 you can also say or release year 2018 now you can have five such years distinct years and you want to see all the movies the better idea is to use in operator so I will do now the exactly same thing using in operator right so you want to include 2022 19 and 18 you will say where release year in you specify all your years okay so 2020 19 and this gives you exactly same result you can use this in operator for text queries as well basically you want to it's like select Clause with where condition and you are having couple of or conditions so these are all okay so 2022 or 19 or 2018. you want to see all the movies from let's say Z Studios and Marvel Studios you can do like Marvel Studios and obviously this should be Studio appropriate column name and the other one is let's say Z Studios and you will see movies from both of these production houses now let's go back again to the IMDB rating and you will notice that uh there is one value which is null here okay this annual means null for a numeric column null is a valid type null means we don't have the value available and when you want to retrieve the records which has no IMDb rating basically you can say where IMDb rating is null and see it shows you only one record you might have humongous database and you want to get let's say all the records which doesn't have IMDb rating due to whatever reason data collection error or maybe movie is released just now you don't have any rating yet you can do opposite of that where you want to retrieve all the records which have a valid value and you can use this not operator for that you're saying give me everything which has some value okay so make sure you make a wise use of is null or is not null Whenever there is a situation to use them now when I print all the movies with IMDb rating let's say I want to print IMDb rating of all the Bollywood movies okay so I will say where let's say industry industry is equal to Bollywood this case insensitive of course so you see all the Bollywood movies but now you want to order them you want to see the highest rating movie and then the next highest how do you do that you can use a clause called order by and you can order by IMDb rating okay so it will order by that particular column and you see it started from small value and going all the way up see these are the rating this is the worst rating ever then 72.2 7.4 ascending order so by default the order is ascending if you want to go by descending order you can specify d e s c that's a specifier that will tell you first print highest rating the next highest and so on the default is ascending and that is specified by this keyword called ASC and let's say I want to see only first five highest rating movie when I do this it is showing me all kind of movies I want to see only first five let's say you have a business manager who is coming to you and saying hey Mr data analyst can you give me a report or a CSP file with top five movie ratings right top five movies with the highest rating you can use a class called limit whenever you say limit it will retrieve only those many records okay see limit just those many records and you can use this data export option So when you say export data movies DB my rating movies by rating let's say that will be the file name and you can just maybe save it in a downloads directory it got saved and when you look at your download directory here you notice this new CSV file movies by rating and this has see all the movies with the highest rating the top five this is very common use case in the industry where a business manager comes to you ask for a report you run some query export it to CSV file then you compose an email say hello Mr business manager here is the report that you are looking for I ran the same query but this time for Hollywood movies and let's say I want to start with the second movie I don't want to see the first movie there's this thing called offset and the offset starts with zero index so this is zero Index this is one so let's say you want to see all the movies starting from second highest rating movie till next five movies you can do that by saying limit 5 and offset one because see this is in index 0 this is index one and when you do that see Godfather these two movies have 9.0 rating so the order might be swept but don't worry about it you can do let's say you want to start with Inception and that will be offset two three four right so four so there will be times where you have to use limit along with offset that's all we have for this session make sure you work on exercises because the secret of getting things done is to act just by watching this video only you're not going to learn anything you have to practice and for that reason we have given you a lot of exercises along with the Solutions in the next video we'll be talking about summary analytics related queries foreign [Music] videos we looked at count star function which can give you the total record count in a given table or for a given condition count star is an inbuilt SQL function now you may wonder what exactly is a function let me explain you by giving the example of a computer on the computer I have various Keys like the power key the LED key USB I can plug USB so these are inbuilt features of a computer I can turn on or turn off monitor all right so these are inbuilt features of a computer and these features are turning on computer turning off computer turning on LED and so on similarly in SQL there are various inbuilt features count star is one of them and the way you call this inbuilt feature also known as function is you will write the name of the function then you will have bracket inside the bracket here we have star so this is called an argument in the function in the future we will see lot of functions where we will have multiple arguments all right I hope the understanding is clear let's move on in our video now now we are going to look into more of these type of functions which allows you to do basic analytics see you are trying to prepare for a data analyst or data scientist career you will be using all these functions very often when you get a job the first one I want to cover is Max let's say I want to get the maximum IMDb rating for Bollywood movies by running this query it will show me the max similarly there is a mean function which will show you the minimum rating among all the Bollywood movies let's say Marvel move Studios is releasing all these awesome movies and you want to know the average rating of the Marvel movies you can use this function called AVG and as you see this when you type the autocomplete will give you suggestions okay and you can use those suggestions and here I am interested in studio equal to Marvel Studios correct that's what it is and this shows me the average rating of the Marvel Studios now when I printed average it's showing me too many decimal points and if I want to restrict it to only two decimal points I can use this function round I can say around it to two decimal points okay and after dot now you will see only two decimal points one thing you're noticing is this header column header is getting very bigger and in SQL you can Define your custom column Header by saying this as AVG for example evg rating something like this and see it will print that now this is useful in scenarios such as let's say you want to print min max and average rating everything for Marvel Studios the way you do that is you will say select Min IMDb rating as Min rating and then you do the same thing for Max so I will say Max IMDb rating as Max rating and then I think this is average and when you run it see it gives you all these ratings now I'm going back to my original query where I printed number of movies in Bollywood I can print it like this and if I want to print the number of moves for Hollywood I can print it like that but what if I want to print both the numbers at the same time I can have say five different Industries in my movies table and I want to have industry name and account industry name and account something like this see what if I have Hollywood let's say Hollywood has 18 movies and Bollywood has 10 movies and Dollywood has there are all kind of Woods has lesser five movies how do I do that for this you have to use something called Group by and the way Group by works is you will say select industry because industry is the name of the column okay and count star from movies and by the way sometimes I'm using small case uppercase some people use uppercase for the keywords which are special SQL keywords so just select from where Etc both of that works okay a small case uppercase works it just sometimes it's useful to see the keywords in uppercase and here I will say Group by so the group by Clause will allow you to specify on what criteria you're grouping things so here I am grouping things based on industry and when I run this see Bollywood 18 Hollywood 21 let's say you want to group things by studio and Studio again this this has to be the column name okay here let's say Studio I need to specify the studio in in my column only then it will work so let's say Marvel Studio has eight now as you can see here the count is in a random order but let's say you want to sort the result based on this count what you can do is you can say count Star as CNT something like that some sort name and you will say order by CNT descending order and then you will see the studio which has the maximum number of movies here it is Marvel Studios there was one Studio which was blank and it has three so that's probably a data error now let's say you want to print the industry the movie count and the average rating in that industry you can do that so previously if you remember we had this particular query where we were printing industry in the account now the additional thing you want to print is the average rating so what you'll do is you'll say this is the count and average IMDb rating let's say as average rating so see the average rating for all Bollywood movies is 7.6 and it is 8.16 if you want to run this you can round it to decimal point or let's say one decimal point and now I want to let's say print average rating by Studio you can do that again you will do Studio here and you will group things by Studio and you want to order things by average rating right so you will say order by average rating descending order meaning the display the highest value first let's see how this looks here you will supply Studio because you are you are just selecting the studio so you can just say studio and average rating as average rating okay so let's see what this prints see it is printing the average rating of all these different Studios now here I am seeing Universal Pictures and Universal Pictures like two two are repeated and this is because of a data error so if you see if you just copy this field okay you can right click and copy the field you will see this and if you just copy this field right click in this see you see this extra space so many times in databases you can have these kind of issues and we need to fix those problems by updating the the records anyways but you are seeing how the average rating is being printed here and let's say you're seeing this studio which is blank and really that's a data error so you want to just uh skip that part the way you can do that is using our where Clause so you can say where Studio not equal to this and in that case that that particular result will be cleared out now friends don't try to memorize all these functions there are so many functions what you need to learn is the art of Googling Googling is the skill that will help you rise in your career so let's say I want to know all the SQL functions I can say SQL inbuilt functions and you will see so many uh good websites such as W3 schools for example here they are talking about SQL Server function but let's say MySQL and build functions and see on this website you will see all this list of function string functions and then numeric function we looked at say min max all of that right so here you can just practice on this website you can also go to mysql's official documentation where they have listed all the operators and all the functions so for example we looked at AVG function here it says Returns the average value and you can go and read the documentation and try out some of the queries that they have specified here so once again don't try to learn all these SQL functions or memorize it I even I don't remember most of the SQL functions whenever I need to use them I will just do Google okay for example I want to find outliers in my data set and I want to use standard deviation I will say MySQL standard deviation function and immediately it says STD Dev all right so make sure you focus on this art of searching or art of Googling that is the key for this success foreign [Music] let's say you want to print all the years where more than two movies were released how would you do that this is clearly a group by operation because you are grouping your rows based on the release here and you're getting a count so let's write the query we'll say select release year first so you want to print a release here and the count of movies so you will say count star from movies and group by release year okay and when you execute this you get the account pretty straightforward you can also sort this by saying that okay I want to order by count and I will just say count Star as movies count so that I can refer it easily in my order by clause and descending order when I run that pretty straightforward so this four years were the years where my movie count was more than two but this is showing you all the results let's say you want to filter all the results right so I want to say where my movies count is greater than two so you can probably use where correct you will say where movies count is greater than two but when you will run this query it it's not gonna work it will say unknown column movies count in where clause and the reason this is happening is the way SQL will execute all these operations is it will first execute from operation okay so it will say from movies I want to select bunch of Records then it will execute where then it will execute group y so now movies count is not available until Group by is executed but where is executed before Group by so when where is executed it doesn't know what is movies count that is the reason it doesn't work luckily there is something very similar to where which gets executed after Group by and it is called having okay and in the end you Execute order by so let me just move this after Group by and instead of where I will just say Harry it is very straightforward and see it's gonna work now so you need to remember this order it's first from then where then Group by having an order by having is used mainly with Group by operations okay so when you go work in the industry remember this thing having is mainly used with Group by but it can be used without Group by two but the main use case is with group y and another thing that is different with having and where is that when you have where see let's say I want to get all the movies IMDb rating is greater than six in the where clause if I am using a column that column doesn't have to be in my select statement so let's execute this sit still works but in having whatever column you are referring to has to be there in the select so if if I just say Okay IMDb rating greater than two whatever that's not going to work because IMDb rating is not available inside the select Clause most of the time you will find that the column that you are using in having Clause will be an aggregated column for example here movies count is some kind of aggregation correct or a derived column so these are the use cases that you will see in the industry so far we looked into movies table today we are going to move to a different table called actors and we'll discuss calculated columns or derived columns when you look at this data you have actor name and birth here and for your analytical purpose you may want to get an age for each of these actors let's say you want to do analytics such as what is an average age of an actor in Bollywood or Hollywood who is the youngest actor in Hollywood Etc having age will be very helpful in that case and the way you derive the age is you will get a current year and then subtract birth here from that now to get current year in Excel there has to be some formula and I don't know what that formula is so I can go and ask my dear friend Google so you can just say Excel get current year and you will find this formula and you can just use that okay see I use that formula and when you do this you get the current year and then from that current year you can subtract the birth here okay okay I think there was some issue so let me do this again so I will say this is my current year I will double click type in that year minus birth here and that gives me the current the age of that actor and you can just click on this and see you see the age of each of these actors can we do something similar in MySQL so I will now replace my Google search with mySQL getcurrent here see what you type in Google is a very important skill this is the skill that can give you super powers so do not underestimate that good programmers or good data scientists and analysts they don't remember the syntax they they know the art of how to search for the answers and I get this article let's say and see there is a function called year which can take date as an input and it will give you the year for example in this case it will return 2017 but I don't have a date I I want to give a current date how do you get current date well this is the way when you use a function called Cur date it will give you current date and then you can take your portion from it so I'm going to copy this into MySQL so I will say select current date and it gives you the current date and when you wrap it around here when you call it from your function it gives you current year okay now all you need to do is once you confirm that this Returns the current year you can say star from movies DB dot access we don't need to type moviesdb because it's a default database this will return all the columns and in addition to that you want let's say this as a new column so now you've got current date and you can just say minus birth year as age you can say as age and then it will print that as an age here you can give any column name this is up to you but see getting the age was as easy as just typing a formula and using that in a select statement now let's go to a different table and that table is financials we are going to use some more calculated columns for financial tables when we look at this table uh you let me show you this table in the Excel file and what this table has is the movie ID for example 101 is kgf chapter 2. and in the future chapters we'll look into how you can join two different tables using left join right join Etc but for now using ID you can figure out which movie is that right 102 is Doctor Strange and for one or two which is Doctor Strange they made 954 million dollar in box office as of the day that this data was captured and their budget for making movie was 200. now let's say you want to calculate the profit columns how do you do that well it's very simple right it's it is profit is equal to this minus budget and we will be able to do same thing using the formula in MySQL as well so what we'll say is Revenue minus budget as profit so star means all the columns if you want to print selected columns you can specify their name separated by comma but see this worked fine now let's say you want to print the revenue of all the movies into a single currency because right now these numbers that you're seeing they are having this dimension of currency and unit so you can't compare these two numbers you can't say okay this movie made more profit than this because this is in USD there are two in millions whereas this is in billions of INR okay so if you want to do apples to Apple conversion you have to convert everything into a single currency and everything into single unit okay but that's a separate discussion for now let's say your purpose is to convert everything into INR so that the currency that you see here is the INR currency and how can you do that well USD to INR currency conversion ratio right now is 77 it is changing every day if you are working in any organization and if you are doing currency conversion you will use the live spot rate the currency conversion rate as of the as of the date when this data was captured but since this is a tutorial I want to keep things simple we'll just assume that USD to INR conversion ratio is let's say 77. so if you want to convert 954 Millions into Indian rupees you will say 954 into 77. simple okay so how do I have a new column called let's say Revenue INR okay so I want to have a new column called Revenue INR and have every revenue is in INR we will use something called if condition so the if condition the way it works is this you will use if condition and you will say if currency is equal to USD then take revenue and multiply it by 77 and if it is not USD just print Revenue okay and let me run this so what happened is if the num currency is INR then it is keeping its same 12.5 12.5 but if it is USD then it is multiplying this number with 77 and you can verify that by using uh some tool called calculator so let's say 954.8 c73519 okay so the way this if condition works is in if the first thing you have is a condition the second thing you have is what do you want to do if the condition is true and the third thing is what do you want to do if the condition is false it is as easy as that all right now let's print all the revenue numbers all the revenue numbers into millions currency ideally if you want to let's say find out which movie made the maximum money in in on on box office uh across the board you know all the Bollywood Hollywood movies and you want to compare okay which movie made the most money in that case you have to convert Revenue into single currency and then into single unit and that will make our query little complicated and we are in a stage of the course where I don't want to make things too complicated therefore since you learned currency conversion I will now do unit conversion okay I will not do currency conversion just the unit conversion so for unit conversion what you have to do is let's first find out how many units we have so you can say select distinct unit from financials okay do you have any memory of what this will return [Music] it will return the number of unique values that this column has so billions millions and thousands we have three values now Millions is a very popular currency so what I'll do is I will convert everything into a million so I will have some condition here and I will print Revenue in millions because that's very popular how do you convert billions into Millions so the to convert billions into millions you have to whatever number you have let's say you have 12 billion okay what will be the millions it will be 12 000 which means you are multiplying 12 with a thousand so the condition is if the value is in billions you multiply your Revenue with thousand but if it is Thousands let's say you have these many thousands you you divide that by this many thousands okay these many thousands mean meaning this is the actual number so these many thousands is actually 4.567 million okay so the formula that you will use here is revenue divided by thousand okay this is like like a simple math Okay so if condition is useful when you have binary condition previously we had F USD then do this otherwise do that but here we have more than two conditions to check if billions then do this if thousands then do this and if the unit is in millions then just keep things as is okay so how do you express that you can do this using something called as KS statement okay now how does case statement work you will say MySQL case statement you're using your most powerful weapon folks Google search and the way case statement works is you will say case and and you specify when some condition then this when some other condition then this else then this okay so let's do this so you'll say KSN so whatever you get from case that output will be stored in Revenue million what is your case okay my case is when currency is let's say thousands when currency is thousand you what do you want to do then you want to divide Revenue by thousand correct and then when currency is billions then you want to multiply Revenue by 1000 okay let's run this you can select this actually comma is not needed okay there is some problem so not currency actually I have to check unit okay I mixed mixed up two things here okay so see when it was 12.5 it converted into 12 500 which is fine but I'm seeing blank here because for Millions I did not do anything so you have to specify your default case which is else if the unit is everything else or you can also say when unit is equal to Millions that that is probably the right way when you say unit is equal to Millions than this this is probably the right way but now that we already know that we don't have any more units you can also do this else both are same okay and now all the numbers you see are in millions of either dollar or INR okay as I said the right way is to convert now this number into USD or INR but maybe you can do that it will make your query look little complicated all right so I hope you learned few uh new things here uh I'll see you in the next video and make sure you work on the exercises [Music] in our movies database we had multiple tables and in this lecture I am going to discuss why do we need multiple tables in first place I will be using a different data set of grocery store customer transactions so when you go to a grocery store and buy things they have a POS system and in that system you could have transactions that might look something like this so there is a transaction date which item you bought the item category price per kg order weight name of the customer customer email customer address and so on now you can have all the data in one place just like how I'm showing you here on the screen where every single information that is needed for a given transaction is present in one record okay so you have price you have item you have customer you have everything and this works fine but this has some disadvantages number one is information is repeated see Peter Pandey his information is repeated let's say Peter Panda has a thousand records you're repeating that information in the database so that's a disadvantage number one data redundancy second disadvantage is tomorrow let's say potato prices go up and now you want to change from 60 to 80 and if you have another other records you have to upload all of them right for example here let me take example of broccoli brokly price change from 120 to let's say 150 tomorrow okay I mean you cannot change it per transaction but I'm just saying in the future transaction you you need to have that different price you need to have price associated with that item somewhere and the third disadvantage is the data is not organized properly so let's look at how it will look if the data is organized into multiple tables so I will first take customer information and put it in a customer table so there's customer name email customer address and we will give a unique ID for that record so this is the unique ID for that customer we will do the similar thing for items too item name item category and price okay and we might have here also I'm just giving a simple example usual in databases they will have a year or date because the prices are changing constantly and once you have this our sales table will look very neat and clean see it will have item id customer ID and Order weight so what is this record okay let's look at 21 item so 21 item IDs white potato vegetables and 60 okay so you can replace that 21 with this this is your item information or product information okay so that's item number 21. and 401 is Peter Pandey okay so 401 is Peter Pandey so you can put four on here so see now I'm just putting this information for a reference but you saved a lot of space in your database also the data organization is much better when you want to check prices for items you just go to items table and that's it you want to change let's say a customer comes and say they oh I change my email ID all right fine go to customer table and this table will have very less records so you can just change email ID here but if this customer information is in main sales table which can have thousands of Records changing email ID everywhere might be a painful process when we talk about our movies database exactly same principles applied let's say I want to have all the information in one table one of the information I have is actors for example in movie kgf there are two actors yes and Sanjay death I found I want to put that information here how do I put it okay I can do like Yash and his birth here right like actor birth here okay so let's say his birth here is 1986. I can do this but where do I put the second nectar do I have actor one and then actor two maybe do I have do I have information like that but that's not good because I can have more actors right like if I put Sanjay dutt like this see whatever so then information organization becomes difficult if everything is in one table for that reason we have this concept of multiple tables and especially for actors what we do is actor to movies relations is Manny to many which means one movie can have multiple actors and one actor can participate in multiple movies therefore we created a separate table called movie actor this is like a mapping okay 101 is kgf2 50 is yes 101 again is kgf2 51 is Sanjay dutt so this table will allow you to do the mapping and in the future videos we will see things like okay print me all the movies along with their actor names and in order to do that we will be using something called joins [Music] thank you let's say a business manager comes to you and asks you to generate a report where there is a movie title and there is budget Revenue unit Etc columns now this requires joining two tables we have movies table which has movies titles we have Financial table which has Financial details and the common link which joined these two is movie ID for example movie ID 101 here which is kgf2 so using this movie ID we are going to join these two tables and generate a report and we will look into different types of joints so let's get started the first join we are writing is called inner join and the way you do that is first you will specify which columns you want so I want movie ID title all these columns and to join to a different table you will say join financials on using which column you're joining well you are joining using movies dot movie ID is equal to financial thought movie ID this is how you specify the common column okay so you save on and then this dot this column is equal to the right table dot column when you run this see I'm getting an error here at the bottom if you see column movie ID in the fill list is ambiguous what this means is see now since you're joining two tables these columns could be coming from any table now Revenue currency Etc it did not give error for those columns because those columns are not ambiguous a revenue column is available only in financial table it is not available in this table so SQL didn't have issues figuring out which column you're talking about but movie ID column is available in both the tables and when you say movie ID here it doesn't know which column you're talking about so what you have to do is you have to say movies dot movie ID and now you'll see a beautiful report of movie title and budget Etc which you can click on this export and you can export it as a CSV and send it to your business manager now one common practice that people use when they are using joins especially is to make the query shorter they use the table abbreviation and the way you can do that is after movie space m space f and once you do that now you don't need to write the whole table name here see I can just say m here and I can just say F here similarly here I can just say m okay and that makes your query shorter the join that we perform here by default is a inner join so MySQL by default if you don't specify any keyword and you just says join it is an inner join now what is an inner join so I will take an example here so let me show you some presentation so in this presentation I have given few records not all let's say you have these two tables okay and these these movies show an Inception they are not available in financial table so they have some movie IDs but those movie IDs are not available in financial table similarly Financial table has some records some movie idea let's say 4064412 these are not available in our movie table okay and you will actually see this is the case let's look at movies table here okay here we have this movie Inception one one two if you look at Financial table you don't find a record called one one two because of data collection error or some problem that record is not available and this can happen in real life databases similarly the other record Soleil 106 is not available here see you look at the entire table that record is not available so there are some records in movies table which are not available in financials there are some recording Financial tables such as last two record 406412 these two records represent movie IDs which are not available in movies they will see movies don't have any 400 type movie I do records so when you now do inner join what happens is it will only take into account the common records it's like if your Venn diagram it's it's an intersection okay and that's exactly what happened when we ran this query when we did inner join you would not see any show layer Inception just look at this whole list you don't see sholay or Inception because it is available in left table but not right table similarly in a financial table which is my right table I had 406 401412 but I didn't see that see check all this movie ID those are not available okay what if I want to print all the movie titles I want to see sholay an Inception even if it doesn't have budget and revenue numbers if you want to do that you will do something called Left join left means see when you say from something that becomes your left table and the second one which is financial it becomes your right table and when you run this you will notice DC there is an Inception but it doesn't have budget Revenue Etc similarly there is a record for sholay it doesn't have budget Revenue Etc and it will show now okay so pictorial representation of this will be like this it's like a left all the records from left table including the common records you might have guessed what will happen when you join when you do a right join in right join you will see a records all the records on the right right hand side table which is our financial table but Shola an Inception will not be visible so let's just try that out when you do right join here and run the query see these two so you got 406 and the other one but you didn't get a title now you you'll be wondering why it's not showing movie ID you have any guess okay I think you figured it out because here we are saying m dot movie ID I want to get movie ID from movies table if you want to display the movie ID if you just change this to f during your right join and run it you will see those IDC 406 and 4 1 2 and the final join that we want to cover today is full join where you get all the records okay so the picture gives you a complete idea on what this is about and the way you do that in MySQL is there is nothing like outer join see if you do outer join it says outer is not valid at this position and by the way this is the Syntax for MySQL if you are using Oracle or SQL Server the syntax might be different SQL is a standard language that we use for different databases but these different databases has a slight tweak in terms of syntax so you need to keep that in mind so what do we do now all right when we did this we got so when we did the left join right so left join I think it makes sense you do m dot movie ID you got this when you did right join okay so let me just put these things into this line and now when you do the same thing but with right join what happens so for right drain I will use f because I want to display those movie IDs and I want to do right join here okay and see this is right join this one is left join in the left join you you see certain records which are available on left but the budget revenue is not available in the right join you see record which has Financial details available but not the uh movie title available right now let's call Union on these two so you had this two query in between the query when you write this keyword called Union Affairs studied set theory Union is just a union of those two rows okay when you use Union keyword you have to make sure that this number of columns and their column names are same if not then it will give you an error okay so let's run this and now you are seeing records such as sholay which is available on the left hand side solar Inception but you see records these records as well okay apparently there are like three records in the right hand side table I said two but that is third one which is one on four so this way you are getting all the records and this is how in MySQL you can perform a full join you should take a screenshot of this picture because it will be very helpful it just summarizes all kind of joints that we have seen so far now I want to mention one more thing which is these three joints left right and full joints are also called outer joints so whatever is not the inner joint it is also known as outer join which is a common term for this left right and full join so you can say left outer join or right outer join or full outer join as well and the MySQL syntax supports the keyword so you can say right outer join and that will give you the same result you see same way if you have left versus left outer join that gives you the same result but just to save some space we don't use that outer when you say left join it is implicitly assumed that you are talking about left outer join now one last thing I want to mention before we end this lecture is something called a using Clause so here most of the time you will be using on because in many cases this column name could be different let's say it is M ID so when you use on you can explicitly specify the exit column names from both the tables but in our case both the tables have common column name movie underscore ID so you can use something called using movie ID and the reason we use specify bracket is you can actually join based on two columns also okay so it's not like you have to just specify one column using which you can join you can sometimes join two tables based on two columns so here you can specify whatever is the second column but anyways in in our case it's just one column and when you do that now you don't need to specify this amp because it will figure that out in a smart way so let's see what happens so I have a right join and previously remember when I was doing m dot movie ID I was not seeing the movie ID for both of this but now I'm seeing it because it is smart enough to figure out from where to take movie ID similar thing will happen when you do left join when you do left join and let's say solely right see it figured out the movie ID so this is a convenient syntax you have to write less code but this requires that the column you're joining on has a common name which is movie ID in real life databases you will see that you're joining two tables but the actual column names are different in that case you don't have any other way but to specify the exit column name using on here now I mentioned this previously before also that you can join two tables using multiple columns also all right so let me just go back to my previous query and here I was joining based on one column but if you have another column let's say another column you can do join based on another column also like this okay you can ask your dear friend Google also see in Google I typed in MySQL join on multiple columns and immediately it will show your page with a syntax see on this end the other date and we are going to uh look at more complicated joints in the future videos but this is something that you need to remember that's all I had for this lecture and once again learning SQL is like learning swimming if you just watch two hours of swimming videos on YouTube you're not going to learn it you have to jump in the poll and get yourself wet so I guess you're getting what I'm I'm trying to say here make sure you work on the exercises [Music] [Applause] [Music]

Channel: codebasics

Views: 24,090

Keywords: yt:cc=on, sql, sql tutorial, sql tutorial for beginners, sql for beginners, Codebasics, mysql tutorial for beginners, sql select statement tutorial

Id: Rm0xH2Vpfi0

Length: 86min 9sec (5169 seconds)

Published: Sat Jul 29 2023