SQL Full Course 2026: Step-by-Step Guide & Transcript

[00:08] you make, every product you order, and every report a business creates, all of it depends on data. But here is the real question. Where is all the data stored? How companies retrieve exactly the right record from the millions of rows and how

[00:23] data analysts, developers, and database administrators manage information without confusion? And that's where the SQL comes in. According to the US Bureau of Labor Statistics, database administrators and architects continue

[00:36] to remain important for organizations that need to store, secure, organize, and manage large volumes of data. And as companies continue to use data for decision-m, reporting, applications, and AI systems, SQL remains one of the most

[00:49] essential skills for anyone entering tech analytics or back-end development. With that said guys, I welcome you all to this session on SQL certification where we are going to master databases, query and real world data management.

[01:03] Now before we begin our session, just a quick info guys. Simpan has got analytics, generative AI and adaptive systems in collaboration with IHFC and TI of IIT Delhi. This program actually helps you master AI powered analytics

[01:17] using Excel, SQL, Python, R, PowerBI and Azure along with hands-on projects and real world case studies. You're going to gain 180 plus hours of learning, 40 plus exercises, 15 plus industry projects and capstone projects, plus access to tools

[01:31] like Tableau, PowerBI, AWS, Azure and GNI tools like chat, GPT and clot. Learners will also receive a certificate from IHFC, TIH of IIT Delhi along with job assist plus for réumé support and mock interviews and career guidance. So

[01:46] guys, hurry up now and join the course. The course link is mentioned in the description box. Now before we move ahead in the session, here is a short question is what does SQL stands for? And your options are simple query logic,

[01:59] structured query language, system quality language or software query list. Please mention your answers in the comment section below. Database is a collection of database objects. So I'm writing something right. So this one is

[02:14] okay for you because I'm not good in writing because always I'm using the keyboard. Okay. So, but I'm to make the inter interactiveness in the session. So, I'm using what is that one? So, by writing

[02:28] using what is that one? So, by writing so something here. So, if you are asking me in real world what is the purpose of databases means databases means any application

[02:49] database only correct that is the first thing if you are developing any application for example so the Facebook application

[03:08] developing they want to store their data per suppose those for suppose if you're per suppose those for suppose if you're taking Amazon products details in the database. Amazon wants to store or insert or update all

[03:24] those data in the database. Even if you're posting some post in the Facebook also, so all the post details we are storing in the database only, right? We are storing in the database only.

[03:42] databases in databases also we have so different types we have types of what is that one? So we have types of what is that one? So databases

[04:01] So or otherwise we can call it as what is that one? So relational is that one? So relational databases

[04:13] non relational so that means we can call it as NoSQL so that means we can call it as NoSQL databases

[04:26] we have vector databases are also available. So might be in some articles we can able to observe vector databases uh we are using in AI especially in artificial intelligence to store the

[04:42] data in a different format but we are not in a different format but we are not focusing on these vector databases we are focusing on only what is that one so relational databases

[04:56] is the relationship between this relational relational and NoSQL NoSQL databases. These relational databases also we can call it as SQL databases. Right? So internally

[05:11] as SQL databases. Right? So internally these databases are using SQL. SQL means what? So structured query language but NoSQL databases we are not language but NoSQL databases we are not using SQL. So we are using NoSQL.

[05:24] So which is nothing but uh we are not using SQL query language. instead of that. So these NoSQL databases are providing some APIs providing some APIs but you are all new but I just want to

[05:39] uh give you some information. So what is relational database situations we can use relational databases and at what type of situations

[05:52] we can use NoSQL databases. So let's take a scenario simple So let's take a scenario simple scenario.

[06:06] So somebody is questioning. So relational databases. Oh you're answering. So if you it is a relational database. It is establishing

[06:18] a relationship between the structured tables. But it's not something like that. So we can try to understand. For example,

[06:31] so for example, let's say we are having employees database for suppose. What is employees database? For example, it contains some employee table

[06:47] what is that one? So the employee and also some other tables might be in that according to the application there might be some other tables will be involved in that particular application right there might be some other tables will be

[07:00] there might be some other tables will be involved in the particular application not like that so try to understand so first if you are talking about

[07:16] relational databases this right so we have employee number name or otherwise I'm just taking so another database for your understanding so take

[07:28] any social media application as an example so like Instagram have so the post if you're posting a post

[07:43] so post ID and also the content of the post and also so like some other details for suppose

[07:55] the post ID and also content and also the creator of the post Can you please tell me is it a best practice to keep this

[08:09] post uh for suppose Instagram application can be developed by using so the uh relational databases? No why? What is the reason

[08:25] relational database tables for example if you are talking about here rely on what is that one? So the schema in the relational databases we are relying on what is that one? So the

[08:38] relying on what is that one? So the schema example if you are taking so the content. So this content must be only one type that might be string

[08:51] that might be string or otherwise text we can say or otherwise text we can say only in the Instagram we posting only the content only the text type of content always it's not like that right

[09:04] in Instagram so might be I am posting some text some others posting might be some text some others posting might be video and some others are posting audio always we cannot expect so the content should be text.

[09:18] So in such cases so the relational databases are not suitable. So if you're asking me that what type of situations we can go for relational databases is so always whenever we are dealing with

[09:32] what is that one. So some common schema across all the records which we are inserting per post then we can go for relational databases. So but what is database first of all? So database is mainly for storing the data

[09:46] database is mainly for storing the data of course for what purpose? Storing and manipulating data. So simple definition is storing and manipulating the data. Storing, manipulating and securing

[10:06] so the data. So for that one so we have so databases. storing our application data in the database and we are manipulating. So

[10:21] deleting all those operations we are performing correct all those operations we are performing even if I want to maintain my calendar application okay whatever the meetings I scheduled

[10:38] and whatever the activities I'm attending right so I have a separate application this application is keep on updating so the records in the database but uh As a developer we have to choose which database is required, which

[10:53] database we have to select. So that is SQL database or NoSQL database or something like that. For example, if you are asking me if you are going to store are asking me if you are going to store employee records for for example

[11:07] employees. So we have an employee table which is capable of holding all the employee records. So we have employee number and employee name and address. [snorts]

[11:24] and employee name and address. [snorts] If you're asking me so this employee number whatever the details you are entering in the database performing right so that operations are mainly for what is that one so

[11:40] manipulating database for example so I'm specifying so one sur specifying so one sur so delhi or suppose

[11:52] so two subu from hydroad R3 John from San Francisco.

[12:12] So now tell me so all the records are following so the same schema right? So this record is following the same schema. All the records which we are inserting. So all the records are following the same schema. Schema in the

[12:24] sense the structure. So this one requires employee number. Employee number should be of type integer. So what is the schema for this employee table. So we have employee number whose type is integer and also

[12:39] the employee name whose type is text type type and also the employee address whose type and also the employee address whose type is also what is that one the text type.

[12:52] So what I'm saying is whenever we are having structured data structured data in the sense all the records which we are inserting should follow the same schema same rules which we defined in the schema. So what

[13:08] rules we define? So we define some data type we define some constraints all these rules should be satisfied. to each record. So then the type of

[13:20] databases we can call it as structured databases. So structured databases yes but uh these structured databases are also relational

[13:37] talking about so the structured manner structured behavior with respect to relational databases but after that might be I will think I will talk about so what is a relational database also so first try to understand

[13:55] certain schemas uh schema will be defined per yearly. So for example another example I am giving for example if you are taking so the department table per suppose this one is having so department number

[14:09] this one is having so department number and department name this is also what is the data type text data type

[14:21] so what I'm saying is almost all the records which we are inserting so you must follow the certain schema That means you must provide so department number value as integer and you must provide so department name as

[14:36] text and location also you must provide it as text but all the applications just think about yourself all the applications will support this one for example let's say if you are

[14:50] one for example let's say if you are having so HR system for suppose uh or otherwise HRM application

[15:07] uh majorly what are the functionalities we have? So they have to manage the employee details and they have to manage their skill right. What are the skills that the employee

[15:22] have and they have to manage their personal and they have to manage their personal details employee personal details. if you're joining in a company might be you have to fill up all these things in

[15:37] portal right? So your personal details and all those things. So like that there are so many things should be associated with an employee. So if you are taking HR system, so that one is a broad system which contains not

[15:51] only very specific details to the employee. There are some personal details and also compensation details for example compensation details for example especially so compensation details

[16:08] employee what is the variable pay what is the normal pay and all those that table compensation details but try to think about if you're having all these tables

[16:25] So but what about the data whether the data which is present in the tables should be related right all these tables contains data for example. So these all these tables contains data

[16:46] correct if the data is not related how can you manage the employee details it's not possible for example I'm an employee for possible for example I'm an employee for example of simply learn for example

[17:00] I'm having so my personal details and my skill details and what type of courses I am delivering and also I'm having some other details also like my compensation details and also some other details

[17:15] which are relevant to me right so all the details will be show stored in a single table no

[17:27] so each detail will be stored in a separate table and they are what is that relation relationship with one another table. But there are some cases we have to observe. So this is super super important case. Try to understand.

[17:43] important case. Try to understand. There are some cases. suitable. So that means especially

[18:05] Can you please tell me what are the cases? If you are asking me for example if you are taking Instagram application or Facebook application simple example Instagram or Facebook

[18:21] whatever the post you are posting whether the post is following so the whether the post is following so the certain structure certain schema no certain structure certain schema no if you're taking post for example

[18:35] each post is having so different content we cannot expect so the same type of data we are expecting from the user Correct. So one user might be posting audio, one user is might might be posting image and

[18:48] one another user is might might be posting video. So we cannot say all the data should be satisfying that that certain schema in such cases.

[19:03] in such cases. So we can go for what is the databases? No SQL databases. NoSQL means the every record present in NoSQL means the every record present in the database is having their own schema.

[19:17] the same schema like structured databases in NoSQL databases. So every record right so this is super important every record.

[19:40] so own schema. So in the sense for example if you're having post per suppose the post will be written as so the JSON post will be written as so the JSON object so which contains the post ID

[19:53] object so which contains the post ID so 01 and also uh this one is having so some details right so content might be so this is right so content might be so this is image but suppose

[20:08] and second post if you are talking about so the post ID two post we cannot say. So the first post post

[20:21] structure and second post structure should be same. So this post ID is what is that one? So 02 and the content will be

[20:36] if you are observing. So here this one is image, this one is text and also this one contains some extra attributes also for example so name so technology for example so name so technology something

[20:54] this is another record so can you please tell me all the records are following so tell me all the records are following so same schema same structure no we cannot expect it is not following the same structure Each record is having so their

[21:09] own schema. So you have to think as a architect for example while developing the application what type of database is suitable in the particular context.

[21:21] If each record is having their own schema so not common schema in such cases we can go with what is that one? So no SQL databases correct. So no SQL databases [snorts]

[21:36] but uh so if you're observing so if you're asking me as an application you're asking me as an application developer per suppose

[21:49] and NoSQL databases but the most of the consumption will be what is that one consumption will be what is that one SQL databases only.

[22:03] structured data almost I am getting what type of data? Structured data because if you are working in an enterprise applications almost the data will be what is that one? So the structured data

[22:20] asking me so there are some situations yes we have unstructured data also.

[22:33] what in in image just need to record that uh the video or image like that type of data. Yes. Yes Maria. So based upon your

[22:45] expectations we have to decide so what is the database and okay all right so you understand so database but I'm showing in a practical database but I'm showing in a practical way. So for example so what is database?

[22:58] So simply I can say the definition of database will be in your books might be if you're a fresher might be reading through books books right through books books right in the books in academic books. So there

[23:12] are multiple definitions we have in a practical point of view. If you're asking me what is database means database is a collection of

[23:32] Database is a collection of what is that one? So database So if you're asking me so what is database means database a database is a objects. What are the databases?

[23:47] What are the objects will be present in database? Anyone? Can you please guess it? So, first one is tables. If you are taking any database, so we have several types of objects, tables, views.

[24:02] So, synonyms, what is that? Synonyms. Indexes. Indexes are mainly for performing uh improving the performance of a search

[24:16] query indexes. So clusters

[24:35] There are several objects present in the database but the most important thing you want to we are working with this one is tables. We need to understand so how to create the tables and how to relate the tables

[24:51] but we are not working with some other objects might be we are working on views also and we are working on indexes as well and we are not touching so

[25:03] database first if you are a basic learner you should know about what is that one so how to create tables and how to provide the relationship between the tables that this is the reality

[25:17] and the second priority will be what is that one how to create views and third priority will be what is the purpose of creating indexes. So the synonyms clusters so functions. So after that so might be

[25:33] we are discussing so we are discussing about functions but procedures and all those are all some advanced concepts that will be covered in advanced SQL but don't think about all these things.

[25:49] So if you're taking any database for suppose that may be war or MySQL or any database these are all objects will be present in the database that might be present in the database that might be tables or views or synonyms or indexes

[26:04] or anything. So what are the companies that are providing so database solutions? So Vacra right? So is the one of the enterprise level database.

[26:20] Veracle corporation is providing Veracle database and also MySQL is open source. So open source license that means anybody can use without license also

[26:32] and DB2 so which is from IBM actually. database. So database relational database is a

[26:45] concept actually right. So based upon the concept so the multiple vendors they provided their own implementation. Verac is providing their own implementation. Mysql is providing their

[26:57] own implementation. DB2 and also Postgress. implementation. So the postgress SQL we can call it as

[27:10] can call it as but in this session in this training so we are using what is that one the MySQL so which is open source right which is open source and most of the intermediate

[27:24] applications right intermediate level applications we are using MySQL so all come all these database vendors are providing this database softwares but try to Understand if you are taking any database software that might be

[27:39] any database software that might be veracular or MySQL or DB2. veracular or MySQL or DB2. So what I'm saying is the objects will be same. So the tables we have to work on what is that means how to create

[27:52] tables and how to insert the data and how to work with views and how to work with indexes. These concepts will be common okay across concepts will be common okay across multiple vendors.

[28:07] Is it clear all of you what I'm speaking? Is it understandable? So these are also different companies they are providing their own databases. But in our curriculum so

[28:20] what is that one? So we are using so we are using so MySQL and also another important concept we have to understand while working with the databases we need to understand. So two terminologies one is WTP

[28:35] two terminologies one is WTP and WAP. Anyone could you please guess it? Anyone have an idea about sop

[29:03] So, WTP stands for what is that one? So, online transaction processing system. I'm just writing it here. What is that? So, online

[29:18] So, OLTP stands for what is that one? So, online transaction processing system. So, it's a simple right. So for example because if you are working with databases how the database being

[29:31] utilized for example online transaction processing system means it is a data processing system means it is a data entry system. So we can simply say data entry systems. So might be your in daily life you are

[29:46] observing almost all the systems are what type of systems systems. Can you please tell me some examples for OLTP systems means might be you are booking a systems means might be you are booking a flight ticket?

[30:06] right? Booking a flight means it involves several operations. those things. It is a data entry system only right indirectly. If you are

[30:19] booking a ticket through online, you're making your data entry and you're performing uh so ticket booking. consider for example so ticket booking and also

[30:35] hotel booking. Uh we can see uh like uh new Aadhaar card creation or new PAN card creation. So these are all data entry systems.

[30:50] So that type of systems are called as OLTP systems. OLTP systems majorly mainly used for performing. So what type of operations? Insertions, updates

[31:04] and delete. So these type of operations we can So these type of operations we can perform basically in OTP applications. Almost nowadays if you're observing almost all the types of applications are

[31:18] WLTP applications only but if you're asking me what is the but if you're asking me what is the difference between sop and systems means what is that one? So online analytical processing

[31:42] important so you should know about whether the system is system orp system whether the system is system orp system so if you're asking me so the it's an analytical process for suppose you're having huge amount of data for

[31:56] data If you're asking me Amazon customers data, it's a global business, right? In a global business, there are so many

[32:08] customers are there. It's in pabytes or zabytes. So from the customer data, you want to get some insights per suppose.

[32:20] So the people who are actively purchasing electronics, the people who are actively purchasing some other goods per suppose. analytical operations and you are generating some reports.

[32:36] So this is super important. If you are generating some reports based If you are generating some reports based upon

[32:52] operations or deletion operations or anything. We are not going to manipulate the data. We are just performing what is that one? So we are using the data.

[33:11] and reporting purpose right purpose. So that type of systems we can call it as OAP systems. Now we need to

[33:26] understand the differentiation rate. So database we are using in two purposes. Either we can use that one as OLTP systems. systems. So for online transaction processing or

[33:39] either we can use that one for what is that one systems. If you're asking me where we can use for online analytical processing means so might be your manager is asking some insights about what is that one. So some

[33:55] business per suppose we need to provide some insights. So finally we are generating the report. How we are generating the report? Some visualization report might be you are representing the data in a visualized

[34:08] uh in the form of bar chart or otherwise pie chart or something to differentiate the sales or to differentiate so the data for suppose. So such type of systems we can call it

[34:23] as OAP systems. But almost if you are observing in government offices observing in government offices almost all the systems are what is the OLTP systems online transaction processing systems. That means through

[34:38] applications. So we are inserting the data, we are data, all these operations we are performing. Is it clear all of you? What is OLTP and OAP? Do you have any questions?

[34:54] I'm just looking at the chart I think. So previously I'm not addressed any So previously I'm not addressed any question.

[35:08] when it comes to database objects, we need to create a database from scratch. need to create a database from scratch. Yes.

[35:26] tables and other types? Okay. So I will give you some examples. Okay. So I will give you some examples. No worries. First basic question is can you please provide some examples for [snorts] OLTP and OJP?

[35:40] [snorts] OLTP and OJP? Okay. Say you're working in a company you are working as a customer support engineer for suppose

[35:58] So what we have to do? So we are receiving calls from the customer and we need to raise the complaint or something. Can you please tell me? So raising a complaint is a WTP or OAP.

[36:16] WTP correct? Yes or no? So raising a complaint comes under what type of system? WLTP system. So why WLTP system. So why you are entering? So complaint details?

[36:31] So what is the cause and everything? So we are raising a complaint as a customer we are raising a complaint as a customer support engineer for suppose for example. So somebody's questioning somebody's raising uh asking me as a

[36:44] customer support engineer. So uh my ado a devops system is not working or otherwise so something there is a problem with my laptop. What I have

[36:57] to do I need to fill out some form right and I'm raising a complaint. So that and I'm raising a complaint. So that type of systems are royalt systems. If you are raising a complaint so automatically what happens? So that

[37:09] complaint will be received by IT team. Right? So that complaint will be received by the IT team. So again IT team is resolving that problem. So IT team is trying to resolve that problem. So they

[37:23] are updating the status. Right? So they are updating the status. So updating status is also comes under what is that one? So only because already have that data. So they are updating the data.

[37:40] Some other examples might be [snorts] easiest examples. You are booking a easiest examples. You are booking a flight ticket.

[37:53] So booking a flight ticket comes under what is that one? So OLTP correct? So online transaction processing but after that what happens after booking flight ticket so you're not uh possible to uh continue the journey on

[38:08] the particular day so what we have to do so we need to update the booking for so we need to update the booking for suppose we need to update the booking

[38:25] performing coming on the data might be insertion or updation or deletion all these comes under what is that one OLTP operations can you please tell me so some examples related to related to AAP operations online analytical processing

[38:42] mostly the data analytics people right so data analytics so these people they are using some tools so like powerbi so Microsoft PowerBI

[38:58] and also SQL and they are using some Python and they are using some Python all these tools they are generating. So all these tools they are generating. So this WTP oil processing per suppose

[39:12] means what? So online analytical processing analytical means what? So they're going to perform some process the major outcome will be what is that one? So they're expecting some some report.

[39:26] they're expecting some some report. So the outcome will be so they are analyzing the data and uh finally they they are generating some report

[39:38] right for reporting they are using some tools like PowerBI for analyzing the data so they are using SQL and Python okay like that in each stage there are some tools will be used but finally expectation will be so they

[39:54] are going to generate some report. This report will be used for what is This report will be used for what is that one? So decision making. All right? If a leadership want to make any decisions,

[40:07] they will use this report. So if you want to so generate report by analyzing the existing data. So then we can go forward that one. So while AP systems there are two paths for you, right?

[40:23] So one is you have to work as work as wellp developer for suppose. So WLTP developer for suppose So WLTP developer for suppose system developer

[40:46] nothing but web or mobile nothing but web or mobile or any application developers. that one? So the YTP systems only but if you're going for online

[41:01] analytical processing. So if you are going for online analytical processing so mostly data analytics comes into the picture right. So they have to generate the reports they have to get the insights from the

[41:16] data. So most of the professionals will be from so data analytics. So for that be from so data analytics. So for that one also uh we need SQL right? We need SQL. We need Python. So some technologies we

[41:31] need Python and we need some tools for reporting. So like PowerBI so some tools are which are required for reporting and all for app developers. So

[41:44] we need to learn Java or Python. Okay, we need a So what we can say? We need to learn full stack development. We can say full stack development.

[41:59] This is one track but most of the people nowadays trending analytics right? I can see even in simply learn or

[42:12] outside also. the people who are more concentrating on SQL so they are migrating to this area. So like data analytics so the people are from so different domains right so marketing

[42:27] domains right so marketing HR course finance is comes under banking only and also insurance

[42:47] so there are different domains the people are working so logistics right so management is also one of the important domain

[42:59] so why these people are learning so the SQL the reason is they have to transform their careers into what is that fun so the data analytics yes very good so the medical good question so medical

[43:14] good question so medical and all those things and R means there are lot of differences is there but I cannot discuss sak here uh because this is not a Python session

[43:27] uh because this is not a Python session right Python and R R is mainly used for machine learning okay so the Python itself it is providing some libraries like scikitlearn uh tensorflow so some libraries that are

[43:41] very good in what is that one so performing machine learning operations so just for to your high level. So I just want to understand your track actually. So can you please post it in the chat?

[43:56] I'm just just observing what is your track whether you want to become an application developer or your focus should be on so data analytics. What is your track? So could you please post it in the chat? I just

[44:09] want to understand see ultimately I'm getting so like data analyst data scientist these roles are getting demanded nowadays in the market right so your focus should be on what is that

[44:24] one so working on data only I cannot see the application developer that's gone I worked as an application developer previously also I worked with Java many number of many years uh Java front end technologies I can

[44:40] able [snorts] to develop the fullstack applications very quickly applications very quickly but nowadays you know so like from 10 but nowadays you know so like from 10 years uh or 7 years

[44:53] so this data is getting very demanded the people who are having so very good domain experience they can easily handle the data they can they can easily handle the data they can easily understand the data like that

[45:07] so that This is a okay I will tell you simple example why you are learning SQL what are the roles might be you are suitable for so if you are learning SQL the first

[45:23] the first most thing is your domain knowledge most thing is your domain knowledge so plays important role in your job and second one is your technical knowledge

[45:37] right so what is that one so your technical knowledge and this technical knowledge after that so usage of a tools

[45:53] whether you are comfortable with a tools or not like that so I think uh Sudha is asking some question for entering day-to-day data

[46:07] so we can use OLTP for generating reports and making decisions. Yes, we'll reports and making decisions. Yes, we'll use OAP. Correct. Yes, correct. But when coming to the applications, if you are observing,

[46:20] it's a combination, right? If you are observing some software applications, it contains all the parts. Some of the parts will be OLTP systems. Some of the parts will be analytical processing systems. If you are logging into the

[46:35] application in HR portal, HR can able to get the analytics how many employees are going to what is the attrition rate and all those things there comes under analytics right how many people are not interested in the

[46:50] company HR should know if you are logging with HR details the application will display how many employees are leaving next month how many employees are leaving next year all the analytics might be HR can able to

[47:04] attrition rate and all based upon the attrition rate so might be HR can able to initiate some what is that one so recruitment plans and all in the similar way new employees are getting onboarded in the company if new employees are

[47:20] getting onboarded means that type of system is OLTP system correct the new employees getting into the company means they have to create create so new user so new user for that particular

[47:34] employee. All these things will be taken care by the HR people. If you are talking about the HR application, that application contains both OLTP and OAP, online transaction processing and online

[47:48] analytical processing. But try to understand the differences. Analytical processing is more about what is that one of doing some analytics and getting insights from the data for decision making might be improving sales

[48:04] and improving the customer experience or improving some other details. These are all decisions which is required for management level right level is it clear all of you what I'm speaking

[48:19] so that's why most of the people like I can see from your personas mostly you are interesting in what is that on analytics side

[48:31] and also it is not easy right so if you are getting into an application developer right you have to learn Java And all these tag it will take many time many years

[48:44] years but uh now it is becoming simpler but what happens means getting into the analytics job so data jobs any data job any data job so it is getting demanded nowadays because because of this machine

[49:01] learning and AI and generative a all these stuff is it clear all of you got some idea so got some insights what exactly your career path and all

[49:13] why I'm taking so might be somebody of some of you might be asking me question so why subu you are speaking all this nonsense so you need to understand your path right so but what are the difficulties in that

[49:28] path also you have to understand what is what is the effort you have to keep also you have to understand so that's why I just speaking nearly 1 hour about what is that so what are different paths you can choose it like that

[49:44] is it clear so can I proceed further let's jump into our concept I'm not going to spend more time with that one I already brainstormed a lot what is the

[49:56] path you have to select I am suggesting so mostly if you're having domain knowledge you're very good with your domain what you are working better go domain what you are working better go with analytics

[50:11] a good in Java, I am good in so Python or any programming language. So we can go it as what is that one so the fullstack developer and use SQL and all those the fullstack developer can easily transform but the based upon the

[50:27] domain right so I'm in banking per suppose my domain [snorts] is banking uh if I want to developing in logistics per suppose I want to develop an application in logistics so I'm new to the domain maybe I have to

[50:42] understand something what are the operations of logistics and how this one works and Okay. Like that. Yeah. So now when coming to the databases, the first step what we have to do is

[50:57] so we are using what is the database we are using? So MySQL. So throughout this session so we are using so the MySQL database.

[51:09] I am encouraging so to install so your personal machines because you can simply learn is providing the lab also but I am suggesting to install so what is that one so in your personal machines as well

[51:23] so I will guide you how to install so install MySQL

[51:41] company don't install it. That means your company laptop don't install it. So if you are using company laptop

[52:04] restrictions are there in the company laptop. So don't install it. So if you want to work with databases, there are several databases we have. So might be you are asking me why we are using only MySQL

[52:20] because this one is open source actually. Okay, we don't want to worry about licenses and all. So that's why I'm using MySQL. So where to access SQL means I will tell you. So just wait for some time. Yeah,

[52:35] where you can install it. All those things I will tell you. So just you can observe. So go to the Google just follow my steps. Even if you want to ask me to repeat, I will repeat it three times. But please

[52:50] bear with the instructions. Don't write on your company laptops because the company laptops does not allow these installations. allow these installations. So I'm just installing. So the MySQL

[53:08] or 8.1 or 8 MySQL 8 download for suppose

[53:20] What is that? So the my escalate download in the chat you can go to the first link

[53:37] right so you can see 8.0.45 45 or any version is sufficient. No

[53:50] And uh the operating system most of the people are using Windows only. So you can go with the Windows operating system.

[54:02] So if you're coming down, we can able to see different sizes of this uh one right. So otherwise we can see click download page. download page. I think uh uh nam uh for the Mac we have

[54:16] to check. Okay. So because we have to select select DMG file right. So in the Mac so we have so the Mac OS also available. So you can just download that particular

[54:29] one. So installed what you installed actually. So you followed some options and install some setup right. So but what happened behind the scenes? What are the installations we made? So those

[54:42] are all super important things we have to understand. If you're asking me so the MySQL or any database software, it's a server software. Please listen carefully. This is super important for

[54:56] carefully. This is super important for any database or anything. Uh so so that is the problem. Correct. Install workbench. Workbench is just a client. Okay. Just wait. Okay. I will

[55:12] come back to you. Seems I need to interact with you. Okay. I think OB. interact with you. Okay. I think OB. Yeah, I can just wait for some time. After the discussion, I will come back to you. I know. So different

[55:27] experiences. So try to understand first what exactly you done up to now. If you are doing installation right. So this is the step

[55:45] there are two things first what is this MySQL actually MySQL oracle or whatever it may be this one we can call it as DB server what is that DB server

[56:02] so in a company if you are working in a company. So this installation cannot be done in your personal machine. Your database machine, some virtual server. For example,

