marketApp database queries.txt

DROP TABLE IF EXISTS ITEM;
DROP TABLE IF EXISTS USER;
DROP TABLE IF EXISTS USER_ITEM;
DROP TABLE IF EXISTS RECEIPT;
DROP TABLE IF EXISTS RECEIPT_ITEM;

CREATE TABLE IF NOT EXISTS ITEM(
id identity,
name varchar(50) not null,
code varchar(6) not null,
type varchar(50) not null,
quantity integer,
price number
);

INSERT INTO ITEM(NAME, CODE, TYPE, QUANTITY, PRICE)VALUES
('Apple', 'FD4689', 'Food', 1, 22.5),
('Banana', 'FD7515', 'Food', 1 , 10),
('Pear', 'FD9124', 'Food', 1, 5),
('IphoneX', 'ES1712', 'Electronics', 1, 500);

CREATE TABLE IF NOT EXISTS USER(
id identity,
name varchar(25) not null,
surname varchar(25) not null,
type varchar(25) not null, 
username varchar(50) not null,
password varchar(255) not null
);

INSERT INTO USER (NAME, SURNAME, TYPE, USERNAME, PASSWORD)VALUES
('adminName', 'adminSurname', 'Admin' ,'admin', 'MTIz'),
('John', 'Doe', 'User', 'johndoe', 'MTIz');

CREATE TABLE IF NOT EXISTS USER_ITEM(
user_id bigint not null,
item_id bigint not null,
constraint fk_user foreign key (user_id) references user(id),
constraint fk_item foreign key (item_id) references item(id)
);

INSERT INTO USER_ITEM(USER_ID, ITEM_ID) VALUES
(1, 1),
(1, 2),
(1, 3);


CREATE TABLE IF NOT EXISTS RECEIPT(
id identity not null,
name varchar(50) not null,
user_name_surname varchar(80) not null,
date_issued date not null,
time_issued time not null,
price number not null
);


CREATE TABLE IF NOT EXISTS RECEIPT_ITEM(
receipt_id bigint not null,
item_id bigint not null,
constraint fk_receipt foreign key (receipt_id) references receipt(id),
constraint fk_purchased_item foreign key (item_id) references item(id)
);


///QUERY TO FETCH SELECTED ITEM FOR USER ID
SELECT ITEM.* FROM ITEM INNER JOIN
USER_ITEM ON ITEM.ID = ITEM_ID INNER JOIN
USER ON USER_ID = USER.ID
WHERE USER.ID = ?

///QUERY TO FETCH ALL ITEMS OF SPECIFIC RECEIPT 
SELECT ITEM.* FROM ITEM INNER JOIN
RECEIPT_ITEM ON ITEM.ID = ITEM_ID INNER JOIN
RECEIPT ON RECEIPT_ID = RECEIPT.ID
WHERE RECEIPT.ID = ?