SQL Fundamentals: Step-by-Step Guide & Transcript

[00:01] 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 data analysts,

[00:16] 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 to remain important

[00:28] 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 essential

[00:41] 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. Now

[00:54] before we begin our session, just a quick info guys. Simpan has got professional certificate program in data analytics, generative AI and adaptive systems in collaboration with IHFC and TIH of IIT Delhi. This program actually

[01:07] helps you master AI powered analytics 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

[01:21] capstone projects, plus access to tools like Tableau, PowerBI, AWS, Azure and Geni tools like chat GPT and clot. Learners will also receive a certificate from IHFC, TIH of IIT Delhi along with job assist plus for rumé support and

[01:36] mock interviews and career guidance. So guys, hurry up now and join the course. description box. Now before we move quiz to test your knowledge and the question is what does SQL stands for?

[01:49] And your options are simple query logic, structured query language, system quality language or software query list. Please mention your answers in the comment section below. Database is a collection [snorts] of database objects.

[02:02] So I'm writing something right. So this one is 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

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

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

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

[03:16] 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:33] 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

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

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

[04:17] we have vector databases are also available. So might be in some articles you can able to observe vector databases. able to observe vector databases. Uh we are using in AI especially in

[04:31] artificial intelligence to store the data in a different format but we are not focusing on these vector databases. We are focusing on only what is that one for relational databases.

[04:47] But what is the relational database? What is the relationship between this What is the relationship between this relational and NoSQL? NoSQL databases. These relational databases also we can call it as SQL databases, right? So

[05:02] 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:15] using SQL. So we are using NoSQL. So which is nothing but uh you're not using SQL query language instead of that. So these NoSQL databases are providing some APIs but you are all new but I just want to

[05:30] but you are all new but I just want to uh give you some information. So what is relational database and what is the at what type of situations we can use relational databases and at what type of situations

[05:43] databases and at what type of situations we can use NoSQL databases. So let's take a scenario simple scenario.

[05:58] So somebody's questioning so relational databases. oh you're answering so if you it is a relational database it is establishing a

[06:10] relationship between the structured tables but it's not something like that. tables but it's not something like that. So we can try to understand for example.

[06:22] 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:38] 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

[06:52] 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:08] relational databases Right? So we have employee number, name or otherwise I'm just taking so another database for your understanding. So take

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

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

[07:47] 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:01] post uh for suppose Instagram application can be developed by using application can be developed by using so uh relational databases? No. Why? What is the reason

[08:16] 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 schema

[08:35] example if you are taking so the content so this content must be only one type that might be string or otherwise as text we can say

[08:47] only in the Instagram we posting only the content only the text type of content always it's not like that right in Instagram so might be I am posting some text some others posting might be video and some others are posting audio

[09:04] always we cannot expect so the content should be text so in such cases so the relational database as are not suitable. So if you're asking me that what type of situations we can go for

[09:18] relational databases is so always whenever we are dealing with 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.

[09:34] So but what is database first of all? So database is mainly for storing the data database is mainly for storing the data of course for what purpose? of course for what purpose? Storing and manipulating data. So simple

[09:46] definition is storing and manipulating the data. Storing, manipulating and the data. Storing, manipulating and securing

[09:58] so the data. So for that one so we have so databases. the purpose of databases mean we are storing our application data in the database and we are manipulating. So like we are inserting, updating,

[10:14] 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:29] 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:45] 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're going to store employee records for for example employees.

[11:00] 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.

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

[11:31] manipulating database for example so here I'm specifying so one surely here I'm specifying so one surely he suppose

[11:44] so two subu from hydroat three John from San Francisco.

[12:04] 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 sense the structure.

[12:19] 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 the employee name whose type is text type

[12:35] type and also the employee address whose type is also what is that one so the text type so what I'm saying is whenever we are having structured data

[12:48] structured data in the sense all the records which we are inserting should follow the same schema same rules rules which we defined in the schema. So what rules we define? So we define some data type, we define some constraints.

[13:06] All these rules should be satisfied. So each record so then the type of databases we can call it as structured databases.

[13:22] structured databases are also relational. talking about so the structured manner structured behavior with respect to

[13:34] might be I will think I will talk about so what is a relational database also so first try to understand

[13:47] certain schema schema will be defined per yearly so for example another example I'm giving for example if you are taking so the department table suppose This one is having so department number

[14:01] This one is having so department number and department name This is also what is the data type text data type.

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

[14:25] you must provide so department name as text and location also you must provide it as text but all the applications just think about yourself all the applications will support this

[14:38] one for example let's say if you are having so HR system for suppose

[14:51] uh or otherwise HRM application roles? Uh majorly what are the functionalities we have? So they have to

[15:03] manage the employee details and they have to manage their skill details, right? have

[15:16] and they have to manage their personal details employee personal details. So that means uh if you're joining in a company might be you have to fill up all

[15:28] these things in 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:42] 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:00] is the normal pay and all those the table compensation details. compensation details. But uh try to think about

[16:13] if you're having all these tables. So but what about the data whether the data which is present in the tables should be related right? example. So these all these tables contains data

[16:38] 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

[16:51] example of simply learn for example I'm having so my personal details and my skill details details and what type of courses I am delivering and also I am having some other details also like my compensation details and also some other

[17:06] compensation details and also some other details which are relevant to me right so all the details will be show stored in a in a single table no

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

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

[17:56] not suitable. 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

[18:10] simple example Instagram or Facebook whatever the post you are posting whether the post is following so the certain structure certain schema no

[18:23] if you're taking post for example 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

[18:37] user is might might be posting image and one another user is might might be posting video. So we cannot say all the data should be satisfying that that certain schema

[18:51] in such cases. So we can go for what is the databases? No SQL databases. No SQL means the every record present in

[19:04] No SQL means the every record present in the database is having their own schema. the same schema like structured databases in NoSQL databases. So every record right so this is super

[19:18] So every record right so this is super important every record.

[19:31] so won't schema. So in the sense for example if you're having post but suppose the post will be written as so the JSON object. So which contains the post ID. So 01 and also

[19:48] 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:00] and second post if you are talking about so the post ID two. post we cannot say. So the first post post

[20:13] 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:28] 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:46] record, this is another record. So can you please 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 own schema. So

[21:02] you have to think as a architect for example while developing the application what type of database is suitable in the particular context if each record is having their own schema

[21:17] so not common schema in such cases we can go with what is that one so noSQL databases correct so no SQL databases but so if you're observing so if you are

[21:31] asking me as an application developer per suppose. and NoSQL databases. But the most of the consumption will be

[21:45] what is that one? SQL databases only. structured data almost I'm getting what type of data?

[22:00] working in an enterprise applications almost the data will be what is that almost the data will be what is that one? of the structured data. asking me so there are some situations yes [snorts] we have unstructured data

[22:16] yes [snorts] we have unstructured data also what in in image just need to record that uh the video or image like that

[22:30] type of data. Yes. Yes. Yeah. So based upon your 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

[22:46] way. So for example so what is database? So simply I can say the definition of database will be in your books might be if you're a fresher might be you're if you're a fresher might be you're reading through books books right

[23:00] reading through books books right in the books in academic books. So there 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:24] Database is a collection of what is that one? So database database? Means database a database is a database objects. What are the databases?

[23:39] 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.

[23:51] Tables views. So synonyms. What is that? Synonyms indexes Indexes are mainly for performing

[24:06] uh improving the performance of a search query. Indexes query. Indexes so clusters

[24:27] 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:42] 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

[24:54] all the objects which are present in the 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 First priority

[25:09] 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:25] we are discussing so we are discussing about functions but we are not discussing about stored procedures and all those are all some advanced concepts that will be covered in advanced SQL

[25:38] but don't think about all these things. So if you're taking any database for So if you're taking any database for suppose that may be varacle or MySQL or any database these are all objects will be present in the database that might be

[25:52] tables or views or synonyms or indexes or anything. So what are the companies that are So what are the companies that are providing so database solutions?

[26:04] So Veracle right? So is the one of the enterprise level database. Veracle corporation is providing Oracle Veracle corporation is providing Oracle database and also MySQL is open source.

[26:19] So open source license that means anybody can use without license also and DB2 so which is from IBM actually. So this is also one of the relational database.

[26:34] So database relational database is a concept actually right. So based upon the concept so the multiple vendors they provided their own Veracle is providing their own implementation. MySQL is providing their

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

[27:02] but in this session in this training so we are using what is that one the 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:15] applications right intermediate level applications we are using MySQL so all will come all these database vendors are providing this database softwares but try to understand if you are taking

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

[27:42] on what is that how to create 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 multiple vendors.

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

[28:11] 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 royp

[28:26] two terminologies one is royp and royap. Anyone could you please guess it? Anyone have an idea about sop

[28:55] 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:10] 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:23] 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:37] 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're booking a systems means might be you're booking a flight ticket?

[29:58] right? Booking a flight means it involves several operations. those things. It is a data entry system

[30:10] only right indirectly. If you are booking a ticket through online, you're making your data entry and you're performing uh so ticket booking. So there are several ways we can

[30:23] consider for example so ticket booking and also hotel booking. Uh we can see uh like uh new AAR card creation or new PAN card creation. So these are all data entry systems.

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

[30:56] 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:10] OLTP applications only but if you're asking me what is the but if you're asking me what is the difference between sop and AP systems while AAP means what is that one so online analytical processing

[31:33] important so you should know about whether the system is OLAP system or OLTP system. So if you're asking me so the oil it's an analytical process for suppose you're having huge amount of data for

[31:48] for example so like Amazon customers data if you're asking me Amazon customers data it's a global business right in a global business there are so many customers are there it's in pabytes or zabytes

[32:07] so from that customer data you want to get some ins sites per suppose. So the people who are actively purchasing electronics, the people who are actively electronics, the people who are actively purchasing some other goods per suppose.

[32:23] analytical operations and you are generating some reports. So this is super important. If you are generating some report based If you are generating some report based upon

[32:44] 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:03] and reporting purpose all right So that type of systems we can call it as OAP systems. Now we need to

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

[33:32] that one systems. If you're asking me where we can use for 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:47] business per suppose we need to provide some insights. How we are generating the report? Some visualization report might be you are representing the data in a visualized form

[34:01] 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:14] So such type of systems we can call it as OAP systems. But almost if you are observing in government offices almost all the systems are what is the OLTP systems online transaction processing systems that means through

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

[34:43] is OLTP and OAP? Do you have any questions? [snorts] previously I'm not addressed any question.

[34:59] when it comes to database objects, we need to create a database from scratch. need to create a database from scratch. [snorts] Yes.

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

[35:31] OLTP and JP? Okay. You're working in a company or suppose you're working as a customer support you're working as a customer support engineer for suppose

[35:50] 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:08] WTP correct? Yes or no? So raising a complaint comes under what type of system? WTP system. So why WTP system. So why you are entering? So complaint details?

[36:22] So what is the cause and everything? So 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 customer support engineer.

[36:38] 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 to do? I need to fill out some form right and I'm raising

[36:52] a complaint. So, that type of systems are systems. If you are raising a complaint so automatically what happens? So that complaint will be received by IT team right? So that complaint will be received by the IT team. So again IT

[37:09] team is resolving that problem. So IT team is trying to resolve that problem. So they are updating the status right. So they're updating the status. So updating status is also comes under

[37:22] So updating status is also comes under what is that one? So only because already have that data so they are updating the data. Some other examples might be [snorts] easiest examples. You are booking a

[37:36] easiest examples. You are booking a flight ticket. So booking a flight ticket comes under what is that one? So OLTP correct?

[37:49] 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 the particular day. So what we have to

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

[38:19] insertion or updation or deletion all these comes under what is that one operations can you please tell me so some examples related to related to OLAP some examples related to related to OLAP operations online analytical processing

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

[38:49] and also SQL and they are using some Python all these tools they are generating. So, this OLTP oil processing perose

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

[39:18] So the outcome will be so they are analyzing the data and uh finally they they are generating some report

[39:30] 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:45] 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 right if that one so decision making right if leadership want to make any decisions

[39:59] they will use this report. So if you want to analyzing the existing data so then we can go forward with that one. So AAP systems there are two parts for you right.

[40:15] So one is you have to work as work as OLTP developer per suppose. So WLTP developer per suppose So WLTP developer per suppose system developer

[40:37] So mostly app developers which is nothing but web or mobile nothing but web or mobile or any application developers. So all applications comes under what is that one? So the WTP systems only

[40:51] but if you're going for online 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

[41:05] they have to get the insights from the data. So most of the professionals will be from so data analytics. So for that one also uh we need SQL So for that one also uh we need SQL right we need SQL

[41:20] we need Python. So some technologies we need Python and we need some tools for 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 developer. So

[41:35] we need to learn Java or Python. Okay we need so what we can say we need to learn full stack development we can say full stack development.

[41:50] This is one track but most of the people nowadays trending is what is that one? So this is data analytics right. I can see even in simply learn or

[42:03] outside also the people who are more concentrating on 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

[42:15] domains right so marketing HR

[42:29] so all these not only finance banking of course finances comes under banking only course finances comes under banking only and also insurance so there are different domains the people are working so logistics

[42:45] right so management is also one of the important domain so why these people are learning so the SQL the reason is they have to transform their careers into what is that so the data analytics

[43:00] yes very good so the medical good question so medical and all those things so what is the Difference between Python and R means there are lot of differences is there but I cannot discuss sak here

[43:16] 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

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

[43:50] 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 want to understand

[44:04] 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 once working on data only I cannot see

[44:18] once 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:32] 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:44] years uh or 7 years 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 easily understand the data like that

[44:59] 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:15] the first most thing is your domain knowledge so plays important role in your job and second one is your technical knowledge right so what that once your technical

[45:30] right so what that once your technical knowledge and this technical knowledge after that so usage of AI tools

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

[45:58] so we can use OLTP for generating reports and making decisions. Yes, we'll reports and making decisions. Yes, we'll use OLAP. Correct? Yes. Correct. But are observing, it's a combination, right? If you're

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

[46:27] 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:42] 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

[46:56] 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:11] getting onboarded means that type of system is OLTP system correct the new employees getting into the company means they have to create so new user so new user for that particular

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

[47:40] analytical processing. But try to understand the differences. Analytical processing is more about what is that once doing some analytics and getting insights from the data for decision making

[47:54] improving sales and improving the customer experience or improving some other details. These are all decisions which is which are required. So at management level

[48:08] is it clear all of you what I'm speaking? So that's why most of the people like I can see from your personas mostly you are interesting in what is that one analytics side

[48:22] 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 but uh nowadays it is becoming

[48:38] years but uh nowadays 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 learning and AI and

[48:54] this machine 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 why I'm taking so might be somebody of

[49:08] some of you might be asking me question so why you are speaking all this so why you are speaking all this nonsense So you need to understand your path right? So but what are the difficulties in that

[49:20] part 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 one so what are the different paths you can choose it like that

[49:35] paths you can choose it like that 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 path you have to select. I'm

[49:50] suggesting so mostly if you're having domain knowledge you're very good with your domain what you are working better go with analytics if you're having so already so that I'm a good in Java I am good in so Python or

[50:06] any programming language so you 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 domain rights I'm in banking for suppose

[50:21] my domain 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 understand something what are the

[50:36] operations of logistics and how this one works and all. Okay like that. Yeah. So now when coming to the databases the first step what we have to do is

[50:49] 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:01] 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 also in your personal machines as well.

[51:15] So I will guide you how to install. So install MySQL.

[51:32] or suppose if you are working from company don't install it. That means your company laptop don't install it. install it. So if you are using company laptop

[51:55] install so because there are some IT 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 maybe you're asking me why we are using

[52:09] only MySQL 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

[52:24] you so just wait for some time yeah 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.

[52:39] I will repeat it three times. But please bear with the instructions. Don't write on your company laptops because the company laptops does not allow these installations. So I'm just installing. So the MySQL

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

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

[53:29] right so you can see 8.0.45 45 or any version is sufficient no worries

[53:43] and uh the operating system most of the people are using Windows only so you can people are using Windows only so you can go with the Windows operating system. So if you're coming down we can able to see different sizes of this uh one right

[53:58] so otherwise we can see click [clears throat] download page [clears throat] download page I think uh uh nm uh for the Mac we have to check okay so because we have to select

[54:12] select DMG file right so in the Mac so we have so the Mac O is also available so we can just download that particular So installed what you installed actually

[54:25] so you followed some options and installed some setup right so but what happened behind the scenes what are the installations we made so those are all super important things we have to understand

[54:38] 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 carefully this is super important for any database or anything

[54:53] uh so so that is the problem correct you installed workbench workbench is just a client okay just wait okay I will come back to you seems I need to interact with you okay I think OB yeah I

[55:12] can just wait for some time after the discussion I will come back to you I know so different 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:36] there are two things first what is this MySQL actually MySQL or veracular or whatever it may be this one we can call it as DB server

[55:48] what is that DB server so in a company if you are working in a company. So this installation cannot be done in your personal machine. Your database

[56:02] server is located in some remote machine, some virtual server. For example, uh so my company, for example, I am uh so my company, for example, I am working in some bank. My company main

[56:15] working in some bank. My company main branch is located in Hong Kong branch is located in Hong Kong or otherwise United States per.

[56:27] So my business is more related to what is that one? So United States. is that one? So United States. Yes. I need to maintain so my database server

[56:42] right. So for that United States I created one uh virtual machine that means I need to create one I need to maintain one my DB server.

[56:55] about so DB server means what? So that is nothing but

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

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

[57:39] 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 correct? You are an SQL developer per

[57:54] 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:11] communicate with the server. So if you are working with especially So if you are working with especially MySQL so MySQL is providing one tool called as what is that one? So MySQL workbench

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

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

[58:58] other location or the particular business is located in some particular region according to that region. So they are locations. But as a developer we need to perform

[59:11] 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 server with the help of some tool some graphical user interface tool. So GUI

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

[59:38] 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.

[59:50] tool? So that is nothing but MySQL workbench. So whenever we are doing installation right for training purpose what we are getting. So for training we are doing so full

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

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

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

[1:00:51] 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:06] 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:23] training this is not essential and also it is providing so MySQL shell

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

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

[1:02:03] 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:17] the command line so if you want to write the query okay so you can use what is that one so the command line command line so if you want to execute the query so we can use what is that one

[1:02:32] so 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 MySQL. So that is database server is

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

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

[1:03:17] establishing a communication establishing a channel between client and server applications. But to establish a channel between the two applications, it requires some port number.

[1:03:31] What is that? It requires what is that one? So the port number. so that may be MySQL or VRA or otherwise any application if you're running

[1:03:48] that application is running on some port number. So during the installation so we can see this port number. What is the port number 306 this is the port number. So by defaultly

[1:04:02] this is the port number. So by defaultly the MySQL is getting uh attaining the port number explicitly. So MySQL is getting this port number by

[1:04:14] default. You don't want to worry about anything. Just click next next. So automatically your installation is getting finished. But the thing is think about it's not MySQL it might be

[1:04:29] my HR application or it might be some other application. other application. Every application if you are running the application is associated with what is that some port number.

[1:04:41] that some port number. Okay. So what does MySQL server contain? what does MySQL server contain? Yes, all client and server all are

[1:04:55] 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 software and client software both. Is it clear sed?

[1:05:10] Okay. So once you done the installation, right? So your server is running or not, how can you check it out? The next question is what is that one? So the next question,

[1:05:49] 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 services.

[1:06:03] 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:22] nonsense so there are so many services are listed here are listed here the reason is in my machine by default Ely there are some windows services are running along with that one. So there

[1:06:35] running along with that one. So there are some applications are all installed. are some applications are all installed. So adab acrobat this one is for PDF opening PDF files right. So that service is running. Yes.

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

[1:07:04] and we can see 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 it is taking MySQL 80 by default because

[1:07:22] it is taking MySQL 80 by default because we installed so MySQL 8.0 version. By defaultly 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.

[1:07:38] The same service name is getting reflected here. So try to understand and also what is the status? Of course it is running. Running means by default

