SQL Tutorial for Beginners | SQL Tutorial | Intellipaat


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

100 Replies to “SQL Tutorial for Beginners | SQL Tutorial | Intellipaat”

  1. Sir, please make a video comprising complete guidance for XML(from Excel) & TDL creation on our own for export purpose

  2. 👋 Guys everyday we upload high quality in depth tutorial on your requested topic/technology so kindly SUBSCRIBE to our channel👉( http://bit.ly/Intellipaat ) & also share with your connections on social media to help them grow in their career.🙂

  3. I have a question on sql
    Display name, salary,city from salary order by salary, if salary is same then by city in descending order if city is null show Na.

  4. Your Tutorial is awesome man.

    I got the ideas of the concept better and learned more about MSSQL in these Hours than in the Entire Semester.
    It's really really good. I Subbed for sure, cause I feel I can learn more from you and please, please keep up your good work!

  5. Hi, While connecting to Sql Server why we need to put "DESKTOP" as a prefix of the server name? what does it mean there in Server Name: "DESKTOP-Server nameIGNEOUS "?

  6. Hi sir, Thank you very much. Is this just a basic course or complete course, Can we join SQL Jobs after perfectly learning this?

  7. I am not able to install Microsoft SQL server management studio….it says please install visual c++ 2017 distributable and i did from the given link…but it is still saying the same thing when i restart and try to install the management studio again

  8. hi sir nice vedio we enjoyed a lot plz once tell me this doubt
    // Msg 8152, Level 16, State 14, Line 2

    String or binary data would be truncated.

    The statement has been terminated.

    Completion time: 2019-09-30T11:17:48.5802412+05:30
    //
    error will be like this .i had create an table .i had insert some values after some time again it will come like this. so please clarify this pllz ……………………………………

  9. Awesome video on SQL on YouTube.

    Sir plz tell me the full form of dbo which we use while calling a user defined function. And why we use it?

  10. Great work.i learned the concepts clearly. I spent almost a month to learn SQL but i was not clear in concepts. Keep doing

  11. hi, it is really useful info. but ,unfortunately I'm not able to login,i.e. my SQL Server is not getting connected.May I know what could be the reason? What should be the login id and password?

  12. Sir please tell me ,, Jo SQL Apne install kiya hai kya vo abhi b chal Raha hai ya Nahi???? And kya password Chahiye Hoga after installation????

  13. Sir plz make a video on java. And java frameworks spring and hibernate. Teacher for java are not good. Sir you plz make a video on java.. Your teaching technique is outstanding sir.

  14. This is the one-stop point for SQL learning!!! Thank you so much to share in-depth knowledge in simplest way for all levels of students.

  15. Vb.net configuration with Ms SQL server 2017
    which edition is more compatible of Ms SQL server Developer or express with vb.net?

Leave a Reply

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