Student_2_year_SQL/Lab11 at main · DamianLokic/Student_2_year_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
CREATE TABLE MY_RESTAURANT_CLIENTS
(NAME VARCHAR2(20) NOT NULL,
SURNAME VARCHAR2(20) NOT NULL,
CLIENT_ID NUMBER(6,0) PRIMARY KEY CHECK (CLIENT_ID>0),
PHONE NUMBER(9) UNIQUE,
EMAIL VARCHAR2(50) UNIQUE,
ADDRES VARCHAR2(30));

CREATE TABLE MY_RESTAURANT_MENU
(FOOD_TYPE VARCHAR2(25) NOT NULL,
PRICE NUMBER(3,1) CHECK(PRICE>0),
PORTION_SIZE VARCHAR2(25),
FOOD_DESCRIPTION VARCHAR2(50),
FOOD_CODE NUMBER (5,2)PRIMARY KEY UNIQUE);

CREATE TABLE MY_RESTAURANT_STAFF
(NAME VARCHAR2(20),
SURNAME VARCHAR2(20),
STAFF_ID NUMBER(6,0) PRIMARY KEY,
SALARY NUMBER(8,2),
DEPARTMENT_ID NUMBER(6,0) REFERENCES MY_RESTAURANT_DEPARTMENT(DEPARTMENT_ID),
STAFF_TYPE VARCHAR2(20));

CREATE TABLE MY_RESTAURANT_ORDERS
(ORDER_ID NUMBER(4,0) PRIMARY KEY,
PRICE NUMBER(5,0) NOT NULL,
STAFF_ID NUMBER(6,0) REFERENCES MY_RESTAURANT_STAFF(STAFF_ID),
CLIENT_ID NUMBER(6,0) REFERENCES MY_RESTAURANT_CLIENTS(CLIENT_ID),
FOOD_CODE NUMBER(5,2) REFERENCES MY_RESTAURANT_MENU(FOOD_CODE),
ORDER_DESCRIPTION VARCHAR2(50)DEFAULT NULL,
CHECK(STAFF_ID!=CLIENT_ID));

CREATE TABLE MY_RESTAURANT_JOB_HISTORY
(STAFF_ID NUMBER(6,0),
JOB_NAME VARCHAR2(25),
SALARY NUMBER(6,2) DEFAULT 0,
START_DATE DATE,
END_DATE DATE,
DEPARTMENT_ID NUMBER(6,0) REFERENCES MY_RESTAURANT_DEPARTMENT(DEPARTMENT_ID),
FOREIGN KEY (STAFF_ID) REFERENCES MY_RESTAURANT_STAFF(STAFF_ID),
CHECK(END_DATE>START_DATE));

CREATE TABLE MY_RESTAURANT_DEPARTMENT
(DEPARTMENT_ID NUMBER(4,0) PRIMARY KEY UNIQUE,
DEPARTMENT_NAME VARCHAR2(25),
MANAGER_ID NUMBER (6,0),
FACEBOOK_SIDE VARCHAR2(25),
LOCATION_ID NUMBER(4,0) REFERENCES MY_RESTAURANT_LOCATION(LOCATION_ID));

CREATE TABLE MY_RESTAURANT_LOCATION
(LOCATION_ID NUMBER(4,0) PRIMARY KEY,
STATE VARCHAR2(25),
CITY VARCHAR2(25),
COUNTRY VARCHAR2(20),
PHONE_NUMBER NUMBER(9));

//LOKACJA
INSERT INTO MY_RESTAURANT_LOCATION (LOCATION_ID,STATE,CITY,COUNTRY,PHONE_NUMBER) VALUES (100,'Alabama','Montgomery','USA',321967520);
INSERT INTO MY_RESTAURANT_LOCATION (LOCATION_ID,STATE,CITY,COUNTRY,PHONE_NUMBER) VALUES (200,'Masovian','Warsaw','Poland',486204678);
INSERT INTO MY_RESTAURANT_LOCATION (LOCATION_ID,STATE,CITY,COUNTRY,PHONE_NUMBER) VALUES (300,'Ontario' ,'Ottawa','Canada',348234893);

