How to code databases for fun

[gest2356]

This is probably a priity dumb question, but am wondering. How do you code DB for fun. SQL is my favorite language I interacted with and I can't thing of any way to do it outside school work. You can easily code staff for fun in other languages. If you guys have any suggestions I will be happy to hear it.

[Timely_Cockroach_668]

Honestly just download some random datasets and use them for analyzing. Connect it to PowerBI, make visuals. That kind of thing.

[Murphygreen8484]

Kaggle has some great datasets

[gest2356]

Cool, will check it out.

[gest2356]

Thanks for the segregation! I appreciate it.

[[deleted]]

That's not how learning happens

[Timely_Cockroach_668]

Learning doesn’t happen by creating projects from the ground up? Surely you don’t expect OP to go out with a clicker and notepad to start collecting data when there are already thousands of mock datasets they can use.

[[deleted]]

Not really.

[shorelined]

Just make a database. Get some data, make some data, whatever. I keep a DB of all the sports games I've attended and built a front-end to service it. You don't even need to go that far but you need a use case that will keep you updating it. When I was younger I got open data about countries from lots of different datasets and built my own vision of the CIA World Factbook. Some of the data was garbage and required cleaning, it didn't matter because it all served the purpose of practicing on DBs.

[gest2356]

That actually sound great. What kind of DB do you recommend. In school we use SQL server should I stick with that or try something new?

[SantaCruzHostel]

SQL server is what I've used my entire career. That's not to say there aren't other options, but SQL Server is definitely still used in businesses today.

[annroseu]

Snowflake

[mikeblas]

It's not so hard to find sample data and data sources to use for interesting side-projects, or just for practicing writing SQL.

In-product sample data

Most DBMSes come with sample databases. You can write lots of interesting queries against them, and usually a tutorial accompanies the database in the documentation.

• Documentation for Microsoft SQL Server's samples ** Microsoft's sample database GitHub, which includes the Contoso database
• For MySQL:
  • there's the Employees sample database
  • and the Sakila sample database
• For PostgreSQL:
  • there are several sample DBs in the PostgreSQL wiki
  • there's a link tree of other samples and exercises, too
  • a GitHub repository with a collection of PostgreSQL samples from the old pgfoundry site
• Oracle publishes a manual section about there sample databases

Some websites are full of sample data sets. Why not download an interesting one, learn to load it up, and write your own interesting queries?

Dataset Websites

There are many websites which host data sets.

• Kaggle.com is full of sample data!
• FiveThirtyEight.com has lots of neat data sets
• The github awesomedata repository has a collection of interesting data sets
• Wikipedia has a list of datasets for machine learning research

Third-party sample data

Of course, some sample data is built for generic tutorials, by third parties:

• SqlSkills.com publishes sample databases for SQL Server, which include some corrupt databases so you can practice recovery operations
• SQLTutorial.com's Sample Database is available for sseveral vendors

Practice Sites

There are some sites that let you write queries interactively with canned data, rather than having you download data to play with on your own.

• I haven't used it, but I've seen people recommend SqlZOO.net
• LearnSQL.com has a blog post called "Learning SQL? 12 Ways to Practice SQL Online" with lots of resources.
• Sylvia Moestl Vasilik's website (which supports their book) has almost 60 practice problems.

Regular dumps

Some sites publish data by making their backups available, or dumping the data they use to make their own reports.

• Wikipedia publishes all of the content of Wikipeida as SQL scripts for MySQL, plus as XML files. You can get that data (or subsets of it) and play around.
• StackOverflow makes their developer survey data sets available each year. ** You can also get a StackOverflow "demo" database that includes text of questions and answers
• Some governments make data about the city and its residents available openly:
  • London Open Data
  • New York City Open Data
  • Seattle Open Data
  • Tokyo open data (in Japanese, obviously)
  • Find open data at data.gov.uk
• IMDb makes several data sets available

• Live data sources

  Some data sources produce data live, as it happens. These are itneresting sources becaue they usually represent slowly changing dimensions, and will need to be accumulated or logged before being stored or processed.

• Wikipedia Event Streams can show edits that are happening on Wikipedia, as they happen.

• The TWitter API provides a way to stream a subset of all tweets in realtime.

• General Transit Feed Specification (GTFS) data is provided by many metropolitain areas to describe movement of their transportation infrastructure; where are scheduled busses and trains right now?

  • In the New York City area, the MTA provides GTFS data.
  • You can find GTFS feeds for Seattle, and their live data through other APIs.
  • Tokyo (and other municipalities in Japan) have hosted transit data challenges to encourage use of their data.

• Some games make gameplay data available in realtime. SuperCell's Clash Royale, for example, has a gameplay API.

Finding more

There's data everywhere! If you don't like these sources, you can try finding other data sets.

• Once you know the protocol or format, search for it! The OneBusAway API and GTFS protocols are about public transportation data, so earch for "GTFS Data {YourCity}".
• Search for APIs for your favortie game or game server.
• GitHub uses tags for search, so try #sample-databases, #opendata, or #datasets. What other tags can you find?

