Question & Answer: Use the ‘classicmodels’ database to retrieve the number of orders of one customer and the status is ‘Shipped’. Write a stored proced…..

select orderNumber,count(*) –to find the number of orders made by a customer we use count function
from orders–we cann get the order status and order numbers frm orders table
where orderNumber=103 and status=’shipped’ –these are the conditions provided
group by orderNumber–to use count aggregate function we use group by and which ever column is in                        –select list will be in the group by list

create procedure OrdersByCustomerAndStatus(@status varchar(16),@orderno int,@Listorders int output)
–storeprocedures with parameters out of which one will be output parameter which will be the –returned value when stored procedure is called
as
begin
–we have written the same query in the storedprocedure
select @Listorders=count(*) –assigning the count to @listorders which will be a output parameter
from orders
where orderNumber=@orderno and status=@status
group by orderNumber

end

exec OrdersByCustomerAndStatus ‘Shipped’, 103