//DEPARTAMENTY

INSERT INTO MY_RESTAURANT_DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,FACEBOOK_SIDE,LOCATION_ID) VALUES (1111,'Administration' ,111,'facebook.com/312124',100);
INSERT INTO MY_RESTAURANT_DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,FACEBOOK_SIDE,LOCATION_ID) VALUES (2222,'Restaurant' ,112,'facebook.com/312124',100);
INSERT INTO MY_RESTAURANT_DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,FACEBOOK_SIDE,LOCATION_ID) VALUES (3333,'Restaurant' ,222,'facebook.com/93247',200);
INSERT INTO MY_RESTAURANT_DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,FACEBOOK_SIDE,LOCATION_ID) VALUES (4444,'Restaurant' ,333,'facebook.com/84524',300);
INSERT INTO MY_RESTAURANT_DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,FACEBOOK_SIDE,LOCATION_ID) VALUES (5555,'IT Department',334,'facebook.com/84524',300);

//KLIENCI

INSERT INTO MY_RESTAURANT_CLIENTS (NAME,SURNAME,CLIENT_ID,PHONE,EMAIL,ADDRES) VALUES('Kuba' ,'Kowalski', 10 ,123753290,'kubakowalski@wp.pl','132 Main Street');
INSERT INTO MY_RESTAURANT_CLIENTS (NAME,SURNAME,CLIENT_ID,PHONE,EMAIL,ADDRES) VALUES('Arek' ,'Kowicki', 20 ,789452318,'arekkowicki@op.pl','429 Cale Street');
INSERT INTO MY_RESTAURANT_CLIENTS (NAME,SURNAME,CLIENT_ID,PHONE,EMAIL,ADDRES) VALUES('Kasia' ,'Jarocka', 70 ,123893128,'kasiajarocka@wp.pl','211 Matarata');
INSERT INTO MY_RESTAURANT_CLIENTS (NAME,SURNAME,CLIENT_ID,PHONE,EMAIL,ADDRES) VALUES('Ala' ,'Wysocka', 30 ,365295782,'alawysocka@wp.pl','163 Yokalo');
INSERT INTO MY_RESTAURANT_CLIENTS (NAME,SURNAME,CLIENT_ID,PHONE,EMAIL,ADDRES) VALUES('Maciek' ,'Adamski', 40 ,304238392,'maciekadamski@wp.pl','712 York Eye');
INSERT INTO MY_RESTAURANT_CLIENTS (NAME,SURNAME,CLIENT_ID,PHONE,EMAIL,ADDRES) VALUES('Basia' ,'Nowicka', 60 , 413714238,'basianowicka@wp.pl','131 Maslana');
INSERT INTO MY_RESTAURANT_CLIENTS (NAME,SURNAME,CLIENT_ID,PHONE,EMAIL,ADDRES) VALUES('Czarek' ,'Szaralo', 50 ,895437123,'czarekszaralo@wp.pl','11 Jakubianow');

//MENU

