Project Deliverable #5: Sample DML Queries
These queries answer business related questions relevant to the operation of GrowFlower Farms. The DML population script populates the farm’s database.
SELECT Statement Queries
The following six questions are not originally from our project proposal since we asked the wrong questions; however the proposal will be updated with the below questions when we submit our deliverable.
Q1. Which farmers are contacts with which vendors?
A useful question for finding out which farmers are responsible for transactions with what vendors.
Query
The results are displayed as unique pairs of farmer name and vendor name and they are ordered by the farmer name.
SELECT F.Name AS FarmerName, V.Name AS VendorName
FROM Contacts C
JOIN Farmer F ON (C.FarmerID=F.FarmerID)
JOIN Vendor V ON (C.VendorID=V.VendorID)
ORDER BY FarmerName;
Resultset
Both Brian Smith and Helen Hay maintain contact with Lauren Wentworth.
+--------------+------------------+
| FarmerName | VendorName |
+--------------+------------------+
| Brian Smith | Lauren Wentworth |
| Chris Slover | Richard Young |
| Daisy Dukes | Henrey Mullen |
| Helen Hay | Lauren Wentworth |
| Marcus Ford | george Doughtry |
| Nate Litvak | Henrey Mullen |
+--------------+------------------+
Q2. Which farmers are responsible for which fields?
A useful question for finding out where each farmer should be working.
Query
The results are displayed as unique pairs of farmer name and field location. The plant currently on the particular field (if applicable) is listed next to its respective field location. Results are ordered by the farmer name.
SELECT Farmer.Name AS FarmerName, Field.Location AS FieldLocation, Field.CurrentPlant
FROM IsResponsibleFor I
JOIN Farmer ON (Farmer.FarmerID=I.FarmerID)
JOIN Field ON (Field.Location=I.FieldLocation)
ORDER BY FarmerName;
Resultset
Brian Smith is responsible for two fields.
+--------------+---------------+--------------+
| FarmerName | FieldLocation | CurrentPlant |
+--------------+---------------+--------------+
| Brian Smith | 10W25N | Tulip |
| Brian Smith | 30W30S | Rose |
| Chris Slover | 20E30N | Sunflower |
| Helen Hay | 20E25S | Daffodil |
| Marcus Ford | 5E5S | Violet |
+--------------+---------------+--------------+
Q3. What harvests were sold in 2013 and to which distributors?
A useful question for finding out the farm’s total revenue of the year.
Query
The results are displayed as unique pairs of farmer name and vendor name and they are ordered by the farmer name.
SELECT D.Name AS DistributorName, H.HarvestDate, H.FieldLocation,
H.PlantType, H.PlantCount, H.PriceSold
FROM Distributor D
JOIN Harvest H ON (D.DistributorID=H.DistributorSold AND
H.DistributorSold IS NOT NULL)
ORDER BY D.Name;
Resultset
Only one purchase has been recorded so far.
+-----------------------+-------------+---------------+-----------+------------+-----------+
| DistributorName | HarvestDate | FieldLocation | PlantType | PlantCount | PriceSold |
+-----------------------+-------------+---------------+-----------+------------+-----------+
| Plants Plants Plants! | 2013-10-11 | 5E5S | Violet | 150 | 5000 |
+-----------------------+-------------+---------------+-----------+------------+-----------+
Q4. What materials has the farm purchased and from whom?
The question aims to find out all material purchases made by the farm; useful for knowing the total cost of all materials, the date when they were each purchased and from which vendor, just in case the farmers need to follow up on those purchases.
Query
The results are displayed by vendor name and contain the fields material type, quantity, price, and date purchased.
SELECT V.Name, MB.MaterialType AS Material, MB.Quantity, MB.Price, MB.DatePurchased
FROM Material_Bundle MB
JOIN Vendor V ON (V.VendorId=MB.VendorID )
JOIN Material M ON (MB.Material.MaterialType)
ORDER BY V.Name;
Resultset
+------------------+------------+----------+-------+---------------+
| Name | Material | Quantity | Price | DatePurchased |
+------------------+------------+----------+-------+---------------+
| george Doughtry | Gas Tank | 5 | 25000 | 2000-11-12 |
| Henrey Mullen | Pesticide | 500 | 10000 | 2000-11-12 |
| Henrey Mullen | Tractor | 1 | 5000 | 2000-11-12 |
| Lauren Wentworth | Plow | 1 | 10000 | 2000-11-12 |
| Lauren Wentworth | Plow | 1 | 10000 | 2012-05-20 |
| Richard Young | Fertilizer | 100 | 2000 | 2000-11-12 |
+------------------+------------+----------+-------+---------------+
Q5. What seeds did the farm purchase in 2012 and from whom?
A similar question to the previous, except this one aims to find the total purchases of seeds in the specific year 2012. Useful for finding the total cost and quantity of seeds purchased in the year 2012, as well as the vendor and date purchased for each purchase, in case the farmers want to follow up with the vendors on what they bought.
Query
The results are displayed by Vendor name and contain fields seed type, quantity, price, and date purchased.
SELECT V.Name, SB.SeedType AS Seed, SB.Quantity, SB.Price, SB.DatePurchased
FROM Seed_Bundle SB
JOIN Vendor V ON (V.VendorId=SB.VendorID
AND SB.DatePurchased < '2013-01-01'
AND SB.DatePurchased > '2011-12-31')
JOIN Seed S ON (SB.Seed.SeedType)
ORDER BY V.Name;
Resultset
+------------------+-----------+----------+-------+---------------+
| Name | Seed | Quantity | Price | DatePurchased |
+------------------+-----------+----------+-------+---------------+
| george Doughtry | Rose | 2000 | 100 | 2012-07-18 |
| george Doughtry | Poppy | 5000 | 450 | 2012-08-18 |
| Henrey Mullen | Violet | 20000 | 600 | 2012-06-18 |
| Lauren Wentworth | Sunflower | 1000 | 50 | 2012-04-18 |
| Lauren Wentworth | Tulip | 3000 | 200 | 2012-05-18 |
+------------------+-----------+----------+-------+---------------+
Q6. Which farmers were responsible for each harvest sold?
This question aims to find out who is accountable for the sale of each harvest.
Query
The results are displayed by farmer name and include fields distributor name, field location of harvest, plant type of harvest, and purchase date of harvest.
SELECT F.Name, D.Name, H.FieldLocation, H.PlantType, H.PurchaseDate
FROM Harvest H
JOIN Distributor D ON (
H.DistributorSold IS NOT NULL AND H.DistributorSold=D.DistributorID)
JOIN Farmer F ON (F.FarmerID = D.FarmerContact)
ORDER BY F.Name;
Resultset
+-------------+-----------------------+---------------+-----------+--------------+
| Name | Name | FieldLocation | PlantType | PurchaseDate |
+-------------+-----------------------+---------------+-----------+--------------+
| Daisy Dukes | Plants Plants Plants! | 5E5S | Violet | 2013-10-15 |
+-------------+-----------------------+---------------+-----------+--------------+
These queries answer business related questions relevant to the operation of GrowFlower Farms. The DML population script populates the farm’s database.
SELECT Statement Queries
The following six questions are not originally from our project proposal since we asked the wrong questions; however the proposal will be updated with the below questions when we submit our deliverable.
Q1. Which farmers are contacts with which vendors?
A useful question for finding out which farmers are responsible for transactions with what vendors.
Query
The results are displayed as unique pairs of farmer name and vendor name and they are ordered by the farmer name.
SELECT F.Name AS FarmerName, V.Name AS VendorName
FROM Contacts C
JOIN Farmer F ON (C.FarmerID=F.FarmerID)
JOIN Vendor V ON (C.VendorID=V.VendorID)
ORDER BY FarmerName;
Resultset
Both Brian Smith and Helen Hay maintain contact with Lauren Wentworth.
+--------------+------------------+
| FarmerName | VendorName |
+--------------+------------------+
| Brian Smith | Lauren Wentworth |
| Chris Slover | Richard Young |
| Daisy Dukes | Henrey Mullen |
| Helen Hay | Lauren Wentworth |
| Marcus Ford | george Doughtry |
| Nate Litvak | Henrey Mullen |
+--------------+------------------+
Q2. Which farmers are responsible for which fields?
A useful question for finding out where each farmer should be working.
Query
The results are displayed as unique pairs of farmer name and field location. The plant currently on the particular field (if applicable) is listed next to its respective field location. Results are ordered by the farmer name.
SELECT Farmer.Name AS FarmerName, Field.Location AS FieldLocation, Field.CurrentPlant
FROM IsResponsibleFor I
JOIN Farmer ON (Farmer.FarmerID=I.FarmerID)
JOIN Field ON (Field.Location=I.FieldLocation)
ORDER BY FarmerName;
Resultset
Brian Smith is responsible for two fields.
+--------------+---------------+--------------+
| FarmerName | FieldLocation | CurrentPlant |
+--------------+---------------+--------------+
| Brian Smith | 10W25N | Tulip |
| Brian Smith | 30W30S | Rose |
| Chris Slover | 20E30N | Sunflower |
| Helen Hay | 20E25S | Daffodil |
| Marcus Ford | 5E5S | Violet |
+--------------+---------------+--------------+
Q3. What harvests were sold in 2013 and to which distributors?
A useful question for finding out the farm’s total revenue of the year.
Query
The results are displayed as unique pairs of farmer name and vendor name and they are ordered by the farmer name.
SELECT D.Name AS DistributorName, H.HarvestDate, H.FieldLocation,
H.PlantType, H.PlantCount, H.PriceSold
FROM Distributor D
JOIN Harvest H ON (D.DistributorID=H.DistributorSold AND
H.DistributorSold IS NOT NULL)
ORDER BY D.Name;
Resultset
Only one purchase has been recorded so far.
+-----------------------+-------------+---------------+-----------+------------+-----------+
| DistributorName | HarvestDate | FieldLocation | PlantType | PlantCount | PriceSold |
+-----------------------+-------------+---------------+-----------+------------+-----------+
| Plants Plants Plants! | 2013-10-11 | 5E5S | Violet | 150 | 5000 |
+-----------------------+-------------+---------------+-----------+------------+-----------+
Q4. What materials has the farm purchased and from whom?
The question aims to find out all material purchases made by the farm; useful for knowing the total cost of all materials, the date when they were each purchased and from which vendor, just in case the farmers need to follow up on those purchases.
Query
The results are displayed by vendor name and contain the fields material type, quantity, price, and date purchased.
SELECT V.Name, MB.MaterialType AS Material, MB.Quantity, MB.Price, MB.DatePurchased
FROM Material_Bundle MB
JOIN Vendor V ON (V.VendorId=MB.VendorID )
JOIN Material M ON (MB.Material.MaterialType)
ORDER BY V.Name;
Resultset
+------------------+------------+----------+-------+---------------+
| Name | Material | Quantity | Price | DatePurchased |
+------------------+------------+----------+-------+---------------+
| george Doughtry | Gas Tank | 5 | 25000 | 2000-11-12 |
| Henrey Mullen | Pesticide | 500 | 10000 | 2000-11-12 |
| Henrey Mullen | Tractor | 1 | 5000 | 2000-11-12 |
| Lauren Wentworth | Plow | 1 | 10000 | 2000-11-12 |
| Lauren Wentworth | Plow | 1 | 10000 | 2012-05-20 |
| Richard Young | Fertilizer | 100 | 2000 | 2000-11-12 |
+------------------+------------+----------+-------+---------------+
Q5. What seeds did the farm purchase in 2012 and from whom?
A similar question to the previous, except this one aims to find the total purchases of seeds in the specific year 2012. Useful for finding the total cost and quantity of seeds purchased in the year 2012, as well as the vendor and date purchased for each purchase, in case the farmers want to follow up with the vendors on what they bought.
Query
The results are displayed by Vendor name and contain fields seed type, quantity, price, and date purchased.
SELECT V.Name, SB.SeedType AS Seed, SB.Quantity, SB.Price, SB.DatePurchased
FROM Seed_Bundle SB
JOIN Vendor V ON (V.VendorId=SB.VendorID
AND SB.DatePurchased < '2013-01-01'
AND SB.DatePurchased > '2011-12-31')
JOIN Seed S ON (SB.Seed.SeedType)
ORDER BY V.Name;
Resultset
+------------------+-----------+----------+-------+---------------+
| Name | Seed | Quantity | Price | DatePurchased |
+------------------+-----------+----------+-------+---------------+
| george Doughtry | Rose | 2000 | 100 | 2012-07-18 |
| george Doughtry | Poppy | 5000 | 450 | 2012-08-18 |
| Henrey Mullen | Violet | 20000 | 600 | 2012-06-18 |
| Lauren Wentworth | Sunflower | 1000 | 50 | 2012-04-18 |
| Lauren Wentworth | Tulip | 3000 | 200 | 2012-05-18 |
+------------------+-----------+----------+-------+---------------+
Q6. Which farmers were responsible for each harvest sold?
This question aims to find out who is accountable for the sale of each harvest.
Query
The results are displayed by farmer name and include fields distributor name, field location of harvest, plant type of harvest, and purchase date of harvest.
SELECT F.Name, D.Name, H.FieldLocation, H.PlantType, H.PurchaseDate
FROM Harvest H
JOIN Distributor D ON (
H.DistributorSold IS NOT NULL AND H.DistributorSold=D.DistributorID)
JOIN Farmer F ON (F.FarmerID = D.FarmerContact)
ORDER BY F.Name;
Resultset
+-------------+-----------------------+---------------+-----------+--------------+
| Name | Name | FieldLocation | PlantType | PurchaseDate |
+-------------+-----------------------+---------------+-----------+--------------+
| Daisy Dukes | Plants Plants Plants! | 5E5S | Violet | 2013-10-15 |
+-------------+-----------------------+---------------+-----------+--------------+