BD v3

CREATE TABLE client(client_id int,ime varchar(100), phone varchar(100));
CREATE TABLE employee(employee_id int,ime varchar(100), phone int, position_id int);
CREATE TABLE groups(group_id int, grupa varchar(100));
CREATE TABLE position(position_id int, position varchar(100));
CREATE TABLE product(product_id int,ime varchar(100), cena float, group_id int);
CREATE TABLE sales(sales_id int, sale_date date, cena float, client_id int, employee_id int);
CREATE TABLE relation(product_id int, sales_id int, Quantity int);

ALTER TABLE client ADD PRIMARY KEY (client_id);
ALTER TABLE employee ADD PRIMARY KEY (employee_id);
ALTER TABLE groups ADD PRIMARY KEY (group_id);
ALTER TABLE position ADD PRIMARY KEY (position_id);
ALTER TABLE product ADD PRIMARY KEY (product_id);
ALTER TABLE relation ADD PRIMARY KEY (product_id,sales_id);
ALTER TABLE sales ADD PRIMARY KEY (sales_id);

ALTER TABLE employee ADD FOREIGN KEY (position_id) references position;
ALTER TABLE product ADD FOREIGN KEY (group_id) references groups;
ALTER TABLE relation ADD FOREIGN KEY (product_id) references product;
ALTER TABLE relation ADD FOREIGN KEY (sales_id) references sales;
ALTER TABLE sales ADD FOREIGN KEY (client_id) references client;
ALTER TABLE sales ADD FOREIGN KEY (employee_id) references employee;

INSERT INTO client(client_id, ime, phone) VALUES (1, 'David', '89342123');
INSERT INTO client(client_id, ime, phone) VALUES (2, 'Dave', '89342124');
INSERT INTO client(client_id, ime, phone) VALUES (3, 'Dinko', '9123456');
INSERT INTO client(client_id, ime, phone) VALUES (4, 'Jordan', '123456789');

INSERT INTO employee(employee_id, ime, phone, position_id) VALUES (1, 'Mihail', 088123456, 2);
INSERT INTO employee(employee_id, ime, phone, position_id) VALUES (2, 'Petkan', 0878123456, 1);
INSERT INTO employee(employee_id, ime, phone, position_id) VALUES (3, 'Ivan', 0876123456, 3);

INSERT INTO groups(group_id,grupa) VALUES (1, 'Dairy');
INSERT INTO groups(group_id,grupa) VALUES (2, 'Meat');
INSERT INTO groups(group_id,grupa) VALUES (3, 'Fruits');
INSERT INTO groups(group_id,grupa) VALUES (4, 'Vegetables');

INSERT INTO position(position_id, position) VALUES (1, 'Retailer');
INSERT INTO position(position_id, position) VALUES (2, 'Manager');
INSERT INTO position(position_id, position) VALUES (3, 'CEO');

INSERT INTO product(product_id, ime, cena, group_id) VALUES (1, 'Tortila', 2.5, 1);
INSERT INTO product(product_id, ime, cena, group_id) VALUES (2, 'Cucumbers', 3, 4);
INSERT INTO product(product_id, ime, cena, group_id) VALUES (3, 'DonerKebab', 15.5, 2);
INSERT INTO product(product_id, ime, cena, group_id) VALUES(4, 'Peaches', 250, 3);
INSERT INTO product(product_id, ime, cena, group_id) VALUES(5, 'Apples', 200, 3);
INSERT INTO product(product_id, ime, cena, group_id) VALUES(6, 'Cherry', 100, 3);

INSERT INTO relation(product_id, sales_id, Quantity) VALUES (1,2,1);
INSERT INTO relation(product_id, sales_id, Quantity) VALUES (2,4,2);
INSERT INTO relation(product_id, sales_id, Quantity) VALUES (3,1,1);
INSERT INTO relation(product_id, sales_id, Quantity) VALUES (4,3,4);
INSERT INTO relation(product_id, sales_id, Quantity) VALUES (5,5,10);
INSERT INTO relation(product_id, sales_id, Quantity) VALUES (6,6,6);
INSERT INTO relation(product_id, sales_id, Quantity) VALUES (6,7,6);

INSERT INTO sales(sales_id, sale_date, cena, client_id, employee_id) VALUES (1, '02-OCT-14', 2.5, 1, 2);
INSERT INTO sales(sales_id, sale_date, cena, client_id, employee_id) VALUES (2, '03-MAY-12', 3, 2, 2);
INSERT INTO sales(sales_id, sale_date, cena, client_id, employee_id) VALUES (3, '21-OCT-11', 15.5, 3, 1);
INSERT INTO sales(sales_id, sale_date, cena, client_id, employee_id) VALUES (4, '14-JUN-13', 250, 1, 3);
INSERT INTO sales(sales_id, sale_date, cena, client_id, employee_id) VALUES (5, '5-JUL-13', 200, 2, 3);
INSERT INTO sales(sales_id, sale_date, cena, client_id, employee_id) VALUES (6, '1-JAN-12', 15, 2, 2);
INSERT INTO sales(sales_id, sale_date, cena, client_id, employee_id) VALUES (7, '1-JAN-13', 15, 2, 2);

