add test scripts from bordo

diff --git a/ex1/ex15a.sh b/ex1/ex15a.sh
new file mode 100755 (executable)
index 0000000..391a49c
--- /dev/null
+++ b/ex1/ex15a.sh
@@ -0,0 +1,9 @@
+#!/bin/sh
+
+EXPLAIN="EXPLAIN(ANALYZE,COSTS)"
+
+echo "ex1.5.a unoptimized"
+echo "${EXPLAIN} SELECT distinct(displayname) FROM users u WHERE id IN (SELECT owneruserid FROM posts p WHERE p.viewcount > u.views);" | psql
+
+echo "ex1.5.a optimized"
+echo "${EXPLAIN} SELECT distinct(displayname) FROM posts p JOIN users u ON u.id = p.owneruserid WHERE p.viewcount > u.views;" | psql

diff --git a/ex1/ex15b.sh b/ex1/ex15b.sh
new file mode 100755 (executable)
index 0000000..6aefd85
--- /dev/null
+++ b/ex1/ex15b.sh
@@ -0,0 +1,9 @@
+#!/bin/sh
+
+EXPLAIN="EXPLAIN(ANALYZE,COSTS)"
+
+echo "ex1.5.b unoptimized"
+echo "${EXPLAIN} SELECT score FROM comments WHERE lower(substring(text for 3)) = 'yes';" | psql
+
+echo "ex1.5.b optimized"
+echo "${EXPLAIN} SELECT score FROM comments WHERE text ILIKE 'yes\%';" | psql

diff --git a/ex1/ex15c.sh b/ex1/ex15c.sh
new file mode 100755 (executable)
index 0000000..230a74f
--- /dev/null
+++ b/ex1/ex15c.sh
@@ -0,0 +1,9 @@
+#!/bin/sh
+
+EXPLAIN="EXPLAIN(ANALYZE,COSTS)"
+
+echo "ex1.5.c unoptimized"
+echo "${EXPLAIN} SELECT DISTINCT postid FROM votes v WHERE (SELECT COUNT(*) FROM votes v2 WHERE v2.postid = v.postid AND v2.votetypeid = 2) = (SELECT COUNT(*) FROM votes v2 WHERE v2.postid = v.postid);" | psql
+
+echo "ex1.5.c optimized"
+echo "${EXPLAIN} SELECT DISTINCT postid FROM votes WHERE postid NOT IN (SELECT postid FROM votes WHERE votetypeid != 2);" | psql

diff --git a/ex1/ex15d.sh b/ex1/ex15d.sh
new file mode 100755 (executable)
index 0000000..ad413c1
--- /dev/null
+++ b/ex1/ex15d.sh
@@ -0,0 +1,17 @@
+#!/bin/sh
+
+EXPLAIN="EXPLAIN(ANALYZE,COSTS)"
+#EXPLAIN=""
+
+echo "ex1.5.d unoptimized"
+echo "${EXPLAIN} SELECT p.*, c.*, u.* FROM posts p, comments c, users u, badges b WHERE c.postid=p.id AND u.id=p.owneruserid AND u.upvotes+3 >= (SELECT AVG(upvotes) FROM users WHERE u.creationdate > c.creationdate) AND EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid > p.id) AND u.id = b.userid AND (b.name SIMILAR TO 'Autobiographer' OR b.name SIMILAR TO 'Supporter');" | psql
+
+echo "ex1.5.d optimized"
+echo "${EXPLAIN} SELECT p.*, c.*, u.* FROM posts p
+JOIN users u ON p.owneruserid = u.id
+JOIN badges b on u.id = b.userid
+JOIN comments c ON p.id = c.postid
+WHERE b.name IN ('Autobiographer','Supporter')
+       AND p.id < (SELECT MAX(relatedpostid) FROM postlinks)
+       AND u.upvotes+3 >=
+               (SELECT AVG(upvotes) FROM users WHERE u.creationdate > c.creationdate);" | psql