SQL Tutorial 3: Create Database,Table, Inserting Record… — Transcript

Learn how to create databases, tables, insert records, and retrieve data using MySQL Workbench GUI and SQL commands.

Key Takeaways

MySQL Workbench is a default GUI client installed with MySQL for easier database management.
Database connections require specific parameters like username, password, and port number.
Schemas organize tables and other database objects similar to folders in file systems.
SQL commands can be executed in both GUI and command-line clients.
Using semicolons to end SQL queries helps in clear query execution and management.

Summary

Introduction to MySQL Workbench as a GUI client for managing MySQL databases.
Explanation of database connections, including local and remote setups.
Overview of default databases and schemas available in MySQL.
Steps to create and manage database connections in MySQL Workbench.
How to navigate schemas and tables within a database.
Using the query editor in MySQL Workbench to execute SQL commands.
Importance of semicolon to end SQL commands for clarity.
Difference between GUI clients and command-line interfaces for SQL execution.
Demonstration of retrieving data from tables within the HR database.
Clarification of schema concept and its relation to databases and tables.

Full Transcript — Download SRT & Markdown

Speaker A

Alright, so in the previous session, we have seen the basics of database, like what is data, what is SQL, and also we have seen how to install MySQL database, operate on Windows operating system, and also have shown you there are two types of clients: one is a GUI client and the other one is a command-line interface.

Speaker A

a command-line interface so we can exhibit our sequel commands in both the clients so every database is having the two pens of clients some of them are command-line interfaces and some of them are GUI clients so from today exactly we are going to discuss about sequel commands okay now

Speaker A

So we can execute SQL commands in both the clients. Every database has two types of clients: some of them are command-line interfaces and some of them are GUI clients.

Speaker A

with the my sequel software so if you just go for this edge and here if you say workbench so my sequel to our two bench age or 8.0 so this is a GOI client R will be installed along with the

Speaker A

So from today exactly, we are going to discuss about SQL commands. Okay, now let me just open my GUI client.

Speaker A

on this one so it will open the geo a client so let's wait for a few seconds here so now it is open the geo h and this is my sequel workbench so now i am going to use this particular tool

Speaker A

So most of the coming sessions, also I'm going to use GUI clients, so that is SQL MySQL Workbench. So that is a default client which will be installed along with the MySQL software.

Speaker A

currently my database server as well as client or install on my same system or my local system ok so here I already created one connection this my sequel Windows connection I already created so suppose if you want to create a new connection first time when you open your my sequel workbench

Speaker A

So if you just go for this edge, and here if you say Workbench, so MySQL Workbench 8.0, so this is a GUI client which will be installed along with the software. Okay.

Speaker A

you the connection details so every database is having some connection details so by using those connection meters we can connect to the database whether it can be local system or it can be resetting the remote systems that doesn't matter only we need a connection regions so here need

Speaker A

Alright, so in the search window, we need to just search for Workbench, so MySQL Workbench. So this is basically a GUI tool or GUI client which is installed along with the MySQL framework. Look at that, we are going to choose it.

Speaker A

and this user name we have configured at the time of installation for my sequel right so we need to click on the store in store involved and it will automatically get the password and then click on OK so once you provided these details so once you click on the ok button then it will create

Speaker A

So once you open this, just click on this one. So it will open the GUI client. So let's wait for a few seconds here.

Speaker A

ways so for example by default when you open so you don't get all these things so you will get this administration options and sometimes you will get a different kind of windows like this ok we don't need all these Butler windows so we need only specific windows to use the result set

Speaker A

So now it is open the GUI, and this is MySQL Workbench. So now I am going to use this particular tool to execute all my commands. Okay.

Speaker A

schema so then it will go it will show you water all schemas or currently databases available in my sequence ok so here I am just clicking on the schemas now it is showing the currently whatever schemas are available my database so here we need to understand one important term is database and

Speaker A

So this is your MySQL Workbench. So this is our GUI client, right? So once you open the GUI client, we need to first establish the connection to the database.

Speaker A

table or view or whatever is having some kind of schema so basically schema is some portion like a folder structure so normally if you have multiple types of files we will create folders and we'll keep or we can copy the similar kind of files into one folder right similarly database also

Speaker A

So once you have installed MySQL database, it will come along with the server as well as client. Now currently, my database server as well as client are installed on my same system or my local system.

