cystanford / sql_nba_data Goto Github PK

DROP TABLE IF EXISTS "height_grades";
CREATE TABLE "height_grades"  (
  "height_level" varchar(255) ,
  "height_lowest" numeric(3, 2) NOT NULL,
  "height_highest" numeric(3, 2) NOT NULL
);

-- ----------------------------
-- Records of height_grades
-- ----------------------------
INSERT INTO "height_grades" VALUES ('A', 2.00, 2.50);
INSERT INTO "height_grades" VALUES ('B', 1.90, 1.99);
INSERT INTO "height_grades" VALUES ('C', 1.80, 1.89);
INSERT INTO "height_grades" VALUES ('D', 1.60, 1.79);

DROP TABLE IF EXISTS "player";
DROP SEQUENCE IF EXISTS "player_id_seq";
CREATE SEQUENCE player_id_seq START 10038;
--select nextval('player_id_seq');

CREATE TABLE "player"  (
  "player_id" int DEFAULT nextval('player_id_seq') ,
  "team_id" int NOT NULL,
  "player_name"  varchar(255) NOT NULL UNIQUE,
  "height" numeric(3, 2) NULL DEFAULT NULL,
  PRIMARY KEY ("player_id")
);

-- ----------------------------
-- Records of player
-- ----------------------------
INSERT INTO "player" 
VALUES 
  (10001, 1001, '韦恩-艾灵顿', 1.93)
 ,(10002, 1001, '雷吉-杰克逊', 1.91)
 ,(10003, 1001, '安德烈-德拉蒙德', 2.11)
 ,(10004, 1001, '索恩-马克', 2.16)
 ,(10005, 1001, '布鲁斯-布朗', 1.96)
 ,(10006, 1001, '兰斯顿-加洛韦', 1.88)
 ,(10007, 1001, '格伦-罗宾逊三世', 1.98)
 ,(10008, 1001, '伊斯梅尔-史密斯', 1.83)
 ,(10009, 1001, '扎扎-帕楚里亚', 2.11)
 ,(10010, 1001, '乔恩-洛伊尔', 2.08)
 ,(10011, 1001, '布雷克-格里芬', 2.08)
 ,(10012, 1001, '雷吉-巴洛克', 2.01)
 ,(10013, 1001, '卢克-肯纳德', 1.96)
 ,(10014, 1001, '斯坦利-约翰逊', 2.01)
 ,(10015, 1001, '亨利-埃伦森', 2.11)
 ,(10016, 1001, '凯里-托马斯', 1.91)
 ,(10017, 1001, '何塞-卡尔德隆', 1.91)
 ,(10018, 1001, '斯维亚托斯拉夫-米凯卢克', 2.03)
 ,(10019, 1001, '扎克-洛夫顿', 1.93)
 ,(10020, 1001, '卡林-卢卡斯', 1.85)
 ,(10021, 1002, '维克多-奥拉迪波', 1.93)
 ,(10022, 1002, '博扬-博格达诺维奇', 2.03)
 ,(10023, 1002, '多曼塔斯-萨博尼斯', 2.11)
 ,(10024, 1002, '迈尔斯-特纳', 2.11)
 ,(10025, 1002, '赛迪斯-杨', 2.03)
 ,(10026, 1002, '达伦-科里森', 1.83)
 ,(10027, 1002, '韦斯利-马修斯', 1.96)
 ,(10028, 1002, '泰瑞克-埃文斯', 1.98)
 ,(10029, 1002, '道格-迈克德莫特', 2.03)
 ,(10030, 1002, '科里-约瑟夫', 1.91)
 ,(10031, 1002, '阿龙-霍勒迪', 1.85)
 ,(10032, 1002, 'TJ-利夫', 2.08)
 ,(10033, 1002, '凯尔-奥奎因', 2.08)
 ,(10034, 1002, '埃德蒙-萨姆纳', 1.96)
 ,(10035, 1002, '达文-里德', 1.98)
 ,(10036, 1002, '阿利兹-约翰逊', 2.06)
 ,(10037, 1002, 'Ike Anigbogu', 2.08);

-- 重复键报错
-- INSERT INTO "player"(team_id, player_name, height) 
-- VALUES (0, 'Ike Anigbogu', 0);
-- SELECT * FROM player WHERE height = 0;

DROP TABLE IF EXISTS "player_score";
CREATE TABLE "player_score"  (
  "game_id" int NOT NULL,
  "player_id" int NOT NULL,
  "is_first" smallint NOT NULL,
  "playing_time" int NOT NULL,
  "rebound" int NOT NULL,
  "rebound_o" int NOT NULL,
  "rebound_d" int NOT NULL,
  "assist" int NOT NULL,
  "score" int NOT NULL,
  "steal" int NOT NULL,
  "blockshot" int NOT NULL,
  "fault" int NOT NULL,
  "foul" int NOT NULL,
  "shoot_attempts" int NOT NULL,
  "shoot_hits" int NOT NULL,
  "shoot_3_attempts" int NOT NULL,
  "shoot_3_hits" int NOT NULL,
  "shoot_p_attempts" int NOT NULL,
  "shoot_p_hits" int NOT NULL
);

