SQL Tutorial 1: Overview on Database, DBMS/RDBMS, SQL |… — Transcript

So this is the full session on SQL. So as part of today's session, now we are going to discuss the basics or fundamentals of database or SQL, all these things. So first of all, we need to understand what is DBMS, what is RDBMS, and what is the database, what is SQL. So we need to be very clear about these particular terms, okay? So today is mostly theoretical part, and once we have understood basics, then we'll slowly enter into the practical part.

is RDBMS and what is the database? What is equal? So we need to be very clear about these particular terms, okay? So today is mostly theoretical part and So once we have understood basics and then we'll slowly enter into the practical part.

Okay. So first of all, let's start with what is DBMS and RDBMS. So before DBMS and RDBMS came into the market, in the initial days, the data was stored in the form of text files. So if you want to store data in some place, we need a kind of a file. So normally the data was stored in the form of files. So that's called the file management system. So in the form of notepad or sometimes in Excel files or Word files. So normally we store the data in the file. So that's called the file system concept.

some place, we need a kind of a file. So normally the data will be stored in the form of files. So that's called the file management system. So in the form of notepad or sometimes in Excel files or word files. So normally we

So in the initial days, people were storing the data in the form of files. But later, what happened is the main problem in the file system is, we cannot organize the data properly. Suppose if you want to retrieve some data based upon certain conditions, or if you want to place a number of joins or subqueries, all these things, we cannot properly organize the data in the structured file or file system management system.

we cannot organize the data properly. Suppose if you want to retrieve some data based upon certain conditions, or if you want to place it a number of joints or sub-coris, all these things, so we cannot properly organize the data in the structured file

File management system. So if I store the data in the form of files, organizing the data, you can just only for storage, but we cannot perform certain kinds of operations on those file systems. And then slowly Excel came into the market. So in Excel also we can perform certain kinds of things but not exactly 100% operations, we cannot do that. And we cannot maintain the relationship between the data.

came into market. So in Excel also we can perform certain kind of things but not exactly 100% operations, we cannot do that. And we cannot maintain the relationship between the data. And later point of time, DBMS came into market. So in the

And later, DBMS came into the market. So DBMS means database management system. So DBMS means database management system. So this is a concept actually. So DBMS is the data can be stored in the form of tables. So we can have some space in the hard drive and the data can be stored in the form of tables. So that's a kind of a concept of DBMS.

be stored in the form of tables. So that's a kind of a concept of DBMS. So DBMS is the data can be stored in the form of tables. So DBMS means database management system. So this is just a concept. So there are multiple rules are there.

So DBMS is the data can be stored in the form of tables. So DBMS means database management system. So this is just a concept. So there are multiple rules are there. So there are different concepts out there. So Codd is a scientist who created a certain number of rules, how the DBMS should be, how this DBMS should work.

management system is saying, we can store the data in the form of tables in the hard drive. So we can store the data in the table or we can store the database in the tables in the hard drive.

So database, DBMS means database management system. So what database management system is saying is, we can store the data in the form of tables in the hard drive. So we can store the data in the table or we can store the database in the tables in the hard drive.

easily retrieve the data and we can store the data and we can refer the particular columns as well as rows if I align the data in the form of tables. So that is a concept is proposed by the code in database management system.

So DBMS is a database management system where we can store the data in the form of tables. So if the data is available in the form of tables, it means we have rows as well as columns, right? So in that case, we can easily retrieve the data and we can store the data and we can refer to particular columns as well as rows if I align the data in the form of tables.

a little advanced than DBMS. So RDBMS. So what is RDBMS means? Relational database management system. So the same concepts are there but additionally they have added something called a relational. Because in the DBMS we can store all the data in

So that concept is proposed by Codd in database management system. So that is the first database which is there in the market. And later, in the DBMS also they found some kind of issues and to overcome the issues again they have proposed one more concept called RDBMS. So this is a little advanced than DBMS.

which contains employees, names, salaries, designation, job, all these things, right? Date of birth or joining date, all those things. But instead of storing all the data in one table itself and some data, I can split it to some other table. And I can

So RDBMS. So what is RDBMS means? Relational database management system. So the same concepts are there but additionally they have added something called relational. Because in the DBMS we can store all the data in one or two tables. But we should be able to split the data in multiple tables and we can establish the relationship between the tables.

on this particular principle, they have proposed another kind of system that's called relational database management system. So after DBMS, our DBMS came into market. So in the DBMS and to satisfy this particular concept, there are some software's are developed

Suppose for example, I want to store the employee data. So if I store all the employee data in one table, which contains employees' names, salaries, designation, job, all these things, right? Date of birth or joining date, all those things. But instead of storing all the data in one table itself, some data I can split to some other table. And I can join those two tables. I can establish the relationship between those tables.

these are all basically databases which will support DBMS concept. So DBase, FoxPro, MS Access, these are all the softwares which we can download and install, we can work with them. And later point of time, RDBMS came into market. So once

So in that particular case, the retrieval of the data will be very faster and we can also get the data very effectively. We can perform the operations very effectively. So then based on this particular principle, they have proposed another kind of system that's called relational database management system.

then we have a MySQL is also one of the software. There are multiple software's are available in the current market. Even MS Access also supporting the RDBMS features now, Microsoft MS Access. And these databases all comes from different companies.

So after DBMS, RDBMS came into the market. So in the DBMS and to satisfy this particular concept, there are some software developed and that time almost 20 years back, there were some software called DBase and FoxPro and MS Access, MS Access. So these tools are available. So these all the tools come under the DBMS software.

And MySQL is an open source database. Oracle is a license plate. It's also open source is available, but if you want to use advanced features, we need to go and purchase a license. And then Microsoft SQL Server is there. So this is from

So these are all basically databases which will support DBMS concept. So DBase, FoxPro, MS Access, these are all the software which we can download and install, we can work with them. And later, RDBMS came into the market. So once RDBMS came into the market, again there are some software that have implemented, that is Oracle is one of the software, and then MS SQL Server, MS SQL Server is one of the software, and DB2 is also one of the software.

Okay, so after RDBMS came into market then Database plays very very important role and database become very very popular to store huge data So all the companies started using RDBMS software in their organization to store the data Okay, so that's a basic concept

And then we have MySQL is also one of the software. There are multiple software available in the current market. Even MS Access also supports the RDBMS features now, Microsoft MS Access. And these databases all come from different companies.

MS SQL Server and etc. So again in RDBMS they have implemented lot of features and keep on implementing so many features. But nowadays what happens is in RDBMS also not able to hold the huge data set. Suppose if an organization we have

And some of them are open source and some of them are again license-based. So the first relational database management system is Oracle. So Oracle is a software which came into the market at the first time. Then MySQL is also from the same company.

But we need to maintain a lot of servers like hardware servers, we need to have a lot of hard disk and we need to increase the size of the memory to store more data. So that's the RDBMS. But in the nowadays, in the

And MySQL is an open source database. Oracle is a license plate. It's also open source is available, but if you want to use advanced features, we need to go and purchase a license. And then Microsoft SQL Server is there. So this is from Microsoft company. And DB2 is IBM company. MS Access is again from Microsoft company. So there are different companies, they have developed their database software to satisfy the DBMS features.

dynamical. Even in the Google also generating lot of data. So to maintain all of this data, this is basically huge data. And to maintain this particular data, RDBMS software are not feasible for that. So they cannot hold this much of data. Because they

Okay, so after RDBMS came into the market, then database plays a very, very important role and database became very, very popular to store huge data. So all the companies started using RDBMS software in their organization to store the data. Okay, so that's a basic concept of DBMS and RDBMS.

still software development process, so still there are some products or suppose if it's a standalone product or it is a web based applications, still they are using back end as a databases, RDPM software as a back end systems. But if you want to

DBMS is a very old technology. So in the nowadays market, no one is using that. So RDBMS is a latest technology. In RDBMS we have multiple database software available in the market like Oracle, SQL Server, DB2, MSX, MS SQL Server, etc.

there is a certain technology should be there like Hadoop is one of the example for that okay and spark is another example for that so that is again different stream so those technologies came into market to maintain the huge data but if you

So again in RDBMS they have implemented a lot of features and keep on implementing so many features. But nowadays what happens is in RDBMS also not able to hold the huge data set. Suppose if an organization we have people in the thousands mostly, so it will not go to crores of data. But RDBMS software will hold certain limited data or certain amount of data.

