INTERNSHIP REPORT

A Report Submitted
In Partial Fulfillment
for award of Bachelor of Technology

In
BRANCH NAME
Computer Science and Engineering
By

Prashant Saini (2101330100173)

Under the Supervision of

NAME OF MENTOR
Designation, Computer Science and Engineering
< Times New Roman, font size 14>

Department of Computer Science and Engineering

School of Computer Science and IT
NOIDA INSTITUTE OF ENGINEERING AND TECHNOLOGY,
GREATER NOIDA
(An Autonomous Institute)
Affiliated to
DR. A.P.J. ABDUL KALAM TECHNICAL UNIVERSITY, LUCKNOW
December, 2024
 Computer Science and Engineering

DECLARATION

I hereby declare that the work presented in this report was carried out by me. I have not
submitted the matter embodied in this report for the award of any other degree or diploma of
any other University or Institute.

Name : Prashant Saini

Roll Number : 2101330100173
(Candidate Signature)

i
 Computer Science and Engineering

ACKNOWLEDGEMENT

I would like to express my gratitude towards <Industrial Supervisor name> and <Institutional
Supervisor name> for their guidance, support and constant supervision as well as for
providing necessary information during my internship.
My thanks and appreciations to respected HOD, Dy. HOD, for their motivation and support
throughout.

ii
 Computer Science and Engineering

TABLE OF CONTENTS
Page No.
Declaration i
Certificate from the institute ii
Acknowledgement iii
1. INTRODUCTION
1.1 ORGANIZATION OVERVIEW

1.2 OBJECTIVE AND SCOPE

2. INTERNSHIP EXPERIENCE
2.1 PROJECT WORKED ON
2.2 TOOLS AND TECHNOLOGY USED
2.3 ROLES AND RESPONSIBILITIES OF THE INTERN
2.4 DETAILS OF WORK DONE
2.5 SKILLS LEARNT

3. CONCLUSION
CURRICULUM VITAE

1
 Computer Science and Engineering

INTRODUCTION

INTRODUCTION:

Celebal Technologies is a software services company that specializes in digital

transformation and enterprise reinvention. Their solutions are built on Generative AI,
Machine Learning, Data Science, and other technologies.

1.1 ORGANIZATION OVERVIEW

Celebal Technologies is a premier software services company specializing in digital

transformation and enterprise reinvention, delivering end-to-end results to clients across
diverse sectors. Our dynamic suite of advanced solutions built on Generative AI, Machine
Learning, Data Science, Industry Data Models, Infrastructure & Database Migration, SAP
Extend & Innovate, App Modernization, and Enterprise Cloud Integration, positions us at the
nexus of traditional enterprise systems and modern cloud innovation. With a global presence
spanning the USA, India, APAC, UAE, Europe, and Canada, Celebal Technologies boasts a
dedicated workforce of over 2000+ professionals that serve up an impressive client base,
including 90% of Fortune 500 companies, guiding them through their transformative digital
journeys.

1.2 FOUNDER OF CELEBAL TECHNOLOGIES

The founders of Celebal Technologies, Anupam Gupta and Anirudh Kala, have played
pivotal roles in shaping the company into a prominent player in data, analytics, and
enterprise cloud solutions.

Anupam Gupta

Anupam serves as the Co-founder and Managing Director of Celebal Technologies. With
a rich background in IT and consulting, he specializes in delivering cutting-edge solutions
in Data Engineering, Artificial Intelligence, and Cloud transformation. Anupam's
strategic vision and emphasis on leveraging advanced technologies like SAP and
Microsoft platforms have been instrumental in Celebal's growth and success.

2
 Computer Science and Engineering

Anirudh Kala

Anirudh is the Co-founder and CEO of Celebal Technologies. Known for his innovative
approach, he has extensive experience in data science, AI, and machine learning. Under
his leadership, the company has earned accolades for providing customized solutions
across industries such as manufacturing, BFSI, and retail. Anirudh's commitment to
excellence and fostering a culture of innovation has positioned Celebal as a trusted
partner for global enterprises.

1.3 CELEBAL TECHNOLOGIES MISSION

Our mission is to make data simple and easy to understand for all organizations. We are
committed to providing solutions powered by modern cloud and artificial intelligence that
integrate with traditional enterprise software. Our tailor-made solutions help enterprises
maximise productivity, improve speed and accuracy.

