Pharmacy-Management-Studio/02_Sample_Data.sql at main · tumerf/Pharmacy-Management-Studio · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
-- Inserting Sample Data based on Project Documentation

-- 1. Address Data
INSERT INTO Address (street, city, zip_code) VALUES
('Bond Street No: 007', 'London', 'W1S'),
('Alias Street No:1', 'New York', '10001'),
('Meth Lab Road', 'Albuquerque', '87101'),
('Resistance HQ', 'Los Angeles', '90001'),
('Baker Street 221B', 'London', 'NW1'),
('Evergreen Terrace 742', 'Springfield', '45002'),
('Privet Drive 4', 'Little Whinging', 'SURREY'),
('Bagdat Caddesi No:10', 'Istanbul', '34700'),
('Istiklal Caddesi No:5', 'Istanbul', '34400'),
('Kizilay Meydanı', 'Ankara', '06420');

-- 2. Patient Data
INSERT INTO Patient (first_name, last_name, phone_number, email, date_of_birth, address_id) VALUES
('James', 'Bond', '+1-555-7007', 'james.bond@secret.com', '1980-11-11', 1),
('Jessica', 'Jones', '+1-555-8008', 'jessicaj@alias.com', '1992-04-15', 2),
('Walter', 'White', '+1-555-9009', 'walter.w@chem.com', '1970-09-07', 3),
('Sarah', 'Connor', '+1-555-1010', 'sarah.c@sky.com', '1985-05-20', 4),
('Sherlock', 'Holmes', '+44-20-7224', 'sherlock@detective.com', '1976-01-06', 5),
('Homer', 'Simpson', '+1-555-3223', 'homer@donuts.com', '1956-05-12', 6),
('Harry', 'Potter', '+44-12-3456', 'harry@hogwarts.edu', '1980-07-31', 7),
('Mehmet', 'Yilmaz', '+90-532-111', 'mehmety@mail.com', '1990-02-20', 8),
('Ayse', 'Kaya', '+90-533-222', 'ayse.k@mail.com', '1995-08-15', 9),
('Can', 'Demir', '+90-535-333', 'can.d@mail.com', '1988-11-30', 10);

-- 3. Supplier Data
INSERT INTO Supplier (company_name, phone_number) VALUES
('Bayer', '555-1111'),
('Pfizer', '555-2222'),
('Johnson & Johnson', '555-3333'),
('Novartis', '555-4444'),
('Roche', '555-5555'),
('Sanofi', '555-6666'),
('GlaxoSmithKline', '555-7777'),
('Merck', '555-8888'),
('AbbVie', '555-9999'),
('AstraZeneca', '555-0000');

-- 4. Product Data
INSERT INTO Product (product_name, unit_price, stock_quantity, category, expiry_date, supplier_id) VALUES
('Ibuprofen 400mg', 10.50, 200, 'Pain Relief', '2026-12-31', 1),
('Amoxicillin 500mg', 25.00, 50, 'Antibiotics', '2025-06-30', 2),
('Vitamin C 1000mg', 15.75, 100, 'Supplement', '2027-01-15', 1),
('Cough Syrup', 12.00, 75, 'Cold & Flu', '2025-11-20', 1),
('Dermatix Gel', 45.00, 30, 'Skin Care', '2026-08-15', 2),
('Paracetamol 500mg', 8.50, 500, 'Pain Relief', '2026-10-10', 3),
('Aspirin 100mg', 12.00, 300, 'Blood Thinner', '2027-05-05', 1),
('Bandages', 5.00, 1000, 'First Aid', '2030-01-01', 3),
('Antiseptic Spray', 22.00, 150, 'First Aid', '2026-03-20', 4),
('Insulin Pen', 150.00, 40, 'Diabetes', '2025-09-01', 5);

-- 5. Employee Data
INSERT INTO Employee (first_name, last_name, title) VALUES
('Ahmet', 'Yilmaz', 'Pharmacist'),
('Ayse', 'Demir', 'Cashier'),
('Fatma', 'Celik', 'Pharmacist'),
('Ali', 'Koc', 'Pharmacist'),
('Veli', 'Ozturk', 'Intern'),
('Zeynep', 'Arslan', 'Cashier'),
('Mustafa', 'Sahin', 'Stock Manager'),
('Elif', 'Polat', 'Pharmacist'),
('Burak', 'Gunes', 'Cleaner'),
('Selin', 'Yildiz', 'Pharmacist');

-- 6. Prescription Data
INSERT INTO Prescription (patient_id, doctor_name, issue_date) VALUES
(1, 'Dr. House', '2024-09-01'),
(2, 'Dr. Strange', '2024-09-02'),
(3, 'Dr. Watson', '2024-09-03'),
(4, 'Dr. Who', '2024-09-04'),
(5, 'Dr. House', '2024-09-05'),
(6, 'Dr. Hibbert', '2024-09-06'),
(7, 'Dr. Strange', '2024-09-07'),
(8, 'Dr. Oz', '2024-09-08'),
(9, 'Dr. Oz', '2024-09-09'),
(10, 'Dr. Quinn', '2024-09-10');

-- 7. Prescription Item Data
INSERT INTO Prescription_Item (prescription_id, product_id, dosage) VALUES
(1, 1, 'Twice a day'),
(2, 2, 'Once a day'),
(3, 3, 'Every morning'),
(4, 4, 'Before sleep'),
(5, 6, 'When needed'),
(6, 7, 'Once daily'),
(7, 10, 'Before meals'),
(8, 2, 'Twice a day'),
(9, 5, 'Apply topically'),
(10, 1, 'Three times a day');

-- 8. Sale Data
INSERT INTO Sale (patient_id, employee_id, sale_date, total_amount, payment_method) VALUES
(1, 1, '2024-10-05 10:30:00', 36.75, 'Credit Card'),
(2, 2, '2024-10-06 14:45:00', 25.00, 'Cash'),
(3, 1, '2024-10-07 09:15:00', 150.00, 'Cash'),
(4, 1, '2024-10-08 16:20:00', 12.00, 'Credit Card'),
(5, 3, '2024-10-09 11:00:00', 8.50, 'Cash'),
(6, 4, '2024-10-09 13:30:00', 17.00, 'Credit Card'),
(7, 2, '2024-10-10 10:00:00', 44.00, 'Cash'),
(8, 1, '2024-10-10 15:15:00', 150.00, 'Insurance'),
(9, 6, '2024-10-11 09:45:00', 5.00, 'Cash'),
(10, 4, '2024-10-11 16:50:00', 22.00, 'Credit Card');

-- 9. Sale Detail Data
INSERT INTO Sale_Detail (sale_id, product_id, quantity, unit_price) VALUES
(1, 4, 1, 12.00),
(1, 2, 1, 25.00),
(2, 2, 1, 25.00),
(3, 10, 1, 150.00),
(4, 4, 1, 12.00),
(5, 6, 1, 8.50),
(6, 6, 2, 8.50),
(7, 9, 2, 22.00),
(8, 10, 1, 150.00),
(9, 8, 1, 5.00);

-- 10. Invoice Data
INSERT INTO Invoice (sale_id, invoice_date, tax_amount) VALUES
(1, '2024-10-05', 5.50),
(2, '2024-10-06', 3.75),
(3, '2024-10-07', 22.50),
(4, '2024-10-08', 1.80),
(5, '2024-10-09', 1.25),
(6, '2024-10-09', 2.55),
(7, '2024-10-10', 6.60),
(8, '2024-10-10', 22.50),
(9, '2024-10-11', 0.75),
(10, '2024-10-11', 3.30);