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

Suppose the coffee industry claimed that the average adult drinks 1.7 cups of coffee per day. To test this​ claim, a random sample of 50 adults was​ selected, and their average coffee consumption was found to be 1.9 cups per day. Assume the standard deviation of daily coffee consumption per day is 0.5 cups using a significant level of 0.01

A. The​ z-test statistic is =

(round to two decimal places)

B. The critical z-score(s) are= (round to two decimal places)

(there should be two answers here one positive and one negative)

C. The p-value is=

(round to three decimal places)

In a certain dormitory, for various nights, we compare the number of residents who go to bed with their shoes on with the number who wake up in the morning with headaches.

Find the correlation between these two variables:
sum of X's:
sum of X squared's:
sum of Y's:
sum of Y squared's:
sum of X*Y's:
SS(X)=
SS(Y)=
SS(XY)=
All previous answers should be exact.

The correlation r= __ (to seven places after the decimal)

Compute the missing pieces of the linear regression line of headaches on sleeping with shoes (to five places after the decimal):
Y-hat = __X + __

For each x, write down the prediction y-hat (to one place after the decimal):
For 13 people sleeping with their shoes on, we predict __ headaches.

For 19 people sleeping with their shoes on, we predict __ headaches.

Suppose Johnson​ & Johnson and Walgreen Boots Alliance have expected returns and volatilities shown​ here the table below ​,with a correlation of 20%.Calculate the expected return and the volatility​ (standard deviation) of a portfolio consisting of Johnson​ & Johnson's and​ Walgreens' stocks using a wide range of portfolio weights. Plot the expected portfolio return as a function of the portfolio volatility. Using your​ graph, identify the range of Johnson​ & Johnson's portfolio weights that yield efficient combinations of the two stocks.

Find the expected return and volatility of the portfolio consisting of 50​% of Johnson​ & Johnson's stock and 50 ​% of​ Walgreens' stock.

The expected return of the portfolio is ………….​%. (Round to one decimal​ place.)

The volatility​ (standard deviation) of the portfolio is …………​%. (Round to one decimal​ place.)

Find the expected return and volatility of the portfolio consisting of 60% of Johnson​ & Johnson's stock and 40​% of​Walgreens' stock.

The expected return of the portfolio is ………….​%. (Round to one decimal​ place.)

The volatility​ (standard deviation) of the portfolio is ………​%. (Round to one decimal​ place.)

Find the expected return and volatility of the portfolio consisting of 70​% of Johnson​ & Johnson's stock and 30​% of​Walgreens' stock.

The expected return of the portfolio is ……..​%. (Round to one decimal​ place.)

The volatility​ (standard deviation) of the portfolio is ………..​%. (Round to one decimal​ place.)

Plot the expected portfolio return as a function of the portfolio volatility.

Seasonal affective disorder (SAD) is a type of depression during seasons with less daylight (e.g., winter months). One therapy for SAD is phototherapy, which is increased exposure to light used to improve mood. A researcher tests this therapy by exposing a sample of patients with SAD to different intensities of light (low, medium, high) in a light box, either in the morning or at night (these are the times thought to be most effective for light therapy). All participants rated their mood following this therapy on a scale from 1 (poor mood) to 9 (improved mood). The hypothetical results are given in the following table.

(a) Complete the F-table and make a decision to retain or reject the null hypothesis for each hypothesis test. (Round your answers to two decimal places. Assume experimentwise alpha equal to 0.05.)

State the decision for the main effect of the time of day.

Retain the null hypothesis. Reject the null hypothesis.    

State the decision for the main effect of intensity.

Retain the null hypothesis. Reject the null hypothesis.    

State the decision for the interaction effect.

Retain the null hypothesis. Reject the null hypothesis.    

(b) Compute Tukey's HSD to analyze the significant main effect.

The critical value is _____

for each pairwise comparison.

Summarize the results for this test using APA format.

EXPLAIN THE PROCESS, HOW TO..SS, DF, MS, F, VARIANCE?????

Shouldice Hospital in Canada is widely known for one thing—hernia repair! In fact, that is the only operation it performs, and it performs a great many of them. Over the past two decades this small 90-bed hospital has averaged 7,000 operations annually. Last year, it had a record year and performed nearly 7,500 operations.

