CREATE TABLE beekeepers (
beekeeper_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT UNIQUE,
phone TEXT,
address TEXT,
years_experience INTEGER
);
CREATE TABLE apiaries (
apiary_id INTEGER PRIMARY KEY AUTOINCREMENT,
beekeeper_id INTEGER NOT NULL,
name TEXT NOT NULL,
location TEXT,
FOREIGN KEY (beekeeper_id) REFERENCES beekeepers(beekeeper_id)
);
-- note: the last inspected date of a hive might not be up-to-date!
CREATE TABLE hives (
hive_id INTEGER PRIMARY KEY AUTOINCREMENT,
apiary_id INTEGER NOT NULL,
name TEXT NOT NULL,
install_date TEXT,
queen_marked BOOLEAN,
last_inspection_date TEXT,
FOREIGN KEY (apiary_id) REFERENCES apiaries(apiary_id)
);
CREATE TABLE inspections (
inspection_id INTEGER PRIMARY KEY AUTOINCREMENT,
hive_id INTEGER NOT NULL,
inspection_date TEXT NOT NULL,
queen_seen BOOLEAN,
egg_pattern_stars INTEGER, -- subjective 1-5 rating
temperament_stars INTEGER, -- 1-5 rating
disease_signs BOOLEAN,
notes TEXT,
FOREIGN KEY (hive_id) REFERENCES hives(hive_id)
);
CREATE TABLE diseases (
disease_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL,
treatment TEXT
);
CREATE TABLE inspection_diseases (
inspection_id INTEGER NOT NULL,
disease_id INTEGER NOT NULL,
PRIMARY KEY (inspection_id, disease_id),
FOREIGN KEY (inspection_id) REFERENCES inspections(inspection_id),
FOREIGN KEY (disease_id) REFERENCES diseases(disease_id)
);
CREATE TABLE feedings (
feeding_id INTEGER PRIMARY KEY AUTOINCREMENT,
hive_id INTEGER NOT NULL,
feeding_date TEXT NOT NULL,
syrup_type TEXT,
amount_gallons REAL,
FOREIGN KEY (hive_id) REFERENCES hives(hive_id)
);
CREATE TABLE mite_counts (
count_id INTEGER PRIMARY KEY AUTOINCREMENT,
hive_id INTEGER NOT NULL,
count_date TEXT NOT NULL,
sampling_method TEXT,
phoretic_mites_per_100 REAL,
treatment_applied TEXT,
FOREIGN KEY (hive_id) REFERENCES hives(hive_id)
);
CREATE TABLE honey_harvests (
harvest_id INTEGER PRIMARY KEY AUTOINCREMENT,
hive_id INTEGER NOT NULL,
harvest_date TEXT NOT NULL,
pounds_harvested REAL,
varieties TEXT,
FOREIGN KEY (hive_id) REFERENCES hives(hive_id)
);
INSERT INTO beekeepers (first_name, last_name, email, phone, address, years_experience)
VALUES
('Emma', 'Wright', 'emma.wright@example.com', '+1 (123) 456-7890', '123 Main St, Anytown, USA', 5),
('Liam', 'Anderson', 'liam.anderson@example.com', '+1 (987) 654-3210', '456 Bee Lane, Beetown, USA', 10),
('Olivia', 'Miller', 'olivia.miller@example.com', '+1 (555) 123-4567', '789 Honey Rd, Hiveville, USA', 3),
('Noah', 'Johnson', 'noah.johnson@example.com', '+1 (111) 222-3333', '321 Apiary Ave, Beesburg, USA', 7),
('Ava', 'Williams', 'ava.williams@example.com', '+1 (444) 555-6666', '910 Pollen St, Nectarville, USA', 2);
INSERT INTO apiaries (beekeeper_id, name, location)
VALUES
(1, 'Honey Haven', '77 Sunshine Meadow, Happy Hive Valley'),
(2, 'Bee Utopia', '9 Honey Lane, Apis Acre'),
(3, 'Pollen Paradise', '33 Blossom Road, Flora Fantasia'),
(4, 'Golden Nectar Garden', '55 Brookside, Honeycomb Hollow'),
(5, 'Buzzing Bee Sanctuary', '11 Wildflower Trail, Bee Enchanted Forest');
INSERT INTO hives (apiary_id, name, install_date, queen_marked, last_inspection_date)
VALUES
(1, 'Sunshine Sweetness', '2023-04-10', true, '2023-04-12'),
(1, 'Moonlight Magic', '2023-04-11', false, NULL),
(1, 'Morning Dew Delight', '2023-04-12', true, '2023-04-13'),
(2, 'Utopias Buzzing Belle', '2023-04-09', true, '2023-04-11'),
(2, 'Honeycomb Hideout', '2023-04-10', false, '2023-04-12'),
(2, 'Pollen Palace', '2023-04-11', true, NULL),
(2, 'Bee Happy Haven', '2023-04-12', false, '2023-04-13'),
(3, 'Blossom Belle', '2023-04-12', true, NULL),
(3, 'Flower Fantasia', '2023-04-13', false, '2023-04-14'),
(3, 'Pollen Party', '2023-04-14', true, '2023-04-15'),
(3, 'Floras Fantasy', '2023-04-15', false, NULL),
(4, 'Golden Nectar Gem', '2023-04-10', true, '2023-04-11'),
(4, 'Honeycomb Hollow Hideaway', '2023-04-11', false, '2023-04-12'),
(4, 'Nectar Nook', '2023-04-12', true, NULL),
(5, 'Wildflower Wonder', '2023-04-11', true, '2023-04-12'),
(5, 'Buzzing Bliss', '2023-04-12', false, NULL),
(5, 'Enchanted Forest Hive', '2023-04-13', true, '2023-04-14'),
(5, 'Bee Magical Manor', '2023-04-14', false, '2023-04-15');
INSERT INTO inspection_diseases (inspection_id, disease_id)
VALUES
(1, 3),
(4, 1),
(6, 7),
(8, 8),
(10, 2),
(12, 9);
INSERT INTO inspections (hive_id, inspection_date, queen_seen, egg_pattern_stars, temperament_stars, disease_signs, notes)
VALUES
(1, '2023-04-15', true, 5, 4, false, 'Hive looks healthy and active.'),
(1, '2023-04-20', true, 4, 3, false, 'Some signs of pest presence, will monitor.'),
(2, '2023-04-18', false, 3, 4, true, 'Suspect mild disease, will treat and re-inspect.'),
(2, '2023-04-23', true, 5, 5, false, 'Hive is thriving, no issues detected.'),
(3, '2023-04-16', true, 4, 4, false, NULL),
(3, '2023-04-21', true, 5, 4, false, 'Good egg pattern, healthy queen.'),
(4, '2023-04-19', false, 4, 3, true, 'Will treat for potential disease.'),
(4, '2023-04-24', true, 5, 4, false, 'Hive is recovering well.'),
(5, '2023-04-17', true, 5, 5, false, 'Excellent temperament and egg pattern.'),
(5, '2023-04-22', true, 4, 4, false, 'No issues detected, healthy hive.'),
(6, '2023-04-20', false, 3, 4, false, 'Queen not seen, will re-inspect.'),
(6, '2023-04-25', true, 4, 4, false, 'Queen spotted, hive looks active.'),
(7, '2023-04-18', true, 4, 3, false, 'Some signs of aggression.'),
(7, '2023-04-23', true, 5, 4, false, 'Hive is doing well, no concerns.'),
(8, '2023-04-21', false, 4, 4, false, 'Will inspect again for queen.'),
(8, '2023-04-26', true, 5, 5, false, 'Queen seen, hive is healthy.'),
(9, '2023-04-19', true, 5, 4, false, NULL),
(9, '2023-04-24', true, 4, 4, false, 'No issues, healthy hive.'),
(10, '2023-04-22', false, 4, 3, true, 'Suspect disease, will treat.'),
(10, '2023-04-27', true, 5, 4, false, 'Hive is recovering, will monitor.'),
(11, '2023-04-20', true, 4, 5, false, 'Excellent temperament.'),
(11, '2023-04-25', true, 5, 5, false, 'Hive is thriving, no concerns.'),
(12, '2023-04-23', false, 4, 4, false, 'Will re-inspect for queen.'),
(12, '2023-04-28', true, 5, 4, false, 'Queen spotted, hive looks healthy.');
INSERT INTO diseases (name, treatment)
VALUES
('American Foulbrood', 'Antibiotic treatment, destruction of infected hives'),
('European Foulbrood', 'Antibiotic treatment, feeding affected colonies'),
('Chalkbrood', 'Improve nutrition, maintain strong colonies'),
('Sacbrood', 'Sanitation, destroy infected larvae'),
('Nosemosis', 'Fumagillin treatment, improve nutrition'),
('Wax Moth Infestation', 'Remove and destroy infested comb, improve ventilation'),
('Tracheal Mites', 'Miticide treatment, requeening'),
('Varroa Mites', 'Chemical and mechanical control methods, integrated pest management'),
('Acute Bee Paralysis Virus', 'Sanitation, improve nutrition, requeening'),
('Chronic Bee Paralysis Virus', 'Sanitation, improve nutrition, reduce stress');
INSERT INTO feedings (hive_id, feeding_date, syrup_type, amount_gallons)
VALUES
(1, '2023-04-16', 'Sugar Syrup', 2.5),
(2, '2023-04-17', 'High Fructose Corn Syrup', 3.0),
(3, '2023-04-18', 'Sugar Syrup', 1.5),
(4, '2023-04-19', 'Honey B Healthy', 2.0),
(5, '2023-04-20', 'Sugar Syrup', 2.5),
(6, '2023-04-21', 'High Fructose Corn Syrup', 3.0),
(7, '2023-04-22', 'Sugar Syrup', 2.0),
(8, '2023-04-23', 'Honey B Healthy', 1.5),
(9, '2023-04-24', 'Sugar Syrup', 2.5),
(10, '2023-04-25', 'High Fructose Corn Syrup', 3.0),
(11, '2023-04-26', 'Sugar Syrup', 2.0),
(12, '2023-04-27', 'Honey B Healthy', 1.5),
(13, '2023-04-28', 'Sugar Syrup', 2.5),
(14, '2023-04-29', 'High Fructose Corn Syrup', 3.0),
(15, '2023-04-30', 'Sugar Syrup', 2.0),
(16, '2023-05-01', 'Honey B Healthy', 1.5),
(17, '2023-05-02', 'Sugar Syrup', 2.5),
(18, '2023-05-03', 'High Fructose Corn Syrup', 3.0),
(19, '2023-05-04', 'Sugar Syrup', 2.0),
(20, '2023-05-05', 'Honey B Healthy', 1.5);
INSERT INTO mite_counts (hive_id, count_date, sampling_method, phoretic_mites_per_100, treatment_applied)
VALUES
(1, '2023-04-15', 'Alcohol Wash', 3.5, 'Miticide Treatment'),
(3, '2023-04-18', 'Powdered Sugar Roll', 2.0, 'No Treatment'),
(5, '2023-04-20', 'Alcohol Wash', 4.5, 'Miticide Treatment and Requeening'),
(7, '2023-04-22', 'Powdered Sugar Roll', 1.5, 'No Treatment'),
(9, '2023-04-24', 'Alcohol Wash', 3.0, 'Miticide Treatment'),
(11, '2023-04-26', 'Powdered Sugar Roll', 2.5, 'No Treatment'),
(13, '2023-04-28', 'Alcohol Wash', 4.0, 'Miticide Treatment and Hive Management'),
(15, '2023-04-30', 'Powdered Sugar Roll', 1.0, 'No Treatment'),
(17, '2023-05-02', 'Alcohol Wash', 3.5, 'Miticide Treatment'),
(19, '2023-05-04', 'Powdered Sugar Roll', 2.0, 'No Treatment');
INSERT INTO honey_harvests (hive_id, harvest_date, pounds_harvested, varieties)
VALUES
(1, '2023-05-01', 75.5, 'Wildflower, Clover'),
(2, '2023-05-02', 60.0, 'Orange Blossom, Eucalyptus'),
(3, '2023-05-03', 85.2, 'Alfalfa, Wildflower'),
(4, '2023-05-04', 55.0, 'Clover, Goldenrod'),
(5, '2023-05-05', 70.5, 'Eucalyptus, Wildflower'),
(6, '2023-05-06', 65.0, 'Alfalfa, Clover'),
(7, '2023-05-07', 80.0, 'Wildflower, Goldenrod'),
(8, '2023-05-08', 50.5, 'Clover, Eucalyptus'),
(9, '2023-05-09', 75.0, 'Orange Blossom, Wildflower'),
(10, '2023-05-10', 60.5, 'Alfalfa, Clover'),
(11, '2023-05-11', 85.0, 'Wildflower, Goldenrod'),
(12, '2023-05-12', 55.5, 'Clover, Eucalyptus'),
(13, '2023-05-13', 70.0, 'Eucalyptus, Wildflower'),
(14, '2023-05-14', 65.5, 'Alfalfa, Clover'),
(15, '2023-05-15', 80.5, 'Wildflower, Goldenrod'),
(16, '2023-05-16', 50.0, 'Clover, Orange Blossom'),
(17, '2023-05-17', 75.5, 'Wildflower, Alfalfa'),
(18, '2023-05-18', 60.0, 'Eucalyptus, Clover'),
(19, '2023-05-19', 85.0, 'Goldenrod, Wildflower'),
(20, '2023-05-20', 55.5, 'Clover, Alfalfa'),
(21, '2023-05-21', 70.5, 'Wildflower, Orange Blossom'),
(22, '2023-05-22', 65.0, 'Clover, Eucalyptus'),
(23, '2023-05-23', 80.0, 'Alfalfa, Wildflower'),
(24, '2023-05-24', 55.5, 'Goldenrod, Clover'),
(25, '2023-05-25', 75.0, 'Wildflower, Eucalyptus'),
(26, '2023-05-26', 60.5, 'Clover, Alfalfa'),
(27, '2023-05-27', 85.0, 'Orange Blossom, Wildflower'),
(28, '2023-05-28', 50.0, 'Clover, Goldenrod'),
(29, '2023-05-29', 75.5, 'Wildflower, Eucalyptus'),
(30, '2023-05-30', 60.0, 'Alfalfa, Clover');
-- beekeeper prod
-- WITH BeekeeperProductivity AS (
-- SELECT
-- b.beekeeper_id,
-- b.first_name AS Beekeeper_Name,
-- SUM(hh.pounds_harvested) AS Total_Honey_Harvested,
-- COUNT(DISTINCT h.hive_id) AS Hives_Managed
-- FROM beekeepers b
-- JOIN apiaries a ON b.beekeeper_id = a.beekeeper_id
-- JOIN hives h ON a.apiary_id = h.apiary_id
-- JOIN honey_harvests hh ON h.hive_id = hh.hive_id
-- GROUP BY b.beekeeper_id
-- ),
-- HiveHealth AS (
-- SELECT
-- b.beekeeper_id,
-- AVG(i.temperament_stars) AS Avg_Temperament,
-- AVG(i.egg_pattern_stars) AS Avg_Egg_Pattern
-- FROM beekeepers b
-- JOIN apiaries a ON b.beekeeper_id = a.beekeeper_id
-- JOIN hives h ON a.apiary_id = h.apiary_id
-- JOIN inspections i ON h.hive_id = i.hive_id
-- WHERE i.queen_seen = TRUE
-- GROUP BY b.beekeeper_id
-- )
-- SELECT
-- bp.Beekeeper_Name,
-- bp.Total_Honey_Harvested,
-- bp.Hives_Managed,
-- COALESCE(hh.Avg_Temperament, 0) AS Avg_Temperament,
-- COALESCE(hh.Avg_Egg_Pattern, 0) AS Avg_Egg_Pattern
-- FROM BeekeeperProductivity bp
-- LEFT JOIN HiveHealth hh ON bp.beekeeper_id = hh.beekeeper_id
-- ORDER BY bp.Beekeeper_Name;
-- WITH TreatmentData AS (
-- SELECT
-- mc.hive_id,
-- mc.count_date,
-- mc.phoretic_mites_per_100,
-- mc.treatment_applied,
-- LEAD(mc.phoretic_mites_per_100, 1) OVER (PARTITION BY mc.hive_id ORDER BY mc.count_date) AS Post_Treatment_Count
-- FROM mite_counts mc
-- ),
-- InspectionHealth AS (
-- SELECT
-- hive_id,
-- inspection_date,
-- AVG(egg_pattern_stars) AS Avg_Egg_Pattern,
-- AVG(temperament_stars) AS Avg_Temperament
-- FROM inspections
-- GROUP BY hive_id, inspection_date
-- )
-- SELECT
-- td.hive_id,
-- td.count_date AS Treatment_Date,
-- td.phoretic_mites_per_100 AS Pre_Treatment_Count,
-- td.Post_Treatment_Count,
-- td.treatment_applied,
-- ih.Avg_Egg_Pattern,
-- ih.Avg_Temperament
-- FROM TreatmentData td
-- JOIN InspectionHealth ih ON td.hive_id = ih.hive_id AND ih.inspection_date > td.count_date
-- WHERE td.Post_Treatment_Count IS NOT NULL
-- ORDER BY td.hive_id, td.count_date;
-- WITH BeekeeperPerformance AS (
-- SELECT
-- b.beekeeper_id,
-- b.first_name || ' ' || b.last_name AS Beekeeper_Name,
-- strftime('%Y', i.inspection_date) AS Year,
-- COUNT(DISTINCT id.disease_id) AS Diseases_Count,
-- AVG(hh.pounds_harvested) AS Avg_Honey_Production
-- FROM beekeepers b
-- JOIN apiaries a ON b.beekeeper_id = a.beekeeper_id
-- JOIN hives h ON a.apiary_id = h.apiary_id
-- JOIN inspections i ON h.hive_id = i.hive_id
-- LEFT JOIN inspection_diseases id ON i.inspection_id = id.inspection_id
-- JOIN honey_harvests hh ON h.hive_id = hh.hive_id
-- GROUP BY b.beekeeper_id, Year
-- ),
-- YearlyTrends AS (
-- SELECT
-- bp.Beekeeper_Name,
-- bp.Year,
-- LAG(bp.Diseases_Count, 1) OVER (PARTITION BY bp.Beekeeper_Name ORDER BY bp.Year) AS Last_Year_Diseases,
-- bp.Diseases_Count,
-- LAG(bp.Avg_Honey_Production, 1) OVER (PARTITION BY bp.Beekeeper_Name ORDER BY bp.Year) AS Last_Year_Production,
-- bp.Avg_Honey_Production
-- FROM BeekeeperPerformance bp
-- )
-- SELECT *
-- FROM YearlyTrends
-- WHERE Year = '2023'; -- Analyzing the latest year trends against the previous year
-- WITH HiveIssues AS (
-- SELECT
-- i.hive_id,
-- COUNT(*) AS inspection_count,
-- AVG(i.egg_pattern_stars) AS avg_egg_pattern,
-- AVG(i.temperament_stars) AS avg_temperament,
-- SUM(CASE WHEN i.disease_signs THEN 1 ELSE 0 END) AS disease_incidents,
-- SUM(CASE WHEN mc.phoretic_mites_per_100 > 3 THEN 1 ELSE 0 END) AS high_mite_counts
-- FROM inspections i
-- LEFT JOIN mite_counts mc ON i.hive_id = mc.hive_id AND i.inspection_date = mc.count_date
-- GROUP BY i.hive_id
-- )
-- SELECT
-- hive_id,
-- RANK() OVER (ORDER BY avg_temperament DESC, avg_egg_pattern, disease_incidents DESC, high_mite_counts DESC) AS issue_rank
-- FROM HiveIssues
-- ORDER BY issue_rank
-- LIMIT 1;
WITH FeedingImpact AS (
SELECT
h.hive_id,
f.syrup_type,
AVG(i.egg_pattern_stars) AS avg_egg_pattern
FROM feedings f
JOIN hives h ON f.hive_id = h.hive_id
JOIN inspections i ON h.hive_id = i.hive_id AND i.inspection_date > f.feeding_date
GROUP BY h.hive_id, f.syrup_type
)
SELECT
syrup_type,
AVG(avg_egg_pattern) AS overall_avg_egg_pattern
FROM FeedingImpact
GROUP BY syrup_type
ORDER BY overall_avg_egg_pattern DESC;
To embed this program on your website, copy the following code and paste it into your website's HTML: