/
join.clj
92 lines (76 loc) · 2.6 KB
/
join.clj
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
(ns seaquell.zoo.join
(:refer-clojure
:exclude [alter distinct drop group-by into set update partition-by when])
(:require [seaquell.core :refer :all]))
;; The following queries are valid solutions (as of 7/21/2020) to the
;; tutorial at http://sqlzoo.net/wiki/The_JOIN_operation
;; Each query is named for the question it answers.
;;
;; To try them out, open a repl and issue the commands below.
;; Submitting the resulting SQL into the tutorial should produce
;; correct answers.
(comment
(use 'seaquell.core)
(use 'seaquell.zoo.join)
(select$ q1))
(def q1
(select
[:matchid :player]
(from :goal)
(where {:teamid "GER"})))
(def q2
(select
[:id :stadium :team1 :team2]
(from :game)
(where {:id 1012})))
(def q3
(select [:player :teamid :stadium :mdate]
(from :game (join :goal (on {:id :matchid})))
(where {:teamid "GER"})))
(def q4
(select [:team1 :team2 :player]
(from :game (join :goal (on {:id :matchid})))
(where [:like :player "Mario%"])))
(def q5
(select [:player :teamid :coach :gtime]
(from :goal (join :eteam :on {:teamid :id}))
(where [<= :gtime 10])))
(def q6
(select [:mdate :teamname]
(from :game (join :eteam :on {:team1 :eteam.id}))
(where {:coach "Fernando Santos"})))
(def q7
(select [:player]
(from :goal (join :game (on {:id :matchid})))
(where {:stadium "National Stadium, Warsaw"})))
(def q8
(select-distinct [:player]
(from :game (join :goal (on {:id :matchid})))
(where '(and (or (= team1 "GER") (= team2 "GER"))
(not= teamid "GER")))))
(def q9
(select [:teamname [count :gtime]]
(from :eteam (join :goal :on {:teamid :id}))
(group-by :teamname)))
(def q10
(select [:stadium [count :gtime]]
(from :goal (join :game (on {:id :matchid})))
(group-by :stadium)))
(def q11
(select [:matchid :mdate [count :gtime]]
(from :game (join :goal (on {:id :matchid})))
(where '(or (= team1 "POL") (= team2 "POL")))
(group-by :matchid :mdate)))
(def q12
(select [:matchid :mdate [count :gtime]]
(from :game (join :goal (on {:id :matchid})))
(where {:teamid "GER"})
(group-by :matchid :mdate)))
(def q13
(select
'[mdate
team1, (sum (cond (= teamid team1) 1 :else 0)) :as score1
team2, (sum (cond (= teamid team2) 1 :else 0)) :as score2]
(from :game (left-join :goal :on {:matchid :id}))
(group-by :mdate :matchid :team1 :team2)
(order-by :mdate :matchid :team1 :team2)))