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

Use the ‘classicmodels’ database to retrieve the number of orders of one customer and the status is ‘Shipped’. Write a stored procedure named ‘OrdersByCustomerAndStatus’ which has three parameters: the status, customer number and number of orders. Following is a sample execution of the command: CALL OrdersByCustomerAndStatus(‘Shipped’, 103, @Listorders); SELECT @Listorders

Expert Answer

 

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

Still stressed from student homework?
Get quality assistance from academic writers!