UPDATE employee SET Ime = 'Svetlio' WHERE employee_id = 1;
UPDATE employee SET Ime = 'Joro' WHERE employee_id = 1;
UPDATE employee SET Ime = 'Mihail' WHERE employee_id = 1;

SELECT p.Ime,p.Cena,g.Grupa FROM product p
JOIN groups g ON g.group_id = p.product_id
WHERE p.Cena = 2.50;

SELECT p.Ime,p.Cena,g.Grupa FROM product p
JOIN groups g ON p.Group_ID = g.group_id
WHERE p.Ime = 'Tortila';

SELECT p.Ime,p.Cena,g.Grupa FROM product p
JOIN groups g ON p.Group_ID = g.group_id
WHERE g.Grupa = 'Dairy';

SELECT p.Ime,p.Cena,r.Quantity,g.Grupa,s.sale_date,s.Cena,c.Ime,e.Ime FROM Relation r
JOIN Sales s on s.sales_ID = r.Sales_ID
JOIN client c on c.client_ID = s.client_id
JOIN employee e on e.employee_ID = s.employee_ID
JOIN product p on p.product_ID = r.product_ID
JOIN groups g on g.group_ID = p.Group_ID
Where s.sale_date BETWEEN '02-OCT-14' AND '02-OCT-16';

SELECT p.Ime,p.Cena,r.Quantity,g.Grupa,s.sale_date,s.Cena,c.Ime,e.Ime FROM Relation r
JOIN Sales s on s.sales_ID = r.Sales_ID
JOIN client c on c.client_ID = s.client_id
JOIN employee e on e.employee_id = s.employee_id
Join product p on p.product_id = r.product_ID
JOIN groups g on g.group_id = p.Group_ID
WHERE e.Ime LIKE 'Svetlio'
ORDER BY s.sale_date;

SELECT p.Ime,p.Cena,r.Quantity,g.Grupa,s.sale_date,s.Cena,c.Ime,e.Ime FROM Relation r
JOIN Sales s on s.sales_ID = r.Sales_ID
JOIN client c on c.client_ID = s.client_id
JOIN employee e on e.employee_id = s.employee_id
Join product p on p.product_id = r.product_ID
JOIN groups g on g.group_id = p.Group_ID
ORDER BY r.Quantity;


CREATE OR REPLACE TRIGGER client_id_AUTO_TR
BEFORE INSERT ON client
FOR EACH ROW
WHEN (NEW.client_id IS NULL)
BEGIN :NEW.client_id := SEQUENCE1.NEXTVAL;
END;

CREATE OR REPLACE TRIGGER employee_id_AUTO_TR
BEFORE INSERT ON employee
FOR EACH ROW
WHEN (NEW.employee_id IS NULL)
BEGIN :NEW.employee_id := SEQUENCE2.NEXTVAL;
END;

CREATE OR REPLACE TRIGGER group_id_AUTO_TR
BEFORE INSERT ON groups
FOR EACH ROW
WHEN (NEW.group_id IS NULL)
BEGIN :NEW.group_id := SEQUENCE3.NEXTVAL;
END;

CREATE OR REPLACE TRIGGER position_id_AUTO_TR
BEFORE INSERT ON position
FOR EACH ROW
WHEN (NEW.position_id IS NULL)
BEGIN :NEW.group_id := SEQUENCE4.NEXTVAL;
END;

CREATE OR REPLACE TRIGGER product_id_AUTO_TR
BEFORE INSERT ON product
FOR EACH ROW
WHEN (NEW.product_id IS NULL)
BEGIN :NEW.product_id := SEQUENCE5.NEXTVAL;
END;

CREATE OR REPLACE TRIGGER product_id_AUTO_TR
BEFORE INSERT ON relation
FOR EACH ROW
WHEN (NEW.product_id IS NULL)
BEGIN :NEW.product_id := SEQUENCE6.NEXTVAL;
END;

CREATE OR REPLACE TRIGGER sales_id_AUTO_TR
BEFORE INSERT ON relation
FOR EACH ROW
WHEN (NEW.sales_id IS NULL)
BEGIN :NEW.sales_id := SEQUENCE7.NEXTVAL;
END;

CREATE OR REPLACE TRIGGER sales_id_AUTO_TR
BEFORE INSERT ON sales
FOR EACH ROW
WHEN (NEW.sales_id IS NULL)
BEGIN :NEW.sales_id := SEQUENCE8.NEXTVAL;
END;