SQL-DDL-DML-Learning/Shop.sql at main · YousifHisham-tech/SQL-DDL-DML-Learning · 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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
use master
go

create database Customer_Service
go

use Customer_Service
go
--dropping group--
drop table Customers;
drop table Order_Product;
drop table Products;
drop table Cashier;
drop database Customer_Service
--creating table called products--
create table Products(
Product_ID int primary key identity (1,1),
Product_Name nchar(100),
Price_of_this_product int);
--selection group--
select * from Products;
select * from Customers;
select * from Order_Product;
select * from Products union select * from Customers
--creating table called customers--
create table Customers(
Customer_ID int primary key identity (1,1),
Customer_Name nchar(100),
Telephone_Number int);
--inserting customers--
insert into Customers (Customer_Name,Telephone_Number)
values ('Yousif_Hisham_AbdElFatah','01023337817')

insert into Customers (Customer_Name,Telephone_Number)
values ('NourELDin_Hisham','01120585108')

insert into Customers (Customer_Name,Telephone_Number)
values ('Malek_Hisham','0')

insert into Customers (Customer_Name,Telephone_Number)
values ('Fatma_Mohamed','01023337817')

insert into Customers (Customer_Name,Telephone_Number)
values ('Assmaa_Mohamed','01028497598')

insert into Customers (Customer_Name,Telephone_Number)
values ('AbdElMaksood_Mohamed','01115846978')

insert into Customers (Customer_Name,Telephone_Number)
values ('Hager_Mohamed','01148507654')

insert into Customers (Customer_Name,Telephone_Number)
values ('Ahmed_Salem','01023849685')

insert into Customers (Customer_Name,Telephone_Number)
values ('Hisham_AbdElFatah','01159754198')

insert into Customers (Customer_Name,Telephone_Number)
values ('Mohamed_Hisham','01020854890')

insert into Customers (Customer_Name,Telephone_Number)
values ('Hisham_Sobbhi','01028954676')

insert into Customers (Customer_Name,Telephone_Number)
values ('Ahmed_Yehia_ElKahhki','01154956525')

insert into Customers (Customer_Name,Telephone_Number)
values ('AbdElRahman_AlaaElDin','01025257585')

insert into Customers (Customer_Name,Telephone_Number)
values ('Adam_AlaaElDin','01156958522')

insert into Customers (Customer_Name,Telephone_Number)
values ('Mostafa_Dahi','01250418942')

insert into Customers (Customer_Name,Telephone_Number)
values ('Nermin_AbdElRaoof','01252417485')

insert into Customers (Customer_Name,Telephone_Number)
values ('Rami_Hamdi','01294845465')

insert into Customers (Customer_Name,Telephone_Number)
values ('Amal_Maher','01025874136')
--creating table called order product--
create table Order_Product(
Order_Product_ID int primary key identity (1,1),
Customer_ID int,
Product_ID int,
Quantity_Product int,
Price_of_products int,
Foreign key (Customer_ID) references Customers(Customer_ID),
Foreign key (Product_ID) references Products(Product_ID));
--inserting orders--
insert into Order_Product (Customer_ID,Product_ID,Quantity_Product,Price_of_products)
values ('1','1','2','50')

insert into Order_Product (Customer_ID,Product_ID,Quantity_Product,Price_of_products)
values ('1','2','2','240')

insert into Order_Product (Customer_ID,Product_ID,Quantity_Product,Price_of_products)
values ('1','10','2','14')
--Getting values in an easy way--
select * from Customers
join Order_Product
on Customers.Customer_ID = Order_Product.Customer_ID
--creating table called cashier--
create table Cashier(
Customer_ID int,
Cashier_turn int primary key identity (1,1),
Order_Product_ID int,
Total_cash int,
Foreign key (Customer_ID) references Customers(Customer_ID),
Foreign key (Order_Product_ID) references Order_Product(Order_Product_ID));
--inserting products--
insert into Products (Product_Name,Price_of_this_product)
values ('Litre_Milk','25')

insert into Products (Product_Name,Price_of_this_product)
values ('1Kg_Cheese_natural','120')

insert into Products (Product_Name,Price_of_this_product)
values ('1Kg_Roomi','150')

insert into Products (Product_Name,Price_of_this_product)
values ('Pepsi_Litre','15')

insert into Products (Product_Name,Price_of_this_product)
values ('Pepsi_2_Litre','22')

insert into Products (Product_Name,Price_of_this_product)
values ('Pepsi_Cans','7')

insert into Products (Product_Name,Price_of_this_product)
values ('Sprite_litre','17')

insert into Products (Product_Name,Price_of_this_product)
values ('Colacola_1.5L','20')

insert into Products (Product_Name,Price_of_this_product)
values ('Hohos','5')

insert into Products (Product_Name,Price_of_this_product)
values ('Ice_cream_friday','7')

insert into Products (Product_Name,Price_of_this_product)
values ('Twinkes','5')

insert into Products (Product_Name,Price_of_this_product)
values ('Kit_Kat','10')

insert into Products (Product_Name,Price_of_this_product)
values ('Nesquiq_1Kg','30')

insert into Products (Product_Name,Price_of_this_product)
values ('Nestlant_1L','7')

insert into Products (Product_Name,Price_of_this_product)
values ('Dasani_1L','7')

insert into Products (Product_Name,Price_of_this_product)
values ('Aquafina_1L','7')

insert into Products (Product_Name,Price_of_this_product)
values ('Obourland_Chesse_0.5Kg','45')

insert into Products (Product_Name,Price_of_this_product)
values ('Obourland_Chesse_0.25Kg','25')