1.4 CELEBAL TECHNOLOGIES TEAM

We are a Data and AI company comprised of a dedicated and accomplished team of
professionals who understand the core value of modern analytics over traditional
enterprise. As a leading tech company with 2000+ employees, we follow the concept of a
non-hierarchical work culture, empowering every individual in the organization to do
their best. We are always on the lookout for skilled professionals who believe in integrity
and respect and enjoy working in a fast-paced and autonomous environment.

3
 Computer Science and Engineering

INTERNSHIP EXPERIENCE

2.1 PROJECT WORKED ON

2.1.a Objective:
The goal is to calculate the average salary of employees per department and
include only those departments whose average salary is higher than the overall
average salary across all employees. The result includes:
1. Department Name
2. Average Salary
3. Number of Employees
2.1.b Key Steps:
1. Calculate the overall average salary:
o This is the average of all employees' salaries across the
organization.
o It's stored in a CTE named OverallAvgSalary.
2. Compute department-level statistics:
o Using a JOIN between the Employees and Departments tables,
calculate the average salary (AVG()), count of employees
(COUNT()), and group by department name.
o This is stored in a CTE named DepartmentStats.
3. Filter departments:
o The final query filters departments where the average salary
(computed in DepartmentStats) is greater than the overall average
salary (from OverallAvgSalary).
2.1.c SQL Query Overview:
The query uses two Common Table Expressions (CTEs):
 DepartmentStats: Contains department-level statistics.
 OverallAvgSalary: Stores the overall average salary. The final
SELECT retrieves only those departments satisfying the condition.

4
 Computer Science and Engineering

2.1.d Example Data & Output:

Given the input tables, the Development department is the only one meeting
the condition:
 Development has an average salary of 100,000 with 3 employees.
 Other departments, like Marketing and Research, have lower average
salaries than the overall average.
2.1.e Key Concepts Demonstrated:
1. Aggregation: Use of AVG() and COUNT() to summarize data.
2. Filtering with Subqueries/CTEs: Comparing group-level statistics with
overall statistics.
3. JOINs: Combining data across related tables.
4. Readable SQL Design: The use of CTEs improves the query's clarity and
maintainability.
2.1.f Practical Use:
This exercise demonstrates a real-world scenario where SQL is used for
departmental performance analysis or budgeting:
 Identifying high-performing departments.
 Supporting data-driven decisions in resource allocation.
2.1.f Insights:
The task not only tests technical proficiency with SQL but also emphasizes
analytical thinking and business insight—valuable skills in a role at Celebal
Technologies, which focuses on data engineering and analytics.

5
 Computer Science and Engineering

Tools and Technologies Used for the SQL Project

1. SQL Database Management System (DBMS)

 MySQL, PostgreSQL, or SQL Server:
o Likely tools for creating and managing the Employees and Departments
tables.
o Provide structured querying capabilities to extract and manipulate data
efficiently.
2. SQL Features
 Common Table Expressions (CTEs):
o Used for structuring the query with WITH clauses for DepartmentStats and
OverallAvgSalary.
 Aggregate Functions:
o AVG() for calculating average salaries.
o COUNT() for counting the number of employees per department.
 JOINs:
o Essential for combining the Employees and Departments tables based on
DepartmentID.
3. Database Client or Query Tool
 Tools like phpMyAdmin, pgAdmin, or SQL Developer:
o Used to write, test, and debug SQL queries.
o Provides an interface for executing the query and visualizing results.
4. Integrated Development Environment (IDE)
 DBeaver, DataGrip, or Visual Studio Code with SQL Extensions:
o Aid in query development and syntax checking.
o Improve productivity with features like auto-completion.
5. Data Analytics
 Data Analysis Extensions or Platforms:
o Queries like this may feed into data visualization tools like Power BI, Tableau,
or Excel for reporting purposes.

6
 Computer Science and Engineering

6. Documentation and Version Control

 Git/GitHub:
o For maintaining the version history of SQL scripts and collaborative
development.

Roles and Responsibilities of an SQL Developer Intern

