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

Learn how to create and manage SQL tables, understand data types, and apply constraints in this practical SQL tutorial by Adam Finer.

Key Takeaways

SQL tables organize data in rows and columns with fields defined by specific data types.
Choosing the correct data type (int, decimal, varchar, date, timestamp) is essential for accurate data storage.
Primary keys are crucial constraints that uniquely identify each row in a table.
SQL commands like CREATE TABLE, INSERT, ALTER TABLE, and DELETE allow full management of table data and structure.
Practical examples and tools like Navicat help beginners understand and apply SQL concepts effectively.

What the video covers

This video is the second in a series teaching basic SQL fundamentals, focusing on tables: their structure, creation, and data management.
Explains what tables are in SQL databases, emphasizing rows and columns with fields containing metrics or dimensions.
Covers common SQL data types including int, decimal, varchar, date, and timestamp, explaining their usage and parameters.
Demonstrates how to create a new table using SQL syntax with a practical example based on James Bond films data.
Discusses how to specify data types for each field when creating tables, including numeric and text fields.
Introduces SQL constraints, particularly the importance of primary keys in database tables.
Shows how to insert data into tables, including handling partial data for specific columns.
Explains how to modify tables by adding columns using the ALTER TABLE command.
Covers deleting rows from tables using the DELETE FROM statement with conditions.
Uses Navicat as the database management tool for demonstration, with links and recommendations provided.

Chapters

What are the most common SQL data types used when creating tables?

The most common SQL data types covered are int for integers, decimal for numbers with decimals, varchar for variable-length text, date for dates, and timestamp for date and time.

Why is it important to specify data types for each field in a SQL table?

Specifying data types ensures that the database stores data correctly and efficiently, enforces data integrity, and helps optimize query performance.

What is a primary key and why should every table have one?

A primary key is a constraint that uniquely identifies each row in a table. It is highly recommended to have one to ensure data uniqueness and enable efficient data retrieval.

Full Transcript — Download SRT & Markdown

Speaker A

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.

Speaker A

create them and how to manage the data in them let's jump [Music] in hello and welcome to vitamin bi bringing you business intelligence for begin and beyond my name's Adam and on this channel I help you do more with

Speaker A

Hello and welcome to Vitamin BI, bringing you business intelligence for beginner 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.

Speaker A

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

Speaker A

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. 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.

Speaker A

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

Speaker A

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 a 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.

Speaker A

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 IL to those in a database would be divisional tables in sports so you have columns containing

Speaker A

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.

Speaker A

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

Speaker A

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.

Speaker A

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

Speaker A

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.

Speaker A

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 year- Monon month- day day then you have the timestamp type which takes the previous date type and

Speaker A

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.

Speaker A

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

Speaker A

Next up, we have date dimensions that are specified using the date type. The format for this type is year year year year - month month - day day. Then you have the timestamp type, which takes the previous date type and adds hour hour colon minute minute colon second second. This is great for fields where you want to know the exact time something happened.

Speaker A

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 will be saying that Never Say Never Again shouldn't be

Speaker A

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.

Speaker A

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

Speaker A

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.

Speaker A

simply start by typing create table although it's not compulsory I've typed it in all caps because it's SQL syntax and 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

Speaker A

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 will 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.

Speaker A

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

Speaker A

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.

Speaker A

looking at the title 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 varar as well because although it's a number it's not

Speaker A

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.

Speaker A

another farar 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

Speaker A

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.

Speaker A

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

Speaker A

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.

Speaker A

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

Speaker A

Looking at the title, 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.

Speaker A

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

Speaker A

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 box office is five characters long with one decimal place, so we type decimal and 5, comma 1 for five characters and one decimal place in parentheses.

Speaker A

however it can contain a single null value and you can 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

Speaker A

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.

Speaker A

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

Speaker A

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.

Speaker A

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

Speaker A

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, constrain 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.

Speaker A

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

Speaker A

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.

Speaker A

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 varar fields are all in single quotes I'll run an insert query and then

Speaker A

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 can 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...

Speaker A

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

Speaker A

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

Speaker A

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

Speaker A

don't have the data we have null values except for our box office column that has the default 0.0 value inserted instead Okay so we've learned how to create a table and insert data into it the last thing we'll look at in this

Speaker A

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

Speaker A

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

Speaker A

table Bond add then our new column name studio and the data type varar 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

Speaker A

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

Speaker A

the ad 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

Speaker A

would need the update command here's how we would do it update Bond set actor equals Conor where actor equals sha connory run and if we run a select star we can see that the one row affected by our query has been

Speaker A

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

Speaker A

Again don't forget to check out my other SQL 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

Speaker A

watching and until the next time stay stay be I curious [Music]

Topics:SQL tutorialcreate table SQLSQL data typesprimary keyinsert data SQLalter tabledelete rows SQLbusiness intelligenceAdam FinerNavicat

Get More with the SozAI App

Transcribe recordings, audio files, and YouTube videos — with AI summaries and speaker detection. 30 minutes free.

App Store Google Play

Or transcribe another YouTube video here →

Free tools: TXT to SRT · SRT Validator · Merge SRT · Subtitle to Text · All tools