working or customers information if you want to maintain so for all these things RDBMS software are enough to store that kind of data okay so this is a DBMS and RDBMS concept so DBMS and RDBMS is a concept a theoretical concept

But we need to maintain a lot of servers like hardware servers, we need to have a lot of hard disk and we need to increase the size of the memory to store more data. So that's the RDBMS. But nowadays, in the current system, RDBMS also not able to hold certain amount of data because as soon as internet came into the market, so data is generating very hugely because of mobile phones, because of internet, because of social sites.

software, we can install it and we can store the data in the form of tables. So most of DBMS, RDBMS software's will be stored the data in the form of tables itself. So still the RDBMS software will store the data in the form

So a lot of data is generating dynamically. Even in Google also generating a lot of data. So to maintain all of this data, this is basically huge data. And to maintain this particular data, RDBMS software are not feasible for that. So they cannot hold this much data. Because they have to maintain the huge size of memories, RDS. So

database? So simply we can say database is a storage area. So database is a storage area. We can store the data in that particular space the storage area so we can store the data We can simply say data storage

area is called as a database But where we can exactly use this database in your applications in software development So if you go for any kind of applications or softwares there will be Two to three layers so if I say three

different layers, so for example We have a front-end layer. This is a front-end layer and there is a backend layer. So the MIDI layer is optional. If it is a web-based application, there will be also some MIDI layer is available. So this is

called as application server or web server. So normally what happens is this is a web server or we can also call as application server. So business logic will be residing this. Now this is the GUI mode. So graphical user interface, so customer or

user will always interact with the GUI. So this application can be used by using any browser. So let's say I have opened some sign up form. So what I will do as a user, I will put some my first name, last name,

data bot, all this information, now I'm submitting here. So as soon as submitting this data in the form, what is happening exactly is, so this data is taken by this logic. So in the business logic, there is some program will be written, so

that program will capture this data Whichever user is sent and that particular data will go and store in some location So the third layer is required. So this is called as a database So in the database layer the exactly data will be present

Suppose the user want to retrieve the data for example. They have log in to your banking account You want to check your balance what you will do is you will log in to your account or will log in to your internet banking application

with your valid login data With the login details so once you are logging into your application you can see what are all he previous transactions have done like history of the transaction balance all this information From where you are getting that information and

also you can generate the invoice and reports all these things right? So how you are getting the data means that data is present somewhere? You don't know as a user. I don't know exactly where it is present, but the data is maintained somewhere

so that but let data will be there in the database itself and So from the data it will face the data whenever you say some account number and it will go to the Database and in which all the data related to that particular

account and get the data and same data will be present in the UI So this is actually process will happen So here the front-end applications normally developed by using HTML hypertext markup language or XML ASP dot net or JSP so that they are scripting languages or DHTML So these are

all scripting language. There are two kinds of languages. We have a scripting languages and programming languages So basically the front end the buttons are UI links all these things will be developed by using scripting languages and The web

logic will be designed by using C language C++ Java dot net Okay, she shop so by using this programming languages they will implement this business logic and The database can be either Oracle or SQL Server or MS SQL Server So

one of the database they will be installed on their server and all the data will be go and store in this particular database So this is an actual concept where exactly database come into picture Or sometimes you will have multiple

database servers. So suppose if the server is full, then you can maintain another backup servers and data will be stored in the multiple database servers. Even sometimes we have multiple web servers because if the load is more, one web server or one database

cannot hold that load. So in that case, they can increase the number of database servers. They also increase the number of web servers. So this is exactly where its database came into picture. So database is basically a storage area where we can store

the data and Once we have installed this particular database software it will automatically get some space from the hard drive and Our data will be stored in that particular space in your hard drive. So that is all about database So now

what is sequel? So suppose we have a database. So let us say I have a database. What is the database database contains a data? in the form of tables, let's say I have multiple tables in the database. So in this tables I have

lots of data. So I want to perform certain operations on data, right? So what kind of operations I can perform? Suppose I want to insert some data, I want to add some data, some table inserting the data. Or suppose I want to

update the existing data, like I want to insert some data, I want to update the existing data, or I want to delete some data. from the database, I want to delete some data. Or sometimes we need to retrieve the data. So by providing

account number, it will give you history of the transactions. So that's called as retrieval or selecting the data. So if you want to perform these operations as a user, we need to communicate with the database. So we need to communicate with the database.

But how we need to communicate with the database? We need to communicate with the database by using certain language. So we need a language, that language the database should be able to understand. So what is that language as? Yes, your structure query language.

So whenever the databases came into market, structure query language is designed by multiple companies like ANSI is the first company which is designed SQL. And whatever database I have used or as I say list of databases like Oracle, SQL Server,

DB2, whatever, this language is a safe. SQL is an exact almost 90% is same maybe one or two syntaxes will be changed But maximum SQL is the same for all the databases So if you learn SQL language, you can easily

interact with any kind of database It can be Oracle MySQL SQL Server DB2 or any other databases Because SQL language is a common for all the databases slightly there may be some keywords will be different But other than this the concept

is exactly the same So SQL is a language by which we can communicate with the database. So why we need to communicate with the database to perform these operations?

Suppose I want to create a table, I want to insert some data in the table, I want to update existing data, or I want to delete the data, or I want to retrieve the data from the table. So to perform these kinds of

activities or operations, we need a language called as SQL language, Structure Query Language. okay so sequel is a language by which we can communicate with the database to perform certain number of activities so that is called as sequel so what is this

first SQL means structure structure query language okay structure query language and shortcut we can also call as a sequel some people call SQL and some people call them as a sequel structure query language use it to communicate to the

database to perform to perform different kinds of operations different kinds of operations so that is all about yes so database is a storage area and where the data will be stored and to communicate with the database we need a language that is called as SQL so

SQL is a language generic language which is common for All kinds of databases if you learn this particular SQL you can work with any of these databases Okay Now so let's move on to the next one so database components so far we

have understood what is database and Every database is having two kinds of components client and server So now I will talk about these components. What are those database components? so in real time Exactly what happens is every database is

having two component case. There's a DB database having two two components One is called as a client component. The other one is called as a server component Client is one component server is one component. What is this client and software or client and

server? So basically server and client both comes under the database only but server is responsible for storing the data and actually in the server will store the data and the client will send the request to the data. So

suppose I want to perform certain operations on this patler data. So in the server, the data will be there. So I want to perform certain actions or operations on this patler data. So I want to select some data. I want to insert some

data in the server. I want to update some data. So we need to produce these commands. We need to provide this command to the server. from where we need to provide those command. So we need another kind of layer from where we can

execute this command. We cannot directly execute the commands directly in the server. So we need a kind of a tool from where we can execute your commands. And the client is a tool or component from where we need to execute all your SQL

commands. So all your SQL commands will be executed from the client system. But before executing them, we need to connect to the server. So client software, these are also software. Server is a software, client is a software. So once you have installed Oracle

database or once you have installed MySQL database, by default client and server both will be installed. But if you want to only install the server or if you want to only install the client, you can install only those software, if you don't want

both. By default, if you install the database, you will get client and server both.

But server is responsible for storing the data. And to retrieve the data or to perform certain operations on the data, we need a client software. So we need a client software. So server data, server software required huge memory because it will store the

data and client is a very lightweight tool. It will not occupy much space. It is just a tool, small like a notepad or something like that, or notepad++ like that. So this simple editor will be provided. From there, we can execute all your

SQL commands. And those commands will be executed on the server side. Okay, so every database is having two components. One is client, the other one is server. So server have the data and client will send the request or client will always perform certain

operations on the data which is there in the server. And one more thing here is client and server will no need to be have in the same place. So server will be installed in some remote machines and client can be there in other

machines. And we can have both in the same system or we can separate them into multiple systems. But in the real time environment most of the time servers will be there in the remote systems. So you don't have database software installed in your

local systems. You will be provided just your laptops. In your laptops you don't have the servers. Because in the companies there are n number of people will work on n number of things. They need a database. Huge number of people will access the

database and they will create the tables, they do lot of things. But you don't install the complete database server software in your local system. But in the real time what happens is, this server software will be installed on the remote machines. Either

normally 90% of the time, the server will be installed on Linux or Unix operating systems. So the database server will be installed on Unix or Linux operating systems most of the times. Windows is very very rare. Because Windows operating system

most of the times will be used as a client. So server data because even Linux and Unix will be provided lot of security because the data is very very sensitive. So if you compare with the Windows and other operating systems, Windows will

provide very less security than Linux and Unix operating systems. So in the companies most of the times they will prefer Unix and Linux systems as a servers. So your database servers will be there in your Unix and Linux

systems in real time environment. So now let us see this particular situation. So in real time there are multiple people are working on database. So how these things will organized. So in the company the database installed on the remote machine. Let us

say this is my remote machine. So this is my remote server or machine. Here my database is there. So here my database is installed. This is database server.

And now there are n number of people are trying to connect to the database.

Like this guy is there and this is another guy and this is another guy.

There are n number of people are working. So these people should able to connect to the database and they will perform the activities on this database data. But how these people will connect to the database? So these people should install the client software

only so everybody should install the client software in their systems and By using this client software they can connect to the database server So they will provide some details at the time of connection So they will provide the name of

the machine port number login details and by using those details our client software will connect to the database and The client itself they will be editor will be provided so there we can execute all the sequel commands and those commands directly execute on

the database through this connection So client is very lightweight software so we can install them in your local systems But we never install the database servers on your local system So individual people don't have the database server because this is very costly

and it contains a lot of data and your local systems become very very slow if you install this particular software in your local system. So this is exactly happening in the real time environment. So servers will be there in the remote machines and

you will be provided by connection details and you have to install proper client and through the client software you should be able to connect to the database. So this is a concept of client and server. So every database if you install MySQL you

will get client and server. If you install Oracle, you will also get client and server. If you install SQL server, you will get client and server. So there are so many clients also there. Server is always one, but the clients can be anything.

Okay, I will show you in few minutes how the clients will be, how the server will be, okay? So one server, we can connect any client. Suppose if you see this particular scenario, the database server, let us say this is a MySQL. Let

us say this is a MySQL database server or let us say this is a Oracle. So this guy is using SQL Developer. SQL Developer is a client.

And this guy is using Squirrel Client. Or this guy will using Toad as a client. And this guy can use Command Line Interface. So even Clients are, there are two types of Client. Command Line Interfaces are there and GUI

Clients are also there. So these people can use any different Clients. But all the Clients will be connecting to the same server. and all the clients require the connection details at the time of connecting to the server. Again, I discussed

about client and server. What is client and server? Server means can hold the data. Can hold the or means server.

Data is actually stored in the server. That is called database server. This is database server. So normally this will be in the remote machines. So most of the times it will be there in the remote machines. And you will be provided the connection details with

those connection details you will be able to connect. So data is actually stored in the database server. But what is the use of client? We can send SQL commands using client software using client software So what are the examples? Suppose if I take Oracle is a database server for that

there are n number of clients are there like SQL developer and again clients are there are two types of clients so two types of clients So some people will like to work on UI and some people will like to work on command prompt

So that is up to you whichever client you are going to choose But the ultimate goal is same so all the clients we can use it for exhibiting our sequel commands So there are two types of clients again, so one some of the

clients are graphical mode so graphical mode they will use this will provide you GUI all the tree structure buttons or UI everything will be there It will be just a mess or document or excel sheet document And there are other clients will be

there that is called command line CLI clients. We can say CLI means command line interface. So there are two kinds of tools are there. But when I use graphical mode of client, we no need to remember any commands. So all the

things will be there itself. We need to just know how to use them. But when you work with a command line interface, you need to type all the commands manually. You have to remember a lot of things. Okay, so some people will feel

difficulty in working on CLI mode, some people will like Graphical mode, some people will like Command Line mode. So these are the two different clients are available. For any database, any database even MySQL, Oracle, SQL Server, any database having two different type of

clients. Graphical clients and we can also say GUI clients and CLI clients. Now I will show you a few examples. So for example, if I take Oracle as a database operator, This is also from Oracle Company. So if the Oracle Database,

if I take, there are so many clients are there. So for example, let's say SQL Developer. So these clients by default you will get once you have installed the databases. So or else if you don't have, you can also install these clients separately.

You can download these clients and you can install separately. So for Oracle Database, SQL Developer is one of the client. And this is a default client. And SQL Plus is a command line interface. So this is a GUI client and this is a

command line client. So there are multiple clients are there. So this is a GUI client. So this is a graphical user interface client and this is a CLI client.

So we'll see only a command prompt where we need to execute all the commands.

SQL plus and we can also connect some third party clients but these two are the default clients of Oracle and we can also other tools like we have a toad is available, toad is another client C, O, A, T, O, D is available

and then we have a squirrel is available and many more there. So many clients are there. But SQL Developer, SQL Plus, these two are the default client. By default, whenever you install your database, by default, you will get two clients. One is GUI

client, one is CLI client. But other clients, if you want to use, you have to download and install them. But by default, if you install any database, you will get one GUI client, one CLI client. So if you install Oracle, you will get

SQL Developer Client and SQL Plus Client. One is GUI, one is CLI. And suppose if you install MySQL as a database, you will also get one GUI client called as a Workbench. This is a GUI client, SQL MySQL Workbench. We

can call it as a MySQL Workbench. MySQL Workbench, this is a GUI tool. And we don't need to write more commands there. It's very simple to use it.

and the command line client is we have mysql command line is also there so mysql mysql command line tool is also there so this is a CL like line this is a CL like line and in the mysql we can also use stores these tools

we can commonly use for almost every database even we have aqua data studio also there this is also more popular client Okay, so because these are the generic tools are available guys. So by using these tools we can connect to the any database.

Aqua Data Studio is also there. So by using these tools we can connect to the any databases, not only specific database. Okay, Squirrel, Tool, Aqua Data Studio also supported multiple components. These are the generic client software. So these

are all nowhere related to MySQL, Oracle. So Oracle and MySQL by default will give you some clients. But if I don't like those clients, we can also go for another third party client. So these are the third party clients are available. Okay, by

using those clients also, we can connect to the any databases. All right. So this is all the concept of SQL server. So server and client. So these are the two components. So we have seen what is the client and server.

So server is basically storage area where the data will be stored. The client is a software which we need to use to write our SQL commands to connect to the database and write your commands. That's all about the client Okay, so now we

will see a few more important database and schema. We'll talk about later So here I will talk about this so first now so far. We just discussed about the concept So we understood what is DBMS? What is our DBMS? What is database? What

is exactly sequel and what is client and server? So these are the fundamental topics which we need to understand before going to use databases. Okay. So now throughout my series of sessions, I am going to use MySQL as a database because Oracle also

we can use it, but Oracle is a little bit complex to install in your system. So that's the reason I'm just choosing MySQL as a software. So we can just download this and you can install MySQL in your system. On Windows, you can

work with that. So moreover, this is an open source software. So MySQL is a open source database software which is available in the market. So now I will show you how to install MySQL, how to download and install MySQL software in your system.

And I will show you two clients. As soon as we have installed on MySQL, we will get two clients. One is MySQL Workbench and MySQL Command Line Tool.

So all the SQL commands we are going to execute in Workbench or Command Line Tool. Okay, so once we have installed MySQL software, then I will show you how these clients will work, how to connect to the database server by using these

clients. And even though if you have a database server in your local system, we should use client to connect to the database. So sometimes, suppose in my case, if I install MySQL, I will get the client and the server both in my same

system. But in that case also, we need a client software, we need a server software, both will be there in the same system. In that case also, we need to use client software to connect to the database and executing your queries. All right.

So now we will see how to install MySQL database on your Windows operating system. And then I will show you what is Workbench. This is a default client of MySQL. This is a GUI client. And also I will show you

command line interface. Both I will show you, Workbench as well as command line tool.

So once we have understood this, then I will show you a few important things, how to connect to the database, how we can see the tables, how we can see the databases, all these things I will show you now. So let's start with

the installing SQL, MySQL. So throughout my sessions, I'm going to use MySQL as a database because SQL is a common language for every database. If you learn SQL in MySQL, tomorrow if you're working on Oracle, still the SQL is saved. And if you

are going to work with MSX, MS SQL Server, again SQL is same. OK, so once you learn the SQL, you can work with any other databases. You will not see much differences. But maybe 1 or 2% syntactical differences will be there. But almost

everything is the same. OK. So now I'm going to install MySQL on my Windows operating system.