-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathSQL.html
More file actions
1201 lines (1131 loc) · 104 KB
/
Copy pathSQL.html
File metadata and controls
1201 lines (1131 loc) · 104 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
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
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=0.6, maximum-scale=3.0">
<link href="https://fonts.googleapis.com/css2?family=Gloria+Hallelujah&display=swap" rel="stylesheet">
<title>Python and CS topics</title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<script src="scripts/modal.js"></script>
<script src="scripts/responsive.js"></script>
<h1 class="content">Welcome to my fridge door!</h1>
<header>
<nav><a href="CS.html">CS</a> <a href="Django.html">Django</a> <a href="dj_ORM.html">dj ORM</a> <a href="JS.html">JS</a> <a href="Math.html">Math</a> <a href="numpy.html">numpy</a> <a href="index.html">Python</a> <a href="SQL.html">SQL</a> <a href="SQLAlchemy.html">SQLAlchemy</a></nav>
</header>
<div id="grid">
<!-- SQL Numeric Data Types -->
<div class="grid-item">
<button class="tilt1" id="openBtn-SQL_Numeric_Data_Types">SQL Numeric Data Types</button>
<div id="myModal-SQL_Numeric_Data_Types" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>SQL Numeric Data Types</h2>
<p><!--START-CONTENT-SQL_Numeric_Data_Types--></p>
<p><div class="category-content">1. INTEGER (INT)<br>Stores whole numbers (no decimals).<br>Typical range: -2,147,483,648 to 2,147,483,647 (4 bytes).<br>Good for things like IDs, counts, ages.<br>Variants: SMALLINT (2 bytes), BIGINT (8 bytes).<br><br>2. SMALLINT<br>Smaller range than INT.<br>Typical range: -32,768 to 32,767 (2 bytes).<br>Saves storage if you know values are small (e.g., number of items in stock, months of the year).<br><br>3. BIGINT<br>Larger range than INT.<br>Typical range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (8 bytes).<br>Use for very large counts (e.g., social media user IDs, transaction logs).<br><br>4. DECIMAL(p, s) / NUMERIC(p, s)<br>Stores exact fixed-point numbers.<br>p = precision (total number of digits).<br>s = scale (digits after the decimal point).<br>Example: DECIMAL(10,2) can store up to 99999999.99.<br>Great for money, prices, financial data (no rounding errors).<br><br>5. FLOAT / REAL / DOUBLE PRECISION<br>Stores approximate values with floating-point representation.<br>FLOAT → database decides precision (often 4 bytes, ~7 digits).<br>DOUBLE PRECISION → higher precision (8 bytes, ~15 digits).<br>Faster than DECIMAL, but not always exact (can have rounding errors).<br>Use for scientific data, measurements, percentages, but avoid for money.<br><br>These are all floating-point numeric types, meaning they store approximate numbers (not exact). The main difference is in precision (how many digits they can represent reliably) and storage size.<br>REAL = small, fast, less precise.<br>DOUBLE PRECISION = bigger, slower, much more precise.<br>FLOAT(p) = SQL standard that maps to REAL or DOUBLE depending on p.<br><br>6. SERIAL / BIGSERIAL (PostgreSQL only)<br>Auto-incrementing integer.<br>SERIAL = INTEGER + auto-increment.<br>BIGSERIAL = BIGINT + auto-increment.<br>Perfect for primary keys.</div>
<div class="category-content">in PostgreSQL, it does not matter whether you use DECIMAL or NUMERIC</div>
<!--CONTENT-SQL_Numeric_Data_Types--></p>
</div>
</div>
<script>
setupModal("myModal-SQL_Numeric_Data_Types", "openBtn-SQL_Numeric_Data_Types");
</script>
</div>
<!-- END OF SQL Numeric Data Types -->
<!-- SQL String (Character) Data Types -->
<div class="grid-item">
<button class="tilt3" id="openBtn-SQL_String__Character__Data_Types">SQL String (Character) Data Types</button>
<div id="myModal-SQL_String__Character__Data_Types" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>SQL String (Character) Data Types</h2>
<p><!--START-CONTENT-SQL_String__Character__Data_Types--></p>
<p><div class="category-content">1. CHAR(n)<br>Fixed-length string.<br>Always stores exactly n characters → pads with spaces if shorter.<br>Example: CHAR(5)<br>'hi' is stored as 'hi ' (3 spaces added).<br>Efficient for codes of fixed length (e.g., country codes like USA, postal codes).<br><br>2. VARCHAR(n)<br>Variable-length string.<br>Stores up to n characters, without extra padding.<br>Example: VARCHAR(50) → can store between 0 and 50 characters.<br>More flexible than CHAR.<br>VARCHAR (without n) → unlimited length (same as TEXT).<br>Common for names, emails, descriptions.<br><br>3. TEXT<br>Stores unlimited-length text (well, very large).<br>Example: in PostgreSQL, up to 1 GB per value.<br>No need to define a max length.<br>Good for long text (comments, articles, logs).<br>But often lacks some indexing/constraint options compared to VARCHAR.<br><br>4. NVARCHAR / NCHAR (Unicode types, mainly SQL Server / Oracle)<br>Like VARCHAR/CHAR, but stores Unicode characters.<br>Needed when working with multilingual data (Chinese, Arabic, emojis, etc.).<br>Each character can take more bytes (UTF-16 encoding).<br><br>5. Other Useful String Types (DB-specific)<br>UUID (PostgreSQL, MySQL 8.0) → special string for universally unique identifiers.<br>JSON / JSONB (PostgreSQL, MySQL, SQL Server) → structured text data.<br>ENUM (MySQL, PostgreSQL) → predefined list of string values (like "pending", "approved", "rejected").</div>
<!--CONTENT-SQL_String__Character__Data_Types--></p>
</div>
</div>
<script>
setupModal("myModal-SQL_String__Character__Data_Types", "openBtn-SQL_String__Character__Data_Types");
</script>
</div>
<!-- END OF SQL String (Character) Data Types -->
<!-- SQL Date & Time Data Types -->
<div class="grid-item">
<button class="tilt3" id="openBtn-SQL_Date___Time_Data_Types">SQL Date & Time Data Types</button>
<div id="myModal-SQL_Date___Time_Data_Types" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>SQL Date & Time Data Types</h2>
<p><!--START-CONTENT-SQL_Date___Time_Data_Types--></p>
<p><div class="category-content">1. DATE<br>Stores a calendar date (year, month, day).<br>Format: YYYY-MM-DD.<br>'2025-09-09'<br>No time of day is stored.<br><br>2. TIME [WITHOUT TIME ZONE]<br>Stores only the time of day (hours, minutes, seconds, fractions).<br>Format: HH:MI:SS[.ffff].<br>'14:35:20'<br><br>3. TIMESTAMP [WITHOUT TIME ZONE]<br>Stores both date and time together.<br>Format: YYYY-MM-DD HH:MI:SS[.ffff].<br>'2025-09-09 14:35:20'<br><br>4. TIMESTAMP WITH TIME ZONE (timestamptz in PostgreSQL)<br>Same as TIMESTAMP, but also stores a time zone offset.<br>'2025-09-09 14:35:20+02'<br>Useful when working across multiple time zones.<br><br>5. INTERVAL (PostgreSQL, some others)<br>Stores a time duration (years, months, days, hours, minutes, seconds).<br>'2 days 5 hours 30 minutes'<br><br>6. DATETIME (MySQL, SQL Server)<br>Equivalent to TIMESTAMP in many systems.<br>Stores both date and time (without timezone).<br>Copy code<br>'2025-09-09 14:35:20'</div>
<div class="category-content"><br><br>>>> General SQL Rules for Dates & Intervals<br><br>Type awareness<br><br>You can only add/subtract intervals to/from DATE or TIMESTAMP types.<br>Example: DATE '2025-01-01' + INTERVAL '10 days' → 2025-01-11.<br><br>Valid units for INTERVAL<br>Standard SQL supports: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.<br><br>Some databases add extras like WEEK, MICROSECOND, etc.<br><br>Addition vs. subtraction<br><br>date + interval → moves forward.<br>date - interval → moves backward.<br>date1 - date2 → returns an interval (the difference).<br><br>Overflow handling (months/years)<br><br>Adding months is tricky because months have different lengths.<br>Example in PostgreSQL:<br>DATE '2025-01-31' + INTERVAL '1 month' → 2025-02-28 (it clamps to the last valid day).<br><br>MySQL might behave differently.</div>
<div class="category-content">In PostgreSQL, there’s no separate “bare” date literal without quotes. Every date you write in SQL is either:<br><br>A string literal -> 'YYYY-MM-DD' -> PostgreSQL automatically casts to DATE if needed.<br>A string explicitly cast to date -> 'YYYY-MM-DD'::DATE.<br>A date literal using the DATE keyword -> DATE 'YYYY-MM-DD'.<br><br>There isn’t a scenario where you literally type something like 2025-09-23 without quotes and PostgreSQL interprets it as a date—it would be treated as a numeric expression (2025 minus 9 minus 23 = 1993).</div>
<!--CONTENT-SQL_Date___Time_Data_Types--></p>
</div>
</div>
<script>
setupModal("myModal-SQL_Date___Time_Data_Types", "openBtn-SQL_Date___Time_Data_Types");
</script>
</div>
<!-- END OF SQL Date & Time Data Types -->
<!-- ENUM SQL -->
<div class="grid-item">
<button class="tilt1" id="openBtn-ENUM_SQL">ENUM SQL</button>
<div id="myModal-ENUM_SQL" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>ENUM SQL</h2>
<p><!--START-CONTENT-ENUM_SQL--></p>
<p><div class="category-content">CREATE TYPE type_mood AS ENUM ('happy', 'relaxed', 'stressed', 'sad');<br>ALTER TABLE minions_info ADD COLUMN mood type_mood;<br></div>
<!--CONTENT-ENUM_SQL--></p>
</div>
</div>
<script>
setupModal("myModal-ENUM_SQL", "openBtn-ENUM_SQL");
</script>
</div>
<!-- END OF ENUM SQL -->
<!-- Custom type in SQL -->
<div class="grid-item">
<button class="tilt2" id="openBtn-Custom_type_in_SQL">Custom type in SQL</button>
<div id="myModal-Custom_type_in_SQL" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>Custom type in SQL</h2>
<p><!--START-CONTENT-Custom_type_in_SQL--></p>
<p><img src="cat_images/custom_type.png" alt="Custom type in SQL" width="500" style="display:block; margin-right:auto;">
<!--CONTENT-Custom_type_in_SQL--></p>
</div>
</div>
<script>
setupModal("myModal-Custom_type_in_SQL", "openBtn-Custom_type_in_SQL");
</script>
</div>
<!-- END OF Custom type in SQL -->
<!-- RANDOM() -->
<div class="grid-item">
<button class="tilt5" id="openBtn-RANDOM__">RANDOM()</button>
<div id="myModal-RANDOM__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>RANDOM()</h2>
<p><!--START-CONTENT-RANDOM__--></p>
<p><div class="category-content">RANDOM() generates a pseudo-random floating-point number<br><br>0.0 ≤ RANDOM() < 1.0 0.0≤RANDOM()<1.0<br><br> SELECT RANDOM();<br> # Might output: 0.374829<br><br><br>Multiplying RANDOM() by a number scales it<br>0.0≤RANDOM() * 100<100.0<br> SELECT RANDOM() * 100;<br> # Might output: 37.4829<br><br>random integer between 0 and 99:<br> SELECT FLOOR(RANDOM() * 100);<br><br>To get between 1 and 100:<br> SELECT FLOOR(RANDOM() * 100) + 1;<br><br>Random row selection:<br> SELECT *<br> FROM users<br> ORDER BY RANDOM()<br> LIMIT 5;<br>Picks 5 random rows from users.<br><br><br>RANDOM() → 0 ≤ x < 1<br><br>RANDOM() * N → scales to 0 ≤ x < N<br><br>FLOOR(RANDOM() * N) → integer 0..(N-1)<br><br>FLOOR(RANDOM() * N) + 1 → integer 1..N</div>
<img src="cat_images/rand1.png" alt="RANDOM()" width="500" style="display:block; margin-right:auto;">
<!--CONTENT-RANDOM__--></p>
</div>
</div>
<script>
setupModal("myModal-RANDOM__", "openBtn-RANDOM__");
</script>
</div>
<!-- END OF RANDOM() -->
<!-- ALTER TABLE vs UPDATE -->
<div class="grid-item">
<button class="tilt5" id="openBtn-ALTER_TABLE_vs_UPDATE">ALTER TABLE vs UPDATE</button>
<div id="myModal-ALTER_TABLE_vs_UPDATE" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>ALTER TABLE vs UPDATE</h2>
<p><!--START-CONTENT-ALTER_TABLE_vs_UPDATE--></p>
<p><div class="category-content">ALTER TABLE <br>Table schema Add/drop columns, change types, constraints<br><br> UPDATE <br>Table data Modify or fill in values in existing rows<br><br><br>ALTER TABLE countries<br>ADD COLUMN capital_code CHAR(2);<br><br>ALTER TABLE countries<br>DROP COLUMN capital_code;<br><br>ALTER TABLE countries<br>ALTER COLUMN population TYPE BIGINT;<br>Add a constraint (e.g., primary key or foreign key):<br><br>ALTER TABLE countries<br>ADD CONSTRAINT pk_country_id PRIMARY KEY (id);<br><br><br>UPDATE countries<br>SET capital_code = SUBSTRING(capital, 1, 2);<br><br>UPDATE countries<br>SET population = population * 1000<br>WHERE population < 1000;<br><br>UPDATE always uses SET</div>
<!--CONTENT-ALTER_TABLE_vs_UPDATE--></p>
</div>
</div>
<script>
setupModal("myModal-ALTER_TABLE_vs_UPDATE", "openBtn-ALTER_TABLE_vs_UPDATE");
</script>
</div>
<!-- END OF ALTER TABLE vs UPDATE -->
<!-- LIKE -->
<div class="grid-item">
<button class="tilt5" id="openBtn-LIKE">LIKE</button>
<div id="myModal-LIKE" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>LIKE</h2>
<p><!--START-CONTENT-LIKE--></p>
<p><div class="category-content">LIKE is used in WHERE clauses to match text using wildcards.<br><br>% → matches any sequence of characters (including empty).<br>_ → matches exactly one character.<br><br>-- Find all cities starting with 'New'<br>SELECT * FROM cities<br>WHERE name LIKE 'New%';<br><br>-- Find all cities with 3 letters<br>SELECT * FROM cities<br>WHERE name LIKE '___';</div>
<div class="category-content">>><br>WHERE LOWER("companion_full_name") LIKE '%and%' <br> "Ms. Brandy Rice"<br> "Terrell Blanda IV"<br> "Andrew Gottlieb PhD"<br> "Sandra Langosh"<br><br>AND "email" NOT LIKE '%@gmail';<br> "[email protected]"<br> "[email protected]"<br> "[email protected]"<br> "[email protected]"<br><br><br>AND "email" NOT LIKE '%@gmail%';<br> "Ms. Brandy Rice" "[email protected]"<br> "Terrell Blanda IV" "[email protected]"</div>
<!--CONTENT-LIKE--></p>
</div>
</div>
<script>
setupModal("myModal-LIKE", "openBtn-LIKE");
</script>
</div>
<!-- END OF LIKE -->
<!-- ESCAPE -->
<div class="grid-item">
<button class="tilt2" id="openBtn-ESCAPE">ESCAPE</button>
<div id="myModal-ESCAPE" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>ESCAPE</h2>
<p><!--START-CONTENT-ESCAPE--></p>
<p><div class="category-content">-- Find names that literally contain '50%'<br><br>SELECT * FROM products<br>WHERE name LIKE '%50!%%' ESCAPE '!';<br><br>! is defined as the escape character.<br>!% means "treat % literally."<br><br>SELECT * FROM products<br>WHERE name LIKE '%50#%%' ESCAPE '#';<br><br><br>-- Find strings ending with '_end'<br><br>SELECT * FROM logs<br>WHERE message LIKE '%!_end' ESCAPE '!';</div>
<!--CONTENT-ESCAPE--></p>
</div>
</div>
<script>
setupModal("myModal-ESCAPE", "openBtn-ESCAPE");
</script>
</div>
<!-- END OF ESCAPE -->
<!-- TO_CHAR() -->
<div class="grid-item">
<button class="tilt5" id="openBtn-TO_CHAR__">TO_CHAR()</button>
<div id="myModal-TO_CHAR__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>TO_CHAR()</h2>
<p><!--START-CONTENT-TO_CHAR__--></p>
<p><div class="category-content">TO_CHAR in PostgreSQL is a formatting function — it converts numbers or dates/timestamps into nicely formatted strings.<br><br>1. Numbers → String<br>SELECT TO_CHAR(1234.567, 'FM9999.00'); <br><br>-- "1234.57"<br>'9' → digit placeholder (optional if leading zeros aren’t needed)<br>'0' → digit placeholder (always show zero if missing)<br>FM → fill mode (removes padding spaces)<br>. → decimal point<br>, → thousands separator<br><br>SELECT TO_CHAR(12345.678, '999,999.99'); -- "12,345.68"<br>SELECT TO_CHAR(5, '0000'); -- "0005"<br><br><br>2. Dates / Timestamps → String<br><br>SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS'); <br>-- e.g. "2025-09-23 14:45:30"<br><br><br>YYYY → 4-digit year<br>YY → 2-digit year<br>MM → month (01–12)<br>MON → abbreviated month (e.g., JAN)<br>MONTH → full month name<br>DD → day of month<br>DY → abbreviated weekday (e.g., MON)<br>DAY → full weekday name<br>HH24 → hour (24-hour)<br>HH12 → hour (12-hour)<br>MI → minutes<br>SS → seconds<br>AM / PM → meridiem<br><br>SELECT TO_CHAR(NOW(), 'Day, DD Mon YYYY'); <br>-- "Tuesday , 23 Sep 2025"<br><br>SELECT TO_CHAR(NOW(), 'HH12:MI:SS AM'); <br>-- "02:45:30 PM"</div>
<!--CONTENT-TO_CHAR__--></p>
</div>
</div>
<script>
setupModal("myModal-TO_CHAR__", "openBtn-TO_CHAR__");
</script>
</div>
<!-- END OF TO_CHAR() -->
<!-- EXTRACT() -->
<div class="grid-item">
<button class="tilt1" id="openBtn-EXTRACT__">EXTRACT()</button>
<div id="myModal-EXTRACT__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>EXTRACT()</h2>
<p><!--START-CONTENT-EXTRACT__--></p>
<p><div class="category-content">EXTRACT in PostgreSQL always returns a double precision number.<br><br>EXTRACT(field FROM source)<br>field → what you want (like year, month, day, epoch…)<br>source → a DATE, TIMESTAMP, or INTERVAL<br><br>1. From a date<br><br>SELECT EXTRACT(YEAR FROM DATE '2025-09-23'); <br>-- 2025<br><br>SELECT EXTRACT(MONTH FROM DATE '2025-09-23'); <br>-- 9<br><br>SELECT EXTRACT(DAY FROM DATE '2025-09-23'); <br>-- 23<br><br>2. From a timestamp<br><br>SELECT EXTRACT(HOUR FROM TIMESTAMP '2025-09-23 14:45:30'); <br>-- 14<br><br>SELECT EXTRACT(MINUTE FROM TIMESTAMP '2025-09-23 14:45:30'); <br>-- 45<br><br>SELECT EXTRACT(SECOND FROM TIMESTAMP '2025-09-23 14:45:30'); <br>-- 30<br><br>3. From intervals<br><br>SELECT EXTRACT(DAY FROM INTERVAL '3 days 5 hours'); <br>-- 3<br><br>SELECT EXTRACT(HOUR FROM INTERVAL '3 days 5 hours'); <br>-- 5<br><br>4. Special one: epoch<br>SELECT EXTRACT(EPOCH FROM TIMESTAMP '2025-09-23 14:45:30'); <br>-- 1769246730 (Unix timestamp)<br><br>SELECT TO_TIMESTAMP(1769246730); <br>-- "2025-09-23 14:45:30+00"</div>
<!--CONTENT-EXTRACT__--></p>
</div>
</div>
<script>
setupModal("myModal-EXTRACT__", "openBtn-EXTRACT__");
</script>
</div>
<!-- END OF EXTRACT() -->
<!-- AGE() -->
<div class="grid-item">
<button class="tilt4" id="openBtn-AGE__">AGE()</button>
<div id="myModal-AGE__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>AGE()</h2>
<p><!--START-CONTENT-AGE__--></p>
<p><div class="category-content">AGE() in PostgreSQL is for working out differences between dates/timestamps, but instead of giving you just a number, it returns an interval.<br><br>SELECT AGE(TIMESTAMP '2025-09-23', TIMESTAMP '2000-09-23');<br>-- 25 years 0 mons 0 days<br><br>SELECT AGE(TIMESTAMP '2000-09-23');<br>-- relative to NOW(), e.g. "25 years 0 mons 0 days"<br><br><br>SELECT '2025-09-23'::date - '2000-09-23'::date;<br>-- 9132 (days)<br><br>SELECT AGE('2025-09-23'::date, '2000-09-23'::date);<br>-- 25 years</div>
<!--CONTENT-AGE__--></p>
</div>
</div>
<script>
setupModal("myModal-AGE__", "openBtn-AGE__");
</script>
</div>
<!-- END OF AGE() -->
<!-- NOW() -->
<div class="grid-item">
<button class="tilt3" id="openBtn-NOW__">NOW()</button>
<div id="myModal-NOW__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>NOW()</h2>
<p><!--START-CONTENT-NOW__--></p>
<p><div class="category-content">NOW() in PostgreSQL is a function that returns the current date and time as a TIMESTAMP WITH TIME ZONE.<br><br>Returns current timestamp at the moment the query runs.<br>Includes date and time, e.g., 2025-09-23 14:35:12.123456+00.<br><br>.123456 → the microseconds (fractional part of a second, up to 6 digits)<br>+00 → the time zone offset from UTC (in this case, +00 means UTC itself)<br><br>-- Insert a record with the current timestamp<br>INSERT INTO orders (order_date, customer_id)<br>VALUES (NOW(), 42);<br><br>-- Select events happening from now onwards<br>SELECT * FROM events<br>WHERE start_time >= NOW();<br><br>Equivalent to CURRENT_TIMESTAMP<br><br>SELECT NOW()::DATE; -- returns '2025-09-23'</div>
<!--CONTENT-NOW__--></p>
</div>
</div>
<script>
setupModal("myModal-NOW__", "openBtn-NOW__");
</script>
</div>
<!-- END OF NOW() -->
<!-- :: CAST() -->
<div class="grid-item">
<button class="tilt5" id="openBtn-___CAST__">:: CAST()</button>
<div id="myModal-___CAST__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>:: CAST()</h2>
<p><!--START-CONTENT-___CAST__--></p>
<p><div class="category-content">In PostgreSQL, :: is shorthand for casting a value from one type to another. It’s equivalent to using the CAST() function.<br><br>'2025-09-23'::DATE<br>CAST('2025-09-23' AS DATE)<br><br>SELECT 123::TEXT; -- Converts number 123 to text<br>SELECT '12.34'::NUMERIC; -- Converts string to numeric<br>SELECT 'true'::BOOLEAN; -- Converts string to boolean<br><br>:: is PostgreSQL-specific shorthand.<br>CAST() is standard SQL and works in other databases too.</div>
<div class="category-content">>><br>CAST(b.starts_at, DATE) -> WRONG<br><br>CAST(b.starts_at AS DATE) -> RIGHT</div>
<!--CONTENT-___CAST__--></p>
</div>
</div>
<script>
setupModal("myModal-___CAST__", "openBtn-___CAST__");
</script>
</div>
<!-- END OF :: CAST() -->
<!-- ROUND, CEIL (or CEILING), FLOOR -->
<div class="grid-item">
<button class="tilt1" id="openBtn-ROUND__CEIL__or_CEILING____FLOOR">ROUND, CEIL (or CEILING), FLOOR</button>
<div id="myModal-ROUND__CEIL__or_CEILING____FLOOR" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>ROUND, CEIL (or CEILING), FLOOR</h2>
<p><!--START-CONTENT-ROUND__CEIL__or_CEILING____FLOOR--></p>
<p><div class="category-content">SELECT ROUND(12.34); -- 12<br>SELECT ROUND(12.56); -- 13<br><br>-- Round to 1 decimal place<br>SELECT ROUND(12.345, 1); -- 12.3<br><br>SELECT ROUND(0.55); -- 1<br>SELECT ROUND(-0.55); -- -1<br><br>SELECT CEIL(12.01); -- 13<br>SELECT CEILING(12.99); -- 13<br><br>SELECT FLOOR(12.99); -- 12<br>SELECT FLOOR(12.01); -- 12<br><br><br>ROUND 12.56 13<br>CEIL 12.01 13<br>FLOOR 12.99 12</div>
<div class="category-content">In PostgreSQL FLOOR() and CEIL() (or CEILING()) only work on the whole number—they do not take a precision argument like ROUND() does.</div>
<div class="category-content">>> How to format decimals:<br><br>CAST(salary AS DECIMAL(18,2))<br>18 total digits, 2 after the decimal.<br><br>This is a common safe default — enough for salaries, prices, even big amounts.<br>Max: 9999999999999999.99</div>
<div class="category-content">>><br>Also works:<br><br>TRUNC(salary, 2)</div>
<!--CONTENT-ROUND__CEIL__or_CEILING____FLOOR--></p>
</div>
</div>
<script>
setupModal("myModal-ROUND__CEIL__or_CEILING____FLOOR", "openBtn-ROUND__CEIL__or_CEILING____FLOOR");
</script>
</div>
<!-- END OF ROUND, CEIL (or CEILING), FLOOR -->
<!-- TRANSLATE() -->
<div class="grid-item">
<button class="tilt2" id="openBtn-TRANSLATE__">TRANSLATE()</button>
<div id="myModal-TRANSLATE__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>TRANSLATE()</h2>
<p><!--START-CONTENT-TRANSLATE__--></p>
<p><div class="category-content">TRANSLATE() is used to replace characters in a string, one-to-one. It's different from REPLACE() because it works character by character, not substrings.<br><br>SELECT TRANSLATE('abcdef', 'abc', '123');<br>-- Result: '123def'<br>-- 'a' → '1', 'b' → '2', 'c' → '3'<br><br>SELECT TRANSLATE('hello world', 'ld', 'XY');<br>-- Result: 'heXXo worY'<br>-- 'l' → 'X', 'd' → 'Y'<br><br>SELECT TRANSLATE('123-456-789', '123456789', '987654321');<br>-- Result: '987-654-321'<br><br>TRANSLATE(string, from_chars, to_chars)<br>Characters in from_chars without a matching character in to_chars are removed.</div>
<!--CONTENT-TRANSLATE__--></p>
</div>
</div>
<script>
setupModal("myModal-TRANSLATE__", "openBtn-TRANSLATE__");
</script>
</div>
<!-- END OF TRANSLATE() -->
<!-- REPLACE() -->
<div class="grid-item">
<button class="tilt3" id="openBtn-REPLACE__">REPLACE()</button>
<div id="myModal-REPLACE__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>REPLACE()</h2>
<p><!--START-CONTENT-REPLACE__--></p>
<p><div class="category-content">REPLACE() is used to replace substrings within a string, not individual characters.<br><br>REPLACE(string, from_substring, to_substring)<br><br>SELECT REPLACE('hello world', 'world', 'PostgreSQL');<br>-- Result: 'hello PostgreSQL'<br><br>SELECT REPLACE('ababab', 'ab', 'cd');<br>-- Result: 'cdcdcd'<br><br>SELECT REPLACE('2025-09-23', '-', '/');<br>-- Result: '2025/09/23'</div>
<!--CONTENT-REPLACE__--></p>
</div>
</div>
<script>
setupModal("myModal-REPLACE__", "openBtn-REPLACE__");
</script>
</div>
<!-- END OF REPLACE() -->
<!-- LEFT, RIGHT, SUBSTRING -->
<div class="grid-item">
<button class="tilt1" id="openBtn-LEFT__RIGHT__SUBSTRING">LEFT, RIGHT, SUBSTRING</button>
<div id="myModal-LEFT__RIGHT__SUBSTRING" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>LEFT, RIGHT, SUBSTRING</h2>
<p><!--START-CONTENT-LEFT__RIGHT__SUBSTRING--></p>
<p><div class="category-content">SELECT LEFT('PostgreSQL', 4); -- 'Post'<br>SELECT LEFT('PostgreSQL', 0); -- ''<br>SELECT LEFT('PostgreSQL', -3); --Postgre<br><br><br>SELECT RIGHT('PostgreSQL', 3); -- 'SQL'<br>SELECT RIGHT('PostgreSQL', 0); -- ''<br>SELECT RIGHT('PostgreSQL', -3); -- tgreSQL<br><br><br>SELECT SUBSTRING('PostgreSQL' FROM 5 FOR 3); -- 'gre'<br>SELECT SUBSTRING('PostgreSQL' FROM -3 FOR 2); -- Null<br>SELECT SUBSTRING('PostgreSQL' FROM -3); -- PostgreSQL (seems to break)<br><br>SELECT SUBSTRING('PostgreSQL' FROM 5);<br>-- Result: 'greSQL' (till end of string)</div>
<div class="category-content">SELECT RIGHT('PostgreSQL', -1);<br>ostgreSQL<br><br>SELECT LEFT('PostgreSQL', -1);<br>PostgreSQ</div>
<div class="category-content">>><br>SELECT SUBSTRING('PostgreSQL', 1, 4); -- Output: 'Post'<br>SELECT SUBSTRING('PostgreSQL', 5, 6); -- Output: 'greSQL'</div>
<!--CONTENT-LEFT__RIGHT__SUBSTRING--></p>
</div>
</div>
<script>
setupModal("myModal-LEFT__RIGHT__SUBSTRING", "openBtn-LEFT__RIGHT__SUBSTRING");
</script>
</div>
<!-- END OF LEFT, RIGHT, SUBSTRING -->
<!-- LENGTH() -->
<div class="grid-item">
<button class="tilt4" id="openBtn-LENGTH__">LENGTH()</button>
<div id="myModal-LENGTH__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>LENGTH()</h2>
<p><!--START-CONTENT-LENGTH__--></p>
<p><div class="category-content">SELECT LENGTH('PostgreSQL'); -- 10<br>SELECT LENGTH('こんにちは'); -- 5 (counts characters, even multibyte)<br>SELECT LENGTH(''); -- 0<br><br>-- Get last 3 characters<br>SELECT SUBSTRING('PostgreSQL' FROM LENGTH('PostgreSQL')-2 FOR 3); -- 'SQL'<br><br>-- Remove last 3 characters<br>SELECT LEFT('PostgreSQL', LENGTH('PostgreSQL')-3); -- 'Postgre'</div>
<!--CONTENT-LENGTH__--></p>
</div>
</div>
<script>
setupModal("myModal-LENGTH__", "openBtn-LENGTH__");
</script>
</div>
<!-- END OF LENGTH() -->
<!-- BIT_LENGTH() -->
<div class="grid-item">
<button class="tilt5" id="openBtn-BIT_LENGTH__">BIT_LENGTH()</button>
<div id="myModal-BIT_LENGTH__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>BIT_LENGTH()</h2>
<p><!--START-CONTENT-BIT_LENGTH__--></p>
<p><div class="category-content">BIT_LENGTH() returns the number of bits used to store a string (or bytea). Since each character in a standard string is typically 1 byte (8 bits), BIT_LENGTH() is usually 8 × LENGTH(string).<br><br>SELECT BIT_LENGTH('PostgreSQL'); -- 80 (10 chars × 8 bits)<br>SELECT BIT_LENGTH(''); -- 0<br><br>-- Multibyte characters (UTF-8)<br>SELECT BIT_LENGTH('こんにちは'); -- 40 (5 characters × 8 bits, in UTF-8 each char may take more bytes but BIT_LENGTH counts actual storage in bits)</div>
<!--CONTENT-BIT_LENGTH__--></p>
</div>
</div>
<script>
setupModal("myModal-BIT_LENGTH__", "openBtn-BIT_LENGTH__");
</script>
</div>
<!-- END OF BIT_LENGTH() -->
<!-- POSITION() -->
<div class="grid-item">
<button class="tilt4" id="openBtn-POSITION__">POSITION()</button>
<div id="myModal-POSITION__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>POSITION()</h2>
<p><!--START-CONTENT-POSITION__--></p>
<p><div class="category-content">POSITION() finds the starting position of a substring within a string. It returns an integer indicating the 1-based index of the first occurrence, or 0 if the substring is not found.<br><br>POSITION(substring IN string)<br><br>SELECT POSITION('SQL' IN 'PostgreSQL'); -- 9<br>SELECT POSITION('Post' IN 'PostgreSQL'); -- 1<br>SELECT POSITION('x' IN 'PostgreSQL'); -- 0 (not found)</div>
<img src="cat_images/position.png" alt="POSITION()" width="500" style="display:block; margin-right:auto;">
<!--CONTENT-POSITION__--></p>
</div>
</div>
<script>
setupModal("myModal-POSITION__", "openBtn-POSITION__");
</script>
</div>
<!-- END OF POSITION() -->
<!-- CONCAT(), CONCAT_WS() -->
<div class="grid-item">
<button class="tilt3" id="openBtn-CONCAT____CONCAT_WS__">CONCAT(), CONCAT_WS()</button>
<div id="myModal-CONCAT____CONCAT_WS__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>CONCAT(), CONCAT_WS()</h2>
<p><!--START-CONTENT-CONCAT____CONCAT_WS__--></p>
<p><div class="category-content">SELECT CONCAT('Hello', ' ', 'World'); -- 'Hello World'<br>SELECT CONCAT('Value: ', NULL, '123'); -- 'Value: 123'<br><br>SELECT CONCAT_WS('-', '2025', '09', '23'); -- '2025-09-23'<br>SELECT CONCAT_WS(' ', 'Hello', NULL, 'World'); -- 'Hello World'<br><br>SELECT 'Hello' || ' ' || 'World'; -- 'Hello World'<br><br>SELECT 'Value: ' || NULL || '123'; -- NULL<br>Here NULL propagates and the entire concatenation returns NULL. <br><br>SELECT 'Value: ' || COALESCE(NULL, '') || '123'; -- 'Value: 123'</div>
<div class="category-content">concat ignores null but || does not</div>
<!--CONTENT-CONCAT____CONCAT_WS__--></p>
</div>
</div>
<script>
setupModal("myModal-CONCAT____CONCAT_WS__", "openBtn-CONCAT____CONCAT_WS__");
</script>
</div>
<!-- END OF CONCAT(), CONCAT_WS() -->
<!-- TRIM() -->
<div class="grid-item">
<button class="tilt2" id="openBtn-TRIM__">TRIM()</button>
<div id="myModal-TRIM__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>TRIM()</h2>
<p><!--START-CONTENT-TRIM__--></p>
<p><div class="category-content">1. TRIM()<br>Removes leading and/or trailing characters (by default, whitespace) from a string.<br><br>TRIM([LEADING | TRAILING | BOTH] [characters FROM] string)<br>BOTH (default) → removes from both ends.<br>LEADING → removes from the start only.<br>TRAILING → removes from the end only.<br><br>If no characters are specified, spaces are removed.<br><br><br>SELECT TRIM(' hello '); -- 'hello'<br>SELECT TRIM(BOTH 'x' FROM 'xxxhelloxx'); -- 'hello'<br>SELECT TRIM(LEADING 'x' FROM 'xxxhelloxx'); -- 'helloxx'<br>SELECT TRIM(TRAILING 'x' FROM 'xxxhelloxx'); -- 'xxxhello'<br><br>2. LTRIM()<br>Removes characters from the left (start) of the string.<br><br>SELECT LTRIM(' hello'); -- 'hello'<br>SELECT LTRIM('xxxhello', 'x'); -- 'hello'<br><br>3. RTRIM()<br>Removes characters from the right (end) of the string.<br><br>SELECT RTRIM('hello '); -- 'hello'<br>SELECT RTRIM('helloxxx', 'x'); -- 'hello'</div>
<div class="category-content">-- Trim whitespace and handle empty result<br> IF TRIM(full_name) = '' THEN<br> full_name := NULL;<br> END IF;</div>
<!--CONTENT-TRIM__--></p>
</div>
</div>
<script>
setupModal("myModal-TRIM__", "openBtn-TRIM__");
</script>
</div>
<!-- END OF TRIM() -->
<!-- NULL -->
<div class="grid-item">
<button class="tilt1" id="openBtn-NULL">NULL</button>
<div id="myModal-NULL" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>NULL</h2>
<p><!--START-CONTENT-NULL--></p>
<p><div class="category-content">SELECT *<br>FROM employees<br>WHERE manager_id = NULL;<br>This will return no rows, even if some manager_id values are NULL.<br><br>The correct way<br>Use IS NULL or IS NOT NULL instead:<br><br>-- Find rows where manager_id is NULL<br>SELECT *<br>FROM employees<br>WHERE manager_id IS NULL;<br><br>-- Find rows where manager_id is not NULL<br>SELECT *<br>FROM employees<br>WHERE manager_id IS NOT NULL;</div>
<!--CONTENT-NULL--></p>
</div>
</div>
<script>
setupModal("myModal-NULL", "openBtn-NULL");
</script>
</div>
<!-- END OF NULL -->
<!-- SET -->
<div class="grid-item">
<button class="tilt2" id="openBtn-SET">SET</button>
<div id="myModal-SET" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>SET</h2>
<p><!--START-CONTENT-SET--></p>
<p><div class="category-content">1. SET in UPDATE statements<br>Here, the = is required — it assigns a value to a column.<br><br>UPDATE employees<br>SET salary = 50000<br>WHERE id = 123;<br><br>2. ALTER statements<br><br>ALTER TABLE my_table<br>ALTER COLUMN my_column TYPE varchar(100);<br><br>ALTER TABLE my_table<br>ALTER COLUMN my_column SET NOT NULL;<br><br>ALTER TABLE employees<br>ALTER COLUMN salary SET DEFAULT 500;<br>This doesn’t change existing rows. It only affects new inserts where salary isn’t specified.</div>
<!--CONTENT-SET--></p>
</div>
</div>
<script>
setupModal("myModal-SET", "openBtn-SET");
</script>
</div>
<!-- END OF SET -->
<!-- search_path -->
<div class="grid-item">
<button class="tilt5" id="openBtn-search_path">search_path</button>
<div id="myModal-search_path" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>search_path</h2>
<p><!--START-CONTENT-search_path--></p>
<p><div class="category-content">SET search_path TO public;<br>ALTER ROLE yoana SET search_path TO public;<br><br>This allows for the table and column names to pop up as suggestioons in the QT</div>
<!--CONTENT-search_path--></p>
</div>
</div>
<script>
setupModal("myModal-search_path", "openBtn-search_path");
</script>
</div>
<!-- END OF search_path -->
<!-- SERIAL, GENERATED ALWAYS AS -->
<div class="grid-item">
<button class="tilt4" id="openBtn-SERIAL__GENERATED_ALWAYS_AS">SERIAL, GENERATED ALWAYS AS</button>
<div id="myModal-SERIAL__GENERATED_ALWAYS_AS" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>SERIAL, GENERATED ALWAYS AS</h2>
<p><!--START-CONTENT-SERIAL__GENERATED_ALWAYS_AS--></p>
<p><div class="category-content">CREATE TABLE sales (<br> price numeric,<br> quantity int,<br> total numeric GENERATED ALWAYS AS (price * quantity) STORED<br>);<br><br>GENERATED ALWAYS AS (expression) defines the computed column, i.e., it specifies how the value is derived.<br><br>By itself, it doesn’t store anything physically. To actually save the value in the table, you need to add STORED.<br><br><br>CREATE TABLE users (<br> id SERIAL PRIMARY KEY,<br> name text<br>);<br><br>INSERT INTO users (name) VALUES ('Alice');<br>-- id = 1 automatically, then 2, 3, ...<br><br>GENERATED ALWAYS AS automatically computes the column value and does not allow manual inserts, while SERIAL uses a sequence to auto-fill values but still lets you manually insert if desired.</div>
<div class="category-content">>>><br>ALTER TABLE <br> countries<br>ADD COLUMN <br> capital_code CHAR(2) GENERATED ALWAYS AS (SUBSTRING(capital FROM 1 FOR 2)) STORED;</div>
<div class="category-content">>><br><br>CREATE TABLE example (<br> id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,<br> name TEXT<br>);<br><br>GENERATED ALWAYS AS IDENTITY → PostgreSQL always generates the value. If you try to insert into id, it will fail (unless you use OVERRIDING SYSTEM VALUE).<br><br>GENERATED BY DEFAULT AS IDENTITY → PostgreSQL generates a value if you don’t supply one.</div>
<!--CONTENT-SERIAL__GENERATED_ALWAYS_AS--></p>
</div>
</div>
<script>
setupModal("myModal-SERIAL__GENERATED_ALWAYS_AS", "openBtn-SERIAL__GENERATED_ALWAYS_AS");
</script>
</div>
<!-- END OF SERIAL, GENERATED ALWAYS AS -->
<!-- PRIMARY KEY vs UNIQUE -->
<div class="grid-item">
<button class="tilt5" id="openBtn-PRIMARY_KEY_vs_UNIQUE">PRIMARY KEY vs UNIQUE</button>
<div id="myModal-PRIMARY_KEY_vs_UNIQUE" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>PRIMARY KEY vs UNIQUE</h2>
<p><!--START-CONTENT-PRIMARY_KEY_vs_UNIQUE--></p>
<p><div class="category-content">PRIMARY KEY <br><br>Uniquely identifies each row in a table. No two rows can have the same value, and the column(s) cannot be NULL.<br>Number of constraints per table: 1 - one table cannot have more than 1 PRIMARY KEY columns<br><br>UNIQUE <br>Ensures all values in the column(s) are distinct, but NULLs are allowed (except in some DBMS where multiple NULLs are allowed).<br>Allows NULLs <br>Can be used many times in a table<br><br><br>CREATE TABLE employees (<br> emp_id SERIAL PRIMARY KEY,<br> email TEXT UNIQUE,<br> ssn CHAR(9) UNIQUE<br>);<br>emp_id uniquely identifies each row.<br>email and ssn must be unique, but they are separate from the primary key.<br><br><br>PRIMARY KEY = unique + not null + only one per table<br>UNIQUE = unique values (can be multiple per table, can allow NULL)</div>
<!--CONTENT-PRIMARY_KEY_vs_UNIQUE--></p>
</div>
</div>
<script>
setupModal("myModal-PRIMARY_KEY_vs_UNIQUE", "openBtn-PRIMARY_KEY_vs_UNIQUE");
</script>
</div>
<!-- END OF PRIMARY KEY vs UNIQUE -->
<!-- RETURNING -->
<div class="grid-item">
<button class="tilt3" id="openBtn-RETURNING">RETURNING</button>
<div id="myModal-RETURNING" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>RETURNING</h2>
<p><!--START-CONTENT-RETURNING--></p>
<p><div class="category-content">RETURNING only works with data-modifying statements:<br><br>INSERT<br>UPDATE<br>DELETE<br><br><br>1. INSERT … RETURNING<br>Use it when you want to know what values were inserted, especially useful for auto-generated IDs.<br><br>INSERT INTO users (name, email)<br>VALUES ('Alice', '[email protected]')<br>RETURNING id, name;<br><br>Returns the id and name of the newly inserted row(s).<br><br><br>2. UPDATE … RETURNING<br>Use it when you want to see the new state of updated rows.<br><br>UPDATE users<br>SET email = '[email protected]'<br>WHERE name = 'Alice'<br>RETURNING id, email;<br><br>Returns the id and updated email for the rows that were changed.<br><br><br>3. DELETE … RETURNING<br>Use it when you want to know what was deleted.<br><br>DELETE FROM users<br>WHERE name = 'Alice'<br>RETURNING id, name;<br>Returns the id and name of the deleted row(s).</div>
<!--CONTENT-RETURNING--></p>
</div>
</div>
<script>
setupModal("myModal-RETURNING", "openBtn-RETURNING");
</script>
</div>
<!-- END OF RETURNING -->
<!-- GROUP BY -->
<div class="grid-item">
<button class="tilt2" id="openBtn-GROUP_BY">GROUP BY</button>
<div id="myModal-GROUP_BY" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>GROUP BY</h2>
<p><!--START-CONTENT-GROUP_BY-->
<div class="category-content">GROUP BY is used to aggregate data. It takes a table with many rows and groups them into “buckets” based on one or more columns. Then you can apply aggregate functions (COUNT, SUM, AVG, etc.) to each group.<br><br>SELECT <br> LEFT(first_name, 2) AS initials,<br> COUNT(*) AS user_count<br>FROM users<br>GROUP BY initials<br><br>GROUP BY initials tells SQL to group all rows by the first two letters of first_name.<br>Within each group, COUNT(*) counts how many users are in that group.<br><br>SELECT city, COUNT(*) AS user_count<br>FROM users<br>GROUP BY city;</div>
<img src="cat_images/usr_count.png" alt="GROUP BY" width="500" style="display:block; margin-right:auto;">
<div class="category-content">SQL looked at the city column.<br>Made one group for each unique city.<br>Counted how many rows were in each group using COUNT(*).<br><br>COUNT(*) → counts all rows in the group, including duplicates and NULLs.<br>COUNT(column_name) → counts only rows where the column is not NULL.<br><br>SELECT city,<br> COUNT(*) AS total_users,<br> AVG(age) AS avg_age,<br> MIN(age) AS youngest,<br> MAX(age) AS oldest<br>FROM users<br>GROUP BY city;</div>
<img src="cat_images/sql_avg_grp.png" alt="GROUP BY" width="500" style="display:block; margin-right:auto;">
<div class="category-content">>><br>count(distinct) returns the count of groups<br>count(*) returns the count of rows</div>
<!--CONTENT-GROUP_BY--></p>
</div>
</div>
<script>
setupModal("myModal-GROUP_BY", "openBtn-GROUP_BY");
</script>
</div>
<!-- END OF GROUP BY -->
<!-- WHERE vs HAVING -->
<div class="grid-item">
<button class="tilt2" id="openBtn-WHERE_vs_HAVING">WHERE vs HAVING</button>
<div id="myModal-WHERE_vs_HAVING" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>WHERE vs HAVING</h2>
<p><!--START-CONTENT-WHERE_vs_HAVING--></p>
<p><div class="category-content">WHERE filters before the aggregation, HAVING filetrs after the aggregation (COUNT, AVG, SUM, MIN, etc)</div>
<div class="category-content">>><br>In SQL, the key thing to understand is the order in which the clauses are logically processed:<br><br>SELECT <br> department_id,<br> SUM(salary) AS "Total Salary",<br> COUNT(*) AS "Num Employees"<br>FROM employees<br>WHERE salary > 1000 -- filter individual rows<br>GROUP BY department_id<br>HAVING SUM(salary) < 4200 -- filter grouped results<br>ORDER BY department_id;<br><br>1. FROM employees → take the table.<br>2. WHERE salary > 1000 → only rows with salary > 1000 remain.<br>3. GROUP BY department_id → group those rows by department.<br>4. SUM(salary) + COUNT(*) are calculated per group.<br>5. HAVING SUM(salary) < 4200 → throw away groups that don’t meet this condition.<br>6. SELECT → output the columns (including aliases).<br>7. ORDER BY department_id → sort the result.</div>
<!--CONTENT-WHERE_vs_HAVING--></p>
</div>
</div>
<script>
setupModal("myModal-WHERE_vs_HAVING", "openBtn-WHERE_vs_HAVING");
</script>
</div>
<!-- END OF WHERE vs HAVING -->
<!-- CASE -->
<div class="grid-item">
<button class="tilt5" id="openBtn-CASE">CASE</button>
<div id="myModal-CASE" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>CASE</h2>
<p><!--START-CONTENT-CASE--></p>
<p><div class="category-content">CASE<br> WHEN condition1 THEN result1<br> WHEN condition2 THEN result2<br> ELSE result_default<br>END<br><br>SELECT<br> e.id,<br> e.first_name,<br> e.last_name,<br> CAST(e.salary AS DECIMAL(18,2)),<br> e.department_id,<br> CASE<br> WHEN d.name NOT IN ('Management', 'Kitchen Staff', 'Service Staff')<br> THEN 'Other'<br> ELSE d.name<br> END AS department_name<br><br>FROM employees as e<br>JOIN departments as d<br>ON e.department_id = d.id</div>
<img src="cat_images/case1.png" alt="CASE" width="500" style="display:block; margin-right:auto;">
<img src="cat_images/case2.png" alt="CASE" width="500" style="display:block; margin-right:auto;">
<div class="category-content">>><br> CASE department_id<br> WHEN 1 THEN salary*1.15<br> WHEN 2 THEN salary*1.10<br> ELSE salary*1.05<br> END</div>
<div class="category-content">>><br>UPDATE employees<br>SET<br> salary = CASE <br> WHEN hire_date < '2015-01-16' THEN salary + 2500<br> WHEN hire_date < '2020-03-04' THEN salary + 1500<br> ELSE salary<br> END,<br> job_title = CASE<br> WHEN hire_date < '2015-01-16' THEN CONCAT('Senior ', job_title)<br> WHEN hire_date < '2020-03-04' THEN CONCAT('Mid-', job_title)<br> ELSE job_title<br> END;</div>
<div class="category-content">>><br>Nested:<br>SELECT "first_name", "last_name", "job_title", "salary", "department_id",<br><br>CASE<br> WHEN salary >= 25000 THEN <br> CASE<br> WHEN job_title LIKE 'Senior%' THEN 'High-performing Senior'<br> ELSE 'High-performing Employee'<br> END<br> ELSE 'Average-performing'<br>END AS performance_rating<br><br>FROM "employees"</div>
<div class="category-content">>><br>SELECT<br> SUM(CASE WHEN department_id = 1 THEN 1 ELSE 0 END) AS "Engineering",<br> SUM(CASE WHEN department_id = 2 THEN 1 ELSE 0 END) AS "Tool Design",<br> SUM(CASE WHEN department_id = 3 THEN 1 ELSE 0 END) AS "Sales",<br> SUM(CASE WHEN department_id = 4 THEN 1 ELSE 0 END) AS "Marketing",<br> SUM(CASE WHEN department_id = 5 THEN 1 ELSE 0 END) AS "Purchasing",<br> SUM(CASE WHEN department_id = 6 THEN 1 ELSE 0 END) AS "Research and Development",<br> SUM(CASE WHEN department_id = 7 THEN 1 ELSE 0 END) AS "Production"<br>FROM employees;<br><br><br>SET<br> salary = CASE <br> WHEN hire_date < '2015-01-16' THEN salary + 2500<br> WHEN hire_date < '2020-03-04' THEN salary + 1500<br> ELSE salary<br> END,<br> job_title = CASE<br> WHEN hire_date < '2015-01-16' THEN CONCAT('Senior ', job_title)<br> WHEN hire_date < '2020-03-04' THEN CONCAT('Mid-', job_title)<br> ELSE job_title<br> END;</div>
<!--CONTENT-CASE--></p>
</div>
</div>
<script>
setupModal("myModal-CASE", "openBtn-CASE");
</script>
</div>
<!-- END OF CASE -->
<!-- JOIN -->
<div class="grid-item">
<button class="tilt1" id="openBtn-JOIN">JOIN</button>
<div id="myModal-JOIN" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>JOIN</h2>
<p><!--START-CONTENT-JOIN--></p>
<p><div class="category-content">1. INNER JOIN<br><br>Returns only the rows that match in both tables.<br>If there’s no match, the row is not included.<br><br>SELECT students.id, students.student_name, exams.exam_name<br>FROM students<br>INNER JOIN exams<br>ON students.id = exams.id;<br><br>Here, only students who have matching id in exams will appear.<br><br>2. LEFT JOIN (or LEFT OUTER JOIN)<br><br>Returns all rows from the left table (first table), and the matching rows from the right table.<br>If there’s no match in the right table, you get NULL for the right table’s columns.<br><br>SELECT students.student_name, exams.exam_name<br>FROM students<br>LEFT JOIN exams<br>ON students.id = exams.id;<br>All students appear. If a student has no exam, exam_name will be NULL.<br><br>3. RIGHT JOIN (or RIGHT OUTER JOIN)<br><br>Opposite of LEFT JOIN: returns all rows from the right table, and matching rows from the left table.<br>If there’s no match in the left table, you get NULL for the left table’s columns.<br><br>4. FULL JOIN (or FULL OUTER JOIN)<br><br>Returns all rows from both tables, with NULL where there’s no match.<br><br>5. CROSS JOIN<br><br>Returns every combination of rows from the two tables.<br>If Table A has 3 rows and Table B has 4 rows, the result has 3 × 4 = 12 rows.</div>
<img src="cat_images/join_sc.png" alt="JOIN" width="500" style="display:block; margin-right:auto;">
<div class="category-content">>>><br>JOIN customers AS c<br>USING customer_id -> WRONG<br><br>JOIN customers AS c<br>USING (customer_id) -> RIGHT</div>
<!--CONTENT-JOIN--></p>
</div>
</div>
<script>
setupModal("myModal-JOIN", "openBtn-JOIN");
</script>
</div>
<!-- END OF JOIN -->
<!-- TRUNC -->
<div class="grid-item">
<button class="tilt5" id="openBtn-TRUNC">TRUNC</button>
<div id="myModal-TRUNC" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>TRUNC</h2>
<p><!--START-CONTENT-TRUNC--></p>
<p><div class="category-content">SELECT TRUNC(123.456); -- Result: 123<br>SELECT TRUNC(123.456, 2); -- Result: 123.45<br>SELECT TRUNC(-123.456, 1); -- Result: -123.4<br>SELECT TRUNC(123.456, -1); -- Result: 120<br><br>SELECT DATE_TRUNC('year', CURRENT_TIMESTAMP); -- Jan 1, 00:00:00 of current year<br>SELECT DATE_TRUNC('month', CURRENT_TIMESTAMP); -- 1st of month, 00:00:00<br>SELECT DATE_TRUNC('day', CURRENT_TIMESTAMP); -- Today at 00:00:00<br>SELECT DATE_TRUNC('hour', CURRENT_TIMESTAMP); -- Current hour, minutes and seconds zeroed</div>
<!--CONTENT-TRUNC--></p>
</div>
</div>
<script>
setupModal("myModal-TRUNC", "openBtn-TRUNC");
</script>
</div>
<!-- END OF TRUNC -->
<!-- DUAL (Oracle) -->
<div class="grid-item">
<button class="tilt4" id="openBtn-DUAL__Oracle_">DUAL (Oracle)</button>
<div id="myModal-DUAL__Oracle_" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>DUAL (Oracle)</h2>
<p><!--START-CONTENT-DUAL__Oracle_--></p>
<p><div class="category-content">In Oracle SQL, DUAL is a special one-row, one-column table that exists by default in every Oracle database. It’s mainly used when you need to select a value or evaluate an expression without querying a real table. The column is named DUMMY and contains the value 'X'.<br><br>SELECT 1 + 1 FROM dual; <br>-- Result: 2<br><br>In PostgreSQL, there is no DUAL table like in Oracle. PostgreSQL allows you to select expressions or call functions without specifying a table at all.<br><br>SELECT 1 + 1; -- Result: 2</div>
<!--CONTENT-DUAL__Oracle_--></p>
</div>
</div>
<script>
setupModal("myModal-DUAL__Oracle_", "openBtn-DUAL__Oracle_");
</script>
</div>
<!-- END OF DUAL (Oracle) -->
<!-- COALESCE -->
<div class="grid-item">
<button class="tilt3" id="openBtn-COALESCE">COALESCE</button>
<div id="myModal-COALESCE" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>COALESCE</h2>
<p><!--START-CONTENT-COALESCE--></p>
<p><div class="category-content">COALESCE() is a SQL function used to return the first non-NULL value from a list of expressions. It’s very useful when you want to handle NULLs gracefully in queries.<br><br>SELECT COALESCE(NULL, NULL, 'Hello', 'World');<br>--Hello<br><br>COALESCE(country, 'Unknown')<br>--If country is NOT NULL, it returns the value of country.<br>--If country is NULL, it returns 'Unknown'.</div>
<div class="category-content">>><br>COALESCE() is not for conditional logic—it’s only for replacing NULLs.<br><br>Bad example:<br>COALESCE(COUNT(salary) > 1000, 'Rich') <br>--COUNT(salary) returns a number.<br>--COUNT(salary) > 1000 is a boolean expression (true/false in some SQL dialects, 1/0 in others).<br>--COALESCE() is meant to return the first non-NULL value, but COUNT(salary) > 1000 is almost never NULL—it will be either true/false or 1/0. So COALESCE() won’t behave the way you want here.</div>
<!--CONTENT-COALESCE--></p>
</div>
</div>
<script>
setupModal("myModal-COALESCE", "openBtn-COALESCE");
</script>
</div>
<!-- END OF COALESCE -->
<!-- CTE (Common Table Expression) - WITH -->
<div class="grid-item">
<button class="tilt2" id="openBtn-CTE__Common_Table_Expression____WITH">CTE (Common Table Expression) - WITH</button>
<div id="myModal-CTE__Common_Table_Expression____WITH" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>CTE (Common Table Expression) - WITH</h2>
<p><!--START-CONTENT-CTE__Common_Table_Expression____WITH--></p>
<p><div class="category-content">In SQL, you can’t use the alias (age_group) inside the same SELECT for aggregation, hence this lame repeated block:<br><br>SELECT<br> CASE <br> WHEN age BETWEEN 11 AND 20 THEN '[11-20]'<br> WHEN age BETWEEN 21 AND 30 THEN '[21-30]'<br> WHEN age BETWEEN 31 AND 40 THEN '[31-40]'<br> WHEN age BETWEEN 41 AND 50 THEN '[41-50]'<br> WHEN age BETWEEN 51 AND 60 THEN '[51-60]'<br> ELSE '[61+]'<br> END AS age_group,<br> COUNT(*) AS group_count<br>FROM wizard_deposits<br>GROUP BY<br> CASE <br> WHEN age BETWEEN 11 AND 20 THEN '[11-20]'<br> WHEN age BETWEEN 21 AND 30 THEN '[21-30]'<br> WHEN age BETWEEN 31 AND 40 THEN '[31-40]'<br> WHEN age BETWEEN 41 AND 50 THEN '[41-50]'<br> WHEN age BETWEEN 51 AND 60 THEN '[51-60]'<br> ELSE '[61+]'<br> END<br>ORDER BY age_group;<br><br>It can be resolved with CTE:<br><br>WITH age_buckets AS (<br> SELECT <br> CASE <br> WHEN age BETWEEN 11 AND 20 THEN '[11-20]'<br> WHEN age BETWEEN 21 AND 30 THEN '[21-30]'<br> WHEN age BETWEEN 31 AND 40 THEN '[31-40]'<br> WHEN age BETWEEN 41 AND 50 THEN '[41-50]'<br> WHEN age BETWEEN 51 AND 60 THEN '[51-60]'<br> ELSE '[61+]'<br> END AS age_group<br> FROM wizard_deposits<br>)<br>SELECT age_group, COUNT(*) AS group_count<br>FROM age_buckets<br>GROUP BY age_group<br>ORDER BY age_group;</div>
<div class="category-content">>>><br><br>WITH <br> results<br>AS (<br> SELECT<br> c.country_name,<br> COALESCE(p.peak_name, '(no highest peak)') AS highest_peak_name,<br> COALESCE(p.elevation, 0) AS highest_peak_elevation,<br> COALESCE(m.mountain_range, '(no mountain)') AS mountain,<br> ROW_NUMBER() OVER (PARTITION BY c.country_name ORDER BY p.elevation DESC) AS row_num<br> FROM<br> countries AS c<br> LEFT JOIN<br> mountains_countries AS mc<br> USING<br> (country_code)<br> LEFT JOIN<br> peaks AS p<br> USING<br> (mountain_id)<br> LEFT JOIN<br> mountains AS m<br> ON<br> m.id = p.mountain_id<br>)<br><br><br>SELECT<br> country_name,<br> highest_peak_name,<br> highest_peak_elevation,<br> mountain<br>FROM <br> results<br>WHERE <br> row_num = 1<br>ORDER BY<br> country_name ASC,<br> highest_peak_elevation DESC;</div>
<!--CONTENT-CTE__Common_Table_Expression____WITH--></p>
</div>
</div>
<script>
setupModal("myModal-CTE__Common_Table_Expression____WITH", "openBtn-CTE__Common_Table_Expression____WITH");
</script>
</div>
<!-- END OF CTE (Common Table Expression) - WITH -->
<!-- Pivot -->
<div class="grid-item">
<button class="tilt5" id="openBtn-Pivot">Pivot</button>
<div id="myModal-Pivot" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>Pivot</h2>
<p><!--START-CONTENT-Pivot--></p>
<p><div class="category-content">When you transform row values into columns, that operation is generally called a pivot in SQL.<br><br>SELECT<br> SUM(CASE WHEN department_id = 1 THEN 1 ELSE 0 END) AS "Engineering",<br> SUM(CASE WHEN department_id = 2 THEN 1 ELSE 0 END) AS "Tool Design",<br> SUM(CASE WHEN department_id = 3 THEN 1 ELSE 0 END) AS "Sales",<br> SUM(CASE WHEN department_id = 4 THEN 1 ELSE 0 END) AS "Marketing",<br> SUM(CASE WHEN department_id = 5 THEN 1 ELSE 0 END) AS "Purchasing",<br> SUM(CASE WHEN department_id = 6 THEN 1 ELSE 0 END) AS "Research and Development",<br> SUM(CASE WHEN department_id = 7 THEN 1 ELSE 0 END) AS "Production"<br>FROM employees;<br><br>>>><br>SQL Server/ Oracle (Postgre does not have PIVOT()):<br><br>SELECT *<br>FROM (<br> SELECT employee_id, department_code<br> FROM employees<br>) AS src<br>PIVOT (<br> COUNT(employee_id)<br> FOR department_code IN ([1],[2],[3])<br>) AS pvt;</div>
<!--CONTENT-Pivot--></p>
</div>
</div>
<script>
setupModal("myModal-Pivot", "openBtn-Pivot");
</script>
</div>
<!-- END OF Pivot -->
<!-- FOREIGN KEY + REFERENCES -->
<div class="grid-item">
<button class="tilt2" id="openBtn-FOREIGN_KEY___REFERENCES">FOREIGN KEY + REFERENCES</button>
<div id="myModal-FOREIGN_KEY___REFERENCES" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>FOREIGN KEY + REFERENCES</h2>
<p><!--START-CONTENT-FOREIGN_KEY___REFERENCES--></p>
<p><div class="category-content">CREATE TABLE employees_projects (<br> id SERIAL PRIMARY KEY, <br> employee_id INT NOT NULL,<br> project_id INT NOT NULL,<br> FOREIGN KEY (employee_id) REFERENCES employees(id),<br> FOREIGN KEY (project_id) REFERENCES projects(id)<br>);<br><br>Foreign Key points to a column in another table (usually that table’s primary key).<br>You define it as a normal column, then add the FOREIGN KEY constraint.</div>
<div class="category-content">>><br>In Postgre SQL we can reference upon creation:<br><br>mountain_id INT REFERENCES mountains --(id by default)</div>
<div class="category-content">>>><br>We can name constraints like this:<br>CREATE TABLE peaks (<br> id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,<br> name VARCHAR(50),<br> mountain_id INT NOT NULL,<br> CONSTRAINT fk_peaks_mountains<br> FOREIGN KEY (mountain_id) REFERENCES mountains(id)<br>);</div>
<div class="category-content">>> NOT NULL placement<br>brand_id INT REFERENCES brands(id) NOT NULL ON DELETE CASCADE ON UPDATE CASCADE, -> WRONG<br>brand_id INT NOT NULL REFERENCES brands(id) ON DELETE CASCADE ON UPDATE CASCADE, -> RIGHT</div>
<!--CONTENT-FOREIGN_KEY___REFERENCES--></p>
</div>
</div>
<script>
setupModal("myModal-FOREIGN_KEY___REFERENCES", "openBtn-FOREIGN_KEY___REFERENCES");
</script>
</div>
<!-- END OF FOREIGN KEY + REFERENCES -->
<!-- Regex in PostgreSQL -->
<div class="grid-item">
<button class="tilt4" id="openBtn-Regex_in_PostgreSQL">Regex in PostgreSQL</button>
<div id="myModal-Regex_in_PostgreSQL" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>Regex in PostgreSQL</h2>
<p><!--START-CONTENT-Regex_in_PostgreSQL--></p>
<p><div class="category-content">~ Matches regex (case-sensitive)<br>~* Matches regex (case-insensitive)<br>!~ Does NOT match regex (case-sensitive)<br>!~* Does NOT match regex (case-insensitive)<br><br>-- Case-sensitive match<br>SELECT 'Hello123' ~ '^[A-Z][a-z]+[0-9]+$'; -- true<br><br>-- Case-insensitive match<br>SELECT 'hello123' ~* '^[A-Z][a-z]+[0-9]+$'; -- true<br><br>-- Negation<br>SELECT 'Hello123' !~ '[0-9]$'; -- false<br><br><br>1. REGEXP_MATCHES<br><br> SELECT REGEXP_MATCHES('abc123xyz', '[0-9]+');<br> -- {123}<br><br>Can return multiple matches with the g (global) flag:<br><br> SELECT REGEXP_MATCHES('abc123xyz456', '[0-9]+', 'g'); <br> -- {123}, {456}<br><br>2. REGEXP_REPLACE<br><br> SELECT REGEXP_REPLACE('My phone is 408-123-4567', '[0-9]', 'X', 'g');<br> -- 'My phone is XXX-XXX-XXXX'<br>'g' = global replacement (all matches).<br>Without 'g', only the first match is replaced.<br><br>3. REGEXP_SPLIT_TO_ARRAY<br> SELECT REGEXP_SPLIT_TO_ARRAY('apple,banana;cherry', '[,;]');<br> -- {apple,banana,cherry}<br><br>4. REGEXP_SPLIT_TO_TABLE<br>Splits a string into multiple rows instead of an array.<br> SELECT REGEXP_SPLIT_TO_TABLE('apple,banana;cherry', '[,;]');<br> -- apple<br> -- banana<br> -- cherry</div>
<!--CONTENT-Regex_in_PostgreSQL--></p>
</div>
</div>
<script>
setupModal("myModal-Regex_in_PostgreSQL", "openBtn-Regex_in_PostgreSQL");
</script>
</div>
<!-- END OF Regex in PostgreSQL -->
<!-- CONSTRAINT -->
<div class="grid-item">
<button class="tilt4" id="openBtn-CONSTRAINT">CONSTRAINT</button>
<div id="myModal-CONSTRAINT" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>CONSTRAINT</h2>
<p><!--START-CONTENT-CONSTRAINT--></p>
<p><div class="category-content">PRIMARY KEY -> Uniquely identifies each row. Implies NOT NULL. -> id INT PRIMARY KEY<br>FOREIGN KEY -> Ensures a column’s values match values in another table (referential integrity) -> customer_id INT REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE<br>UNIQUE -> Ensures all values in a column (or combination of columns) are distinct -> email VARCHAR(50) UNIQUE<br>CHECK -> Ensures values meet a condition -> age INT CHECK (age >= 18)<br>NOT NULL -> Ensures a column cannot be NULL -> name VARCHAR(50) NOT NULL<br>DEFAULT -> Assigns a default value if none is provided -> status VARCHAR(10) DEFAULT 'active'<br><br><br>a) Foreign Key<br><br>Foreign Key points to a column in another table (usually that table’s primary key).<br>You define it as a normal column, then add the FOREIGN KEY constraint.<br><br>ALTER TABLE employees<br>ADD CONSTRAINT fk_department<br>FOREIGN KEY (department_id) REFERENCES departments(id)<br>ON DELETE CASCADE<br>ON UPDATE CASCADE;<br><br>ON DELETE/UPDATE actions:<br><br>NO ACTION Default. Don’t allow the change if it breaks the foreign key.<br>RESTRICT Same as NO ACTION, but checked immediately.<br>CASCADE Automatically update/delete the child row when the parent is updated/deleted.<br>SET NULL Set the foreign key in the child row to NULL if the parent row is deleted/updated.<br>SET DEFAULT Set the foreign key in the child row to its default value if the parent row changes.<br><br>When crateing a table:<br><br>passport_id INT REFERENCES passports(id) --> CORRECT<br>passport_id REFERENCES passports(id) --> CORRECT --> INCORRECT<br>passport_id INT FOREIGN KEY REFERENCES passports(id); --> INCORRECT<br><br>The FOREIGN KEY keyword is only allowed in table-level constraints, not inline.<br><br>department_id INT,<br>CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id) --> CORRECT<br><br>b) Unique<br><br>Copy code<br>ALTER TABLE employees<br>ADD CONSTRAINT unique_email UNIQUE (email);<br><br>c) Check<br><br>ALTER TABLE employees<br>ADD CONSTRAINT check_name_length CHECK (char_length(name) > 2);</div>
<div class="category-content">Each ADD CONSTRAINT must be its own ALTER TABLE.<br><br>ALTER TABLE countries<br>ADD CONSTRAINT fk_currency_code<br>FOREIGN KEY (currency_code)<br>REFERENCES currencies(currency_code)<br>ON DELETE CASCADE;<br><br>ALTER TABLE countries<br>ADD CONSTRAINT fk_continent_code<br>FOREIGN KEY (continent_code)<br>REFERENCES continents(continent_code)<br>ON DELETE CASCADE;</div>
<div class="category-content">>>><br><br>customer_id INT REFERENCES customers(id) ON DELETE SET NULL ON UPDATE CASCADE ---> WRONG<br><br>Inline foreign key definition (inside the column definition) does not always support ON UPDATE and ON DELETE actions in all databases.<br>Most SQL databases (PostgreSQL, MySQL, etc.) require the more explicit CONSTRAINT syntax if you want to define actions like ON DELETE SET NULL or ON UPDATE CASCADE.<br><br>customer_id INT,<br>CONSTRAINT fk_customer<br> FOREIGN KEY (customer_id)<br> REFERENCES customers(id)<br> ON DELETE SET NULL<br> ON UPDATE CASCADE ---> CORRECT</div>
<!--CONTENT-CONSTRAINT--></p>
</div>
</div>
<script>
setupModal("myModal-CONSTRAINT", "openBtn-CONSTRAINT");
</script>
</div>
<!-- END OF CONSTRAINT -->
<!-- INSERT INTO -->
<div class="grid-item">
<button class="tilt5" id="openBtn-INSERT_INTO">INSERT INTO</button>
<div id="myModal-INSERT_INTO" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>INSERT INTO</h2>
<p><!--START-CONTENT-INSERT_INTO--></p>
<p><div class="category-content">INSERT INTO table_name (column1, column2, column3, ...)<br>VALUES (value1, value2, value3, ...);<br><br>INSERT INTO customers (customer_name)<br>VALUES <br> ('BlueBird Inc'),<br> ('Dolphin LLC');<br><br><br>Insert into all columns without specifying column names:<br><br>INSERT INTO students<br>VALUES (1, 'Alice');</div>
<!--CONTENT-INSERT_INTO--></p>
</div>
</div>
<script>
setupModal("myModal-INSERT_INTO", "openBtn-INSERT_INTO");
</script>
</div>
<!-- END OF INSERT INTO -->
<!-- START WITH - INCREMENT BY -->
<div class="grid-item">
<button class="tilt2" id="openBtn-START_WITH___INCREMENT_BY">START WITH - INCREMENT BY</button>
<div id="myModal-START_WITH___INCREMENT_BY" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>START WITH - INCREMENT BY</h2>
<p><!--START-CONTENT-START_WITH___INCREMENT_BY--></p>
<p><div class="category-content">SERIAL: Old-style PostgreSQL pseudo-type. Automatically creates a sequence behind the scenes. You cannot directly set START WITH or INCREMENT BY in the column definition; you have to modify the sequence manually.<br><br>GENERATED AS IDENTITY: SQL standard-compliant. Supports options like START WITH and INCREMENT BY directly.<br><br>CREATE TABLE students (<br> id INT GENERATED ALWAYS AS IDENTITY <br> (START WITH 101 INCREMENT BY 1) PRIMARY KEY,<br> student_name VARCHAR(50)<br>);<br>START WITH 101 → The first value of id will be 101.<br>INCREMENT BY 1 → Each new row increases by 1 (can be any number).<br><br>CREATE TABLE students (<br> id INT GENERATED ALWAYS AS IDENTITY<br> (START WITH 100 -- first value<br> INCREMENT BY 5 -- step size<br> MINVALUE 100 -- minimum value allowed<br> MAXVALUE 200 -- maximum value allowed<br> CYCLE) -- when max is reached, restart from min<br> PRIMARY KEY,<br> student_name VARCHAR(50) NOT NULL<br>);<br><br>START WITH The first value assigned to the column (here: 100)<br>INCREMENT BY How much the value increases with each new row (here: 5 → 100, 105, 110…)<br>MINVALUE Minimum allowed value (here: 100)<br>MAXVALUE Maximum allowed value (here: 200)<br>CYCLE After reaching MAXVALUE, the sequence restarts at MINVALUE<br>NO CYCLE Default behavior; sequence stops at MAXVALUE instead of restarting</div>
<!--CONTENT-START_WITH___INCREMENT_BY--></p>
</div>
</div>
<script>
setupModal("myModal-START_WITH___INCREMENT_BY", "openBtn-START_WITH___INCREMENT_BY");
</script>
</div>
<!-- END OF START WITH - INCREMENT BY -->
<!-- MTM mapping (junction) table -->
<div class="grid-item">
<button class="tilt2" id="openBtn-MTM_mapping__junction__table">MTM mapping (junction) table</button>
<div id="myModal-MTM_mapping__junction__table" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>MTM mapping (junction) table</h2>
<p><!--START-CONTENT-MTM_mapping__junction__table--></p>
<p><div class="category-content">CREATE TABLE students (<br> student_id INT PRIMARY KEY,<br> name VARCHAR(100)<br>);<br><br>CREATE TABLE courses (<br> course_id INT PRIMARY KEY,<br> title VARCHAR(100)<br>);<br><br>--MAPPING TABLE<br>CREATE TABLE student_courses (<br> student_id INT,<br> course_id INT,<br> PRIMARY KEY (student_id, course_id),<br> FOREIGN KEY (student_id) REFERENCES students(student_id),<br> FOREIGN KEY (course_id) REFERENCES courses(course_id)<br>);<br><br>This table connects students and courses.<br><br>The composite primary key here is:<br>PRIMARY KEY (student_id, course_id)<br><br>That means:<br>The combination of student_id and course_id must be unique.<br>A student can’t be enrolled in the same course twice.</div>
<!--CONTENT-MTM_mapping__junction__table--></p>
</div>
</div>
<script>
setupModal("myModal-MTM_mapping__junction__table", "openBtn-MTM_mapping__junction__table");
</script>
</div>
<!-- END OF MTM mapping (junction) table -->
<!-- COUNT() -->
<div class="grid-item">
<button class="tilt5" id="openBtn-COUNT__">COUNT()</button>
<div id="myModal-COUNT__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>COUNT()</h2>
<p><!--START-CONTENT-COUNT__--></p>
<p><div class="category-content">COUNT(DISTINCT m.mountain_range)<br><br>COUNT(...) → counts the number of rows<br>DISTINCT → ensures duplicates are removed before counting<br><br>If you had a countries table and wanted to know how many unique mountain ranges per country:<br><br>SELECT country, COUNT(DISTINCT mountain_range) AS unique_ranges<br>FROM mountains<br>GROUP BY country;</div>
<div class="category-content">>>><br>SELECT country_code<br>from countries<br>LEFT JOIN countries_rivers<br>USING (country_code)<br>LEFT JOIN rivers<br>ON rivers.id = countries_rivers.river_id<br>GROUP BY country_code<br>HAVING COUNT(rivers.river_name) >= 3<br><br><br>COUNT(rivers.river_name) counts how many rivers are linked to each country.<br><br>HAVING COUNT(r.river_name) >= 3 filters to only those countries with at least 3 rivers.<br><br>COUNT() in SQL doesn’t count globally — it counts within the group defined by GROUP BY.<br><br>So, COUNT(rivers.river_name) gives you the number of non-null rivers.river_name values in each group (i.e., for that country).<br><br>It does not count all rivers in the entire dataset.<br>>>></div>
<!--CONTENT-COUNT__--></p>
</div>
</div>
<script>
setupModal("myModal-COUNT__", "openBtn-COUNT__");
</script>
</div>
<!-- END OF COUNT() -->
<!-- relation vs relationship -->
<div class="grid-item">
<button class="tilt4" id="openBtn-relation_vs_relationship">relation vs relationship</button>
<div id="myModal-relation_vs_relationship" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>relation vs relationship</h2>
<p><!--START-CONTENT-relation_vs_relationship--></p>
<p><div class="category-content">1. Relation<br>→ A table in the relational database sense.<br>A relation is a mathematical concept from relational algebra (the theory behind SQL).<br>In practice, it means a table with:<br> Rows (called tuples)<br> Columns (called attributes)<br> Each row represents one record.<br> Each column represents a property of that record.<br><br>2. Relationship (One-to-One, One-to-Many, Many-to-Many)<br>→ A connection between two (or more) relations.<br>A relationship describes how tables (relations) are linked to each other.<br><br>It’s part of data modeling — in ER diagrams, relationships connect entities.</div>
<!--CONTENT-relation_vs_relationship--></p>
</div>
</div>
<script>
setupModal("myModal-relation_vs_relationship", "openBtn-relation_vs_relationship");
</script>
</div>
<!-- END OF relation vs relationship -->
<!-- sequences (start, increment) -->
<div class="grid-item">
<button class="tilt5" id="openBtn-sequences__start__increment_">sequences (start, increment)</button>
<div id="myModal-sequences__start__increment_" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>sequences (start, increment)</h2>
<p><!--START-CONTENT-sequences__start__increment_--></p>
<p><div class="category-content">CREATE SEQUENCE my_sequence<br> START WITH 100<br> INCREMENT BY 10;<br><br>START WITH 100 First number generated will be 100<br>INCREMENT BY 10 Each next number increases by 10: 100, 110, 120, …<br><br><br>INSERT INTO employees (emp_id, name)<br>VALUES (my_sequence.NEXTVAL, 'Alice');<br><br>SERIAL and GENERATED AS IDENTITY are also called sequences.<br><br>In PostgreSQL, the SERIAL type does not support START WITH or INCREMENT BY directly — those options are only available with GENERATED ... AS IDENTITY.<br><br>CREATE TABLE employees (<br> emp_id INT GENERATED ALWAYS AS IDENTITY<br> (START WITH 100 INCREMENT BY 10),<br> name VARCHAR(100)<br>);</div>
<!--CONTENT-sequences__start__increment_--></p>
</div>
</div>
<script>
setupModal("myModal-sequences__start__increment_", "openBtn-sequences__start__increment_");
</script>
</div>
<!-- END OF sequences (start, increment) -->
<!-- subqueries -->
<div class="grid-item">
<button class="tilt4" id="openBtn-subqueries">subqueries</button>
<div id="myModal-subqueries" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>subqueries</h2>
<p><!--START-CONTENT-subqueries--></p>
<p><div class="category-content">SELECT <br>MIN(average) AS min_average_area<br>FROM (<br> SELECT<br> AVG(area_in_sq_km) AS average<br> FROM countries <br> GROUP BY continent_code ) AS average_area<br><br>The alieses are mandatory in older versions. <br><br>Inner query → calculates average area per continent.<br>Outer query → finds the minimum among these averages.<br>Use of subquery is necessary because you need to aggregate twice:<br>First by continent (AVG)<br>Then across all continents (MIN)<br><br>>>><br><br>UPDATE monasteries<br>SET three_rivers = TRUE<br>WHERE country_code IN (<br> SELECT country_code AS CC<br> from countries<br> LEFT JOIN countries_rivers<br> USING (country_code)<br> LEFT JOIN rivers<br> ON rivers.id = countries_rivers.river_id<br> GROUP BY country_code<br> HAVING COUNT(rivers.river_name) >= 3)<br><br>The outer UPDATE checks monasteries.country_code against the subquery.<br><br>For any monastery in a country with 3+ rivers, three_rivers is set to TRUE<br><br>>>><br><br>UPDATE countries<br>SET three_rivers = (<br> SELECT COUNT(*) >= 3<br> FROM countries_rivers AS cr<br> WHERE cr.country_code = countries.country_code<br>);<br><br>For each row in countries, the subquery:<br> Counts how many rows exist in countries_rivers for that country.<br> Evaluates COUNT(*) >= 3 → returns true or false.<br> That boolean value is assigned to countries.three_rivers.<br> No grouping needed — it’s evaluated per row (correlated subquery).<br><br><br>three_rivers = FALSE -> Does not match null, only rows that are explicitly FALSE<br>three_rivers IS FALSE -> Does not match null, only rows that are explicitly FALSE<br>three_rivers IS NOT TRUE -> Does match null<br>NOT tree_rivers -> Does match null</div>
<div class="category-content">>><br><br>CREATE VIEW<br> continent_currency_usage<br>AS<br>SELECT<br> ra.continent_code,<br> ra.currency_code,<br> ra.currency_usage<br>FROM (<br> SELECT<br> ct.continent_code,<br> ct.currency_code,<br> ct.currency_usage,<br> DENSE_RANK() OVER (PARTITION BY ct.continent_code ORDER BY ct.currency_usage DESC) AS ranked_usage<br> FROM (<br> SELECT<br> continent_code,<br> currency_code,<br> COUNT(currency_code) AS currency_usage<br> FROM <br> countries<br> GROUP BY<br> continent_code,<br> currency_code<br> HAVING <br> COUNT(currency_code) > 1<br> ) AS ct<br>) AS ra<br>WHERE<br> ra.ranked_usage = 1<br>ORDER BY<br> ra.currency_usage DESC;</div>
<img src="cat_images/sub1.png" alt="subqueries" width="500" style="display:block; margin-right:auto;">
<img src="cat_images/sub2.png" alt="subqueries" width="500" style="display:block; margin-right:auto;">
<img src="cat_images/sub3.png" alt="subqueries" width="500" style="display:block; margin-right:auto;">
<!--CONTENT-subqueries--></p>
</div>
</div>
<script>
setupModal("myModal-subqueries", "openBtn-subqueries");
</script>
</div>
<!-- END OF subqueries -->
<!-- ROW_NUMBER(), RANK(), DENSE_RANK() -->
<div class="grid-item">
<button class="tilt2" id="openBtn-ROW_NUMBER____RANK____DENSE_RANK__">ROW_NUMBER(), RANK(), DENSE_RANK()</button>
<div id="myModal-ROW_NUMBER____RANK____DENSE_RANK__" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>ROW_NUMBER(), RANK(), DENSE_RANK()</h2>
<p><!--START-CONTENT-ROW_NUMBER____RANK____DENSE_RANK__--></p>
<p><div class="category-content">1ROW_NUMBER()<br>Assigns a unique sequential number to each row within a partition (or entire table if no partition).<br>No ties — each row gets a different number even if values are the same.<br><br>Copy code<br>SELECT <br> country, population,<br> ROW_NUMBER() OVER (ORDER BY population DESC) AS row_num<br>FROM countries;<br><br>country population row_num<br>A 1000000 1<br>B 800000 2<br>C 800000 3<br>D 500000 4<br><br><br>2️RANK()<br>Assigns a rank based on the ordering of a column.<br>Tied values get the same rank, but gaps appear after ties.<br><br>SELECT <br> country, population,<br> RANK() OVER (ORDER BY population DESC) AS rnk<br>FROM countries;<br><br>country population rnk<br>A 1000000 1<br>B 800000 2<br>C 800000 2<br>D 500000 4<br><br><br>3 DENSE_RANK()<br>Similar to RANK(), but no gaps after ties.<br>Tied values get the same rank, next distinct value gets the next consecutive rank.<br><br>SELECT <br> country, population,<br> DENSE_RANK() OVER (ORDER BY population DESC) AS dense_rnk<br>FROM countries;<br><br>country population dense_rnk<br>A 1000000 1<br>B 800000 2<br>C 800000 2<br>D 500000 3<br><br><br>ROW_NUMBER → when you need a unique identifier for each row.<br><br>RANK → when you want competition-style ranking (like 1st, 2nd, 2nd, 4th).<br><br>DENSE_RANK → when you want ranking without skipping numbers (1, 2, 2, 3).</div>
<img src="cat_images/dense_r1.png" alt="ROW_NUMBER(), RANK(), DENSE_RANK()" width="500" style="display:block; margin-right:auto;">
<!--CONTENT-ROW_NUMBER____RANK____DENSE_RANK__--></p>
</div>
</div>
<script>
setupModal("myModal-ROW_NUMBER____RANK____DENSE_RANK__", "openBtn-ROW_NUMBER____RANK____DENSE_RANK__");
</script>
</div>
<!-- END OF ROW_NUMBER(), RANK(), DENSE_RANK() -->
<!-- pg_indexes -->
<div class="grid-item">
<button class="tilt5" id="openBtn-pg_indexes">pg_indexes</button>
<div id="myModal-pg_indexes" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>pg_indexes</h2>
<p><!--START-CONTENT-pg_indexes--></p>
<p><div class="category-content">SELECT <br> tablename,<br> indexname,<br> indexdef<br>FROM <br> pg_indexes<br>WHERE <br> schemaname = 'public'<br>ORDER BY <br> tablename ASC,<br> indexname ASC;<br><br>pg_indexes is a PostgreSQL system view that stores metadata about all indexes in the database.<br>schemaname = 'public' filters only indexes belonging to the public schema.<br>ORDER BY tablename ASC, indexname ASC ensures the sorting order matches the requirement — first by table name, then by index name alphabetically.</div>
<!--CONTENT-pg_indexes--></p>
</div>
</div>
<script>
setupModal("myModal-pg_indexes", "openBtn-pg_indexes");
</script>
</div>
<!-- END OF pg_indexes -->
<!-- indexes -->
<div class="grid-item">
<button class="tilt3" id="openBtn-indexes">indexes</button>
<div id="myModal-indexes" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>indexes</h2>
<p><!--START-CONTENT-indexes--></p>
<p><div class="category-content">1 What an Index Is<br>An index is a database structure that speeds up data retrieval.<br><br>Common uses:<br> WHERE filters<br> ORDER BY<br> JOIN conditions<br> GROUP BY queries<br><br>2 Types of Indexes in PostgreSQL<br><br>a) B-tree Index (default)<br><br>Most commonly used.<br>Maintains sorted order of the indexed column.<br>Supports:<br>=, <, <=, >, >=<br><br>ORDER BY optimization<br>Default for PRIMARY KEY and UNIQUE.<br><br>b) Hash Index<br>Optimized for equality comparisons (=).<br><br>Faster than B-tree for equality, but:<br>Cannot be used for range queries (<, >)<br>Less commonly used in practice<br><br>c) GIN (Generalized Inverted Index)<br>Optimized for array, JSON, full-text search.<br>Great for containment queries, e.g., WHERE column @> '{value}'.<br><br>d) GiST (Generalized Search Tree)<br>Flexible, supports geometric data types and full-text search.<br>Can handle range, nearest neighbor, and other complex queries.<br><br>e) BRIN (Block Range Index)<br>Very small and fast for large, naturally ordered tables.<br>Stores summary info per block instead of per row.<br>Good for time-series or sequential data.<br><br>3 Clustered Index<br>PostgreSQL does not automatically cluster tables like SQL Server or MySQL.<br>A clustered index in PostgreSQL is actually a table physically reordered according to an index.<br><br>CLUSTER table_name USING index_name;<br><br>Reorders table rows on disk to match the index order.<br><br>Makes range queries and sequential scans faster.<br>Note: PostgreSQL does not maintain clustering automatically.<br>If you insert new rows later, they may not follow the clustered order.<br>You need to CLUSTER again manually if desired.<br><br>Key: In PostgreSQL, every index is technically separate from the table. Clustered indexes only affect physical storage order, not logical indexing.</div>
<div class="category-content">>><br>CREATE INDEX first_name_idx ON people (first_name)<br><br>Type: B-tree<br>Single-column: Indexes only first_name.<br>Column: first_name<br>Unique: No (unless you add UNIQUE)<br>Separate structure: The index is stored independently from the table data.<br>Not clustered: PostgreSQL does not automatically cluster tables. Rows in people are not reordered on disk unless you use CLUSTER.<br>Purpose: Speeds up equality and range queries (=, <, >, etc.), ORDER BY, JOIN, and GROUP BY operations.</div>
<img src="cat_images/indx1.png" alt="indexes" width="500" style="display:block; margin-right:auto;">
<img src="cat_images/indx2.png" alt="indexes" width="500" style="display:block; margin-right:auto;">
<!--CONTENT-indexes--></p>
</div>
</div>
<script>
setupModal("myModal-indexes", "openBtn-indexes");
</script>
</div>
<!-- END OF indexes -->
<!-- functions -->
<div class="grid-item">
<button class="tilt1" id="openBtn-functions">functions</button>
<div id="myModal-functions" class="modal">
<div class="modal-content">
<span class="close">×</span>
<h2>functions</h2>
<p><!--START-CONTENT-functions--></p>
<p><img src="cat_images/funcs1.png" alt="functions" width="500" style="display:block; margin-right:auto;">
<img src="cat_images/funcs2.png" alt="functions" width="500" style="display:block; margin-right:auto;">
<img src="cat_images/funcs3.png" alt="functions" width="500" style="display:block; margin-right:auto;">
<div class="category-content">>>><br> Three Main Types of Functions (by return type)<br><br>1. Scalar Function <br>Returns a single value (one number, text, date, etc.) <br>LENGTH('hello') → 5<br><br>2. Aggregate Function<br>Returns one value per group of rows <br>AVG(salary) or SUM(amount)<br><br>3. Table-Valued Function (Set-Returning Function) <br>Returns a set of rows (like a table) <br>generate_series(1,5) or custom RETURNS TABLE(...)</div>
<div class="category-content">>>><br>In PostgreSQL, a function declared with RETURNS void means:<br>It performs some work (like logging, inserting, updating) but doesn’t return any data.<br><br>CREATE OR REPLACE FUNCTION log_employee_action(emp_id INT, action TEXT)<br>RETURNS void<br>AS $$<br>BEGIN<br> INSERT INTO employee_log(employee_id, action, action_time)<br> VALUES (emp_id, action, NOW());<br>END;<br>$$ LANGUAGE plpgsql;<br><br>You would call it like this:<br>SELECT log_employee_action(101, 'Promoted');<br><br>or inside a trigger:<br>PERFORM log_employee_action(OLD.id, 'Deleted');</div>
<!--CONTENT-functions--></p>
</div>