[56:16] uh so my company, for example, I am working in some bank. My company main working in some bank. My company main branch is located in Hong Kong

[56:28] branch is located in Hong Kong or otherwise United States per. So my business is more related to what is that one? So United States.

[56:40] is that one? So United States. Yes. Yeah. I need to maintain so my database server right. So for that United States I

[56:52] created one uh virtual machine that means I need to create one I need to means I need to create one I need to maintain one my DB server. about so DB server means what? So that is nothing but

[57:19] training purpose we are installing server software in this machine. will be installed in your machine. But uh try to think about in a real

[57:32] environment server is not in your machine. So database server database software will be installed in a separate location. So separate infrastructure will be proen

[57:46] separate infrastructure will be maintained for the particular server. But you are a developer per suppose for example you are an SQL developer. So you want to communicate with that server

[58:00] correct? You are an SQL developer per suppose or SQL developer or otherwise what we can say you're working on analytics. So you want to communicate with the server. So we need some tools

[58:19] communicate with the server. So if you are working with the SP especially MySQL so MySQL is providing one tool called as what is that one? So MySQL workbench

[58:34] workbench so it's a graphical user interface. so it's a graphical user interface. So this is a GUI tool. to communicate with the MySQL server which is running

[58:51] on so might be my server is located in London or USA or Hong Kong or anywhere because when coming to the business where is their business location for

[59:04] suppose the bank is located in somewhere some other location or the particular business is located in some particular region are maintaining their server in

[59:16] different locations. But as a developer we need to perform the operations. So we are going to connect to that server with the help of some tool some graphical user interface tool. So GUI

[59:31] stands for what? So graphical user interface tools right.

[59:47] So with the help of graphical user interface tools so we can able to so interface tools so we can able to so communicate with the servers actually. tool? So that is nothing but MySQL workbench.

[1:00:03] So whenever we are doing installation right for training purpose what we are getting. So for training we are doing so full setup right full installation we are choosing

[1:00:16] right full installation we are choosing full installation. So while doing full installation so we are getting some MySQL 8.0

[1:00:35] So which is nothing but server. We need server engine. So server engine which is mainly for processing MySQL statements.

[1:00:47] And also we need some client tool right. So which is nothing but workbench. One of the client tool which is mainly for communicating with

[1:00:59] the server. And also we have so some other tools also some other softwares also will be installed. So MySQL router installed. So MySQL router what is that? So MySQL router

[1:01:15] application developer if you want to connect to the MySQL so the application wants to communicate with MySQL. So we are using so MySQL router but right now as a part of this training this is not essential

[1:01:31] training this is not essential and also it is providing so MySQL shell. So this is also another tool. So this one is providing some bash shell

[1:01:45] some Linux shell to communicate with what is that the MySQL database server and also it is providing some command line also.

[1:01:59] So these are all tools will be provided for you. these two right one is workbench.

[1:02:11] So what is the reason we are using mostly this one is GUI so graphical user interface we don't want to worry about writing more code and another one is what is that one so the command line

[1:02:26] the command line so if you want to write the query okay so you can use what is that one so the command line if you want to write a query through command line so if you want to execute

[1:02:39] the query so we can use what is that one so the command line like that. So we have so the all these options will we are getting with full installation but in the company environment we are not responsible for installing

[1:02:53] MySQL. So there is database server is already running. Okay. So the MySQL server is already running in some other location. So with the help of workbench we have to connect to the server

[1:03:09] with the help of some protocol called as TCP by IP. TCP by IP. So this TCP by IP is a network protocol. So this TCP by IP is a network protocol. What is TCP? TCP stands for transmission

[1:03:22] control protocol which is mainly for so establishing a communication establishing a channel between client and server applications. But to establish a channel between the two applications, it requires some port

[1:03:37] number. What is that? It requires what is that What is that? It requires what is that one? So the port number.

[1:03:49] so that may be MySQL or Vacu or otherwise any application if you are that application is running on some port number. So during the installation so we can see this port number. What is the port

[1:04:03] this port number. What is the port number?306. This is the port number. So by defaultly the MySQL is getting

[1:04:18] uh attaining the port number explicitly. So MySQL is getting this port number by default. You don't want to worry about anything. Just click next next. So automatically your installation is getting finished.

[1:04:31] But the thing is think about it's not MySQL it might be my HR application or it might be some other application. Every application if you are running the application is associated with what is

[1:04:47] that one some port number. Okay. So what does MySQL server contain?

[1:05:00] Yes, all client and server all are included. Correct. You can get server software and we can get client software also in your machine itself for training purpose. We are installing both server

[1:05:14] software and client software both. Is it clear Sedro? Okay. So once you done the installation, right? So your server is running or not. How can you check it out? The next question

[1:05:27] is what is that one? So the next question,

[1:05:57] everybody will have in mind how can you verify whether the server is running or not. So for that one so we have to go to the So for that one so we have to go to the services.

[1:06:12] application right so that application is running as a service for example if I'm running so MySQL so MySQL is running as a service you can go MySQL is running as a service you can go to the services

[1:06:30] nonsense so there are so many services are listed here the reason is in my machine by default Definely there are some windows services are running along with that one. So there are some applications are all

[1:06:46] installed. So adab acrobat this one is for PDF opening PDF files right. So that service is running. Yes.

[1:06:58] Similar way my MySQL software is also one service. Please remember MySQL is also what is that one? One service. So just type M and we can see

[1:07:15] so MySQL 80 here. Look at this. All of you can find the service name during the installation. So I notified what is the service name So I notified what is the service name it is taking MySQL 80 by default because

[1:07:30] it is taking MySQL 80 by default because we installed. So MySQL 8.0 Z version by default the service name is taking as what is that one so the MySQL 80 so we can just keep the service name as it is and we done installation the same

[1:07:47] service name is getting reflected here so try to understand and also what is the status of course it is running means by default whenever I machine I'm starting my machine Right. By defaultly

[1:08:02] the server is getting started. So could you please check all of you in Windows services all of you can able to find this MySQL 80 service those who installed MySQL in your machines and like this message

[1:08:30] >> Where did you where did how did you uh open this services? >> Uh yes I understand. So again I am showing. So please look into this one. showing. So please look into this one. So go to search box in Windows and just

[1:08:43] type services correct correct >> okay. So we can see >> okay. So we can see all the services and in the services

[1:08:55] you just click on any service type M. M means what? So I need to display only means what? So I need to display only the services which are related to MySQL. That means M is starting with M. So we can see so the MySQL 80 service which is

[1:09:09] running already and just explore try to understand the how the Windows machine is working. what are the other applications are running and all those stuff. So please like that message you can able

[1:09:23] to identify the MySQL 80 service is running or not. So I'm suggesting so this MySQL service is running or not. So it is running.

[1:09:38] So the next step is what is that one? So check it. So the client tool what is the check it. So the client tool what is the client tool? So MySQL workbench. So look at this. There are so many client tools are available, right? What

[1:09:50] are the client tools are available? What are the tools available to connect to the server? MySQL workbench. So the MySQL workbench. MySQL workbench. So the MySQL workbench. So click on that MySQL workbench.

[1:10:12] I am just uh expecting so all of you please open that MySQL workbench and like that message then only I can able to understand all of you can able to find that one otherwise I cannot identify

[1:10:27] please do that step and like that message. So still we are not connected. So we have to connect to the server.

[1:10:43] post it in the chat what is the and which port number server is running which port number server is running and which port number server is running3

[1:10:58] see here already some MySQL connection is available here. Can you please tell me another question I'm asking the server is running in my machine or remote server? Remote machine. Remote means some other

[1:11:12] location server is running or in my machine itself it is ser running my machine itself not remote s correct right. If you are accessing so simply learn lab.

[1:11:28] So good question. What is the difference between remote and local? If you are accessing simply learn lab might be the simply learn server is might be the simply learn server is located in USA the lab server

[1:11:42] you are connecting from MySQL client to what is that one from workbench we are connecting to so the simply learn server but here we are installing locally so that means in your in my machine itself

[1:11:58] so server is present so you can just double click on So this MySQL connections local host it is asking so the password what is the password I provided what is the common password I suggested

[1:12:12] root so click okay so that's all so you are successfully connected connected yes almost if server is running means

[1:12:25] to that machine others here it is populating so connect to in the MySQL connections It is just showing so local instance just double click on that one and what is the password we have to provide it here

[1:12:41] what is the password so root right so click okay that's all but by default it is showing administration and schemas if you want to see what are the databases present in your

[1:12:54] environment that means in your database environment so you can just look here we can see so the banking DB employs all these databases right so like that no you have only three schemas will be there might

[1:13:08] only three schemas will be there might be HR CIS and secil be HR CIS and secil so in companies do we use same MySQL 80 for coding good question right

[1:13:22] so in companies you are asking me so to you so same MySQL means no there are different tools are there you must See that's why I'm explaining right so client tool is different server is different so the tool there are

[1:13:39] several tools are available so just I'm naming here some of the tools so maybe you can expect if you are working in SQL environment so SQL working in SQL environment so SQL developer is one of the popular tool

[1:14:01] But most of the people are using so the SQL developer and MySQL workbench. What is that one? So MySQL workbench. But all the tools So MySQL workbench. But all the tools are having similar options.

[1:14:17] simple, right? So only we can connect and after that these tools are providing options for you to write the SQL query and execute the SQL query. Okay? So no worries. These tools are very easy to use it. how we are using Microsoft Word,

[1:14:31] how we are using Microsoft SQL in the similar way we can use these tools. No worries at all. So writing SQL query is difficult. Writing using the tool it's not a problem, right? It's not a big deal. Yeah.

[1:14:46] deal. Yeah. Yes. The procedures will be almost same. Yes. The procedures will be almost same. Uh it's based upon the tool. Okay. So I cannot say like you can easily mingle with the tool. That's what I am saying.

[1:14:59] If you know if you're you're having knowledge of using so what document how to format what document how to increase size and all right so if you're having that knowledge that is sufficient to use this tool that's

[1:15:14] what I'm trying to tell you is it clear all of you yeah see look at this once you are getting so see look at this once you are getting so connected to the database server right

[1:15:28] so first AP is what is that one? So we have to create databases.

[1:15:44] which are already present. These are all created by me previously.

[1:15:57] don't delete any database from your end. So these are all created by me. So for the previous sessions or something.

[1:16:18] these are all the default databases which we are getting. Correct. we are getting. Yes. So precash. So you're asking

[1:16:34] uh how to reset the database. There is a procedure will be there but I cannot discuss here. It will take 1 hour. So it's a complex process. it's a complex process. Okay. So that's why I'm requesting.

[1:16:47] So if you want documentation or anything I will share you the link. If you want to reset the password you can raise the ticket also no problem. But in the reset the password it will take more time. Actually you need to troubleshoot

[1:17:02] where exactly you are facing problem. Is it clear? will tell you okay what are the steps you have to follow and all.

[1:17:18] So we have so three databases like HR secila and also sis if you want to create so another database for example so managing employees details. So how can you create the databases? You just right click here.

[1:17:34] You can just click on so create schema. That is one step. That is one step. Correct. Or otherwise. Correct. Or otherwise. So go to here SQL tab here.

[1:17:52] you're getting so this uh SQL file right here I'm specifying. So create database. database name? HR DB per suppose HR DB

[1:18:09] HR DB per suppose HR DB or otherwise we can say HR database. creating a database. So if you want to execute this query,

[1:18:24] how can we execute this query? So just click on so this uh look at this first select this query and click on what is that one this option execute the select portion of the script

[1:18:39] or otherwise we can keep the cursor in the same line. You just keep the cursor in the same line and you can click on this one. So execute the statements under the keyboard cursor. So I'm preferring. So

[1:18:53] first select the query and execute that query. So look at this and execute that query. So look at this database is created. Right in the bottom observe my mouse cursor in the bottom. We can able to see

[1:19:09] create database HR database. It is created or not. It is in green color. So we can refresh. So observe my mouse cursor, mouse So observe my mouse cursor, mouse pointer. If I'm clicking refresh, I can

[1:19:24] able to see one database, right? What is the database? HR database. So I'm just sharing this query in the chart. So HR is by default present. I am

[1:19:38] provided my name as HR database like that. So different name I am providing it in the chart. So please check it out whether you can able to create the database or not.

[1:19:52] So if you want to open this SQL file right so how to open

[1:20:07] is that one. So this new file so automatically this one will this will automatically this one will this will open the editor actually. So this will open the editor. Is it clear?

[1:20:24] database. All of you please create two databases All of you please create two databases and let me know. Okay. So let's start with the discussion.

[1:20:40] servers. How can you know so whether the MySQL service is running or not? So just MySQL service is running or not? So just go to the services. I'm just checking. So my MySQL service is running or not. How can I check it? So just I'm typing

[1:20:54] How can I check it? So just I'm typing so services. services are running. So especially I'm looking for the MySQL service. Just type looking for the MySQL service. Just type M.

[1:21:11] this service right. Could you please check out check it in your machine also whether the MySQL service is running or not. Of course in my machine it is running. You can see the status it is stating like it is running.

[1:21:28] So if it is running means what the server is ready installing. So yesterday we installed the server and machine. So the server is started automatically.

[1:21:44] So we don't want to worry about restarting the server and everything. Okay. So now the server is running. So what we have to do? So we have to connect to that server.

[1:21:56] tool. Right. What is the client tool we are using? So the MySQL workbench. So this is a client tool. There are several tools are available. I'm not

[1:22:10] talking about so only this one is the client tool, right? So there are MySQL command prompt is available and also as well as MySQL workbench is available. So there are several tools are available. Just establish a connection with the

[1:22:26] server. So from this client tool if I'm speaking what is the client tool actually I'm expecting responses from you yesterday already I discussed right what is the client tool what is this MySQL

[1:22:41] workbench what is the purpose of MySQL workbench I am expecting responses from you please type it in the chat type it in the chat what is the purpose of MySQL workbench

[1:22:54] very good Nikita to communicate with server right we have server running in my machine itself yes server is capable of processing what is that one so all the details right

[1:23:09] whatever the queries we are giving okay so all the queries will be taken care by the server my data also will be stored in the my data also will be stored in the server but what I have to do

[1:23:22] I have to interact I have to communicate with the server yes it's say engine which is capable of uh processing your SQL queries and all right so if I'm giving any query what happens the query will be executed by the server

[1:23:39] the query will be executed by the server itself this is just a client tool it's an user interface so from this user interface we can able to write the queries and send the queries but I want to connect to that server

[1:23:53] can you please tell me What are the things required for connecting to the server? I think we heard about port number correct. And which port number? So the MySQL server is running.

[1:24:08] Anyone could you please guess it? 306. Correct. So just establish a connection with the server. So you just double click on this

[1:24:22] MySQL connections which are already present. decreasing my pace. I understand

[1:24:35] if I'm just double clicking on this MySQL connections automatically it is establishing a connection with what is the service connection with what is the service MySQL at the rate of localhost 306.

[1:24:48] MySQL at the rate of localhost 306. You can look at this service here. with the MySQL service which is running on

[1:25:00] the MySQL service which is running on what is the port number. So 306

[1:25:16] what is that one. So root password. So root is the password I just connected to the database. So now we have so so many databases. I'm just removing whatever the databases that are present. Okay. How to remove

[1:25:32] the databases? You just right click on whatever the database you created and we can able to see all the options right which are related to the database. I'm just deleting the database. So drop schema.

[1:25:47] Drop schema means what? So it is deleting whatever the databases whatever the database you selected it is dropping the database.

[1:26:02] for suppose. So let's create a new database. So let's create a new database. So for that one we are using SQL right? So can you please tell me what is SQL? Most of the people don't know we are new

[1:26:17] to this SQL. So SQL stands for what is that one? So structured query language, right? I'm just maximizing this one.

[1:26:45] perform so different types of operations. So what are the different types of operations we can able to perform? So based upon the operations right so this SQL also

[1:26:59] can be divided into what is that one? So some other languages. might be uh just bear with me because most of the learners will be you know uh

[1:27:16] if I'm going fast right what happens means if I'm not recapping whatever the contents which are already present yesterday discussed yesterday most of the people are missing the connectivity

[1:27:28] the people are missing the connectivity okay that is the problem no worries okay okay that is the problem no worries okay so we'll try to

[1:27:40] entire cohort right so SQL stands for what is that one? So structured query language what is the structured query language can you please

[1:27:52] tell me I might be you are already in your academics you written some programs but suppose you are having a file text file if you want to manipulate the file

[1:28:04] how many lines of code you have to write it for example I'm having a text file which contains the employee data if you want to insert data delete data how many how many lines of code you have to write for example in C language might be most

[1:28:20] of the people are go gone through the C language in your academics long back so please remember at least minimum 10 to 20 lines of code we have to write it but what is the specialty of this SQL is with a single line of code

[1:28:37] with a single line of query we can able to perform what is that one so complex operations that's Five this one we can call it as fifth generation language

[1:28:50] and also very very important thing please remember we don't want to write 20 lines of code everything will be getting abstracted abstracted in the sense we just write

[1:29:03] the query for example I want to retrieve the data from employee table so you just the data from employee table so you just write so select star from emp Right? With the help of this simple query, we can able to fetch the all the

[1:29:18] employee details. Correct? We can able to fetch all the employee details. That means what is the logic behind the scenes? We don't want to worry about

[1:29:33] what is that one. So writing 20 or 100 lines of code, right? So writing 20 or 100 lines of code like that. Could you please increase? We could see zoom a little bit. Yeah.

[1:29:51] So definitely I will do that one. Now it is okay. Fine. I think Maria. Oh okay great. So please give me the sations that type of might be if you are

[1:30:03] not that uh content is not visible or something. If I want to write a simple SQL query right here I'm writing simple SQL query. So behind the scenes there is a script will be executed. The script is going to

[1:30:16] perform operations on the table. It is going to fetch all the records. But one good thing with this SQL is we are just writing two or three lines of script per suppose SQL script. With

[1:30:33] the help of the script we can able to perform some very complex operations. complex operations in the sense might be fetching the data from the server or inserting the data, deleting the data or might be we are creating a new

[1:30:48] database or we are dropping the database. Okay, all these operations we are going to perform through SQL. But how it is possible? How it is possible means all the

[1:31:03] database management system vendors. Can you please type it in the chat? Who Can you please type it in the chat? Who are the database system vendors? that are providing database as a solution? Veracle.

[1:31:17] solution? Veracle. Okay. So the MySQL is open source DB2. DB2 is provided by IBM International Business Machines. So these companies already developed some scripts. We don't want to worry about what is that one. So

[1:31:32] writing some complex program to perform these operations of course. But uh if you want to work with SQL based upon the query we are writing

[1:31:48] might be all the situations I am not going to perform uh retrieval only right. So based upon the operation I am performing these queries can be divided into different languages. One is called as

[1:32:01] what is that one? DDL. So DDL stands for So DDL stands for so data definition language.

[1:32:16] DML. DML stands for what? So data manipulation language.

[1:32:35] we can call it as DQL also. So data retrieval language.

[1:32:47] languages also we have might be whenever we are working as a database administrator right so might be we have to set the permissions and all but the major these three languages we should know data definition language

[1:33:03] data manipulation language and also data retrieval language what is exactly so data retrieval what is exactly so data retrieval language

[1:33:18] retrieval language sorry data definition language first we are talking about so DDL right. So DDL stands for what data definition we need to understand the difference

[1:33:32] we need to understand the difference between so data and data definition. between so data and data definition. What is that? So data and data You need to get some clarity about this one otherwise you don't know what

[1:33:46] definition and what operations we are performing on and what operations we are performing on so data. For example, let's assume that so data. For example, let's assume that so we have an employee table.

[1:33:59] This employee table contains some fields right? So employee number and also name and also address.

[1:34:13] So we have data. So employee number one and name is Subu and address so Sam Chennai for suppose and employee number is to James. So USA can you please tell me what is from this

[1:34:29] table. So try to understand what is data and what is metadata. I need to know about two terms. What is that one? So what is metadata that one? So what is metadata and what is data?

[1:34:45] metadata right metadata deals with what is that one? So the struct schema is that one? So the struct schema so simply we can say so schema what is the structure of the particular table. Yes. So from the academics might be you

[1:35:00] know the definition of metadata. So it is nothing but data about data. Right. But in a practical point of view if you want to understand so what exactly metadata. So if you are taking employee table so this is the table name. So for

[1:35:15] example employee is the table name which contains what are the fields employee number of course whose type is integer type and name it's a vat type. So

[1:35:27] generally in the case of databases every column right what are the columns for this table employee number name and address employee number is integer type and name is var and also the address is also what

[1:35:43] is var and also the address is also what is the type so type course. So the table name and also followed by

[1:35:55] column names and what data type we are providing to that column and what column. Constraint means what? So now you don't know about what is the constraint but uh again I will come back I will

[1:36:11] all but uh here try to understand soar means what is that one? So string. So now itself you can feel like uh where

[1:36:24] car means what? So simply a string. I will discuss about what is the difference between so the car and wire car. Okay. But high level integer means all of you know integer means what? It is accepting. So numeric

[1:36:39] values only without decimal point. So name means what? It's a string which is a group of It's a string which is a group of characters and address is also a string.

[1:36:54] language, there are different data types are available. In the similar way in the database terminology, these data types will be represented as

[1:37:06] what is that one? So the vare so int from database to database these data types will be different. Okay, I'm not saying I cannot say in MySQL also these are the data types whether these data types are same in

[1:37:22] other databases means that means in var also it will be same no in var instead also it will be same no in var instead of integer so we are representing number instead of care we are representing vare 2 so like that so don't worry about so

[1:37:40] remembering all these data types Because nowadays nobody is relying on what is that punch. So data types and all syntaxes and all everything will be taken care by the AI tools. Okay. Only just we have to understand what is the

[1:37:55] just we have to understand what is the concept. definition? data definition if I'm simply saying practically

[1:38:08] simply saying practically data definition majorly deals with your schema schema is nothing but so the table name followed by the column names and its data types and also additionally if you are

[1:38:24] applying some rules additionally if you are applying some constraints or additionally you are applying any other components These are all comes under what is that one? So the metadata

[1:38:37] but what is data here data is nothing but this is the data actually correct. So whatever the records present in this table. So this is called as what is that the data

[1:38:56] So what is the difference between data definition and uh data? Data definition majorly deals with the structure of a table. That means what is the table? What exactly what type of data

[1:39:10] it is allowing? Because if you are working with these databases, right? Mysql and all these are all relational databases. So these are all allowing structured records. Structured records

[1:39:24] means what? All the records present in this table. All the data present in this this table. All the data present in this table must follow this metadata. Must follow this metadata which is nothing but every record must contain employee

[1:39:38] number, name and address. So that's what I'm trying to say. Morty again I will come back. So what is care and what is the difference between care and what is the difference between so care and care. Okay. So no worries.

[1:39:52] Right? Now we can just try to understand high level. So based upon this one so you understand. So data definition and data. So if you are talking about DDL right? So DDL stands for what?

[1:40:05] So DDL stands for what? So data definition language. some keywords to perform operations on. What is that one? M operations on

[1:40:26] perform any operations on schema then we can go with what is that one so DDL schema means what so the overall structure of your table if you want to perform any operations on overall structure of your table

[1:40:41] not with data that's what I'm saying please remember you are not working with data we are working with what is that one so data definition what type of operations generally we can be able to perform. So if you're

[1:40:54] thinking we can create a table right so create we can create a table right so create and also alter and also alter and also drop.

[1:41:13] So if you are observing these three key words right so create alter and drop. So create means what? So what we can say we can create any database object correct. So create is mainly for what is that one? So creating

[1:41:34] I'm not talking about. So only table yesterday I already discussed what is database. Correct? Database is a collection of database objects.

[1:41:46] Database contains tables, views, synonyms, clusters, indexes, so many things. If you are creating any database object that might be either table or view or synonym whatever it may be correct,

[1:42:02] right? So for that one so we can use what is that one? So this create is mainly for creating any database object. It's not which is specific to

[1:42:14] the table. That's what I'm trying to tell you. And alter is mainly for so think yourself for example [snorts] we are already having so a table for example.

[1:42:33] if you want add or drop a column or you want to change the data type of a column. Okay you want to change the data type of so column

[1:42:48] or you want to change the size of a column. So these are all comes under what is that one the alter

[1:43:00] that one the alter alteration is nothing but what? alteration is nothing but what? So making some changes to the schema. We have already some schema already table is present or any object is

[1:43:12] table is present or any object is present for suppose might be uh that might be either table or stored procedure or function whatever it may be. If you want to make some alterations we can use this alter command.

[1:43:25] Alteration in the sense it's not creating so entire thing from very scratch. You just want to make the changes to the existing one. So that one comes under alteration. And finally, so the drop.

[1:43:39] So drop is nothing but so dropping a table, table, right? So dropping a table.

[1:43:51] completely we are removing. So try to understand the terminology So try to understand the terminology here. Mostly in the DDL only in the data definition language only we are using so this drop operation

[1:44:04] deleting data if you are deleting if you are working with data per suppose you are deleting one record as per the terminology we can call that one as delete but if you want to drop any object so

[1:44:20] try to understand from my voice also from my mouth also it is coming drop why I'm using the drop because I'm dropping an object entire because I'm dropping an object entire object. So whenever we are dropping

[1:44:34] object. So whenever we are dropping whenever we are removing entire object any database object from the database we can use this drop command. So we have another option also truncate but right now I'm not talking about

[1:44:48] truncates I will discuss please note down that one. So whenever I'm discussing truncate at that time I will discuss. So we have another language called as what is that one? So data manipulation

[1:45:01] language. This is super important for you. This is super important for you. So data manipulation language.

[1:45:16] manipulation language. So what exactly data manipulation language? So we are working with data. So that means insert. So update

[1:45:44] just a second. So insert update. So delete. So these operations we are performing on performing on data. It's not performing on schema.

[1:45:57] Yes s you are asking question. These operations are done on data. Yes. It's not on schema. Correct. Your thinking is correct. S we are not going

[1:46:10] to work with schema. We are going to work with what is that one? So the data whenever we are working with data right. So most probably we are performing these type of operations. What is that one? So insert,

[1:46:22] update and delete. So I hope all of you understand what is the difference between so the schema and what is data

[1:46:34] right? First we have to build a schema according to that one. So we have to insert the data right because whatever the data you are inserting so that uh follow certain rules for example if you're taking so here we are inserting

[1:46:49] one record this is following what is that one so certain schema so we have so employee number and also name and address this employee number is of type what type integer type

[1:47:03] and employee name is string type and address is also address is also So what is the type? So string type. the data we are inserting so that would be bounded with the particular schema.

[1:47:20] So let's jump into the practical and we can create one first table first and we can create one first table first of all. It's not a part of of all. It's not a part of okay so our lab practice and all okay

[1:47:32] just we can per your understanding. So I'm just creating the table. Once you are comfortable with creating table right, I will share you one document. So for creating table and all once you are getting comfortable with the concepts we

[1:47:45] have to do it on your own. Okay, that is the requirement. All of you are ready. Can I jump into a practical part of creating a table?

[1:48:06] first of all so these queries are super important initial queries see if you're asking me how many databases if you're asking me how many databases are there in my system

[1:48:21] correct I just want to check what are the databases are present in my system so it is showing four but But I'm not looking is showing four but But I'm not looking here. I just going to SQL.

[1:48:33] here. I just going to SQL. I just want to write an SQL query to see how many databases are currently in my server. So you can make use of what is server. So you can make use of what is the command show databases.

[1:48:51] be ended with semicolon. So I just select this query and execute it. See in the result we can see all the databases right it's more than four because we are not

[1:49:04] it's more than four because we are not refreshed it seems so we have so whenever we are issuing show databases

[1:49:16] uh let just a second I will zoom in zoom this

[1:49:32] for this. So the zoom option is I'm getting so

[1:50:18] reset grid that is okay but it is not showing any size here. charge GPT or otherwise how to increase the size.

[1:50:32] the size. Okay instead of writing this one

[1:50:51] right. So you can always ask what is that one? So charge how to increase the

[1:51:16] response right. So we follow the steps but suppose edit preferences under SQL editor change the font size and click okay. Method two

[1:51:33] result set grid it is saying so the same options we choose go to edit. So preferences under the SQL editor.

[1:51:46] under the SQL editor. So you can just go to the query editor.

[1:52:03] colors? Adjust the font under result set grid. grid. So here