[1:07:50] whenever I machine I'm starting my machine right by defaultly the server is 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

[1:08:06] installed MySQL in your machines and like this message

[1:08:22] is running. 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:35] Correct. >> Okay. >> So we can see all the services and in the services all the services and in the services you just click on any service type M. M

[1:08:50] means what? So I need to display only the services which are related to MySQL. That means M which is starting with M. So we can see so the MySQL 80 service which is running already and just explore try to understand the how the

[1:09:05] Windows machine [snorts] is working. What are the other applications are What are the other applications are running and all those stuff. So please like that message you can able to identify

[1:09:17] the MySQL 80 service is running or not. So I'm suggesting so this MySQL service So I'm suggesting so this MySQL service is running or not. So it is running. So the next step is what is that one? So

[1:09:31] check it. So the client tool what is the client tool? So MySQL workbench. client tools are available right? What are the client tools are available?

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

[1:10:04] I'm 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:18] Please do that step and like that message. So still we are not connected. So we have to connect to the server.

[1:10:35] 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 running?

[1:10:49] 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:03] location server is running or in my machine itself it is ser running my machine itself not remote s correct right s correct right if you are accessing so simply learn lab

[1:11:19] so good question what is the difference between remote and local if you are accessing simply learn lab right might be the simply learn server is located in be the simply learn server is located in USA the labs server

[1:11:34] 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:50] 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:04] root. So click okay. So that's all. So you are successfully connected. Yes, almost if server is running means what? So you are successfully connected

[1:12:18] 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:32] 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:45] 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. Now you have only three schemas will be there.

[1:13:00] might be HRC and secil. So in companies do we use same MySQL 80 for coding good question right?

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

[1:13:28] different so the tool there are several tools are available so just I'm naming tools are available so just I'm naming here some of the tools so might be you can expect if you are working in SQL environment so SQL

[1:13:42] working in SQL environment so SQL developer is one of the popular tool but most of the people are using so the SQL developer

[1:13:56] SQL developer and MySQL workbench what is that one so MySQL workbench but all the tools are having similar options

[1:14:08] 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 you are using Microsoft

[1:14:22] Word, how you 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:38] 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 if you know if you

[1:14:54] 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 what I'm trying to tell you is it clear

[1:15:08] all of you Yeah, see look at this once you are getting so connected to this database server right.

[1:15:20] So first step is what is that one? So we have to create databases

[1:15:35] So I'm deleting all these databases which are already present. These are all which are already present. These are all created by me previously.

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

[1:16:10] these are all the default databases which we are getting correct. we are getting. Yes. So should preach ready. So you're

[1:16:24] asking 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 [clears throat] complex process. Okay. So that's why I'm requesting.

[1:16:39] 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 to [snorts] reset the password, it will take more time. Actually, you need to

[1:16:53] troubleshoot 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:13] secila and also sys if you want to create so another database for example create so another database for example so managing employees details. So how can you create the databases? You just right click here.

[1:17:26] 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:43] So if you are clicking so SQL tab. So you're getting so this uh SQL file right 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:01] 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:16] 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? So this option execute the select portion of the script

[1:18:31] 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:45] 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. Yeah, observe my mouse cursor in the bottom. We can able to see

[1:19:00] create database HR database it is created or not. It is in green color. So we can refresh. So observe my mouse cursor mouse pointer

[1:19:13] if I'm clicking refresh I can able to see one database right what is the database HR database so I'm just sharing this query in the chart

[1:19:27] so HR is by default present I am 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

[1:19:41] create the database or not. So if you want to open this SQL file, So if you want to open this SQL file, right? So how to open,

[1:19:59] what is that one. So this new file. So automatically this one will this will open the editor actually. So this will open the editor. Is it So this will open the editor. Is it clear?

[1:20:15] suppose employee database or HR database. All of you please create two database. All of you please create two databases and let me know.

[1:20:27] Okay. So let's start with the discussion. server. So how can you know so whether the MySQL service is running or not? So just go to the services. I'm just checking. So my MySQL service

[1:20:42] is running or not. 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

[1:20:57] looking for the MySQL service. Just type M. this service right. Could you please check check it in your machine also

[1:21:09] 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. So if it is running means what the server is ready

[1:21:24] installing. So yesterday we installed the server and machine. So the server is started automatically. So we don't want to worry about restarting the server and everything.

[1:21:40] Okay. So now the server is running. So what we have to do? So we have to connect to that server. tool, right? What is the client tool we are using?

[1:21:53] are using? So the MySQL workbench. So this is a client tool. There are several tools are available. I'm not talking about. So only this one is the client tool right. So there are MySQL

[1:22:07] 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 just establish a connection with the server. So from this client tool

[1:22:21] if I'm speaking what is the client tool actually Yesterday already I discussed right? What is the client tool? What is this MySQL workbench? What is the purpose of MySQL workbench?

[1:22:37] I am expecting responses from you. Please type it in the chat. What is the purpose of MySQL workbench? Very good, Nikita. To communicate with server, right? We have server running

[1:22:53] in my machine itself. Yes, server is capable of processing. right? Whatever the queries we are giving, okay, so all the queries will be taken care by the server,

[1:23:08] server. But what I have to do, I have to interact. I have to communicate with the server. Yes, it's a engine which is capable of processing your SQL queries and all. Right?

[1:23:24] your SQL queries and all. Right? So if I'm giving any query, what happens? the query will be executed by the server itself. This is just a client tool. It's an user interface. So from this

[1:23:38] user interface, we can able to write the queries and send the queries. But I want to connect to that server. Can you please tell me what are the things required for connecting to the server? I think we

[1:23:52] heard about port number correct and which port number. So the MySQL server is running. Anyone could you please guess it Anyone could you please guess it 306. Correct?

[1:24:06] 306. Yeah. So just establish a connection with the server. So you just double click on this MySQL connections which are already MySQL connections which are already present.

[1:24:21] I am going fast might be. So I'm just decreasing my pace. I understand. If I'm just double clicking on this MySQL connections automatically it is establishing a connection with what is the service

[1:24:35] connection with what is the service MySQL at the rate of localhost 306. MySQL at the rate of localhost 306. You can look at this service here.

[1:24:47] with the MySQL service which is running on the MySQL service which is running on what is the port number? So 306

[1:25:07] 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:24] 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:39] Drop schema means what? So, it is deleting whatever the databases whatever the database you selected it is dropping the database.

[1:25:53] for suppose. [snorts] 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?

[1:26:06] Most of the people don't know we are new to this SQL. So SQL stands for what is that one? So structured query language right? right? I'm just maximizing this one.

[1:26:36] 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:51] can be divided into what is that one? So some other languages. So I think Maria your advanced learner might be uh just bear with me because

[1:27:04] might be uh just bear with me because most of the learners will be you know uh means if I'm not recapping whatever the contents which are already present yesterday discussed yesterday most of

[1:27:18] 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:32] cohort right so SQL stands for what is that one? So structured query language what is the structured query language can you please structured query language can you please tell me it might be you already in your

[1:27:47] academics you written some programs but suppose you are having a file text file if you want to manipulate the file 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

[1:28:03] 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 of the people are go gone through the C language in your academics long back. So please remember at least

[1:28:19] 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 with a single line of query we can able to perform what is that one

[1:28:33] so complex operations that's why this one we can call it as fifth generation language very very important thing please

[1:28:46] we don't want to write 20 lines of code everything will be getting abstracted abstracted in the sense You just write the query. For example, I want to retrieve the data from employee

[1:28:58] table. So you just write so select star from MP. Right? With the help of the simple query, we can able to fetch the all the

[1:29:10] 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:25] 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:42] could see zoom a little bit. Yeah. So definitely I will do that one. 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:29:55] not that content is not visible or something. If I want to write a simple SQL query right here I'm writing simple SQL query. will be executed. The script is going to

[1:30:08] 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:24] 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 fetching the data from the server or inserting the data, deleting the data

[1:30:38] 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 database

[1:30:55] management system vendors. Can you please type it in the chat? Who are the database system vendors? That means who what are the companies that are providing database as a solution? Veracle.

[1:31:09] Okay. So the MySQL is open source DB2. DV2 is provided by IBM International Business Machines. So these companies already developed some scripts. We don't

[1:31:21] want to worry about what is that one. So writing some complex program to perform writing some complex program to perform these operations of course. [snorts]

[1:31:35] But uh if you want to work with SQL based upon the query we are writing might be all the situations I am not going to perform uh retrieval only right so based upon the operation I am performing

[1:31:49] these queries can be divided into different languages one is called as what is that one DDL so DDL stands for so DDL stands for so data definition language

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

[1:32:27] we can call it as DQL also. So data retrieval language. languages also we have might be whenever we are working as a database

[1:32:43] 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:32:55] data manipulation language and also data retrieval language. Okay let's observe try to understand so what is exactly so data retrieval language if you are speaking about so data

[1:33:10] 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 between so data and data definition

[1:33:28] what is that so data and data definition. You need to get some clarity about this one otherwise you don't know what definition and what operations we are performing on

[1:33:43] and what operations we are performing on so data. For example, let's assume that so we have an employee table. This employee table contains some fields, right? So employee number and also name

[1:33:57] and also name and also address. So we have data employee number one and name is subu and address so some

[1:34:09] chai for suppose and employee number is to James. So USA can you please tell me what is from this table? So try to understand what is data and what is metadata.

[1:34:26] 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? metadata right metadata deals with what is that one? So the struct schema

[1:34:42] so simply we can say so schema what is the structure of the particular what is the structure of the particular table. Yes. So from the academics might be you know the definition of metadata. So it is nothing but data about data.

[1:34:55] 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 example employee is the

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

[1:35:22] every column right what are the columns for this right what are the columns for this table employee number name and address employee number is integer type and name is ware and also the address is also

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

[1:35:47] 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:02] discuss about what is the constraint and all all but uh here try to understand so where carar means what is that one so string so now itself you can feel like

[1:36:15] uh var means what so simply a string I will discuss about what is the difference between so the car and wcar okay but high Well, integer means all of you know integer means what? It is accepting. So numeric

[1:36:31] 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:45] If you are working with any programming language, there are different data types language, there are different data types are available. In the similar way in the database terminology these data types will be represented as

[1:36:57] what is that one so the var so int from database to database these data types will be different okay I'm not saying I cannot say not saying I cannot say in MySQL also these are the data types

[1:37:12] whether these data types are same in other databases means that means in veracle also it would be same no in var Instead of integer so we are representing number instead of var we are representing var 2

[1:37:28] instead of var we are representing var 2 so like that so don't worry about so remembering all these data types because nowadays nobody is relying on what is that one so data types and all syntaxes and all everything will be taken care by

[1:37:42] and all everything will be taken care by the AI tools okay only just we have to the AI tools okay only just we have to understand what is the concept So but try to understand. So what is data definition? Data definition if I'm

[1:37:57] simply saying practically data definition majorly deals with your schema. Schema is nothing but so the table name followed by the column

[1:38:09] the table name followed by the column names and its data types. And also additionally if you are applying some rules, additionally if you are applying some constraints or additionally you are applying any

[1:38:23] other components these are all comes under what is that? these are all comes under what is that? So the metadata but what is data here? Data is nothing but this is the data actually correct. So whatever the

[1:38:36] records present in this table. So this is called as what is that one? So this is called as what is that one? of the data.

[1:38:48] 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:02] 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:15] 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:29] number, name and address. So that's what I'm trying to say. where care and what is the difference between so where care and care okay so

[1:39:42] no worries 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:39:57] So DDL stands for what? So data definition language. some keywords to perform operations on what is that one? So operations on

[1:40:18] perform any operations on schema then we can go with what is that one? So DDL structure of your table. If you want to perform any operations on overall

[1:40:30] structure of a table 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

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

[1:40:57] and also alter and also drop. keywords right so create, alter and drop.

[1:41:09] 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:25] any database object I'm not talking about. So only table yesterday I already discussed what is database correct database is a collection of database objects. Database contains tables, views,

[1:41:40] 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:41:54] 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:06] the table. That's what I'm trying to tell you. And alter is mainly for so think yourself for example we are so think yourself for example we are already having so a table for example.

[1:42:25] 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:40] or you want to change the size of a column So these are all comes under what is that one the alter

[1:42:52] 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:04] 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:17] Alteration in the sense it's not creating so entire thing from very You just want to make the changes to the existing one. So that one comes under alteration. And finally, so the drop.

[1:43:31] So drop is nothing but so dropping a table, table, right? So dropping a table. So dropping a table in the sense completely we are removing. So try to

[1:43:46] understand the terminology here. Mostly in the DDL only in the data definition language only we are using so this drop operation deleting data if you are deleting if you are working with data per suppose you

[1:44:00] 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:12] 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:26] whenever we are removing entire object any database object from the database we 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:40] truncates I will discuss please note it 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:44:53] language. This is super important for you. This is super important for you. So data manipulation language.

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

[1:45:36] So insert, update. So delete. So these operations we are performing on data. It's not performing on schema.

[1:45:49] Yes. Chab 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:01] 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 operations what is that one so insert update and delete.

[1:46:16] update and delete. So I hope all of you understand what is the difference between so the schema and what is data right first we have to build a schema according to that one. So we have to

[1:46:29] 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 one record

[1:46:43] this is following what is that one so certain schema so we have so employee number and also name and address this employee and employee name is string type and address is also

[1:46:59] 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:11] 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:24] just we can for 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,

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

[1:47:57] 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:12] Correct. I just want to check what are the databases are present in my system. So it is showing four but I'm not looking it is showing four but I'm not looking here. I just going to SQL.

[1:48:25] 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 the command show databases.

[1:48:42] be ended with semicolon. So 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:48:56] it's more than four because we are not refreshed it seems so we have so whenever we are issuing show databases uh just a second I will zoom in zoom this

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

[1:50:00] So any size will be there here. Reset grid that is okay but it is not showing any size here.

[1:50:20] So instead of that one so just ask charg or otherwise how to increase the size. or otherwise how to increase the size. Okay instead of writing this one

[1:50:40] So, we cannot find so the certain option right. So, you can always ask what is that one? So chargeability how to increase the

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

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

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

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

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

[1:52:39] 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. Okay. Anyway, I'm sharing this uh

[1:52:54] query with you all of you. So, please follow this query. I will just try to follow this query. I will just try to increase. Just give me a second. Okay. Don't be hurry for anything.

[1:53:13] just wait. So, instead of that one, so let's make my differences here. Okay. let's make my differences here. Okay. So result grid the suggested right

[1:53:25] 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:37] 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:53:51] providing intelligence also. If I'm clicking so show databases so populated. If I'm selecting this query and I'm executing it is displaying. So all the databases for me

[1:54:05] 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:18] line. So command line means what? So command lines you can use hyphen right

[1:54:37] So iPhone so display so display all databases.

[1:54:49] displaying all the databases. Little bit I'm decreasing because it is very difficult to perform operations. I'm just going to so same preferences and I'm just decreasing. So the size in SQL editor.

[1:55:04] So 30 I provided I'm just making it as 20. So let's make it comfortable. So now it is fine

[1:55:20] fresh one so go to SQL. So now it is fine. So show databases it is visible right? Now it is it is visible right? Now it is comfortable for you and all of you.

[1:55:34] So now I want to create what is that one? So HR database for suppose or otherwise some employee database. How can I create it? can I create it? So create a database

[1:55:50] employees or otherwise employee DB. What is the query? So just a simple query is create database. So what is that query name? So the employee DB

[1:56:05] employee DB while providing the database name right. So just provide the database name that's all. So just I'm providing so employee

[1:56:19] DB don't provide any spaces or anything. If you want to specify underscore that is fine. Just I'm selecting this query and I'm executing this query. How can you know whether this query is

[1:56:33] executed successfully or not? In the output you can see in the last one 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

[1:56:49] successful for example if I'm missing semicolon 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

[1:57:03] so it is showing red color red color means whato error what does it mean 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 semicolon

[1:57:18] so all of you Please create so this employee DB quickly and after that so we will uh try to understand so what is table and try to understand so what is table and all.

[1:57:34] you can just put done message in the chart.

[1:57:48] So in show DB why extra DBs are showing. So extra DBS means might be you can able to see the secular database and also CIS database and HR database right three databases by default we are getting these databases by default provided by

[1:58:03] the MySQL for you if you're asking me what is SIS database means SIS database is a system level database that means employee if even if you are creating employee DB

[1:58:19] employee DB? This employee DB metadata also will be stored in system databases, also will be stored in system databases, system tables. In system database, there are many tables we have. If you are looking here, there are CIS config

[1:58:34] table, right? So internally whatever the objects you are creating that may be either database or table or synonym or function all those information will be maintained in this system database

[1:58:49] and these two databases right HR database and also Shakila database these database and also Shakila database these two two databases will be provided okay these two databases will be provided for your practice purpose

[1:59:04] 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 we can able to uh perform operations right but we are

[1:59:17] 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 okay

[1:59:29] 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 you check it? What is the command to

[1:59:44] check whether the employee DB is present in your databases list or not? What is the command we have to execute? Show databases. Right? So command level I'm asking. So show

[1:59:57] databases. Now why what is the purpose of creating database? Yesterday I already discussed database database objects. If you're asking me what is the definition of database? Why

[2:00:12] we are creating database means inside database we can create so multiple database we can create so multiple so database objects.

[2:00:28] So database objects is nothing but so what is that one? the tables. So views, so synonyms, so indexes. Okay, so functions,

[2:00:45] 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? to creating a table.

[2:01:03] Right? If you want to create a table, what is the command? So create table. what is the command? So create table. What is the command? So employee. Inside database, I'm creating one database object.

[2:01:15] 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:29] 100 for example and also the address. and also the address. So worker of 100

[2:01:44] 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 name and what is the type? So integer it will accept only what type of

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

[2:02:12] just high level I'm discussing what is the difference between the difference between so difference between care and wear curve right so care and worker

[2:02:27] if you're asking me the difference between so the car and wear curve so the car is mainly for representing so fixed length characters okay if you are fixed length characters okay if you are talking about so the fixed length string

[2:02:45] need to understand these two things fixed length string and variable length strings. So what is fixed length string? For So what is fixed length string? For example, let's say for the name you are

[2:02:57] example, let's say for the name you are providing so car of 10. characters for the name you must provide. For example, if I'm providing subu, how many characters this string is

[2:03:12] me? Five, right? Five characters. But what happens means it is appending some spaces. Five spaces additionally.

[2:03:25] Why? Even if you are providing five characters, but the car is considered as string. So even if you are providing the name as characters only remaining characters will be appended with some spaces

[2:03:43] 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 not providing so what is that one so the length has five characters

[2:03:59] 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 allocate memory for 10 characters but what about variable length string

[2:04:14] so for variable length string so it is very flexible very flexible so if you're using so name of 10 even if you are providing so some name perose

[2:04:27] so Ram perose Ram is having how many characters three characters. So if you're asking me what is the length of this name means? So length of name is it is taking only three characters only.

[2:04:43] Variable length means what? So maximum size is 10 characters. Maximum length is 10 characters. In this 10 10 characters, characters, it will consider only that three characters only. It is not

[2:04:58] appending any spaces in the ending. So that's why most preferable one is what is that one? So where care but at what type of situations we can go for care?

[2:05:12] For example, if you are representing a gender. So for example, if you are taking scenarios gender,

[2:05:24] 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. So that's why we can keep it. What is

[2:05:39] that one? The car of one right. So whenever we are dealing with fixed length characters so then we can go for what is that one? so then we can go for what is that one? Then we can go for so the car data type.

[2:05:55] 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:10] so now with this details I'm just providing so I'm just creating a table so look at that so look at this

[2:06:22] so currently what is the database we are 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

[2:06:36] where I have to create in employee DB 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 so for that one so we have to make use of what is the command so use

[2:06:51] 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:04] What does it mean? So now currently selected databases what is that employee DB? So currently I selected this employee DB. So in this employee DB I need to create a table. Okay. So

