How can I write a SQL query to display ID, First Name, Last Name of all players with more than 2000-CodePudding

I am new to SQL and DB management. I am working on writing queries based on a schema which you can find below. This is an exercise for me to get familiar reading, writing queries on SQL Server for my job. Could you please help me out defining query based on the schema and simply explain the logic?

Thanks a lot!

SQL Server is my DBMS and here are the question

Display ID, First Name, Last Name, and Hits to display all players with more than 2000 career hits.

Data Schema

CodePudding user response：

I guess you could do it with a join and a group

select p.MLB_PLAYERS.FIRST_NAME,
       p.MLB_PLAYERS.LAST_NAME,
       p.MLB_PLAYERS.ID,
       count(g.KEY_GAMES_STATS.HITS) as hits
from   MLB_PLAYERS p
  left join KEY_GAMES_STATS on p.ID = g.ID -- not sure how to link there 2 tables
group by p.MLB_PLAYERS.FIRST_NAME,
         p.MLB_PLAYERS.LAST_NAME,
         p.MLB_PLAYERS.ID
having count(g.KEY_GAMES_STATS.HITS) > 2000

CodePudding user response：

This one you can get by typing this query in Microsoft SQL Server

SELECT
MLB_PLAYERS.FIRST_NAME,
MLB_PLAYERS.LAST_NAME,
MLB_PLAYERS.ID,
CAREER_STATS.HITS
FROM
MLB_PLAYERS LEFT JOIN KEY_GAMES_STATS on MLB_PLAYERS.ID=CAREER_STATS.ID
WHERE
CAREER_STATS.HITS>2000

So you have a simple structure to follow:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

But you decide to get only 3 of them, which is select, from and where. By SELECT you decide which columns you wanna have as an output. Then in FROM you have to choose tables from which you wanna take your variables. But if you decide to use 2 different tables you need to join them. I used left join because I wanted to match hits to existing players. We can match them by similar key, in this case this is their ID. And eventually, you can use where to apply conditions to your queries