[1:52:16] let's see so whether this one is getting so increased or not

[1:52:48] So we cannot find any option. So the zoom option here, right? You can find any option zoom option here. This one is just find option. It is not. So the zoom option like that. Okay. Anyway, I'm sharing this uh

[1:53:02] sharing this uh query with you all of you. So, please follow this query. I will just try to increase.

[1:53:14] Just give me a second. Okay. Don't be hurry for anything. just wait. So, instead of that one, so let's make my differences here. Okay.

[1:53:29] let's make my differences here. Okay. So result grid they suggested right that is for result grid and SQL editor also I'm just increasing so the size for also I'm just increasing so the size for suppose the 30

[1:53:46] editor so now it is coming so properly right so now it is coming so properly right it is visible now all of you

[1:54:00] providing intellisense also if I'm clicking so show show databases so that automatically it is getting populated if I'm selecting this query and I'm executing it is displaying so all the databases

[1:54:12] for me HR HR database and all so this is the query which is mainly for what is that one so displaying all the what is that one so displaying all the databases

[1:54:27] line. So command line means what? So lines you can use hyphen right

[1:54:46] So iPhone so display so display all databases. displaying all the databases. Little bit I'm decreasing because it is very

[1:55:03] difficult to perform operations. I'm just going to so same preferences and I'm just decreasing. So the size in SQL editor. So 30 I provided. I'm just making it as 20.

[1:55:22] So [snorts] let's make it comfortable. So now it is fine. the fresh one. So go to SQL. So now it is fine. So show

[1:55:34] databases it is visible. Right? Now it is it is visible. Right? Now it is comfortable for you and all of you. So now I want to create what is that one? So HR database per suppose or

[1:55:48] otherwise some employee database. How can I create it? can I create it? So create a database employees or otherwise employee DB.

[1:56:05] What is the query? So just a simple query is create database. employee DB while providing the database name. Right?

[1:56:23] So just provide the database name that's all. So just I'm providing so employee DB. Don't provide any spaces or anything. If you want to specify underscore that is fine. Just I'm selecting this query and

[1:56:36] fine. Just I'm selecting this query and I'm executing this query. How can you know whether this query is executed successfully or not? In the output we can see in the last one observe my mouse pointer

[1:56:49] observe my mouse pointer in the output we can see last one so create database employee DB it is green color green color means what so successful for example if I'm missing semicolon

[1:57:02] so if I'm trying to execute this query so then what happens it is giving error right so it is showing red color red color means whatso error What does it mean?

[1:57:16] So the database is not created. So of course already it is created. Okay. So always your query should be ended with what is that one? So the So all of you please create. So this employee DB quickly and after that so we

[1:57:31] will try to understand. So what is table and try to understand. So what is table and all. you can just put done message in the chart.

[1:57:57] So extra DBs means might be you can able to see the secular database and also SIS database and HR database. Right? Three databases by default we are getting

[1:58:09] these databases by default provided by the MySQL for you. If you're asking me what is SIS database means CIS database is a system level database that means even if you are creating a table here

[1:58:23] employee if even if you are creating employee DB what about the metadata for this employee DB this employee DB metadata also will be stored in system databases system tables in system database there

[1:58:38] are many tables we have if you are looking here there are CIS config tables Right? So internally whatever the objects you are creating that may be either database or table or synonym or function all

[1:58:53] those information will be maintained in this system database and these two databases right HR database and also secular database these database and also secular database these two two databases will be provided

[1:59:09] provided for your practice purpose that means this is we can we and call it a sample data set with the help of sample data set uh we can able to uh perform operations right but we are

[1:59:25] what it is provided and also my customized data set whatever the topic I'm explaining that's why we don't want to use the secular database and all

[1:59:37] to use the secular database and all okay like that is it clear so what is the database we pro created employee DB. So whether this employee DB is present or not. So how can you check it? How can

[1:59:51] you check it? What is the command to check whether the employee DB is present in your databases list or not? What is the command we have to execute? Show databases. Right?

[2:00:04] So command level I'm asking. So show databases. Now why what is the purpose of creating database? is a collection of what is that one? So database objects. If you're asking me

[2:00:18] what is the definition of database? Why we are creating database means inside we are creating database means inside database we can create so multiple. database we can create so multiple. So database objects.

[2:00:37] so what is that one? So the tables. So views, So views, so synonyms, so indexes. Okay. So functions,

[2:00:53] so procedures. Okay. These are all comes under what is that one? Database objects. You can create anything inside the database. But first majorly our focus is on what is that one? So creating a table.

[2:01:12] right if you want to create a table what is the command so create table what is the command so employee inside database I'm creating one database object

[2:01:24] what is the table name employee so employee number so in MySQL so we are providing the data type as integer and also name here this one is worker of

[2:01:38] 100 for example and also the address. and also the address. So worker of 100

[2:01:52] So this is the way. So we can create a table in MySQL. So here one more extra parenthesis we need to close. But what is this? Employee number is a column and name. And what is the type? So integer. It will accept only what

[2:02:08] type of values? integer values and name is what is that one so the name of a column and what is this type var of 100 so here try to understand so just

[2:02:20] high level I'm discussing what is the difference between difference between so difference between care and worker

[2:02:32] right so care and worker if you're asking me the difference if you're asking me the difference between the care and worker fixed length characters. Okay. If you are talking about so the fixed length

[2:02:47] are talking about so the fixed length string need to understand these two things fixed length string and variable length strings. So what is fixed length string? For

[2:03:02] example, let's say for the name you are providing so care of 10. characters for the name you must provide. For example, if I'm providing

[2:03:16] subu how many characters this string is me? Five right? Five characters. But what happens means it is appending some spaces. five spaces additionally

[2:03:32] why even if you are providing five characters but the car is considered as what is that one so the fixed length string so even if you are providing the name as five characters it will take it as 10

[2:03:46] characters only remaining characters will be appended with some spaces it's a bad drawback right if I'm asking so what is the length of the name it is providing so 10 characters even your name is so only five characters it is

[2:04:01] not providing so what is that one so the length has five characters so fixed length means what so even whatever the length you provided per suppose here I'm providing so 10 characters exactly it is going to

[2:04:16] allocate memory for 10 characters but what about variable length string so for variable length string so it is very flexible very flexible so if you're using so name var of 10

[2:04:31] even if you are providing so some name perose so RAM per suppose Ram is having how many characters three characters so if you're asking me what is the length of this name means

[2:04:46] so length of name is it is taking only three characters only variable length means what so maximum size is 10 characters maximum length is 10 characters in this 10 10 characters even

[2:05:02] will consider only that three characters only it is not appending any spaces in the ending. So that's why most preferable one is So that's why most preferable one is what is that one so care but at what

[2:05:17] type of situations we can go for care for example if you are representing a gender so for example if you are taking so for example if you are taking scenarios gender

[2:05:32] Right? So gender will be male or female. So male means we are specifying so M or female means we are specifying F. In such cases for gender what is the recommended data type? It will accept only one character. It's a fixed length.

[2:05:46] So that's why we can keep it. What is that one? The car one right? So whenever we are dealing with fixed length characters so then we can go for what is that one then we can go for so the car data type

[2:06:04] so most probably whenever we are storing string values right always we are dealing with variable length strings only not fixed length strings strings we can go with what is that one the vat type just highle discussion I'm

[2:06:18] providing So now with these details I'm just providing so I'm just creating a table. So look at that. So look at this. So currently what is the database we are

[2:06:33] currently using. That is also a good question right? So we have employees DB is present. Can you please tell me whether the employee DB is selected? If I want to create an employee table where I have to create in employee DB

[2:06:47] itself I need to create. Correct. I need to create it in employee DB. Yeah. So for that one so we have to make use of what is the command. So use

[2:06:59] use of what is the command. So use so employee DB. you can look at that. See this employee DB is getting highlighted here.

[2:07:12] What does it mean? So now currently selected databases. What is that one? Employee DB. So currently I selected this employee DB. So in this employee DB I need to create a table. Okay. So

[2:07:28] please remember only creating database is not enough. You create a database but inside the database if you want to create any object. First we have to select the database

[2:07:42] right and which database we have to create the table. So we have to select create the table. So we have to select what is that one. So the database. use employee DB. This one is mainly for selecting the database.

[2:07:58] So next step what we have to do. So we have to create a table inside the employee DB. So how to create a table? So inside this employee DB per suppose. So creating

[2:08:22] table so create table so this is the common syntax what is the table name so common syntax what is the table name so employee table we have to specify the column names so don't think about the cases okay for

[2:08:37] example so here if you are using keywords Right. So create so table keywords Right. So create so table these are all keywords correct. So these keywords you can use upper case or lower case that one is not case sensitive.

[2:08:54] So your schema is not case sense. That's what I'm saying. But your table data is are inserting right. So that one is case sense too. For example I'm just going with lower case itself. So create table. So employee

[2:09:10] so employee number whose type is integer and also the name var of 30. and also the name var of 30. So the maximum length is 30 characters

[2:09:22] and also address it should be more than that right might be you can keep so 100 characters but suppose address what is type so of 100.

[2:09:41] So it will create a table. So my question is how can you know Anyone could you please type it in the chat where I can check where I can check. So whether the table is created or not

[2:09:56] in the bottom you can see right. So first initial option is whenever we are executing the query we can able to see the output here. So create table employee this one is coming green color.

[2:10:11] So just read it. So that means the table is successfully created and also additionally so currently selected database is what is that one? So employee DB in the employee DB if you want to see all the

[2:10:26] tables right. So see all the tables then we can use command. What is that command? So show tables

[2:10:40] some experience with this basic commands right? Yeah that's great actually. So show tables then automatically what happens only one table is present in this employee DB. Okay so please complete this task

[2:10:56] first. Make sure that we are using so the employee DB and after that so create an employee table and also just check show tables but I don't know for example so I'm having some table which is already

[2:11:10] having some table which is already present in my database okay I just want to know what are the columns present in the particular table so that one we can call it as what is that one so describing a table

[2:11:28] describing a table. So for that one, what is the command we are using?

[2:11:45] right? describing in the sense it is showing so table and what are the data types associated with that particular column and whether it is null or not null it is providing so the complete information

[2:12:01] that means the employee number if we are talking about so employee name and address these are all accepting null values and is there any key will be assigned still we are not discussed about keys primary key and

[2:12:14] foreign key and I'll uh just discuss that part might be after the break or something but try to understand it is providing so the complete details complete description about your table it is

[2:12:27] providing so what are the fields and what are the data types so whether that one is accepting null or not and every column is associated with some key or not so all the information it is providing what is the query so now

[2:12:42] the next step is what is that one so DML Well, we create a table. So, but we have to perform. So, these operations, right? What are the operations? Insert. So, update

[2:12:56] and also delete we have to insert the data. So, update we have to insert the data. So, update the data and delete the data. So, for that one, so what what I have to do

[2:13:09] columns present in this table. So of course I can use describe right I can use describe to describe the table. So before that one I'm just asking so

[2:13:23] could you please confirm all of you are comfortable with my pace. What is the way I'm delivering the session? Is it okay? Can I decrease the pace or something? I'm not writing two times. If you want to execute the

[2:13:35] previous query so you can write it. You can just go execute it. For example, so describe employee all the columns. There is no need of writing. So query again and again.

[2:13:58] If you are speaking about DML, so the first operation is insert operation. But if you are asking me how to perform this insert operation means might be there are multiple ways we can able to perform this insert operation.

[2:14:11] So just look into this multiple approaches. The first approach I'm talking about what is that one? So the first approach in the first approach we have to insert the data into a table.

[2:14:25] we have to insert the data into a table. So insert into employee space values of if you are using the syntax right so you must provide so values for all the

[2:14:37] columns. What are the columns? We have employee number. So name and address you must provide the details for all the columns. But please remember in SQL especially the strings can be

[2:14:54] represented with single quotes not double quotations. double quotations. For example, so the name is John.

[2:15:10] Okay. So I'm just keeping so within single quotations right. some other programming languages mostly most probably you can see strings are

[2:15:24] represented with double quotations but when coming to the SQL especially so single quotes. Please remember that point and also try to understand the syntax. If you are observing the syntax insert

[2:15:40] that means I'm inserting the data into employee table but I'm not specifying for which columns I am inserting the data. Please try to understand here. So one key important thing I don't want to insert only specific columns. I want to

[2:15:55] insert data for all the columns of this table. Correct? because I am specifying so just employee of course so you must provide values for all the columns yes I provided so there are three

[2:16:11] columns are present I provided all the values so we can run this query and let's see so whether this record is getting inserted or not always please remember how can you know whether your

[2:16:26] query is correct or not whether is successfully executed or not. So in the bottom in the output section we can observe the last row last row observe my mouse pointer we can able to see this is

[2:16:41] successful but I'm not satisfied with this output right so this query is successfully executed but still I'm not satisfied I executed but still I'm not satisfied I want to just want to see the result

[2:16:53] so how can you check it so this is comes under body that DRL DRL stands per what? So data retrieval language

[2:17:06] correct? So data retrieval language. So here we can just make use of select query type. So select star from what is that query? So select star from what is that query? Select star from employee.

[2:17:19] So if I'm issuing select star from employee I can able to see so entire table right. So I have inserted only one record inserted only one record one John's San Francisco like that.

[2:17:34] So for suppose I want to insert another record how can I insert? record how can I insert? So insert into

[2:17:46] so employee space values of so two and bumsy so two and bumsy and he is from so the Bangalore

[2:18:00] so we can do it like this I'm just inserting the second record so just insert this record it is successful how can you just execute. So this DRL query so but what is the problem here?

[2:18:15] Let's say all the situations I am not providing so details for all the columns right. So let's say I take a situation for suppose I want to insert only the data for specific columns

[2:18:28] for specific columns I don't want to insert value for address I don't want to insert value for address insert into employee

[2:18:40] so space values of so 3 comma rajan okay so chennai

[2:18:55] not in inserting the address only two values I am providing whether please guess it whether this query will execute

[2:19:07] no why this query cannot execute why this query cannot execute any sessions

[2:19:20] this query in the employee table we are having so three columns but we are providing only values for how many columns columns two columns

[2:19:34] inserting data into the employee table you must provide the values for all the columns correct so in the third column if you are not providing so automatically we are getting by error see look at this this is wrong what it

[2:19:47] see look at this this is wrong what it is the column and count does not match the values count. So for that one so additionally we have to specify what is the value.

[2:20:01] So null value because I'm not inserting any value specifically especially. So we have to provide what is the value. So the null value. So now it becomes three values right? So three rajen and also null. So you can

[2:20:15] three rajen and also null. So you can just select and execute this query. So now we can run this query. So we can see so it is getting inserted right. So that means if you are not having a requirement to insert the third value

[2:20:30] but you must provide the value instead of that one. So you can just keep null or otherwise so similarly so you can just provide so open single quotation and closing single quotation

[2:20:46] right for the string values so which is nothing but empty right so if I'm just uh executing this query this one also will work

[2:21:01] the able it's already present the query is if I'm trying to insert the uh just getting the data see look at this this getting the data see look at this this one contains non value this one is empty

[2:21:14] so three rajan three rajan so this one is getting repeated two times is getting repeated two times because I executed this query so the same record I'm inserting two times right so like that so try to

[2:21:28] understand whenever we following this syntax right so always we are not inserting for all the columns in a table you just want to the columns in a table you just want to insert only specific columns

[2:21:48] you are providing some space don't use double quotations always use what is that one for single quotations only it does not work with the double quotations so in the programming languages might be you can able to See we have to use

[2:22:02] what is that one so single quotations only not double quotations in especially in SQL that might be you are working with MySQL if you are working with Postgress SQL even if you are working with any SQL any SQL language strings

[2:22:20] can be represented with what single quotations here look at this another strange syntax X this is the second approach but

[2:22:32] suppose if I'm keeping the title as what that one so second approach columns inserting the data into a specific columns

[2:22:56] so employee so we can specify what are the columns we are inserting any describe syntax star. Right? I'm not using any syntax star in describe

[2:23:08] command. So describe command just for describing the table. So describe employee. So this is star which is coming from the ID. Okay. So my

[2:23:20] query is describe employee. That's all. I'm not using any star here. Got it? Shock. I'm not putting any star inside that one only in the select command only we are using star.

[2:23:34] So now the second approach per suppose you can see see the second approach is very much comfortable. For example I want to insert only values for employee number and address.

[2:23:47] I don't want to insert the value for name. name. So we can use so the values of here I'm inserting only so the employee number and also address right so

[2:24:00] employee number is four and address is so USA per suppose only so the employee number four and also the

[2:24:13] address is USA that one is only getting inserted because here clearly I specified what are the columns we are inserting right we are inserting only for employee number and address you can just execute this query

[2:24:31] from so employee so address will be name will be null right because we are inserting only for

[2:24:46] employee number and address we are not inserting for name I'm just sharing these queries could you please check what is the difference between so the previous queries and especially the second approach in

[2:25:01] the second approach we don't want to worry about so putting null values right we are inserting only values for specific columns correct we are inserting values only for the specific columns like that

[2:25:16] specific columns like that so we'll start with the discussion So just get the data for suppose. So select star from what is that one the

[2:25:30] employee table and after that I will give you some exercise for you. You have to complete that exercise. So select star from

[2:25:53] able to see all the details of employee, right? So whatever the details that are present in employee table, all the details are present here. But what happens means I want to remove

[2:26:08] I want to delete this record. So this record is coming twice. This record is inserted twice. Right? I just want to delete this record. So please remember so generally what happens means in real databases

[2:26:23] we don't want to encourage to insert a duplicate data duplicate data but I will that one I will discuss later but as for the current scenario you want to remove this duplicate record

[2:26:39] might be somebody's asking right so we have so duplicate records I want to delete this duplicate record so for that one so we are going to perform another DML operation. So what is that one? So deleting a particular record.

[2:26:59] So how can you delete it? So we can make use of delete command right. So delete from what is the syntax we have to follow what is the syntax we have to follow here is delete from. So the employee

[2:27:13] so where so here where is a keyword which is so here where is a keyword which is mainly for specifying the criteria or condition what we can say what is the condition so employee number equals to

[2:27:27] three. So if I am specifying employee number equals to three, how many records are getting deleted? Can you please tell me only one record or two records?

[2:27:41] deleted. We don't have a choice here because so what happen means the name and also the employee number both are same. In such cases what happens? The

[2:27:53] same. In such cases what happens? The two records are getting deleted. So we can execute this query. So we can see the two records are getting deleted. I'm getting an error here. What is the mistake I made here?

[2:28:11] So the option is showing right. All are getting this error. So I'm sharing this getting this error. So I'm sharing this command with you.

[2:28:24] getting this error. What is that one? So safe update. So for this for this one so safe update we need to change some settings.

[2:28:39] So in workbench actually. Okay. So just copy this error. Okay. So just copy this error. So whatever the error we have. for suppose. I know where we have to tweak. So

[2:28:55] anyway, so we have to identify the solution, right? You're all having access to the charge GPT. We need to understand the feature of charge GPT as understand the feature of charge GPT as well.

[2:29:15] So what it is saying it is suggesting so some some of the options. So set up uh set SQL saved updates

[2:29:28] equals to zero. So we have to execute this query. This providing some this query. This providing some solutions for us. But I'm asking charge solutions for us. But I'm asking charge in workbench.

[2:29:58] Copilot means what? So it is generating the SQL queries for us.

[2:30:13] so it is showing. So select go to preferences go to SQL editor and also uncheck save updates. So let's do that same thing.

[2:30:27] So we have to go to the go to edit preferences. Here there is a check box might be. So you can check it out here. Save updates.

[2:30:43] you can check it out here. Save updates. So you can uncheck this check box. Just uncheck this check box. So where we have to go? So I'm just canceling again. I'm going. So go to edit and preferences

[2:30:58] edit and preferences and go to SQL editor. and go to SQL editor. In the SQL editor, we can just go down and we can uncheck this check box. So save updates and click okay.

[2:31:13] So now we can execute this query. So delete from employee. So where employee number equals to three. So still it is giving what is that one? So the same error right. So we can just reconnect it. But suppose

[2:31:29] I'm just closing this MySQL workbench and I'm reopening. So the MySQL workbench and I'm connecting to the server again. and I'm connecting to the server again. So please provide the password.

[2:31:47] closing that one and I'm connected back. So now again I'm checking the setting go to edit preferences and go to SQL editor and go down. So we can able to see this one is unchecked right. So that is fine.

[2:32:02] one is unchecked right. So that is fine. So now we can run this query. getting so different so error right. This one is not related to that one. So

[2:32:17] we are not selected the database. So first select the database first. So how to select the database? First use employee DB. Right? So this is the query we have to execute. It is successful. So now come down.

[2:32:36] So now after selecting the database we are executing this query. So delete from employee where employee number equals to three. Once you are setting the preferences right. So we just restart that uh

[2:32:51] MySQL workbench. We just close it and open it again. Connect it back. So automatically your settings will be getting updated. So just execute this query. So all are getting the results or not. So how can

[2:33:05] you know? So I need to just retrieve the data from database right. So select star data from database right. So select star from employee.

[2:33:17] is getting deleted that is no longer existed here. Okay. So it is working fine. So you just try it out and like this code snippet once you are done with that particular task.

[2:33:34] If you are feeling difficulty in identifying that option. So again I am repeating you just go to edit you just go to edit and preferences.

[2:33:53] So select query SQL editor and just go down to the bottom and this one by default it is getting checked. Uncheck that one and click okay

[2:34:11] and restart your MySQL IDE. So that means MySQL admin MySQL workbench we can means MySQL admin MySQL workbench we can just restart it. So that is sufficient. So automatically we can able to execute the queries. So safe updates are

[2:34:23] disabled. So this is for deleting right? So we deleted the record. But think about But think about we are already having so

[2:34:41] you're working on so the company portal right is it correct could you please share your screen that would be helpful for me to figure it out what exactly the >> yeah I'm sharing my screen >> yeah

[2:34:55] >> yeah so I did log out and log back Can you see my screen? >> Yeah. Yeah. So, but very font size is

[2:35:08] check. >> So, I did, you know, I logged out and I checked this back again. Uh, >> so it's in a company environment, right? So, not required actually. >> Yeah. So, that means in simple

[2:35:23] execute that command. So, let's try it out. What is the error we are getting here also? You're getting safe updates. >> Yeah, I did get the save update error. Uh so I uncheck that and this is the

[2:35:37] using >> show me show me that error might be. Can I paste it in the chat? Copy that one and paste it in the chat.

[2:36:02] you're looking >> yeah just uh how do I up scroll this up it says >> yeah yeah so what happen means you can just uh put your cursor on the the particular point might be you can

[2:36:17] able to get it >> on the edge edge of that one. >> So when I'm trying to uh if you see this right like when I'm trying to query this I'm still getting

[2:36:31] >> your screen is not visible actually. >> Oh the screen is not visible. >> Oh the screen is not visible. Let me try that again.

[2:36:47] >> Yeah. Yeah. Okay. >> So it's not updating. It's not deleting. You are saying correct. >> Uh the the delete query command is not working. So if I run this

[2:37:02] >> so for that one. So only the thing is you have to observe the output. We can just drag up. So I will show you just give me a second. Uh where we can how to drag that one. Okay.

[2:37:15] It should be like usually just >> you see here look at this uh we can find

[2:37:28] drag option if you are putting the cursor like this can able to find it. so it is the cursor icon is getting changed right? >> Yep. Yep. >> You can drag and up and down. Okay.

[2:37:43] >> So >> could you please check it out? So the same thing is happening for you. >> Okay. I'll >> see in the panel. Okay. Where are the

[2:37:58] junction panel? So you can just put the cursor identify where exactly. >> so you can share your screen. You can share your screen. Yeah. >> Oh, it's okay. Stop again. >> It's okay. It's visible. Yeah.

[2:38:16] up but I don't get that change in the cursor. cursor. >> Click double click on that one. >> Okay. You can select that blue color. Click on that blue color. Whatever the

[2:38:32] content will be there. Uh press down arrow. Down arrow. you're using safe mode.

[2:38:46] >> Yes. Yes. Yes. Yes. >> Up and down only.

[2:38:58] This is the challenge. I think I need to check what exactly. So, how to drag that check what exactly. So, how to drag that one up and down and all. know >> here. Yeah. In this the preferences have

[2:39:11] not be the problem. >> Ah that is not the problem. So the preferences not required to uncheck also by default it checked. Did you check >> It was it was checked. Yeah. So I did uncheck it that uh and then I close this

[2:39:28] again you have to reconnect to the server. Reconnect it correct. >> Yeah. Mhm. Yep. I did that. >> Okay. Okay. Please give hold the problem or you can just uh drop a message. I will let you know what is the exactly

[2:39:43] the solution for that one. What exactly happening? So uh sorry Nam could you please share your screen? So I just want to reiterate one more thing. >> Once you reconnected right did you selected the database?

[2:39:58] >> Uh actually it just came up. I I >> Okay. So use use database. We can just select the use database command and execute it first.

[2:40:13] three. >> Mhm. >> So, just select that and execute it. >> Okay. >> Why? Because we reconnected, right? So, we not selected the database. And after

[2:40:26] we not selected the database. And after that, now we can execute delete command. >> No. >> Now we can select. Now we can select.

[2:40:40] >> So check whether the record uh whether the record is deleted or not. Could you please verify it? >> Sure. No, these null are still there. >> No, it's not deleted. >> Okay. So I will just uh check and come

[2:40:57] back to you. Okay. Regarding that problem. Okay. Thank you. Thank you. you are uh some of you are facing problems. No worries. But please follow

[2:41:11] the procedure. What happens means uh it is not allowing safe updates. You have to disable that one. So then only it will allow the safe updates in the platform. Those who are working

[2:41:24] you the solution. So maybe I will check with uh the platform. I will work with the platform and I will get back to the the platform and I will get back to the solution.

[2:41:38] the database after deleting? So the third record so we are having. So the fourth record right which is having so name is empty. All of you please look at name is empty. All of you please look at my screen. The name is empty or not?

[2:41:51] Yes. So the name is empty. Do you uh all of you can able to identify the name is empty or not? for the fourth record. So what operations I I can perform here?

[2:42:08] I need to delete this record or I want to replace this value. What is the meaning of replacing here? Anyone could you please guess it? What exactly the you please guess it? What exactly the meaning of replacing? So the name of

[2:42:22] meaning of replacing? So the name of fourth employee record it comes under insert or update or delete. What is the option we have to choose? There are three operations right basically

[2:42:35] it is coming under insert or update or delete from you very good so the update

[2:42:48] so let's try to understand so update the employee so whose employee number is four or suppose whose employee number is four H.

[2:43:04] So how can you update it? So we can write the query. So update employee. So one important trick I will tell you here. So whenever we are doing update, right?

[2:43:18] So one keyword comes into the picture. What is that keyword is set keyword. Okay. So whenever we are updating a particular record, so we must use what is that keyword? So set So set name equals to

[2:43:33] So set name equals to some hurry per suppose. So where employee number equals to four I'm just

[2:43:45] specifying the condition. So automatically it is updating the employee employee record whose employee number is what is that one the four. I'm just executing this query. It is successful.

[2:44:00] How can you say? So, so whether record is successfully updated or not. So, we can just check select star from employees. Right? Look at this employee number four whose name is updated as hurry. Okay, it is

[2:44:16] working fine. So, this is the query which is mainly used for what is that? The updating the record. So you want to update the name or you want to update the address we can make

[2:44:28] use of this query. So whenever we are using update keyword right so the set keyword comes into the So in the record what is the value we

[2:44:40] are setting. So that is super important right? We have to set the value. message if the count is getting

[2:44:52] increased. So then I can able to proceed with the next query. multiple records I need to update here. Right? If I'm going down

[2:45:09] I think there are no records with empty data. data. Okay. So I want to update the employee record whose employee number is four and I want

[2:45:23] to update the address. What is the query? I have I can write it. So update query? I have I can write it. So update employee

[2:45:44] so previously USA I'm just changing this one to Bangalore. So where employee number equals to what is the so the four

[2:46:00] equals to four then what happens already employee number four address is USA I just updating with what is that one so the Bangalore I just executing one so the Bangalore I just executing this query

[2:46:16] updated how can you know you just execute this query right so select star execute this query right so select star from employee updated. Hurry address is now also the Bangalore not USA. So like that.

