SQLize | PHPize | SQLtest

-- ================================ -- CREATE TABLES -- ================================ -- Create User table CREATE TABLE "User" ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, phone VARCHAR(20) NOT NULL, city VARCHAR(50), -- добавлено поле city created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create Shelter table CREATE TABLE "Shelter" ( shelter_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, shelter_name VARCHAR(100) NOT NULL, city VARCHAR(50) NOT NULL, phone VARCHAR(20) NOT NULL, address VARCHAR(255) NOT NULL, capacity INT DEFAULT 0, -- добавлено поле capacity created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create Pet table CREATE TABLE "Pet" ( pet_id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, age INT, species VARCHAR(50) NOT NULL, breed VARCHAR(50), special_care_required VARCHAR(255), shelter_id INT NOT NULL, status VARCHAR(20) DEFAULT 'Available' CHECK (status IN ('Available', 'Adopted', 'Pending', 'Medical Hold')), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (shelter_id) REFERENCES "Shelter"(shelter_id) ON DELETE CASCADE ); -- Create AdoptionRequest table CREATE TABLE "AdoptionRequest" ( request_id SERIAL PRIMARY KEY, user_id INT NOT NULL, pet_id INT NOT NULL, status VARCHAR(20) DEFAULT 'Pending' CHECK (status IN ('Pending', 'Approved', 'Rejected', 'Completed')), request_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, processed_date TIMESTAMP, notes TEXT, FOREIGN KEY (user_id) REFERENCES "User"(user_id) ON DELETE CASCADE, FOREIGN KEY (pet_id) REFERENCES "Pet"(pet_id) ON DELETE CASCADE, CONSTRAINT unique_user_pet_request UNIQUE (user_id, pet_id) ); -- Create AdoptionHistory table CREATE TABLE "AdoptionHistory" ( history_id SERIAL PRIMARY KEY, pet_id INT NOT NULL, user_id INT NOT NULL, adoption_date DATE NOT NULL, return_date DATE, adoption_fee NUMERIC(10, 2), notes TEXT, FOREIGN KEY (pet_id) REFERENCES "Pet"(pet_id) ON DELETE RESTRICT, FOREIGN KEY (user_id) REFERENCES "User"(user_id) ON DELETE RESTRICT ); -- Add check constraints ALTER TABLE "Pet" ADD CONSTRAINT chk_pet_age CHECK (age >= 0); ALTER TABLE "AdoptionHistory" ADD CONSTRAINT chk_adoption_dates CHECK (return_date IS NULL OR return_date >= adoption_date); -- ================================ -- INSERT SAMPLE DATA -- ================================ -- Shelters INSERT INTO "Shelter" (username, password, shelter_name, city, phone, address, capacity) VALUES ('asyl_januar', 'hashed_pass1', 'Asyl Januar Shelter', 'Almaty', '+77271234567', '123 Dostyk Street', 30), ('bota_panahana', 'hashed_pass2', 'Bota Shelter', 'Astana', '+77123456789', '45 Kabanbay Batyr Ave', 20); -- Users INSERT INTO "User" (username, password, email, phone, city) VALUES ('alimzhan_k', 'user_pass1', 'alimzhan@mail.com', '+77011234567', 'Almaty'), ('aigul_s', 'user_pass2', 'aigul@mail.com', '+77022345678', 'Astana'); -- Pets INSERT INTO "Pet" (name, age, species, breed, special_care_required, shelter_id) VALUES ('Barsik', 2, 'Cat', 'Street Cat', 'None', 1), ('Alabai', 4, 'Dog', 'Central Asian Shepherd', 'Needs large space', 2),; -- Adoption Requests INSERT INTO "AdoptionRequest" (user_id, pet_id, status) VALUES (1, 2, 'Pending'), (2, 1, 'Approved'); -- Adoption History INSERT INTO "AdoptionHistory" (pet_id, user_id, adoption_date, adoption_fee) VALUES (1, 2, '2023-06-15', 15000.00); -- ================================ -- WHERE Filtration -- ================================ -- 1. Dogs available for adoption SELECT * FROM "Pet" WHERE species = 'Dog'; -- 2. Approved adoption requests SELECT * FROM "AdoptionRequest" WHERE status = 'Approved'; -- 3. Users located in Almaty SELECT * FROM "User" WHERE city = 'Almaty'; -- 4. Pets under 1 year SELECT * FROM "Pet" WHERE age < 1; -- 5. Adoptions after Jan 2023 SELECT * FROM "AdoptionHistory" WHERE adoption_date > '2023-01-01'; -- ================================ -- String Functions -- ================================ -- 1. Concatenated message SELECT CONCAT(u.username, ' adopted a pet named ', p.name) AS adoption_message FROM "User" u JOIN "AdoptionHistory" h ON u.user_id = h.user_id JOIN "Pet" p ON h.pet_id = p.pet_id; -- 2. Uppercase shelter names SELECT UPPER(shelter_name) AS shelter_name_uppercase FROM "Shelter"; -- 3. Lowercase emails SELECT LOWER(email) AS email_lowercase FROM "User"; -- 4. First 10 chars of pet name SELECT SUBSTRING(name FROM 1 FOR 10) AS short_name FROM "Pet"; -- 5. Trim shelter address SELECT TRIM(address) AS trimmed_address FROM "Shelter"; -- ================================ -- Date Functions -- ================================ -- 1. Current time SELECT NOW() AS current_time; -- 2. Follow-up 30 days later SELECT adoption_date + INTERVAL '30 days' AS follow_up_date FROM "AdoptionHistory"; -- 3. Years since adoption SELECT DATE_PART('year', AGE(NOW(), adoption_date)) AS years_since_adoption FROM "AdoptionHistory"; -- 4. Extract year SELECT EXTRACT(YEAR FROM adoption_date) AS adoption_year FROM "AdoptionHistory"; -- 5. Extract month SELECT EXTRACT(MONTH FROM adoption_date) AS adoption_month FROM "AdoptionHistory"; -- ================================ -- UPDATE Statements -- ================================ -- 1. Update pet status UPDATE "Pet" SET status = 'Adopted' WHERE pet_id = 1; -- 2. Update user phone UPDATE "User" SET phone = '+77011234568' WHERE user_id = 1; -- 3. Increase shelter capacity UPDATE "Shelter" SET capacity = capacity + 10 WHERE shelter_id = 1; -- 4. Complete adoption request UPDATE "AdoptionRequest" SET status = 'Completed' WHERE request_id = 1; -- 5. Update adoption fee UPDATE "AdoptionHistory" SET adoption_fee = 16000 WHERE history_id = 1; -- ================================ -- DELETE Statements -- ================================ -- 1. Delete a rejected request DELETE FROM "AdoptionRequest" WHERE status = 'Rejected'; -- 2. Delete pets older than 10 years (example logic) DELETE FROM "Pet" WHERE age > 10; -- 3. Delete history with return date before 2020 DELETE FROM "AdoptionHistory" WHERE return_date < '2020-01-01'; -- 4. Delete users without email (if any) DELETE FROM "User" WHERE email IS NULL; -- 5. Delete shelters with 0 capacity DELETE FROM "Shelter" WHERE capacity = 0;