0

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

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

Data Schema

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
contodore
  • 13
  • 2
  • 1
    I've removed the conflicting tags. [[tag:mysql]] is a completely different product to SQL Server, and you have tagged [[tag:sql-server-2008]] and [[tag:sql-server-2005]] as well. – Thom A Oct 25 '21 at 11:30
  • 2
    By the way, if you are new to SQL, I **strongly** recommend not using SQL Server 2008 or SQL Server 2005 as they are both *completely* unsupported; for over 2 and 5 years respectively. If you want to learn SQL, I suggest using a supported version. – Thom A Oct 25 '21 at 11:31
  • 1
    Finally, don't ask multiple questions in a question. Just ask 1. If you have multiple questions, you need to post multiple questions; ensuring that each question on it's own is complete, and meets the guidelines. – Thom A Oct 25 '21 at 11:32
  • Hello Larnu, thank you for the feedback, i edited my question according to that. Additionally thanks for the advices on learning path too. Cheers – contodore Oct 25 '21 at 11:49
  • 1
    This, now looks like it asking for *homework help*. Such questions must include a summary of the work you've done so far to solve the problem, and a description of the difficulty you are having solving it. [What topics can I ask about here?](https://stackoverflow.com/help/on-topic) You can find more information on how to ask a homework question in the FAQ: [How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822/2029983) – Thom A Oct 25 '21 at 11:52

2 Answers2

0

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

Mateusz Domaradzki
  • 41
  • 3
  • Hello Mateusz, thank a lot for feedback and your answer. Based on the information you say, can i also use right join and modify it all the way around or it doesn't work that way? – contodore Oct 25 '21 at 13:50
  • Secondly, there is another question related to the same table. 2. Display ID, First Name, Last Name, Team, Division, Start Date, and End Date for players who have played for an American League (AL) team. Can you please review my code if that works? SELECT MLB_PLAYERS.FIRST_NAME, MLB_PLAYERS.LAST_NAME, MLB_PLAYERS.ID, MLB_TEAMS.LOCATION, MLB_TEAMS.DIVISION, PLAYED_FOR.START DATE PLAYED_FOR.END_DATE PLAYED_FOR.TEAM MLB_TEAMS.LOCATION FROM MLB_PLAYERS LEFT JOIN PLAYED_FOR on MLB_PLAYERS.ID= PLAYED_FOR.ID and MLB_TEAMS.LOCATION WHERE MLB_TEAMS.LOCATION = USA – contodore Oct 25 '21 at 13:52
  • Q1: Sure you can use right join. Here you have a topic to SQL joins types https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join – Mateusz Domaradzki Oct 25 '21 at 13:57
0

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
GuidoG
  • 11,359
  • 6
  • 44
  • 79