Speaker A

some applications schema is also considered as a database but when you go for Oracle software so there we also have a schema but there we generally refer the table structure is a schema and if you go for my sequel server so there also we have a schema concept okay but in my sequel also normally

Speaker A

Okay, so here I already created one connection. This MySQL Windows connection I already created.

Speaker A

we have installed you will get default databases for samples like volsces circular so these are the three databases by default you will get it okay and the rest of the two databases I have created or I have imported so like HR database my flix DB so these databases I have imported

Speaker A

So suppose if you want to create a new connection first time when you open your MySQL Workbench, it will ask you to create a connection, right?

Speaker A

just connected to the my sequel and we have seen different type of databases here now let's go to HR database and if you go to the tables here you can see different type of tables like countries departments employees job HT jobs locations and beaches right so these are the called as a tables

Speaker A

So for that, what we need to do is we need to go to Database, and here we can see an option Connect to Database. So once you click on this, it will ask you the connection details.

Speaker A

Butler will give you this one so if you click on this it will open up the quarry editor where we can exude all our sequel commands okay so I'm just clicking on this then it will open the query

Speaker A

So every database has some connection details. So by using those connection parameters, we can connect to the database, whether it can be local system or it can be remote systems. That doesn't matter. Only we need a connection region.

Speaker A

output here we can see the output and these are all the different tables are available and HR is my database name and here we need to write the quarry so all that for use we need to execute from here and as soon as write your query you have to use this petal or option to exhibit your quarry

Speaker A

So here we need to give some connection name and port number where exactly our database is present or installed, and this is a default port number of database. So every database will run on a port number. So this is the one default port number, and this is a username and password. Username by default populated here.

Speaker A

this so this will go on now if I again get back again click on this it will show you schemas and again if you click on the bottom the middle one so the middle column is four and if you click

Speaker A

And this username we have configured at the time of installation for MySQL, right? So we need to click on the store password, and it will automatically get the password, and then click on OK.

Speaker A

schemas window and query editor and at the bottom you will get the output once you have executed your quarry so these are the only windows we need so other things I have just closed now so this is basically UI of my sequel workbench so now I'm going to use the same UI so in the in our series

Speaker A

So once you provided these details, so once you click on the OK button, then it will create a new connection here. So this will show you here I have currently two connections available.

Speaker A

want to get that data from employees table here so in which cert database my employees table is here my employees table is present inside the HR database so the first command I need to give is use HR and semicolons every line is end with the semicolon so but this is not mandatory

Speaker A

So this is a connection I have created. So once you click on this one, it will connect it to your local MySQL database.

Speaker A

because that will be give you the clarity like if you have n number of quarries so where the quarry started where poori is ended you can clearly see by using semicolon and first we need to specify the database name on which schema or which database we are executing the quarry and

Speaker A

So this is a UI you can see, and this UI can be seen in multiple ways. So for example, by default when you open, you don't get all these things.

Speaker A

you very initial very basic query to understand how we can retrieve the data from the table so I'll say select a star from employee so first we need to exude your database and select this execute and then execute this particular quarry so this will give you data like this so here data is

Speaker A

So you will get the administration options, and sometimes you will get a different kind of windows like this.

Speaker A

10 rows then execute this chorus again so it will give you only 10 rows ok so this is your schemas all the databases tables everything we can see here and this is the area where we can execute our sequel commands and here bottom area where we can see the result of the particular quarry

Speaker A

Okay, we don't need all these builder windows, so we need only specific windows to use the result set to execute our queries.

Speaker A

SQL is a language structure for e language so the SQL is divided into again multiple sub categories like DD l vm l b RL or DQ l and we can also call as a DC l like a TCL and DCM so these are the

Speaker A

So what we can do is we can just go to the Schemas. Here you can see the option called Schemas. So this one. So we need to click on this one.

Speaker A

ml means data manipulation language DRL means data retrieval language and DQ will also called as a DQ data query language and T cell is a transaction control language and dzl is nothing but data control language so every category is having n number of commands there's a certain number of

Speaker A

So once you click on the schema, then it will show you all schemas or currently databases available in MySQL.

Speaker A

are there so there are multiple people or working on databases so they don't need all the commands they should know certain commands what is the area they are explicitly working suppose people are working one as an administrator they should more focusing on t-cell commands data control

Speaker A

