Useful stock SQL datasets?

Question

Does anyone know of any resources that provide good, useful stock datasets? For example, I've downloaded a SQL script that includes all of the U.S. states, cities, and zipcodes. This saved me a lot of time in a recent application where I wanted to be able to do lookups by geography. Are any of you aware of other useful datasets that are freely available for download?

For example:

• Blacklisted IP addresses
• Names of colleges/universities
• Names of corporations/stock symbols

Anyone have any recommendations?

EDIT:

As an example, here is the location where I found a MySQL script containing all of the U.S. zip codes and their corresponding latitude/longitude. Has anyone else found similarly useful datasets in SQL that can be easily imported and used?

http://www.chrissibert.com/blog/wp-content/uploads/2009/06/zipcodes.7z

EDIT 2:

To clarify what type of datasets I'm talking about... I'm referring to datasets that can be immediately useful for applications, can be applied across a variety of scenarios, and typically represent information that is easy to find for small cases but harder to compile for larger data sets. The zip code database is a great example to me. It's not hard to get the lat/long for a single given zip code. But, it's a bit more time consuming to get the values for all valid zip codes in the U.S. This data is also not useful to a single industry or business sector, but can be applied across a range of applications.

Resee

...blacklisted for what? – Extrajudicial

Why don't you post up where you got the State|City|Zip SQL script to start with? That would kick this post off to a good start. – Thermotaxis

Possible repeat of #2252644 – Balfore

Blacklisted as known malicious IP addresses, for example. – Resee

Just to mention, US Zip codes do change over time. A general list is probably good enough, but if you need pinpoint accuracy you have to get the periodic updates ($$) from the USPS. – Akmolinsk

Answer 1

Lots of links to open data sets here:

http://readwrite.com/2008/04/09/where_to_find_open_data_on_the/

although I doubt any of them will generate SQL statements for you.

Maisey

Answer 2

Shadowman, better if you say detail list of what you want.

• Blacklisted IP addresses - Ad? Xxx? Fraud?
• Names of colleges/universities - All in the world? Wouldn't it be too much?

Here is an idea how to drop down a list of something - this is how I do that:

For example, I need a list of colleges/universities in California.

1. I google for: colleges california wikipedia. Then open the first found item there;
2. By using mouse I select all the colleges and universities from there to clipboard;
3. Open Excel and paste copied names into the first row+column;

4. In the second cell of the first row write templated script, like:

   ="INSERT INTO Colleges (state, name) VALUES ('CA', '" & RC[-1] & "');"
   

   This should produce something like

   INSERT INTO Colleges (state, name) VALUES ('CA', 'Academy of Art University, San Francisco');
   INSERT INTO Colleges (state, name) VALUES ('CA', 'Allied American University, Laguna Hills (Online)');
   INSERT INTO Colleges (state, name) VALUES ('CA', 'American Jewish University, Los Angeles');
   INSERT INTO Colleges (state, name) VALUES ('CA', 'American Sports University, San Bernardino');
   INSERT INTO Colleges (state, name) VALUES ('CA', 'Anaheim University, Anaheim (Online)');
   INSERT INTO Colleges (state, name) VALUES ('CA', 'Antioch University, Culver City');
   -- etc...
   

5. Then just copy generated script and use it for your database

Discontinue

Answer 3

http://www.data.gov/ has a lot of different datasets but most are not "stock".

Heliotropism

Answer 4

EDIT: I will leave my previous answer. If you want to convert arbitrary CSV into SQL scripts for database use, read below. Otherwise, the Chinook Database offers excellent sample data with scripts that are compatible with a variety of relational databases.

---

I was looking for sample basic SQL-like data sets to assist in teaching a friend how to make SQL queries. Some of the links posted here were no longer available, so I'll post what I ended up using.

There are some pretty cool CSV data available from SpatialKey here.

The CSV data can be used, if that's what you need, but I wanted the data in a SQLite database, so I used this handy CSV->SQL online converter.

After I downloaded the SQL, I ran the following:

At shell:

sqlite3 <database_file>

In SQLite shell:

.load <path to SQL script file>

Conlee

Answer 5

Stock symbols are problematic, they can be different for every trade execution venue and pretty much all are held as protected intellectual property of the venue or data vendor, e.g. Thomson Reuters, Bloomberg, Nasdaq, NYSE.

Tarnation

Answer 6

If you're looking for time series data, check out Quandl. The great thing here is that it has tons of different data sets (stocks, economics, health, education, etc.) but accessible all by one easy, RESTful API. If programming isn't your thing, then there is a free Excel plug in that lets you easily grab the data into your spreadsheet.

Karyotin

Answer 7

The MySQL documentation site has a list to a downloadable dataset already in SQL format, ready for use in a database.

1. Download the dataset, such as the world database: http://downloads.mysql.com/docs/world_innodb.sql.gz
2. Change directory into the folder you downloaded to, e.g. cd Downloads
3. Unzip gunzip world_innodb.sql.gz
4. Login to mysql and create a world database

$ mysql mysql> create database world mysql> quit

5. Import the contents of the mysqldump file using cat

cat world_innodb.sql | mysql world

This dataset is idea for learners, looking to practice their SQL.

Hiragana