detective-sql/query-2.html at main · Jamie228/detective-sql · GitHub

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
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <link rel="apple-touch-icon" sizes="180x180" href="/detective-sql/apple-touch-icon.png">
    <link rel="icon" type="image/png" sizes="32x32" href="/detective-sql/favicon-32x32.png">
    <link rel="icon" type="image/png" sizes="16x16" href="/detective-sql/favicon-16x16.png">
    <link rel="manifest" href="/detective-sql/site.webmanifest">

    <link rel="stylesheet" href="styles/main.css">

    <script src="https://kit.fontawesome.com/9477a9faa7.js" crossorigin="anonymous"></script>

    <link rel="preconnect" href="https://fonts.gstatic.com">
    <link href="https://fonts.googleapis.com/css2?family=Fredoka+One&family=Poppins&display=swap" rel="stylesheet">

    <link rel="stylesheet" href="https://unpkg.com/tippy.js@6/animations/scale.css" />

    <script src="https://cdnjs.cloudflare.com/ajax/libs/tingle/0.15.3/tingle.min.js"
        integrity="sha512-plGUER9JkeEWPPqQBE4sdLqBoQug5Ap+BCGMc7bJ8BXkm+VVj6QzkpBz5Yv2yPkkq+cqg9IpkBaGCas6uDbW8g=="
        crossorigin="anonymous"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/tingle/0.15.3/tingle.min.css"
        integrity="sha512-j1u8eUJ4f23xPPxwOrLUPQaCD2dwzNqqmDDcWS4deWsMv2ohLqmXXuP3hU7g8TyzbMSakP/mMqoNBYWj8AEIFg=="
        crossorigin="anonymous" />

    <script src="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.59.4/codemirror.min.js"
        integrity="sha512-M1DpFDDFHNgGdgVsSkTP1sjfWC7mQFJM3obQo8gyKHbZmbmr95Kl9wPYa5T70BFQBi0+/TlcG6IZDLowJCbKBQ=="
        crossorigin="anonymous"></script>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.59.4/codemirror.min.css"
        integrity="sha512-xIf9AdJauwKIVtrVRZ0i4nHP61Ogx9fSRAkCLecmE2dL/U8ioWpDvFCAy4dcfecN72HHB9+7FfQj3aiO68aaaw=="
        crossorigin="anonymous" />
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.59.4/theme/nord.min.css"
        integrity="sha512-sPc4jmw78pt6HyMiyrEt3QgURcNRk091l3dZ9M309x4wM2QwnCI7bUtsLnnWXqwBMECE5YZTqV6qCDwmC2FMVA=="
        crossorigin="anonymous" />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/codemirror/5.59.4/mode/sql/sql.min.js"
        integrity="sha512-dfObApt1XdGl62IJLrjbIOc9QtnRORA5TCwdnJkSj6C/KjwMz2L/Sc4WlcrgAuWoY+n5xTf6NMMojoUOlgwjug=="
        crossorigin="anonymous"></script>

    <title>Unauthorised Transaction: The Masked Gnome</title>
</head>

