More About Sql Examples

This article contains sql views,triggers,functions and stored procedures.

View usage in sql

We combine multiple tables in the database with the join structure and write queries to fetch the columns we want. However, when we want to use the query result we obtained again in the future, we need to rewrite the same sql query. Therefore, in order not to write the same sql query every time we need it, a view, that is, a virtual table structure, is used.It is used in places such as writing a complex query once and then calling a normal table, shortening the query time.

When we want to create a new view, the create view command and the view name are written, and then the query that we want to run in the view is determined. Thus, when we run the query, we create a new view.

--Create View

create view ProductsList
as
select ProductName,UnitPrice,UnitsInStock from products

--test query

select * from ProductsList

In this example we define a view that simplifying the query result.To create, we use create ,for updating is alter and to delete we use drop keyword.

The example belowe two tables variables joined as a view.

--Update View

alter view ProductsList
as
select ProductName,UnitPrice,UnitsInStock,CategoryName
from products p
inner join categories c on c.CategoryID=p.CategoryID

--test query

select * from ProductsList

--Delete View

drop view ProductsList

User define functions

Functions allow us to access the sql queries we repeat constantly from a single point in order to make our work easier. Functions can return any type of value. You can return an int or varchar value as well as a table.The basicly structure of functions as shown belowe:

CREATE FUNCTION functionName(parameters if exist)
RETURNS INT or NVARCHAR
AS
BEGIN
RETURN Select EXPRESSİON
END

--Create function

Create Function Fn_Concatenate(@firstName Nvarchar(20),@surname Nvarchar(30))
Returns Nvarchar(51)
As
Begin
Return @firstName + Space(1)+ @surname
End

--test query
Select dbo.Fn_Concatenate('Jone','Doe')

We can arrange the functions as we want and assign any task. In this example above, what the function does is combine an entered first and last name with a space in between. In the example below, it returns the result of the record that matches the id value entered by the user.

--Table returned functions

Create Function fn_listTable(@odId int)
returns table
as
return select * from [Order Details] where OrderID=@odId

--Test query

select * from dbo.fn_ListTable(10248)

Stored Procedures

Stored procedures are generally developed to easily perform routine tasks. Stored procedures written with Sql statements are compiled only when they are run for the first time. They are not compiled on subsequent runs and thus a quick work is provided.

Stored Procedures have the ability to call other stored procedures. That is, while a stored procedure is running, it can trigger another stored procedure to run at the same time. This event is called nesting. Stored procedures can call other procedures 32 times. This number is 32 If passed, it will cause the stored procedure chain to be broken and a new stored procedure cannot be called. So the nesting event never goes into an infinite loop. There will be a maximum of 32 nesting times.

Stored procedures can have parameters or can be use without parameters. This can change depend on purpose of usage.

--Create procedure

CREATE PROCEDURE sp_GetProducts
AS
BEGIN
SELECT ProductID,ProductName,UnitPrice FROM Products
END

--Test Query

Execute sp_GetProducts

--Create procedure

Create Procedure sp_CustomerSpend @id nchar(5)
As
Begin
	 select od.OrderID,c.CompanyName,
	 Sum(Quantity*UnitPrice*(1-Discount)) as Total from Customers c
	 Inner Join Orders o on o.CustomerID=c.CustomerID
	 Inner Join [Order Details] od on od.OrderID=o.OrderID
	 Where c.CustomerID=@id
	 Group By od.OrderID,c.CustomerID
End

--test query

Execute sp_CustomerSpend @id='ALFKI'

In the example above, the use of the stored procedure is similar to a view. But the example below has a different usage scenario. In this example, some information is brought by filtering along with the expenditure made by the customer whose id information is entered. In this query, when the id information entered by the user and the id specified in the parameter match, all matching records are retrieved. Here, we have processed the id information as it acts as a unique key. If we knew that the customers' names were also unique, the name would also serve as a parameter.

Trigers in sql

Triggers are used when an operation (insert, update, delete) occurs on the table and another operation is desired. Trigger structure is a special type of store procedure that runs automatically when certain events occur or before a table occurs in relational database management systems. We use the trigger structure when we want certain operations to be performed on the same table or on another table, before or when an insertion, update, or deletion of a table occurs.

After triggers: It is used to perform certain operations after insert, update, and delete operations on a table.

Instead of triggers: Skips insert, update, or delete operations in a table or view structure and instead executes other statements defined in the trigger.

--Create a trigger that list the table after adding a record.

Create Trigger trg_listEmployeeRecords On Employees
After Insert
As
Begin
	Select * From Employees
End
 
 
--test query
insert into Employees(LastName,FirstName,
Title,TitleOfCourtesy,City,Country)
values('Doe','Jane','It Manager','Mrs.','Texas','USA') 

In this trigger, after entering a new employee record in the employees table, it brings the employees table to the user.

Use master

--create sample table
Create Database SampleDb
Use SampleDb

create table Members(
[MemberId] int identity(1,1) not null primary key,
[Full Name] nvarchar(50)not null,
[Status]bit default 1,
);

--add records to table
insert into Members([Full Name]) values('Jane Doe')
insert into Members([Full Name]) values('Max Doe')

select * from Members

--creating trigger
Create Trigger trg_SoftDeleteMembers
on Members
Instead Of Delete
As
Begin
	Update Members
	Set Status=0
	From Members
	Inner Join  deleted on deleted.MemberId=Members.MemberId
End

--test triger
Delete Members Where MemberId=1

In the second example here, a record deletion method that is frequently used in databases is written with trigger. As an example, a table has been created and the member's id information will be entered from this table and the member will be deleted from the table. When the trigger is tested, the default value of 1 is changed to 0, indicating that the member is soft deleted without losing its records.