CREATE TABLE nba_players(player_id INT,
								player_name VARCHAR(80),
								team VARCHAR(50),
								position_name VARCHAR(50),
								height INT,
								points_per_game DOUBLE,
								assists_per_game DOUBLE,
								rebounds_per_game DOUBLE, 
								total_games INT)

INSERT INTO nba_players (player_id, player_name, team, position_name, height, points_per_game, assists_per_game, rebounds_per_game, total_games) 
VALUES
    (1, 'LeBron James', 'Los Angeles Lakers', 'Forward', 206, 28.7, 7.0, 7.9, 1420),
    (2, 'Stephen Curry', 'Golden State Warriors', 'Guard', 191, 27.0, 6.7, 5.2, 900),
    (3, 'Giannis Antetokounmpo', 'Milwaukee Bucks', 'Forward', 211, 29.9, 5.6, 11.0, 714),
    (4, 'Nikola Jokić', 'Denver Nuggets', 'Center', 211, 24.8, 10.0, 10.8, 741),
    (5, 'Joel Embiid', 'Philadelphia 76ers', 'Center', 213, 30.6, 4.2, 10.2, 576),
    (6, 'Jayson Tatum', 'Boston Celtics', 'Forward', 203, 26.9, 4.6, 8.1, 564),
    (7, 'Luka Dončić', 'Dallas Mavericks', 'Guard', 201, 27.4, 8.6, 8.0, 462),
    (8, 'Devin Booker', 'Phoenix Suns', 'Guard', 198, 26.8, 4.8, 4.2, 613),
    (9, 'Ja Morant', 'Memphis Grizzlies', 'Guard', 193, 27.3, 8.2, 5.9, 326),
    (10, 'Zion Williamson', 'New Orleans Pelicans', 'Forward', 201, 27.0, 4.6, 7.0, 154),
    (11, 'Anthony Davis', 'Los Angeles Clippers', 'Forward/Center', 208, 26.7, 2.3, 9.2, 700),
    (12, 'Damian Lillard', 'Portland Trail Blazers', 'Guard', 191, 25.4, 7.3, 4.1, 801),
    (13, 'Jimmy Butler', 'Miami Heat', 'Forward', 201, 22.4, 5.2, 5.9, 822),
    (14, 'Karl-Anthony Towns', 'Minnesota Timberwolves', 'Center', 211, 24.6, 4.5, 10.8, 580),
    (15, 'Donovan Mitchell', 'Cleveland Cavaliers', 'Guard', 191, 25.9, 4.5, 4.1, 542),
    (16, 'Trae Young', 'Atlanta Hawks', 'Guard', 188, 26.1, 10.2, 3.0, 445),
    (17, 'Shai Gilgeous-Alexander', 'Oklahoma City Thunder', 'Guard', 198, 31.4, 5.5, 4.8, 378),
    (18, 'Kyrie Irving', 'Dallas Mavericks', 'Guard', 191, 27.1, 5.1, 5.0, 736),
    (19, 'Chris Paul', 'Phoenix Suns', 'Guard', 183, 10.7, 10.0, 4.5, 1365),
    (20, 'Kawhi Leonard', 'Los Angeles Clippers', 'Forward', 201, 25.5, 5.2, 6.5, 743);
    
  SELECT player_name, team FROM nba_players #all players and their teams 
  
  SELECT * FROM nba_players WHERE height > 205 #players above 205 cm
  
  SELECT * FROM nba_players WHERE team = 'Los Angeles Lakers' #all players from Lakers
  
  SELECT * FROM nba_players WHERE position_name = 'guard' #all guards
  
  SELECT player_name, team nba_players, points_per_game FROM nba_players ORDER BY points_per_game DESC # players by ppg
  
  SELECT player_name, team nba_players, points_per_game FROM nba_players ORDER BY points_per_game LIMIT 5 #top 5 players by ppg
  
  SELECT SUM(height) / COUNT(player_id) FROM nba_players #average height
  
  SELECT team, COUNT(*) 