As an intern at Celebal Technologies, I worked on real-world problem statements, applying
SQL skills to design and implement solutions that align with business needs. Below are the
roles and responsibilities I undertook:
1. Database Design and Management
 Designed and managed relational database schemas to accommodate dynamic
requirements.
 Worked on database normalization and maintaining data integrity.
2. Problem-Specific SQL Development
 Subject Change Tracking:
o Created a stored procedure to manage changes in student subject allocations
(SubjectAllotments table).
o Implemented business logic to handle subject transition requests
(SubjectRequest table) while preserving data history for audit purposes.
 Stored Procedure Workflow:
o Checked the existence of students in the SubjectAllotments table.
o Updated validity of previously allotted subjects and added the requested ones.
o Inserted new records when a student's ID did not exist in the database.
3. Dynamic Resource Allocation
 Subject Allotment System:
o Developed a system to allocate subjects to students based on preferences and
GPA rankings, while respecting subject capacity constraints.
o Handled unallocated students when all their preferences were unavailable.

7
 Computer Science and Engineering

 SQL Implementation:
o Designed stored procedures to iterate through students in descending GPA
order.
o Allocated subjects using a preference hierarchy and updated the remaining
seats dynamically.
4. Advanced Query Design
 Project Grouping:
o Wrote queries to group consecutive tasks as part of the same project and
calculate their start and end dates.
o Optimized queries using CTEs (WITH clauses) and window functions
(ROW_NUMBER) to achieve accurate results.
 Relational Queries:
o Developed a query to identify students whose best friends received higher
salary packages by joining multiple tables (Students, Friends, Packages) and
sorting results based on dynamic conditions.
5. Testing and Debugging
 Debugged stored procedures and optimized query performance to ensure efficient
handling of large datasets.
 Performed validation and unit testing of SQL queries and stored procedures to verify
accuracy and consistency.
6. Documentation
 Documented workflows, SQL procedures, and design decisions to ensure
maintainability and future scalability.
7. Collaboration and Problem-Solving
 Collaborated with mentors to understand problem requirements and refine solutions.
 Contributed to brainstorming sessions for optimizing database operations.
Key Accomplishments:
 Successfully implemented subject tracking and dynamic allotment systems, meeting
the requirements of a robust college management solution.
 Created scalable and efficient SQL solutions for complex data management problems.

8
 Computer Science and Engineering

DETAILS OF WORK
Task 1: Consecutive Projects
Objective: Group consecutive tasks as projects and output their
start and end dates in ascending order by duration and start date.

WITH ProjectGroups AS (
SELECT
Task_ID,
Start_Date,
End_Date,
ROW_NUMBER() OVER (ORDER BY Start_Date) -
ROW_NUMBER() OVER (ORDER BY Task_ID) AS ProjectGroup
FROM Projects
),
ProjectsAggregated AS (
SELECT
MIN(Start_Date) AS Project_Start_Date,
MAX(End_Date) AS Project_End_Date,
COUNT(*) AS Duration
FROM ProjectGroups
GROUP BY ProjectGroup
)
SELECT
Project_Start_Date,
Project_End_Date
FROM
ProjectsAggregated
ORDER BY
Duration ASC,
Project_Start_Date ASC;

Task 2: Higher Salary

Objective: Find students whose best friend earns a higher salary than they do.
SELECT
[Link]
FROM
Students S1
JOIN
Friends F ON [Link] = [Link]
JOIN
Packages P1 ON [Link] = [Link]
JOIN
Packages P2 ON F.Friend_ID = [Link]

9
 Computer Science and Engineering

WHERE
[Link] > [Link]

ORDER BY
[Link] ASC;

Task 3: Symmetric Pairs

Objective: Find all symmetric pairs (X, Y) where X1 = Y2 and X2 = Y1.
SELECT DISTINCT
F1.X, F1.Y
FROM
Functions F1
JOIN
Functions F2 ON F1.X = F2.Y AND F1.Y = F2.X
WHERE
F1.X <= F1.Y
ORDER BY
F1.X ASC, F1.Y ASC;

Task 4: Contest Statistics

