Learn Basic SQL in 15 Minutes (PART 2/3) | Creating Tables | SQL Tutorial | Business Intelligence

this video is the second in a series of videos teaching you basic sql fundamentals in the previous video i showed you how to query data using select statements in this video we're talking tables what they are how to create them and how to manage the data in them let's jump in [Music] hello and welcome to vitamin bi bringing you business intelligence for beginners and beyond my name's adam and on this channel i help you do more with data so if you're new here do think about subscribing and hit that bell so you don't miss any uploads so in the first sql basics video we looked at how to select data in a database you don't need to have watched that video before you watch this one so don't worry about missing anything and i'll add a card to the other video at the end and also a link in the description so you can check it out i've also added timestamps in this video so you can jump around or skip this intro and go straight to the sql part right on with the video first things first what is a table and what is it made up of a sql database is made up of tables tables get their name from the structure of the data they contain data in tables is in a rows and columns tabular format each column often also called field contains a metric or dimension if you're not familiar with what these are then you can check out this video here but long story short metrics are quantitative numbers and measure stuff and dimensions are qualitative and describe stuff each row contains an entry in the data set so if we take this example table you can see that each column is a different field of data and each row is an entry other examples of tables similar to those in a database would be divisional tables in sports so you have columns containing metrics like wins losses draws and points and each row represents the data entry for each team with team name being a dimension with tables in a sql database the metrics and dimensions are broken down into different data types when you're creating a data table in a sql database you'll need to specify the type of data for each field you want to add to the table let's break down by far the most common sql data types now first up when it comes to numbers and metrics the two main types are int and decimal int stands for integer and specifies a field that will contain numbers without decimals and decimal as the name suggests specifies a field that will contain numbers with decimals when creating a decimal field you need to include its size i.e the total number of digits it can contain and the number of decimal places separated by a comma like so in terms of text or dimensions the main type is varchar which specifies a field that will contain a string of data with a variable number of characters letters numbers special characters or a combination of all three the size parameter specifies the maximum length of the data that can be contained in the field next up we have date dimensions that are specified using the date type the format for this type is year year year dash month month dash day day then you have the timestamp type which takes the previous date type and adds our hour colon minute minute colon second second this is great for fields where you want to know the exact time something happened so those are the most important or most used data types when creating fields in data tables but do bear in mind that there are others and some will be specific to the database management system that you're using let's now look at how to create a new database table with some fields in it using sql in terms of data i've got a list of james bond films containing an id the year it was released the actor the director and the dollar amount in millions it made at the box office non-adjusted now before you say anything about this list yes i do know that it doesn't contain the 1967 casino royale film because i don't consider it to be a proper james bond film i mean it's got woody allen in it and there will be those of you who'll be saying that never say never again shouldn't be included because it wasn't an official film but it did have sean connery as james bond so i'm including it if you agree or disagree let me know in the comments below so if we look at the table start thinking about which of the data types we've just talked about they should be specified as when we're creating our table we've got id year and box office that are numbers and title actor and director that are all text values let's go ahead and create our table as with the previous video the tool i'm using to manage and query the database is called navicat it's my tool of choice and there's a link to it in the description if you'd like to check it out to create our new table we'll simply start by typing create table although it's not compulsory i've typed it in all caps because it's sql syntax and that makes it easier to see when you've got a lot of text in your queries then we type the name of the table we want to create i'm just going to call my table bond after that i'm going to type open and close parentheses then a semicolon in a lot of tools you'll need a semicolon at the end of queries even though you don't in navicat let's throw a couple of returns and start listing out the fields we want to include in our table the first is id so we type the field name first id followed by the data type in this case it's a number without decimals so we'll use int for integer then title which is text so we'll use varchar remember that with varchar you need to specify a maximum number of characters that the field can contain looking at the titles i'd say it's probably around 30 but i'll put 50 just to be safe then we have released we'll use int for this but it could theoretically be a varchar as well because although it's a number it's not really a metric it's a dimension because it's descriptive anyway we'll use int because it does have the advantage of us being able to use it in mathematical calculations if we wanted to for some reason then we have actor which is another varchar we'll put 30 for this field for director i'll just copy and paste this line and change it to director and for box office it's a number that has decimals in it so we'll use the data type decimal we can see from the data that the largest number for skyfall is five characters long with one decimal place so we type decimal and five comma one for five characters and one decimal place in parentheses when i run this we see that everything is okay which means that the table has been created containing the columns we've specified if i were to run the query again we get an error saying that the table has already been created which tells us that we cannot have two tables of the same name in the same database now once our table has been created and we need to delete it for any reason we can do so using the drop command so i type drop table bond run and it's deleted let's go back to our create table query and now we're going to talk about things called constraints there are different types of constraints that you can add to the fields of your tables that as the name suggests constrains them in some way the most common of these constraints is called a primary key although they're not mandatory it's highly recommended to have a primary key in every database table the job of a primary key is to uniquely identify each specific row in the table in our case we have the id field which contains unique values so in order to specify the id field as our primary key we just need to type primary key after the data type it's important to note that primary keys cannot contain null or empty values and that there can only be one primary key in every table another constraint you can specify that's similar to primary key is unique which means that there can also be no duplicate values in the field however it can contain a single null value and you could have as many of them in a table as you like we could use unique on our title field if we wanted to to make sure that no duplicate values were inserted into the table the next constraint we'll look at is not null by specifying this on a field you make sure that there must be a value in the field for every single row i'm going to add this to our released field so the general gist of these constraints is that they help to keep your data clean by making sure that there are no duplicate or null values in your table because when data is inserted into a table where the constraint is not met an error is produced and the insert fails the final constraint we'll look at is default this is used in cases where perhaps data is missing from an entry for a specific field but instead of the value showing up as null we can choose to specify a default value that will be used instead if we look at our data we can see that there's no box office value for the latest film no time to die so instead of the table showing a null value we could type default and then the default value in this case we could use 0.0 in single quotes note that the default value needs to match the data type specified for the field remember that we deleted or dropped the bom table we previously created so in order to create a new one we would need to run this query again and the result is okay right we have our table created and what we want to do now is to insert data into it which is fairly simple to do using the insert into command in a new query window i'll type insert into and then we specify the table into which we want to insert our data bond followed by values and then i'll type open and close parentheses in between these we'll specify the data we're going to add i've gone ahead and done that notice that the text values for our varchar fields are all in single quotes i'll run an insert query and then a select star query on the bond table please check out my other video if you don't know what this is so that we can see the data now populated great but what about if we don't have data to insert for all columns perhaps we only have data for specific columns how do we deal with that well let me go ahead and type out the next row of data to insert like so if i were to remove any of the values say from the end and run the query you can see that we get an error message saying that the column count doesn't match the value count the way we get around this is to in our insert statement specify exactly which columns we do have values for in the statement here we only have id title and released so in parenthesis after the table name i type id comma title comma released i run the insert query and then the select star query and we can see that where we don't have the data we have null values except for our box office column that has the default 0.0 value inserted instead ok so we've learned how to create a table and insert data into it the last thing we'll look at in this tutorial is how to modify a table or its data if for any reason you wanted or needed to the three reasons we'll look at are if you needed to add a column delete a column or change the values of a column to add a column we use the alter table command followed by the table name in our example i'd like to add a column for the studio just so that we can differentiate never say never again from the other films so alter table bond add then our new column name studio and the data type varchar 30. if i run that the column will be added if at any time you'd like to see the design of the table you can use the command describe with the table name so describe bond in the output we can see that the studio column is present if we changed our mind about this column and decided that we didn't want it after all you could just change the add to drop and remove the data type run and run and it's gone again finally let's say we wanted to modify the data in one of our columns perhaps we want to refer to our actors by just their surname to do this we would need the update command here's how we would do it update bond set actor equals connery where actor equals sean connery run and if we run a select star we can see that the one row affected by our query has been updated and finally if we wanted to delete certain rows in our table say we decided that we would remove never say never again after all then we can just use delete from bond where title equals never say never again don't forget to check out my other sequel tutorial if you haven't already if you liked this video please leave a thumbs up and please do subscribe and turn on notifications so you don't miss any uploads thank you so much for watching and until the next time stay bi curious [Music] you

Channel: Adam Finer - Learn BI Online

Views: 175,693

Keywords:

Id: gm6tNK_iOHs

Length: 16min 53sec (1013 seconds)

Published: Fri Oct 01 2021