Dvdrental

Handles inventory management across different physical locations. 2. Setting Up the dvdrental Database

// GET /api/rentals/overdue app.get('/api/rentals/overdue', async (req, res) => const result = await pool.query(` SELECT * FROM overdue_rentals ORDER BY days_overdue DESC `); res.json(result.rows); );

The dvdrental sample database is an indispensable resource for any developer or data analyst looking to strengthen their PostgreSQL skills. Its realistic schema, combined with a comprehensive set of tables, provides a robust environment to practice everything from basic CRUD operations to advanced performance optimization. If you'd like to explore this further, I can help you by: dvdrental

-- Main tables involved: -- rental (rental_id, customer_id, inventory_id, rental_date, return_date) -- customer (customer_id, first_name, last_name, email, address_id) -- inventory (inventory_id, film_id, store_id) -- film (film_id, title, rental_rate) -- payment (payment_id, customer_id, amount, payment_date)

-- Process payment for customer ID 5 CALL process_late_fee_payment(5, 15.00); Its realistic schema, combined with a comprehensive set

CREATE OR REPLACE PROCEDURE process_late_fee_payment( p_customer_id INTEGER, p_amount NUMERIC ) LANGUAGE plpgsql AS $$ DECLARE v_total_owed NUMERIC; BEGIN -- Calculate total late fees for customer SELECT COALESCE(SUM(GREATEST(0, (CURRENT_DATE - r.rental_date::DATE) - f.rental_duration) * 0.50), 0) INTO v_total_owed FROM rental r JOIN inventory i ON r.inventory_id = i.inventory_id JOIN film f ON i.film_id = f.film_id WHERE r.customer_id = p_customer_id AND r.return_date IS NULL AND (CURRENT_DATE - r.rental_date::DATE) > f.rental_duration; IF p_amount >= v_total_owed THEN -- Insert payment record INSERT INTO payment ( customer_id, staff_id, rental_id, amount, payment_date ) SELECT p_customer_id, 1, -- Default staff ID NULL, v_total_owed, NOW() WHERE v_total_owed > 0;

CREATE OR REPLACE VIEW overdue_rentals AS SELECT r.rental_id, c.customer_id, c.first_name, c.last_name, c.email, f.title AS film_title, r.rental_date, r.return_date, CURRENT_DATE - r.rental_date::DATE AS days_rented, f.rental_duration, CASE WHEN r.return_date IS NULL AND (CURRENT_DATE - r.rental_date::DATE) > f.rental_duration THEN (CURRENT_DATE - r.rental_date::DATE) - f.rental_duration ELSE 0 END AS days_overdue FROM rental r JOIN customer c ON r.customer_id = c.customer_id JOIN inventory i ON r.inventory_id = i.inventory_id JOIN film f ON i.film_id = f.film_id WHERE r.return_date IS NULL AND (CURRENT_DATE - r.rental_date::DATE) > f.rental_duration; Its realistic schema

Providing to solve complex analytical problems using this database.