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