LEARNING_SQL/NS_DAY3_Notes.sql at main · Kalyan4636/LEARNING_SQL · GitHub

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
select top 5*
from orders
order by order_date desc
;
--to get distinct value of a column
select distinct order_date from orders
order by order_date;

select distinct ship_mode,segment from orders;

select distinct * from orders;
---filters-----
select * from orders
where ship_mode='First Class';

select * from orders
where order_date='2020-12-08';

select order_date,quantity
from orders
where quantity != 5
order by quantity desc;


select * from orders
where order_date < '2020-12-08'
order by order_date desc
;


select * from orders
where order_date between '2020-12-08' and '2020-12-12'
order by order_date desc

select * from orders
where quantity between 3 and 5
order by quantity desc;

select distinct ship_mode from orders
where ship_mode in ('First Class','Same Day');


select * from orders
where quantity in (3, 5 , 4)
order by quantity desc;

select distinct ship_mode from orders
where ship_mode not in ('First Class','Same Day');


select distinct ship_mode from orders
where ship_mode >= 'Second Class'

select order_date, ship_mode,segment from orders
where ship_mode = 'First Class' and segment='Consumer'


select order_date, ship_mode,segment from orders
where ship_mode = 'First Class' or segment='Consumer'

select distinct ship_mode from orders
where ship_mode in ('First Class','Same Day');

select * from orders
where ship_mode = 'First Class' or ship_mode='Same Day'; --or filter always increase rows

select * from orders where quantity>5 and order_date<'2020-11-08' -- and will decrease;

select *,profit/sales as ratio,profit*sales,getdate() as total
from orders
where order_date between  '2022-11-01 12:00:00' and  '2022-11-01 12:40:00'
order by order_date
;
--pattern matching like operator
select order_id,order_date,customer_name
from orders
where customer_name like 'Chris%';

select order_id,order_date,customer_name
from orders
where customer_name like '%t';

select order_id,order_date,customer_name
from orders
where customer_name like '%ven%';


select order_id,order_date,customer_name,upper(customer_name) as name_upper
from orders
where upper(customer_name) like 'A%A' ;


select order_id,order_date,customer_name
from orders
where customer_name like '_l%' ;
--%--0 or more any characters
-- _ - > one character

select order_id,order_date,customer_name
from orders
where customer_name like 'C[albo]%'


select order_id,order_date,customer_name
from orders
where customer_name like 'C[^albo]%';


select order_id,order_date,customer_name
from orders
where order_id like 'CA-20[][1-2]%'
order by customer_name