SQL stands for Structured Query Language. It is mainly used for data manipulation, data modification, and data retrieval. This comes around with Relational Database Management System (RDBMS).
We will learn about more advanced features of SQL like Joins and Functions.
How to work with SQL Joins
A simple join means is to combine two or more tables in a given database. A join works on a common entity of two tables.
A join contains 5 sub-joins which as; Inner join, Outer Join, Left Join, Right Join and Cross Join.
Inner Join
An inner join is used to select records that contain common or matching values in both the tables (Table A and Table B). Non-matching are eliminated.
So, let’s understand the type of joins, and with common examples and the differences between them.
Table 1: Employee Table (tblEmployee)
Table 2: Departments Table (tblDepartments)
So, let’s create table tblDepartments for execution of a program.
Now, Insert records into table tblDepartments.
Let’s create another table tblEmployee for the execution of a program.
So, Insert records into table tblEmployee.
Therefore, a general formula for joins.
To make a query to find Name, Gender, Salary and DepartmentName from both the tables tblEmployee and tblDepartments.
Note: JOIN or INNERJOIN means the same. But always better to use INNERJOIN, and this specifies your intention explicitly.
Output: Now the final output table will look like this;
If you look at the output window, we have got only 8 rows, but in the table tblEmployee, we have 10 rows. We didn’t get JAMES and RUSSELL records. This is because the DEPARTMENTID, in table tblEmployee is NULL for these two employees and does not match with their ID column in table tblDepartments.
So, in a final statement, Inner Joins return only matching rows from both the tables and non-matching rows are eliminated due to its subquery.
Left Join
LEFT Join returns all the matching rows and non-matching rows from the left side table. In addition, Inner join and Left join are extensively used each other.
So, let’s take an example, I want all the rows from the tblEmployee table, including JAMES and RUSSELL records. Then the output will look like as;
Right Join
RIGHT Join returns all the matching rows and non-matching rows from the right side table.
So, let’s take an example; I want all the rows from right tables involved in the join. As a result would be like;
Full Outer join
OUTER join or FULL OUTER Join returns all rows from both the left and right tables, and including the non-matching rows from the tables.
So, let’s take an example; I want all the rows from both the tables involved in the join.
Cross join
This join gives the Cartesian product of the 2 tables in the join function. This join does not contain ON clause.
So, let’s understand an example: In the tblEmployee table we have 10 rows and in the tblDepartments table we have 4 rows. So, a Cross joins between these 2 tables produces 40 rows.
How to work with Advance SQL Joins
In this session, I will explain these things as follows;
Advanced or intelligent joins in SQL Server.
Retrieve data only the non-matching rows from the left table.
Fetch data only the non-matching rows from the right table.
Retrieve data only the non-matching rows from both the left and right tables.
So, let’s consider both the tables tblEmployee and tblDepartment.
Left Join
So, let’s understand an example, I want to retrieve only the non-matching rows from the left side table.
Output: Finally, the output will look like this;
Right Join
So, let’s understand an example, I want to retrieve only the non-matching rows from the right side table.
Output: Finally, the output will look like this;
Full Outer join
So, let’s understand an example, I want to retrieve only the non-matching rows from the right side table and left side table and matching rows should be eliminated.
Output: Finally, the output will look like this;
Types of Keys in SQL
A Key in SQL is a data field that exclusively identifies a record. In another word, a key is a set of column(s) that is used to uniquely identify the record in a table.
Create relationships between two tables.
Maintain uniqueness and liability in a table.
Keep consistent and valid data in a database.
Might help in fast data retrieval by facilitating indexes on column(s).
A SQL server contains keys as following;
Candidate Key
Primary Key
Unique Key
Alternate Key
Composite Key
Super Key
Foreign Key
Before going ahead, and please have a look at the image below;
Let’s understand each key in details
Candidate Key
A candidate key is a key of a table which can be selected as a primary key of the table and a table can have multiple candidate keys, therefore one can be selected as a primary key.
A primary key is similar to selected candidate key of the table to verify each data record uniquely in the table. Therefore, primary key does not contain any null value in any of the columns of a table and it also keeps unique values in the column. In the given example, Employee_Id defines primary key of Employee table. Consequently, in SQL Server Management Studio, Primary key creates a clustered index on a heap table by default and a table which does not consist a clustered index is known as a heap table. Whether defines a nonclustered primary key on a table by type of index explicitly.
Furthermore, a table can have only one primary key and primary key can be defined in SQL Server using SQL statements:
CRETE TABLE statement (at the time of table creation) – as a result, the system defines the name of the primary key.
ALTER TABLE statement (using a primary key constraint) –as a result, User itself declares the name of the primary key constraint.
Example: Employee_Id is a primary key of Employee table.
Unique Key
A unique key is much as the primary key and which does not contain duplicate values in the column. It has below differences in the comparison of the primary key:
It allows one null value in the column.
By default, it creates a non-clustered index and heap tables.
Alternate Key
The alternate key is similar to candidate key, But not selected as a primary key of the table.
Example: License_Number and Passport_Number are alternate keys.
Composite Key
Composite key (also known as a compound key or concatenated key) is a group of two or more columns that identifies each row of a table uniquely. Furthermore, A single unit column of a composite key may not be able to uniquely verify the data records. As a result, It can be either primary key or candidate key also.
Example: In the table, Employee_Id & Salary_Month_Year both columns verify each row uniquely in Salary table. Therefore, Employee_Id or Salary_Month_Year column in the table, which cannot identify each row uniquely. We can create a single composite primary key on Salary table by using Employee_Id and Salary_Month_Year column names.
Super Key
Super key is a set of columns on which all columns of the table are functionally dependent. Due to the set of columns that uniquely identifies each row in a table. In another word, this key holds few additional columns which are not strictly required to uniquely verify each row in the table. Seems like, Primary key and candidate keys are minimal superkeys or you can say a subset of superkeys.
So, let’s look at above example, In the Employee table, column name Employee_Id is hardly sufficient to uniquely verify any row of the table. So, that any set of a column from Employee table which contains Employee_Id is a superkey for Employee Table.
For example: {Employee_Id}, {Employee_Id, Employee_Name}, {Employee_Id, Employee_Name, Address} etc.
License_Number and Passport_Number are the columns name, it can also uniquely verify any of row of the table. Anyone of column name set which consists License_Number or Passport_Number or Employee_Id is a superkey of the table.
For example: {License_Number, Employee_Name, Address}, {License_Number, Employee_Name, Passport_Number}, {Passport_Number, Employee_Name, Address, License_Number}, {Passport_Number, Employee_Name}, {Passport_Number, Employee_Id} etc.
Foreign Key
An FK defines the relationship between two or more than two tables at a time. A primary key of a single table is referred to a foreign key in another table. A foreign key can have duplicate values in a table and also it can have null values if the column name is defined to accept null values yet.
For Example Column name “Employee_Id” ( which is a primary key of Employee table ) is a foreign key in Salary table.
Note: Keys like primary key and unique key creates indexes with keys columns. Organized data in B-Tree structure node (Balanced Tree: Leaf nodes are all at the different level from the root side) in SQL Server. Hence, Nonclustered index creates a separate structure from the base data table but clustered index converts base data table from heap structure to a B-Tree structure.
In addition, the clustered index does not create a separate structure apart from the base table and that is the reason we can create only one clustered index on a table. Hence, we can sort a table in only one way (it may have multiple columns to sort but sorting can be done in one only way) which is the order of the clustered index.
How to work with SQL Functions
A Function is an entity program which is stored in the SQL Server Database either you can pass parameters into or return a value. Furthermore, we will look forward to some very useful Built-In function and User-defined functions.
Coalesce Function
Coalesce() : This function return only coming first Non NULL value. So, let’s take an example over Coalesce() function.
Let’s understand the table as name ‘Employee’ above. As a result, you can see some of the employees have their First name missing, some have a Middle name and some of them have Last Name missing. So, I want to return only “Name” of the employee.
How will it work? Understand, we are processing FirstName, MiddleName and LastName columns as parameters to COALESCE() function. Hence, this function will return the only first non-null value from 3 of the columns.
Query: Select Id, COALESCE(FirstName, MiddleName, LastName) AS Name FROM tblEmployee
Finally, the output will look like this;
LEFT() Function
LEFT(Character_Expression, Integer_Expression) – This function returns the specified number of characters from the left-hand side of the given character value expression.
Example: Select LEFT(‘ABCDE’, 3)
Output: ABC
RIGHT() Function
RIGHT(Character_Expression, Integer_Expression) – This function returns the specified number of characters from the right-hand side of the given character value expression.
Example: Select RIGHT(‘ABCDE’, 3)
Output: CDE
CHARINDEX() Function
CHARINDEX(‘Expression_To_Find’, ‘Expression_To_Search’, ‘Start_Location’) – This functions returns the starting position of the specified value expression in a character sub string. Start_Location parameter is optional.
Example: Let’s understand, we make the starting position of ‘@’ character in the email string ‘[email protected]’.
SUBSTRING(expression’, ‘Start’, ‘Length’) – This function returns substring (subpart of the string), from the given value expression. In addition, when you specify the starting position using the ‘start’ parameter and the other number of characters in the substring using ‘Length’ parameter. All three of the parameters are mandatory.
Example: I want to display just domain part of the given email ‘[email protected]’.
As a result, we made the coding with the starting position and the length parameters. Instead of hardcoding the parameters, we can dynamically fetch them using CHARINDEX() and LEN() string functions as shown below.
So, let’s take an real example with the use of LEN(), CHARINDEX() and SUBSTRING() functions. Let’s think we have a table as shown below;
So, the question is How will you find the total number of emails by their domain.
Output: Finally, the output will look like this;
REPLICATE() Function
REPLICATE(String_To_Be_Replicated, Number_Of_Times_To_Replicate) – This function repeats the given point of the string, and for the specified number of times.
Example: SELECT REPLICATE(‘Pragim’, 3)
Output: Pragim Pragim Pragim
Let’s talk about a practical example of using REPLICATE() function: We will be using this table most of the time, and for the rest of our examples in this article.
So, let’s suppose we have a table as shown below;
Query: Select FirstName, LastName, SUBSTRING(Email, 1, 2) + REPLICATE(‘*’,5) + SUBSTRING(Email, CHARINDEX(‘@’,Email), LEN(Email) – CHARINDEX(‘@’,Email)+1) as Email
from tblEmployee
Let’s make email with 5 * (star) symbols. Then, the output would be like this
SPACE() Function
SPACE(Number_Of_Spaces) – This function returns the only number of spaces, and specified by the term Number_Of_Spaces argument.
Example: The SPACE(5) function, It will inserts 5 spaces between FirstName and LastName
Select FirstName + SPACE(5) + LastName as FullName From tblEmployee
PATINDEX() Function
This function only returns the starting location of the first occurrence of a pattern in a specified effective expression. Hence, it takes only two arguments, and the pattern to be searched and the expression. Therefore, PATINDEX() is similar to CHARINDEX(). With CHARINDEX() we cannot use wildcards, while PATINDEX() involves this capability. If the specified pattern value is not found, PATINDEX() returns ZERO.
Example: Select Email, PATINDEX(‘%aaa.com, Email’) as FirstOccurence from tblEmployee where PATINDEX(‘%@aaa.com’, Email) > 0
REPLACE() Function
REPLACE(String_Expression, Pattern, Replacement_Value), This function replaces all occurrences position of a specified string value with another string value.
Example: All .COM strings are replaced with .NET
Select Email, REPLACE(Email, ‘.com’, ‘.net’) as ConvertedEmail from tblEmployee
STUFF() Function
STUFF(Original_Expression, Start, Length, Replacement_expression), This STUFF() function only inserts Replacement_expression, which is specified at the starting position, along with removing the characters specified using Length parameter value expression.
Example: Select FirstName, lastName, Email, STUFF(Email,2,3,’*****’) as StuffedEmail from tblEmployee.
Date Time function
There are several built-in DateTime functions available in SQL Server database. Most of the following functions can be used to get the current system date and time, and where you have SQL server installed.
Hence, UTC stands for Coordinated Universal Time, based on which, the world regulates clocks and time data. Noteworthy. there are minor differences between GMT and UTC, but for most common purposes, UTC is synonymous across with GMT.
So, let’s take another example as shown below;
isDate() Function
ISDATE() – This function checks if the only given value, and exists a valid date, time, or DateTime. Then, it will return 1 for success, 0 for failure.
Example:
Select ISDATE(‘PRAGIM’) — it will returns 0
Example:
Select ISDATE(Getdate()) — it will returns1
Example:
Select ISDATE(‘2018-01-20 21:02:04.167’) — it will returns 1
Note: For datetime2 values, IsDate returns ZERO.
Example:
Select ISDATE(‘2018-01-20 22:02:05.158.1918447’) — it will returns 0.
Day() Function
Day() – This function only returns the ‘Day number of the Month’ of the given date.
Examples:
Select DAY(GETDATE()) — It will give the output on behalf of the day number of the month, and based on current system DateTime.
Select DAY(’01/14/2018′) — it will returns 14
Month() Function
Month() – This function will give the output on behalf of the ‘Month number of the year’ of the given date.
Examples:
Select Month(GETDATE()) — This function will give the output on behalf of the ‘Month number of the year’, and based on the current system date and time.
Select Month(’05/14/2018) — it will returns 5
Year() Function
Year() – This function will give the output on behalf of the ‘Year number’ of the given date
Examples:
Select Year(GETDATE()) — Returns the year number, and based on the current system date
Select Year(’01/20/2018) — it will returns 2018
DateName() Function
DateName(DatePart, Date) – This function returns only a string expression, and that only represents a part of the given date. These functions consist 2 parameters.
The first parameter ‘DatePart’ specifies, the part of the date, that we want. The second parameter is the real date, from which we want the part of the Date.
Example 1:
Select DATENAME(Day, ‘2017-04-20 13:47:47.350’) — it will returns 20
Example 2:
Select DATENAME(WEEKDAY, ‘2017-04-20 13:47:47.350’) — it will return Thursday
Example 3:
Select DATENAME(MONTH, ‘2017-04-20 13:47:47.350’) — it will returns April
So, let’s take an example using some of these DateTime functions. Consider the table tblEmployees.
Example: I want to return all Name, DateOfBirth, Day, MonthNumber, MonthName, and Year as shown below.
Select Name, DateOfBirth, DateName(WEEKDAY,DateOfBirth) as [Day], Month(DateOfBirth) as MonthNumber, DateName(MONTH, DateOfBirth) as [MonthName], Year(DateOfBirth) as [Year] From tblEmployees
DatePart() Function
DatePart(DatePart, Date) – This function gives an integer representing the specified DatePart value. Mostly function is similar to DateName() function. DateName() only returns nvarchar value, while DatePart() returns only an integer value. The valid DatePart parameter values are shown below.
Examples:
Select DATEPART(weekday, ‘2012-08-30 19:45:31.793’) — it will returns 5
Select DATENAME(weekday, ‘2012-08-30 19:45:31.793’) — it will returns Thursday
DateAdd() Function
DATEADD (datepart, NumberToAdd, date) – This SQL function gives only the DateTime, after specified term NumberToAdd, and to the datepart specified of the given date.
Examples:
Select DateAdd(DAY, 10, ‘2018-01-20 19:45:31.793’) — it will returns ‘2018-01-30 19:45:31.793’
Select DateAdd(DAY, -10, ‘2012-08-30 19:45:31.793’)– it will returns ‘2018-01-20 19:45:31.793’
DatedDiff() Function
DATEDIFF(datepart, startdate, enddate) – This function gives the count of the specified datepart boundaries crossed among the specified startdate and enddate.
Examples:
Select DATEDIFF(MONTH, ’11/30/2005′,’01/31/2006′) — it will returns 2
Select DATEDIFF(DAY, ’11/30/2005′,’01/31/2006′) — it will returns 62
So, let’s take an example, Let’s suppose we have a table given below;
So, Write a query to find out the age of a person, when the date of birth is given.
Finally, the output will look as shown below.
Cast() and Convert() Functions
To convert a single unit data type to another one, CAST and CONVERT functions can be used.
In addition, As you can see that CONVERT() function has an optional style parameter value, whereas CAST() function lacks this capability.
So, Let’s take an example, we taking a table given below;
The following 2 queries convert DateOfBirth’s DateTime datatype to NVARCHAR. The first query uses the CAST() function, and the second one uses CONVERT() function. Finally, the output is exactly the same for both the queries as shown below.
Select ID, Name DateOfBirth, Cast(DateOfBirth as nvarchar) as ConvertedDOB from tblemployees.
Select ID, Name DateOfBirth, Convert(DateOfBirth as nvarchar) as ConvertedDOB from tblemployees.
So, let’s make the style parameter of the CONVERT() function value, and to format the Date as we would like it. So, we are using 103 as passing the argument for style parameter in given below query, and which formats the date as dd/mm/yy.
Select ID, Name, DateOfBirth, Convert (nvarchar, DateOFBirth, 103) as ConvertedDOB from tblEmployees.
So, let’s have a look at practical example with help of CAST() function;
Let’s suppose we have a registration table below as;
Now, let’s find the total number of registration by day.
Example: Select CAST(RegisteredDate as DATE) as RegistrationDate, COUNT(Id) as TotalRegistrations tblRegistrations Group By CAST(RegisteredDate as DATE)
Output: Finally the output will look as ;
User Defined Functions
There are 3 types of User-Defined Functions in SQL Server which as
Scalar functions
Inline table-valued functions
Multistatement table-valued functions
Scalar Functions
Scalar functions vary in parameters that may or may not have parameters, and always gives a single (scalar) value in the output. Therefore, the returned value can be of any data type format except text value, text, image, cursor, and timestamp.
Example: So, let’s develop a function which calculates and returns the age of a person in output. Consequently, to compare the age we required for, date of birth. So, let’s pass date of birth as a parameter. Therefore, AGE() function will return an integer and will accept date parameter.
Select dbo.Age( dbo.Age(’10/08/1982′).
So, let’s take a practical example in given table below as follows;
Scalar user-defined functions can be used in the Select clause as shown below.
Select Name, DateOfBirth, dbo.Age(DateOfBirth) as Age from tblEmployees
Mostly view the text of the function use sp_helptext FunctionName.
Inline table-valued functions
An Inline Table Valued function always returns a table as output.
So, let’s take an example below; Create a function that returns EMPLOYEES by GENDER.
due to calling method for the user-defined function,
Select * From Fn_EMPLOYEEbyGender(‘male’)
MULTI-STATEMENT TABLE VALUED FUNCTION
Multi-statement table-valued functions are much more similar to Inline Table-valued functions and with some differences. So, let’s have a look at an example, and then note the differences.
Employee Table
Multi-statement Table Valued function(MSTVF):
Due to calling method for vthe Multi-statement Table Valued Function:
Select * from fn_MSTVF_GetEmployees()
Conclusion
The JOINs is very much understanding term for beginners during the learning phase of SQL commands. Consequently, In the interview, Interviewer asks at least one question is about the SQL joins, and functions. So, in this post, I am trying to simplify the things for new SQL learners and make it easy to understand the SQL joins. Furthermore, The functions in SQL, A lot of people are having trouble to understand actual working function. Because SQL contains a lot of data in bulk in different database and table names. A Function is a stored program in the SQL Server Database where you can pass parameters into and return a value. So, I have given some more useful term about working of Functions.
I am a database administrator who has the responsibility of maintaining the integrity and performance of company databases. I guarantee that the data is stored securely and ensure all the users are informed of any changes in the same. Apart from managing all this bulk of technological binaries, I love to enjoy little things in my life. I am an avid foodie who loves to explore the abundance of specialties our cultures offer. I am also into sports, especially cricket. I make sure that I never miss my weekend morning cricket matches alongside my long-time friends. I cherish both my professional and personal lives to the fullest.
