SQL Full Course | SQL Tutorial For Beginners | Learn SQL (Structured Query Language) | Edureka

SQL Full Course | SQL Tutorial For Beginners | Learn SQL (Structured Query Language) | Edureka
Spread the love

  Hi Guys.  Welcome to this interesting session on SQL Full Course.  So in this we’ll mainly understand the different concepts  related to SQL.  So without wasting any further time,  Let’s take a look at the topics for today’s session.  So we’ll start today’s session by  understanding the data definitions language commands  in which we’ll understand  what is relational database management system.  How to normalize your database management systems,  how to create, delete and alter database objects.  And also get an understanding  about the different types of constraints  present in database such as create, alter and delete.  Once you understand the data definition language commands,  the next topic would be the data manipulation commands.  So in the data manipulation language commands  would basically understand how to enter and update the data  in existing tables using SQL commands.  Apart from that,  you will also understand how to delete  data from single tables,  and also how to fetch and show the data from databases  using various kinds of commands.  And finally we’ll end this topic with  the SQL operators such as comparison, logical, and so on  to get an understanding  of how you can play around with your databases.  After you get an understanding about  the data manipulation commands.  We’ll the next get into how to retrieve data  from multiple tables.  So in this part of the session,  you’ll basically understand the different  types of joints, present in SQLs,  that is the inner left, right and cross.  And after you understand the joints,  the next topic would be the inbuilt  functions in SQL.  So in this topic,  we’ll basically look into how to use  the built in functions and SQL  and also understand what is group by clause  and having clause.  And finally, we’ll end the session by understanding  how to create advanced database objects  in which we’ll mainly focus on the store procedures,  functions, and triggers.  So I hope the agenda was clear to you guys.  So today we have a special guest, Anil,  who’s gonna take the session forward.  So, over to you Anil.  Hello guys, this is Anil from Edureka,  and welcome to SQL Full Course.  So let’s start with what is RDBMS.  So as you can see on the screen,  RDBMS basically stands for,  relation database management system.  So basically in RDBMS we’ll define the data,  or we store the data in to collection of tables,  which is a two dimensional database.  So as I mentioned,  it’s stored the data in tables  and the tables have rows and columns.  So some of the properties of relational database,  as you can see in the screens,  it’s Values are Atomic,  Column Values are of the same thing.  Columns are undistinguished.  Sequence of row,  how the data is stored is of insignificance.  They can store the data in any sequence  and all the columns have a unique name.  Most of the database which we know as  of the like Oracle, MySQL,  all are relation database management system,  and SQL is the common query language  for all database management systems.  As we discussed that RDBMS is basically  we store data into collection of tables.  So then we store the data  and collection of tables,  it is very important that data is organized properly.  So we applied the concept  of normalization for the better management of DBMS.  As you can see,  normalization is a process of organizing data  to avoid duplications and redundancy.  So we apply normalization to database systems  to minimize duplicate data,  data modification issues to simplify queries  because we don’t want to run into complex queries  when we fetch the information from database.  If our database is not properly normalized,  then we may face issues when we try to update  any records in the database.  There are various rules for normalizations,  but generally we follow third normal form  in order to organize our data efficiently.  So we have first NF that is first normal form,  second normal form, third normal form  and the end we have Boyce and Codd Normal Forms,  But it is enough and it’s sufficient  if we satisfied up to third normal form.  Okay, so what is first normal form?  So as for the first normal form,  as you can see on the screen  that each set of column must have a unique value.  It means every column should have  a single value in it.  On the screen as you can see in the left hand side  we have our students table  and this table has three columns,  student, age and course.  But in the first row as you are seeing on screen  that we have two values for a single column:  CR001 and CR005.  CR001 and CR005.  Because of the multiple values in a single column,  it is violating first normal form.  So in order to satisfy first normal form,  we have to split the row into multiple rows.  So after I splitting in the right hand side  you can see that we have split the row first into two rows,  Adam 15 and the course is CR001.  Adam 15 and the course is CR001,  and another ways, Adam 15 CR005.  So we have split the multiple values into single values  and now our table is satisfying the first normal form.  Let’s move to the second normal form,  as per the second normal form,  there must not be any partial dependency  of any column on primary key.  So first of all, what is primary key?  So primary key is a key which helps us  in identifying a unique row in a table.  Most of the time,  the primary key is a single column,  but sometimes more than one column  can be combined to create a single primary key.  Now as you can see on the screen,  our table was not satisfying the first normal form.  So we have split into two rows.  So in this table we cannot consider  student as a primary key  because as I mentioned that the very first prerequisite  of a primary key is that no two row  can have the same value for that key.  Here in the first column strength,  we have Adam as two entries of Adam.  So a student is not a primary key.  So in order to make a primary key,  sometimes big continent,  or add one more column to our primary key.  So in our case we have student and goes as a primary key.  So our primary key generally contains a single column,  but sometimes aspect of business requirement.  We may add multiple columns in the primary key.  So in this table, student and course  together have a primary key.  Now, second normal form states that in order for a table  to be in satisfy the second normal form,  it first satisfy the first normal form.  And after that all the columns should  depends only on the primary key  and they should not partially dependent on the primary key.  So as of now,  and you can see in this table that column age,  only depend on the student and not on the course.  So age is partially dependent on the primary key  because our primary key has student and goes in it.  So that is the reason  it is not satisfying second normal form.  So in order to make it satisfy the second normal form,  we have split the table into two tables.  Now the first table we have student as a one column  and age as another column.  So age is now dependent on the student  as well as in the second table  we have student and course.  So the course is also dependent on the student.  So now the columns are not partially dependent on primary.  So in order to satisfy the second normal form,  our tables should first satisfy the first normal form  as we can see that no columns has a multiple values in it  so it is satisfying first normal form.  And the second normal form states that any non-key columns  should not partially dependent on primary key.  So in this age is dependent on student  as well as schools is dependent on student.  So our table is satisfying the second normal form.  Let’s move to third normal form.  third normal forms applies that every non-prime attribute  of a table must be dependent on primary key.  Or we can say that there should not  be a case that a non-prime attribute is determined  by another non-prime attribute .  So let’s take a example here.  So this example will help us understand  what the statement means.  So we have a student table here and these are the columns:  student id, student name, date of birth,  street, city, state and zip.  So we have seven columns in our student table.  And in this table their student id is the primary key.  So a third normal forms states that all columns  should be dependent on the primary key only.  So if you’ll see street, city and state,  street, city and state,  these three columns are basically dependent on the zip,  zip code of that area  and not on that student.  So that is the reason it is not satisfying  the third normal form.  So in order to make it  satisfy the third normal form,  we have again split the table into two tables.  So one table we have student id,  student name, date of birth and zip.  And the address table we have zip street, city and street.  So this is how guys we design our database.  So it is very important when you design your database,  when you decide that,  okay these are the tables which we are going to create.  So you need to first apply all the normal forms.  So database will be more efficient,  it will allow you to remove any redundant data  or duplicate data from our database.  So I hope the first, second  and third normal forms are clear.  And the important part here guys is  that the third normal form, for example,  any normal form should satisfy the previous normal form.  It cannot be the case that  my table is satisfying the third normal form,  but it will fail the first normal form.  So second normal form,  it has to be satisfied the first normal form  and the third normal form,  it has to satisfy the second normal form.  After we know the best practices to design our database,  we’ll discuss about the Data Types in SQL.  When you store the data in our database,  we know that what kind of information you are storing.  It can be integer value,  it can be strings or characters  and we can store the salary, it can be monitoring data.  For example, money or the currencies.  We can store the data in terms of date and time  when some action has happened or when an order  is made or when we purchased certain items,  we can store the binary string.  For example, we can store images  or we can store pictures in our databases,  so the store in the binary form and so on.  So basically we would define that there are  four broad categories of data types in SQL.  What the the character string, numeric values,  date and time, and binary.  So let’s discuss about the character string data type.  So we have these six type of string data types.  One is char(n) and and then we have nchar(n),  varchar(n), varchar(max) and nvarchar(n) and nvarchar(max).  So these slides are self explanatory  and it mentioned that it basically depends upon  what is the size of data we can store for these data types.  Apart from this guys,  we have this varchar, nchar.  So var basically stand for variable length character.  And this n stands for Unicode character.  So if you want to store the data,  for example, all non-English data,  I want to store some Japanese languages  or in some Arabic keywords,  then we basically use nchar or nvarchar,  basically stores for Unicode characters.  Character is a static, for example,  if I defined character 20  and I’m trying to store our values of five corrector  then in order to match that defined data type  the right side of my data type,  will be embedded with the blank spaces.  So we decide basically whether we want  a variable character or the static character.  We have numeric data type, so numeric data type  we have eight tiny, small and big.  And so there’s this tiny small, big,  and it all depends upon the size of the data  we want to store in our database.  So as for our business requirement,  how small or the big data we want to student database,  we decide our data type.  If you want to store the data in terms of currencies  that we have money.  Again money based upon what is the size  of a new data we want to store in money data type.  We have money in small money,  we have decimal in the decimal,  as you can see we have two parameters, P and S.  Which basically stands for precision and scale.  So the maximum precision is 38 digits.  Again, we have numeric float and real.  So these data if you will see,  are basically more in line with the database  of the basic language like cc plus, plus  we have all these data types.  Data and time is one of the most important data type guys.  So we have different format of data types available  for this section, we have date.  So as you can see the date data types store states  between Jan 1, 001 and December 31st, 999.  between Jan 1, 001 and December 31st, 999.  And its length is basically have three bytes  and if you want to store time along with date  then we have datetime.  So in the datetime also we have two different date.  One is date and one datetime two.  Also depends upon whether we want to store the time  in milliseconds, or you want to include  the nanosecond part also.  We have the small datetime  and if you want to store only the time component,  not the date part, then we can use the data type time.  And in the end we have binary data type.  So we have, as we discussed,  we have with store a single bit of data.  For example, if you want to have true and false  zero and one we have binary stores and bytes of binary data.  We have varbinary, varbinary(max),  so we generally use varbinary(max) when we try to save data.  For example, some images and pictures  where we don’t know about the site.  It can change dynamically run time of big data,  so we generally use varbinary.  So I hope you are clear with the basic data types.  So so far we have discussed what RDBMS is,  how we design a better database management systems  by applying the rules of normalization.  We discussed three normalization forms,  first NF and second NF and three NF  and then we discuss the order that various data types  which we can use to store our data in database system.  After that we’ll discuss about the various SQL statements.  So generally in SQL we write queries.  So our queries falls into four broad categories.  One is data definition language.  One is data manipulation language.  We have data control language  and we have transaction control language.  So data definition language  that we are going to discuss in the coming slides  and we’ll discuss about the schema of our database  that how we create the database object,  how we modify our database objects.  And if not required, how we can drop  or delete those database objects.  In data manipulation language we do not  manipulate the database schema.  We basically manipulate that data which is stored  in our database objects.  So these are the four basic commands as you can see,  we have select commands,  we have insert, update and delete commands.  So we modify the data, we tried the data,  we update the data and if not required we delete the data.  So the basic difference between deleting  the database objects and data  is that when we delete the database  is there just we are deleting that data insight object.  And then we have data control language.  So include command such as grant,  mostly concerned with rights and permissions.  For example, a new member has joined in your team  and you want to give the permission so that  he can play around with your database.  Then you have to grant some permission to the new user.  If someone is leaving your team or leaving your projects.  In that case you no longer want him to the database.  So in that case,  you’re gonna revoke the permissions  with a person having on the database.  So we use grant and revoke commands,  and this falls on the data control language.  Transaction control language is basically  when we commit or the rollback the data.  When we are savings the data  and we might be constantly saving the data  in multiple tables.  And while saving the data,  if there is an error in any one of the table,  we basically rollback the entire data.  So we use rollback or commit commands to save  or roll back all the changes which you made to the database.  So this falls on the transaction control language.  So as discussed in this module,  we’ll be discussing more about  the data definition language,  where we discuss about the schema of our database.  So in order to start with,  we have to first create our database  and in the database again,  then we’ll create multiple different  database objects like functions, store procedures, tables.  So to start with the very first step  is that we need to create a database object.  So in order to understand database,  database is nothing but it is an organized collection  of the data.  When I say organize the data is stored in multiple tables.  The tables are related to each other  based upon the various business conditions.  So these business requirements  and the main purpose of databases  basically to operate large amount  of information’s by storing, by retrieving and manipulating  and managing the data.  So I’ll show you how to create a new database.  So as I mentioned earlier,  that after installation of Microsoft SQL server,  we have in order to access the server,  they need to open Microsoft SQL Server Management Studio.  So if you’ll search for SSMS  that is SQL server management studio.  So this is how the I can look like and you have to open it.  Once you open it,  the screen will look like this.  So on the left hand side you have object explorer  and these are the various tool box.  So you can go through these toolboxes as a self-explanatory.  So in order to create new database  we have to click on new query  and it is a straight forward command to create a database.  Let me increase the font size for better visibility.  So we have to just write create database  and the name of the database.  So by default, as you can see,  these are the databases here.  These are the custom in which we have created,  Eduraka, empTB.  These were the system database.  And once we install Microsoft SQL server,  this database will be presented by default.  So that command is create then database  and the name of the database.  So all you have to do is,  and the name of the database.  For example, I’m creating test one.  So to execute this command,  either you can select this query,  and then can press F5 audit after selection,  you can click on execute button here.  So if there’s no error while executing the command,  you will get this message that  command completed successfully.  And in order to check whether the database  has been created successfully or not,  we can go ahead and refresh our database.  So in order to refresh it,  you have to right click the databases  and just click refresh here.  As you can see that test one database has been created.  You can expand this and you can see by default  in any database you will see  these categories will be present.  But under tables, these are the system tables  because we have not created anything custom as of now.  So whatever is present and the test one database  as of now is out of box.  So creation of database is first step  to manage the entire database objects  or to start playing with the data.  After creation of a database  we need to create some tables in it  because table is where we store our data.  And all other database objects are basically  based on SQL tables.  As you can see on the screen,  the basic syntax to create the table is, create table.  Then we have table name,  what is the name you want to give it to our table.  Then we define what the columns we required in our tables  and followed by the data type.  We’ve already discussed what are the various data types  present in SQL.  So based upon our business requirement,  based on our requirement we defined kind of data type  a column should have.  So let me execute this command here.  So let’s, let’s create our table  and see how we can create the table.  So in order to save time,  let me crate one table for you guys.  So we can delete this line  or we can create a new query window.  Let me again increase the font size.  So as, as you can see,  we have to follow, this syntax is very important.  And the best part is that it is not case sensitive.  It is not that I have to give everything in cap.  I can provide this small bit font size  or it is not case sensitive.  So we have provide create table and name of your table,  it can be any name.  So as of now it’s giving the test table  and these are the columns which I’m defining in my table.  Employee id, INT primary key.  We’ll discuss about what are the keys  in the very end of this module,  we’ll discuss more of the various constant,  how we can create a primary key,  what is a use of it, what is foreign key.  So we create when columns as of now  I’m not making it primary key  and just defining the data type.  So for employee id I’m defining the data as INT,  then I’m defining the last name.  So let’s make it more readable  and defining it as a last name in one column  and first name as another column.  And I’m defining the data type as varchar  and I’m defining it’s not null  that it is required to have a value in this.  So if we want that certain column are mandatory,  while we’re inserting the data,  then we have to make them not null.  By default it is null.  So we have to explicitly write this keyword, not null,  if we want to make those column mandatory.  Semi column in the end is not required.  It is not mandatory in Microsoft SQL server.  But if you will write this query in MySQL,  then semi-colon the end of statement is required.  So I’ll execute this command  and if everything runs successfully,  there is no error while executing this command,  then we’ll get this message command completed successfully.  So in order to see, okay, now guys, important part.  If I go to, if I’ll see that,  whether the table has been created in the database,  which we have created.  So let me refresh our database  and see the table is not here.  So it is very important guys  that when we create the table,  we have to ensure under which database  we are creating this table.  So this window on the top will specify that  which database we are using to execute these commands.  So in order to create the database in test one database,  which we have created, we have to select this.  If we don’t want to select this,  then we have the write the keyboard, use test one.  So this ensure that we are creating this table  under this database test one.  Now if I execute this command, executed successfully,  and if I refresh my database now  I can see the test table has been created in it.  So this is how we create the tables,  so you can expand this table  and can see various columns in it.  So we have created three columns.  So since I have not mentioned as I mentioned that  if I am not provide whether this column needs information,  yes or not.  Then by default it will be marked as a null,  means it is optional to provide values for this column.  So we have to explicitly write not null  if you want to make the column mandatory,  like a table can have many columns,  So based on business requirements  we add the columns in it  and then we defined the data type of the columns.  I hope creation of the table is clear to all of you.  Let’s move to the next point.  So you can try this example guys,  where you can create a new table, employees.  So this has employee id one of column  and its data type is numeric four.  First name, last name, manager id, hire date.  Since it is a date,  we have defined the data type as date here.  Then we have job id and department number.  Okay, so we have created a table.  Now there might be a scenario that  we might need to modify our table.  We need to define or we need to redefine  the scheme of table while creating the table.  We may have missed any column we have.  We may have wrongly defined the data type  of any column or we might not want certain columns.  And we want to drop those columns,  so delete those columns from our table.  So to define, to redefine or to alter the schema  of our database object, we use alter commands.  So we use alter table commands to add,  delete and modify columns,  or we use alter table commands  to add or drop constraint on an existing table.  So as of now we’ll discuss about first point  and the second point about the constraint  we’ll discuss when we discuss about the various constraints  in the coming slides.  So as you can see, we can use alter table commands  to add a new column in our existing table.  We can use alter table commands to drop a column  to remove or delete a column from our table  and we can again use alter table command  to modify that data type of a column.  So let’s see how we can use it, alter table.  So let’s see how we can use it, alter table.  Then we have to follow the name of the table.  Let’s assume that we missed age of the employee.  So we are going to add a new column, age in our database.  Age and the, so this is the syntax, alter table,  Age and the, so this is the syntax, alter table,  the name of the table,  add name of the column  and then the data type.  So we’ll define add age and we define data type as INT.  So we’ll select this and we’ll execute.  So it has executed successfully.  So let’s see whether this column has been edited  in our existing table.  So we’ll just right click our table  and click on refresh here.  As we can see we have added a new column, age in it.  Since they have not defined it as not null.  So it is again an optional,  so it is up to us whether we provide the values for age  while inserting the data in this column or not.  Again guys, we can use alter table to drop a column  from our table.  Syntax is again same.  We have to write alter keyword first,  followed by the table keyword  and the name of the table  from which we you want to delete our column.  Then you have to use the keywords drop  again followed by keyword column  and the name of the column.  So guys, this is the syntax.  So we have to replace the table name.  In our case we have the table name as test table here,  drop column and the name of column.  So in order to remove the same column,  which you’ve added just now,  so I’m deleting the column again.  So you have to follow alter table,  name of the table.  The column which needs to be dropped.  You know it’s an important part is that  if you have multiple queries in the same window,  then please don’t click on execute,  because if you click on execute in the top,  it will execute all the queries present in the same window.  So either you will write your query in the new window  or in order to execute the required query,  you have to select that.  And then you can either click F5  or you have to click execute.  It’s mentioned that command completed successfully.  So now if I refresh my table again,  the column is normal.  We have successfully deleted the column from our table.  So this is one use of our alter commands  that we can add columns in it.  And again we can delete the columns from a table.  Other than that we can use alter table commands  to modify the data type of any extreme columns.  So this is the commands for that.  I’ll go to management studio, paste it  I’ll go to management studio, paste it  and they have to replace the value in the ankle bracket.  So we’ll have to define the table name.  So we have that test table here,  alter column, name of the column.  So let’s assume we need to chase the data type  of employee id from INT to character.  So I’ll define it as mentioned the name of the column  of which we want to change the data type  and now we’ll define what new data type we want.  So I’m defining it as character 10.  If I’ll go ahead and refresh this,  I can see the data type of employer id  has been changed from INT to character 10.  And modifying the data type,  we can use this alter tables command  to modify the constraint to add a constraint in a table.  Even we can use it to disable the constraint.  Sometime we don’t want to delete the constraints.  We just want to disable.  And then as and when required re enable again.  So we’ll discuss this when we we’ll discuss about  the various constraints in the coming slides.  These are the various examples  so you can practice these examples.  So here we are adding a new column salary  in the employee table  and the data type is numeric 10, two.  In a second statement we are again removing your column  called job id from employer table.  And in the third statement we are changing that data type  of first name column and we are making it to varchar(25).  Good part here is that we do need to  provide the old data type.  All we have to do is that we have to type the new data type.  Now we have drop table statements.  So in the drop table statement is basically if you want to  drop a table, we need to delete the entire table  from the database.  As of now in the previous example we have deleted a column  from a table.  But now if we want to delete the entire table  from our database,  in that case we use the drop table keywords.  So syntax is straight forward,  we have to write the drop keyword  followed by the table keyboard and the name of the table  which you want to drop from our database.  So let’s assume you want to drop the table,  which we have created just now.  In that case, we simply write the syntax here.  drop table and the name of table.  drop table and the name of table.  Guys it is very important when you’re dropping  or deleting a table because when you drop a table,  that data, it will also get deleted.  So we have to be very cautious  while deleting the database objects.  Select this and click on execute.  Command has completed successfully.  And if I’ll try to go and refresh the tables again,  I won’t see the test table there  because we have just now deleted it  by executing drop table command.  So so far we have discussed that we can use create keywords  to create a new database.  When we create a new database,  it is of no use because we have not defined  our objects in it.  The database will by default  have all the out of box objects in it.  So we have to first create our tables  so that we can store our data anytime  and can create various other objects.  So we have a created tables,  we have created database and after creation of a table  we saw that in order to modify  any schema of any existing tables,  then we have to use alter commands.  We have to use alter keywords.  So we have modify the data type.  We have added a new column,  we have deleted a column.  We have deleted an entire table from our database.  Now we’ll discuss about the constraints.  So constraints basically these are the various rules  which we apply on the table whenever a row are inserted,  updated and deleted from our tables.  So it even prevents the deletion of a table,  if there are dependency from other tables.  So for example, as we discussed that this database  is relationship database.  So various tables have relations among them.  So we can not delete a table  if it has a dependency on another table.  We’ll see that what kind of dependency tables may have  in these slides.  So define the constraint at column or table level.  So we can define whether the constraint  is only for the one particular column  or the constraint is for the entire table.  So good part here is that it is not that  we have to always apply the constraint  while creation of the tables.  So let’s assume that you have created a table  and then you realize that you forget to add a constraint,  then no problem.  Even after creation of a table,  we can go ahead and use the alter commands,  we can add constraint in the existing tables also.  So we will see now how we can add those,  how we can add a constant while creating a table  or how we can concern after creation of the table.  So before that lets discuss  what are the various common constraints  which apply on tables or columns.  So not null as we already discussed that  specified that a column must have some value.  If you won’t provide not null,  then by default the column will be optional.  And it is up to us whether we want to provide value  for that column while inserting data in a table.  We have unique keyword,  specify that collar must have a unique value.  So if I define the column as a unique,  it means no two row can have the same value for the column.  We have primary key, primary key specify column  or a set of column that uniquely identify a row.  It does not allow null values.  So guys this is the difference,  and as you can see we,  when we say unique constraint and primary key constraints,  both the constraint states that it helps us  to uniquely identify the values.  Then what is the difference between unique key  and primary key?  So one basic differences that is unique constraint  even allows us to insert the null value.  The point is that there should be  only one single null value,  because the moment you’ll add one more null values  in another row, then it violates the unique key constraint  because no two row can have the same value for that column.  But yes, for a single row you can have a null value,  but it’s just not the case for the primary key.  Primary key, if you want to make any column  as a primary key the first criteria is that  the column cannot have null values in it.  Second main differences is that  we can have only one primary key in a table,  whereas a table can help multiple unique keys  or multiple unique columns in it.  Okay, so let’s discuss more about primary keys  and how we create it.  So as we discussed in the normalization also,  that generally a single columns is a part of a primary key,  but it is not that only single columns will be there,  based on your business requirement,  we can add more columns in a primary key.  So a primary can key can have more than one value in it.  It can be two, it can be three.  So those primary key which have more than one column it,  they are called as a candidate key.  Let’s see how we can create the primary keys.  Okay, so far we have discussed about  like how we can drop the tables from the database  and let’s discuss about the various constraints.  So constraints enforce rules on the table,  whenever rows are inserted, updated  and deleted from the table.  So we can apply the constraint activity level  or at the particular column level.  Constraint also prevents the deletion of a table  if there are dependencies from another table.  So as we discussed in RDBMS,  So as we discussed in RDBMS,  that RDBMS is a collection of related tables,  is a relational database.  So if their dependency between tables  in terms of primary key, foreign key,  if you try to delete a table,  then if you give an error that the two tables are related  and there’s a dependency on other tables.  So constraint prevails, the deletion of a table,  the good part of our constraint is that  they can be applied during creation of a table  and even we missed the constraint while creation  of the table,  then we can add the constraint  even after the creation of the tables.  Like we using alter commands.  So before we see how we can apply the constraints,  lets discuss what are the various constraint available,  column one and not constraint.  You already discuss about this constraint  when we created our table.  So not null specify that a column must have some values.  So if you won’t provide this constraint by default SQL  consider it as a null and that column will become optional.  So in order to make column mandatory,  we have to explicitly provide not null constraint  when providing the data type.  So we have to specify the not null after that data type.  So let me show you.  So in the last name we have defined the data type  where I get 50 and after that we defined not null.  It means that the value in this column will be mandatory  and we cannot provide a null value  while inserting data in test table.  Where as for employer id,  since we have not provided this constraint,  employee id will be null  and we may or may not provide the data  while inserting in the test table.  Then we have unique constraints.  Unique constraints specify  that column must have unique values.  So if I’ll specify the column as a unique,  no two rows can have the same values for that column  and we have primary key.  Primary key, specify a column or a set of column  that uniquely identifies a row  and it does not allow an values.  So as you will see, unique and primary,  both of these constant define,  that column should be having a unique value.  Then what is the basic difference between unique  and primary keys?  Difference is that unique allows null value in it  whereas primary key, does not allowed null value.  The second difference is that  there can be only one primary key in a table,  but there can be many uniquely in a table.  So this is the second difference,  second main difference between this.  Again guys, primary key,  generally we have single column as a primary key,  but based on business requirements we can add more columns  to that primary key.  It may be two columns, it may be three columns.  So our primary key having more than one minute  is called as candidate key.  So before we’ll discuss about the foreign key  and other constraints,  we’ll first see what his primary key, how we’ll define it.  I will create the primary key while creating that table  and how we can add the primary key  after we have done with the creation of the table.  So let me move to management studio  and to save time I have your examples with me,  so we’ll create an employee table.  So I’ll open a new query here.  So guys, if I want to make a key, primary key,  all I have to do is that I have to write the keyword  primary key after my column.  So if I’ll execute this command,  let me increase the font,  once I’ll execute this and if I’ll refresh the table,  I can see the employee table has been created.  And if I’ll go and see the key that created  on my column employee id.  And you can see there’s a prefix of PK, primary key.  So you can check which column  and you can see the key sign,  so this is for primary key.  So just by expanding the columns  we’ll come to know okay,  which column is basically my primary key.  So you can check the keyword  and you can check this icon before the column.  So to create a column as a primary key,  all you have to do is write the keyword,  primary space key,  right after that data type of your column.  So this is one way of creating the key.  Some alternate ways also,  so this is another way or the alternate way  to create the primary key.  So I’ll just changed the name of the table  since we already created emp table,  and if I’ll try to recreate the same table,  it will give me an error  the table already exist in the database.  So one database cannot have two tables with the same name.  So let me show you,  if I’ll try to create the same table again,  it will give me an error that  there is already an object name emp in the database.  So it means at database cannot have any two objects  with the same name.  So you have to be careful.  So let’s create an employee one table here.  So in the previous example, we have just added keyword,  So in the previous example, we have just added keyword,  primary key after employee id.  But here I am not adding that query,  and I’m defining the constraint.  Constraint and I have to write the name of the constraint.  Here, I’m not provided the name  of my primary key constraint.  So SQL itself will provide a name of the constraint.  As you can see, we have not provided this PK_emp and so on  and followed by a unique id.  But in our case, if we are creating a primary key  using this alternate approach,  then we have to provide the keyboard constraint,  name of our constraint and we’ll define the primary key.  And we have to explicitly in the parenthesis  we’ll define okay for which column  we are considering for primary key.  So let’s create a primary key here, successfully created.  So I’ll go ahead and refresh the table.  So here I’ll see columns,  again employer id is a primary key.  If I’ll expand the keys here.  see the name is exactly the same,  which we have provided the emp_pk.  Yeah, so both this approach is good.  You can pick anywhere on the approach  to create the primary key.  But there’s a slight disadvantage of using  the alternate approach.  If I want to include multiple columns in the primary key  as we discuss that a primary key  may have more than one column in it,  then we cannot go with this approach, this approach,  if you want to create only one column, primary key.  So if you want to consider multiple columns,  then we need to consider the second approach,  this alternate approach for primary key.  Let me show you one example.  Let’s consider this example guys.  In this case we are considering two columns for primary key.  Last name and first name,  so I cannot go with the first approach.  I have to fall in the second approach  where I have explicitly provide the constraint EPR.  So I’ll go ahead and create the primary key,  the table again and let me refresh.  As you can see,  we have successfully created employee two table  and since we have considered both of these columns  for primary key,  I can see the key icon in front of both these columns,  l_name and f_name, let me expand this,  As we mentioned, primary key cannot allow null values  so by default this columns are having not null  and if you’ll see the key’s name,  this is the same name which we provided  while creating the constraint.  So this is how we create the primary key  while creating the table.  But it is not that we cannot add primary key  once the label has been created.  So let’s see, one example where we will create  primary key once the table has been created.  Okay, so this is the syntax for that.  Before that, let me go ahead and create a table.  So what I’m going to do,  I’m creating an employee four table  and I’m not providing this keyword here  I’m just providing not null.  So I have created a table,  so I’ve created successfully the table employee four.  Let me go ahead and refresh the tables again.  So we have employ four tables,  but there’s no primary key here.  And now I want to make employee id as a primary key  so for that we need to follow this syntax.  We have to alter the table.  Again, alter table not only helps us to add a column,  to delete a column from the table,  or to modify the data type of a column,  but we can also use alter keywords  to add a constant or to believe the constraint  from the table.  So I’m using alter table, name of our table.  In our case we have employee four as our table.  So I’ve given the name employee four,  I’ll provide name of our employee key for example,  employee primary key,  and then followed by the keyword.  And then I’ll define which column  I need to consider for my primary key.  So we are considering employer_id.  So I’ve given employer_id here.  And if I’ll run this particular SQL query,  command completed successfully.  And now if I’ll refresh employee four,  I can see the key has been created  and if I’ll go and see the columns  its taking time, yeah, I can see  the employee ids now the primary key.  So you’ve seen both examples  where we can create the primary key  while creating the table  or we can alter the existing table  and add the primary key  even after the table has been created.  I hope this was clear guys.  Important part and most important part here is that  please do not forget that,  we can have more than one columns in a primary key  and those primary key sometimes  are often called as candidate key.  Let’s continue and discuss and we’ll discuss  about the foreign key.  So these two tables can be related  and the two tables in the database can be related  by means of primary key and foreign key.  So first let’s define what foreign key is.  So foreign key helps us to create or to enforce  the referential integrity within our database.  A foreign key means that value in one table  must also appears in the another table.  So when we joined these two tables,  that reference table is called as the parent table.  While the table in which we create the foreign key  is called the child table.  So the foreign key in the child table.  will generally reference a primary key in the parent table.  This is how we create primary key  or this is how we get relationship between tables  by means of primary key and foreign key.  So let’s first see how we can create the foreign key  and then we’ll discuss about the relationship.  So if I create a foreign key, see now again,  let’s revisit the point number two.  The point number two says that constraint prevents  the deletion of a table  if there are dependency from another table.  So if two tables are related by means of primary key  and foreign key and if you tried to delete the parent table,  then it won’t allow us to delete the parent table  because the parent table is referenced by the child table.  So that is what is the meaning of the sentence to that.  If there are relationship between tables,  it prevents the deletion of a table.  So let’s see the syntax of creating foreign key.  Again guys like how we edit primary key.  We can create the primary key while creating the table  or we can modify the table,  and then using alter table commands  or using alter keywords we can add the primary key.  Same way we can create the foreign key  while creating the table or once a table has been created,  we can go ahead, use the alter commands  and then add a foreign key.  So for this I’ll consider two tables guys.  As you can see on my screen,  I am considering, let me increase the font.  Yup, so I’m considering two tables here.  First is the product table  and in product table prod_id is my primary key  and I’m creating one more table called orders.  And orders, order_id is my primary key,  but I’m creating a foreign key.  So the product id of my orders tables  will be related or will be connected with the product table.  By means of a foreign key.  So let’s see how we’ll add,  let me copy these two tables  and we’ll open a new window in management studio.  Increase the font, paste it here.  So first of all, I’m creating my parent table.  That is a product.  So I’ll select it and execute it.  So command completed successfully.  If I’ll go ahead just to verification  I’ll just refresh this and I see okay,  it has been created successfully  and all the columns are there,  order id is a primary key.  Now what I’m doing is that I’m creating  a child table called orders, and the order tables  we’ll be referencing the product id column,  which is the primary key of products table.  So this is the primary key of orders table  and this product id.  This second column will be a foreign key  and this will be connected with the product id  off my product table.  So in order to do that we have the write constraint,  explicit keyword.  Then we have to provide the name of our constraint.  It can be any name, you have to decide,  all we have to do is that we have to ensure  that this constraint does not exist in our database.  Okay, it has to be unique value.  Then we have to provide the foreign key.  Okay, which column we want to consider for foreign key.  So out of these three columns:  order_id, prod_id, quantity.  We are considering prod_id for a foreign key.  So I provided the prod_id in the parenthesis  and then we have to tell okay,  which table we are referencing,  there can be 100s of table in our database.  So if I want to create a foreign key,  I need to define the parent table for that,  the table which we are referencing.  So in our case we are referencing the product table.  So I’ve selected product here and then in the parent table  which columns we want to reference.  So we are the first finalize the table and then the table,  then we have to finalize the key which we’ll be referencing.  So don’t get confused because I have the same name  of the column in the child as well as in the parent table.  I’ll execute this command, successfully created.  So if I’ll go ahead and refresh the tables again,  I can see product with id  and if I’ll expand orders,  so guys, can you see the icon with this orange color  is basically primary key.  And this, this grey color is my foreign key.  And you can see this keyword as a prefix FK foreign key.  So this is how we create our foreign key.  So this is how we create while creating the table.  And let me show you the syntax  and how we can create the foreign key  once the table has been created.  Yep, so I will be creating it.  So let’s assume I have one table orders one,  and have tables one product one.  So as far as create these two tables,  the tables has been created successfully.  Now I want to add a foreign key,  once the table has been created.  So I have to define alter table commands,  and I defined which table I want to modify.  So I want to modify the child table  that is the orders one.  I’ll define the constraint here,  I’ll define the foreign key again,  which column I need to consider.  So we are considering again the product_id.  So I provide that in the parenthesis.  We are referencing the product one,  since I’ve renamed the table.  So I have just renaming the table as a product one  and the column name is same here.  So we have product_id.  So if I execute this command  this will go ahead and create a foreign key.  So I’ll refresh the tables of the database.  I can see there’s a table called orders one  and these are the two columns.  Order id and product id and the key is FK_product_id one,  which we have given.  So this is how can we create the foreign key  and we’ll discuss more about foreign key in the module  when we insert the data.  Quite an important part here guys is,  that when we create a foreign key on a child table,  for example the data of a parent table is  referenced in our child table  and if you want to delete the data from the parent table,  it will give us an error that the data  is already referenced in the child table.  Sometimes there are business scenario  when you want to go ahead and delete the data  from the parent table,  which is referenced in your child table.  But you cannot do that because it will give you an error,  but it will define our foreign key  in such a way that there is a cascade delete  that if I’ll delete the data from the parent table,  it will also delete the data from the child’s table.  Then while creating the key,  we need to define this keyboard on delete cascade.  This is an important one guys.  So by default your foreign key  is having a relation of restrict delete.  It means if you’ll try to delete the data  from the parent table,  which is referenced in child table it will give an error.  That data is referenced, you cannot go ahead and delete it.  In order to fix this issues,  there might be business scenario  when you want to delete the data  from parent as well as child.  Then while creating the foreign key,  we have to provide this keyword on delete cascade.  So this is all about foreign key guys.  So we have learned various constraints.  We discuss about the not null,  that not null is used  if you want to make our column mandatory  that it is mandatory for us to provide the data  while inserting records.  We discuss about the unique key.  For the unique key,  all you have to do is that you just have to write  the unique key word in front of your column.  So unique keyword, just define the unique key.  We discuss about the primary key  and discuss about the difference between unique key  and primary key.  Unique key may allow null keywords,  but it only allow for one single row  because the moment we’ll try to add null key in two rows,  it violates the constraint of uniqueness  because uniqueness defines that no two rows  can have the same value for the column.  But primary key won’t allow null values.  Second thing is that our table  can have more than one unique key,  but they will be only one and one primary key in a table.  Then we’ll discuss about foreign key,  and foreign key we can create a relationship  between two tables by means of primary key and foreign key,  that tables which you’re going to reference  is called as a parent table  and the table in which will create a foreign key  is called as my child table.  I hope your understanding the various constraints  and check again specify a condition that  and check again specify a condition that  must be satisfied by all row in a table.  So again these are the various examples.  So if you’ll see in the first example  they’ve created an employee table  and while creating the table  we are providing the keyword primary key.  So we are creating the primary key,  while creating the table.  But in the second example,  well we are adding the constraint to an existing table.  We’ve already seen through the example  of the key in the syntax.  So you have to write alter table.  name of the table in which we want to add the constraint.  We have to provide the constraint by using add keywords.  So we have that in add constraint.  Name of the constraint is mandatory.  If we are adding the constraint to existing table,  what kind of constraint we are adding.  So this keyword, for example here,  if I’ll add foreign key  then it will learned a foreign key key constraint.  If I’ll add a unique key  then it will add unique key constraint.  So as of now we are creating a primary key constraint,  then I have to explicitly define what kind of constraints  I am adding to existing table.  And in the last,  as you can see it is,  not only we can create a constraint to an existing table,  but we can remove the constraint from an existing table.  As you can see that syntax is straight forward,  you have to write the keyword alter table,  and then we have the name of the table,  followed by the keywords, drop constraint  and then which constraint we want to delete.  So guys it is very important  that when you are creating or adding a constraint,  you should give a meaningful name to it  so that it will be easy for you to understand  what is the name of the constraint.  So if you want to drop that constraint,  it comes handy because we have to provide the name  of the constraint which you want to delete.  So I hope we are clear with the constraints,  various constants we have discussed, not null, unique,  primary key, foreign key and check.  We have seen that we can create those constraints  while creating the tables  or we can add to the existing tables  and not only we can add, but we can drop the constraint  from the existing tables.  The last topic for this module is view.  As you can see, a view is a named design virtual table  of view takes the output of a query  and treat it as a table.  So view basically it takes out just as focused on this time,  it takes the output of a query,  and treat it as a table, but it is not a table.  It act as a virtual table.  So we can create a view from a single table,  or we can create a view from multiple tables.  So I’ll show you how we create the views  from multiple tables in module three  then we discuss about the joints.  So before creating used for multiple tables  we first to understand what join is  and how we can join more than two tables.  So as of now, I will show them how we can create  a view using a single table.  The syntax is straightforward.  We have to just write create view,  name of the view which you want to create.  Just focus on the keyword guys.  We have to write as keyword  and select and then start from which table  you want to include, okay.  So let me show you one example.  If write create view,  guys as I mentioned that SQL is not case sensitive,  but sometimes as an naming convention we provide caps  for our reserve keywords that we know  that these are the reserve keyboards.  So you can follow the same naming convention if you wish.  Create view, followed by the name of the view.  So as of now, I’m creating a test view  and then as select,  and then as select,  star from and the name of the table.  For example, let’s say I’m creating a view  from employee table.  Yeah, it’s completed successfully.  And in order to see the view,  I have to first go and refresh my database.  And guys, there’s a separate categories for views.  We have separate categories for tables.  We have separate categories for programmability  and under programmability we stored procedures, functions,  which we are going to discuss  and triggers in the last module.  So we have a separate categories  for most common database objects.  So in order to see the view which we have created just now  we’ll see, okay, see of you has been created, dbo.TestView.  And if I’ll expand this guys,  I’ll see that this view has three columns,  employee id, last name and first name.  Because we have created a view from employee table.  So I’m employee table has three columns,  employee id, l name, f name.  If we do not want all columns from a table,  in that case guys we have to explicitly provide  the name of the columns.  For example, I just want only employee id and l name,  I don’t want first name while creating the view.  So if I’ll create a view now,  it is basically having employee id  and the l name from employee.  So if I’ll execute this,  we’ll go ahead and create another view,  but this view will now be having the selected columns  which you provided while creating the view.  Let me see, it has only two colors.  So this up too is how many columns  we want to define inner view.  A table can have like 100 of columns,  but if you want to have a selected columns,  few columns in our view we can do that.  So view is a virtual table.  We create view, for example,  we need to write a query quite often,  you’re using that query quite often.  So we can, instead of writing that query everytime,  we can create a view  and then can view can act as a virtual table  and we can query the view,  the way query our SQL table.  So DML commands basically define the schema  of database objects.  And DML commands basically helps us  to manipulate the data in the database objects.  So the important DML commands are,  or the most common DML commands are:  insert, update, delete and select.  Lets first discuss about the insert statement.  So insert command is used to insert data  or record in the database table.  When we create a table, the table is blank.  There’s no data in it.  It has only the scheme of the tables.  What are the columns we have defined  and the constraint of the tables.  So very first thing we have to do is  that we have to insert some record in the tables.  So they are two basics syntax of the inserted into statement  which we use to insert records in the table.  The first one is insert into name of the table  in which we want to insert record  followed by the column names  and we assume that these are the field names,  field names or column names.  So sometimes we refer the columns as a field also.  So after table name we’ll define what are the columns  in the table.  Then we define the can keyword values  and the values of the corresponding columns.  Important part here is that the order  in which we’ll define the column here,  the value should be in the same order  or else we’ll get an error while inserting the record  in the table.  So let me show you one example.  I’m increasing the font size for better visibility.  Let’s assume we have a table called department one here.  So we are using employee DB database here.  Good practice to use it upfront that which database  we are using for our queries.  I’m using emp database,  so I’m using it the false statement in my query window,  then I’m writing insert into department one,  department number, department name, values, 20 and HR.  So these are the values I’m going to insert in.  So this is the syntax, insert into name of the table,  followed by the column names.  Then we’ll use the keyword values  and followed by the values of the column  which we are going to insert.  So let me execute this command.  As you can see, one row affect it means,  we have inserted the changes.  So in order to see whether the record has been inserted,  you need to fetch the data.  To fetch the data We basically use the commands,  but we will discuss about the select command  in the coming slides.  You can right click the table  and you can see select top 1,000 rows.  As you can see here, we have inserted the ninth record  with the department id.  Department number 50 and the department name HR.  This is what we’ve inserted into.  So if I will change the order,  lets assume I’ve changed the order and make it 17.  So I’ve changed the order of the values.  The order is not the same as I have defined the columns.  And if I execute the query, let’s see what happen,  we’ll get an error because of the mismatch data type.  So department number is basically numeric  and department name is character type.  So what we are going to do,  since we have changed the order of our values,  so we are trying to insert string  or character data into numeric.  That is why we are getting this error.  So the important thing is we have to take care about  the order of the columns and order of the values  while inserting that record.  So this is the first syntax that we need to provide,  the name of the columns.  Second syntax is, if you want to insert the data  in all the columns,  let me show you on the slide,  so you can see insert into departments  which we have discussed.  So if you want to insert values in all columns,  then no need to specify the column names,  but order of the column value should be in sync  with the column names, that’s what we discussed now.  So they are two difference in texts.  One we need to define the columns  or we can skip the column names,  we can directly mention insert into table name  and the values here.  So if I’ll go here I can say insert into department one  and then I can directly right what values I want to insert,  and say I want to enter,  the department IT in the location, Mumbai.  Again the insert is successful.  The one important thing here guys,  have you noticed that when when we are inserting  the record, numeric records.  We asked simply writing it,  but when we are inserting string or character  then we have to enclose this end in single codes.  Since IT and Mumbai, these are the string values  which I’m going to insert into the columns.  The columns are location,  department name and department number.  Department number is numeric.  So I’m not enclosing this value in single codes,  whereas department name and location  is variable and character.  So I need to enclose the string into single quotations.  If I’ll tried to insert the record without the quotation,  I will get an error,  let me try to insert this, invalid column name.  So please take care of this.  For string data or character data,  we need to enclose this in single stream.  And other important insert command  is insert as select statement.  So far we are inserting the data in table,  but here using insert as select statement,  we’ll insert the data in the table  but the data will come from another table.  So using this commands,  records from one table will be inserted in another table.  As you can see the syntax,  we can insert into table name, followed by the column names.  What are the columns names, which you want to define.  Then we need to select the columns from the next two table.  So we are defining, select column names from table name,  where condition and then this is the syntax.  So let me show you the examples here.  For example, I’m inserting the values  in department one table,  I’m defining what are the columns  which I want to insert into.  So these are the columns.  So if we have department number,  we have the department name,  and then we have location.  And then if I want to insert all the columns  so I can define the star here, followed by the column name.  If I’ll execute this query,  I can see they were four records in the department tables.  If you’ll see in the department,  if I’ll execute this query,  select top 1,000 queries,  I can see there are four records.  That is the reason when I executed this command,  I got a message, four rows affected.  So what we have done in this query is,  I have inserted all the record from department table  into department one table.  If I don’t want to insert all the records,  then I have to specify a where condition here.  If you see if for example,  let’s assume this is my table  and I want to only insert those records  whose having department number greater than 20.  In that case, I have to write where department number  is greater than 20.  If I’ll execute this query now,  see only two records are affected.  Why?  Because we have only two records  which is satisfying the conditions of department number  greater than 20.  So if I’ll go and see the department table,  I can see all records has been inserted.  In the last, we have inserted 30 and 40  because it is satisfying the condition  that department number is greater than 20.  So if we want to insert all the records of one table  in another table, then we have to skip the where command,  or the where clause.  If you want to insert a specific rows, specific records,  then we have to filter those records using the where clause.  Another important thing here is,  it’s not that we have to always insert  the values of all the columns.  I can skip or I can selectively insert  the values for column.  For example, I don’t want to insert the value for location,  I can skip it.  But here also instead of a star,  I need to define which are the column I’m selecting.  So I have for defined department number  and then department name.  Again the important part here to notice is  we have to consider the same order of the columns.  Since we are inserting department number  and department name,  from the second table also we need to arrange the column  in the same order.  If I execute this query,  it will execute it successfully.  All the full records are inserted into department one again,  but if I changed the order,  and I felt executed now, we’ll get an error,  the same error, the data types mismatch.  So be careful whichever insert syntax you are using,  whether you want to insert one table record to another table  or you are inserting only directly the table,  the order of the column is very important.  Another important part here is  that when we are inserting record in a table,  we have to ensure that we’ll always select all the columns  which are not null.  If you remember in the previous module  we discuss about the not null constraint.  Not null constraint defines that  value in the column should be mandatory.  So when I’m inserting a record in a row,  I have to provide the value for all the columns  which are mandatory.  Let’s take this example.  In the department one table,  we have department number as a not null department name  as not null and location that is loc is null.  So in this example I have not provided loc  but still I’m able to insert the record.  But if I’ll skip the department name  and I’ll consider loc for insertion,  and even though I’ll maintain the same order of the column,  but I will get an error while inserting the record, see.  Cannot insert the value null  into the column department name.  So department name is not null column,  when we are inserting the records  we have not specified department named column.  So if you’re not specify so then null value  will be inserted in that.  And since the column has a constraint of not null,  it won’t allow us to insert the record.  So we have discussed about insert statements.  These are the very important table commands  because without inserting records in the tables,  we cannot update those.  We cannot delete them, we cannot fetch them.  So insert statement is the main table commands  as it helps us to populate our database objects  with some data.  Database is not static.  It’s not that we insert the data once and then we are done.  No, data needs to be modified as and when required,  as and when there is a business requirement.  So used update statement to modify the data.  Update statements modify the existing data in the tables.  Using these statements we can update the value  of a single column or multiple columns,  in a single statement.  We have the choice,  whether we want to update the value of a single column  or whether we want to update the values  of the multiple columns and these can be executed  in a single SQL query.  We don’t have to write multiple queries  for each and every column.  When the update the records in the SQL table,  it is very important that we should provide the condition  that which records we want to update.  If you fail to provide the condition,  then all the records in a table will get updated.  So whenever we are updating the records,  it is very important for us to define the condition.  As you can see in the notes, without where clause,  all the rows will get updated.  So let’s see one example,  let me open another table.  So I have department two  and I can see they are four records in it  and I want to update the department name  of the department number 30.  As of now it is IT and I wants to change it to marketing.  So we have to follow the syntax update.  Let me copy it so we can save some time.  So we have to set the department name  and we want to set the department name to marketing.  So I had to define the new value,  which we are going to update,  and we have to update the value of department name  for the department number 30.  So I need to mention that in the where clause.  So let me execute this query.  As you can see, one row affected.  It means the update has been done and it is successful.  So let me again see the records.  So we have updated department, not the department two.  So I have updated the wrong table.  So let me execute it again.  Again, one row affected and if I’ll execute this,  select top 1,000 rows.  I can see that the department name has been updated.  I was talking about the where clause.  That where clause is very important  while you make any update statements.  Let’s assume we forget to include,  so this is how guys, if you want to comment anything,  you can two hyphen.  So the green part here in the query is that it is commented.  Or you can write forward slash star,  and then star for the backward slash.  This is how you can add comments in your SQL queries.  We’ll see the use of this comments  when we’ll discuss about functions and modules  when you have to write some comments  so that other developers can understand  what you are writing.  So to add comments in SQL queries,  either we have to follow this syntax  or we have to write two hyphen.  So if I’ll skip the where clause in the update statement.  Let’s see what happens.  Can you see four rows effected.  They were four rows in the department two table.  And while updating, when we skip the where clause,  all the records get updated.  So can you see, the department name for all the rows  has been updated to marketing.  So it is very important that while we are updating  we have to specify the where clause  or else all the records will get updated  with the same value for a particular column  which we are updating.  So in this example,  I have updated the value of a single column,  that is department name.  But in single SQL query we can update  values of multiple columns.  Let’s take the example.  So in the department two,  I am just updating the values  of department name into marketing  and I’m updating the location to Chennai.  And I’m updating it for only department name number 30.  So in this single statement,  I’m trying to update department name and location  where it has been executed successfully  and if you’ll go and see the record now,  30 has been up updated to marketing.  So maybe to remove the confusion, what we can do,  we can update it with some of the value.  Let’s say it’s a HR and may execute it again.  As you can see, since in the where clause  I have mentioned department number 30.  So I have updated the department name and location  in a single update statement.  Whether we insert the record in the table  or whether we update the records in the table.  The data type plays a important row.  We have to always update the data  as per the data type of a column.  If I’ll try to insert,  let’s assume that I’m updating the department number,  so let me write one more query.  Let me first increase the font,  let’s assume I’m updating the department number  and instead of a number  I’m trying to update with string or character.  So I’m trying to update the department number 30,  with value HR.  If I’ll execute this, you give me an error.  That error converting data type where get to numeric  because department number,  it has a data type numeric  and I’m trying to insert string or character  in the numeric data type.  So irrespective of whether we are executing update statement  or insert statement,  we have to always take care about the underlying data type  of our fields or columns.  Now we’ll show some lights on delete statements.  So delete commands helps us to delete row  or records from the database table.  Similar to update statement,  delete statement can be executed  with or without where condition.  Again, point here to be noted is that,  if you’ll execute the delete commands  without where condition,  than all the records or rows from the table will be deleted.  So where clause is very important.  Whether we are working with update statements  or delete statement.  So if we purposefully want to delete all rows  from our table,  then of course we won’t include the where clause.  And if you want to specify whether to start a single row  but multiple rows.  But if you want to not delete all the rows,  then of course we need to include a where clause.  Let me show you few example,  let me open a new query.  So let me first you what are the records are there  and we’ll try to delete those.  So I’m, we’ll be deleting the record  with department number 40.  So one rows affected means the query  has executed successfully.  So if I’ll go ahead and execute this again.  I can see only three records,  because the desire has been deleted from the table.  Now if I will skip the where clause,  so I’m commenting the where clause  by adding two hyphens here,  as discussed we can add the comments  by using either hyphens  or using forward slash star  and then star and then again forward slash.  If I execute this query now,  can you see in the recent, three rows affected.  So after deleting the department  with the department number 40,  we had only three rows  and all three of those has been deleted.  So if I query the table again, it’s empty.  Now there are no more record in department two,  so that is a reason it is very important  to include where clause in the delete.  When we add the condition in the where clause,  it can be like single conditions or multiple conditions.  We’ll discuss that how we can add the logical operators  and how we can use the or conditions,  or and conditions in the where clause.  So far we have discussed three important statements  for DML data manipulation, language commands,  insert, update and delete.  Now we’ll be discussing about the select statement.  So far we have discussed that how we can insert the record,  how we can update the existing data  or we can remove that unwanted data from our database.  But how we are going to fetch the information  from our database and show it to the end user  is very important.  In this module we will be focusing  only on retrieval from a single table.  In the next module we will be discussing about  retrieval from multiple tables by means of joins.  Where condition is optional in select statement,  so as we discussed earlier,  where condition is always optional,  whether it is updated statement,  delete statement or select statement.  So we have to be very careful while using where conditions.  It’s up to us that whether there’s a business requirement  to include the where clause or not.  When we use where conditions there are various operators  which helps us for the better retrieval or better condition.  So to retrieve the data, we use select statement.  As you can see the syntax, select, field one,  field two, field n, and these are the various columns  from table name,  which column we want to include for data retrieval,  and followed by a where clause which is optional.  Let’s see one example of the select statement  and then we’ll move to the next slide.  Let’s assume I want to select the records  from department tables or employee table.  Let me like the query here,  let me delete the previous query.  So select statement helps us in data retrieval.  If we want to include all the columns of a table,  then we have to use this star or asterisk sign.  So this is the basics syntax off a select query.  Asterisk helps us in retrieval of all the columns.  So I’m writing select star or asterisk from employee.  As you can see all the columns of employee table  are their in my result sets.  Lets retrieve some other table.  Let’s retrieve department table.  Again, I want to retrieve all the columns,  so I will write select star from department,  since they are totally three columns in the department,  I can see all the three columns are here.  And there are only four records,  so all the four records are displayed.  Why four records are displayed  because we have not included any where condition.  We are telling SQL server  that we want to fetch all columns and all rows.  So if we’ll skip the where clause  and we’ll add an asterisk in as select,  it means we want to fetch all the columns  and all the rows in our result sets.  One important thing that for example,  I’m creating two different tables as of now,  so it is not that at any given point of time  I can fetch only values from single table.  If I click on execute  without selecting any particular SQL query,  I can see the results that of both the SQL queries.  So though where condition is optional,  but it is very important to include where clause  in SQL queries to set right conditions  so that we’ll get that precise result  or else you’ll get all the results of all the rows.  In real world scenarios,  we have millions of records in our database.  Generally 1,000 of rows.  So if you forget to add the where condition,  think about the number of records you will get  and it’s very difficult to get the right results.  So it’s very important to include where condition,  whether they use update, delete, or select statement  in DML commands.  As of now, since we have not included where clause,  but even without using where clause,  we can filter the number of records  which we want to include in our result sets.  So we have a keyword, a reserve keyword in SQL that is top.  Let me show you one example of that.  So though I’m including all the columns  by using asterisk sign in this query.  But I’m explicitly telling to the SQL server  that I want only want top three results to be included  in my result sets.  So if I’ll execute this query,  I will get only three records.  So we can define, we can change the values.  In this example I’ve shown only top three.  We can include top five, we can include top one.  I can confine or I can restrict the result sets,  but again it will be only from the top five, or bottom five.  So we cannot replace the where clause  or the where conditions by top five,  but top five helps us to retrieve only selected portions  of our data in a result sets.  So far we have discussed that we can use asterisk signs  if you want to include all the columns in our result sets.  But in the practical scenario and in the real time,  our table may contains 100s of columns.  But we might not be interested in getting values  of fetching values for all the columns.  We might be interested in few columns,  then yes, we can include selected columns  in our select statement instead of star or asterisk.  Let’s do an example.  So as of now, as you can see,  we have nine columns in the employee table.  But I want to see only the first name  and last name of the employee.  So in instead of writing star or asterisk,  I can mention select,  first name, followed by comma, last name from employee.  first name, followed by comma, last name from employee.  So I will get the results only for two columns.  And again, if we want to restrict the results,  I can use top, even with the selected columns.  So this can show me only top five records  in the result sets.  So when we are retrieving the results.  We, if you want to include all the columns,  then we’ll use asterisk.  If you want to include selected columns,  then we have to specify the name of the columns  and all the column should be separated  with each other by comma.  And the order in which we specify the column names  in select statement, in the same order,  we’ll get the columns in the result set.  Since in my results that I’m getting first name  as the first column because I have defined the first name  in the same order in my select statement.  In select statement, we can use Alias name  for a field or a column.  Please don’t get confused between field and a column.  Field is used for the column name of a column in SQL.  Why we use Alias, let me show you.  Let’s take the example of department table.  As you can see, we have loc for the location.  But maybe it is not clear to some developer  what loc stands for,  so I can change loc to location.  So for that we need to use alias in SQL.  So how do you use that?  Let me show you.  You have to write select department number, loc.  We have to use as keyword  and we have defined the name of our column location  from department.  If you’ll see I have used an alias location  for the loc column.  So to increase the readability of columns, we use alias.  Sometimes, when use of alias is in inner joins,  so when we’ll discuss about joins in the next module.  I’ll revisit this particular topic  and I’ll explain to you,  sometimes the column name, table name is very long  so we can use alias for column as well as for the tables.  As of now I’m showing you example for the column,  but we can use alias for tables also.  It is not that we have to use alias  only for a single column.  We can use it for as many as columns  in our select statement.  Even we can use for all the columns  which are there in our select statement.  So to sum up, alias helps us and better result sets,  it made result sets more readable  for the end user or for developers.  Let’s move to the next slide, distinct values.  A column can have the same values if it is not primary key.  So as we discussed in the previous modules,  a primary key or a unique constraint.  These helps us in maintaining a unique values  for each row for a single column,  but other than unique and primary key,  we can have multiple values for the same columns  in different rows.  Sometimes when we are retrieving,  we are more interested in only distinct values.  In that case we’ll use distinct keyword  to filter our result in such a way  that it will only retrieve the unique values  for a particular column.  So let’s understand that using one example.  Let’s take the example of department one column.  As you can see, I have Chennai at different places  for locations, and I want to know that in which location  my department is.  And I want only the unique values,  I don’t want that repeated data or redundant data  in my result sets.  So far that, what we can do is that we can use  unique keyword white fetching the data from tables.  So we use other by to show the results  or to filter the records.  So far we have discussed about the unique  or distinct keywords in the select statement.  Now we’ll see how we can sort the data.  So for sorting the use order by,  so order by is used along with a where clause  to display the specified column in ascending order  or descending order.  By default, the order is ascending order.  If we won’t provide any specific keyword,  the results will always be in ascending order.  So order by is used along with where clause,  but it is not a mandatory conditions that we have to use  order by only with the where clause.  So let’s see one example, let me make a new query.  We have select star from department,  let me increase the font,  select star from department and then we can use order by,  so we have department,  since I have not provided any keywords here,  so by default it will be ascending,  or even I can provide ASC.  ASC stands for ascending.  So if I’ll execute it now,  I can see the results, same result.  But if I change the keyword ascending to descending,  descending stands, DESC stands for the descending.  So if I’ll execute it now,  I can see department in the reverse order.  The results that has been reversed.  So this is how we can sort our records in the result set.  So in the previous slides we have discussed about  the top one or top five, top 10,  so we can club top keywords along with order by  to fetch the bottom records.  We can filter or we can fetch the bottom five records,  the bottom 10 records  because there is no result givers for bottom.  We only have the keywords as a top.  So if I want to show the bottom two records,  let’s see how we can club top with order by  to fetch the desired results.  So first of all I’ll show you that results  without using order by,  so I’m just showing you top two results  from department column.  And in the next query,  I will write select top two,  star from department and I write order by department number.  If I’ll execute both the queries.  You will see that in the first results.  In the first since we have not used the order by  in the descending order, I’m getting the top two records,  but in the next result sets since we have used order by  along with the top two,  we are getting the bottom two records.  So this is how we can, use a various concepts  in our select queries to get the desired result set.  So we have discussed the sorting,  that how we can start the data.  As you can see in this example we have used multiple columns  in the order buyer by,  so it is not that we can use only one column  in the order by,  we can include multiple columns,  but the results will be sorted in the order  in which we have specified our columns.  So since we have specified department name first  and the other is ascending,  the result sets that will be first sorted by  department name column.  So yes we can include multiple columns  in the order by clause.  Let’s move to the next slide, filtering.  So so far we have discussed that how we can insert the data  and then updation of data using update statements  then delete statements  and we discussed about the select statement,  top five, bottom five,  how we can include all the columns in our query  or result sets or we can include a selected columns.  We have seen that by order by clause we can sort our data.  Now we are going to discuss about the filtering.  So so far whatever examples you have discussed about  the select statement,  we have not included where clause,  since we clause is optional.  But if you will not include where clause in our SQL queries,  then we are going to get all the results,  which is not a good idea because your database  or your table can have 1000s of records.  So it may impact the performance also than when you are  you know, fetching all the records in one go.  So we use various conditions in a SQL query,  While fetching that data,  so we use where clause to narrow down our result.  So in the where clause, we use various operators.  We use comparison operators, we use logical operators.  So there are three main logical operators, and, or and not.  So we’ll discuss and operator first.  So and logical operator used to combine two conditions  and it fetches the result which satisfy both the conditions.  So instead of writing two different queries,  we can club the two conditions in one single query.  And if a query will satisfy both the condition,  that only those records are or those rows  will be shown in our result sets.  Let’s see with an example.  Let me first show you all the records  of the department table.  So we have four records  and let’s assume we want to filter the record  which has department number 10  and along with one more records,  which has department name, HR.  So what we can do,  we can use select since we are including all the columns,  select star from, table name, department  and now we’ll use the where clause.  So we’ll use where keyword,  where departments number is equals to 10  followed by the and keyboard, and department name is HR.  Please pay attention that we should not use double quotes.  We always use a single quotes in SQL.  So like other programming languages,  CC plus plus, C Sharp Java,  where we use double quotes for string.  Here we use only single quotes.  Whether it is a character or a string.  If I’ll execute this query,  it is not showing me any results  because it is not satisfying any condition.  Why?  Because there is not as single row,  we just having department number 10  and department name HR.  So if I change it department number to 20.  That will satisfy the condition,  the second record will satisfy the condition.  So that’s what we have discussed,  that if it will satisfy both the condition,  then only the rows will be displaced in the result sets.  Now if I’ll execute this query,  we’ll get the desired results.  This is how we use and logical operator to get the results  or get the desert rows in the result set.  So again, let’s see this example.  So in the first example we mentioned that  we are fetching the selected columns,  we’re using select first name,  the last name from employee table  where first name is equals to Miller  and last name is equals to Ward.  This is how we’re getting the result.  In the second example,  we are fetching that that the first name,  department name and salary from employee table,  where salary is greater than 20,000  and salary is less than 35,000.  So we can use multiple conditions  and only those records will come  which was satisfy both the conditions.  Let’s move to another logical operator  or like end operator,  or operator is also used to combine two or more conditions.  But the main difference is that  it will fetch the results  if any one of the conditions is satisfied  in the old statement.  So let’s discuss it with an example,  example we discuss when we made it to 10  we got the empty result set  because no row is or satisfying both the conditions.  But if I’ll replace and with all keyboard,  we’ll see both the rows.  So the main difference between all logical operators  and and logical operator is that  in and it has to satisfy both the condition,  whereas in or if any one of the condition satisfy,  we’ll get the rows or record in our result set.  So based upon the business requirement,  we use either and or or logical operator.  I will discuss the last logical operator not,  so not operator is basically used to negate the condition.  It basically fetches the opposite of the results,  which is basically defined in the conditions.  So it is also used in combinations  with other keywords like not in, not between.  As you can see in this screen,  we are using select department name,  location from department when location  not in Chennai and Bangalore.  Let’s see this with an example.  So let me show you first all the records  so that we know what we querying.  So let’s assume that I want to get all the departments  which are not located in Hyderabad.  So I’ll do, I’ll write select star from table  So I’ll do, I’ll write select star from table  where column name, not keyword  because we’re looking for a negation, not in,  we’ll discuss about in operator in the meanwhile.  Let’s execute this and here we go.  So now since we use,  we want all the departments which are not in Hyderabad.  So we used not logical operator in a negative sense  and we are getting all the records other than Hyderabad.  So this is how we use not operator.  So for filtering we use logical operators  as well as comparison operator.  So so far we have discussed  three important logical operators and, or and not.  We used and, and the we’ll get the desired result  if it satisfies both the conditions  we get the result set if it will satisfy  any one of the condition,  if you use the or logical operator  and not logical operator is used in a negative sense,  or to negate the conditions.  So let’s move to other operators,  like comparison operators.  So we have various comparison operators,  which we can use it in the where clause  to filter our results.  We have equals two followed by a not equals two.  We can use either exclamatory sign and equal to  for not equals to or less than greater than sign,  in combinations for not equals two.  So we’ll get the same results.  We can use either one of them.  Then we have greater than and equal to,  less than equals to, like operator.  Like operator we generally use,  when they use our play around  with the strengths or characters,  we use between operators when we want to find  or when we want to affect the results  from a range in keyword.  We just saw an example.  So these are the various comparison operators.  Let’s start with the between operator.  So the between operator is used to search for values  within a set of values.  So let’s see this example.  So we are fetching the details of employee  whose salary is basically between 2,000 and 35,000.  So lets see with this example.  Let me open a new query.  So we are going to fetch employees,  so let me first increase the font size  for better visibility.  So now I want to fetch the record,  salary is greater than 20,000 and less than 35,000.  So I’ll do that.  Then right select star from employee  Then right select star from employee  where salary between 20,000 and then 35,000.  where salary between 20,000 and then 35,000.  So between operators always used in combination  with and operator, this is how I get the results.  There’s only one record  which is satisfying these conditions.  This one, Smith.  So this is how we are getting the desire row  in our result set.  So it’s basically used for a range,  so you can use between operator.  Lets move to the next operation, not in.  We’ve already discussed about the not in.  So not keywords is, not is a basically the logical operators  and it is used along with other operators like in an other,  so we have just seen one example  where we want to show the departments  which are not in Hyderabad.  So this is the repetition of the same example.  In the previous example which I shown  I include only one locations,  but we can use multiple keywords  if you’re using the in keywords,  we’ll see the example, not in.  In operator fetches values from a set of literals.  So this condition see generally used  when you want to fetch values from multiple values  in a single statement.  So it’s not that I can use only in,  I can use or operators,  if there are only one or two conditions,  I can either use or operator or in operator.  But if the values are more,  it is not a good idea to use so many or operators  or logical operators.  Let’s assume, I want to see the results intensities.  So it I’ll write,  like where location is equals to Mumbai  or New Delhi or Hyderabad,  or Bangalore or Chennai, it is not readable, it’s not good.  So instead of writing so many or operators,  we can use in operators.  So the in we basically include all the values  out of which we want to filter our reserves.  So you want to fetch our values.  So let’s see with an example.  So let me open a new query window,  and write select from department.  So if I want to see the results,  or see the departments which are either in  Chennai or Bangalore.  So I’ll write, select star from department,  where location in, it can be in anyone of these location.  That’s how we are Bangalore.  Don’t forget to include strings or characters  in single codes in SQL.  So this fetch the desired results.  So we can use multiple values,  in the parenthesis if he used in comparison operator.  So if I use not in, then this will go in negation  and it will show the opposite of what an operator is doing.  So we have discussed not in, as well as in.  So if there are multiple values instead of or,  we have to use it is good practice to use in keyword.  Then basic comparison operators, we have greater than,  equals to, less than, greater than equals to.  So this is pretty much self-explanatory.  So if I want to select the employees  who’s salary is greater than 20,000,  so let’s see how we’ll get the results.  So I can use the previous window,  so where salary, instead of this,  I’d can write salaries greater than 20,000.  So I’m getting three results.  So the three I got are three rows  or three employees,  whose salary is greater than 20,000.  So if I include greater than or equals to,  then I guess I’ll get four reasons.  So I don’t think so we have a salary of 20,000 here.  No, we don’t have.  So let’s take the example of 30,000 here.  So we’ll get the three results.  But if I remove the equals to keywords,  then of course we’ll get only two rows in result set.  And if I use the equals to,  then we’ll get the only single row  which will satisfy that desire or required condition.  So this is how we use our various operator  greater than, equals to, less than, greater than equals to.  Again, this example, we can use equal operator,  it will satisfy the desired conditions to fetch the results.  Comparison operator use a like,  how we use the like keywords in comparison operator.  So generally we use light operators  to perform wild card searches of valid search string values.  As I mentioned earlier,  that like keyword is generally used when we are filtering  or fetching the strings.  So it used with two wild cards.  One is a percentage,  percentage denotes zero many characters  and underscore denotes one characters.  So let’s see.  So in the first example as you can see,  I’m getting the values from the department table  whose locations starts with C, or the C alphabets.  whose locations starts with C, or the C alphabets.  Let’s see how we can use it.  So we’ll first see how we use the percentage of wild card.  If I want to note, that what are the different departments  where the location start with the alphabet C.  So will write select star from department,  where LOC like we use the like keyword, like parenthesis.  And then the keyword C with the percentage sign  that is wild card would get the results.  So if I just change the order,  if I’ll write the wild card first and then C,  it means I have to show the department  whose location ends with C.  So if I’ll write it, I here.  Then again, I’ll get the desired results  because I am fetching the rows  who’s location and with the I keyboard or I alphabets.  So if I’ll use first wild card and then alphabet,  it means it ends with,  if I first used the alphabet and then wild card  means it’s start.  If I’ll write something like this,  it C and then wild card, it means it starts with C,  and ends with I.  So fetch me the locations who’s start with C  because I’m using the C before the wild card.  And since I’m using I after the wild card,  so it ends with I, if I’ll execute this query,  again it will show me the same results because,  if I’ll change it, for example,  I’m starting with H and ending with I.  So there’s no department within such a condition,  so you’ll get an empty result set.  And I find to show that,  show me the department who starts with edge,  in that case on I have to do is  that we have to remove the I.  So we have to use the keyword before the wild card  and it will show us the results.  So along with percentage signs,  we use underscores also as a wild card.  So in this example, as you can see,  the second example, select department  or location from department where locations like.  So it means the first three words  should be CHEN, then we can use underscore.  So we have to first satisfy the first keyword.  So the first three should be this.  So this is how we use the underscore or wild card.  The last topic of this module is the case expression.  So case expressions helps us to kind of simulate  the if then else statement,  like in most of the programming language we use if else.  So this helps us to write the same,  only point or the key statement is that,  as you can see that note,  we cannot use non values in the case statement  because it always use the equals to operator.  And we cannot use equals to operator with the null keyword.  We have to use is null,  that I’ll show you after this example.  So let’s assume I’m fetching values from employee.  So if I’m looking for the salary column,  there are a few rows which are having none values  in there are a few rows which having values in it.  So if I want to see that,  what are the different employees  which have a null values for the salary column.  So if I try to write where salary equals to null,  then I won’t get any results here  because equals to operators does not work with null.  If I’m checking now we have to use is null.  If I’ll execute this query, again I’ll get the desires.  If I want to check, if I want to fetch the columns  which do not have null values for the salary column,  then we have to use is,  and then they have to use the not logical operator,  is not null.  So not logical operators is always used  in combinations with other operators.  They get all the employees did not have null values  for their salary column.  So that is a reason we are using case expressions,  so we cannot use the null values,  otherwise it will break  because we use the equal to operators  and equal operators not used along with null.  So this is how the syntax of cases expressions,  as you can see we have declared a variable int input  instead of int.  And we have set the values as equaled to two.  Its just an example so the have to put in the value here  in this example.  So we are selecting case,  I’m just selecting that if the value is one,  then we’ll show that put as one.  When it is two, them we are showing the output as two.  When it is three, then we are showing output as a three.  And if it is not out of any these three values,  one, two and three,  then we’ll show the message as your message.  This is how we are doing in this example.  So let me give you one example for that.  Let me do one thing.  Let me find out the states of the cities  where my department is.  So as you know, the channel is in Tamil Nadu,  and Bangalore is in Karnataka.  So let’s see how we can do that using the case statement.  So we have to write select, then case keyword,  in the case keyword we will define which columns  you want to fetch,  I am fetching the location.  And then we write the when condition,  so I’m writing when Chennai,  then show the result as Tamil Nadu,  when Bangalore.  Then let’s say Karnataka,  and I’m adding an else condition  if it is not part of anything.  So lets just write, no idea.  So we have to end the when condition,  then we have to write from which tables  we are basically fetching (mumbles).  So if I’ll execute this query,  I’ll get say, if it is Chennai,  I’m getting the results as Tamil Nadu,  and if it Bangalore then I’m getting as KR,  and there’s some departments which do not have any values  like Hyderabad we’re getting is just no idea.  So this is how we can use if else conditions  and SQL statement or select statement  by using case expression.  Important point is that we should not deal with  where we’re using null because we use equal to operator.  A join clause is used to fetch data from two  or more than two data tables,  based on the join condition  which we’ll provide in the SQL query or SQL statement.  Join clause is used to combine rows from one or more tables.  When we say one means, if we are trying to join  the condition on a single table,  then this particular condition is called as self-join.  So these joints basically happens,  or takes place between tables  based on the common columns between them.  In SQL server we have the following joins,  we have self-join,  we have inner join, left join, right join  and full outer join.  Cross join is also call as Cartesian products.  A table can we join to it to itself, in a self-join.  The important part here is that  when we’ll use the self-join.  Then we have to use that table alias.  Because our table name is same,  because we are making a join on the same table.  So if you’ll see the syntax,  in the select statement we will mention that columns  which we are trying to fetch from the tables.  Then we have a keyboard from  which table we wants to fetch  and then the table alias T1.  Then join another table,  table name because the table name is same.  That’s why we have to use another alias  for the same table, that is T2.  And then we’ll provide a joint condition  with a on key word you mentioned T1.column name  equals to T2.column name.  So this index will be more clear  when we will show some examples of self-joins  and other joins.  So this is an example of a self-join,  where we are joining the same columns.  So we will see this example,  we’ll execute this example in the management studio,  but before that we’ll discuss other join as well.  And then I’ll show you the examples of our self-join.  It is not that we quite often execute self-join.  We execute self-join only in some rare scenarios.  Most of the time we tried to join two  or more than two tables.  So we have inner join.  The inner join fetches the record that have matching values  in both the tables.  So that is called self-join  or sometimes we call it as simple join also.  So here is the same text for the inner join,  we write the select statement or the column list,  we mentioned the from,  that from which tables we want to fetch the informations.  And then we provide the table alias.  It is not mandatory to provide the table alias  but it is good practice as sometimes,  the table name is very long  so we don’t have to write the table name always.  So to avoid this we provide small table alias  which will help us to identify,  which helps us to fetch the columns in the select statement  as well as in the on condition.  So let’s see an example of inner join first.  Let me close all this windows which are open  in management studio.  So for join, we will be using these two tables.  We will be using employee table.  Let me show you the data of the employee table.  This is our first table, employee table.  So we have the following records in it.  We have six rows and the column is employee id,  first name, last name and address.  In the address we have the city name.  For the fifth record,  we do not have the value for that address, it is null.  The other table which will consider to show you  the examples is orders,  and the order table,  let me first execute the single statement first.  So this is the order table.  We have five entries of five records in it  and employee id is null for the order id fifth  or for the fifth record.  Let me execute these two statements together.  So if you’ll see my orders table is the child table,  whereas the employee table is the parent table.  Employee id is the primary key  and order id is the primary key in this table.  And these two tables are joined by employee id column.  So when we join two tables,  there should be one common column  or there should be some matching values in both the tables.  If there is no matching values in parent and child tables  or two tables in which we want to apply joins,  then there won’t be any results and the result sets.  So the basic prerequisite is that  the two tables which we are going to join  should have some common values in between them.  So let’s take an example of inner join now.  We are clear with the two tables.  So this is this syntax.  In select statement we’ll provide what are the columns  which we are going to fetch.  So in this example we are fetching three columns,  one column from employee table  and two columns from the orders table.  So that is the reason we have explicitly mentioned  the table name, the dot followed by the column name in it.  So when we use joins,  it is important to provide the table name  before the column name.  Let’s assume what will happen if I’ll remove the table name.  See the moment I removed the table name,  I’ll get an error.  That ambiguous column name, employer id.  Why ambiguous?  Because if you’ll see we have employee id column  in employee table as well as in the orders table.  So if we will not provide the table name  then it is difficult for SQL server  to identify that which column,  we want to fetch as the same column  maybe present in both the tables.  But if I try to remove this table then I won’t get an error.  The reason is the order id column is unique.  It is present only in the orders table  and not in the employee.  But it is good practice that we will provide the table name  in the select statement to avoid any such issues.  I hope this is clear that why will provide the table name  before the column name the select statement.  Because if the column name is same in parent  and child table,  then it will throw an error  and we won’t be able to execute our SQL queries.  Because there will be an ambiguity for the same column name.  If the column is unique  and is not presenting both the tables,  then we have the options to skip the table name  before the column name.  So we provide the column names in the select statement.  Then we’ll first provide the name of our first table  followed by a keyword inner join.  So based upon type of joint which you want to create,  we’ll provide these keywords.  If it is left join,  then we’ll replace inner join with the left join.  If it is right joint than replace in a join with right join.  So based upon the join we’ll replace this keywords.  So as of now in this example  I’m showing you the inner join example.  So we have included the inner join keyword.  So if you’ll see we are basically joining two tables.  So one is employee, the other is orders.  So first we have to identify which two tables  we need to consider for join.  In our case we have considered employee, emp table  and orders table.  And the second part of join is that,  how we want to join these two tables.  As I mentioned, there should be some matching columns  or matching data between these two table  in order to join them.  So in both the tables employee id is common  and it has the matching data.  So we have to specify an on condition.  And then the on condition,  I have mentioned that emp dot employee  is equals to ordered dot employee,  fetch me all those records.  And then followed by the employee id.  As we discussed in module two,  we use order by keywords  or order by clause to sort the data.  By default, sorting is in the ascending order.  If you won’t provide any keyword and the order by clause,  it will be always be in the ascending order.  So let’s execute this statement first.  Yeah, so if you’ll see the the table data here,  we’ll be getting only four records here  because in the order tables,  we have only four records for the employee id.  Though we have six records in the employee table  and five records in the orders tables,  But matching condition is only for the four records  of four rows in both the tables.  In the result we will get one zero four employee id,  one zero two one zero five and one zero one.  Since employee id is not there in the orders table.  Also one zero six is not here.  That is a reason these two row  will be skipped from the result set.  As you can see,  there is no row with the employee id one zero three  and one zero six.  So in this example,  the inner joining is returning all rows  from the employee and order tables  where there is a matching employee id value  in both the employer and orders table.  That is the reason,  the rows where employee id equals two one zero three  and one zero six in the employee table would be omitted.  Since they are not present in both the tables.  So this the syntax which we are seeing as of now  on the screen is the latest syntax,  or the recommended syntax  or the (mumbles) syntax.  But there is an older syntax  where we do not use the on keywords  but still we can achieve the same result of join.  Let’s just have a look on the oldest syntax also  because some authors or some people choose oldest syntax  for the inner join and other joins.  But this index are not recommended  and if you’re writing joins  you have to follow the syntax  which we have discussed just now.  So in the oldest index  we just mentioned the name of the tables  and the tables will be the comma separated  and instead of on conditions  we include the condition in the where clause,  where employee dot employee id is equals to  order dot employee id, followed by the order by clause.  So it is not that every time when you’re writing joins,  we have to provide the order by clause.  Order by clause is always optional.  So if I’ll try to execute this query,  I’ll get the same result set.  And if you want to change the sorting order,  we can change it to the descending order also.  And if I’ll execute,  the result will be in the descending order of employee id,  as you can see.  105 is on the top.  Again, order by is always optional  and it is not mandatory to involve order by  an inner joins or any of the query.  So inner join helps us to fetch the rows  from both the tables which have matching value  in both the tables.  That’s why sometimes call it as (mumbles) joins  or simple joins or inner join.  So don’t get confused between these synonym terms.  Sometimes since we are matching the values which is common,  people call this (mumbles) joins.  It is the simplest of all the joins.  So we often call it as a simpler join, as simple join.  And technically this is inner join.  Since we are using the inner join keyword  to fetch themselves.  But in the oldest syntax,  we do not use inner join keywords.  It is good to know about the oldest syntax,  but it is not recommended  to use oldest syntax going forward.  Let’s understand more about inner joins  by helps of a Venn diagram.  So best we’re looking at this diagram  you’ll come to know,  so we have for example, two tables,  table employee and table department.  As of now I’ve shown you the examples from orders tables,  but let’s assume that we are joining two tables,  department and employee.  So in inner join the values which is common  in both the tables,  so the intersecting part will be fetched in the result set.  So better visualizations,  you can relate inner join with this Venn diagram example.  And if it is self join,  instead of these two tables we’ll join the same table.  When we saw the example,  when you see the syntax of inner join.  See that we’re using table alias here.  But in our case we haven’t used any table alias.  Using table is optional.  If I want to change to the table alias then I have to do,  for example, let’s say I’m writing it as E for employ  and orders for orders, O for orders table.  In that case cases I’m using alias,  I have to remove the table name  and have to replace with the table alias.  To sum up, if you use the table alias  then we cannot use a table name.  We have to replace with alias only,  not only the select statement,  we have to use all the places  for this not mandatory to use this.  We can either give the table in other table alias.  Generally we use table alias when there is a long name  for the tables.  Good practices is that do not provide the keywords  like how I have done E and O.  It has to be meaningful name.  For example, let’s assume the table name  is employee information.  So I can write the short form emp info,  just looking at alias we should understand that this alias  is for this particular table.  So if I’ll execute this statement again,  I’ll get the same results.  So if we are using self-join,  then it is mandatory to use the table alias  because, or it is good to use,  I would rather not say mandatory,  it is good to use because we will be using  the same table name.  If I will be using the self-join,  then if I’ll provide the same alias,  I cannot use it.  Because we cannot use the same table name  in both the clause.  So let’s assume that I’ll remove this table  then get an error because you cannot use  the same table name in the inner join,  as you can see the objects emp and emp in the from clause  has the same exposed names.  Use correlation names to distinguish name.  So my first statement was right,  yes, it is mandatory to use table alias  when you are using self-join.  So we can give it as E1,  or we can give the same table the other alias as E2,  and I can replace it with E1, E2 and E2.  These columns are not there.  So that is the reason it is giving an error.  So we’ll get this desire.  So this is how we create the self-join  or provide the syntax for self-join.  So it is mandatory to provide the table alias in self-join  because we cannot have the same table name  in the from clause or in the inner join.  I hope, self-join and inner-join is clear.  And let’s move to the next slide.  So this is again example of inner join  before we move to the next slide.  So in this we are joining the department table  and employee table, with the department number.  So we have provide the table alias,  for the department we provide the T1  and for employee T2.  And when we are providing the in the select statement  then we have to explicitly provide that which column  is coming from which table.  So first name is defined in the employee table,  but that is the reason they’re mentioned T2.first name.  And this is a good example,  here we are providing the alias  for column name as well as table name.  So alias is applicable for column name,  if you want to give a meaningful name for the columns  because first and sole name  it is good to use in the backend.  But if you want to show the results  and the result set in the query window,  or if you want to consume this result set  somewhere in our programming language  by meanings 80.net or any other means,  then it is good to provide them meaningful  and more readable names.  So that is the reason we provide column alias.  So let’s move to next join that is left outer join.  So the left outer join returns rows to the left,  even if there is no rows on the right of the join clause.  So the left and right is basically decided by  the arrangement of the tables in the on clause.  So this type of join,  return all rows from the left hand table  specified in the on clause.  And on conditions,  and only and only those clause from the other table  where the join fields are equal  join condition is met.  So I repeated it again,  as you can see the points.  Second, the result is null for the row on the right table  when there is no match.  When they execute right outer join,  it will always fetch the values from the left table.  And if there is no matching terms in the right table,  then those values will be replaced by a null keyword.  The syntax of left join is almost similar to the inner join.  The major differences is inner join is replaced  with another keyword that is left outer join.  Here the left table will be T1,  and right table will be T2.  So let’s see an example of left outer join.  So here are left table employee  and the right tables is orders.  So if I execute this statement,  this is the result set.  So the left outer join is basically returning  all the rows from the employee table.  And only those rows from the orders table  where the join fields are equal  or there is a matching condition.  So if the employee id value in the employee table  does not exist in the order table,  all the fields in the order table will display  the null value in the result sets.  In this result sets the rows where employee id  is 103 and 106 would be included the left outer join,  but the order id and order date will displace null.  So if you’ll see the data,  since we are using left outer join  and that left table is employee.  So it will fetch all the six records,  101, 102, three, four, five and six inner result sets,  because we are fetching three columns,  employer id, order id and order date.  So left outer join will include all the records  from the left table.  That’s why we are having six records in the result sets.  But if there’s no matching values in the right table,  so our right table is orders and order id  and order date are basically fetched from right table  that is orders, and then the order tables,  there’s no value for employee id 103 and employee id 106.  If you’ll see again in the orders table,  we do not have any rows with the employee id 103 and 106.  So those values are replaced by null here.  So that is the reason for the employee id 103  we have null values for order id and order date.  Which is coming from orders table  and that is the right table  in the left outer join in this example.  I repeat when we use left outer join,  all the values from the left hand side table  will be included in the result sets  and if there are no matching records  for those rows in right table,  then those values will be replaced by null.  In some databases, the outer keyword is omitted  and basically use left join.  So in some database instead of writing left outer join,  we simply mention left join.  And if I’ll execute the command again  I’ll get the same result sets.  So don’t get confused between left outer join and left join.  It is one in the same thing.  I hope left join is clear.  Let’s move to other examples.  So this is one more example of left outer join  before we move to the next join.  Here we have first name and department.  These are from two different tables.  So employee is our left table here  and department is the right table.  So we’ll always get all the records from the left table,  that is the reason if you’ll see in the first name  we have all the records,  but in the right hand side that is a department table.  For Miller, there is no value in the department.  There is no department number  which is there in the employee table  in the department table.  That is the reason the value  is coming as null in the result set.  All the values from the left table plus  all the values which have them matching values  and if there is no matching  than it will be replaced by null keyword.  So let’s move to the right outer join.  So right after joint returns the rows  to the right relationship table,  even if the is no matching rows on the left relation table.  It is almost similar to the left outer join,  only the conditions as reversed in right outer join.  That result is not for the rows on the left table  when there is no match, whereas in the left outer join  the result is null for the right table  when there is no match.  If you’ll see the syntax,  only difference here in the syntax is  in the right join we basically use right outer join  as a keyword, whereas in a left outer join  and you left outer join as the keywords for join.  So right after joint returns all the rows  from the right table specified in the on conditions  and only those rows from other table  where the join tables are equal will be fetched.  And as I mentioned in the point two,  if there is no match the results will be null  in the result sets.  Again, it is not mandatory to have the outer keyword.  We can simply use right join as well.  Don’t get confused between right outer join and right join.  These are one in the same thing.  So let’s see an example of right outer join,  or right join.  In this example, right table is orders,  so we’ll get all the results,  or all the rows from the right table.  As you can see, I’m fetching,  let me first use change order by clause  instead of employee id,  let me fetch it using the order id,  order by, order id and descending.  order by, order id and descending.  Let me execute the statement again.  As you can see we have sorted this using the order id,  the result sets.  Since orders is the right table,  we have all the records from the orders table,  but since there’s no matching records in the employee id,  we’re getting it as a null value.  So in this example,  the right outer join is there turning all rows  from the order table and only and only those rows  from the employee table where the join fields are equal.  So it is almost a reverse of the left outer join.  I hope this is clear.  So let’s understand left outer join, right outer join  by help of a diagram.  So if you’ll see this is example of left join example here,  bottom left.  So in this example we are considering these two tables,  employee and department.  So if you’ll see all the records from the table employee  will be included in the result set.  Whereas only those records from the department table  will be considered,  which are common in both the tables  or which has a matching value in the employee table.  So this is a good example,  or good example to understand the left outer join.  Same if you’ll see the right join example,  in the result sets all the records  from the department they will will be included  but from the employee table only those records  will be included which has a matching value  in the department table.  And the right bottom you can see this is an example  of a self-join, we are connecting to itself.  The table employee is connected to itself.  And as we mentioned it is very important  to provide the table alias if you are using self-join  because we cannot have the same table name  in both the conditions.  So I hope by looking at this example  it’s more clear to you the basic concept of join.  Other than that we just have to follow the syntax  and we have to take care about that which table  we are placing in the on condition  that is defined that which one will be our left table  or which one will be our right table.  So the placement of table in the query is very important.  If we are looking for left and right join.  If we are considering it as inner join,  then the order and placement does not make any sense  because we are going to fetch only those records  which have the matching values in both the tables.  So the placement of tables in the query is important.  If we are writing left outer join or left join  or right outer join or right join.  So let’s move to the next example.  Full outer join.  So the full outer join keywords returns all records  when there is a match in either left or right table records.  If there are rows in table one  that do not have matches in table two  or their rows in table T2  that do not have matches in table T1,  those rows will be listed as well.  It means all the rows from the left  and all the rows from right  will be included in the result sets  and if there is no matching values  that it will be replaced with null keyboard.  If you look at the syntax.  Syntax is almost similar.  We have to just replace the inner join  or left outer join with the full outer join here.  So it is very easy to work with the joins.  If we know the syntax of any one join  then it is very easy for us to frame the syntax  for other joins also.  All we have to do is that we have to replace  the main keyword, what kind of join we are working with.  If it is inner join we have to mention inner join,  if it is left then you can mention either left outer join  or left join.  Only thing which we need to consider  for this left and right join  is the placement of table on conditions.  So this is an example of full outer join.  So I’m selecting it as a first name  and department from employees T1,  full outer join department as T2,  and then there’s a join conditions  on department number on department.  So though we are getting the results from both of the table,  but still we have to provide the matching condition.  So if there is no matching condition,  then only the null value  will be included in the result sets.  So it is not that since we are you know,  fetching all the rows from both the tables  though we should not provide the join condition.  So let’s see an example of full outer join.  So we have all the records  and if there is no matching values  then we’ll have the null keyword.  So if you’ll see we have the seven records here,  when I execute left outer join then we have six records.  But here we’ll be getting the results from both the tables.  So there’s no matching values for order id five  in the employee table.  That’s why the first two is null here.  Same way there’s no matching really for employee id 103  and 106 in the orders tables.  That’s why the order id and order date  is null for these columns.  Again, we can omit the outer keywords from here  instead of full outer join,  we can simply say full join.  So we can always omit outer keyword from all these joins,  whether it is left, right or full.  So full outer join will return all the rows  from the employee table  and all the rows from the employee table.  Whenever the join condition is not met a null value  will be included in the result set.  So let’s understand it with diagram for better clarity.  So if you’ll see here in the full join example,  the entire area is colored because we are fetching  all the rows from employee  as well as all the rows from department,  including the matching values.  So if there is a matching values that  will be included in the result sets.  If not, null of will be included.  I hope this is clear.  Let’s move to the next join, cross join.  This is also called the Cartesian product  and it displays all rows and all columns of both the table.  So what do you mean by Cartesian products?  Let’s understand it.  If you’ll see in the upper right corner  we have the cross join example.  So one will be multiplied with all the three values,  one, two, three from the department table.  Same way, value two will be multiplied  with all the three values of the department table.  Same is the case with employee name three,  it will be multiplied with or.  So they will be Cartesian product.  That is why it is also called as Cartesian product.  So some people call it cross join  or some people refer to as a Cartesian product.  So let’s see or understand with this example.  See the number of rows.  we have only six rows and five rows in here.  So six into five since it’s the Cartesian product.  So we’ll be getting 30 rows.  So each row will be multiplied  with all the rows of another table.  We have six rules in the employee table  and five rows in the order table.  That’s why we’re getting 30 rules in the result sets.  So this is all about cross join.  So we have discussed all joins, right from simple joins,  inner join, left join, right outer join  or right join and full outer join.  All the examples which we have discussed,  we have not included any other,  you know where clause of group by clause or having clause.  So as of now we have not providing any filters.  We are just fetching all the rows even in the inner joins  we are fetching all the rows.  But in practical scenario it is not that every time  we need to fetch all the records  which have a matching value,  so we have to provide some conditions  or some filtering conditions to get the desired result sets.  So let’s revisit the inner join example.  So we can add a condition,  for example where emp dot employee id is greater than 104.  So only those records will be included.  So if you’ll see.  We have only one employee id which is greater than 104.  And which has a matching value with both the tables.  If I’ll make it 102, then I’ll get two values.  Because 103 and 106 do not have matching values  in both the tables.  If I’ll skip the where clause  and how it can skip by using a single line comments.  Now we have the four records in my result sets.  When including where clause I’m filtering the records  and the result sets to two.  So we can use where clause,  we can use order by clause  and on order by clause also  it’s not that we have to always provide the ascending.  We can provide the descending keywords also.  And let’s see what if I’ll instead providing  the column names here.  What if will provide the asterisk or star.  Lets execute this.  In that case I will be having all the columns  from both the tables.  When we used to provide the asterisk mark,  when we fetching the results from a single table,  then only the columns from that tables  would be included in the result sets.  Since we are creating joins or working on joins  and if we’ll provide asterisk in the select statement,  then all the columns from both the tables  will be included in the result sets.  As per the business requirement,  we include all the columns or the required columns  from both the tables.  Important part here is that as we discuss earlier also,  if the column is same in both the tables,  then we have to have a prefix of table alias.  Or else we will get an error that column is ambiguous  between two tables.  So far in all the examples which we have discussed,  or which we have executed in management studio  we have considered only two tables,  but it is not that we can always join only two tables,  if there’s a business requirement we can join three tables,  four tables and five tables.  The syntax is same, only thing is that we need to keep  on adding the other tables and the join condition  using the on keyword.  For beginner level you can start with joining two tables.  So the important criteria for joining two tables  is there should be at least one matching columns  between these two tables.  Some people say that you joined when there is a foreign key,  primary key relationship between two tables.  It is good to have primary key foreign  for key relationship between the two tables,  but it is not mandatory that if the two tables  are related using foreign key,  then only we can apply the joins.  We can very much apply the joins  if there is a matching columns between both the columns  though there might not be any foreign key  or even primary key.  The result in a result set will be exactly similar  if we’re joining two tables having foreign key, primary key  and other two tables which not have any such relationship.  Foreign key, primary key basically helps us  in adding constraints.  If we have some records in my child table  which is referring or which is referencing the record  in parent table, if you try to delete some record  from the parent table,  we’ll get an error that that record has been referenced.  So foreign key, primary key,  helps us in achieving referential integrity constraints  on our database,  but that is not a mandatory conditions to have  join in the tables.  Built-in functions are used to calculate values  and manipulate data.  These functions can be used anywhere as expressions.  When we say anywhere it means we can use them  in the select query,  we can use them in where clause,  we can use them in having clause.  So these built-in functions can be used anywhere  based upon the business requirement,  or based upon the type of functions which we are using.  So we broadly categorize the various built-in functions  into the following categories as you can see on the screen.  So we can divide the built-in functions  into conversation functions where we can convert the values  from one data type to another data type.  We have logical functions,  math functions to play around with the data,  mathematical or operations, we generally use math functions.  Aggregate functions, if you want to calculate  for example average, we want to calculate sum or total of  salary of all the employees.  Then we use aggregate functions.  A string functions helps us to play around  with various strings or characters in our data.  Most of the string functions are somewhat similar  to the string functions  which we have in other programming language  or the basic programming languages is like CC plus plus,  C sharp or Java.  Date functions have all the basic functions  which we can use to manipulate date  or a retrieve date from our database.  So let’s discuss all of the functions  one by one in coming slides.  As we discussed that these are the various categories again,  conversion functions that support data type  casting and converting.  Logical functions are scalar functions  that perform logical operations.  These are kind of similar to the math functions,  are again the scalar functions  which performs calculations  usually based on the input values  that I’ve provided as an argument.  And most of the times the returns are numeric value.  Aggregate functions, as I mentioned we use some views,  average, we use maximum of values,  so aggregate functions basically perform  a calculation on a set of values  and they return a single value except for the count.  So count is also an aggregate functions  other than count, all of that aggregate functions  ignore null values.  In the objective we saw that we are going to discuss about  the group by and having clause.  So aggregate functions are frequently used  with a group by clause of the select statement.  All we can say that we use group by clause  only with the aggregate functions, or group functions.  Some people refers aggregate functions as a group functions,  so don’t get confused.  They are one and the same thing.  String functions again as scalar functions  and the performance an operations on a string input value  and return the strings on numerical value,  based upon the kind of functions which we are using.  Date functions, straightforward it manipulates the dates.  So let’s start with the conversion functions,  as the name itself suggests that,  these functions helps us to convert an expressions  or a value from one data type to another.  So we have three commonly used conversion functions,  cost, convert and pass.  And in the bottom three you can see that we have try_cast,  we have try_convert, try_pass.  So the top three and bottom three  they give the same results.  They work in the similar fashion.  The only difference here is that the top three functions  returns value of specified data type  and on success throw errors on failure.  Whereas the bottom three,  they give the null values on failure.  So we can relate this try as  like how we have the try catch block  in other programming language to handle the exception.  Same way we used try_cast  if you want to get the null values if something wrong happen  while calculating the expressions,  or while fetching the results.  Same is the case with try_convert and try_pass.  But if any error happened while using cast converting  we’ll get an exceptions, or we’ll get an error.  Whereas error can be handled  by converting it to the null values.  If you use try_cast, try_convert and try_pass.  So as you can see on the slide we are converting the value,  then as int and we’re multiplying by 20.  Again, we are converting int, 10 to int.  And again we are doing a multiplication of the value  after conversion with 20.  So that what is the difference between cast and convert?  Because the end result is almost similar.  So both function can be used interchangeably  in most of the situations.  The only difference is that cast is NC SQL compliant.  It follows American National Standard Institute,  whereas convert does not follow any such compliance.  Let’s see an example of these basic operation.  So I’ll open management studio of SQL,  let open a new query  and let me copy the example.  So let me copy both the examples.  I increase the font size.  So if I’ll execute, I’m getting this result.  Since we are converting 10 as int  and again if you’ll see the result of cast function  and convert functions is exactly similar.  Only difference is in the syntax.  The syntax of convert function  is different from cast function.  So one important of point I just want to mention  here is that, in select operation  we can use mathematical operators like,  we can use plus, minus, divide and multiplications.  So here after converting 10 into int,  we are multiplying it with 20 to get the end result.  And this is the alias which we discussed in module two.  So we can change it,  we can change it to any value, we can change it.  Let’s say I’m writing it to cast result  and I can change it to convert result  and if I’ll execute it I’ll get the same results.  Cast results and convert result here are alias.  These are the alias for the columns.  In the model three we saw that we use alias for the table.  So let’s see that if there’s an issue while casting  or converting a desired value,  then we’ll get an error when we’ll use cast and convert.  But we’ll get a null value when we’ll use try cast  and try convert.  Let’s verify this using an example.  So I’m trying to convert character into int.  So we’ll get an error.  That conversion fail when converting the where care value A  two data type int.  Now let’s use try cast.  So I’ll copy this statement.  So let’s assume I used to try convert  and if I’ll try to convert a value which is not numeric  and if I’ll executed it we’ll get the incorrect syntax  near the keyword try convert.  Sorry, I forgot to write select.  And if I’ll execute it now we’ll get null value.  So instead of error, we are getting a null value  if you’ll use try convert or try cast.  So based upon the business requirement  we’ll choose either the try convert or convert.  Let’s move to the next function.  Let’s discuss about the logical functions.  So we have two logical function, choose and if.  Logical functions can be used to display  one of the several values based on a logical conditions.  As we have discussed, we have two logical function  choose and if.  Choose returns as specified index from a list of values.  And returns null if the index is zero  or greater than the number of items.  Or we can say that the choose basically act as index  into an error.  When an area is composed of the argument  that followed the index argument.  So as you can see in syntax,  we have the choose keyword  and in the parenthesis we have index.  So this is the index which we have to define  that what value we want to return.  And this val one, value two and we can have up to value n.  So let’s understand choose by an example.  So let me write one example,  so that could help us to understand.  Choose keyboards, and all these functions are basically,  can you see the color code?  For the keywords, we basically have blue colors,  but for this functions we have pink color.  So we’ll define three.  This is the index and then followed by the various values.  Let’s say I have test as of one value.  I have just as another value.  Third value I’ll be having rest.  So if I’ll execute this statement,  I should get the value rest because this index  did not start with zero.  Please note it down.  In the choose function, the index start from one.  This is one base index, not the zero based index,  like we have in most programming languages.  So if I’ll execute this statement,  I’ll get rest because we are instructing the SQL server  that we want the third value,  after the various values available.  The choose function basically gives an error.  If you try to use the index as the value zero  or we’ll try to provide the index  which is outside this range.  So let’s understand it by an example.  So if I’ll provide index zero,  if I execute this, I’ll get a null value.  Or if I’ll provide the index  which is outside the bounds of this error.  When I say bound, if it is more than the number of values  which we are trying to select from,  then again it will return a null value.  So this is their are only four values here  and I’m giving index five.  So since there’s no value add the fifth index,  it will return a null.  It won’t give us zero.  So choose functions helps us to select  from a given range of values,  like how we used to fetch the values from an array.  So let’s discuss the other logical function.  So here, before we discuss the if,  let’s see these examples as well.  So in the first example where using select choose  and we are giving index 2A, B and C.  These are the three values.  So this will, the first example will return the value B  in the results set because we’ll count from the index one,  as I mentioned, that choose is based on one index  and not the zero index.  So let’s take the case of the second example.  Here, we have not provided an integer.  If our fraction value 1.4,  so choose only consider the integer value.  In both the cases it will return A as in the result set.  So now let’s discuss the other logical function.  If so, let’s focus on this syntax first.  So it’s followed by if keyword  and then we have a boolean expression  and then true values and false values.  So when we exit this function,  it returns one of the two values.  Either it will then true value  or either it will return false value.  So the output, whether it is a true or false,  depending upon the evolution of boolean expression,  which we’ll provide in that if function.  So if it’s a shortened way of writing a case expression,  it evaluates a boolean expression fast  as the first argument  and then returns either of the two arguments based  on the results of the evolution.  So we can even provide the nesting in the if functions.  The nesting should be up to 10 levels,  similar to the nesting  which we can have in the case expression.  As it is almost similar to the case expression.  So let’s see our discuss the example of if logical function.  So in this case it will written false  because this boolean expression is not satisfying.  One is not greater than 10,  so it will fetch the result falls in the results set.  Let’s execute this example in management studio.  If you write if,  if then you have to write the boolean expression  than any value.  Let’s say I’m just writing true and the next venue,  if it turns out to be false,  let’s see example.  The example seems to be right.  Let me copy from here.  Okay, so we forward to add the select keywords.  Sorry about that.  And for I’ll execute it will return to false.  So since there’s no header for the column name,  we can provide an alias as well.  If I’ll make this statement as true,  if I’ll felt divorce the values here,  then I even get through as an output in my result set.  So, as I mentioned,  if works like a case expression  and we can nest it  and nesting can allowed up to 10 level.  So this is all about logical functions.  So let’s continue with other categories of functions.  So let’s just goes about the math functions.  So math functions can be used to calculate business  and engineering calculations.  So these are various self-explanatory  and we must have used these functions  during our engineering classes  or any other classes  where you do any engineering calculation.  For example, when we convert it  into an absolute positive value  then the use ABS functions.  If we want to generate a random float values  from zero to one,  then we use rand function.  Seed is an optional value here.  If you want to return an exponential value  than we use exp function,  in most of the mathematical values  we need to round up the values  when the values are of decimals than we use down functions.  So round returns and numerical value around it  to a specific length of positions  that we need to provide in the functions.  Floor provides or it returns the largest integer  less than or equals to the specified numerical expressions.  Then we have square root,  square root returns the square root  of the specified float value.  Saving is just opposite of the flow  and it returns the smallest integer,  greater than or equal to the specified numeric expression.  Square again, as we know the square, square of two is four.  So like we use the square functions  to calculate the square of the specified float value.  Power, power basically returns the value  of the specified expression to the specified power.  So these are the standard mathematical functions.  We have used these functions.  So here we can see a consolidated examples  of all mathematical functions.  So in the first example we see select ABS minus 10.  So here we are using absolute value.  So it is converting the negative values  to the positive value.  And all these keywords are we’re using as alias.  So we are using ABS as alias for the first column.  Rand as we discuss, Rand is basically helps us  to get any random values,  we can provide the seed as optional.  When you use this functions,  as you can see we are getting at random value here.  Exp four it basically helps us to get the explanation value.  Floor, we use floor so it will get the value.  4.66 will get the value four here,  and floor we’ll get the value less than the nearest integer  in the ceiling we’ll get the values  greater than the nearest integer.  If I’ll use floor 4.66 I’m getting the value four  when I’m using ceiling,  I’m getting very five here.  Square root, the square root of 2.56 is 1.6.  So this is the value we are getting.  Square is just opposed to the square root.  So we’ll get the square of a digit  we should provide in that parenthesis.  So we have provided a value four,  so we are getting 16 at this result.  Power to three, so we are basically giving power three  the base value here is the two  and three is it’s exponential value.  To the power three, we’ll get eight.  It’s states, if you own expand this,  it’s two into two into two.  So they’re getting eight as it results.  Round functions is basically it’s too round the values  and we are using two as a decision value.  So we are provided to it is basically rounded up to 2.57.  So let’s execute few of the examples in management studio  for better clarity.  Let’s use the square function  and square root function first.  The square root of 16,  and always use the select statement  or else we’ll get an error.  So I can even do like multiplications of various,  I can use where’s mathematical functions  and then I can multiply it  or I can use other operators like plus minus.  If I’ll execute this,  so what I’m doing is I’m taking the square root of 16  that will four, taking square four that is 16.  So end results should be 64, yep,  so if we get the results.  As you can see guys,  when we don’t give the name of the column.  The header is blank.  So it is good practice to provide the alias.  As of now I’m giving a standard result set.  If I’ll execute it now I’ll get 64 it’s value.  I can’t even use other mathematical operator,  I can use plus, so we’ll provide the sum  of these two values.  16 plus four, 20.  So I’ll get 20 in the result sets.  I’m getting minus 12 its value.  And let’s see, can we clutch the functions here?  So let me write, select  and if I’ll use apps function here.  So now I should be getting plus 12  because I’m using absolute function  so it will convert the negative value  into positive values.  So we can use, we can club, we can nest the functions.  So here in the ABS functions,  I’m using a security functions and square functions.  We can play around with these functions  as per our business requirement.  Let’s use an example of power.  Let’s use the base value as two.  Two to the power four.  So the results will be 16.  So this is equal to,  sorry,  if I’ll expand this power,  two is the base and four is the expansion.  So I’ll get the same result set 16.  So this is all about the maths functions.  That’s moves to another category.  Ignores null values.  Aggregate functions are frequently used  with the group by clause and for a filtering.  Like we used to filter the data using where clause.  So if you’re using group by clause,  then we use having keyboard to filter the result set.  So we have a following aggregate functions.  We have average min, sum, count and max.  Let’s start at average function first.  Average returns the average of the values in a group  as we discussed null values ignored.  So let’s take an example of average function.  First, let me fetch the values from department table.  Okay.  So I can use the average functions  on department number column.  So I can write select average  and write the column number from department.  If I’ll execute this,  I’ll get the average of the values.  Let me execute both the queries in one go  so we can compare the results.  So 25 is the average of 40, plus 30, plus 20,  10 divided by four.  So we can use average function with the distinct keyword.  We discuss about the distinct keyword in module two.  So distinct keyword helps us to fetch the unique record.  If you want to find the average of the distinct values,  then we’ll provide the distinct keyword  before the column name.  So let’s take another example of the average function.  Let me get the results from employee table.  So as we discuss that average functions  ignores the null values.  So if I’ll try to get the average of salaries.  So we have only three rows  where the salaries present, rest all the values undone.  And then let’s see what is the result.  Select average,  provide the name of the column, from employee.  So this is the average.  So let me execute both the statement in one go,  like I did earlier.  So you’re getting the average of this.  So we can use average with the distinct keywords  if you want to fetch the results.  Okay, let’s see whether we can apply the district here.  So in the department one table,  we have the repeated values for the department number.  So if we want to ignore that repeated or redundant data  or the duplicate data,  then I can use the distinct keyboard.  If I executed now, I’ll get the average as 25.  But if I remove the distinct keyword,  the result set will be different.  Sorry, my bad.  I’m using department column.  I have to change to department one.  So let’s first see the results that  without using the distinct keyword,  the results is 30.  Now I’ll use the keyword distinct.  Value is different because I’m just taking the unique values  for my result set.  So let’s move to another aggregate function.  Let’s discuss the min function.  Similar to average, min also ignores the null value  and if returns the minimum value in the expression.  So if I’ll see the values in the department one table,  and if I try to find the lowest value  of the department number,  then I have to use the main function.  Okay?  So out of all the aggregate functions,  count is the only one which considers  or which takes into account the null values as well.  So let’s see the example of count.  Let’s move to management studio.  So I’ll be using employee table  ’cause we have lot of null values in those.  I’ll execute this.  So let’s take the count of the salary column.  Select count.  Oh I have to write the column name, employee.  Oh I have to write the column name, employee.  So if I’ll execute, I’ll get three results.  But if I execute star, I’ll get nine columns.  But if I execute star, I’ll get nine columns.  Even we can use the distinct operation here,  in the count function.  So count basically fetch the count of rows from a table.  So let’s move to another aggregate function.  We have max.  So max is exactly opposite of min.  We’ve already shown an example of minimum.  So max, we can use it for numeric  as well as with the character data type.  If you use for the character data type,  it will show the maximum value,  or in the sorting order.  If you use with the numeric,  then of course it will show the maximum of the value.  Though let’s see,  an example of max as well.  So if I’ll use max,  we are trying to find the maximum salary  from employee table.  So this is the maximum salary.  And if I try to find the location  let me first show you the various location  from the department table.  So as for the sorting order,  we should get Hyderabad,  if you’ll use max for this column.  so let me write the query.  Select max loc from department table.  Select max loc from department table.  Then we can provide in alias as location.  So we get Hyderabad in my result set.  So we have to discuss all aggregate functions.  common aggregate functions are average.  If you get the average of values,  min as names suggest,  the is the minimum value in the expression.  Sum helps us defining of the total for a column.  Sum only accepts the numeric value count  returns the number of items or rows in a group  or in a table.  Max returns the maximum value in the expression.  So let’s move to other category of functions.  So these are the various examples,  before we move to other functions  lets see the examples here.  So in one example of shown all the aggregate functions,  so we can use multiple aggregate functions  in a single statement, a single select statement.  So in the single select statement,  we have used average of salary,  minium of salary, maximum of salary,  and sum of salary from employee table.  Second example is for the count functions.  So we will discuss about the group functions  and having functions,  once we are done with most of the outer box functions.  And then we have string functions.  String functions are against scalar functions  and they perform an operations on a string input value.  And the return is string or numeric value  based upon the type of functions which we are using.  When string functions are passed arguments  that are not string values,  the input type is implicitly converted to a text data type.  So these are the various string functions.  LTRIM, it basically returns a character expression  after it removes leading blanks.  It basically removes the blanks  from the left hand side of the string.  We have our RTRIM, it returns the character string  after truncating all the trailing blanks,  basically it remove the blank from the right hand side  of a string.  CHAR functions converts an int ASCII code to a character.  CHARINDEX find out, searches an expressions  for another expressions  and returns its starting positions if found.  STR returns character data converted from numeric data.  CONCAT, return a string that is results of concatenating  two or more string values.  So these functions are almost similar  to other string functions which we use  in other programming languages.  Most of the programming languages, we have trim functions.  We have our functions to find out the index of a character.  We use a concatenating and operation,  this is a very common string functions to concatenate.  Two strings, or more than two strings.  Then we have replace functions.  So replace all occurrence of a specified string value  with another specific value.  We have substring, it returns part of the character  or text based upon the length or that start index  which we give in.  As we can see that syntax of the substring,  we have a substring, a keyword followed by the expression  like the string from which we will want to find out  the part of a character.  We’ll define the start index.  And then we’ll define the length that from start index  till how many characters we want to fetch the substring.  Then we have the format functions.  We can format strings in the desire format  using the culture.  Then have left functions.  It returns the left part of a character string.  The opposite of left is the right functions,  which returns the right part of a character string.  Reverse, return the reverse order of a string value.  Upper functions at converts the string into upper case,  opposite of which is the lower functions which converts  the expressions into lower case.  And in the end we have length function or LEN,  which basically a returns the number of characters  of the specified string and exclude the trailing blanks.  So these are the various examples on our slide.  so first one is for the replace function.  So this is our a string  and this string,  we are replacing CDE with xxx as a replace.  As you can see in the results set here  for the replace column.  The value CDE is replaced with triple x.  We have sub string functions in the sub string.  We all get out of this ABCDEF,  which is our main string from index two  and (mumbles) we are getting the sub string.  So index two is start from B  and then up to three character we’ll get the sub strings.  So we’ll get B, C and D in our output.  Then we have left.  So basically written the left from a character index.  So from index two,  we are getting the values the values from the left,  it is A and B.  From the right, we are getting the value of B C D.  In the end we have the examples of date and time and format.  So here we are provided the various cultural values,  so forces for a US English,  second is for British English  and third is for the German.  So based upon the info culture,  we basically get the different results here.  So most of the string functions  are pretty much straightforward.  So let’s discuss about a few of them.  So we’ll discuss about a reverse,  so I’m adding a string, A B C.  Again, if I were to write the select statement,  it will give me an error  if I’ll try to execute this statement without select.  And if I’ll executed it now,  I’ll get the reverse of the string.  Always good to provide the alias.  Then we have trim on the LTRIM,  select LTRIM.  So I’ve added a leading blank spaces in this string  and if I’ll execute this,  that leading spaces has been trimmed.  Same way I can use RTRIM.  If I’ll use RTRIM,  that only the trailing spaces will be removed,  not the leading one.  If I execute this, I’ll get this result.  And if I’ll use trim, that will consider for both.  I do not have trim here.  So LTRIM removes the leading spaces and RTRIM removes  the trailing spaces.  This is all about the string functions.  You can practice about the other string functions,  they are pretty much straightforward.  Let’s see an example for the upper  and lower functions as well.  So if I’ll use lower here, I’ll get the same result.  So if I’ll use lower here, I’ll get the same result.  But if I’ll use, or let me convert, let me first write  the uppercase characters, it’s converting to lowercase.  And if I’ll write the lower case.  If I’ll use the upper function,  it can work it into uppercase.  So all other string functions are also self-explanatory.  Please try all of them so that you will have an idea  and if you will try all of these,  you will come to know that if the situations arise  you can start using those.  So let’s move to other functions,  the date and time functions.  So date and time functions are against scalar function  that performs and operations on a date and time input value.  And returns either a string, numeric or date and time value.  So as you can see in the screen,  they are various date and time functions.  Again, we have SYSDATETIME.  It returns a date time value that contains  the date and time,  its current time stamp,  against return at date time value  that contains the date and time.  Date part basically fetch as the date part  of a data component.  Day, month, year, returns an integer  that represents the day, month and year part  of the specific date.  DATEDIFF returns the number of date  or time datepart boundaries that are crossed  between two specific dates.  And DATEADD functions returns a new date time values  by adding an interval to the specific part.  So let’s see the examples of date and time functions,  since date time it returns the system date time.  It has date component as well as the time component.  Current timestamp also gives the current date and time.  Date part basically provides  if I’ll provide year here  then it is providing the year of the date part.  If I provide here, the DD,  it provides the day, if I’ll provide MM,  it will provide or fetch the value of the month.  Date different.  So again, if we, here we are providing MM  So we are getting the difference of month  between these two dates.  If I’ll provide DD  then it will provide me the difference of days  between the two dates.  So DATEADD is that I’m adding two months.  So this is how we have to do since we are provided MM.  MM specify for months here.  If I’ll provide the DD then it add two days,  since we have provided MM here.  So it will add two months to the current date.  So let’s even example here,  DATEPART.  Year.  Now let’s say, MM.  And then we can provide the date.  This providing the month.  So 12 months as I’m using December.  If I’ll change it to October.  And I’ll execute it again it will show me that value 10.  So this is how we use the data part  and let’s see the DATEADD function.  So if I’ll execute it now.  It will add two months.  I can also provide DD here.  So it will add two days,  this is all about the date functions.  So now let’s discuss about the group by clause  and having clause.  So as you can see, we are using group by keyword here  and we’re using having keyword here.  So let’s discuss about the group by  and having clause.  Lets discuss group by first,  the SQL group by clause is used along  with the group functions to retrieve data,  group according to one or more columns.  The important part here is that the group by clause  should contain all the columns  in the select list,  except those used along with the group functions.  So let’s see an example of group by functions  for better understanding.  Lets move to management studio  and we will use department one table for group by clause.  Let me first execute this query.  So what am I going to do,  I’m going to group by the department number.  So I’ll find select,  count of rows from department one.  count of rows from department one.  Then I’ll use group by keyword  and I want to group the data by department number.  So can you see it’s grouped,  or even I can write the department number  and let’s me write the alias for this  and I’m writing the alias here as  department number.  If I’ll execute this I’ll get,  so we have count four for the department number  there are four values for the department number 20.  Same way, five values for department number 30.  Five values for 40.  One values for department number 50.  One value for department number 80.  So we use group by functions generally in association  with aggregate functions.  It helps us in grouping the data in a table.  The important part here is that  we have to use all the columns in the group by  which are there in a select statement.  So if I’ll try to include location here,  there’s one more column locations  in that department one table  but if you’re trying to execute it now  I’ll get an error,  because all the columns  other than the aggregate functions  should be there and the group by clause.  If I’ll include the other tables as well  then the query will be executed successfully.  So to in order to execute group by successfully  we have to include all the columns  which we are including the select statement  in the group by clause.  And also we can include multiple columns  in the group by clause.  It is not that we have to use only the single column.  So group helps us to group the data in a single table  and it is generally used along with  other aggregate functions.  So let’s see the example.  So here we are basically again  grouping by the department number here.  So let me fetch the results from the employee table.  So here we can group the records again  using the department number.  Or using the job id,  so if I were to use for the employee table  I’ll write select job id, job_id.  I’ll write select job id, job_id.  And then again I can use for example, lets say count,  it’s not that we have to always use  the count aggregate function.  We can use other aggregate functions also from employee.  We can use other aggregate functions also from employee.  And then I’ll write, group by job id.  So this will group the data according to the job.  So only for a job id seven we have two rows,  for all other job ids we have a single row.  So we are clear with the group by,  now let’s discuss with,  lets discuss the having a keyword.  So the having clause was added to SQL  because the where keyword would not be used  with the aggregate function.  We cannot use a where keyword with this aggregate function.  So having applies to the summarize group records  whereas where applies to the individual records.  So for the summarize record or the group,  when we use group by then we have to use having,  and if it is the individual records,  then we’ll use the where clause.  If we’ll use the having clause,  only the groups that meets the having criteria  will be returned in the result sets.  And having keyword requires that we’ll use group by  in our SQL query.  So we can say that having clause is used in combination  with the group by clause  to restrict the group of returned rows  to only those which is matching that conditions  or where the conditions is true.  So lets see an example.  So here in this example  we are selecting the department number,  taking the average salary,  minimum salary and the maximum salary  and we are finding that if the having the sum of the salary  is greater than 3,000 fetches only those results.  So lets write,  let’s see this example in the management studio.  And I can write here having salary greater than  And I can write here having salary greater than  let’s say 30,000.  We have to include the, where the minimum salary,  you have to use the aggregate functions  in the having clause.  Yeah, so we get only two results.  So let me first show you the result sets.  So we have three rows which are having the value  for the salary column, J4 for having salary of 40,000.  G5 is blank.  So we have J6 as 500,000  and then we have the J1 as 30K.  So as per our query we should get only the two results.  That is J4 and J6 because these are the only two rows  which are satisfying the having clause.  So having this kind of we used to filter the result sets,  but with the aggregate functions we cannot use where command  so we are supposed to use having clause.  This is it about the various outer box SQL functions.  And first we’ll discuss about the stored procedure.  A stored procedure is a set of SQL statements  with a name that has been created and stored  in the database.  So then the execute or when we write SQL statement,  but we are not storing those queries.  But in store procedure we can write a set of SQL statements  and we can execute them and they can stored in a server.  So every time I don’t have to write SQL queries,  I can write my business logic and the store procedure.  So store procedures can be defined as a set of logical group  of SQL statements which are grouped to perform  a specific task.  So basically in store procedure  we write most of our business logic.  It’s like other programming languages.  Store procedures accepts input parameters.  They return multiple values by means of out parameters  or output parameters.  They contain programming statements.  A store processor can call other store procedures  or functions or return status  to indicate success or failure.  Let’s see the syntax of store procedure first.  So this is the syntax, we have create keyword.  To create a store procedure, we have to write create,  followed by a keyword procedure,  name of the store procedure which we want to create.  If you want to provide any parameter in the store procedure  then we’ll provide the parameter name.  If want to create the parameter as output parameter  then we have to use the keyword out or output,  followed by as keyword and we’ll write begin  and whatever the statement or competitions we want,  we’ll write that and the end we have write and keyword.  So in the syntax it is mentioned that it is create or alter.  So if you want to modify the existing store procedure  then we have to use the keyword alter.  If we are creating the store procedure first time,  then we have to use the keyboard create.  So alter keyword is used if you want to modify  any existing store procedure.  Now why we create store procedure  when we can write SQL queries.  So here are some of the benefits  of creating store procedure.  All commands in a store procedure  are executed as a single batch of code.  When we aggregate queries in SQL,  generally each query goes as a single batch.  But when we execute store procedure,  though it may have multiple SQL statement,  all SQL statement are executed as a single batch of code.  So by doing this it reduced the server  or client network traffic and improves the performance.  Store procedure helps us in achieving strong security in SQL  because we can impersonate another user,  execute as can be specified  to enable impersonating another user  to perform certain database task  without providing direct permissions to the procedure.  So it makes our environment more secure.  Reuse of code.  Store procedures helps us in achieving reusability  because as we discussed in the previous slides  that we can call another functions  or store procedures from one store procedures.  Easier maintenance.  Changes need to be done within the store procedure,  which handles all DB operations and no changes are required  in client applications.  So sometimes we move our business logic  to the store procedure.  So if any changes are required,  we need to modify our business logic  and we don’t have to change our client applications,  or our code.  We have to just modify the store procedures  and executed again.  Also if there is any error  we know where exactly to look for  and we have for development only one store procedure  instead of looking for multiple SQL statements.  That is the reason store procedures are easier to maintain.  Improved performance.  All procedures by default are compiled in the database  the first time it is executed  and it maintains that execution plan  for subsequent call.  So if we’ll execute or if we’ll call  or run the store procedure again  it will take less time  because there is already compiled in the database  the very first time.  So by doing this the performance of applications increase.  So on the slide we can see an example of store procedure.  So we creating a store procedure,  so we have writing a first,  the create keyword followed by the procedure,  sp_GetEmployeeInfo is the name of the store procedure.  This store procedure we are passing a parameter employee id  who’s datatype is int followed with a basic syntax.  We are defining the keyword as and begin.  And then we are writing the basic query  that select employee id, first name, last name,  manager id, phone number, high date, job id.  These are the columns of table employ.  And then we are passing that condition.  We are filtering the records from employee table  by using a where clause and the where clause  we are not hard coding any value.  We are executing it with an employer id,  which is the parameter.  So then we’ll execute this store procedure,  we will pass the parimeter.  So let’s see few example of the store procedure.  So let me open management studio.  Let me open the examples I already have.  So let’s create a store procedure without parameter first.  It is not that every time  when we’ll create a store procedure  we have to define a parameter.  So I’m writing the create keyword  followed by the procedure and all result keyword  will be highlighted in the blue color.  Then providing a name of the store procedure.  So I can provide any name here.  So I’m providing a get details or I can mention it,  give it more meaning, get department details.  And the store procedure I’ve simply written,  a one select query that select star from department.  And I’ll execute this query.  Command completed successfully  means we have successfully created  the store procedure in database.  Now where to check for the store procedure.  So I’ll first expand the database  under which I am creating the store procedure.  An important thing guys is that we have to use  the database first.  That under which database  we want to create a store procedure.  So always write the name of the database,  use keyword followed by a go keyword.  Now if we’ll try to execute this statement again.  I’ll get an error that this is already an object name,  USP get department details in the database.  So since I’ve already created this store procedure,  if I’ll try to create again,  it will give an error.  So when you provide a name of the store procedure,  please ensure or please check that this  the name which you’re providing is unique  and it does not exist in the database  in which you are creating that store procedure.  So if I want to check where exactly  the stored procedure has been created.  I have to expand the programmability folder  under programmability folder we have a separate folder  for the store procedures.  Let me refresh it.  So here we can see the store procedure  DBP USP get department details.  So from this object explorer,  I can modify the store procedure,  I can execute the store procedure  or even I can delete it.  So to delete it we have to simply click delete here.  Again, reconfirm whether we actually want to delete  this store procedure.  And if there are any dependency of this store procedure,  we can check it with the store dependency.  So when we delete something from our database,  it is better to check the dependency  if there are any independency,  where are the not delete that particular object.  As of now we know that there’s no dependency.  So I’m just clicking okay here  and it will delete the store procedure.  Since we have deleted the store procedure,  and now if I’ll execute this statement again,  it will be executed successfully  as there was no store procedure and database  and by execution of statement will again  create that store procedure.  So if I’ll refresh this stored procedure again,  I’ll see the store procedure.  Now we have created the store procedure  and we’ll see how we can execute the store procedure.  To execute the store procedure,  we have to write one command.  So there are various keywords  which we can use to run this as store procedure.  The first one is we have to write this keyword  exec followed by the name of the store procedure.  So if I’ll execute this,  it will execute the store procedure  and it will return the query or this result set  because in the store procedure  we have given only one select statement  that select star from department  and the department table has three columns.  So we are getting all the rows  because we have not specified any where condition.  So this is an example of a store procedure  without a parameter.  Since the store procedure doesn’t have any parameter,  we are not passing any parameter while executing  this as store procedure.  So it is not mandatory to provide that he exec keyword  while executing the store procedure.  Even if you’ll provide the name of the store procedure  and if we’ll execute it, it will fetch the same results.  So exec keyword or execute keyword is optional  while executing the stored procedure.  So let me again deleted this store procedure.  So while creating the store procedure again,  we can either provide this create procedure  or we can even provide the short form,  that create proc.  Even if I’ll execute the statement  it will be executed successfully.  And if I refresh this note,  we can see the store procedure  has been created successfully.  While executing again, it is optional,  provide the execute keyword.  Similarly, while creating the store procedure  we can either provide, create proc or create procedures.  Now let’s see how we can create a store procedure  with a parameter.  So I’m opening a new query window.  Increase the font size for better visibility.  So this is an example of a store procedure with a parameter.  So here we are providing the parameter department number.  So here we have department number as a parameter  and the data type of this parameter is nvarchar  and length is 30.  So in the previous example we have written  our select statement but without the where clause  here I’m providing or I’m filtering the record or rows  by using where clause and the where clause  I’m checking the department number  with the parameter which we’ll pause while executing  the store procedure.  So let me create the store procedure first  and then we will execute it.  The command completed successfully  means the store procedure has been created successfully.  So let’s again refresh this note.  Yup, so now when we are executing this store procedure,  even like as we saw that we can provide the execute keyword  or we can even directly  write the name of the store procedure  or if you’re using the management studio,  we can right click the store procedure  and then we can click execute store procedure.  So the execute store procedure will open this window  and if there are any parameter it will pass,  it will check whether what value we want to pass  to this particular parameter.  As we know that we have various values of department number,  start from 10 to 80,  so I’m passing 20 as a value for the department number  and I will click okay.  The moment I’ll click okay I’ll get this result.  So in earlier example,  we got all the rows in the result set.  So if we do not want to execute the store procedure  from the object explorer,  then again, we have to write the same command.  We have to execute keywords,  followed by the name of the store procedure,  and then we’ll pass the parameter.  In our case the parameter is department number.  So now this will give the desired result.  So this is how we create store procedure  with parameter and this is how we execute  the store procedure which has a parameter in it.  And now I feel try to execute this store procedure  without providing the parameter.  Now let’s see what happens.  See, you’ll receive an error that procedure of function  USP get detail expect parameters which was not supplied.  It means when we created the store processor,  we have ensured that the parameter  which we are providing in our store procedure is mandatory.  Why?  Because we have not explicitly  or defined that this parameter  can accept null value as well.  So if you want to make that parameter null,  then we have to explicit really provide that null keyword.  So let me modify the store procedure  or let me first delete the store procedure  and we’ll recreate it again.  So I’m just deleting this store procedure.  And in order to make our parameters optional  instead of mandatory we have to provide the keyword null  and I’ll execute the statement again  to create the store procedure.  Now if I’ll execute the store procedure,  we won’t receive an error,  but we won’t get the value  because when we are checking the null value  and if I’m not providing any null  so it will not return any result.  But yes, the error is gone.  So this is how we make the parameter optional  by providing the null keyword.  Now let’s see how we can create a store procedure  with multiple parameters  or is it possible to create a store procedure  with multiple parameters?  Yes, it is very much possible to create a store procedure  which accepts multiple parameters.  So let’s see it with an example.  Let me open a new query again  and we already have an example handy with us.  Let me paste it in management studio.  So we have this store procedure  and in this store procedure,  we have a defined two parameters,  one is department number and department name.  We are make both of these parameters as optional  by providing the null keyword.  So if you want to create  or if you want to define multiple parameters  in the store procedure,  all we have to do is that we have to separate  the two parameters with the comma, that’s it.  Rest everything is same,  we have to provide an as keyword after the parameters  and then we’ll write the store procedure in here,  we are again are writing a select statement,  but we are adding a where clause  to filter the results sets by providing two conditions.  And we are using logical operators  that is and, to combine two conditions.  So let me execute these statements  to create a new store procedure with multiple parameters.  The store procedure has been created successfully.  We’ll refresh this note.  And this is a store procedure.  So let me first see the data in our table  so that we can provide the right parameters values  to get the values and the result set.  Okay, so I’ll pass the parameter,  department number as 50  and department name as IT.  So all I have to do is,  I have to provide the keyword execute,  name of the store procedure get details from.  Then we have to provide the value for first parameter  that is department number, its value.  So we have decided to pass its value as a 50  and then we’ll provide the value for another parameter.  Since it is strength,  we have to enclose it as a single quotation  and now like I’ll execute this  and we’ll get the end result that department name is IT  and its located in Mumbai.  So this is how we’ll execute  and the store procedure with multiple parameters.  So we write the name of the store procedure  and followed with the first parameter  then write a comma and then the second parameter,  and so on.  In parameters also in other store procedure.  So if you want to execute it from the object explorer,  then you have to right click the store procedure  and click execute store procedure.  And we’ll get both the names,  we’ll pass the value here and we’ll click okay.  If I’ll click okay now,  though they won’t be any result in the result set.  But it won’t throw any error  because while you’re creating the store procedur,  we have defined that these two parameters  can accept null value.  So they are not mandatory.  These are optional.  That is the reason even though we won’t provide any value,  we won’t receive when while exiting the store procedure.  Now let’s discuss about the output parameter  in the store procedure.  So if we want to return the value of the parameter,  then we have to define an extra keyword  that is out or output.  Both keywords are valid, whether we use out or output  against the name of the parameter  whose value we want to return.  So let’s see it with an example.  So I’ll open a new query.  In this store procedure, I have two parameters,  one is the department number and one is count.  So we have defined key word output,  which suggests that this parameter  will be the output parameter,  so we can pass the value to this parameter.  And this parameter will also return  the value after execution.  So we can provide the keyword output or out.  There’s no difference.  The end result will be the same.  So lets create it,  we’ll refresh the note of the store procedure  to see the results.  Yeah, we have the store procedure.  Now in order to execute this, we have to do like this.  We have to first declare the count int,  followed by the executive name of the store procedure  will provide the value.  This is how we have to define if it is an output parameter  and then we are writing the select statement  to see the value of the count.  So let being execute this,  so the counties one because there’s only one row  with a department number 10.  So this is how we use the output  or out keyword against the paremeter.  Now let’s see how we can add comments  in the store procedure.  So we can add the comments in two ways.  One is the line comment and one is block comments.  So if you need to provide the line comments  then we have to use two dash, followed by a statement.  And in order to forward slash and then star,  and just writing, this is multiparameter, store procedure.  Having output parameter.  Again, write star, and a forward slash.  So this is how we defined the comments in a store procedure.  Not in the store procedure we can write  or define the comments.  Anywhere in SQL queries,  whether it is a functions or normal SQL statements  or it is a store procedure.  So line comments or single line, we use two dash,  and for block comments we used forward slash and star.  Let’s see how we can handle the exception  in the store procedure.  So if you have handle the exceptions  and your programming languages like CC plus plus  or Java or C sharp,  then we try catch there  and along try catch sometimes we use finally block  because finally always execute.  So if you want to close some,  like we want to close some connections  or we want that some statement to execute,  no matter whether there’s exception or not.  Then we write those statements in the final block.  So we do not have any final block  in the store procedure on SQL.  Let’s see how we can define that  try and catch blocks in store procedure with an example.  Let me first increase the font size.  I’m creating this store procedure,  the name DBO.USP try catch test  and to write a catch block  we have to provide the syntax begin try.  Then in the body will write whatever the statement  we want to write and then followed by the end try.  So end try basically ends that try block.  After that we’ll provide begin catch  and in the catch, see what we are doing here  is that we have returned a select statement  that we are providing what is the error number,  what is the severity of this error,  what is the state procedure  and which line the error isn’t in encounter  and the error message.  So let’s create this store procedure first  and then we’ll refresh this note.  So we have this store procedure  and now this store procedure do not have any parameter.  I’ll execute this store procedure,  execute keyword followed by the name of the store procedure.  No parameters, so we are not passing any parameters  while executing it.  And see since we are dividing by zero  it will give an error  and it results in infinity  and see since it will go to the catch block  since error has encountered.  So we are written the statement in the try block.  So in the catch we at handling the exception  and while handling the exception  we have written a select statement  which provide all these values.  So the error number is 8134, error severity 16.  So these are the predefined the severity levels,  so we can check on Google  that what the error severity 16 means.  Then we have error straight,  and which procedure the error has occurred.  So to give you the name of the procedure,  the name of the procedure is USP try catch test.  Error line is four, and the error message  that we are dividing by a zero.  So we will get the message  that divide by zero error encountered.  So this is how we mentioned try catch block  in the store procedure.  We have seen various examples of the store procedure.  So we have created the store procedure  without parameter, with parameter.  We have created to a store procedure  with multiple parameters.  We have seen that how we can you know,  include comments in a stored procedure using  single line comments, or block comments.  And we have seen that how we can handle the exception  of store procedure.  So this exception handling  is not only for the store procedure  we can handle these exceptions in functions as well.  So we can write, try block and followed by a catch block  in the catch block.  Again, we can write the select statement to show the message  that where exactly what the severity level,  what is the error message.  We can even provide the customer error message  it’s up to us.  So let’s move to the next topic.  So I hope you are clear with the concept  of store procedure in SQL.  Let’s move to the next topic.  The next topic is user defined functions.  So though SQL server provides  list of many predefined functions  that are built into the T SQL language.  These supplied or outer box functions  helps in extending the capabilities of SQL.  By providing the ability to perform  like string manipulations, mathematical calculations,  data type conversions by using  converter cost functions et cetera.  But sometimes these outer box functions  or inbuilt functions are not enough  and there certain functionalities  which is not provided by these functions.  So this issue can be easily addressed  if we can create some user defined function.  So like functions and programming language  and all programming language we write functions.  There are some functions which do not return any values,  they are functions which returns a value  and we pass parameters.  We do not pass parameters while you know  calling the functions.  So user defined functions in SQL  are almost on the same lines of the functions  in other programming languages.  So user defined functions are compact piece  of transact SQL code,  which can accept parameters  like how we pass a parameter in the store procedure.  Same way functions can also accept parameters.  Functions can perform complex calculations  and written either a value or a table.  So this written type is basically based upon  what kind of functions we are creating or executing.  So let’s discuss more of the various benefits  of user defined functions.  So again, like store procedure,  functions also helps us in achieving  the concept of reusability.  It will prevent us from writing same logic multiple times.  We can dump a set of SQL statement in a function  and we can call it as many as times.  Like store procedure they also improve the performance  because the user defined function  reduce the compilation time of SQL query,  by catching the execution plan and reusing it.  Functions that are also easier to maintain  because functions help us to separate  the complex calculations or the complex business logic  from the regular SQL query.  So that we can understand and debug  the query quicker and better.  It again reduce the network traffic  because of its catching plan.  Functions again can be used in where clause as well.  By this we can limit the number of rows  sent to the client.  So we can use functions and select clause  and we can use the functions in where clause.  Now let’s discuss about the structure of the functions,  user defined functions are composed of two parts.  One is header and one is function body.  We’ll see in some examples that how we create functions,  so the header contains the function name, input parameters.  If there are any input parameters  because again the parameters are optional  whether it was store procedure  or whether to the user defined function.  Along with parameter we have written parameter  in for its name and type in the header section.  The function body contains the logic  and it gives this logic  so it contains one or many a SQL statement  depending upon what we are writing in the function body.  As you can see on the screen,  there are if you need to divide the user defined functions  and some broad category  then we can divide into two broad categories.  One is that scalar value functions  and one is the table valued function.  The table value function can again  be divided into a line table value functions  and multistatement table value functions.  Let’s move to the next slide.  Let’s first discuss about  what are the scalar functions in SQL?  Scalar functions returns a single data value  after type defined in the written clause.  If it is an inline scalar function,  we even don’t need a functioning body.  We use scalar functions when multi statements  need to be provided within beginner end block.  So let’s discuss about the syntax.  As I mentioned that the syntax contains header and a body.  So the the create alter function,  it is basically the header of the functions  in the head or we can define the parameters,  the written type parameter.  And then in the beginning and end  we define the function body.  What are those SQL statements  or any competitions or any calculations which you are doing.  We basically do it in the body of the function.  So if we need to summarize,  the scalar functions are functions  which returns a single value  where written scalar values or single values  that can be used anywhere a constant expression can be used.  Every function, whether it’s a scaler  or whether it is like table value functions,  every function must return a result  using the written statement.  This is kind of mandatory.  So let’s see some examples of the scalar functions.  So in this example we are creating a function  with the name fn_ get department number.  So we are creating a parameter employee id  of the data type int here.  This functions basically returns int data type.  Then there’s a begin keyword,  after begin keyword we are defining the body.  In body we are writing select department number  from employee, where employee id is employee id.  Where at written employee id is the parameter,  which we’ll pass when we’ll execute this function  or we’ll run this function.  So in the right hand side,  we are executing this functions  and we are executing these functions in the where clause.  Lets pay attention.  In the where clause we are mentioning  where department number equals two  and then we are calling the function  by providing the parameter.  So when this function will execute,  it will be replaced by the value  which the function will return.  And then that value will be used in the where clause  to filter the data.  So let’s discuss some examples  of functions for better clarity.  Let me open management studio  that we close this queries first.  So we’ll first create a function,  a scalar function without any parameters.  So this is the name of the function, no parameters.  We are not creating any parameters here.  And the return type is int.  And I’m just returning in the return statement.  You have just provided a SQL statement  that selects some salary from employee.  After executing this, function will be created.  This is a scalar function.  So we want to verify whether the function  has been created successfully.  Then we have to refresh our database.  So we have created this function in employee DB database.  I’ll refresh that and I’ll expand the programmability.  In programmability note we have sub note called functions,  again functions, we have four notes,  table-valued functions, scalar-valued functions,  aggregate functions and system functions.  If I’ll expand the scalability functions,  we can see the name of the functions here.  So in order to execute this function,  we have to write select statement  followed by the name of the function.  If I’ll execute, we have to provide the parenthesis.  This function will return the sum of the salary  from employee table.  So let me show you the data  which is there in the employee table,  so that we verify a result  whether we got the right result or not.  So if you’ll calculate this we’ll get 5708.  This is the sum of the salary for all the employees.  Since we have three employees only.  So this is how we’ll create the functions  without any parameter.  Let’s see how we can create a functions with parameter.  So in this function we are providing two parameters,  first name and the data type of the parameter  is varchar 50, the second parameter is the last name  and the data type of second parameters is again varchar,  they’re they same length as the first parameter.  Return type is also of the same datatype.  And then in the body we are executing the SQL statement.  So what we are doing, we are just concatenating.  So if it is a mathematical operations,  if we provide the plus operator  then we’ll add the two values.  But if it is string of varchar  then it will concatenate the string.  Space is a function in, it is inbuilt function in SQL,  which basically add a space empty space.  So what we are doing,  we are concatenating first name and last name  and we are adding a space between the two names  to make it more readable.  So as you can see,  when I hoovering over the space a keyword,  it is giving me a message that this is a built-in function.  So let me create this function first,  function has been created.  Again, this is a scalar function, but with parameters.  Let me refresh the scalar-valued function notes  and I can see the function name.  Let me execute this function,  name of the function  and then we have to pass the parameters.  So I pass two parameters here,  and the name of the second parameter.  Why I’m enclosing it in single code  because this is a string type.  And if you want to add an alias for the column name,  we can mention it as full name.  So if I’ll execute it again,  we’ll get the results you wrote here.  So this is how we’ll create a functions  with multiple parameters and we can call the functions  in this select statement or we can, sorry,  call in the select statement, right?  And we can even call the functions in where clause  as we saw in the example in the previous table.  Again in the previous example,  let me first increase the font size.  We can even use the functions in the where clause.  So how we can use it,  let’s assume I’m calling this query select star  from employee and let me first alter this function.  So to alter this function we have to use the alter keyword  and instead of sum,  I’m just finding the average salary of the employee  and I’m executing again.  So this is how we modify the function, existing function.  Same way we can modify store procedure also.  So alter is the keyword which helps us  to modifying the existing functions  as well as existing store procedure.  If I have to use the functions in the where clause.  So this is how we’ll do, let me show you.  So I’m writing a query,  so what I want to do is that I want to  select the employees whose salary is greater than  the average salary.  So I’ll write where clause where salary is greater than,  then I’ll call the function.  So if I execute it now,  I’ll get only one result because there’s only one record  or one employee who’s salary is greater than  the average salary of all the employee.  So this is how we can include  or we can call the function in where clause  as well as in the select statement,  as per our business requirement.  So let’s move to the next topic.  So we are clear with the scalar functions I believe.  So let’s move to the next type of function  that is the table-valued functions.  And as I mentioned earlier,  we have two types of our table-valued function.  One is inline, one is multi-valued.  So inline table value functions are a subset  of user defined function that returns a table.  So scalar functions returns only single value,  whereas the inline table value functions  returns the entire table.  So inline functions, if you remember,  we have created view in the first module.  So we can use use inline functions if you want achieve  the functionality of that parameterized view.  Inline table valued function can also be used  to support parameters in search condition  specified in the where clause.  So these are the some of the rules which are common  for the inline table-valued functions.  The return clause contains only the keyword table.  Format need not to be defined by the return value.  There’s no function body do you delimited  by begin and an end.  The return clause contains a single select statement  in parenthesis and a table-valued function  accepts holy constraints or the local variable arguments.  This is the syntax for inline table-valued functions  as you can see in the slide.  So the return type is table here  and we generally have the SQL statement.  So this is an example, in the last slide we saw the syntax,  this is an example.  So here in this example we are creating a function  get employee info.  You’re passing the parameter employee id  and it is returning the values as a table,  not as a single value.  And the return statement  we are providing the select statement  where we are returning the values of employee table  and we are filtering the result sets  by using the parameter employee id.  And this is how we’ll execute.  So if you’ll remember when when we executed  the scalar-value function,  I did not use select star from,  we simply mentioned select and the name of the function.  But if you’re using table-value function  then we are using the select star from,  because we are kind of as we mentioned,  we kind of use it what parameterized view.  So if you need to fetch the values from views,  then again we use select star from the name of the view.  So let’s see an example of inline table-valued function.  Let me open management studio.  So this is example of inline functions with no parameter.  We can create parameter in the same way we created  the parameter or we defined the parameters  for the scalar-value functions.  So I’m creating a function, top 10 customers.  We are returning a table.  And in the written statement  I’m just writing a select statement,  select top 10 from employee,  let me execute this to create this function.  I’ll execute this function.  So to execute this I have to mention the select plus,  select star from and then the name of the function.  So I’m getting, so there are only nine rows,  so it’s just fetching the nine only.  So if I can go out and modify it  instead of top 10 let me modify to top five.  So to modify the function we have to use the alter keyword.  So we got the top five results.  This is how we create the inline table-valued function.  So this is an example of the functions  without any parameters,  if you required we can go ahead  create the parameter as well.  So to create the parameter  we have to define the parameter name here  and then we can have to provide the name of,  data type of parameter.  And then we can write a where clause.  Let’s say you employee id is, (mumbles) employee id,  command executed successfully.  And now if I were to run this,  I’ll pass a value to the functions.  if I’ll execute it will get the values  and the result sets  who’s employee is greater than 1003.  I hope this is clear and let’s move to the next topic  So I’ll discuss the next category of table-valued function.  That is the multi-statement.  So in the inline function we can use a single statement,  but in multi-statement table valued function  we can use multiple statements.  So again they also return a table data type  so the return clause defines the format of the table.  We cannot provide the format and the in the inline  table value function.  But yes we can provide the format of the table  in multi statement table-valued function.  That transact-SQL statement in the function body  build and insert rows into the return variable  defined by the written clause.  And the rows in certain into the variable  are return as the table output of the function.  So here we have the syntax for this,  to modify we use the alter keywords,  to create we use create keywords,  name of the function if you wish.  We can define the parameters which you can consume  in the function body.  Written type will always be the table,  but here we can define the format which is not available  in the inline table-valued function.  Let’s see an example.  So this is an example here.  So we are defining the format of the table  and we are defining like that this is the table  will be the retGetEmployeeInfo  and we are providing four columns here  and we are defining the data type.  We’re defining the constraint on the table,  we are defining whether there’s a null constraint  or not on those columns.  And in the function body  we are inserting records in the table  and we’re fetching from the employee  and we’re filtering the result set  by using the parameter which will pass  when we’ll execute the function.  So lets see one more example other than this.  So you can use the same example and executed it  and try to write the entire thing then it will take time.  So I’m skipping the example in management studio,  but the basic difference between these two functions table  will function is that  here we can format the table  and we can use multiple statement in the function body.  Which is not the options in the inline functions.  So we already discussed the benefits of  user defined functions, modular programming.  It saves time because we can write or dump the statements  in a function and use it or call it  as many as number of times.  It’s faster executions because of this,  it catches the exhibition plan  and use it for the subsequent calls  and it reduced the network traffic.  So these are few of the main benefits  of user defined functions.  So we discussed the user defined functions  and I hope you are clear.  So we can extend the capability  by writing user defined functions  and we can achieve the functionality  which cannot be fulfilled by outer box  or the in-built functions of SQL.  Now we’ll understand what triggers us,  as the name suggests, it’s some database object  which triggers automatically.  How we can say that trigger is a special kind of  stored procedure that automatically executes  when an event occur in database.  So we have various type of triggers.  Let’s first see the syntax of the trigger  and then discuss that what are that different types  of triggers available in SQL.  So we have DML triggers, we have DDL triggers,  lets discuss what are the DML triggers,  what are the DDL triggers?  And then we’ll discuss about the syntax.  So in both category we can define the triggers  into two major types.  One is that DDL at triggers,  so DDL stands for the data definition language  which for discussion in the module first where we created  few database objects,  so those triggers as the name suggests,  data definition language they file in response  to the DDL command that starts with create or alter  or the drop of that database objects.  Like if I’ll create a table, if I’ll create a view,  if I’ll try to drop a table or try to drop off view,  or if you want to alter any table,  then the DDL triggers  will be automatically fired or triggered.  The other type of triggers are DML triggers,  so they file in response to  the data manipulation language commands  that start with like if you want to insert some data  in the table, if you want to update some records,  or if you wish to delete some data  from the existing database objects.  So based on the commands these triggers get fired.  If we executing the DDL commands,  then DDL triggers will execute  and if you’re executing the DML commands  then they will trigger will get fired.  We can further divide the DML triggers  into two types, after triggers and instead of triggers.  Let’s discuss what are these after triggers  and instead of triggers.  After triggers and instead of triggers,  let’s see an example of DDL triggers  and see how they get executed.  Let’s open studio, open a new query,  increase the font for better visibility.  Open our example sheet.  So let’s first create a DDL trigger.  So I’m creating a trigger called safety on database  for create table, alter table or drop table.  So if I’ll try to create a table in this database,  if its create table, alter table or drop table.  So it really print a message.  To print a message in SQL we basically use print keyword  and if it is string we’ll enclose as in our single codes,  whatever the message we want to print  and the rollback as we discussed that is the DCL command.  So it will rollback, that changes it won’t be saved.  So let’s execute the statements to create this trigger.  That’s the trigger has been created successfully.  So that’s all about triggers.  And these are some of the benefits  it provides alternate matters  for implementing referential integrity constraint.  So if you want to achieve a referential integrity constraint  we have to call,  instead of triggers it restrict or controls updates  on the database objects and can be used  to publish information about the database events.  Like the startup and shutdown,  so we can call the triggers based on this  and we can publish the information’s  because we don’t have to explicitly call,  there’s no manual intervention.  These are auto trigger events.  So these are some of the benefits of the triggers.  Hope you enjoyed this course and happy SQL learning.  – [Announcer] Alright,  so thank you for the great session, Anil.  I hope all of you found it informative.  If you have any further queries related to the session,  please comment in the comment section below.  Until then, that’s all from our side today.  Thank you and happy learning.  I hope you have enjoyed listening to this video.  Please be kind enough to like it  and you can comment any of your doubts  and queries and we will reply them at the earliest.  Do look out for more videos in our playlist.  And subscribed to Edureka channel to learn more.  Happy learning.  

admin

Leave a Reply

Your email address will not be published. Required fields are marked *