Okay, so here I am just clicking on the Schemas. Now it is showing the currently whatever schemas are available in my database.

Speaker A

designers will be there so that is another kind of missing nation so database designers basically design the tables establish the relationships of tables all these things will be taking care so for them DDD VDL commands will be very very useful so DDL commands sometimes DML also very useful

Speaker A

So here we need to understand one important term: database and schema. So let me just clarify what is the database and schema.

Speaker A

sessions I am going to show you all the commands so we are going to discuss all the commands don't worry but in the real time in real environment so people we don't use all the commands so some people based upon their role in designation and based on their requirement they will focus on

Speaker A

So what is a database and schema? So basically, the database is a whole part, like it contains a lot of schemas.

Speaker A

the basically commands which are comes under data definition language so why we call them them as a data definition language because when you see these commands create alter drop truncate and premium so these commands will basically works on the definition side like if you want to create a

Speaker A

So databases contain a lot of objects like tables, views, stored procedures, and a lot of objects will have. So every object like table or view or whatever is having some kind of schema.

Speaker A

on a definition side so eat these commands will work on the data which is there in the tables so we can insert the data we can update the data we can delete the data so we need to perform

Speaker A

So basically, schema is some portion like a folder structure. So normally if you have multiple types of files, we will create folders and we'll keep or we can copy the similar kind of files into one folder, right?

Speaker A

number of commands filters all those things and select is also very very important command so we we are going to say a lot of combinations with select command we are also going to learn a lot of other commands with the Select command sub-queries join set commands everything will be used along

Speaker A

Similarly, database also internally maintains the schemas. So schema is, we can say, simply a subfolder, and inside the schema also we can store the database objects like tables, views, and all these things.

Speaker A

or delete as soon as I have done some action it will not automatically commit it into the database so we need to commit the transaction and suppose I already some time committed I need to go back suppose my mistake I have done something wrong in the database I need to again rollback that change

Speaker A

And sometimes in some software like MySQL especially, schema is also considered as a database. So some software, some applications, schema is also considered as a database.

Speaker A

sessions net finally data control language and these be these commands basically used by okay administrator so because database is using by multiple people some of them are administrators some of them are developer some of them are designers lot of people will use so and also they

Speaker A

But when you go for Oracle software, so there we also have a schema, but there we generally refer the table structure as a schema.

Speaker A

administrator don't provide the full access to the users database users so based upon their role and designation and they work they will decide which troll have to send to that particular user so only that but your own they will grant and if their if they want to get back out they want to take

Speaker A

And if you go for MySQL server, so there also we have a schema concept. Okay, but in MySQL also normally we call or refer databases and schema as the same in MySQL, right?

Speaker A

discuss all this command with different type of examples okay now so first thing so as soon as we have entered into the database the first and most important item is a database and table these are the very basic entities in the database okay in the database once we have started you're

Speaker A

So database as well as schema will be considered as the same. So here if you go to this particular MySQL Workbench, and these are the different schemas, we can also call them different databases.

Speaker A

objects will be there so data can be stored in different ways but table is a main source where exactly that data will be stored okay so table is a main source where exactly data will be stored so database and tables so these are the main two entities in the database so first thing is we will

Speaker A

Okay, so by default once we have installed, you will get default databases for samples like world, sales, sakila. So these are the three databases by default you will get it.

Speaker A

data in the table all these things are possible if you have only database because database is a storage area where we can create anything so first the most important entity is database and my sequel the database is also called as a schema so if you want to create a new database in my sequel

Speaker A

Okay, and the rest of the two databases I have created or I have imported, so like HR database, MyFlix DB. So these databases I have imported from external sources.

Speaker A

database database name so this command will drop the database and if you want to cream the database this is the one command create database database name or we can also use this command create schema and schema name as I said before in my sequel database and FEMA because it has the same so

Speaker A

So these databases are required for practicing purpose. Okay, for day one purpose. So that's the reason I have installed them.

Speaker A

database will be treated as safe sometimes what happens is when you create in your database with some name with the same name there is already some databases available so then it will throw an error okay so because we cannot create multiple data databases with the same name so the database name

Speaker A

So I will show you today how to import all these databases for practice, all these things. Fine.

Speaker A

the database then created I am internal intestine saying create the database in the database with this name if this if the database is not already exist so if you want to provide that you need to give this command create database if not exist database so this command will create the database

