Question

Below is what I have to do. This is all performed in SQL. I have written a bunch of code, that I have also provided. Any help is appreciated

Exercises

Complete each of the following exercises. If you are unsure how to accomplish the task, please consult the coursework videos where there are explanations and demos.

1. Use built in SQL functions to write an SQL Select statement on fudgemart_products which derives a product_category column by extracting the last word in the product name. For example
   1. for a product named ‘Leather Jacket’ the product category would be ‘Jacket’
   2. for a product named ‘Straight Claw Hammer’ the category would be ‘Hammer’

Your select statement should include product id, product name, product category and product department.

2. Write a user defined function called f_total_vendor_sales which calculates the sum of the wholesale price * quantity of all products sold for that vendor. There should be one number associated with each vendor id, which is the input into the function. Demonstrate the function works by executing an SQL select statement over all vendors calling the function.
   
3. Write a stored procedure called p_write_vendor which when given a required vendor name, phone and optional website, will look up the vendor by name first. If the vendor exists, it will update the phone and website. If the vendor does not exist, it will add the info to the table. Write code to demonstrate the procedure works by executing the procedure twice so that it adds a new vendor and then updates that vendor’s information.
   
4. Create a view based on the logic you completed in question 1 or 2. Your SQL script should be programmed so that the entire script works every time, dropping the view if it exists, and then re-creating it.
   
5. Write a table valued function f_employee_timesheets which when provided an employee_id will output the employee id, name, department, payroll date, hourly rate on the timesheet, hours worked, and gross pay (hourly rate times hours worked).

Written Code

use fudgemart_v3
go

--Question 1
--This runs but doesn't supply correct output
select * from fudgemart_products
select right(product_name, charindex(' ', product_name)) as product_category from fudgemart_products
go

---Runs but returns NULL for product_Category
select product_id, product_name, product_department
from fudgemart_products
order by product_id
declare @product_name as varchar(20)
select right(@product_name, charindex(' ',@product_name)) as product_category

print len(@product_name)

---question 2-----

drop function dbo.f_vendor_sales
go

declare @vendor_id int
set @vendor_id = 1
select count(*) from fudgemart_products where product_vendor_id = @vendor_id
go

--Function says it is completed
create function dbo.f_total_vendor_sales(
   @vendor_id int --input
   ) returns int as
begin
   declare @count int
   set @count = (select count(*) from fudgemart_products.dbo.product_wholesale_price where product_vendor_id = @vendor_id)
   return @count --output
end
go

---When i attempt function, I get invalid object name

select product_vendor_id, product_wholesale_price,
   dbo.f_total_vendor_sales(product_vendor_id) as total_vendor_sales
   from fudgemart_products

----For question 3-------
create procedure p_write_vendor
(
   @vendor_name varchar (50),
   @vendor_phone varchar (20),
   @vendor_website varchar (100)
   ) as
if exists ( select 1 from fudgemart_vendors
               where vendor_name = @vendor_name
               or vendor_phone = @vendor_phone
               or vendor_website = @vendor_website)
   begin
       update fudgemart_vendors
       set vendor_name =@vendor_name,
           vendor_phone = @vendor_phone,
           vendor_website = @vendor_website
       where vendor_name = @vendor_name
       or vendor_phone = @vendor_phone
       or vendor_website = @vendor_website
   end
else
   begin
       insert into fudgemart_vendors values (@vendor_name, @vendor_phone, @vendor_website)
   end

Answer 1

--Question 1
select * from fudgemart_products
select right(product_name, charindex(' ', product_name)) as product_category from fudgemart_products
go

select product_id, product_name, product_department
from fudgemart_products
order by product_id
declare @product_name as varchar(20)
select right(@product_name, charindex(' ',@product_name)) as product_category

print len(@product_name)

---question 2-----

drop function dbo.f_vendor_sales
go

declare @vendor_id int
set @vendor_id = 1
select count(*) from fudgemart_products where product_vendor_id = @vendor_id
go

--Function says it is completed
create function dbo.f_total_vendor_sales(
   @vendor_id int --input
   ) returns int as
begin
   declare @count int
   set @count = (select count(*) from fudgemart_products.dbo.product_wholesale_price where product_vendor_id = @vendor_id)
   return @count --output
end

select product_vendor_id, product_wholesale_price,
   dbo.f_total_vendor_sales(product_vendor_id) as total_vendor_sales
   from fudgemart_products

----For question 3-------
create procedure p_write_vendor
(
   @vendor_name varchar (50),
   @vendor_phone varchar (20),
   @vendor_website varchar (100)
   ) as
if exists ( select 1 from fudgemart_vendors
               where vendor_name = @vendor_name
               or vendor_phone = @vendor_phone
               or vendor_website = @vendor_website)
   begin
       update fudgemart_vendors
       set vendor_name =@vendor_name,
           vendor_phone = @vendor_phone,
           vendor_website = @vendor_website
       where vendor_name = @vendor_name
       or vendor_phone = @vendor_phone
       or vendor_website = @vendor_website
   end
else
   begin
       insert into fudgemart_vendors values (@vendor_name, @vendor_phone, @vendor_website)
   end