FROM nba_players 
GROUP BY team; #number of teams  nedosiel som na to sam

SELECT total_games, player_name FROM nba_players ORDER BY total_games DESC LIMIT 1 #player with highest games

SELECT player_name, points_per_game, assists_per_game FROM nba_players WHERE points_per_game > 25 AND assists_per_game > 6 #condition

SELECT player_name FROM nba_players WHERE team LIKE 'Los Angeles%'; #players with teams from los Angeles

SELECT player_name, position_name FROM nba_players WHERE position_name = 'Guard' OR POSITION_name = 'Forward' #all guards and forwards

SELECT position_name, player_name, points_per_game FROM nba_players ORDER BY position_name

COUNT(position_name) FROM nba_players  

SELECT SUM(points_per_game) / COUNT(position_name) FROM nba_players ORDER BY position_name

SELECT POSITION_name, AVG(points_per_game) AS avg_points_per_game
FROM nba_players 
GROUP BY position_name; #average points by position

SELECT player_name, total_games FROM nba_players WHERE total_games > 600 #players with more than 600 games

SELECT player_name, points_per_game, team
FROM nba_players 
GROUP BY team; #best ppg sorted by team



SELECT player_name, points_per_game 
FROM nba_players WHERE height >= 210 #ppg from players above 210 cm

SELECT player_name, points_per_game 
FROM nba_players WHERE player_name = 'a'

SELECT player_name FROM nba_players WHERE player_name LIKE '%a%'; #players with letter a in their names

SELECT player_name, team FROM nba_players WHERE team NOT LIKE 'Los Angeles%'; #players with teams not from LA

SELECT player_name, position_name, points_per_game FROM nba_players
WHERE position_name = 'Guard' AND points_per_game >= 25; #all guards above 25 ppg

SELECT POSITION_name, AVG(height) AS avg_height
FROM nba_players 
GROUP BY position_name; #average height by position

SELECT player_name, team 
FROM nba_players 
WHERE SUBSTRING(player_name, 1, 1) = SUBSTRING(team, 1, 1);


SELECT player_name, team, points_per_game, total_games FROM nba_players 
WHERE total_games > 600 AND points_per_game > 25 ORDER BY points_per_game DESC #players with more than 600 games and more than 25 ppg, desc order

SELECT player_name, height  FROM nba_players WHERE player_name LIKE '%a%' AND height > 205 ;

SELECT player_name, height 
FROM nba_players 
WHERE (player_name LIKE 'J%' OR player_name LIKE 'K%') AND height > 205;

SELECT player_name FROM nba_players WHERE player_name LIKE '%a%' AND team LIKE '%b%' ; #players with a in their name and also with team with b in their team

SELECT player_name, team FROM nba_players WHERE (player_name LIKE 'J%' OR player_name LIKE 'K%') AND (team LIKE 'l%' OR team LIKE 'm%') ;

CREATE VIEW player_overview SELECT player_id, player_name, height FROM nba_players

DELIMITER //

CREATE PROCEDURE get_players()
BEGIN
SELECT player_name, team
FROM nba_players;
END //

CALL get_players() //


CREATE PROCEDURE get_players_by_city(IN city_name(VARCHAR(255))
BEGIN
SELECT player_name, team
FROM nba_players
WHERE team = city_name;
END //

DELIMITER ;

CALL get_players_by city('Phoenix Suns');

DELIMITER //

CREATE PROCEDURE get_players_by_city(IN city_name VARCHAR(255))
BEGIN
    SELECT player_name, team
    FROM nba_players
    WHERE team = city_name //
END //

DELIMITER ;

CALL get_players_by_city('Phoenix Suns');

DELIMITER //


CREATE PROCEDURE get_players_by_city(IN city_name VARCHAR(255)),
BEGIN
    SELECT player_name, team
    FROM nba_players
    WHERE team = city_name //
END //
DELIMITER ;

CALL get_players_by_city('Phoenix Suns');