How To Use Joins And Functions In SQL

How To Use Joins And Functions In SQL SQL Introduction 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; 1.Candidate Key 2.Primary Key 3.Unique Key 4.Alternate Key 5.Composite Key 6.Super Key 7.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. Example: Employee_Id, License_Number, & Passport_Number shows candidate keys 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: 1.CRETE TABLE statement (at the time of table creation) – as a result, the system defines the name of the primary key. 2.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: 1.It allows one null value in the column. 2.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 ‘sara@aaa.com’. Select CHARINDEX(‘@’,’sara@aaa.com’,1) Output: 5 SUBSTRING() Function 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 ‘John@bbb.com’. Select SUBSTRING(‘John@bbb.com’,6, 7) Output: bbb.com 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. Example: Select SUBSTRING(‘John@bbb.com’,(CHARINDEX(‘@’, ‘John@bbb.com’) + 1), (LEN(‘John@bbb.com’) – CHARINDEX(‘@’,’John@bbb.com’))) Output: bbb.com 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. Syntax of CAST and CONVERT function: CAST ( expression AS data_type [ ( length ) ] ) CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) 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 1.Scalar functions 2.Inline table-valued functions 3.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.

Connect with source url:- https://www.loginworks.com/blogs/use-joins- functions-sql/

How To Use Joins And Functions In SQL