Speaker A

So this is basically MySQL Workbench. So we have just connected to the MySQL and we have seen different types of databases here.

Speaker A

throw an error so these are the different commands we have to use on creation of database dropping the database and if already database is available we can also verify the database existence or not okay these are the very basic command we have to use at the database or schema level so now I

Speaker A

Now let's go to HR database, and if you go to the tables, here you can see different types of tables like countries, departments, employees, jobs, job history, locations, and beaches, right?

Speaker A

inside so along with them I will also create a new database so with this commands whatever commands are just now I told by using those commands I will create a new database okay so let me create just a

Speaker A

So these are called tables.

Speaker A

need to do is we need to click on this one so the first icon when you click on this it will give you one quarry editor so he ran into NZ with this camera the first command is create database

Speaker A

Now by default, you don't see that query window like this, guys. So we need to have query window here to execute our queries.

Speaker A

it so once you have executed then here reference this one there is small refresh icon is there under schemas menu just click on the refresh then you will see my DB is created here ok my DB is created here and if you expand this my DB did we do currently we don't have any objects tables

Speaker A

So here if you see the small icon at the right side, so this builder will give you this one. So if you click on this, it will open up the query editor where we can execute all our SQL commands.

Speaker A

create schema schema name and before creating this I want to delete this one suppose I want to drop my database then the command s drop database and database name so this is a command so when I execute this command the my DB will be gone so now again I am executing this command right click

Speaker A

Okay, so I'm just clicking on this, then it will open the query editor like this.

Speaker A

by using schema command create schema schema schema name and drop schema schema name so let me use those commands this time so now the same thing I am doing by using schema command so let us say create schema schema name I'm executing now select and execute so once you refresh here you

Speaker A

So here we need to write all the SQL commands, and there are other windows also there. So this window will give you little information about all these objects.

Speaker A

the same things the schema and database will be treated as same okay suppose already have my database for example I am creating the database called my DB again so refresh so I already have my database for my DB now if I try to create the same again one more time it will give you an error see

Speaker A

We need to select the schema here, and here if we can see the output.

Speaker A

command if not exist so this keyword we can use and this will check before executing this command so create database if not exist okay so create the database if not exist now whenever run this again it will not give you any error but it will just give you what warning because Kondik a database

Speaker A

Okay, so once again, so here this is output. Here we can see the output, and these are all the different tables available, and HR is my database name.

Speaker A

DB so now I have created my own database okay so these are the first important command s creation of the database or schema and we can drop the database or drop the schema okay drop and create so these commands again thumbs under the GDL data definition language and the same commands we can

Speaker A

And here we need to write the query. So all that for use, we need to execute from here, and as soon as you write your query, you have to use this pedal or option to execute your query.

Speaker A

command so the whatever commands we have here data definition language these are the set of commands we can use it for any kind of database object so database object means the table is one of the database object view index in and I'm stored procedure function everything is a database object

Speaker A

Okay, and the other windows will be displayed like SQL add-ons and other stuff. We don't require all these things.

Speaker A

currently we have a my DB but we don't have any tables or nothing table is there only trees are there but we don't have any tables nothing these are empty project empty database okay now I will show you a few important come on the first thing is creating the table so before going

Speaker A

And if you want to just close the unnecessary windows, so it is occupying some space, so we can just select these views.

Speaker A

the command is create table and which object for creating table we are eating create table table name your student is my table name and inside the bracket we need to specify the column name and s number is a column name his name is a column name and mass is a column along with the column name

Speaker A

So there are three views are there, right? So if we click on this, so this will go on.

Speaker A

and s name is a string so inner is equal we have a character data type so we can use this and 15 is nothing but number characters we can store so his name should be below 15 characters and

Speaker A

Now if I again get back, again click on this, it will show you schemas, and again if you click on the bottom, the middle one.

Speaker A

specify column column data type and size of the data so three things should be involved here so this command will create a new table so here I am writing this table so because in the UI we have a

Speaker A

So the middle column is four, and if you click on the right side and the right side window.

Speaker A

for that reason first we need to use we need to specify a command called use a char so use my de because my DB I created right so my DB so because this command will say use this putler

Speaker A

DB and whatever commands are next time executing will be executed on this particular database so that's the reason we need to specify that okay so in the UI it is required but once with this is only one time this every time you no need to specify for every command you want you to

