Using MySQL to analyze given database.
Programming
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 identifi er (integer), date (use numeric), ticker, company name, price, volume,
outstanding shares.
? In compustat.csv, the columns are identifi er (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 fi rms, 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.
