What Is Stored Procedures and User Defined Functions In Database Using SQL Queries

With the help of following example we will try to understand the following topic.We will consider the following schema to cover the whole topic so let's start:

To understand the following example we will divide this into following parts and try to solve it one by one so lets see how we can solve this schema. 

 

Part  01

select MONTH(enter_date) from Works_on_Table GROUP by MONTH(enter_date);

select * from Works_on_Table

Part 02

 select COUNT(emp_no),[Department Table].location from [Employee Table] JOIN [Department Table] ON [Employee Table].dept_mo=[Department Table].dept_id  GROUP BY location HAVING COUNT(emp_no)>0;

 select * from [Employee Table]

 select * from [Department Table]

 

 

 

 

Part 03

select COUNT(emp_no),[Department Table].location from [Employee Table] JOIN [Department Table] ON [Employee Table].dept_mo=[Department Table].dept_id  GROUP BY location HAVING COUNT(emp_no)>0;

 select * from [Employee Table]

 select * from [Department Table]

 

 

Part 04

SELECT  [Project Table].project_no,[Project Table].project_name FROM [Project Table] LEFT OUTER JOIN Works_on_Table ON [Project Table].project_no=Works_on_Table.project_no

EXCEPT

SELECT  Works_on_Table.project_no,[Project Table].project_name FROM [Project Table] RIGHT OUTER JOIN Works_on_Table ON [Project Table].project_no=Works_on_Table.project_no;

select * from Works_on_Table

select * from [Project Table]

 

Part 05

SELECT [Employee Table].emp_no,Works_on_Table.project_no FROM [Employee Table] LEFT OUTER JOIN Works_on_Table ON [Employee Table].emp_no=Works_on_Table.emp_no

select * from Works_on_Table

select * from [Employee Table]

 

Part 06

SELECT customer_tbl.Customer, ages_tbl.AgeGroup FROM ages_tbl JOIN customer_tbl  ON DATEDIFF(year,customer_tbl.DOB,getDate())<=ages_tbl.AgeMax AND DATEDIFF(year,customer_tbl.DOB,getDate())>=ages_tbl.AgeMin

Some of the important short questions and their answers .

Q01: What is SQL server Authentication? How many types are there for Microsoft SQL Server Authentication, elaborate each?

Ans:A. It is a process to ensure that only valid user can access the server.

There are two types:

 Windows Authentication, connect to sql server using your windows account. This option is easier and simpler SQL Server Authentication, the database engine uses its own account and verification each time you connect.

Q02:Summarize the role of Breakpoint in debugging. Highlight the following features and their role 

1) Breakpoint hit count (2) Breakpoint action Can you ascertain the use of “Quick Watch Window”?

Ans: Breakpoint is the marker placed by the user during code development, the compiler halts the execution of program at this marker and then user has the control to execute step by step for debugging. 1) Breakpoint condition, the condition defined by the user which has to be met in order to execute the breakpoint 2) Breakpoint hit count, the number of times a break point is executed 3) Breakpoint action, the action defined by the user on execution of breakpoint “Quick Watch Window” monitors the variables during execution

Q03: Write 3 main differences between stored procedures and user defined functions (be precise and short). 

Ans:.1. Procedure can return zero or n values whereas function can return one value which is mandatory. 

2. Procedures can have input/output parameters for it whereas functions can have only input parameters. 

3. Procedure allows select as well as DML statement in it whereas function allows only select statement in it. 

4. Functions can be called from procedure whereas procedures cannot be called from function.

Q4: Write a query to retrieve a particular Invoice for Customer “101” and order number “A221219” 5 C. Create a trigger that updates the stock as soon as an order is successfully completed (i.e. payment received and product(s) shipped to customer).

Ans:

Create Trigger T_UpdateStock ON tbl_Sale AFTER INSERT,UPDATE 

AS IF UPDATE (product_quantity) 

Begin 

 Get values from inserted table and update the Stock table.

 End