declare

SQL Tutorial - 9: Create Table Statement



Sharing buttons:

hello guys welcome to my channel this is

the ninth tutorial in this course and in

this tutorial we are going to talk about

creating tables in SQL so there are

three steps that you need to follow

while creating tables in SQL and the

first one is well obviously you have

named the table that you are creating

and the second one is that you have to

define the columns that the table is

going to have and the last thing that

you have to do is mention data types of

columns and you know I have not

specified the you know mentioning

constraints in the create table

statement because that's not necessary

right so these are three things that you

absolutely have to do but if you also

want to apply some constraints to the

columns that you have in your table then

you can do that too right there are lots

of things that you can do in create

table statement is one of the most

powerful statements in SQL and obviously

it has to be right because the the data

is going to be in tables and you know

creating tables is like the sense of you

know database management so this is the

syntax of the create table statement and

create table statement by the way is

what you use to create tables and you

know you have to first specify two

keywords the first one is create the

second one is stable so these keywords

you just have to mention and then you

have to give a space and type in the

name of the table and then you have to

type in the opening parenthesis

character and then you have to specify

all the columns that you want your table

to have along with the data types and

there has to be a space between the

column name and the data type and then

if suppose after you've declared the

first column you want to you know

mention more columns and you have to use

the comma character and once you are

done specifying all the columns and the

data types you can put in the closing

parenthesis character and then put in a

semicolon to terminate the entire

statement and I have mentioned the

primary key constraint at the last you

know step or the last line in the create

table statement in the slide that you

are seeing at the moment but you know

you can specify it while creating the

columns as well and that's what we're

going to check out next when I

demonstrate the create table statement

to you in MySQL workbench so now I have

my workbench and I'm going to show you

guys how the create table statement

works so at the moment I'm using the

test database and

you can see that you know the test

databases is written in bold and that's

because I have used it but if you still

want to double-check then what you can

do is use the use statement and type in

the name of the database that you want

to use so you know in this case I'm

going to use test so that's why I'll

type in use test and press the ctrl

enter key combination and in the output

part I see that the statement has been

processed right so now I'm going to use

the test database and I'm going to

create a table called employee and to do

that as I said we're going to use a

create table statement and all SQL

keywords you know preferably you should

write in uppercase letters because when

you share your code with someone you

know it becomes easier for that person

to follow and it also becomes easier for

you to know you know what the words in

your statements are SQL words or key

words and you know what statement words

are your own words or identifiers right

so the name of the table as I mentioned

is going to be employee and you know on

the next line I'm going to put in the

opening parenthesis character and the

first column that this table is going to

have is the ID column and it's going to

be of individual type right because the

ID for the employees is going to hold

numeric values only and that's not

necessary but you know in this

particular case I would want it to have

only numeric values and I would also

want this you know this field to be the

primary key for the table and I'm missed

mentioning it in the last tutorial but

you know in a table if you have five

columns or ten columns or 500 columns no

matter how many columns you have only

one column can be the primary key right

and I would just have to type in the

keywords primary and key after declaring

the what do you call column and then

I'll put in a comma because I'm going to

specify the next column name and that is

going to be name right so we are going

to have a field that holds names for

employees and this is going to be of

value I type and the maximum number of

characters that I want this field to

hold is 30 so I'm going to specify 30

here and the limit part has to be

between parentheses and this is

something that we discussed in the T

Tauri

and arrow types next we are going to

specify another column for the table and

this one is going to be date of birth

and we would want this to be of

date/time type right in the next field

that I want this table to have is the

email field and this is also going to be

of virtio type and I would want this to

have let's say 40 characters and well

that's it I want this table to have no

more fields so I'll put in the closing

parenthesis character and put in a

semicolon to terminate the statement and

now when I press the ctrl enter key

combination I get the message in the

output portion of the workbench that the

create table employee statement has been

processed correctly and no rows have

been affected thus part because we

haven't populated the table yet we've

just created it and if you want to make

sure that your table has been created

there are a couple of things that you

can do the first and the easiest thing

that you can do is just go to the object

browser portion and you know click on

the drop-down for the database that

you've been using so in this case we've

been using tests so you know click on

the drop down for tests and then you

would find three options under the

database so that you've you know

selected so under test I see tables

views and routines so click on the

drop-down for tables and you would see

the employee table in that list so this

is the easiest way of checking whether

your table has been created or not the

other way that we're going to discuss is

you know by using the de SC statement

and again the ESC is an SQL keyword and

you know do you have de SC with MySQL

and Oracle but I don't think my

Microsoft SQL Server allows you to use

the ESC so you know that's why I would

suggest you to you know use some other

alternatives to check whether your table

exists or not and we're going to check

out the other alternatives later on but

you know des he works with MySQL so it's

a very very very nice command and I love

using it so you type in DES C which is a

short form for describe and then the

name of your table which in this case is

employee right you put in a semicolon

and you press the Enter key in the

results section

you would see that you know all the

columns that you have in the table are

displayed you also see the datatypes of

each column and you also see whether the

column can have null values or not and

you know if you have any constraint

applied on that on that column or not so

the ID field as you can see is of

integer type and it cannot hold null

values so that's why it no is written

under the null column and it is the

primary key so that's mentioned and of

course it doesn't have any default value

because we haven't specified that in the

create table statement the name field is

of value type and the DOB field is of

date/time type and the email field is of

variable character type too right so

that's all I wanted to discuss in this

tutorial I hope you guys had fun in the

next tutorial we are going to discuss

something important too and create table

we're going to check out lots of

variations of Reddit in this course and

you know this was just a brief

introduction to the command and I hope

you've gained some information and thank

you so much for watching this tutorial

see you in the next one and please

subscribe to my channel in case you

haven't already and take care