[2:07:19] 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:34] 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:49] So next step what we have to do? So we have to create a table inside the have to create a table inside the employee DB. employee DB per suppose. So creating

[2:08:14] table so create table. So this is the common syntax. What is So this is the common syntax. What is the table name? So employee table the table name? So employee table we have to specify the column and names.

[2:08:26] So don't think about the cases. Okay. For example, so here if you are using For example, so here if you are using keywords, right? So create. So table these are all keywords correct. So these keywords we can use upper case or lower

[2:08:41] keywords we can use upper case or lower case that one is not case sense too. So your schema is not case sensitive that's what I'm saying but your table data is case sensitive whatever the data you are inserting right so that one is

[2:08:54] case sense too for example I'm just going with lower case itself so create table so employee so employee number whose type is integer so employee number whose type is integer and also the name var of 30

[2:09:10] and also the name var of 30 so the maximum length is 30 characters 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:33] 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:48] 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:02] So just read it. So that means the table is successfully created. And also additionally so currently selected databases what is that one? So employee DB in the employee DB if you want to see all the tables

[2:10:19] DB if you want to see all the tables right so see all the tables then we can use command. What is that command? So show tables

[2:10:31] 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:47] 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. having some table which is already present in my database.

[2:11:07] 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, right? Describing a table. So for that one what is the command we are using?

[2:11:37] 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:11:53] 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 And still we are not discussed about keys primary key

[2:12:05] and foreign key and all I will 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:18] 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

[2:12:33] the now the next step is what is that one? So DML perform so these operations right. What are the operations? Insert.

[2:12:46] So update and also delete. 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:00] first I need to know what are the columns can use describe right I can use describe to describe the table. So before that one I'm just

[2:13:13] asking so 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:27] previous query so you can write it. You can just go through go to that particular query and execute it. For example, so describe Just execute that query. It is showing all the columns. There is no need of

[2:13:41] all the columns. There is no need of writing. So query again and again. We are speaking about DML. So the first operation is insert operation.

[2:13:54] 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. So just look into this multiple approaches. The first approach I'm

[2:14:07] talking about what is that the first approach in the first approach we have to insert the data into a table. So insert into employee space [snorts] values of

[2:14:25] if you are using the syntax right so you must provide so values for all the columns what are the columns we have employee number so name and address you must provide the details for all the columns

[2:14:39] but please remember in SQL especially the strings can be represented with a single quotes not double quotations double quotations for example so the name is John

[2:15:01] 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:16] quotations but when coming to the SQL especially so the strings can be represented with only single quotes please remember that point and also try to understand the syntax if you are observing the syntax insert into

[2:15:32] that means I'm inserting the data into employee table but I'm not specifying data please try to understand here so one key important thing I don't want to insert only specific speific columns I

[2:15:47] want to 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:03] 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

[2:16:15] Remember how can you know whether your 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

[2:16:30] pointer we can able to see this is successful right so this query is successfully executed but still I'm not satisfied I want to just want to see the result. So how can you check it?

[2:16:47] So this is comes under what is that one DRL DRL stands for what? So data retrieval language correct?

[2:16:59] So data retrieval language. So here we can just make use of select query type. So select star from what is that query? Select star from employee. So if I'm issuing select star from employee I can able to see

[2:17:16] so enter table right so I have inserted only one record only one record one John's San Francisco like that so for suppose I want to insert another record how can I insert

[2:17:31] so insert into so employee space values of so and bumsy and bumsy and he is from so the Bangalore.

[2:17:52] inserting the second record. So just insert this record. It is successful. How can you know? Just execute. So this DRL query. So but

[2:18:04] what is the problem here? 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

[2:18:17] 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:31] So the space values of so 3 comma rajan okay so chennai

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

[2:19:01] No. Why this query cannot execute Why this query cannot execute any sessions? this query in the employee table we are having so three columns but we are

[2:19:16] providing only values for how many columns? columns? Two columns. you must provide the values for all the columns correct. So in the third column

[2:19:33] if you are not providing so automatically we are getting by error. See look at this this is wrong what it is saying. is saying. So the column count does not match the

[2:19:46] values count. So for that one so additionally we have to specify what is the value. So null value because I'm not inserting any value specifically especially so we have to provide what is the value. So

[2:20:00] the null value. So now it becomes three values right. So three rajan and also null. So we can just select and execute this query. So now we can run this query. So you can see so it is getting inserted right. So

[2:20:17] that means if you are not having a requirement to insert the third value but you must provide the value instead of that one. So you can just keep null or otherwise so similarly

[2:20:33] so you can just provide so open single quotation and closing single quotation 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:20:52] the table. It's already present. The query is if I'm trying to insert the uh just getting the data see look at this. This one contains non value. This one is empty.

[2:21:06] 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:20] understand whenever we are following the syntax, right? So always we are not inserting for all the columns in a table. We just want to insert only specific columns.

[2:21:39] uh you are providing some space don't use double quotations always use what is that one so single quotations only it does not work with the double quotations you can able to see we have to use double quotes but here so we make use of

[2:21:55] what is that one so single quotations only not double quotations in especially in SQL that might be you are working with MySQL if If you are working with Postgress SQL, even if you are working with uh any SQL, any SQL language,

[2:22:11] strings can be represented with what? Single quotations. here look at this another strange syntax. This is the second approach. But

[2:22:24] suppose if I'm keeping the title as what is that one? So second approach. columns inserting the data into a specific columns.

[2:22:47] so employee. So we can specify what are the comments we are inserting. So why use describe star? I am not using any describe syntax star. Right? I'm not using any syntax star in describe command. So describe command just for

[2:23:02] describing the table. So describe employee. So this is star So describe employee. So this is star which is coming from the ID. Okay. So my query is describe employee. That's all. I'm not using any star here.

[2:23:16] Got it. I'm not putting any star inside that one. Only in the select command only we are using star. So the now the second approach for suppose you can see

[2:23:30] see the second approach is very much comfortable for example I want to insert only values for employee number and address name so we can use so the values of

[2:23:47] here I'm inserting only so the employee number and also address right so employee number is four and address is So USA per suppose.

[2:24:00] only so the employee number four and also the 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

[2:24:13] for employee number and address. You can just execute this query. star from so employee.

[2:24:33] So address will be name will be null, right? Because we are inserting only for employee number and address. We are not inserting for name. I'm just sharing these queries. Could you please check what is the difference

[2:24:47] between so the previous queries and especially the second approach in we don't want to worry about so putting null values right we are inserting only

[2:24:59] null values right we are inserting only values for specific columns correct we are inserting values only for the specific columns like that specific columns like that so we'll start with the discussion

[2:25:16] so up to now so inserted the data So just get the data for suppose. So select star from what is that one the employee table and after that I will give you some exercise for you. You have to complete

[2:25:28] exercise for you. You have to complete that exercise. So select star from

[2:25:45] 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

[2:25:58] I want to remove 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.

[2:26:10] So please remember so generally what happens means in real databases we don't want to encourage to insert a duplicate data duplicate data but I will that one I will discuss later

[2:26:24] but as for the current scenario you want to remove this duplicate record might be somebody is 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

[2:26:40] DML operation. So what is that one? So deleting a particular record. So how can you delete it? So we can make use of delete command, right? So delete

[2:26:55] from what is the syntax we have to follow what is the syntax we have to follow here is delete from. So the employee so where so here where is a keyword which is

[2:27:10] mainly for specifying the criteria or condition what we can say what is the condition? So employee number equals to three. So if I am specifying employee number equals to three how many records are getting deleted? Can you please tell

[2:27:26] are getting deleted? Can you please tell me only one record or two records? course the two records are getting deleted. We don't have a choice here deleted. We don't have a choice here because so what happen means the name

[2:27:40] and also the employee number both are 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

[2:27:55] getting deleted. I'm getting an error here. What is the mistake I made here? So the option is showing right all are getting this error. So I'm sharing this

[2:28:09] getting this error. So I'm sharing this command with you. getting this error. What is that one? So safe update.

[2:28:21] So for this for this one so safe update we need to change some settings. okay so just copy this error so whatever the error we have

[2:28:41] for suppose [snorts] I know where we have to tweak so anyway so we have to identify the solution right we are all having access to the chart GPT we need to understand the feature of charge GPT as

[2:29:06] So what it is saying it is suggesting so some some of the options. So set up uh set SQL saved updates equals to zero. So we have to execute

[2:29:21] this query. is providing some solutions for us but I'm asking charge apt for us but I'm asking charge apt in workbench in workbench so in MySQL workbench especially

[2:29:50] copilot means what so it is a generating the SQL queries for us.

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

[2:30:19] So we have to go to the go to edit preferences. here there is a checkbox might be so you can check it out here save updates

[2:30:35] 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

[2:30:47] 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:04] So, now we can execute this query. So, delete from employee. So, where employee number equals to three. So, still it is giving that one. So, the same error, right? So, we can just reconnect it. For

[2:31:20] suppose I'm just closing this MySQL workbench and I'm reopening. So the MySQL workbench and I'm connecting to the server again.

[2:31:32] and I'm connecting to the server again. So please provide the password. closing that one and I'm connected back. So now again I'm checking the setting.

[2:31:44] 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. one is unchecked right. So that is fine. So now we can run this query.

[2:32:03] getting so different. So error right? This one is not related to that one. So we are not selected the database. So So we are not selected the database. So first select the database first. So how

[2:32:15] 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:27] 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:43] 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:32:56] getting the results or not. So how can you know? So I need to just retrieve the data from database. Right? So select star from employee.

[2:33:09] 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:25] 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:45] 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:03] and restart your MySQL ID. So that 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 disabled.

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

[2:34:33] 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:47] >> yeah so I did log out and log back >> Yeah. Yeah. So, but very font size is

[2:35:00] 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:14] execute that command. So, let's try it out. What is the error we are getting out. What is the error we are getting here also? You're getting safe updates. >> Yeah, I did get the safe update error. Uh so I uncheck that and this is the

[2:35:29] 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:35:54] looking. >> Yeah just uh how do I up scroll this up it says >> yeah yeah so what happen means you can >> yeah yeah so what happen means you can just uh put your cursor on the

[2:36:07] the particular point might be you can 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

[2:36:21] I'm still getting >> your screen is not visible actually. >> Oh the screen is not visible. Let me try that again.

[2:36:39] >> Yeah. 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 >> so for that one so only the thing is you

[2:36:56] 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. uh where we can how to drag that one. Okay. Yeah,

[2:37:09] >> you see here, look at this. Uh we can find drag option. If you are putting the cursor like this can able to find it.

[2:37:24] See if you are putting the cursor here. So it is the cursor icon is getting >> Yep. Yep. >> You can drag and up and down. Okay. >> could you please check it out? So the same thing is

[2:37:41] same thing is happening for you. are the junction panel? So you can just put the cursor identify where exactly.

[2:37:53] >> This is where >> so you can share your screen. You can >> Oh, it's okay. Stop again. >> It's okay. It's a visible. Yeah.

[2:38:08] cursor. >> Click double click on that one. >> Okay. You can select that blue color. Click on that blue color. Whatever the

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

[2:38:40] >> Yes. Yes. Yes. Yes. >> Up and down only. This is the challenge. I think I need to check what exactly. So how to drag that

[2:38:53] check what exactly. So how to drag that one up and down and down. know >> here. Yeah, in this um the preferences should not be >> ah that is not the problem. So the

[2:39:09] by default it checked. >> Did you check that one? >> It was it was checked. Yeah. So I did uncheck it that uh and then I >> close this uh link and I reload. >> So closing in the sense again you have

[2:39:22] it. Correct. >> Yeah. Mhm. Yep. I did that. >> Yeah. Mhm. Yep. I did that. >> Okay. Okay. Please keep hold the problem or you can just uh drop a message. I will let you know what is the exactly

[2:39:35] 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:50] >> 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:05] >> Mhm. >> So, just select that and execute it. >> Okay. >> Why? Because you reconnected, right? So, we not selected the database. And after that, now we can execute delete command.

[2:40:27] >> No. >> Now we can select. Now we can select. >> 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.

[2:40:41] >> It's not deleted. >> No, it's not deleted. >> Okay. So I will just uh check and come back to you. Okay. Regarding that problem. Okay. Thank you. Thank you.

[2:40:58] you are uh some of you are facing problems. No worries. But feel please follow 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

[2:41:12] updates in the platform. Those who are working 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 solution.

[2:41:27] So now what are the records present in 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 my screen the name is empty or not?

[2:41:43] Yes. So the name is empty. name is empty or not for the fourth record.

[2:41:57] So what operations I I can perform here? 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 meaning of replacing? So the name of

[2:42:14] fourth employee record it comes under insert or update or delete. What is the option we have to choose? Basically, it is coming under insert or update or

[2:42:28] it is coming under insert or update or delete. from you. Very good. So the update.

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

[2:42:56] H. 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:10] 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:25] so set name equals to some hurry per suppose employee number equals to four I'm just specifying the condition

[2:43:39] employee employee record whose employee number is what is that one for the number is what is that one for the I'm just executing this query. It is successful. How can you say so? So so whether record

[2:43:54] How can you say so? 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:07] working fine. So this is the query which is mainly used for what is that one? the updating the record. So you want to update the name or you want to update the address, we can make

[2:44:20] use of this query. So whenever we are using update keyword, So the set keyword comes into the picture. So in the record what is the value we are setting? So that is the super

[2:44:33] important, right? We have to set the value. So please do that one and like that message if the count is getting increased. So then I can able to proceed with the next query.

[2:44:55] multiple records I need to update here. Right? If I'm going down I think there are no records with empty data. data. Okay. So I want to update the

[2:45:09] employee record whose employee number is four and I want to update the address. What is the query? I have I can write it. So update

[2:45:21] query? I have I can write it. So update employee.

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

[2:45:51] equals to four then what happens already employee number four address is USA I'm just updating with what is that one so the Bangalore I just executing this query you can see so the details are getting

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

[2:46:26] 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? Yes of course you can update name also. For suppose

[2:46:42] For suppose if you want to update the name perose

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

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

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

[2:47:45] 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:01] 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 delete. Okay, that is fine. So let's see how we can make use of AI tools to learn

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

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

[2:48:49] So we are just assigning some role to the charge GPT right any a tool if you are interacting so we need to assign some persona persona is something like so act as SQL developer suppose

[2:49:06] or otherwise so mySQL developer especially I'm working on so the MySQL right so MySQL developer

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

[2:49:45] So update and delete the charg to provide the better results right. So it's a persona pattern. So

[2:49:59] 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 the AI tool. So I'm just suggesting so act as a MySQL

[2:50:13] So I'm just suggesting so act as a MySQL developer for suppose. So here I just developer for suppose. So here I just assigning a persona like act as a MySQL developer explain how to create a database and tables

[2:50:26] delete operations operations on tables.

[2:50:57] 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:11] am just sharing this prompt in the chart as well. So for your reference.

[2:51:23] So if you want to make changes, yes, of course you can make changes also. Okay, let's see uh first initially how it is generating and if I'm executing this one so automatically the charge GB is

[2:51:40] understanding my question right it is generating so I'm not provided with any database name so it is creating so company database creating so company database and also it is using company database

[2:51:55] and it is creating a table but especially whatever the schema is specified and it is adding so some primary key and key and all I will discuss about that one no worries

[2:52:10] so I missed some context don't use any constraint or anything so it is saying so inserting multiple rows and all those stuff stuff so I'm opening a new chart so same query

[2:52:24] I'm using same question. So act as a MySQL developer explain how to create a database and tables and how to perform insert, update, delete operations on the tables.

[2:52:39] So better you can provide some examples with sample schema with sample schema and uh I'm mentioning especially don't use constraints. Okay. So I am just uh mentioning don't

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

[2:53:09] 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:26] I will tell you with the different tools that's what I am saying I'm just asking that's what I am 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

[2:53:39] no but the prompt which is very very important. So we are using some persona pattern. So especially here so don't use pattern. So especially here so don't use any constraints up to now we are not

[2:53:51] 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 database and how to use the database and how to insert the data and how to delete the data and how to update the

[2:54:03] data. So all these things are satisfying properly right? Okay. For suppose maybe you're asking me a question it's not like that. So I am

[2:54:15] new to this area for suppose I am from marketing. I am from medical or suppose healthcare I am from so manufacturing.

[2:54:27] How can you ask SHP 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 we can call it as persona pattern.

[2:54:42] 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. have to behave in such a way right. So just look into this persona pattern

[2:54:56] uh not persona but suppose audience persona pattern. We have two patterns. One is what is that one? So the persona pattern so just be used. So persona pattern means what? So for

[2:55:12] who and what right? Who and what means what? developer. So we are just assigning some persona to the AI tool and what the AI

[2:55:27] tool is going to do that is called as what is that one persona pattern and next one is what is that once audience persona pattern. Audience persona pattern means these two

[2:55:41] patterns we have to understand. So while learning while using any charge tool right measly these two patterns basic patterns look at this I'm asking in a different way

[2:55:55] way so I explain

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

[2:56:39] into the tables? how to insert or update delete data in how to insert or update delete data in the tables.

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

[2:57:13] table and how to insert update and delete data in the table to a marketing person per suppose here I'm just specifying what is that

[2:57:26] one audience so for whom you are generating the content. So to a marketing person for suppose marketing professional so I'm

[2:57:38] suppose marketing professional so I'm just keeping so marketing professional so just I'm keeping so extra assumption don't add

[2:57:52] any constraints and suggested schema is

[2:58:05] name and also address right so let's see how it is generating

[2:58:17] so let's see how it is generating but what is the problem I made a mistake by default it is understanding MySQL so I'm not specifying any MySQL keyword I'm not specifying any MySQL keyword okay it is creating so the marketing DB

[2:58:31] And it is creating employee table for you and it is inserting the data. So that means it is keeping so I'm explaining to the marketing person how the table will be created and

[2:58:46] 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 salesperson or something like that. So let's change

[2:58:59] this one to so just copy this query just change this one to healthcare department for suppose so just I'm

[2:59:11] copying this query so I have to create a database and table and uh how to insert the data delete the data in uh tables

[2:59:23] data in uh tables in MySQL saying per suppose so your job is what is that healthare professional

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

[2:59:51] employee employee number name and address and uh address and uh I explain details

[3:00:03] the pattern so what we are saying so healthare professional does not know about SQL he don't know about SQL right so might be he never used so might be he's attending this training

[3:00:17] after this training might be you are getting some doubts so he you can ask It is providing so perfect details for you. What is the database we are creating? Why we are creating the database? It is providing so clear

[3:00:30] details about how we can create an employee table and 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:46] 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

[3:00:58] okay so something so one employee record 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 you what I'm saying this would be helpful for you this prompt patterns

[3:01:12] helpful for you this prompt patterns how can you generate the prompt persona pattern. So act as expert in SQL. Act as SQL developer

[3:01:25] automatically the chart GPT or some other tool will you. So maybe you're asking AI. So maybe you're asking AI. Uh so please provide

[3:01:53] so automatically the chart GP 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:08] for you. You can just go through that once and complete the exercises. once and complete the exercises. So what is the tool name? Perplexity.ai.

[3:02:22] I think two days back only this is 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

[3:02:35] efficient. You're saying so same prompt right act as a right act as a uh SQL developer

[3:02:51] or otherwise you can change the pattern per suppose explain.

[3:03:12] So updating and deleting

[3:03:28] functional person. Okay. So to a functional person to a Okay. So to a functional person to a functional employee technology and all. So they are functional employee working in

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

[3:04:10] what is the response it is giving. 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 with respect to the HR so what what are

[3:04:26] 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 is sufficient for your learning purpose

[3:04:40] 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. We can use any of present in the market. We can use any of the tool for your learning purpose.

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

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

[3:05:21] 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

[3:05:34] alter option. Okay. what is that one alteration how to alter the change the column names and all other stuff so what I'm saying is don't worry

[3:05:47] about writing code today today only knowledge is required actually okay knowledge in the sense like what is table what is database you should know