-- ----------------------------
-- Records of player_score
-- ----------------------------
INSERT INTO "player_score" 
VALUES
  (10001, 10001, 1, 38, 4, 1, 3, 2, 26, 0, 1, 0, 3, 19, 10, 13, 4, 4, 2)
 ,(10001, 10002, 1, 30, 6, 4, 2, 4, 22, 0, 0, 6, 3, 19, 8, 5, 1, 5, 5)
 ,(10001, 10003, 1, 37, 17, 7, 10, 5, 18, 4, 0, 3, 4, 18, 8, 1, 0, 5, 2)
 ,(10001, 10004, 1, 42, 6, 1, 5, 2, 14, 0, 4, 1, 2, 10, 4, 7, 4, 2, 2)
 ,(10001, 10005, 1, 19, 2, 0, 2, 2, 0, 2, 0, 1, 1, 1, 0, 1, 0, 0, 0)
 ,(10001, 10006, 0, 23, 2, 2, 0, 1, 9, 1, 0, 0, 2, 10, 3, 3, 2, 1, 1)
 ,(10001, 10007, 0, 13, 1, 1, 0, 1, 7, 0, 0, 0, 2, 4, 2, 2, 1, 2, 2)
 ,(10001, 10008, 0, 20, 2, 0, 2, 3, 6, 0, 0, 3, 3, 5, 3, 0, 0, 0, 0)
 ,(10001, 10009, 0, 11, 1, 0, 1, 1, 0, 0, 0, 1, 4, 0, 0, 0, 0, 0, 0)
 ,(10001, 10010, 0, 7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 0, 1, 0, 0, 0)
 ,(10002, 10022, 1, 37, 7, 1, 6, 6, 19, 3, 0, 1, 3, 16, 7, 3, 1, 4, 4)
 ,(10002, 10025, 1, 34, 9, 1, 8, 5, 19, 0, 0, 5, 1, 12, 8, 0, 0, 4, 3)
 ,(10002, 10024, 1, 34, 6, 0, 6, 0, 17, 3, 5, 0, 2, 7, 5, 3, 2, 6, 5)
 ,(10002, 10028, 1, 27, 3, 0, 3, 3, 13, 1, 1, 3, 1, 10, 4, 6, 4, 2, 1)
 ,(10002, 10030, 1, 31, 1, 0, 1, 3, 4, 2, 0, 1, 2, 9, 2, 3, 0, 0, 0)
 ,(10002, 10023, 0, 23, 12, 4, 8, 3, 18, 0, 0, 3, 6, 10, 8, 0, 0, 2, 2)
 ,(10002, 10029, 0, 24, 2, 1, 1, 2, 11, 0, 0, 1, 2, 8, 5, 3, 1, 0, 0)
 ,(10002, 10031, 0, 25, 1, 0, 1, 5, 10, 0, 1, 2, 3, 4, 3, 3, 2, 4, 2)
 ,(10002, 10032, 0, 4, 1, 0, 1, 0, 0, 0, 1, 1, 0, 1, 0, 1, 0, 0, 0);

-- ----------------------------
-- Table structure for team
-- ----------------------------
DROP TABLE IF EXISTS "team";
CREATE TABLE "team"  (
  "team_id" int NOT NULL,
  "team_name" varchar(255)  NOT NULL,
  PRIMARY KEY ("team_id")
);

-- ----------------------------
-- Records of team
-- ----------------------------
INSERT INTO "team" VALUES (1001, '底特律活塞');
INSERT INTO "team" VALUES (1002, '印第安纳步行者');
INSERT INTO "team" VALUES (1003, '亚特兰大老鹰');

DROP TABLE IF EXISTS "team_score";
CREATE TABLE "team_score"  (
  "game_id" int NOT NULL,
  "h_team_id" int NOT NULL,
  "v_team_id" int NOT NULL,
  "h_team_score" int NOT NULL,
  "v_team_score" int NOT NULL,
  "game_date" date NULL DEFAULT NULL,
  PRIMARY KEY ("game_id")
);

-- ----------------------------
-- Records of team_score
-- ----------------------------
INSERT INTO "team_score" VALUES (10001, 1001, 1002, 102, 111, '2019-04-01');
INSERT INTO "team_score" VALUES (10002, 1002, 1003, 135, 134, '2019-04-10');