Database Systems - SQL Query

Location of Database: https://bit.ly/2DBuUIJ

Schemas: https://pastebin.com/dHw7epUH

Database Svstems Construct a single SQL query to generate the answer to each of the below questions Use the baseball database ( https://bit.ly/2DBuUIJ) A schema, specifying name and order of columns, is provided for each question A few useful notes and clarifications: Batting average is defined as h/ab (hits divided by at-bats) Salary data is only available starting in 1985. Any questions that involve salary information assume a natural join (i.e., don't try to pull in data from years without salary data) Players are chosen for the "All Star Game" each year. A given player may appear in the all star game in multiple years. 1. The number of pitchers with more than >) 40 saves (sv) in a single year since (>-) 1975. Note that a 2. The number of players under 6 feet (-) 1975 where the manager with the most wins also managed the team that won 4. The combined team salaries for each year's world series winner in descending order, with the highest 5. Franchise name and average total yearly franchise attendance from (>) 1997 to the present (use 6. First and last name and number of All Star appearances by the ten players on the ballot for the Hall of pitcher may play multiple stints in a given year. (count) that integer arithmetic will apply and needs to be taken into account. (count) the world series, in ascending order with the earliest year first. (years) combined team salary first. (year, salary) attendance from teams table, only consider the number of home games) (franchise, attendance Fame in 2000 with the most All Star appearances. Note that having a tuple in the halloffame table for a given year indicates that a player is on the ballot for that year. (first, last, appearances) 7. Career (total) batting average (label the column career avg) of players born after in or after (>) 1970, ordered by average, highest to lowest. Note that your calculations should erclude any players who have no at-bats, as they have no batting average. (playerid, careeravg) 8. Ratio of average All Star player salaries to average player salaries for each year, in order of year, earliest years first. (year, ratio) 9. Average combined team salary for each year, in order of year, earliest years first. (year, salary) 10. The school name and number of All Star players for the ten colleges with the most All Star players. List the school with the most players first. A player counts as an All Star player regardless of how many times thev've plaved in the All Star game. (school name, count) Database Svstems Construct a single SQL query to generate the answer to each of the below questions Use the baseball database ( https://bit.ly/2DBuUIJ) A schema, specifying name and order of columns, is provided for each question A few useful notes and clarifications: Batting average is defined as h/ab (hits divided by at-bats) Salary data is only available starting in 1985. Any questions that involve salary information assume a natural join (i.e., don't try to pull in data from years without salary data) Players are chosen for the "All Star Game" each year. A given player may appear in the all star game in multiple years. 1. The number of pitchers with more than >) 40 saves (sv) in a single year since (>-) 1975. Note that a 2. The number of players under 6 feet (-) 1975 where the manager with the most wins also managed the team that won 4. The combined team salaries for each year's world series winner in descending order, with the highest 5. Franchise name and average total yearly franchise attendance from (>) 1997 to the present (use 6. First and last name and number of All Star appearances by the ten players on the ballot for the Hall of pitcher may play multiple stints in a given year. (count) that integer arithmetic will apply and needs to be taken into account. (count) the world series, in ascending order with the earliest year first. (years) combined team salary first. (year, salary) attendance from teams table, only consider the number of home games) (franchise, attendance Fame in 2000 with the most All Star appearances. Note that having a tuple in the halloffame table for a given year indicates that a player is on the ballot for that year. (first, last, appearances) 7. Career (total) batting average (label the column career avg) of players born after in or after (>) 1970, ordered by average, highest to lowest. Note that your calculations should erclude any players who have no at-bats, as they have no batting average. (playerid, careeravg) 8. Ratio of average All Star player salaries to average player salaries for each year, in order of year, earliest years first. (year, ratio) 9. Average combined team salary for each year, in order of year, earliest years first. (year, salary) 10. The school name and number of All Star players for the ten colleges with the most All Star players. List the school with the most players first. A player counts as an All Star player regardless of how many times thev've plaved in the All Star game. (school name, count)