[3:06:00] about at least what is table what is database and all the concepts present in uh SQL. So you don't want to worry about so writing code almost all the companies are allowing so these tools licenses for

[3:06:15] are allowing so these tools licenses for you especially GitHub copilot so I'm having so the multiple licenses 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

[3:06:31] writing code today nowadays 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

[3:06:45] to make some changes to the existing logic I'm just just tweaking 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:05] okay that's great so let's jump into the next level.

[3:07:17] 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:37] So I'm just creating one SQL file inside this. So samples SQL integrated with the SQL tool, right? Sorry, it is already integrated with

[3:07:50] Sorry, it is already integrated with what is that one? So one of the A tool here it is asking so control + I to write the prompt. So if I'm pressing control + I, it is asking what is that one? So generate the

[3:08:03] code. So you can write the uh script here. For suppose here. For suppose uh what is the script? I can write it.

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

[3:08:36] So maybe this one is not visible for you. So now it is visible right? Inserting. So same thing right. So inserting, updating, deleting and all those stuff updating and deleting

[3:09:10] which contains employee number comma name So if I'm executing this one so if I'm connected to the model see automatically generating so all the code for you.

[3:09:25] code for you. So I just I'm keeping this code

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

[3:09:55] 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:09] whatever the I discussed but suppose updating so employee table so it is automatically generating accuracy accuracy means what so it is generating so whatever it may be.

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

[3:10:37] 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 so this is the model actually the codeex model it's not free up to some level it

[3:10:50] is free okay so for training purpose it is okay you can install this codec 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

[3:11:03] can able to uh make use of this tool right. So no worry about what is that one. So writing code manually even if you are making mistakes you can just check with this uh

[3:11:16] whatever the code generated by the AI. But uh truly I'm suggesting first in the course while learning the course right first you can write it manually

[3:11:30] 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 you are asking me so whether the AI tools will generate the accurate code means I can say 100%

[3:11:45] not correct okay so that is one important assumption I want to talk about correct

[3:11:59] So as a developer you should know what is the concept how to write the query 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?

[3:12:14] what I'm saying? A models will always hallucinate. Hallucination means what? So it is not giving. So the exact answer what is required for you. We need to tweak the prompt. We need to

[3:12:26] use some patterns like persona pattern and also audience persona pattern. So different types of patterns to get the exact answer. If you are normally asking like a question to your friend, it does not give answer. It will hallucinate.

[3:12:42] So you have to mention so act as a super SQL developer. SQL developer. Okay. So you have to assign some role or otherwise explain. So the SQL queries to the non- techy perose non-technical

[3:12:57] person they should they don't know about technology how you can explain. So please explain so use the scenario in this way if you are giving the question. this way if you are giving the question. So the uh the a models is going to

[3:13:11] produce better answers for you. Is it clear all of you? What I'm speaking all of you can able to get it. Can I proceed further? Can I proceed further? Yeah. So this would be more helpful.

[3:13:27] So try to use charge between so that's what I'm saying first initially. So we created so the employee table of course you understand so insert data update delete update delete and all those stuff

[3:13:42] those stuff but let let's think yourself is the table so employee table so which contains what are the columns

[3:13:56] employee number whose type is integer and the name what is the type worker of and the name what is the type worker of 100 whatever it may be worker of 100 or worker of 30 might be this one is worker of 30

[3:14:17] and address will be so worker of 100. right I just want to add an extra column called as salary.

[3:14:30] called as salary. So salary is of type in So if you want to make any changes to the schema. So again I'm repeating if you want to make changes to the schema.

[3:14:48] So then we can go for what is that one? So alter command. Can you please tell me this alter command comes under DML or DDL? So DDL create alter drop. Create, alter, drop.

[3:15:03] These are all comes under what is that one? So, DDL commands. So, alter means what are the operations you can able to perform under alter. We can add column name. Add or drop. So, we can add or drop column

[3:15:23] and also we can change so the size of the column. And also we can change so the data type of a column right.

[3:15:39] column. We can change the size of a column. So all these things are possible with the help of what is that one? So the alter command. So let's see how to work with this

[3:15:53] So let's see how to work with this alter. So first we can try to add column and let's observe we can take a support of charge also. First describe this table. So describe employee.

[3:16:09] columns present in this table? So we 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 keeping. So I add another column.

[3:16:32] So what is that column? So salary. How to add alter? So table. So employee. So employee. So I'm just keeping so add column.

[3:16:46] 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:16:59] successful. So now we can check describe employee. So salary column is getting added or not. It is getting added. So look at this salary column is getting added.

[3:17:13] And if you're selecting so the employee details for suppose select star from so employee.

[3:17:28] 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. By default the salary will be empty.

[3:17:42] 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 means constraint is a rule or regulation.

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

[3:18:13] So it's a rule what are regulation. So applied on your columns of a table applied on what is that one the columns

[3:18:30] constraints. So the first one is what is that one? So the unique constraint So first try to understand what exactly unique constraint we have an employee

[3:18:42] table. So employee number name and address have to assign what is that one so unique constraint here

[3:19:00] constraint here so what is unique unique means it does not allow duplicate values right it does not allow duplicate values for example so here employee number one and name is John

[3:19:16] and name is John address is Bangalore per suppose employee number for example again if I'm trying to give so one

[3:19:34] possible so because this one is treating as what type of value so the duplicate record correct this one is treating as duplicate record because for the employee number I assigned what type of

[3:19:47] constraint so unique constraint. So duplicate records are not allowed. So this is super important right? So generally if you are working with any real production database might be you can see email

[3:20:04] so email column must be unique. Can you please tell me post it in the chat? So anyone could you please identify what are the specific columns that are uniquely identified. So that is your guess. Take customer or

[3:20:19] employee or any entity. What are the unique columns might be you can easily identify anyone could please post it in the chat.

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

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

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

[3:21:22] notal? Another key word. What is that one? So the notal.

[3:21:34] 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 interview. So what is not null mean? Say not null is simply a keyword. It's not a

[3:21:50] 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:14] So employee number one and for this name I am assigning what is that one null.

[3:22:29] 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:41] 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:22:55] speaking about so what is that one the primary key primary key super important constraint database right any database table we have

[3:23:13] suppose if you are taking any table so we have some key attributes So what are the key attributes and what are the non-key attributes? We have to

[3:23:27] we have key attributes. Some of the attributes will be acting as key attributes and some of the attributes will be will be so non-key attributes.

[3:23:43] if you are working with so the attributes which are mainly used for what is that one? So identifying the particular record for suppose that type of attributes we can call it as key attributes.

[3:23:56] For example in employee table say employee number. for uniquely identifying the particular employee. Right? If you're asking me

[3:24:09] 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:21] or passport number or other number. So so many right. So based upon the columns some of the columns might be so

[3:24:33] the key attributes those key attributes are mainly for uniquely identifying the particular uh record but some of the attributes are nonkey attributes for example. So the salary

[3:24:51] designation. So designation of an employee this is also an key attribute. But first that the thing is if you are deciding any entity per suppose if you are taking any entity

[3:25:05] so first we have to identify what are the key attributes and what are the non-key attributes. So key attributes are mainly for record and non-key attributes are not used for

[3:25:20] uniquely identifying a record per suppose for example. So salary designation based upon the salary we cannot uniquely identify a particular employee based upon the designation also the same

[3:25:32] 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 important role. So for uniquely

[3:25:45] 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:25:57] 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:12] But in these attributes also you are having an option to choose the super key. Super key is nothing but a primary key. For suppose you have to choose the primary key.

[3:26:26] Among all those data attributes mostly 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 one? So it does not allow duplicate

[3:26:40] values and is not it does not allow null values always we are keeping. So 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.

[3:26:55] So might be you're asking me question. So you are saying only employee number why I have to choose I can go with what is that one of the email or phone number or other I understand your question so those are

[3:27:09] I understand your question so those are all key attributes only. So key attributes means what? Those are all employee number all employee number and address. Okay, sorry.

[3:27:23] So, employee number, email, passport number, address, these are all majorly used for identifying an employee. For example, let's say Mahima uh how can you identify how can I identify as a simply learn

[3:27:38] employee for suppose if you are working in simply learn how can you identify how can I identify you as a simply learn employee might be you are alerted with some employee ID right and you are having so

[3:27:50] simply learn email id so based upon these things only employee There are many many things are there that might be email that might be employee ID

[3:28:07] or that might be your personal identification number like other number or otherwise social security number or anything anything but among those only one only one column

[3:28:21] that might be employee number or social security number so or other number whatever it may be we are choosing only one as super key like that. So somebody's asking so what is the

[3:28:35] difference between key attribute and super key any key attribute might be correct any key attribute might be what is that one super key any key attribute

[3:28:47] means what we have salary designation these are all not key attributes employee number passport number among those we can choose anyone as a super key like that super key is nothing but what that means the primary key is it

[3:29:02] what that means the primary key is it here what I'm saying here what I'm saying all of you can able to understand compulsory we have to use this one is the so primary key and all that is up to

[3:29:18] you okay so nikita it's a simple concept right so for example if you are taking uh uh student details per suppose we are all joined in the session

[3:29:32] How can I how simply learn will identify you as a student? You are all joined in this session. How the administration team can identify you as a part of the this course. But suppose we have also some

[3:29:48] fields right in the student table. We have some fields. name, email

[3:30:01] and also like your uh SSN number or whatever it may be. So there are many fields are there mobile number.

[3:30:16] 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? This means what? So that might be

[3:30:32] allocated uniquely for the particular student. For example, so if you're talking about the keys, eligible keys is here. So email, so almost email must be unique. Correct?

[3:30:47] 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 the student ID. So this is also we can consider it as a key attribute

[3:31:02] key attribute and also SSN number that one also we can attribute and mobile number. So in a real world like mobile number we can consider it as a key attribute but might be the same

[3:31:16] mobile might be used by multiple people per suppose the number is not allocated with multiple people but when coming to the real aspect right so might be

[3:31:28] 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 those which one is strong among those which one is strong. So

[3:31:43] either email is strong and SID is strong and SSN is also strong. Strong in the sense you can uniquely identify the particular student with the help of SID or social security number or email.

[3:31:58] Correct? And also what about the non-key attributes? Non-key attributes means we cannot identify. So based upon so that non-key attributes

[3:32:12] 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 one so some ID for each and every student record this SID can be used as

[3:32:26] 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 that one primary key.

[3:32:40] But what exactly practically if you're asking me what is primary key means?

[3:33:01] 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:15] 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:29] 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:43] 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

[3:33:55] created that is nothing but what is that one? So the primary key one? So the primary key it does not accept null values values. These two constraints needs to

[3:34:08] be satisfied. And some important points related to the And some important points related to the primary key. the primary keys. So foreign keys

[3:34:24] are always [snorts] refers to the primary keys. So let's try to understand. So I will discuss more in detail. So while

[3:34:38] 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:34:52] Relational database management system. If you are taking so almost all database management systems they are RDBMS. [snorts] [snorts] RDBMS stands for what?

[3:35:30] system what happens means we 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:43] you are having if you're taking e-commerce application for suppose. So in the e-commerce application, so we have products table,

[3:35:55] right? So this is called as what is that one? So products table

[3:36:13] 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:36] product to a cart, so automatically that product will be visible in the cart, right? So [snorts] in a relational database management system, if you are designing a relational database management system

[3:36:48] for your application. 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

[3:37:01] data. Correct? Take Amazon as an example. we are purchasing a product. 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

[3:37:17] customer details and customer details are present in customers table and the product related with what is that on the particular category and also first customer wants to purchase the product. customer has to add item to the cart

[3:37:31] right so what I'm 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

[3:37:45] that one into multiple tables but suppose we have so products table and customer table and also catalog table so we have c table so we have so multiple tables our data will be stored in that once the

[3:38:00] multiple tables like that our data will be stored in that once the multiple tables but even though these tables are having but even though these tables are having relationships so customer and products

[3:38:14] per suppose one customer can purchase many products right and otherwise so many products right and otherwise so many customers can do many products so there are we have so different types of tables many customers will purchase

[3:38:28] what is that one so many products I a customer you are also a 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

[3:38:43] of relationships what are the different types of what are the different types of relationships so we have one to one relationships so we have one to one and one to many meaning

[3:38:57] have so One to many. What are so this one we can call it as what is that one? So many to one

[3:39:12] relationship. So many to many relationship. relationships but I'm not going deeper right now. So right now I discussed

[3:39:24] constraints one is unique constraint and another one is what? So the primary key constraint.

[3:39:40] 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

[3:39:56] it 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 part so how to assign the kind of

[3:40:11] constraint after this concept I will discuss about 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:25] will go some deep dive into what is that once the foreign key how it works and all. Okay. So no worries. First try to understand what is that one unique primary key and not null. Can you please tell me what is unique? Unique means

[3:40:38] tell me what is unique? Unique means what? It does not allow. It does not allow duplicate values. Correct? Duplicate values. What is primary key? Primary key means

[3:40:52] unique plus not null. So please remember so keep it in mind. So primary key which so keep it in mind. So primary key which is called as key which is unique plus

[3:41:06] 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:26] I think you can able to follow Sushmita or someone I already posted. If you are feeling okay so then no issue. If you are not feeling like my pace is not going fast or anything so please post it again so I can proceed

[3:41:41] You can look at here. So I'm just connecting to so this MySQL database connecting to so this MySQL database you can just go to the services.

[3:41:55] check what is that one the MySQL service right. So of course this MySQL service is running. So MySQL IT service is what is that one up and running.

[3:42:08] So now what I have to do I have to connect to that MySQL IT service for that one. So go to so this MySQL workbench

[3:42:24] provide so the password for suppose root. asking so what is the database what are the databases that are available. So

[3:42:37] the databases that are available. So show databases. getting all the databases right. So you can just check it. So we have an

[3:42:49] employee DB. Of course we can see here also in the left panel 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 schema

[3:43:04] the drop this schema and also we have HR database is present. So I'm just dropping. So don't delete. So other databases because they are system databases. Here I'm just writing. So create

[3:43:16] database. [snorts] So database name. What is the database So database name. What is the database name I am creating? So employee DB for example if you want to drop the database. So which is already present.

[3:43:28] So what we have to do? What is the command we have to use? So from the command lens drop database. What is the database name? database. What is the database name? So employee DB

[3:43:46] If you want to delete the drop database but suppose we can use this command. So drop database employee DB like that. Okay. So all of you please execute this command. If you already created the

[3:43:59] database I think in the last session so might be you created so employee DB right all of you dropped this employee DB database and verify whether this is successfully deleted or not

[3:44:13] so once you deleted the database the next step what we have to do what is the next step we have to do anyone again recreate it you please type in the chat so what is the command And to create a database

[3:44:30] already you have experience in the last week. Can you please tell me so the commands how to create a database? how to create a database? So create database

[3:44:45] 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

[3:44:57] the employee DB is created but it is not reflecting here. 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

[3:45:12] or 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 just refresh it. So that means uh as Robin posted message

[3:45:29] 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 creating the database that type of

[3:45:44] 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 [snorts] so directly can I create the table without selecting the database. No, right.

[3:45:58] What is the next step? So, use So, use what is the database?

[3:46:11] 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:24] right. So the next step is what is that one? So I have to create a table with employee number as unique unique constraint. But suppose I want to apply unique constraint for employee number. But try to understand

[3:46:39] here if you are talking about any constraint right constraint any constraint

[3:46:54] we can apply two levels one is column level

[3:47:07] and also what that one. So the table level example. So if you are having so employee number

[3:47:21] so name and also email of course employee number must assign so primary key.

[3:47:34] right? So not on the tables. If you want to use the particular database just use Tables will be created inside the database. Correct?

[3:47:46] So tables will be created inside the database. If you want to create the first we have to select the database. For that one we are using use command.

[3:48:00] 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 keyboard? So not null keyboard.

[3:48:16] mean? So this name does not allow null values. Correct? keyword? So not null. Not null means what? So it does not accept null values.

[3:48:28] And email might be must be what is that one? So 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

[3:48:43] both are I'm not applying on so same 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

[3:48:56] employee number primary key we are 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:11] so remaining columns I am keeping it is as it is so let's observe how to create what is that one so employee table with unique constraint unique constraint I am just writing so create table

[3:49:28] please listen because after this uh one right I will give you I will share you one document you have to create all the tables and on your own tables and on your own employee number whose type is integer

[3:49:44] and also this one I am keeping it as what is that one so the primary key chat so what is the primary key what does it mean primary key means what unique plus [snorts]

[3:49:59] no No, no, not unique key. Primary key means what? Primary key means what? So unique plus not null. Not null. Okay. Unique plus not null. It's not null. It does not allow null values.

[3:50:15] allow null values. And also for other fields for suppose [snorts] what are the other fields name it is of 30. So here I'm assigning what is that the particular keyword. So not keyword

[3:50:35] name I'm assigning what is that one the notal keyword notal keyword and also for email 100 so this one must be what is that one so

[3:50:50] 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:05] which contains what is that one the employee number as a primary key and name it's not null not null means what it does not allow null values and also for the email so I'm assigning unique

[3:51:19] constraint so you can just run this query so the table is getting created so now try to insert the values into this table but suppose I am just uh sharing this uh script with you. So all

[3:51:34] of you please create this table with this constraints and like that message once you completed. So please like that message.

[3:51:52] what is primary key and also what is unique? 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

[3:52:05] not null for example. So we have a simple table if you are taking. number and name and also as well as email.

[3:52:19] So email must be unique per suppose if you are assigning so email as unique unique key. So this one does not allow duplicate values. That's what I'm saying. per suppose

[3:52:36] so gmail.com perose so the same should I set the rate ofgmail.com again I repeat it for another record no right so this one does not allow duplicate values this one is treating as

[3:52:49] what type of values so the duplicate value 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

[3:53:03] again. So for the particular column and primary key means what? So primary key basically if you're using for identifying a particular record suppose if you're assigning a primary key right so the symbol will be

[3:53:18] 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:31] 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

[3:53:43] should I set the rate ofgmail.com. So in the similar way can I give it again one? It's not possible. Can I leave it this one as empty? For example, so here I'm specifying. So one this is null. If you are trying

[3:53:58] to insert null this one is possible. No because if you're assigning a primary key right that one does not accept not accept null values. That's what I am trying to tell you. Is it clear at

[3:54:16] still you are having any questions please unmute and speak with me. constraints right so null and unique constraints null not

[3:54:31] so null and unique constraints null not null I'm saying so it's not null if you are talking about so not null means what for example if you're taking name you're particular column so must I accept what is that one value we cannot keep that

[3:54:46] one as empty we cannot keep null values into that into that In such cases we can assign notal rate. For example, you are doing some business for suppose you are maintaining your

[3:54:58] 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:12] cases what we are keeping. So email cannot be null and email must be unique per suppose. Unique means what? So each customer is having so unique email id. Okay. So according to your requirement

[3:55:27] difference between unique and not null means unique means it does not allow duplicate values. values. So not allows

[3:55:44] duplicates and not null means what? null values like that. That's what I'm trying to tell you. So

[3:55:59] 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 a form, feedback form. For suppose,

[3:56:13] if customer is filling out the feedback form means you must specify some fields as mandatory. Mandatory means what? They have to must they have to fill that values, right? So that type of values are not null. Not null means what? So

[3:56:27] you 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 very

[3:56:39] Very simple terms actually it's not very much complex. this is no output. Right? So creating a table means here look at this. This one is table is created. This is only the output.

[3:56:53] This is only the output. Okay. If you want to see so how to know created means what? So we can specify what is that one. So show tables. If you are specifying so show tables then automatically what happens? What

[3:57:07] are the tables that are created? So it will display all the tables right all the tables for you. So we have an employee table already

[3:57:23] 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 if I'm specifying so describe employee so then automatically describing so the

[3:57:37] employee table right look at this here very very important information we can see so we have an employee number what is the key is assigned here what is the key is assigned so primary key

[3:57:52] is assigned so primary key and name constraint is assigned it will accept accept null value or not. It does not accept null value. You can see no. Why? Because we assign not null

