SQLize | PHPize | SQLtest

-- POSTGRESQL 15 SYNTAX ----------------DDL QUERIES-------------------- CREATE TABLE USERS( ID SERIAL PRIMARY KEY, USERNAME VARCHAR(50) NOT NULL, EMAIL VARCHAR(100) NOT NULL ); CREATE TABLE MANAGERS( ID SERIAL PRIMARY KEY, USERNAME VARCHAR(50) NOT NULL, EMAIL VARCHAR(100) NOT NULL ); CREATE TABLE REQUEST_TYPES( ID SERIAL PRIMARY KEY, NAME VARCHAR(256) NOT NULL ); CREATE TYPE REQUEST_STATUS AS ENUM ('NEW', 'IN PROGRESS', 'CLOSED'); CREATE TABLE SUPPORT_REQUESTS( ID SERIAL PRIMARY KEY, QUESTION_TEXT VARCHAR NOT NULL, ANSWER_TEXT VARCHAR, USER_FEEDBACK_MARK INTEGER CHECK (USER_FEEDBACK_MARK >= 1 AND USER_FEEDBACK_MARK <= 5), USER_ID INT NOT NULL, MANAGER_ID INT, REQUEST_TYPE_ID INT NOT NULL, STATUS REQUEST_STATUS NOT NULL DEFAULT 'NEW', DATETIME_CREATED TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, DATETIME_CLOSED TIMESTAMPTZ, FOREIGN KEY (USER_ID) REFERENCES USERS(ID), FOREIGN KEY (MANAGER_ID) REFERENCES MANAGERS(ID), FOREIGN KEY (REQUEST_TYPE_ID) REFERENCES REQUEST_TYPES(ID) ); ----------------END DDL QUERIES-------------------- ----------------INSERT SAMPLE DATA-------------------- -- Insert sample data into USERS table INSERT INTO USERS (USERNAME, EMAIL) VALUES ('user1', 'user1@example.com'), ('user2', 'user2@example.com'), ('user3', 'user3@example.com'); -- Insert sample data into MANAGERS table INSERT INTO MANAGERS (USERNAME, EMAIL) VALUES ('manager1', 'manager1@example.com'), ('manager2', 'manager2@example.com'), ('manager3', 'manager3@example.com'); -- Insert sample data into REQUEST_TYPES table INSERT INTO REQUEST_TYPES (NAME) VALUES ('Technical Support'), ('Billing Inquiry'), ('General Inquiry'); -- Insert sample data into SUPPORT_REQUESTS table INSERT INTO SUPPORT_REQUESTS (USER_ID, MANAGER_ID, REQUEST_TYPE_ID, STATUS, DATETIME_CREATED, DATETIME_CLOSED, QUESTION_TEXT, ANSWER_TEXT) VALUES (1, 1, 1, 'NEW', '2023-12-12 10:00:00'::TIMESTAMPTZ, NULL, 'How can I reset my password?', NULL), (2, 2, 2, 'IN PROGRESS', '2023-01-02 12:30:00'::TIMESTAMPTZ, NULL, 'I''m facing issues with my billing statement. Can you please assist?', NULL), (3, 3, 3, 'CLOSED', '2023-12-12 15:45:00'::TIMESTAMPTZ, '2023-01-04 09:20:00'::TIMESTAMPTZ, 'Thank you for resolving my issue! How was it fixed?', 'The issue was resolved by updating your account settings. Please let us know if you need further assistance.'); ----------------END SAMPLE DATA-------------------- ----------------USEFUL QUERIES-------------------- -- 1. FOR USERS ----- INSERT INTO SUPPORT_REQUESTS (USER_ID, QUESTION_TEXT, REQUEST_TYPE_ID) VALUES (1, 'MY BEAUTIFUL QUESTION TEXT', 1); ----- SELECT * FROM SUPPORT_REQUESTS WHERE USER_ID = 1 AND STATUS != 'CLOSED'; ----- UPDATE SUPPORT_REQUESTS SET STATUS = 'CLOSED' WHERE ID = 1; ----- UPDATE SUPPORT_REQUESTS SET STATUS = 'CLOSED', USER_FEEDBACK_MARK = 5 WHERE ID = 1; -- 2. FOR MANAGERS ----- SELECT * FROM SUPPORT_REQUESTS WHERE MANAGER_ID = 1 AND STATUS != 'CLOSED'; ----- SELECT * FROM SUPPORT_REQUESTS WHERE MANAGER_ID = 1; ----- SELECT * FROM SUPPORT_REQUESTS WHERE USER_ID = 1; ----- UPDATE SUPPORT_REQUESTS SET STATUS = 'CLOSED' WHERE ID = 1; ----- UPDATE SUPPORT_REQUESTS SET REQUEST_TYPE_ID = 1 WHERE ID = 1; -- 2. FOR RESPONSIBLE ----- SELECT TOTAL_AMOUNT, CLOSED_AMOUNT, CAST(CLOSED_AMOUNT AS FLOAT) / TOTAL_AMOUNT * 100 AS CLOSED_PERCENT FROM ( SELECT COUNT(*) AS TOTAL_AMOUNT, COUNT(CASE WHEN STATUS = 'CLOSED' THEN 1 END) AS CLOSED_AMOUNT FROM SUPPORT_REQUESTS WHERE DATETIME_CREATED > CURRENT_DATE - INTERVAL '1 month' ) AS subquery; SELECT RT_DATA.TOTAL_AMOUNT AS TOTAL_AMOUNT, RT_DATA.CLOSED_AMOUNT AS CLOSED_AMOUNT, CAST(RT_DATA.CLOSED_AMOUNT AS FLOAT) / TOTAL_AMOUNT * 100 AS CLOSED_PERCENT, RT.NAME FROM ( SELECT SR.REQUEST_TYPE_ID, COUNT(*) AS TOTAL_AMOUNT, COUNT(CASE WHEN STATUS = 'CLOSED' THEN 1 END) AS CLOSED_AMOUNT, FROM SUPPORT_REQUESTS AS SR WHERE DATETIME_CREATED > CURRENT_DATE - INTERVAL '1 month' GROUP BY SR.REQUEST_TYPE_ID ) AS RT_DATA JOIN REQUEST_TYPES AS RT ON RT_DATA.REQUEST_TYPE_ID = RT.ID; SELECT * FROM SUPPORT_REQUESTS WHERE DATETIME_CREATED > CURRENT_DATE - INTERVAL '1 month'; -- todo: -- 1. group by type -- 2. count in each group -- 3. show as another table ----- ----- ----- ----------------END USEFUL QUERIES--------------------