1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
# name: test/sql/subquery/test_neumann.test
# description: Test subqueries from the paper 'Unnesting Arbitrary Subqueries'
# group: [subquery]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE students(id INTEGER, name VARCHAR, major VARCHAR, year INTEGER)
statement ok
CREATE TABLE exams(sid INTEGER, course VARCHAR, curriculum VARCHAR, grade INTEGER, year INTEGER)
statement ok
INSERT INTO students VALUES (1, 'Mark', 'CS', 2017)
statement ok
INSERT INTO students VALUES (2, 'Dirk', 'CS', 2017)
statement ok
INSERT INTO exams VALUES (1, 'Database Systems', 'CS', 10, 2015)
statement ok
INSERT INTO exams VALUES (1, 'Graphics', 'CS', 9, 2016)
statement ok
INSERT INTO exams VALUES (2, 'Database Systems', 'CS', 7, 2015)
statement ok
INSERT INTO exams VALUES (2, 'Graphics', 'CS', 7, 2016)
query TTI
SELECT s.name, e.course, e.grade FROM students s, exams e WHERE s.id=e.sid AND e.grade=(SELECT MAX(e2.grade) FROM exams e2 WHERE s.id=e2.sid) ORDER BY name, course;
----
Dirk Database Systems 7
Dirk Graphics 7
Mark Database Systems 10
query TTI
SELECT s.name, e.course, e.grade FROM students s, exams e WHERE s.id=e.sid AND (s.major = 'CS' OR s.major = 'Games Eng') AND e.grade <= (SELECT AVG(e2.grade) - 1 FROM exams e2 WHERE s.id=e2.sid OR (e2.curriculum=s.major AND s.year>=e2.year)) ORDER BY name, course;
----
Dirk Database Systems 7
Dirk Graphics 7
query TT
SELECT name, major FROM students s WHERE EXISTS(SELECT * FROM exams e WHERE e.sid=s.id AND grade=10) OR s.name='Dirk' ORDER BY name
----
Dirk CS
Mark CS
|