Speaker A

specify just for once because currently this will currently focused on this particular database but if you are currently working on command window we don't require this we can directly execute this command because by default at the command window by default initial stage which will ask you which

Speaker A

DB you're going to use so we specify the database there so there's a reason we don't require them ok so here we need to use this one fine so create table student this is a column name data type and

Speaker A

size of the data again column name data type and size of the data again one more column named age that size of the data and one more thing guys this is a same statement we can also write in

Speaker A

multiple lines suppose I can split that into multiple lines like this so this is one column this second problem and this is a third command here the semicolon so we can also write the entire statement in multiple lines that is also perfectly right okay so now when exhibit this command select

Speaker A

this command first use this command execute and now execute this command once you have executed it will create a table under my dB now once you refresh here okay so my DB here refresh here now under tables you can see the student table so again if you expand the student table you can see

Speaker A

the different things and if you expand the columns you can see s number s name and March and this is a UI label suppose if you want to see here itself as an output what does the columns data types all

Speaker A

these things available in this table we have a one more command called describe so describe command will describe that table what exactly columns are available data type size all these things will be displayed as an output so describe and table name is student so describe student so

Speaker A

when I execute this command it will give you the data about the table so s number s name marks so these are the column names are called as a field column or field and this is a type of the data

Speaker A

and there's a null key is a default extra some other information also will be populated they are a system at the time of creation of the tables by default system the database will add some more columns ok so there are again maintaining the metadata all these things so the data about the

Speaker A

data is quality metadata okay so here is member s name mask and there is the data what end of data integer integer these type of data it is contained so that's the data so description command is the command will basically give you the structure of the table ok the schema of the table fine so now

Speaker A

we have just created one student table so this is the first thing which we need to perform so how to create the student table any table so create table table name and specify the column name and data type size and another column data type in size another column type and size we can create any

Speaker A

number of columns like this okay so now just we created one table in my database so now the next thing is we need to insert some I in the table so then only we can perform certain operations right

Speaker A

so we can perform insertion in multiple ways so if you want to insert the data in the database in the table we can use insert command but insert command itself we can use in different flavors so the syntax is again insert into student so insert into is a command insert into student student is

Speaker A

a table name and values directly we can specify the values so in the columns in which every way in whichever order the columns are maintaining in the table in the same order we need to also pass the data so this command will insert the table so let me just copy this and here I am executing okay

Speaker A

so here I am executing this command so insert into student so while so currently this command already executed so every time you no need to execute this use command guess once you have executed so currently our session is focused on the my DB so every time you don't need to execute this

Speaker A

use command okay so now insert into student I am inserting the values into student values what are the values of main setting and what are the columns are there first s number s name and marks so based on the data type of data we have to pass the values first time first values integer so

Speaker A

that I need to pass integer then second value is s name so it should be string so in databases in sequel language strings we need to include in the single quotations so strings we need to include in the single quotations so this is a string type of data and these are the mods again it is in

Speaker A

number so now let me execute this statement once we have executed so it will be inserted so if you want to see the output whether inserted or not we don't know right so the reason we need to put

Speaker A

this particular page click on the middle icon it will give you this window so it will give you some information see now insert into student values it will show you whether your command is successfully executed or not so that's the use of this window so in this window in output window you can see

Speaker A

what are the action you have performed and what is the message it is affected by this command I there are any errors it will clearly show here warning servers everything will be dissipating this modular table okay so in the last command I have executed at this one insert into student

Speaker A

values three values have passed so the last command is successful so once you have done this if you want to see the data from the student table how we can see the data again how do you select the command select star from student so star representing all the columns okay select

Speaker A

the star suppose if you want to get only specific column data I specify select s number from student or select s name from student so when I say star representing all the columns now currently we have one of one record in the table so when I execute this it will give you one record here okay so this

Speaker A

will give you only one record so this is a way of we need to go insert and there are two other ways we have suppose what I will do is this is another way of inserting the data suppose here exactly if

Speaker A

you if you go for this butler approach the first approach in whichever order you have created your columns like s name is name and marks in the same order you how to pass the data okay so in the same

Speaker A

order you need to insert the data suppose I don't want insert like this I don't I want to insert the data in my own order okay first I will send a name then I will st. mark then I will say a

Speaker A

stem so it is also possible so how it is possible is insert into student and in which order you are going to pass the data we need to specify him so in which order you want to pass the data that we

