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 reportsduring 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 abovedatabase in your computer.To be submitted:Submit each SQL assignment in ONE file. The file should include everything required to besubmitted for that assignment. Note that the required statements may not be covered in myvideo or statements I covered in the video may not be required in this assignment. Refer to thisassignment 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 4screenshots, 10 points). You do not have to show all the rows but make sure yourscreenshots show “Total rows loaded” as well.I also recommend copying and pasting your SQL statements into a word or notepad file andsaving the file in multiple places (e.g., on a computer, email, cloud, external hard drive, etc.). Ifyou have already experienced losing your hard work at some point then you know howimportant having a backup is…at all times. Note that you should execute (run) the query foreach 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 helphttps://www.w3schools.com/sql/. Keywords are included in the assignment to help you locatethe related online help for each exercise.Please copy and paste your statements and screenshots of your output to a WORD file. Clearly label yourquery statements and output for each requirement below. Put the homework title, your section, and yourname in the Title of the file and name the file the same way “HWTitle_01-JamesBond”. Use your nameand section number as the header in the Word file, such as “01_JamesBond”. Insert page numbers. PointsPage 2 of 3will be deducted if not following the instructions and you will receive a 0 if not submitted in a WORDfile.SQL #1 Questions1) 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, TotalLiabilities, 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 greaterthan 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 sortingby:a) company market value (MV) in descending order.b) company market value for only fiscal year 2021 in ascending order, using ASC asascending.3.3) LIKE and WILDCARDS. Use the Data_ Environmental table and create a query thatshows:a) all companies with “INC” in their company name (CONAME). (Do not need to considerthe 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 fieldnamed 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 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 1 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
Page 2 of 3
CEOs’ stock and option awards, defined as STOCK_AWARDS + OPTION_AWARDS. Display
fyear as well.
b) Use the Data_CEO table and create a query that shows two calculated fields. The first
field named CEO containing the CEO’s first and last name. The second field named
CompensationOver20Million which displays “Yes” if total compensation (TDC1) is
greater than “20000” and “No” if it is not. Display fyear as well. Submit a screenshot of
output 4.b.
5) Create VIEWS.
Note that views are virtual tables. If you need to troubleshoot a view at some point you can
simply delete it and recreate it.
a) Copy and paste the query from 4.b and create a view called CEOCompensation based on
this query.
6) GROUP and AGGREGATE (e.g., MIN, AVG, MAX, COUNT, SUM, ROUND):
a) Using the Data_Comp table, create a query that shows for each company, the average
retained earnings (RE) and name this field AverageRE. Use the column PERMNO to
group the companies, which is a unique company identification number.
b) Using the Data_Comp table, create a query that shows for each company, the fiscal year
in which they had the highest amount of debt in current liabilities (DLC). Name this field
HighestCL. Again, use the column PERMNO to group the companies. Display fyear as
well. Also submit a screenshot of output 6.b
Extra Credit: (6 points)
7) LEFT JOIN (same as LEFT OUTER JOIN):
a) Left joining the DATA_CEO and DATA_Environmental tables. Display: PERMNO, the CEO’s
first and last names, Salary (from the Data_CEO table), company name (from the
Data_Environmental table). Link the tables on PERMYR. Also submit a screenshot of output.
Page 3 of 3
ACCTG 333 (Professor Li)
Instructions for downloading SQLiteStudio for DB HW
The installation of SQLiteStudio is very easy compared to larger relational database management
systems (RDBMS) such as MySQL, SQL Server, and Oracle. SQLiteStudio is also available for a number of
operating systems, including MacOS and Windows. This will allow us to focus more on learning SQL
rather than installing databases. Please follow these steps:
Note you can use this version of SQLite for your SQL projects as well.
There are two ways to download SQLiteStudio and install in your computer:
Method 1:
Directly download the correct “InstallSQLiteStudio-3.2.1” from Canvas
Windows: “InstallSQLiteStudio-3.2.1.exe”
Mac: “InstallSQLiteStudio-3.2.1.dmg”
Go to Item 2 in Method 2.
If the file does not work for you, go to Method 2.
Method 2:
1. Go to https://www.dropbox.com/sh/ao4nz2qjfsz2yuy/AABwiiss3do7n0wNecuk-uyna?dl=0
Windows: Click folder win32-inst ➔ download “InstallSQLiteStudio-3.2.1.exe”
Mac: Click folder macosc-inst ➔ download “InstallSQLiteStudio-3.2.1.dmg”
2. Double click on your downloaded file and follow the prompts to complete the installation. (Note the
extra help for MAC users and security permissions if needed on the next page):
…Extra help for MAC users and security permissions:
a) Open the downloaded zip file and click the InstallSQLiteStudio-3.4.1.app icon to install it.
Page 1 of 2
ACCTG 333 (Professor Li)
Instructions for downloading SQLiteStudio for DB HW
b) Because the installer is not downloaded from App Store, you may encounter errors saying that you
cannot open it.
c) Click System Preferences —> Security & Privacy then click Open Anyway button to open the SQLite
Studio installer. The below picture does not contain the Open Anyway button, because the button
has already been clicked on. If you install SQLite Studio for the first time, the Open Anyway button
will be shown.1
d) After the installation, you can find the SQLiteStudio icon in Finder —> Go —-> Applications folder.
You are now ready to begin the SQL assignment .
1 If this does not work temporarily disable Gatekeeper by opening the System Preferences window (click the Apple icon at top-
left corner of your screen or click the System Preferences icon on your dock) and click the Security & Privacy icon. Now click the
lock icon (in the bottom left corner), enter your password, and set the “Allow apps downloaded from” option to “Anywhere.”
Page 2 of 2
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
