Lex L- Profile

–creation of roles
CREATE ROLE logistics;
CREATE ROLE analytics;
CREATE ROLE sales;

— Logistics role
GRANT SELECT ON members, items, transactions TO logistics;
GRANT INSERT, UPDATE ON transactions TO logistics;

— Analytics role
— No write permissions needed

— Sales role
GRANT SELECT ON members, items, transactions TO sales;
GRANT INSERT, UPDATE, DELETE ON items TO sales;

— All roles have read access
GRANT SELECT ON members, items, transactions TO logistics, analytics, sales;

— Get the total weight of the total items bought, for staff from Logistics

SELECT
SUM(total_items_weight_kg) AS total_weight
FROM
transactions;

— Update the table with a new transaction, for staff from Logistics

INSERT INTO transactions (transaction_id, membership_id, items_bought, total_items_price, total_items_weight_kg)
VALUES (71, ‘Jones_cdd9f’, ARRAY[‘Item3’, ‘Item5’], 27.00, 0.80);

— Analyze the sales and membership status, for staff from Analytics
— Sales records of Standard and Senior group. Senior being defined as born before the year 1970, Standard born 1970 and after
SELECT
m.membership_id, m.first_name,m.last_name, m.email,
t.transaction_id,t.items_bought,t.total_items_price, t.total_items_weight_kg,
CASE
WHEN DATE_PART(‘year’, TO_DATE(m.date_of_birth, ‘YYYYMMDD’)) < 1970 THEN ‘Senior’
ELSE ‘Standard’
END AS member_type
FROM
members m
JOIN
transactions t ON m.membership_id = t.membership_id;

— Total sales between Seniors and Standard members, for staff from Analytics

SELECT
CASE
WHEN DATE_PART(‘year’, TO_DATE(m.date_of_birth, ‘YYYYMMDD’)) < 1970 THEN ‘Senior’
ELSE ‘Standard’
END AS member_type,
SUM(t.total_items_price) AS total_sales
FROM
members m
JOIN
transactions t ON m.membership_id = t.membership_id
GROUP BY
CASE
WHEN DATE_PART(‘year’, TO_DATE(m.date_of_birth, ‘YYYYMMDD’)) < 1970 THEN ‘Senior’
ELSE ‘Standard’
END;

— Add 3 new items, for staff from Sales

— Assuming item_id values are unique and not already existing in the table
INSERT INTO items (item_id, item_name, manufacturer_name, cost, weight_kg)
VALUES
(‘Item11’, ‘Laptop’, ‘Lenovo’, 1200.00, 2.5),
(‘Item12’, ‘Smartphone’, ‘Apple’, 1000.00, 0.3),
(‘Item13’, ‘Headphones’, ‘Sony’, 150.00, 0.2);

— Remove old Item1, for staff from Sales

DELETE FROM items
WHERE item_id = ‘Item1’;