Speaker A

need to specify so I want to pass as name first then s simmer and then mask after that specify the values fever and here in whichever order is specified in the same order you have to pass but in the table at the time of insertion in the table that command will be take care of it in

Speaker A

which order the value should be assigned in the table so this is another way of inserting data in the database so in the first approach we need to exactly pass the data in the same order in the second approach we can specify our own order based upon this you can pass the data here so now

Speaker A

I'm exhibiting this one also and after that so my command is successful here and then again and say select star from student so this time you will see two regards so 1 0 1 and 1 single true ok so these

Speaker A

are the two types of insert and there is one more in studies there so this is all again same and but suppose if you don't know exact value so I have a three columns one more important point here is

Speaker A

this we have a three columns right here we passed the columns data and here also we have our C coms data but sometimes what happens is you know the name and student name and number but you don't know the marks but still you want to insert the data in the table but it will not help you enter

Speaker A

the data or one or two columns if you have a three columns we must enter the data in three columns so that completes one record so it doesn't allow us to put only as number and s name word which will

Speaker A

not leave marks as empty so if you want to insert the data in the table we must pass the data for all the colors but sometimes if you don't know exactly what is the value of the Mars then how

Speaker A

we can do that is we can pass null so instead of Mars place we can just pass in null values if you don't know the value but later once you know the value you can update that man okay you can update

Speaker A

the particular value in the table so in that in that particular case we need to use this approach this is the third appointment so we have seen three times this first one second one and third one suppose I don't know exactly what are the Mars then what I can say is insert into student values

Speaker A

this is my s number two rate number name of the student and I don't know math so that I can just say none so null is not an MP and not a value this is just nollie is nothing but unknown notley's

Speaker A

nothing but unknown value and suppose tomorrow if I know the exact match then I can update the table you can replace this null value with some other work I can do that by using update command okay so there are three different flavors we have so to insert the data in the table the first approach is

Speaker A

we can directly pass data in proper order in the same order and the second approach is if you want to store the data in my own order you can specify the columns list and in the same order we need to

Speaker A

specify the data and third approaches if you don't know exactly value in the column still you can insert the data in the table by passing null value okay so these are all different types of insert statement so now I will exhibit this one last one and after execution it is successful then

Speaker A

again I will retrieve the data from the student so now we have a three columns and you can see the last column value is null so that is unknown value okay 1 0 1 1 0 2 and 1 0 3 so this is how we need

Speaker A

to execute insert insert commands in sending the data into the table so so far we have done three action phase one is the first thing we have to have we have done is we have created a new database step 1 we created a new database step 1 we created a new database in step 2 we created a

Speaker A

new table created a table called student student and the third step we have inserted the data inserted data into student table so these are the very basic operations we have done created the database created the table student inside the database and then we have inserted the date range

Speaker A

to the student table so now we will see a few more options so selecting the rows if you want to select the rows from the table how we can do that just now I have shown you one select command right

Speaker A

so select command is used to selecting the data from the table but we can also use select command in different ways and we can use select command to retrieve all the data all the rows from the table or we can also use select command to retrieve the specific roses specific columns of data so select

Speaker A

command will be used in multiple ways so now I will show you how to use select command here so here now I'm shifting to another database called HR database there we have some tea all tables are available like employees departments okay so countries locations there are Dupree different

Speaker A

tables are available which contains a lot of data so now I am going to use that Butler database so that is pre different database and we can just I'm so I will give you one a sequel file today so we

Speaker A

can guys can import that file then you will get this HR database so HR is a database by default you will import and if you expand this HR you will see lot of tables and these tables contains a lot of data we can do a lot of operations in coming sessions ok so now I am removing this so

Speaker A

now what I can do is let's move on to the next one how does how many or how to select the rows from a table so we can select the rows from a table by using select command so when it says select a star

Speaker A

it will retrieve all the columns data but if you want to reduce the specific column data we need to specify like this columns employee ID okay as an EMP ID first name as an F name salary as a cell

Speaker A

from employees so I will show you simple way just a moment so here go to your workbench so now I need to see shift to HR database how we can shift it use use a chart use hecha so first time execute

Speaker A

this command okay that will be 62 HR database now we can exert all the commands as usual every time you know it takes you this okay and one thing here is whenever you are using keywords like a use

