Stored Procedures & Exercise (Sources from Northwind Database)

21. June 2016 SQL 5

In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that’s storedin the database in compiled form so that it can be shared by a number of programs.

As below, it shows some exercise about Stored Procedures (Sources from Northwind Database):

First of all, the relationship between different tables as the following picture:

Northwind_A4_size_for_Print

  1. Find Products by searching by name

 

USE [Northwind]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[FindProductByName]

@productName NVARCHAR(40)

AS

BEGIN

SET NOCOUNT ON;

 

SELECT * FROM dbo.Products

WHERE ProductName = @productName

END

 

  1. Find Products with the product name similar to _?_

 

USE [Northwind]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[FindProductSimilar](

@keyWords NVARCHAR(100))

AS

BEGIN

SET NOCOUNT ON;

 

SELECT * FROM Products p

WHERE p.ProductName LIKE ‘%’+@keyWords+’%’

END

 

  1. Find Products made by the supplier name

 

USE [Northwind]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[FindProductsBySupplierName]

@companyName NVARCHAR(40)

AS

BEGIN

SET NOCOUNT ON;

 

SELECT * FROM dbo.Products p

JOIN dbo.Suppliers s ON p.SupplierID = s.SupplierID

WHERE s.CompanyName = @companyName

END

 

  1. Find the number of Orders a customer with ID has

 

USE [Northwind]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[FindNumberOfOrdersByCustomerID]

@customerID NCHAR(5)

AS

BEGIN

SET NOCOUNT ON;

 

SELECT COUNT(OrderID) AS NumberOfOrders FROM dbo.Orders

WHERE CustomerID = @customerID

END


5 thoughts on “Stored Procedures & Exercise (Sources from Northwind Database)”

Leave a Reply