SQL Assignment 1 (100 points + 6 extra points)
Purpose: To provide you an opportunity to apply (SQL) in the data processing cycle.
Database : climatechange_data (on Canvas)
Tools: SQLiteStudio (See Instruction for Downloading SQLiteStudio on Canvas).
To open the database, use SQLiteStudio!!! Do not click the database to open.
For Mac users, if you can not add the database, such as the “Ok” button is dimmed.
Rename the database and then try it again.
This database is built on real data extracted from 34 random selected S&P 500 financial reports
during 2019 to 2021. The environmental data is hand collected from the public company filings.
With this database, you can perform analyses on public firms’ disclosure of climate-related risk.
Before starting the assignment, install SQLiteStudio on your computer and download the above
database in your computer.
To be submitted:
Submit each SQL assignment in ONE file. The file should include everything required to be
submitted for that assignment. Note that the required statements may not be covered in my
video or statements I covered in the video may not be required in this assignment. Refer to this
assignment to submit required query statements and screenshots.
1. SQL query statements (15 statements, 90 points).
2. Screenshots of partial output for questions that require screenshots of output (total 4
screenshots, 10 points). You do not have to show all the rows but make sure your
screenshots show “Total rows loaded” as well.
I also recommend copying and pasting your SQL statements into a word or notepad file and
saving the file in multiple places (e.g., on a computer, email, cloud, external hard drive, etc.). If
you have already experienced losing your hard work at some point then you know how
important having a backup is…at all times. Note that you should execute (run) the query for
each question only once and do not need to store your queries in SQLiteStudio.
To complete these exercises, you need to consult the textbook (chapter 4) and online help
https://www.w3schools.com/sql/. Keywords are included in the assignment to help you locate
the related online help for each exercise.
Please copy and paste your statements and screenshots of your output to a WORD file. Clearly label your
query statements and output for each requirement below. Put the homework title, your section, and your
name in the Title of the file and name the file the same way “HWTitle_01-JamesBond”. Use your name
and section number as the header in the Word file, such as “01_JamesBond”. Insert page numbers. Points
Page 2 of 3
will be deducted if not following the instructions and you will receive a 0 if not submitted in a WORD
file.
SQL #1 Questions
1) Select all data in a table:
a) Use the Data_Comp table and create a query that displays all the data in this table.
2) Select specific columns:
a) Use the Data_Comp table and create a query that displays only the Total Assets, Total
Liabilities, and Common/Ordinary Equity (AT, LT, & CEQ).
3) Select specific rows:
3.1) WHERE. Use the Data_CEO table and create a query that displays:
a) all CEOs that are female.
b) all the data that does not include fiscal year (FYEAR) 2019.
c) CEOs whose salary is less than 1200, and whose total compensation (TDC1) is greater
than 15000.
d) CEOs with a percentage of total shares owned (SHROWN_TOT_PCT) greater than 50%
or who receive stock awards (STOCK_AWARDS) greater than 12000.
Also submit a screenshot of output 3.1.d.
3.2) ORDER BY. Use the Data_Comp table and create a query that displays all rows sorting
by:
a) company market value (MV) in descending order.
b) company market value for only fiscal year 2021 in ascending order, using ASC as
ascending.
3.3) LIKE and WILDCARDS. Use the Data_ Environmental table and create a query that
shows:
a) all companies with “INC” in their company name (CONAME). (Do not need to consider
the duplication of names)
b) unique company names that have “CORP” in their name.
Also submit a screenshot of output 3.3.b.
4) ALIASES and CASE. Create new columns (fields) in the results.
Note that aliases are temporary…and not stored for reuse in a query.
a) Use the Data_CEO table and create a query that shows two calculated fields. One field
named CEO containing the CEO’s first and last name defined as (EXEC_FNAME ||’ ‘||
EXEC_LNAME). The other field named StockPlusOptionAwards showing the sum of the
IN THIS ASSIGNMENT YOU WILL NEED TO DOWNLOAD EVERYTHING FIRST BEFORE GETTING STARTED. UNDERSTAND THAT YOU WILL HAVE TO DOWNLOAD SOME DATA SETS THAT I CANNOT PROVIDE!