SQL Assignment 1 (72 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).

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.

1. SQL query statements (21 statements, 63 points).
2. Screenshots of partial output for questions that require screenshots of output (total 4 screenshots, 8 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 isat 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.

SQL #1 Questions

1. Select all data in a table:
2. Use the Data_Comp table and create a query that displays all the data in this table.

1. Select specific columns:
2. Use the Data_Comp table and create a query that displays only the Total Assets, Total Liabilities, and Common/Ordinary Equity (AT, LT, & CEQ).

1. 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) WHERE. Use the Data_Comp table and create a query that displays all:

a) rows from fiscal year 2021.

b) income taxes paid (TXPD) that are less than total income taxes (TXT).

3.3) DISTINCT. Create a query that displays:

a) all unique companies (GVKEY is the unique company identifier) and fiscal year(fyear) using the Data_Environmental table.

3.4) 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.5) 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.5.b.

c) display the data of all companies with "international" in their company name. (Do not need to consider the duplication of names)

1. ALIASES and CASE. Create new columns (fields) in the results.

Note that aliases are temporaryand not stored for reuse in a query.

1. Use the Data_CEO table and create a query that shows two calculated fields. One field named CEOcontaining the CEOs first and last name defined as (EXEC_FNAME ||' '|| EXEC_LNAME). The other field named StockPlusOptionAwards showing the sum of the CEOs stock and option awards, defined as STOCK_AWARDS + OPTION_AWARDS. Display fyear as well.

1. Use the Data_CEO table and create a query that shows two calculated fields. The first field named CEOcontaining the CEOs 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.

1. 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.

b) Using the CEOCompensation view that you created in 5a, create a query that only shows the rows where CEOs earned greater than 20 million (only the rows which display Yes).

1. GROUP and AGGREGATE (e.g., MIN, AVG, MAX, COUNT, SUM, ROUND):
2. 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.
3. 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
4. Using the Data_Environmental table, create a query that shows for each company (the PERMNO is unique for each company): the PERMNO, the company name (coname), calculation of the sum of the total presence (Total_presence) and name this field AllPresence, average presence (Total_presence) and name this field AvgPresence, round the AvgPresence to 2 decimal places.