[2:46:34] So I'm just sharing this command with you. So might be you are asking me uh at a time can I can I update so multiple columns?

[2:46:46] Yes of course you can update name also. For suppose if you want to update the name for suppose

[2:47:11] okay so for example oh so we can update the name as hurry to harish hurry to hurry Of course, this one also works.

[2:47:23] Now we can just execute this query. So by selecting this query

[2:47:39] because it's lengthy. So the font size I increase it right It is successful that is executed and also we can verify.

[2:47:54] So select star from employee. So then automatically so we can able to see. So previously the name of that particular employee is hurry. Now it is getting changed to what is that one. So Harish and also the both name and

[2:48:09] address we are updating at the same time. Right? I'm just sharing this. Uh so we discussed about insert, update and delete. Okay, that is fine. So let's see how we can make use of AI

[2:48:24] tools to learn this how to create a table and how to insert the table and all those stuff. I'm showing it in multiple AI tools for suppose. Initially I'm going with charge GBT. All of you please look into this one if

[2:48:39] you are new to the what is that one so the SQL right. So this charge GBT and AI tools will help you uh in a better way. So how to create the tables and So how to create the tables and here we can use some persona pattern

[2:48:54] right what is persona? So we are just assigning some role to So we are just assigning some role to the chart GPT right any AI tool if you are interacting so we need to assign some persona persona is something like

[2:49:09] so act as SQL developer for suppose or otherwise so MySQL developer especially I'm working also the MySQL right so MySQL developer

[2:49:34] so create database database and uh tables and how to insert the data? How to perform

[2:49:53] So update and delete the charge GPT to provide the better results right so it's a persona pattern

[2:50:07] so while writing the prompt so we have a concept called as prompt engineering as a part of the prompt engineering right we need to assign some persona to right we need to assign some persona to the AI tool so I'm just suggesting so

[2:50:21] act as a MySQL developer perose so we just assigning a persona like act as a MySQL developer I explain how to create a database and tables

[2:50:34] and how to perform insert, update and delete operations on tables.

[2:51:06] So what is the schema? So employee which is having so employee number name, address. Okay, this is the simple prompt. Okay, I'm just giving in charge. prompt. Okay, I'm just giving in charge. Let's see how charge is helping you. I

[2:51:19] am just sharing this prompt in the chat as well. So for your reference

[2:51:31] so if you want to make changes yes of course you can make changes also. it is generating and if I'm executing this one

[2:51:46] so automatically the chart GB is understanding my question right it is generating so I'm not provided with any database name so it is provided with any database name so it is creating so company database

[2:52:00] creating so company database and also it is using company database and it is creating a table but especially whatever the schema is specified and it is adding so some primary key and

[2:52:12] key and all I will discuss about that one no worries so I missed some context don't use any constraint or anything so it is saying

[2:52:24] so inserting multiple rows and all those stuff stuff so I'm opening a new chart so same query I'm using same question. So act as a MySQL

[2:52:37] developer explain how to create a database and tables and how to perform insert, update, delete operations on the tables. So better you can provide some examples with sample schema

[2:52:51] with sample schema and uh I'm mentioning especially don't use constraints. Okay, so I'm just mentioning don't use constraints.

[2:53:05] Okay. So then automatically it is not using any constraints. creating only plain table right.

[2:53:18] So like this it is not adding any primary key or anything. So could you please check it out? So in the chart GPT so whether this one is working properly for you or not.

[2:53:34] I will tell you with the different tools that's what I'm saying I'm just asking about. So charge if it is a common tool whether you are getting so the perfect queries whatever you written say yes or no

[2:53:50] important. So we are using some persona pattern. So especially here so don't use any constraints. Up to now we are not we don't know about what is constraint and all. We learned about only how to create the table uh first how to create the

[2:54:06] database and how to use the database and how to insert the data and how to delete the data and how to update the data. So all these things are satisfying properly right? Okay. For suppose might be you're asking

[2:54:20] Okay. For suppose might be you're asking me a question it's not like that. So I'm new to this area for suppose I am from marketing. marketing. I am from medical suppose healthare I am

[2:54:32] from so manufacturing. How can you ask SPT to explain these concepts here? Look at this we are using so different pattern whatever the pattern so now currently we are using that one

[2:54:47] we can call it as persona pattern persona means what so we are just persona means what so we are just assigning a role to the chart GPT so we assigning a role to the chart GPT so we have to behave in such a way right

[2:55:00] have to behave in such a way right so just look into this persona pattern uh not persona but suppose audience persona pattern we have two patterns one What is that one? So the persona

[2:55:13] What is that one? So the persona pattern. So just be used. So persona pattern means what? So for who and what, right? Who and what means who and what, right? Who and what means what?

[2:55:30] developer. So we are just assigning some persona to the AI tool. And what the AI tool is going to do that is called as what is that one? Persona pattern. And next one is what is that one? So audience persona

[2:55:44] pattern. Audience persona pattern means these two patterns we have to understand. So while learning while using any chargely learning while using any chargely these two patterns basic patterns

[2:56:00] way. way. So I explain

[2:56:28] and how to insert? So you can mention whatever you want. How to insert

[2:56:47] into the tables? How to insert or update delete data in How to insert or update delete data in the tables?

[2:57:03] one. So so don't specify any constraints for suppose that is up to you. So whatever you have so you have so don't

[2:57:21] So explain how to create a database and table and how to insert update and table and how to insert update and delete data in the table to delete data in the table to a marketing person perose.

[2:57:33] Here I'm just specifying what is that one audience. So for whom you are generating the content so to a marketing person suppose marketing professional. So I'm just keeping so marketing professional

[2:57:56] So just I'm keeping so extra assumption don't add any constraints and suggested schema is

[2:58:13] so which is having so employee number name and also address right so let's see how it is generating Okay, but what is the problem? I made a

[2:58:27] mistake. By default, it is understanding MySQL. So, I'm not specifying any MySQL keyword. Okay, it is creating. So, the keyword. Okay, it is creating. So, the marketing DB

[2:58:40] you and it is inserting the data. So, that means it is keeping. So I'm explaining to the marketing

[2:58:52] person how the table will be created and all this one is called as what is that one audience pattern audience persona pattern. So we are explaining something to the particular audience might be saleserson

[2:59:05] or something like that. So let's change this one to this one to so just copy this query just change this one to healthcare department for suppose. So just I'm

[2:59:19] department for suppose. So just I'm copying this query. copying this query. So I have to create a database and table and uh how to insert the data delete the data in tables

[2:59:31] data in tables in MySQL saying per suppose. So your job is what is that on a healthare professional?

[2:59:48] So to a healthare professional and don't add any constraint. and suggested schema is what that one employee employee number name and

[3:00:00] employee employee number name and address and uh address and uh explain details.

[3:00:12] healthare professional does not know about SQL he don't know about SQL right so might be he never used so might be he attending this training after this training might be you are

[3:00:27] getting some doubts so you can ask charging so perfect details for you what is the database we are creating why we are creating the database it is providing so clear details about

[3:00:41] how we can create an employee table and what are the columns what are the columns and uh okay how to insert the data. So and uh okay how to insert the data. So it is giving some examples also for you.

[3:00:54] So this is one tool right and also it is suggesting some other options also like what is the database hospital database table is staff register okay so something so one employee record

[3:01:09] so you can just go through this one so for your practices is it clear all of you what I'm saying this would be helpful for you this prompt patterns how

[3:01:21] helpful for you this prompt patterns how can you generate the prompt So there are two patterns. One is persona pattern. So act as expert in persona pattern. So act as expert in SQL. Act as SQL developer and write a

[3:01:34] query for this one. So automatically the chart GP or some other tool will automatically generate the query for you. So maybe you're asking AI. Uh so please provide

[3:02:02] so automatically the charge if it is providing some exercises for you. So it is providing so level one what are the things we have to do. Okay. So level two so the update practice it is giving so so many details

[3:02:17] for you you can just go through that once and complete the exercises so what is the tool name perplexity.ai AI

[3:02:31] getting released this one is special so it is providing so multimodels it is using so multiple models to generate the response that means the response will be very efficient you are saying so same prompt

[3:02:47] right act as a uh SQL developer

[3:02:59] Or otherwise you can change the pattern per suppose explain.

[3:03:21] So updating and deleting

[3:03:37] Okay. So to a functional person to a functional employee. is that fun. How to what is the technology and all? So a functional

[3:03:50] employee working in employee working in HR department but suppose

[3:04:18] see what is the response it is giving. So it is doing so some deep thinking So it is doing so some deep thinking and uh it is giving so very good experience for you right it is providing so the more details

[3:04:32] with respect to the HR so what what are the things are there so it is explaining very clearly so we can make you try to use all the tools but these tools are free right so they are having certain limitations that

[3:04:46] is sufficient for your learning purpose I don't want to explain further So I just introduced uh two of the tools right. So there are so many tools are present in the market. You can use any of the tool for your learning purpose.

[3:05:02] So now we completed what are the things we completed insert update and also we completed insert update and also delete we completed. Correct? delete we completed. Correct? So what are the operations in DML?

[3:05:14] So in DDL we completed. So creating a table correct So we understand how to insert the data and how to update

[3:05:29] the data and how to delete the data I completed. So if you're all comfortable with this concepts, I'm just posting this one in the chat. So please quickly like this message I can able to proceed with alter

[3:05:43] option. Okay. What is that one alteration? How to alter the so the column and names for suppose how to change the column names and all other stuff. So what I'm saying is don't worry about writing code

[3:05:58] today only knowledge is required actually okay knowledge in the sense like what is table what is database you should know about at least what is table what is database and all the concepts present in

[3:06:14] the SQL so you don't want to worry about so writing code almost all the companies are allowing so these tools licenses for are allowing so these tools licenses for especially GitHub copilot.

[3:06:28] So I'm having so the multiple licenses I'm having license to Microsoft copilot I'm having license to Microsoft copilot as well as GitHub copilot in my company. If I want to develop any project I'm not writing code today nowadays

[3:06:42] I'm just making use of AI tools. I'm just generating the project and I'm just looking it into the project where exactly I want to tweak. Okay. I just want to make some changes to the existing logic. I'm just just tweaking.

[3:06:57] So the existing logic only. So your job also same almost right. So you don't want to write so every line from very scratch like that.

[3:07:13] Okay. That's great. So let's jump into the next level.

[3:07:25] tell you. So I'm just opening so the VS code per So I'm just opening so the VS code per suppose. So these are all Python projects. Let me open so new folder or otherwise.

[3:07:45] So I'm just creating one SQL file inside this. So sample.sql integrated with the SQL tool, right? Sorry, it is already integrated with

[3:07:59] what is that one? So one of the AI tool here it is asking. So control plus I to write the prompt. So if I'm pressing control + I, it is asking what is that one? So generate the code. So you can write the

[3:08:14] uh script here for suppose uh what is the script I can write it

[3:08:31] and generate code for code for so creating database

[3:08:45] you. So now it is visible, right? Inserting. So same thing, right? So inserting, updating, deleting and all inserting, updating, deleting and all those stuff updating and deleting

[3:09:25] So if I'm executing this one so if I'm connected to the model see automatically generating so all the code for you. code for you. So I just I'm keeping this code

[3:09:48] line right it is not including any content it's more interesting than that right so when compared to chart GPD and all so it is creating database so create database banking DB and inside that one.

[3:10:04] So it is creating an employee table and it is inserting the data into the employee table. So every query is written by the GP uh So every query is written by the GP uh so the model itself the A itself

[3:10:18] updating generating accuracy. Accuracy means what? So it is generating so whatever it may be.

[3:10:31] So in my machine so there is a model called as codeex. Okay you can see here uh I integrated with what is that one. So the codeex model So the codeex model I think uh I just want to show you

[3:10:46] can just go to the if you are installing visual studio code right you can go to the extensions. So this is the model actually the codeex model it's not free up to some level it is free. Okay. So for training purpose

[3:11:01] it is okay. You can install this codec codeex extension in VS code. So automatically it is just generating. So for training purpose right. So you can able to uh make use of this tool right. So no

[3:11:17] worry about what is that one. So writing code manually. Even if you are making mistakes you can just check with this uh whatever the code generated by the AI. whatever the code generated by the AI. But uh truly I'm suggesting

[3:11:32] first in the course while learning the course right first you can write it manually but if you want to improve further right so you can just make use of AI tools don't rely on AI tools completely so if

[3:11:46] you are asking me so whether the AI tools will generate the accurate code means I can say 100% not correct okay so that is one important assumption I want to talk about

[3:12:01] about correct So as a developer you should know what is the concept how to write the query

[3:12:13] and all you should validate whether that query is correct or not. So that is super important. Is it clear all of you what I'm saying? A models will always hallucinate. Hallucination means what? So it is not

[3:12:28] giving. So the exact answer what is required for you. We need to tweak the prompt. We need to use some patterns like persona pattern and also audience persona pattern. So different types of patterns to get the

[3:12:42] exact answer. If you are normally asking like a question to your friend, it does like a question to your friend, it does not give answer. It will hallucinate. So you have to mention so act as a super SQL developer.

[3:12:55] SQL developer. Okay. So we have to assign some role or otherwise explain. So the SQL queries to the non- techy perose non-technical person they should they don't know about technology how we can explain so please

[3:13:11] explain so use the scenario in this way if you are giving the question so the uh the a models is going to produce better answers for you.

[3:13:23] Is it clear all of you what I'm speaking all of you can able to get it? Can I proceed further? Yeah. So this would be more helpful.

[3:13:36] So try to use charging. So that's what I'm saying first initially. So we created so the employee table of course you understand. So insert data those stuff. But let let's think yourself.

[3:13:57] We have an employee table, right? So what is the table? So employee table. So which contains what are the columns? Employee number whose type is integer and the name. What is the type? Worker of 100 whatever it may be worker of 100

[3:14:14] or worker of 30 might be this one is worker of 30. and address will be so of 100.

[3:14:33] right I just want to add an extra column called as salary. So salaries of type. So if you want to make any changes to the schema. So again I'm repeating if you want to

[3:14:49] So again I'm repeating if you want to make changes to the schema. So then we can go for what is that one? So alter command. Can you please tell me

[3:15:03] DDL? So DDL create alter drop create alter drop these are all comes under what is that one? So DDL commands. So alter means what are the operations we can able to

[3:15:17] perform under alter. We can add column name add or drop. So We can add column name add or drop. So we can add or drop column

[3:15:43] and also we can change so the data type of a column right. column. We can change the size of a column. So all these things are possible with the help of what is that one alter

[3:15:58] So let's see how to work with this alter. So first we can try to add column and let's observe you can take a support of chargity also.

[3:16:10] First describe this table. So describe employee. columns present in this table? So we have three columns right? So employee

[3:16:22] have three columns right? So employee number, name and address. I want to add another column. So what is that one? So I add I'm just So what is that one? So I add I'm just keeping. So I add another column.

[3:16:41] to add alter? So table. So employee so I'm just keeping so add column

[3:16:54] so I'm just keeping so add column so what is the column name so what is the column name salary and what is type integer I'm just writing this query just run this query and check it it is

[3:17:07] successful so now we can check describe employee so now we can check describe employee so salary column is getting added or it is getting added. So look at this salary column is getting added

[3:17:21] and if you are selecting so the employee details for suppose select the employee details for suppose select star from star from so employee

[3:17:36] all the employee details right so we are getting so all the employee details but salary is empty because we are adding extra column if you are adding extra column so by default the salary will be empty Right?

[3:17:48] By default the salary will be empty. Okay. So we'll start with what is the first concept of constraints. What are the constraints? So basically we have what is constraint actually? If you're asking me what is constraint

[3:18:01] regulation. Okay. So what exactly constraint? So constraint is a rule or regulation. So that is applying on or regulation. So that is applying on what is that of the table?

[3:18:31] So applied on your columns of a table applied on what is that on the columns. constraints we have? So we have unique constraints. So the first one is what is that one? So the unique constraint we'll see all those things practically.

[3:18:47] So first try to understand what exactly unique constraint we have an employee table. So employee number name and address

[3:19:01] have to assign what is that one so unique constraint here constraint here. So what is unique? Unique means it does

[3:19:13] not allow duplicate values, right? It does not allow duplicate values. for example. So here employee number one and name is John name is John address is Bangalore per suppose

[3:19:31] employee number for example again if I'm trying to give so one possible so because this one is treating as what type of value so the duplicate

[3:19:47] record correct. This one is treating as duplicate record because for the employee number I assigned what type of constraint. So any constraint so duplicate records are not allowed.

[3:20:02] So this is super important right? So generally if you are working with any real production database might be you can see email so email column must be unique. Can you please tell me post it in the chat? So

[3:20:17] anyone could you please identify what are the specific columns that are uniquely identified. So that is your guess. Take customer or employee or any entity. What are the unique columnments might be

[3:20:32] you can easily identify anyone could please post it in the chat. So in employee email is the unique employee ID is unique. Okay.

[3:20:46] Okay. There are different cases for suppose employee ID will be primary key. So why employ ID is the primary key I will discuss.

[3:20:59] your card ID right other number per suppose or otherwise your passport suppose or otherwise your passport number what are these? So these values cannot be duplicated. Correct? These values cannot be

[3:21:15] duplicated. So credentials, phone number. Yes. So these all things cannot be duplicated. So that type of values we can call it as what is that one? So unique keys. [snorts]

[3:21:30] the notal another keyword. What is that one? So the notal.

[3:21:43] actually it is not a constraint. It is just a simple keyword actually. Okay, it's not a constraint. So don't think uh if somebody is asking in interview. So what is not null means? Not null is simply a keyword. It's not a

[3:21:58] constraint. It does not allow null values. If you are putting not null on the particular column so the particular column so does not allow null values. for example. So the same employee table if you are taking

[3:22:22] So employee number one and for this name I am assigning what is that one.

[3:22:37] For example, if you're trying to insert null value, so then what happens? So, automatically we are getting an error, right? So, we are getting an error. But we cannot see any message constraint

[3:22:50] violated like that. We cannot see any message like constraint violated or not because not null is not a constraint. So, it is just a keyword. And another important constraint so I'm

[3:23:03] speaking about so what is that one the primary key primary key super important constraint so generally if you are working with any database right any database table we

[3:23:17] database right any database table we have suppose if you are taking any table so we have some key attributes

[3:23:30] non-key attributes utes. So what are the key attributes and what are the non-key attributes? We have to understand we have key attributes. Some of the attributes and some of the attributes will be

[3:23:44] will be so non-key attributes. So what are the key attributes basically if you are working with? So the attributes which are mainly used for what is that one? So identifying the

[3:23:59] of attributes we can call it as key attributes. For example in employee table say employee number.

[3:24:13] for uniquely identifying the particular employee. Right? If you're asking me what is key attribute in the sense that key attribute can be used for uniquely identifying an employee. So that might be employee number or email

[3:24:29] or passport number or other number. So so many right. So based upon the columns some of the columns might be so

[3:24:42] the key attributes those key attributes are mainly for uniquely identifying the particular uh record but some of the attributes are non key attributes for example. So the salary

[3:24:59] designation. So designation of an employee this is also an key attribute. But first the thing is if you are deciding any entity for suppose if you are taking any entity

[3:25:14] so first we have to identify what are the key attributes and what are the non-key attributes. So key attributes are mainly for uniquely identifying the particular record

[3:25:26] and non-key attributes are not used for uniquely identifying a record per upon the salary we cannot uniquely identify a particular employee based

[3:25:38] upon the designation also the same designation might be multiple employees or might be existed but these key attributes plays very very important role right. So key attributes plays very very

[3:25:52] important role. So for uniquely identifying the record but in this key identifying the record but in this key attributes also we have so keys.

[3:26:05] attributes also might be we are choosing only one attribute for identifying the record. In your table we have employee number, email and passport number and other number and all.

[3:26:20] But in these attributes also you are having an option to choose the super Super key is nothing but a primary key. Per suppose you have to choose the primary key. Among all those data attributes mostly

[3:26:37] we are choosing the primary key for what is that one? So identification numbers for example. So the employee number always we are keeping as what is that values and is not it does not allow null values always we are keeping. So

[3:26:52] employee number as what is that one the primary key. So this one can be treating as what is that one? So the primary key actually. So maybe you're asking me question. So you are saying only employee number why

[3:27:08] I have to choose I [snorts] can go with what is that one the email or phone number or other I understand your question so those are all key attributes only.

[3:27:22] So key attributes means what those are all employee number passport number and all employee number passport number and address. Okay, sorry. So, employee number, email, passport number, address, these are all majorly used for

[3:27:36] identifying an employee. For example, let's say Mahima uh how can you identify how can I identify you as a simply learn employee for suppose if you are working in simply learn how can you identify how

[3:27:51] employee might be you are alerted with some employee ID right and you are having so simply learn email id so based upon these things only I can able to identify you as an

[3:28:06] employee There are many many things are there that might be email that might be employee ID or that might be your personal identification number like other number

[3:28:20] or otherwise social security number or anything anything but among those only one only one column that might be employee number or social security number

[3:28:34] security number so or other number Whatever it may be, we are choosing only one as super key like that. difference between key attribute and super key? Any key attribute might be

[3:28:48] super key. Correct? Any key attribute might be what is that one? So super key. Any key attribute means what we have. So salary designation these are all not key attributes. Employee number, email,

[3:29:02] passport number among those we can choose anyone as a soaper key like that. So key is nothing but what is that one the primary key is it clear what I'm saying all of you can able to understand

[3:29:22] we have to use this one is the so primary key and all that is up to you okay so nikita it's a simple concept right so for it's a simple concept right so for example if you are taking Huh?

[3:29:37] Uh student details per suppose we are all joined in the session. How can I how simply learn will identify you as a student? We are all joined in the session. How the administration team

[3:29:52] can identify you as a part of the this course. But suppose we have some fields right in the student table. We have some fields.

[3:30:05] name, email email and also like your uh SSN number or whatever it may be. So there are many fields are there mobile number.

[3:30:25] many fields are there. But suppose there are 100 fields are there. But among are 100 fields are there. But among those what are the key attributes here? Among those what are the key attributes? Keys means what? So that might be

[3:30:41] allocated uniquely for the particular student for example. So if you're talking about the keys eligible keys is here. here. So email so almost email must be unique.

[3:30:53] Correct? every student is having uh two or three every student is having uh two or three students are having same email id. No, students are having same email id. No, in the similar way that the student ID

[3:31:06] so this is also we can consider it as a key attribute and also SSN number that one also we can consider it as what is that one? So key attribute and mobile number. So in a real world

[3:31:20] like mobile number we can consider it as a key attribute but might be the same mobile might be used by multiple people per suppose the number is not allocated with multiple people but when coming to the

[3:31:33] real aspect right so might be same mobile number might be it is a home same mobile number might be it is a home number might be uh that call will be okay so we can consider mobile number is also also as a key attribute but among

[3:31:47] those which one is strong among those which one is strong. So either email is strong and SID is strong and SSN is also strong. Strong in the sense you can uniquely identify the particular student

[3:32:02] with the help of SID or social security number or email. Correct? And also what about the non-key attributes? Non-key attributes means we cannot

[3:32:16] identify. So based upon so that non-key attributes among those mostly we are choosing what is that one so for identification so we are separately as assigning what is that one so some ID for each and every

[3:32:31] student record this SID can be used as what is that one so unique identification so that's why mostly most probably if you are observing the tables we are assigning so some ID to the table and we are treating that one as what is

[3:32:46] that one to the primary key. But what exactly practically if you're But what exactly practically if you're asking me what is primary key means?

[3:33:09] So any means what? So it does not uh it will uh it does not allow duplicate values. and it does not allow. So null values as well. So that is your responsibility.

[3:33:24] So not a particular identification. So student ID mostly most probably if you're observing entities entities right in real life most probably we can we can keep so the primary key as what is that one? So the

[3:33:38] identification number so that is uniquely identifying the particular record and it does not allow null values. If the student is present or suppose if the student is present means what? So uh that student must have

[3:33:52] a student ID. Even if the student is not having email if even if the student is not having SSN but student is registered means what? So the student ID will be created that is nothing but what is that one? the primary key.

[3:34:08] one? the primary key. It does not accept null values and also it does not allow duplicate values. These two constraints needs to be satisfied. And some important points related to the

[3:34:20] And some important points related to the primary key. the primary keys. So foreign keys

[3:34:32] are always refers to the primary keys. So let's try to understand. So I will discuss more in detail. So while

[3:34:47] discussing what is that one? So the foreign key. So what is the purpose of creating foreign key and all if you are creating a primary key. So generally we are working with what type of systems nowadays

[3:35:01] relational database management system. If you are taking so almost all database If you are taking so almost all database management systems they are RDBMS. management systems they are RDBMS. RDBMS stands for what?

[3:35:38] system what happens means you are not storing the entire data in a single table. Right? If you're having any complex applications we are not going to store the entire data in a single table. For example, if

[3:35:52] you are having if you're taking e-commerce application per suppose. So in the e-commerce application, so we have products table,

[3:36:04] right? So this is called as what is that one? So products table

[3:36:22] that product comes under electronics or what what is the category or otherwise so we can say instead of category so we can specify. So product category so we can specify. So product catalog table

[3:36:44] automatically that product will be visible in the cart right. So in a relational database management system if you are designing a relational database management system for your application.

[3:36:59] So always uh please remember if you are designing a database means what so for what purpose you are designing always your applications are generating the data correct take Amazon as an example you are purchasing a product

[3:37:15] product in the sense we are generating the data only right so whatever you product you purchased so that goes to products table and our customer details

[3:37:27] Amazon customer details are present in customers table and the product related with valid that wants the particular category and also first customer wants to purchase the product customer has to add item to the cart right so what I'm

[3:37:42] saying is all the data we cannot keep it in a single table so we are creating we are separating so that one into multiple tables but suppose we have so products table

[3:37:58] and customer table and also catalog table. So we have cart table. So we have so multiple tables our data will be stored in what is that once the multiple tables like that

[3:38:12] our data will be stored in what the multiple tables but even though these tables are having relationships so customer and products per suppose one customer can purchase many products right

[3:38:26] many products right or otherwise so many customers can do many products correct so There we have different types of tables. Many customers will purchase what is that one? So many products.

[3:38:39] customer. I'm purchasing one product. Might be you are also purchasing the same product. So what is the relationship between these tables? We have so different types of relationships.

[3:38:54] What are the different types of relationships? So we have one to one relationships? So we have one to one and one to many

[3:39:06] have so one to many who are so this one we can call it as what is that one? So many to one

[3:39:21] relationship. So many to many relationship. relationships but I'm not going deeper right now. So right now I discussed about what is that one. So these two constraints

[3:39:36] constraints one is unique constraint and another one is what? So the primary key constraint.

[3:39:49] constraints actually unique constraint and also the primary key constraint and after that so further we'll discuss about what is relational databases okay what is one to one mapping and what is one to many all those things is it

[3:40:04] clear can I proceed with creating unique constraint first we'll complete these two two unique and primary key and also not null so not null also one simple keyword we'll discuss about that So how to assign the kind of constraint

[3:40:20] after this concept I will discuss about the foreign key okay so foreign key the foreign key okay so foreign key requires it's more understanding so just I provided what is that one so simple summary about the foreign key I

[3:40:34] will go some deep dive into what is that on the foreign key how it works and all okay so no worries first try to understand what is that one so unique primary key and not null can you please tell me what is unique unique Means

[3:40:47] what? It does not allow. It does not allow duplicate values. Correct? Duplicate values. What is primary key? Primary key means unique plus not null. So please

[3:41:02] remember. So keep it in mind. So primary key which is called as key which is key which is called as key which is unique plus

[3:41:14] values. It does not allow null values. Is it clear? So my pace is okay now. Somebody is asking. So go slow. Right. Somebody is asking. So go slow. Right. Is it okay everyone? Now

[3:41:35] I think you can able to follow Sushita or someone I already posted. If you are feeling okay so then no issue. If you're not feeling like my pace is not going fast or anything so please post it again so I can proceed

[3:41:49] further. You can look at here. So I'm just connecting to so this MySQL database you can just go to the services.

[3:42:03] check what is that one the MySQL service right. So of course this MySQL service is running. So MySQL 80 service is what is that one up and running.

[3:42:17] So now what I have to do I have to connect to that MySQL 80 service for connect to that MySQL 80 service for that one. So go to that one. So go to so this MySQL workbench

