test.sql

-- test cases generated BY ChatGPT

SELECT id, name, email FROM Users WHERE active = true;
UPDATE users SET last_login = now() WHERE id = 1;
DELETE FROM sessions WHERE user_id = 1;
INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 2, 3);

SELECT a.name, b.amount
FROM customers a
INNER JOIN transactions b ON a.id = b.customer_id
WHERE a.country = 'USA and Canada'
AND b.amount > 100
ORDER BY b.date DESC;

CREATE TABLE products (
    id serial PRIMARY KEY,
    name varchar(100) NOT NULL,
    price decimal(10, 2) NOT NULL CHECK (price > 0)
);

CREATE TABLE orders (
    id serial PRIMARY KEY,
    user_id int REFERENCES users(id),
    product_id int REFERENCES products(id),
    quantity int CHECK (quantity > 0)
);

CREATE PROCEDURE get_customer_balance(IN customer_id int)
BEGIN
    DECLARE balance decimal(10, 2);
    SELECT sum(amount) INTO balance FROM transactions WHERE customer_id = customer_id;
    SELECT balance;
END;

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE active = true;

CREATE INDEX idx_user_email ON users (email);