Transcript for:
SQL Full Course by Intellipaat

[Music] hi everyone i welcome you all to the live session on sql full course by intellipaat this session is conducted by multiple experts who will be teaching you everything about sql from scratch to advanced level but before that do subscribe to our intellipaat channel and also hit on the bell icon so that you will never miss an update from us now let's see the agenda firstly we will begin with introduction to database then after that we will see introduction to database management system then later on we'll see sql concepts from basic to advanced level then after that we will see how to install sql server post that we will be doing a quick hands-on on how to hack login credentials of a website and also how to prevent attack from happening then later on we'll be seeing the difference between sql and mysql then after that we will be seeing sql career job and salary trends then lastly we will be covering sql interview questions and answers now let's begin the session we live in a world which is surrounded by data right so what is data uh in my opinion data can be considered as the facts which are basically related to any object which we are talking about so uh you know data can be age well age is a data uh you know you can put that in numerics you can put that in a form where people can take note take note out of it and you know make sense out of it because uh you know age can be anything from 0.1 all the way to 100 and much more as well right so age is a numeric in which we can gauge something and get something out of and then if you're thinking about the other forms of data again we have video data as well right so we do is what we call as unstructured data but then if you do not know what unstructured mean do not worry uh at this point you need to know that at this point you need to know that pretty much you know video is a form of data as well and then there are other data as well when you think about all the data we generate by you know leaving messages to each other on whatsapp or leaving tweets on twitter or sharing photos on instagram or putting some updates on our timelines on facebook so all these uh stuff and lots and lots more is what we call as data guys so this brings us to the question what is a database you might have heard of data so if you've heard of database guys head to the comments section and put down the comments as usual i we all love an interactive uh session so and you guys are very good at it as well guys so head to the comment section and put your opinions down so what is a database well a database is basically nothing but a very systematic collection of data in the most simplest terms so why would we go about needing uh the systematic uh you know collection of data well it's pretty much to you know support some sort of storage where we can uh hold the data maintain the data and eventually we can you know we have to have the ability to manipulate the data in in in a very easy way to be honest so to give you examples of what databases actually are uh if you guys remember when we were you know a couple of years ago we used to get these telephone directories where they used to be all the numbers it's called yellow pages in a couple of countries as well so that is a very good example of a hard copy of a database and the contacts option on your mobile phone right now is also a database well then coming to power generation power generation you might not know has a huge database of you know what power is being consumed by what house and whatnot because again at the end of the day you get billed for the power you use and this data goes to your local uh electricity power supply board and again this forms a very big database as well because pretty much they need to know what the power they're generating versus what's being used and many many other details as well guys and then coming to social media as i just told you uploading images uploading statuses and uploading stories what not so all of these are stored in a database in secure data centers what we call especially when it comes to social media and the privacy things so pretty much you know all of these are stored in in the database and it is stored securely guys so basically this brings us to the question why would we ever go about needing a database well i've pretty much tried to keep this session very simple uh and this is these these are the reasons that i could pretty much think of of you know why pretty much we would need a database the first thing in my opinion is that we need a database because managing large amounts of data is a hassle because you might say you know what i can store data in a spreadsheet and it's going to be fine but then when you think about very very large amounts of data large chunks of data and throw it all into one sheet and make it work it might not work or it might be extremely difficult even if it works right so uh you know let's say the size of the data increases from a couple of you know tens of hundreds of records to thousands and millions of records so when this happens you know it will create a huge problem if you're using spreadsheets to maintain this data and at the end of the day it is gonna hurt with respect to efficiency time and whatnot as well right so this is the first point of why you initially why you should need a database and the second thing is the accuracy of the data because when you think about it doing data entry pretty much into a spreadsheet sounds very easy but then it becomes extremely difficult to pretty much track how accurate the data is uh you know on the spreadsheet because at the end of the day if you cannot have a method where you can validate the spreadsheet to find out if your data is accurate or not it doesn't make sense and again think about this manual validation of thousands and millions of entries and verification of the same it are pretty much near impossible and even if they were possible pretty much it brings the efficiency right down to zero right in my opinion so this brings us to the third reason of pretty much why would we need a database guys so uh in my opinion again it's the ease of data uploading because with the database you have very good amount of flexibility to pretty much you know update the update the data present manipulate it play around with it create it delete it whatever you want at your convenience so you do not have to wait for the person who created the spreadsheet go through millions of entries and do all of that so again you know uh the best advantage of a database is that multiple people can edit the data at the same time be this brings up the efficiency and pulls it from 0 to 100 you know almost 100 in my opinion guys so what do you think about the efficiency when it comes to the database technologies of today head to the comment section and do let me know on that note this brings us to the next uh reason why you should consider the database that is pretty much because of data security because uh you know we do have passwords for spreadsheets and whatnot but then the data is considered to be less secure and it makes it gullible because you know every gun can pretty much get access to all of these files they can do some changes remove whatever they want or you know add whatever unnecessary stuff and it hurts uh the data at the end of the day but then with databases pretty much you can have something called as uh security groups privilege accesses and so much more so at the end of the day here you can pretty much uh you know restrict access uh to a certain number of people certain uh you know people with the privilege to access the data manipulate the data and whatnot so your data will be in good hands at the end of the day so what does that mean well adding on to that this brings us to the next point which is data integrity data integrity is one of the big questions today when we're storing uh you know data in in spreadsheets as well as databases because again we've already spoken about how data can be gullible when we put it in spreadsheets but then when you put it in a database you know you are assured of accuracy number one thing it is extremely efficient number two and number three your data is consistent because databases many multiple database service providers today pretty much you know go about giving you very good integrity checks very good access controls privilege accesses member accesses and so much more so this brings us to something uh you know you might have heard of or you might have used in college or you might not know as well it's called as dbms well dbms pretty much means database management system guys so what is dbms well dbms in the most simplest terms is basically just a collection of programs which enables the user whoever is using this particular dbms software to go about accessing a database creating a database manipulating the data uh you know deleting the data so basically this gives the user an interface where he can where he or she can you know talk to the database and pretty much work with the database edit the database delete the database and do whatever the person wishes to uh if he has the right access and this basically gives the person you know a very good interface guys so i bought three facts for you with respect to your database management systems and the first thing is that dbms is not a new concept it was first implemented pretty much in 1960 so that is how old a database management system uh is and then when you can think about it uh the person who pretty much uh gave us the first dbms system pretty much is called as charles bachmann and uh his ids is considered to be the first dbms ids is basically integrated data store is what mr charles chose to cause call uh his first dbms machine guys and then third important fact is that today we know that the database technologies have evolved so much and all of the functionalities have been given and these functionalities increase every quarter every year every month and then the database systems today are so powerful it drives you know millions and millions of gigabytes of data every single day yes millions and millions every single day guys i can say millions of millions as well because the world you know the entire world is basically a big data problem so uh having a place where we can handle all of these store all of these and manipulate all of these is always a blessing guys so this brings us quickly to uh the introduction to sql well so what is sql well sql is one of the standard languages we use when we go on about dealing with relational databases which can be used to create read update and delete database records guys so these are some of the operations we can perform so what does sql stands for well sql is simple it stands for structured query language guys are you going to find out why we call it the query language in just a moment but then you read the term relational databases so what is a relational database well a relational database is basically just a type of database that stores and provides accesses to all the data points which are related to one another well how are these data related to one another guys so basically when we call it a relational database there has to be some relation with respect to the data uh uh using sql and uh the you know all the dbms we called the rdbms because it's relational database management systems here we have the data organized in tables which you will see in just a second and then we map the data so that the database knows what's important and what's not and we do this using something called as keys well keys such as primary keys candidate keys secondary keys all of these provide relation between one column of the data to the other column of the data guys you'll see all of this in a couple of moments but then coming to a little bit more about uh you know relational databases while rational databases are pretty much based on what we call as a relational model where we pretty much map one concept to another the other concept is mapped to another and we have these interdependency interrelationships which are built throughout the data guys and then this makes it very intuitive and extremely straightforward you know pretty much it gives us a very straightforward way of representing the data in the form of tables as i just mentioned and then the other good thing about the relational database as uh as i already told you is that each row has something called as a record and there is some unique id called as key as well again we're going to check all of these in the upcoming slides and then we have something called as columns which any data table will have and then these columns basically hold all of the data and each record in this column basically is mapped onto a unique thing and then you know we go on about establishing something called as interdependency relationships and much more among all of the data present guys and if you're wondering how sql is pronounced it is pronounced as either sql or it's called sql guys pretty much you will find developers enthusiasts and many others either calling it sql or sql so whenever you heard of the term you know whenever you hear the top sql uh make sure to map it into sql guys it's it's pretty much simple and straightforward so uh coming to mo uh you know a little bit more about relational databases there are some flavors of sql that i thought i should tell you guys well here are some of the examples of relational databases we have mysql we have oracle sql we have microsoft sql uh we have psy base and we have many more flavors of sql as well the subtle differences in between all of these providers are pretty much you know in uh in the form of syntaxes of how we pretty much talk to the database and you know there are some proprietary syntaxes as well where you know the syntax only works with you know microsoft sql server and it might not work with mysql and so on but then it is recommended for all the beginners to pretty much go on to start with mysql and then pretty much you know jumping to any of these other providers are the flavors of sql it becomes very easy if you're a master of mysql guys and on that note make sure you stick till the end of the video whereas as usual i'm going to give you a very attractive offer just for the viewers of this particular video where you can fast track your way to becoming a mysql expert guys so on that note uh we need to quickly check out uh what a query looks like so a query is something very simple it is the actual let's say the code we write guys in terms of sql well uh a query will look something like this think about you entering this in a shell or in a particular workbench guys so we have something called a select star from data 1 where age is less than 20. if you know a little bit of sql i'm sorry so if you know a little bit of sql then pretty much you can make sense out of this but then if you do not do not worry because i'm going to walk you through what each of these mean well so basically we're going to be viewing a lot of data present in the database right so select statement is pretty much used to dispic data from those actual servers and then show it to us star pretty much means that you know show everything that is present everything from where well everything from a particular database that we have and that database in this particular name is called as data one and uh so do you want to see just see everything from data one well no i want to see some stuff from data one where the age of a person let's say we have talk we are talking about a table which contains details about a lot of human beings or college you know college people uh school people and what not so we just want to find out who all are less than the age of 20. so when you think about it uh instead of just seeing millions or thousands of entries of students who are you know aged various from i don't know three to 100 but then when you're thinking about uh efficiency then we need to bring it down to a certain condition where we go on checking it right so instead of just seeing thousands of entries let us say we only require 11 and we're going to see 11 entries when we put the condition saying age is less than 20 because there are only 11 people who are less than 20 years old guys so pretty much this is what a sample sql query will look like and uh we'll just be doing very simple operations at the end you can follow along in so on that note this brings us to this brings us to the applications of sql so sql has a couple of applications and let me tell you one by one sql can be used as a data definition language ddl for short uh what this basically means is that you can pretty much you know create a database on your own define its structure on your own use it on your own and just you know discard it when you're done with it so this doesn't have to be just you this can be among hundreds and thousands of people you can scale it obviously but then know that with respect to data definition language you have all the right where you can individually create a database define the structure and delete it when you're done with it as well guys and this brings us to the next application which is data manipulation language because sql can be used as dml as well so what it basically means is that that you know when we have something already in establishment like uh data which is already present in our database we need to go on changing something in that database we need to update a database we need to delete a row we need to update a column and so much more so in that particular case we can use sql as a data manipulation language where you can pretty much alter stuff in the table you can go on adding removing and what not so this brings us to the next application which is the dcl guys data control language so data control language is mainly concerned with your data uh in terms of how you can avoid corruption and how you can avoid misuse as well so pretty much as you know data has to be protected and uh misusing data is extremely easy uh even though with all the security measures these days so uh you know sql can pretty much be used in terms of dcl where we can you know protect the databases against corruption and misuse in a very good way as well guys and the next application of sql is pretty much that uh it can go on to be used as a very good client server language you can uh pretty much so program on the client side and the server side where you can you know connect front ends and back ends together where we have an entire architecture which is set up what we call as a client server architecture where there is one server and then i know there are lots of clients i'm sorry about that there's lots of clients you know connected to that particular server and then we pretty much you know go on uh querying and talking to the server getting the details from the server for each individual client and more guys so the next application pretty much is what we call as the three tier architecture the three tier architecture is something very similar to the client uh server client architecture where you know pretty much we have something called as an application server we have a database and all of these together in terms of the server the client and the database form uh the three basic tier architectures guys so it's the client it's a server and the database where you know the data is uh present and it can be it can go through the server where it can go to multiple machines or pretty much you can have it come to the server and talk to the client directly as well guys so on that note what do you guys think about the applications of sql can you think about any direct applications of sql if you can think of it make sure to head to the comment section right now we can check out the basics of sql tables really quickly guys well uh as i've already told you let's have a quick recap a table is basically a database object which is comprised of rows and columns in sql we already saw that it is the structured way of going about using data a table and then it can also be defined as the collection of you know related data held in a table it is related by the forms of keys uh which i told you but then there are two things that i really want you guys to visualize which is basically fields and records well fields are very simple guys fields are the column where you know we have specific data under that well consider the snapshot i have given in your screen uh we have some we have a field called as e underscore salary let's say it's employee salary and this salary pretty much you know uh is the information which we provide but then we have one header under which the salaries are all quoted right so this basically is the salary field this entire column is what we call as a field and then when we come to records records is something very simple because what is the record corresponding to a particular field uh well let's say we want uh the record of the employee name annie here annie pretty much goes on to uh you know be the employee id3 uh it's under the employee name we searched it through the employee name right so we said we want to find out annie so annie is pretty much the employee's name and the employee's name was the field and the record was number three right so it's pretty simple uh records give us an entire description a complete information of all the entities present in that particular uh data set guys so you know we already have a lot of data on the screen but then when we highlight one row uh the annie's row pretty much this row gives us the complete information about that particular employee guys so this is what we call as a record let me quickly step back where you can see fields again so this is the field guys the field is the column the record is a single entity which gives us complete information i hope i was clear with that so this brings us quickly to the data types which are present in sql guys so i will not go in depth uh because we need to keep this tutorial to the scope of all the viewers uh or present uh tonight with our session so basically uh know that you can have numerical data character data date and time data and much more with respect to numeric data we can have integer data decimal data and all of these as well so basically what you have on your screen is the data type but with the corresponding range of the numbers that you can use so you can see a big number there which is nine two two three three seven two zero three six five four double seven five double you know five eight zero eight right so this one particular number if in case you want to put in a number uh you know say you wanna put a phone number you wanna put an address or whatever it is and you need this amount of number the huge number then you go on to use uh the data type what we call as the big int guys then the normal integer supports pretty much uh in the data range that you can see on the screen a small link is a fairly smaller number somewhere around 32 000 and tiny it is even smaller where it only supports positive numbers between 0 and 255 guys and then with decimals as usual you have a huge number access to huge numbers on the negative side and on the positive side as well guys so this brings us to characters there are three main data types one is the car one is the varchar and the text again pretty much it is as simple at this point of time you need to know that you can have 255 characters at once uh when you're declaring something when you're declaring the name of a data type or when you're putting in the actual data for the data type as well guys so but then we have something called as text where let's say you want to put in a description of something so this will be a limit if you actually uh say 255 characters right so for that particular case uh you know the standard sql manufacturers pretty much have given us a range of 65 535 characters where you can pretty much i think that should suffice uh you know most of the applications where we're using the data type text where we are actually putting out some sort of description guys and then this brings us to the date time data type well basically you can go on to using date format in the format your month day and then you can go on to use the time as well time in terms of hours minutes and seconds as shown on your screen and we can just use the data type here by showing uh you know by seeing the format on your screen as well guys so yyyy pretty much means four numbers uh if you have to put it down right now it's two zero one nine it's 2019 so that'll be uh the format of the year for now guys we'll be installing microsoft sql server so all you have to do is go to google and then type microsoft sql server download and you'll get these search results now all of these searched results click this one where you see sql server downloads from the microsoft site so we are into the site now if i scroll down we see two free editions we have the developer edition and the express edition so i'd be downloading the developer edition i'll click on download and the download would start now this is the downloaded file over here sql server 2017 ssei development so we get this window over here and we have three options basic custom and download media so i'll click on custom now we see that we have a media location where the sql server will be downloaded and i'll click on install so see that the download is successful and our installation will begin shortly here i'll click on installation and i'll select this one so i'll be installing a standalone installation of the sql server so let us wait for this to open right so here i click on next now i only have one instance of sql server so i'll be installing a new one so i'll uh select this perform a new installation of sql server 2017 and then i'll select this free edition i'll click on next after that i'll accept this license agreement and again i'll click on next so we have all of these features over here so i would just need the database engine services so i'll select this and again i'll click on next so i'd have to name the instance for the server which i'm installing so i'll name this server as let's say sparta similarly i'll give the same instance id over here i'll click on next so again i'll click on next over here so we see that we have two authentication modes windows authentication mode and mixed mode i'll select the mixed mode and then enter the desired password let me put in my password over here i'll add the current user so the current user has been added now i'll click on next again right so we are finally ready to install sql server i'll click on install right so once the installation is done i would also have to install the sql server management tools so the sql server management tools basically help us to connect with the database i'll click on this and this will redirect us to the microsoft web page and over here i'd have to download the sql server management studio and i'll be downloading the latest version and this is the downloaded file over here ssms setup enu i'll click on this so here we have these three options repair uninstall and close now i have these three options because i've already installed microsoft sql server management studio in my system and if you're installing this for the first time you'll have the install button over here so you'd have to click on install and the install would be done automatically right so once you install the sql server management tools go to the search tool and type microsoft sql server management studio click on this and microsoft sql server management studio will start so over here you would have to specify the password which you gave while creating the instance and then click on connect and to start writing your sql commands click on new query over here and then you can happily implement all of your sql commands right so this was the installation of microsoft sql server how to create use and drop a database so this is the syntax to create a database in sql we will given the keywords create database and then give the name of the database which you'd want to create so let's go ahead and create a database in sql so let's create a first database guys so i will given the keywords create database and let's say i want to name the database as happy right so i'll press semicolon over here i'll hit execute so see that commands have been completed successfully now let me hit refresh over here so initially we just had two databases igneous and sparta after implementing this command create database happy we have successfully created a new database happy now after we have created a database we need to know how to select a database from our existing list of databases so we have the use database statement for this all we have to do is give the keyword use and then state the name of the database now this database which you selected remains default until the end of a session or execution of another use statement with some other database so if i put my mouse pointer over here so you see that currently we are using the sparta database so now i want to use the happy database instead of the sparta database so i will write the command for that use and then after that i need to give in the database which would be a happy i'll press semicolon i'll hit execute so again commands have been completed successfully so now we see that the database has changed from sparta to happy so currently we are inside the happy database now we'll see how to delete a database or in other words how to drop a database so we have to given the keywords drop database and then specify the name of the database which you want to draw so now i do want to drop this igneous database so let me write the command for that drop database and then the name of the database is igneous so i'll press semicolon over here i'll hit execute so again commands have been completed successfully now i'll hit refresh so we have only two databases happy and sparta so database igneous has been dropped so constraints are basically used to specify some rules on the data and limit the type of data that can go into a table we will look at these four constraints not nil default unique and primary key let's start with the not nil constraint so by default a column can hold null values and with the help of the not null constraint we can ensure that a column does not have null values so the not null constraint basically ensures that a field always contains a value for example if we assign the not null constraint to this e-salary column over here then every single entry of this column should have a value then we have the default constraint so the default constraint is used to provide a default value for a column so the default value will be added to all the new records if no other value is specified for example if we assign the default constraint to the eh column and set a default value of 25 then all the entries of this column will have the default value of 25 if no other value is specified during insertion then we have the unique constraint which ensures that all the values in a column are different for example if we assign the unique constraint to this e-name column then every entry of this column should have a unique value and finally we have the primary key constraint so the primary key constraint uniquely identifies each record in a table or simply put it is just a combination of the not null constraint and the unique constraint so when we assign the primary key constraint to a column then that column shouldn't have any null values and all the entries should also be unique for example if we assign the primary key constraint to the employee id column then there should be no null values in this column and all the entries should also be unique how to create a table in sql so we'll be creating an employee table which would comprise of employee id employee name the employee salary agent gender and the department in which the employee works so let's have a look at the steps to create a table in sql so first we have to start off by giving a name to the table after naming the table i'd have to define the columns which would be a part of a table and finally we'd have to assign a data type to each of the column which we have defined now let's have a look at the create table syntax so first we'd have to specify the two keywords which are create table and then we'll give a space and then give a name to the table and inside the parentheses we will define all of our columns and assign them their respective data types now finally if you want to give a primary key constraint we can do it over here so we will given the keywords primary key and then given the name of the column which would be a primary key so as we have already learned in a previous session a primary key basically helps us to uniquely identify all of the records in our table and guys also keep this in mind a table cannot have more than one primary key so now that we've understood the syntax of create table command let's go to msequalserver and create our employee table right so this is i'm a sql server so i'll start with the command i will given the keywords create table then i will given the name of the table which would be employee and inside the parenthesis i will give all of the columns so the first column would be employee id so i will type e id which is the name of the first column and this is of type integer after this i will also put in the not null constraint so this not null constraint means that all of the cells in this column should definitely have a value and then i will given the second column so the second column is employee name so e name and this is of type wirecraft so wirecast stands for variable length character and the maximum length would be 20. then i'll given the third column so the third column would be employee salary so i will name the column to be e salary and this would be of type integer again after this we have the employees age so the column name would be e h and this is of type integer 2 then we have the employee's gender so i will name the column to be e gender and this would be of type wire cap so this is of variable length character and the maximum length is 20 and then we will given the final column so the final column would be the department in which the employee works so let me name it to be e d e p d and this would be of type varchar2 and a maximum length of 20. now after this i can set in the primary key so i want this employee id column to be the primary key i will type primary key and inside the parenthesis i will given e id right now i will put in semicolon over here to indicate that this is the end of the syntax now i will select all of this and click on execute right so we have successfully created the employee table how to insert records into a table we can insert records with this insert into statement now let's understand this command properly so first we have to type in the keywords insert into then well given the table name following which we'll given the keyword values and inside the parenthesis we'd have to given the values for each of the column respectively now let's go to msequalserver and insert records into our employee table so let me start with the command so i will type in insert into then i'd have to given the name of the table which would be employee after which i will given the keyword values and inside the parenthesis i can given all of the records so let me start with the first record right so we'll have to start off with the employee id so the employee ids let's say one then the second column is the employee's name and this is of type wirecare that is why i will given the value inside the single quotes so the employee's name is sam after this we would have to given the employee salary so sam earns ninety five thousand dollars per annum i will given ninety five thousand over here then we have the employees age so sam's age is 45 after which we have the employee's gender again since this is a variable character so i will use the single quotes over here and sam as male obviously after which we'd have to give in the department in which the employee works so again this is variable length character i will given single quotes and sam works in operations so let me type in operations over here right now i will put in semicolon over here i will select all of this and i will click on execute so we have successfully inserted one record into our table now let me follow the same procedure and insert another record so insert into employee values and inside the parenthesis the first column is employee id which would be two after this i'd have to given the name of the employee so the name of the employee's bob and bob earns 80 dollars which is his salary so let me put in 80 000 over here then we have the employees age so bob's age is 21 then we have the gender of the employee so bob does male obviously again then we'll have to given the department in which the employee works so bob works in support again i will put in semicolon i'd have to select all of these let me click on execute right so we have also inserted the second record now let me insert the third record so again it would be the same command insert into employee values inside the brackets let me give all of the values for the columns so employee id is three the name is anne and anne's salary is 125 000 per annum she's quite rich isn't she and her age is 25 and she's obviously female now we'll have to give in the department in which the employee works so anne works in analytics let me type in analytics over here so it seems like people who work in analytics earn quite a lot isn't it let me put in semicolon over here i'll select all of these and let me click execute so similarly i will insert three more records into the same table right so we have successfully inserted six records into a table so guys this is how we can insert records into a table how to select data from our table so first we will go for the select statement and learn how to extract individual columns then we will work with the select distinct statement to select only distinct values so the select query is one of the most important commands in sql and it is used to get data from our table so let's have a look at the syntax so first we'd give the keyword select and then list down all of the column names that we want to select following which we'll get the keyword from and then finally give the table name from which we are supposed to select these columns so let's select some data from our employee table so let's say i want to extract only the employee name column from the employee table so for that i would have to given select and then i would have to given the name of the column so the name of the column is e name and then i will given the from keyword after which i will given the name of the table which is employee let me put in semicolon and let me hit execute right so i have successfully extracted only the name column from the entire employee table so similarly if i wanted to extract only the age column from the employee table we'd have to put in a similar syntax so it would be select employee age from and the name of the table is employee execute right so we have extracted the age column from the entire employee table now we can also extract multiple columns from our table so for that we would have to separate each of the columns with a comma so let's say i want to extract the name column the gender column and the salary column from the entire employee table so let me write the syntax for that so it would be select so i will given the first column which would be e-name now i would also want the gender so i will put in e-gender i'll put in a comma and then i would also want the salary column so i will put in e salary after which i will use the from keyword and then i will given the name of the table which would be employee semicolon and let me hit execute right so i have extracted three columns from the employee table and those three columns are e-name e-gender and e-salary so this is how we can extract multiple columns from the same table now we can also extract the entire data from our table and to extract the entire data from our table we'd have to use the asterisk operator so let me show you guys how it's done so the command would be select and then i will put in star after which it'll be from employee so as simple as that guys select star from employee so whatever table it is all you have to do is select star from and then you'd have to give in the table name so this is extract all of the data from a particular table so i will given semicolon and let me hit execute right so when i gave the command select star from employee i was able to extract all of the data from the employee table just a quick info guys intellipaat provides sql online training in partnership with microsoft and mentored by industry experts the course link is given in the description below now let's continue with the session now let's look at the select distinct statement so a column often contains many duplicate values and sometimes we'd want to extract only the distinct values from the column so this is where select distinct comes in so the syntax of select and select distinct is pretty much the same the only difference is we follow up the select keyword with the distinct keyword now let's select some distinct values from our employee table if we have a glance at this gender column we see that these two values are being repeated so we just have two values male and female and these two values are being repeated so now if i wanted to extract only the distinct values so this would be the command for that so i will type in select and i'll follow up the select keyword with the distinct keyword after which i'll given the column name whose distinct values i'd want so the column name would be e gender and then it will be from the name of the table which is employed so let's see what do we get select distinct e gender from employee right so we see that we only get the distinct values which are female and male now let me actually remove this distinct keyword and let me hit execute so let's see what is the difference right so if i remove the distinct keyword we get all of the values so male male female female male and female now so if we use a distinct keyword we'll only get the values which are different so these are the cases where select distinct can be used so the where clause is used to extract only those records that fulfill a specified condition for example we'd want to extract those records where the age of the person is more than 60 or filter out those records where the occupation of the person as doctor now let's have a look at the syntax of where clause so we'll start with the select keyword then we will given the list of all of the columns which we want to select following which we'll use the from keyword and give the table name and finally use the where keyword and follow it up with the condition that would determine which records are to be extracted so let's filter out some records from our employee table now let's say we want to find out all of the female employees of our company so for this i'd have to extract those records where the gender equals female so let me go ahead and write the command for that so that would be select start from i will given the table name which is employee and then i will use the where keyword and follow it up with the condition so the condition would be e gender s equal to female right so what i'm basically doing is i am extracting all of the female employees from this employee table let me hit execute right so anne and julia are the only female employees now i'd want to find out those employees whose age is less than 30. so let me write the command for that select start from employee where i will given the condition so this time the condition would be e h is less than 30 right so the age needs to be less than 30 i'll give a semicolon let me hit execute right so these are the three employees so these are the three records where the value of each is less than 30. so bob and jeff these are the three employees whose age is less than 30. now similarly let's say i don't want to find out all of those employees whose salary is greater than hundred thousand dollars so let me write the command for that so this time the command would be select start from employee where so the condition would be e salary is greater than 100 000 semicolon and execute so and matt and jeff are the three employees whose salary is greater than hundred thousand dollars so and salary is hundred and twenty five thousand dollars math salary is 159 dollars and jeff's salary is hundred and twelve thousand dollars so guys this is how we can filter out records with where clause how to extract records on the basis of multiple conditions using the and or not operators so we'll start with the and operator first then we'll filter records for the r operator and finally we'll work with the not operator so with the and operator we can basically fetch those records which satisfy all of the conditions separated by and for example we want to find out all the doctors whose age is greater than 60. so there are two conditions over here first the age needs to be greater than 60. second the occupation needs to be doctored so we'll get only those records where both the conditions are being satisfied now let's have a look at the syntax so here after we give the first condition of the verb loss we follow it up with the and operator and give the second condition and similarly if we have another condition to be satisfied we'll again give the and operator and then give the next condition so let's fetch some records using the and operator so i want to extract all of those employees whose gender is male and age is less than 30. so the command would be select star from employee i'll use the where clause and then given the first condition so the first condition is gender needs to be equal to male so e gender is equal to male i'll use the and operator and then given the second condition so the second condition is the age needs to be less than 30. let me execute right so bob and jeff are the two employees whose gender is male and age is less than 30. now similarly i'd want to extract all of those employees to work in the operations department and their salary is greater than hundred thousand dollars so the command would be select star from employee where i have to give the first condition so the first condition is the department needs to be equal to operations i'll use the and operator again and then i'll give the second condition so the second condition is the salary needs to be greater than hundred thousand dollars right execute so jeff is the only employee who works in operations and his salary is greater than hundred thousand dollars then we have the or operator so the or operator displays those records where at least one of the conditions is satisfied for example if we want to filter out either a software engineer or a doctor then we can use the or operator and this is the syntax for the or operator so similar to and operator after we give the first condition we follow it up with the or operator and then give the second condition now we'll fetch some records from the employee table using the or operator so i don't want to extract all of those records where the employee works either in operations or in analytics so the command would be select star from employee were need to get the first condition so the first condition is department should be equal to operations i'll use the or operator now and then give the second condition so the second condition is the department needs to be equal to analytics right so we have four records over here so sam works in operations and works in analytics julia works in analytics and jeff works in operations so there are four results out of which two people work in operations and two people work in analytics now similarly i'd want to extract those records where either the salary is greater than hundred thousand dollars or the age is greater than 30. so this would be the command select star from employee salary is greater than hundred thousand dollars i'd have to use the r operator and then give the second condition so the second condition is h needs to be greater than 30 execute so again we have four records over here so the first record is sam whose age is greater than 45 then we have ann whose salary is greater than hundred thousand dollars then we have matt which satisfied both the conditions actually his salary is greater than hundred thousand dollars and his age is also greater than 30 then we have jeff whose salary is greater than hundred thousand dollars so these four records we see that at least one of these conditions is being satisfied and finally we have the not operator so with the not operator we can extract those records where the condition is not true for example if i wanted to extract all those records where the occupation of the person is not equal to software engineer then i'll use the not operator so let's have a look at the syntax of not operator so here we follow up the where clause with the not operator and then give the condition now let's work with this not operator so i want to extract all of those records where the gender is not equal to female so the command would be select star from employee where after what loss i need to use the not operator and then give the condition so gender is equal to female right so before the condition i use the not operator so this would basically give me all of the records where gender is not equal to female so i'll hit execute so see that i get four records in total and all of those four records the gender value is male now similarly i'll extract all of those records where the h is not less than 30 right so select star from employee where i need to follow it up with the not operator and then give the condition which would be e of h is less than 30 so this is the actual condition e of age is less than 30 and since i preceded with the not operator i will get all of the records where age is actually not less than 30. i'll hit execute so these are the three employees who see just not less than 30. we have sam whose age is 45 we have julia whose age is exactly 30 and then we have matt whose age is 33. so guys this is how we can work with and or and not operators how to extract records with a like in between operators we'll start with the like operator for the like operator is used to extract those records from a table where a particular pattern is present for example where do you want to extract all those records where the person's name includes john so if we have records of three people in our table with the names jonathan johnny and marcus then with the help of like operator we can extract records where the names are jonathan and johnny since they have the required pattern present in them now the like operator is used in conjunction with the wildcard characters and there are two wildcard characters first one is the percentage symbol and the second one is the underscore character so the percentage symbol is substitution for zero one or more characters while the percentage symbol is substitution for a single character and this is a syntax for the like operator after giving the where clause be given the column name followed by the like operator and then the pattern which you want to extract and keep this in mind guys the pattern should be inside single quotes now let's do some operations with the like operator so let's say i want to extract all of those records where the employee's name starts with the letter jig so the command would be select star from employee where i will given the condition which would be e name following which i will give in the like operator and inside single quotes since the name should start with j i will type j after which i will use the percentage wildcard so all of those names which start with g would be extracted with this command so let me hit execute so julia and jeff are the two employees whose name starts with g now similarly i'd want to extract all of those employees whose age is in the 30s right select star from employee where i need to get the condition so e h following which i'll give the like operator and then inside single quotes the age needs to be in 30s so after three i will use the underscore wildcard character and then put in the semicolon let me execute so we have julia and matt over here so julius each is 30 and matt's age is 33. so the other two employees whose age is in the 30s now let's look at the between operator so the between operator is used to select values between our given range for example we can extract only those records where the age of the person is between 20 and 25 and this is the syntax after white loss we given the name of the column then we give the between keyword after which we given the lower value of the range then use the and operator and then give the maximum value of the range so why don't we extract all of those employees whose age is between 25 and 35 so the command would be select star from employee where the condition would be e h should be between 25 and 35 execute so and julia matt and jeff are the four employees whose age is between 25 and 35 so as you see over here this value is inclusive right 25 is inclusive so anne's age is 25 julia's age is 30. matt's age is 33 and jeff siege is 27 and these are the four employees whose age is between 25 and 35 now similarly i'd want to extract all of those employees whose salary is between ninety thousand dollars and hundred and twenty thousand dollars so the command would be select start from need to given the name of the table which is employee then i'll use the where clause and then give the condition so the condition would be e salary between so the salary needs to be between 90 000 and 120 thousand dollars i'll press semicolon let me hit execute so sam and jeff are the two employees whose salary is between ninety thousand dollars and hundred and twenty thousand dollars so sam salary is ninety five thousand dollars and jeff's salary is hundred and twelve thousand dollars with some basic functions in sql so we'll look at the min function max function count function sum function and average function let's start with the min function the min function gives the smallest value in a column let's look at the syntax first we given the select keyword then we give the min function and inside the parenthesis we specify the name of that column from which we're supposed to find the minimum value so why don't we extract the age of the youngest employee of the company so this would be the command for that select and then i'll use the min function and then pass in the column which would be e h i'll use the from keyword and then given the name of the table which would be employee so the total command is select minimum of each from employee i'll hit execute so we see that the minimum age in the table is 21. similarly if i wanted to extract the salary of that employee who would get the least salary so the command would be select min and instead of e h it would be e salary from employee so the salary of the employee who owns the lease is 73 000 then we have the max function the max function gives the largest value in a column here again we given the select keyword following which we give the max function and inside the function we specify the name of the column from which we're supposed to get the maximum value now i don't want to extract the maximum age and maximum salary from this table so this would be the command for that select and i will start off by extracting the maximum age inside the max function i will pass in the column which is e of h and then i'll use the from keyword and then given the name of the table which is employee so the maximum age of an employee is 45 similarly i want to find out the maximum salary so that would be e salary from employee execute so the maximum salary is 159 000 and there's a count function so the count function returns the number of rows that match the specified criteria that is if you wanted to find out the number of male and female employees in a company then we can use the count function let's look at the syntax so after the select keyword you'll use the count function and given an asterisk inside the parenthesis then you'll give the table name and finally you'll use the where clause to give the condition so let's use this count function to get the count of number of male and female employees so i'll start off by getting the count of number of male employees so select i will use the count function and inside the count function i will pass in the asterisk symbol after which i'll use the from keyword and then given the name of the table which would be employee then using the where clause i will given the condition so i'd want the count of all of the male employees so e of gender should be equal to male let me see what do we get so there are four male employees in total similarly if i wanted to get the count of all the female employees the command would be select count of star from employee where e gender equals female right so this would be the only difference i'll hit execute so i get two so there are two female employees in the entire organization then we have the sum function so the sum function would give you the total sum of a numeric column and this is the syntax so here similarly we will given the sum function after the select keyword and give the column name inside the parenthesis so i don't get the total sum of salaries of all of the employees right so the sum of salaries of all of the employees select and then i'll use the sum function and then i'll pass in the column which would be e salary from the name of the table which is employee so the total salary of all of the employees included is 644 thousand dollars per annum and finally we have the average function so the average function gives us the average value of our numeric column and the syntax is same as the rest so we'll given the average function after the seller cured and give the column name inside the parenthesis so this time i do want to get the average age of all of the employees so select and then i will use the avg function and then given the column which would be e h from the name of the table which is employee so the average age of all of the employees is 30. so guys these were some of the aggregate functions in sql some string functions so first we have the l trim function which helps in removing the blank spaces on the left side of the character expression so let's implement this function in sql server so let me first create a string with some line spaces on the left side so i'll type select and then i will give some blank spaces on the left side now let me pass in a string and i'll name the string to be sparta i'll hit execute right so this is the string which has some leading spaces on the left side now if i want to remove all of these blank spaces on the left side all i need to do is use the ltrim function so i will type select l trim and inside this i will pass in the string with the leading spaces so i'll copy this and i will paste it over here now let me execute both of this simultaneously right so this is the string with the leading spaces and when we use the l trim function all of those blank spaces on the left side were removed then we have the logo function which basically converts all the characters to the lower case so let's look at an example this time let me actually print a string which has all the characters and capitals so this is pata so here we see that all of the characters in the string are in capitals i'll execute this and this is what we get now if i want to convert all of the characters into lowercase i can just use a lower function so i will type select lower and then pass in the string inside the function i'll copy the string i will paste it inside this i'll hit execute so this is the string where all the characters were in capitals and after using the lower function the string where all the characters were in capitals have been turned to lowercase similarly we have the upper function which basically converts all the characters to uppercase so this time i will take in a string which has all the characters in lowercase so select i will type hello world let me print this right so here we see that initially all of the characters are in lower case now after this i will type select upper and then pass in the string inside the function i'll copy this i will paste it over here i'll hit execute so this was our initial string and after using the upper function we have converted the string into capital case after that we have the reverse function which basically reverses all the characters in the string so let's implement this too so let me take in our random string again select and i will type i love ice cream i'll hit execute so this is what we get initially now after this i will use the reverse function select reverse and i will pass in the string inside the reverse function i'll copy it and i will paste it inside this function over here now let me execute so this is the initial string and after using the reverse function we have reversed all of the characters in the string right so you see that this is the starting and this is the ending over here and after reversing it we get this final string and finally we have the substring function which gives us a sub string or part of the string from the original string now let me given the original string first so i will type select and then the string would be this is part now let's say i would want the sub string sparta from this entire string so this is where we can use the substring function so i will type select sub string now the substring function takes in three parameters first as the original string so i will copy the original string and i will pass it into this as the first parameter now after that i need to give in the index value from where the substring starts so this is the first character of the substring which i want so the index value would be 1 2 3 4 5 6 7 8 and 9. so i'll type 9 over here and then the third parameter would be the length of the substring so the length of the substring is 1 2 3 4 5 and 6 so i will pass in 6 over here now let me execute all of this and let's see what do we get so this is the original string this is part and after using the substring function we have extracted only this part from the entire string so these were some of the string functions in sql order by in the top clause so order by is used to sort the data in ascending or descending order and by default order by sorts the records in ascending order so let's have a look at the syntax we'll start with select keyword and then given the column list then we'll use the from keyword and give the table name following which will type the keywords order by and give the columns with respect to which you want to sort the data and finally we'll state whether the ordering should be in ascending or descending and i've already told you guys that by default the sorting is done in ascending order and if you want the sorting to be done in descending order we will use the desc keyword so i want to sort the table with respect to the salary column in ascending order so this would be the command for that select start from the name of the table is employee after which i will use the keywords order by and then given the name of the column which would be salary and since i want it with respect to ascending order so i don't need to add anything else so let me hit execute right so we have successfully sorted this table in ascending order right so julia has the lowest salary and matt over here has the highest salary so this is an ascending order seventy three thousand eighty thousand ninety five thousand and goes until hundred and fifty nine thousand now let's say i want to sort this table in descending order of the salary so for that all i need to do is add in the keyword desc let me hit execute right so this time the table is sorted in descending order right so over here at the top we have the maximum salary which is 159 000 and at the bottom we have the lowest salary which is 73 000. now let's look at the top clause the top class is used to fetch the top and records from a table so top class is useful on large tables with thousands of records and this is the syntax they've got the select keyword first then we type in top and then the number of records we want to see in our result followed by the column list and then we use the from keyword and given the name of the table so i want to see only the top three records from the entire employee table so this would be the command for that select and then i will use the top keyword and since i want three records the number would be three and since i want to see all of the columns i will put in star over here then i want all of these records from the employee table so this is the entire command select top three star from employee i'll hit execute right so i've got the top three records from the table now i will use the top clause and the order by clause and fetch the three oldest employees of this company so let me write the command for that select top three star so i've used the top class to fetch the top three records now this would be from the employee table and then i will use the order by clause so i want to order it with respect to the age of the employee and i want it in descending order that is the employee who has the maximum age should be at the top now let me hit execute so i've got three records over here and the records are arranged in descending order of their age so sam has the highest age 45 and julia over here has the lowest age 30 right so sam matt and julia are the three senior most employees of this company and this is how we can work with the order by clause in the talk clause with a group by statement the group by statement is used to get aggregate result with respect to a group for example let's say we wanted to find out the average salary with respect to gender that is average salary of male employees separately and average salary of female employees separately this is where we can use group by to divide the data into two groups male and female and find out the average salary of each group separately now let's have a look at the syntax so here you have to be careful of the sequence so the group by clause follows the where clause and precedes the order by clause so if you mix up the sequence then you may not get the right result and if you're not using the where clause and the order by clause you can directly follow up the group by statement after the table name so i would want the average salary with respect to the gender so this would be the syntax for that select and since i want average salary i will use the avg aggregate function and then given the name of the column which would be e salary and since i also want to see the gender column i will give an e-gender over here and this is from the employee table and since i have already given the name of the table i can use the group by statement over here and after the group by statement i need to give in the column with respect to which i have to group the data and i want to group the data with respect to the gender column so this would be my total command select average of e-salary and each gender from employee group by each ender so let me hit execute right so this is the average salary with respect to the female employees and this is the average salary with respect to the male employees so the average salary of female employees is around 99 000 dollars per annum and average salary of male employees is around 111 000 per annum now i want the average age with respect to the department and i'll also use the order by keyword to sort the data in descending order with respect to the average each so this is the syntax select since i want average age i will use the average aggregate function and then pass in the column which would be e h and i would also want to have a class at the department so e d e p d i will given the name of the table which would be employee after which i will given the group by statement and i want to group this with respect to the department so group by e-d-e-b-t and after the group by statement i need to give the order by statement so order by and i want to order the result with respect to the average age so again this would be average age and i want the sorting to be in descending order so d e s c so this is a total command select average e agent e department so i'll be getting the average age and the department column from the employee table and i'm grouping it with respect to the department column and i'm sorting it in descending order of the age so this would be eh over here so let me hit execute so we have the average age with respect to each department ordered in descending order so operations department has the maximum average age of 36 then sales has an average age of 33 analytics department has an average age of 27 and support has the youngest staff of age 21 and this is how we can use a group by clause the having is used in conjunction with the group by statement to impose certain conditions on the groups which are to be displayed so the having clause was added to sql because the where keyword could not be used with aggregate functions for example over here we have four departments a b c and d and we also have the corresponding average salary of the employees belonging to each department now let's say we wanted to see only those departments where the average salary is greater than hundred thousand dollars so this is where having clause can be used to impose the condition and get only those departments where the average salary is greater than hundred thousand dollars and this is the syntax for the having clause here you need to keep in mind that the having clause must follow the group by clause and must also precede the order by clause if used so let's go to sql server and implement the having clause so we'll group this table with respect to the department column and then use the having clause to display only those departments where the average salary is greater than hundred thousand dollars so let me go ahead and write the syntax for that i will type in the select keyword and then list down all of the columns which i want from this employee table so i need the department column i would also need the average salary so i'll use the aggregate function avg and then pass in the column which is e-salary and i will name this column as average salary now i would want this from the employee table so i'll use the from keyword and then give the name of the table which is employee after this i'll give the group by keyword to group this table with respect to the department column so i will given the name of the column which is e d e p t now before i go ahead and use the having clause let me actually execute just these three lines of code right so what i've basically done is i have grouped this employee table with respect to the department column and we have the average salary with respect to each of these departments right so this is the average salary of analytics department average salary of operations department and average value of sales and support now i will use the having clause to display only those records where the average salary is greater than 100 000 dollars so the average salary needs to be greater than hundred thousand dollars now i'll select all of this i'll hit execute right so we see that out of the four departments only operations and sales department have the average salary of the employees to be greater than hundred thousand dollars so the average salary of employees in operations department is hundred and three thousand dollars and the average salary in the sales department is hundred and fifty nine thousand dollars this is how we can work with having clause in sql so the update statement is used to modify or change the existing records in a table and this is the syntax for the update statement so we'll start with the update keyword and then given the name of the table then we'll use the set keyword and assign new values to the columns which you want to update and finally we'll use the where clause given the condition which should determine where the columns are to be updated so by putting the where clause over here in square brackets because it is optional now let's update some records of our employee table so this is our employee table and i want to set this age value to 42 where the name equals sam so this would be the command for that so i will given the update keyword and then given the name of the table which would be employee and then use the set keyword and then set the age value to be equal to 42 then use the where clause and then set the condition so the condition is the name should be equal to sam so where the name is equal to sam i am updating the value of age from 45 to 42. let me put a semicolon let me hit execute so see that one row has been affected so now let me have a glance at the change table select start from employee right so we see that initially the age value was 45 where the name was sam so the age value has been changed from 45 to 42. now similarly i don't want to change the department to take wherever the gender value is female that is i want to change the department of all of the female employees from analytics to tech so this is how i can do it okay update keyword and then i'll given the name of the table and then the set keyword so over here i want to set the department to be equal to tech where the gender is equal to female so let me put in female over here right so let me hit execute now and let's see what happens so we see that two rows have been affected now again let me have a glance at the modified table select star from name of the table is employee right so we see that initially the department of both of the female employees was analytics so that department has been changed from analytics to tech now i want all of the employees to have the same salary so i will update this entire column to have the same value which is fifty thousand dollars so this command would be quite simple update and then the name of the table which is employee and then i'd have to set the salary would be equal to 50 000 that's it let me it execute so you see that six rows have been affected again let me have a glance at the modified table select start from the name of the table is employee execute again right so we see that the salary has been right so we see that the salary has been updated so every employee salary is same it is 50 000 per annum and this is how we can work with the update state so the delete query is basically used to delete or remove one of our existing records in the table so let's have a look at the syntax we'll start with a delete keyword and then put in the keyword from then we specify the name of a table after that we'll specify the where condition but that's optional now let's delete some records from our employee table so we have our employee table right here and i'd want to delete that record where the age of the employee is equal to 33 so this would be the command for that i will given the delete keyword and then i'll type from and given the name of the table which would be employee after that i'll use the where clause and give the condition so the condition would be e h is equal to 33 i'll put in the semicolon over here and i'll hit execute so you see that one row has been affected now let me have a glance at the modified table so i will type in select star from employee execute again right so you see that that record with age was equal to 33 has been removed so initially we had six records now we only have five records in our table now similarly i don't want to delete that record where the employee name is equal to sample so let me write the commands that so i will given the delete keyword first and then i'll give in the from keyword after that i'll give the name of the table employee then use the where clause and then give the condition so this time the condition would be e name is equal to sample right execute again we have one row which has been affected select star from employee and let's see the modified table now so see that another record has been deleted and that record where the name was equal to sam is the record which has been deleted then we have the truncate statement so the truncate statement deletes all of the data inside the table so here we'll given the keywords truncate table and then give the name of the table so let me go ahead and remove all of the records from this employee table using the truncate command so i'll type truncate table and then i'll given the name of the table which is employee execute so see that commands completed successfully now i will type select start from employee now let's see what do we get so we see that all of the records are empty this is because the truncate table command basically removes all of the data from the table and the specialty of the truncate command is even though it removes all of the records from the table the structure of the table remains intact so you see that the structure of this table over your employee table it is still intact so this is how we can work with the delete and truncate statements how to implement the inner join we'll be implementing the inner join on these two tables first is the employee table which comprises of employee id employee name employee salary age and gender and the department in which the employee works then we have the department table which comprises of department id department name and the location where the department is present so the inner join basically gives us those records that have matching values in both the tables so let's say we have two tables table a and table b and when we apply inner join on these two tables we'll get only those records which are common to both table a and table b so let's have a look at the syntax well give the select keyword first and then the list of the columns calling which will get the from keyword and give the name of the first table then we'll use the keywords inner join and give the name of the second table after which we'll use the on keyword so this on keyword tells sql that this inner join needs to be done on some condition and that condition will be values from column x of table 1 should be equal to the values of column y from the table too now this might sound complicated so let's just implement this inner join on the employee table and the department table so before i go ahead and implement the inner join let me have a glance at both of the tables so i'll start with the employee table select star from employee all right so this is our employee table so we see that we have this department column over here which tells us about all the departments in which the employees work in now similarly let me have a glance at the department table select star from i will given the name of the table which would be department right so we have three columns over here and i have this d name column over here which gives me all of the names of the departments so this is the common column between the employee table and the department table so we'll be applying the inner join condition on the d name column of the department table and the e d e p d column of the employee table so let me go ahead with the syntax of inner join i will given the select keyword and then given all of the columns which i do want to select so i want to select the e name column and edepd column from the employee table so first i would have to given the name of the table which is employee then i'll put dot over here and then given the name of the column so i want the name column from the employee table and similarly i'd also want the department column from the employee table first i will given the name of the table i'll put in dot over here and then given the name of the column which is e d e p d right now i'd also want the department name column and the department location column from the department table so first i will given the name of the table which is department i'll put in dot over here and then get the name of the column which is the name similarly i want to extract the department location column from the department table first i will given department over here which is the name of the table i'll put in dot and then i will given the name of the column which is d underscore location after this i will given the from keyword and then given the name of the first table which would be employee then i will given the keywords inner join and given the name of the second table which would be department right so i am selecting these four columns and the inner join would be on the employee table and the department table right now i will use the on keyword and give the condition so the condition would be the values of the e d e p d column of the employee table and the values of the d name column of compartment table should be equal so employee dot e d e b d should be equal to department dot d name right guys so this is the complete syntax to implement inner join on these two columns of the employee table and the department table and i am selecting the e name and e department columns from the employee table and d name and delocation columns to the department table let me hit execute right and this is what we get so we have these four columns over here employee name employee department department name and department location and since this is inner join we have only those records where the values from e d e p t have matched to those values of d name and this is how we can implement inner join so the left join basically returns all the records from the left table and the match records from the right table so if we take table a and table b then applying left join on these two tables would give us all the records from table a and only the match to records from the table b so let's have a look at the syntax we'll start with the select keyword and then get the list of the columns following which we'll give the from keyword and give the name of the first table then we'll use the keywords left join and then give the name of the second table after which we'll use the keyword on so this on keyword tells sql that this left join needs to be done on some condition and that condition would be values from column x of table 1 should be equal to values from column y of table 2. so let's implement this left join on the employee table and the department table so before i implement the left join let me actually have a glance at both of the tables so i'll start with the employee table select star from employee and these are the columns present in the employee table employee id employee name employee salary employee age employee gender and employee department so i'll be taking this employee department column from the employee table now similarly let me have a glance at the department table select star from the name of the table is department so we have the department id department name in the department location and from this department table we'll be selecting this d name column so we'll be applying the left join on the e d e p t column of the employee table and the d name column from the department table so let me start with the syntax to implement the left join so i will give in the select keyword first and then i will give all of the columns which i want to select so i want to select the employee name column from the employee table so first i will given the name of the table which is employee then i'll put dot over here and then i will type employee name now i also want the employee department column so first i will given the name of the table which is employee i'll put dot over here and then i will give an e d e p d over here then i'd want the department name column and the department location column from the department table so i will given the name of the table first which is department i'll put a dot over here and i will select the department name column from the department table similarly again i will type in the table name which is department and i'll put a dot over here and then i'll also select the department location column so now that i've selected all of the columns let me given the keyword from and then give the name of the first table which is employee then i'll give the keywords left join and then give the name of the second table which is department now i'll give the on keyword and then give the condition and the condition would be the values from the employee department column from the employee table should be equal to the values of the department name column from the department table so employee dot e d e p t these values should be equal to department dot t name i'll press semicolon over here now let me hit execute right so this is the result when we apply the left join operation on the employee table and the department table so we've got all of the records from the left table which is the employee table and we see that we get only the match records from the department table which is the right table over here and wherever the records haven't matched we get null values so you see that the operations department is not present in the department table so that is why we have null values over here and this is how we can implement the left join just a quick info guys intellipaat provides sql online training in partnership with microsoft and mentored by industry experts the course link is given in the description below now let's continue with the session so the right join basically returns all the records from the right table and only the match records from the left table so if we take table a and table b then applying right join on these two tables would give us all the records from table b and only the match records from table a so let's have a look at the syntax we'll start with the select keyword first and then given the list of the columns for language we'll give the from keyword and then give the name of the first table then we'll use the keywords write join and then give the name of the second table after which we'll use the keyword on so this on keyword tells sql that this right join needs to be done on some condition and that condition would be values from column x of table 1 should be equal to values from column by of table 2. so before i go ahead and implement the right join let me actually have a glance at the employee table and the department table so i'll start with the employee table select star from employee so this is our employee table which comprises of these columns employee id employee name employee salary employees employee gender and employee department and will be implementing the right join with respect to this column employee department now let me also have a glance at the department table select star from the name of the table as department i'll hit execute so the department table comprises these three columns department id department name and department location and we'll be employing the right join with respect to this d name column so now let me go ahead and write the syntax for the right join so i will put in the select keyword first and then i'll given all of the columns which i want to select so from the employee table i want to select the employee name and the employee department columns so for that i'd have to given the name of the table first then i'll put dot and then i will given the name of the column which i want to select so employee name similarly i also want the employee department column so first i will given the name of the table which is employee dot and then i'll select the e d p t column and then i want the department name and department location columns in the department table so i'll have to given the name of the table which is department i'll put in dot over here and then given the name of the column which is d name similarly i'd also want the department location so i'll put dot and then select department location now i will given the from keyword and then given the name of the first table which would be employee after that i will type the keywords write join and then give the name of the second table which is department then i'll use the on keyword and then give the condition so the condition would be values of edept column from employee should be equal to values of the name column from the department table so employee dot e d e p d should be equal to department dot d name i'll put semicolon over here let me hit execute and this is the result which we get when we apply right join on the employee table and the department table and since this is the right join we see that we have all the records from the department table and we have only the match records from the employee table so you see that there is no content deck and finance departments in the employee table so that is why we have null values over there and this is how we can implement the right join so the full join basically returns all the records from the left table and also all the records from the right table so if we take table a and table b then applying full join on these two tables would give us all the records from table a and also all the records from table b and wherever the join condition is not met we'll get null values in that place so let's have a look at the syntax we'll start with the select keyword first and then given the list of the columns following which we'll give the from keyword and then give the name of the first table then we'll use the keywords full join and then give the name of the second table after which we'll use the keyword on so this on keyword tells sql that this full join needs to be done on some condition and that condition would be values from column x of table 1 should be equal to values of column y from table two so before i go ahead and implement the full join let me actually have a glance at the employee table and the department table so i'll start with the employee table first so select start from the name of the table is employee so we have these columns in the employee table employee id employee name employee salary employee age employee gender and employee department and we'll be applying the full join on the employee department column now let me also have a glance at the department table select star from the name of the table as department i'll hit execute so the department table has these three columns department id department name and department location and we'll be applying the full join condition on this department name column so let me start with the syntax for the full join so i will given the select keyword first and then i'll give in the list of all of the columns which i want to select so i want to select the employee name and the employee department columns from the employee table so first i'll given the name of the table which is employee and then i'll given the name of the column which would be employee name similarly i'd also want the employee department column so i'll given the name of the table employee i'll put dot over here and then i'll select the e d e p d column after that i'd also want the department name and department location columns in the department table so i'll given the name of the table department i'll put dot over here and then i'll select department name similarly i'll also select the department location column so i will given the name of the table i'll put dot and then i'll select the department location column after this i will use the from keyword and then given the name of the first table which is employee then i will use the keywords will join and then given the name of the second table which is department after that i will give the keyword on and then specify the condition on the basis of which the full join is to be done so the condition is the values of ed pt column from the employee table should match with the values of the d name column from the department table so that would be employee dot e d e p d is equal to department dot t name i'll put semicolon over here let me hit execute so this is the result of the full join on the employee table and the department table so wherever we see null values that means that the records have not matched over here so operations is present in the employee table but it is not present in the department table so that is why we have null values in the columns extracted from the department table similarly we have content tech and finance departments only in the department table and that is why we have null values in the columns extracted from the employee table and this is how we can implement the full join so let's understand how can we use update with join here we have two tables the department table and the employee table now i don't update the age value in the employee table wherever the department location is new york so from the department table we can find out that content and analytics departments are based in new york so in the employee table we'll update those age values whether department is either analytics or content and after updating the table using the join statement we see that these two values have been modified so we have the department and employee tables right in front of us and we'll be implementing the same task so from this employee table we'll add 10 more to the age values wherever the department location is equal to new york and since we've already seen that content and analytics are based in new york so basically these two records where the ed pt value is equal to analytics will be adding 10 more to these two h values so let me write the syntax for that i'll type update and then give the name of the table which i want to update so that would be employee after that i'll use the set keyword and then update the age value so e h and i'm adding 10 more to the e h value after that i'll use the from keyword and give the name of the first table which would be employee then i'll type join and then give the name of the second table which would be department then i'll type on and then state the join condition so the join condition is the values of the e d p d column from the employee table should be equal to the values of the name column from the department table so employee dot e d e p d should be equal to department dot d name and finally we'll specify the where condition which would be the location should be equal to new york so i'll select all of this i'll click on execute so you see that two rows have been affected now let me have a glance at the modified employee table select start from i'll type the name of the table which would be employee now i'll execute this so let me see the original age values so over here the department is analytics and we see that the initial age of anne and julia is 25 and 30 and after applying the update command we see that the age has been modified to 35 and 40. so anne's initial age was 25 after updating it became 35. similarly julia's initial age was 30 after updating it became 40. so this is how we can use update with a join condition now let's understand how can we use delete with join so this time from the employee table i'll be deleting those records where the department location is equal to new york and since content and analytics are based in new york we'll go ahead and delete those two records from the employee table so in the final modified employee table we see that there are no records where the department is either content or analytics so again we'll be implementing the same task from this employee table i'll be deleting those records where the department location is in new york so let me write the syntax for that so i'll type delete and then give the name of the table from which i'd want to delete the records so that would be employee after that i will give the from keyword and then give the name of the first table which would be employee again then i'll type join and then give the name of the second table which would be department now i will give on and give the join condition which is same again so the values of e d e p t column from the employee table should be equal to the values of d name column from the department table so employee dot e d p d should be equal to department dot d name and finally i'll use the wire condition which would be d location should be equal to new york so i'll select all of this i'll click on execute so again we see that two rows have been affected now let me have a glance at the modified table select start from employee so see that there are no records for the department is analytics so if you have a look at the original table we see that there are two records where the employees were present in the analytics department and after deleting those two records we just have four records present in the employee table and this is how we can work with update and delete statements using join so the union operator is used to combine the results of two or more select statements for example the first select statement returns these four fish and the second select statement returns these three fish then the union operator would return the result set of these two select statements and if there is a record which is present in both the tables we'll get only one of them in the final result that is there would be no duplicates in the final result so here we see that this blue fish is present in both the tables a and b but in the final result of a union b we'll get only one of the value and this is the syntax for the union operator we'll give the first select statement after that we'll give the union operator following which will give the second select statement now you also need to keep in mind that the number and order of columns must be same in both the select queries and we'll be implementing the union operator on these two tables so we have the student details one table which has the columns s id s name and s marks and has these four records the other table is student details too which has the same columns and comprise of these three records so let's go to sql server and work with the union operator right so we have our two tables right in front of us so let me start with the syntax i'll get the first select query select start from and then i'll give the name of the table which is student details one after that i'll give the union operator and then give the next select statement select star from and then the name of the table is student details 2. i'll select all of this i'll hit execute so the union operator has given us the result set of both of the select statements then we have the union all operator the union all operator gives all the rows from both the tables including the duplicates as well so here since the blue fish is present in both the tables a and b the final result of a union all b will also have the duplicate value so now let me just add the all keyword over here so i am using the union all operator between these two tables now i'll hit execute so we see that we also have the duplicate values so here these records were anne and julia are present these are from the first table and again here ann and julia so these records are from the second table so we have also got the duplicate records this time because we've used the union all operator now again let me remove this all keyboard let me hit execute so you see that there are no duplicates this time so this is a difference between union and union all operators so this is how we can work with the union and union all operators so the accept operator combines two select statements and returns unique records from the left query which are not part of the write query for example if the first select statement returns these four fish and the second select statement returns these three fish then the except operator would return us these two fish which were part of the first select statement but not the second select statement and this is the syntax of the accept operator we'll give the first select statement after that we'll give the except operator following which we'll give the second select statement now you also need to keep in mind that the number and order of columns must be same in both the select queries and we'll be implementing the except operator on these two tables so we have the student details one table which has the columns s id as name and s marks and has these four records and the other table is student details two which has the same columns and comprises of these three records so let's go to sql server and work with the accept operator right so we have our two tables right in front of us so let me type in the syntax i will give the first select query which is select start from and then i'll given the name of the table which is student details one after that i will use the accept operator and then given the second select statement select star from the name of the table as student details 2. i'll select all of this again i'll hit execute so this is the result of the accept operator so these are the two records which were part of the first select statement but not the second select statement and this is how we can work with except operator so the intersect operator helps to combine two select statements and returns only those records which are common to both the select statements and this is the syntax for the intersect operator well give the first select statement after which we'll give the intersect operator for language we'll get the second select statement so this intersect operator basically retrieves the common records from both the left and right side of the intersect operator now you also need to keep in mind that the number and the order of the columns must be same in both the select queries and we'll be implementing the intersect operator on these two tables so we have the student details one table which has the columns s id s name and s marks and has four records the other table is student details two which has the same columns and comprises of three records so let's go to sql server and work with intersect operator so these are our two tables over here this is the student details one table and this is the student details 2 table now let me implement the intersect operator on these two tables so i will given the keyword select start from and then give the name of the table which would be student details 1 after that i will give the intersect operator enter sec and then give the second select statement select star from student details 2 right guys so this is pretty much the syntax so you'll get the first select statement after that we'll give the intersect operator and then we'll give the second select statement now let me select all of this let me hit execute so these are the two records which are common to both the student details one table and also the student details to table and this is how we can work with the intersect operator so views are virtual tables which are used to limit the information that you want to display and these are actually nothing but the result of an sql statement which have a name associated with them for example from this employee table let's say we wanted to perform multiple operations on the records where the gender is female so we can create a view for only the female employees from the entire employee table so let's have a look at the syntax to create a view we'll start off by using the keywords create view then give the name of the view after which we'll give the keyword as and then give the list of the columns which you want to select from the table so let's actually create a view from our employee table so why don't create a view which would comprise of only the female employees from the entire employee table so this would be the command for that create view then i'll give the name of the view which would be female employees after that i will give in the keyword as and i will select all of the columns from the employee table so select star from employee then using the where clause i will give in the condition so the condition would be e gender is equal to female so i'll put a semicolon over here i'll hit execute so you see that commands have been completed successfully so now let me have a glance of the female employees view select star from i'll give the name of the view which would be email employees i'll press semicolon hit execute right so we see that we have successfully created a view from the employee table and in this view we have only two records which comprise of the employees ann and julia and both of them are obviously female now let's see how can we draw up a view so the syntax for dropping a view is pretty simple all we have to do is give the keywords drop view and then give the name of the view so let me drop this female employees view i will type in drop view and then i will given the name of the view which is female employees i'll press semicolon execute so again commands have been completed successfully so now let me see if the view exists or not select star from female employees execute and this is the result which we get invalid object name female employees and we get this because the view female employees has been dropped so this is how we can work with views so the auditable statement is used to add delete and modify columns in an existing table now let's have a look at the syntax to add a column to an existing table so we'll start by giving the keywords alter table then we'll give the name of the table after that we'll give the add keyword and the name of the column which you want to add to our table and finally we'll have to assign a data type to this new column so to our existing employee table i'll add a new column date of birth using the alter table statement so let me start with the syntax alter table then i'll give the name of the table which would be employee after that i will given the add keyword and then given the name of the column so i'll name the new column to be e d o b which would stand for employee date of birth and then i'll finally give the data type of this new column so the data type would be date so i'll put a semicolon i'll hit execute so commands have been completed successfully now let me have a glance at the modified table select star from employee right right so we see that this new column employee date of birth has been added so you see that this new column employee date of birth has been added to our employee table now let's see how can we drop a column from a table again we'll start by giving the keywords all to table and then we'll give the table name after that we'll give the keywords drop column and specify the name of the column which is to be dropped so i want to drop this new column which i have just added to this employee table so let me write the command for that [Music] so i want to drop this new column edob which i've just added to this employee table so let me write the command for that [Music] alter alter table and then the name of the table is employee although table and then the name of the table is employee after that i will give in the keywords drop column and i want to drop the e d o column from this employee database i'll put a semicolon hit execute so again the commands have been successfully executed now let me have a glance at the modified table select star from employee i'll put a semicolon execute so again the new column which you had just added has been dropped so this is how we can work with the alt table statement so the merge statement helps us to perform insert update and delete in just one statement this means we no longer require multiple statements to perform insert update and delete and for the merge statement we require two tables a source table and a target table so the source table basically contains all the changes which are to be applied to the target table and the target table a start table upon which we'll be implementing all the changes now the merge statement basically joins the target table to the source table using a common column in both the tables and based on how the rows match up we can perform insert delete and update operations on the target table now let's have a look at the syntax of the merge statement so we'll start with the merge keyword and then give the name of the target table and assign it an alias using the as keyword then we'll type using and get the name of the source table and also assign it on alias using the as keyword again after that we'll type on and then set the join condition then we'll type when matched so if the rows match up based on the join condition then we'll go ahead and update the target table after that we check when not match by target so this basically means that if there are some rows which are present in the source table but not in the target table then we'll go ahead and insert those rows from the source table into the target table and finally we check we're not matched by source so here we are checking if there are some rows which are present in the target table but not in the source table then we'll go ahead and delete those rows from the target table and for the practical purpose we'll be using these two tables so this is our source table with the name employee source and this is our target table with the name employee target so using the merge statement we'll be updating the e salary and eh columns in the target table wherever there's a match also insert the records with employee id 7 and employee id 8 into the target table from the source table and delete the records with employee id 4 and employee id 5 from the target table because they're not present in the source table so let's go to sql server and work with a merge statement right so we have our two tables right in front of us this is the employee source table and this is the employee target table and we also have the merge syntax to apply on these two tables so we'll type merge and then get the name of the target table employee target will assign it an alias similarly we'll give the employee source table and then assign it an alias s after that we'll type on and then set the join condition so join condition is t dot e id is equal to s dot eid so we basically set the join condition on the employee id columns of the source table and the target table then we have when matched so if there is a match between the rows of these two tables then we'll go ahead and update the target table so here in the update statement we have t dot e salary equals s dot e salary and t dot e h is equal to s dot e h so if the records match then i'll be setting the values of the salary column of the target table to be equal to the values of the salary column of the source table similarly i'll also set the values of the age column of the target table to be equal to the values of the age column of the source table and then we have when not matched by target so here we are checking if there are some rows which are present in the source table but not in the target table and if that is the case we'll go ahead and insert all of those into these columns of the target table and the values would be coming from the source table so this employee id column from the target table would be getting the values from the employee id column of the source table similarly this employee name column of the target table would be getting its values from the employee name column of this source table and finally we'll check when not match by source so if there are some rows which are present in the target table but not present in the source table then we'll just delete all of those rows so i'll select all of this and i'll click on execute so you see that eight rows have been affected now let me type the select statement and have a glance at the modified employee target table select start from employee target i'll select this i'll click on execute right so this is our modified employee target table so we also have our original employee target table over here so let me compare these two tables so we see that initially sam's salary was 95 000 and his age was 45 and after applying the merge statement his salary changed to 93 000 dollars and his age changed from 45 to 40. similarly anne's initial salary was 125 000 and after applying the murd statement her salary changed to 130 000 and then with the merge statement we have also inserted the records with employee id 7 and employee id 8 and deleted these two records from the employee target table so this is how we can work with the merge statement so these are the two types of user defined functions in sql we have scalar valued functions and table valued functions we'll start with the scalar valued functions so as the name states a scalar valued function always returns a scalar value so a scalar value could be in wirecard date and so on and this is the syntax for a scalar valued function so we'll type create function and then we'll give the function name and inside the parenthesis we'll specify the names of the parameters and the data types and since the function is going to return a value we'll type the returns keyword and specify the type of data which will be returned after that we'll type as begin and then give the function body and then return the final scalar value finally we'll use the end keyword to end the function so let's go to sql server and create our first scalar valued function so i'm going to create a scalar valued function which will take one integer value add five more to the original value and return the updated value so let me type the syntax for that create function and then i'll give the function name which would be add phi and inside the parenthesis i'll pass the first parameter so i'll name the parameter to be num and this would be of integer type after that i'll type the returns keyword and then the type of data which will be returning is also of integer type after that i'll type as begin and since all we have to do is and since all we have to do is add 5 more to the original value so i'll not write anything inside this so i'll directly type the return keyword and inside this return keyword i will add five more to the parameter value so that would be the rate num plus five so what we are basically doing over here is the value which is sent as parameter inside this function will take it and add five more to that value and then return it and finally i'll type the n keyword to end this function so let me hit execute right so we have successfully created this add5 function now let me go ahead and call this function so to call the function i'll type select and then i'll have to type dbo dot and then i'll have to give the function name which is add file now let's say i'll pass in the number 10 i'll select this i'll hit execute right so we see that this function works properly so i've sent 10 and the result which you've got is 15. similarly let me send 100 as the parameter now i'll select i'll hit execute right so the value which is returned is 105. so this is how we can create a scalar valued function then we have table valued functions so the stable valued functions return a table instead of a scalar and this is the syntax for a table valued function so here the return type is stable so after the returns keyword we will type in table because we are supposed to return a table after that we'll give the keywords as return and then given the select statement now you need to keep in mind that there are no begin and end keywords for the table valued function so let's go to sql server and create a first table valued function now i'll be creating a function which would help me to individually extract the male employees and the female employees from the employee table so before i do that let me actually have a glance at the employee table first so i'll type select start from employee right so we have this employee table right in front of us now let me go ahead and create the command so again i'll type create function and then i'll give the name of the function which would be select gender now inside the parenthesis i will pass in the parameter so uh i'll name the parameter to be gender and this would be of varchar type so as var and the length would be 20 after that i'll give the returns keyword and since this is a table valued function the return type would be table so i'll type as return and then given parenthesis and inside the parenthesis i will give my select statement so the select statement would go something like this select star from employee where e gender is equal to at the rate gender so this e-gender is basically the e-gender column from the employee table and this at the rate gender is the parameter which will be passing inside this function so let's say the user passes the value mail inside this function then we'll be extracting all of the records where the gender value is male similarly if the user passes the value female inside this then we'll be extracting all the female employees from this table so let me select this let me hit execute right so we have also created this select gender function so now using this function let me extract all of the mail employees from the employee table so to do that the syntax would be a bit different select start from and then i'll type dbo dot and then give the name of the function which would be select gender and inside the parenthesis i will pass in the value and since i want all of the mail employees i'll type mail over here right so i'm typing select star from this because this will basically return a table and from the table which is returned i would want all of the records so let me select this i'll hit execute right so we have successfully extracted all of the mail employees from the employee table similarly if i'd have to extract all of the female employees the syntax would be similar so i'll type select star from and then i'll type dbo dot select gender and then i will pass in the value female i will select this i'll hit execute so again we have successfully extracted the female employees from the employee table so this is how we can create table valued functions in sql so temporary tables basically help us to store and process intermediate results now these temporary tables are created in the temp db and are automatically deleted when they're no longer used so the temporary tables could be very useful in cases wherever we need to store temporary data and this is the syntax to create a temporary table so first we'll give the keywords create table and before we give the name of the temporary table we'll proceed it with a hash so this hash symbol basically signifies the table which you are creating is temporary so let's work with some temporary tables now so let me create a temporary table with the name student which has two columns one is student id another as a student name so i'll type create table i'll put in the hash symbol and then give the name of the temporary table which would be student so now this would have just two columns first would be student id so s id and this is of integer type then we have the student name so i'll name the column as s name and this is of variable length character of maximum length 20. so i'll put a semicolon over here and i'll hit execute right so you see that the commands have been completed successfully now i've already stated that this temporary table is stored in the temp db so let me show you guys where the tempdb is actually so you'd have to click on the system databases and over here you will see the temp db and inside temp db you have all of the temporary tables so let me refresh this right so we see that we have this temporary table student inside our temp db database now similarly i can also use the select statement with this temporary table which i've created so i'll type select star from and before giving the name of the table i need to give the hash symbol and then give the table name which would be student right so let me select this and i'll click on execute right so this is our table and we don't see any values because we haven't inserted any into this table so similarly we can also use the insert command and insert some values into this temporary table so i'll type insert into after that i'll give the hash symbol and then get the name of the temporary table which would be student i'll follow it up with the values keyword and then give the values which are to be inserted inside this so first the student id which will be one then i'll give the name of the student so name of the student is sam so again i will select this and i'll click on execute so one row has been affected now i'll select this line again and i'll hit execute right so we have successfully inserted this record inside our temporary table student so this is how we can work with temporary tables in sql so the sql case statement helps us in multi-way decision making so let's take this example to understand k statement properly let's say there are ten buttons starting from one to ten so if the person selects one then he wants if selects two then he wants to order coffee similarly if he selects three then he wants milk and for any other case the person will order water so this is basically how the case statement works so the case statement goes through conditions and returns a value when the first condition is met and this is the syntax for the case statement so we'll type in keys following which will give the when keyword and give the first condition after that we'll type then and specify what will happen if this condition is satisfied and we'll do the same for the rest of the conditions after that we'll type else and state what will happen if none of the above conditions are satisfied finally we'll give the and keyword so now let's go to sql server and start working with the case statement just a quick info guys intellipaat provides sql online training in partnership with microsoft and mentored by industry experts the course link is given in the description below now let's continue with the session so i'll check some conditions using the case statement so first i'll check if 10 is greater than 20 and if this evaluates to true i'll just print 10 is greater than 20. after that i'll check if 10 is less than 20. and if this condition evaluates to true i'll print 10 is less than 20 and if none of these two conditions evaluate to true i'll just print 10 is equal to 20. so let me write the syntax for that select i'll give the key statement then type 1 and get the first condition which would be 10 is greater than 20 i'll type 10 and state what will happen if this condition is evaluated to true so i'll just print then this greater than 20 then i'll type 1 and then stay the second condition which would be 10 is less than 20 i'll type then and if this condition evaluates to true i have to print 10 is less than 20 and if none of these conditions evaluate to true i'll type else and just state 10 s equal to 20 and i'll finish off the case statement with the and the keyword so let me hit execute so you get the result 10 is less than 20. so we get this result because the first condition has been evaluated to false because obviously 10 is not greater than 20 and since 10 is less than 20 this has been evaluated to true and we print 10 is less than 20. so now let's also implement this k statement on top of our employee table so before we do that let us actually have a glance at the employee table so i'll type select star from employee right so this is our employee table now using the key statement i would want to add a new column grade which would be dependent on the salary column so if the salary is less than ninety thousand dollars the grade would be c if the salary is between ninety thousand dollars and hundred and twenty thousand dollars the grade would be b and if the salary is greater than hundred and twenty thousand dollars the grade would be eight so let me write the syntax for that so i'll type select and i want all of the columns from the employee table i'll type start and i'll add a new column grade and i'll be getting the values in this column with the help of the case statement so i'll type case and then type the when keyword and give the first condition so the first condition would be e salary is less than 90 000 and if this evaluates to true the grade would be c i'll type one and say the second condition so the second condition would be if e salary is less than 120 000 dollars so here if the first condition is evaluated to false then it would mean that the salary is greater than ninety thousand dollars and after that we'll check if the salary is between ninety thousand dollars and hundred and twenty thousand dollars and if that evaluates to true the grade would be p and if none of these conditions evaluate to true that would mean the salary is greater than hundred and twenty thousand dollars so i'll type else and then the grade which i want is eight and i'll finish off this case statement with the end keyword and i want all of this from the employee table so i'll type from and then give the name of the table which would be employee i'll type go i'll select all of this and i'll hit execute right so we see that this new column grade has been added to the employee table and this grade column is dependent on the salary column so here we see that the salary is between ninety thousand dollars and hundred and twenty thousand dollars that is why the grade is b here the salary is less than ninety thousand dollars the grade is c and over here the salary is greater than hundred and twenty thousand dollars that is why the grade is eight so this is how we can work with a case statement in sql server so the f function is an alternative to the case expression this f function peaks in a boolean expression as the first parameter and returns the true value if the boolean expression is evaluated to true similarly returns the false value if the boolean expression is evaluated to false for example let's say in the boolean expression we are checking if the marks scored by a student are greater than 50 and if it evaluates to true we'll return bars and if the boolean expression evaluates to false will return fail so this is how the if function works now let's implement this if function in sql server so using the if function i want to check if 10 is greater than 20 and if it evaluates to true i'll print 10 is greater than 20 on the other hand if it evaluates to false i'll print 10 is less than 20. so let me write the syntax for that i'll type select after that i'll use the if function and pass in the first parameter which is the boolean expression so 10 is greater than 20 and if it evaluates to true i would want to print 10 is greater than 20. on the other hand if it evaluates to false i would want to print is less than 20 i'll hit execute right so what we get is 10 is less than 20 so we get this result because this boolean expression has been evaluated to false and it is redirected over here and we get the false result which is 10 is less than 20. now i'll go ahead and also implement this f function on top of the employee table so before i do that let me actually have a glance at the employee table first so i will type select star from employee so we have our employee table right in front of us now using the f function i would want to add a new column employee generation and that new column would be dependent on this age column so if the age of the employee is greater than 30 then the new column would have a value old employee similarly if the age of the employee is less than 30 then the value in the new column would be young employee so let me write the syntax for this i will type select and list down all of the columns which i want from the employee table so i want the employee id column employee name column employ each column now after this i will use the if function to add the new column so i'll give the first parameter which would be the boolean expression so here i will check if e of age is greater than 30 and if the age is greater than 30 i would want the value to be old employee on the other hand if the age is less than 30 i would want the value to be young employee now i will name this new column as employee generation so let me just type the name of the column employee generation over here and i would want all of this from the table employee now i'll select this i'll hit execute right so we see that a new column has been added to this and the name of this column is employee generation so here if we have a glance at this age value it is 45 and the corresponding value in employee generation is old employee similarly here it is 21 and since the value is less than 30 we get young employee and these two it is 25 and 30 and since these two values are also not greater than 30 we have young employee over here and matt whose age is 33 we have the value old employee and we have jeff whose age is 27 he's a young employee too so this is how we can work with a function in sql so a stored procedure is basically a prepared sql code which we can save so that the code can be used again so if you have an sql query that we write over and over again we can save it as a stored procedure and then just call it whenever we want now let's have a look at the syntax of the stored procedure so we'll give the keywords create procedure and then give the procedure name after that we'll give the keyword as and then give the sql statement for which we need to create the procedure and we'll end the procedure with the go keyword now after we create a procedure we need to execute it so this is the syntax to execute the procedure we'll type ex ec and then give the name of the procedure which is to be executed so let's go to sql server and work with some procedures so now i want to create a procedure which would help me to easily access this eh column from the employee table so i'll type create procedure and then i'll give the name of the procedure which would be employee age i'll type as and then give the sql statement which would help me to access this eh column that would be select e h from the name of the table is employee and then i'll give the go keyword now i'll hit execute right so we see that we have successfully created the employee procedure now i will use the exec keyword and then call this procedure so the name of the procedure is employee age i'll select this line i'll hit execute right so with this employee age procedure we can easily access the eh column from the employee table now similarly i'll create another procedure which would help me to access all of the records from the employee table create procedure and i'll name this procedure as employee details i'll follow it up with the ask keyword and then give the sql statement which would help me to access all of the records from the employee table select star from employee and again i'll finish this procedure off with the go keyword i'll select all of this i'll hit execute so again we have successfully created the employee details procedure now to call this procedure i'll use the exec command and then pass in the name of the procedure which is employee details so i'll select this i'll hit execute right so with the help of employee details procedure we can easily access all of the columns and all of the records from this employee table now we can also pass parameters to the procedure so the syntax for this is just slightly different here after giving the procedure name we follow it up with the list of parameters and their corresponding data types so let's create a procedure with a parameter now so now i'll create a procedure which would help me to access the male employees and the female employees separately from this employee table create procedure and i'll name this procedure to be employee gender after this i will pass in the parameter so the name of the parameter would be gender and that is of variable length character with a maximum length of 20. i will give the as keyword after this and then pass in the statements which would help me to individually access the male employees and the female employees select star from the name of the table as employee i'll follow it up with the where clause and give the condition so this time the condition would be e gender is equal to at the rate gender now let's understand this properly so here e gender is basically this column and we are passing in the value of this parameter to this column so let's say when we call this procedure and the value as female then we'll get all of the female employees and similarly if the value is male then we'll get all of the male employees now let me finish off this procedure with the go keyword right so we have successfully created this employee gender procedure now let me go ahead and execute this procedure so i'll type e x e c and then give the name of the procedure which would be employee gender and now let's say i want all of the male employees so i will given the parameter name at the rate gender and i will given the value which would be equal to male so let me execute this now right so i've got all of the male employees from the employee table similarly if i wanted all of the female employees from the employee table this would be the command for that execute i will given the name of the procedure which would be employee gender i will give the name of the parameter which would be at the rate gender and then pass in the value so this time i would want all of the female employees so the value would be female i'll select this i'll hit execute right so this time we've got all of the female employees from the employee table so this is how we can work with stored procedures either with parameters or without parameters in sql so an error condition during a program execution is called as an exception and the mechanism for resolving such an exception is known as exception handling and we can do exception handling sql with the help of try catch blocks so the try statement allows us to test a block of statements while they're being executed and the catch block allows us to handle any exception which occurs in the try block now let's have a look at the syntax of the try catch block so we'll get the keywords begin try and then give the statements which are to be tested after that we'll end the try block and begin the catch block and inside the catch block we'll either bring the error or roll back the transaction and we'll end the catch block as well so let's go to sql server and do some exception handling so i'll be declaring two variables and divide one of the variables with zero so this needs to give us divided by zero exception so let's go ahead and handle this exception now so we can declare a variable in sql by using the declare statement so i'll type declare and then use the add rate symbol to declare a value and i'll name this variable as while 1 and this is of integer type similarly let me go ahead and declare the second variable i'll type declare at the rate and the name of this variable is value and the data type is integer again now i'll begin to try block i'll type begin try and then inside this i will set the values for both value 1 and value 2. so i'll use the set keyword and then the value which i'll be assigning to val 1 that's 8 i'll put semicolon similarly let me assign something to val 2 as well so set at the rate while do now inside while do what i'll basically do is divide val 1 with zero so now this statement should basically give us the error so when we divide any integer value with the zero we'll get an indefinite value so this is the statement which will be giving the error now i will end the try block and try right now after this we'll begin the catch block begin catch and inside the cache block i will just print out the error which occur in the try block and i will display the default error message and i will end the catch block as well so let me hit execute now so the default error message which you get is divided by zero error encountered so what we basically did with the set of statements was we encountered an error within the try block and we handle that error inside the catch block so inside the catch block we sent a default error message and the default error message was divided by zero error encountered now we can also create user defined messages so let's go ahead and create some user defined messages and for that purpose i'll be doing exception handling on top of the employee table so let me actually have a glance at the employee table first select start from the name of the table is employee right so we have this employee table right in front of us so now what i'll do is i'll try to add the salary column and the name column now the problem with this is we cannot add a numerical column with a string value so let's handle this exception so i'll type begin try and then i will give the select keyword and i will try to add e salary column with the e name column and i want this from the employee table i will and try block after this i need to begin the catch block so begin catch now inside this i will print a user defined message so print and i would want to print cannot add a numerical value with the string value i will end the cache block as well after this i will give the go statement let me hit execute so this is the message which we get cannot add a numerical value with a string value and we got this user defined message because we had encountered an error inside the try block so this is how we can do exception handling in sql so a transaction is basically a group of commands which change data stored in a database and the transaction is treated as one single unit so this transaction basically ensures that either all of these commands are executed successfully or none of them must so let's say if one of the commands in the transaction fails then all the commands will fail and any data which was modified in the database will be rolled back similarly if all the commands inside the transaction execute successfully only then the transaction will be committed so this is how transactions ensure integrity of data so now i'll begin a transaction and inside that transaction i'll try to update this age value from 45 to 30. so let me write the syntax to begin a transaction so i'll type begin and then type transaction and inside this i'll give all of those sql statements which either have to be committed or rolled back so over here i'll give the update statement i'll type update and then give the name of the table which would be employee set e h to be equal to 30 where e name is equal to sam so i'll select these two statements and i'll execute them so we see that one row has been affected so now let me have a glance at the modified table so i'll type select star from employee i will select this command over here i'll execute this right so you see that initially the age value was 45 and after we applied this update command the age has been changed from 45 to 30. now since this command is inside a transaction we have the option to either roll back this command or commit this command so now let me go ahead and actually roll back this transaction so to do that i'll type rollback transaction so now let me select this i'll click on execute so you see that the commands have been completed successfully now let me again have a glance at the employee table right so since we have rolled back the transaction we see that the age value has gone back to the original value which is 45. so initially using the update command we had changed the age value from 45 to 30 and after that we had rolled back the transaction now now what i'll do is i'll execute these two statements again and let me have a glance at the table right so again we see that the age has been changed to 30 so this time instead of ruling back the transaction i will commit the transaction so i'll type commit transaction so i'll select this i'll click execute now let me have a glance at the table so this time the transaction has been committed and the change is permanent so this is how we can either commit or roll back the commands using a transaction now let's look at another example so in this example we are using transactions inside the try catch block so over here we are beginning the try block and inside this we are beginning the transaction and inside the transaction we are trying to update the salary value to be equal to 50 where the gender value is equal to meal so wherever the gender is equal to meal will update that salary value to be equal to 50. similarly wherever the gender value is equal to female i will change that salary value to be 195 divided by zero which would give us an undefined value and after that we'll commit this transaction and print transaction committed now since we have put this transaction inside the try block if any error occurs that will be sent to the catch block and we will roll back the transaction so over here we see that we are dividing 195 with zero and since this gives us an undefined value we will get an error and that error will be handled in this catch block and we will roll back the transaction so let me execute this so you see that the transaction has been rolled back because we get an error inside the try block now what i'll do is i will remove this divided by 0 and run this command again so this time we see that the transaction has been committed because there was no error inside the try block so now let me have a glance at the modified employee table so i'll type select star from employee i'll select this i'll click on execute right so we see that we have changed the salary value to be 50 where gender is equal to male similarly i have changed the salary value to be equal to 195 where the gender is equal to female this is how we can work with transactions in sql so who is a database administrator well a database administrator is someone who directs or performs all activities related to maintaining a successful database environment so now that we know who is a dba let's look at some of the roles of database administrator so dba has to take care of the initial installation and configuration of a new oracle or sql server database the system administrator sets up hardware and deploys the operating system for the database server then the dba installs the database software and configures it for use as updates and patches are required the dba has to handle this ongoing maintenance a dba has to also take care of backup and recovery of databases so dbas have to create backup and recovery plans and procedures based on industry best practices that make sure the necessary steps are followed another role of dba is to maintain security of the database so a dba needs to know potential weaknesses of the database software and the company's overall system and work to minimize risks in case of a security breach or irregularity the dba can consult audit logs to see who has done what to the data the dba has to also take care of access control that is he has to verify which users can be granted access to the system and which user should be denied then a dba has to also monitor databases for performance issues so if some part of the system is slowing down the processing the dba may need to make configuration changes to the software or add additional hardware capacity so now that we know what are the rules of a dba let's look at the types of dba let's start with production dba so a production dba is responsible for maintaining databases within an organization the production dba often gets involved when all the design decisions have been made and he or she has to keep things up and running so the production dbas basically take over after applications have been created and they would have to keep the server running smoothly back it up and plan for future capacity needs then we have application dba so an application dba focuses on a specific business application he or she would be an expert in sql and would implement the application business logic using sql or any other query language they would be also responsible for performance tuning of the database application next we have development dba so the development dba focuses on tasks related to building an effective usable database environment to support the creation and maintenance of applications development dbas need to be skilled in the process of data modeling and normalization to ensure that databases are designed to promote data integrity then there's ua dba so a uat database actually stands for under application testing database and the uat dpa is completely responsible for the testing and development of uat database and finally we have data warehouse dba so a warehouse dba is responsible for analyzing and understanding the data provided it is his responsibility to study and track patterns for analytics and business intelligence hence a warehouse dba handles the most significant amount of data for a more critical function now let's understand what is ms sql server so microsoft sql server is a relational database management system or rdbms that supports a wide variety of transaction processing business intelligence and analytics applications so microsoft sql server is built on top of sql that is tied to transact sql or tsql which is an implementation of sql from microsoft that adds a set of proprietary programming extensions to the sander language now let's look at the evolution of sql server so the sql server database platform that we know today began with the sql server 7.2 release in 1998. so microsoft re-architected the core database engine code in sql server 7.0 to address the databases scalability issues transforming it from a departmental database into a true enterprise level database perhaps even more important microsoft added olap services which would eventually become sql server analysis services then the sql server 2000 release saw microsoft continuing to push the platform into enterprise this release boosted scalability through large memory and address windowing extension support the company also expanded sql servers data handling capabilities with the new xml data type and then came sql server 2005 which was a major milestone microsoft added clr integration plus this was the first release to include the popular sql server reporting services sub system out of the box microsoft also had to replace data transformation services with the new sql server integration services in this release and in sql server 2008 microsoft refined sql server's enterprise capabilities adding transparent data encryption as well as the file stream and geospatial data types sql server 2008 was also the first release with built-in row and page compression for tables and indexes then came sql server 2012 which expanded sql server's bi capabilities with the addition of power view which is a graphical data navigation and visualization tool in this release microsoft also added always on availability groups which extended sql servers high availability and disaster recovery options the next release was sql server 2014 which provided a new in-memory capability for tables that could fit entirely in memory it also provided a new hybrid disaster recovery and backup solutions with microsoft azure enabling customers to use existing skills with the on-premise version of sql server to take advantage of microsoft's global data centers then sql server 2016 released which added new built-in json support in sql server for json imports exports parsing and storing a new polybase query engine was also added to integrate sql server with external data in hadoop or azure blob storage and the latest release is sql server 2017 and with this release sql server is no longer just a windows based rdbms we can now develop applications with sql server on linux windows ubuntu or docker and deploy them on these platforms this release also has support for python so our data scientists can now more easily perform machine learning right inside of sql server now let's look at the different editions of sql server so first we have the enterprise edition so sql server enterprise edition includes both the core database engine and add-on services and with a range of tools for creating and managing a sql server cluster so it can manage databases as large as 524 beta bytes and it supports 640 logical processors then we have the standard edition the sql server standard edition includes the core database engine along with the standalone services so it differs from the enterprise edition in that it supports fewer active instances that is fewer number of nodes in a cluster and it also does not include some high availability functions such as hot add memory and parallel indexes then we have the web edition which is just a low tco option for web hosting and then we have the developer edition so this edition includes the same features as enterprise edition but it is limited by the license to be only used as a development and test system and not as a production server and finally we have the express edition so it is basically a scaled down free edition of sql server which includes the core database engine while there are no limitations on the number of databases or users supported it is limited to using one processor 1gb memory and gb database files now let's learn about instances so you can consider an instance to be one complete installation of sql server and each instance manages several system databases and one or more user databases so an instance is either the default unnamed instance or it is a named instance a default instance does not require a client to specify the name of the instance to make a connection but a named instance is identified by the network name of the computer plus the instance name that you specify during installation so the client must specify both the server name and the instance name when connecting a computer can have only one default instance and all the others must be named now let's go ahead and install sql server into our systems so we can install sql server from this site over here microsoft sql server sql server downloads so i'll click on this link so this is the website and for this course i'll be using the developer edition so all i have to do is click on download now and it'll be downloaded now let's look at the different system databases in sql server so when we install microsoft sql server the system databases are automatically created on every sql server instance these system databases allow the database engine and administrative applications to properly manage the system and these are the available system databases master model msdb and tempdb so let's start with master database the master database contains all of the system level information for sql server all of the logins linked servers endpoints and other system-wide configuration settings the master database is also where sql server stores information about the other databases on this instance and the location of their files so if the master database is not present sql server cannot start then we have the model database so the model database is used as a template whenever a new user database is created so modifications made to the model database such as database size collation recovery model and other database options are applied to any databases created afterward then we have msdb which is used by sql server agent for scheduling alerts and jobs so msdb also holds backup history so using the msdb tables it's possible to determine when each database and file group was last backed up then we have tempdb which is basically a workspace for holding temporary objects or intermediate result sets one interesting thing about 10db is that it is recreated every time the sql server services started and any objects that we've created in tempdb will be gone once the server restarts so one of the most important tasks of a database administrator is to backup and restore the databases to ensure that there is no data loss and for this purpose the sql server backup and restore component provides an essential safeguard for protecting critical data stored in our sql server databases so to minimize the risk of catastrophic data loss we need to back up our databases to preserve modifications to our date on a regular basis and this is where a well-planned backup and resto strategy helps in predicting a databases against data loss caused by a variety of failures now it's time to restore and back up the adventureworks database so i will right click on this databases folder over here and since i want to restore the adventure works database i will select this option restore database and i will set the source to be device and i will add a device so that would basically be the file which i have to add so i will go to the d drive so inside d drive i have this adventure works folder and this is my database over here adventure works 2014 i'll click on ok again i'll select this and i'll click on ok so i have successfully added this adventureworks 2014 database now i'll click on ok and i'll just wait for the database to be restored right so we get this message that database adventure works 2014 has been restored successfully now if i open this databases folder i will refresh this and we see that we have this adventureworks 2014 database over here now my next task would be to make a backup of this database so for that purpose i will right click on this and then select tasks and i have this option of backup i'll select this now i will remove all of the previous backup models which i had created so i will select this i'll click on remove now i will click add and add a new backup let me name that to be new backup i'll click on ok now again if i want the tsql code for this all i have to do is select the script and this would be the tsql code to get a full backup of this adventure works database so now i'll select ok i can let me wait till the backup is completed so get this message set the backup of database adventure works 2014 has been completed successfully right so first we went ahead and restored this adventureworks 2014 database and after that i made a full backup of this database now let's look at recovery models so recovery model is basically your database property that controls how transactions are logged whether the transaction log requires backing up and what kinds of restore operations are available and all of the backer pre-store and recovery operations are based on one of these three available recovery models which are simple full and bulk log recovery models so let's start with simple recovery model well as the name suggests it is the simplest among the available models so when we choose the simple recovery model sql server maintains only a minimum amount of information in the transaction log and while using the simple recovery model we may restore full or differential backups only so it's not possible to restore such a database to a given point in time and we may only restore it to the time when a full or differential backup occurred therefore it will automatically lose any data modifications made between the time of the most recent full or differential backup and the time of the failure so the simple recovery model requires very less administration and it is easier to manage in the full or bulk log models but there would be higher data loss if a data file is damaged so next we have full recovery model so with the full recovery model all the transactions are fully recorded in the transaction log file and this allows us to design a disaster recovery plan that includes a combination of full and differential database backups in conjunction with transaction log backups and in addition to preserving data modifications store in the transaction log the full recovery model also allows us to restore a database to a specific point in time and hence no work is lost due to a damaged file next we have bulk log model the bulk log recovery model is a special purpose model that works in a similar manner to the full recovery model the only difference is in the way it handles bulk data modification operations so the bulk log model records these operations in the transaction log using a technique known as minimal logging so this saves significantly on processing time but prevents us from using the point in time restore option so now let's change these recovery models in ssms so this would be the command to set the recovery model to be simple so set recovery simple with no wait go let me just execute it so see that commands have been completed successfully now if i want to change this recovery model from simple to full all i have to do is write full over here now i'll click on execute so this time we have changed the recovery model from simple default now again if i want to change this recovery model from full to bulk lock all i have to do is type in bulk underscore logged over here i click on execute right so this time the recovery model which we are using is bulk locked now let's see how can we plan our backup strategy so after we have selected a recovery model that meets our business requirements for a specific database you'd have to plan and implement a corresponding backup strategy so these are some of the questions which we'd have to keep in mind while planning a backup strategy how many hours a day do applications have to access the database and how frequently are changes and updates likely to occur and are these changes likely to occur in only a small part of the database or in a large part of the database and these are the different types of backup models which we can use so we have full backups differential backups file and file group backups partial backups copy only backups mirror backups and transaction log backups so a full backup as the name implies backsip everything it is the foundation of any kind of backup so this is a complete copy which stores all the objects of the database that has tables procedures functions views indexes so basically everything so the full backup will be able to easily restore a database in exactly the same form as it was at the time of the backup then we have differential backup so you can consider a differential database backup to be the superset of the last full backup and it contains all changes that have been made since the last full backup so if there are very few transactions that have happened recently a differential backup might be small in size but if you made a large number of transactions the differential backup could be very large in size then we have file and file group backup so whenever the database size and performance requirements make a full database backup impractical we can create a file backup instead so a file backup contains all the data in one or more files which are known as file groups then we have partial backup so partial backups allow us to backup only a subset of the data files so these partial backups will make a backup copy of the primary file group and all read write file groups and by default they will omit any file groups designated as read-only this means that partial backups are only relevant for databases that contain read-only file groups otherwise a partial backup will capture exactly the same data and objects as an equivalent full database backup so next we have copy only backup so copy only backup is a sql server backup that is independent of the sequence of conventional sql server backups usually taking a backup changes the database and effects how later backups are restored however occasionally it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database and this is exactly where a copy only backup can be used then we have mirror backup so mirror backups are actually identical copies of the same backup and these identical copies are stored on multiple locations so this increases the protection level by having additional copies of the backup set and in case one of the copies gets lost or is corrupted we can use the mirrored copy to perform a restore so finally we have the transaction log backup so our transaction log backup allows us to backup the active part of the transaction log so after we issue a full or differential backup the transaction log backup will have only those transactions that were created after those other backups had completed so now let's implement backup models in ssms so now again i will select this adventure works database right click on it i will select tasks and since i want to backup i'll click on backup so this time i want a differential backup so i'll select differential and all i have to do is click on ok and before that if i also want the tsql code for this all i have to do is click on script and this is the tsql code to get the differential backup of this database i will click on ok so see that the backup of the database has been completed successfully now similarly if i want to get a transaction log backup i'll right click this tasks backup and this time instead of choosing full i'll select transaction log and again if i want the tsql code i'll click on script so i'll just click on ok so this time we have a transaction log backup of the adventure works 2014 database so now let's understand what is point in time recovery so point in time recovery allows us to restore a database into a state it was in at any point of time this type of recovery is applicable only to databases that run under the full or bulk log recovery model so now let's implement point in time recovery in ssms so i have this adventure works 2014 database over here i'll right click this select tasks i'll click on restore database and i have this timeline option over here so i'll select timeline and i will set a specific date and time so i want to recover this database from two hours back so i'll set this to be 18 and i will click on ok right so this is how we can do point in time recovery so again if i want the tsql code for this all i have to do is click on script but for now i'll click on ok just a quick info guys intellipaat provides sql online training in partnership with microsoft and mentored by industry experts the course link is given in the description below now let's continue with the session so we've successfully got the point in time recovery of this database for two hours back now we'll see how to import and export data in ssms so i have this happy database over here and i want to import a flat file into this database so for that i'll right click this i have the option of tasks and over here i have to select import data i have to set the data source so the data source would be a flat file flat file source now let me browse that flat file so i have this new file over here i'll open this now all i have to do is keep clicking on next so this is the data which is present in this flat file click on next and then i'd have to set the destination so i want to store this into my database so the database would be sql server native client now this will be stored in this happy data fees i'll click on next next and i'll have to click on finish over here so we see that we have successfully imported this flat file into our database now let me refresh this let me have a look at the tables over here so we have imported this new file into this database now let me go to this database first which would be happy over here so select start from new file i'll execute right so we have successfully imported the data set now what i'll do is add a new row to this file and export this into a new file so insert into new file it will be values and then let's see the name is and and her age is three i'll execute this now let me look at the modified data so that'll be select star from new file so we have successfully added this new record now i would want to export this data so this time again i will right click this database and i will select tasks over here i have the option of exporting data now the source would be sql server native client now this time the database is again happy i'll click on next i'd have to choose a destination so the destination would be a flat file i'd have to choose a file name over here so i'm going to store this into new file too and i'll click on next next so over here i'd have to choose the table which i want to export so i want to export this new file i'll click on next now next again and i'll click on finish so this time we have successfully exported this data from ssms so dynamic management views and dynamic management functions are system views and system functions that return metadata of the system state on querying the related system objects database administrators can understand the internals of sql server so these dynamic management views allow us to monitor the performance of sql server instance and diagnose issues with it now let's look at some of the properties of dynamic management views so dynamic management views are composed of both views and table valued functions some of them apply to the entire server and are stored in the master database others are specific to each individual database and all of these dmvs start with the prefix dm underscored now let's look at some categories of dmvs so dmvs can be divided into many different categories and in the session we'll be looking at three of those so we'll be looking at database related dmvs sql server os related dmvs and execution related dmvs so let's start with database related dmvs first we have dm underscore db underscore file underscore space usage which returns the space usage information for each file in the database now we will use this dmv to get the total number of free pages and total free space in megabytes available in all files in tempdb so let me just run this command over here i will click on execute so the total number of free pages available in temp db are 3592 and the total free space is 28 mb next we have dm underscore db underscore log space usage which returns space usage information for the transaction log and we will use this dmv to get the total free logs piece in megabytes available in tempdb again i will run this command in ssms i'll click on execute so see that the total free log space is 5.74 mb so this is the total free log space in tempdb next we have db underscore partition stats which returns page and row count information for every partition in the current database and this time we will get the counts for all partitions of all indexes and heaps in the adventureworks 2014 database let me click on execute so these are all of the partition stats for all of the indexes and heaps in the adventure works 2014 database so here we have different statistics such as index id partition number the data page count and so on and over here we have the reserved page count the used page count the row count right so these are all of the partition stats which we can get from this dmv next we'll head on to sql server os related dmvs so first we have the dm underscore os buffer descriptors which returns information about all the data pages that are currently in the sql server buffer pool so with this command over here we'll get the cached page count for each database again i'll run this command in ssms i'll click on execute so these are the different databases which i have in sql server and this is the cached page count so in masterdb i see that there are 137 cash pages in msdb there are nine in spada there are six and in adventure works there are 304 next we have dm underscore os threats which returns a list of all sql server operating system threads that are running under the sql server process so this query over here is used to find workers along with time used for execution that are running threads which are actually not started by sql server so this is all of the information about the threats which are not yet started by sql server so you have different columns we have the os thread id the creation time over here the kernel time user mode time and over here at the back end of it we've got the address associated with each of these threads over here now it's time for the third category of dmvs which is execution related so first we have dm underscore exec connections which returns information about the connections established to this instance of sql server and the details of each connection and this command over here is used to gather information about a query's own connection so for this query we see that the session id is 51 and we have other columns as well so we have the host name over here this is the host name we have the program name over here we have the login name and we have the original login name the connection time and the login time so next we have dm underscore exec sessions which shows information about all active user connections and internal tasks and this command over here it finds the users that are connected to the server and returns the number of sessions for each such user so we see that there are two users currently one is anti-service igneous another is essay and for the first user the session count is one and for the second user the session count is 50. so those were some of the dynamic management views now we will work with activity monitor so sql server activity monitor is a feature in sql server management studio that displays information about the sql server processes and their effect on sql server performance so the activity monitor consists of these expandable panes so we have overview we have processes resource weights data file io recent expensive queries and active expensive queries now let's go ahead and start activity monitor in ssms so over here i would have to right click on this and then select activity monitor so we have opened up activity monitor so this overview pane over here it contains the graphs for the most important sql server instance information so this percent processor time it tells us the percentage of time the processor spent to execute threads that are not idle and then we have waiting tasks so this over here gives us the number of tasks that are waiting for processor io or memory to be released so that tasks can be processed then we have database i o so this gives us the data transfer rate in megabyte per second and it could be either from memory to disk disk to memory or disk to disk and finally we have batch requests per second which gives us the number of sql server batches received by the instance in a second now let's look at the processes pane so this process pane shows us the information about the currently running processes on the sql server databases and who are running those processes so we have uh information for session id we have user process we have login name and so on right so we see that these are the users over here and these are the databases which the users are currently working on you see that the user essay has worked on 10 db master db and this adventure works db after that we have the resource weights pane and this provides information with respect to a thread which is waiting for a key resource such as memory cpu or network so these are all of the threads which are waiting for something and then we have the next pane which is data file io so this pane shows information about the database files on the sql server instance so for each database all the database files are listed so we have all of the different databases over here tempdb master model and so on and these are different files which are part of this database and then following on we have the recent expensive queries pane so this pane shows all the expensive queries in the last 30 seconds which use up a lot of resources whether it is memory disk or network and finally we have the active expensive queries which shows all the currently active expensive queries running now we will work with the performance monitor now sometimes there might be issues which occur outside of sql server that may cause performance problems in sql server so this is where we can use performance monitor so performance monitor is a monitoring tool which is shipped with windows and it helps in tracking various performance counters and monitors overall system and application performance and these counter values are shown in real time graphs which can be saved in log files and used for analysis now let's work with performance monitor in ssms so to open performance monitor i'd have to click on tools and then select sql server profiler i will just give the connection name and the password over here i will connect now again inside the sql server profiler i have this tools tab so i'll select this and then select performance monitor over here i will select performance monitor and we see that there is a graph which tells me about performance of the system now if i want to add some counters i would have to click on this plus symbol over here and i can add all of the performance counters so let's say i want performance counters related to memory so let me go to the memory section over here and inside memory maybe i want to add available megabytes i will add this i will add committed bytes free and zero page list bytes so this is memory again in logical disk maybe i want the cache bytes i would also want the commit limit now similarly under physical disk i would want the disk read time and the idle time over here now all i have to do is click on ok now if i want to get a report of this we see this change graph type and i will click on report so this is the report which i have so we see that available megabytes this is the available megabytes cache bytes committed limit committed bytes and the free and zero page list bytes over here and we have the percent discrete time under physical disk percent idle time under physical disk and we also have the processor time over here now let's see how can we trace sql server activity using sql server profiler so whenever we are working with really long and complex sql queries we have to make sure that all of those tasks are performed accurately this is where tracing all these tasks becomes important so sql server provides a tool called as sql server profiler for exactly this purpose so profiler is a tool for tracing recreating and troubleshooting problems in mysql server and it lets developers and database administrators to create and handle traces and replay and analyze these trace results now let's go ahead and work with sql server profiler in ssms so we are back to ssms now i'd have to click on tools and then select sql server profiler again i will give in the password and then establish the connection let me click on connect now i'd have to give a new name to this trees so i will name this as new trace and then maybe i will also save this to a file so let me given the destination so i'll save this in my c drive i'll click on save over here now i can also set the trace time so let me schedule it so i want this sql server profiler to run for at least one hour and again over here event selection so this is where i can set all of those events which i want to be traced so i click on this show all events and let me add some events over here so i want to trace the cursor prepare cursor unprepare option and let me go to the objects tab over here and i want to know whenever an object is altered created or deleted similarly let me go to this performance tab and i want to know about auto stats and now all i have to do is click on run so we see that the trace has started and we get all of the information over here so we have the detailed information of whatever is happening so you have the event class text data application name entity username login name and so on and we also have the reads rights and the duration for the reads and writes over here so this is how we can work with sql server profiler so why do we need data integrity well data integrity is used to maintain accuracy and consistency of data in a table so whatever data we have with us we need to make sure that it is correct error free and useful and data integrity can be grouped into these categories so we have entity integrity referential integrity domain integrity and user defined data integrity so entity integrity ensures that each row in a table is a uniquely identifiable entity and we can apply entity integrity to the table by specifying a primary key unique key and not null then we have referential integrity which ensures the relationship between two tables is proper and we can apply this using a foreign key constraint and then we have domain integrity which ensures the data values in a database follow defined rules for values range and format and we can enforce these rules using check and default constraints and finally we have user user-defined data integrity which can be enforced through triggers so now that we know about data integrity let's learn about indexes in sql server so indexes are special lookup tables that the database search engine can use to speed up data retrieval simply put an index is a pointer to data in a table so these indexes in a database are very similar to indexes at the end of the books whose purpose is to find the topic quickly now again that you know what exactly is an index let's look at the types of indexes so in sql server indexes can be grouped into clustered and non-clustered so a clustered index defines the order in which data is physically stored in a table and since stable data can be sorted in only one way therefore there can be only one clustered index per table so when sql server the primary key constraint automatically creates a clustered index on that particular column but when it comes to non-clustered index it doesn't sort the physical data inside the table in fact a non-clustered index is stored at one place and table data is stored in another place and this is actually similar to a textbook where the book content is located in one place and the index is located in another and this allows for more than one non-clustered index for table and we'll be implementing indexes on this new table which comprises of these columns object name color name and id right so we have this new table over here now if i have to create an index on top of this we have this index folder inside this table so i would have to right click on this select on new index and if i want to create a clustered index i will select this option clustered index and then give a name to this clustered index so i will name this as clustered index 101 and i will go ahead and add this clustered index on top of the id column of this table i'll click on ok now again if i want the tsql code for it all i have to do is click on script so this is the tsql code to create a clustered index with the name clustered index 101 and it is built on top of the id column so now again i will go to this dialog box and click on ok so see that we have successfully created a clustered index with the name clustered index 101 which is on top of the id column now similarly if i want to create a non-clustered index i will right click on this folder select new index and this time i will choose non-clustered index and again i will give a name to this so this will be non-clustered index 101 i will go ahead and add a column on top of which this non-clustered index is supposed to be so i'll choose the color name column i'll click on ok again if i need the code for this i'll click on script so this is the code to create a non-clustered index with the name non-clustered index 101 and this is on top of the color name column so again i will finally click on ok and we have also successfully created the non-clustered index with the name non-clustered index 101 now let's talk about one of the problem which is associated with indexing and that is known as fragmentation so index fragmentation happens when the indexes are scattered all over the database well whenever sql server performs io operation it initially reads the data from the disk which is known as physical read so it pulls everything into the sql server buffer so there is a logical order in this buffer and whenever the query runs again instead of doing physical read sql server would look into the buffer and if it can't find the requisite data in the buffer that is when it will do physical read from the disk now there might be a case where the logical order of the indexes is not equal to the physical order of the indexes and this is called as index fragmentation now why does it happen well whenever we insert or update data the indexes become fragmented and as indexes become fragmented ordered data retrieval becomes less efficient and reduces database performance so let's actually take this example so initially we have two data pages for a table with a clustered index now a new row with the primary key phi needs to be inserted and since it is a clustered index new row is inserted in order and because the target page is full over here the new row does not fit and sql server splits the page roughly into two halves and insert the data into this new page now what happens is the logical order of the index does not match with the physical order and this is when we say that the index has become fragmented now to deal with fragmentation we have two solutions we can either rebuild the index or reorganize the index so rebuilding is the process of changing the whole b tree that is all the existing indexes are dropped and new indexes are recreated while reorganizing index is the process of cleaning organizing and defragmenting the leaf level of the b3 now we'll go ahead and implement this concepts in ssms so i will take this clustered index and i want to rebuild this clustered index so if i want to do that i will right click on this and i have this option to rebuild i'll click on this now i all i have to do is click on ok and again if i need the tsql code for this all i have to do is click on script and this is the tsql code to rebuild this clustered index 101 so i will go ahead click on ok and i have successfully rebuilt this clustered index now i want to reorganize this non-clustered index so this time i will right click on this select reorganize end again select this and click on ok so this time i have successfully reorganized this non-clustered index now let's see how can we get the fragmentation stats of indexes so we can use this dynamic management function cis dot dm underscore db underscore index underscore physical stats to get the fragmentation stats and i will be applying this on top of this person dot address table from the adventure works 2014 database so i'll just paste the code over here and i want these index stats for this table over here person dot address i'll click on execute and these are the fragmentation stats for stable crud operations crud operations is where we'll actually check out some quick sql queries where uh but first you need to know what crud stands for guys so every time you begin to learn sql or you're revising sql or you need to understand what sql is you will begin with cred operations as your first practical hands-on guys and that's the reason i have chosen this particular thing to show it to you guys c in card stands for create r stands for read u stands for update and d stands for delete guys so these are some of the operations we can perform on a database so we can perform a create operation we can create something on the database we can read stuff from the database we can update the database and eventually delete the database as well guys you can eventually you know not just the database you can create tables delete tables read tables update tables and so much more as well guys so on that note what can sql do we talk we're talking a lot about you know sql can do this sql can do that but then if you have to put everything under one header this is what i would say guys sql can execute queries in a database it can retrieve data it can insert the record into a database update the records delete basically the thread operations it can create new databases create new tables and then you have something called a stored procedure which is basically like a ready set of commands all you have to do is you know pretty much just run that stored procedure and it will run lots and lots of commands for you at once and you know let's say let's say you're a huge organization you do not want to type queries every single time and there are thousands and thousands of lines of queries so stored procedure is basically a saved file where you just run it and it runs all of these thousands of lines of queries for you to pick whatever data you're looking for in a database case so it's basically like a shortcut and then sql can create views as well views are these temporary entities where you know let's say our data our table contains thousand rows where we don't want to see thousand rows or thousand columns in fact so we just want to see three columns and three entries so in that case we create something called as a view where we only pick these stuff that we want to see and then we show it on the screen it's as simple as that guys and then coming to permissions as we already told you it offers very good security measures and we can set any permission for any person accessing the you know any client accessing the database on tables procedures views and so much more guys so this quickly brings us to our crud operations demo well let me quickly jump into uh mysql workbench so mysql workbench again this comes completely with the sql server installed uh it gives you a workbench it gives you a platform to basically write your queries guys so i have pretty much written down a couple of sample queries for you guys to just take a look at and work with and let me work with you uh on every single line of query that we've pretty much written so the first query says create database demo this line is fairly simple because you can pretty much it's a very high level language right so we're creating a database entity and we're calling our database as demo this particular name is extremely important because you're going to be using it a lot guys so the first step is to create a database and let me quickly run this query and as you can see our database has been created now so uh our we just created one database and we left it there so think of it as a box where you just close something in the box and put it next to you so unless you actually open that box and you know start using stuff it won't make sense right so then we actually use the query which is use demo demo is again it's basically used followed by the name of the database so use demo will pretty much uh open that database box for us where we can work with it on the inside guys and the next query is something very simple where you know our database is again a plain entity right so we need to create some tables there so in this particular demo i'm going to create one table for you guys using this very simple syntax so the actual syntax to begin with creating a table is create table uh the table name in my particular case is test well you can have it anything that you want and we're going to open a bracket where uh whatever command we put in this particular uh you know as a parameter needs to be under this parentheses guys so as you can see you have highlighted one and it highlights the other one for me so whatever from here till here pretty much goes on uh it goes on under the create table header guys so i have created three columns for my database one is the name it's a varchar it means it's a character name is a character right i cannot have name as two and three and what not and then we have h age is pretty much the integer as we uh pretty much can use go on using we don't have to use shortened long end uh you know tiny intent all of that because again we know pretty much if you're going to work with a human age it's going to be somewhere between 0 and maximum 100 or something right so interval suffice and then we have the uh pretty much if the person is the male or a female and more so pretty much that that again is a single character so we're gonna use care varchar and we can go on using text as well if you want but then that's a waste of memory guys so as soon as i run this particular query i'm gonna select this query i'm gonna run it as soon as i run this what it does is our database is now created our table is now created but then we just created an empty table so we do not have any data in our table as of now so to quickly change that we're going to put in some data into the table so the command we use to put data into the table is insert into followed by table name and values and this is the most simple way you can go on to insert a test insert a value to your table guys so uh we're gonna have to map it in the way that we have created so i've created it as name age and sex so basically i need to go on to define name define age and define the sex guys so uh the name is name of this particular entry is rajesh his age is 20 and rajesh is male and then we have rohan who's 18 years old he's a male as well then we have sneha who's 22 years old and she's a female and we have anonym who is 19 years old and she's a female as well guys so we can run this query individually where we know we go on adding one by one one by one in fact i'm going to show you that right now so if i go on to run this query it says that you know one row has been affected it means that you've changed something in one row of your database so give me one second let me run this for you guys you can see here right so this is the output basically this is what we call as a result grid guys so here we can see that there's a date table created basically there's a database created there's a table inside the database where the name is rajesh he's 20 years old and he's male so we just used one of these right so let me run all of these and then we're going to run this command select star from test you might have seen the uh you know pretty much the basic sql query command that i showed you at the start of the session where we had something more like a where clause and whatnot right so we can use that as well but then this will give us the entire entry present in our date uh present in our table so as soon as i run this instead of just rajesh all of the all the four entities are present for us rajesh rohan sneha ananya and more guys so it is pretty much you know as simple as let me quickly bring this down a little let me bring this down a little yeah perfect so uh then this command is particularly for mysql workbench where we have something called as a safe mode and we uh you know take away the safe mode to work with the data without having to worry too much about keys case so this is something that you might not have to use all the time and i just quickly run it to make sure i do not get any uh warnings or errors up front guys so the next command is uh so we've created uh since we're doing the crud demo so we are done with the c part where we have created the database created the table uh we have read the data where we use this particular command to pretty much read all the data present in our database and then we pretty much now are left with u and d where u is for update d is for delete right so now we're going to update some entry in this table it's not as easy as double clicking and editing guys so you need to put in queries and that's why we call it a structured query language and then as you guys can see it is extremely simple so let me quickly zoom in a little in case uh you guys are watching this on your mobile or something so uh here is the query for updating it so update the table name so up it's basically test is our table name if you can see here right so test is our table name this is the creation command test again where we're inserting the data to which we're already done with we're printing all of the things that we've already done and now we're going to update this particular table and we're going to set a is equal to 19 okay but whose age are we going to set to be 19 right so there are four people right now we need to tell our database whose age i want to change to 19 uh so basically that is where the where clause comes into case so the where clause says hey uh hey database update my date uh you know update my table set age equal to 19 where name equal to rohan so it says find this person called rohan under the uh under the name column and change his particular age whatever it is to 19. so rohan as you can see in this particular mouse highlight that i've done for you guys currently is 18 years old so let's say manually you might have to change ages at the end of the year or for his birthday or whatnot so as soon as i go ahead and run this command rohan will not be 18 years anymore rohan will be 19 years old so let me use the select star from test to print the database out as you can see right so rohan is not 18 he's 19. so you can change all as well so you you can change rohan's name you can change his sex if you want and you can have a lot more this is a very simple database example guys you can have a lot more columns a lot thousands of entries where you can run complex queries once you are trained to work with sql as well guys so again as i've said uh pretty much my intention here is to just show you the basics and make you guys uh clear with the fundamentals of the crud operations so now we have updated rohan's age rohan is not 18 anymore rohan is 19 and pretty much this brings us to the d operation which is the delete operation again so let's say uh you know probably rohan just uh you know cleared out of college and they decided to drop his name from the database and they do not want his name anymore so delete from the table name or table name is test where name equal to one so basically it's i am telling it to wipe this so as you can see the mouse highlight and the blue color i'm just telling my database you know what i don't want rohan or any particular thing associated with rohan anymore so as soon as i run this command uh you can pretty much see that every time you run the command you have to view it right so again as you can see i have a lot of select star from tests everywhere present so as soon as i run this as you can see rohan is not there anymore we just have rajesh niha and ananya guys so this pretty much is the most simple way you can go on to performing a crud operations in my particular opinion guys but then if you have to reuse the same thing you will come up with one particular error case which i'll just show you right now so this this is a server architecture right so our database which is called demo and our table called test inside this demo is already present so if i go ahead and just run this query again to see what happens it's going to give me an error it says hey i cannot create another database with the name demo because something called as demo already exists so you're gonna have to you know change this to demo one demo one or two whatever you're gonna have to get into that database demo one or two and then you can use the same table name because you're using a different database now guys so it's as simple as that so and after you have done everything if you want to delete an entire database uh you know a database can have n number of tables so if you choose to delete all of them you can pretty much hit drop uh database and followed by the database name and done it says one row affected your database has been dropped so whatever we have done until line 26 from now pretty much is deleted guys you know it is as simple as this and you can understand the query because it is in such a high uh level syntax that it makes it very simple for beginners to learn guys what is sql injection so guys sql injection is basically a method using which you are able to run commands on an sql server or on an sql database through the user interface of a website and then you are able to do malicious tasks such as logging into the website without even knowing the username password of an account or getting or retrieving the details of a particular account or all the accounts which are there on our website by running some sql commands from the user interface right for example in this session today what we're going to do is we are going to have a look at a website like this which is basically going to have the username name and password fields and we are basically going to check when we basically enter the username we basically enter the password then we are able to login to the website but what happens when you do not know the username or when you do not know the password how can you inject an sql query from this user interface and log into the website without even knowing any username and password all right so that is something that we are going to understand and the method of um you know passing sql queries from a website into the database directly is basically called sql injection okay so now you might be wondering how does it basically work all right guys so let's go ahead and first understand how does a site basically work so first thing that you have is a database right and inside the database you will have a table which will have all the usernames which will have all the passwords and this usernames and passwords will then be basically fetched by a website like this right so whatever username and whatever password you will be entering right it will be checked against the names and the passwords which are entered over here right if they are an exact match that is that the if the email let's say if the username is sam and if the password is qwerty123 if this matches exactly that means it's a correct input and then in that case you will get a success message in case the username is sam but the password is something else let's say the password is not matching what is there in the database in that case the output or the sorry the input will be wrong and with that even the output will be failed right so first of all we will need an application which is going to work like this and uh what i've done is i've created a sample application which works exactly like this so what i have is basically a website so basically i have written some code in python which is basically going to help us have a web page right so let's run this code so let me just run it quickly python 3 1 dot p by and with this my code is now running so if i go to my browser and if i go to localhost 5000 this is the website which which i created now in this website what i can do is i can enter some username i can enter some password and then i can sign in i can check whether that username and password is correct or not okay so what is the username and what is the password it will be basically be picked up from mysql so this is this is the username this is the password which i have in the database as of now so if i enter a username which is intellipart and if its password is intel123 in that case you know i will get a success message over here so let us try that let us enter in telepart and let us enter the password as intel 123 let me click on sign in and you can see the messages login is successful similarly if let's say i enter the username as intellipart and now let's say i enter a wrong password let's say i enter in delhi part one two three let's say four so if i click on sign in in that case it is going to tell me login fail why because in my database for the username and telepart the password is intel123 so i can add more usernames over here so let's say if i add a username let's say insert into user let me enter a username let's say the username is sample and the password for the same is going to be sample 123 let's say this is a query that i pass and i missed out on values over here so let me enter that okay so now in if i check my database so these are the three records that i have so my username is sample and my password is sample123 so now if i go ahead and if i try this out so if i enter the login name as sample and if the password is sample one two three if i click on sign in you can see the password is login is success or the output is login is success if i let's say enter some wrong password and if i click on sign in it will say login as failed so guys with this you now get to know that the website is working right and this is the prerequisite that you need in order to understand how sql injection will work now what we're going to do is we're going to spoof this website into believing that i am a correct username or at the username that i am entering and the password that i'm entering is correct let's see how we can go ahead and understand that or how to do that let's go ahead and understand uh but before doing that you will have to know some basics about sql injection so let's understand those basics so guys these are some commands or these are some syntax that you need to know in order to perform this attack right so first of all what you need to understand is what is the meaning of hyphen hyphen so whenever you enter hyphen hyphen in an sql query that basically means it's a start of a comment what is a comment a comment basically means that let's say in this code in this python code if i want to make this line a comment which when basically a comment is made then basically that line is not processed or that code is ignored right so if i wanted to make this line a comment what i can simply do is i can add a hash in front of it and now you can see this code has now become gray which basically means when i will be running this code now this line will be ignored so in sql if you want to add a comment uh the line or the command for that is hyphen hyphen so wherever you will write hyphen iphone all the commands after hyphen hyphen will be ignored all right then you have something called as or now what is the or operator or operator basically means either of the input should be true so if i'm using this website either the username or the password should be correct right to simulate this uh let me just go to my database so let's say i enter something like this so if i enter select star from user where username is equal to him right this is correct the username is there or password is equal to let's enter something random then it should tell us whether this username is correct or not this is a query let's hit enter it says password in where clause okay so not password sorry it will be pass the column name is pass so can you see it is still returning me the username and password saying that these exist why because i've used the or operator but this is not the correct output this is not the output that i want when i'm trying to log in when i'm trying to log in what i want to do is i want to see whether the username and the password are correct or not right so taking the same statement again if i change this or to be and then it will not give me any output which means empty set which basically means that there is no username with the name heymonth and the password this and hence it will give me a wrong or it will give me a empty set which basically the user does not exist and hence i will get a login failed on my website right so this is the difference between or and and so in or either of the input should be there in the database and then and both the input should be present correctly in the database only then it will give us a successful message what is one is equal to one one is equal to one is a statement which is always true right and uh what does that mean whenever i will write let's say if i write when username is equal to hemanth and 1 equal to 1 which basically means that this statement is always going to be true in case the username is hema because the second condition is always going to be true which is one equal to one right so this syntax basically means that the statement or the condition is true and you can use it anywhere you can use it while you are you know working with an sql query or when you basically want to go ahead and execute a statement so now let's go ahead and try these out and understand how you know will this work when you're working with an sql query using these syntaxes okay so let's go ahead and write an sql query and let's see what the output would be according to what we have just learned okay just a quick info guys intellipaat provides sql online training in partnership with microsoft and mentored by industry experts the course link is given in the description below now let's continue with the session so coming back to my mysql shell so let's say i have let's say i go ahead and first show you all the records again which i have in the table so these are all the records that i have in the table right now what i'll be doing is let's say i type in select star from user where hey where username is equal to hemanth and 1 is equal to 1. now what do you think will be the output of this so let's try and analyze so what did we learn in this slide we learned that when we use and then both of the input should be true so do we have username equal to hemanth in the user table yes we do and this is the condition which we're using so this is true now let's see if this is true or not and according to what we have learned this condition is always true right now if you try and execute this you will get the username heymonth and the password heyment123 and then if we type in let's say let's say i type in a query like select star from user where username equal to a month and password is equal to let's say a wrong password let's say it's one two three four five right so in this case it will not return anything now let's try to edit this query and let's try to apply the syntax that we have just learnt right so what we'll do is first let's comment out the part which we don't need so we don't need the pass uh the part over here this is correct but this part part is wrong right this because of this the sql query is not running so let's comment this part out so we'll add hyphen hyphen space so this part is now commented and now if we just enter 1 equal to 1 over here and give a semicolon let's see what is the output that we get and now as you can see you were able to bypass what you just wrote right so you commented out whatever was there later and you added one equal to one before that comment and because of this this query is just executing up till here and whatever was there after and you have commented it by adding hyphen hyphen right so this is the basics of an sql injection if you understand this now it will be very easy for you to gauge what i'll be telling you later right so now let's go ahead guys and now let's try and understand how we can do sql injection using a website like this all right guys so what we know of this website right now is that if i enter the username and i let's say this is the correct username as i know let me enter the wrong password over here so what happens let's see so if i enter the wrong password and if i click on sign in i get login failed so i want to change this or i want to achieve that i should get login success over here so let's see how we can bypass this feature so as we know right now that the syntax that the current website is running in the background is select star from user where username is this and password is this so this is how most of the login websites basically work now in order to achieve an sql injection query or an sql injection use case what i will have to do is i will have to change the sql query or i will have to try to change the sql query from this user interface so that i can basically bypass into the system so how can we do that so that can be achieved by using the syntax that we learned earlier and let me first show you how we can do it and then i'll explain you what would have happened so before whatever string that i'm entering over here that will have a semi uh that will have a apostrophe starting before it or that will have an inverted comma starting before it's let me close the inverted commas first and then what i'll type in is i will type in or and then i will type in the query which is also always going to be true which is one equal to one right and then i'm going to close this statement and after this whatever is there i'm going to comment it out by typing in hyphen hyphen and then space okay so this is you know the argument that i'm going to pass over here and now let's click on sign in so it says this field is required no issues you can fill this field and now let's click on sign in so now as you can see it says login success now how do you think this would have happened let me go ahead and show you so the query that we have in the code is select star from user where username is equal to hemanth and pass is equal to some password okay this is the query which is executing in the background now what i have done is i have replaced hemanth with a string which goes something like this so i first entered inverted comma then i entered a space and then i said or one is equal to one semicolon space comment sign and then space now see what has happened this query has now changed to this query and i have commented the rest of the part out now if you execute this query what will you get you will get all the usernames and you will get all the password let me hit enter can you see i've got all the usernames and i've got all the password and because this is something which is not an empty set the website is now telling me that the login is successful so guys this is a very simple and this is a very dangerous sql injection that we have just done because not only you have got access to the website not only you have spoofed the website but in case you are able to get this result set out uh through some method you will be able to get all the usernames and all the passwords that are there on the database of this website right so guys this is how you do sql injection now now that we know how sql injection is done let's go ahead and understand is it really important to prevent sql injections so you might be wondering now whatever i've told you not a lot of people will be knowing about it right and that is actually true not a lot of people know about how to hack websites or why would what would be the purpose of someone hacking your website so probably it just happens to big companies like sony like linkedin like twitter right so why should you be worried about it so that's a valid point but imagine right now if you're doing a startup right probably right now the need is not there but if something like this happens to your website because as we are growing towards a more technological world a more world which is a world which is more online more and more people are getting the skill sets to work online or use the internet and with that if let's say somehow somebody you know targets your website it is going to be a huge loss to your business because the users which were using your website will not trust your website anymore so to sum up what is it important to prevent sql injections the point is that yes the prevalence is going to be very less the uh the attack might be happening to your website or it might not be the chances of it happening is very less because there are very less individuals in the world out there who can do sql injections right and who would want to target a very small company that you have just started but does this mean that you should ignore it well no because the ease of exploitation for this particular subject that we just discussed is very easy right and with that in the future as and when you know we are going into the future more and more people will get aware about this particular this particular concept this particular technology and hence the chances of even somebody playing around with your website is going to get you a huge business loss right and that's why the impact of this is going to be devastating so that's why it is very important to prevent sql injections be it a very small company be it a medium size company and it is important for large companies obviously right and that's why you should learn all the best practices for development you should include people in the team who are cyber security experts you should have test cases in your development team which is basically going to go and help you out in understanding if your code is safe or not because it does not matter if your code is just working your code has to be safe as well and this is why you know the demand for ethical hackers the demand for security specialists is now increasing day by day because companies have now started to recognize that yes this is a threat yes this is something that needs to be addressed okay so with that said how to prevent sql injections now you will say okay so it's very important to prevent sql injections but how should you go ahead and advise a company so that they can prevent sql injection so let's understand that so first thing is you should use the principle of least privilege which basically means let's say this website is able to you know insert or select data out of the database so this website should have a username which has the least privileges allotted to it so that it can only do what it is supposed to do for example we just saw that if we use this website we are able to list all the usernames and all the passwords you know which are there in the table so probably that should be a permission which should not be allowed you know to the to the user who is going to be you know assigned inside your code for example if you have a look at the code the username that we have given over here is root and that is absolutely wrong you should never use root or you should never use an admin user in your code so it's very important to have a username which is going to have the least privileges the privileges which will only help you to get the work done and that should be assigned to your code so that's point number one second point is that all the passwords all the usernames if they can be hashed in some way then also this attack can be it can be basically avoided how because right now we relied on the fact that whatever i'm entering it is not being encrypted somehow and then it is plainly being passed to sql but what if we put in a system in between which can basically encrypt all the data or all the user inputs that you are giving and that encryption is basically tested out in uh you know your or it is passed to sql as an argument so in that case what is going to happen is you will not be able to run any sql query right so that is also a possible you know preventive measure that you can take and third thing that you can do is you can always employ third-party authentication so you would have seen that a lot of websites now have options like login with facebook login with google right so all these big companies they have gone through a lot of you know work ex they have been through a lot of situations they have a lot of people who are working on the security aspect of their website and you can be rest assured that you as a comp owner or you as a manager of a tech team if you apply your brains and try to create a model which is cannot be spoofed obviously that is not going to help you out because the kind of feedback which feedback which facebook and twitter as a company gets because they have a lot of people like you right who probably would be able to you know test the application before it goes on to production test out with different kind of scenarios right and with that fact that application obviously is more mature right so it's always a good idea if let's say you do not want to go ahead and just make your application secure because otherwise your your thought would be just making your application secure whereas your stakeholders would want more features in your application right so if you have to balance both of them why not use things which are already available and with that fact why not use the third party authentication tools which are already available out there right so this would be some of the methods that you can use to prevent sql injections and obviously there are more ways as well so you can go ahead and practice the best practices of you know coding for example instead of concatenating the arguments that like we just did in the code you can also pass them as arguments and that is a topic for a separate discussion altogether but majorly if you follow all these three points they should be enough to prevent a sql injection now how do you become an sql developer there are several tasks that are performed by an sql developer and if you wish to become an sql developer then you need to be comfortable performing these tasks so let's take a look at them one by one the first is data modeling data modeling basically means getting the entire business requirements that you need for a company to store data and then creating relational models with creating tables creating identifying rows identifying the columns identifying the data types that those columns might hold and identifying the relationships between those tables and the constraints between those tables now it might sound very difficult but don't worry stick with me till the end of the presentation and i'll show you how you can learn all of these things these aren't as hard as they may sound so data modeling is one of the most important tasks that an sql developer performs it allows him to transfer the business requirements into the sql database queries then comes data management once the data modeling is done the sql developer then needs to get the data that the customer wants to insert insert them and keep the databases running in a consistent state and figure out how to keep the entire database in a consistent state this is one of the tasks that are performed by sql developer then comes backup and restore this is more of a precautionary measure because data is the most important part of an of an organization they need to keep the backups of the databases that they have now this could be decided by the company about how to go about it it could be that they take hourly backups could be that they take monthly backups or weekly backups whatever the company policy is the sql developer needs to implement those policy and many times they do it by automating these backups and then when the databases go out go out of network or something bad happens to the databases that we are working on we can restore it using the latest copy of the backup that we've made then comes data migration it does happen more often than you might think that you might need to change the database management system or you might need to migrate the data from one server to another server this is where data migration comes into play supposing you are using a local sql server and you're storing the data in that and your company decides that now it's time time to move on cloud databases now you need to figure out a way to transfer all the data that you have on your local sql server to the cloud sql or cloud database and that's part of data migration which is one of the tasks performed by an sql developer now let's take a look at the skills that you need to become an sql developer you need to know sql i mean it's in the name sql developer sql stands for structured query language you basically need to know how to interact with the database management systems how to issue queries and also how understand how you could write queries that perform the tasks exactly as you need them then comes programming now you don't have to be an expert in programming by no means you need to know the entirety of a programming language all you need to know is how to use a programming language to interface between your application and your database so suppose you are you building a web application you need to and you're using mysql and your web application is php then you need to understand how to connect that php application to your database and how to issue queries and how to fetch the results and display to the user then comes profiling now this is one of the most important tasks that a sql developer does profiling basically means that once your database becomes of a considerable size it might start to slow down it might perform perform poorly and now your tasks becomes to profile it which is to say to understand what parts of the database are causing this performance lag you need to then take a look at the entire databases issue some queries profile them and figure out what are the performance bottlenecks and try and figure out how you can remove those bottlenecks and how you can improve the performance which brings us to the final point which is performance tuning as the name suggests performance tuning is used to increase the performance of our database management systems there are several ways to do it and you need to understand what are the pros and cons of all those ways and then implement the one that fits best for your need and now these are the steps to become an sql developer so the first thing that you need to understand is you need to be extremely comfortable with sql you need to understand how you can issue queries how you can write sql statements that translate a business requirement into into an entire data query this should allow you to write queries and get the data back from the database in the desired format and display to the user or send it back to the client or the application that's using the database then comes getting certified now in today's a day and age certification is one of the most important things that allows recruiters to to separate you from the crowd of all the resumes that they get for given job many people will be applying and if you are certified you definitely have an edge over the other uncertified candidates so definitely keep in mind getting certified do does bring you ahead then build a portfolio this sort of ties in with getting certificates getting the certification building a portfolio basically means that you need to build things with the knowledge that you acquired while you are getting certified now this sometimes as so happens that you are asked to build projects when you are getting certified so even after you do get certified it's more than important to work with the skills that you have and try and build things that could attract some of the recruiters they look at your portfolio and they'll understand that you like doing these kinds of things and you might even get job offers because of those and then finally after building a reasonable portfolio apply for the jobs start looking for jobs and apply for them and you have a really good chance of getting the jobs if you perform these tasks to give an example here's a job description of an sql developer so they need you to be an sql expert with the sql queries you need to understand how to do joins and sub queries we'll take a look at them in the demo uni also need to be an expert in creating tables triggers views don't worry if you don't understand them as i said stick with me till the end of the slide and i'll show you how you can go about learning these things and getting a job as an sql developer you do need some medium level stored procedures think of stored procedures as basically functions that you can run by issuing just one command and they'll do an entire list of things for you you also need to have a good grasp of the concepts of databases there are several fundamental theoretical concepts about database management systems that you need to understand in order to be very efficient with your database skills and finally good communication skills here's another job description and it's quite similar to the previous one but you do need to understand that you several uh several companies might have different requirements from their sql developers for instance here they want you to optimize in application sql statements so this is where the programming part of your learning journey comes in you need to ensure performance security and availability of databases you need to prepare documentations and specifications that tell the user of the database how the tables are structured and how you can go about doing them then there's profiling the server and resource users and optimizing and tweaking as necessary this is where it comes to optimizing the servers to perform better than they are and finally collaborate with other team members and stakeholders this is where you collaborate with other people try and figure out the value that the system should be bringing out and then use the knowledge that you acquired in that collaboration and use your sql skills to build databases and work with the team and now finally we'll take a look at a demo so i'm using mariadb you can use any database system that you want to use if you can you can use mysql as well or any other database system that you want everything is fine so we'll be in this demo i'll try and keep things simple so that everything doesn't go over your head and i'll try and unders make you understand what i'm trying to do so in this demo we'll be creating a database for a small car inventory database so for a car inventory application we'll be building a small database that will have two tables each table first table will contain the make of the car such as toyota hyundai or whatever and the second table would contain the model information about the car the name of the model the make id or the year and the price and so on so let's start whenever you are working with a database management system the first thing you need to see is what are the databases that are already created so type the command show databases its basis with ns plural so as you can see by default our database management systems comes with four databases these are used by the database management system internally so you don't have to fiddle around with those databases now you can pick the name of your database that you want make sure that it's not already existing in the existing databases so just type the name so to create a database just type create database and as you can see i'm typing the queries in uppercase letters i'll show you why i'm doing that in a moment and the name of the database would be car inventory now you can name it anything you want just make sure that you remember what you named it and end the sql statement with a co semicolon press enter and our query ran okay it one robot was affected which means our database is created perfectly now if we run the show databases command again as you can see we have a car inventory we just run use car inventory now whatever commands we issue will use this database this is an important step now comes the fun part now we'll be creating the tables and i'll show you what are the keys that we'll be using why we are using them the first thing we need to do is we need to create a table named make which will store the make of the car now as you can see i am typing the create in table cover words in the keywords in the sql statement as capitalized and make is in small letter this is mostly preferential but i like to do this to separate the things that i can rename and separate the things that are the keywords so i can't type any other keyword then create to create a table but i can rename the table from make to make car or car make or whatever this is something that i can choose so to differentiate between those i like to do it this way you can choose whatever syntax you like now open the brace press enter and now we will begin defining the model of our database so the first thing we need is an id think of an id as a primary key so if you have been a student you know that you are assigned role numbers those role numbers are used to uniquely identify you so even if another student in your class has the exact same name as you you can still be differentiated using the roll number this is what the id is for in any database or relational database table it would be an integer we do not want it to be null null means absence of value we do not want anyone as we do not want any student to not have a role number and finally we want it to auto increment which means as we are entering the rows into our database tables these ids are auto generated and they are automatically assigned in an increasing order from one okay now we need the name of the make of the card i'm going to store it as a variable character of length 30 and finally i will define the primary key to be id don't worry if this syntax doesn't make sense i'm just trying to get you to understand how to issue these queries and i'll guide you to the resources that you need to learn these syntaxes finally let's close our syntax and press enter and as you can see it worked fine if it would have thrown some error that would have been a problem because it has not let's move ahead now we will create the table model which is the last table that we need to create for this demo [Music] now again i'll be assigning an id which would be the similar thing it would be integer not null and auto increment press comma and now we need to define the things that we need to store in this model table the name of the model would be a variable character variable character basically means it could be somewhere around 30 characters the name of the name of the name of the model of the car and we do not want it to be null so it's not then comes price now price i can store it as integer although you do need to spend more time with the customers and your business stakeholders to understand whether you want to store it as null or a precision value like float or double and then comes year now you can store here as integer as well i'll be storing it as variable character because i will not be performing any mathematical operations on it and variable characters allow you to use less memory you do it not null because we do not want it to be empty at any case which is why it maintains the integrity of our data and finally this is the foreign key this make id will contain the information about the make of the car which will be stored in the make table i'll show you what that means in just a minute which has to be inked and it has to be north null now one thing to note is that this make id would have same properties as the id of the make table you don't need to add the auto increments just the integer and it's not null and finally we will define the primary key the primary key of the table would be the id not the make id the id of the table and finally and it's done now the last part of the table is that we'll be adding a constraint a constraint basically allows us to enforce some data integrity rules so in this what we're doing is we are adding a constraint by altering the table named model we are adding the constraint and we are naming it car model make fk fk stands for foreign key so we will be adding a foreign key on the make id of the table make id as you remember is the attribute that we created when we are creating the model table and this will be referencing the make tables id column and the final thing that we want to add is we want it to cascade on delete i will show all of what all of this means in just a moment okay i think that's in oh i misspelled the word foreign [Music] okay and another thing that i references i think yeah as you can see typing long queries could lead to issues as you can see it's done and our foreign key constraint are in place so what this means basically is that inside our model table the make id will only contain the information that already exists in the make table so for instance if i enter a car and i want to add its make i cannot add the id 4 or 5 or whatever i want arbitrarily this would lead to data integrity violation which we have prevented by by adding a foreign key constraint and what all of these means i'll show you in a minute let's start and insert into the make table and now we will be inserting into make and the thing that we want to insert is the name the id will be auto generated and auto incremented so we do not need to worry about it and the values that i want to insert is since it's a variable character i need to store it as a string and i will store it as maruti so it's inserted let's store let's store toyota toyota finally let's store value and hyundai now that these three things are inserted let's see select star from make so we have three names for the makes of the cards that we'll be storing and the ids are as you can see automatically generated and incremented now what we need to do we need to insert into the model inside the model we will insert the name of the model the price of the model here of the model and finally the make id now this is where our integrity constraints come into play i'll show you what i mean in just a second as you can see there are only one two and three ids so if i try to add a car with id4 it will it should show me an error let's see whether it does or not so i'll be adding hyundai i10 let's price it arbitrarily okay and let's say the year is 2018 now finally comes time for the make id so i enter four and as you can see that we cannot insert using oh sorry there's a yeah yeah so the foreign key constraint fails and it disallows me from inserting invalid data now if i want to insert insert the hyundai i10 i just need to make the make ids 3. and it does work now let's store some other values so that we can run further queries let's store i20 finally let's store also and let's do it this way so it works let's see select star from model and we have inserted everything and we have disallowed from inserting an invalid data as you can see invalid data was disallowed we got an error so it works perfectly fine now we will be looking at some advanced query we'll be using something called a join so suppose that i want to write a query that will show me the name of the model the make of the model and the price of the car so i want the make name the model name and the price i want maruti uh sorry hyundai i10 and price seven lakh or whatever it is so since these are stored in two tables for maintaining the integrity we need to perform a slightly more advanced query this is called a join query so let's do that so i will show you how to do it using the select keyword i will be using the make tables name and i will be showing it as make underscore name i'll show you what that does in just a moment now that that's done i will be doing the showing the model name and model dot price now that we have defined what we want to display let's tell which table we want to use so i will be using the model table press enter and now we will tell the sql database management system which is the table column that we want to join on so to join on we'll be using the foreign key constraint basically we tell it okay join on the table named make and on the column id on the make and make sure that it equals the model tables make id and here we go so we have maruti alto 7 lakh hyundai i10 hyundai i20 so we have the data that we want to use and we have it stored in different tables and just one query allowed us to write it entirely this make dot name is make underscore name i wrote it because the name attribute might have conflicted and having two names in the same attribute could be a little uh ambiguous to understand which we are talking about so that's why i named the column name is make underscore name and we can do it on the fly so it's not actually done in the table it's done right here now if you want to look at what are the constraints that you have laid on the table basically write this command we will see what are the constraints that are on our make table so we will be selecting the column name and the constraint name [Music] from information underscore schema this is a table that has been already created or this is the database that was already created and used by our database management system it's called information schema and we will be using the key column usage table these this is maintained by the database management system so we do not have to do anything and finally we tell it the name of the table that we want the data for so we go where table name let me write it table name equals to and the name of the table that we are using here is the model table and as you can see we have a primary key constraint on the id attribute and a foreign key constraint named car model fk which is called make id now i'd like to show you what we did when we applied the foreign key constraint and added the on delete cascade so what it actually means is that let me show you we select everything from make select everything from model and now what i want to do is when i delete the hyundai row from our make table the model table every car related to hyundai should be deleted automatically so to do this you basically have to just issue a command like this delete from make where id equals to and the id for the hyundai is three i press enter now let me see and as you can see every car associated with the hyundai make has been removed this again enforces the integrity constraints and as you can see the hyundai arrow is deleted from here as well now finally i'd like to show you how you can remove a foreign key constraint so for instance this if if you placed an incorrect constraint how you can go about deleting it so it's quite simple all you have to do is type the query alt table the name of the table on which we have placed the foreign key constraint was called model type drop foreign key and the name of the foreign key as we can see here is car model fk car model make fk and i press enter and this is done so now let me go up and see if the foreign key constraints are still there and it's removed so these are the kind of tasks that a sql developer needs to perform so the meaning is pretty simple guys as i told you sql stands for structured query language and it is one of the most amazing uh language which is basically used to manage a relational database system and what is mysql mysql is the relational database system which is basically used alongside sql to perform operations case so any operations such as storing data retrieval of data or modification of data in the database and this brings us to the second point which is basically complexity when if you talk about complexity you know sql as a language it has to be learnt on it has to be worked on and practiced to ensure that you can make the most efficient use of your queries to work with your database then coming to mysql you know mysql is basically a readily available software which is you know you can which you can download it install it and begin working with it without much of a prerequisite and then coming to point number three it is the type of usage that we have to talk about because sql is a very fine language it is a very well-tuned language which is uh put together to work with a database but mysql is an entire database software an entire platform which is basically making use of sql to query the database guys again uh when you have to think about what to learn with again if you're using mysql then obviously learning sql is a prerequisite but then if you're just learning sql then you might not have to work with a mysql database because there are many other forms of relational database management systems where you can go on to use sql as well coming to point number four point number four is the purpose when you talk about purpose again sql is the language which is used to operate on the database while mysql is the tool which allows sql to perform operations such as data handling data storage data modification deletion operations and much more so i hope you guys are getting the general picture your sql is the language while mysql is the database on which sql works with guys coming to point number five point number five is the usage uh when you have to talk about usage again there are various uh techniques which sql uses you know there are multiple code segments code snippets multiple commands and pretty much everything else which you know is used to talk to various rdbms systems and in fact mysql is one of them but then you have to talk about mysql mysql is the rdbms uh database pretty much which ensures our languages such as sql can run on top of it but then if you're thinking how well does sql run on mysql guys it is extremely efficient and even today sql is a very old language and mysql is a pretty old open source software too from its day of the release pretty much all those years ago it's still there and i would not consider uh calling sql or any of the mysql rdbms as legacy because as i told you uh there are companies around the world fortune 500 companies who make use of these every single day guys and then coming to point number six it's updates because sql is a fixed language right so no matter what there are multiple flavors of sql of course there's a oracle sql there's some microsoft sql there's multiple types of sql which is basically like a flavor of it you can run a pretty much if you learn uh sql right so sql is a fixed language if you learn it then pretty much you can all the code all the commands 99 of the code segments of the syntax pretty much remains the same but then when you have to talk about mysql uh the updates are more frequent here because at the end of the day it's a software and it has to cater to various customer bases but then even at the end of the day solving bugs making your software more efficient or helping you work with your database management system in a more efficient way if that requires frequent updates then yes mysql provides that guys then coming to the quick summarization of this particular difference sql as i told you it has been a big player it has been a huge player in the world of i.t and it is very very vital when we have to uh talk about the applications and its usage in applications guys and coming to mysql you know mysql brings about a software which pretty much makes the best possible way to store structured data into it on the database and eventually to work with it later on by making the developers or the coders life a lot easier guys and on that note you have reached the end of this quick comparison between sql and mysql okay so we've seen that next we'll look into the skills to have as a sql developer so we saw what are the requirements and the roles of an sql developer now the skills just a quick info guys intellipaat provides sql online training in partnership with microsoft and mentored by industry experts the course link is given in the description below now let's continue with the session first of all data management obviously because database manages uh so has data in it so to manage that you will need to know a very good knowledge of sql so that you will be able to manipulate it and also manage the data accordingly software programming is required it is not mandatory that you can only become an sql developer having a very raw knowledge of a programming language but having the basics or having the intermediate knowledge of software programming so let's say your company uses java then learning how java works will obviously help when you build a database for that company so because that database should be compatible with java and yes so that's what you should also know software programming server development and maintenance so this again you can learn on the go so server development in the sense to run virtual machines and to create databases on it and also maintain it problem solving if there is a problem arised if there is a problem so this particular person should be able to create a query which solves that problem or should be able to make the database more compatible and then tsql which is basically related with transactions so learning this is not compulsory at the beginning for an sql developer but learning this will also improve their chances of getting a job then process enhancement this is to make better business decisions so you can be an sql developer and also be involved in process enhancement that is a company might go with a set of processes which is already organized and they go with that flow so let's say an sql developer can go ahead and change that process if that benefits the need okay so you've seen this okay so these are the basic uh skills which you need first of all learn sql so that is the most basic thing uh so if you learn sql you'll be able to manage data and understand software programming because without software programming without knowing how an application works you will not be able to build in a compatible database with it development and maintenance problem solving t sql and process enhancement so make sure to go buy the flow start off with sql software programming and then move it with the other tools and then the career path so once you have all these skills you can obviously start applying for a job then as an sql developer there are various profiles it is not just developer there are so many different profiles within a company so as i told you a database sorry an sql developer should be able to maintain uh should be able to build should be able to manipulate so let's say if you want to maintain a database uh so you can become a database administrator so the basic task of a datum is a database administrator is to administer the usage of the database in that particular company so you will be the head of the entire database system you will be able to administer every single transaction so it's basically maintaining and then a database developer is a person who builds and also manipulates databases so you can also become a database developer by doing by having the skills uh so let's say for a database administrator you should go with more of a view that you'll be able to manage any kinds of problems which are arising you should be able to solve any kinds of problems which are arising a database developer should go with uh let's say this person should be able to create sql queries for any scenarios given in the company okay so next is data scientists so obviously data scientists need sql without sql it will be very hard for them so sql is a need for data scientists as well so learning any sql that is any sql engine will help the data scientists and then etl developer so this basically involves big data extract transform and load and again you can also become an etl developer by learning databases then bi developer and administrator so bi is business intelligence so in business intelligence you obviously need to create visualizations and to create visualizations you need to manipulate or query data and to do that you should obviously know sql and it will help in becoming a ba developer so to become a ba developer and administrator you should not just know these skills which i showed you for an sql developer you should also learn bi tools like tableau or power bi so only then you can move ahead with bi okay so now we've seen that we look into the certification path for an sql developer first of all uh what are certifications and why do you need it first of all it's a stamp of excellence that is if you uh have it on your resume it adds value so that you don't need to even explain that you know database if they see that you have a certificate from oracle or let's say a certificate from microsoft they will understand that you have prepared and cleared that exam and you know well enough and then substantial increase in pay so once you get earning certifications obviously you are putting in time and effort to make yourself better in your career so that will obviously give you a good hike clear corporate requirements so as i told you every single company needs a database developer so for that so for clear corporate requirements that is few companies might have a requirement of having a particular certification and that will help if you're going to go for that particular job and helps get you hired so obviously these everything goes back to that point so these are certifications why do you need a certification and also while you do certifications you'll be able to understand a lot and also will be able to experiment with a lot of projects before you enter enter the corporate career itself okay so these are some sql developer certifications first one is the ibm certified database administrator db2 so this involves the database called db2 then microsoft sql server database certification this involves a microsoft sql and then oracle under oracle there are two one is oracle certified professional or mysql database administrator so mysql is one of the most popular databases in the world and you can go ahead with mysql database administrator without any hesitations but the one more thing you'll have to remember is there are n number of mysql database administrators or oracle certified professionals also oracle database 12c administrator is also one more certification which you take up to go for a particular role so these are some of the most popular certification courses so in s for sql developer so if you want to uh it does not just so let's say if you complete the ibm certification it is not just you can go for ibm the thing is you can go for any company which uses db2 or if you do the microsoft sql server so any company which uses microsoft sql server needs a database developer or an administrator with an app certification and you can be eligible for that and same goes with oracle so these are some of the popular certifications you can check out more if you want to check out other so let's say mariadb or there are there is a number of database engines out there you can go ahead and check them out so these are some of the popular certifications and highly recommended okay so we've seen that now let's look into the most important part which are the job trends because obviously at the end of the day you will need a job and also salary so first of all sql developer in the usa they are getting about seventy two thousand two hundred and eighty two dollars per year as an average so this is the average amount there is low there is a low there is a high to this and in india it goes around 6.5 lpa so these are the two variants in u.s and india so this is for an sql developer role and this is totally relative because this is uh this data is collected from glassdoor or pscale and those data is based on the salaries which other let's say there are 100 users in glassdoor who are sql developers if they post their salary it is aggregated and the mean is given to you as the salary which is provided here so it can vary it can be a it can be lower than this for a beginner and it can be higher than this for a experienced database professional so we've seen that so who's hiring so every single company which uses the database so that puts under uber spotify netflix instagram dropbox airbnb twitter and any other company so you can go ahead learn this and you will have to pick out you will have n number of companies to pick out you can attend a lot of interviews and if you are talented enough you will be able to bag a job in a couple of days or maybe weeks so what exactly a sequel so this is a very basic question so to answer this sql stands for the structured query language and according to ansi it is a standard query language for relational database management systems which is used for maintaining the relational database and also to perform different operations of data manipulation on different types of data so sql is widely used language for maintaining relational database and in the year 1986 sql became the standard of american national standards institute or ansi so basically it is a database language which is used for creation and deletion of databases and it can be used to fetch and modify rows from the table and also multiple other things so let's head on to the next question so we are supposed to explain the different types of sql commands so the sql commands can be divided into four categories so we've got data query language data definition language data manipulation language and data control language so the data query language comprised of just one command select though it can be accompanied with many clauses to compose queries on a database then we have the data definition language so ddl is that part of sql which defines the data structure of the database in the initial stage when the database is about to be created so it is mainly used to create and restructure the database objects so you can see commands like create table alt table and drop table in ddl and after that we have the data manipulation language so dml is used to manipulate the already existing data in the database that is it helps the user to retrieve and manipulate the data and it is used to perform operations like inserting data into the database through the insert command updating data in the database through the update command and deleting data from the database through the delete command and finally we have the data control language which is used to control access to the data in the database so these dcl commands are normally used to create objects related to user access and also to control the distribution of privileges among users and you see commands such as grant and revoke in dcl right so this is the next question so what exactly are constraints and we are supposed to explain about the default and the unique constraint so constraints are basically used to specify some sort of rules on the data and limit the type of data that can go into a table all right so let's understand about default constraint so the default constraint is used to provide a default value for a column so the default value will be added to all the new records if no other value is specified for example if we assign the default constraint to this e salary column and set the default value to be equal to 85 000 then all the entries of this column will have a default value of 85000 if no other value is assigned during the insertion so similarly we can also assign a default value of analytics to this e d e p t column right so now we'll go to sql server and we'll work with the default constraint now i'll start by creating a new table and i'll add the default constraint to one of those columns so i'll create a new table and i'll name this table as stu 1 so the syntax would be create table 2 1 and this would comprise of these columns that would be s id which basically stands for the student id and the data type of this column would be integer after this there is s name which stands for student name and the data type would be varchar and the length of the string or the maximum length of the string would be 20. and then i'll finally have the marks coded by the student which would be s marks and this would be of integer type now after this i will given the default constraint and i will set the default value to be equal to 50. right so i have successfully created this table now let me have a glance at this table first so select star from stu 1 so this is our table which is empty as of now now what i'll do is i will go ahead and insert some values into the table so i'll type in insert induce 2 1 and then i'll given the name of the columns so this needs to be inserted so the name of the first column is sid and then i've got s name now i'll type in values and given the values for these two columns so let's see the first id is one and the name of the student is sam now i'll click on execute now let me have a glance at this modified table now select star from s21 so now we see that we have this default value of 50 in this s marks column and this is because we had assigned the default constraint to this column now similarly let me add two more records into this table so insert induced to one sid as name i'll given values so it'd be two and the name of the second student is bob right so i've also added the second record now let me add in the third record so the student id is three and the name of the student is matt right so now i have three records on this table select star from stu one so these are the two new records and the default value is 50 for these two new records so this is how we can work with the default constraint now comes the unique constraint so the unique constraint ensures that all the values in a column are different for example if we assign the unique constraint to this e name column then every entry in this column should have a unique value so here we see that all the names of the employees are different now again let's go to sql server and work with a unique constraint so again i'll create a new table and add the unique constraint to one of the columns create table and this time the name of the table would be sdu2 again there would be the id and this is of integer type now i would be assigning the unique constraint to this sid column and there would be a second column which is basically the student name and this is of wirecard type again the length of the string would be 20 right now let me have a glance with this table again so select star from stu 2 so this is our new table which is empty as of now now let me go ahead and add values into this so insert into sdu2 while use i'll add in the first record so 1 which would be the student id name of the student is julia now let me look at this table select star from sdu2 right so we have successfully inserted this record now let me add a new record so insert into stu 2 values now what i'll do is i will again give the id of the student to be 1 and the name of the student to be mad and let's see what happens so this time we get a message or a warning message and this states that violation of unique key constraint and we cannot insert duplicate key in object dbo dot sq2 and that is because we had given the unique constraint to this s id column that is why this has to be different so let me change this to be 2 now let me again run this right so now since this is a unique value 2 is not a duplicate that is why we were able to insert this record now again what i'll do is let me add another record with the id 2. so let's see now the name of the student is annie now again we get this warning message because id number two is already present so let me change this to three now let me run this again right so now we are able to insert this record so this unique constraint basically makes sure that there are no duplicate records inserted into this column so now we have the next question so again we have this employee table with us and we are supposed to find out the second highest salary from this table so let's go to sql server and find out the solution so let me start off by having a glance at the employee table select star from employee right so this is our employee table over here and we see that the highest salary is 159 000 and the second highest salary is 125 000 so we are supposed to extract this particular value from this data set now let's see how can we do this so we can perform this operation with the help of sub query now let me just type in the command select and since we want the maximum value i'll use the max aggregate function and this would be e salary so i want the maximum salary from the name of the table as employee i'll given the where clause and given the condition where e salary is not in after this i will given the sub query over here so the sub query again would be the same so select max of e-salary from employee so now if you have a glance at this you would see that both of these queries are same so select max of e sally from employee and select microsoft e sally from employee now this sub query would given this result which is 159 000 so and then this main query would make sure that this salary does not lie in this so this would give us the 125 000 result so select max of e sally from employee where e salary is not in 159 000 that would mean 125 000. so let me click on execute right so we have successfully got the result which is 125 000 and this is how we can extract the second highest salary so we have our next question over here so what do you understand by normalization and denomination so this is a question which is very frequently asked during the interviews so let's get to the answer so normalization and denormalization are basically methods used in databases and normalization is the process of reducing data redundancy and dependency by organizing fields and tables of the database so it involves constructing tables and setting up relationships between those tables according to certain rules and the redundancy and inconsistent dependency can be removed using these rules in order to make it more flexible so i am repeating it again guys so simply put normalization is the process of reducing the data redundancy and we are able to do this by organizing the fields and tables of the database and then we have denomination which is a contrary process to normalization so in denomination we basically add redundant data to speed up complex queries involving multiple table joints so in denomination we attempt to optimize the read performance of a database by adding redundant data or by grouping the data right so this is our next question now we are supposed to find out what exactly is wrong with the sql query so this is the sql query over here select gender average of age from employee where average age is greater than 30 and again we are grouping this with respect to gender so this is our employee table over here now let me go ahead and just type in the command which would be select e-gender and then i would want the average of each so average of e each from the name of the table is employee where again the average of each needs to be greater than 30 and this is being grouped with respect to gender now let me go ahead and click on execute so let's see what is the error so this warning message states that an aggregate may not appear in the where clause unless it is in a sub query contained in a having clause or a select list and the column being aggregated is an outer reference so this basically means that whenever we are working with aggregate functions and we are using group by we can't use the where clause so instead of the where clause you would have to use the having clause now let me change this to having and let me click on execute so now again we get an error over here and this error says that incorrect syntax near the keyword group and this is because if we are using the having clause then group by would come first and having would come second now let me cut this over here and let me paste this before having right now i'll click on execute so this has to be e gender over here now we've successfully got the result so just to reiterate the errors whenever we are working with aggregate functions we cannot use the where clause and we would have to use the having clause and if you are also using group by with having then group by would come first and having would follow it so next question so what do you know about the stuff function so their stuff function deletes a part of the string and then inserts another part into the string starting at a specified position so this is the syntax of the stuff function so stuff the stakes in these four parameters string one position length and string two so the string one is basically that string which would be overwritten position would basically indicate the starting location for overwriting the string and length is the length of the substitute string and string two is that string which would overwrite the string so again let's go to sql server and work with an example with the stuff function so now let's say i have this string which basically stands like this sql tutorial now i'd want to replace this sql tutorial with python tutorial right so for this purpose we can actually use the stuff function so let me go ahead and write the syntax for that it would be select i'll given the function stuff so let me start with the force parameter which is the string which is to be replaced so the string which is to be replaced is sequel tutorial and then i'll given the second argument which is the position so the position would be one that is i want to replace this so this is position number one and then i would have to given the length of the string which is substituted right so the length of sql is three i'll just type in three over here and then finally i will given string two which is basically that string which would replace this over here so i would type in python over here now i'll execute so initially our string was sql tutorial and with the help of the stuff function you were able to change this from sql tutorial to python tutorial so next up we'd have to explain about views in sql so what exactly is a view and then we'd have to give an example of you so you can consider views as virtual tables which are used to limit the information that we want to display and these are actually nothing but the result of an sql statement which have a name associated with them and since views are not virtually present it takes less space to store so let's take this example from this employee table let's say we wanted to perform multiple operations on the records where the gender is female so we can create a view only for the female employees from the entire employee table so let's go ahead and perform this in sql server now let me type the syntax for this so i would want to create a view which would comprise of only the female employees from the entire employee table now let me just have a glance at the employee table first so select star from employee now i would want a view which would comprise of just these two records now let me go ahead and write in the syntax for the view so it'd be create view and the name of the view would be female employee and after this i will given the keyword a s after that i'll type and select and then i'll given the condition so select start from employee where after the where clause i will given the condition which actually matters so gender needs to be equal to female right so i've successfully created this view with the name female employee which would comprise of only the records of the female employees now let me delete this and let me have a glance with the view which i've just created so select star from female employee right so this is our view which comprise of these two records and the names of the employees are ann and julia now similarly let's say if i wanted a view which would comprise of only those employees whose age is greater than 25 so let me go ahead and write the syntax for that so create view and then let's say the name of the view is h and then i'll type in the keyword as select now over here let me give in the statement so select star from employee where and then the condition would be e h needs to be greater than 25 so we have created this view now let me have a glance at this so select star from age right so this is a view which would comprise of only those records by the age of the employees greater than 25 and we've got four records over here sam's age is 45 julia's julius ages 30 math series 33 and jeff's age is 27 right so what is a stored procedure and then we'll have to give an example of stored procedure so a stored procedure is a prepared sql code which can be saved and reused or in other words you can consider a stored procedure to be a function consisting of many sql statements to access the database system so we can consolidate several sql statements into a stored procedure and execute them whenever and wherever required and stored procedure can be used as a means of modular programming that is you can create a stored procedure once store and call for several times whenever required so this supports faster execution when compared to executing multiple queries now let's look at the syntax of a stored procedure so well given the keywords create procedure and then we'll give the procedure name over here after that we will given the keyword e s and then we'll give the sql statement for which we'd want to create the procedure and then we'll end the procedure with the go keyword now after we create a procedure we need to execute it so this is the syntax to execute the procedure so we'll type in exec and then give the name of the procedure which is to be executed right so again let's have an example with the stored procedure so now i'm going to create a stored procedure which would help me to extract the age of the employees in a very easy way so let me type in the syntax for that create procedure and then i'll given the name of the procedure so this would be employee age after this i will type in the keyword es and then i'll given the select statement so select e each from employee now that this is done i'll given the go keyword right so now i have successfully created the stored procedure with the name employee age now i'll go ahead and execute this so it'll be exec or execute employee h right so we have created the stored procedure which would help me to easily access the age column from this employee table so next question what do you understand about join and explain about different joints so what joint clause is used to combine rows from two or more tables based on a related column between them so there are various types of joints which can be used to retrieve data and it depends on the relationship between tables so first we have inner join which basically returns records that have matching values in both the tables then we have left join so left join returns rows which are common between the tables and all rows of the left hand side table that is it returns all the rows from the left hand side table even though there are no matches in the right hand side table then we have right join so right join returns rows which are common between the tables and all the rows of the right hand side table that is it returns all the rows from the right hand side table even though there are no matches in the left hand side of the table and finally we have the full join so full join returns all rows from the left hand side table and all the rows from the right hand side table so now we'd have to explain about inner join properly so the inner join basically gives us those records which have matching values in both the tables so we've already learned this now let's say we have two tables table a and table b and when we apply inner join on these two tables we'll get only those records which are common to both table a and table b so this gray portion which you see over here so this would be the result of the inner join now let's look at the syntax of inner join so we'll give the keyword select and then we'll given the list of the columns following which will give the from keyword and then we'll give the name of the first table then we'll use the keywords in a join and then give the name of the second table after which we're given the on keyword so this on keyword tells sql that this inner join needs to be done on some condition and the condition over here states that the values from column x of table 1 should be equal to the values of column y from table 2. let me just retreat this the values of column x from table 1 should be equal to the values of column y from table 2. now let's go to sql server and work with this inner join so i have two tables over here so the first table would be the employee table and the second table would be the department table so let me have a glance at both of the tables so select star from employee now the second command would be select star from department so this is the employee table over here which we've already seen and then we have this department table now i'd want to have a inner join on both of these tables where the edept column from this table is equal to the d name column from this table that is wherever the values from these two columns match i'd want only those records now let me go ahead and write the syntax for this so i will type in select and then i'll given all of the columns which i want so i want the e name column and e department column from the employee table so this would be employee dot e name then i would want employee dot e department of that i would want the department name and the department location from the department table so i'll type in department dot d name and then i also want department dot d location right so these are all of the columns which i want from both the tables now after this i'll type in from and then i will type in the name of the first table which is employee and then i'll just type in inner join and then given the name of the second table which would be department then i'll given the on keyword and then given the condition so here the condition is the values of the d name column should be equal to the values of the e d e p d column so employee dot e name should be equal to department dot the name now i'll execute this right so this is the result so we have our employee table over here this is the department table and finally this is the result of the inner join so here we see that we've got only those records where the departments match in both of the tables so the match departments are support analytics and sales right so we've got support analytics and sales from over here because only those departments are present in this department column so next question well what do you understand about temporary table and then we'd have to write a query to create a temporary table so simply put temporary tables basically help us to store and process the intermediate results now these temporary tables are created in the temp db and are automatically deleted when they are no longer used so the temporary tables could be very useful in cases where we need to store temporary data now let's look at the syntax of a temporary table so first we will given the keywords create table and before we go ahead and give the name of the temporary table we'll have to precede it with a hash so again let's go ahead and create a temporary table so now what i'll do is i'll create a temporary table and i will name this as let's say book and then before the name of the table i'd have to precede it with a hash right now this would have two columns so first would be the book id and this would be of integer type and then this would have book cost which is again of integer type now let me execute this now i'll refresh over here i'll click on the plus sign i'll go into system databases and i'll go into temp db and over here let me click on the temporary table so we see that we have successfully created this temporary table with the name book now let me see what is present in this table so i'll type in select star from i'll put in the hash symbol over here and then i'll given book right so as of now this temporary table is empty now let me go ahead and insert something into this table so the command would pretty much be the same so insert into and then lb book i'll type in values over here so the only difference which you see when you're creating or working with a temporary table and the normal tables is you'd have to precede the name of the table with the hash so as simple as that rest everything would be the same and over here let me given the values so the book id is 1 and the book cost us 100 right now let me also add another record so the second id would be 2 and now the book cost would be let's say 232 all right now let me have a glance at this modified temporary table so select start from hash book right so we have added two records into this temporary table so now we'd have to explain the difference between oltp and olap so let's start with oltp so oltp basically stands for online transaction processing and you can consider it to be a class of software applications capable of supporting transaction oriented programs and an important attribute of an oltp system is its ability to maintain concurrency so oltp systems often follow a decentralized architecture to avoid single points of failure and these systems are generally designed for a large audience of end users who conduct short transactions and queries involved in such databases are generally simple need fast response times and return relatively few records so the number of transactions per second acts as an effective measure for that systems then we have olap so olap stands for online analytical processing and it is a class of software programs which are characterized by relatively low frequency of online transactions so queries are often too complex and involve a bunch of aggregations and for olap systems the effectiveness measure relies highly on response time so such systems are widely used for data mining or maintaining aggregated historical data and this is usually used in multi-dimensional schemas so then we have the next question so what is the difference between the delete and the truncate command so again keep this in mind guys this is a question which is very frequently asked in interviews so let's start off with the delete query so the delete query is used to delete or remove one or more existing tables so let's have a look at the syntax so we'll start with the delete keyword and then we'll put in the keyword from then we'll specify the name of the table of that we'll specify the where condition but then that's optional so let's see an example of this delete command so we have this table so the name of the table is sdu which basically is the student table now from this table i'd want to delete the record where the name of the student is bob so let me write the syntax for that so delete from and then i'd have to give the name of the table so the name of the table is stu so delete from sdu after that i'll given the where clause and give the condition where s name is equal to bob let me execute this right so we have successfully deleted that record from this table where the name of the student was bob so similarly let's say if i want to delete that record where the student id is three right so over here the condition will change so delete from sdu where s id equals three so about this time we have deleted that record where the student id was equal to three now let's look at the truncate statement so the truncate statement deletes all of the data inside the table so here we'll given the keywords truncate table and then give the name of the table so let's go to sql server and work with this truncate command so now we have this stu one table so let me go ahead and use the truncate command of this table so i'll type in truncate and then i'll given the name of the table which would be stu 1 so before that i'd have to type in table so truncate table stu 1 let me run this right so we have deleted all of the records from this table right so this is how the truncate command works now let's understand some differences between the delete command and the truncate command so when it comes to the type of language truncate would be your ddl command or a data definition language command and delete is a dml command or a data manipulation command now another difference would be we can't really execute a trigger in case of truncate while with delete we can accomplish a trigger now also if a table is referenced by any foreign key constraints then truncate won't work so if you have a foreign key then you'd have to use delete and you wouldn't be able to use the truncate command so next question what is the difference between union and union all operators so the union operator is used to combine the result set of two or more select statements for example if the first select statement returns these three fish and the second select statement returns these three fish then the union operator would return the result of these two select statements and if there is a record which is present in both the tables we'll get only one of them in the final result so here we see that this blue fish is present in both the tables a and b now when we apply the union operator on these two tables the final result of a union b would have only one of these bluefish and this is the syntax of the union operator so we'll give the first select statement after given the union operator following which will give the second select statement now you also need to keep in mind that the number and the order of columns must be same in both the select queries so let's work with this union operator now so we have these two tables over here so the name of this table is student details one and the name of this table is student details too and if we have a glance of the columns we see that both of the tables have three columns and the names of the columns are same and also the order of the columns are same so here we have sid s name and desk marks similarly over here we have sid s name and desmogs now let's go ahead and apply the union operator on this so i'll type in the first select statement so it'll be select star from student details one so let me change the spelling to student details after this i will apply the union operator and then given the second select statement so select star from student details 2. now let me click on execute so this is the result over here now let me also print in the first two original tables so select star from student details one and then i'll also put in select star from student details too so let me just change the spelling to student over here [Music] let me also change this over here so this will be student right so this is student details one this is student details two and this is the result of the union operator now if you have a glance at these two tables then you see that these two records are repeating over here right so there's and julia over here in the first table and there's also ann and julia in the second table now when we apply the union operator then we'll just have one record coming from each of these tables right so you've got an over here we've got julia over here and then we have finally have matt which comes from the second table this is how the union operator works just a quick info guys intellipaat provides sql online training in partnership with microsoft and mentored by industry experts the course link is given in the description below now let's continue with the session right now let's look at the union all operator so the union all operator gives all the records from both the tables including the duplicates as well so here since this blue fish is present in both the tables a and b the final result of a union all b would also have the duplicate value so here we see that we've got both of the blue fish from table a and table b now let's go ahead and implement this in sql server as well so here we have these two tables already ready which are student details 1 and student details 2. now what i'll do is i will copy this i'll paste this over here and just type in union all so union all i'll click on execute so student details one student details two let me just expand this over here right and this is the result of union all operator right so if you have a glance at this you'll see that these two are repeating so we would have duplicate values so we've got an twice over here and also julia twice over here so next question well what is the use of the intersect operator so the intersect operator helps to combine two select statements and returns only those records which are common to both the select statements right so if we've got table a and table b over here and if we apply the intersect operator on these two tables then we'll get only those records which are common to the result of the select statements of these two now let's look at the syntax so over here this is the syntax so we'll start off by giving the for select statement and then we'll give the intersect operator following which will give the second select statement now let's see an example on this so again we have these two tables over here student details one and student details too now let me apply the intersect operator on these two so let me just type in intersect over here and i'll click on execute now let me maximize this over here right so student details one student details two and this is the result of the intersect operator so we see that since anne and julia are common to both of these tables this is the resultant of the select statement right so the select statement on the first table gives out ann and julia and the select statement from the second table also gives out ann and julia so the intersection of these two would be this so we have our final question now let's say you're given a table now you would have to copy the data of this table into another table so you have this employee table now you'd have to go ahead and copy the data which is present in all of this table into another table so let's see how can we do this so this is our employee table over here now we have to copy all of this into another table so for this purpose we can use the insert into select operator now before we go ahead and do that we would also have to create another table which would have the same structure as this table so the table doesn't have the same structure then you wouldn't be able to copy the data so let me go ahead and create the table first so i'll type in create table and i'll name this as employee duplicate and then i'll given all of the values over here so my first column would be e id and this would be of integer type second column would be e name and this would be of varchar type and the length of the string would be 20 and then i would have the e salary over here this would be of integer again after this i would have the e8 which is of integer then i would have the gender of the employee so the gender of the employee would again be of character type now i have to make sure that i given gammas after all of these so let me just put in a comma after this as well and then the final column would be e d e p d and this again would be of variable character of length 20 right so i've successfully created this employee duplicate table select star from employee duplicate right so as of now we see that this table is empty now i'd want to copy all of the table present in the employee table into this table so let me write in the command for that so i will start off by giving the keywords insert into now after that i'll given the name of the table into which i want to insert so the name of the table would be employee duplicate then i will type in select start from and then i would have to given the name of the original table so the name of the original table would be employee right so six rows have been affected now let me have a glance at the new table which would be select star from employee duplicate right so we have successfully copied all of the data which was present in employee table into this employee duplicate table just a quick info guys intellipaat provides sql online training in partnership with microsoft and mentored by industry experts the course link is given in the description below you