SQL SERVER QUESTION

USE OES2 Database Here: https://drive.google.com/file/d/0B_OOaWV7RgsReU1nWFEwY0RfaUE/view?usp=sharing

3: Using ms sql server oes2 database, convert the following select sql statement into several user defined functions to produce the same result (15 points)

INCLUDE DROP USER DEFINED FUNCTION IF EXISTS.

user defined functon names.

udf_sales_total

udf_num_customers

udf_num_orders

udf_shipping_total

udf_total_gross

A:

SELECT

r.region_no,

sum(o.subtotal) as udf_sales_total,

count(distinct c.customer_no) as udf_num_customers,

count(o.order_no) as udf_num_orders,

round(sum(o.shipping_charge), 2) as udf_shipping_total,

round(sum(o.total_amt), 2) as udf_total_gross

FROM orders o

INNER JOIN customer c

on c.customer_no = o.customer_no

INNER JOIN branch b on o.branch_no = b.branch_no

INNER JOIN regions r on c.region_no = r.region_no

group by r.region_no

order by 1;

*/