CS170 - Computer Applications for Business
Spring 2024 - Assignment 11
|
Due Date:
|
Before 11:59 p.m. on Friday, April 26th, 2024
|
|
Accept Until:
|
Before 11:59 p.m. on Monday, April 29th, 2024
|
|
Evaluation:
|
20 points
|
|
Submit to Canvas:
|
Assignment11.zip file
|
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’
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,
|
feb
|
float, mar
|
float, apr
|
float, may
|
float,
|
|
|
jun
|
float, jul
|
float, aug
|
float, sep
|
float, oct
|
float,
|
|
nov
|
float, dec
|
float)
|
|
|
|
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
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.