[3:58:07] 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 email it is showing us. It accept null values. But it does not allow

[3:58:19] 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 table and uh let me know in the chat all

[3:58:33] of you can able to create the table or not.

[3:58:51] So others what about the others all are created right? 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 level

[3:59:11] what is column and level actually if you're taking any constraint it. Is it okay all of you?

[3:59:32] that what is that one the column and level what is table level. So we need to understand. So column level means if you are having assume that if you are having a table employee number employee name and also

[3:59:47] email and address and address so mobile number or something so try to understand if you're assigning if you're taking any constraint that may

[4:00:01] if you're taking any constraint that may be unique constraint for one column. Right? For example, if you're assigning a primary key for this

[4:00:16] particular column. So this one is at column level. Column level means what? So only for the particular column. But think about yourself if you are assigning at column level for example.

[4:00:29] 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:44] column so does not allow duplicate values. Correct? not allow so duplicate values and also null values. For example, if I'm trying

[4:00:58] to insert so again what happens one so again what happens one is it possible to insert one again no the same one cannot be repeated again so

[4:01:13] 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:33] assigning a constraint at what is that on the table level. on the table level. What is table level actually?

[4:01:46] we are assigning a constraint. So for more than one column. one column. So we are assigning a constraint.

[4:02:02] it as what is that one? So composite constraints. Composite keys per suppose. constraints. Composite keys per suppose. What is that one? So the composite

[4:02:16] 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 are taking so employee number and email or employee number and name

[4:02:30] and email. So what I'm doing here is for employee number and name combination. So I'm assigning so one primary key.

[4:02:45] combination. So I'm just assigning what is that one? So some primary key. is that one? So some primary key. So for example so one So for example so one sur

[4:02:59] here also I'm keeping so sur at the rate ofgmail.com primary key right but not for the particular column so as a combination of

[4:03:11] employee number and name correct I assign one single key what does it mean the same employee number name same set of values cannot be

[4:03:23] repeated again for example if I'm trying to write so one should raise again so that this one works this one does not work right this one does not work right so the same set of values

[4:03:37] cannot be repeated again the same set of values cannot be repeated again for example if I'm trying to specify one ROM and some email ID Is this one accepted? Yes, this one is getting accepted.

[4:03:57] 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:15] values? Correct? The same set of values. First this record is accepted. Next the same set of values cannot be repeated again because you know the primary key means what? It does not accept duplicate values and null values. For example,

[4:04:30] especially uh if you're taking your account number, your account number is considered as what is that one the primary key. If you have opening an account so compulsory you must have a so account

[4:04:44] the same account number cannot be duplicated again the same account number cannot be duplicated again duplicated again so that's what I'm trying to tell you

[4:04:59] 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 example let's Okay, here I'm assigning so for the same table

[4:05:13] so for the same table take the fields employee number, name So what I'm saying is for the name and email

[4:05:27] as a combination I'm assigning one unique constraint, employee number I'm assigning primary key. What does it mean? So unique means so the same name name and email cannot be

[4:05:40] repeated again and again right same name and email as a combination so cannot be repeated and again and again for example employee number one and name is Ros

[4:05:52] so some email for suppose again if I'm trying to give so same ros automatically this one is record is getting rejected correct so this one is getting rejected this will not allow you to insert.

[4:06:10] So the reason is so the same record we are trying to insert right the same record with name and also email. So we are trying to insert so the same record we cannot

[4:06:22] insert so the same record we cannot insert it like that. Is it clear? insert it like that. Is it clear? So what is composite key? the composite key do you have any questions?

[4:06:41] others? Could you please tell me do you have any questions? Means I will explain have any questions? Means I will explain it again. So look at this. I'm just dropping this table for

[4:06:56] suppose. What is the table I created earlier? employee table or otherwise we can keep this employee table as it is or

[4:07:08] this employee table as it is or otherwise that is your wish I'm not table if you want to create so what is the command so drop table if I'm creating so drop table employee so then automatically what happens the

[4:07:24] 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 whether the table is present or not So you can just execute. So show tables

[4:07:39] you can just execute. So show tables right. So show tables command which is mainly for displaying. So all the tables.

[4:07:52] 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:10] employee number, name and also email and assign constraints right assign constraints. What are the

[4:08:24] assign constraints. What are the constraints? So employee number as constraints? So employee number as so PK PK means what? So primary key

[4:08:38] 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:50] specifying for suppose? So email must be unique. So this is my command per suppose I'm just writing creating a table with all these specifications.

[4:09:05] Let's create the table. So create table employee of employee number whose type is what is the type integer and especially this one must be what is that the

[4:09:20] primary key. So you can specify what is that the primary key and name. So var of 100

[4:09:32] So var of 100 right and also it is what is that not what is data type actually so the data type is worker of 100

[4:09:47] right so the email is worker of 100 and also unique constraint And I'm assigning unique means what it does not allow. So

[4:10:00] 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. I'm sharing this one with you. So all of you please create the table.

[4:10:16] exercise with you after discussing this constraints we have to do on your own. So that one 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

[4:10:32] describe employee. then automatically what happens it is providing so complete description about this table. Anyone could you please unmute and speak

[4:10:47] with me? Can you please tell me what is the description it is stating? What do you understand from this description? Because you need clarity right so somebody might be already uh good in this area for suppose can you

[4:11:01] please explain so what is the description it is showing so in my screen so take employee number what is null no what why it is showing so null as no

[4:11:15] any idea because we assigned what is that key what is the constraint I assigned on employee number primary key primary key means what it does not accept.

[4:11:29] So duplicate values it does not accept null values also. So that's why it is specifying as what is that one? No. And for the name as well we specified not null. Correct. For the name what is

[4:11:42] the constraint? What is the keyword I assigned? Not null. What does it mean? So for name also it does not accept what type of values null values and for email

[4:11:57] I specify unique. Unique means what? So it does not accept duplicate values. So here it is specified as unique and here it is specified as what is that the it is specified as what is that the primary key. So by describing the table

[4:12:12] we can able to understand what exactly the constraints that are applied on the 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

[4:12:27] and the name uh not null constraint is applied on the email also unique constraint is applied like that. So all of you please execute that one and try to insert the values.

[4:12:42] So try to insert the values whether it is allowing so duplicate values or not. So you can check it already you are having experience with the insert command right I'm requesting all of you please try to do some insert commands

[4:12:55] whether that one is working or not so could you please confirm I'm just inserting so on my own per suppose let's see

[4:13:11] 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:24] number one and name is John John and also the email will be and also the email will be so John

[4:13:45] So this is the way so we can able to insert multiple records right. So you can run this query this works successfully. So no worries without having any problem. So this one works successfully.

[4:14:02] again but suppose I'm just copying the existing. So the query one again so John so the same record I'm trying to insert

[4:14:18] 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:33] 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:46] the employee number I am trying to 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

[4:14:59] So still we are getting error right. What is that once the employee number cannot be null? So that means the primary key is working fine. That means we are assigning a primary key on the particular column.

[4:15:13] primary key on the particular column. 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?

[4:15:27] Yes. Great. So the next step is for suppose I am 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:44] but I'm trying to insert null value here is it possible is it possible this record will be getting inserted not at all this record is not getting inserted at all so the reason is

[4:15:59] for this employee name right I specified one keyword what is that keyword not what is the meaning of not null so the null values so if I'm trying to execute this query

[4:16:15] so ultimately it is giving so an error right what is the error so column and name so cannot be null so the particular column so cannot accept what is that the null values like that. So not null means what? So the

[4:16:30] null values. Here I'm specifying for suppose Here I'm specifying for suppose uh some name rajan so rajan at the rate of sogmail.com or something.

[4:16:46] Now look at this. This one is unique and also not null and this name is not null 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

[4:17:02] on the query. So this email should not be uh email must be unique. It does not 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

[4:17:19] is successfully inserted. Now we can check. So if I'm writing so select star from employee. So what happens? It is working fine as expected. Correct? It is working fine as expected.

[4:17:34] So we are getting so employee number, name and also email like that. is unique constraint, what is not null constraint and all.

[4:17:47] 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 is that once primary key so could you please confirm

[4:18:01] questions please feel free to unmute and speak with me okay otherwise I cannot speak with me okay otherwise I cannot understand right

[4:18:14] 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 [snorts] remember that point

[4:18:27] 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:43] think about So the table level what is table level actually already I discussed right table level already I discussed right table level means what if you want to assign.

[4:19:00] 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:17] [snorts] right so for more than so the table level for example so we have an employee number

[4:19:38] I want to assign the constraint 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:19:51] cannot be repeated right so for example so one sur so same similarly again I'm repeating so one this one does does it works no because the same set of values cannot be repeated

[4:20:07] because this one is treating a set of values like that but if I'm changing So one rash per suppose this one is valid.

[4:20:20] 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:32] So again one sud cannot be repeated. So first we are having one sud and next it 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:46] 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 let's see how this one so let's assign so a primary key for first unique key

[4:21:02] so a primary key for first unique key for this employee number and name. So the employee table I'm dropping it. I need to check for what is that one. So

[4:21:17] I need to check for what is that one. So assigning

[4:21:31] table level per suppose. So previously we applied at the column level. So now we are applying at what is that once the table level. So first I'm dropping the table. So before starting it. So first I'm

[4:21:46] present. So what is the command we can use? Show tables. This command is mainly for displaying

[4:21:59] employee table is which is already present. But I want to I'm creating so employee table in right. So what I'm doing? So I'm dropping this table. So what is the

[4:22:12] command? So we can use drop table. 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 the what is that one? So the

[4:22:27] composite. So composite unique key. So composite what is that one? So unique key like that.

[4:22:43] 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:22:59] What is this type? So worker of 100. and also the email. So which is having so the employee number and also

[4:23:20] and also email. So for this employee number and name so I need to assign. So name I I need to assign what is that one unique constraint. So for this we are using one keyword. What is that keyword? So constraint and

[4:23:36] constraint name. What is the constraint name? That is your wish. We can provide any name but I am recommending so we can provide some meaningful name. per suppose for example I'm assigning unique

[4:23:50] 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 is that one so e number name underscore UK uk stands for what so unique

[4:24:05] constraint unique key we can feel like that and otherwise that is up to you you that and otherwise that is up to you you can provide UC unique constraint it's a best practice Right? Because after creating if you want to refer to

[4:24:19] this name. So e number name uc what is the constraint we are assigning unique of

[4:24:32] unique of so e number name here try to understand we have created what is that one so one and the name of the constraint is what is that one so e number name uc

[4:24:49] and which columns we are assigning the constraints so e number common, name for assigning what is that one? So unique constraint like that. So just execute this one. So you can run this code. So you can

[4:25:03] able to see. So the table is successfully created. I'm just sharing this query. So please look into that query. So for your understanding because this is the first query which we

[4:25:16] are writing at the table level, right? So that means we are assigning a constraint. So for more than one column I just straight that query in the chart. So please observe that one. So now I'm trying to insert the data.

[4:25:34] unique constraint and what are the columns as a combination of E number and name I assigned what is that one? So unique constraint. So now let's try to insert.

[4:25:48] So now let's try to insert. So the data per insert into first check so show show tables right so first check show tables whether the tables are getting created or not if I'm executing

[4:26:02] show tables I can see so the employee show tables I can see so the employee table is getting created created so once the employee table is created so

[4:26:16] now we can try to insert the data insert into what is the commands of the into what is the commands of the employee.

[4:26:28] 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:43] okay so the first record will insert successfully so without having any right this one will execute so without having any problem and also to see that records So you can just go there. So select star from what

[4:26:59] 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 that question?

[4:27:14] 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 is that one for the

[4:27:30] male for suppose. So the male I'm keeping it as Ramon. Now can you please tell me this one works? [snorts] Could you please type it in the chat if this works?

[4:27:52] I have written so chak I will explain it again so no worries that is the name of the constraint name of the constraint is different unique is different unique is the actual keyword which we have to

[4:28:05] assign which we are using to assign unique constraint name of the constraint which we are using for our purpose I will discuss about that one over first please concentrate here so first our concentration is whether

[4:28:19] this one will execute or not if I'm running this query whether this one will execute or not it does not work right because with the same set of right because with the same set of values already one record is present we

[4:28:33] can run this code and verify so look at this it is giving error what is giving so I'm just pasting this one in notepad so for your better visibility

[4:28:46] I'm just keeping this one in the notepad so I will show you what is the exactly so I will show you what is the exactly error it is facing

[4:29:05] So duplicate entry one-on 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:19] 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

[4:29:31] so previously one ROM but uh same one is 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 ramen so now this one works without having any

[4:29:47] problem without any error. So now look at this. If I'm trying to execute this query so I am not getting any error right so this one works fine as So now I'm writing so select star from employee

[4:30:05] so the record is getting 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 employee number and name the same set of values cannot be

[4:30:21] 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. Is it clear all of you? What is

[4:30:35] composite key? So we understand about so what is So we understand about so what is composite and what is normal key right? We understand about what is the normal constraint, normal way of applying

[4:30:47] constraint, normal way of applying constraint

[4:31:02] applying on only one column right might be you're applying on so employee number or somewhere but composite key means what? So more than one column for example so employee number and name

[4:31:16] we are applying so single unique constraint or primary key whatever it constraint or primary key whatever it may be but now I'm talking about so the foreign key

[4:31:31] so try to understand so what exactly foreign key how it behaves this is super important for example let's assume that so we have for example let's assume that so we have an employee table

[4:31:44] 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:10] and also salary. and department name

[4:32:22] So is it possible to store all these details in a single employee table? but the problem there are some problems might be there. For example, what are

[4:32:35] the problems? For example, one employee number and name is number and name is Sams surregmail.com

[4:32:47] and department name is so analytics department per suppose

[4:33:01] 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:13] and also email is something and salary is something and he is also working for what is that one? So same analytics department analytics department and it is also located in same USA

[4:33:31] but try to understand so for example so we have another record

[4:33:43] and he belongs to analytics department only but his location is different per only but his location is different per suppose. So SF, San Francisco

[4:33:57] or otherwise New Zealand or some some other country per suppose NZ. So but what happened here the same analytics department is repeated again and again. So same department name is repeated

[4:34:10] So same department name is repeated again again and again. 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

[4:34:25] uh in India might be there is a gumato app right application or swiggy those are all food order applications like Amazon

[4:34:37] 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

[4:34:49] first question. Is it correct? Are you agree with me? So is it possible to enter store the entire data in a single table? It's not a good practice.

[4:35:01] Why? There are some problems. What are the problems? So the same values cannot be repeated again and again. might be I'm purchasing a laptop. So for suppose

[4:35:13] uh Lenovo laptop some model the same laptop will be purchased by many people. So the same report same records will be repeated again and again. So this

[4:35:25] problem we can call it as data redundancy problem. So data redundancy. So if you are observing so the same analytics observing so the same analytics department is repeated again and again.

[4:35:37] So for that one what we have to do as a database designer database designer we have to perform some normalization. So what is normalization I will discuss theoretically might be in the next

[4:35:49] session. So in the tomorrow session normalization means overall high level we can try to understand it is not possible to store the entire data of possible to store the entire data of your application in a single table. For

[4:36:03] for Amazon. Amazon contains many tables. It's not possible to store the entire data in a single table. For that, so normalization

[4:36:15] is a process of decomposing, right? Of decomposing a big table into what is that one? So smaller tables.

[4:36:37] to reduce radar redundancy to reduce data redundancy

[4:36:50] 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:03] So I'm not talking about what errors we are getting right now. What I'm doing is the employee details and also department details and location details instead of keeping all the details in a

[4:37:16] tables into what is that one? So 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

[4:37:30] one? So the table so we have so employee table and also location table. So we are creating so three different tables.

[4:37:43] This employee table contains what are the fields? Employee number, name, email and department table contains department ID and department name

[4:37:55] 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 veracular or MySQL or any database every table is

[4:38:09] 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:23] 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:36] values and primary key does not allows duplicate values. So we have so these tables for suppose and we have department table and we have

[4:38:48] and we have department table and we have added that on the location table. So the location details might be stored in a separate one. So location ID 101 and location name is USA and 102.

[4:39:08] right? So NZ and we have so department. are there for suppose one is analytics department.

[4:39:20] department. So for example so analytics department So for example so analytics department and uh another department might be what is that one? So the programming department

[4:39:38] and we have employees but suppose employee number so name is so Ras employee number so name is so Ras so he's having some email id in a similar way so Raman or Ram is having some email id

[4:39:53] each other for example let's Say particular department for example so Raj is working in

[4:40:07] 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:20] extra column in employee table so that is department number column so 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:35] what is that one the primary keys that means if Raj is belongs to 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:49] 10. 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.

[4:41:02] Okay. So reference refer to the another 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

[4:41:15] 10. So this foreign key references what is that once the primary key in the similar way. So the department is located in what is that one? the New Zealand perose right. So the location ID if you're

[4:41:30] keeping here this location ID we can call it as what is that on the foreign key this location ID refers to what is that one location table primary key. So please remember

[4:41:45] foreign keys always refers to what is that one 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 I am keeping so that means

[4:42:02] I am keeping so that means Raz is working in analytics department and he belongs to the location so New Zealand and in the similar way So Ram is also working in analytics department only

[4:42:16] 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 that means USA location and Raj belongs to New Zealand. So Ram is belongs to

[4:42:33] what is that one? So USA location like that. So in this way so what do you understand? It's not a best practice to store the entire data in a single

[4:42:47] table right in a real life you have. So if you are working with the real life data right your data will be stored in what is that one the multiple table not like excel sheet in excel sheet what you done might

[4:43:02] be already undergone excel training we have entire excel sheet might be that excel sheet is containing entire data so what we have to do we have to use the what we have to do we have to use the normalization process

[4:43:19] to split that table into what that one. So multiple tables we have to identify the entities in the tables. But 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

[4:43:33] our application and we can divide that one into what is that one multiple tables and we can provide the link between the tables with the alpha foreign key. Yes, we have to link the tables with the

[4:43:49] help of foreign key. identifying relationships it 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

[4:44:05] want to transform that one you want to 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

[4:44:19] pointing According to the domain, we have to According to the domain, we have to identify the entities in that data set. If you are taking HR data set, so there might be employees are there,

[4:44:31] departments are there, locations are there. Okay, there are so several entities will be involved. We just divide that entities and we can create a separate tables. But while after dividing the entities,

[4:44:46] not only dividing the entities I'm speaking. So we need to identify the speaking. So we need to identify the relationship between the tables also. relationships so listen this one gives more knowledge

[4:45:00] for you after dividing the tables the tables. If you are talking about relationships, If you are talking about relationships, we have so one to one relationship

[4:45:19] and also we have a one to many relationship. Correct?

[4:45:31] 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:48] relationships we have one to one relationship and one to many and many to one and also many to many relationship.

[4:46:00] 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:17] details in a single table. So employee table contains employee details like employee number, name. So address or email

[4:46:29] and passport table contains what is that one? So the passport ID, passport number and expiry date for suppose

[4:46:42] 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:46:58] 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:13] So we are not storing the data of 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

[4:47:27] that one? So single employee table and also passport table separately. we have to create two tables means there is some problem like beha. So what is the problem? So data redundancy problem. There are

[4:47:44] about that problems. what problems we are getting okay so otherwise you can just visit the self-arning so we can able to understand what exactly normalization I will

[4:47:56] discuss maybe tomorrow I will uh show you the slides and I will explain what you the slides and I will explain what exactly normalization process like that but in this case we have to identify as a engineer you have to identify what is

[4:48:10] the relationship between these two tables relationships right so identified this one is so one to one relationship one to one relationship means what one

[4:48:23] instance of employee is associated with what is that one so one employee one instance of passport so for example so we have so the passport ID and passport number and also expiry date

[4:48:49] these instances for suppose the s passport is this is the passport passport ID one in such cases where we can create foreign key so either employee table you can create foreign key

