DEV Community

Seelam Anusha
Seelam Anusha

Posted on

My First SQL Project: Crop Production Analysis

#sql #datascience #beginners #learning

📌Introduction

Hi everyone!

This is my first SQL project where I worked on a small dataset related to crop production. I wanted to understand how production varies across states, seasons, and crops using simple SQL queries.

📊 About the Dataset

I created a database called green_harvest and a table named crop_production.

The table includes details like:

  • crop name
  • state
  • season
  • production
  • rainfall
  • land used (hectares)

🧠 What I Did Using SQL

Created Database 

create database green_harvest;
use green_harvest;
Enter fullscreen mode Exit fullscreen mode

Created Table 

create table crop_production (
    crop_id int,
    crop_name varchar(50),
    state varchar(50),
    season varchar(20),
    year int,
    hectares decimal(10,2),
    production decimal(10,2),
    rainfall decimal(10,2)
);
Enter fullscreen mode Exit fullscreen mode

SQL execution in MySQL Workbench:

SQL queries executed in MySQL Workbench showing table creation

Inserted Sample Data 

insert into crop_production values(1, 'Rice', 'Telangana', 'Kharif', 2023, 1500, 4000, 900),
(2, 'Wheat', 'Punjab', 'Rabi', 2023, 2000, 5500, 650),
(3, 'Maize', 'Karnataka', 'Kharif', 2023, 1200, 3000, 800),
(4, 'Rice', 'Andhra Pradesh', 'Kharif', 2023, 1800, 4800, 950),
(5, 'Cotton', 'Gujarat', 'Kharif', 2023, 1400, 3500, 700);
Enter fullscreen mode Exit fullscreen mode

Checked the Data 

select * from crop_production;
Enter fullscreen mode Exit fullscreen mode

Sample output of the dataset:

Output of crop_production table showing crop data

👉 This helped me see all the records clearly.

Total Production 

select sum(production) as total_production from crop_production;
Enter fullscreen mode Exit fullscreen mode

👉 I used this to calculate overall production.

Crop-wise Production 

select crop_name, sum(production) as total_production from crop_production group by crop_name order by total_production;
Enter fullscreen mode Exit fullscreen mode

👉 This shows how each crop is performing.

Top Producing State 

select state, sum(production) as total_production from crop_production group by state order by total_production desc limit 1;
Enter fullscreen mode Exit fullscreen mode

👉 This helped me find which state produces the most.

Average Rainfall by Season 

select season, avg(rainfall) from crop_production group by season;
Enter fullscreen mode Exit fullscreen mode

👉 I wanted to see how rainfall differs between seasons.

Land Usage vs Production 

select production, sum(hectares) as total_hectares from crop_production group by production order by total_hectares desc;
Enter fullscreen mode Exit fullscreen mode

👉 This shows how land usage relates to production.

📈 What I Observed

  • Rice production is higher compared to other crops
  • Punjab has strong wheat production
  • Kharif season gets more rainfall
  • Production depends on both land and region

🛠️ Tools I Used

  • SQL
  • MySQL

✅ Conclusion

This project helped me understand how SQL can be used for basic data analysis. As a beginner, it gave me confidence in using queries like GROUP BY, SUM, and AVG.

💬 Final Thoughts

I'm still learning and improving my skills.
If you have any suggestions or feedback, feel free to share

Top comments (0)