SQL

The objective of this lab is to give you practice in writing SQL queries and using the sqlite3 program to work with databases.

To pick up the files you will need for this lab, go to your individual repository and do a git pull upstream master. You will then have a lab2 directory seeded with .csv files.

You should start by reviewing the SQL lecture notes from module 3. We included the data and script to construct the FEC database in your lab2 directory, so you can follow along with the notes.

For each task, your solution should go in the file with the corresponding name. For example, the solution for task 1 goes in the file named task1.sql.

Data

The data we will be using represents companies, their stocks, and the stock market indices that they belong to.

We will be working with three types of data: company information, index information, and price information. Each row in the company data contains a ticker symbol (7), name (100), and sector (50) for a company. Each row in the index data contains a ticker symbol and the name of an index (10). And finally, a row in the price data includes a ticker symbol and a price. The numbers in parenthesis specify an upper bound on the length of the longest string for that field.

You may assume that the ticker symbol is a key for the company data and the price data. In contrast, a given ticker symbol may appear more than once in the index data. For example, all the stocks in the DJIA are also in the SP500 index.

Tasks

Task 1

Write a set of SQL statements and sqlite import commands to create three tables—company_info, index_info, and prices—and import the associated data into the tables from the files we have provided in your lab2 directory.

Task 2

Write a SQL query to identify the names of all the companies in the “Services” sector.

Task 3

Write a query that produces a table with the names of all the sectors.

Task 4

Write a query to determine the number of companies in each sector. The output should be sorted in increasing order by sector name.

Task 5

Write a query to determine the names of the stocks in the Dow Jones Industrial Average (DJIA).

Task 6

Write a query to determine the top five sectors (by number of stocks) in the SP500 index.

Task 7

Write a query to determine the names of the companies that appear in both the DJIA and the SP500 index. Hint: use a three-way join (x JOIN y JOIN z).