CREATE, SELECT, INSERT - SQL Tutorial Series #1

Key Takeaways

what is going on guys welcome back to a new tutorial series on the neural 9 Channel about SQL or some people like to call it SQL about the structured query language the language that we use to work with databases and the data inside of databases the language that we use to select data to insert new data to create tables to connect them to apply constraints to specific Columns of tables and all that stuff and of course a lot of more advanced stuff as well and this is a fundamental skill for any programmer but especially for data scientists because we are working with databases all the time when you do data science for machine learning databases are at the core of it because data is at the core of it and sometimes you will find data in CSV files in Excel sheets but a lot of times most of the times I would argue you will find data usually in databases and this is something that you need to know about how to work with databases and especially with relational databases which are the most common databases and this is why we're going to to do this SQL or SQL tutorial Series so let's get right into it [Music] all right now for this tutorial series I would like to keep everything language independent and language neutral meaning we're not going to focus on one specific implementation of a database system we're not going to make this tutorial series about MySQL or about postgres or about sqlite or something like that we're going to learn SQL or SQL in general I'm going to jump back and forth between the two pronunciations because both are correct I like to usually call it SQL but when I say something like MySQL I like to say MySQL both are correct so pronounce it however you like but we're not going to focus on one specific implementation one specific database system we're going to learn SQL in general and most of it is basically the same in a different implementation so when I show you something in MySQL you will probably be able to maybe with some minor changes run the same thing in postgres or an sqlite so this is a uh for the most part this is a language or dialect independent dialect neutral tutorial I should say however we still need to go with an implementation because I don't want to just write the code in a text editor and never run it and because of that we're going to go with mySQL because it's a very beginner friendly very simple and how you install MySQL depends on your operating system you just have to go to downloads and follow the instruction essentially I think you're intelligent enough to figure it out for your specific operating system so if you have Mac OS just Google how to install MySQL on Mac OS on Windows I think the installation is quite simple I will briefly show you how to do it on Linux or at least on Ubuntu based systems now some of you guys are not going to like this because I installed it using snap so what you do is you basically say sudo apt install MySQL servers so sudo apt install MySQL Dash server and this is basically just the database server uh and then the tool that I'm working with here for this tutorial series is the MySQL workbench you don't have to use this tool you can also use uh D Beaver or you can use a data grip from jetbrains if you have the premium version or if you pay for it or if you are a student or if you have a trial version whatever use the software that you want I'm going to use MySQL workbench because it's just the the most convenient choice for making a tutorial I would never use this editor because it doesn't support Vim bindings but for the tutorial I'm going to just use the MySQL workbench and to install it what we need to do is we need to say sudo snap install MySQL Dash workbench Dash community in my case it's already installed so I'm not going to run that and then what you can do is you can run the service so sudo system CTL start MySQL or MySQL dot service in my case it should already be running so this this is not necessary and then what you want to do at least on Linux systems I'm not sure if this is necessary on Windows and Mac but what I had to do is I had to do sudo MySQL and then when I'm inside I had to set a root password so I had to basically let me just copy this here so that I can show you the commands uh like this I had to run this statement in the MySQL prompt so you say sudo MySQL and then alter user root add localhost identified with mySQL native password by and then you can choose a password I chose password here and the important thing now is that once you run this and then you exit from the MySQL shell what you need to do is you need to open the MySQL workbench and here you have your connection and you need to edit this connection so that the password that you're setting here um is password so you set this to password and then you can just click on it and here I have my prepared videos you're going to see what they're about in the future episodes but this is now the MySQL workbench connected to your database to your mySQL database and this is what you need as a setup here now I trust you to be intelligent enough and capable enough to figure out the steps in between if something goes wrong if you get some error message if you don't have privileges if uh things change a year from now which is likely to happen that you can Google your way around into the installation so that you get this running on your system whatever the system is so I don't want to focus too much on this I want to focus on SQL itself especially because this tutorial is not about MySQL but about SQL in general so I don't want to spend too much time here talking about the specifics of installing MySQL um yeah so the basic thing that we have here I already have a database here that I use to prepare the videos uh what you have here by default is you have some MySQL server with databases and if you want to see what databases there are by default you can just go and say show and then databases and then you use a semicolon to uh terminate this line to say okay this is now the end of the command and then you can click on this uh thunder bolt button here to run this statement so you can click on this and you can see that first of all and by the way if this is somehow not visible you probably have to to drag it from the bottom like this drag it up from the bottom like this you're going to see that this action was executed successfully this is what you get down below here uh and you can see here the result of running this command and in my case you can see I have this neural DB you will not have such a database but you will have information schema MySQL performance schema insists at least in MySQL this is going to differ probably between the different uh database systems that you use but this command shows you what databases there are now in databases what we have is we have data and the data is structured in rows and those rows are located in tables and those tables are in a database so the basic structures you have a database in this database you have different tables being somewhat connected or also independent in those tables you have different columns different attributes you could say and one row filling the values of these different columns is one data entry one piece of data in the database so again databases inside of those databases tables inside of those tables columns and rows and they can be connected so in this video today in the first episode here I want to talk about the very basics of SQL so the very basics of creating a database creating a table in that database defining the different Columns of that table and then adding some data into that table and then querying the data out of the table so going through the basic process of using databases uh so the first thing that we want to do is we want to say create database and then we can pick a name for the database so for example tutorialdb and always end the command with a semicolon so create database tutorial DB will now create a new database which you can also add here to make this uh less prone to errors is you can say create database if not exists and what you will notice here is that SQL is very much like the English language so you can just tell it what to do you say create a database if it does not exist tutorialdb and the reason you want to add this if not exists is because in this way if I run this command and I can also run a command by holding Ctrl and pressing enter so Ctrl enter will run this line and you can see it created a database tutorial DB I can now run this show database command again and you can see we have this tutorial DB down here and the reason we have this if not exists is because if I run this now again you can see that I get a warning but I don't get an error so it says okay the command was executed but this database already exists whereas if I remove this if I just say create database tutorial DB you can see I get an error that that tells me that it cannot create a database so this is just a nice way of not worrying about uh executing the command again if it was already executed so now we have this database but this database is empty we don't have anything in it and we can see that this is the case by showing the tables that are part of that database but before we can run the show tables command we need to actually say that we want to use this database as you saw we have five different or in my case now six different databases here and when we when we do something like show tables we need to specify which database we're actually using to see the table so we can say here use and then tutorial DB to say that we're actually using tutorialdb um you can see this executed successfully and now I can say show tables and the reason I'm writing in uppercase by the way is just because this is how you usually write SQL code there's nothing that prevents you from just saying show tables like this it's not wrong so you're not going to get an error if you write it like that but when you look at code examples you will always see the SQL statements being written in uppercase so I like to do that as well show tables and then you can see we have no tables here so tables in tutorialdb we have no entries here so what we can do now is we can create a new table and we do that guess what with the command create table and we can also again say if not exists so that I can run the command multiple times and it will not crash because the table already exists so create table if not exists and let's go with people for example so create table if not exists people and now what we need to do here in this create statement is we need to say okay what part what kind of attributes what columns are going to be part of that people table so when I have a person what attributes am I going to have for a person and for this we use these parentheses here and again we separate or not we separate we end the line or the command with a semicolon um and in between those parentheses here now we specify the different fields so for example we can say a person has an ID a unique identifier so I can say p underscore ID you can call this whatever you want you can also call it this if you want to so there's no convention here that you have to follow otherwise it won't execute but it's a good practice depending on what you're working on and what code base you're working with that you do something like P underscore for people underscore and then PID or actually P underscore ID would be enough in this case so what we need to specify now for this PID is what is it actually so a data type we need to say okay this PID is an integer and you can say that it's an integer by saying int or by saying integer doesn't really matter it's basically the same so now we have this PID which is an integer and now we can add some other information about it so for example we can say that it's Unique we can say it it's it's not allowed to be null and we're going to talk about these constraints in a future video but one of the constraints that we need to talk about here right now is the primary key so we need to say that this is a primary key and the reason for that is because every table or every row in a table needs to have a unique identifier and the primary key basically just says this attribute here is unique and it is not allowed to be null so it can't be empty and it can't have duplicates which means every single row is going to have a value for PID and no two rows are going to have the same values uh so because of that we have now a unique identifier which is called the primary key and this is going to be interesting and relevant when we have also foreign Keys later on referencing that primary key to make connections but this is something that we're going to talk about in one of the later videos in this series now we're going to just say okay we have this PID which is the unique identifier for a person if you don't want to call this ID you can also use something like social security number in this case we would use a different data type probably so we could say the social security number of a person is a text uh or maybe text is too large let's go with a character of size 32. so we have the social security number is a string of length 32 and this is the unique identifier because no two people will have the same Social Security number so we're going to stick with the ID for now but this is also a possibility now talking about the data types let me just briefly go through most of them or to the most important one so that we have this covered for the sake of completeness um a field can be an integer a field can be a character as I showed you so character would be a fixed size usually also faster than the alternative of the variable character where we have essentially a character but it's of variable size meaning the 32 indicates the maximum size but not uh the actual size so it's not like this is always going to be of length 32 it can also be of length 15 but the max size is going to be 32. and then we also have the text and the text is essentially uh for very long texts you could say so character when you have a small predictable size variable character when you have to have room for a lot of text but you don't need all the text necessarily and text when you have large text then we also have um enumerations enumerations are essentially um enumeration of values so we have a certain number of values that are possible for example we could say uh favorite programming language can be C plus plus can be Java or KB Python and this is basically like a string then but we cannot add something like go to this list and then we also have stuff like floats stuff like double and stuff like decimal or we can specify also the decimal places and uh what else do we have we have Boolean data types basically true or false values and then we have also date and time and date time and timestamps now the reason I'm going through this quite quickly is because I don't want to show you examples for every single data type we're going to use some of them as we go on with this tutorial series we don't have to go through examples for every single one of them now uh but essentially that's those are the most important data types so you can store different types of informations uh different types of information in a database so let us move on now with the name which is going to be a variable character of let's say side size 255. and then we're going to have H which is going to be an integer and then we're going to have height which is going to be float for example so this is how we create a table we say create table if not exists the table is called people we have four columns in that table and those are the data types and this is also a primary key now I can run this then I can say show tables and you can see we have a people table that is what we just created now this people table is empty and we can see that it is empty by trying to select everything from that table so there is a statement in SQL which is called the select statement and what you do essentially is you specify which columns you want to select so you say select pidp name PHP height or just P name pH or simply you can say everything so you can say select asterisk and then from because you can have multiple tables from people for example and this would give me all the data that is in the people table in this case we have oh did I change something right now let me run this again uh oh this one actually there you go uh so we have pidp name PHP height we can see the column structure but there is no data in here so we don't have anything here those are just null values um so what we can do now is we can insert data into this table and how do we do that guess what insert into then we need to say people and then we have two options either we're going to specify all the attribute values or just some of them because remember the PID has to be specified it cannot be null but I can't leave out all of these values so I can also say just a name and on and an ID and I don't need to care about H and height if I want to limit myself to certain Fields I have to specify which ones I'm feeling uh not feeling which ones I'm filling um in parentheses so I need to say okay I'm inserting into people a PID and a p name um and not anything else and then this is followed by the values keyword and then I use again parentheses followed by a semicolon to say what kind of values am I actually putting into the table now and I could start with something like id1 and name mic and I can run this and when I run this I can call this select from people select everything from people statement again and you can see I have id1 mic um I can also go ahead and get rid of all this and I can say insert into people values now if I do it like that I have to provide all the fields if I don't limit myself to specific Fields explicitly I need to provide all of the fields otherwise it's not going to accept that so I can say here to John and then um John is going to be 78. and he's going to have a height of 180 centimeters insert select and you can see now we have Mike and John with all the data um as I said since we have this as a primary key we cannot just specify everything else so I cannot just say pname p h p height and then omit this ID because in this case we would say we would get this error message that the field PID doesn't have a default value and it's also primary key so we cannot just insert without it uh yeah so that's basically the insert command now what else can we do we can also of course this is important I didn't mention this explicitly we can of course also insert multiple um multiple instances here or multiple rows in one command so I can say here PID again so this is a valid command again and then I can just list a couple of those those insert rows like this and of course we're going to need a comma here to separate them and in the end again a semicolon but this is how how you can also do it so you can say here three four five six seven eight and then we can say Anna we can say Bob we can say Kate we can say James uh Samuel and Lisa something like this now we can change the values here foreign like this uh yeah so we can run this and we can select again everything from people and there you go we have all the data now in the database which brings us to the next Point how can I not select everything but just specific rows I'm not talking about specific columns here because we said already how we can do that I can also say pname and pH to only get name and H as a result here but how do I select for example only those that are older than 40 years for example would make sense in this case this is where the where Clause comes into place so I can say select P name and age from people and I can attach here a wearer statement where the H is above and I can just use the simpler above or greater than operator here 30. and I can run this and you can see here I only get the entries where I'm above 30 or I can say 40 and then I'm only going to get those above 40. and this works also with less than this works also with equal so I can go and say equals 38 then I'm going to get only those that are exactly 38 years old I can go and say not equal 38 which is done like this as you can see and I can also say um that I want to have where H is not null because I think we have here if I run this again we have one instance where we have no h which is mic I can also say where P underscore h is not null and then I'm not going to get mic uh yeah so that is that what we can also do is we can also filter based on attributes that were not displaying so I can say give me name and age where the height of the person is above 180 centimeters there you go uh all right I think that's it one more thing maybe that could be interesting is uh we want to show for example combinations here so we want to say give me the name and age where the people are taller than 180 and their h is above uh 40 for example then we only get Kate and James or I can say or where the H is above 40 so in this case we would get uh what did we get here John was it John how how tall is John see yeah so even though John is not above 180 we still get John because he's above 40 and or means that uh yeah if we get when one of the two is uh at least one of the two is fulfilled we can also say xor to get only when either or so only when the height is above 180 or when the age is above 40 but not when both is the case so those are just a logical combinations here so that's it for today's video I hope you enjoyed it and hope you learned something if so let me know by hitting a like button and leaving a comment in the comment section down below and of course don't forget to subscribe to this Channel and hit the notification Bell to not miss a single future video for free on that thank you much for watching see you next video and bye