Objective: Aggregate contest statistics and exclude contests with zero activity.
SELECT
C.contest_id,
C.hacker_id,
[Link],
COALESCE(SUM(VS.total_views), 0) AS total_views,
COALESCE(SUM(VS.total_unique_views), 0) AS total_unique_views,
COALESCE(SUM(SS.total_submissions), 0) AS total_submissions,
COALESCE(SUM(SS.total_accepted_submissions), 0) AS
total_accepted_submissions
FROM
Contests C
LEFT JOIN
Colleges Col ON C.contest_id = Col.contest_id
LEFT JOIN
Challenges CH ON Col.college_id = CH.college_id
LEFT JOIN
View_Stats VS ON CH.challenge_id = VS.challenge_id
LEFT JOIN
Submission_Stats SS ON CH.challenge_id = SS.challenge_id
GROUP BY
C.contest_id, C.hacker_id, [Link]
HAVING
SUM(VS.total_views) > 0 OR
SUM(VS.total_unique_views) > 0 OR
SUM(SS.total_submissions) > 0 OR
SUM(SS.total_accepted_submissions) > 0

10
 Computer Science and Engineering

ORDER BY
C.contest_id;

Task 5: Daily Hacker Activity

Objective: Count hackers with daily submissions and identify the hacker with the most submissions
each day.
WITH DateSeries AS (
SELECT DATE '2016-03-01' + INTERVAL (LEVEL - 1) DAY AS
contest_date
FROM dual
CONNECT BY LEVEL <= 15
),
DailySubmissions AS (
SELECT
submission_date,
hacker_id,
COUNT(*) AS num_submissions
FROM Submissions
GROUP BY submission_date, hacker_id
),
MaxDailySubmissions AS (
SELECT
submission_date,
hacker_id,
num_submissions,
RANK() OVER (PARTITION BY submission_date ORDER BY
num_submissions DESC, hacker_id ASC) AS rank
FROM DailySubmissions
)
SELECT
contest_date,
COUNT(DISTINCT hacker_id) AS unique_hackers,
MAX(CASE WHEN rank = 1 THEN hacker_id END) AS max_hacker_id,
MAX(CASE WHEN rank = 1 THEN [Link] END) AS max_hacker_name
FROM
DateSeries DS
LEFT JOIN
Submissions S ON DS.contest_date = S.submission_date
LEFT JOIN
Hackers H ON S.hacker_id = H.hacker_id
GROUP BY
contest_date
ORDER BY
contest_date;

11
 Computer Science and Engineering

SKILLS LEARNT
Technical Skills:
1. SQL Querying:
o Writing complex SQL queries using WITH clauses and advanced techniques
like ROW_NUMBER, GROUP BY, and HAVING.
o Aggregating data, joining tables, and filtering results effectively.
o Working with database schemas, including normalization and understanding
relationships between tables.
2. Data Analysis:
o Extracting insights from relational data.
o Implementing logic to identify patterns (e.g., consecutive dates, symmetric
pairs).
o Using advanced sorting and filtering techniques to organize and analyze data.
3. Database Design & Management:
o Understanding and working with relational databases.
o Using tools to test and debug SQL queries.
o Optimizing queries for performance.
4. Problem-Solving:
o Designing efficient solutions for real-world scenarios like project grouping,
symmetric pair identification, and contest analytics.
o Handling edge cases and ensuring data consistency.
Soft Skills:
1. Critical Thinking:
o Analysing complex requirements and breaking them down into smaller,
manageable tasks.
o Developing logical solutions to meet specific business needs.
2. Attention to Detail:
o Ensuring accurate output by handling edge cases and validating query results
against sample data.

12
 Computer Science and Engineering

3. Time Management:
o Balancing multiple tasks, such as solving distinct database problems within
deadlines.
4. Collaboration:
o Likely collaborating with team members or mentors for debugging and
solution validation.

13
 Computer Science and Engineering

Conclusion

During this internship, I gained valuable technical expertise and practical

experience in SQL querying, database design, and data analysis. By working on
real-world challenges, I enhanced my problem-solving skills, attention to detail,
and ability to optimize solutions for efficiency. This experience also honed my
critical thinking and time management abilities, preparing me to tackle complex
database-related problems effectively. Overall, this internship provided a solid
foundation for a career in database management and data-driven decision-
making, and I am confident in applying these skills to future projects and
opportunities.

14
 Computer Science and Engineering

15
 Computer Science and Engineering

INTERNSHIP CERTIFICATES

16
 Computer Science and Engineering

17