SQL Projects for Freshers: 5 Portfolio Ideas Using Indian Open Data

The best SQL projects for freshers use real, messy data to answer a question someone cares about. India's Open Government Data Platform at data.gov.in publishes free datasets such as mandi prices, air quality readings and the pincode directory. Here are five SQL Server projects built on that kind of data, with table designs, sample queries and a way to present each one.

At a glance

Set up once, before the first project

Install SQL Server and a query tool. Microsoft's What's new in SQL Server 2025 page confirms that Express edition, the free entry edition, now allows databases of up to 50 GB, which is far more than these projects need. The Standard Developer and Enterprise Developer editions are also free for development and test work. Use SQL Server Management Studio or Visual Studio Code to write queries.

Create one database with two schemas: stg for raw files exactly as downloaded, and dbo for clean, typed tables. Load every file into staging as text first, then convert. A bad row then becomes a NULL you can count, instead of a load that fails at row 40,000.

CREATE DATABASE IndiaOpenData;
GO
USE IndiaOpenData;
GO
CREATE SCHEMA stg;
GO
-- Match these columns to the header row of your downloaded file
CREATE TABLE stg.MandiRaw (
    StateName    nvarchar(100),
    DistrictName nvarchar(100),
    MarketName   nvarchar(150),
    Commodity    nvarchar(100),
    Variety      nvarchar(100),
    ArrivalDate  nvarchar(20),
    MinPrice     nvarchar(20),
    MaxPrice     nvarchar(20),
    ModalPrice   nvarchar(20)
);

BULK INSERT stg.MandiRaw
FROM 'C:\data\mandi\2026-10-26.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, CODEPAGE = '65001');

The BULK INSERT documentation explains each option: FORMAT = 'CSV' handles quoted fields, FIRSTROW = 2 skips the header and CODEPAGE = '65001' reads UTF-8. Then convert with TRY_CONVERT, which returns NULL instead of an error when a value does not fit.

Project 1: Mandi price tracker

The data: daily commodity prices by market from Agmarknet, published on data.gov.in with state, district, market, commodity, variety and minimum, maximum and modal prices. Read the dataset description for the price unit and write it in your data dictionary.

The twist: the daily resource is refreshed with recent arrivals, so you build your own history. Download it every day, append each file and add a unique key on date, market, commodity and variety so that loading the same day twice does not double count. That is incremental loading, a skill data teams use daily.

Questions to answer: which markets in your state paid the most for tomato this week, how wide the price gap between markets is on the same day, and how prices moved week on week. After converting staging rows into a typed dbo.MandiPrice table, this query answers the last one:

WITH weekly AS (
    SELECT MarketName,
           DATETRUNC(iso_week, PriceDate) AS WeekStart,
           AVG(ModalPrice) AS AvgModal
    FROM dbo.MandiPrice
    WHERE Commodity = N'Tomato'
      AND StateName = N'Tamil Nadu'
    GROUP BY MarketName, DATETRUNC(iso_week, PriceDate)
)
SELECT MarketName,
       WeekStart,
       AvgModal,
       AvgModal - LAG(AvgModal) OVER (
           PARTITION BY MarketName ORDER BY WeekStart) AS ChangeVsLastWeek
FROM weekly
ORDER BY MarketName, WeekStart;

DATETRUNC with iso_week always starts weeks on Monday, whatever your server's DATEFIRST setting, according to the DATETRUNC reference. It needs SQL Server 2022 or later, or Azure SQL.

Project 2: Air quality across monitoring stations

The data: Central Pollution Control Board readings from monitoring stations, published on data.gov.in. The file is long: one row per station and pollutant, with minimum, maximum and average values and a last-updated time.

The twist: a long table is hard to read, so pivot it into one row per station and reading time. Expect gaps and text placeholders in numeric columns. Convert them with TRY_CONVERT, then report how many readings each station is missing.

SELECT City,
       StationName,
       ReadingTime,
       MAX(CASE WHEN Pollutant = 'PM2.5' THEN AvgValue END) AS PM25,
       MAX(CASE WHEN Pollutant = 'PM10'  THEN AvgValue END) AS PM10,
       MAX(CASE WHEN Pollutant = 'NO2'   THEN AvgValue END) AS NO2
FROM dbo.AirReading
GROUP BY City, StationName, ReadingTime;

Map the pollutant codes in your file to these names in a small lookup table. Then add DENSE_RANK() over PM2.5 within each city to find the station with the worst reading in every snapshot.

Project 3: Pincode and post office directory

The data: the Department of Posts' all-India pincode directory, with office name, pincode, office type, delivery status, district and state, and, for many offices, coordinates.

The twist: this is a cleaning project. Find pincodes that span more than one district, offices without coordinates and district names spelled differently from other government datasets, including old and new names such as Gurgaon and Gurugram. Build a clean district table with a mapping column, and you have a dimension that joins your other projects together.

SELECT Pincode,
       COUNT(DISTINCT DistrictName) AS DistrictCount
