Help writing some SQL queries

Question

0.00/5 (No votes)

See more:

I was just a little bit confused with the following SQL questions. I'm not sure if I should use any joins or should just use select, count or avg statements. I devised my own question to help me try and understand how to write SQL statements better.

Consider two tables:

CHAMPIONS with columns YEAR, COUNTRY, NAME, ROLE

Listing all players and managers in a world cup winning team. You can assume any person with the same name for the same country in different years is the same person.

GOALS with columns YEAR, COUNTRY, NAME, NUM_GOALS

Listing all goal scorers from any team (ie not just the champions). Non goal-scorers are not included.

So for example entries may look like the below (there are of course many more rows)

CHAMPIONS (~500 rows)

YEAR    COUNTRY NAME    ROLE

2018    France  Didier Deschamps    Manager

2018    France  Hugo Lloris Goalkeeper

2018    France  Paul Pogba  Midfielder

2014    Germany Joachim Loew    Manager

2014    Germany Mesut Ozil  Midfielder

2014    Germany Miroslav Klose  Forward

2002    Brazil  Da Silva    Midfielder

1994    Brazil  Da Silva    Midfielder

1998    France  Didier Deschamps    Midfielder

GOALS (~1700 rows)

YEAR    COUNTRY NAME    NUM_GOALS

2018    England Harry Kane  6

2018    France  Antoine Griezmann   4

2014    Argentina   Lionel Messi    4

2014    Brazil  Fred    1

2010    Germany Thomas Muller   5

2010    Japan   Shinji Okazaki  1

1992    England Gary Linekar    6

Questions 1. Write a query to show how many people have won as player and manager? (eg Didier Deschamps)

Q2. Write a query showing how many times each country has won the world cup for countries winning the world cup at least twice.

Q3. Write a query showing all world cup winning players who have never scored a goal.

Q4. Write a query to identify the golden boot winner (highest goal scorer) in each world cup.

I've tried to answer the first question. I think it's right. I'd appreciate it if anyone could help me with the rest. I would be grateful.

What I have tried:

For Question 1 I came up with this:

SQL

SELECT COUNT(*) AS CHAMPIONS_ROLE FROM CHAMPIONS WHERE ROLE = 'Manager'AND 'Midfielder';

Posted 13-Jun-20 5:15am

Hassan Majid

Updated 13-Jun-20 8:10am

Patrice T

v2

Add a Solution

Comments

[no name] 13-Jun-20 15:24pm

For Question 3 I came up with the following although not sure about the part of just world cup winning players. I can get all players who haven't scored but world cup winning players is bugging me a bit.

3. Write a query showing all world cup winning players who have never scored a goal.

SELECT NAME
FROM GOALS
WHERE NUM_GOALS = 0;

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-06-13T05:47:00

Quote:
For Question 1 I came up with ...

So try it, and see what happens. Does it work? If it doesn't, what does it do?

We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

Just posting your homework questions and hoping we will do them for you isn't going to work.

Patrice T · Answer 2 · 2020-06-13T08:10:00

This query:

SQL

SELECT COUNT(*) AS CHAMPIONS_ROLE FROM CHAMPIONS WHERE ROLE = 'Manager' AND 'Midfielder';

Is not what you think, you should be written like:

SQL

SELECT COUNT(*) AS CHAMPIONS_ROLE FROM CHAMPIONS WHERE ROLE = 'Manager' AND ROLE = 'Midfielder';

But result will always be zero because you are looking for a record where ROLE value is Manager and Midfielder at same time.

[Update]

Quote:
Many thanks for your help. Was just wondering if you could elaborate further on your explanation.

I know languages enough to spot this beginners mistake, but I an not an SQL user.
Have a look at SELF JOINS: SQL Self JOIN[^]

Help writing some SQL queries

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0