[4:49:03] or passport table we can create foreign key that is up to you here I am making so employee is the owning side of the relationship so we owning side of the relationship so we have to decide what is that one So

[4:49:20] warning side of the relationship. What is that one? So warning side

[4:49:36] either we can do the warning side as employee or passport. For example, if I'm doing owning side as employee, what I'm keeping? So the passport ID as a foreign key I'm keeping. I'm keeping. So this passport ID as a

[4:49:50] foreign key. This foreign key references what is that one? So the primary key So if I'm keeping one here or otherwise so you can maintain so different

[4:50:04] 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:18] so or otherwise 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 so either we can keep it so the employee

[4:50:33] employee number I'm keeping it as foreign key here. key. But this foreign key references what is that one? So the primary key for example. So this foreign key references this primary key.

[4:50:49] That is also possible. So that is your wish. We have to decide. So which entity is the owning side of the relationship? That is the super important step here. What is the entity I'm choosing as a owner of the

[4:51:03] I'm choosing as a owner of the relationship in this context? of the relationship. So that's why I'm keeping so the passport ID here. So this is acting as what is that once the foreign key

[4:51:20] which is mainly for providing the reference between these two tables. So please remember and keep in mind. So very very important point foreign keys are always referring to the primary keys. Correct? Foreign keys are

[4:51:33] always referring to primary keys. That means we are just providing a reference 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:51:52] 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:05] 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 key relationship sorry what is that one so one to one relationship

[4:52:19] so in real life also employee one employee is having one passport or multiple passports might be that is the business case right that is the scenario one passport In such cases we have to provide one to

[4:52:33] one. Can you please tell me if it is one to one? So the for this foreign key colony you must assign unique key also. Right? It is foreign key and also as well as what is that one? So we have to an extra

[4:52:48] constraint? 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?

[4:53:03] So two ROS and also email ID again is having so 101. So two people are having same passport number. Are you understanding what I'm speaking

[4:53:16] 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

[4:53:30] by extra we are assigning what is that one? So unique key. So that means uh this 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

[4:53:44] one to one mapping. Yes, you are open to the questions. Please start your questions. All of you understand what exactly one to one mapping.

[4:54:01] 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 toone mapping? for one toone mapping? One employee one passport that is okay.

[4:54:13] Any other scenarios for suppose one employee is having employee is having one other card number correct? one other card number correct? Any others

[4:54:32] Very good. But one employee is having multiple account numbers, right? Sub one employee is having some multiple account numbers. Yesi please go ahead.

[4:54:44] 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

[4:55:15] 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 we the employees not having passport. So in such cases

[4:55:29] what this foreign key will allow null value. Okay, there is no need to specify explicitly null. That is one good That means might be employees having passport. If employee is not having

[4:55:42] passport, I cannot say always mandatory. So the employees having passport, right? For example, so if you are taking uh uh some some people so those who are working in domestic

[4:55:56] perose they are not having they don't want any passport per suppose assume that in such cases what happens for that employee so this value should be null. So foreign key also allows what is that

[4:56:11] So foreign key also allows what is that one null values also. employee not required. So we are just assigning a foreign key. So the foreign key by default. So if you are taking so the foreign key right.

[4:56:29] also there is no need to specify. So null explicitly for 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

[4:56:43] suppose unique unique means what? So the same set of values cannot be repeated. In the case of unique also it will allow null values. What is the purpose of keeping so separate foreign key for each record?

[4:56:57] Not required. So on the top of column one itself you can mention that one as foreign key. Correct. Is it clear Swati your question? Sorry any what exactly this is your question.

[4:57:17] So foreign key it will alone all values also. Okay. So the next step is what is that one? one to many but one question I am asking before going to this one to many can you please

[4:57:29] tell me in the case of one to many relationship right in the case of one to one per suppose

[4:57:42] so we have to decide so warning side of the relationship right side means what so that is either employee side

[4:57:57] so the passport side who will decide that is your wish we can choose okay according to your uh right your

[4:58:09] requirement in business we have to choose the warning side so either we can keep so that one in the employee or in the passport shooting it What is the purpose of providing this foreign keys?

[4:58:25] Why we have to provide this foreign key? For example, let's say in your application, if you uh suppose you are working as a HR, we are searching for an employee.

[4:58:39] 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 employee personal information the employee pay pay scale information and

[4:58:54] everything we need to get. So pay payroll information will be present in payroll table. Employee information is present in employee information is present in employee table. But still uh the employ still the

[4:59:07] HR knows only employee details. From that employee details he can able to that employee details he can able to fetch he or she can able to fetch. So the pay payroll details and other details as well right. So that is the

[4:59:19] 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:32] 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:47] 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:02] suppose for one to many anyone can you please tell me one scenario for one to We are all students for [snorts] suppose as a part of this course students and courses that is not one to

[5:00:17] many right h one employee is having so multiple phone numbers that is correct [snorts]

[5:00:29] so let's analyze but suppose might be one one of you mentioned like student and courses it's not one to many why it It is not it's not one to many why it It is not one to many

[5:00:44] 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:00:58] 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:12] 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:25] many relationship. Surash. Okay. Uh customers and orders also. Okay. So you are talking about customers and orders, right? So let's rethink. So think yourself okay it's one to many

[5:01:39] So think yourself okay it's one to many only it's not many to many sorry surash 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:01:54] is the relationship between the customers and products under one to many many customers can order many many products.

[5:02:09] 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:29] 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'm just

[5:02:44] designing it here so please look at this 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:02:57] 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:24] so here I'm specifying so what is the table name so the customer table 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:49] and also as well as a customer address. Okay. So these are the three fields orders table per this customer is placed how many orders

[5:04:05] 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:18] table I'm just double clicking here I'm just double clicking here so I'm getting so the orders table

[5:04:33] 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:50] 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:02] type integer type so we have so these two tables relationship one to many relationship Right? Please listen. So in one to many

[5:05:16] relationship always it is a best practice. Okay. So it is a best practice to keep foreign key on many side. What is the

[5:05:28] many side here? So one customer can place many orders. Correct? The same order cannot placed by the another customer placing an order means the order ID will be different. Right? So for every

[5:05:43] 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:05:55] create the foreign key. For example if you are going here. So if you want to specify the relationship here

[5:06:07] have so 1 to n. So one to end relationship per suppose I can select this one. So first I'm selecting order and after that I'm clicking on customer. So look at this the foreign key is

[5:06:23] created here or not. So in the orders table so the foreign 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:35] treating as a foreign key. that foreign key is referencing what is the primary key. So customer table. So customer ID as a primary key. So might be you are asking me question

[5:06:50] why you are creating foreign key in order 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:02] key right. So many side always you 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 ID if you want

[5:07:17] to 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 so if you're observing so this one is red color right so this one is called as

[5:07:33] what is that one the foreign key this foreign key is referencing so the foreign key is referencing so the customer id prim primary key like that. So this is the way [snorts] we have to specify. uh one to many relationship

[5:07:50] one understand all of you do you have any questions you can unmute and speak with me and also try it how to go how to create a model just go to file new model

[5:08:02] okay so then automatically it will open that one if anyone is not getting understanding so please share your screen I I will help you how to create this year diagram stands for what entry relationship

[5:08:14] diagram. Entity relationship diagram. Is it clear all of you? All of you can able to follow up the session.

[5:08:29] All of you can able to understand. So what is one to many relationship? You don't want to worry about so drawing diagrams but try to understand the concept first. What is one to many relationship and how it works.

[5:08:53] cannot understand. So what exactly where ID key will appear? >> Uh sir actually I didn't uh uh install secure. >> Okay. Got it. Got it. Got it. >> Sir my question is um you said that uh

[5:09:07] >> Sir my question is um you said that uh there are some uh ID uh appear. So where is the I uh foreign key sorry where is the foreign key appear so we can recognize >> it's many side many side always right

[5:09:19] first if I'm just going back but suppose look at this >> there is no relation now correct >> yes you have to make a decision as a database designer correct

[5:09:34] database designer correct >> so where I can keep foreign key first Identify the relationship between these two tables customer and orders. You identified as it is one to many.

[5:09:48] 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:02] So that's why you can just go there and select we have so relationship symbols also available here.

[5:10:19] So 1 to n you can see. So 1 to n right just click 1 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 red color

[5:10:34] foreign key is referencing what is what is that? This foreign key is referencing so the primary key. so the primary key. So customer c this one is a foreign key

[5:10:48] that is referencing what is that one? So C column like that C column like that >> sir can we change this um foreign key or can we like >> keep it in customer you are asking

[5:11:00] >> Yes. That's not a good practice I'm saying. Okay. So the reason is in the case of one to many relationship always many side even if you are observing so many design database designs right. So always

[5:11:15] you are 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:29] 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:42] But uh when coming to the frameworks right I am an application developer. I designed databases. In my experience I identified what is that one always many side we have to keep the foreign key that is a

[5:11:55] best practice that's what I am saying you can observe so many databases in uh uh by asking in uh what is that one so charge GPT for example if I'm asking in charge GPT for example if I'm asking in charge GPT for suppose

[5:12:12] so I'm just opening so charge GPT

[5:12:37] 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:02] 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 for

[5:13:16] suppose in the similar way. So one customer can place many orders right many orders. So each employee belongs to one department. So the order whatever if you other side if you are taking one

[5:13:31] 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:43] 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:13:55] a foreign key uh primary keeper suppose. Okay. So the logical reason is so if 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:11] 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 able to

[5:14:26] understand. So what exactly the problem? Okay. So you can just go through that discussion tomorrow also then you can able to understand.

[5:14:43] question. Could you please unmute and speak? speak? Sir can you show the linking part once again? How did you link both? >> One second.

[5:14:56] linking part in the sense which one you ask the foreign key and primary Kra. >> Okay. This one only right? >> Yeah.

[5:15:09] arrow one? >> Nothing. It's uh so just I'm just >> Nothing. It's uh so just I'm just deleting existing one.

[5:15:22] >> Yeah. Here we have so the links are there one to one one to many and all >> Okay. >> You can select so one to many in a strong way. So not like dot dot dot 1 to n. First you can select the orders

[5:15:39] because one order belongs to one customer. >> Yeah >> one customer can place many orders. So here orders I am selecting and also customers I am selecting.

[5:15:53] So automatically what happens there is a relationship will be established between so the customers and orders one order can uh one customer can place many orders in the customer ID is allocated as

[5:16:07] >> customer ID it is taking automatically when you have the >> automatically it will select okay that is the design right the table will uh uh >> that is the good thing about what that one workbench like that

[5:16:22] >> I think others is asking so sir should not the quantity type be in integer. So 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

[5:16:37] you have to specify it as integer correct the reason is the quantity 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

[5:16:51] we have to keep that one as a what is that one so separately foreign key or primary key in the many head. Okay. So that one you can just do some research. Okay. So always it is a best practice we can specify

[5:17:06] what is that one the many side again I'll come back to the discussion I'll come back to the discussion and last one what is that one so many to many relationship if you are asking me this is one to many and many

[5:17:21] to one correct could you please tell me so this relationship will cover what is that one so one to many and many to one if you are having order details you can Correct. We can able to get the customer details

[5:17:37] and if you're having customer details then we can able to get what is that 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

[5:17:50] the orders the customer placed all those details we can able to get it 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:16] what is that one? So many to many what is that one? So many to many relationship. So if you're asking me about so many to many relationship, right?

[5:18:45] many. So how it's looks like more to one? >> Yes. >> So I'm just speaking like an application point of view. Right. See you are not inserting so data manually. Who will ins

[5:18:59] how you are inserting the data? Could you please tell me? I think uh good discussion actually for suppose in real life how we are inserting the data you are booking a flight ticket correct for suppose if you

[5:19:14] want to travel you are booking a flight ticket. Let's take some example how you are interacting with uh uh the database through some application

[5:19:26] uh the database through some application might be you're going to some website okay so the flight booking website flight booking website not Google and all if you're going to the directly flight booking website and we are

[5:19:38] providing your details and your booking correct so your details will be you are a customer for that airlines lines and flight details will be stored in a separate table per suppose correct

[5:19:54] separate table flight details will be stored in separate table 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

[5:20:11] so what happens means so your question comes into the Right? What is your question? So I am having order details. So I specified foreign key here for suppose let's say

[5:20:25] here I specified foreign key in one to one. What where I specified foreign key 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

[5:20:40] details how can I fetch the employee details? Whatever the frameworks we have available in the market programming frameworks I'm talking about frameworks are having are suggesting so don't

[5:20:57] 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 foreign key

[5:21:11] that one decreases the performance performance. So internally the frameworks which we are using in programming. So which we are using in programming. So those frameworks will support you

[5:21:25] 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:39] 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:21:52] foreign keys on both sides if you are creating foreign keys on both sides right so automatically it will degrade the performance the performance so it will decrease the performance like

[5:22:09] foreign key in the reverse mapping also that means if you're having a passport from the passport if you want to get the employee in the reverse mapping also we'll use the same foreign key okay we'll use the

[5:22:23] 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 to store the

[5:22:37] application data only. Correct? To store the application data. Whenever application programs are interacting with databases.

[5:22:51] create what is that one? So multiple foreign keys on both sides. We'll use the same foreign key to refer from reverse side as well. Hope you understand Swati. So what I'm saying is it clear

[5:23:15] fun of multiple things what happens automatically your performance will be So let's think about another table per suppose many to many relationship suppose many to many relationship what is that one so the customers

[5:23:33] 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:48] relationship between the customer and products Anyone can you please guess it?

[5:24:02] 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:18] 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:36] 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:24:54] 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:07] So there is a separate table will be created. So this one is called as might be customer products. So one separate table will be created. So customer what is that one sub products.

[5:25:31] we can able have we have so two foreign keys. one is customer ID and another one is what is that one a product ID. product ID. So this product ID is a foreign key

[5:25:50] 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 key. So we have a junction table. This one is called as what is that one? junction

[5:26:04] called as what is that one? junction table 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

[5:26:18] for example so the customer ID it is having some name and also email having some name and also email and product ID is also 101 which is having so some product name. If customer is placing an order for a

[5:26:32] particular product, so customer ID and also 101. So customer so customer ID and also 101. So customer is two per suppose

[5:26:46] and also he also placed so the product order for suppose. So product ID. So two customers placed for same product, right? Same product like that same product will be ordered by many customers.

[5:27:04] 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:19] 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. So let's see after that we'll see.

[5:27:35] So let's see after that we'll see. So let's create a product table. So I'm just clicking on so that this uh table and I'm just double clicking on table and I'm just double clicking on here or otherwise just drag and drop.

[5:27:56] coming. This table name is what is that one? So products. [snorts] So what are the fields will be present in this table? So we have so product ID

[5:28:27] Right. So the name of the product and also the price of the product. products so what type of mapping I need to

[5:28:42] provide between these two. So I'm providing so many to many perose. So many to many mapping I'm selecting customer and also products. What happen?

[5:28:55] So a separate table is junction table is getting created or not 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

[5:29:10] so I'm just changing the name to what is that one. customer products. table right so this one contains customer ID and

[5:29:26] also products ID these two are foreign keys you can see here so this products ID references what is that one products table and customer ID references what is that once the customer table customer ID

[5:29:42] so these two are acting as what is that once the foreign keys if you are designing a database for suppose if a customer is placing

[5:29:57] uh an order for a particular product, the order details will be stored in a separate table and uh the mapping information. So the customer is placed for an placed an order for a particular product that one

[5:30:12] is mapping information will be stored in what is that one separate table. relationship only especially in the case of many to only especially in the case of many to many relationship only. So we have a

[5:30:27] 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. Is it clear all of you? All of you can able to understand. So what I am

[5:30:41] speaking up to now I just want to hear some questions from you. So please start some questions from you. So please start asking questions.

[5:30:54] could you please tell me what is the example I provided here? So employee and example I provided here? So employee and passport is it correct?

[5:31:06] So the employee and passport one employee is having so only one passport. So where could you please tell me where you can provide the foreign key? That is my question in employee table or passport table.

[5:31:21] in employee table or passport table. What is your opinion? So this is uh the one right. So employee table and also passport table. So in

[5:31:35] foreign key or in the passport table I have to provide the foreign key that is your wish right? >> It's up to us sir. >> Yes. So let's connect to the MySQL workbench. Let's see how I am creating

[5:31:53] workbench. Let's see how I am creating uh how I am connecting these two tables. So let's connect with this workbench. I am just providing for the password. So am just providing for the password. So something wrong password I entered.

[5:32:15] command? Could you please tell me? So to drop this database. So drop database. So database name. What is the database name? Employee DB.

[5:32:35] 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:32:52] So employee DB inside the employee DB I want to create so employee table and also passport table. But my requirement is in employee table I'm going to keep so the foreign key. So what is the table

[5:33:08] first I can create it is first I'm creating so the passport table. So creating so the passport table. So create table. passport ID whose type is what is that one? So the integer

[5:33:27] So this one is a primary key right? Primary key and also passport number. So this one is worker of 30 or worker of some some number suppose worker of 30

[5:33:50] So expir date is whose type it is date data type. you please 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:04] without using the database this query can execute? We can execute. No. First database we can create. It's already created. Next step is what is that one? So we have to use the database, right? What is the command?

[5:34:22] So use employee DB. This is super important without using the database we cannot execute the query. So now currently we are using so you can just refresh we can see currently we are using so the employee DB and after that

[5:34:38] so I'm creating so this passport table correct so just execute this query so the passport this is successfully created but might be you can observe so the data types here

[5:34:54] so the passport ID is primary key all of you know about so what is primary key primary key means one table 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

[5:35:09] table so that is super important thing we have to remember so just a second so we have to remember so just a second so I'm just connecting uh my pen

[5:35:28] 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? Unique plus not null as discussed yesterday. That means in the previous session.

[5:35:45] Is it possible to create some multiple primary keys? Anyone can you please tell me is it possible to create so multiple primary keys? No,

[5:35:57] No, we can create. So only one primary key. I think Arita you are getting the error. So the reason is might be you are not using the database right. You are not using the database. You created database

[5:36:12] 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 getting the error. you can unmute and speak with me. >> Uh sure sir, I'll just share it. So I've

[5:36:28] just copied the comment which you have pasted in the chat box, right? So able to >> Sure. I'm doing that. I'm doing that >> Yeah. >> Okay. So I'm just following uh from

[5:36:42] >> So what happen means like uh first you can check green color right? >> Yeah green color. Now it is red means it is not created. So what is the misting mistaken is

[5:36:55] you create a database you are not using the database correct what is the command 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

[5:37:11] >> creating a database that is [snorts] one option so after that what we have to do so we have to use we have to select the database right what is the command and I used here use employee DP correct

[5:37:28] so if I'm selecting so use employee DB 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

[5:37:42] refresh icon if you are refreshing so this employee DB is getting highlighted once you are executing after executing this command use employee DB. Now you can execute this query. So create table. So the

[5:37:56] passport 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

[5:38:09] and the date data type? Because if you're talking about so the expiry date so this one is going to be what? What type of data type? Date data type. So we need to insert the date.

[5:38:24] Is it clear Arita? What is the mistake you made? Or otherwise you can share your screen quickly. >> Uh yes sir. If you can just guide me one second. >> Yeah.

[5:38:44] Okay. Uh shall I write this uh >> before uh before that command? So you employee DB >> here right

[5:38:56] >> before that line that means line number two you can write it >> line number two okay >> use employee >> space space use space employee >> don't get confused okay no worries

[5:39:14] so put semicolon and we can just execute that query >> So now that is using employee DB, right? How can you know whether it is using employee DB? So go to the refresh icon and click it.

[5:39:27] This >> we are not watching my screen actually. So previously I shown right. So please look at my screen. >> This is called as my mouse pointer. You can observe

[5:39:41] 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:39:56] >> Team, could you please confirm my screen is visible? is visible? >> Yes sir, your screen is visible.

[5:40:08] 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 check this mouse pointer. So the refresh icon is visible,

[5:40:22] right? >> Just click on this refresh icon. >> it is automatically refreshing and it is displaying the employee D is getting >> Yes. Yes. Right, sir. Got it. >> So you just refresh it and employee DB

