Overview: In this assignment you'll explore using joins to answer more complex business questions, you will also look at implementing some of your answers into the form of a view and stored...

1 answer below »


Overview:


In this assignment you'll explore using joins to answer more complex business questions, you will also look at implementing some of your answers into the form of a view and stored procedures. View's and stored procedures or stored procs often form the basis for creating reports based on your data.



Recommended Readings:



Introducing SQL Server - Chapter 12 Joins:



https://learning.oreilly.com/library/view/introducing-sql-server/9781484214190/9781484214206_Ch12.xhtml



Introducing SQL Server - Chapter 13 Views:



https://learning.oreilly.com/library/view/introducing-sql-server/9781484214190/9781484214206_Ch13.xhtml



Recommended Studies:


Review SQL Server Joins:



SQL Joins by Joey Blue


Answer the following questions:



  • What is a view?


    • Why would you use one?


  • What is a stored procedure?

  • What is a join?


    • Left Join

    • Right Join

    • Full Join

    • Inner Join




Required Assignment:


Provide the SQL Query that will answer the following questions:


1. Write a Query that returns the following statement in a column called 'WhosMyRep'


o "Hello [CustomerFirstName] your representative is: [EmployeeFirstName] [EmployeeLastName]."


o It should filter based on CustomerID


2. Similar to the previous assignment, create a query that shows out potential profit, however this time group the results by the Product line TextDescription:


o


3. Create a view called CallList that provides a phone list for all employees it should include: Their full name in "lastname, firstname" format, their office City and their cell and desk phone numbers. If a Phone Number is NULL, it should display as 'N/A'


o Select all records from the new CallList View


o How would I delete the view


4. Create a view for our business users called StatusTranslator This view should return the Status from an order based on orderID that uses a CASEstatement to translate 4 status to their English counterparts


o 1 - Order Received


o 2 - Processing Started


o 3 - Processing Complete


o 4 - Order Shipped


5. We're worried about products that aren't selling, write a query that returns the name of of product, the vendor and the quantity in stock that haven't been ordered.


6. We're thinking about increasing the credit limit for a number of our customers. Produce a query that will list all the customers who made payments that more than their credit limits and display how much that paymentwas over.


o


7. Create another query similar to the one above, but display the customer names, theirphone numbers and there new recommended credit limit.The recommended credit limit should be there current limit + the amount their payment was over rounded to the nearest 100 of dollars.For Example, if their credit limit was 1918.00 and their payment was over 974.88 The total would be 2892.88 - So new credit limit would be $2900.00


8. Our data Science team is requesting a flat file of all our data, our DBA objected to this, but the data science teaminsists that it needs all the points it can get to create a predictive model. As such you've been tasked with writing aquery that Returns every data point in our database. Since this is a one time dump of data you can use *.Hintthe ERD may help you here so you don't missa table and/or a relationship. Your answer shouldNOTuse UNION.


9. Christmas bonuses are being awarded in the form of commission, these commission are being based on payments made by the customer. Write a query that displays the bonus amount for a 15% commission based onpaymentsreceived, the result should be:
grouped by Employee last name, ordered by commission earned descending and finally formatted in dollars


o


Answered 116 days AfterOct 19, 2021

Answer To: Overview: In this assignment you'll explore using joins to answer more complex business questions,...

Ankur answered on Feb 12 2022
99 Votes
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here