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.
