CS170 - Computer Applications for Business

Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due

CS170 - Computer Applications for Business

Spring 2024 - Assignment 11

Requirements:

Thehttps://www.programiz.com/sql/online-compiler/is the site you will use to practice your SQL.  Remember to copy text is highlight text and then hit ctrl- c   Paste your text is hitting ctrl-v

A.   Make sure to empty out the input.  Create a table called FINANCIAL_REPORTING by pasting the following text into programiz and hitting  You will see on  the output at bottom of screen saying “SQL query successfully executed. However, the result set is empty.”

create table FINANCIAL_REPORTING

(account   varchar(100),  business_unit  varchar(100),currency    char(3),

reporting_year   int, scenario         varchar(100), jan              float,

E.   Type in SELECT “your first name”,” your lastname”,” your CS170 section”,*  from FINANCIAL_REPORTING and take a screenshot like below.  Call the name of the    screenshot as screen1.jpg    The result will be like below screenshot.   Example select clauses can be seen herehttps://www.w3schools.com/sql/sql_where.asp

Build on the query you used in the prior line for FINANCIAL_REPORTING:  Apply a filter where jan + feb + maris less than -89,000,000 and reporting_year is less than 2018.   Please make a screenshot of your SQL and the output and save as screen2.jpg Below is sample output.  Use to give you an idea of structure of output:

F.   Write a nested query that finds the max value of jan + feb + mar and then show your name, section,  associated account, business_unit, currency, reporting_year, scenario, jan +  feb + mar for that max value.  Save screenshot of SQL and output, similar to what you did in question D, as screen3.jpg.  This topic should be covered in your recitation.  But you can also refer tohttps://www.geeksforgeeks.org/nested-queries-in-sql/

G.  Write a nested query that finds the average value of jan + feb + mar with a filter account    like ‘%Sales%’   This filter with % will help to pick up any account with Sales in it.  Show the associated account, business_unit, currency, reporting_year, scenario, jan + feb + mar that  are above average value when you have the wildcard filter of like “%Sales%” .  Save as screen4.jpg.  This topic should be covered in your recitation.  But you can also refer to https://www.geeksforgeeks.org/nested-queries-in-sql/

H.   Use Customers and Orderstable to do a join by customer_id and filter on first_name = “John” and last_name = “ Doe” .  Display your name and section and all fields in both tables. Save as a screen5.jpg You can do this as INNER JOIN as referenced here https://www.w3schools.com/sql/sql_join.asp

I.    Create an answer.txt file.  In the answer.txt file, please also acknowledge the honor code by pasting the statement in the select statement highlighted at the beginning of the assignment.  YOU ARE ON YOUR HONOR TO COMPLETE THIS ASSIGNMENT WITHOUT ASSISTANCE.  You may use your text and resources  available to you through Canvas. You MAY NOT ask any human for assistance, give assistance to anyone, or use any online  tutoring service  For the screenshot below, the where clause includes many of the columns needed to uniquely identify a record on the FINANCIAL_REPORTING table (aka primary key).  Can you identify an additional column needed for the primary key?  Please answer in the answer.txt

J.    Based on what you did earlier in the assignment to insert records into the FINANCIAL_REPORTING table, pleaseadd two new customers into the Customers table.     Please take a screenshot (screen6.jpg) of the insert and take a screenshot (screen7.jpg) of a select from the Customers table.

K.   Put all the imagefiles and answer.txt into assignment11.zip and upload to Canvas for assignment 11.