[5:40:37] out. >> Yes, it is visible sir. employee DB and then under that tables view stored procedures and functions. >> So now we can execute the query. So creating a table query it will execute

[5:40:50] tables. >> So shall I reenter that query again or >> You can just share your screen. You can share your screen might be

[5:41:11] see your query is still not executed 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 >> okay

[5:41:28] again. Refresh the tables. Now go to the tables. You can see your >> Yes. >> Arrow mark. Y mark. >> Your table is created or not? >> Passport.

[5:41:41] >> So I'm requesting please go through the previous session. Yesterday itself I >> Yes. Yes sir. I I went through but little more practice needed for me. >> Yeah. Yeah. No problem. Next week you can get it record. No issue.

[5:41:54] >> Sure. Sure. S. Thank you. >> Yeah. Thank you. created and then next please like the messages So now I'm creating so employee table. So create table employee

[5:42:15] integer. Of course this one is a primary key right? And also the name what is it type? So worker of 30

[5:42:41] so worker of 100 and email and at last so we have to provide the foreign

[5:42:54] Can you please tell me anyone? So what is the foreign key column? Passport ID. 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

[5:43:07] one? So the primary key. So that is your wish might be you're asking me so is it mandatory to provide the same column name. So not required. For example, if I'm specifying so the pass ID. So that means passport ID whose type is

[5:43:24] what is that one? So integer. So this one references. So the passport of show P I like this.

[5:43:42] is looking somewhat strange for you. It's not strange. Very simple. In employee tables I need to keep one extra column. What is that extra column? So column. What is that extra column? So the passport id.

[5:43:56] So 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 must be same not required. If you want to keep the separate name so

[5:44:10] 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:22] the p ID right so in employee table so this is the passport id is a column so that is referencing always foreign key is always referencing

[5:44:36] so primary key right so passport of p like this so Just execute this uh query. We can able to see. So that query is successful. So the table is getting created and relationship is established.

[5:44:52] 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 scenes that is super important.

[5:45:08] 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 primary key which is present in the passport table.

[5:45:24] So we are proving the relationship between the two tables. But additionally we have to provide unique also because the p foreign key value must be unique. Unique means what? It does not allow duplicate values.

[5:45:37] But right now so we can just execute this query message once you are done with the particular task

[5:45:50] foreign key. So do you have any questions? questions? Is it okay everyone? You can unmute and speak with me if you are not understand anything.

[5:46:05] Just I'm keeping an extra column where I am keeping an extra column. So in the employee table that is your wish either we can keep it in employee table or in passport table. Yesterday I discussed right in the case of one to

[5:46:19] 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:33] in such cases that is your wish either we can keep foreign key in employee table or either we can keep so foreign key in passport table. So that is up to you. And also the column

[5:46:49] names also might be deferred. You cannot say so the column names in employee table should be matched with the passport table like that. Okay. But one important thing so we have to specify what is that one. So unique

[5:47:05] one extra constraint in the foreign key we need to provide in the case of one to one relationship. What is that one? unique constant we have to provide could you please look into that one if

[5:47:17] you are not understanding so please ask me unmute and speak with so please ask me unmute and speak with me I will explain it again

[5:47:33] so now from the yes please >> uh sir if uh uh um I want to like address address.

[5:47:48] one to many at that situation we have to go for one to many correct so we have to create a separate address table because one person is having many addresses correct I cannot say one

[5:48:02] person is having only one address one 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

[5:48:15] always many side We have to keep the foreign key. Did you remember that one? Yesterday you joined you asked so many questions rel related to one to many and >> Yes. >> Uh did you remember that concept?

[5:48:27] >> how that one works. >> Okay. >> Okay. >> Yeah. query. So is it clear right? How it is working and all?

[5:48:43] working and all? So how the foreign key will be defined one employee is having many addresses. So the same scenario we can take it for example. So as discussed so Swati is requesting right.

[5:49:01] So one employee is having many addresses. created. Right? So for example let's say so we have an employee table

[5:49:14] employee number, name, address and also email and also we have address table. So we cannot keep address here. Correct?

[5:49:26] cannot keep address here. Correct? Employee number, name and also email. >> 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:42] Thanks for uh letting me. My screen is visible right right now. also email it's visible right?

[5:49:58] >> Yeah. Okay. And also there are some properties might be you can keep if you want for suppose gender [snorts]

[5:50:10] are 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:24] 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:39] 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 to one means what one address is associated

[5:50:53] with only one 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 a address.

[5:51:10] So some other so landmark per suppose some other fields. some other fields. Okay. So these are the fields. So now tell me where I can create. So the foreign key what are the primary

[5:51:24] 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:36] in any table or 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:51:54] treated as what is that one the primary key email and also vendor and in the address table so we have ID and also address and

[5:52:09] also the landmark. So these are all what are the non- primary keys. [snorts] So one employee is having many addresses in such cases always it is best practice

[5:52:21] to keep so foreign key so this is acting as what is the one 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:40] best practice I'm saying uh if you are working as an application developer right So you can able to understand while working with the frameworks always we are keeping so the foreign for foreign key in many to one to many

[5:52:52] foreign key in many to one to many relationship at many side. So this address contains many employees right. So might be this employee is ID is getting repeated again and again.

[5:53:09] So one to many relationship. So one to relationship means what? So one employee is having

[5:53:22] but the address belongs to only one employee only other side. Is it clear? So can I implement this one? So I already have employee table.

[5:53:35] 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 refers to what is that one? So the employee number primary key.

[5:53:51] So let's observe here. So we have already tables.

[5:54:03] I'm just doing so describe employee perose. name? So the employee table could you please check it? So what is

[5:54:17] the what we are getting? Of course we are getting the employee 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:29] 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:46] address column. Correct. Because I don't want address. Add address. I need to want address. Add address. I need to maintain it in a separate table. It is successful. So now if I describing employee,

[5:54:59] 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:17] one address column. So I I'm not interested in keeping address in employee table. So I want to maintain it separately. Correct? separately. Correct? So for that one what I did here

[5:55:31] 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:46] So now this employee table is free. It does not contain any address column. The next step what I have to do is I need to create an separate address table which is mainly for keeping the addresses.

[5:56:09] associated with some ID, right? Unique identification number. So ID is integer and this one is going to be what is that one? The primary key

[5:56:21] to be what is that one? The primary key and also the actual address this is 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:37] worker of 30 or of 20. But this address there is a relationship with the employee table right. So one with the employee table right. So one employee is having so many addresses.

[5:56:52] So what is the many side? So address is the many side. Here we have to keep what is that one? So the foreign key that means I'm keeping. So like simply means I'm keeping. So like simply emphasization

[5:57:17] please remember this is important point while creating the foreign keys right 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

[5:57:32] employee number E number is the primary key. So I'm not expecting so the foreign key name also should be same as primary key name right. So just I'm keeping so the MP ID. So which whose type is what is that one?

[5:57:46] So integer. So this one is going to be fr that means this one is referencing. So employee of what is that one? So employee number as discussed

[5:58:03] foreign keys are always referencing what is that one? So the primary keys. Okay. If you're asking me why what is the purpose of creating foreign key means foreign keys always referencing what? Primary keys.

[5:58:16] Here employee ID is a foreign key that is referencing. So the employee table employee number. So that's all. So automatically a relationship will be established between these two tables.

[5:58:31] So this query is successfully executed. So the relationship is getting established. But what happens in address table we have an extra column which is nothing but employee ID. So while giving the

[5:58:45] employee ID so that should be matched with the existing employee number. 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 that task.

[5:59:04] So this is called as what is that one? So one one to many relationship correct. Can you please anyone could you please tell me another example might be many one employee is having many addresses

[5:59:17] one employee is having multiple mobile numbers might be mobile number table right so first complete this one

[5:59:32] understand in a better way so I will write another example how to manage one to many relationship so might be one person one employee is having so multiple mobile numbers or one employee is having so multiple what we can say um

[5:59:50] vehicles right there that is also a relationship one employee is having multiple vehicles so I'm having so uh two bikes and also one car perose that means as an employee I am having

[6:00:07] 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:19] 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:35] 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:48] remaining people. I am requesting everyone should be on I am requesting everyone should be on the same page. Yeah Swati sir sir. >> Yes Swati please go ahead. Yeah >> sir in alter table s so you just write

[6:01:04] alter table employ drop column but uh uh in my laptop in my uh SQL break uh in my laptop in my uh SQL break uh workbench it's not working it's a cross error code >> okay so it's a safe update safe update

[6:01:19] we are getting error is safe update related to safe error is safe update related to safe update correct What is the error we are getting? So

[6:01:32] 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 so I'll help you. So >> I am I'm uh uh like I'm logging in from

[6:01:46] my phone. Uh that's >> okay. So that is the biggest challenge >> yes. >> Yeah. So I cannot see your screen means break time you can connect to your

[6:02:00] can able to provide the solution for you. So please make sure that you can just log from your laptop itself then it would be better to interact. >> Actually here some uh network problem that's why uh in laptop lots of uh I can

[6:02:19] that's why uh in laptop lots of uh I can face that's why and login from my phone. Okay, I >> you can share your at least screenshot screenshot in the like that chat. Maybe I will look into that screenshot.

[6:02:32] >> Yeah. So here look at this. I'm just creating so vehicles table. Same scenario right. So one employee is having so many vehicles.

[6:02:45] So how can you write it? So create table vehicles. it should be what type? So it is a primary key

[6:03:04] vehicle type or suppose that is motor bike or otherwise the two wheeler or four four-wheeler like that. So type it is worker of 10 again

[6:03:21] and also we can specify some other things right. also the vehicle type and also uh what other things we can specify

[6:03:34] here. So in the vehicles so might be the price of the vehicle. So price of the vehicle should be integer. But try to understand so this vehicle belongs to what is that one employee right? So what is the

[6:03:48] vehicles? One to many relationship. So one employee is having so many vehicles. So in such cases

[6:04:00] so what I have to provide? I have to provide so employee ID. provide so employee ID. So this is an integer. So this one is referencing. So references employee table of what is that one? So employee

[6:04:15] 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:30] We need to provide the relationship between these two tables. In such cases always in one to many relationship I recommended always many relationship I recommended always many side we can keep the foreign key right

[6:04:43] it's always best practice but suppose if I'm creeping so employee ID in vehicle table so that one is referencing what is that one so the employee of employee number so if I'm executing this query

[6:04:59] executing this query so this query is successfully executed so this query is successfully executed so Now we can just uh refresh these tables. What are the tables are present?

[6:05:11] Look at so we have employee table and address table and also passport table and vehicle table. So for this employee table if I want to So for this employee table if I want to draw a diagram per suppose

[6:05:25] draw a diagram per suppose here look at this. just want to see the diagram per suppose

[6:05:38] 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:51] from this tables [snorts] >> sir for wait please wait 5 minutes >> okay no problem >> I'm just I'm just doing this. >> See the problem is try to understand actually

[6:06:06] >> 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 numbers wherever you are mentioning 100 50 20 10 I mean uh I'm just uh h little

[6:06:22] clueless I mean how you uh putting these numbers is it random or is there a strategy behind this? So just randomly I'm putting for suppose if you are taking uh uh for example so gender for gender only one character is

[6:06:38] sufficient in such cases we are just putting car one like that correct >> care of one so single character male or female female >> for generally okay

[6:06:52] >> so if you're generally so data type for suppose if you're talking about what is data type actually so data type represents the type of data. Correct? >> Yes. >> So, what type of what are the different

[6:07:06] types of data we have? Name. Name is what type of data? String >> Okay. >> And also salary. Salary is what type? >> And also salary. Salary is what type? Salary might be this as an integer or

[6:07:19] floating floating point sometimes might be. So, some point decimal point will be there. >> 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

[6:07:34] because salary is always it's a numeric value >> no no name name you said right name yeah string means string is in the sense text

[6:07:46] >> correct okay I'm just providing the generic names I'm saying okay in the similar way so if you are working with dates it is date data type Got it. >> Got it sir. >> So, so like that. So, but what I'm for

[6:08:01] simplicity I'm just keeping integer. Okay. We have >> so numbers can be as per my wish. I mean I can put any numbers as I want. integer means that is having range right.

[6:08:16] >> So what what is the range is minus 3 to 767 to 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'm not

[6:08:32] focusing on specifically these data types we have to use and all means this 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

[6:08:47] nowadays we have all the a tools are available right so for example if you're getting so some doubt related to what is that once I discussed so the data types suppose you can just ask a it will provide so for

[6:09:02] can just ask a it will provide so for example so I'm asking perplexity or charg so can you please tell me

[6:09:14] so explain data types in MySQL

[6:09:27] it's all right so MySQL to beginner for suppose.

[6:09:43] so details detailed documentation for suppose detailed documentation so you can able to get all the things right. So what are those numeric types?

[6:09:55] So there are so many things are there you can see. So tiny int. So it is accepting 0 to 255 or minus 128 to 127. somewhat deeper into this area because we have so three uh three three weeks

[6:10:11] we have so three uh three three weeks only right but this one 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

[6:10:23] don't want to become an expert in one technology like that correct >> so for example now we are learning so MySQL so tomorrow you cannot say we are not going to work on MySQL so maybe we are working on some other database or We

[6:10:36] are working on some other database or We are working on data analytics side. So these are the data types available. So you can make use of this if you want for learning purpose. If you want some other tools, you can just

[6:10:50] explore if you want further. Okay, that's what we are saying. Is it clear? >> Yes sir. Yes sir. Thank you. >> Yeah.

[6:11:02] So I just shared this query. So all of you please create this vehicles query. So we have so the multiple vehicles right already so I think shared the query could you please like that query I can

[6:11:14] see only five members are liking that query so that means remaining people are query so that means remaining people are not executing that query I am feeling anyone could you please confirm so like all of you are executing that query

[6:11:32] are completed but uh I can see okay great so I things I can see 10 members liking that message then only I can able to understand so whether you completed

[6:11:44] to understand so whether you completed the task or not so please do that one 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

[6:11:58] tables are present please look at here so I'm just refreshing 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

[6:12:13] suppose database diagram let's see here so go to tools database we have so one option called as what is that one so reverse engineering

[6:12:25] all of you could please check it out whether this option is available for you engineering? So we created tables. I'm just doing

[6:12:40] So we created tables. I'm just doing reverse engineering here. and click next. So provide the password as what is that

[6:12:53] one. So whatever the password you have so that is root So which database we are going to draw this diagram for which database?

[6:13:09] So employee DB all my tables are present in employee DB only. So select that employee DB. So click next and click next and execute. See automatically it is preparing some

[6:13:24] model diagram for me. So there are four tables identified. So click finish.

[6:13:37] relationships. So that's what I want to check. So tables are created but automatically the relationships also needs to be that part why the relationships are not established.

[6:13:52] Only tables you are getting or relationships also we are getting. Uh sir can you please show again because I missed out how to where to put the password and all. I am in that page. >> Yes I will repeat it again. No worries.

[6:14:08] >> Sure sure. >> So if I'm right clicking on so this uh I'm selecting currently what is the database you selected. Please identify database you selected. Please identify that employee DB. Correct.

[6:14:20] that employee DB. Correct. So employee DB is selected.

[6:14:35] 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, vehicles table. So now we can just go to database

[6:14:52] 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 that one so that

[6:15:11] uh ER diagram for this tables I'm just going so reverse engineering it is providing all the details don't touch that things click next

[6:15:24] here it is asking so password right so you can just provide the password root here I'm selecting valid that on the employee DB

[6:15:43] so retrieve objects from the selected schema check the results click next so there are total four objects selected

[6:15:57] selected? Address and employee. So for example, I'm just selecting the tables and I'm just moving to the next table. That means next uh I'm just moving all those things to the next uh

[6:16:12] whatever the uh tables that are required in the diagram in the diagram and click execute. But unfortunately what happen mean it is not creating so

[6:16:28] relationships but we mentioned the relationships as well as a part of the creation of the table right. So previously

[6:16:40] relationships as well but it is not listing. So the relationships I will check that one again. So is there any option I'm missing?

[6:16:55] 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:15] So this one right? So I'm just closing this. What happened? This is completely getting closed it seems. Okay. Now it is getting closed it seems. Okay. Now it is fine.

[6:17:36] 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:17:53] one option will be there and also reverse engineering. So click on reverse reverse engineering. So click on reverse engineering. click next and provide the password.

[6:18:09] and provide the password. What is the password? So root. database and we are selecting what is that one the employee database right? So what is the database we are currently using? So the employee DB

[6:18:22] using? So the employee DB and click next and click next. So we have so the table objects we are importing all the table objects. Click execute.

[6:18:36] 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:50] cannot see other ones for suppose if you're not getting created I will check and I will let you know why the relationships

[6:19:02] 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 employee table, right? So, we have forward engineering and reverse

[6:19:17] is that one? So, reverse engineering. So, first we created tables and from the tables we are just trying to so create the diagrams

[6:19:29] but relationships are not created. I will just check and I will let you know. Okay. So might be today or tomorrow that means in the upcoming session I will just let you know

[6:19:42] what is the mistake happened because it is not showing any options or related to that one. So we'll look into that part.

[6:19:56] So majorly we have to work on what is that one. So creating tables right. And to many relationship uh sorry many to many relationship.

[6:20:09] Can you please tell me employees and courses? What is the relationship? So we have an employee table and courses between the employee and courses? >> Many to many sir.

[6:20:24] >> Many to many correct. So if you're taking so the employee table which is having so these properties so employee number, name, email and gender

[6:20:37] gender and also we have so the courses table columns we have? So the course ID so the course name

[6:20:50] so the course name okay and also like uh the duration. duration. So this one comes under many to many relationship. If you want to if you are establishing many to many relationship, right? So a separate

[6:21:06] junction table needs to be created. So that junction table name is employee what is that one? The course table. So this employee course table contains

[6:21:18] two foreign keys. So what are the two foreign keys? One is employee number and another one is valid that once the course ID this employee number is acting as valid that the foreign key

[6:21:35] acting as what is that one the foreign key 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 once the foreign key like that

[6:21:50] so you can observe here. So what we are getting getting we can able to see so here we have so how we can implement this part for suppose

[6:22:04] suppose I'm just keeping so create table so courses so first I'm keeping so ID what is that so first I'm keeping so ID what is that one so integer it's a primary key right

[6:22:23] also what is that one the course name so worker of 100

[6:22:37] duration it's of integer type suppose so I'm just creating so the courses table which is having these fields right so and the next what is that one

[6:22:51] so we to create so employee courses we have to create a separate junction table so create table so employee

[6:23:06] courses we have so the employee id whose type is what is that one integer and this is a foreign key so that means this one references [snorts]

[6:23:18] the employee number correct can call it as junction table or joint table. So this one contains employee ID.

[6:23:31] This one is referencing so the employee table of employee number table of employee number and also we have so the course ID. So this one is also an integer. This one

[6:23:45] So this one is also an integer. This one references.

[6:23:57] right so very simple so this junction table contains two foreign keys are mainly for referring to the both the tables so how this works

[6:24:10] so already yesterday I discussed so theoretically so again I'm just repeating so please observe try to understand so we have employee table so which contains employee number, name and also email perose

[6:24:27] and also we have so the course table which contains course ID, course name which contains course ID, course name and duration. number is the primary key and in course table course ID is the primary key. So

[6:24:44] for suppose the course ID is 101. So course name is something like Java and duration is so some so 10 hours or something and employee number one so name is Suresh

[6:24:59] so some email id and employee number two so John he's also having some email id but suppose so like that we have multiple

[6:25:11] courses 102 so analytics days something like that. But many employees can register for many

[6:25:26] courses. In such cases we have a separate junction table. So what is the table? So employee what is the table? So courses table.

[6:25:41] Right. So in employee courses table. So we have to keep uh so the employee provide any name. So this one I'm providing it as employee ID

[6:25:53] and also course ID. So these two are foreign keys actually. 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.

