-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathDataproject.sql
110 lines (102 loc) · 7.02 KB
/
Dataproject.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
-- RENTING_EMP (EMP_ID, NAME, ADDRESS, EMP_PNUM, DATE_OF_BIRTH, START_DATE,
-- BASE_SALARY, COMMISSION_RATE, YEARLY_SALES);
-- CUSTOMERS (CUS_NAME, ADDRESS, CUS_ID, BUDGET, CARRIER, CUS_PNUM, EMP_ID);
-- VEHICLES (CAR_ID, OPTIONS, PRICE_WIHTOUT_CUSTOMS, TRANSMISSION_TYPE, NUM_OF_PASS, PETROL_TYPE, CAR_MODEL, CAR_COLOR, NUM_OF_DOORS, CAR_BRAND, RENT_PRICE, EMP_ID, year);
-- rent (CAR_ID, CUS_ID, rent_ID, rent date, return date, EMP_ID, price);
-- payment (CAR_ID, cus_id, PAY_DATE, price, pay_id);
drop database dataproject;
create database dataproject;
use dataproject;
CREATE TABLE RENTING_EMP (
EMP_ID INT PRIMARY KEY auto_increment,
NAME VARCHAR(30),
ADDRESS VARCHAR(30),
EMP_PNUM INT,
DATE_OF_BIRTH DATE,
START_DATE DATE,
BASE_SALARY INT,
COMMISSION_RATE REAL,
YEARLY_SALES INT
);
CREATE TABLE CUSTOMERS (
CUS_NAME VARCHAR(30),
ADDRESS VARCHAR(30),
CUS_ID INT PRIMARY KEY auto_increment,
BUDGET INT,
CARRIER VARCHAR(20),
CUS_PNUM INT,
EMP_ID INT,
FOREIGN KEY (EMP_ID)
REFERENCES RENTING_EMP (EMP_ID)
);
CREATE TABLE VEHICLES (
CAR_ID INT PRIMARY KEY auto_increment,
OPTIONS VARCHAR(50),
PRICE_WIHTOUT_CUSTOMS INT,
TRANSMISSION_TYPE CHAR,
NUM_OF_PASS INT,
PETROL_TYPE CHAR,
CAR_MODEL VARCHAR(30),
CAR_COLOR VARCHAR(20),
NUM_OF_DOORS INT,
CAR_BRAND VARCHAR(30),
RENT_PRICE INT,
EMP_ID INT,
year INT,
FOREIGN KEY (EMP_ID)
REFERENCES RENTING_EMP (EMP_ID)
);
CREATE TABLE rent (
CAR_ID INT,
CUS_ID INT,
rent_ID INT AUTO_INCREMENT PRIMARY KEY,
rent_date DATE,
return_date DATE,
emp_id INT,
price REAL,
FOREIGN KEY (cus_ID)
REFERENCES customers (cus_id)
ON DELETE CASCADE,
FOREIGN KEY (emp_id)
REFERENCES RENTING_EMP (emp_id)
ON DELETE CASCADE,
FOREIGN KEY (CAR_ID)
REFERENCES VEHICLES (CAR_ID)
ON DELETE CASCADE
);
CREATE TABLE payment (
CAR_ID INT,
cus_id INT,
PAY_DATE DATE,
price REAL,
pay_id INT AUTO_INCREMENT PRIMARY KEY,
FOREIGN KEY (CAR_ID)
REFERENCES VEHICLES (CAR_ID),
FOREIGN KEY (cus_ID)
REFERENCES customers (cus_id)
ON DELETE CASCADE
);
insert into renting_emp(name,address,emp_pnum,DATE_OF_BIRTH,START_DATE,BASE_SALARY,COMMISSION_RATE,YEARLY_SALES) value ('Salah Tawafsha', 'Sinjel',592485699,'2002-08-17','2022-06-11',5000,1.5,2500);
insert into renting_emp(name,address,emp_pnum,DATE_OF_BIRTH,START_DATE,BASE_SALARY,COMMISSION_RATE,YEARLY_SALES) value ('Anas Taleeb', 'beit our',569899675,'2002-08-02','2022-06-11',5000,1.5,2500);
insert into renting_emp(name,address,emp_pnum,DATE_OF_BIRTH,START_DATE,BASE_SALARY,COMMISSION_RATE,YEARLY_SALES) value ('Mohammad Shair', 'Sorda',595030114,'2002-06-13','2022-06-11',5000,1.5,2500);
insert into customers(CUS_NAME,ADDRESS,BUDGET,CARRIER,CUS_PNUM,EMP_ID) values ('Salah Yaaqba','al terra', 2000,'Programmer',568217250,3);
insert into customers(CUS_NAME,ADDRESS,BUDGET,CARRIER,CUS_PNUM,EMP_ID) values ('Abdelarheem','Birziet', 3000,'Programmer',597123765,1);
insert into customers(CUS_NAME,ADDRESS,BUDGET,CARRIER,CUS_PNUM,EMP_ID) values ('Mohammad Mosleh','al terra', 5000,'builder',597190708,1);
insert into customers(CUS_NAME,ADDRESS,BUDGET,CARRIER,CUS_PNUM,EMP_ID) values ('Asma AbuRaqi','Al Sawia', 1000,'Student',595378865,2);
insert into customers(CUS_NAME,ADDRESS,BUDGET,CARRIER,CUS_PNUM,EMP_ID) values ('abedalmajeed','rammallah', 1500,'studenat',0594627023,1);
insert into customers(CUS_NAME,ADDRESS,BUDGET,CARRIER,CUS_PNUM,EMP_ID) values ('abedalhameed','ramallah', 1900,'student',0598887639,2);
insert into customers(CUS_NAME,ADDRESS,BUDGET,CARRIER,CUS_PNUM,EMP_ID) values ('ahmad','raddad', 7,'student',0591234567,3);
insert into customers(CUS_NAME,ADDRESS,BUDGET,CARRIER,CUS_PNUM,EMP_ID) values ('moath haj ali','jamaeen', 5000,'build eng.',592438547,1);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('6400', 400000, 'A', 2, 'P', 'Challenger Demon','Red',2,'Dodge',1000,1,2022);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('2500cc', 70000, 'G', 2, 'P', 'E46','Silver',2,'Bmw',1000,2,2020);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('1600cc', 30000, 'A', 5, 'D', 'Picanto','Blue',4,'Kia',50,2,2019);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('2000 CC', 180000, 'A', 5, 'P', 'Golf Mk7 GTI','Blue',4,'Volkswagen',200,1,2020);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('1600 cc', 60000, 'G', 5, 'D', 'Corsa','White',4,'Opel',100,2,2015);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('2000 CC', 200000, 'G', 4, 'P', 'A200','Black',4,'Mercedes-Benz',400,3,2020);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('4000 CC 612HP', 400000, 'A', 5, 'P', 'GLE 63 S','Black',4,'Mecededes-Benz',1000,1,2022);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('3000CC', 250000, 'A', 4, 'P', 'M4','Red',2,'Bmw',500,2,2019);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('2000 cc', 70000, 'G', 5, 'D', 'Mk5','Silver',2,'Volkswagen',100,3,2004);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('2200 CC', 150000, 'A', 5, 'P', 'Copra FR','Bink',2,'Seat',300,2,2019);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('2000CC', 250000, 'A', 5, 'P', 'Evuqe','White',4,'Range Rover',500,2,2020);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('3000cc', 5000, 'A', 5, 'D', 'G Class','Black',4,'Mercedes-Benz',900,2,2020);
insert into VEHICLES(OPTIONS,PRICE_WIHTOUT_CUSTOMS,TRANSMISSION_TYPE,NUM_OF_PASS,PETROL_TYPE,CAR_MODEL,CAR_COLOR,NUM_OF_DOORS,CAR_BRAND,RENT_PRICE,EMP_ID,year) value ('5000cc V8', 40000, 'A', 5, 'P', 'Sport SVR','White & Black',4,'Rang Rover',800,2,2020);