SQLize | PHPize | SQLtest

CREATE TABLE patients ( id INTEGER PRIMARY KEY, name VARCHAR(50), gender VARCHAR(10), birth_date DATE ); INSERT INTO patients (id, name, gender, birth_date) VALUES (1, 'Ahmad Rasyid', 'M', '1992-03-12'), (2, 'Lestari Putri', 'F', '1987-07-21'), (3, 'Budi Santoso', 'M', '2000-11-02'), (4, 'Sari Melati', 'F', '1995-01-30'), (5, 'Dimas Akbar', 'M', '1998-08-01'); CREATE TABLE doctors ( id INTEGER PRIMARY KEY, name VARCHAR(100), specialization VARCHAR(100) ); INSERT INTO doctors (id, name, specialization) VALUES (1, 'dr. Andi Wijaya', 'General Practitioner'), (2, 'dr. Rina Kartika', 'Pediatrician'), (3, 'dr. Hendra Gunawan', 'Internist'); CREATE TABLE appointments ( id INTEGER PRIMARY KEY, patient_id INTEGER, doctor_id INTEGER, date DATE, complaint TEXT ); INSERT INTO appointments (id, patient_id, doctor_id, date, complaint) VALUES (1, 1, 1, '2025-08-01', 'Demam dan pusing'), (2, 2, 2, '2025-08-02', 'Batuk dan pilek'), (3, 3, 3, '2025-08-03', 'Nyeri perut'), (4, 4, 1, '2025-08-04', 'Sakit kepala'), (5, 5, 1, '2025-08-05', 'Kontrol rutin'); CREATE TABLE medicines ( id INTEGER PRIMARY KEY, name TEXT, stock INTEGER, price INTEGER ); INSERT INTO medicines (id, name, stock, price) VALUES (1, 'Paracetamol', 100, 5000), (2, 'Amoxicillin', 80, 7000), (3, 'Vitamin C', 150, 3000), (4, 'Antasida', 60, 6000), (5, 'Ibuprofen', 90, 8000); CREATE TABLE prescriptions ( id INTEGER PRIMARY KEY, appointment_id INTEGER, medicine_id INTEGER, quantity INTEGER ); INSERT INTO prescriptions (id, appointment_id, medicine_id, quantity) VALUES (1, 1, 1, 2), (2, 1, 3, 1), (3, 2, 2, 2), (4, 3, 4, 1), (5, 4, 1, 1), (6, 5, 3, 1); --- Tampilkan daftar obat yang diresepkan lebih dari 1 kali, urutkan dari yang paling sering. SELECT medicines.name, COUNT(prescriptions.id) AS times_prescribed FROM prescriptions INNER JOIN medicines ON prescriptions.medicine_id = medicines.id GROUP BY medicines.name HAVING COUNT(prescriptions.id) > 1 ORDER BY times_prescribed DESC;