[3:42:33] and provide so the password for suppose root asking so what is the database what are the databases that are available so show

[3:42:46] the databases that are available so show databases getting all the databases right so you can just check it so we have an employee DB of course we can see here also in the left panel

[3:43:03] we have an employee DB so I'm dropping so this database but suppose employee DB I'm just deleting this database so the drop this gemma drop this gemma and also we have HR database is present

[3:43:17] so I'm just dropping so don't delete so other databases because they are system databases here I'm just writing. So create database [snorts] so database name what is the database

[3:43:29] name I am creating so employee DB for example if you want to drop the database so which is already present so what we have to do what is the command we have to use so from the command lens drop

[3:43:44] database what is the database name so employee DB If you want to delete the drop database but suppose we can mute this command. So

[3:44:00] drop database employee DB like that. Okay. So all of you please execute this command. If you already created the database I think in the last session so might be you created so employee DB right?

[3:44:14] All of you drop this employee DB database and verify whether this is successfully deleted or not. So once you deleted the database the So once you deleted the database the next step what we have to do?

[3:44:27] What is the next step we have to do? Anyone again recreate it? Can you please tell me in the chat? Can you please type in the chat? So what is the command to create a database? Already you have experience in the last

[3:44:40] week. Can you please tell me? So the commands how to create a database. So create database

[3:44:54] might be you are asking me without deleting I can create it means no first we can delete it and after that you can create it. So the employee DB is created but it is not reflecting here.

[3:45:09] You can see in the right left side you can see any employee DB is reflected here. No right yes we can use we can create or

[3:45:21] uh if exist we can do that one also but we can go in a manual way Robin otherwise people are getting confused if I'm writing so some commands so we can I'm writing so some commands so we can just refresh it.

[3:45:35] So that means uh as Robin's posted message right so we can write the query so create table if exists create database if exists or not we have a syntax like if the table is if the database is existed so it is not

[3:45:50] creating the database that type of implementations also available but right now I'm not taking into that one but meanwhile I will show you so after that how to use the database so directly can I create the table without selecting the

[3:46:04] data database no right what is the next step so use what is the database

[3:46:19] so we can select this uh what is that one so the database so now currently which database we are using so the employee DB we are using

[3:46:33] right so the Next step is what is that one? So I have to create a table with employee number as unique unique constraint for suppose I want to apply unique constraint for employee number. But try to understand

[3:46:47] here if you are talking about any constraint right constraint any constraint

[3:47:02] we can apply two levels one is column level

[3:47:15] you can apply constraint at column level and also edit So the table level example. So if you're having so employee number

[3:47:29] so name and also email of course employee number you must assign so primary key.

[3:47:42] right? So not on the tables. If you want to use the particular database just use database command. So not on the tables. Tables will be created inside the database. Correct? So tables will be created inside the

[3:47:57] tables first we have to select the database. first we have to select the database. For that one we are using use command.

[3:48:09] is for this email. So I want to assign what is that one? So unique constraint and also for the name I want to assign. So what is that I want to assign. So what is that keyword? So not null keyword.

[3:48:24] mean? So this name does not allow null values. Correct? >> Because we are providing so what is the keyword? So not null. Not null means what? So it does not accept null values. And email might be must be what is that

[3:48:39] one? unique. So you're asking me so not null and unique not like that only not null will be a separate constraint I assigned on name and unique will be applied on email both are I'm not applying on so same

[3:48:53] column try to understand if you're keeping not null and unique means it comes under primary key right it comes under primary key so primary key we are assigning it for what is that the employee number primary key we are

[3:49:07] assigning it for what is that the employee number so this is the requirement but currently if you're observing so I'm just applying so unique constraint

[3:49:20] so remaining columns I am keeping it as it is so let's observe how to create what is that one so an employee table with unique constraint with unique constraint I am just writing so create table

[3:49:36] please listen because after this uh one right I will give you I will share you one document we have to create all the tables and on your own tables and on your own employee number whose type is integer

[3:49:53] what is that one so the primary key anyone could you please post it in the does it mean primary key means what

[3:50:05] unique plus no no not unique key primary key means means what? Primary key means what? So unique plus unique plus not null. Not null. Okay. Unique plus

[3:50:18] not null. It's not null. It does not allow null values. And also for other fields for suppose what are the other fields name it is of 30.

[3:50:34] So here I'm assigning what is that the particular keyword. So not null keyword. name I'm assigning what that oneal keyword

[3:50:47] keyword and also for email. 100. So this one must be what is that one so

[3:50:59] So this one must be what is that one so the unique unique. Unique means what? It does not allow so duplicate emails like that.

[3:51:14] which contains what is that one the employee number as a primary key and name it's not null not null means what it it's not null not null means what it does not allow null values and also for

[3:51:26] constraint so you can just run this query so the table is getting created so now try to insert the values into

[3:51:38] this tables for suppose I'm just uh sharing this uh script with you. So all of you please create this table with this constraints

[3:51:53] and like that message once you completed so please like that message. what is primary key and also what is unique.

[3:52:05] So try to understand. So the primary key means what? So unique plus means what? So unique plus not null for example. So we have a not null for example. So we have a simple table if you are taking.

[3:52:19] number and name and also as well as email. you're assigning so email as unique

[3:52:32] So this one does not allow duplicate values. That's what I'm saying. Suppose at the rate of so gmail.com per suppose

[3:52:47] so the same should at the rate of gmail.com again I repeat it for another no right so this one does not allow duplicate values this one is treating as what type of value so the duplicate value

[3:53:01] so if you're assigning unique constraint so it does not allow what is that one duplicate values. Duplicate values means what? The same value cannot be repeated what? The same value cannot be repeated again. So for the particular column

[3:53:15] and primary key means what? So primary key basically if you're using for identifying a particular record per suppose if you're assigning a primary key right. So the symbol will be underlined symbol. So primary key means

[3:53:28] underlined symbol. So primary key means it is unique Not null means what? It does not allow. So duplicate uh it does not allow null

[3:53:40] values as well. Unique means it does not allow duplicate values and not null means what? It does not allow null values. For example, so one should I set the rate ofgmail.com. So in the similar way, can I give it again

[3:53:56] one? It's not possible. Can I leave it this one as empty? For example, so here I'm specifying someone this is null. If you are trying to insert null, this one is possible.

[3:54:09] No, because if you're assigning a primary key, right, that one does not accept duplicate values and also that one does not accept null values. That's what I am trying to tell you. Is it clear at

[3:54:24] still you are having any questions? Please unmute and speak with me. constraints right so null and unique constraints null not

[3:54:39] so null and unique constraints null not null I'm saying sorry so it's not null if you are talking about so not null means what for example if you're taking name you're having a restriction like that particular column so must I accept

[3:54:52] what is that one value we cannot keep that one as empty we cannot keep null values into that In such cases we can assign notal rate for example you are doing some business but suppose you are maintaining your

[3:55:06] customer's data you don't want to miss what is that once the email you don't want to miss the email of that particular customer because that is important for your business in such

[3:55:20] cases what we are keeping so email cannot be null and email must be unique per suppose unique means SP so each customer is having so unique email id okay so according to your requirement if

[3:55:36] you're asking me what is the difference between unique and not null means unique means it does not allow duplicate values values so not allows

[3:55:52] duplicates and not null means what values like that that's what I am trying to tell you so

[3:56:07] according to your business what columns must accept values you must provide something value to the particular column for example your customer is filling out for example your customer is filling out a form feedback form per suppose

[3:56:21] if customer is filling out the feedback form means you must specify some fields as mandatory mandator Means what? They have to must they have to fill that have to must they have to fill that values right. So that type of values are

[3:56:34] must provide the value for the particular column and like that. And unique means there are some fields which are that does not allow duplicate values. Very simple terms actually. It's not

[3:56:47] Very simple terms actually. It's not very much complex. table means here look at this. This one is table is created.

[3:56:59] This is only the output. Okay. If you want to see so how to know so what are the tables are getting created means what? So we can specify what is that one. So show tables. If you are specifying so show tables

[3:57:13] then automatically what happens what are the tables that are created. the tables that are created. So it will display all the tables right?

[3:57:26] all the tables for you. So we have an employee table already created and also another query if you want to know so describe right. So describe table what is the table name? So describe employee table

[3:57:42] if I'm specifying so describe employee so then automatically describing so the employee table right look at this here very very important information we can so we have an employee number what is the key is assigned here what is the key

[3:57:57] the key is assigned here what is the key is assigned so primary key is assigned so primary key and name constraint is assigned it will accept null value or not. It does not accept

[3:58:11] null value. You can see no. Why? Because we assign not null constraint. Not null means what? It does not allow null values. And also for not allow null values. And also for email it is showing us. It accept null

[3:58:24] values. But it does not allow what type of values? Duplicate values because we assigned what is the constraint? What is the key? Unique constraint like that. Is it clear all of you? Could you please create the

[3:58:39] table and uh let me know in the chat all of you can able to create the table or of you can able to create the table or not.

[3:59:00] Okay. So let's talk about what is that one. So, so we can able to create so constraint at what is that one? So the column and level

[3:59:19] what is column and level actually if you're taking any constraint What happened means this one I'm getting it.

[3:59:33] it. Is it okay all of you? that what is that one? So the column and level what is table level. So we need to

[3:59:45] understand. So column and level means if you're having assume that if you are having a table employee number, employee name and also email and address.

[4:00:01] and address. So mobile number or something. So try to understand if you're assigning if you're taking any constraint that may if you're taking any constraint that may be unique constraint

[4:00:20] for one column. Right? For example, if you're assigning a primary key for this particular column. So this one is at column level. Column level means what? So only for the particular column

[4:00:33] but think about yourself if you are assigning at column level for example. So this email should be what is that one so unique constraint unique constraint so and this one should be a primary key.

[4:00:52] at column level so only the particular column so does not allow duplicate column so does not allow duplicate values correct not allow so duplicate values and also null values for example if I'm trying to

[4:01:07] so again what happens one is it possible to insert one again no the same one cannot be repeated 10 so only for the particular column so that

[4:01:23] constraint is applied that rule is applied so the same value cannot be the same thing again so we are getting an error an error correct we are getting an error

[4:01:41] assigning a constraint at what is that on the table level on the table level what is table level

[4:01:55] we are assigning a constraint. So for more than one column. one column. So we are assigning a constraint.

[4:02:11] it as what is that one? composite constraints composite keys per suppose constraints composite keys per suppose what is that one so the composite

[4:02:24] so what is composite key so try to understand here so put your focus here same thing only for example so if you are taking so employee number and email or employee number and name and email

[4:02:41] so what What I'm doing here is for employee number and name combination. employee number and name combination. So I'm assigning so one primary key.

[4:02:54] combination. So I'm just assigning what is that one? So some primary key. So for example, so one sur

[4:03:08] here also I'm keeping so at the rate ofgmail.com. In this case, what happens? This is a primary key, right? But not for the particular column. So, as a combination of employee number and name, correct? I

[4:03:23] assign one single key. What does it mean? The same employee number and name, same set of values cannot be repeated again. For example, if I'm trying to write so one again. So, that this one works. [snorts]

[4:03:39] This one does not work right. So the same set of values cannot be repeated again. The same set of values cannot be repeated again.

[4:03:52] For example, if I'm trying to specify one ROM and some email id this one accepted. Yes, this one is getting accepted.

[4:04:06] is we are assigning a composite key whatever the rule we are specifying so that is applied to the set of values that means if I'm assigning a primary key for this employee number and name

[4:04:23] values correct the same set of values first this record is accepted next the same set of values cannot be repeated again because you Oh, the primary key means what? It does not accept duplicate values and null values. For example,

[4:04:38] especially uh if you're taking your account number, your account number is considered as what is that one? So the primary key if you have opening an account so compulsory you must have a so account

[4:04:52] number right and along with that one so the same account number cannot be duplicated again. The same account number cannot be The same account number cannot be duplicated again.

[4:05:04] So that's what I'm trying to tell you but it's composite means what? So for more than one column so we are assigning so same primary key or unique key that is your wish. For

[4:05:16] example let's say here I'm assigning so for the same table for the same table take the fields employee number name

[4:05:30] So what I'm saying is for the name and email as a combination I'm assigning one unique constraint employee number I'm assigning primary key.

[4:05:44] What does it mean? So unique means so the same name name and email cannot be repeated again and again right? Same name name and email as a combination so cannot be repeated and again and again. For example, employee number one and

[4:05:57] name is ROS. So some email for suppose again if I'm trying to give so same ROS and same email then what happens automatically this one is record is getting rejected correct.

[4:06:12] So this one is getting rejected. This will not allow you to insert. So the reason is so the same record you are trying to so the same record you are trying to insert right the same record with name

[4:06:26] and also email. So we are trying to insert. So the same record we cannot insert. So the same record we cannot insert it like that. Is it clear? So what is composite key? All are comfortable in understanding. So

[4:06:40] the composite key do you have any questions? have any questions? Means I will explain it again.

[4:07:01] So look at this. I'm just dropping this table for suppose. What is the table I created earlier?

[4:07:13] table. Or otherwise we can keep this employee table as it is or otherwise that is your wish. I'm not if you want to drop this table so same table if you want to create so what is the command so drop table

[4:07:27] if I'm creating so drop table employee so then automatically what happens the table is getting dropped h can you please tell me how can we check how can we check whether the table is present or not so

[4:07:42] whether the table is present or not so you can just execute so show tables you can just execute so show tables Right. So show tables command

[4:07:54] which is mainly for displaying. So all the tables. present. Now I have to create a table with the employee number. So my requirement is this one. So I want to create a table.

[4:08:18] employee number, name and also email and assign constraints, right?

[4:08:31] Asign constraints. What are the constraints? So employee number as constraints? So employee number as so PK PK means what? So primary key

[4:08:46] So name will be what is that one? So not null. So name must uh accept the values. It does not to be null. And uh what is the another rule I am

[4:08:58] specifying for suppose? So email So email must be unique. just writing creating a table with all these specifications.

[4:09:13] these specifications. Let's create the table. So create table employee of employee number whose type is what is the type integer and especially

[4:09:27] this one must be what is that one the primary key. So we can specify what is that one. So the primary key and name. So worker of 100 right and also it is what is that not

[4:09:49] What is data type actually? So the data type is worker of 100 right? So the email is worker of 100 and also

[4:10:04] unique constraint I'm assigning unique means what it does not allow. So duplicate values it does not allow. It does not allow duplicate values. That's does not allow duplicate values. That's all. So I'm just executing this query.

[4:10:19] I'm sharing this one with you. So all of you please create the table. After that you after discussing this constraints you have to do on your own. So that one

[4:10:32] requires hands-on practice. The next step is what is the command we have to execute to see the description of the table. What is the command? So describe employee if I'm writing so describe employee so

[4:10:48] providing so complete description about this table. Anyone could you please unmute and speak with me. Can you please tell me what is the description it is stating? What do you understand from this description

[4:11:03] because you need clarity right? So somebody might be you're already uh good in this area for suppose. Can you please explain? So what is the description it is showing? So in my screen

[4:11:15] so take employee number what is null? No. What why it is showing? So nal has no any idea because we assigned what is that key what is the constraint I assigned on

[4:11:29] employee number primary key primary key means what it does not accept so duplicate values it does not accept null values also so that's why it is specifying as what

[4:11:43] is that one so no and for the name as well we specified not null correct for the name. What is the constraint? What is the keyword I assigned? Not null.

[4:11:57] What does it mean? So for name also it does not accept what type of values? Null values. And for email I specified unique. Unique means what? So it does not accept duplicate values.

[4:12:11] So here it is specified as unique and here it is specified as what is that? The primary key. So by describing the table we can able to understand what exactly the constraints that are applied on the

[4:12:25] particular columns. So here clearly we can able to identify. So on the employee number so what is the constraint is assigned. So primary key on the name uh not null constraint is applied on the

[4:12:40] email also unique constraint is applied like that. So all of you please execute that one. and try to insert the values. So try to insert the values whether it is allowing so duplicate values or not.

[4:12:55] having experience with the insert command. Right? I'm requesting all of commands whether that one is working or not. So could you please confirm

[4:13:13] I am just inserting so on my own per suppose. Let's see. It does not accept duplicate values. Right? So for this what I'm doing here Right? So for this what I'm doing here insert into

[4:13:33] number one and name is John and also the email will be so John

[4:13:54] insert multiple records right. So you can run this query this works successfully. So no worries without having any problem. So this one without having any problem. So this one works successfully.

[4:14:10] again. Suppose I'm just copying the existing of the query. one again. So John so the same record I'm trying to insert.

[4:14:26] What happens? The one is repeated again. So automatically we are getting so an error right. What is the error? It is saying so duplicate entry one for the key. So the primary it is clearly stating like

[4:14:41] so the employee number one cannot be inserted again. So that one is reading as duplicate entry. For example, if I'm trying to insert null value okay so instead of

[4:14:55] insert null value because if you're asking me what is primary key means primary key does not accept duplicate values and it does not accept null values as well. So now we can run this query.

[4:15:09] What is that once the employee number cannot be null. cannot be null. So that means the primary key is working fine. That means if we are assigning a primary key on the particular column

[4:15:22] this one is at column level on the particular column we are applying only on single column. That one does not accept duplicate values and also null values. Correct. Yes, great.

[4:15:38] So the next step is for suppose I am providing so providing so so different uh employee number two but what I'm doing here is for the name what is the constraint I assigned not null

[4:15:52] but I'm trying to insert null value here is it possible this record will be getting inserted not at all this record is not getting not at all this record is not getting inserted at all so the reason is

[4:16:07] for this employee name right I specified one keyword what is that keyword not null what is the meaning of not null so the particular column so does not accept null values

[4:16:20] so if I'm trying to execute this query so ultimately it is giving so an error right what is the error so column and name so cannot be null so

[4:16:32] the particular column so cannot accept what is that the null values like that. So not null means what the particular column so does not accept null values column so does not accept null values here I'm specifying for suppose

[4:16:46] here I'm specifying for suppose uh some name rajan so rajan at the rate of sogmail.com or something now look at this this one is unique and also not null and this name is not null

[4:17:00] and email is also not unique different email I provided so now this is a valid record or not as per our rules which we defined earlier on the query. So this email should not be uh email must be unique. It does not

[4:17:17] allow so duplicate values like that. So now this query works fine. We are not getting any error. So look at this. We are not getting any error. So the record is successfully inserted. Now we can check. So if I'm writing so select star

[4:17:32] from employee. So what happens? It is working fine as expected. Correct? It is working fine as expected. So we are getting so employee number, name and also email like that.

[4:17:51] is unique constraint, what is not null constraint and all. If you are okay, if you're comfortable with assigning unique constraint on the with assigning unique constraint on the particular column not null and also what

[4:18:04] is that once primary key so could you please confirm in the chat if you are having any questions please feel free to unmute and speak with me okay otherwise I cannot understand right

[4:18:22] thank you very much uh Nikita for responding because I'm putting so lot of effort right so whether you are understanding or not so that is my importance please remember that point if you are not understanding I'm ready

[4:18:37] if you are not understanding I'm ready to explain so multiple examples but be don't be silent but okay so but you understand so like at column level so only for the particular column so

[4:18:51] so only for the particular column so think about So the table level what is table level actually already I discussed right table level means what if you want to assign.

[4:19:08] So if you're asking me what is table level. So if you want to assign the constraint if you want to assign constraint.

[4:19:27] So for more than one column for suppose right so for more than so the table level for example so we have an employee number

[4:19:46] I want to assign the constraints so for more than one column so for examples I want to assign so primary key for these two columns as a combination so that means the same set of values

[4:20:00] cannot be repeated right so for example so one sur so same similarly again I'm repeating so one sur this one does it works no because the same set of values cannot be repeated

[4:20:16] because this one is treating a set of values like that but if I'm changing So one ra suppose this one is valid.

[4:20:28] Why this one is valid? The same set of values cannot be repeated. Same set of values set I am say speaking about here. Try to understand set in the sense.

[4:20:40] So again one sud cannot be repeated. So first we are having one sud and next it one ROS the same set of values cannot be repeated again and again. So this type of constraints we can call it as that

[4:20:54] of constraints we can call it as that one. So same primary key same unique key only but we are assigning for more than one column correct we are assigning for that one. So more than one column

[4:21:06] let's see how this one so let's assign so a primary key for first unique key so a primary key for first unique key for this employee number and name.

[4:21:18] So the employee table I'm dropping it. I need to check for what is that one. So I need to check for what is that one. So assigning

[4:21:40] table level per suppose. So previously we applied at a column and level. So now we are applying at what is the on the table level. So first I'm dropping the table. So before starting it. So first I'm

[4:21:54] checking what are the tables that are present. So what is the command we can use? Show tables. This command is mainly for displaying all the tables. Right? So we have an

[4:22:09] employee table is which is already present. But I want to I'm creating so employee table in dropping this table. So what is the command? So we can use drop table.

[4:22:23] What is the table name? Employee. I'm just dropping this table because I need to recreate this table again. I need to recreate this table again with what is that one? So the composite.

[4:22:38] composite. So composite unique key. So composite what is that one? So unique key like that.

[4:22:52] So create table. What is that one? So the employee we have. So the employee number whose type is integer and also name. What is this type? So worker of 100.

[4:23:08] What is this type? So worker of 100. and also the email. So which is having so the employee number and also

[4:23:28] and also email. So for this employee number and name so I need to assign. So as a combination of employee number and name I I need to assign what is that one unique constraint. So for this we are using one keyword.

[4:23:41] What is that keyword? So constraint and constraint name. What is the constraint name? That is your wish. We can provide any name but I am recommending so we can

[4:23:53] provide some meaningful name. or suppose for example I'm assigning unique constraint on employee number and name both the columns so in such cases best practice is what is that one so e number name underscore

[4:24:10] is that one so e number name underscore UK uk stands for what so unique constraint unique key we can feel like that or otherwise that is up to you you that or otherwise that is up to you you can provide UC unique constraint

[4:24:22] it's a best practice Right? Because after creating if you want to refer to that constraint so we can make use of this name so e number name u

[4:24:35] this name so e number name u what is the constraint we are assigning unique of so e number name here try to understand we have created what is that one so one

[4:24:50] unique constraint and the name of the constraint is what and the name of the constraint is what is that one? So, E number name U and which columns we are assigning the constraint? So, E number, name for

[4:25:02] assigning what is that one? So, unique constraint like that. So, just execute this one. So, you can run this code. So, you can able to see so the table is successfully created.

[4:25:16] I'm just sharing this query. So, please look into that query. So for your understanding because this is the first query which we are writing at the table level right so that means we are assigning a constraint so for more than

[4:25:29] one column I just state that query in the chart so please observe that one so now I'm trying to insert the data

[4:25:43] columns as a combination of e number and name I assigned what is that one so any constraint so now let's try to insert so the data suppose insert into

[4:26:01] first check so show show tables right so first check show tables whether the tables are getting created or not if I'm executing show tables I can see so the employee table is getting created Employee

[4:26:22] So once the employee table is created so now we can try to insert the data insert into what is the command. So the employee

[4:26:37] so Ram so Ram at the rate ofgmail.com but suppose I am keeping so Ram at the rate ofgmail.com but try to understand here this is the first record

[4:26:51] okay so the first record will insert successfully so without having any worries right this one will execute so without having any problem and also to see that records So [snorts]

[4:27:04] you can just go there. So select star from what is the table employee table if I'm issuing. So select star from employee. Of course we are getting one record. Now the question will start. What is

[4:27:21] that question? Can I insert so same employee number name as a set per suppose? So try to understand. I'm just copying this query and I am reexecuting this query again. So same employee number and name but uh I'm changing what

[4:27:38] number and name but uh I'm changing what is that one for the male for suppose. is that one for the male for suppose. So the male I am keeping it as Ramon. Now can you please tell me this one works? Would you please type it in the

[4:27:50] chat if this works? I have written so chak I will explain it again so no worries that is the name of

[4:28:06] the constraint name of the constraint is different unique is different unique is the actual keyword which we have to assign which we are using to assign unique constraint name of the constraint which we are using for our purpose I

[4:28:20] will discuss about that one over this first please concentrate here so first our concentration is whether this one will execute or not if I'm running this query whether this one will execute or not it does not work

[4:28:35] right because with the same set of values already one record is present we values already one record is present we can run this code and verify so look at this it is giving error what is giving so I'm just pasting this one

[4:28:50] is giving so I'm just pasting this one in notepad so for your better visibility so I will show you what is the exactly error it is facing

[4:29:14] So duplicate entry one-h ROM right the duplicate entry one ROM it is repeating again and again. So we are trying to insert so the duplicate entries that one does not work. So what we have to do per suppose I am changing

[4:29:27] we have to do per suppose I am changing this one to different name perose. Now it works. Yes it works. The reason is the same set of values are not repeated again. Right? So previously one ROM but uh same one is

[4:29:43] repeated but the name is not repeated. Try to understand. So the name is not repeated. Name is Raman. So previously Ram now it is getting changed to Raman. Ram now it is getting changed to Raman. So now this one works without having any

[4:29:56] at this if I'm trying to execute this So I am not getting any error right. So this one works fine as expected. So now I'm writing so select star from employee

[4:30:13] you can see so the record is getting inserted that one is not written as inserted that one is not written as duplicate record. The reason is in the case of what is that one? So composite keys. If you're assigning a key for

[4:30:26] employee number and name the same set of values cannot be repeated again and again. For example, here one Ram and here one ramen the same set of values cannot be repeated again and again. So that's what I am trying to tell you.

[4:30:41] Is it clear all of you? What is composite key? So we understand about so what is composite and what is normal key right we understand about so what is the normal constraint normal way of applying

[4:30:55] normal constraint normal way of applying constraint

[4:31:11] on only one column right might be applying on so employee number or somewhere but composite key means what? So more than one column for what? So more than one column for example so employee number and name

[4:31:25] we are applying so single unique constraint or primary key whatever it constraint or primary key whatever it may be but now I am talking about so the

[4:31:38] so try to understand so what exactly foreign key how it behaves this is super for example let's assume that so we have an employee table

[4:31:52] capable of storing employee details, department details. So my question is is it possible to store employee details for example employee number, name and email

[4:32:18] and also salary. and department name

[4:32:31] So is it possible to store all these details in a single employee table? might be there. For example, what are the problems? For example, one employee

[4:32:45] number and name is Sam sures some surregmail.com whatever the email and salary and department name is so analytics department per suppose

[4:33:09] who are working in the same department right who are working in the same department for example. Employee number two two and uh name is John

[4:33:24] and salary is something and he is also working for what is that one? So same analytics department and it is also located in same USA

[4:33:39] but try to understand so for example so we have another record

[4:33:51] salary and he belongs to analytics department only but his location is different perose. perose. So SF, San Francisco

[4:34:06] or otherwise so New Zealand or some some other country per suppose NJ. So but what happened here the same analytics department is repeated again and again. So same department name is repeated

[4:34:19] again again and again. So if you are having if you want to maintain the data entire the application data for example even if you are using some applications right in real life might be you are booking uh

[4:34:34] uh in India might be there is a gumato app right application or swiggy those are all food order applications like Amazon

[4:34:46] in Amazon also we can purchase product but try to understand in a relational database management system we are not going to store the entire data in a single table. So that is the first question is it correct? Are you

[4:35:01] agree with me? So is it possible to enter store the entire data in a single enter store the entire data in a single table? It's not a good practice. Why there are some problems? What are the problems? So the same values cannot

[4:35:15] be repeated again and again. Might be I'm purchasing a laptop. So for suppose uh Lenovo laptop some model the same laptop will be purchased by many people

[4:35:28] so the same records will be repeated again and again. So this problem we can again and again. So this problem we can call it as data redundancy problem. observing so the same analytics department is repeated again and again.

[4:35:45] So for that one what we have to do as a database designer we have to perform so normalization. theoriatically might be in the next session. So in the tomorrow session

[4:36:01] normalization means overall high level we can try to understand it is not possible to store the entire data of your application in a single table. for Amazon. Amazon contains many tables. It's not

[4:36:18] possible to store the entire data in a single table for that. single table for that. So normalization is a process So normalization is a process of decomposing

[4:36:32] right of decomposing a big table into what is that one? So smaller tables.

[4:36:45] smaller tables to reduce radar redundancy to reduce radar redundancy to reduce data redundancy

