Prompts: Review DDL & Generate Test Data

-- The AI's improved design: proper types, a primary key, NOT NULL and a default
CREATE TABLE players_v2 (
    id   INT          PRIMARY KEY,
    name VARCHAR(50)  NOT NULL,
    runs INT          DEFAULT 0
);

INSERT INTO players_v2 (id, name) VALUES (1, 'Shubman Gill');  -- runs gets the default
SELECT * FROM players_v2;

-- Parent table first, then child rows that reference it
CREATE TABLE test_teams (team VARCHAR(10) PRIMARY KEY, team_name VARCHAR(50), city VARCHAR(30));
CREATE TABLE test_players (
    id   INT PRIMARY KEY,
    name VARCHAR(50),
    team VARCHAR(10) REFERENCES test_teams(team),
    runs INT
);

INSERT INTO test_teams VALUES ('RCB', 'Royal Challengers', 'Bengaluru'), ('MI', 'Mumbai Indians', 'Mumbai');
INSERT INTO test_players VALUES (1, 'Virat Kohli', 'RCB', 750), (2, 'Rohit Sharma', 'MI', 500);

-- Check every player's team exists in test_teams
SELECT p.name, p.team, t.team_name
FROM test_players AS p
LEFT JOIN test_teams AS t
    ON p.team = t.team;

-- 🏏 Your turn: the AI generated a player for team 'KXIP', which is not in teams.
-- Run the check below. Which row has a NULL team_name? Fix the INSERT to use a real team.
CREATE TABLE ai_players (id INT, name VARCHAR(50), team VARCHAR(10));
INSERT INTO ai_players VALUES (1, 'Arshdeep Singh', 'KXIP'), (2, 'Shubman Gill', 'GT');

SELECT a.name, a.team, t.team_name
FROM ai_players AS a
LEFT JOIN teams AS t
    ON a.team = t.team;