Speaker A

create insert so just practice these keywords so including the uppercase letters because my sequel is are not case-sensitive language or it will not support anything so everything will be treated as the same so you can write all the commands all in lowercase letters or in upper case letters or you

Speaker A

can also write sequel commands mix the cases anything so it will not be considered as both different okay it is not case-sensitive language so you can use everything is a small case letters or uppercase letters doesn't matter suppose if I use here I have a command use use command so

Speaker A

how type in the uppercase letters if I type in the lowercase letters also nothing wrong in this okay it will be treated as the same but the best practice is to differentiate the keywords and our own our own scripts always keywords including the upper case letters and rest of them will use in

Speaker A

the lower case letters so that's the standard we follow generally okay that's not mandatory but that's the standard we normally follow so all the keywords I will put in upper case letters this is again standard so use a charm so when they exhibit this command currently my HR database is using

Speaker A

now so here I have some predefined tables already available employees countries all these things so now I need to retrieve the data from employees so let's say select star from employees select the star from employees and when I exhibit this command it will give you data from the employees

Speaker A

table so it will redo all the columns ID first name last name email and so on suppose I want to get only specific columns of data suppose I want to get employee ID first name last name so then now we can get it if we can say like this select employee ID underscore ID comma then first

Speaker A

name first underscore name comma and again last underscore name so the beauty of this putler tool is so all the columns all the methods all the commands automatically it will show you here so as soon as you're trying to write the command okay that's a very best option in this Butler

Speaker A

client so these features are comes from the client side so all the claims will not behave in the same way okay so this client is very very good one and which is come with my sequel software itself so the next one is last name I'll say last name and employee ID first name and last name and I

Speaker A

say salary also I'm capturing hand then salary okay so this salary from employees so this is microgreen so now I'm exhibit this Cori so this will give you only three columns so first name laughs employee ID first name and ER salary so the salary is giving something wrong here first

Speaker A

employee ID first name last name I have not so give some comma and then execute so now we got the exact data so employee ID first name last name and salary right suppose when I'm reclaiming this data it is giving by default some name employee ID first name last name and salary suppose at the

Speaker A

time of displaying this data I wanted to specify my own names to these columns I want to give my own names to these columns instead of putting the original names I want to use the different names to these columns so in that case we can specify the aliases for those columns you can call it

Speaker A

as an alias so what is alias means is as soon as we specify some column followed by give your own name ok so what I can do is instead of printing employee ID here I will just print only EMP ID so

Speaker A

that is an alias of the column column alias we can call it as a column alias now instead of putting first-name I can say only F name this is a first name column alias now instead of last name as a

Speaker A

last name this is another alias of last name and instead of salary I will just say only cell this is the alias of salary column now when I execute this code now this time you can see your own names

Speaker A

instead of original names personal names will be hiding so now it is giving him EMP ID of name L name and salary so this is also we can write like this select command columns we can redo so we can

Speaker A

retrieve all the columns and also all the rows as well as specific columns and specific rows so this is how we need to use select command so one more important thing here is suppose so in the exact in the table we have only salary comma Phi at the time of presenting this data

Speaker A

for every salary value I want to increase like a five thousand so here for every salary I want to add five thousand extra so how we can do this so simply here we are printing an image salary column

Speaker A

hide salary plus and just say five thousand so now what happens is it will bring to all the columns as usual while building the salary column whatever value we have already there in the salary field it will add 5,000 to the existing value and the new value will will be spread in the output okay so

Speaker A

currently 24,000 is there after adding 5,000 it will become more right so now execute this one more time so this time it becomes on 29,000 so this butler value which is added to every field in the salary and I have given Sal as a alias name so this way we can just select the data from the

Speaker A

tables by using select a command so main important thing is if you want to select all the columns all the rows you start and if you want to select all the columns specify the columns and if you don't

Speaker A

want to project the original names of the columns in the output then you can also specify the alias for those columns okay so this is all about select the command selecting rows from the table so now we have done selection so created the database created the table in student in such a data into

Speaker A

a student table and also we are learning how to retrieve the data so we can say select or retrieve the data select retrieve the data from a table so these are the very very basic operations we can perform things okay so now we'll slowly begin to few more topics so now we will discuss about

Speaker A

the sequel database so far we have seen just the very basic operations so at the time of creation of the table we need to specify the data type of column so why we need to know the data types in