INSERT INTO MY_RESTAURANT_MENU (FOOD_TYPE,PRICE,PORTION_SIZE,FOOD_DESCRIPTION,FOOD_CODE) VALUES ('Fast Food', 13.90,'2 Burgers','Double Big Mac',5);
INSERT INTO MY_RESTAURANT_MENU (FOOD_TYPE,PRICE,PORTION_SIZE,FOOD_DESCRIPTION,FOOD_CODE) VALUES ('Pizza', 24.99,'Capriciossa','40 cm',15);
INSERT INTO MY_RESTAURANT_MENU (FOOD_TYPE,PRICE,PORTION_SIZE,FOOD_DESCRIPTION,FOOD_CODE) VALUES ('Sphagetti', 12.50,'Carbonara','300 oz',25);
INSERT INTO MY_RESTAURANT_MENU (FOOD_TYPE,PRICE,PORTION_SIZE,FOOD_DESCRIPTION,FOOD_CODE) VALUES ('Home-cooked meal', 21.09,'150oz/200oz','Steak with potatoes',35);
INSERT INTO MY_RESTAURANT_MENU (FOOD_TYPE,PRICE,PORTION_SIZE,FOOD_DESCRIPTION,FOOD_CODE) VALUES ('Soup', 9.99,'1 litr','Tomato soup',45);
INSERT INTO MY_RESTAURANT_MENU (FOOD_TYPE,PRICE,PORTION_SIZE,FOOD_DESCRIPTION,FOOD_CODE) VALUES ('Appetizer', 12.90,'150oz/200oz','Chicken and fries',55);
INSERT INTO MY_RESTAURANT_MENU (FOOD_TYPE,PRICE,PORTION_SIZE,FOOD_DESCRIPTION,FOOD_CODE) VALUES ('Desert', 14.99,'100oz','Panacotta',65);
INSERT INTO MY_RESTAURANT_MENU (FOOD_TYPE,PRICE,PORTION_SIZE,FOOD_DESCRIPTION,FOOD_CODE) VALUES ('Drinks', 16.90,'0,5 litr','Coca-cola',75);

//STAFF

INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Louis', 'Griffin',111,120000,1111,'Manager');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Jack', 'Sparrow',1,8000 ,1111 ,'Accountant');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Joe', 'Barrow',3,15000,1111,'Accountant');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Jeremy','Nomsy', 7 ,13600,1111,'Accountant');

INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Amy','Swanson',112,11000,2222,'Manager');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Peter', 'Swanson',11,19000,2222,'Cooker');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Jeremy','Kinglsey',13, 30000,2222,'Waiter');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Amy','Kingsley',17,31200,2222,'Cleaner');

INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Witek','Kowalski',222,30000,3333,'Manager');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Kim', 'Chojnicka',21,16000,3333,'Cooker');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Mark','Rasojny','23',7500, 3333 ,'Cleaner');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Jacob','Mitco',27,13000,3333,'Waiter');

INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Joe','Kinglsey',333, 300000,4444,'Manager');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Barry', 'Allen',31,16000,3333,'Cooker');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Josephine','Aligato','33',7500, 3333 ,'Cleaner');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Klaudia','Maslowska',37,25000,2222,'Waitress');

INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Quagmire','James', 334 ,300000,5555,'Manager');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Stewie','Cardito',41,250000,5555,'IT specialist');
INSERT INTO MY_RESTAURANT_STAFF (NAME,SURNAME,STAFF_ID,SALARY,DEPARTMENT_ID,STAFF_TYPE) VALUES ('Angelina','Lolie',47,250000,5555,'IT specialist');

//ORDERS
INSERT INTO MY_RESTAURANT_ORDERS (ORDER_ID,PRICE,STAFF_ID,CLIENT_ID,FOOD_CODE,ORDER_DESCRIPTION) VALUES (121,45.75,11,10,15);
INSERT INTO MY_RESTAURANT_ORDERS (ORDER_ID,PRICE,STAFF_ID,CLIENT_ID,FOOD_CODE,ORDER_DESCRIPTION) VALUES (122,31.66,11,20,5);
INSERT INTO MY_RESTAURANT_ORDERS (ORDER_ID,PRICE,STAFF_ID,CLIENT_ID,FOOD_CODE,ORDER_DESCRIPTION) VALUES (123,45.75,11,30,35);

INSERT INTO MY_RESTAURANT_ORDERS (ORDER_ID,PRICE,STAFF_ID,CLIENT_ID,FOOD_CODE,ORDER_DESCRIPTION) VALUES (121,45.75,11,10,15);
INSERT INTO MY_RESTAURANT_ORDERS (ORDER_ID,PRICE,STAFF_ID,CLIENT_ID,FOOD_CODE,ORDER_DESCRIPTION) VALUES (121,45.75,11,10,15);

SELECT * FROM MY_RESTAURANT_MENU