[4:36:58] means what? So to reduce data dependency and also errors we can say. So in database terminology we can call that errors as anomalies.

[4:37:12] So I'm not talking about what errors we are getting right now. What I'm doing is so the about table which is holding so the employee details and also department details and location details instead of keeping all the details in a

[4:37:25] tables into what is that one? multiple tables per suppose I'm just dividing that entire entity so entity is nothing but what is that one so the table so we have so employee table

[4:37:46] and also location table so we are creating so three different tables this employee table contains what are the fields employee number name email And department table contains department ID and department name.

[4:38:03] And location table contains what are the fields? Location ID and also location name. Can you please tell me if you are creating a table in a Veracle or MySQL or any database every table is

[4:38:17] associated with one primary key. Is it correct? Here employee number is a primary key. So department ID is a primary key and location ID is a primary key. What is the purpose of primary key?

[4:38:31] Primary key is mainly for uniquely identifying a particular record. Okay? So primary key values cannot be duplicated and primary key does not accept null values as well. Correct? So the primary key does not accept null

[4:38:44] the primary key does not accept null values and primary key does not allows duplicate values. So we have so these tables for suppose and we have so department table and we have what is that on the location table.

[4:39:03] So the location details might be stored in a separate one. So location ID 101 and location name is USA and 102.

[4:39:17] right? So NZ and we have so department. are there for suppose one is analytics department.

[4:39:29] department. So for example so analytics department So for example so analytics department and uh another department might be what is that one? uh the programming department

[4:39:47] and we have employees but suppose employee number so name is so ras so he's having some email id in a similar way so ramen in a similar way so ramen or ram is having some email id

[4:40:01] but how these tables are connected with each other each other for example let's Okay. particular department for example. So Raj is working in

[4:40:15] department analytics department. So we need to provide some mapping between these tables. Right? I will discuss about what are the mappings we can do. But what I'm doing here is I'm just keeping so another

[4:40:28] extra column in employee table. this is called as what is that one? So the foreign key FK FK stands for what? So the foreign key. So always foreign keys is referencing

[4:40:43] So always foreign keys is referencing what is that one? So the primary keys analytics department means we can just keep what is that one? So RO department number is what is that one? So 10. So the foreign key value will be

[4:40:58] That means Raj is belongs to what is the department? So analytics different department. So if you're asking me what is foreign key means it is just a reference. Okay. So reference refer to the another

[4:41:14] table record. So already in department table. So we have department ID 10. One record is present here we are keeping. So the foreign key. So department number 10. So this foreign key references what is that on the primary key.

[4:41:29] located in what is that one? So the New Zealand perose, right? So the location ID if you're keeping here this location ID we can

[4:41:41] call it as what is that one? The foreign key. This location ID refers to what is that once location table primary key. So please remember foreign keys always refers to what is

[4:41:55] that on the primary keys. It is just a link. It is just establishing a link between the two tables. For example, so your location ID is 102

[4:42:07] I am keeping. So that means Raz is working in analytics department and he belongs to the location. So New Zealand

[4:42:19] and in the similar way so Ram is also working in analytics department only because here 10 means what? So this one is referring to this record right and he belongs to what is that one? So 101 location

[4:42:35] 101 location that means USA location and Raj belongs to New Zealand. So Ram is belongs to what is that one? So USA location like that. So in this way so what do you

[4:42:48] understand? It's not a best practice to store the entire data in a single table. Right? In a real life we have so if you are working with the real life data right

[4:43:03] your data will be stored in what is that one the multiple table not like excel sheet in excel sheet what you done might be already undergone excel training we have entire excel sheet might be that excel sheet is containing entire data so

[4:43:19] what we have to do we have to use the normalization process have to split the table into what is that one? So multiple tables we have to

[4:43:31] identify the entities in the tables. For suppose in the Excel sheet we have so lot of data from the data we have to identify what are the uh possible entities for our application and we can divide that one into what is

[4:43:46] that one for multiple tables and we can provide the link between the tables with the help of foreign key. Yes, we have to link the tables with the help of foreign key. But

[4:44:00] identifying relationships is also biggest task right I cannot say it's not easy if I'm providing so some data set per suppose for you in the excel sheet you want to transform that one you want to

[4:44:15] migrate that one into the SQL tables if I'm giving some task for you what happens first we have to identify what exactly the data and what exactly the domain it is pointing According

[4:44:30] to the domain, we have to identify the entities in that data set. If you are taking HR data set, so there may be employees are there, departments are there, locations are there. Okay, there are so several entities will be

[4:44:45] involved. We just divide that entities and we can create a separate tables. But while after dividing the entities, right? not only dividing the entities I'm

[4:44:58] speaking so we need to identify the relationship between the tables also so if you are speaking about the relationships [snorts] so listen this one gives more knowledge for you after dividing the tables

[4:45:13] how to provide the relationships between the tables if you are talking about relationships we have so one to one relationship

[4:45:27] And also we have so one to many relationship correct

[4:45:39] In the reverse we can call it as what is that one so many to one relationship. And also another one is what is that one so many to many relationship.

[4:45:57] relationship and one to many and many to one and also and one to many and many to one and also many to many relationship.

[4:46:09] one relationship can you please give me one example per suppose so we have an one example per suppose so we have an two tables per suppose employee table two tables per suppose employee table and passport table.

[4:46:25] the employee details and passport details in a single table. So employee table contains employee details like employee number, name. employee number, name. So address or email

[4:46:37] and passport table contains what is that one? So the passport ID, passport number and expiry date perose.

[4:46:51] primary key and also your passport ID is called as primary key. So we have two called as primary key. So we have two tables for suppose. passport ID, passport number and also expiry date.

[4:47:06] So these two tables are individual tables. Passport table is mainly for storing the passport details and employee table is mainly for storing the employee details. All of you are listening carefully what I'm speaking.

[4:47:22] employee and passport details in a single table. So we are identified as two entities per suppose. So what we done we identified as what is that one? So single employee table and also passport table separately.

[4:47:42] we have to create two tables means there is some problem like beha problems we have. I will discuss about that problems. what problems we are

[4:47:56] getting. Okay. So otherwise you can just visit the self-arning so you can able to understand what exactly normalization I will discuss maybe tomorrow I will uh show you the slides and I will explain

[4:48:09] what exactly normalization process like that but in this case we have to identify what is the relationship between these two tables.

[4:48:22] relationships right. So I identified this one is so one to one relationship one to one relationship means what one what is that one? So one employee one instance of passport.

[4:48:39] So for example, so we have so the passport ID passport ID and passport number and also expiry date

[4:48:57] this instances for suppose the passport is this is the passport passport ID one in such cases where we can create foreign key foreign key or passport table you can create foreign

[4:49:13] key that is up to you here I am making so employee is the warning side of the relationship so we have to decide what is that one so

[4:49:28] warning side of the relationship what is that one so warning side

[4:49:44] either we can do the warning side as employee or passport for example if I'm doing side as employee what I'm keeping so the passport ID as a foreign key I'm keeping I am keeping so this passport ID as a

[4:49:59] foreign key this foreign key references what is that one the primary key Right? So if I'm keeping one here or otherwise

[4:50:12] so you can maintain so different passport ids here. So this is 101. If I'm keeping so 101 so that means this one is mapped with this passport record right and this is one approach

[4:50:26] so or or otherwise I'm not saying so like always we have to I'm not saying so like always we have to keep so the foreign key at employee side that is your wish we can choose your option

[4:50:39] so either we can keep it so the employee number and the passport ID per suppose employee number I'm keeping it as foreign key here. So this employee number is a foreign key. But this foreign key references

[4:50:51] what is that one? So the primary key for example. So this foreign key references this primary key. That is also possible. decide. So which entity is the owning side of

[4:51:05] the relationship. That is the super important step here. What is the entity I'm choosing as a owner of the relationship in this context. I'm choosing employee is the owning side of the relationship. So that's why I'm

[4:51:19] of the relationship. So that's why I'm keeping so the passport ID here. So this is acting as what is that one? So the foreign key which is mainly for providing the reference between these two tables. So

[4:51:32] please remember and keep in mind. So very very important point foreign keys are always referring to the primary keys. Correct? Foreign keys are always referring to primary keys. That means we are just providing a reference

[4:51:46] of what is that one the passport here. So this 101. So this one is mapped with So this 101. So this one is mapped with what is that one? So the passport ID

[4:52:00] So try to understand in the case of one to one mapping only one instance of employee is associated with one instance of passport. one instance in the sense one record of employee is associated with what is that

[4:52:13] one so one record of passport okay so that type of relationship is called as what is that one so foreign key relationship sorry what is that one so onetoone relationship so in real life also employee one

[4:52:30] multiple passports might be that is the business case right that is the scenario if one employee is associated with only one passport In such cases we have to provide one to one. Can you please tell me if it is one one to one.

[4:52:46] So for this foreign key column you must assign unique key also. Right? It is foreign key and also as well as what is that one? So we have to assign an extra constraint. What is the extra constraint?

[4:52:59] Unique constraint because the same set of values cannot be repeated. For example, if it is in one to one relationship, can you please tell me can I write it like this? So two ROS and also email id again is

[4:53:14] having so 101. So two people are having same passport number. Are you understanding what I'm speaking all of you? Two people are not having

[4:53:28] all of you? Two people are not having same passport number. So how can you restrict the duplicate values? You can restrict the duplicate values by extra we are assigning what is that one so unique key. So that means uh this

[4:53:43] person is having so 102. So a separate passport number. So that passport is having so different expiry date like this. This is called as one to one mapping. Yes, you are open to the questions. Please start your

[4:53:57] questions. All of you understand what exactly one to one mapping.

[4:54:09] Might be I'm asking you in the chat or otherwise you can unmute and speak. Can you please some give me some examples for one to one mapping? for one to one mapping? One employee one passport that is okay.

[4:54:21] Any other scenarios for suppose one employee is having one other card number correct? correct? Any others

[4:54:40] Very good. But one employee is having multiple account numbers, right? Sub one employee is having so multiple account numbers. Yes. Please go ahead.

[4:54:52] There is no need to raise hand. You can unmute and free feel free to speak. unmute and free feel free to speak. >> Uh sir actually uh if

[4:55:21] >> Good question. I understand your scenario. See, good question. Right. So, Swati is raising. See what happen means sometimes some employees are not having passport. Yes, of course. If if he the employee is

[4:55:36] not having passport. So, in such cases what this foreign key will allow null Okay. There is no need to specify explicitly null. That is one good question. That means might be employees having

[4:55:49] passport. If employee is not having passport, I cannot say always mandatory. So the employees having passport, right? For example, so if you're taking uh uh some some people so those who are working in domestic

[4:56:05] perose they are not having they don't want any passport want any passport suppose assume that in such cases what happens for that employee so this value should be null.

[4:56:17] So foreign key also allows what is that one? So null values also. So key also can change for every employee not required. So we are just assigning a foreign key. So the foreign key by default. So if you are taking so

[4:56:31] key by default. So if you are taking so the foreign key right. also. There is no need to specify. So null explicitly.

[4:56:43] But suppose if you want to make it so the foreign key must be unique in the case of one to one mapping right in the case of one to one mapping per suppose unique unique means what? So the same set of values cannot be repeated. Now in

[4:56:59] the case of unique also it will allow null values. What is the purpose of keeping so separate foreign key for each record? Not required. So on the top of column itself you can mention that one as foreign key. Correct?

[4:57:12] Is it clear Swati your question? Sorry any Sorry any what exactly? This is your question.

[4:57:27] values also. Okay. So the next step is what is that one? So one to many. But one question I am asking before please tell me in the case of one to many relationship

[4:57:42] right? In the case of one to one per suppose. So we have to decide. So warning side of the relationship right? Warning side

[4:57:55] the relationship right? Warning side means what? So that is either employee means what? So that is either employee side so the passport side who will decide

[4:58:11] who will decide that is your wish. We can choose okay according to your uh right your requirement in business we either we can keep so that one in the employee or in the passport.

[4:58:27] Another question I'm asking I'm shooting it here. What is the purpose of providing this foreign keys? Right? For example, let's say in your application if you uh uh suppose you are

[4:58:42] working as a HR we are searching for an employee support for what is that one? So getting other details also right. So we are visiting the employee and also from the employee we want to know the

[4:58:58] employee personal information the employee pay pay scale information and everything we need to get. So pay payroll information will be present in payroll table. Employee information is present in employee

[4:59:12] information is present in employee table. But still the employ still the HR knows only employee details. From that employee details he can able to fetch he or she can able to fetch. So the pay payroll details and other

[4:59:26] is the benefit of what is that one this foreign keys. one? So we have so one to many relationship. So try to understand. So

[4:59:40] what exactly one to many but this one is bit uh difficult to understand. So bit uh difficult to understand. So please concentrate. many in the reverse [snorts]

[4:59:55] it is so many to one there is no particular special right so one to many and reversal it would be one to many and reversal it would be many to one

[5:00:10] suppose for one to many anyone can you please tell me one scenario for one to We are all students for suppose as a part of this course students and courses that is not one to

[5:00:26] h one employee is having so multiple phone one employee is having so multiple phone numbers that is correct [snorts]

[5:00:38] one of you mentioned like student and courses it's not one to many why It is not one to many

[5:00:53] is comes under what is that one so many to many it's not one to many because we can think in a way one student can register for many courses correct if we are thinking in a student point of

[5:01:06] view the student can register for many courses and the same course is registered by many students so that means it is One side one to many and other side it is one to many again.

[5:01:20] So this becomes what is that one? So many to many relationship. Many students are registered for many courses. Many customers can place many orders. Customers and orders are also many to many relationship.

[5:01:34] Surash. Okay. Uh customers and orders also. Okay. So you are talking about customers and orders, right? Let's rethink. rethink. So think yourself okay it's one to many

[5:01:48] only it's not many to many sorry surash the reason is order ID will be different the reason is order ID will be different right so order ID will be different customers and products now tell me what is the relationship between the

[5:02:02] is the relationship between the customers and products under one to many many customers Customers can order many many products.

[5:02:18] Okay. So if you're taking so customers and orders if you're taking so customers and orders for suppose simple example customers and orders it is one to many relationship right.

[5:02:37] example if you are taking customers table. So customers table contains customer ID, customer name and also it contains some other details also right I'll just show you I am just

[5:02:53] designing it here so please look at this I'm just opening so the MySQL workbench so go to file new model see here it is providing some option to

[5:03:05] see here it is providing some option to create a new model I'm just clicking creating what is the new table for suppose

[5:03:33] table name so the customer table I am creating suppose the customer table and this customer table is having some properties. So the customer ID

[5:03:57] and also as well as customer address. Okay. So these are the three fields orders table per suppose this customer is placed how many orders

[5:04:13] if we are taking so customers and orders and customers and products okay there are some relationships will will come but right now I have customer and order so that is in my main so what I'm doing is I'm selecting a new

[5:04:27] table I'm just double clicking here so I'm I'm just double clicking here so I'm getting so the orders table

[5:04:42] one so the orders table in the orders table so we have some in the orders table so we have some fields so the order ID

[5:04:58] h so quantity and also price per suppose so we can say and also price per suppose so we can say so total price

[5:05:11] type so integer type so we have so these two tables relationship one to many relationship Right.

[5:05:23] Please listen. So in one to many relationship always it is a best relationship always it is a best practice. foreign key on many side. What is the many side here? So one customer can

[5:05:38] many side here? So one customer can place many orders. Correct. The same order cannot placed by the another customer because every customer if customer is placing an order means the order ID will

[5:05:50] be different. Right? So for every customer the order ID will be different. In such cases only the blind way of thinking is always many side we can create the foreign key.

[5:06:03] create the foreign key. For example if you are going here. So if you want to specify the relationship here.

[5:06:16] have. So 1 to n. So one to end relationship per suppose you can select this one. So first I'm selecting order and after that I'm clicking on customer.

[5:06:30] So look at this the foreign key is created here or not. So in the orders table so the foreign key is getting created. So you can see in orders table so the customer ID is

[5:06:44] key is referencing what is the primary key. So customer table so customer ID as a primary key. primary key. So might be you are asking me question

[5:06:58] why we are creating foreign key orders table. So in the case of one to many relationship especially it is a best practice always many side we can keep the foreign

[5:07:10] always many side we can keep the foreign key right so many side always we can keep the foreign key like that so the customer ID so that is your wish you can provide any name might be customer c if you want to

[5:07:25] change the name here so just double click and you can go to this column and we can specify what is that one. So the customer id so if you want to change it per suppose so if you're observing so this one is

[5:07:39] red color right so this one is called as what is that one the foreign key this foreign key is referencing so the customer ID primary key like that customer ID primary key like that so this is the way we have to specify so

[5:07:53] so this is the way we have to specify so one to many relationship any questions you can unmute and speak with me and also try it how to go how to

[5:08:06] with me and also try it how to go how to create a model just go to file new model okay so then automatically it will open that one if anyone is not getting screen I I'll help you how to create this ER diagram stands for what entity

[5:08:22] relationship diagram entity relationship diagram

[5:08:34] Is it clear all of you? All of you can able to follow up the session. All of you can able to understand. So what is one to many relationship? diagrams but try to understand the concept first. What is one to many

[5:08:48] concept first. What is one to many relationship and how it works. the ID key will appear? You can share your screen maybe I cannot

[5:09:03] will appear? >> Uh sir actually I didn't uh uh install >> Okay. Got it. Got it. Got it. >> Sir my question is um you said that uh

[5:09:15] >> Sir my question is um you said that uh there some uh ID uh appear. So where is the uh foreign key? Sorry. Where is the foreign key appear? So we can recognize >> it's many side many side always right first if I'm just going back for suppose

[5:09:30] look at this >> there is no relation now correct >> yes >> in this tables might be you are having you have to make a decision as a database designer correct

[5:09:43] >> so where I can keep foreign key first what we have to do is first we have to identify the relationship between these two tables customer and orders you two tables customer and orders you identified as it is one to many.

[5:09:56] So always your recommendation should be always many side you can keep the foreign key. What is the many side? Many many many orders right? Customer can place many orders. So that's why you can just go there and

[5:10:11] So that's why you can just go there and select can see we have so relationship symbols also we have so relationship symbols also available here.

[5:10:28] you can see. So 1 to n right just click one to end first check on click on one to end first check on click on orders and next click on customers. So automatically so the foreign key is created here. Look at this radical

[5:10:42] foreign key is created right. So this foreign key is referencing what is what is that? This foreign key is referencing. So the primary key so customer cid this one is a foreign

[5:10:55] key that is referencing what is that one so c I column like that so c I column like that >> sir can we change this um foreign key or >> keep it in customer you are asking correct

[5:11:09] >> yes >> that is not a good practice I'm saying okay so the reason is in the case of one to many relationship always many Even if database designs right so always you are

[5:11:24] keeping so many side as a foreign key that's what I'm saying best practice I'm saying so you people suppose if you are new to the database design if you new to the database table creation and all so what

[5:11:37] happens how can you say whether the particular order belongs to the particular customer if you are keeping order ID in the customer table but it works

[5:11:50] But when coming to the frameworks right I am an application developer I designed I am an application developer I designed databases in my experience I identified what is that one so always many side we have to keep the foreign key that is a

[5:12:04] best practice that's what I'm saying you can observe so many databases in uh uh by asking in uh what is that one so charg for example if I'm asking in charg for example if I'm asking in charge GPT for suppose

[5:12:21] So I'm just opening. So charg.

[5:12:45] We are creating my screen is visible all of you. my screen is visible all of you. Why we are creating foreign key

[5:13:11] what is that one? So which record in the one side to the many records it belongs to. So it is giving some information right. So you can just check it out. One department can have many employees per

[5:13:24] suppose in the similar way. So one customer can place many orders right many orders. [snorts] So each employee belongs to one department. So the order whatever

[5:13:38] the if you other side if you are taking one order belongs to one customer only. So if you are talking about where the should the foreign key will go is

[5:13:52] in employee table so we are keeping so department ID as a foreign key in department table. So department ID as a foreign key uh primary keeper suppose.

[5:14:04] Okay. So the logical reason is so if you're keeping foreign key in the one side per suppose it would not work properly it's a wrong

[5:14:19] design okay so because the department is having so 50 employees per suppose you can just uh correlate your queries like this per suppose okay then you and able to

[5:14:34] suppose okay then you and able to understand. So what exactly the problem? one and you can discuss put the discussion tomorrow also then you can discussion tomorrow also then you can able to understand.

[5:14:52] question you please unmute and speak. question you please unmute and speak. Sir can you show the linking part once Sir can you show the linking part once again or did you link both? One second.

[5:15:04] linking part in the sense which one you ask the foreign key primary care. >> Okay. This one only right? >> Yeah.

[5:15:17] >> Yeah. How how did you link that this arrow one? >> Nothing. It's uh so just I'm just deleting existing one.

[5:15:30] present. Right. >> Yeah. Here we have so the links are correct. >> Okay. >> You can select so one to many in a strong way. So not like dot dot dot

[5:15:43] in a strong way. So not like dot dot dot 1 to n. First you can select the orders 1 to n. First you can select the orders because one order belongs to >> Yeah. >> One customer can place many orders. So

[5:15:57] here orders I am selecting and also customers I am selecting. So automatically what happens there is a relationship will be established between so the customers and orders one order can

[5:16:10] the customers and orders one order can uh one customer can place many orders in the customer ID is allocated as >> customer ID it is taking automatically when you have the automatically it will select okay that is the design right the

[5:16:23] >> okay >> that is the good thing about what is that one workbench like that >> I think Others is asking so sir should >> I think Others is asking so sir should not the quantity type be in integer. So

[5:16:37] integer means a quantity in the sense in my scenario from the context it will be varied actually you have to specify it as integer you have to specify it as integer correct the reason is the quantity

[5:16:49] correct the reason is the quantity number right that is a number but now the question is what I'm asking is I think Swati mentioned right so why we have to keep that one as a what is that one so separately foreign key or

[5:17:03] primary key in the many trade. Okay. So that one you can just do some research. Okay. So always it is a best practice we can specify what is that one so the foreign key and what is that one the many side again

[5:17:18] I'll come back to the discussion and last one what is that one so asking me this is one to many and many to one correct could you please tell me

[5:17:32] so this relationship will cover what is that one so one to many and many to one if you're having details we can able to get the customer details. Correct? We can able to get the customer details and if you're having customer details

[5:17:48] then we can able to get what is that one. So who are all placed that uh who are all customers that means what is the customer details and also who what are the orders the customer placed all those details we can able to get it

[5:18:04] up to here. Is it okay all of you? So one to many relationship. Do you have any questions? You can unmute and speak with me.

[5:18:25] more thing I need to talk about what is that one? So many to many relationship. So if you're asking me about so many to many relationship, right?

[5:18:54] one? >> Yes, good question. >> So I'm just speaking like an application point of view. Right. See you are not inserting so data manually. Who will insert how you are inserting the data?

[5:19:09] Could you please tell me I think uh good discussion actually for suppose in real life how you are inserting the data you are booking a flight ticket correct for suppose if you want to travel you are booking a flight

[5:19:25] ticket. Let's take some example how you are interacting with uh how you are interacting with uh uh the database through some application might be you're going to some website okay so the flight booking website

[5:19:41] flight booking website not google and all if you're going to the directly providing your details and your booking correct so your details will be you are a customer for that airlines lines

[5:19:56] and flight details will be stored in a separate table. Per suppose correct [snorts] your details will be stored in a separate table. Flight details will be stored in separate table.

[5:20:08] In such cases if you are providing so many side as a foreign key in the application point of view in the in the programming point of view. So what happens means so your question comes into the picture

[5:20:24] right what is your question so I am having order details so I specified foreign key here for suppose let's say here I specified foreign key in one to one what I specified foreign key

[5:20:39] in employee table correct so that means if I am having employee details I can able to fetch the passport what about reverse mapping reverse mapping means if I'm having passport details. How can I fetch the employee

[5:20:52] details? Whatever the frameworks we have available in the market programming frameworks I'm talking about all the almost all the programming all the almost all the programming frameworks are having are suggesting so

[5:21:05] don't create foreign keys on both sides. For example, if you're having so the passport details, so from the passport details, if you want to fetch the employee, if you want to get the employee, so don't create

[5:21:19] foreign key, employee number on. So passport table that one decreases the performance. So internally the frameworks which we are using in programming. So those frameworks will support you

[5:21:33] without creating a separate foreign key. So it is using the same foreign key. So to get the passport details as well in a reverse mapping for example. So frameworks means might be if you are working with PHP or Laravel or otherwise

[5:21:48] if you are working with Java there are separate frameworks are there for implementing this uh inserting records into the database right we are writing some logic those frameworks what they are suggesting is don't create

[5:22:01] foreign keys on both sides. If you are creating foreign keys on both sides right so automatically it will degrade the performance. So it will decrease the performance like that

[5:22:18] foreign key. So in the reverse mapping also that the passport if you want to get the employee in the reverse mapping also we'll use the same foreign key. Okay, we will use the same foreign key to get the

[5:22:32] employed place in the database level. It is not you are designing a database means according to the application only we are designing the database because your databases are designed

[5:22:45] databases are designed to store the application data only correct to store the application data. Whenever application programs are Whenever application programs are interacting with databases,

[5:22:59] create what is that one multiple foreign keys on both sides. keys on both sides. We'll use the same foreign key We'll use the same foreign key to refer from reverse side as well. Hope

[5:23:11] you understand Swati. So what I'm saying is it clear

[5:23:24] automatically your performance will be gone so let's think about another table per suppose many to many relationship suppose many to many relationship what is that once the customers

[5:23:42] and also as well as what is that one so products so customers products customer is placing order for products right we have customers orders table customer products table but what is the

[5:23:56] relationship between the customer and products anyone can you please guess it

[5:24:11] correct Many to many, many to many means what? Many customers will order for many products. In such cases, please remember in the case of many to many relationship, how

[5:24:26] the database design looks like, how the tables will be created here is. tables will be created here is. So, we have a customer table So, we have a customer table and also the products table.

[5:24:44] ID right. So customer name and also email and also products table is having so and also products table is having so product ID.

[5:25:02] So for example, so your customer ID is a primary key. So so your customer ID is a primary key. So product ID is a primary key. But what happens means in the case of many to many relationship.

[5:25:16] created. So this one is called as might be So this one is called as might be customer products. So one separate table will be created. So customer what is that one products?

[5:25:40] we can able have we have so two foreign keys one is customer ID and another one is what is that one so product ID product ID so this product ID is a foreign key

[5:25:59] key so this customer ID references so customer table so primary key and this product ID references what is that one so product ID primary Okay. called as what is that one? Junction table.

[5:26:18] In the case of many to many relationship always junction table will be created. So this junction table what it contains? It contains the foreign keys for suppose for example. So the customer ID it is having some name and also email

[5:26:32] and product ID is also 101 which is having so some product name. If customer is placing an order for a particular product. particular product. So customer I and also 101. So customer

[5:26:47] So customer I and also 101. So customer is two per suppose and also he also placed so the product order perose. So product ID.

[5:26:59] So two customers placed for same product, right? Same product like that. Same product will be ordered by many customers.

[5:27:12] customers can order many products like that or many products will be ordered by that or many products will be ordered by many customers. what we can say. So junction table will be getting created in between. So the

[5:27:27] order table will also include. So let's see here. I'm not instructing anything. So I'm just creating what is that one? So products table here. I understand your questions.

[5:27:40] So let's see after that we'll see. So let's create a product table. So I'm just clicking on. So what is that one? So this uh table and I'm just

[5:27:52] double clicking on here or otherwise just drag and drop.

[5:28:04] coming. This table name is called that one. So products. So what are the fields will be present in this table. So we have so product ID

[5:28:36] Right? So the name of the product and also the price of the product. so what type of mapping I need to provide between these two

[5:28:54] so I'm providing so many to many per suppose so many to many mapping I'm selecting customer and also products what happens so a separate table is what happens so a separate table is junction table is getting created or not

[5:29:09] as per the design so a separate junction table is getting created so This table name is called as what is that one? So the customer so I'm just changing the name to what is that one. So customer products.

[5:29:29] table right so this one contains customer ID and so this one contains customer ID and also products ID these two are foreign keys [snorts] you can see here. So this products ID references

[5:29:43] what is that once products table and customer ID references what is that once customer ID references what is that once the customer table customer ID. So these two are acting as what is that one? So the foreign keys.

