· 6 years ago · Jul 22, 2019, 08:08 AM
1CREATE TABLE IF NOT EXISTS `contest` (
2 `id` int(7) NOT NULL AUTO_INCREMENT,
3 `grp` int(7) NOT NULL,
4 `name` varchar(16) COLLATE utf8_unicode_ci NOT NULL,
5 `cost` decimal(10,2) NOT NULL,
6 `pts` int(7) NOT NULL,
7 PRIMARY KEY (`id`),
8 KEY `grp` (`grp`),
9 KEY `cost` (`cost`),
10 KEY `pts` (`pts`)
11) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=89 ;
12
13--
14-- Dumping data for table `contest`
15--
16
17INSERT INTO `contest` (`id`, `grp`, `name`, `cost`, `pts`) VALUES
18(1, 1, 'Bob', 7.82, 79),
19(2, 1, 'Mary', 7.34, 81),
20(3, 1, 'Tim', 7.57, 80),
21(4, 2, 'Fred', 10.01, 67),
22(5, 2, 'Claire', 9.99, 68),
23(6, 2, 'John', 9.98, 69),
24(7, 3, 'Steve', 8.57, 93),
25(8, 3, 'Pam', 8.82, 91),
26(9, 3, 'Sam', 8.13, 91),
27(10, 4, 'Tom', 10.43, 85),
28(11, 4, 'Dick', 10.35, 97),
29(12, 4, 'Harry', 10.39, 94),
30(13, 5, 'Bert', 7.50, 75),
31(14, 5, 'Sara', 8.50, 85),
32(15, 5, 'Terry', 9.50, 95),
33(16, 6, 'Frank', 7.88, 51),
34(17, 6, 'Bill', 7.77, 53),
35(18, 6, 'Greg', 7.93, 58),
36(19, 7, 'Dave', 8.75, 66),
37(20, 7, 'Jack', 7.88, 61),
38(21, 7, 'Jill', 7.44, 64),
39(22, 8, 'Jeff', 10.50, 45),
40(23, 8, 'Will', 9.50, 55),
41(24, 8, 'Kara', 8.50, 65),
42(25, 1, 'Karen', 7.41, 74),
43(26, 2, 'Alan', 7.52, 75),
44(27, 3, 'Barry', 7.61, 76),
45(28, 4, 'Corey', 7.72, 77),
46(29, 5, 'Ken', 7.83, 78),
47(30, 6, 'Gary', 7.88, 78),
48(31, 7, 'Henry', 7.80, 79),
49(32, 8, 'Jeb', 7.94, 80),
50(33, 1, 'Kim', 10.80, 98),
51(34, 2, 'Amy', 10.70, 97),
52(35, 3, 'Ben', 10.60, 96),
53(36, 4, 'Mel', 10.50, 95),
54(37, 5, 'Nancy', 10.40, 94),
55(38, 6, 'Nelly', 10.30, 93),
56(39, 7, 'Polly', 10.20, 92),
57(40, 8, 'Robin', 10.10, 91),
58(41, 1, 'Bart', 6.10, 61),
59(42, 2, 'Annie', 6.20, 62),
60(43, 3, 'Allie', 6.30, 63),
61(44, 4, 'Lester', 6.40, 64),
62(45, 5, 'Violet', 6.50, 65),
63(46, 6, 'Victor', 6.60, 66),
64(47, 7, 'Kate', 6.70, 67),
65(48, 8, 'Jake', 6.80, 68),
66(49, 1, 'Noah', 8.90, 89),
67(50, 2, 'Lucas', 8.80, 88),
68(51, 3, 'James', 8.70, 87),
69(52, 4, 'Jim', 8.60, 86),
70(53, 5, 'Ryan', 8.50, 85),
71(54, 6, 'Dylan', 8.40, 84),
72(55, 7, 'Owen', 8.30, 83),
73(56, 8, 'Wyatt', 8.20, 82),
74(57, 1, 'Evan', 9.10, 91),
75(58, 2, 'Tyler', 9.20, 92),
76(59, 3, 'Norah', 9.30, 93),
77(60, 4, 'Maria', 9.40, 94),
78(61, 5, 'Julia', 9.50, 95),
79(62, 6, 'Alice', 9.60, 96),
80(63, 7, 'Tony', 9.70, 97),
81(64, 8, 'Joe', 9.80, 98),
82(65, 1, 'Asher', 6.20, 62),
83(66, 2, 'Austin', 6.40, 64),
84(67, 3, 'Blake', 6.60, 66),
85(68, 4, 'Chris', 6.80, 68),
86(69, 5, 'Chloe', 7.00, 70),
87(70, 6, 'Colin', 7.20, 72),
88(71, 7, 'Dirk', 7.40, 74),
89(72, 8, 'Dwight', 7.60, 76),
90(73, 1, 'Elijah', 7.70, 77),
91(74, 2, 'Ella', 7.50, 75),
92(75, 3, 'Elliot', 7.30, 73),
93(76, 4, 'Isaac', 7.10, 71),
94(77, 5, 'Kostas', 6.90, 69),
95(78, 6, 'Layla', 6.70, 67),
96(79, 7, 'Leo', 6.50, 65),
97(80, 8, 'Lily', 6.30, 63),
98(81, 1, 'Mark', 7.71, 77),
99(82, 2, 'Zach', 7.81, 78),
100(83, 3, 'Zoe', 7.91, 79),
101(84, 4, 'Rudy', 8.01, 80),
102(85, 5, 'Riley', 8.11, 81),
103(86, 6, 'Perry', 8.21, 82),
104(87, 7, 'Nolan', 8.31, 83),
105(88, 8, 'Reggie', 8.41, 84);
106
107SELECT c1.name AS A, c2.name AS B, c1.cost + c2.cost AS Price, c1.pts + c2.pts AS Points FROM `contest` c1, `contest` c2 WHERE c1.`grp`=1 AND c2.`grp`=2 AND c1.cost + c2.cost < 17 ORDER BY Points DESC LIMIT 100;
108
109SELECT c1.name AS A, c2.name AS B, c3.name AS C, c1.cost + c2.cost + c3.cost AS Price, c1.pts + c2.pts + c3.pts AS Points FROM `contest` c1, `contest` c2, `contest` c3 WHERE c1.`grp`=1 AND c2.`grp`=2 AND c3.`grp`=3 AND c1.cost + c2.cost + c3.cost < 28 ORDER BY Points DESC LIMIT 100;
110
111SELECT c1.name AS A, c2.name AS B, c3.name AS C, c4.name AS D, c1.cost + c2.cost + c3.cost + c4.cost AS Price, c1.pts + c2.pts + c3.pts + c4.pts AS Points FROM `contest` c1, `contest` c2, `contest` c3, `contest` c4 WHERE c1.`grp` =1 AND c2.`grp` = 2 AND c3.`grp` = 3 AND c4.`grp` = 4 AND c1.cost + c2.cost + c3.cost + c4.cost < 40 ORDER BY Points DESC LIMIT 100;
112
113SELECT c1.name AS A, c2.name AS B, c3.name AS C, c4.name AS D, c5.name AS E, c1.cost + c2.cost + c3.cost + c4.cost + c5.cost AS Price, c1.pts + c2.pts + c3.pts + c4.pts + c5.pts AS Points FROM `contest` c1, `contest` c2, `contest` c3, `contest` c4, `contest` c5 WHERE c1.`grp` =1 AND c2.`grp` = 2 AND c3.`grp` = 3 AND c4.`grp` = 4 AND c5.`grp` = 5 AND c1.cost + c2.cost + c3.cost + c4.cost + c5.cost <50 ORDER BY Points DESC LIMIT 100;
114
115SELECT c1.name AS A, c2.name AS B, c3.name AS C, c4.name AS D, c5.name AS E, c6.name AS F, c1.cost + c2.cost + c3.cost + c4.cost + c5.cost + c6.cost AS Price, c1.pts + c2.pts + c3.pts + c4.pts + c5.pts + c6.pts AS Points FROM `contest` c1, `contest` c2, `contest` c3, `contest` c4, `contest` c5, `contest` c6 WHERE c1.`grp` =1 AND c2.`grp` = 2 AND c3.`grp` = 3 AND c4.`grp` = 4 AND c5.`grp` = 5 AND c6.`grp` = 6 AND c1.cost + c2.cost + c3.cost + c4.cost + c5.cost + c6.cost <60 ORDER BY Points DESC LIMIT 100;
116
117SELECT c1.name AS A, c2.name AS B, c3.name AS C, c4.name AS D, c5.name AS E, c6.name AS F, c7.name AS G, c1.cost + c2.cost + c3.cost + c4.cost + c5.cost + c6.cost + c7.cost AS Price, c1.pts + c2.pts + c3.pts + c4.pts + c5.pts + c6.pts + c7.pts AS Points FROM `contest` c1, `contest` c2, `contest` c3, `contest` c4, `contest` c5, `contest` c6, `contest` c7 WHERE c1.`grp` =1 AND c2.`grp` = 2 AND c3.`grp` = 3 AND c4.`grp` = 4 AND c5.`grp` = 5 AND c6.`grp` = 6 AND c7.`grp` = 7 AND c1.cost + c2.cost + c3.cost + c4.cost + c5.cost + c6.cost + c7.cost < 70 ORDER BY Points DESC LIMIT 100;
118
119SELECT c1.name AS A, c2.name AS B, c3.name AS C, c4.name AS D, c5.name AS E, c6.name AS F, c7.name AS G, c8.name AS H, c1.cost + c2.cost + c3.cost + c4.cost + c5.cost + c6.cost + c7.cost + c8.cost AS Price, c1.pts + c2.pts + c3.pts + c4.pts + c5.pts + c6.pts + c7.pts + c8.pts AS Points FROM `contest` c1, `contest` c2, `contest` c3, `contest` c4, `contest` c5, `contest` c6, `contest` c7, `contest` c8 WHERE c1.`grp` =1 AND c2.`grp` = 2 AND c3.`grp` = 3 AND c4.`grp` = 4 AND c5.`grp` = 5 AND c6.`grp` = 6 AND c7.`grp` = 7 AND c8.`grp` = 8 AND c1.cost + c2.cost + c3.cost + c4.cost + c5.cost + c6.cost + c7.cost + c8.cost < 69 ORDER BY Points DESC LIMIT 100;