Tutorial: SQL Tutorial for Beginners | Future of Data and AI | Conference

Key Takeaways

[Music] welcome to getting started with SQL so there are a lot of great talks throughout this conference I would say SQL is one of the more basic topics or talks that we're going to be talking about but it's one of the most important ones so I want to preface by saying that this presentation is for complete beginners with no prior experience and it's not for those looking for a deep dive tutorial into SQL so I'm going to be doing a lot of explaining of Concepts and keywords and a little bit of programming and a demo but the majority of this is for completing beginners so again my name is Alice currently a data science analytics instructor a lot of my tutorials can be found on a dash of data that's my brand on YouTube or in GitHub today I want to be talking to you again about SQL and so in the past I've held all these roles that you'd see here the scientists data analysts and so on and in every single one of my jobs I use SQL and so what I did was I took everything I learned from the past 15 years and I put it into my book SQL pocket guide and this is the table of contents of the book today we're going to be focusing on chapter one The SQL crash course I take everything from that chapter and I blew it up into this presentation you see here let's get started so today first we're going to go through the sequel Basics an example database I'm going to get into a little bit of SQL code because there's a ton you can learn in just about half an hour and then we'll do a software demo and the reason I do this is a lot of times people will stick my presentations and they'll be like great I see slides with code but how do you actually write the code in the real world so that's why we have this short demo at the end so let's get started with the basics and we're going to start really basic we're just going to start with the term data so what is data it's information stored by a computer and there's lots of different types of data numeric data text Data a more complex data like sound images video okay so that's data so then the next question is what's a database and a database is this place to store data in an organized way so the way I like to talk about this is Imagine data is this Lego piece and then everybody's always talking about big data and a lot of data that's basically this huge pile of Legos and then I would say a database looks something like this so I'm really thinking to home organization and so I have a lot of home organization examples throughout here but a database is essentially that piece of furniture right there it's that cart where you can organize all your data in this really nice way so there are so many ways to organize data these are all different home organization tips from Instagram but you can see this is a really good analogy for organizing data organizing your Legos at home or organizing your data at a company there are many different ways to organize that data and there really isn't a wrong way it depends on your company and the situation and you can organize your data in a lot of ways just like these logos are organized in different ways that work for these homes okay so checkpoint data is information stored by computer a database a database is a place to store that data and there are a lot of ways to do so and two the two types of databases that people always talk about are SQL databases and nosql databases so let's start with SQL because that's what we're all here for SQL stands for structured query language and I want to focus on that language piece so imagine you have an app on your phone that's a birthday app it stores information about all your contacts birthdays in English in the English language you would say hey app when is my husband's birthday and it would return this value and if you are talking in the SQL language you would say select all from birthdays but person equals husband so it sounds kind of like a robot but because it is it's a programming language and then you would get that same result okay so now we know SQL is the language a database is a way to organize data a SQL database is data organized specifically into tables so we have a table right here it has columns it has rows and if we had a bunch of other tables and they're all connected to each other and they're one big family of tables that whole thing would be called a SQL database and sometimes in textbooks you'll see tables called relations and so this is sometimes called a relational database so a SQL database or a relational database so the same thing it's just basically this big group of data organized into tables and then you can talk to that database using SQL the SQL language so I could say select all from birth year and then that would give me information from that database okay we've been talking about a lot of theory but let's go through a real world example so imagine you had this app on your phone if you have an iPhone you see the messages app and you have a lot of conversations in the app so fun fact the data that is in the messages it's actually stored in a database so this is a database that you can access so I have a Macbook and an iPhone I can just go to my MacBook and then I can pull up this chat DB and inside this chat database there are a bunch of tables so there's a handles table that has information about all my contacts phone numbers and there's also a message table that has all the text messages that I've sent and received so let's say that we wanted to see all the messages that have been liked so if you're not technical you go on your phone and you can just scroll up forever until you see all the messages that have been liked but if you know the SQL language you go to that chat DB that is on your MacBook and you can write a SQL Bryson SQL code and it selects all the messages that have been liked and then it would return this so this is a real world example of how SQL is so close to your everyday life even if you don't know it okay let's summarize what we've gone through so far so SQL is structured query language a SQL database is made up of tables those tables are called relations and the SQL database is sometimes called a relational database so another thing I want to mention is that SQL databases require these predefined schemas so you can think of a schema as a plan or blueprint so basically before you start putting data into a SQL database or storing your data in there you have to first come up with a plan so you you got to think this is what I want my tables to look like this is what columns should be in there so if I was to put SQL into a SQL database first I would have to say Okay this is the table I want and then I would throw data in there and going back to home organization it's like saying okay I bought this piece of furniture now I'm going to label these drawers of what colors they should of what color of Lego should be inside and then you put the Lego inside so again this these are all attributes of a SQL database which is where you have these highly organized tables okay so you'll hear about these nosql databases and this is in our Focus here but a question I get all the time is what's SQL versus no SQL so nosql stands for not only SQL and and a nosql database again a database is a place where you store data and a nosql database you have these Dynamic schemas that means there's no predefined structure so instead of saying my table should look like this before I put data inside you just start throwing data in the database so one popular nosql database is mongodb and in mongodb the data isn't organized at the table it looks like this so basically throw in data about Lily and then you throw in different data about Henry and that's just how this database is organized so going back to the home organization example imagine if you had a room in your house where you stored all your Legos a nosql database would be like you get the classic Lego set and you just put the box in there you get the Harry Potter set and you just put the box in there and you just put the boxes in there and maybe everything isn't organized by red orange yellow but it's still organized in some way and that might work for your household or this nosql database might work for your company so there are so many nosql databases out there but in general what I want you to get out of this is that no SQL stands for not only SQL and it's called a non-relational database because data isn't in these these organized tables okay at this point we've gone through all these terms here so I'm going to do on the next slide is take all these terms I just introduced and summarize them in a few sentences okay SQL is a language and we use it to work with relational database so here's what SQL code looks like and here's what a relational database looks like and you can see the data is stored in these tables which are also called relations and they had this predefined scheme or this predefined plan before you load the data and then a less rigid way of storing data is using nosql also called a non nosql or a nosql database or a non-relational database okay so those are Basics now that you have the terminology let's dive into an example database So today we're going to imagine that we work at a school and we want to keep track of all the students and all the grades so typically what happens is when you are storing data for the first time you're going to store them in spreadsheets and that's totally fine I would say it works for a lot of the cases out there but let's say we're running to a few issues so currently the data is a lot of spreadsheets but there's no single location for the data so teachers are trying to access the data but they they can't talk to each other like they have to talk to uh sorry they can't get the data in the same place they have to talk to another teacher to get their data to get student grade about extreme great information and also the data needs to be locked down so maybe someone keeps deleting data in one of these spreadsheets that you're collecting student grades let's say the student body is growing and there's a lot more data to manage and so you're trying to find a better way to deal with this data than just keeping in these spreadsheets and so we create the student grades database and in the student grades database we have a student's table and a grade staple so let's dive into these tables first of all we have these IDs so one thing in SQL that's different from typically using Excel or spreadsheets is every row has to be unique and so every row here is unique and it has an ID and these fields are called primary keys so student ID is the primary key of the student table and grade ID is a primary key of the grade table okay so now let's look at these rows here so what's interesting here is you can see that these two tables connect to each other so Alexis is in row one in the students table and then you see she has three grades in the grades table so you can match rows in one table with rows in a table and another thing to note here is that one student can have many grades so this is sometimes called one to many relationship okay so this student ID here you can see that it matches that student ID in the table above and it's called a foreign key so if we summarize everything we just talked about we can put it all into one image here so this is sometimes called a data model so if you're starting at a company for the first time and you want to know what their data looks like you ask them for the data model and this will give you an idea of everything we just talked about the last few slides so this database has two tables the students table and the grades table and you can see all the columns in the tables and then both of them have that primary key or that unique ID and it's labeled with PK also there's this kind of funny looking Fork symbol and that says that one row in the students table matches many rows in the grades table and then finally that student ID in the second table is a foreign key meaning it matches the primary key or the unique ID in that first table so here that was your example database now that we have this example database and you understand the table let's move on to running SQL code so what do we know so far we know the basic SQL terms and Concepts and we have this database that's ready to look at so what we can do now is we can either read data or edit data so most people read data from a database and you can start here or even just end up here so if you're a data analyst data scientist you're going to be reading data from database other people can create tables modify those tables like insert rows or delete tables even and those people have roles like data engineer DP admin and so on but we're going to focus on reading data because that's where people usually start okay so let's get into the coding Basics I've been talking about reading data from a database but in SQL terms your query data from a database so just remember SQL stands for structured query language so the most basic SQL query you can write looks like this and one thing I want to note is at the top we use Query like a verb and down here we're using query as a noun so if I were to use both of those in a sentence I would say I'm writing a SQL query to query my database there's there's this in terms query so in English what this is telling me to do is show me all the data within the table called my table so let's break down this query here so in purple these are your keywords so every computer programming language has keywords that means a word has a predefined usage to it so select and from already have meanings when it comes to coding in SQL and this is the table that has the data in it that I want to extract data from like I want to read from and this R means all columns and I end every SQL query with a semicolon so whenever I introduce this students always say what about capitalization does it matter and the answer is no SQL is case insensitive whether you write capital select or lowercase select the code will run the same but typically keywords should be in all caps and everything else should be lowercase all right so this is our students table so let's do our basic SQL query on that so select all from students if we did that this is the table we give back and you can see it's the exact same table so this is the most basic SQL query which is get everything from this table and if we just selected this name then we would only pass student name column and then if we did student ID student name you can see I would get both those columns so you're kind of sensing a pattern here you can see that with the select keyword what that does is it tells me what columns should be displayed if I look at the from keyword it's telling me what tables should I pull from so these two keywords select and from are part of this larger family of keywords that some people call the big six or these are the six keywords you should know in SQL so going forward instead of calling out a keyword specifically going to call this the SQL clause um that's how people typically refer to different parts of the SQL query so the first part is the select clause and next we have the from clause and these are the six main Clauses when you're working with SQL so next let's go to the where clause so here's our students database if I added the where clause in here what happened is it would look at the student ID column because I'm saying where student ID is 101 and it says oh these three rows have one on one in them and then it would return those three rows to be if I did a slightly more complex query you can see here I'm doing course name is intro to SQL and final grade is greater than 90. and what would happen is well first if I read this query I would look at the select Clause first so it's going to say okay well it says you want to select these three columns great now within those three columns these are the cases where course names enter to SQL these are the rows where final grade is greater than 90 so which rows have both of those things you can see that's only the first rule so only the first row and those three columns would just be displayed okay now we're done with half the SQL Clauses the main SQL Clauses we're going to skip a few and go down to the order by Clause so with the order by Clause if I said select all from grades so that's everything that table I can order by final grade descending so if I order ascending it's lowest to highest descending is highest to lowest and I would get this here and you can see the final grades are descending I can also do something that's really commonly done which is using the distinct keyword and this is typically used within the select class sorry not typically it's always used within the silver class so a distinct is like a unique so this is saying show me the unique course names so I'm gonna go to this course name column there's some duplicates here I only want the unique ones and at the end of the day I'm going to order it by course name and you can see this is my result here so from these SQL queries you've probably seen it's all about reading you have this big table and you're going to read different parts of the table and extract just different parts of that table that's what querying is all about okay we've gone through before simpler query SQL SQL Clauses so now let's move into some of the more complex ones and the most complex one this whole presentation is this group by clause so here's our SQL database and here's a query that uses the group by if I read the first line of this code this is actually a comment so anything that starts with Dash Dash if you write something after it the computer won't read it as code it's just whatever notes you want to take along the way and you have comments in every programming language next let's go through the group by so in this case we're grouping my student ID so student ID you can see we have two student IDs we have student ID 101 and student ID 102. so what group by does is really cool it's going to look at just student ID 101 and do something with those rows and then it's going to look at just student ID 102 and do something with those rows so what should it do that's when you move on to the next part so Group by again that's grouping but in terms of what it's going to do with that you look back up at the select class so here it says average final grade so what you're going to do is for the first group 101 you're going to look at the average final grade for that and then the next we wanted to you look at the average final break for that so that I average all those like a number if I average all those I get another and that average is called a function so a function is just a calculation one of many functions in SQL we have average min max count and so on another part of this query you see this as average grade what that's doing it's telling you in my final result what should that column look like and so it's saying when I average my grade I want to rename that final column as average grade you see here and that is called an alias okay so from here with this SQL query we've been able to use this group by again to group the 101s the 102s apply some function which is the average of all the one and ones and one and twos and then reading that final column so here's my one-on-ones my average here's my 102s and I average okay so one thing to note here this is a little bit more in detail if you're not a true beginner but one thing students always mess up when they're first writing code is that whatever you Group by in the group by Clause you have to repeat that same column in this class so that might be a little bit more advanced if you're a complete beginner but it's just like a tip that you should always know when you're writing SQL queries so now we've done Group by Our Last Clause is having so what having does is if you've grouped your data in some way and you want to filter that then you use having so this is the code that we just went through if I were to add an extra line on having it says having average final grade greater than 90. what that would do is it would take my final table and only return the feel the rows where the average grade is greater than 50. so it would remove that second one so the tip here is you should only use a heading Clause when a group by Clause is present so having I would say is use less than all the other Clauses just because it's only there if there's a group by okay so those were our six SQL queries SQL Clauses in this query so now let's pull that all together into one final example just so you get a good sense of every clause and action so here's our database again there's a huge SQL query but it's really not that bad so I'm going to go through every section in detail okay so this query here's the end result so first thing select Clause what's happening those select Clause well that's what you're going to those are the columns you're gonna see in your final output okay next we have our from Clause this is a little bit more complex here but what this join is doing is it's telling me I want to get data from the student table and I also want to get data from the grades table I'm going to connect them together using that student ID and then the combination of two tables that's where I want to query from now we have a where class so here this is saying I don't want to see situations where the final grade is zero so the two carrots that's saying not equals it's the only show me situations where the final grade is a number not just zero and then we have our group by so again a group by or group by student name so for student Alexis for student Brianna I want to be able to aggregate their data in some way so just remember that student name is also in the select Clause up there and what I'm going to do with each student name is I'm going to find the average final grade for that student so those are for two final grades and those end up going into our final table here and then we have having which is filtering that final table so we're saying only give me the students with grades greater than 90. so remember we had Brianna here but she disappeared because of that having and then finally there's the order by so we only have one situation here with the order by you people to sort that data okay so we just went through all the Clauses in the SQL query so one thing to know is the SQL Clauses must always be listed in this order so it's a good idea to remember the order of the Clauses and if you have a hard time doing that a Numark that I came up with that I like is start Fridays with Grandma's homemade oatmeal so that will give you select firmware Group by adding order by so to summarize this section um beginners often start by querying a database and then in a query when you write a query the Clauses they must always be written in the same order which is the order you see here so as a checkpoint it's out of time you've learned the basic concepts and terms we've gotten familiar with the data we understand the structure of a query which is those six Clauses so now let's practice coding okay so last we're going to do a software demo okay so now that you know all the basics of SQL where can you write SQL code the answer there are so many places to write a SQL code you can write it in a ton of software which I'll go through in detail there's database tools where you can write SQL code and you can also write SQL code with another code it's pretty amazing so the demos that I'm going to do today are within SQL Lite and D Beaver which is software and a tool okay but let me explain what those are before I go into them so first what is This Acronym it stands for relational database management system what does that mean it's basically software you use to work with the database or a place where you can write SQL code this is some a table of all the popular softwares out there so I will subscribe here and you can write SQL code in all the software so how do you choose which one to write in let's start with the first one Microsoft SQL Server that's owned by Microsoft which means that you have to pay money to Microsoft to use Microsoft SQL Server and so typically you would use this if you're already working at a company and you want to work with other Microsoft products like Windows like if you're on a Windows machine you might use Microsoft SQL Server if you're using Azure things like that and then there's MySQL so MySQL is open source meaning it's free to use and this is typically used if you're already in webtov so if you're using HTML and CSS JavaScript use MySQL Oracle is another one that's used by big companies so it's proprietary meaning you have to pay money to use Oracle postgres is open source meaning it's free to use and it's usually used by data scientists so I've got some background I come from so I'm familiar with postgres and it's usually used along other open source data science tools and then finally there's SQL Lite sqlite is also open source which means it's free and it's used if you're doing any app development so just like I talked about with that little messages app and my example early on all that data is held in a SQL like database okay so I have a SQL fun fact which I call a slightly fun fact SQL syntax varies slightly from software to Software so this is a little bit confusing because you're learning SQL right now but the syntax is actually different a little bit in each software so let's say you want to return 10 rows of data then you have your rdbms and then this is what the code would look like in that rdbms so you can see it's different there's three different ways to write how do you return 10 rows a date it should be really straightforward so my Pro tip for you is that when you're Googling SQL syntax include the rdbms in your search and I say this because I want this in a very hard way my first job I was using SQL server and I knew how to write top 10. my next job I used an RDS called teradata and I couldn't I would Google how to write SQL queries and then it looked like it should work and then when I plugged in the code it didn't work and I could not figure out how it wasn't working and I realized well it's because the syntax is different in every database so in that case if you're Googling SQL syntax do return 10 rows MySQL return 10 rows SQL Server okay so the takeaways for this section is there's so much software out there so I talked about these rdbms's there's a ton of cloud-based software as well so like redshift bigquery snowflake and it seems intimidating because you're like how do I learn them all and the answer is nobody knows them all um but all you have to know is although each one has slightly different syntax The Core Concepts are the same so I was using staple Circle at one job we moved to another job I was using redshift and you basically just open up a different software and you're writing SQL code sometimes it breaks you Google it and then you slightly tweak it for that software so because this is a beginner course I wanted to introduce some free resources for SQL so if you want to get set up on your computer if you want to start security SQL code very quickly I would use SQL Lite it comes pre-installed if you have a Mac and it's just really easy to install if you have a PC and then if you want to start writing queries in a web browser I would use I'm not affiliated with them at all but I really like w3schools.com they have these databases already set up and then you can just write SQL queries within your web browser so that allows you to get used to the syntax before even downloading anything onto your machine okay so I'm just going to do a very quick SQL Lite demo because everything looks nice in a presentation but what does it actually look like when you are working okay here is my terminal window and for any rdbms if you want to just interact with it and it's almost raw form you can access it in a terminal so what I'm going to do here I'm going to open up sqlite so again it comes pre-installed on a map I'm going to type in SQL lite3 and I've created this student grades database so I do that I'm using SQL Lite now so from here I can look at the tables I have available right and I can write a SQL query select all from students and you can see that all my students I can make it a little bit more complex even student ID is greater than 102 and you get that as well this is a very very basic way of writing single code is again within SQL Lite okay let me get back to my presentation here all right now we've gone through rdbmf software which again is relational database management software and that's where you can write SQL code I would say try not to write SQL code directly in an rdbms you really want to be using a database tool instead so what is a database tool it's a tool that has a graphical user interface or GUI so instead of that ugly black and white screen I was just showing you instead you want to be able to click things and things pop up for you and you want to have a nice environment to write SQL code there are so many database tool options a few of them are free and I've listed them below so for the first four uh rows there you'll see that people when you use the Microsoft SQL Server are dbms you'll typically use SQL Server management Studio well if you're working with postgres you'll use PG admin as your database tool those go hand in hand sqlite there's many options I like to use DB browser and then there's a ton of tools out there where you can connect to multiple databases so I like to use D Beaver because it's free but I know people love to use Toad and so on and it just allows you to connect to a lot of databases and write in this user-friendly environment so let me share my screen again so here's D Beaver it looks really nice what I'm going to do is I'm going to connect to mySQL Lite database so there's a ton of options here and I'm going to choose sqlite but just to show you like these are all these different databases you connect to there's also nosql big data and so on if I'm choosing SQL Lite and then my student grades database is right on my desktop and I'm in okay so from here look at all these things you can click on you can look at the grades table the students table these are the columns and the Grays teeth these are the comps in the students table so you're seeing that this database tool is a much better place to write SQL code and if I want to write SQL code I'm going to open up a new SQL script and you can do my select statement select all from students and get this nice thing here where student ID is greater than 102. and you get this nice thing here and you can see in my result you can also oops make sure you want to load oops nope you can also filter and sort so here I've been sorting okay so this is again D Beaver it's a free database tool that I use to connect with our DPMS Okay so we've covered our dbms software again don't use those in their raw form go use a database tool I recommend D Beaver for free and another place where you can write SQL code is in other programming languages so they all know there are a ton of programming languages out there you can create apps with it do analysis and so on but if you're working with database sorry if you're working with data you're going to need a database and if you're working with database you're going to need to use SQL so let's say you're a developer and you typically code in HTML CSS JavaScript you're gonna have to store your web data somehow if people fill out forms that's going to be stored in a SQL database if you're an engineer you go to Matlab collecting sensor data that needs to be stored in a database if you're an analyst who use python or R I have to analyze customer data that data is going to come from a database so basically I would say if you're working with data like in any of these roles you're going to need SQL so the cool thing is that you can connect SQL to many other programming languages so if you are working as an analyst in python or R you connect to a SQL database and then you can write python code and SQL code alongside each other so let's say I'm coding r and I have data in two databases maybe there's a mySQL database and a sqlite database what I need to do is install a database driver and it sounds kind of intimidating but it's not you basically just install this program like a machine one's called our MySQL one's called R SQL Lite and what that database driver does is it allows R and MySQL to talk to each other really seamlessly it's awesome so let's say we had here's an R example let's say if we're trying to connect to our SQL Lite database you can see the green that's where you're connecting R to SQL Lite once you do that that last line you're writing SQL code and it's this mix of R and SQL code same with Pythons I import the SQL Lite module that connects me to my database and then at the bottom again I'm writing I'm using pandas which is a python library with SQL okay that is your crash course in SQL so let's summarize everything that we've talked about this is my one pager that you could also find on GitHub I have a GitHub link on the next page but this summarizes everything we talked to today so key terms we know a database is a place to store data in an organized way most the times data will be stored in a relational database and you use SQL to work with that database so if you're just starting with programming for the first time SQL is a great first programming language to learn because it can be used almost everywhere this is what a data model looked like and it shows how everything is connected in a database data models aren't usually this easy they're usually huge and then everything is all these tables are connected to each other but it helps you understand how data is organized and then the most important I would say key term in all that is to know what our primary key is so it's a unique ID for that table and then query can be used as a verb or a noun so you query data from a database and you also write a SQL query to read data from the database and these are the main SQL Clauses remember you can remember the order with start Fridays with Grandma's homemade oatmeal and the other thing to remember is that the syntax is slightly different in each software so the pro tip there was when you're Googling make sure to also include the name of your software and your Google search and then finally where can you write SQL code and a lot of places these are the most popular are dbms's and then if you're just getting started I would say go to w3schools.com or you can download SQL Lite and then you can write it within other tools and other languages okay and that's all I had for today foreign [Music]