Student Internship and Placement System - SQL
Project
This outlines a SQL project for a Student Internship and Placement System, including database
schema, sample queries, and considerations.
Project Goals:
● Manage student information.
● Track internship opportunities and applications.
● Record placement details.
● Generate reports for analysis.
Database Schema:
-- Students Table
CREATE TABLE Students (
StudentID INT PRIMARY KEY AUTO_INCREMENT,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DateOfBirth DATE,
Gender VARCHAR(10),
Email VARCHAR(100) UNIQUE NOT NULL,
Phone VARCHAR(20),
Major VARCHAR(50),
GPA DECIMAL(3, 2),
GraduationYear INT
);
-- Companies Table
CREATE TABLE Companies (
CompanyID INT PRIMARY KEY AUTO_INCREMENT,
CompanyName VARCHAR(100) NOT NULL,
Industry VARCHAR(50),
Location VARCHAR(100),
ContactPerson VARCHAR(100),
ContactEmail VARCHAR(100),
ContactPhone VARCHAR(20)
);
-- Internships Table
CREATE TABLE Internships (
InternshipID INT PRIMARY KEY AUTO_INCREMENT,
CompanyID INT,
Title VARCHAR(100) NOT NULL,
Description TEXT,
StartDate DATE,
EndDate DATE,
Stipend DECIMAL(10, 2),
FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID)
);
-- Placements Table
CREATE TABLE Placements (
PlacementID INT PRIMARY KEY AUTO_INCREMENT,
StudentID INT,
CompanyID INT,
JobTitle VARCHAR(100) NOT NULL,
Salary DECIMAL(10, 2),
JoiningDate DATE,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CompanyID) REFERENCES Companies(CompanyID)
);
-- Applications Table
CREATE TABLE Applications (
ApplicationID INT PRIMARY KEY AUTO_INCREMENT,
StudentID INT,
InternshipID INT,
ApplicationDate DATE,
Status VARCHAR(20) DEFAULT 'Pending', -- 'Pending', 'Accepted',
'Rejected'
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (InternshipID) REFERENCES Internships(InternshipID)
);
-- Skills Table
CREATE TABLE Skills(
SkillID INT PRIMARY KEY AUTO_INCREMENT,
SkillName VARCHAR(50) UNIQUE NOT NULL
);
-- StudentSkills (many to many)
CREATE TABLE StudentSkills(
StudentID INT,
SkillID INT,
PRIMARY KEY(StudentID, SkillID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (SkillID) REFERENCES Skills(SkillID)
);
-- InternshipSkills (many to many)
CREATE TABLE InternshipSkills(
InternshipID INT,
SkillID INT,
PRIMARY KEY(InternshipID, SkillID),
FOREIGN KEY (InternshipID) REFERENCES Internships(InternshipID),
FOREIGN KEY (SkillID) REFERENCES Skills(SkillID)
);
Sample SQL Queries:
1. List all students with their majors and GPAs:
SELECT FirstName, LastName, Major, GPA
FROM Students;
2. Find all internships offered by a specific company:
SELECT Title, Description, StartDate, EndDate
FROM Internships
WHERE CompanyID = (SELECT CompanyID FROM Companies WHERE
CompanyName = 'Example Company');
3. List all students who applied for a specific internship:
SELECT s.FirstName, s.LastName, a.ApplicationDate, a.Status
FROM Students s
JOIN Applications a ON s.StudentID = a.StudentID
WHERE a.InternshipID = 123; -- Replace 123 with the actual
InternshipID
4. Find the average GPA of students placed in a particular company:
SELECT AVG(s.GPA)
FROM Students s
JOIN Placements p ON s.StudentID = p.StudentID
WHERE p.CompanyID = (SELECT CompanyID FROM Companies WHERE
CompanyName = 'Another Example Company');
5. List all companies in a particular industry:
SELECT CompanyName, Location
FROM Companies
WHERE Industry = 'Technology';
6. Find internships requiring a specific skill:
SELECT i.Title
FROM Internships i
JOIN InternshipSkills iskill ON i.InternshipID =
iskill.InternshipID
JOIN Skills s ON iskill.SkillID = s.SkillID
WHERE s.SkillName = 'Python';
7. Find students with a specific skill:
SELECT s.FirstName, s.LastName
FROM Students s
JOIN StudentSkills sskills ON s.StudentID = sskills.StudentID
JOIN Skills skill ON sskills.SkillID = skill.SkillID
WHERE skill.SkillName = 'Java';
8. Count the number of applications for each internship:
SELECT i.Title, COUNT(a.ApplicationID) AS ApplicationCount
FROM Internships i
LEFT JOIN Applications a ON i.InternshipID = a.InternshipID
GROUP BY i.InternshipID;
Considerations:
● Data Validation: Implement constraints and triggers to ensure data integrity.
● Indexing: Use indexes to optimize query performance.
● Security: Implement appropriate security measures to protect sensitive data.
● Reporting: Create views and stored procedures for generating reports.
● User Interface: Consider how the database will be accessed (e.g., through a web
application).
● Scalability: Design the database to handle a growing number of students, companies,
and internships.
● Error Handling: Implement robust error handling in your SQL code.
● Normalization: Ensure the database is properly normalized to reduce redundancy.
● Triggers: Triggers can be used to update the status of applications, or to update related
tables when a value changes.
● Stored Procedures: Stored procedures can be used to perform complex operations,
such as generating reports or updating multiple tables.
● Views: Views can be used to simplify complex queries and to provide a customized view
of the data.
● Backup and Recovery: Implement a backup and recovery plan to protect the data.
This project provides a solid foundation for building a robust Student Internship and Placement
System. Remember to adapt the schema and queries to meet your specific requirements.
