-
Notifications
You must be signed in to change notification settings - Fork 0
/
shoppingmall.sql
246 lines (164 loc) · 6.42 KB
/
shoppingmall.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
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
use ShoppingMall
--Create Table [dbo].[Products]
--(
--[Id] INT Identity (1,1) Not Null,
--[ProductName] nVarchar(20) Not Null,
--[Unitprice] money Not Null,
--[UnitInStock] int Not Null
--);
Alter Table [Products]
add[CategoryId] int not null
Alter Table [Products]
add[StoreId] int not null
Create Table [dbo].[Categories]
(
[Id] INT Identity (1,1) Not Null,
[CategoryName] nVarchar(30) Not Null
);
Create Table [dbo].[Customers]
(
[Id] INT Identity (1,1) Not Null,
[FirstName] nVarchar(30) Not Null,
[LastName] nVarchar(30) Not Null,
[Phone] nVarchar(30) Not Null
);
--Create Table [dbo].[Stores]
--(
--[Id] INT Identity (1,1) Not Null,
--[StoreName] nVarchar(30) Not Null,
--[ContactName] nVarchar(30) Not Null,
--[Phone] nVarchar(30) Not Null,
--[Adress] nVarchar(30) Not Null
--);
Alter Table [Stores]
add[ProductId] int not null
Alter Table Stores
drop column [ProductId]
Create Table [dbo].[Orders]
(
[Id] INT Identity (1,1) Not Null,
[Quantity] int Not Null
);
Create Table AssignedorderToCustomers(
[Id] int identity(1,1) not null,
[OrderId] int not null,
[CustomerId] int not null ,
[StoreId] int not null
);
use ShoppingMall --We will use sql dataase
select * from Products --You can find all value
select StoreName,ContactName from Stores
select StoreName,
ContactName,
Phone
from Stores
where storeýd= 1 --"WHERE" does the filtering
select
ProductName,
UnitPrice,
from Products
where UnitsInStock > 100
and UnitsInStock < 200 --If you have 2 or more value you can use "AND "
select Id, ContactName, Phone, Adress
from Stores
where Id= 2
or ID = 5 -- If you have 2 or more option . you can use "OR"
select FirstName+' '+LastName as [Full namer], YEAR( GETDATE())- YEAR( BirthDate)) as Age from Customers where DATEDIFF( YEAR, BirthDate, GETDATE())>60
--NULL
select * from Customers where Phone is null
--Not null
select * from Customers where Phone is not null
select * from Employees where EmployeeID >2 order by FirstName ASC
select +FirstName+' '+LastName as [Full namer],
BirthDate,
TitleOfCourtesy
HireDate
from Employees order by 3 desc -- Ýf you want to arrangement You can use "order by"
select FirstName+' '+LastName as Name ,
from Customers where YEAR( BirthDate) between 1992 and 1990 order by 1 desc -- if you need range you can use "Between And"
select +FirstName+' '+LastName as [Fullname ]from Customers
where YEAR(Birthday) In(1990,1994,1992.) -- If you have 2 or more option . you can use "OR"
select Top 2 FirstName, LastName, DATEDIFF(YY, BirthDate, GetDate()) as Age
from Customers
order by age desc -- you need best or worst value , you can use "TOP "
--Like--
select FirstName, LastName
from Customers
where FirstName Like 'A%' -- you can find the word that starts with the letter 'A'
select FirstName, LastName
from Customers
where FirstName Like '%N' -- you can find the word that stops with the letter 'N'
select FirstName, LastName
from Customers
where FirstName Like '%E%' -- you can find the word that anywhere with the letter 'N'
select FirstName, LastName
from Customers
where FirstName Like '[AL]%' --you can find the word that starts with the letter 'A' or 'L'
select FirstName, LastName
from Customers
where FirstName Like '%[RN]%' you can find the word that anywhere with the letter 'N' or 'R'
select FirstName, LastName
from Customers
where FirstName Like '%[^T]' -- you can find the word that not stops with the letter 'T'
-- STRÝNG FUNCTÝON
select ASCII('A') as [ASCII CODE ] -- You can find numberic value
select CHAR(65) as letter -- you can find word value
select CHARINDEX('@', '[email protected]') as Location -- You can find location of first parameter
select LEFT('Songul soyturk ', 4) as [number of characters from the left]
select Right('songulsoyturk', 4) as [number of characters from the right]
select Len('songul soyturk ') as [number of characters]
select lower('SONGUL SOYTURK') as [all small ]
select upper('songul soyturk') as [all big ]
select LTRIM(' songul sytrk') as [deletes spaces on the left ]
select RTRIM('songul soyturk ') as [deletes spaces on the right ]
select LTRIM( RTRIM(' soongul ' )) as [deletes spaces on the everywhere ]
select REPLACE('birbir','bir','iki') as [Replaces texts with new ones]
select SUBSTRING('songul soyturk',3,6) as [Subtitle ]
select REPLICATE('Bilge',5) --Repeats the specified text as many times as the value in the 2nd parameter
--Aggrate functions
select COUNT(*) from Products -- it can find number of lines
select SUM(UnitPrice) as [Total price ] from Products -- Sum() : sums all numbers
select AVG(UnitPrice) from Products -- it can be find average
select MIN(UnitPrice) as [ cheapest] from Products
select Max(UnitPrice) as [most expensive] from Products
-- GROUP BY
select ProductName, count(*) as [UnýInstock ]
from Products
where UnitInStock < 100
group by ProductName -- you can group the same
--HAVÝNG --
select CategoryID,sum(UnitsInStock) as [stok miktarý toplam ]
from Products
group by CategoryID
having sum(UnitsInStock) <400 -- "Having" does the filtering for group
order by 2 desc
--DML(Data Manipulating Language) : can made update, delete , insert
--insert:
insert Products(ProductName, UnitInStock) values ('shoes', '100')
--Update
update Products set Phone ='2222222' where ID =4
--Delete
delete FROM Products WHERE ID=7
select * from Products
--Join=inner join -----
select c.CategoryName,p.ProductName
from Categories C
join Products P on c.ID=p.ID -- If you will use 2 table you can use join . You should secondary key
select
C.CategoryName,
S.CompanyName ,
C.FirstName+''+C.LastName as [customername ],
P.ProductName,
P.UnitPrice,
P.Quantity,
from Categories C
join Products P on P.CategoryID=c.ID
join Stores S on P.StoreId=S.ID
order by 1 asc
select p.ProductName ,
CategoryId.OrderID,
P.Quantity
P.UnitPrice
from Products P
left join categories D on p.categoryID=C.ID
order by o.OrderID