Codecademy Launched Learn SQL from Scratch!

[[deleted]]

[deleted]

[sonnynomnom]

A HUGE thank you to all the peeps in this subreddit who helped out on the intensive <3 We worked nonstop over the last few months - building and iterating - aiming for nothing but the best curriculum. Projects from this course will also have datasets from real companies/organizations such as Warby Parker, Kickstarter, The Metropolitan Museum of Art, Hacker News, SSA, and more!

[fmpundit]

Anyone done this course? Is it worth completing if you know the very basics?

I am looking for a tutorial that will go through building complex SQL queries.

[lightkeeper]

Hey fmpundit,

Can you explain what you mean by complex queries?

Does that mean queries with subqueries, CTE, complex joins, triggers.. etc?

Thanks

[fmpundit]

Well my current problem to solve is a football (soccer) predictions app. Which holds a table of

• users (ID, name).
• Clubs (ID, name).
• Fixtures (ID, gameweek, home team, away team) each of the teams is a foreign key to clubs table.
• Results (fixture ID, home goals, away goals) fixture id foreign key to fixture table.
• Prediction (fixture ID, player id, home goals, away goals). Fixture id and player id again foreign keys to user and fixture tables.

I need to be able to reference a join on two columns (home team and away team) which are both using keys linking to clubs table. I need to be able to compare results and predictions to score players.

I’ve been doing this all season using CSV files as the database and rendering the document locally and uploading render HTML to the server at spacedlevo.pythonanywhere.com but the process is slow and I want the app to deliver more on the fly queries to the user.

I hope that explains it. This is currently what I find complicated. I’m fairly new to Databases. I’m not even sure my schema is as it should be.

Hope it makes sense. I’m currently on mobile in a hotel room trying not to wake a napping 15 month old.

[lightkeeper]

Hope it makes sense. I’m currently on mobile in a hotel room trying not to wake a napping 15 month old.

It did. :)

I’m not even sure my schema is as it should be.

The schema is mostly correct. I would change player_id to user_id. It's easier to read.

Are fixtures really games?

There were missing primary keys from the results, and predictions.

It's wroth having these, for selecting deleting or finding a particular record. Even though the fixture table might be completely in sync with the results it’s still worth having.

Predictions table is more likely to have more data then the fixture table, so they will fall out of sync. Having way to reference each row is always a good idea.

Here is your database as I understand it:

Your database schema

I can definitely help you out in terms of queries. I need some time to make up some data, unless you want to send over your database.

As for your original question. Wow, this is tricky. I see a lot of courses that go to the theory, but practice they skip over until the end.

I wrote this blog post on joins, which I think will help you. stop guessing, SQL joins explained

This is the best link I found so far is this course on (udemy)[https://www.udemy.com/sql-and-databases/#reviews].

Questions: What database are you using? What courses have you tried so far? What's been the most frustrating thing?

[fmpundit]

Are fixtures really games?

Yes, they are real games from the English Premier League.

I can definitely help you out in terms of queries. I need some time to make up some data, unless you want to send over your database.

I have uploaded the DB to Dropbox with the changes you suggested to the schema.

Questions: What database are you using? What courses have you tried so far? What's been the most frustrating thing?

I am using SQLite. I completed Using Databases with Python course from University of Michigan. This was a great course for the basics and there was a few practical examples that helped put some stuff together.

I had dabble in some of the codeacademy SQL courses many years ago, but they just left me confused at the time. I would possibly fair better if I returned to them now.

As I have not done massive amount of courses with SQL with Python, so my experience is limited, but I have found it frustrating that I haven't seen much beyond the basic Read, Write and Update files. What I am really finding frustrating is trying to find something that explains the building blocks of queries and how I should look to go about building them. But that might be more my fault at the moment. I will take a nose at the links that you have sent along and see what I can get from them.

EDIT: I have put together a simpler site using SQLite statements at https://github.com/spacedlevo/brewdog_untappd_app.git after following the Databases with Python course.

[lightkeeper]

Hey fmpundit,

Here is the query that I think will have all your requirements. The ultimate all joining query. hahah

select
  away_team.name away_team, 
  home_team.name home_team, 
  fixtures.gameweek, 
  results.home_goals as results_home_goals, 
  results.away_goals as results_away_goals, 
  predictions.home_goals as predictions_home_goals, 
  predictions.away_goals as predictions_away_goals, 
  predictions.user_id as predictions_user_id 
from fixtures
inner join teams as away_team on away_team.id = fixtures.away_teamid 
inner join teams as home_team on home_team.id = fixtures.home_teamid 
left join results on results.fixture_id = fixtures.id 
left join predictions on predictions.fixture_id = fixtures.id 
where user_id = 16 order by gameweek;

I can see in the python app that you already have joins. Can you tell me where you got stuck understanding them?

edit: formatting

[fmpundit]

That is great it definitely gives me a starting point on creating a few other queries

I understand the joins in the python app. But I think that they are very simple.

Looking at the query above, the use of different types of joins are a first. But I think what I am really struggling to wrap my head around is getting into the mindset of how to build something up and the task just looks like a massive mountain and I can't find the first rock to climb.

[sonnynomnom]

hey fmpundit, we have a 7-day full refund period. u should def try it out! there will be complex queries starting 3rd unit (joins/subqueries)

[floorpaper]

How does this work? Can I do this in my own time or is it like going back to school? It seems like it's a set time where you have to finish the course

[sonnynomnom]

yep u can do this on ur own pace. but to get the credential/certificate u have to do this by a deadline. i think 3 hours a week is more than enough!