FROM dbo.PostOffice
GROUP BY Pincode
HAVING COUNT(DISTINCT DistrictName) > 1
ORDER BY DistrictCount DESC;

This project shows that you check data before you trust it. Explain one decision you made, such as which district a shared pincode belongs to, and why.

Project 4: A century of monsoon rainfall

The data: the India Meteorological Department's sub-divisional monthly rainfall series, which starts in 1901. It is wide: one row per subdivision and year, with a column for each month.

The twist: unpivot the months into rows, total the June to September monsoon, and compare every year with that subdivision's own average.

WITH monthly AS (
    SELECT Subdivision, RainYear, MonthName, Rainfall
    FROM dbo.RainfallWide
    UNPIVOT (Rainfall FOR MonthName IN (JUN, JUL, AUG, SEP)) AS u
),
monsoon AS (
    SELECT Subdivision, RainYear, SUM(Rainfall) AS MonsoonMm
    FROM monthly
    GROUP BY Subdivision, RainYear
)
SELECT Subdivision,
       RainYear,
       MonsoonMm,
       100.0 * (MonsoonMm - AVG(MonsoonMm) OVER (PARTITION BY Subdivision))
             / AVG(MonsoonMm) OVER (PARTITION BY Subdivision) AS PctFromSeriesAverage
FROM monsoon
ORDER BY Subdivision, RainYear;

Two honest caveats make this project stronger. Microsoft's PIVOT and UNPIVOT guide notes that NULL values in the input of UNPIVOT disappear in the output, so count months per year to catch gaps. And your baseline is the series average you calculated, not IMD's official long period average, so say that clearly in your findings.

Project 5: Company registrations by year

The data: company master data from the Ministry of Corporate Affairs, published on data.gov.in by Registrar of Companies. Expect columns for the corporate identification number, name, registration date, status, class and capital.

The twist: status values may arrive as short codes. Build a lookup table that maps each value to a readable label and an IsActive flag, then analyse registrations over time.

SELECT YEAR(c.RegistrationDate) AS RegYear,
       COUNT(*) AS Registered,
       SUM(CASE WHEN s.IsActive = 1 THEN 1 ELSE 0 END) AS ActiveAtDownload
FROM dbo.Company AS c
JOIN dbo.CompanyStatus AS s
  ON s.StatusCode = c.CompanyStatus
GROUP BY YEAR(c.RegistrationDate)
ORDER BY RegYear;

The file is a snapshot, so status means status on the day you downloaded it. Name that date in every chart title. Overclaiming from a snapshot is a common mistake that interviewers notice.

Taking one project a step further

Once the basic queries work, one extension makes a practice exercise look like real work:

• Automate the daily load. SQL Server Express does not include SQL Server Agent, so Microsoft's guidance for Express is to run sqlcmd scripts from Windows Task Scheduler. Use the same approach to load each day's mandi or air quality file.
• Wrap the load in a stored procedure that writes row counts, rejected rows and the file name to a log table.
• Tune one slow query. Add an index, compare the execution plan before and after, and note what changed in your README.
• Put a report on top. Connect Power BI to your clean tables and build one page that answers your main question.

Mistakes that weaken SQL portfolios

• Loading straight into typed tables. One bad date stops the load. Stage as text, then convert.
• Skipping the data dictionary. Every column needs a meaning, a unit and a source.
• Using names as keys. Market, district and company names repeat and change. Use codes where the data has them, or build your own surrogate keys.
• Mixing snapshots. Record the download date in every row you load.
• Showing only the final query. Recruiters want to see the cleaning steps too.

How to present the projects

Put each project, or all five, in one repository laid out so a reviewer finds everything in a minute:

india-open-data-sql/
  README.md
  data/SOURCES.md
  sql/01_create_tables.sql
  sql/02_load_staging.sql
  sql/03_clean.sql
  sql/04_analysis.sql
  docs/data-model.png
  docs/findings.md

The README should state the question, the dataset name with its data.gov.in link, the download date, the licence and credit to the publishing ministry, row counts before and after cleaning, and three findings with the query that produced each. Do not commit large raw files; explain how to download them instead.

In interviews, tell each project as a two-minute story: the question, the data problem you hit, the query that solved it and what the result showed. Be ready to rewrite a window function on a whiteboard. Our list of SQL interview questions for data analysts is good practice, and if you are still deciding whether SQL deserves your time, read is SQL still worth learning. Engineering students can turn projects 1 or 2 into a larger final-year project with AI and data.

How CertTulen Academy can help

CertTulen Academy teaches SQL Server in live, MCT-led online classes with hands-on labs. If you are starting out, Microsoft SQL Server Fundamentals (CT-MSSQL-B) is the place to build the query skills every project above depends on. If you want the complete route from first query to advanced work, the Microsoft SQL Server Beginner-to-Master Bootcamp takes you through it step by step. Contact us for a batch that suits your schedule.