SQL Queries for Testing

-- Count records matching criteria
SELECT COUNT(*) FROM users 
WHERE status = 'active' AND created_date >= '2024-01-01';
 
-- Verify data exists
SELECT * FROM orders 
WHERE order_id = 'ORD-12345' AND status = 'completed';
 
-- Check for duplicates
SELECT email, COUNT(*) as count 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

-- Insert test user
INSERT INTO users (username, email, status, created_date)
VALUES ('testuser', 'test@example.com', 'active', NOW());
 
-- Bulk insert test data
INSERT INTO orders (user_id, product_id, amount, status)
VALUES 
  (1, 101, 99.99, 'pending'),
  (1, 102, 149.99, 'completed'),
  (2, 101, 99.99, 'pending');
 
-- Create test data from existing
INSERT INTO test_users SELECT * FROM users WHERE id <= 10;

-- Delete test data by pattern
DELETE FROM users WHERE email LIKE '%test.com';
 
-- Delete by date range
DELETE FROM orders 
WHERE created_date < NOW() - INTERVAL 30 DAY 
AND status = 'test';
 
-- Clear test table
TRUNCATE TABLE test_orders;

-- Verify foreign key relationships
SELECT o.order_id, o.user_id, u.username 
FROM orders o 
LEFT JOIN users u ON o.user_id = u.id 
WHERE u.id IS NULL;
 
-- Check data integrity
SELECT * FROM payments 
WHERE amount < 0 OR amount IS NULL;
 
-- Validate status transitions
SELECT order_id, status, previous_status, updated_date 
FROM order_audit 
WHERE (previous_status = 'completed' AND status = 'pending')
ORDER BY updated_date DESC;

-- Find slow queries (with indexes)
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE user_id = 123 AND status = 'pending';
 
-- Count records by status
SELECT status, COUNT(*) as count 
FROM orders 
GROUP BY status 
ORDER BY count DESC;
 
-- Get sample data
SELECT * FROM products 
ORDER BY RANDOM() 
LIMIT 10;

-- Safe update with WHERE clause
UPDATE users SET status = 'inactive' 
WHERE last_login < NOW() - INTERVAL 90 DAY 
AND status = 'active';
 
-- Conditional insert (if not exists)
INSERT INTO users (email, username)
SELECT 'new@test.com', 'newuser'
WHERE NOT EXISTS (
  SELECT 1 FROM users WHERE email = 'new@test.com'
);
 
-- Get recent records
SELECT * FROM error_logs 
WHERE created_date >= NOW() - INTERVAL 1 HOUR 
ORDER BY created_date DESC 
LIMIT 100;