-
-
Notifications
You must be signed in to change notification settings - Fork 624
Expand file tree
/
Copy path04_Filtering_Data.sql
More file actions
121 lines (98 loc) · 3.49 KB
/
Copy path04_Filtering_Data.sql
File metadata and controls
121 lines (98 loc) · 3.49 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
/* ==============================================================================
SQL Filtering Data
-------------------------------------------------------------------------------
This document provides an overview of SQL filtering techniques using WHERE
and various operators for precise data retrieval.
Table of Contents:
1. Comparison Operators
- =, <>, >, >=, <, <=
2. Logical Operators
- AND, OR, NOT
3. Range Filtering
- BETWEEN
4. Set Filtering
- IN
5. Pattern Matching
- LIKE
=================================================================================
*/
/* ==============================================================================
COMPARISON OPERATORS
=============================================================================== */
-- Retrieve all customers from Germany.
SELECT *
FROM customers
WHERE country = 'Germany'
-- Retrieve all customers who are not from Germany.
SELECT *
FROM customers
WHERE country <> 'Germany'
-- Retrieve all customers with a score greater than 500.
SELECT *
FROM customers
WHERE score > 500
-- Retrieve all customers with a score of 500 or more.
SELECT *
FROM customers
WHERE score >= 500
-- Retrieve all customers with a score less than 500.
SELECT *
FROM customers
WHERE score < 500
-- Retrieve all customers with a score of 500 or less.
SELECT *
FROM customers
WHERE score <= 500
/* ==============================================================================
LOGICAL OPERATORS
=============================================================================== */
/* Combining conditions using AND, OR, and NOT */
-- Retrieve all customers who are from the USA and have a score greater than 500.
SELECT *
FROM customers
WHERE country = 'USA' AND score > 500
-- Retrieve all customers who are either from the USA or have a score greater than 500.
SELECT *
FROM customers
WHERE country = 'USA' OR score > 500
-- Retrieve all customers with a score not less than 500.
SELECT *
FROM customers
WHERE NOT score < 500
/* ==============================================================================
RANGE FILTERING - BETWEEN
=============================================================================== */
-- Retrieve all customers whose score falls in the range between 100 and 500.
SELECT *
FROM customers
WHERE score BETWEEN 100 AND 500
-- Alternative method (Equivalent to BETWEEN)
SELECT *
FROM customers
WHERE score >= 100 AND score <= 500
/* ==============================================================================
SET FILTERING - IN
=============================================================================== */
-- Retrieve all customers from either Germany or the USA.
SELECT *
FROM customers
WHERE country IN ('Germany', 'USA')
/* ==============================================================================
PATTERN MATCHING - LIKE
=============================================================================== */
-- Find all customers whose first name starts with 'M'.
SELECT *
FROM customers
WHERE first_name LIKE 'M%'
-- Find all customers whose first name ends with 'n'.
SELECT *
FROM customers
WHERE first_name LIKE '%n'
-- Find all customers whose first name contains 'r'.
SELECT *
FROM customers
WHERE first_name LIKE '%r%'
-- Find all customers whose first name has 'r' in the third position.
SELECT *
FROM customers
WHERE first_name LIKE '__r%'