-
Notifications
You must be signed in to change notification settings - Fork 8
Expand file tree
/
Copy pathsql.html
More file actions
268 lines (228 loc) · 11.7 KB
/
sql.html
File metadata and controls
268 lines (228 loc) · 11.7 KB
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
<!DOCTYPE html>
<html lang="en">
<head>
<title>CS 4440</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/jpswalsh/academicons/css/academicons.min.css">
<link rel="stylesheet" href="css/main.css"/>
<link rel="icon" type="image/x-icon" href="img/favicon.ico">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.9.0/styles/default.min.css">
<script src="https://kit.fontawesome.com/982c2a20d7.js" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.9.0/highlight.min.js"></script>
<script>hljs.highlightAll();</script>
<style>
.SQL_Example_Data table {
width: auto;
border-collapse: collapse;
}
.SQL_Example_Data th, td {
border: 1px solid #ddd;
padding: 8px;
padding-left: 10px;
}
.SQL_Example_Data th {
background-color: #f2f2f2;
}
.SQL_Example_Data td {
padding-left: 10px;
}
.plist p {
display: block;
}
</style>
</head>
<body>
<div id="navbar-placeholder" data-wiki-root="" data-site-root="../"></div>
<script src="js/navbar.js"></script>
<!-- ################################################################################### -->
<div class="container main-container" role="main" id="main-content">
<div class="row">
<div class="col-lg-9">
<h1>CS 4440 Wiki: <br class="on-narrow"><strong style="color:var(--color-blue)">SQL Cheat Sheet</strong></h1><br>
<!-- ################################################################################### -->
<p>Below is an abridged cheat sheet of SQL (Structured Query Language) fundamentals that you'll use in this course.</p>
<p><strong>This page is by no means comprehensive—we encourage you to bookmark and familiarize yourself with one of the many in-depth SQL tutorials on the web.</strong> Some great examples are:</p>
<ul>
<li> <a href="https://www.w3schools.com/sql/" >W3 Schools' SQL Introduction</a></li>
<li> <a href="https://www.programiz.com/sql/online-compiler/" >Online SQL Complier</a></li>
</ul>
<!-- ################################################################################### -->
<hr><h2 id="ops">Basic SQL Commands</h2>
<p>Consider an SQL table named <code>CourseStaff</code> comprised of the following data records shown below:</p>
<table class="SQL_Example_Data">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Role</th>
<th>City</th>
</tr>
</thead>
<tbody>
<tr>
<td>0</td>
<td>Stefan Nagy</td>
<td>Professor</td>
<td>Salt Lake City, UT</td>
</tr>
<tr>
<td>1</td>
<td>Bella Miller</td>
<td>TA</td>
<td>Salt Lake City, UT</td>
</tr>
<tr>
<td>2</td>
<td>Alishia Seo</td>
<td>TA</td>
<td>Salt Lake City, UT</td>
</tr>
<tr>
<td>3</td>
<td>Ethan Quinlan</td>
<td>TA</td>
<td>Salt Lake City, UT</td>
</tr>
</tbody>
</table>
<br>
<p>Below are examples of common SQL commands interacting with the data contained in the above table.</p>
<br><br>
<h3 id="basics#select">Selecting Records:</h3>
<p><strong><code>SELECT</code></strong>: return a result set of rows, from one or more tables.</p>
<pre><code>SELECT ID, Name from CourseStaff;
</code> Number of records: 4
{ID, Name}
{0, Stefan Nagy}
{1, Bella Miller}
{2, Alishia Seo}
{3, Ethan Quinlan}
</pre>
<br>
<h3 id="basics#insert">Inserting Records:</h3>
<p><strong><code>INSERT</code></strong>: insert data records into the table.</p>
<pre><code>INSERT INTO CourseStaff (Name, Role, City) VALUES ("Hokie Bird", "TA", "Blacksburg, VA");
</code> Inserted row: {4, Hokie Bird, TA, Blacksburg VA}
</pre>
<h3 id="basics#update">Updating Records:</h3>
<p><strong><code>UPDATE</code></strong>: update data records within table.</p>
<pre><code>UPDATE CourseStaff SET Name = "C​ount Chocula", City = "Hershey, PA" WHERE id = 0;
</code> Updated row: {0, Count Chocula, Professor, Hershey PA}
</pre>
<h3 id="basics#delete">Deleting Records:</h3>
<p><strong><code>DELETE</code></strong>: delete existing records.</p>
<pre><code>DELETE FROM CourseStaff WHERE City="Salt Lake City, UT"
</code> Deleted 4 rows.</pre>
<h3 id="basics#union">Union of Multiple Selections:</h3>
<p><strong><code>UNION</code></strong>: combine results of multiple queries.</p>
<pre><code>SELECT Name FROM CourseStaff UNION SELECT Name FROM SomeOtherTable;
</code> Results in a list of all <strong>unique</strong> names in Name column from CourseStaff and all names from SomeOtherTable.
</pre>
<h3 id="basics#conditionals">Conditional Operators:</h3>
<p><strong><code>WHERE/AND/OR</code></strong>: conditional operations to find data in table.</p>
<pre><code>SELECT * FROM CourseStaff WHERE Name = "Ethan Quinlan"
</code> Number of records: 1
{3, Ethan Quinlan, TA, Salt Lake City UT}
<code>SELECT * FROM CourseStaff WHERE City = "Salt Lake City, UT" AND Role = "TA"
</code> Number of records: 4
{1, Bella Miller, TA, Salt Lake City UT}
{2, Alishia Seo, TA, Salt Lake City UT}
{3, Ethan Quinlan, TA, Salt Lake City UT}
</pre>
<!-- ################################################################################### -->
<hr><h2 id="tips">Syntactical Tips</h2>
<p>Here are some SQL syntax tips to keep in mind.</p>
<div>
<h3 id="tips#operations">Operators:</h3>
<p id="operators#all"><strong><code>*</code> :</strong> all</p>
<pre><code>SELECT * from CourseStaff</code> Returns every column for every row in the table CourseStaff.</pre>
<p id="operators#null"><strong><code>NULL</code> : </strong>nothing. Reports a missing or undefined value in a database. Not equivalent to an empty string or zero, indicates a value is unknown.</p>
<pre><code>NULL = NULL</code> Returns false because NULL is considered unknown. <code>SELECT * FROM CourseStaff WHERE Role IS NULL;</code> To check for NULL you must use IS NULL or IS NOT NULL operators.
This query will return 0 rows as each row in CourseStaff has a value for role.</pre>
<p id="operators#equal"><strong><code>=</code> : </strong>equal (comparison) operator. Checks if two expressions or values are equal. Returns True or False.</p>
<pre><code>SELECT * from CourseStaff where id = 0;</code> Number of records: 1
{0, Stefan Nagy, Professor, Salt Lake City UT}</pre>
<h3 id="tips#booleans">Booleans:</h3>
<p id="booleans#true"><strong><code>TRUE</code> : </strong>true keyword</p>
<pre><code>SELECT 1 = 1;</code> Returns TRUE.</pre>
<p id="booleans#false"><strong><code>FALSE</code> : </strong>false keyword</p>
<pre><code>SELECT 1 = 4;</code> Returns FALSE.</pre>
<p id="booleans#type"><strong><code>"1" = 1</code> : </strong>type mismatch</p>
<pre><code>SELECT '1' = 1;</code> Returns FALSE.
A string is not equal to an integer, type mismatch results in FALSE.</pre>
<p><strong><code>1 = 1</code> : </strong>integer comparison</p>
<pre> Comparison of two equal integer values results in TRUE. </pre>
<p><strong><code>"1" = 1 OR 1</code> : </strong>will result in TRUE</p>
<pre> "1" = 1 returns FALSE but the second part is OR 1.
In SQL, any non-zero number is evaluated as true. So 1 is considered TRUE.</pre>
<h3 id="tips#comments">Code Commenting:</h3>
<p><strong><code>-- </code> : </strong>comment-out the rest of the line (note the space at the end)</p>
<pre><code>SELECT '1' = 1 -- OR 1;</code> Returns FALSE.
Everything after the comment is ignored by the SQL interpreter, so it is the comparison of a string to an integer.</pre>
</div>
<!-- ################################################################################### -->
</div>
<div class="col-lg-3">
<nav class="sidebar-toc" aria-label="Table of Contents">
<h3><strong>Table of Contents:</strong></h3>
<ul>
<li><a href="#basics">Basic Commands</a>
<ul>
<li><a href="#basics#select">SELECT</a></li>
<li><a href="#basics#insert">INSERT</a></li>
<li><a href="#basics#update">UPDATE</a></li>
<li><a href="#basics#delete">DELETE</a></li>
<li><a href="#basics#union">UNION</a></li>
<li><a href="#basics#conditionals">WHERE/AND/OR</a></li>
</ul>
</li>
<li style="margin-top:1ex"><a href="#tips">Syntax Tips</a>
<ul>
<li><a href="#tips#operations">Operators</a>
<ul>
<li><a href="#operators#all">ALL</a></li>
<li><a href="#operators#null">NULL</a></li>
<li><a href="#operators#equal">=</a></li>
</ul>
</li>
<li style="margin-top:1ex"><a href="#tips#booleans">Booleans</a>
<ul>
<li><a href="#booleans#true">TRUE</a></li>
<li><a href="#booleans#false">FALSE</a></li>
<li><a href="#booleans#type">type mismatch</a></li>
</ul>
</li>
<li style="margin-top:1ex"><a href="#tips#comments">Commenting</a></li>
</ul>
</li>
</ul>
</nav>
</div>
</div>
</div>
<!-- ################################################################################### -->
<footer class="bg-light text-center text-lg-start" role="contentinfo">
<div class="text-center p-3">
Copyright © Stefan Nagy. All rights reserved.
</div>
</footer>
<!-- ################################################################################### -->
<script>
document.addEventListener('DOMContentLoaded', function() {
document.querySelectorAll('pre').forEach(pre => {
pre.setAttribute('tabindex', '0');
pre.setAttribute('role', 'region');
pre.setAttribute('aria-label', 'Code block');
});
document.querySelectorAll('pre code').forEach(code => {
code.setAttribute('tabindex', '0');
code.setAttribute('role', 'region');
code.setAttribute('aria-label', 'Code block');
});
});
</script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>