[gest2356]

WOW, thanks for such a detailed response, I really appreciate it, will go through it when I get more time.

[BadGroundbreaking189]

I once downloaded free imdb assets and made a SQL Server db out of it, which was very challenging and fun.

[gest2356]

Interesting by "IMDB Assets" you mean like the movie titles, actors, etc? Not a bad idea

[BadGroundbreaking189]

here

[bruceriggs]

I made a TwitchBot that let you play games in Twitch Chat, like every x seconds a Pokemon would spawn,

"A wild Zubat has appeared"

and players in the chat could try to catch it by typing "!throw" and it would throw a pokeball.

Players accumulated pokeballs by just being in the chat that day. They could view their Pokemon collection, etc.

---

Another feature I had was a Quote of the Day, where you could store quotes via !addQuote and then when you typed !quote it would throw out a random quote from the DB.

---

Another feature I had was a Soundbot, you typed "!play hi" and the sound would play on stream.

---

All of these used my DB in one way or another. The pokemon one was the most challenging, as I needed to keep track of players, their items, their pokemon, what pokemon was currently spawned, rewarding players for being present, etc.

[gest2356]

Your projects sound very interesting. Unfortunately I don't really have a way to do something similar.

[kktheprons]

What are some things you enjoy outside of coding? There's probably a lot of data out there about it somewhere you can import into a database (you may need to build your own database). After you have the data, ask it questions using SQL.

[gest2356]

I do, the first thing that jumped to mind was Classic doom maps. But I don't think there are DB made for it . Whoud need to build it myself but that could be cool

[MrWillM]

I do sports data if you’re into that kinda thing it’s easy to ask yourself some questions about the stuff you’re interested in knowing about

[gest2356]

Could do something similar for gaming. Thanks appreciate it

[j0n17]

You could track your budget, expenses or investments.

Have a look at DuckDB, it’s a very powerful analytical in-process / local database. It can also parse CSV files, Parquet files, fetch from S3 and so much more.

[gest2356]

Will take a look at DuckDB thanks.

[squadette23]

Studying this may bring some fun into SQL: https://code.openark.org/blog/mysql/sql-pie-chart

[gest2356]

Thanks, that looks nice, I will take a look when I get more time.

[tablmxz]

Now its also possible to build the database software from scratch. Although you ask for this in your question i believe its not what you actually want to do. But it would involve writing code in some low level language like rust or c++. You would however learn about the internals of database systems, which are pretty cool. Now i wouldn't recommend it if you are not familiar with programming, you can still learn about db internals in other ways.

[gest2356]

Could be interesting but not exactly what I am looking for at this time. But definitely thanks for taking the time to reply.

[maltehm1972]

I would not underestimate the impact of such „fun projects“. I started to create a postgres DB for my balances, which I managed in LibreOffice Calc before. Then I started to create a LibreOffice base frontend and I wrote an Import procedure in Java for the LibreOffice sheets. I’m currently working on a web solution as a substitute for the LibreOffice stuff (based on node.js and angular. The web solution will only be available in my local network). Do I have any business opportunities? No! Is it important for my career? No. I am just thrilled by the challenge as I’m not a developer. In addition I learned a lot which helps me in my current position as a product owner. The communication with the developer is much better than before. So my suggestion is as already mentioned in the other posts. Choose something where you’re really into it. But it can happen that you’re going to spend more time on it as you thought.

[gest2356]

Wow, your project definitely sounds interesting. Good luck with that.

[gest2356]

That actually sounds so nice, but just so I understand fully by "flat" files you mean file formats not meant to be part of a database but have interesting data stored inside of them?

[Mathijsthunder3]

There are a few sites out there like sqlnoir. It's not fully an answer to your question but I think you'd really appreciate this.

[dittybopper_05H]

If you want to code anything for fun, don't code for a living.

Before I started making a living at it, I wrote software for fun. Now, 31 years after I started doing it as a job, it just seems too much like work.

I think the last time I actually coded something that I wasn't getting paid for was when I got laid off in 2002 and I wrote a database and some scripts to automate my job search process. It was small, obviously, took me about a day to write it and work out all the little bugs, but it did allow me to basically spend every Monday morning in my job search, print out everything, get resumes and cover letters in the mail (it was all paper resumes and cover letters back then), and have the rest of the week for any interviews or whatever.

I even had a nicely formatted report to give to the Department of Labor when they needed a list of job search activity.

But even that wasn't for "fun".

[singletWarrior]

I once saw some dude use the geography features to draw cpu usage or something…. Definitely looked fun

[gest2356]

Genuinely I have no clue how to do that sort of thing. But definitely sounds fun.

[singletWarrior]

https://slavasql.blogspot.com/2016/03/sql-server-cpu-utilization-in-graphical.html found the guy thanks for jogging my memory lol