[5:30:01] if you are designing a database for suppose if a customer is placing uh an order for a particular product the order details will be stored in a separate table and uh the mapping information. So if

[5:30:16] the customer is placed for an placed an order for a particular product that one is mapping information will be stored in what is that one separate table. So in the case of many to many relationship

[5:30:30] only especially in the case of many to many relationship only. So we have a created in the junction table we can able to see in the junction table we can able to see so the customer ID and also product ID.

[5:30:45] Is it clear all of you? All of you can able to understand. So what I'm speaking able to understand. So what I'm speaking up to now I just want to hear some questions from you. So please start asking questions.

[5:31:03] please tell me what is the example I provided here? So employee and passport provided here? So employee and passport is it correct? Say yes or no. All of you remember. So the employee and passport

[5:31:18] passport. So where could you please tell me where you can provide the foreign me where you can provide the foreign key? That is my question in employee table or passport table. What is your opinion?

[5:31:39] So this is uh the one right. So employee table and also passport table. So in employee table I have to provide the foreign key or in the passport table I foreign key or in the passport table I have to provide the foreign key

[5:31:52] >> It's up to us sir. >> Yes. So let's connect to the MySQL >> Yes. So let's connect to the MySQL workbench. Let's see how I am creating workbench. Let's see how I am creating uh how I am connecting these two tables.

[5:32:06] So let's connect with this workbench. I'm just providing for the password. So I'm just providing for the password. So something wrong password I entered.

[5:32:23] this database completely. What is the command? Could you please tell me? So to drop this database. So drop database. So database name. What is the database name?

[5:32:37] name? Employee DB. Again if I want to create the database what is the command. So create database what is the command? So create database employee DB.

[5:33:00] So employee DB inside the employee DB I want to create so employee table and want to create so employee table and also passport table. But my requirement is in employee table I'm going to keep

[5:33:12] I'm going to keep so the foreign key. So what is the table first I can create it is first I'm creating so the passport table. So creating so the passport table. So create table.

[5:33:28] passport ID whose type is what is that one? So integer So this one is a primary key right primary key and also passport number.

[5:33:46] some some number per suppose worker of 30

[5:33:58] So expiry date is whose type it is date data type. look into this query and execute it. After executing all of you, I will execute this query. This one works. Can you please tell me

[5:34:12] without using the database this query can execute? We can execute. No, first database we can create. is that one? So, we have to use the database, right? What is the command?

[5:34:30] So, use employee DB. This is super important without using the database we cannot execute the query. So now currently we are using so we can just refresh we can see currently we are

[5:34:43] using so the employee DB and after that so I'm creating so this passport table correct so just execute this query so the passport this is successfully

[5:34:55] created but might be you can observe so the data types here so the passport ID is primary key all of you know about so what is primary key. Primary key means one table

[5:35:09] contains one primary key. We cannot create more than one primary key. Right? So we cannot create so more than one primary key in a table. So that is super important thing we have to remember. So just a second. So I'm just connecting uh

[5:35:24] just a second. So I'm just connecting uh my pen.

[5:35:36] about so one table contains what is that one? So only one primary key we cannot create so more than one primary key. So primary key means what? So unique plus not null as discussed yesterday that means in the

[5:35:50] previous session. Is it possible to create so multiple primary keys? Anyone can you please tell me is it possible to create so multiple primary keys?

[5:36:03] No we can create so only one primary key. I think Arpita you are getting the error. So the reason is might be you are

[5:36:15] not using the database right. You are not using the database. You created database or not. So could you please verify it or otherwise you can share your screen. So that would be helpful to understand. So where exactly we are

[5:36:28] understand. So where exactly we are getting the error. You can unmute and speak with me. >> Uh sure sir, I'll just share it. >> So I've just copied the comment which you have pasted in the chat box. Right.

[5:36:41] I can able to. >> Sure. I'm doing that. I'm doing that >> Yeah. >> Okay. So I'm just following uh from yesterday. So what happened means like uh first create database command in the

[5:36:56] >> Yeah green color now this red means it is not created. is not created. >> So what is the misting mistaken is you create a database you are not using the database correct what is the command

[5:37:08] I've written in between so use command I've written right use employee DB. So could you please look into my screen so you can able to see >> creating a database that is [snorts] one option. So after that what we have

[5:37:24] to do? So we have to use we have to select the database right? What is the select the database right? What is the command I used here? Use employee DP. Correct? So if I'm selecting so use employee DB.

[5:37:39] So then only the database is getting selected and also you can see here one refresh icon correct you can able to identify arpita. So this refresh icon if you are refreshing so

[5:37:53] this employee DB is getting highlighted once you are executing after executing this command use employee DB now we can execute this query so create table so the passport that query so then automatically so the

[5:38:07] that query so then automatically so the passport table is getting created but might be all of you can see this is a new data type right so you're observing so what is the data type here and the data data

[5:38:21] expiry date so this one is going to be what what so this one is going to be what what type of data type date data type so we need to insert the date. Is it clear Arupita what is the mistake

[5:38:35] you made or otherwise you can share your screen quickly. second. >> Yeah.

[5:38:53] Okay. Uh shall I write this uh >> before before that command? So you can just write use employee DB. Use employee DB >> here. Right. >> Before that line that means line number

[5:39:06] >> Before that line that means line number two. You can write it. >> Line number two. Okay. Mm. >> Use employee >> space space use space employee DV. >> Don't get confused. Okay, no worries.

[5:39:22] >> So, put semicolon and you can just execute that query. >> So now that is using [clears throat] employee DB, right? How can you know whether it is using employee DB? So go to the refresh icon and click it.

[5:39:35] >> So which one is the refresh? This >> you're not watching my screen actually. So previously I shown right. So please >> This is called as my mouse pointer. You can observe

[5:39:49] my screen is visible. >> Uh not yet sir. You're not sharing. >> Okay. I already shared. Could you please check it out how it is visible? >> No I can see you sir but uh screen is not visible.

[5:40:04] >> Team could you please confirm my screen is visible? is visible? Yes sir, your screen is

[5:40:16] screen so could you please check it out my screen is visible or not? >> No. >> Yes sir. Yes sir. Okay I got it. >> So now you can just uh check this mouse pointer. So the refresh icon is visible

[5:40:31] right? >> Just click on this refresh icon. >> it is automatically refreshing and it is displaying. So employee DB is getting highlighted right? >> Yes. Yes. Right sir. Got it.

[5:40:43] >> So you just refresh it and employee DB is visible or not. So you can check it out. >> Yes it is visible sir. Employee DB and then under that tables view stored procedures and function.

[5:40:55] creating a table query it will execute and it will show you the table under the >> So shall I reenter that query again or >> You can just share your screen. You can share your screen. might be B.

[5:41:20] anywhere. Right? You can check after selecting the database only you have to execute the query. So you can just execute line number three now. execute line number three now. >> Okay.

[5:41:35] >> Mhm. >> Refresh again. and refresh the tables. table. >> Yes. Arrow mark. Arrow mark. >> Your table is created or not? >> Passport.

[5:41:49] >> So I'm requesting please go through the previous session. Yesterday itself I told >> Yes. Yes sir. I I went through but uh >> Yeah. Yeah. No problem. Next week you can get it record. No issue.

[5:42:02] >> Sure. Sure. Sure. Thank you. >> Yeah. Thank you. created and the next please like the messages once you are created the passport table. So now I'm creating so employee table.

[5:42:17] So now I'm creating so employee table. So create table employee integer. Of course this one is a primary key right

[5:42:32] and also the name. What is it type? a work cut of 30

[5:42:49] So worker of 100 and email. and at last. So we have to provide the foreign key. Can you please tell me

[5:43:03] anyone? So what is the foreign key column? Passport ID. Right? So passport ID is integer. So here this passport ID is acting as a foreign key that refers to what is that one the primary key. So that is your

[5:43:19] one the primary key. So that is your wish might be you're asking me so is it name so not required for example. So if I'm specifying so the pass ID so that I'm specifying so the pass ID so that means passport ID whose type is what is

[5:43:33] that one? So integer so this one references So the passport of so P I like this.

[5:43:51] one is looking somewhat strange for you. It's not strange very simple in employee table. So I need to keep one extra column. What is that extra column? So column. What is that extra column? So the passport ID.

[5:44:04] Though there are few questions might be we are expecting whether I have to provide the same column name primary key column name and foreign key column name column name and foreign key column name must be same not required

[5:44:17] if you want to keep the separate name so you can keep it so for example so in you can keep it so for example so in employee table I'm keeping so pass ID

[5:44:30] the P ID Right. So in employee table so this is the passport ID is a column. So that is always foreign key is always referencing. So primary key right. So

[5:44:46] passport of P I like this. So just execute this uh query you can successful. So the table is getting created and relationship is established.

[5:45:00] I'm just sharing this query in the chart. You also could you please try this query and try to understand executing query is not a problem. So try to understand so what that query is doing behind the

[5:45:13] scenes that is super important. Correct? So in employee table so we are having so the passport ID. So this one is acting as a foreign key. That foreign key is referencing so the

[5:45:27] primary key which is present in the passport table. So we are proving the relationship between the two tables. But additionally we have to provide unique also because the p foreign key

[5:45:40] value must be unique. Unique means what? It does not allow duplicate values. But right now so we can just execute this query message once you are done with the particular task

[5:45:59] foreign key. So do you have any questions? questions? Is it okay everyone? are not understand anything. Just I'm keeping an extra column

[5:46:16] where I am keeping an extra column. So in the employee table that is your wish or in passport table. Yesterday I discussed right in the case of one to

[5:46:28] one relationship right one instance of employee is associated with what is that one instance of passport instance of passport one employees have one passport only

[5:46:41] in such cases that is your wish either we can keep foreign key in employee table or either we can keep

[5:46:53] so foreign key in passport So that is up to you and also the column names also might be deferred. You cannot say so the column names in employee table should be matched with the passport table like that.

[5:47:08] Okay. But one important thing so we have to specify what is that one. So unique we need to provide in the case of one to one relationship. What is that one? unique constraint we have to provide.

[5:47:24] Could you please look into that one if you are not understanding? So please ask me, unmute and speak with me. I will explain it again.

[5:47:41] So now from the Yes. >> Uh sir, if uh uh um I want to like address address.

[5:47:56] right one to many at that situation we have to go for one to many so we have to create a separate address table because one person is having many table because one person is having many addresses correct I cannot say one

[5:48:11] person is having so one employee is having many addresses that is one to many relationship in the yesterday session I just ex uh just discussed always many side We have to keep the

[5:48:25] foreign key. Did you remember that one yesterday you joined you asked so many questions rel related to one to many and many to many right Swati? Practically we'll see >> how that one works.

[5:48:39] >> Okay. >> Yeah. query. So is it clear right? How it is working and all?

[5:48:51] working and all? So how the foreign key will be defined and the next one is what is that one? So one employee is having many addresses. So the same scenario we can take it for example. So as discussed so Swati is

[5:49:04] example. So as discussed so Swati is requesting right. So one employee is having many addresses. created. Right? So for example let's say so we have an employee table

[5:49:22] employee number, name, address and also email and also we have address table. So we cannot keep address here. Correct?

[5:49:35] Employee number, name and also email. Okay. >> Your screen is not showing. >> Oh, sorry. Sorry. Just give me a second. So I will share my screen. Thanks for uh letting me. My screen is

[5:49:50] Thanks for uh letting me. My screen is visible right right now. So we have employee number so name and also email

[5:50:03] >> Yes sir. >> Yes sir. >> Yeah. Okay. And also there are some properties might be you can keep if you want for suppose gender

[5:50:19] maintaining in a separate table because if you are speaking about the address one employee is having so many addresses it's a one to many or many to many can you please guess it what is the what

[5:50:33] type of relationship between the employee and address table one to many Right? One employee is having many address. So the same address belongs to another employee. No,

[5:50:48] the same address one address belongs to one employee only. That means from employee side one employee is having many addresses. From address side it is one to one. One to one means what? One address is associated with only one

[5:51:02] employee like that. So it is one to many purely. So every address is having associated with some ID. This is a primary key and also uh we can say address.

[5:51:18] So some other so landmark per suppose some other fields. Okay. So these are the fields. So now tell me where I can create. So

[5:51:31] keys? First of all in employee table in address table what are the primary keys in employee table employee number is called as so primary key you know already

[5:51:45] in any table but suppose if you are creating so one table contains only one primary key we have to choose only one primary key and in address table is also this ID can be treating as what is that one the primary key correct

[5:52:02] treated as what is that one the primary key. email and also gender and in the address table so we have ID and also address and

[5:52:17] also the landmark. So these are all what are the non- primary keys. So one employee is having many addresses in such cases always it is best practice

[5:52:30] to keep. So foreign key so this is acting as what is that the foreign key right employee id this one is acting as a foreign key always we can keep foreign key at many site

[5:52:46] so might be you're asking me question it's a best practice I'm saying uh if developer right so you can able to understand while working with the frameworks always we are keeping so the for foreign key in many to one to many

[5:53:01] relationship ship at many side. So this address contains many employees right? So might be this employee is get ID is getting be this employee is get ID is getting repeated again and again.

[5:53:14] So this is called as what is that one? So one to many relationship. So one to many relationship. So one to

[5:53:26] relationship means what? So one employee is having many addresses but the address belongs to only one employee only other side. Is it clear? So can I implement this

[5:53:39] Is it clear? So can I implement this one? So I already have employee table. So now what I have to do I need to create an address table separately. So this address table contains employee ID. So that means address table always

[5:53:53] refers to what is that one? So the employee number primary key. So let's observe here. So we have already tables.

[5:54:12] I'm just doing so describe employee perose. So describe RD S. What is the table name? So the employee table

[5:54:24] could you please check it? So what is the what we are getting? Of course we are getting the employee table which contains address also. Can you please tell me how to remove this address column?

[5:54:38] Very simple, right? So I just want to alter this table. So alter table alter this table. So alter table employee. So what is that one? So drop column what is that column I need to drop it

[5:54:54] address column correct because I don't want address I need to maintain it in as want address I need to maintain it in as separate table it is successful so now if I am describing employee

[5:55:07] describing employee I can see so there is no address column so I'm just sharing these queries in the chart all of you please do that step all of you please do that step and like this message

[5:55:25] one address column. So I I'm not interested in keeping address in employee table. So I want to maintain it separately. Correct? it separately. Correct? So for that one what I did here

[5:55:39] I just dropped this address column. Correct? I just dropped this address column. So drop column address then automatically. So the address column is getting dropped. So now this employee table is free. It

[5:55:55] does not contain any address column. The next step what I have to do is I need to create a separate address table which is mainly for keeping the which is mainly for keeping the addresses.

[5:56:18] associated with some ID right unique identification number. So ID is integer and this one is going to be what is that the primary key

[5:56:30] and also the actual address this is worker of 100 So just simply I'm keeping an extra column. So the landmark this is

[5:56:45] worker of 30 or worker of 20 but this address there is a relationship with the employee table right so one

[5:56:57] employee is having so many addresses so what is the many side address is the many side here we I have to keep what is that one so the foreign key that means that one so the foreign key that means I'm keeping so like simply EMP id so

[5:57:12] that is your wish I'm not saying so compulsory you must provide so empid remember this is important point while creating the foreign keys right

[5:57:29] foreign key names should not be exactly same as primary key names so we primary key name we created right so what is the primary key name here employee number E number is the primary key so I'm not expecting so the foreign

[5:57:46] key name also should be same as primary key name right so just I'm keeping so the empore ID so which whose type is what is that one so integer so this one is going to be frn key that

[5:58:00] means this one is referencing so employee of what is that one so employee number as discussed foreign keys are always referencing what is that one? the primary keys.

[5:58:16] the purpose of creating foreign key means foreign key is always referencing what primary keys here employee id

[5:58:28] is a foreign key that is referencing so the employee table employee number so relationship will be established between these two tables so this query is successfully executed so the relationship is getting

[5:58:44] established But what happens in address table we have an extra column which is nothing but employee ID. So while giving the employee ID so that should be matched with the existing employee number.

[5:58:59] Okay I'm just sharing this query. Uh please have a look into this query and please like this message once you are done with the task.

[5:59:13] So one one to many relationship correct. Can you please anyone could you yesterday we discussed it right one to many one employee is having multiple mobile numbers might be

[5:59:33] one mobile number table right? So first complete this one. I'll try to expand this one. So to understand in a better way. So I will write another example. How to manage one to many relationship. So might be one

[5:59:49] person one employee is having so multiple mobile numbers or one employee multiple mobile numbers or one employee is having so multiple what we can say um right there that is also a relationship. One employee is having multiple

[6:00:03] vehicles. So I am having so uh two bikes and also one car perose that means as an employee I am having

[6:00:15] associated with one car and also two bikes right so that is one to many relationship but that bike does not belongs to another employee that bike belongs to me only that car belongs to me only

[6:00:28] so let's implement so I'm just giving a task for you we have to create one vehicle stable right just think yourself we have to create one vehicle stable one employee

[6:00:43] can have so many vehicles that is the requirement so how can you fulfill that requirement so first do this one I can see only five or six members only doing the task and liking that messages

[6:00:57] remaining people I am requesting everyone should be on I am requesting everyone should be on the same page yeah Swati Please sir. >> Yesi please go ahead. Yeah >> sir in alter table sir you just write

[6:01:12] alter table employee drop column address but uh uh in my laptop in my uh SQL break uh workbench it's not working. It's a cross error code.

[6:01:24] >> Okay. So it's a safe update. Safe update. We are getting error is safe update related to safe update. Correct.

[6:01:36] >> Safe update. >> What is the error we are getting? So just execute it is >> you have an error in your SQL syntax. >> So share your screen. Share your screen. So I'll help you. So

[6:01:50] So I'll help you. So >> I am I'm uh uh like I'm logging in from my phone. Uh that's >> okay. So that is the biggest challenge, >> yes. >> Yeah. So I cannot see your screen means

[6:02:04] you can continue. So meanwhile in the break time you can connect to your laptop might be that would be better I can able to provide the solution for you. So please make sure that you can just log in from your laptop itself then

[6:02:18] it would be better to interact. should be here some uh network problem that's why uh in laptop lots of uh I can face that's why and

[6:02:30] >> okay I >> you can share your at least screenshot screenshot in like that chat maybe I will look into that screenshot >> okay >> okay yeah

[6:02:44] so here look at this I'm just creating so vehicles table same scen scenario right so one employee is having so many vehicles h so how can is having so many vehicles h so how can you write it so create table vehicles

[6:03:02] it should be what type so it is a primary key and also the type of or otherwise so vehicle type suppose that is motor bike

[6:03:16] or otherwise the two wheeler or four wheel four-wheeler like that. So type it wheel four-wheeler like that. So type it is var of 10 again

[6:03:30] and also we can specify some other things right. also the vehicle type and also uh what other things we can specify

[6:03:43] here. So the vehicles so might be the price of the vehicle. integer. But try to understand one employee right? So what is the relationship between the employee and

[6:03:58] vehicles? One to many relationship. So one employee is having so many vehicles. So in such cases so what I have to provide? I have to

[6:04:11] provide so employee ID. So this is an integer. So this one is referencing. So references employee table of what is references employee table of what is that one? So employee number

[6:04:25] that's all. So because employee table is already present we have a separate vehicles table uh the vehicles who that are belongs to the particular employee they are stored in this vehicles table.

[6:04:39] We need to provide the relationship between these two tables. In such cases always in one to many relationship I recommended always many side we can keep the foreign key right it's always best practice but suppose

[6:04:55] it's always best practice but suppose if I'm creeping so employee ID referencing what is that one so the employee of employee number so if I'm employee of employee number so if I'm executing this query

[6:05:12] so now we can just uh So refresh these tables. What are the tables are present? Look at so we have employee table and address table and also passport table and vehicle table.

[6:05:26] So for this employee table if I want to draw a diagram per suppose draw a diagram per suppose here look at this.

[6:05:42] Here we have so several options right? I just want to see the diagram per suppose if I'm specifying so create schema I think uh so create schema that option I think uh so create schema that option is different

[6:05:59] from this tables >> sir for wait please wait 5 minutes >> okay no problem >> I'm just I'm just doing this

[6:06:11] >> see The problem is try to understand actually if I'm stopping the session for 5 minutes other people will strike. [laughter] Okay. So >> uh sir I just have one doubt uh this

[6:06:24] virture numbers wherever you are mentioning 100 50 20 10 I mean uh I'm just uh have little clueless I mean how you uh putting these numbers is it random or is there a strategy behind this?

[6:06:38] So just randomly I'm putting for suppose if you are taking uh uh for example so gender for gender only one character is sufficient in such cases we are just putting care of one like that correct >> okay

[6:06:53] >> care of one so single character male or female >> but generally >> okay >> so if you're generally so data type for suppose if you're talking about what is

[6:07:05] data type actually so data type represents The type of data correct. So >> Yes. >> So what type of what are the different types of data we have? Name? Name is what type of data? String

[6:07:20] >> Okay. >> And also salary. Salary is what type? Salary might be this as an integer or floating floating point sometimes might be. So some point decimal point will be there.

[6:07:34] >> Okay. One is numeric also we can say and name is also like text we can say >> text we cannot keep it as salary so because salary is always it's a numeric

[6:07:46] value >> no no name name you said right name yeah string string means string is in the sense text type only >> correct okay I'm just providing the generic names I'm saying okay in the

[6:08:00] similar way so if you are working with dates it is date data type >> Got it sir. >> So, so like that. So, but what what I'm for simplicity I'm just keeping integer. Okay. We have

[6:08:14] >> so numbers can be as per my wish. I mean I can put any numbers as I want. >> Number means what is that one? So integer means that is having range >> So what what is the range is minus 3 to 767 to

[6:08:30] 3 to 768 like that. >> Okay. between that I have to keep the >> uh numbers so we have so like that so many data types we have why I am not focusing on specifically these data types we have to use and all means this

[6:08:46] training is majorly focusing on what is that one so like uh we need to understand the major concepts after that we have to go deep dive so for that one nowadays we have all the a tools are available right

[6:09:00] so for example if you're getting so some doubt related two what is that once I discussed so the data types suppose you can just ask a it will provide so for can just ask a it will provide so for example so I'm asking perplexity

[6:09:18] or uh charg so can you please tell me so explain data types in MySQL

[6:09:35] it's all right so MySQL to beginner for suppose.

[6:09:52] so details detailed documentation for suppose detailed documentation so you can able to get all the things right. So what are those numeric types? So there are so many things are there you can see. So tiny int. So it is

[6:10:08] accepting 0 to 255 or minus 128 to 127. Okay. So we are not going so somewhat deeper into this area because we have so deeper into this area because we have so three uh 3 weeks only right but this one

[6:10:22] provides overall the view. Nowadays if you are getting a job if you are getting into the IT industry you don't want to write the you don't want to become an expert in one technology like that correct

[6:10:35] so for example now we are learning so MySQL so tomorrow you cannot say we are not going to work on MySQL so maybe you are working on some other database or we are working on some other database or we are working on data analytics side

[6:10:48] this is just knowledge we are providing so these are the data types available so you can make use of this uh If you want for learning purpose, if you want some other tools, you can just explore if you want further. Okay,

[6:11:01] explore if you want further. Okay, that's what we are saying. Is it clear? >> Yes sir. Yes sir. Thank you. >> Yeah. Yeah. you please create this vehicles query. So we have so the multiple vehicles

[6:11:17] right already. So I think shared the query. see only five members are liking that query. So that means remaining people are not executing that query. I am feeling

[6:11:30] [snorts] So like all of you are executing that query. are completed but uh I can see okay great. So I think so I can see 10

[6:11:44] members I can able to understand. So if you are liking that message then only I can able to understand. So whether you completed to understand. So whether you completed the task or not so please do that one.

[6:11:57] So now another relationship what is that one? So one to many relationship right? So one to many relationship for example as for this up to now we have what are the tables are present please look at here so I'm just refreshing

[6:12:11] we have an employee table address table passport table and vehicle table but here we created so tables from this table I want to generate diagram for table I want to generate diagram for suppose database diagram

[6:12:25] suppose database diagram let's see here so go to tools database we have so one option called as what is that once reverse engineering All of you could please check it out whether this option is available for

[6:12:37] you. So on the database menu we have so what is the option reverse engineering. So we created tables I'm just doing reverse engineering here.

[6:12:57] and click next. So provide the password as what is that one. whatever the password we have. So that is root

[6:13:11] So which database we are going to draw this diagram for which database? So employee DB all my tables are present in employee DB only. So select that employee DB. So click next and click next

[6:13:27] and execute. See automatically it is preparing some model diagram for me. So there are four tables identified. So click finish.

[6:13:45] relationships. So that's what I want to check. So tables are created but automatically the relationships also needs to be established right. So I need to check that part. Why the relationships are not

[6:13:59] established. Anyone is getting relationships as well only tables you are getting or relationships also you're getting uh sir can you please show again because I missed out how to where to put the

[6:14:11] password and all I am in that page. >> Yes I will repeat it again no worries. Sure. >> So if I'm right clicking on so this uh database you selected please identify that employee DB correct.

[6:14:29] that employee DB correct. So employee DB is selected.

[6:14:43] and also the passport table and also vehicle table and also address table. I don't know why this one is taking so showing. Oh okay. Employee table, address table, passport table and vehicles table. So now we can just go to

[6:14:58] database and also this one is getting highlighted or not. So please verify it. So we are creating we have several databases we have but from the tables after creating the tables I want to generate what is

[6:15:13] the tables I want to generate what is that one so that uh ER diagram for this tables I'm just going so reverse engineering

[6:15:27] it is providing all the details don't touch that things click next here it is asking so password right so you can just provide the password root

[6:15:43] here I'm selecting what is that one so the employee DB so retrieve objects from the selected schema check the results click next

[6:16:01] so there are total four objects selected selected? Address and employee. So for example, I'm just selecting the tables and I'm just moving to the next table.

[6:16:15] That means uh next uh I am just moving all those things to the next uh whatever the uh tables that are required in the diagram diagram and click execute.

[6:16:32] But unfortunately what happen mean it is not creating so relationships but we mentioned the relationships as well as a part of the creation of the table right. So previously

[6:16:46] you know as a part of the creation of the table so we mention the relationships as well but it is not listing. So the relationships I will check that one again. So is there any option I am missing?

[6:17:04] What is the option we have to select? So final check reverse engineering. Are any is there any other options are available? Could you please check it in that one?

[6:17:24] So this one right. So I'm just closing this. getting closed it seems. Okay. Now it is fine.

[6:17:44] executing what is that command. So commit command. So commit is belongs to the transactions. Now we can just Now we can just go to the database.

[6:18:02] reverse engineering. So click on reverse engineering. click next and provide the password.

[6:18:17] and provide the password. What is the password? So root. So it is automatically connecting to the database and we are selecting what is what is the database we are currently using? So the employee DB

[6:18:31] and click next and click next. So we have so the table objects we are importing so the table objects we are importing all the table objects. Click execute.

[6:18:45] But uh the same options only I'm following. So I cannot see. So like we need to identify only the tables are getting imported but I cannot see what about so the other ones right so I

[6:18:58] cannot see other ones for suppose if you're [snorts] not getting created I will check and I will let you know why the relationships

[6:19:10] are not creating so in the case of reverse engineering and all so we need to look into that one also right so that is super important For example, if you are looking at the employee table, right? So, we have

[6:19:24] engineering. But, uh here we done what is that one? So, reverse engineering. So, first we created tables and from the tables we are just trying to so create the diagrams but relationships are not created. I

[6:19:39] will just check and I will let you know. Okay. So might be upcoming session I will just let you know what is the mistake happened because it is not showing any options or

[6:19:55] related to that one. So we'll look into that part. So majorly we have to work on what is that one. So creating tables right and

[6:20:10] the next step is what is that one? One to many relationship uh sorry many to many relationship. Can you please tell me employees and courses? What is the relationship?

[6:20:25] table. Right? What is the relationship between the employee and courses? >> Many to many sir >> many to many. Correct? table which is having so these properties

[6:20:42] so employee number, name, email and gender gender and also we have so the courses table in the courses table. So what are the columns we have? So the course ID so the

[6:20:56] course name okay and also like uh the duration. duration. So this one comes under many to many relationship. If you want to if

[6:21:10] you are establishing many to many relationship, right? So a separate junction table needs to be created. So that junction table name is employee what is that one? The course table.

