-
Notifications
You must be signed in to change notification settings - Fork 14
/
Copy pathCSM Schema.sql
231 lines (179 loc) · 8.59 KB
/
CSM Schema.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
Create database CSM
USE CSM
CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID VARCHAR(15) PRIMARY KEY,
EMPLOYEE_NAME VARCHAR(25) NOT NULL,
EMPLOYEE_PASSWORD CHAR(8) NOT NULL,
EMPLOYEE_CONTACT CHAR(11) NOT NULL,
EMPLOYEE_ADDRESS VARCHAR(50) NOT NULL,
EMPLOYEE_EMAIL VARCHAR(25) UNIQUE NOT NULL,
EMPLOYEE_DESIGNATION VARCHAR(15) NOT NULL,
EMPLOYEE_HIREDATE DATE NOT NULL,
EMPLOYEE_FIREDATE DATE,
EMPLOYEE_STATUS VARCHAR(10) NOT NULL,
EMPLOYEE_SALES INT,
)
CREATE TABLE MANUFACTURER
(
MANUFACTURER_ID VARCHAR(15) PRIMARY KEY,
MANUFACTURER_NAME VARCHAR(25) NOT NULL,
MANUFACTURER_EMAIL VARCHAR(25) UNIQUE NOT NULL,
MANUFACTURER_ADDRESS VARCHAR(50) NOT NULL,
MANUFACTURER_CONTACT CHAR(11) NOT NULL,
)
CREATE TABLE CUSTOMER
(
CUSTOMER_CNIC CHAR(13) PRIMARY KEY,
CUSTOMER_NAME VARCHAR(25) NOT NULL,
CUSTOMER_CONTACT CHAR(13) NOT NULL,
CUSTOMER_ADDRESS VARCHAR(50) NOT NULL,
)
Create Table CAR
(
CAR_ID VARCHAR(15) PRIMARY KEY,
CAR_NAME VARCHAR(20) NOT NULL,
CAR_MODEL CHAR(4) NOT NULL,
CAR_COMPANY VARCHAR(15) NOT NULL,
CAR_STATUS VARCHAR(10) NOT NULL,
CAR_PRICE INT NOT NULL,
)
CREATE TABLE MANUF_ORDER
(
ORDER_ID VARCHAR(15) PRIMARY KEY,
EMPLOYEE_ID VARCHAR(15) FOREIGN KEY REFERENCES EMPLOYEE(EMPLOYEE_ID),
CAR_ID VARCHAR(15) FOREIGN KEY REFERENCES CAR(CAR_ID),
MANUFACTURER_ID VARCHAR(15) FOREIGN KEY REFERENCES MANUFACTURER(MANUFACTURER_ID),
ORDER_DATE DATE NOT NULL,
BILL INT NOT NULL,
)
CREATE TABLE STOCK
(
ORDER_ID VARCHAR(15) FOREIGN KEY REFERENCES MANUF_ORDER(ORDER_ID),
CAR_ID VARCHAR(15) FOREIGN KEY REFERENCES CAR(CAR_ID),
REC_DATE DATE NOT NULL
)
CREATE TABLE CUSTOMER_ORDER
(
ORDER_ID VARCHAR(15) PRIMARY KEY,
EMPLOYEE_ID VARCHAR(15) FOREIGN KEY REFERENCES EMPLOYEE(EMPLOYEE_ID),
CAR_ID VARCHAR(15) FOREIGN KEY REFERENCES CAR(CAR_ID),
CUSTOMER_CNIC CHAR(13) FOREIGN KEY REFERENCES CUSTOMER(CUSTOMER_CNIC),
ORDER_DATE DATE NOT NULL,
BILL INT NOT NULL,
)
CREATE TABLE STOCK_PAYMENT
(
ORDER_ID VARCHAR(15) FOREIGN KEY REFERENCES MANUF_ORDER(ORDER_ID),
PAYMENT_DATE DATE NOT NULL,
)
CREATE TABLE SELL_PAYMENT
(
ORDER_ID VARCHAR(15) FOREIGN KEY REFERENCES CUSTOMER_ORDER(ORDER_ID),
PAYMENT_DATE DATE NOT NULL,
)
CREATE TABLE ACCOUNT
(
MANF_ORDER VARCHAR(15) FOREIGN KEY REFERENCES MANUF_ORDER(ORDER_ID),
CUST_ORDER VARCHAR(15) FOREIGN KEY REFERENCES CUSTOMER_ORDER(ORDER_ID),
AMOUNT INT,
IS_PAID varchar(5) NOT NULL,
PAYMENT_DATE DATE NOT NULL,
)
Drop table account
INSERT INTO EMPLOYEE(EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_PASSWORD,EMPLOYEE_CONTACT,EMPLOYEE_ADDRESS,EMPLOYEE_EMAIL,EMPLOYEE_DESIGNATION,EMPLOYEE_HIREDATE,EMPLOYEE_STATUS,EMPLOYEE_SALES)
VALUES('SM123','Faisal','999480','03137721207','Hafizabad','faisal@gmail.com','Salesman',CONVERT(DATE, GETDATE()),'Working',0)
INSERT INTO EMPLOYEE(EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_PASSWORD,EMPLOYEE_CONTACT,EMPLOYEE_ADDRESS,EMPLOYEE_EMAIL,EMPLOYEE_DESIGNATION,EMPLOYEE_HIREDATE,EMPLOYEE_STATUS,EMPLOYEE_SALES)
VALUES('MG999','Ahmad','999480','03076821561','Lahore','ahmad@gmail.com','Manager',CONVERT(DATE, GETDATE()),'Working',0)
INSERT INTO CAR(CAR_ID,CAR_NAME,CAR_MODEL,CAR_COMPANY,CAR_STATUS,CAR_PRICE)
VALUES('C1356','Model X','2020','Tesla','Available',2500000)
INSERT INTO CAR(CAR_ID,CAR_NAME,CAR_MODEL,CAR_COMPANY,CAR_STATUS,CAR_PRICE)
VALUES('C1245','Z4','2019','BMW','Available',4500000)
INSERT INTO CUSTOMER(CUSTOMER_CNIC,CUSTOMER_NAME,CUSTOMER_CONTACT,CUSTOMER_ADDRESS)
VALUES('1273648292039','FAISAL','12345543213','LAHORE')
INSERT INTO EMPLOYEE(EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_PASSWORD,EMPLOYEE_CONTACT,EMPLOYEE_ADDRESS,EMPLOYEE_EMAIL,EMPLOYEE_DESIGNATION,EMPLOYEE_HIREDATE,EMPLOYEE_STATUS,EMPLOYEE_SALES)
VALUES('MG999','Ahmad','999480','03076821561','Lahore','ahmad@gmail.com','Manager',CONVERT(DATE, GETDATE()),'Working',0)
INSERT INTO CAR(CAR_ID,CAR_NAME,CAR_MODEL,CAR_COMPANY,CAR_STATUS,CAR_PRICE)
VALUES('C3241','GLB','2020','Mercedes','Available',3500000)
INSERT INTO MANUFACTURER(MANUFACTURER_ID,MANUFACTURER_NAME,MANUFACTURER_CONTACT,MANUFACTURER_EMAIL,MANUFACTURER_ADDRESS)
VALUES('MF123','MERCEDES','23456789087','nkwjdnck@gmsil.com','London')
INSERT INTO MANUF_ORDER(ORDER_ID,EMPLOYEE_ID,CAR_ID,MANUFACTURER_ID,ORDER_DATE,BILL)
Values('MOD123','MG999','C3241','MF123',GETDATE(),3555000)
INSERT INTO STOCK_PAYMENT(ORDER_ID,PAYMENT_DATE)
VALUES('MOD123',GETDATE())
INSERT INTO Stock(Order_ID,REC_DATE)
VALUES('MOD123',GETDATE())
Insert into Account(MANF_Order,AMOUNT,IS_PAID,PAYMENT_DATE)
Values('MOD123',3500000,'TRUE',GETDATE())
INSERT INTO CUSTOMER_ORDER(ORDER_ID,EMPLOYEE_ID,CAR_ID,CUSTOMER_CNIC,ORDER_DATE,BILL)
Values('COD123','MG999','C1245','1273648292039',GETDATE(),3355000)
Insert into Account(Cust_Order,AMOUNT_RECEIVED,IS_PAID,PAYMENT_DATE) Values(@order,@amount,'FALSE',GETDATE())
SELECT *FROM CUSTOMER
SELECT *FROM EMPLOYEE
SELECT *FROM CAR
SELECT *FROM MANUFACTURER
SELECT *FROM STOCK
SELECT *FROM CUSTOMER_ORDER
SELECT *FROM MANUF_ORDER
SELECT *FROM STOCK_PAYMENT
SELECT *FROM SELL_PAYMENT
SELECT*FROM ACCOUNT
DELETE FROM ACCOUNT
DELETE FROM SELL_PAYMENT
DELETE FROM STOCK_PAYMENT
DELETE FROM STOCK
DELETE FROM CUSTOMER_ORDER
DELETE FROM MANUF_ORDER
DELETE FROM SELL_PAYMENT
DELETE FROM MANUFACTURER
DELETE FROM CAR
DELETE FROM CUSTOMER
DELETE FROM EMPLOYEE
SELECT CONVERT(DATE,GETDATE())
SELECT MAX(SUBSTRING(CUSTOMER_ORDER.ORDER_ID,4,LEN(CUSTOMER_ORDER.ORDER_ID))) FROM CUSTOMER_ORDER
SELECT MAX(SUBSTRING(EMPLOYEE.EMPLOYEE_ID,3,LEN(EMPLOYEE.EMPLOYEE_ID))) FROM EMPLOYEE WHERE EMPLOYEE_DESIGNATION = 'SALESMAN'
DELETE FROM EMPLOYEE WHERE EMPLOYEE_DESIGNATION = 'SALESMAN'
UPDATE EMPLOYEE SET EMPLOYEE_STATUS = 'Working' WHERE EMPLOYEE_ID = 'MG999'
Select SELL_PAYMENT.ORDER_ID, CAR.CAR_NAME, EMPLOYEE.EMPLOYEE_NAME, CUSTOMER.CUSTOMER_NAME,CUSTOMER_ORDER.BILL, SELL_PAYMENT.PAYMENT_DATE
from SELL_PAYMENT
inner join CUSTOMER_ORDER on SELL_PAYMENT.ORDER_ID = CUSTOMER_ORDER.ORDER_ID
inner join CAR on CUSTOMER_ORDER.CAR_ID = CAR.CAR_ID
inner join EMPLOYEE on CUSTOMER_ORDER.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID
inner join CUSTOMER on CUSTOMER_ORDER.CUSTOMER_CNIC = CUSTOMER.CUSTOMER_CNIC
Select CAR.CAR_NAME,CAR.CAR_ID,CAR.CAR_MODEL,CAR.CAR_COMPANY
FROM MANUF_ORDER
INNER JOIN STOCK_PAYMENT ON MANUF_ORDER.ORDER_ID = STOCK_PAYMENT.ORDER_ID
INNER JOIN CAR ON MANUF_ORDER.CAR_ID = CAR.CAR_ID
WHERE STOCK_PAYMENT.ORDER_ID = 'MOD123'
Select CAR.CAR_NAME, CAR.CAR_ID, CAR.CAR_MODEL, CAR.CAR_COMPANY FROM CUSTOMER_ORDER INNER JOIN SELL_PAYMENT
ON SELL_PAYMENT.ORDER_ID = CUSTOMER_ORDER.ORDER_ID
INNER JOIN CAR ON CUSTOMER_ORDER.CAR_ID = CAR.CAR_ID
WHERE CUSTOMER_ORDER.ORDER_ID = 'COD0001'
SELECT CUSTOMER.CUSTOMER_NAME, CUSTOMER.CUSTOMER_CNIC,CUSTOMER.CUSTOMER_CONTACT,CUSTOMER.CUSTOMER_ADDRESS
FROM SELL_PAYMENT
INNER JOIN CUSTOMER_ORDER ON SELL_PAYMENT.ORDER_ID = CUSTOMER_ORDER.ORDER_ID
INNER JOIN CUSTOMER ON CUSTOMER_ORDER.CUSTOMER_CNIC = CUSTOMER.CUSTOMER_CNIC
WHERE SELL_PAYMENT.ORDER_ID = 'COD0001'
SELECT MANUFACTURER.MANUFACTURER_NAME, MANUFACTURER.MANUFACTURER_ID, MANUFACTURER.MANUFACTURER_CONTACT , MANUFACTURER.MANUFACTURER_ADDRESS
FROM STOCK_PAYMENT
INNER JOIN MANUF_ORDER ON STOCK_PAYMENT.ORDER_ID = MANUF_ORDER.ORDER_ID
INNER JOIN MANUFACTURER ON MANUF_ORDER.MANUFACTURER_ID = MANUFACTURER.MANUFACTURER_ID
WHERE STOCK_PAYMENT.ORDER_ID = 'MOD123'
Select EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.EMPLOYEE_NAME, EMPLOYEE.EMPLOYEE_CONTACT, EMPLOYEE.EMPLOYEE_DESIGNATION
FROM SELL_PAYMENT
INNER JOIN CUSTOMER_ORDER ON SELL_PAYMENT.ORDER_ID = CUSTOMER_ORDER.ORDER_ID
INNER JOIN EMPLOYEE ON CUSTOMER_ORDER.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID
WHERE SELL_PAYMENT.ORDER_ID = 'COD0001'
SELECT MANUF_ORDER.ORDER_ID, MANUF_ORDER.BILL, MANUF_ORDER.ORDER_DATE
FROM MANUF_ORDER
INNER JOIN STOCK_PAYMENT ON STOCK_PAYMENT.ORDER_ID = MANUF_ORDER.ORDER_ID
WHERE STOCK_PAYMENT.ORDER_ID = 'MOD123'
Select ((sum(ACCOUNT.Amount) where ACCOUNT.IS_PAID = 'FALSE') - ( SELECT SUM(ACCOUNT.AMOUNT) WHERE ACCOUNT.IS_PAID = 'TRUE')) from account
SELECT SUM(ACCOUNT.AMOUNT) AS Spent FROM ACCOUNT WHERE ACCOUNT.IS_PAID = 'TRUE'
SELECT SUM(ACCOUNT.AMOUNT) AS Recieved FROM ACCOUNT WHERE ACCOUNT.IS_PAID = 'FALSE'
SELECT (SELECT SUM(ACCOUNT.AMOUNT) FROM ACCOUNT WHERE ACCOUNT.IS_PAID = 'TRUE') - (SELECT SUM(ACCOUNT.AMOUNT) FROM ACCOUNT WHERE ACCOUNT.IS_PAID = 'FALSE') AS 'DIFF'
Select EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.EMPLOYEE_NAME, EMPLOYEE.EMPLOYEE_CONTACT, EMPLOYEE.EMPLOYEE_DESIGNATION
FROM SELL_PAYMENT
INNER JOIN CUSTOMER_ORDER ON SELL_PAYMENT.ORDER_ID = CUSTOMER_ORDER.ORDER_ID
INNER JOIN EMPLOYEE ON CUSTOMER_ORDER.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID
WHERE SELL_PAYMENT.ORDER_ID = 'COD0001'