<body>
    <div class="hero-slim">
        <h1>Unauthorised Transaction: The Masked Gnome</h1>
        <p>Violet Hatpin does not keep an organised database; it's truly a miracle she even managed to set one up. The
            structure on this is particularly iffy, but luckily it doesn't look like she has many records in it. Maybe
            we should just return all the records?</p>
    </div>

    <br>

    <div class="h5-wrap">
        <iframe src="https://h5p.org/h5p/embed/1123930" width="1090" height="677" frameborder="0"
            allowfullscreen="allowfullscreen"
            allow="geolocation *; microphone *; camera *; midi *; encrypted-media *"></iframe>
        <script src="https://h5p.org/sites/all/modules/h5p/library/js/h5p-resizer.js" charset="UTF-8"></script>
    </div>

    <div class="explan">
        <p>Good job on that first query. For this one, we need to return all the <span id="record_pop" class="tt">records</span> in the <span id="database_pop"
                class="tt">database</span>. The first
            thing to consider is that not all queries need a <code>WHERE</code> clause - you only need it if you want to
            refine the data being returned.</p>
        <p>Secondly, if you'd like to return all the columns, you don't have to waste time typing out all the column
            names. Just use <code>*</code> in their place instead.</p>
    </div>

    <style>
        .content-grid th:hover {
            cursor: default;
        }
    </style>

    <div class="content-grid">

        <div>
            <h2 class="center">Table Structure: <code>purchases</code></h2>
            <div>
                <table class="structure">
                    <thead>
                        <tr>
                            <th>customer_description <br><em>VARCHAR(60)</em></th>
                            <th>items_purchased <br><em>VARCHAR(30)</em></th>
                            <th>amount_paid <br><em>DECIMAL</em></th>
                        </tr>
                    </thead>
                    <tbody id="query-return">
                        <tr>
                            <td>&hellip;</td>
                            <td>&hellip;</td>
                            <td>&hellip;</td>
                        </tr>
                    </tbody>
                </table>
            </div>
        </div>
        <style>
            #reload {
                font-size: 16px;
            }
        </style>
        <div class="query-region">
            <h2 class="center">Query Writer</h2>
            <div id="how-to" class="center">
                <p><em>How do I use this?</em></p>
            </div>
            <div id="error" class="center"></div>
            <textarea id="code" rows="3"></textarea>
            <div class="center"><button onclick="runQuery()" class="center" id="run-button"><i
                        class="fas fa-database"></i> Run Query</button>
                <button id="reload"><i class="fas fa-sync-alt"></i> Reload DB</button></div>
        </div>
    </div>

    <div id="test"></div>

    <br>
    <br>

    <script src="js/sql-wasm.js"></script>
    <script src="js/main.js"></script>
    <script>
        const exercise = new Exercise('costume', "White jacket, chef's hat. Baker?", "customer_description",
            "narrative3.html");

        var editor = CodeMirror.fromTextArea(document.getElementById('code'), {
            lineNumbers: true,
            mode: 'text/x-sql',
            smartIndent: true,
            indentWithTabs: true,
            lineWrapping: false,
            scrollbarStyle: "null",
            theme: 'nord',
            viewportMargin: Infinity
        });

        function run(query) {
            try {
                var stmt = database.exec(query);
                return stmt;
            } catch (err) {
                var error_div = document.getElementById("error");
                error_div.innerHTML = "";
                console.log(err.message);
                var error_info = document.createElement("p");
                error_info.innerText = err.message;
                error_div.appendChild(error_info);
                return false;
            }
        }

        function runQuery() {
            var query = editor.getValue();
            var result = run(query);
            if (result !== false) {
                showResults(document.getElementById('test'), result, query);
            }
        }
    </script>
    <script>
        getDatabase('costume_shop.db');
        var reload = document.getElementById('reload');
        reload.addEventListener('click', function () {
            reloadDB('costume_shop.db');
        });
    </script>

    <script type="text/javascript">
        var modal = new tingle.modal({
            footer: false,
            closeMethods: ['overlay', 'escape', 'button'],
            closeLabel: 'Close'
        });

        modal.setContent(`
        <h2>Using the Query Writer</h2>
        <p>The query writer works a lot like the query builder, but you have to do more typing!</p>
        <p>Instead of tapping the buttons, it's time to use the skills you've learnt to write out your own queries. We use a special plugin to make writing code as realistic as possible. This means that you can lay your code out however you want! It can become really useful as your queries get bigger.</p>
        <p>Don't be afraid of jumping onto the next line by pressing <code>ENTER</code>. As long as you don't break up a word, you can spread your query out over several lines to make it more readable.</p>
        <p>When you're done, hit 'Run Query'. If you're just practicing SQL and accidentally delete the table, the database or alter the table schema, just press 'Reload DB'.</p>
        <p>Happy Querying!</p>
        `);

        var info_modal_trigger = document.getElementById('how-to');
        info_modal_trigger.addEventListener('click', function () {
            modal.open();
        });
    </script>

    <script src="https://unpkg.com/@popperjs/core@2"></script>
    <script src="https://unpkg.com/tippy.js@6"></script>

    <script>
        tippy('#database_pop', {
            content: `A collection of one or more tables to store the data.
            These tables are often linked through relationships.`,
            animation: 'scale'
        });

        tippy('#record_pop', {
            content: 'A single entry in the database. Also known as a row in a table.',
            animation: 'scale'
        });
    </script>
</body>

</html>