-
Notifications
You must be signed in to change notification settings - Fork 1
/
Presentation.sql
128 lines (98 loc) · 3.08 KB
/
Presentation.sql
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
/*
SQL Basics
- How to get data from tables
- Select statements
- FROM clause
- WHERE clause
Common symbols used in WHERE statements include:
> (greater than)
< (less than)
>= (greater than or equal to)
<= (less than or equal to)
= (equal to)
!= (not equal to)
- LIKE operator
- IN operator
- NOT LIKE
- NOT IN
- AND / BETWEEN
- OR
*/
/*
SQL JOINs
- A way of combining two tables information
- JOIN ON clause
- Multiple filters with JOIN clause
JOIN region r
ON r.id = s.region_id
AND r.name = 'Midwest'
AND s.name LIKE '% K%'
-- SELECT DISTINCT
*/
/*
SQL AGREGATIONS
- Performing an operation on multiple rows in the table
- AGREGATION FUNCTIONS
- SUM
- MIN
- MAX
- AVG
- COUNT
- GROUP BY clause (A way of segmenting data based on columns provided)
- WORKING WITH DATES
- DATE_TRUNC()
- DATE_PART()
- CASE WHEN
*/
/*
SQL SUB_QUERIES AND TEMP TABLES
- WITH AS table () - FOR TEMP TABLES
*/
/*
SQL DATA_CLEANING
- LEFT()
- RIGHT()
- LENGTH()
- POSITION()
- STRPOS()
- SUBSTRING() | SUBSTR()
- REPLACE()
- LOWER()
- UPPER()
- CONCAT()
- CAST()
- COLASCE()
*/
/*
SQL WINDOWS FUNCTION
- OVER: is the main clause for the windows function
- PARTITION BY: tells how the data is to be partitioned or grouped, if not specified, it treates all the rows as belonging to one group
- ORDER BY: This orders the table by the specified column and also performs the specified aggregation function on all the data above a
specific row. If not specified, then it performs the aggregation function for the whole rows in the partition.
- ROW_NUMBER()
- RANK()
- DENSE_RANK()
- LAG() AND LEAD() - USEFUL FOR ROW COMPARISION
- NTILE()
- -- A valid windows function syntax.
-- You can specify the windows required by aliasing. Goes between the WHERE clause and the GROUP BY clause.
-- You can only use windows function in SELECT clause and ORDER BY clause and no where else.
SELECT account_id, COUNT(account_id) OVER w
FROM orders
WINDOW w AS (PARTITION BY account_id ORDER BY id),
w2 AS (PARTITION BY account_id)
ORDER BY COUNT(account_id) OVER w DESC, 1
*/
/*
SQL ADVANCED JOINS AND PERFORMANCE TUNING
- FULL JOIN
- LEFT JOIN
- RIGHT JOIN
- CROSS JOIN
- WEBSITE WITH DIAGRAM OF JOIN
- UNION () - NO DUPLICATES IN BOTH TABLES
- UNION ALL() - STACKS EVERYTHING
- PERFOMANCE TUNING
- PERFORM AGREGATION BEFORE JOINING
- LIMIT YOUR DATA WHEN TESTING, THEN BRING IN EVERYTHING AFTERWARDS
*/