CS170 – Computer Applications for Business Spring 2024 - Assignment 11

Learning Objective:

This assignment is designed to gain additional proficiency in the use of SQL.

To get credit for this assignment:

1   Deliver the assignment11.zip file to Canvas on time.

2   Your TA should be able to open your file by clicking on its link.

Directions:

•    Follow the requirements listed on the next page.

•    Submit your assignment11.zip file to Canvas using the Canvas-> Assignments link.

Note: Explanation of how to use SQL wildcard to filter data.   Note the Where clause has “A%” which returns every CustomerName that starts with ‘A’ by adding a ‘%’ after the ‘A’

B.   Make sure to empty out the input.   You can enter “select * from

FINANCIAL_REPORTING” , hit                             and see the screen below

C.   In the DBload.txt file, you will see the data needed to set up the table for this

assignment.  Make sure to empty out the input.     Copy and paste text from

DBload.txt file into programiz and hit the button.    You will see something similar to below

D.   Once you have done this you will see on right side of screen a table, your table with the data.

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