[6:21:24] So this employee course table contains two foreign keys. So what are the two foreign keys? One is employee number and another one is valid that one the course ID. This employee number is

[6:21:37] course ID. This employee number is acting as valid that the foreign key key right so

[6:21:49] this employee number refers to the employee table and also the cash uh what is that one the course ID refers to what is that one the foreign key like that. So you can observe here. So what we are getting

[6:22:04] getting you can able to see. So here we have so how we can implement this part for suppose suppose I'm just keeping so create table.

[6:22:20] So first I'm keeping so ID. What is that one? So integer it's a primary key one? So integer it's a primary key right?

[6:22:32] also what is that the course name so worker of 100

[6:22:46] duration it's of integer type suppose table which is having these fields

[6:22:58] Right. So and the next what is that one? So we have to create so employee courses we have to create a separate junction table. So create table. table. So create table. So employee

[6:23:14] whose type is what is that one? So integer and this is a foreign key. So that means this one references

[6:23:26] So the employee number correct. So the employee courses table this is we can call it as junction table or join table. So this one contains employee ID.

[6:23:40] This one is referencing so the employee table of employee number and also we have so the course ID. So this one is also an integer. This one references.

[6:24:06] All right. So very simple. So this junction table contains two primary key two foreign keys. These foreign keys are mainly for referring to the both the tables. So how this works?

[6:24:18] So already yesterday I discussed so theoretically. So again I'm just repeating. So please observe try to understand. So we have employee table. understand. So we have employee table. So which contains employee number, name

[6:24:30] So which contains employee number, name and also email perose. which contains course ID, course name and duration.

[6:24:46] number is the primary key and in course table course ID is the primary key. So for suppose the course ID is 101. So course name is something like Java and

[6:24:58] duration is so some so 10 hours or something and employee number one so name is Suresh so some email id and employee number two so John

[6:25:15] he's also having some email id but suppose so like that we have multiple courses 102 so analytics

[6:25:27] And also this duration is valid that one. So 20 days something like that. But many employees can register for many courses. In such cases we have a separate junction table. So what is the table? So employee what is the table? So

[6:25:41] table? So employee what is the table? So courses table. Right. So in employee courses table. So we have to keep uh

[6:25:54] so the employee number so that is your wish you can provide any name. So this one I'm providing it as employee ID and also course ID. and also course ID. So these two are foreign keys actually.

[6:26:08] So this is a foreign key and also as well as this course ID is and also as well as this course ID is also a foreign key. what is that one? So the employee table employee number and this course ID is

[6:26:22] referring to this course ID. So the primary key for example if I'm saying 1 one and one two that means the employee number one is registered for how many courses? two courses

[6:26:36] in the similar way. So the same courses are registered by some employee number two also right? So for example, so here I'm specifying so 21 and 22. So that means many employees are registered for that one. So many courses

[6:26:52] if you're observing so not one or two here. So this one is this is 101 and this is going to be what is that one? So 102 course one? So 102 course here also this is 101.

[6:27:07] So employee number two also registered for so same courses right. So 102. So like that we have so n number of courses and n number of employees. But how we are providing the mapping? So we need some separate table. So that is

[6:27:22] many employees are working for many courses like that. table. That's what I am trying to tell you. I'm just sharing this code with You just execute whether this one is working or not. So in my case

[6:27:40] it is working fine. So look at this if I'm running this query. created. I'm just sharing this table with you.

[6:27:52] with you. So please run this query and check it out. So whether this one so working fine as expected or not. So employee ID integer foreign key of employee ID. Yes, we can do sura

[6:28:08] employee ID. Yes, we can do sura uh surash we can do it no problem. So that is constraint we are specifying. So constraint name right. constraint name right. So there are different syntaxes we have.

[6:28:21] So employee ID integer foreign key of employee ID. So if you are using the syntax right so better you can provide the constraint name. So what we have to do? So constraint space constraint name and after that so

[6:28:33] space constraint name and after that so foreign key of employee ID references I'm just writing the syntax in the chart for that. for that. So employee id integer right.

[6:28:45] So this is the foreign key column. So here we can use make use of what is that one. So better you can provide your own constraint name. So mp_fk

[6:29:00] constraint actual constraints of foreign key right? No, in my case I'm just saying if we use foreign key then we can uh have that

[6:29:12] relations in reverse engineering we need to use that word foreign key. >> Okay. Okay. Okay. So if we are using that model so then only it is providing. getting enabled we are talking about. Yes. We'll see that one also. Okay. So

[6:29:27] first let's complete this one and after that I will implement the same thing same tables with the help of what is that one the foreign key keyword so we can implement it I will show you that one as well

[6:29:46] key relationship if you're using foreign key >> yes I checked it's working for me oh >> yeah Yeah,

[6:30:00] please like that message. Once you are completed, I'll tell you another way of creating. So this foreign keys, complete it and like that message. If you are having any questions, you can

[6:30:13] you are having any questions, you can ask me right? So we have so a separate syntax

[6:30:25] as discussed previously also we are using constraint keyword right so with the help of the primary key so especially we are specifying the name reverse engineering is showing so the diagram so it is making so the foreign

[6:30:40] key entries so he is telling so maybe we'll just look into that one also we'll drop this uh tables and we'll recreate get it again with the help of that option. But both the ways like this is the basic

[6:30:56] approach right if you want to go for some advanced approach especially we are mentioning so the foreign key but I cannot say so like the same production means the next option [snorts] what I'm talking about so

[6:31:11] constraint keyword that one works in real time okay this is just for understanding so we are just using references keyword but in production if you want to use as an any key how to assign the key I will

[6:31:25] just discuss. [snorts] So we understand so what is foreign key So we understand so what is foreign key and all. So now we can observe so in production environment right. So production in the sense like in the live

[6:31:37] environment if you want to design a table. So there are uh several options we have to follow. But I'm not writing actual production code. I will show you how the production code looks like also.

[6:31:52] But what I'm doing is here right now. So I'm just deleting all these queries. I'm dropping this employee DB first. So drop database. So drop database. What is the database? Employee DB.

[6:32:11] is no longer uh the employee DB is not existed. So the next step what I can do here I have to create the employee DB. So how to create the employee DB? So create table. So same story right? for the

[6:32:24] table. So same story right? for the employee DB you done all these steps [snorts] what it is saying? So employee DB is it what it is saying? So employee DB is it is giving error.

[6:32:41] So drop database employee DB is selected. What is the issue here? So drop database right. Sorry. So I have created a table. So it's a database.

[6:33:00] So it's created. So how can I check? Just refresh it. But still we are not using this employee DB. How to use the employee DB? What is the command to use the employee DB? What is the command? So we have to use

[6:33:13] What is the command? So we have to use use employee DB. automatically this employee DB is getting selected. So now let's create some employee table first.

[6:33:27] So you know already how to create employee table. So create table employee table. So create table employee. number integer and also name worker of 30

[6:33:51] maximum size is 30 like that I'm keeping I'm just creating a so table. So same table. So employee table again. So employee number name but here I am not assigned. So the primary key right

[6:34:06] can you please tell me how to assign the primary key? So another approach we can make use of what is that keyword. So constraint.

[6:34:18] number so PK what is the constraint I'm assigning so primary key of E number

[6:34:31] instead of specifying so primary key here so we are specifying our own constraint name let's execute this code so the table is successfully created

[6:34:44] how can you say so whether successfully created or not so we can describe the created or not so we can describe the table. Okay. So you can describe the table. You can see

[6:35:00] so I'm just describing the table. So the primary key is assigned or not. primary key is assigned or not. So describe what is the table name. So describe what is the table name. So employee table.

[6:35:15] addresses. employee is having many addresses. So create table addresses or otherwise create table address which is having ID whose type is integer and here itself I'm assigning so primary key

[6:35:31] here itself I'm assigning so primary key that is up up to you and also the name sorry address. So this is worker of 100

[6:35:48] as well as this one contains some landmark. So this landmark is of type what is the type. So aircraft of

[6:36:04] and we need to specify so the foreign key here. So for this I am just creating so constraint. So first we can specify the column and name right.

[6:36:19] ID. So whose type is integer. So I'm just keeping an extra column called as empore ID and we can specify constraint. So empore

[6:36:34] ID FK. This one we can call it as what is that on the foreign key. Here I'm specifying what is that one the foreign key.

[6:36:51] So foreign key off what is that one the employee ID? here especially I'm using so the foreign keyword right so references

[6:37:04] uh which table so employee ID so employee table of employee ID that means employee table of employee ID that means employee table of employee number

[6:37:18] so we can able to specify so explicitly the foreign all, let me execute this one. I am just executing this query. It is

[6:37:30] successful. But what is the benefit we are getting here is as presa right what persona is saying. So if you are specifying explicitly so the foreign

[6:37:42] key then automatically it is populating in reverse engineering. So I'm just sharing this code with you. I already shared. So how to create a

[6:37:54] I already shared. So how to create a primary key. So previously Okay. So please like it once you have completed that part.

[6:38:07] Now we can do try reverse engineering perose. So go to database. So reverse engineer. If I'm trying to generate a diagram from these tables for suppose.

[6:38:24] relationships. You can observe. So employee DB I selected. So click next and click next and let's create

[6:38:36] and click finish. So now look at this uh this one whenever I'm using the foreign keyboard right. So automatically what happens this one is So the foreign key and primary key relationship as well. If you are keeping

[6:38:50] so a cursor on this relationship we can able to see uh this foreign key is pointing to what is that one the primary key but we cannot say always right so we cannot say this is the myasql admin if

[6:39:03] you're going with some other tool. So might be there is some little bit difference will be there. So this is another approach of what is that one. creating a foreign key. Correct?

[6:39:17] This is another approach of creating what is that one? The foreign key. So you can just make use of that approaches and we can create the foreign keys in the similar way. For example, let's say

[6:39:36] instance. So many employees can register for many courses. So let's create. So course table again. So create course create table again. So create course create table course

[6:39:52] and here I'm keeping what is that one the primary key and also course name whose type is worker of 100

[6:40:08] I'm just keeping so another field what is that one so duration So integer and uh

[6:40:22] okay so I'm just keeping so duration. So this is courses table but when coming So this is courses table but when coming to the relationships what is the relationship between the employee and courses

[6:40:36] employee and courses it is many to many relationship correct. What type of relationship it is? So many to many relationship. So we need to create a separate table for that one. What is the table? So create table

[6:40:52] What is the table? So create table employee courses. [snorts] So this one is accepting. So employee ID

[6:41:07] and also the course ID. Correct? So these two are foreign keys. specify constraint. So constraint name what is the So constraint name what is the constraint name? For example, emp ID_fk.

[6:41:23] So that is your wish. You can provide any name. Here I'm giving. So foreign any name. Here I'm giving. So foreign key of

[6:41:39] I'm just keeping. So employee of what is that one? So e number in the similar way. So another constraint what is that constraint.

[6:42:01] So references this one is referring to what is that one? the courses of so course of what is the table so ID sorry sorry here I'm making a mistake constraint

[6:42:16] here we have to specify what is the keyword so foreign key keyword so foreign key of

[6:42:35] id. So that one references what is that one the course table. So C column I'm just executing this query I'm getting an error.

[6:42:55] just check it out why we are getting so might be that uh name is already present. So you can specify what is that one fk1 So you can specify what is that one fk1 per suppose

[6:43:17] you have to specify what is that one so foreign key right

[6:43:30] again so all these queries what I didn't I did here. I'm just sharing this query in the chart.

[6:43:43] foreign keyword, right? So but now we are using what is that keyword? So the foreign key keyword. So now we can go to the chart and you can see

[6:43:55] so I posted that query. Now we can so do so reverse engineering for suppose with all these tables you just right click or otherwise we just go to go to

[6:44:07] click or otherwise we just go to go to the database and do reverse engineering. So all these tables are getting populated or not in the year diagram. So populated or not in the year diagram. So we can check it in the database diagram.

[6:44:21] we can check it in the database diagram. So click next and click finish. maybe I can able to see. Okay. So the tables are create getting created here

[6:44:36] successfully. What happens? So we have an employee table and also address table. The relationship is one to many and also we have an

[6:44:48] what is the relationship? Many to many. Many employees belongs to many courses. That's why so separate junction table is getting created right separate junction table is getting created. Now let's recall the syntaxes but

[6:45:03] suppose what we done up to now scenarios we are not used the keywords to create the tables

[6:45:17] right. So but here look at this here I specified explicitly so the constraint keyword to create the primary key and especially the foreign key also I used what is that one so constraint so constraint name

[6:45:32] what is that one so employee table employee number here especially I'm employee number here especially I'm using so the foreign key keyword because whenever we are using constraint keyword right we can specify so the name

[6:45:46] of the constraint What is the name of the constraint here? So the name of the constraint is foreign key. So this is the syntax we have to follow explicitly if you want to create a foreign key. Okay. So like this. So I'm

[6:46:01] just sharing. So this entire code snippet once. So please look into this code snippet and if you are having any questions you can start asking your questions. I think most of the people understand but still

[6:46:15] the difference between so the previous approach and this approach. In the previous approach we are not mentioned explicitly. So the foreign key keyword but in this approach

[6:46:30] explicitly we mention the foreign key keyword mostly in production right if you're writing so production related queries in real time always we have to make use of what is that keyword so this foreign key

[6:46:45] keyword to provide the relationship with other tables. Okay. So this is the standard syntax we have to follow. But simple syntax previously we executed just I'm not using foreign key. Just I'm providing

[6:46:59] references like that. Okay. difference between the previous approach and this approach? I'm just quickly and this approach? I'm just quickly giving a reference. But suppose here

[6:47:13] the approach is getting changed. For example, so previously we have

[6:47:26] you can able to see my screen is it visible right? is it visible right? Okay great.

[6:47:38] creating so employee table how we are creating so you can observe. So create creating so you can observe. So create table we are supplying. So employee number whose type is integer and also it is

[6:47:53] what is that one? the primary key, right? whose type is what is that one? The var of 30

[6:48:06] or var of 100 and also the email and also the email it is var of 100.

[6:48:24] creating so what is that one addresses table. So previous approach what we table. So previous approach what we done? So create table

[6:48:40] of ID whose type is integer and it's a primary key and also the actual address which is var of 100

[6:48:57] extra column? We are keeping it here. So the landmark.

[6:49:09] So that is your wish. You can provide any maximum size that is up to you. And also we are keeping so an extra foreign key column and right. So that is nothing but empore ID that is your wish. You can provide any name and whose type

[6:49:25] is integer. Here directly we are writing so references correct references what we are writing so emp means employee of employee number. This is the first

[6:49:41] approach we did but in the second approach what we are doing we are just adding an extra column first and after that so we are using what is the keyboard so constraint keyboard.

[6:49:55] So constraint what is the constraint name? So EMP ID. So for this column only name? So EMP ID. So for this column only we are assigning foreign key right? we are assigning foreign key right? So EMP ID FK.

[6:50:10] So we are keeping. So what is that one? So the foreign key. So foreign [snorts] key of what is the column in my table? So EMP id.

[6:50:22] column in my table? So EMP id. So I'm just writing. So references. approach. Production grade approach in the sense.

[6:50:38] So it is a preferable approach. If you are doing like this explicitly we are mentioning one extra column here. So the employee ID is an extra column. For that one explicitly I'm assigning what is that one? So constraint. What is the

[6:50:52] key right so foreign key for which column referencing so employee of employee number I think edk so is it clear what

[6:51:07] previous approach and this approach what are the topics I discussed in the create tables so that means we discussed about DDL what are the DDL

[6:51:22] what are the DDL operations. and also delete. So these are all operations comes under

[6:51:38] So these are all operations comes under DDL and also another operation is DML. manipulation language. Right? So we discussed about discussed about insert

[6:51:53] and also delete operations. So delete operations still might be uh update and delete I need to discuss it seems still we are not completed and also the DRL. So DRL stands for what data retrieval language. So today the

[6:52:09] major focus is about so DRL. So we need to work on what is that once the select queries. So if you are working as a data analyst right so most probably so we are getting a chance to work with

[6:52:22] what is that one. So mostly select queries only. So what is the purpose of select queries we need to understand uh how to write the select queries because if you are working in real time right so the data

[6:52:36] is already present we are not creating any data correct so already data is present tables are already existed so what we have to do so

[6:52:48] we need to retrieve the data also based upon your requirement if you are doing some data analytics or anything get some insights from the existing data. Right? If you want to get some

[6:53:02] insights from the existing data, so we need to write some select queries to retrieve the data and uh based upon that data we need to perform some analytics. So that's why this session is very very important

[6:53:16] session especially for data analytics people. So that's why please concentrate and also it's a simple only but uh if you are getting any question right so feel free to ask me

[6:53:31] so today we are talking about what is that one so select operation by using the existing schemas whatever the tables we have already in the last session so we discussed how to import the data so we created customers table and all those

[6:53:46] we created customers table and all those stuff right so we'll continue that setup Okay. So that is the agenda for the today's session. First we'll try to

[6:53:58] to retrieve the data from so the database table and after that so we are entering into some complex things like [clears throat] uh how to work with the [clears throat] uh how to work with the joins. Okay. So joins is what very very

[6:54:13] important concept. We'll discuss about that one today. So try to understand. So the select statement. So this one comes under what is that one? So data retrieval language.

[6:54:27] So by using select. So we can retrieve the data. Right? So So we can retrieve the data. Right? So we can retrieve the data. So from tables with the help of what is that one?

[6:54:40] Select queries. For example, if you're writing I want to So what is the query we are writing generally? So select star from we are writing so employee. What is the meaning of this one? So star

[6:54:56] What is the meaning of this one? So star means what? Star means all. That means we need to retrieve all the rows and columns. So from this employee table, columns. So from this employee table, right?

[6:55:13] It's everything is fine. So I think uh uh Priti Chakraati is it okay? facing any network issues please let me know.

[6:55:27] employee. So what happens it is just retrieving the data from so the particular employee table right. So the employee table contains rows and columns. So what it is doing it is retrieving

[6:55:42] both the rows and columns from the employee table. But we are not expecting like that. There are some situations right. So maybe you want to retrieve only particular columns

[6:55:54] because in the business if you are working in a real business but suppose the tables contains many columns. Okay. So the table contains not only three or four columns we can expect might be 50 columns also

[6:56:09] might be 50 columns also always for our uh the for our analysis and also what data is required only we have to select the data. So if you want to select only the particular data so we can retrieve the

[6:56:22] data. So select E number. So common name, comma, address from what is the table. So the employee table. So we are retrieving we are getting only employee number, name and address only. We are not getting any other fields.

[6:56:37] Okay. So we are getting only employee number, name and also address in the similar way. So if you want to retrieve name and address only, not employee number. So we can specify only the specific

[6:56:50] address from what is that one the employee table. So we are writing it like this. Select name come address from what is that one the employee table. So like that we have so some tables are already present here.

[6:57:06] All of you are having these tables. Could you please look into in the last week we imported these tables. Is it correct? >> Uh [snorts] sir I don't have it. Uh like uh because I'm using the Yeah.

[6:57:19] >> Yeah. So that import option is not available for you. Did you check that option? >> No, I I mean u those are not already here because they reset uh this my workbench right. So I have to

[6:57:32] >> Okay. Okay. Uh just do the import all of you. Please make sure that I will tell you how to import also again what we have to do. So just right click have to do. So just right click go to table data import wizard.

[6:57:46] So where we have to right click right click on tables go to table data import wizard and click on browse here and click on browse here and select your CSV files. For example,

[6:58:01] if you are creating customer table right you have to select so customer data set CSV. So click next. Here the table name it is taking as customer data sets by default. You can change this name according to

[6:58:16] your requirement. For example, I want to maintain this one as customer one because customer table is already present. So click next. So check out the data types. Your customer ID is having integer type.

[6:58:31] customer ID is having integer type. Customer name is text and also customer location is text and phone number is integer like that it is showing. So click next and click next. So but I am already

[6:58:44] having tables. So that's why I'm not creating. If anyone want if you are new to import the tables and all so please share your screen and I will help you. I will guide you what we have to do. I think Nikita you can share your

[6:58:59] I think Nikita you can share your screen. also could you please confirm all of you are having so imported the tables from the data sets. Could you please confirm in the chat?

[6:59:20] Nikita, you're not getting that option. You're getting right. So table data You're getting right. So table data import widget. Yeah.

[6:59:55] Please check it out whether the table is present or not. Okay, you can do one thing. Change the name. Change the name. One second. Customers. Table name is customers.

[7:00:08] So click next. Unhandled exception. Okay. First here you have to select the database. Correct. So you can use this database or otherwise create your own database.

[7:00:22] Create database. What is the database we have to create as per the document? Just open the document. We can see the database will be SQL basics. The database name is first

[7:00:37] we have to create a database called as SQL basics. Create database. Write the SQL basics. Create database. Write the query.

[7:00:49] Create slowly you can create no problem. So create database SQL basics space SQL basics don't give space okay [snorts] SQL basics that one is single name >> okay

[7:01:01] >> okay so put semicolon execute that query database what is the command for using the database

[7:01:13] the database use SQL basics Right. No, no, no. You just use space [snorts] No, no, no. You just use space [snorts] SQL basics.

[7:01:37] import. Refresh it first. You can get that SQL basics database. So now right click on tables. Go to data import wizard

[7:01:55] and select the location. So where that file is located? >> You can just uh select that one and click okay.

[7:02:18] So click next. Click next. finally how many records are inserting? 13 records.

[7:02:30] So now refresh it whether we can able to see that table or not. Just click on that arrow. You can see customers table. So just like that you can import other tables as well. Oh, sure sir. Thank you.

[7:02:45] Oh, sure sir. Thank you. >> Yeah. Yeah. same steps. So to import the table I'm just giving 2 minutes of time. All of you make sure that you can ready with the data set.

[7:03:06] Okay. So the relationship if you want to edit the relationship you can do it. No problem. So how to edit I will discuss okay if you want we'll uh first we'll complete the select

[7:03:19] introduce the query whatever the questions I will take it right at that time I will explain how to edit the relationships and all okay na you can stop screen sharing it works for but make sure that the table

[7:03:34] names you can change it okay >> yeah thank you >> yeah okay So I think now all of in the same okay So I think now all of in the same page.

[7:03:48] all customers. All of you please execute the same queries. So what I'm doing here is I'm just right clicking on customers table and I'm clicking on customers table and I'm opening

[7:04:03] tables or otherwise here. So add SQL. or otherwise here. So add SQL. So I'm just writing so use what is the database name what is the database I have to select first

[7:04:18] have to select first SQL basics right automatically so the SQL basics table is getting create uh database is getting getting create uh database is getting selected

[7:04:32] want to see the tables what is the query anyone could you please type it in the chat what is the query to display all the tables

[7:04:44] table site. See most of the people are not responding. So what are the tables present in this So what are the tables present in this SQL basics?

[7:04:59] So we have customers and also products and also sales table. that once the customer's table perose. So if I want to retrieve the data from

[7:05:12] customers table so then what we have to do? So select star from So okay so we executed select star from customers. It is displaying all

[7:05:27] customers data. Now the requirement is I want to retrieve only customer ID name and location only. Okay. So I want to retrieve only customer ID name and location. So how to

[7:05:43] retrieve it? So C ID and also C name and also C name and C underscore.

[7:05:56] So location from what is the table? So the customer's table, right? the customer uh table but what are the columns I'm going to retrieve? So

[7:06:08] customer ID, name and also location. So only these three columns I want to retrieve. See look at this I can able to retrieve only these three columns right? What are the three columns?

[7:06:22] So customer ID so name and also location. so name and also location. So now if I want to write some conditions for suppose let me execute select star from

[7:06:36] suppose let me execute select star from emp select star from customer. only the details who are belongs to Delhi per suppose I want to display all

[7:06:49] the customers. So who belongs to so what is that one? So who belongs to so what is that one? So Delhi h how can I write it? Anyone could you please tell me? >> Sir, quick question. Uh, why did you do

[7:07:02] C underscore? >> See this is the column name right? So which is present here. I'm not specifying on my own. Correct. >> The table contains what is the column name?

[7:07:16] name? C ID C name. So especially I'm retrieving only the specific comments C ID C name and also so C location like that. Is it clear?

[7:07:33] >> Okay. >> But I'm getting error for the same uh >> So in such cases what we have to do? First select star from first we are not using the database. Correct? First you are using the

[7:07:49] >> Mhm. Yep. >> You can share your screen. I think easily I can easily identify what is exactly the error. >> Uh let me just run this again and see if I Okay, I I'm fine now. Thank you sir.

[7:08:03] I Okay, I I'm fine now. Thank you sir. >> Yeah. the questions all of you. So we have to write the answers for this query. I want to display all the customers belongs to belongs to Delhi per suppose.

[7:08:20] How can I retrieve it? What is the query for retrieving the customers who are belongs to Delhi?

[7:08:34] Correct. Right. Very good. So select star from to specify where

[7:08:46] to specify where so c location equals to I think somebody's unmuted nikita nam could you please go on mute okay sorry nikita always calling nam and

[7:09:02] sorry nikita always calling nam and nikita confusing bit okay nikita confusing bit okay so c equals to

[7:09:14] so we have to check what exactly so the data is present in the table so I'm specifying where C location equals to what is that one Delhi so automatically we are getting retrieving the data from what is that one the

[7:09:29] customer's table so where customer name equals to what is so where customer name equals to what is that one Delhi like that is it clear so now another question I I want to display. So all the customers

[7:09:44] who are belongs to Delhi and Kerala. who are belongs to Delhi and Kerala both both the cities. How can we write it? Anyone can you please tell me I want to display all the customers who

[7:09:59] are belongs to Delhi and Kerala. >> Sir after Delhi we can again put uh >> Sir after Delhi we can again put uh within the bracket Kerala. So here I'm just writing. So I'm not putting any bracket right now. So let's see. So

[7:10:12] bracket right now. So let's see. So select star from customers where

[7:10:29] C location equals to Delhi and again so I am specifying Android. and again so I am specifying Android. So C location equals to Why this one is not a good approach? I

[7:10:42] will tell you. Okay. So just I'm writing. So C location equals to del and C location equals to what is that one? The careral like this.

[7:10:58] >> Sir why two times and you have put >> so this is a condition right? So if I'm writing two times what happens sorry where I written so two times C location equals to Delhi and C under C location equals to Kerala I have written

[7:11:12] >> so double >> no no no the and sign you have put two times that's what I'm asking >> so this one so the single% it is not and operation right so basically in your programming languages right if you want

[7:11:25] to represent and so we are using what is that one so double amp% operation operator single a% there there is a difference between single amp% and double amp% so this amp% we are talking about so

[7:11:38] >> yes >> yes >> if I'm putting so instead of that one and here and here right so let's see

[7:11:57] details for suppose Kerala what is the spelling So I can just check. So select star from customers whether the any anybody is there from Kerala. Yes there are few people are there from

[7:12:10] there are few people are there from Kerala. Okay. So what is the why we are not getting any data here is try to understand [snorts] here we specified and what is the difference between and and

[7:12:25] getting satisfied. So then only the condition is success right is there anyone who belongs to Delhi and Kerala it does not work but suppose instead of that one if I'm keeping R

[7:12:43] customers who are belongs to Delhi either Delhi or Kerala so if I'm writing like this we can able to see so the people who are from so Kerala and Delhi only that people only only that customers are only

[7:12:58] getting this plate right. So like this here Nisha and Oliver both are from Kerala and Na is from what is that one? So dealership why and is not working means because one customer belongs to only one

[7:13:15] location he is not belongs to two locations right both the conditions needs to be satisfied both the conditions requires to be satisfied

[7:13:28] satisfied if you are putting r means what either the first condition or second condition if the c location equals to del So the Delhi people are displayed and also Kerala people also getting

[7:13:41] also Kerala people also getting displayed like that. Is it clear? So you're asking me so previously. So why we are keeping so this and so generally in programming languages right? If you're keeping double emperson that is

[7:13:55] equivalent to and operation. Okay. So if you're keeping so double pipe symbol but in the programming languages but in the SQL so it is languages but in the SQL so it is directly under warr if it is R means

[7:14:08] what R like that. Now guys we have come to the end of this session on SQL would have enjoyed this certification course and also got a brief idea regarding SQL. Thank you guys for watching this video. Also guys do not

[7:14:22] forget to subscribe SimplyLearn for more such informative full courses.