A hernia repair operation at Shouldice Hospital is performed by one of the 12 full-time surgeons assisted by one of seven part-time assistant surgeons. The first operations begin at 7:30 AM each day, Monday through Friday. Surgeons generally take about one hour to prepare for and perform each hernia operation, and they operate on an average of at most four patients per day. This four patient per day limit on the average number of operations performed per surgeon has been found to be the best operating level for the hospital as it take into account time the surgeons need for patient exams and consultations, updating medical charts, writing reports, traveling to professional conferences, vacations, and other times when they are performing other duties or are not available to perform surgeries. A given surgeon may perform more than four surgeries on a given day, but the average cannot exceed four without having adverse effects on overall hospital operations. The surgeons’ day ends at 4 p.m. Although hernia repair operations are performed only five days a week, the remainder of the hospital is in operation continuously to attend to recovering patients.

The below table shows the number of operations with 90 Beds (30 patients per day).Each row in the table follows the patients who checked in on a given day. The columns indicate the number of patients in the hospital on a given day. Patients check-in to the hospital the day before their operation is scheduled and stay for three days.

For example, the first row of the table shows that 30 people checked in on Monday and were in the hospital for Monday, Tuesday, and Wednesday. By summing the columns of the table for Wednesday, we see that there are 90 patients staying in the hospital that day.

The medical facilities at Shouldice consist of five operating rooms, a patient recovery room, a laboratory, and six examination rooms. An operation at Shouldice Hospital is performed by one of the 12 full-time surgeons assisted by one of seven part-time assistant surgeons. Surgeons generally take about one hour to prepare for and perform each hernia operation.

Now look at the effect of increasing the number of beds by 50 percent. Although financial data are sketchy, an estimate from a construction company indicates that adding bed capacity would cost about $100,000 per bed. In addition, the rate charged for the hernia surgery varies between about $900 and $2,000 (U.S. dollars), with an average rate of $1,300 per operation. The surgeons are paid a flat $600 per operation.

How many weeks would it take the hospital to payback its investments? (Round your answer to 1 decimal place.)

If DNA from an evidence sample and DNA from a suspect or victim share a profile that has a low frequency in the population, this suggests that the two DNA samples came from the same person; the lower the frequency, the stronger the evidence. But the possibility remains that the match is only apparent—that an error has occurred and the true profile of one of the sources differs from that reported by the laboratory. Please discuss the ways that laboratory errors, particularly errors that might falsely incriminate a suspect, can arise, how their occurrence might be minimized, and how to take into account the fact that the error rate can never be reduced to zero.

Please discuss the quality assurance and control.

C# & ASP.NET

Create a console application that prompts the user to enter a regular expression, and then prompts the user to enter some input and compare the two for a match until the user presses Esc:

The default regular expression checks for at least one digit.
Enter a regular expression (or press ENTER to use the default): ^[a- z]+$
Enter some input: apples
apples matches ^[a-z]+$? True
Press ESC to end or any key to try again.
Enter a regular expression (or press ENTER to use the default): ^[a- z]+$
Enter some input: abc123xyz
abc123xyz matches ^[a-z]+$? False
Press ESC to end or any key to try again.

If DNA from an evidence sample and DNA from a suspect or victim share a profile that has a low frequency in the population, this suggests that the two DNA samples came from the same person; the lower the frequency, the stronger the evidence. But the possibility remains that the match is only apparent—that an error has occurred and the true profile of one of the sources differs from that reported by the laboratory. Please discuss the ways that laboratory errors, particularly errors that might falsely incriminate a suspect, can arise, how their occurrence might be minimized, and how to take into account the fact that the error rate can never be reduced to zero.

Please discuss the quality assurance and control.

C#

Create a console application that prompts the user to enter a regular expression, and then prompts the user to enter some input and compare the two for a match until the user presses Esc:

The default regular expression checks for at least one digit.
Enter a regular expression (or press ENTER to use the default): ^[a- z]+$
Enter some input: apples
apples matches ^[a-z]+$? True
Press ESC to end or any key to try again.
Enter a regular expression (or press ENTER to use the default): ^[a- z]+$
Enter some input: abc123xyz
abc123xyz matches ^[a-z]+$? False
Press ESC to end or any key to try again.