· 6 years ago · Jun 29, 2019, 02:52 PM
1CREATE TABLE IF NOT EXISTS xbox (
2 game_id char(4) NOT NULL default '',
3 game_name varchar(32) default NULL,
4 developer varchar(32) default NULL,
5 PRIMARY KEY (game_id)
6);
7
8INSERT INTO xbox (game_id, game_name, developer) VALUES
9('1001', 'Sunset Overdive', 'Insomniac Games'),
10('1002', 'Quantum Break', 'Remedy Entertainment'),
11('1003', 'Gears of War 4', 'The Coalition'),
12('1004', 'Halo 5', '343 Industries'),
13('1005', 'Forza 6', 'Turn10 Studios');
14
15
16CREATE TABLE IF NOT EXISTS playstation (
17 game_id char(4) NOT NULL default '',
18 game_name varchar(32) default NULL,
19 developer varchar(32) default NULL,
20 PRIMARY KEY (game_id)
21);
22
23INSERT INTO playstation (game_id, game_name, developer) VALUES
24('2001', 'Bloodborne', 'From Software'),
25('2002', 'The Witness', 'Thekla'),
26('2003', 'Destiny', 'Bungie'),
27('2004', 'Black Ops 3', 'Treyarch'),
28('2005', 'DriveClub', 'Evolution');
29
30
31CREATE TABLE IF NOT EXISTS pc (
32 game_id char(4) NOT NULL default '',
33 game_name varchar(32) default NULL,
34 developer varchar(32) default NULL,
35 PRIMARY KEY (game_id)
36);
37
38INSERT INTO pc (game_id, game_name, developer) VALUES
39('3001', 'Fallout 4', 'Bethesda'),
40('3002', 'Tomb Raider', 'Crystal Dynamics'),
41('3003', 'Overwatch', 'Blizzard'),
42('3004', 'Rocket League', 'Psyonix'),
43('3005', 'DiRT Rally', 'Codemasters');
44
45
46CREATE TABLE IF NOT EXISTS nintendo (
47 game_id char(4) NOT NULL default '',
48 game_name varchar(32) default NULL,
49 developer varchar(32) default NULL,
50 PRIMARY KEY (game_id)
51);
52
53INSERT INTO nintendo (game_id, game_name, developer) VALUES
54('4001', 'Mario Kart 8', 'Nintendo'),
55('4002', 'Mario Maker', 'Nintendo'),
56('4003', 'Mario Party 9', 'Nintendo'),
57('4004', 'Bayonetta 2', 'Nintendo'),
58('4005', 'Splatoon', 'Nintendo');
59
60
61CREATE TABLE IF NOT EXISTS sales (
62 customer_id char(10) NOT NULL default '',
63 game_id char(4) NOT NULL default '',
64 quantity int(11) default NULL,
65 paid double default NULL,
66 payment varchar(32) default NULL,
67 PRIMARY KEY (game_id)
68);
69
70INSERT INTO sales (customer_id, game_id, quantity, paid, payment) VALUES
71('1234567890', '1002', 1, 79.99, 'Debit'),
72('0987654321', '2003', 1, 50, 'Credit'),
73('1122334455', '2001', 2, 120, 'Cash'),
74('6677889900', '4004', 3, 110.49, 'Cash'),
75('1357924680', '3002', 1, 50, 'Credit');
76
77
78CREATE TABLE IF NOT EXISTS products (
79 game_id char(10) default NULL,
80 platform varchar(32) default NULL,
81 stock int(11) default NULL,
82 price double default NULL,
83 PRIMARY KEY (game_id)
84);
85
86INSERT INTO products (game_id, platform, stock, price) VALUES
87('1001', 'xbox', 33, 69.99),
88('1002', 'xbox', 42, 79.99),
89('1003', 'xbox', 50, 89.99),
90('1004', 'xbox', 80, 59.99),
91('1005', 'xbox', 10, 49.99),
92('2001', 'playstation', 12, 55.99),
93('2002', 'playstation', 44, 34.99),
94('2003', 'playstation', 89, 45.99),
95('2004', 'playstation', 65, 39.99),
96('2005', 'playstation', 73, 19.99),
97('3001', 'pc', 50, 69.99),
98('3002', 'pc', 41, 49.99),
99('3003', 'pc', 77, 59.99),
100('3004', 'pc', 24, 19.99),
101('3005', 'pc', 19, 24.99),
102('4001', 'nintendo', 10, 19.99),
103('4002', 'nintendo', 17, 39.99),
104('4003', 'nintendo', 33, 28.99),
105('4004', 'nintendo', 53, 34.99),
106('4005', 'nintendo', 97, 15.99);