Scalar Function in SQL

To Get data repeatedly in many Store Procedure. We will write a query or statement that execute to provide desired data. 

The main advantage of the function is that it can be used in SQL Procedure. 

  Built In Function

Like some function are already built in SQL i.e is used to get value which is called built in function. 

e.g 

  DateName(year,dob),

  DayName()

  User Defined Function

We can write our function to get desired data.Lets see how to write it

CREATE FUNCTION functioname

(

@mydate AS DATETIME

)

RETURNS VARCHAR(MAX)

AS

BEGIN

RETURN

  DATENAME(DW, @mydate)+ ', '+

  DATENAME(DAY, @mydate)+ ' '+

  DATENAME(MONTH, @mydate) +', '+

  DATENAME(YEAR, @mydate)

 

END

This Function Can be used in any Procedure to get value

Lets see how

 

SELECT Empname,

[dbo].[functioname](DOB) FROM Employee

 

Introduction to SQL

SQL stands for Structured Query Language.

SQL is a standard language for accessing and manipulating databases and records

It helps to Manage Data in database according to Our Need.

Let see what we can do with SQL

1.Create a Database .

2.Create table inside database to store data.

3.Insert data using SQL Query.

4.Update data using SQL Query

5.Delete data using SQL Query

6.Retrive Record from Datable as per our need.

7.Set permission to Database and Table.

8.We can create Store Procedure to fetch record or manipulate record.

We have to use Some Command to Manipulate Database record.

Insert,Update,Delete,Select...........etc.

There are Some Syntax in SQL which is used to Manipulate record in database

which is ANSI standard Syntax and Command

.Like To Fetch all records From Table we will write

select * from TableName

To Delete Record From Table

Delete TableName where ID=X

This will delete record from table where ID is X

Order by In SQL

  Order by in Sql is used to sort record in ascending or desending order

We will write query like 

SELECT X, Y, ...
FROM mytable
ORDER BY x, y,Z ... ASC|DESC;

by default it gives record in ascending order but get record in ascending order we will use order by Desc

Example

StudentID	StudentName	Age	Marks
1	Navneet	11	82
2	Nitesh	16	90

Select * from Studenttable order by age  desc

Result as below

 

StudentID	StudentName	Age	Marks
2	Nitesh	16	90
1	Navneet	11	82

Case In Sql

While wring a query or fetching record from sql database we need  some condition to be satisfied according to record we fetch. where we use Case as Condition

It returns value when and then conditions(when a condition satisfied then it returns value else it moves to check for next condition)

Lets Check how to write Case Statement 

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

When it satisfy first condition it stops else move for next one and gives record accordingly.

StudentID	StudentName	Age	Marks
1	Navneet	11	82
2	Nitesh	16	90

SELECT StudentID, Marks,
CASE
    WHEN Marks> 85 THEN 'Top'
    WHEN Quantity <30 THEN 'Fail'
    ELSE 'Good'
END AS Position
FROM Student;

Joins In SQL

                                       Join In SQL

A join is a clause in sql used to join two or more than two table rows based on relationship between them

  Types of Joins

1.Inner Join

This join gives record having matching values in tables

Query will be written as

We have two table class and Students

Select Class.ClassName,Student.Student Name

 from Students 

inner join Class on Class.StudentID=Student.StudentID

2. Left Join

This Join give all data from left table and matching data from right table

Query will be written as

* means all record

Select * 

 from Students 

left join Class on Class.StudentID=Student.StudentID

3.Right Join

This Join give all data from right table and matching data from left table

Query will be written as

Select * 

 from Students 

right join Class on Class.StudentID=Student.StudentID

4.Full Outer Join 

This join return all record from both table having matched data

Query will be written as

Select * 

 from Students 

full outer join Class on Class.StudentID=Student.StudentID

5.Union

Union is used to get all record from both table like all column in both table will be 

returned by using union

Query will be written as

Select * 

 from Students 

Union

select * from  Class