-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsql.html
400 lines (264 loc) · 99.5 KB
/
sql.html
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
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
<!DOCTYPE html>
<!-- saved from url=(0077)https://www.executeprogram.com/courses/everyday-typescript/lessons/namespaces -->
<html class="font-sans mode-dark" lang="en"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>SQL Notes</title>
<meta name="description" content="Learn programming tools like JavaScript, TypeScript, SQL, and regular expressions fast. Interactive lessons with real code examples.">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Execute Program">
<meta name="twitter:description" content="Learn it. Code it. Remember it.">
<meta name="twitter:image" content="https://www.executeprogram.com/images/twitter-card.png">
<meta property="og:title" content="Execute Program">
<meta property="og:type" content="website">
<meta property="og:url" content="https://www.executeprogram.com/courses/everyday-typescript/lessons/namespaces">
<meta property="og:image" content="https://www.executeprogram.com/images/twitter-card.png">
<meta name="execprog-client-version" content="b984e5877a1990312c4a15e5036c7ea74120d45a1094d1356aa71bed198ab5e9">
<meta name="robots" content="index, follow">
<link rel="prefetch" as="fetch" crossorigin="" href="https://www.executeprogram.com/dist/sql-wasm-1.8.0.wasm">
<link rel="preload" as="font" crossorigin="" href="https://www.executeprogram.com/fonts/IBMPlexSans-Regular-Latin1.woff2">
<link rel="preload" as="font" crossorigin="" href="https://www.executeprogram.com/fonts/IBMPlexSans-SemiBold-Latin1.woff2">
<link rel="preload" as="font" crossorigin="" href="https://www.executeprogram.com/fonts/IBMPlexSans-Italic-Latin1.woff2">
<link rel="preload" as="font" crossorigin="" href="https://www.executeprogram.com/fonts/Inconsolata-lgc-ep.woff2">
<link rel="preload" as="font" crossorigin="" href="https://www.executeprogram.com/fonts/Inconsolata-lgc-bold-ep.woff2">
<link rel="stylesheet" media="all" href="style.css">
<link rel="alternate" type="application/rss+xml" title="Execute Program Blog RSS Feed" href="https://www.executeprogram.com/blog/feed.rss">
<script>window.USER = {"kind":"authenticated","id":33541,"email":"kevinyu14@gmail.com","showOverlays":false}</script>
<script type="module" src="./Namespaces Lesson_files/app.KQPGFW7V.js.download"></script>
<link rel="canonical" href="https://www.executeprogram.com/courses/everyday-typescript/lessons/namespaces"></head>
<body class="tone-neutral bg-back flex min-h-full text-base w-full vsc-initialized">
<div class="
bg-front border-base sm:border-l bottom-0
fixed focusable w-full max-w-3xl overflow-x-hidden overflow-y-auto
pb-16 px-2 md:px-4 right-0 shadow-2xl text-base top-0
"><div><div class="font-mono text-md text-muted text-center my-4 pb-4 border-b">SQL Reference<div class="text-sm">For 33/33 lessons.</div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Basic tables <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/basic-tables" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>In SQL, data is stored in tables made up of columns, similar to a spreadsheet.
We can <code>CREATE</code> tables, <code>INSERT</code> data into them, and <code>SELECT</code> the data back out.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span><span class="tok-variableName">email </span>TEXT<span class="tok-punctuation">,</span> <span class="tok-variableName">name </span>TEXT<span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span><span class="tok-variableName">email</span><span class="tok-punctuation">,</span> <span class="tok-variableName">name</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'amir@example.com'</span><span class="tok-punctuation">,</span> <span class="tok-string">'Amir'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">SELECT</span> <span class="tok-operator">*</span> <span class="tok-variableName">FROM </span><span class="tok-variableName">users</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{email: 'amir@example.com', name: 'Amir'}]</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Basic column types <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/basic-column-types" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>The available data types vary from database to database.
SQLite's data types include <code>TEXT</code> (strings), <code>INTEGER</code>, and <code>REAL</code> (numbers with decimal points, stored as floating point numbers).</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">cats</span> <span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-variableName">name </span>TEXT<span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">age </span>INTEGER<span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">weight </span>REAL<span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">No type enforcement in SQLite <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/no-type-enforcement-in-sqlite" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>Most SQL databases enforce column types, but SQLite doesn't.
It will let us insert the wrong type of data into a column.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">rects</span> <span class="tok-punctuation">(</span><span class="tok-variableName">width </span>REAL<span class="tok-punctuation">,</span> <span class="tok-variableName">height </span>REAL<span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">rects</span> <span class="tok-punctuation">(</span><span class="tok-variableName">width</span><span class="tok-punctuation">,</span> <span class="tok-variableName">height</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'oh'</span><span class="tok-punctuation">,</span> <span class="tok-string">'no'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Null <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/null" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>SQL has <code>NULL</code>, which is similar to the nulls in other languages like JavaScript.
If we declare a column as <code>NOT NULL</code>, the database won't allow us to insert nulls.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name </span>TEXT<span class="tok-variableName"> </span>NOT<span class="tok-variableName"> </span>NULL<span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name</span><span class="tok-punctuation">)</span> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-variableName">NULL</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>Error: NOT NULL constraint failed: users.name</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Selecting columns <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/selecting-columns" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>We can select specific columns by naming them in the <code>SELECT</code> statement.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name </span>TEXT<span class="tok-punctuation">,</span> <span class="tok-variableName">logins </span>INTEGER<span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name</span><span class="tok-punctuation">,</span> <span class="tok-variableName">logins</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Amir'</span><span class="tok-punctuation">,</span> <span class="tok-number">1</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">SELECT </span><span class="tok-variableName">name </span><span class="tok-variableName">FROM </span><span class="tok-variableName">users</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{name: 'Amir'}]</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Inserting multiple rows <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/inserting-multiple-rows" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>We can insert multiple rows at the same time.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name</span><span class="tok-punctuation">)</span> <span class="tok-variableName">VALUES</span></div><div class="cm-line"> <span class="tok-punctuation">(</span><span class="tok-string">'Amir'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-punctuation">(</span><span class="tok-string">'Betty'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">SELECT</span> <span class="tok-operator">*</span> <span class="tok-variableName">FROM </span><span class="tok-variableName">users</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{name: 'Amir'}, {name: 'Betty'}]</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Select where <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/select-where" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>We can use <code>WHERE</code> to select only the rows whose data matches a certain condition.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Amir'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Betty'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">SELECT</span> <span class="tok-operator">*</span> <span class="tok-variableName">FROM </span><span class="tok-variableName">users </span><span class="tok-variableName">WHERE </span><span class="tok-variableName">length</span><span class="tok-punctuation">(</span><span class="tok-variableName">name</span><span class="tok-punctuation">)</span> <span class="tok-operator">></span> <span class="tok-number">4</span><span class="tok-string2">`);</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{name: 'Betty'}]</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">No booleans in SQLite <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/no-booleans-in-sqlite" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>Most database systems support booleans natively, but SQLite doesn't.
We can get the same effect with <code>INTEGER</code> columns, using 0 for false and 1 for true.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">cats</span> <span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-variableName">name </span>TEXT<span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">vaccinated </span>INTEGER<span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">cats</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name</span><span class="tok-punctuation">,</span> <span class="tok-variableName">vaccinated</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Ms. Fluff'</span><span class="tok-punctuation">,</span> <span class="tok-number">1</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Selecting expressions <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/selecting-expressions" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p><code>SELECT</code> can be used for more than just tables.
We can select arbitrary expressions.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">SELECT </span><span class="tok-number">1</span> <span class="tok-operator">+</span> <span class="tok-number">1</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{'1 + 1': 2}]</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Updating rows <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/updating-rows" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p><code>UPDATE</code> modifies rows that already exist.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">UPDATE </span><span class="tok-variableName">cats</span></div><div class="cm-line"> <span class="tok-variableName">SET </span><span class="tok-variableName">name</span> <span class="tok-operator">=</span> <span class="tok-string">'Mr. Reeves'</span></div><div class="cm-line"> <span class="tok-variableName">WHERE </span><span class="tok-variableName">name</span> <span class="tok-operator">=</span> <span class="tok-string">'Keanu'</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div><div class="col-span-5 border-base md:border-r"><div class="md:pt-8"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>If we <code>UPDATE</code> without a <code>WHERE</code>, we'll update all rows.
Be careful not to do this accidentally!</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-8"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">cats</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Ms. Fluff'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Keanu'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">UPDATE </span><span class="tok-variableName">cats </span><span class="tok-variableName">SET </span><span class="tok-variableName">name</span> <span class="tok-operator">=</span> <span class="tok-string">'Cat'</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">SELECT</span> <span class="tok-operator">*</span> <span class="tok-variableName">FROM </span><span class="tok-variableName">cats</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{name: 'Cat'}, {name: 'Cat'}]</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Unique constraints <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/unique-constraints" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>A unique constraint ensures that no two rows have the same value in a column or set of columns.
For example, we might want to ensure that each pair of user name and cat name is unique.</p>
</div></div></div><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>This constraint is on two columns, <code>name</code> and <code>cat_name</code>.
There can be duplicate <code>name</code>s, and duplicate <code>cat_name</code>s.
But no row can have the same <code>name</code> and <code>cat_name</code> as another row.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-variableName">name </span>TEXT<span class="tok-variableName"> </span><span class="tok-variableName">NOT </span><span class="tok-variableName">NULL</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">cat_name </span><span class="tok-variableName">TEXT </span><span class="tok-variableName">NOT </span><span class="tok-variableName">NULL</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">UNIQUE</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name</span><span class="tok-punctuation">,</span> <span class="tok-variableName">cat_name</span><span class="tok-punctuation">)</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name</span><span class="tok-punctuation">,</span> <span class="tok-variableName">cat_name</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Amir'</span><span class="tok-punctuation">,</span> <span class="tok-string">'Ms. Fluff'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Amir'</span><span class="tok-punctuation">,</span> <span class="tok-string">'Ms. Fluff'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>Error: UNIQUE constraint failed: users.name, users.cat_name</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Column aliases <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/column-aliases" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>When selecting columns, we can "alias" them to different names.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">cats</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name</span><span class="tok-punctuation">,</span> <span class="tok-variableName">age</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Keanu'</span><span class="tok-punctuation">,</span> <span class="tok-number">2</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">SELECT </span><span class="tok-variableName">name</span><span class="tok-punctuation">,</span> <span class="tok-variableName">age </span><span class="tok-variableName">AS </span><span class="tok-variableName">oldness </span><span class="tok-variableName">FROM </span><span class="tok-variableName">cats</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{name: 'Keanu', oldness: 2}]</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Primary keys <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/primary-keys" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>Primary keys are normally unique identifiers for rows.
We often use integers, but sometimes we use special strings like <a href="https://en.wikipedia.org/wiki/Universally_unique_identifier" target="_blank">UUIDs</a>.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">people</span> <span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-variableName">id </span>INTEGER<span class="tok-variableName"> </span><span class="tok-variableName">PRIMARY </span><span class="tok-variableName">KEY</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">name </span><span class="tok-variableName">TEXT</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div><div class="col-span-5 border-base md:border-r"><div class="md:pt-8"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>Primary keys must be unique.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-8"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">people</span> <span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-variableName">id </span>INTEGER<span class="tok-variableName"> </span><span class="tok-variableName">PRIMARY </span><span class="tok-variableName">KEY</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">name </span><span class="tok-variableName">TEXT </span><span class="tok-variableName">NOT </span><span class="tok-variableName">NULL</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">people</span> <span class="tok-punctuation">(</span><span class="tok-variableName">id</span><span class="tok-punctuation">,</span> <span class="tok-variableName">name</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-number">1</span><span class="tok-punctuation">,</span> <span class="tok-string">'Amir'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-number">1</span><span class="tok-punctuation">,</span> <span class="tok-string">'Betty'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>Error: UNIQUE constraint failed: people.id</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Bind parameters <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/bind-parameters" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>Bind parameters allow us to put placeholders in our SQL, then separately provide values for the placeholders.
They're used when running SQL queries from another programming language.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-comment">// Select the user whose ID is 2.</span></div><div class="cm-line"><span class="tok-keyword">const</span> <span class="tok-variableName tok-definition">users</span> <span class="tok-operator">=</span> <span class="tok-variableName">exec</span><span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-string2">`SELECT * FROM users WHERE id=?`</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-punctuation">[</span><span class="tok-number">2</span><span class="tok-punctuation">]</span></div><div class="cm-line"><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Multiple statements <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/multiple-statements" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>We can combine multiple statements with <code>;</code>.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">SELECT </span><span class="tok-number">1</span><span class="tok-punctuation">;</span> <span class="tok-variableName">SELECT </span><span class="tok-number">2</span> <span class="tok-variableName">AS </span><span class="tok-variableName">two</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{two: 2}]</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Deleting rows <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/deleting-rows" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>The <code>DELETE</code> statement deletes rows.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">DELETE </span><span class="tok-variableName">FROM </span><span class="tok-variableName">users </span><span class="tok-variableName">WHERE </span><span class="tok-variableName">name</span> <span class="tok-operator">=</span> <span class="tok-string">'Betty'</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div><div class="col-span-5 border-base md:border-r"><div class="md:pt-8"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>If we <code>DELETE</code> without a <code>WHERE</code>, we'll delete all rows.
Be careful not to do this accidentally!</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-8"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">DELETE </span><span class="tok-variableName">FROM </span><span class="tok-variableName">users</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Dropping tables and columns <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/dropping-tables-and-columns" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>The <code>DROP</code> statement lets us remove entire tables.
A dropped table doesn't exist at all.
This is different from <code>DELETE</code>, which leaves the table in place but deletes its rows.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">DROP </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">users</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Comments <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/comments" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>We can write single-line comments with <code>--</code> and multi-line comments with <code>/* ... */</code>.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">cats</span> <span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-variableName">id </span>INTEGER<span class="tok-variableName"> </span><span class="tok-variableName">PRIMARY </span><span class="tok-variableName">KEY</span></div><div class="cm-line"> <span class="tok-operator">--</span> <span class="tok-variableName">name </span><span class="tok-variableName">TEXT</span></div><div class="cm-line"> <span class="tok-comment">/* owner_name TEXT */</span></div><div class="cm-line"><span class="tok-string2">`);</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Referencing other tables <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/referencing-other-tables" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>Tables in SQL databases often reference other tables.
Usually, this is done with ID columns.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">people</span> <span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-variableName">id </span>INTEGER<span class="tok-variableName"> </span><span class="tok-variableName">PRIMARY </span><span class="tok-variableName">KEY</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">name </span><span class="tok-variableName">TEXT </span><span class="tok-variableName">NOT </span><span class="tok-variableName">NULL</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-comment">/* The owner_id column is used to reference</span></div><div class="cm-line"><span class="tok-comment"> * users' IDs. */</span></div><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">cats</span> <span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-variableName">owner_id </span>INTEGER<span class="tok-variableName"> </span><span class="tok-variableName">NOT </span><span class="tok-variableName">NULL</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">name </span><span class="tok-variableName">TEXT </span><span class="tok-variableName">NOT </span><span class="tok-variableName">NULL</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Foreign keys <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/foreign-keys" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>When a table references columns from another table, we can configure the database to ensure that referenced records actually exist.
For example, we'd like the database to only accept a cat with an <code>owner_id</code> of 3 if a user with the ID of 3 exists.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">people</span> <span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-variableName">id </span>INTEGER<span class="tok-variableName"> </span><span class="tok-variableName">PRIMARY </span><span class="tok-variableName">KEY</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">name </span><span class="tok-variableName">TEXT </span><span class="tok-variableName">NOT </span><span class="tok-variableName">NULL</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-comment">/* Every cat row's owner_id must reference the</span></div><div class="cm-line"><span class="tok-comment"> * ID of a person row. */</span></div><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">cats</span> <span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-variableName">owner_id </span>INTEGER<span class="tok-variableName"> </span><span class="tok-variableName">REFERENCES </span><span class="tok-variableName">people</span><span class="tok-punctuation">(</span><span class="tok-variableName">id</span><span class="tok-punctuation">)</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">name </span><span class="tok-variableName">TEXT </span><span class="tok-variableName">NOT </span><span class="tok-variableName">NULL</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Comparing with null <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/comparing-with-null" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>Comparisons and mathematical operations on <code>NULL</code> return another <code>NULL</code>.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">SELECT </span><span class="tok-variableName">NULL</span> <span class="tok-operator">=</span> <span class="tok-number">1</span> <span class="tok-variableName">AS </span><span class="tok-variableName">result</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{result: null}]</pre></div></div></div><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">SELECT </span><span class="tok-variableName">NULL</span> <span class="tok-operator">+</span> <span class="tok-number">1</span> <span class="tok-variableName">AS </span><span class="tok-variableName">result</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{result: null}]</pre></div></div></div></div></div><div class="col-span-5 border-base md:border-r"><div class="md:pt-8"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>Even comparing <code>NULL = NULL</code> gives us another <code>NULL</code> back.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-8"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">SELECT </span><span class="tok-variableName">NULL</span> <span class="tok-operator">=</span> <span class="tok-variableName">NULL </span><span class="tok-variableName">AS </span><span class="tok-variableName">result</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{result: null}]</pre></div></div></div></div></div><div class="col-span-5 border-base md:border-r"><div class="md:pt-8"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>If we need to check for whether a value is actually <code>NULL</code>, we can use the <code>IS NULL</code> and <code>IS NOT NULL</code> operators.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-8"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">SELECT </span><span class="tok-number">5</span> <span class="tok-variableName">IS </span><span class="tok-variableName">NOT </span><span class="tok-variableName">NULL </span><span class="tok-variableName">AS </span><span class="tok-variableName">result</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{result: 1}]</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Defaults <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/defaults" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>We can specify default values for columns.
If we insert a row without specifying a value for that column, the default is used instead.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-variableName">name </span>TEXT<span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">logins </span><span class="tok-variableName">INTEGER </span><span class="tok-variableName">NOT </span><span class="tok-variableName">NULL </span><span class="tok-variableName">DEFAULT </span><span class="tok-number">0</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-comment">/* Amir's `logins` will default to 0. */</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name</span><span class="tok-punctuation">)</span> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Amir'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Null in unique constraints <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/null-in-unique-constraints" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>Uniqueness constraints normally disallow duplicate values in the column.
However, duplicate <code>NULL</code>s are always allowed.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span><span class="tok-variableName">email </span>TEXT<span class="tok-variableName"> </span>NULL<span class="tok-variableName"> </span>UNIQUE<span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">users</span> <span class="tok-punctuation">(</span><span class="tok-variableName">email</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-variableName">NULL</span><span class="tok-punctuation">)</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-variableName">NULL</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">SELECT</span> <span class="tok-operator">*</span> <span class="tok-variableName">FROM </span><span class="tok-variableName">users</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{email: null}, {email: null}]</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Constraint analysis <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/constraint-analysis" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-12"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>There are three important questions to ask for every column:</p>
</div></div></div><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><ul>
<li>Should this column be a foreign key?</li>
<li>Can this column be null?</li>
<li>Can multiple rows have the same value for this column?</li>
</ul>
</div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Simple joins <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/simple-joins" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>Joins let us query multiple tables that relate to each other, whether via a foreign key or not.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-comment">/* Select all cats' names along with their</span></div><div class="cm-line"><span class="tok-comment"> * owners' names. */</span></div><div class="cm-line"><span class="tok-variableName">SELECT</span></div><div class="cm-line"> <span class="tok-variableName">people</span><span class="tok-operator">.</span><span class="tok-propertyName">name</span> <span class="tok-variableName">AS </span><span class="tok-variableName">person</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">cats</span><span class="tok-operator">.</span><span class="tok-propertyName">name</span> <span class="tok-variableName">AS </span><span class="tok-variableName">cat</span></div><div class="cm-line"><span class="tok-variableName">FROM </span><span class="tok-variableName">people</span></div><div class="cm-line"><span class="tok-variableName">JOIN </span><span class="tok-variableName">cats</span></div><div class="cm-line"> <span class="tok-variableName">ON </span><span class="tok-variableName">people</span><span class="tok-operator">.</span><span class="tok-propertyName">id</span> <span class="tok-operator">=</span> <span class="tok-variableName">cats</span><span class="tok-operator">.</span><span class="tok-propertyName">owner_id</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Join performance <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/join-performance" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-12"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>The easy way to think about a join is: "it creates all possible combinations of rows from both tables, then filters them using the <code>ON</code> clause".
However, the database engine doesn't run them that way.
Its query optimizer will run queries in microseconds when they'd otherwise take years.</p>
</div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Left and right joins <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/left-and-right-joins" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>Left joins always include rows from the left table, even if the <code>ON</code> clause didn't match for those rows.
Right joins always include rows from the right table.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-comment">/* Select users and their discounts. Users with</span></div><div class="cm-line"><span class="tok-comment"> * no discounts will still show up, but the</span></div><div class="cm-line"><span class="tok-comment"> * discount columns will be null. */</span></div><div class="cm-line"><span class="tok-variableName">SELECT</span> <span class="tok-operator">*</span></div><div class="cm-line"><span class="tok-variableName">FROM </span><span class="tok-variableName">users </span><span class="tok-variableName">LEFT </span><span class="tok-variableName">JOIN </span><span class="tok-variableName">discounts</span></div><div class="cm-line"> <span class="tok-variableName">ON </span><span class="tok-variableName">users</span><span class="tok-operator">.</span><span class="tok-propertyName">discount_id</span> <span class="tok-operator">=</span></div><div class="cm-line"> <span class="tok-variableName">discounts</span><span class="tok-operator">.</span><span class="tok-propertyName">id</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">On conflict do nothing <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/on-conflict-do-nothing" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>We can tell the database what to do when we insert records that conflict with constraints.
The simplest form is <code>ON CONFLICT DO NOTHING</code>, which tells the database to ignore the insert.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">vaccinations</span> <span class="tok-punctuation">(</span></div><div class="cm-line"> <span class="tok-variableName">cat_name </span>TEXT<span class="tok-variableName"> </span>UNIQUE<span class="tok-variableName"> </span>NOT<span class="tok-variableName"> </span>NULL<span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">vaccinations</span> <span class="tok-punctuation">(</span><span class="tok-variableName">cat_name</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Ms. Fluff'</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">vaccinations</span> <span class="tok-punctuation">(</span><span class="tok-variableName">cat_name</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Ms. Fluff'</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">ON </span><span class="tok-variableName">CONFLICT</span> <span class="tok-punctuation">(</span><span class="tok-variableName">cat_name</span><span class="tok-punctuation">)</span> <span class="tok-variableName">DO </span><span class="tok-variableName">NOTHING</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">SELECT</span> <span class="tok-operator">*</span> <span class="tok-variableName">FROM </span><span class="tok-variableName">vaccinations</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{cat_name: 'Ms. Fluff'}]</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Selecting expressions from tables <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/selecting-expressions-from-tables" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>When <code>SELECT</code>ing from a table, we can <code>SELECT</code> expressions computed from the table's columns.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-variableName">CREATE </span><span class="tok-variableName">TABLE </span><span class="tok-variableName">cats</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name </span>TEXT<span class="tok-punctuation">,</span> <span class="tok-variableName">age </span>INTEGER<span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">cats</span> <span class="tok-punctuation">(</span><span class="tok-variableName">name</span><span class="tok-punctuation">,</span> <span class="tok-variableName">age</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'Ms. Fluff'</span><span class="tok-punctuation">,</span> <span class="tok-number">3</span><span class="tok-punctuation">)</span><span class="tok-punctuation">;</span></div><div class="cm-line"><span class="tok-variableName">SELECT </span><span class="tok-variableName">age</span><span class="tok-punctuation">,</span> <span class="tok-variableName">age</span> <span class="tok-operator">+</span> <span class="tok-number">1</span> <span class="tok-variableName">AS </span><span class="tok-variableName">age_next_year </span><span class="tok-variableName">FROM </span><span class="tok-variableName">cats</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div class="
text-muted
tone-positive
"><pre>[{age: 3, age_next_year: 4}]</pre></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">SQL injection <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/sql-injection" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-12"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>When working with SQL in another language, never concatenate SQL strings directly like <code>sql1 + sql2</code>!
It opens the possibility of attackers crafting special values that let them execute arbitrary SQL code.</p>
</div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">On conflict update <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/on-conflict-update" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>With <code>ON CONFLICT UPDATE</code>, a single <code>INSERT</code> statement can either insert a record or update an existing record.
The <code>ON CONFLICT UPDATE</code> clause is only triggered when a constraint is violated.</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-comment">/* If Amir exists, increment the count on his</span></div><div class="cm-line"><span class="tok-comment"> * existing row by 1. Otherwise, insert a row</span></div><div class="cm-line"><span class="tok-comment"> * for Amir. */</span></div><div class="cm-line"><span class="tok-variableName">INSERT </span><span class="tok-variableName">INTO </span><span class="tok-variableName">visits</span> <span class="tok-punctuation">(</span><span class="tok-variableName">email</span><span class="tok-punctuation">,</span> <span class="tok-variableName">count</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">VALUES</span> <span class="tok-punctuation">(</span><span class="tok-string">'amir@example.com'</span><span class="tok-punctuation">,</span> <span class="tok-number">1</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">ON </span><span class="tok-variableName">CONFLICT</span> <span class="tok-punctuation">(</span><span class="tok-variableName">email</span><span class="tok-punctuation">)</span></div><div class="cm-line"> <span class="tok-variableName">DO </span><span class="tok-variableName">UPDATE </span><span class="tok-variableName">SET </span><span class="tok-variableName">count</span> <span class="tok-operator">=</span> <span class="tok-variableName">count</span> <span class="tok-operator">+</span> <span class="tok-number">1</span><span class="tok-punctuation">;</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">Join mistakes <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/join-mistakes" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>Here are two important tips for testing your joins:</p>
</div></div></div><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><ul>
<li>When testing a join, use more than one row in each table.</li>
<li>When selecting from a join, qualify your selects with table names (like <code>SELECT users.name</code>) to make sure that you're selecting the right things.</li>
</ul>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-comment">/* This join is missing its "ON", so it returns</span></div><div class="cm-line"><span class="tok-comment"> * all combinations of users and comments. We</span></div><div class="cm-line"><span class="tok-comment"> * may not notice this if we only test simple</span></div><div class="cm-line"><span class="tok-comment"> * cases. */</span></div><div class="cm-line"><span class="tok-variableName">SELECT </span><span class="tok-variableName">users</span><span class="tok-operator">.</span><span class="tok-propertyName">name</span><span class="tok-punctuation">,</span> <span class="tok-variableName">comments</span><span class="tok-operator">.</span><span class="tok-propertyName">text</span></div><div class="cm-line"><span class="tok-variableName">FROM </span><span class="tok-variableName">users </span><span class="tok-variableName">JOIN </span><span class="tok-variableName">comments</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div><div class="mb-8"><div class="font-mono font-bold text-lg text-strong uppercase flex justify-between mb-2">ON vs WHERE <span class="text-sm text-muted whitespace-nowrap"><a href="https://www.executeprogram.com/courses/sql/lessons/on-vs-where" target="_blank" rel="noreferrer">View lesson <svg aria-label="Right Arrow Icon" class="
align-baseline fill-current inline-block relative
h-em-sm w-em-sm
" height="24" role="img" viewBox="0 0 24 24" width="24" xmlns="http://www.w3.org/2000/svg"><title>Right Arrow Icon</title><g aria-hidden="true"><path d="M15.295 20.463a.75.75 0 0 1 0-1.061l6.138-6.137H.75a.75.75 0 0 1-.743-.648L0 12.515a.75.75 0 0 1 .648-.743l.102-.007h20.697l-6.152-6.152a.75.75 0 0 1 1.06-1.06l7.425 7.424a.747.747 0 0 1 .212.418l.007.066v.094l-.002.024a.746.746 0 0 1-.28.523l-7.361 7.36a.75.75 0 0 1-1.061 0z"></path></g></svg></a></span></div><div class="block grid md:grid-cols-12 md:bg-base md:shadow p-2 -mx-2 md:mx-0 md:pt-0 md:rounded-md"><div class="col-span-5 border-base md:border-r"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>It's possible to get the same <code>INNER JOIN</code> results using <code>ON</code> or <code>WHERE</code>.
Try to use the one that communicates your intent to other programmers.
If your condition concerns which rows are joined with other rows, use <code>ON</code>.
If you think of your condition as filtering the result of the join, use <code>WHERE</code>.</p>
</div></div></div><div class="mb-8 md:mx-4 md:mb-4"><div class="mb-4 space-y-4"><div class="copy type-code-sm "><p>However, <code>ON</code> and <code>WHERE</code> are not equivalent for left, right, or outer joins!</p>
</div></div></div></div></div><div class="col-span-7"><div class="md:pt-4"><div class="mb-8 md:mx-4 md:mb-4"><div class="type-code-sm bg-back md:bg-base -m-2 md:m-0 px-2 py-4 md:p-0"><div class="flex"><pre class="mr-2">></pre><pre class="cm-s-execprog highlight"><div class="cm-line"><span class="tok-comment">/* We use an ON here because our conditions</span></div><div class="cm-line"><span class="tok-comment"> * concern which rows are joined with other</span></div><div class="cm-line"><span class="tok-comment"> * rows. */</span></div><div class="cm-line"><span class="tok-variableName">SELECT</span></div><div class="cm-line"> <span class="tok-variableName">people</span><span class="tok-operator">.</span><span class="tok-propertyName">name</span> <span class="tok-variableName">AS </span><span class="tok-variableName">person_name</span><span class="tok-punctuation">,</span></div><div class="cm-line"> <span class="tok-variableName">cats</span><span class="tok-operator">.</span><span class="tok-propertyName">name</span> <span class="tok-variableName">AS </span><span class="tok-variableName">cat_name</span></div><div class="cm-line"><span class="tok-variableName">FROM </span><span class="tok-variableName">people</span></div><div class="cm-line"><span class="tok-variableName">INNER </span><span class="tok-variableName">JOIN </span><span class="tok-variableName">cats</span></div><div class="cm-line"> <span class="tok-variableName">ON </span><span class="tok-variableName">people</span><span class="tok-operator">.</span><span class="tok-propertyName">id</span> <span class="tok-operator">=</span> <span class="tok-variableName">cats</span><span class="tok-operator">.</span><span class="tok-propertyName">owner_id</span></div><div class="cm-line"> <span class="tok-variableName">AND </span><span class="tok-variableName">people</span><span class="tok-operator">.</span><span class="tok-propertyName">name</span> <span class="tok-operator">=</span> <span class="tok-variableName">cats</span><span class="tok-operator">.</span><span class="tok-propertyName">name</span></div></pre></div><h6 class="font-bold inline-block text-muted tracking-wider type-code-sm uppercase">Result:</h6><div></div></div></div></div></div></div></div></div></div>
</body></html>