SQL Command Query Programming In The Jupyter Notebook Please submit your SQL queries and a pdf using the Jupyter Notebook for each sub-question listed belo

SQL Command Query Programming In The Jupyter Notebook Please submit your SQL queries and a pdf using the Jupyter Notebook for each sub-question listed below.

1 Part I

Submit the queries for table creation and data import.

Load the two files: stock.txt and compustat.csv.

In stock.txt, the columns are identifier (integer), date (use numeric), ticker, company name, price, volume,

outstanding shares.

In compustat.csv, the columns are identifier (integer), date (use numeric), year, quarter, month, ticker, cusip code (character), company name, total assets, outstanding shares, total liabilities, cik code (integer), price.

Use compustat table to answer the following questions, and submit all queries you used:

1. The number of records inside the table.

2. The number of tickers inside the table.

3. The earliest and latest date of all records.

4. Do all firms have a price? If no, what is the number of records with price data missing.

Use compustat table to answer the following questions, and submit all queries you used:

1. Calculate the ratio of total liabilities/total assets for all firms, named as debt to asset ratio”, and the multiplication of price and outstanding shares, named as market capital”. In the output, you need to display 5 columns: date, ticker, debt to asset

ratio”, market capital” and the difference between total assets and market capital.

2. Export the previous output into a csv file.

2 Part II

Use COMPUSTAT dataset to answer the following questions:

Get the 3 stock tickers (tic) who has the largest average total assets (atq) in fyearq 2016. (Hint: you can use order and limit to perform this result).

List all company name and the value of outstanding

shares (cshoq) when the largest outstanding shares

between 2015 to 2017 is larger than 500.

Use COMPUSTAT dataset to perform the following operations:

Add a column ceq (common equity) with double type.

Set the value in ceq to the multiplication of cshoq and prccq.

Get the number of NULL value in ceq column. Please submit your SQL queries and a pdf using the
Jupyter Notebook for each sub-question listed below.
1
Part I
Submit the queries for table creation and data import.
• Load the two files: stock.txt and compustat.csv.
• In stock.txt, the columns are identifier (integer), date
(use numeric), ticker, company name, price, volume,
outstanding shares.
• In compustat.csv, the columns are identifier (integer), date (use numeric), year, quarter, month, ticker,
cusip code (character), company name, total assets,
outstanding shares, total liabilities, cik code (integer), price.
Use compustat table to answer the following questions,
and submit all queries you used:
1. The number of records inside the table.
2. The number of tickers inside the table.
3. The earliest and latest date of all records.
4. Do all firms have a price? If no, what is the number
of records with price data missing.
1
Use compustat table to answer the following questions,
and submit all queries you used:
1. Calculate the ratio of total liabilities/total assets
for all firms, named as “debt to asset ratio”, and
the multiplication of price and outstanding shares,
named as “market capital”. In the output, you need
to display 5 columns: date, ticker, “debt to asset
ratio”, “market capital” and the difference between
total assets and market capital.
2. Export the previous output into a csv file.
2
Part II
Use COMPUSTAT dataset to answer the following questions:
• Get the 3 stock tickers (tic) who has the largest average total assets (atq) in fyearq 2016. (Hint: you
can use order and limit to perform this result).
• List all company name and the value of outstanding
shares (cshoq) when the largest outstanding shares
between 2015 to 2017 is larger than 500.
Use COMPUSTAT dataset to perform the following operations:
• Add a column ceq (common equity) with double
type.
• Set the value in ceq to the multiplication of cshoq
and prccq.
• Get the number of NULL value in ceq column.
2
• Set the value in ceq to the difference between total
asset (atq) to total liabilities (ltq) if ceq is NULL
after executing the previous query.
• Get the number of NULL value in ceq column.
• Remove the column fyr.
3

Purchase answer to see full
attachment