SQL Training | SQL Tutorial | Intellipaat

hey guys welcome to the session by intellipaat so we live in an era where data is a most important asset and it is being used at the heart of every decision making process and that's why it becomes very important for us to learn sequel so that we can mine data very easily and also with greater efficiency so keeping this in mind we have come up with an end-to-end sequel developer course so that you can learn all the major concepts of sequel and before we go ahead and start a session do subscribe to our channel and like and share a video so that we can create more such informative content right so that's how quick glance said the agenda we'll start off by understanding what exactly is a database and learn how to create and use a database after that we'll learn how to create a table and then learn how to extract data from it with various clauses and operators going ahead we will learn how to join different tables using different joint statements falling which will work with different inbuilt functions and user-defined functions after that we'll work with stored procedures and transactions in sequel and finally we'll learn how to implement exception handling so what exactly is a database well simply put it's just an organized collection of data that are stored in an electronic format more specifically a database is an electronic system that allows us to easily access manipulate and update the data now the modern databases are managed using something known as a database management system so a database management system or DBMS is basically a system software for creating and managing databases so the DBMS provides users and programmers with a systematic way to create retrieve update and manage data so now that we've understood what exactly is a database and database management system let's have a look at the types of database architecture so the database architecture could either be file server or client server so let's start with the file server architecture so in the file server architecture the files are located on your local system that us their local to you now suppose you have an excel file with you and you send a copy to your friend now if you make any changes in your original excel file those changes would not be reflected in the file with your friend so in the file server architecture the server or the local system acts only as a storage device and only one person at a time can access it so this is the basic premise behind file server architecture now let's look at client-server architecture so we can see that there are three oledb components or in other words they are just client systems or the user systems so we have just shown 300 but they can actually be n number of systems accessing the database at the same time so let's say your login to the IRCTC website and want to know the number of trains running from one place to another place so you'll send a request from this system to the network server now this network server sends the same request to the database over and this is finally forwarded to the database and then the final result is sent back to the user system so similarly if there are three users sending the same request the database server will process all the three requests simultaneously and send back the result to all the three systems so that was about the types of database architecture now let's finally understand what exactly is SQL so SQL basically stands for structured query language and it is a standard language for accessing and manipulating databases or in simple terms SQL helps you to communicate with the database now the SQL commands can be divided into four categories data query language data definition language data manipulation language and data control language so there are data query language comprised of just one command select though it can be accompanied with many clauses to compose queries against a database and DDLS that power of SQL that allows a user to create and restructure database objects so you can see commands like create table all the table and drop table in DDL now for that we have data manipulation language it is used to manipulate the data within tables so the three basic DML commands are insert update and delete and finally we have data control commands which allow us to control access to data within the database so these DCL commands are normally used to create objects related to user access and also control the distribution of privileges among users and you can see commands like grant and revoke in DCL we'll be installing Microsoft sequel server so all you have to do is go to Google and then type Microsoft sequel server download and you'll get these search results now all of these search results click this one where you see sequel server download 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 click on download and the download would start now this is the downloaded file over here sequel's over 2017 SSDI development so we get this window here and we have three options basic custom and download media so I click on custom now we see that we have a media location where the sequel server will be downloaded and I'll click on install to see that the download is successful and our installation will begin shortly here I'll click on installation and I love select this one so I'll be installing a standalone installation of the sequel server so let us read for this to open all right so here I click on next now I only have one instance of sequel server so I'll be installing a new one so I love select us perform a new installation of sequels over 2017 and then I'll select this free edition and 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 have to name the instance for the server which I'm installing so I love meme this over as let's say sparked up similarly I will give the same instance idea over here I click on next so again I'll click on next with you so we see that we have to authentication modes windows authentication mode and mix mode I will select the mixed mode and then enter the desired password let me put in my password over here I like the current user so the current user has been added now I'll click on next again right so you're finally ready to install sequel server I'll click on install right so once the installation is done I will also have to install the sequel server management tools so these sequence of 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 sequel server management studio and I'll be downloading the latest version and this is the downloaded file oh here SSMS set up en you I'll click on this so here we have these three options repair and installing close now I have these three options because I've already installed Microsoft sequencer management studio in my system and if you are 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 sequel server management tools go to the search tool and type Microsoft sequel server management studio click on this Microsoft sequel so management studio will start so over here you'd have to specify the password which you gave while creating the instance and then click on connect and decide writing your SQL commands click on new query over here and then you can happy implement all of your SQL commands right so this was the installation of Microsoft sequel server tables in SQL so simply put the table is a database object which comprises of rows and columns and in SQL the rows are known as records and the columns are known as fields so the fields are basically columns which provides specific information about data and the table for example we have this e salary field in the table which provides information about the salary of different employees similarly we have this e H field which provides information about the age of different employees and recorders basically each individual entry that exists in the table so record gives you complete information of one single entity for example if we take this record over here this would give us complete information of this particular employee so this was all about tables how to create use and draw up 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 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 when let's say I want to name the database as happy right so I will put a 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 sparked up 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 select it remains default until the end of a session or execution of another use statement with some of the 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 happy I'll press semicolon I'll hit execute so again commands are being 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 Datatbase and then specify the name of the database which you want to drop so now I'd want to drop this igneous database so let me write the command for that drop DITA peace and then the name of the databases igneous soil Priscilla : over here I'll it executes 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 the data types in SQL so what data type basically defines the kind of data that I will go into a particular column now it needs to be kept in mind that all the entries of one particular column but are the same data type so let's take this e salary column for example we see that the first entry is of integer type the second entry is also of integer type similarly all the entries of this a salary column are of integer type now let's take this department column here we see that all the entries of this particular column are of character type now that we wonder should what exactly are data types let's have a look at the different datatypes available in SQL let's start with the numerical data types so we have the big in data type which helps us to store really really big values so you can see the range of big endow here then we have the nth data type which helps us to store reasonably big values after that we have the small integer type and the range of small end is from minus 32,768 to 32,767 then the tiny end and the range is only between 0 to 255 after that we have the decimal data type and it is used to store fractional value so the decimal datatype takes in two arguments first is the size of the value that is the total number of digits then we need to specify the number of digits after the decimal point let's say we want to store the decimal value 12.50 here the size would be 4 because the total number of digits are 4 and value of P would be 2 because there are 2 digits after the decimal point now let's have a look at the character data types so first we have the card data type this takes in one argument which is the size of the value let's say you say the size to be 20 this would mean you cannot give a no value which will have more than 20 characters now you need to keep in mind that care is a fixed length data type notice if you said the size to be 30 but you give it only 3 characters then the memory which would be consumed is of 30 characters so you're basically feasting the memory over here then we have the Warka datatype the second peaks in size as the argument now our car is a variable length data type unlike car so here if you set the size to be 30 and given only 3 characters the memory consumed would be only 3 then we have the text data type so this data byte can take in a string with a maximum length of 65535 characters then we have the date and time data types well start with the date datatype so this data type helps us to specify the date in this format over you let's say we want to store the date 2nd January 2019 then first we will give in the year which would be due to 0 1 9 then will specify the month which would be 0 1 and finally we'll specify the do just 0 2 after that we have the word time datatype and this helps us to specify the time represented in the format over here now let's say I want to store the time 8:30 a.m. 23 seconds so first I'll specify the R which would be 0 8 then I'll specify the minutes which is 3 0 and finally specify the seconds which would be 2 3 then we have the year data type we just holds the Year values which are 1995 or 2011 so constraints are basically used to specify some rules on the data and limit the type of data that can go into a table they will look at these four constraints not nulll default unique and primary key let's start with an not null constraint so by default a column can hold null values and with the help of an ordinal constraint you 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 each 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 normal 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 create a table in SQL so we'll be creating an employ table which would comprise of employee ID employee name the employee salary age in gender and the department in which the employ works so let's have a look at the steps to create a table in SQL so first we'll have to start off by giving a name to the table after naming the table and have to define the columns which would be a part of a table and finally we'll have to assign a datatype to each of the column which we have defined now let's have a look at the create tables index so first you'd have to specify the two keywords which are create table and then they'll give us space and then give a name to the table and inside the paranthesis 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 already learned in a previous session a primary key basically helps us to uniquely identify all of the records in a table and guys also keep this in mind but table cannot have more than one primary key so now that we've understood the syntax of create table command let's go to ms equal server and create our employee table right so this is I'm a sequel server so I'll start with the command I will give in the keywords create table then I will give in the name of the table which would be employee and inside the paranthesis 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 now after this I will also put in the not null constraint but this not null constraint means that all of the cells in this column should definitely have a value and then I will give in the second column so the second column is employee name so e name and this is of type worker so Warka stands for variable length character and the maximum length would be 20 then I'll give in 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 each so the column name would be e H and this is of type integer 2 then we have the employees gender so I will name the column to be e gender and this would be of type var 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 de PT and this would be of type var car - 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 paranthesis I will give an e ID right now I will put in semicolon over here to indicate that this is the end of the syntax I will select all of this and click on execute right so we are successfully created the employ table how do you insert records into a table we can insert records for this insert into statement now let's understand this command properly so first would have to type in the keywords insert into then well given the table name following which will give in the keyword values and inside the parentheses we'd have to give in the values for each of the column respectively now let's go to mi sequel server and insert records into our employee table so let me start with the command so I will type in insert into and I'd have to give in the name of the table which would be employed after which I will given the keyword values and inside the paranthesis I can give in all of the records so let me start with the first record right so we'll have to start off with the employ ID so the employee ID is let's say 1 then the second column is the employee's name and this is of type who are care that is why I will given the value inside the single quotes so the employees name is Sam after this we would have to give in the employee salary so Sam earns $95,000 per annum I will give in 95,000 over you then we have the employees age so Sam's age 45 after which we have the employees gender again since this is a variable character so I will use the single quotes over here and Sam has mail 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 give in single quotes and Sam works in operations so let me type in operations over here 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 a table now let me follow the same procedure and insert another record so insert in to employee values and inside the parentheses 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 employees Bob and Bob burns $80,000 which is his salary so let me put in a thousand over here then we have the employees age so Bob's age is 21 then we have the gender of the employee so Bob is mail obviously again then we have to give in the department in missing 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 in the second record now let me insert the third record so again it would be the same man insert into employee values inside the brackets let me give all of the values for the columns so my ID is three the name is an and and salary as one hundred and twenty five thousand dollars per annum she's quite rich wasn'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 an works in analytics let me type in and let Xavier so it seems like people who work in analytics and 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 regards into our table so guys this is how we can insert records into a table how to select data from a table so first we will go up with a 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 a table so let's have a look at the syntax so first we'd give the keyword select and then list on all of the column names that we want to select following which will give the keyword from and then finally give the table name from each 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 give in the name of the column for 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 to purchase employ 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 each column from the employee table we'd have to put in a similar syntax so it would be select employee each from and the name of the table is employed execute right so we have extracted the each column from the entire employee table now we can also extract multiple columns from a table so for that we'd have to separate each of the columns with a comma so let's say I'd 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 a 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 employ'd semicolon and let me hit execute right so I have extracted three columns from the employee table and those three columns are e named 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'll 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 will 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 given the table name so this is how you can extract all of the data for a particular table so I will give in semicolon and let me hit execute right so when I give the command select star from employee I was able to extract all of the data from the employee table now let's look at the select distinct statement so 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 deston comes in so the syntax of select and select distinct is pretty much the same the only differences 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 two values I'd want so the column name would be each Ender and then will be from the name of the table were just employee so let's see what do we get select distinct a 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 you get all of the values so male male female female male and female now so if you use a distinct keyword we'll only get the values which are different so these are the cases where select Destin can be used so the where class is used to extract only those records that fulfill a specified condition for example we want to extract those records where the age of the person is more than 60 or filter out those records were the occupation of the person is doctor now let's have a look at the syntax of a 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 falling which will use the from keyword and give the table name and finally use the where keyword and fill it up with a condition that will 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 the female employees of a company so for this I'll 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 star from I will given the table name which is employee and then I will use the where keyword and fall it up with the condition so the condition would be e gender is 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 texaco it so Ann 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 filler select star from employee who are I will give in the condition so this time the condition would be II each is less than 30 right so each needs to be less than 30 I'll give a semicolon let me it executes right so these are the three employees or these are three records who are the value of e age is less than 30 so Bob and Ann Jeff these are three employees whose age is less than 30 now similarly let's say I want to find out all of those employees whose salary is greater than $100,000 so let me write the command for that so this time the command would be select star from employee were so the condition would be e salary is greater than hundred thousand semicolon and executors so an Matt and Jeff are the three employees whose salary is greater than hundred thousand dollars so and salary is one hundred and twenty five thousand dollars Matt's salary is hundred fifty nine thousand dollars and Jeff salary $112,000 so guys this is how we can filter out regards to the where clause how do you extract records on the basis of multiple conditions using the and or and not operators so we'll start with the and operator first then we'll filter record for the or operator and finally we'll work with the not operator so with the and operator we can basically fits those records which satisfy all of the conditions separated by and for example you want to find out all the doctors whose ear is greater than 60 so there are two conditions over here first the age needs to be greater than sixty second the occupation needs to be doctor 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 fair clause we follow it up with and operator and give the second condition and similarly if we have another condition to be satisfied will again give the and operator and then give the next condition so let's read some records using the and operator so I want to extract all of those employees whose gender is male and age is less than thirty so the command would be select star from employee I'll use the where clause and then given the first condition the first conditioners gender needs to be equal to me so a gender is equal to meal 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 it executes 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 who 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 to the first condition is the department needs to be equal to operations I use the and operator again and then I'll give a second condition so the second condition is the salary needs to be greater than $100,000 right execute so Jeff is the only employee who works in operations and the 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 they can use the or operator and this is the syntax for the or operator so similar to an operator after we give the first condition we follow it up with our operator and then give the second condition now we'll fit some records from the employee table using the or operator so I don't want to extract all of those records whether employee works either in operations or in analytics so the command would be select star from employee where need to get the first condition so the first conditioners Department should be equal to operations I use the or operator now and then give the second conditions or the second conditioners the department needs to be equal to analytics right so we have poor 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 who work in operations and two people work in analytics now similarly I'd want to extract those records who are either the salary is greater than hundred thousand dollars or the age is greater than thirty so this would be the command select star from employee salary is greater than $100,000 I'll have to use the or operator and then give the second condition so the second condition is th needs to be greater than 30 execute so again we have food records over here so the first record as Sam who's the age is greater than 45 then we have earn who's salary is greater than $100,000 then we have matt which satisfy both the conditions actually his salary is greater than hundred thousand dollars and his age is also greater than thirty then we have Jeff whose salary is greater than hundred thousand dollars so these full 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 who are the conditioners not true for example if I wanted to extract all those records where the occupation of the position 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 work loss 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 work 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 to see that I get four records in total and all of those four records the gender value is male now similarly I will extract all of those records were the age is not less than 30 right so select star from boy I need to follow it up with an not operator and then give the condition which would be e of each person 30 so this is the actual condition your phages less than 30 and since I proceeded with the not operator I will get all of the records where the age is actually not less than 30 I'll 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 we'd want to extract all those records where the person's name includes John so if you 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 burden present in them now the like operator is used in conjunction with the wildcard characters and there are two I got characters first one is the percentage symbol and the second one is the underscore character so the person did simpler substitution for zero one or more characters while the percentage symbol a substitution for a single character and this is a syntax well like operator after giving the where clause be given the column name followed by the like operator and then the pattern which you're extract and keep us in mind guys the pattern should be inside single quotes now let's do some operations for the like operator so let's say I want to extract all of those records where the employees name starts with the letter J so the command would be select star from employee well I will given the condition which would be e name calling 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 with start with G would be extracted with this command select minute execute so Julia and Jeff are the two employees whose name starts with chief now similarly I'd want to extract all of those employees whose age is in the thirties right select star from employee who are I need to get the condition so e each polling which I'll give the like operator and then inside single quotes each 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 Julia speech as 30 and Matt's the ages 33 so the other two employees whose ages in the 30s now let's look at the between operator so the between operator is used to select value is between a 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 where clause we given the name of the column then we kept the between keyword after which we given the lower value of the reach 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 age should be between 25 and 35 execute so and Julia Matt and Jeff are the food employees whose age is between 25 and 35 so as you see over here this value is inclusive right 25 is inclusive so an see each verse 25 Julius II just 30 mats these is 33 and Jeff sees is 27 and these are the four employees whose age is between 25 and 35 now similarly I had want to extract all of those employees whose salary is between 90 thousand dollars and 120 thousand dollars so the command would be select star from need to give him 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 he salary between so the salary needs to be between 90 thousand dollars and 120 thousand dollars Alfre semicolon let me hit execute so Sam and Jeff are the two employees whose salaries between 90 thousand dollars and 120 thousand dollars so Sam salary is $95,000 and Jeff salary is 112 thousand dollars some basic functions in SQL so we'll look at the main function max function found function sum function and average function let's start with the main function the main function gives the smallest value in a column let's look at the syntax first we giving the Select keyword then we give the main function and inside the parentheses we specify the name of that column from which we're supposed to find the minimum value so I want to extract the age of the youngest employee of the company so this would be the command flat sell it and then I'll use the min function and then pass in the column which would be e age I'll use the from keyword and then given the name of the table which would be employee so the total command us select minimum of e 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 age it would be e salary from employee so the salary of the employee who owns the least is 73 thousand dollars then we have the max function the max function gives the largest value in a column and 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'd want to extract the maximum region 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 each and then I use the from keyword and then given the name of the table which is employed 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 one hundred and fifty nine thousand dollars and there's a count function so the count function returns the number of rules that match a specified criteria that is a fee 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 give it an asterisk inside the parentheses 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 silly I will use the common 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 give in the condition so I'd want the count of all of the male employees so II of gender should be equal to male let me see what 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 but each 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 their sum function so the sum function would give you the total sum of a numeric column and this is a syntax so here similarly we will give in the sum function after the select keyword and give the column name inside the parenthesis so I want to 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 will given the average function after the cell occurred and give the column name inside the parentheses 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 age from the name of the table which is employed 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 blanks pieces 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 blank spaces on the left side while 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 spot ha I'll hit execute right so this is the string which has some leading spaces on the left side if I want to remove all of these blank spaces on the left side all I need to do is use the L trim function so I will type select L trim and inside this I will pass in the string with the leading spaces so I will copy this and I will piece 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 will remove then we have the lower function which basically converts all the characters the lower case so let's look at an example this time let me actually print the string which has all the characters and capitals so this is sparta 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 will 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 this 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 bring this right so here we see that initially all of the characters are in lowercase now after this I will type select a bird and then pass in the string inside the function I will copy this I will paste it over here I will hit execute so this was our initial string and after using the upper function we have converted the string into capital case now 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 brandon string again select and I will type I love Thai screen 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 substring or bar of the string from the original string now let me give in the original string first so I will type select and then the string would be this is fada now let's say I would want the substring spider from this entire string so this is where we can use the substring function so I will type select substring 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 & 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 & 6 so I will pass in 6 over here now let me excute all of this and let's see what we get so this is the original string this is para 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 all Byers used to solve the data in ascending or descending order and by default order by source the records in ascending order so let's have a look at the syntax will start with Select keyword and then given the column list then we will use the from keyword and get the tea we'll name column which will type the keywords order by and give the columns with respect to which should sort the data and finally we'll state whether the ordering should be in ascending or descending and I've already told you guys but by default the sorting is done in ascending order and if you want to sorting to be done in descending order we will use the de SC keyword so I were to sort the table with respect to the salary column in ascending order so this would be the command for that select star from the name of the table is employee after which I will use the keywords order by and then give him 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 Anne or 73,000 80,000 95,000 and goes on till 159,000 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 des see let me hit execute right so this time the table is sorted in descending order right so we're here at the top we have the maximum salary which is hundred and fifty nine thousand and at the bottom we have the lowest salary which is 73 thousand now let's look at the top class the door closes used to fetch the top end 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 were seen a 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 it 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 dog the star so I've used the top class to fetch the dog three records now this would be from the employee people 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 it should be at the top now let me hit execute so I have 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 order by Clause in the dock cloth with a group by statement the group by statement is used to get a great result with respect to a group for example let's say we wanted to find out the average salary with respect to gender that US average salary of male employee separately and average salary of female employees separately this is where we can use group I 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'd 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 simplex for that select and since I won 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 in each Ender 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 a salary and each enter from employee group I each end up 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 ninety nine thousand dollars per annum and the average salary of male employees is around one hundred and eleven thousand dollars 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 age so this is the syntax select since I want average I will use the average aggregate function and then pass in the column which should be e age and I would also want to have a glance at the department's e DEP D I will given the name of the table which should be employed after which I will give in the group by statement and I want to group this with respect to the department so group by EB EB B 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 agency 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 e age 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 each 21 and this is how we can use a group by clause we have in clauses use in conjunction with 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 crude by clause and must also precede the order by clause effused so let's go to sequel server and implement 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 on all of the columns which I want from this employee table so I need the department column I will also need the average salary so I'll use the aggregate function AVG and then pass in the column were just a 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 purchase employee after this I'll give the group by keyword to groove this table with respect to the department column so I will given the name of the column which is ete pp now before I go ahead and use the having Clause let me actually execute just these three lines of code right so what are 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 salary 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 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 and operations department is one hundred and three thousand dollars and the average salary in the sales department one hundred and fifty nine thousand dollars this is how we can work with harbin 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 will it 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 will update and finally we'll use the where Clause to given the condition which should determine why the columns are to be updated so while putting the where Clause over here in square brackets because it is optional now let's update some records or for 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 R so I will give him the update keyword and then given the name of the table which would be employee and then you the set key word and then set the age value to be equal to 42 then you will the where clause and then set the condition to the conditioners the name should be equal to name is equal to some I am updating the value of each from 45 to 42 when you put a semicolon let me hit execute to see that one row has been affected so now let me have a glance at the change table select star employee but so we see that initially the age value was 45 where the name was Sam so the each value has been changed from 45 to 42 now similarly I don't - change the department to take wherever the gender value best female Dallas I already 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 will give in the name of the table and then the set keyword so over here I want to set the department do we equal to that why the gender is equal to female so let me put it 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 so again let me have a glance at the modified table select star from name of the table is employed 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 all to have the same value which is $50,000 so this command would be quite simple ugly and then the name of the label which is employed and then I'd have to set the salary we equal to 50,000 that's it let me execute so we see that six rows have been affected again let me have a glance in the modified table select star from the name of the table list employee execute again to see that the salary has been right so we see that the salary has been updated so every employee salary is same bill is $50,000 per annum and this is how we can work with the updates T 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 well start with the delete keyword and then put in the key word from then we specify the name of a table after that we will specify the condition well 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 give in the delete keyword and then I'll type from and given the name of the table which would be employed 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 a semicolon over here and I'll hit execute to see that one row has been affected now let me have a glance at the modified people so I will type in select star from employee execute again right so see that dot record with age was equal to 33 has been removed so initially you had six records now we only have fire records in our table now similarly I don't delete that record where the imply name is equal to Sam so let me write the command slot so I will give in 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 Sam right execute again we have one group which has been affected select star 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 trunk a statement deletes all of the data inside the table so here it will given the key words 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 while type crunky tabled and then i'll given the name of the table which is employing execute so see that commands completed successfully now I will type select start from no 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 commanders even though it removes all of the records from the table the structure of the table remains intact so we see that the structure of this table over here employee table it is still intact but 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 which is the employed table which comprises of employee ID employ named employ 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 and up matching values and both the tables so let's say we have two tables table E and they will be and when we apply inner join on these two tables they'll get only those records which are common to both table E and T will be so let's have a look at the syntax well you give the Select keyword first and then the list of the columns by language we'll 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 would be values from column X of table one should be equal to the values of column bi from the table to 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 employ table select star from employee all right so this is that employee table you see that we have this department column over here which tells us about all the departments in which the employees working now similarly let me have a glance of 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 employ 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 edep D column of the employ table so let me go ahead with the syntax of inner join I will give it a select keyword and then given all of the columns which I do want to select so I want to select the a name column and edep D column from the employee table so first I would have to given the name of the table which is employed then I'll put dot over here and then given the name of the column so I want the name column from the employ 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 you and then given the name of the column which is a d e PT 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 you and then get the name of the column vectors the name similarly I'd want to extract the department location column from the department table first I will give in Department over here which is the name of the table I'll put in dot and then I will give in 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 employed then I will given the keywords inner join and given the name of the second table which would be Department right 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 edep d column of the employee table and the values of the d name column of the department table should be equal so employee dot d de VD should be equal to Department dot d name alright 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 a department columns from the employee table and D name and D location columns to the department people let me hit execute right and this is what we get so we have these four columns over you my name employee department department name and department location and since this is inner join we have only those records where the values from edep B have matched to those values of B 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 matched records from the right table so if you take table E and they will be then applying left join on these two tables would give us all the records from DB E and only the matched records from the table B so let's have a look at the syntax well start with the Select keyword and then we get the list of the columns following which will give the from keyword and give the name of the first table then we'll use the keywords left join and then get the name of the second table after which we'll use the keyword on so this on keyword tells SQL now 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 bi 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 presently employed table employee ID imply name employee salary employee employ gender and employee department so I'll be taking this employ Department column from the employ table now similarly let me have a glance of the department table select star from the name of the table does 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 edep P 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 give in the name of the table which is employee then I'll put dot over here and then I will type employee name now I'd also want the employee department column so first I will give in the name of the table which is employee I will put dot over here and then I will give in ETD PT 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 give in the key word 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 were just Department now I'll give the on keyword and then give the condition when 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 the epd these values should be equal to Department dot T named al prosimian over here now let me hit execute alright 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 people 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 so the right joint basically returns all the records from the right table and only the matched records from the left table so if we do P Willie and they will be then applying right join on these two tables would give us all the records from table B and only the match the cause from table a so let's have a look at the syntax well start with the Select keyword first and then given the list of the columns following which where you give the from keyword and then give the name of the first table then we'll use the keywords right join and then give the name of the second table after which we'll use the keyword on so this on keyword tells us QL that this right join needs to be done on some condition and that condition would be values from column X of table one should be equal to values from column bi of table two so before I go ahead and implement the right join let me actually have a glance at the employee table in the department table so I will start with the employee table select star from employee so this is our employee table which comprised of these columns employee ID employee name implies salary employees employed gender and employee Department and will be influenced 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 s Department I'll hit execute so the department table comprises these three columns department ID Department name and department location and will be influenced Rajon 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'm given all of the columns which I want to select so from the employee table I want to select the employee name and employ 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 will select so employing him similarly I also on the employ department column so first I will give in the name of the table which is employee dot and then I'll select the EDP t column and then I want the department name and department location column so the department people so I have to give in 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 employed after that I will type the keywords right showing and then give the name of the second table switches Department then I'll use the on keyword and then give the condition so the condition would be values of edep d column from employee should be equal to values of the name column from the department table so employee dot e te be T should be equal to Department dot d I'll put semicolon over here let me hit execute and this is the result which we get when we apply write join on the employee table and the department table and since this is that I joined we see that we have all the records from the department table and we have only the matched records from the employee table so you see that there is no Content tag 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 be scheme returns all the records from the left table and also all the records from the right table so if we take D by E and they will be then applying full join on these two tables would give us all the records from table E and also all the records from P will be 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 well let's start in the Select keyword first and then given the list of the columns falling which well give the from keyword and then get 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 us QL then this full joint 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 bi from table 2 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 employ 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 imply age imply gender and employee department and we'll be applying the full joint on the employee department column now let me also have a glance in 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 with a full join so I will given the Select keyword first and then I given the list of all of the columns which I want to select so I want to select the employee name and the employ 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 employing him similarly I had also warned the employee Department column so I given the name of the table employee I'll put dot over here and then I'll select the IDI EPP column after that I'd also want the department name and Department location column so the department table so I'll give in the name of the table Department I'll put dart 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 employed then I will use the keywords will join and then given the name of the second table lurches 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 conditioners the values of e d PP column from the employee table should match with the values of the d name column from the department table so that could be employed dot e dep D is equal to Department dot e named Albert semicolon over here let me hit execute so this is the result of a full join on the employee table and the department table so wherever we see null values that means that the records have not marched over here so operations as 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 we join here we have two tables the department table and the employee table now I'd want to update the each value in the employ people wherever the department location is New York so from the department table we can find out that content and analytics departments abused in New York so in the employee table we'll update those values where the 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 ten more to the aged 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 EBP p-value is equal to analytics will be adding ten 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 each value so II each and I'm adding ten 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 stay the join condition so the join conditioners the values of the edep D column from the employee table should be equal to the values of the name column from the department table so employee dot e de PD should be equal to Department dot d name and finally we'll specify the wire 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 will type the name of the table which would be employed 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 an inch Elia is 25 and 30 and after applying the update command we see that the age has been modified to 35 and 40 so Ann'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 well 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 for 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 employed after that I will give the from keyword and then give the name of the first table which would be employee again then I will 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 edep peak column from the employee table should be equal to the values of d name column from the department table so employ dot e the PD should be equal to Department dot the name and finally I'll use the wire condition which would be D location should be equal to New York so I will 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 star 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 at off - almost select statements for example at the first select statement returns these for 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 well can only one of them in the final result Dallas there would be no duplicates in the final result so here we see that this blue fish is present in both the tables E and B but in the final result of a union B well get only one of the value and this is the syntax for the Union operator will 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 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 SIDS name and S marks and has these four records the other table has student details - which has those same columns and comprises all 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 a 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 start from and then the name of the table is student details - 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 Bluefish is present in both the tables a and B the final result of a Union all P 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 let 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 and in 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 keyword let me it execute so 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 except operator combines two select statements and returns unique records from the left query which are not part of the right query for example the first select statement returns these four fish and the second select statement returns these three fish then the exit operator would return us these two fish which will power the first select statement but nor the second select statement and this is the syntax of the except operator will get the first select statement after that we'll give the except 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 except operator on these two tables so we have the student details one table which has the columns si D has named in this marks and as these for records and the other table student details two which has the same columns and comprises all these three records so let's go to SQL server and work with the except operator right so we have our two tables right in front of us so let me type in the syntax I will get the first select query were just select start from and then I will give in the name of the table purchase student details one after that I will use the exit operator and then given the second select statement select star from the name of the table as student details - I'll select all of this again I'll hit execute so this is the result of the except operator so these are the two records which were part of the first select statement but nor the second select statement and this is how we can work with the except operator so the intersect operator helps to combine to select statements and returns only those records which are common to both the select statements and this is the syntax for the intersect operator where you give the first select statement after which will give the intersect operator following which will give 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 the 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 as ID s name and thus marks and has four records the other table s student details 2 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 two table now let me implement intersect operator on these two tables so I will given the keyword select star from and then give the name of the table which would be student details that I will give the intersect operator inter SEC and then give the second select statement select star from student details too right guys so this has pretty much the syntax so you're 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 two table and this is how we can work with the intersect operator so views are virtual tables which I used to limit the information that you wanna 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 will give the keyword as and then give the list of the columns which you will select from the table so let's actually create a view from our employee table so I don't create a view which would comprised 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 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 female employees I'll press semicolon hit execute right so you see that we have successfully created the view from the employee table and in this view we have only two records which comprise of the employees and an Julia and both of them are obviously female now let's see how can we drop of you so the syntax for dropping a view is pretty simple all we have to do is give the keywords drop a few 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 give in the name of the you just female employees I'll press any color execute so again commands have been completed successfully so now let me see if the view exists or not select start 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 alter table 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 will 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 datatype 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 the OB which would stand for employee date of birth and then I'll finally give the datatype of this new column so the data type would be deed so I will put a semicolon I let execute so commands have been completed successfully now let me have a glance at the modified 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 draw up a column from a table again we'll start by giving the keywords all the table and then well give the table name after that we'll give the keywords drop call him and specify the name of the column which is to be dropped so why want to drop this new column which I have just added to this employee table so let me write the command for that so I want to drop this new column eGov which i've just added to this employee table so let me write the command for that aldo alter table and then the name of the table is implied alter table and then the name of the table is employed after that I will give in the keywords drop column and I want to drop the e d OB 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 is you have just added has been dropped so this is how we can work with the alter table statement so the merge statement helps us to perform insert update and delete in just one statement this means we no longer required multiple statements to perform insert update and delete and for the merge statement we required two tables a source table and a target table so the so stable basically contains all the changes which are to be applied to the target table and the target table is third 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 have the rules 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 ask keyword then we'll type using and give the name of the source table and also assign it an alias using the ask keyword again after that we'll type on and then set the join condition then we'll type when matched so if the row is 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 rules 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 when not match by source so here we are checking if there are some rules 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 a target table with the name employee target so using the merge statement we'll be updating the a salary and EH columns in the target table wherever there's a match also insert the records with employee ID 7 and employ 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 sequel 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 employ 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 employ target we'll assign it an alias D similarly well give the employee source table and then assign it an alias as after that we'll type on and then set the join condition so join conditioners t dot e ID is equal to s dot e ID so we basically sent the join condition on the employee ID columns of the souls table and the target table then we have when matched so 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 or 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 each column of the target table to be equal to the values of the each column of the source table and then we have then 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 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 each rows have been affected now let me type the select statement and have a glance at the modified employ target table select star from employee target I'll select this I'll click on execute right so this is a modified employ 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 salary was $95,000 and his age was fortified and after applying the merged statement his salary changed to 93 thousand dollars and his eight change from 45 to 40 similarly an initial salary was 125 thousand dollars and after applying the merge statement her salary changed to hundred and thirty thousand dollars 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 employ target table so this is how we can move to 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 was Kaila value could be in wire card eight and so on and this is the syntax for scalar valued function so we'll type create function and then we'll give the function name and inside the parentheses we'll specify the names of the parameters and their data types and since the function is going to return a value we will type the returns keyword and specify the type of data which will be returned after that we will type us 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 sequel server and create a four scalar valued function so I'm going to create a scalar valued function which will take one integer value add five mol to the original value and return the updated value so let me write the syntax for that create function and then I'll give the function name which would be add Phi and inside the parentheses I'll pass the first parameter so I'll name the parameter to be numb and this would be of India type after that I'll type the returns keyword and then the type of data which we'll 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 five more to the original value so I will 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 at the rate num plus five so what we are basically doing over here is the value which is sent as parameter inside this function we'll take it and add five more to that value and then return it and finally I'll type the end keyword to end this function so let me hit execute right so we have successfully created this add five function now let me go ahead and call this function so to call the function I will type select and then I'll have to type DB o dot and then I'd have to give the function name which is ad Phi now let's say I will 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 hundred as the parameter now I'll select I'll hit execute right so the value which has returned this 105 so this is how we can create a scalar valued function then we have table valued functions so these 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 will type in table because we are supposed to return a table after that we'll give the keywords as return and then give in 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 sequel server and create a force table valued function now I'll be creating a function which would help me to individually extract the male employees and the email 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 star 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 will type create function and then I will give the name of the function which would be select gender now inside the paranthesis I will pass in the parameter so I'll name the parameter to be gender and this would be of worker type so us worker and the length would be 20 after that I will give the returns keyword and since this is a table valued function the return type would be table so I'll type Us return and then given parentheses and inside the parentheses I will give my select statement so the silic statement would go something like this select star from employee when each gender is equal to at the rate gender so this a gender is basically the a 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 if the user passes the value male inside this function then we'll be extracting all of the records for 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 male employees from the employee table so to do that the syntax would be a bit different select star from and then I'll type DBU dot and then give the name of the function which would be select gender and inside the parentheses I will pass in the value and since I want all of the male employees I'll type male 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 male 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 will type DB o dot select gender and then I will pass in the value female I will select us I'll attack Secutor so again we have successfully extracted the female employees from the employ 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 no longer use so the temporary tables could be very useful in cases where ever 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 only in the column as as 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 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 for the temp DB 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 have created so I will 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 value because we haven't inserted any into the stable 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 give the name of the temporary table which would be student I will follow it up with the values keyword and then give the values which are to be inserted inside this so first is a student ID which will be one then I'll give the name of the student so name of the student yes Sam so again I will select this and I 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 key statement helps us in multi way decision-making so let's take this example to understand key statement properly let's say there are dead button starting from 1 to 10 so the person selects one then he wants F selects two then he wants to order coffee similarly if we select three then he wants milk and for any other key is the person below the water so this is basically how the key statement works so the case statement goes through conditions and returns a value when the first condition is met and this is a syntax for the key statement so we'll type in keys falling which will give the one key word and get the first condition after that we will type then and specify what will happen this condition is satisfied and we'll do the same for the rest of the conditions a flood will type else and say - what will happen if none of the above conditions are satisfied finally we will give the end keyword now let's go to sequel server and start working with the key statement so I'll check some conditions is in the key 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 die when and get the first condition which will be then is greater than 20 I like them and state what will happen if this condition is evaluated to true so I'll just print then this we do then then I'll type one and then stay the second condition which would be 10 is less than 20 a lifetime and this condition value is to true I have to print then this less than 20 if none of these conditions evaluate to true titles and just state then this equal to 20 and I'll finish off the key statement with the Panda keyword so let me hit execute so 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 then 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 key 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 grid which should be dependent on the salary column so if the salary is less than $90,000 the grade would be see the salary is between ninety thousand dollars and one hundred and twenty thousand dollars the grade would be P and the salary is greater than 120 thousand dollars the grade would be eight so let me write this in texture 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 key statement so I'll type keys and then type the one keyword and give the first condition so the first condition would be e salary is less than ninety thousand dollars and this evaluates to true the create would be C I'll type one and say the second condition so the second condition would be F e salary is less than one hundred and twenty thousand dollars so here if the first condition is evaluated to false then it would mean the reality is greater than ninety thousand dollars and after that we'll check if the salary is between ninety thousand dollars and one hundred and twenty thousand dollars and if that evaluates to true the create 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 he and I'll finish off this key statement with the end keyword and I want all of this from the employee table so I'll type from and then get the name of the table which would be employed I'll type go I'll select all of this and I'll hit execute right so we see that this new column Creed has been added to the employ people and this grade column is dependent on the salary column so here we see that the salary is between $90,000 and $120,000 that is why the greed is B here the salary is less than $90,000 the grade C and over here the salary is greater than 120,000 dollars that is why the grade is e so this is how we can work with a case statement in C equals over so the F function is an alternate of the keys' expression this F function peeks 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 the boolean expression we are checking if the mark score per student a greater than 50 and if it evaluates to true we'll return bars and if the boolean expression evaluates to false we'll return feel so this is how the if function works now let's implement this F function in sequel server so using the F 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 with odd I'll type select after that I'll use the if function and pass in the first parameter which is the boolean expression so then is greater than 20 and if it evaluates to true I would want to print denis greater than 20 on the other hand if it evaluates to false I would want to print denis 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 want to add a new column employ 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 will 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 employee each column now after this I will use the F function to add the new column so I'll use the first parameter which would be the boolean expression so here I will check if a of age is greater than 30 and if the age is greater than 30 I would want the value to be world employee on the other hand at 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 employ Generation over here and I would want all of this from the table employee now I'll select this execute right so we see that a new column has been added to this and the name of this column is employed generation so here if you have a glance at this each value it is 45 and the corresponding value in employ generation is employed similarly here it is 21 and since the value is less than 30 we get young employee and these two is 25 and 30 and since these two values are also not greater than 30 we have young employ 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 if function in sequel so stored procedure is basically a prepared SQL code which we can see so that the code can be reused 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 a 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 cout keyword now after we create a procedure we need to execute it so this is the syntax to execute the procedure will type e x e C and then give the name of the procedure which is to be executed so let's go to sequel server try and work with some procedures so now I want to create a procedure which would help me to easily access this e H column from the employee table so I'll type create procedure and then I give the name of the procedure which would be employee age I'll type ass and then give the SQL statement which would help me to access this eh column that would be silic e H from the name of the table is employee and then I'll give the co keyword now I'll hit execute right so we see that we have successfully created the employee each procedure now I will use the e^x EC keyword and then call this procedure so the name of the procedure is employee age I'll select this line a live execution ploy each procedure we can easily access the e each column from the employee table now similarly I will create another procedure which would help me to access all of the records from the employee table we ate procedure and I'll name this procedure as employee details I'll follow it up with the as 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 a GU keyword I'll select all of this a little cute so again we have successfully created the employee details procedure now to call this procedure I'll use the exe C command and then pass in the name of the procedure verjus employee details so I'll select this I'll hit execute right so 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 a 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 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 ASCII word 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 will follow it up with the where clause and give the condition so this time the condition would be each end ER as equal to at the rate gender let's understand this properly so here each 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 is female then we'll get all of the female employees and similarly if the value is male then we will get all of the male employees now let me finish off this procedure with the GU 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 EC 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 me 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 give in the name of the procedure which would be employee gender I will give the name of the parameter which would be every 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 palette 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 arrow 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 in SQL with the help of try gas blocks so the dry statement allows us to test a block of statements while the 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 give the key words begin try and then if the statements which are to be tested after that we'll end the try block and begin the catch block inside the catch block will either bring the error or rollback the transaction and we'll end the catch block as well so let's go to sequel server and do some exception handling so I'll be declaring two variables and divide one of the variables with 0 so this needs to give us / 0 exception so let's go ahead and handle this exception now so we can declare a variable SQL by using the declare statement so I'll type declare and then use the enter each symbol to declare a value and I'll name this variable as well 1 and this is of integer type similarly let me go ahead and declare the second variable Y quickly 3 and the name of this variable is value and the data type is integer again now I'll begin the try block I'll die they can try and then inside this I will set the values for both value 1 and value 2 so I'll use a set keyword and then the value which shall be assigning to while one state output semicolon similarly let me assign something to I'll do as well so set the rate why do now inside while do what L basically do is divide but one 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 they again catch and inside the cache block I will just print out the error which occur in the try block and I will display that 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 handled 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 will be doing exception handling on top of the employ table so let me actually have a glance at the employee table first select star from the name of the table is employed 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 vegan try and then I will give the select keyword and I will try to add a salary column with the a name column and I want this from the employee table I will and triblock after this I need to begin the catch block so they get catch now inside this I will print a user-defined message so print and I would want to bring and not the numerical value with the string value I will and the cache block as well after this I will give the go statement let me hit execute this is the message we freakin cannot add a numerical value with the 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 transaction is basically a group of commands will 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 s-so let's say if one of the commands in the transaction fields then all the commands will feel 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 the transaction I will try to update this each 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 sequel statements which either have to be committed or rollback so over here I give the update statement I will type update and then give the name of the table which would be employee said eh to be equal to 30 were renamed as equal to Sam so I'll select these two statements and I'll execute them so we see that one drew 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 see that initially the age value was 45 and after we applied this update command that each has been changed from 45 to 30 now since this command is inside a transaction we have the option to either rollback this command or come at this commander so now let me go ahead and actually rollback this transaction so to do that I'll die rollback transaction so now let me select this I'll click on execute so see that the commands have been completed successfully now let me again have a glance at the employ table right so since we have rule about the transaction we see that the each value has gone back to the original value which is 45 so initially using the update command we had changed that each value from 45 to 30 and a fella who 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 each has been changed to 30 so this time instead of rolling back the transaction I will commit the transaction so I'll type commit transaction so I'll select this issac execute now let me have a glance of the table so this time the transaction has been committed and the change is permanent so this is how we can either commit or rollback 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 me so wherever the gender is equal to mid we 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 0 which would give us an undefined value and after that we'll come at 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 1 95 h-0y the skew says 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 ruled back because we get an error inside the try block now what I'll do is I will remove this divided by zero and run this command again so this time you 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 stick on execute right so we see that we have changed the salary value to be 50 where gender is equal to mate similarly I have changed the salary value to be equal to 195 when the gender is equal to female this is how we can work with transactions in SQL so who was 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 rules of database administrator so would I be a has to take care of the initial installation and configuration of a new Oracle or sequel server database the system administrator sets of hardware and deploys the operating system for the database server then the DBA installs the database software and configures it for use plus updates and paths are required the DBA has to handle this ongoing maintenance a DBA has to also take care of backup and recovery of databases so DBS have to create backup and recovery plans and procedures be used on industry best practices that makes sure the necessary steps are followed another role of DBA is to maintain security of the database so a DBA needs no 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 word to the data the DBA has to also take care of access control the rest he has to verify which users can be granted access to the system and which users 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 a software or add additional hardware capacity so now that you know where are the roles of a DBA let's look at the types of DBA let's start with production DBA so production EBA is responsible for maintaining databases within an organization the production DB 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 DB is basically teeka were after applications have been created and they would have to keep the server running smoothly back it up and plan 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 sequel and would implement the application business logic using sequel or any other query language there 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 and effective usable database environment to support the creation and maintenance of applications development dps need to be skilled in the process of theta modeling and normalization to ensure that databases are designed to promote data integrity then the suity DBA so a UAT database actually stands for under application testing database and the UAT DBA is completely responsible for the testing and development of uet database and finally we have the double 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 I'm a sequel server so Microsoft sequel server is a relational database management system or our DBMS that supports a wide variety of transaction processing business intelligence and analytics applications so Microsoft sequel server is built on top of SQL and a style to transact sequel or D SQL which is an implementation of sequel from Microsoft that adds a set of proprietary programming extensions to the sandal language now let's look at the evolution of sequel server so the sequel server database platform that we know today began with the sequence of a 7.0 release in 1998 so Microsoft react affected the cult database engine code in sequence over 7.2 to address the database scalability issues transforming it from our departmental date these into a true or enterprise level database perhaps even more important Microsoft added olp services which would eventually become sequel server analysis services then the sequence of a 2000 release saw Microsoft continuing to push the platform into enterprise this released boost its scalability through large memory and address windowing extension support the company also expanded sequel service data handling capabilities with a new XML data type and then came sequel server 2005 which was a major milestone Microsoft added CLR integration Plus this was the first release to include the popular sequel server reporting services subsystem of the box Microsoft also had replaced data transformation services with the new sequel server integration services in the series and then sequence over 2008 Microsoft refined sequel service on the price capabilities and in transparent data encryption as well as the file stream and geospatial data types sequel server 2008 was also the first release with built-in row and page compression for tables and indexes then came sequel server 2012 which expanded sequel service bi capabilities with the addition of Power View which is a graphical DITA navigation and visualization tool in the suite Microsoft also added always-on availability groups which extended sequel service high availability and disaster recovery options the next release was sequel 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 and Appling customers to use existing skills with the on-premise version of sequel server to take advantage of Microsoft's global data centers then sequence over 2016 released which added new built-in JSON support in sequel server for JSON imports exports parsing and storing a new poly base query engine was also added to integrate sequencer with external data in Hadoop or is your blob storage and the latest releases sequel server 2017 and with the strees sequel server is no longer just a windows-based our DBMS we can now develop applications with sequel server on Linux Windows Ubuntu or talker and deploy them on these platforms this release also has support for Python so what data scientists can now more easily perform machine learning right inside of sequel server now let's look at the different editions of sequel server so first we have the Enterprise Edition so sequence of Enterprise Edition includes both the cool database engine and add-on services and with the range of tools for creating and managing a sequence of cluster so it can manage databases as large as 524 petabytes and it supports 640 logical processors then we have the standard edition the sequel server standard edition includes the cool database engine along with the standalone services so it differs from the enterprise edition in that it supports fewer active instances letters 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 sequel server which includes the cool database engine while there are no limitations on the number of databases or user supported it is limited to using one processor 1gb memory and 10 GB database files now let's learn about instances so you can consider an instance to be one complete installation of sequel server and each instance manages several system databases and one or more user databases so an instances either the default named instance or it hasn't named instance or 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 the computer can have only one default instance and all the others must be named now let's go ahead and install sequel server into our systems so we can install sequel server from this site over here Microsoft sequel server sequel 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 sequel server so when we install Microsoft sequel server the system databases are automatically created on every sequel server instance these system databases allow the database engine and administrative applications do properly manage the system and these are the available system databases master model ms DB and temp DB so let's start with master database the master database contains all of the system level information for sequel server all of the logins linked servers endpoints and other system-wide configuration settings the master database is also sequel server stores information about the other databases on this instance and the location of the files so if the master database is not present sequel 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 MS DB which is used by sequel server agent for scheduling alerts and jobs so M s DB also holds backup history so using the MSTP tables it's possible to determine when each database and file group was last backed up then we have temp DB which is basically a workspace for holding temporary objects or intermediate result sets one interesting thing about MTB is that it is recreated every time the sequel server service is started and any objects that we've created in MTB will be gone once the server restarts so now I'll head on to the pop quiz we have this first question which of these is an edition of sequel so the answer is obviously all of these so entreprise Edition standard edition and web Edition all of these are different editions of sequel server so next question which of these is not a sequel server system database so guys what do you think what would be the answer let's leave database so master database model database and MD be our system databases but there is no such system database with the name slave database 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 sequel server backup and restore component provides an essential safeguard for protecting critical data stored in our sequel server databases so to minimize the risk of catastrophic dita loss we need to backup our databases to preserve modifications to add eat on a regular basis and this is where a well-planned backup and restore strategy helps in protecting the databases against data loss caused by a variety of failures now it's time to restore and backup the adventure works 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 slot 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 adventureworks folder and this is my DW so vo adventureworks 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 adventure works 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'd created so I will select this I 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 t SQL code for this all I have to do is select the script and this would be the T SQL 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 complete so get this message at the backup of database adventureworks 2014 has been completed successfully right so first you went ahead and restore 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 backup restore and recovery operations are based on one of these three available recovery models which are simple full and bulk load 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 sequence of a maintains only your minimum amount of information in the transaction log and while using the simple recovery model we may restore full or differential backups only there is 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 use 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 has easier to my needs in the full or bulk log models but they would be hired at a 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 then addition to preserving data modification stored 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 pipe clock 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 bio-clock model records these operations in the transaction log using the technique known as minimal logging so this seems 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 weight go let me just execute it so see that commands are being 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 seems the recovery model from simple duffel 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 were using is by flocked now let's see how can we plan a backup strategy so after we have selected a recovery model dot 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'll 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 backs up 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 are stables 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 tours 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 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 and 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 file backup contains all the data in one or more files which are known as five loops then we have partial backup so partial backups allow us to backup only a subset of the data files so these partial vacuums 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 lack of 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 sequence of a backup that is independent of the sequence of conventional sequel server backups usually taking a backup changes the database and affects 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 made a 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 word transaction log backup allows us to backup the active part of the transaction log so after the issue of 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 would a backup I 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 SQL code for this all I have to do is click on script and this is the T SQL code to get the differential backup of this database I will click on so see that the backup of the database has been completed successfully now similarly if I want to hit 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 T SQL code I'll click on script so I'll just click on OK so this time we have a transaction log a cup of the adventureworks 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 Teta pieces that run under the full or bulk cloud recovery model so now let's implement point in time recovery in SSMS for half this adventure box 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 will select timeline and I will set a specific date and time so I want to recover this database from tours 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 at the SQL code for this all I have to do is click on script but for now I'll click on OK so we've successfully got the point-in-time recovery of this database for tours back now we will see how to import and export data in SMS 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 how 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 sequel server Native Client now this will be stored in this happy reader face 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 the state of ease now let me go to this database first which would be happy over here so select star from new file I'll execute right so we have successfully imported the dataset 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 Ann and her ages 3 I'll execute this now let me look at the modified leader that will 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 sequence of Annie declined 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 under store this into new file - and I'll click on next next so over here I'd have to choose the table which I would export so I would 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 we have this pop quiz over here so the first question is which of these is a type of recovery model so guys what do you think well all of these so simple fill and bulk log all of these are types of recovery models the next question which of these is not a type of backup model so we have a full backup differential backup and transaction log backup but there is no backup by the name integral backup so dynamic management views and dynamic management functions or system views and system functions that return meta data of the system state on clearing the related system objects database administrators can understand the internals of sequel server so these dynamic management views allow us to monitor the performance of sequel 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 date B's and all of these DMVs start with the prefix DM underscored now let's look at some categories of DMVs so DM bees can be divided into many different categories and in the session he'll be looking at three of those so we'll be looking at database related DMVs sequence our OS related DMVs and execution related DMVs so let's start with database related DM bees 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 three pages and total free space in megabytes available in all files and temp DB so let me just run this command over here I will click on execute so the total number of free pages available in Tempe B 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 temp TB again I will run this command in SSMS I'll click on execute so see that the total free logs piece is 5.7 4 MB so this is the total fee logs piece in temp TB next we have DB underscore partition stats which returns pH 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 starts for all of the indexes and heaps in the adventureworks 2014 database so here we have different statistics such as index ID partition number the data pH count and so on and over here we have the reserved page count the used pH 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 sequel server who is related DMVs so first we have the DM underscore who is buffered the script which returns information about all the data pages that are currently in the sequence of a buffer pool so with this command over here we'll get the cached beach count for each database again I will run this command in SSMS I'll click on execute so these are the different databases which I have in sequel server and this is the cached page count so in master DB I see that there are 137 cache pages in MSD B there are nine in Spada there are six and then adventureworks there are 304 next we have DM underscore OS threads which returns a list of all sequel server operating system threads that are running under the sequel server process so this query over here is used to find workers along with time used for execution and are running threads which are actually not started by sequel server so this is all of the information about the threads which are not yet started by sequel so so you have different columns we have the voice thread ID in 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 exe C connections which returns information about the connections established to this instance of sequel server and the details of each connection and this command over here is used to gather information about a queries 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 XTC 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 nt service igneous another ese and for the first user of the session count as one and for the second user the session count is fifty so those were some of the dynamic management views now we will work with Activity Monitor so sequence of Activity Monitor is a feature and sequel server management studio that displays information about the sequel server processes and their effect on sequel server performance so the Activity Monitor consists of these expandable pins so we have overview we have processes resource weights data file i/o 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 sequel server instance information so this percent processor time it tells us the percentage of time the processor spent execute threads there are not idle and then we have waiting tasks so this over here gives us the number of tasks that are waiting for processor IU or memory to be released so the tasks can be processed then we have database io 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 sequence of batches received by the instance in a second now let's look at the processor spin so this process pane shows us the information about the currently running processes on the sequel's over databases and who are running those processes so we have 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 si has worked on MTB master DB and this adventureworks DB after that we have the resource reach pin and this provides information with respect to a thread which is waiting for a key resource such as memory CP you or network so these are all of the threads which are waiting for something and then we have the next pin which is data file i/o so this pin shows information about the database files on the sequel server instance so for each database all the database files are listed so we have all of the different databases over your MTB master model and so on and these are different files which are power of this database and then following on we have the recent expensive query spin 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 sequel server that may cause performance problems in sequel 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 be 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 sequence of a profiler I will just give the connection name in the password over here I will connect no again inside the sequence of a profiler I have the stool stabbed 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 pays less bytes so this is memory again and logical disk maybe I want the cash a bites 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 a report so this is the report which I have so we see that available megabytes this is the available megabytes kashibai it's committed limit committed bytes and the free and zero page less bytes over here and we have the person 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 sequel server activity using sequence of a profiler so whenever we are working with really long and complex sequel queries you have to make sure that all of those tasks are performed accurately this is where tracing all these tasks becomes important so sequel server provides or - we'll call that sequel server profiler for exactly this purpose so profiler is a tool for tracing recreating and troubleshooting problems in ms sequel 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 sequels of a profile in SSMS so we are back to SSMS now I'd have to click on tools and then select sequel server profile up again I will give in the password and then establish the connection let me click on connect now I'd have to given you a name to this trees so I will name this as new trees and then maybe I will also save this to a file so let me give in the destination so I'll save this in my C Drive I'll click on save over you now I can also set the trees time so let me schedule it so I want this sequence of profiler to run for at least one R 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'll click on this show all events and let me add some events over here so I want to trace the cursor prepare cursor unprepared option and let me go to the objects tab over here and I want to know whenever an object has altered created or deleted similarly let me go to this performance tab and I want to know about all the 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 and - 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 sequel so profile up so now let's head on to the pop quiz so I have this first question the Activity Monitor and sequel server graphically displays information about what well it displays information about all of these that gives us information about user activity blog processes and also logs so the next question is which database related DMV would you use to get the total number of three pages in temp dB so what do you think is the answer the answer to this is DB underscore file space usage so this is the DMV which we'll use to get the total number of three pages in temp TP 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 are 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 followed defined rules for values range and format and we can enforce these rules using check and default constraints and finally we have user-defined data integrity which can be enforced through triggers so now that we know about data integrity let's learn about indexes in sequel server so indexes are special look-up 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 the database are very similar to indexes at the end of the books whose purpose is to find topic quickly now again that you know what exactly is an index let's look at the types of indexes so when sequel server indexes can be grouped into clustered and non-clustered so clustered index defines the order in which data is physically stored in a table and since table data can be sorted in only one way therefore there can be only one clustered index per table so when sequel 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 in the is stored at one place in people 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 per 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 the staple I'll click on OK now again if I want the t-sql code for it all I have to do is click on script so this is the t-sql code to create a clustered index with the name clustered index 101 and it has 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 will choose the color name column I'll click on OK again if I need the code for this I will click on script so this is the code to create a non-clustered index with the name non-clustered index 101 and thus this 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 when it was sequence of a performs IO operation it initially reads the data from the disk which is known as physical read so it pulls everything into the sequence of a buffer so there is a logical order in this buffer and whenever the query runs again instead of doing physical read sequel server would look into the buffer and if it can't find the requisite dita 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 order 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 file needs to be inserted and since it is a clustered index new row is inserted in order and because the target pages will owe you the new root does not fit and sequel server splits the peat roughy 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 see 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 Petri notice all that existing indexes are dropped and new indexes are recreated while reorganizing index does the process of cleaning organizing and B fragmenting the leaf level of the Petri now we'll go ahead and implement this concept in SSMS so I will take these clustered index and I want to rebuild this clustered index if I want to do that I will right-click on this and I have this option to rebuild I will click on this now I all I have to do is click on ok and again if I need the t-sql code for this all I have to do is click on script and this is the T SQL code do rebuild this clustered index 101 so I will go ahead click on OK and I have successfully rebuild this clustered index now I want to reorganize this non-clustered index this time I will right-click on this select reorganize and 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 says dot TM underscore DB underscored index underscore physical starts to get the fragmentation starts and will be applying this on top of this person dot address table from the adventureworks 2014 database so I'll just B's the code over here and I want these index starts for this table over here person dot address I'll click on execute and these are the fragmentation starts for this table now we'll head on to the pop quiz so we have this question over you how many types of indexes are there in SQL so guys what do you think well the answer is - you have clustered index and the non-clustered index so guys this brings us to the end of the session and do stay tuned - in telepaths YouTube channel for more such intimate videos

Channel: Intellipaat

Views: 1,092,000

Keywords: sql training, sql tutorial, sql for beginners, sql course, Structured Query Language, sql, learn sql, free sql course, mysql tutorial, mysql course, mysql crash course, mysql tutorial for beginners, sql crash course, mysql, sql basics, sql in 1 hour, sql full course, sql basics for beginners, learn sql fast, sql videos, sql database tutorial for beginners, sql beginners, sql tutorial videos, edureka sql training, sql training videos, intellipaat

Id: JTDK6r1GuUU

Length: 188min 6sec (11286 seconds)

Published: Fri Jun 14 2019