MySQL Tutorial For Beginners | Introduction to MySQL | Learn MySQL | MySQL Training | Edureka

[Music] hi everyone the society on behalf of idiom and today's session is all about database management systems and my sequel so now without wasting any further time let's just get started with a my sequel tutorial so that you guys understand what exactly my sequel is and start playing with your databases right so first of all let's look into the topics for today's session we'll start today's session by understanding the very basic ideology that is data then we'll understand databases and then finally we will understand database management system once you understand this I'll make you go through the entity relationship diagrams normalizations the various SQL commands and also ways are the concepts of SQL such as views triggers procedures and many others so don't worry guys in this session you'll be learning all about database management systems and my sequel so now without wasting you know any further time let's just get started with the session now so now as all of us know that you know Quinton's of data is all around us but what exactly does this data term mean have you thought about it well you know you can consider the example that you can see on the screen imagine there are around hundreds of employees working in a company now obviously each employee has their own name and they have to get identified with their names in the department's right so all of these names identifying each of these employees with the departments where are they working in what position everything together forms data so if I just have to define it for you well I can say that data is basically the staff pieces of informations or collection of facts related to any particular entity it could be either an employee or it could be either object or anything right so that's how you can basically understand data it's just a collection of facts that defines any particular entity now how do you think the company stores so much amount of data altogether to identify all the employees well that's obviously through a database right so you can consider a database as a container wherein all the data is stored so database is basically a systematic collection of the date it supports the storage and manipulation of the data that you've stored so in simple terms if you have to understand data base then just consider it like a container where in your data is stored right now having such amount of data obviously causes a problem and you do not manage it properly right so to manage your data in the data bases you need something known as the database management systems but what exactly do you think these database management systems are well database management systems is basically a collection of programs which enables its users to access the database manipulate the data or report or represent the data so you can play around with your data when you have proper database management system and you can obviously manipulate it so if you have to just understand the term database management system then in simple terms you can see that you know it's a collection of programs which enables the users to access data manipulate and represent data as I said before or you can also understand that you know it's a technology to store and retrieve the data with the utmost efficiency along with its appropriate security measures so guys that's how you know you can understand data or databases and database management system now as I was talking about database management system you must be wondering right what are the various kinds of database management system because there are weirdest types of data stored and every kind of data has different kinds of relations right so to manage this you need various kinds of database management systems so mainly there are around you know four types of database management systems that is the hierarchical database management system the network database management system the relational database management system and the object oriented database management system so starting with a hierarchical database management system basically this type of database management system employs the parent-child relationship of storing data so as you can understand with the name it basically stones the parent-child relationship of storing data right so this type of DBMS or you know database management system is obviously rarely used nowadays also let me tell you that its structure is like a tree with nodes representing the records and the branch is representing their fields so for example if you have to understand this hierarchical database management system then you can say you know the Windows registry used in the wind XP is an example of this so basically all the configuration settings are stored as the three structures with the nodes now moving on to the network database management system well this type of DBMS mainly supports many-to-many relations so this usually results in the complex database structure and I can say that you know the IDM server is an example of this kind of T VMs right now moving on to the relational DBMS well the relational DBMS as the name suggests defines the database relationships in the form of tables also known as relations so unlike the network database management system the relational database management system does not support many to many relationships it usually halves a predefined data type that they can support and I can say that this is the most popular DBMS today in the market right so if you ask me your example for this then I could say that you know the Microsoft sequel server database are all the examples for this kind of DBMS now moving on to the final DBMS that is the object-oriented DBMS well this type of DBMS mainly supposed to storage of new data types so the data to be stored is in the form of the objects and the objects to be stored in the database have their own attributes and methods so that they define what they have to do with the data right so an example to this particular type of DBMS is the Postgres sequel right so guys that were all the different types of TMS you have hierarchical network relational and object oriented right so now that you have an understanding of what data is with databases and the management and the ways kinds of management systems it's time that we jump into SQL so that you understand what the structured query language is and then you start playing with your databases so let's get started with it so starting with what is SQL that is basically structured query language well it's basically a standardized programming language which is used for managing relational databases with sequel let me tell you that you can modify databases you can add or update or delete any number of rows of data you can also retrieve the subsets of information from a database and many more actions right so you can play around with your databases a lot now not only this but the sequel language is also used in the databases such as the my sequel database since the Oracle the my sequel server the Sybase and many more now how do you think all this happens well that happens because of the queries that we use in the sequel language so with queries another sequel operations such as select insert add update delete create and many more you can retrieve whatever data that you want so guys that was about sequel so as I was talking about the structured query language you must have noticed that you know I mentioned about my sequel well as this tutorial is about my sequel and you know you have understood all the basics before you start with my sequel let's just get started with my sequel now so what exactly is my sequel well let me tell you that my sequel is basically a relational database management system so by relational database management system as I mentioned before they work with tables and then you define relations between the tables and it is also an open source software it provides multi-user access so you can know that your many number of users can use this particular database management system and retrieve the data my sequel also supports multi storage engines and works on many platforms right so in simple terms if you just have to understand in a single line then it is a relational database management system which is open source works on many platforms provides multi-user access and also supports multi storage engines so now that I've told you about my sequel let's just look into the features of my sequel so that you get a clear understanding of why exactly we should use my sequel so as you can see on the screen these are the various features of my sequel it provides ease of management it provides robust transactional support it also provides comprehensive application development it provides high performance it also makes sure that you know you have a low total cost of ownership it's an open source software with 24/7 support it protects your data it is highly available and is also very scalable and flexible so these were the features of my sequel now that you know you have understood what my sequel is and what are the features of my sequel let me just show you how to interact with my sequel for which you'll need a server access tool that can communicate with your my sequel server and my sequel workbench so basically my sequel workbench it's a visual database designing and modeling actions tool for my sequel server relational databases it facilitates creation of new physical data models and also allows the modification of existing my sequel databases with River so forward engineering and change management functions so let me just open my my sequel workbench so that you understand and see how that it goes so let's just open it all right yes so as you can see on the screen this is the dashboard of my sequel workbench it mainly has three modules that is the first one is for the sequel development for working with queries tables data and scribs and then we have the second module that is basically the data modelling for designing database modules in diagrams and then we have the third one that is the administration of server so this is basically used to monitor and secure and also configure your service so these are the basic three modules of the my sequel workbench that you can see on the screen over here now let me tell you we here that you know the workbench is available for Windows Linux Mac OS either on the 32-bit or on the 64-bit as you wish so so guys if you want to learn more about my sequel workbench and you want to explore what all you can do with it you can go through the documentation link that is over here and then you can start learning about it so now let me just use the sequel development and then let me just create a new connection to a server instance and then you know let's start creating new databases and tables right so for that I'll just you know choose this option that is the sequel development and then I'll go to the plus option over here that is basically to add the my sequel connection choose this option so once you choose that option you'll see that you know you have to fill in some details so let's say I fill in the connection name to be Ed Eureka the method could be same let's say the host name stands same the port is three 3:06 username is root let's say we store the password to be whatever password that I've mentioned and then we also have an option to create a default schema right so when you mention some schema that schema would be created and it will be used as your default schema so presently I'm not going to do that and then I'll just you know click on okay so once you click on okay you can see that you know your new connection has been added so let's just open this connection and see okay so you can see that you know your connection opens and this is how the dashboard looks so basically you have four panes that is the basic pane where you have various options to add and query database you can configure your servers and all in the left side you have a navigator pane where you can see the performance instance and management at the bottom side you see an output paint wherein you see all the outputs for the queries that you've mentioned and on the right side we have the sequel editions pane right so let me tell you one more thing over here that is you can hide any of the panes that you want so suppose if I want to hide the right side pane so I'll just click on this option and you can see that you know the right side pane has been hidden similarly you can do for the bottom pane and also the left side pane so if I just do that you'll see that you know all my paints are just hidden and only I just have is the editor to just write my queries so let me just get all my paints back so I'll just get in all these panes all right so well we could start writing queries but before we get into the query spot let me just shift back to my presentation so that I explain you more about the command categories and many more stuff and then we'll get back to this right so let's just go back so now let's just quickly discuss the sequel command categories so basically there are four command categories that is a data definition language - data manipulation language - data control language and the transaction control language so the commands coming under the data definition language basically are used to define the database schema so you know you have your create draw up altered truncate comment rename all these kinds of commands in the grader definition language coming to the manipulation language all the commands that deal with the manipulation of data present in the database come under this particular sections so you have select insert update delete and many more in this section now the data control language includes all the commands which mainly deal with the rights and permissions and other controls of the database system so any permission that you want to grant to your database or any kind of write that you want to give then you have to use the commands in this particular category which are mainly the grant and invoke and finally we have the transaction control language which includes the command which mainly deal with the transactions of database so you have your comment rollback save point sell transaction commands in this transaction control language so guys these are the main four categories of the sequel commands you have your data definition language commands the data manipulation language commands they did a control language commands and the transaction control language commands so guys I hope that is clear to you right so now that you've understood the basic database concepts what exactly sequel is and then my sequel and the various sequel commands categories it's time that we start understanding how to perform the data modeling so when I say data modeling I'm basically talking about how do you design your database so mainly there are two ways either you normalize your complete database or you use the entity relationship diagram we'll start with the entity relationship diagram that is also known as the ER diagram as you can see on the screen this is an example of an ER diagram that I would explain you but before that let me just tell you what exactly ER diagram is right so ER diagram or an entity relationship diagram is basically a diagram wherein you represent real world objects as entities and then you understand the relationship between them so how they are dependent on each other what kind of attributes do they have how is one object or one entity dependent on the other entity and so on so you can understand all these relationships with the help of ER diagram so let me just you know think you'd go through this particular ER diagram that you can see on the screen so this ER diagram is basically for a company database so as you can see we have the employed departments and the project as the main entities so if you have to represent an entity then you put it in a rectangular box and then you can see that employee department projects are the main entities so the complete ER diagram would be based on them so an employee has a social security number which is basically an employee ID through which you know that particular employee can be identified an employee also has a birthdate an employer will obviously have a name also who has the first name middle name and also a last name an employer has an average salary and agenda right so an employee can be identified by looking into all these attribute now similarly when you come to a department a department has its department name and a department number through which it can be uniquely identified so suppose in your company you have your tech department write a tech department will have its own department number and the department name and the department's location also right and then we have the project entities or project can also be identified with the project name project number and where was the project taken right so where did the project happen we have the name number and the locations over here alright so that was about entities now when I say relationship relationship is basically the relationship between two entities right so as you see the employees and the department have this particular relationship that is works forth and manages the employer and the project have the works on relationship and the employer and dependent have the dependents off relationship right so before I move into relationship let me just talk about the weak entities over here well as you can see on the screen the dependent entity is basically a weak entities so when I say a weak entity I mean that you know this particular entity is completely dependent on the strong entity that is basically the employee entity now you must be wondering right why do we have this dependent entity well dependent entity basically keeps a track of each employees insurance purposes or you can say through any official purposes right so as I said before as this particular entity is completely dependent on this so for any kind of insurance purposes we obviously need all the details of the employees right so suppose if you have to get an insurance done then the insurance policy needs your name address birthday salary the gender and so on right so all this data can be taken from this particular entities attributes so that's the reason this particular entity that has a dependent entity is a weak entity which is completely dependent on the employer entity so now that if understood the weak entity let me just tell you that you know the relationship between a strong entity and a weak entity will be a weak relationship so weak relationship is basically represented like this right now coming this a strong relationship as you can see the works for relationship the manages relationship the works on deletion ship and the supervision relationships are basically the strong relationships that's because these are the relationships between two strong entities right so now let me just tell you how are these relationships happening between all these entities coming to the first relationship that is the works for relationship well as you know every employee works for some of the other department right so that's how this particular relationship is that's basically a strong relationship and then we have the employee managers Department now there could be some employers you know who have become the managers of some particular department or they lead the department right that is basically another type of relationship between these two entities so suppose you have ten employees so they could be nine employees who are working for the department and there could be one employee who manages two department right so for the managers relationship we have a Supriya attribute that is the star did so by which we can understand that you know suppose X employer with all these attributes has started managing the wide department from today so with such kind of information we can start understanding that you know the X employee has started managing the Y department from this particular date right now moving on to the next relationship that is works on now obviously for every employee working in some of the other department will obviously work for a project so obviously employee and project entities are connected to each other with the work zone relationship so suppose you can see that you know an ex employee works on a white project and it's from this particular department so employee department project all are connected together right now you must have seen that you know works on relationship has its own attribute that is ours so with this attribute you can get a clear understanding that you know suppose X employee works on Y project for so many hours right now moving on to the next relationship that is between the department entity and the project entity we have the controls relationship right so it's obvious so in the company your department can handle 10 projects and any other department can handle different number of projects it's basically like you know a tech department handles 10 projects with XYZ names or you can see the marketing department is handling 10 projects with XYZ name's and so on right so that's how these two entities are connected and let me tell you one more thing over here there's a sales relationship over here right so you must be wondering over here right how is an employee related to an employer himself so when you work with your boss in the company both you and your boss are two employees of the company right so basically you and your boss are connected to each other with this particular kind of relationship that is supervision and both of you have your own attributes so suppose X is the supervisor and the Y is the supervisee X supervisions Y right so that's how this particular relationship has come into place so I hope that you know you guys have understood what entities are what relationships are what we can today is are what strong entities are right now let me tell you one more thing over here you must be wondering what are these numbers over here n is to 1 1 is to n m is to n and so on right well that's basically the cardinality ratios so let me just explain you with some examples over here so suppose if you understand this particular relationship that is the employee works for Department so there could be n number of employees working for a single department right so suppose you have hundred employees working for tech department that could be completely possible right so that's the reason we have given a cardinality ratio of n is to 1 over here now similarly let me just go through all the other cardinality ratios over here so a single employee works for one particular department only right so suppose if I work for the tech department right so I work for one department so I myself that is basically an employee work for the tech department now similarly an employee can work for n number of projects or m number of employees can work for n number of projects right so group of 10 can work for 20 projects or you singlehandedly can work for 5 projects right so that's how comes to cardinality ratio between employee and project to be MS to n now the relationship between employee and dependent shows one is to n right so a single employee can have n number of insurances right so he can have a house insurance you can have an else insurance or it can have a car insurance so suppose an ex-employee has 3 insurances so you have to mention all the details of all kinds of insurances over that's how the relationship between employer and dependent has one is to end ratio and also coming to the self relationship that is the supervision relationship between two employees a single supervisor can supervise around 10 employees or hundreds of employees right so guys that was all about cardinality you have one-to-one one-to-many many-to-one and many to many relationships so now I'm moving on to you know that participation that is the participation between entities you must be observing this double lines and single lines over here right so that's what I'm talking about so the double lines between the entities basically represent the total participation and the single lines basically represent the partial participation so let me just start with the total participation first so when I talk about total participation between both the entities I mean that both the entities are equally involved in this kind of relationship so when you want to know which employee works for which department then both these entities that is the employee entity and the department entity have to work in together but when it comes to partial participation they're not all the entities are involved in this relationship so if you consider the participation between employee and department with the relationship managers not all employees manage the department right so all the employees are way here to not come into this relationship only few employees come into this particular relationship so that's the reason this is the participation of employees in the managers relationship is partial alright so if you consider the participation of employees in the managers relations they're not all the employees are involved right so that is the reason this has partial participation but yes we need the details of all the departments right so that's the reason the department side shows the total participation now similarly if you consider the relationship between employees project and on the relationship works on all the employees work on some of the other project right so that is a reason the employees and the works on have the total participation and also since all the employees work on some of the other projects and the relationship between works on and project is also total participation now coming to the relationship between employer and dependent as we all know that you know employer is a strong entity and dependent is a weak end the weak entity is completely dependent on the strong entities so that's the reason this shows total participation and this shows partial participation it could be possible that you know not all the employees have insurances right that's also not the reason that shows that you know the employees have partial participation and the dependent entity has a total participation in this kind of relationship so guys that was all about participations now you guys must be wondering right that attributes that I just spoke about when I was talking about entities so some are in different color some are mentioned with a double oval and so on right so let's just discuss each one of these attributes one by one so an ER diagram can have a composite attribute a simple attribute a single attribute or a multivalued attribute stored attributes or derived attributes and also complex attributes so we'll start with the composite versus simple attributes well composite attributes are basically those attributes which can be divided into small sub parts now then each of these sub parts basically represent individual basic attributes with their own meanings but when it comes to the simple attributes they're already simple you cannot divide them further right so if you consider the example on the screen over here the address attribute can be further divided into the street address city state and zip and also this particular attribute that is the street address can be also further divided into number Street and house number so the address attribute and the street address are basically the composite attributes but the city state zip the street number house number all come in to the section of simple attributes as these cannot be divided further right so if you so if you look at your ER diagram you can see that you know the name attribute is basically a composite attributes because yes the name can be further divided into first name middle name and last name but yes the SS end the birthday the salary sex started ours and all cannot be divided further right so all these are basically the simple attributes so I hope that's clear to you guys now moving on to the next attributes type that is the single versus multivalued attributes so single attributes are those attributes which have a single value a particular entity but when it comes to the multivalued attributes a multivalued attribute may have a lower and an upper bound to constrain the number of values allowed for each individual entity so let me just show you in the ER diagram so as you can see on the screen the locations is basically a multivalued attribute so as I mentioned before a multivalued attribute may have lower and upper bounds to constrain the number of values so you know when it comes to locations a department can have n number of locations right so you can say that you know the department is located from floor number one to flow number five right so five floors are for this particular department so that's the reason the location is basically a multivalued attribute now let's just move on and see the next kind of attributes that are basically the stored and the derived attributes so guys as the name suggests the attributes which derived from the real entities are known as derived attributes and the stored attributes are those attributes or you can say such attributes which are already stored at the database so basically stored attributes are those attributes from which the value of another attribute is derived right so if you consider this example of age and birthday then you'll observe that you know the birthdate is basically a stored attribute and obviously the age of a person can be derived when we come to know the birthdate right so suppose I'm born in 1996 so you can roughly get an idea that you know I'm already 22 years old so the age attribute is basically the derived attribute over here and the birth date attribute is basically the stored attribute over here so now let me also mention over here that the derived attributes are mentioned with the dashed oval so let me just open our ER diagram and show you so as you can see in our ER diagram that you know the number of employees are basically the derived attributes over here right so when you have the department name number and the location you can obviously derive how many employees are working for this particular department from the relationships over here right so that's our number of employees is basically it derived the attribute over here now moving on to the final kind of attributes that is basically the complex attributes so complex attributes are basically those attributes which can be represented by grouping composite attribute between parentheses separating the components with commas and also displaying multivalued attributes between square brackets so as you can see in the example over here so the address phone is basically a complex attribute which has a phone attribute which is basically a composite attribute as a phone attribute can be divided into area code and phone number and then we also have an address attribute which is a composite attribute which can be further divided into street address number street flight numbers city state and zip right so the address phone over here is the complex attribute and the phone and address are basically the composite attributes so guys basically these kind of attributes are generally used while you draw your er diagrams so the guys this was all about the ER diagram or you can say the entity relationship diagram that you have to understand about so all the entities all the relationships all the attributes there are different kinds of cardinality ratios different kinds of participation there are different kinds of entities relationships and so on right but there's also one more thing that I missed out over here that is basically you must have observed that I've underlined the text under few attributes so for example employees SSN has been underlined and also dependents name has been underlined projects name and number have been underlined Department name and number of an underline and so on right why have I underlined this I'll let you know you guys later on these are basically the keys and keys are basically how you can identify these entities right so we'll talk about that later on but before that let me just cover a few more topics that is the entity types entity sets keys and value sets all right so as you can see on the screen just consider this example to understand the key terms entity type sets keys and value sets right so we basically have two entities that is the employee entity and the company entity over here now this is basically the entity type name these are basically two entities over here then we have the entity type name that is basically the various attributes of this particular entity right so we have the name mate salary for an employee and name headquarters owner for a company so these are basically the entity types now when you talk about entity sets entities that is basically you not to store the values and identities right so you can have paid as employees and their information so suppose an entity said consists of so many values I'll store the name first because I've mentioned name eights álvarez so in the entity set you have to mention in the same order so I'll mention the name the age salary similarly I'll mention for another employee that is the name age salary and so on and the same goes with the company so here I've mentioned the name headquarters owner so similarly for a company I'll just write in the set that the company's name the headquarters and the owner's name right so that's what basically entity type and entity set is entity type is how you define an entity and how you mention all the values basically in what order do you want the values to be put into the entity set and then to decide this you put in all the values right now coming to keys and value sets I'll tell you what keys next but before that let me just tell you what value sets mean right my value sets I mean that you know whatever you have mentioned over here so every entity has some of the other values right so suppose you have employees entity that's the employees have their own attributes and you can have thousands of employees details in there so the complete information of employees will be the entity set and the information that you have stored for each particular employee will be basically the values right so each entity will have its own values and you can store n number of values based on whatever information or whatever attributes that you've mentioned now let's just move on to key skies alright guys so now let's look into the various kinds of keys in a relational database there are mainly five types of keys in a relational database that is the candidate key the super key the primary key alternate key and the foreign key so we'll be discussing each one of them one by one but let me tell you one more thing over here that we'll be using this particular example that you can see on the screen to understand all these keys right so let's just get started with the way these kinds of keys so starting with the candidate key the candidate key is basically a minimal set of attribute which can uniquely identify a couple right so the value of the candidate key will be unique and non null for every tuple that means the Tuffle that is identified as and it key needs all the values to be present they cannot be any null value present and also they can be more than one candidate key in the relation so a candidate king can also be either a simple key or a composite key now when it's a candid key so if you see the example over here the employee number is basically a candidate key as this uniquely identifies all the employees and also which employees working for which department right also let me tell you that in a sequel server a unique constraint that has a nullable column that is basically which can be a null or not allows the null value in that column for only once right so that means the employee phone is also basically a candidate key but yes when I can treat key is chosen to be a primary key it cannot have any null values right so if I have to just repeat it for you then a candidate key is basically a minimal set of attributes which can uniquely identify the poll right so here we have the employee number and the employee phone as the candidate keys now moving on to the super key the super key is basically a set of attributes which can uniquely identify a tuple now you must be wondering it's similar to candidate key right well yes so as you can see on the screen the employee number the employee name together can be taken as a super key so that they uniquely identify any particular couple right so if one and Rohan can identify the complete couple over here too and Rohan can also identify the complete couple over here so you can clearly see that you know there are two Rohan's who belong to two different states and have two different phone numbers right so the employee ID that is basically the employee number and the employee name can together act as a super key and identify a couple right now moving on to the primary key they can be more than one candidate key in the relation out of which one can be chosen as a primary key so suppose you have two three candidate keys right so out of those two three candidate keys one can be chosen as a primary key but let me tell you where here then a primary key can not have any null values in its topple so for example as I mentioned before we have the employee number and the employee phone number as the candidate keys so out of these any one of them can be chosen as a primary key so let's just say if I choose employee number as Prime Keith and we just have to make sure that you know none of these values have a null value so that was all about primary key guys now moving on to the alternate key the candidate key other than the primary key is basically known as alternate keys so suppose if you have two three candidate keys right out of which you choose one key as your primary key and the rest keys will be basically known as the alternate key so in the example that we saw before where we have the employee number and the employee phone number as the candidate keys if we choose to employ a number to be a primary key then employee phone number would be a alternate key and the vice versa so guys that was about alternate key now moving on to the foreign keys well to understand foreign keys you have to understand the fact that you know if an attribute can only take the values which are present as values of some other attributes then it will basically be the foreign key to the attribute to which it refers right so basically the relation which is being referenced is called the reference relation and corresponding attribute is called the referenced attribute and the relation which refers to the referenced relation is called the referencing relation and the corresponding attribute is called the referencing attribute right so the referenced attribute of a referencing attribute should be a primary key so in the example that you can see on the screen the employee number in the employee department table is basically a foreign key to the employee number in the employee relation so guys that was all about you know the ways kinds of keys in the relational database so now that you've understood so much about entity relationship diagram it's time that you know you also understand what exactly normalization is so if I have to define normalization for you well normalization is basically a technique that organizes tables in such a way that the redundancy and the dependency of the data is reduced so in other words you can say that in a normalization is basically a technique of data modeling again so there are mainly four normal forms that is the 1 and F 2 and F 3 and F PCN F but this is not the end of the story guys you can also have for the normal form such as the 4 and Fi and F and a 6 n F right so basically the levels of normal forms goes on you organize a table in such a way that you know your residency and dependency of data is reduce right so suppose if all your tables are organized by 2n F so you need not go to 3 n F in bcnf similarly if your tables are still not organized still be CNF then you still have to go for higher normal form such as 4 & Fi and F and 6 NM right so let's just get started with 1 and f 2 & 3 NF in bcnf so let's just move on so guys I'm going to consider this particular example to explain you the complete normal forms that is 2 1 and F 2 and F 3 and F and bcnf so we have a database of a video library which maintains all the data of the MU is rented out so we have a table of salutation full name address movies rented and category so we have miss Alice Johnson row signing in this particular address renting out to movie status Mission Impossible and clash of titans similarly mr. David Allen whose address has been divided that means it's a composite key that has been used and I mentioned before right so you can divide your addresses further right so this is basically a composite key and he has rented out three movies that is interstellar edge of tomorrow and Mission Impossible now when you talk about normalization you'll start with the first normalization that is the one an F so the only rule of one and F that is basically the first normal form is that each table cells should have a single value so basically what it means is that all the records must be unique right so whatever table that we just saw before that is basically the example Sabre that I was just showing you the tables have two values in one single cell right so basically we have to divide this table in such a way that each cell has only single value so a first normalized table would be something like that you know miss Alice Johnson deciding in this particular address has rented out mission impossible similarly we will have another couple of miss Alice Johnson residing in this particular address renting out clash of titans so similarly we'll have it for David Allen who's deciding in the third street 45 renting out interstellar and then we'll have another couple of mr. David Allen renting out edge of tomorrow so let's just look into a first normalized table so as you can see our table has been divided in such a way that your each cell has a single value so as you can see miss Alice Johnson residing in this particular address has rented out mission impossible similarly we have others a couple of miss Alice Johnson residing in this particular address renting out clash of titans and similarly we have the details for David Allen right so the guys that was about one in F that is basically the first normal form now if you observe in our table you'll observe that you know David Allen's address is a composite address that's basically you know the address has been divided into two parts right so this is basically a composite key or you can say the composite attribute that I was telling you before now what we want is we want the full name and full address of a person renting out movies right but in the first normalize table you observe that David's alit's information is getting discrete it right it's not that we have the full address of David Allen right so to get both full name and full address what we need is we need the table to be normalized into a second normal form right so let's just jump into second normalized form now okay so the rule of second normal form is that that the database should be in the first normal form and it should also have a single column primary key so when I say single column primary key I mean that you know it cannot have composite keys so what I've done now is I've divided my first normalized table that I showed you before into two different tables that is table 1 and table do so the table 1 has an idea that I've introduced over here identifying each particular person uniquely with the matter of the full name and address so you can see that you know miss Alice Johnson has been given an ID of 1 mr. David Allen residing in this particular address that is basically a composite address has been given an ID 2 and also mr. David Allen having this particular address has been given the ID 3 ray and in the second table what we have mentioned is we have mentioned the IDS and we've mentioned the movies rented by these particular users so with Alice Johnson with ID 1 has rented out mission impossible Alice Johnson again with ID 1 has rented out clash of titans David Allen living in the third street has an ID 2 and has rented out interstellar similarly David Allen of ID 2 has rented out edge of tomorrow but yeah David Allen with id3 that's because of the composite attribute over here has dented out Mission Impossible so that's how basically you know you can go to the second normal form by dividing the first normal form stable into various tables right so we are I've just met divided into two tables where Table one contains the member information and Table two contains the information on movies rented right so we're here the ID is basically the primary key for Table one and records of Table one can be uniquely identified with this particular ID and also this particular ID can be used to find out the movies rented by the respective records so if you just observe over here this ID in the Table two is basically a foreign key to this particular ID in table one right so guys that was all about the second normal form before we move on to the third normal form you have to understand something known as the transitive functional dependencies so what I mean by transitive functional dependencies is that suppose you change the full name of any particular person right so suppose you change David Allen to Monika Allen so that means the salutation will change over here so basically a transit the functional dependency is when changing a non-key column might cause any of the other non-key columns also to change so since of our full name is the non-key column and our ID is a key column if I change the values over here if I change it to Monika Allen as I said before it will obviously change the salutation right so this is basically what is known as transitive functional dependencies now if you're wondering why did I explain transitive functional dependencies that's because in the third normal form the database should be in the second normal form and it must not have any transitive functional dependencies so if you observe in our particular tables that is in the tables of the second normal form you'll see that you know our ID columns are basically the primary key column right and salutation full name address are basically the non-key columns so suppose if I change the name over here let's say Monika Allen that is basically I'm changing a non-key column then it will obviously automatically change my salutation right so it will obviously automatically change my salutation to miss over here and this is obviously a transitive functional dependency but since we want to avoid the transitive functional dependencies in the third normal form so what I'll basically do is I'll have three tables over here with the first table showing the member ID that we had as a primary key column in the second normalized form the full name that is basically all the full names of all the people running out the movies the address and then we'll introduce the salutation ID and in the table too we'll have the ID that is basically the member primary key ID that we have in the second normalized form and also the movies rented table and it's the third table what we'll do is we'll basically take the salutation ID and salutation into account right so the third table will basically have the salutation ID and salutation so each and every person having some particular salutation will be assigned an ID right so if it's a mr. he'll be assigned an ID 1 if the salutation is a Miss then the IDH sign will be two similarly for misses it would be free and dr. would be four right so with this we'll make sure that you know there are no transitive functional dependencies and hence all our tables are in 3nf so guys that's how you know you can make sure that you know your tables are in three and F now one more thing over here is that you know the Table three salutation ID is basically a primary key and the table one salutation idea is basically a foreign key to this particular primary key in table tree right so that's how the relationship between the primary key and the foreign key goes on all right now guys if you observe over here our example has come to an end and you it you can clearly see that you know you cannot be compose it further to attain any higher normal forms right so that means our example is in the highest normal form for itself and it is in the turn enough but yes if you were to go to the higher normal form that is basically the P CNF bcnf is basically used you know when a database is in the third normal form but yes there are few more anomalies left and yes it has more than one candidate key right so that's when the bcnf is used and that's when you start dividing your tables further so that you have only one candidate key present and you have all the animal is to remove these were the few normal forms apart from this if your database is still not sorted out and it still needs to be organized and you can go further and try out the fourth normal form the fifth normal form and the sixth normal form so guys that was all about normalization so now that I've told you so much about databases how you model your data how you play around with it what exactly are we going to use to connect to our my sequel how do we understand the ER diagram or how do we normalize the data it's the time that we start understanding the sequel commands so that you know you start writing your own queries and then you start creating your own databases and play it out with them so before I start with the commands let me just discuss the datatypes and the constraints that would be needed for you to to write the queries so the sequel supports all these data types that you can see on the screen you have the numeric data drive the character spraying the pitch string the boolean data type the date and time datatype and the timestamp right so the numeric data type allows the integer small and float real double precision and the decimal so that's basically all the numeric values that you want to enter and also the floating point values that you want to enter and also the character large objects so that you know you store large amount of characters in your database it also allows the bed string datatype so you can have bits of varying size or a bit of n size you can have boolean values that is the true/false unknown you can store date and time and also the timestamps also let me just introduce you to the constraints so that when I start writing the queries you do not have a confusion why are we mentioning this constant so if you have to first understand what exactly constraints are so basically constraints and simple terms are those rules that can be applied on the type of the data and the table right so it could be either not null it could be unique it could be check it could be default or it could be index so the normal constraint is basically used to ensure that you know the null value cannot be stored in a column the unique constraint makes sure that all the values in that particular column are different the check constraint ensures that all the values in the column satisfies specific condition that you'll mention the default constraint consists a set of default values for a column with no value specified right so suppose if you mentioned the default constant it will just choose the default value when you not mention any value for that particular column last but not the least we also have the index constant which is used to create and retrieve data from the database very quickly right so we have the not null you need check default and the in text constraints so I hope that it's clear to you guys so I'm just going to first give you the overview of all your commands and then we'll move on to my sequel workbench and then we'll use these commands right so first let's just look into the SQL data definition commands so as you can see on the screen all these are basically the commands for the data definition so you have to create index which basically creates an index for a table all the table that modifies the tables definition so you can basically add modify or delete any attributes or constants that you want the drop table basically permanently deletes the table and all its data the create schema authorization is basically creating a database schema you the create table is to create a new table in the users database schema now under the create table you can use all the constraints that I mentioned before that is the not null unique primary key foreign key default key and check key you can use the drop view to permanently delete the view the drop index to permanently delete an index you can also use a create view to basically create a dynamic subset of rows of columns from one or more tables don't don't worry guys we'll be discussing about views very soon and we can also use create table as that is basically create a new table based on the query in the in the users database Kiba right so these are basically the data definition commands that you can use now moving on to the data manipulation commands you can use the update command which is basically used to update any attribute value in one or more table rows the commit command to permanently save the data changes the insert command to insert a new row in the table the Select command which basically selects attributes from rows and one or more tables of views so in select you can use the where group by having an order bias the where command is basically used to restrict the selection of flows based on a conditional expression the group Y command is basically used to group the selected rows based on one or more attributes the having command tricks the selection of grouped rows based on one condition and the order by command is used to order the selected rows based on one or more attributes right now not only this but you can also use the delete command to delete one or more rows or tables the rollback command to restore data into their original values and you also have many more commands just the logical operators which are basically and or not which are used in the conditional expressions at the comparison operators with that you can see on the screen that is equal to or less than greater than less than equal to greater than equal to and so on which are again used in the conditional expressions the aggregate function such as the count min max sum average which basically I used as the aggregate function so with the count function you can return the number of rows with non null values for a given column the main function is used to return the minimum attribute value found in a given column the max is used to return the maximum attribute value found in a given column the sum is used to return all values for a given column the average is used to return average of all values for a given column the special operators are again used in the conditional expressions and therebut and they're between is null like in exists and distinct so the between operator basically checks when an attribute values within a range the is null checks whether an attribute value is null or not the like operator is used to check whether an attribute value matches to a given string pattern or not the in operator it checks whether an attribute value matches any value with the value list the exist operator checks whether a sub-query returns and heroes the distinct operator limits to unique values right so guys these are few data manipulation commands and data definition commands now let's just quickly shift to a my sequel workbench and let's just see how we can play around with these commands alright guys so let's get started with our by sequel workbench so I'm going to use the same connection that I just created before so let me just open that alright so now when you open your connection you'll see a dashboard like this right so let me just enable all the panels over here so that you can see all the panels so as you can see on the screen guys this is how your my sequel workbench looks like so now let me just add a query editor so that we can write so for that you go to file and you'll choose the new query tab so this will basically open your new queries tab right now what we're gonna do is we're gonna first create a schema that is basically a database and that I'm going to create it on the basis of the entity relationship diagram that we just discussed before right so let's say I want to create a new schema so what I'll do is I'll go to this schemas option over here and then I'll right click over here and choose the option create schema and then let's say we wench in the schemas name to be company so I'll mention company over here and I'll click on apply so once you click on apply you'll see that your SQL script which will be applied will be displayed to you so that's the command that we want to be applied so we'll just click on apply over here once you see that you know your sequel script is applied to the database you'll see an output that you know executed sequel statements right once you're done with that just click on finish so you'll see that you know your complete schema has been created right so now once your schema has been created obviously our next step is to create tables so to create the tables we're going to use the create command so we'll just type in create table and then let's say we want to create it for the company's database right so we'll just mention the database name so let's say company dot let's mention the tables name over here let's say I mentioned it to be employed and then I'll mention all the parameters that I want to mention right so let me just go back to my entity relationship diagram over here so as you can see on the screen the employee entity has various attributes such as the name that is the first day a middle name last name address salaries the gender that is basically the sex birthdate and Nestor send right so we're going to mention all these attribute names as the column names right so let's just do that so let's go back over here and let's just type in all the attribute themes so let's say first name to be of vark at types that is basically it could be a variable length of characters let's say of 15 characters and not ton that means when I say it's not null it means that you know it cannot have a null value it's mandatory that the column must have some of the other value for every tuple right once you're done with that you have to mention the other attribute such as last Neiman also let's just mention last name let's see it off to be of work at types again and let's say again 15 characters right and again let's say that it cannot be null now let's also mention the middle name over here and let's say it is of character types right so basically you can only mention a single alphabet or what they write and let's say it can be none now apart from that let's mention the SSN which is basically our social security numbers which will identify all the employees and let's say it is a gain of character types and let's see if nine characters are allowed and again not null because this will be a primary key and as I mentioned before the primary key column cannot have any null values right now once I'm done with that I'll also mention the birthdate of date type so I'll just mention date over here and similarly address of work at types let's say 35 characters are allowed and again not null the gender so let's just mention sex over here and character and it can be null right it's not mandatory that you know the gender is mentioned all the time let's again have a parameter of salary of decimal type with a range of 10 to 2 and can be of nul types now apart from this let's also mention the super assassin now when it comes to super assassin you must have observed that in the entity relationship diagram we do not have the attribute super assistant but yes I'm still mentioning it because you must have observed this relationship of employee related to employees with the supervision relationship right so because of this relationship we need this column name so that you know we understand who is supervising which employees right so I'll just mention that column name also and let's say it's a gain of character types let's say 9 and can be of null it can sometimes happen that right and some employees not a manager and it's not supervising anybody right so it can be null and let's say we mentioned that Department number also because this is our employees table and we need to know which department they're working in so it will be of integer type again if not null and that's all right so I guess we've covered up all the attributes that are needed right so yes we've covered up all the attributes now let's just go back and let's just mention the primary key by now I hope that you know you guys must have understood what exactly primary key is right so what primary key basically is that it uniquely identifies all the tuples over here right so in the employee table we want the SSN column to be the primary key because it is unique for each and every employee right so we'll just mention that primary key is SSN right so let me just type in SSN over here alright and then we'll end it with a semicolon so guys this is how you know you can create your own table so let me just run this command and show you so to run this command what you have to do is you have to just select the complete section and press on ctrl shift enter so in the action output you will see that you know your table has been created right so don't worry guys once we insert values into our tables I'll show you how our table exactly looks right now apart from this let's create the tables for all other entities such as the department and project independent now apart from this let me also tell you that you have to create the tables for works on and the department locations and that's because you know works on is a total participation relationship right so you need the values of both the employees and projects for that particular relationship so that's the reason you have to create a table for that and also Department locations because this is the multi valued attribute and you have to store the values of it right so let's just start creating it so similarly I'll write in the commands for create let's say you know Department right so I'll just type in create table company dot Department and let's say I'll mention all the attributes names right so over here you can see that we have the DNA MD number for the location we're creating another table so that's not required now so we'll add and D name D number and we'll also add in the attributes of the relationships we'll add in the managers start date and also the manager assistant so that we understand which employs the manager of which department right so we'll just do that so we have four attributes to add that is the number name start date and the manager assistant so let's just start with that so I will just type in all right so as you can see I have mentioned all the values over here and I've mentioned the primary key to BD number because the number is the department's number which will uniquely identify each department right so once this is done let's just run this program again and you'll see the actual output now similarly I'm going to create all the other tables but before that you know you can also use a command known as the use database so when you use that command you'll see that you know you don't have to mention this part of the statement so you can directly mention create table Department right so let's say let me use that command so I'll just type and use and I'll mention company right so that is basically a database name now once I'm done with that I'll just run this command and then you'll see that you know use company has been executed so that means we're presently using the company database so any changes that you make or any query that you run will be executed with respect to this particular database so now let me just write the commands for creating other tables so once I've used this command to use the database we can simply write the create table commands without mentioning the name of the database right so I've just mentioned Department locations that's basically my tables project works on and depended now oh here let me just go through all the tables so what I've done is in the department's location I have my T number the location that is basically the department number the department location and both of them together can act as a primary key so I've mentioned them together as a primary key over here and after that in the projects table I have the project's name number location that is basically where the project is taking place and which department is handing it so I'll mention the Dean I'm over here I'll mention the primary key to be the P number that is basically a projects number so by that I mean that you know your project number is going to uniquely identify this particular table and over here if you observe we have used a unique constraint right so basically this unique constraint is used when you want to make sure that you know all the values in that particular column are unique right so we have mentioned it for P name that is basically a projects name now we also have the works on table so that has the employer system that is basically dependent on this particular relationship right that is between employee and projects so we need the employees handling the projects so we need the social security number of the employee so I've mentioned that and then we have the project number that is basically a primary key of the project table and also I've added on a new attribute that is our so that we understand how many hours are spent on that project by that particular employee right now the primary key for this table would be the combination of the social security number of an employer and the project number and similarly we have the dependents table which is completely based on this particular relationship we need the social security number of the employees and we also have to make sure that you know we mentioned the dependents name the sex the birthdate and the relationship so those are basically the attributes that I've mentioned over here so I've mentioned all of them and the primary key to be again the employee social security number and the dependents name right so guys that's how you know you can create your tables so let me just run all these tables so I'll just select all of them and I'll run them together let me just drag this up alright as you can see on the screen in the action output all your tables have been created right so that's all you know you can create your tables now once you know all your tables have been created now let's concentrate on the commands insert update delete alter and drop right so insert command is basically used to you know insert values into your tables so let's say I start inserting values for the employed table so what I do is I just type in the command insert into and then if I had not mentioned the command to use the company database you had to mention the database name and then you will mention the tables name into which you want to insert the employees and over here you can mention all the column names right so let's mention all these column names so let me just copy them right so I've mentioned all the column names so I'll just close this bracket and then I'll use the keyword values and then I'll mention all the values right let's say I start mentioning the values so let me just mention some random values right so you've seen that you know I've used the insert command like this so to use the insert command you have to type an insert into you have to mention the tables name or database tables name you can then mention all the column names and then you have to use the keyword values and mention all the values right now once you're done with that let's just execute the statement all right so you can see in the action output that our values have been inserted into the table right so you can insert the values like that now you can also use the insert statement without mentioning the column names so what you have to do is you just can mention it something like this so I just copy paste this and remove this part right now once you remove this part you can just change the values over here all right so I've mentioned all the values as you can see on the screen let me just run this particular query and you'll see that these values are also inserted in the employee table right all right so you can see that you know it is inserted into so here you can also have one more change that is since we're already using the database you did not have to mention the database table name right so I'll just copy this and let me just change the values again and now let me just mention employ right and over here I'll mention all the values again right so I've mentioned all my values now if we run this particular query you'll see that again they're inserted all right so you can see that all these values have been inserted into the employee table so that's how you know you can insert values into your table now similarly I'm going to insert values into all the tables so let me just write in all the commands and then you'll see that all the values have been inserted into all the tables right you all right so you've seen that you know I've mentioned all the commands to insert values into Department dependent table Department locations table projectable and works on table right self mention all the values that are required so let's just run all these queries all right so if I drag the action output you can clearly see that you know all our insert statements have been executed right now as you know in the entity relationship diagram all our entities are related to each other right so that means we need a foreign key to be mentioned but how do you think we're going to mention a foreign key we can mention a foreign key by using the alter command so all the command is basically used to order your tables so suppose if we want to add a foreign key for the relationship between employee and Department that is the managers relationship what we have to do is we have to add a foreign key that has manager SSN which will reference to the employee SSN because that's from where we get all the Social Security numbers of employees right so what we'll do is we'll use the command all set table and we have to alter the table Department right so we'll just mention Department that is basically a tables names then we'll use the keywords add foreign key and then we'll mention what has to be added as foreign key right so I'll just say it to be MGR SSN which references the employ table and it has to reference SSN right so I'll just mention SSN and I'll end this command with a semicolon right so that's how guys you can use the alter table command you just have to use the keywords all the table and then you have to mention the tables name and over here you have to mention whatever action you want to perform rights over here I wanted to add a foreign key so I just mentioned I had foreign key and then I'll mention the attribute name which has to be mentioned as a foreign key and the reference table name and the column name right so once I'm done with that I'll just execute this command so as you can see the action output you know your foreign key has been added right now similarly if you want to add the foreign keys for the other relationships that is basically are employed to employ supervision relationship or employ dependence of relationship or works for a works on relationship so you can basically write commands like pairs that you know you mentioned all the table and and then you mention the database name but table name which is not always required as because we are already using the database you can directly write the tables name then you have to use the keywords add foreign key and then you have to mention the column name which refers to the other table name in the database and the column name so similarly as you can see in the screen I've added so many foreign key so let me just run all these queries right so as you can see in our action output bar all our queries have been executed right so that's how you can add foreign keys let me tell you that you know you can see the results in some of the way also which I'll show you with the next query now let's go through what all we've gone through we've gone to the insert command the use command the create command and the alter command right now we can also use the drop command and the update command so suppose let's say I want to update the salary of an employee with some assassin right so let's say with that I'll type in the command update employee that is basically our tables name and then I'll use the commands set and then I'll mention the columns name in which have to change the values over here I want to change the Sally so I'll just type in salary is equal to let's say 27,000 where SSN is equal to let's choose some SSN randomly let's say SSN is equal to triple three double four four fives right so I'll just copy this and paste it over here right now once this is done I'll just end this command and I'll run this query so when you run this query you can see that you know that the salary has been updated for the employee who's SSN is triple three double for four fives right so that's how guys you can use the update command now suppose if you want to draw up any particular table you just type in drop table and then you'll mention the tables name right so suppose I want to drop the table works on so I'll just type in works on and if I execute this command you'll see that you know our table has been drop so let me just show you the action output you see the action output that our table has been drop so since we have dropped our table who works on let's just create a table again so that we have a schema ready for the further queries so let me just run these commands again and let's just insert the values all right so as you can see I've inserted all the values back to works on again so guys that so you know you can use the data definition commands that is basically the create alter drop insert update commands right now you can also delete a couple from any of the table right so let's say I want to delete a couple from the works on table where the employees a sign is to be suppose let's say this particular assistant and we also have the project number to be 30 right so what I'll do is I'll just use the command delete from the tables name that is works on and then I'll mention the condition where employees a sin is equal to the SSN that we thought of that was this right so we'll just copy this and let me just put in quotes over here and also the project number to be 30 right so for that you use the keyword and you just type in and you'll mention P number is equal to 30 now once you're done with that if you just execute this command you'll see in the action output that you know you're that particular couple has been deleted from works on table right so guys that's how you know you can insert delete tuples also into your tables now guys that was about the data definition commands now before I go on with the further hands-on on sequel commands let me just give you an overview of the relational algebra and relational calculus so basically in this we have various categories of relational operators for now we're gonna start with unary relational operators that is basically the Select operation the project operation and the rename operation so the Select operation is basically used to choose a subset of tuples from a relation that satisfies a specific condition so let's say you know you mentioned a condition that you know you want all the employees whose salary is greater than thirty thousand so you'll see that you know when you execute this particular command you'll get all those tuples with all the information of those employees whose salary is greater than thirty thousand right now coming to the project operation the project operations selects certain columns from the tables and discards TR the columns so suppose let's say you know when you want to select all the employees who salaries greater than thirty thousand but you only want few columns to come in let's say only the first name and the last name that you want and you don't want any other information so then you use this project operation with the Select operation and then you can distinctly get only the first name last name of all the employees who Salvi's greater than 30000 so don't worry guys I'll be showing you how that's done for the Select operation you use the command select and for the project operation you use the command distinct right now for the rename operation rename operation is basically again a unary operation which is used for renaming the attributes of a relation so basically if you have any big attribute knees so suppose in this particular example where I was explaining you guys I had to manage your start date right it's quite big so I convenient as any small attribute name that can be easily used right so that's what the Select project and the rename operations mean guys next what we have is the set operations so as all first know that you know set operations are really easy to understand so in my sequel also we have the Union operation the intersect operation and the minus operation so the minus operation is also known as the accept operation or the set difference operation so starting with the unit operation when you have two queries let's say the left and the right query and you perform a union operation between both the queries then it will combine all the rows from both the queries right so your output of the Union operation will be a combination of all the rows present in both the queries now when you come to the intersect operation when you perform an intersect operation between the left query and the right query the output that you get is the common rows in both the query so you get only the common tuple values that are present in both the queries right so I hope that I'm clear to you guys now the next set operation that we have is the minus operation or you can say the difference or the accept operation when you perform the minus operation between the left query and the right query that is basically left query minus the right query then you'll get all the rows from the left query which are not included in the right query and it's vice-versa so when you perform the right query minus the left query then you get all the rows from the right query which are not until the left query right so guys that's how you can use the set operations also in the sequel commands so for the Union operation you use the keyword Union for the intersect operation you use the keyword intersect and for the - operation you use the command except right so we'll be doing that very soon now apart from the previous tree operations there's something also known as the Cartesian product operation right so the cross product or the Cartesian product between two relations is basically the attributes of you know one relations followed by each attribute of the other relation so suppose you have two relations that is a and B as you can see on the screen so the cross product of a and B will result in all the attributes of a followed by each attribute of B so basically what will happen is that each record of a will pair with every record of P right so as you can see you in the screen we have Shania to Sunna with age and sex and in the B relation we have the ID and the department's name right when you cross product these two relations you can see the output that you know Shania with age 34 of gender female is followed by the attribute that is what in tag now similarly Shania 34 F is again followed by - and marketing right so that's what cross-product means when you cross for a two relations that is a and B it will result all the attributes of a followed by each attribute of B right so I hope that's clear to you guys so now that you guys have understood the set operations what cross product is what basically Union intersect except is and also the Select project and the rename operations let's just switch back to our workbench and let's just see how we can execute the sequel commands right so let's get started with all these commands so let's get started with the first basic command that is the Selleck command right so suppose you know you want to retrieve the name and address of all those employees who work for the administration department right so what you'll do is you'll just type in select first name and let's say last name from employees that is basically our employee table and then you'll mention the wear condition we want to retrieve the data for all those employees who work for the admin department right where the department number is equal to four right so I'll just mention this in quotes all right so now once it is done let's just run this query and over here let's go to result create over here so as you can see on the screen you can see that you know you've got all those employees who work in the admin department you have retrieved the first name and the last name with the Select query let's say you also want to retrieve the address what you'll do is you'll just mention the address column name over here and then let's just run this query again so once you run this query you'll see that you've retrieved the first name last name and the address of all those employees who work for the administration department right so guys that so you know you can use the Select statement so now let's say you know if you also want to retrieve the data for every project that is located in Houston you want to list the project number the controlling department number the department managers last name address and the birth date right so for that what you'll do is you'll write in a query like this so you'll just type in select and then over here you'll mention whatever you want to retrieve right we want the project number so we'll type in three number we want the controlling departments numbers that is teen armed we also want to find out the last name so that is El name then we also want to know the address you want to know the birth date now we want this information to come from two to three tables right we want the project number to come from the project table the department number to be retrieved from the department's table and the last name address and the Burdett to be retrieved from the employee table right we'll just type in from and then we'll mention the tables name will type in project Department employee and then you'll mention the condition red where that's where mentioning the where and then you'll see that the department number in the projectable must match the department number in the department's table so you'll mention that and you also want to find out the managers last name right so the manager SSN has to match to the employee as a sense so for that I'll just type in manager resistance is equal to assassin and also the location should be Houston right so I'll just mention that right so with this query what will be retrieved is the project number the department's number the last name of the manager the address and the birth date from the project's table Dept table and the employees table will be retrieved where the department number in the projectable should match the department number in the department table and also the manage your social security number should match to the employee social security number and also our main condition was that the location should be Houston right so now let me just run this query and let's go and see the output in the results grid so you can see that the project number the department number the last name address and the bird date has been retrieved for every project that is located in Houston right so guys that's how you know you can play around with the Select query but you can mention n number of column names or you know where you can mention different tables name through which you want to retrieve the data right now as I was talking about you know the rename operation if you remember so what I was telling there was that you can rename any of your column names right so suppose if you have any big column name then you can rename that column name so let's say we want to write a query to retrieve the data in such a way that you know we want to find out the employees first name and the last name and also his supervisors first name and the last name right so for that what you can do is you have to use the ask command right so basically with the ask command you can see that you know you can rename any of your column names so let me just write the query for you guys so to retrieve this data you'll try it in select and let's say e dot first-name that is basically employed or forced in that is the employed table to be e and similarly I'll type in for last name and for the supervisor attribute that will be basically identified by the super assistant I'll just type in s dot first name and S dot last name from over here comes the main game guys so what you have to do is you just type in the table steam that is employee over here and over here you'll use the keyword as and mention e and similarly you'll type in for the supervisor right so what you'll do is since for supervisors we do not have any separate table and it is self relationship to the same table that is the employee table I'll again mention employ as s where eat dot super SS n is equal to s dot s Essen so what's basically happening here is I am selecting all those employees first name and the last name and also their respective immediate supervisors first name and the last name from the employee table with the help of the self relationship that we had with our entity relationship diagram and I'm just renaming the employee table as key for the employee to separate a date of the employees and the supervisors and this can be identified with the Social Security numbers right so we have what I've done is I've just mentioned the super assistant and the SSN so for every employee who has an immediate supervisor must be equal to for every supervisor who has an immediate employer under him so I just run this command and then you can see that you know seven rows are returns alright so as you can see the output we have got all the details of all the employees who have immediate supervisor thread so our employees are basically Emma Java Alicia Franklin Jennifer John Joyce and Ramesh and the supervisor slaves are as follows right so that's how you can perform the rename operation now moving on to the project operation as I mentioned before the project operation was used when you want to find out the distinct value or I can say where you only want some columns to be included and not all the columns to be included right so what you can do is suppose you want to write a query where you want to retrieve the data of all the distinct salaries of all the employees so what you could just simply do is you just have to type in select distinct and since salary is a column name will type in salary and we'll say from and then we'll mention employee right that is basic a table's name so let me just run this command for you guys and now let's see the output so these are the distinct salaries of all the employees present in the database from this particular query now if you guys remember I also told you about the cross-product right the cross-product is where when you have two relations so when you cross one of both these relations the result would be all the attributes of the first relation followed by each attribute of the second relation so suppose if you want to perform the cross-product operation with the help of the sequel commands let's say we want to select all the combinations of employee SSN and the department's T name in the database tray so for that what I will simply do is I'll choose the Select command I'll type in SS in and D name right I'll simply save from employee and Department right so when I execute this command you'll see that you know the output will be all the combinations of employee SSL and Department ste name right let me just run this query alright so let me just open the result grid and expand it for you alright so as you can see we've got so many tuples of all the combinations of all the employee s essence with the department's T name right so this is nothing but the cross product we just chose one attribute in a relation a that is basically our employee SSN and in the second query we chose the department's T name and we've got all the values of them right suppose if we want to change this and let's say we also want the birth dates right so I'll just mention birth date over here and let's just run this query so let me just open the result grade option and over here you can see that you know the S sent the Burdett and the D name has been given as an output so like this you can perform cross product with the sequel commands right now moving on to one more interesting over here that is using the asterisk so when I say Astrid always remember that it is used to select all right so suppose you want to retrieve all the attributes of an employee and the attributes of the department in which he or she works for every employee who works in the headquarters department so for that it's really simple you just type in cell and you use astrick and then you say from you mentioned employ and department that is basically a tables name and then you mentioned the condition where our t name is basically headquarters and then you type in d number is equal to t number right now once this is done let me just end this query and let me just run this query okay and over here you have to mention the ant command also so that you know it considers both the conditions that you've mentioned let's just run this query and you can see that you know one row has been returned so let's just check which row has been returned alright so you can see that you know the first day middle name last name and all the information that means all the column values of employment department have been mentioned over here for an employee who works in the department headquarters right so that's how a guy is you can use the asterisk also now I think that's enough of the Select operations and simple sequel queries right now let's just quickly jump onto destard query so that you guys understand what exactly nested queries are and then you guys can start playing around with your database with nested queries right so basically nested queries as the name suggests are those queries which have an outer query and an inner query so the sub query is a query nested with another query such as select insert update or delete right so as you can see on the screen this is basically our outer query and this section is basically our inner query so as you can see we have a query that is select first name last name from employees with a dress code in is again a sub query where you have to select the a dress code from office where the country is India right so this kind of query is where you have two different queries connected with each other to give uh single output is basically known as nested queries let's get started with our nested queries now let's say you know we want to make a list of all project numbers for the projects that involve an employee whose last name is let's say Narayan either that's a worker or a manager of the department that controls the project right so for that for we have to write a query to make sure that you know the last name is Narayan and then we'll write another query wherein you know we'll find out all those project numbers so let's just start writing the query so let's select distinct project number from project Department employ where dhinam in the project stable must match the denim that is basically the department number in the department so that is t number and manager essence from the employee table should match the SSN of an employee right that's because we've mentioned the condition that you know that person can be either a worker or a manager right it can be either of them so we also have to mention this particular condition and also we want the last name to be nara and right so I'll just mention Narayan all right so let me just put this in bracket now we also want the data of all those employees who are handling the projects right so for that we have to match the employees social security number to the ESS n that is present in the works on table so that we know which employees handling which project right so we'll just type in Union that is basically we're using the Union operation and then we'll again type in select distinct that is we want only few column names that is P number from project work so on and employ right so I'll mention all these tables names where our project number in the projectable should match the project number in the works on table and also what I want is I want the employee SSN in the works on table to match with the employee s tisson in the employee table right well type in employee as a cent is equal to SSN and over here also I'll include the condition of last name to be neurons right now once this is done let's just run this query and check so this is basically a nested query skies so you can see that you know we have an outer query and then we have a sub query right so if you see in this matter this comes something like this this query will be first executed and then its output will be Union to this particular query right all right so when I run this particular query you see that you know your one row has been returned so let's just go to the result grid and see our output all right so you can see that you know our pea number has been returned that is basically tree right so we've retrieved the data of the project number so that's what we wanted to do right we wanted to retrieve the project number for those projects that involve with employee whose last name is basically Nara and either as a worker or a manager of the department that control super check right so that's how guys you can use nested queries now let's just quickly do some other nested queries so that you get a hand of it now suppose you want to retrieve the data of all those employees who work the same project and ask combination on some project that the employee Frankland works with right so I repeat what I'm going to retrieve the data for so I'm going to write a query to retrieve the data of all those employees who work the same combination on some project that is basically the same number of project and ours on some project that the employee Franklin works with right so what we'll do is we'll write a select command and we'll say distinct ESN so that you know we retrieve all the employee assistants who work similarly right Schramm works on where our project numbers and ask combinations should match with those of mr. Franklin right we'll just mention P number and ours so here I'm introducing you to an N operator so with this in operator what happens is that it will execute this command in such a way that you know it has to match with all the queries over here have to match with all those queries that you mentioned after the in operator right so basically what the in operator does is it will allow you to specify multiple values even after the where Clause right so if you observe over here we just mentioned one parameter that is basically you know it has to match it all but we did not mention the combination in any of the previous queries right but over here we're mentioning the combination of the project number and ours so let's just write our sub query now so that is basically we will write against select and then I have to put it in brackets and I'll say project number and ours from works on where our employee ID that is basically an alias n is equal to the employee ID of Franklin right so let's just check the employee ID for Franklin that is basically triple treat double for four files right so I'll just close this and then I'll execute the query so this is also a nested query skies with the in operator what's basically happening is we're retrieving all that date of the employees who are working on the same combination of project and ours as that of an employee assassin of triple three double four four fives right so let's just run this particular query so you can see that in our query has been executed so let's just check our output so as you can see guys we have retrieved three rows that means three of our employees are working in the same combination of project and the ass as that of the employee as Franklin right so guys that's how you know you can play around with nested queries all right so now you know if you want to write a query to find out you know the list of managers who at least have one dependent write so for that you have to use the exist operator so let's just see how you can do that so let's say you know we want to find out the first name and the last name of all the managers who have at least one dependent right so for that I'll write in the query ass select first name last name from employee where exists select star that is select all basically from dependent where our SSN of dependent should match to the employees in right so what I'll do is I'll just say SSN is equal to e SSN right so let me just check if it's ESSN or not yes it's ESSN right and then I'll close the bracket and also I want all the managers names right so I have to match the manager SSN to the employees and also so for that I'll use the exist operator and say exist select star from department where SSN is equal to manager SSN close the bracket over here all right so let's just run this particular query and you can see that two rows have been returned right so let's just go to the result grid and check those two queries so we've got the first name and the last name of the managers who at least have one dependent let's just save it they do not have any dependent I'll just change this command to not exist and let's just run this query again right so you can see that you know your query has been executed so we'll just go to the result grid and you see that you know John Smith is a manager who has nobody dependent on him so that's that so you know you can play around with the nested queries now over here let's just check some other commands such as you know the aggregate functions or the like operator the between operator and so on right so let's get started with those commands now now let's say you want to retrieve all employees whose address is Houston Texas right so for that you'll just type in the commands select first name last name from employee where address like I said address should be in Houston Texas right and if you observe the address that you've mentioned in the employee table you have a variable characters right so for such kind of variables what you can do is you can use the like operator so you just use the like operator and then you save in quotes percentage Houston Texas person tastes right so what's basically happening over here is that anything that is even before Houston Texas or after Houston Texas will be taken into consideration and all those values will be printed out right so let's just run this query all right so you can see that you know you've run this query okay it says zero rows returned that's because we've not given proper spacing let's just give proper spacing as we mentioned over here right so let me just mention it over here all right now let's just run this query again and you'll see that you know fighters have been returned so let's just go to the result grid all right so you can see that you know John Franklin choice change and Amorth have the address of Houston Texas right so that's that's how you know you can use the like operator so basically anything that you have before the percentage operator over here and even after the percentage operate over here would be taken into consideration and would be printed out so the only thing that matters and that has to match the condition is present between the percentage operator so now suppose if you want to find out all those employees who were born during the 1960's it's really simple you just type in select first name last name as I want the first name last name to be retrieved from employees and I'll say where birth date is basically like I put in codes let's see how I've mentioned the date so you can see that you know I've mentioned the date like this right so let me just copy this and let me just paste it over here and now let me tell you over here that you know you have to remove all this and you have to mention underscores slow so let's mention underscores over here but yes you have to leave the six because you want all those employees data who are born during the 1960's right so 60s would be coming over here so let's just run this query now and see if it runs or not all right so now let's go to the result grid and check out our output so you can see that you know mr. Ramesh Emeth Alyssia and John were born during the nineteen sixties right so guys you can definitely check with your tables that you have and you can review it the answer so that's how you know you can use the like operator now suppose you know if you want to find out all those employees in the department for whose salary is between ten thousand to forty thousand how would you do that well for that you have to use the between operator so what you do is you just type in SIL leg and use the asterisk so that you select all from employee where salary between ten thousand and forty thousand right and also a department number months before so I'll just type in department number is equal to four so over here let's just end this query over here and let's just see the output so once you run this query you can see that you know two rows have been returned so again let's just check those two rows okay so you see that you know the first name middle name last name of all those employees whose salary is between you know ten thousand to forty thousand working in department for has been returned so you can see that you know both these people have the salary of 25,000 so let's say you know you change this to around twenty thousand and let's say we change this to five and let's run our query again so you'll see that you know around four tuples have been returned or four different employees who are working in department number five and are earning between a solve a range of 20,000 to 40,000 right so that's how you know you can use the between operator now after this query down let's say you know you want to write the query to retrieve the data of all those resulting salaries you know if every employee working on product said is you know is given thirty percent raise right so forth add you again use the as operator that I had mentioned before so you just type in select a dot F name and let's say e dot last name and we say one point three because that is 30% raise multiplied by e dot salary as increased salary so whatever a value that we are calculating has been stored and increased salary from employ as II works on as W and let's say we have the project as P where II thought SS n is equal to W dot SS n basically we are matching the employees Social Security number to the project he's working on basically from the works on table and then we're also matching the project number right so we'll mention aunt W dot project number is equal to P dot P number also make sure that you know that the product is basically product said right so we'll just mention products names we will say P dot P name is equal to product set right so this will basically you know retrieve all the salaries of all those employees of God or thirty percent raise and are basically working on the product set so let's just run this particular query you can see that you know two rows have been returned so let's go to the results grid all right so you can see the output that you know Franklin and Ramage have been working on the product said and they increase salaries around thirty five thousand and forty nine thousand four hundred respectively right so that's how you know you can use this command now let me tell you one more thing over here you can also use the S operator over here so that you know you find out the data whether it exists or not right so let's say you know we want to retrieve the name of all those employees who do not have supervisors so when I say they do not have supervisors that means that value should be null right so what I'll do is I'll just simply write the query select F name last name from employee where our super SSN is basically null right so that means the square you will retrieve all those employees who do not have supervisors so I hope that's clear so let's just run this query and see the output so you see that you know one row has been returned so let's just check which row is that so you can see that this particular employee that is shapes does not have any supervisor so that was about the different operators such as the between like is none now let me tell you something about the aggregate functions now all of you must have heard about the aggregate functions right what they mean is basically they're just used to perform the mathematical operations now let's say you want to find the sum of salaries of all employees the maximum salary the minimum salary and also the average salary it's really simple guys you just have to type in select and then you have to use the aggregate function that is sum and mention the column names so that is salary and similarly you will mention max salary men's salary and also average salary right and then you'll mention all this has to be selected from the table stream that is employed right so when you run this query you'll see that you know the sum of all salaries the maximum minimum and the average salary has been returned by it so let's just check that output so you see that you know the sum of all the employee salary is around so much the maximum sal is 55,000 the minimum salary is 25,000 and the average salary for all the employees is thirty three thousand five hundred so guys this was a really simple basic query now guys you know you can also count the distinct salaries values in the databases so with that you use the count aggregate function it's really simple you just type in select count mention the columns names that the salary and over here you mentioned the keyword that is testing because you want the distinct salary from employee so what you do is you just run this particular query and then you see the output that you know you have six distinct salaries in the complete database right so guys that was about the aggregate functions now moving on you know we also have commands that I mentioned for data manipulation that is the group by order by having clauses so we'll start with the order by clauses and then eventually we will go into the group I and II having clauses so consider a query where you want to retrieve a list of employees and the projects that they're working on ordered by the department and within each department they're ordered alphabetically again by the last name and then the first name right so what you'll do is basically you'll select the department's name the first name last name of employees and the project's name right so you select d dot d name eat dot F name II thought L name P dot P name from apartment employ project and what I want to mention is I have to mention Department as T right so I'll just type in Department D employ II project P works on W now where we don't mention all the conditions tells us basically we want to match the department number so we'll bounce the department number of from the department's table to the department number in the employees table and also the working essence right that is basically the employees working on the projects and also the project number so it's really simple you just have to type in T dot T number is equal to e dot T numb and a dot SS n is equal to W dot e is a sin and W dot Project number is equal to P dot P number right now this has to be ordered by the department and within each department we wanted to be ordered alphabetically by the last name and then the first name right so we'll type in order by T name then we want to mention the last name so that is e dot L name and then e dot F name right and then I'll close this particular query so what's basically happening is I'm retrieving the names of the employees the project name and the department name true on vegetative work and they're ordered by departments name the last name of employees and the first name alphabetically so let me just execute this query all right so you see that you know 14 rows have been returned so let's just check the output so you can see that you know all our tuples have been ordered alphabetically that is my D name first name and the last name so you see that University administration comes then the headquarters come and then that the search comes all right now suppose if you want to get the information of all the employees in such a way that you know they're ordered by the Salvi's in the ascending or the descending order what you just do is you say select star from employees so that is employed table and then you say order by salary in the ascending order so let's say we want them in the ascending order and then you run this query you can see that you know eight rows have been returned basically all the entries in your employee table and all of them are ordered in the ascending order right so let me just show you that so when you see the Salvi's you see that you know you have in 25,000 fours coming up then 27 and 30 and 38 right so that's how guys you can play around with the order by clause now coming to the group by clause group by clause is basically used to you know retrieve the data in a group form so suppose you know for each department you want to retrieve the department number the number of employees working in the department and also those average salaries of employees working in that department what you simply do is you just type in select t num that is count star and then you say average salary so count star is basically you know to count the number of employees and average salary is an aggregate function that I had mentioned before so you just say average salary and then you say from employee group by department number right so basically the values will be grouped by department number so when you execute this query use basically comes to know that you have around three departments so the number of employees working in the three departments of 1 3 4 respectively and their average salaries are 55,000 31,000 and 30,000 right so that's how you know you can play around with a group by clause now not only this you can mention any number of conditions over here so let's say you know you want to retrieve for each project the project number the project name the number of employees working on that project you just again write the query like select project number project name and count so that is basically counting the number of employees working in that particular project and then you say from Project and works on table you say fair project number in the projectable should match to the p number in the works on table and the group by the project number in the project name rate so we'll just type in project number project name and now let's just run this query so when you run this query you can see that you know six rows have been returned and all of them are grouped by the project number and the project name right so let's just see the output so you get the output of project number and project name and you get how many people are working on the projects right now that's all guys you can play around with the group by clause all right now coming to the having clause the having clause basically allows you to put in conditions you know when you use the group by function so let's say in the query that we just wrote we want to retrieve only that data with the count is greater than two right so for that you just type in having count greater than two I'm sorry I had to mention astrick also yes so what will happen is this will retrieve only that data with the count this to end is also grouped by the P number and the P name right so let's just run this particular query all right so if you see this output you see that you know you get only two tuples but initially when you did not have this having Clause over here you got all the tuples that were grouped by the P number and the P name right so that's how guys you can play around with the order by group by and the having clauses now let's just move on let's just go back to our presentation and let's just understand what exactly joins are so when you talk about joins as you all know that you know joints are basically used to you know combine two queries or you stick to tables based on the conditions so basically the default type of join in the joint table is called the inner joint where the topple is included in the result only if the matching tap will exists in the other relation so basically there are two types of joint skies that is the natural join and the conditional join so if you have a natural join between two relations or two or more relations then the result will be a set of all those combinations of tuples with a core common attributes so basically you do not mention any condition over you just directly joined these two tables so as you can see on the screen we have the employee table and the department table right so what I've done is I've basically performed the National join between the employee table and the department table so as you can see on the screen when you perform the - till join what has happened is that that the common attributes between these two tables that is the department name has been checked so you can see that the ID sales are the two common values present in both the tables so when you join these two tables you see that you get only those tuples you know where you have equal attributes right so you get Shanaya at all and Ave in the output with all the columns of the department that is a department name and the manager's name right so that was about - will join guys now if you see - the conditional join this is basically you mention any condition right so in a natural join the default condition is that it is equal between the common attribute while in the conditional joint you have to specify any condition right so we have two set of table cells a and B now the condition between a and B is that a smocks should be greater than or equal to B right so whenever the ACE marks a greater than or equal to B then when you join these tables all the attributes of a would be followed by each attributes of B which satisfy this particular condition right so you will see that you know 46 is greater than 21 23 so you see those marks over here 55 is again greater than 21 23 you see this marks over here but 60 yes is greater than all these 3 marks so you see the values of all these 3 marks with the respective other tupple values right so that was about conditional join guys now let me tell you one more thing over here this is not an end to the story because there are also ways are the types of joints that you have to understand that is basically the inner join the left outer join the right outer join and the full outer join so when you perform inner join in the table what it returns is that it only pairs the tuples that match the joint condition so that is basically the conditional joiner I'd mentioned so basically whatever tuples match the condition only those tuples would be retrieved but when it comes to the left outer join the right outer join and the full outer join in the case of the left outer join every tuple in the left table will be appearing in the result and even if it does not have any matching tuple it will be padded with a null values for the attributes of the right table similarly for the right outer join every couple in the right table will be appearing in the result and even if it does not have a matching tuples then it will be padded with the null values for the attributes of the left table and finally coming to the full auto join then this is basically a join which will include the tuples of the left table and also the right table so that goes about the joint sky so as you can see on the screen this is a simple summary of all the joints that you just understood so I hope that you guys have understood the basic concept of joints now let's just go back to our my sequel workbench let's just see how do we implement joints in the database that we're using so now let's say you know we want to retrieve the name and address of every employee who works for the research department it's really simple to write you just type in select and let's say first name middle name last team address from employee join department I'm joining the department because you know Department number has to match between both of these since we have to retrieve the name and address of every employee who works for a department research and that research department name is been stored in the department table right we'll join the employee table and the department table on day number is equal to the number that is basically we're trying to match both the department numbers from both the tables and we'll save where that is basically a condition the name is equal to the search right so when you execute this query you'll get the name and address of all those employees who are working in the department research so let's just see the output oh I'm sorry I missed out of coma so we didn't get the last name so let's just run again okay so you can see that you know John Franklin Joyce and Ramesh have the middle name last name and address which I've been retrieved and all these people are working for the department research right so that's how you know you can use a joint now let's just see one more query before we move on to the next section so let's say you know we want to retrieve only those employees who have a supervisor right so it's clear that you know a super assassin whose value will be null will not be included so for that you just have to you know write a query like select let's say e dot first name as employee name and s dot L name as supervisor name from employee table rate so we'll type in employee as II left outer join again employ as s and then on a dot SSN is equal to s dot super assassin right so you write the query like this now let's just run this query again and let's see the output it says twelve rows have been returned so you can see that you know the employee name and the supervisors name has been returned so for every employee who as a supervisor name has been returned but yes if there was no supervisor for any particular employees then null value has been returned right now similarly if you had applied the right how to join for this particular query then you would see that you know for the supervisors who do not have any employees and the temp the null values would be returned over here right so that's how guys you can play around with joints so now let's move on to a next topic that is views of use basically it's a single table which is derived from the other tables so suppose if you have two tables so when you pass a query to create views what will happen is it it is a single table which comes as an output but yes it is derived from both these tables right so you can see that the columns are derived from both these tables right so that's what exactly views are so basically views are used when you want to understand the tables more and then you want to work on a particular table without disturbing the database right so let's just switch back to my sequel workbench and now let's just you know create views over here so before I start creating views let me just make you go through the syntax so suppose if you have to create a view right so what you have to do is to create a view you just have to use the query create view mention the view name and then ask and then use the keyword as select mention the columns names that you want to include in your view from the table so you have to mention the tables name and then you can also mention a condition right so to update a view you again have to type and create or replace view view name you have to mention the view name and then you select basically write the same query as that of creating view and then you update your view right and again - obviously drop of you it's really simple you just have to mention draw a view and the views name right so now let's just switch to our my sequel work so let's say you know I want to create a view on books on table right so I'll just type and create view and I'll mention the views name so let's say the views name is works and I'll say as select and then I'll mention the columns that I want to include right so I'll mention first name last name project name number of ours from the employee table that is for the first time at the last name the project name from the project table and the us from the works on table rate so I'll just mentioned work so on and then I'll mention the condition right so our assistants have to match obviously that is basically a primary key to which we understand which tuple is been uniquely identified so we'll just type in where SSN is equal to employers the sane of the works on table and also the project number in the project table is equal to the project number in the works on table right so once this is done if you execute your query your view has been created right so that's how you know you can create a view so similarly you can also drop of you you can just drop this view so you just have to use the command drop view and let's say works so when you execute this command you can see that you know your view has been dropped and there's no view which exists right so guys that was about the views now moving on to triggers so triggers are basically triggered you know when you perform an action right so these are basically a set of sequel statements which are stored in the database catalog and are executed or fired whenever an event associated with that with table occurs so basically you can just understand that a trigger can be invoked either before or after the data is changed by insert update or delete statements so the way this kinds of you know triggers that you can invoke at the before insert the after insert the before update after updates the before delete and after delete so the before insert is basically activated before the data is inserted into the table the after insert is activated after the data is inserted into the table the before update is activated before the data in the table is updated the after update is activated after the data in the table is updated now the before delayed is activated before the data is removed from the table and the after delete is activated after the is removed from the table right so that's how guys you know you can use trigger so let's just see in a workbench how we can use triggers so if you have to create a trigger what you have to do is you have to just simply try to create trigger so suppose you know we want to create a trigger so that you know whenever a new employee comes and the Salvi's updated or inserted the supervisor of that particular employee comes to know right so for that you'll just type and create trigger and you'll give a triggers name let's say you know Sal info now we'll mention the action that is before insert or update of basically salary rates so we'll mention salary the supervisor under school SSN on employ for each row when new salary is greater than you know select salary from employee where SSN is equal to new dot supervision SSN right and then it has to inform the supervisor so I'll type in inform underscore supervisor new dot supervisor underscore SSN and new dot SSN right so that's a basic example of how you can create triggers guys you just have to mention the name and then you have to mention the action and then you have to mention and which condition that you want the trigger to be invoked right so that's basically how you can create triggers guy is the moving on to the next topic that is basically transactions so transactions basically group a set of tasks into a single execution unit so basically each transaction begins with a specific task and ends when all the tasks in the coop successfully complete right so a transaction can have only two results either it is a success or it's a failure right so there are basically five commands related to your transactions so the first one is the set transaction so this basically places a name on the transaction the commit transaction with this command you can save all the transactions to the database since the last commit or the rollback command right so it's basically like gate you know when you commit something that you know you can just save it right now the rollback command this command can be used to undo the transaction since the last commit all the rollback command that was issued the save point creates points within the groups of the transactions in which to rollback and coming to the last one that is the release safe point this command is used to remove a save point that you have created so all these commands are basically used to control the transactions all right guys now moving on to the next topic that is basically x x so x x are basically a function which return at a time value based on the given date or date/time values so let's say you know we execute this function so let me just switch back to my workbench all right so let's say we type in select time stamp and we mentioned let's say 2017 zero nine zero nine right alright so let me just close this and let's just run this particular query so you can see that in a one row has been returned so let's just go and check that row so you see that in a one row has been returned and you get the date and time value right as you've mentioned so suppose if you had mentioned two expressions then you would get the value of two expressions right so basically if there are two arguments specified within the function then it first as the and argument to the force and afterwards returns at a ten-time value right so that's a basic concept of my sequel so guys that's all for today's session I hope you found this session informative and I hope you guys have understood data database instead of waste management systems the relational database management systems and obviously the sequel right so I hope that you guys had a lot of fun you know executing the queries so guys that's all from my side today thank you and have a great day I hope you have enjoyed listening to this video please be kind enough to like it and you can comment any of your doubts and queries and we will reply them at the earliest do look out for more videos in our playlist and subscribe to Eddie Rica channel to learn more happy learning

Channel: edureka!

Views: 364,732

Rating: 4.8094425 out of 5

Keywords: yt:cc=on, mysql tutorial for beginners, mysql tutorial, mysql database, mysql, mysql training, mysql workbench, what is mysql, learn mysql, mysql basics, mysql edureka, sql edureka, mysql download, mysql commands, mysql insert, introduction to mysql, mysql command line, relational database management system, what is database management system, edureka

Id: WmGgxTpGs_8

Length: 125min 57sec (7557 seconds)

Published: Tue Oct 23 2018