Speaker A

sequel because at the time of creating the table we need to specify the few things column name data type and size of the data so these are the things we need to put inside the command okay so now we need to know what are the different data types available in my sequel so there are three

Speaker A

categories of data types are available some of them are numeric data types so those data types can store numbers and text is another type of data type dot but read can store characters and strings and date and time is another kind of data types which can hold bales and times date and times of

Speaker A

data okay so these three are the categories of sequel data sequel data types numeric text date and time so now we will see detailed in numeric what are the different types are there in text also there are so many things are they in the date and time also we haven't so many data types

Speaker A

are available so now this is just a categorization number data types text in the data types and date and time data types now we will see one by one so under numeric data types these are the different set of data types like teeny in numeric data dash means these data types can hold only numbers okay

Speaker A

so teeny into small into medium into into begin to float double decimal so these are the different types are available so why these many types are available because the size of the data so teeny into means it will accept only small size number probably only one digit and Smalling means maybe

Speaker A

it is accepted one or two digits not more than that medium it means little bit higher and in this more higher and if you have a long string like 10 disease or 15 digits then go for baking so first two file types will accept only numbers without a decimal number without a decimal so here

Speaker A

I mentioned the range within the range this particular end we can assign the values okay within this partner range we can assign the values in to those particular variables so this is a basically internal mechanism of sequence so first these types these data types can hold numbers but

Speaker A

if you want to store a decimal numbers then we can go for this one float double or decimal so here we have a decimal number so before this man whatever part is called precision and after decimal whatever disease we have called as a precision so scale and precision scale and precision so these

Speaker A

two parts will be there in the decimal number so these are all numbered data types so whenever you create a column we have to specify the data type so that time you need to decide what kind of data

Speaker A

is suitable for your column but most of the times if you go for without decimal number if you want to store always we need to go for integer this is a suggestible but it can handle almost every kind

Speaker A

of a number and if you have a huge number then go for begin okay and if you have a decimal number then go for decimal or double because load is very smallest number it can hold so double or decimal

Speaker A

number can hold a long number which contains a decimal value so these are the different numeric data types we have in sequence now the text to data types so in the text generator sub carries they're back blob medium dense medium long taste there are a lot of data types are available case

Speaker A

because these data types are exactly used in the real time the complex data because data is in there in the different formats not only the text so data will be there in the text format number format images it's image is also a data so images or sometimes text file directly we can put

Speaker A

the text files XML files into the table so there are different kind of data types different kind of data invoice images audios videos all these things have comes under the data or mate right so how we can store that kind of data so we have a special types are available in the database and

Speaker A

all blobs and flops long text blobs so these data types will be used to store that kind of data in the tables and if data is very very shows image file ok audio file video files size is very very

Speaker A

shoes so normal suppose if you have installed databases in your local system so they cannot handle that kind of data so in the real time in the production environment you have that kind of data so in that time they will use these kinds of data types very advanced data types now the

Speaker A

next one is date and time datatype suppose if you want to store a date in a column in a table then that Pirtle a column we can specify has one of this data types either case a date date and time

Speaker A

timestamp and time if you want to store only date in that column specify that only date datatype and if you want to store date along with the time then go for the date time and if you want to

Speaker A

store this particular format timestamp format go for this one and if you want to store only time in the column then go for time datatype so these are the data types are available to store date and time in the column okay so these are the tcats of data types case one is numeric text date and

Speaker A

time in the coming session going forward wherever it is required I'll use those popular data types

Topics:MySQLSQL tutorialMySQL Workbenchdatabase creationtable creationinsert recordsretrieve dataSQL commandsGUI clientdatabase connection

Frequently Asked Questions

What is MySQL Workbench?

MySQL Workbench is a GUI client installed along with MySQL software that allows users to manage databases, execute SQL queries, and visualize schemas.

How do I connect to a MySQL database using Workbench?

You create a connection by providing connection details such as username, password, host, and port number. Once configured, you can connect to local or remote databases.

What is the purpose of a schema in MySQL?

A schema in MySQL organizes database objects like tables, similar to how folders organize files, and is often used interchangeably with the term database.

Get More with the Söz AI App

Transcribe recordings, audio files, and YouTube videos — with AI summaries, speaker detection, and unlimited transcriptions.

App Store Google Play

Or transcribe another YouTube video here →