[6:26:10] employee number and this course ID is 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

[6:26:25] courses in the similar way so the same courses are registered by some employee number two also right so for example so I'm two also right so for example so I'm specifying so 21 and 22

[6:26:38] registered for valid that So many courses if you're observing so not one or two here. So this one is this is 101

[6:26:50] and this is going to be what is that one? So 102 course here also this is 101. So employee number two also registered So employee number two also registered for so same courses right. So 102.

[6:27:04] 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 many employees are working for many courses like that.

[6:27:19] table. That's what I am trying to tell you. I'm just sharing this code with you. You just execute whether this one is You just execute whether this one is working or not. So in my case

[6:27:32] it is working fine. So look at this. If I'm running this query, so the table is successfully getting created. I'm just sharing this table with you. So please run this query

[6:27:51] 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 sur employee ID. Yes, we can do sur uh sur we can do it. No problem.

[6:28:04] So that is constraint we are specifying. So constraint name right. So constraint name right. So there are different syntaxes we have. So employee ID integer foreign key of employee ID. So if you are using the

[6:28:16] 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 space constraint name and after that so foreign key of employee ID references.

[6:28:28] I'm just writing the syntax in the chart for that. for that. So employee ID integer right here we can use make use of what is that one so better you can provide your own

[6:28:42] one so better you can provide your own constraint name so MP id fk here we can specify what is the constraint actual constraints of foreign

[6:28:55] key right >> no in my case I'm just saying if we use foreign key then we can uh have that relations in reverse engineering. We need to use that word foreign key. >> Okay. Okay. Okay. So if you are using

[6:29:10] So reverse engineering feature is getting enabled we are talking about. getting enabled we are talking about. Yes. We'll see that one also. Okay. So first let's complete this one and after that I will implement the same thing

[6:29:23] 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:37] engineering is getting so the form key relationship if you're using form key? >> Yes, I checked it's working for me. >> Oh yeah. Yeah.

[6:29:52] 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 ask me

[6:30:10] right. So we have so a separate syntax 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

[6:30:27] reverse engineering is showing so the diagram so it is making so the foreign key entries so he telling so might be we'll just look into that one also we'll drop this uh tables and we'll recreate it again

[6:30:43] with the help of that option but both the ways like this is the basic 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

[6:30:57] the same option so which is recommended in production means the next option [snorts] what I'm talking about so constraint keyword that one works in real time okay this is just for understanding so we are just using

[6:31:09] references keyword but in production If you want to use assign any key how to assign the key I will just discuss. [snorts] So we understand so what is foreign key and all. So now we can observe. So in

[6:31:24] production environment right? So production in the sense like in the live environment if you want to design a table. So there are uh several options you have to follow. But I am not writing actual production

[6:31:38] code. I will show you how the production code looks like also. But what I'm doing is here right now. So I'm just deleting all these queries. I'm I'm just deleting all these queries. I'm dropping this employee DB first.

[6:31:52] So drop database. What is the database? Employee DB. is no longer uh the employee DB is not existed. So the next step what I can do

[6:32:08] here I have to create the employee DB. So how table. So same story right? So the table. So same story right? So the employee DB

[6:32:22] you done all these steps what it is saying? So employee DB is it what it is saying? So employee DB is it is giving error. So drop database employee DB is selected. What is the issue here?

[6:32:42] So drop database right sorry so I've created a table so it's a database so it's created so how can I check just refresh it

[6:32:56] 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 use employee employ.

[6:33:10] automatically this employee DB is getting selected. So now let's create employee table first. So I know already how to create employee table. So create table employee.

[6:33:29] number integer and also name worker of 30

[6:33:42] and also email so worker of 30 so the 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

[6:33:55] primary key right can you please tell how to assign the primary key. So another approach we can make use of what another approach we can make use of what is that keyword. So constraint

[6:34:10] number so PK so what is the constraint name actually what is the constraint I'm assigning so primary key of E number so this is another approach right so

[6:34:24] instead of specifying so primary key here so we are specifying our own constraint name let's execute this code constraint name let's execute this code so the table is successfully created

[6:34:38] created or not? So we can describe the table. Okay. So you can describe the table. You can see

[6:34:52] so I'm just describing the table. So the primary key is assigned or not. So describe what is the table name. So employee table

[6:35:07] addresses. Employees having many addresses. Employees having many addresses. So create table addresses or otherwise create table address which is having ID whose type is integer and

[6:35:20] here itself I am assigning. So primary key that is up up to you and also the key that is up up to you and also the name sorry address. name sorry address. So this is worker of 100

[6:35:39] as well as this one contains some landmark. So this landmark is of type what is the type. So aircraft of

[6:35:56] 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 name right.

[6:36:11] id. So whose type is integer. So I'm just keeping an extra column called as empid called as empid and we can specify constraint.

[6:36:24] So empore ID fk. This one we can call it as what is that on the foreign key. Here I'm specifying what is that on the foreign specifying what is that on the foreign key.

[6:36:43] So foreign key of what is that one the employee id here especially I'm using so the foreign keyword right so references

[6:36:56] 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:09] so we can able to specify so explicitly the foreign key I'm just sharing this one first let me execute this one I'm just executing this query it is successful

[6:37:24] here is as pres right right what preser is saying so if you are specifying explicitly so the foreign key then automatically it is populating in reverse engineering

[6:37:44] already shared. So how to create a primary key. So previously Okay. So please like it once you have completed that part.

[6:37:58] Now we can do try reverse engineering perose. So go to database. So reverse engineer if I'm trying to generate a diagram from this tables for suppose.

[6:38:16] relationships you can observe. So employee DB I selected. So click next and click next and let's create

[6:38:28] and click finish. So now look at this uh this one whenever I'm using so the foreign keyboard right. So automatically what happens this one is populating. So the foreign key and primary key

[6:38:40] relationship as well. If you are keeping so a cursor on this relationship we can able to see uh this foreign key is pointing toward that one the primary key but we cannot say always right. So we cannot say this is the MySQL admin. If

[6:38:55] 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. for creating a foreign key. Correct?

[6:39:09] 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:27] 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:44] and here I'm keeping what is that one the primary key and also course name whose type is worker of 100

[6:40:00] I'm just keeping so another field. What is that one? So duration. type? So integer and uh

[6:40:13] and uh okay. So I'm just keeping so duration. So this is courses table. But when coming to the relationships, [snorts] what is the relationship between the employee and courses?

[6:40:27] It is many to many relationship. Correct? Correct? What type of relationship it is? So many to many relationship. So we need to create a separate table for that one.

[6:40:40] What is the table? So create table employee

[6:40:59] 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

[6:41:13] So that is your wish. You can provide any name. Here I am giving. So foreign any name. Here I am giving. So foreign key of

[6:41:30] 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:41:53] this one is referring to what is that one? The courses of so course of what is the table. So course ID sorry here I'm making a mistake. Constraint

[6:42:08] here we have to specify what is the keyword. So foreign key keyboard so foreign key of

[6:42:27] id so that one references what is that one the course table so c I column I'm just executing this query I'm getting an error

[6:42:47] 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 perose

[6:43:08] you have to specify what is that one so foreign key

[6:43:22] again. So all these queries what we didn't I did here. chart. So previously we are not using the

[6:43:36] are using what is that keyword? So the foreign key keyword. So now you can go foreign key keyword. So now you can go to the chart and you can see. So I posted that query. Now you can so do so reverse engineering per suppose

[6:43:53] with all these tables. You just right click or otherwise you just go to the click or otherwise you just go to the database and do reverse engineering. So all these tables are getting populated or not in the year diagram. So

[6:44:08] populated or not in the year diagram. So we can check it in the database diagram. we can check it in the database diagram. So click next and click finish

[6:44:23] might be I can able to see. Okay. So the tables are create getting created here What happens? So we have an employee table and also address table. The relationship is one to many.

[6:44:38] courses table. So 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.

[6:44:52] Now let's recall the syntaxes. But suppose suppose what we done up to now. scenarios we are not used the keywords to create the tables.

[6:45:08] 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:23] foreign key of employee ID references what is that one so employee table 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:37] 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:45:53] 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 if you are having

[6:46:07] between 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:21] explicitly we mentioned 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

[6:46:34] So this foreign key 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

[6:46:49] using foreign key just I'm providing references like that. Okay. So nothing difference. So what is the and this approach? I'm just uh quickly giving uh a reference for suppose here

[6:47:04] the approach is getting changed for the approach is getting changed for example so previously we have

[6:47:18] can able to see my screen is it visible right is it visible right okay great

[6:47:30] 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 some employee number whose type is integer and also it is

[6:47:45] what is that one? So the primary key right whose type is what is that one the worker of 30

[6:47:57] worker of 30 or of 100 and also the email it is var of 100.

[6:48:15] creating so what is that addresses table. So previous approach what we table. So previous approach what we done? So create table

[6:48:32] of ID whose type is integer and it's a primary key and also the actual address which is var of 100

[6:48:49] extra column? We are keeping it here. So the landmark.

[6:49:01] 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 right. So that is nothing but empore ID that is your wish. You can provide any name and whose type

[6:49:16] is integer. Here directly we are writing so references correct references what we are writing so EMP of that means employee of

[6:49:30] employee number this is the first 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

[6:49:43] the keyword so constraint keyboard so constraint And what is the constraint so constraint And what is the constraint name? So, empore ID. So, for this column only we are assigning foreign key, right?

[6:49:57] right? So, empore ID FK. So, we are keeping. So, what is that one? The foreign key.

[6:50:10] So, foreign key of what is the column in my table? So, emping.

[6:50:25] approach. Production grade approach in the sense. 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

[6:50:39] one explicitly I'm assigning what is that one? So constraint. What is the constraint I'm assigning? So the foreign key, right? So foreign key for which column? The employee ID column and it is

[6:50:52] referencing. So employee of employee number I think edk. So is it clear? What is the difference between so the previous approach and this approach? What are the topics I discussed in the previous session? So we know how to

[6:51:06] create tables. So that means we discussed about DDL. Correct? What are the DDL operations?

[6:51:24] update and also delete. So, these are all operations comes under DDL and also another operation is DML. So, DML stands for what? Data manipulation language, right? So, we

[6:51:37] discussed about insert and also delete operations. So delete operations still might be uh

[6:51:50] 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 major focus is about so DRL. So we need to work on what is that once

[6:52:05] 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 what is that one? So mostly select queries only.

[6:52:18] So what is the purpose of select queries? You need to understand uh how queries? You need to understand uh how to write the select queries because if you are working in real time right so the data is already present we are not

[6:52:30] the data 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 we need to retrieve the data so based upon your

[6:52:43] requirement if you are doing some data analytics or anything get some insight from the existing data. Right? If you want to get some insights from the existing data, so we need to write some select queries to retrieve

[6:52:59] the data and uh based upon the data we need to perform some analytics. So that's why this session is very very important session especially for data analytics people. So that's why please concentrate

[6:53:14] and also it's a simple only but u if you are getting any question right so feel free to ask me so today we are talking about what is that one so select operation by using

[6:53:27] 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 we created customers table and all those stuff right so we'll continue that setup

[6:53:45] Okay. So that is the agenda for the today's session. First we'll try to to retrieve the data from so the database table and after that so we are entering into some complex things like uh how to work with the joins. [snorts]

[6:54:02] Okay. So joins is what very very 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

[6:54:16] one? So data retrieval language. So by using select 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:31] Select queries. [snorts] For example, if you're writing I want to So what is the query we are writing generally? So select star from we are generally? So select star from we are writing so employee.

[6:54:46] 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 rows and columns. So from this employee table right?

[6:55:05] It's everything is fine. So I think uh uh Chakraati is it okay? know. So if I'm writing so select star from

[6:55:20] 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:34] employee table. But we are not expecting like that. maybe you want to retrieve only particular columns because in the business if you are working in a real business for suppose

[6:55:50] the tables contains many columns. Okay. So the table contains not only three or four columns we can expect might be 50 columns also. might be 50 columns also. always for our uh the for our analysis

[6:56:05] and also what data is required only we have to select the data. particular data so we can retrieve the data. So select E number. So common name comma address from what is the table. So the employee table.

[6:56:23] employee number name and address only. We are not getting any other fields. 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

[6:56:38] number. So we can specify only the specific address from what is that one? So the employee table. So we are writing it like this. Select name come address from what is that one?

[6:56:52] The employee table. So like that we have so some tables are already present here. Could you please look into in the last week we imported these tables. Is it

[6:57:04] correct? >> Uh sir, I don't have it. Uh like uh >> Yeah. >> Yeah. So that import option is not that option? >> No, I I mean u those are not already

[6:57:20] workbench, right? So I have to >> 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 go to table data import wizard.

[6:57:38] So where we have to right click right click on tables go to table data import wizard and click on browse here and select your CSV files. For example, if you are creating customer table

[6:57:54] rights, 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 your requirement. For

[6:58:08] example, I want to maintain this one as customer one because customer table is customer one because customer table is already present. So, click next. So check out the data types here. Customer ID is having integer type.

[6:58:23] 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 having tables. So

[6:58:37] that's why I'm not creating. If anyone want if you are new to import the tables I will help you. I will guide you what we have to do.

[6:58:49] I think Nikita you can share your screen. also could you please confirm all of you are having so imported the tables from

[6:59:01] the data sets. Could you please confirm in the chat? Nikita, you are not getting that option. You're getting right. So table data

[6:59:16] You're getting right. So table data import wizard. Yeah.

[6:59:47] present or not. >> Okay, you can do one thing. Change the name. Change the name. One second. Customers. Table name is customers. So click next.

[7:00:03] Unhandled exception. Okay. First year we have to select the database. Correct. So you can use this database or otherwise create your own database. Create database.

[7:00:16] as per the document? Just open the document. We can see the database will be the database will be SQL basics. The database name is first

[7:00:29] we have to create a database called as SQL basics. Create database. Write the SQL basics. Create database. Write the query. create slowly you can create no problem. So create database SQL basics space

[7:00:44] So create database SQL basics space SQL basics don't give space okay SQL basics that one is single name >> okay >> okay so put semicolon execute that query

[7:00:58] it's created now we have to use the database what is the command for using the database use

[7:01:11] No, no, no. You just use space SQL basics.

[7:01:29] import refresh it first. You can get that SQL basics database. So now right click on tables go to data import wizard

[7:01:47] and select the location. So where that file is located. can just uh select that one and click okay.

[7:02:10] Click next. Next. So it is imported. So click next. And finally how many records are inserting? 13 records. So now refresh it whether we can able to

[7:02:24] 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. >> Yeah yeah yeah.

[7:02:44] So to import the table I'm just giving 2 minutes of time. All of you make sure minutes of time. All of you make sure that you can ready with the data set.

[7:02:57] 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:11] introduce that query. Whatever the questions, I will take it right at that time. I will explain how to edit the relationships and all. Okay, Nikita, you can stop screen sharing. It works for but make sure that

[7:03:25] the table 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:40] 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:03:58] 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 have to select first SQL basics right

[7:04:15] automatically. So the SQL basics table is getting create uh database is getting is getting create uh database is getting selected. query? Anyone could you please type in the chat? What is the query to display

[7:04:29] the chat? What is the query to display all the tables? What is the command? So show tables right see most of the people are not responding.

[7:04:43] So what are the tables present in this SQL basics? So we have customers and also products and also sales table.

[7:04:58] I want to retrieve the data from what is that one? So the customers table perose. customers table. So then what we have to do? So select star from

[7:05:14] So okay so he executed select star from customers. It red displaying all customers data. Now the requirement is I want to retrieve only customer ID name and location only.

[7:05:29] Okay. So I want to retrieve only customer ID and name and location. So customer ID and name and location. So how to retrieve it? So C ID how to retrieve it? So C ID and also C name

[7:05:42] and also C name and C underscore so location from what is the table? So the customer's table right

[7:05:55] the customer uh table but what are the columns I'm going to retrieve so customer ID name and also location so only these three columns I want to retrieve see look at this I can able to retrieve

[7:06:09] only these three columns right what are the three columns so customer ID so name and also location. So now if I want to write some conditions for

[7:06:25] suppose let me execute select star from emp select star from customers again. only the details who are belongs to Delhi per suppose I want to display all

[7:06:40] 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? >> Sir, quick question. Uh why did you do C

[7:06:54] >> See this is the column name right? So you can see this is the column name which is present here. I'm not specifying on my own. Correct. >> The table contains what is the column name?

[7:07:07] name? C ID C name. So especially I'm retrieving only the specific comments C ID C name and also

[7:07:19] so C location like that. Is it clear? >> Okay. >> But I'm getting error for the same uh >> So in such cases what we have to do? First select star from

[7:07:36] first we are not using the database. Correct? First you are using the >> Mhm. Yeah. >> You can share your screen. I think easily I can easily identify what is exactly the error.

[7:07:48] >> Uh let me just run this again and save file. Okay. I I'm fine now. Thank you sir. >> Yeah.

[7:08:01] 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. How can I retrieve it? What is the query for retrieving the

[7:08:17] What is the query for retrieving the customers who are belongs to Delhi? Correct. Right. Very good. So select star from

[7:08:34] where so c location equals to I think somebody's unmuted nikita nam could you please go on mute

[7:08:48] okay sorry nikita always calling nam and sorry nikita always calling nam and nikita confusing bit okay nikita confusing bit okay so c location equals to

[7:09:05] so this one is case sensitive actually 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 he so automatically we are getting retrieving

[7:09:19] the data from what is that one the customer's table so where customer name equals to what is that one del like that is it clear so now another question I I want to display. So all the customers

[7:09:35] 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

[7:09:47] please tell me I want to display all the customers who are belongs to Delhi and Kerala. Sir after Delhi we can again put uh within the bracket Kerala. So here I'm just writing so I'm not putting any

[7:10:02] bracket right now. So let's see. So select star from customers where

[7:10:21] C location equals to Delhi and again so I am specifying Android. and again so I am specifying Android. So C location equals to Kerala. But this is not a good approach. Why this one is not a good approach? I

[7:10:33] will tell you. Okay. So just I'm writing. So C location equals to Delhi and C location equals to what is that one? The carerala like this

[7:10:50] >> so this is a condition right? So if I'm writing two times what happen? Sorry where I written. So two times C location equals to Delhi and C and loc >> So double and >> no no the and sign you have put two

[7:11:07] times that's what I'm asking. >> So this one so the single amp% it is not your programming languages right if you want to represent and

[7:11:19] so we are using what is that one? So double amp% operator single amp% there there is a difference between single amp% and double amp%. So this amp we are talking about. So this operator right why you are keeping

[7:11:32] >> yes >> if I'm putting so instead of that one and here right so let's see

[7:11:49] details per 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:01] Kerala. Okay. So, what is the why we are not getting So, what is the why we are not getting any data here is try to understand here we specified and what is the difference between and and

[7:12:16] means what? So, both the conditions are 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

[7:12:28] Kerala? It does not work. But suppose instead of that one if I'm keeping R instead of and we are keeping R that means I want to display all the customers who are belongs to Delhi either Delhi or

[7:12:41] 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 getting displayed right so like this here Nisha and Oliver both

[7:12:56] are from Kerala And Na is from what is that one? So Delhi why and is not working means because one customer belongs to only one location he's not belongs to two locations right.

[7:13:10] both the conditions needs to be satisfied. Both the conditions requires to be satisfied.

[7:13:23] the first condition or second condition. If the C location equals to Delhi so the Delhi people are displayed and also Kerala people also getting also Kerala people also getting displayed like that. Is it clear? So

[7:13:37] you're asking me so previously so why we are keeping so this and so generally in the programming languages right if you're keeping double% that is you're keeping double% that is equivalent to and operation.

[7:13:50] Okay. So if you're keeping so double pipe symbol but in the programming languages but in the SQL so it is directly under what are if it is R means what R like that. Now guys we have come to the end of this session on SQL

[7:14:04] 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 forget to subscribe Simply for more such

[7:14:16] forget to subscribe Simply for more such informative full courses.