Skip to main content

SQL Interview Question - 1


Consider a table named Employee with the following schema:
CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2),
    hire_date DATE
);

Write an SQL query to find the employee(s) with the highest salary within each department.

Sample Table:


emp_id emp_name department salary hire_date
1 Alice Sales 50000 2020-01-01
2 Bob Sales 55000 2019-05-15
3 Charlie HR 60000 2020-03-20
4 David HR 58000 2018-11-10
5 Emma Marketing 52000 2021-02-28
6 Frank Marketing 53000 2019-08-10

Solution:

SELECT 
    emp_id,
    emp_name,
    department,
    salary
FROM 
    Employee e
WHERE 
    (department, salary) IN (
        SELECT 
            department,
            MAX(salary)
        FROM 
            Employee
        GROUP BY 
            department
    );

 

Explanation:

  • The inner query (SELECT department, MAX(salary) FROM Employee GROUP BY department) finds the maximum salary for each department.
  • The outer query selects all rows from the Employee table where the (department, salary) tuple matches those found by the inner query.
  • This effectively retrieves the employee(s) with the highest salary within each department.

Share your alternate solution in comments.

Comments

Other popular job openings

IBM is hiring for a Data Analyst Intern role in India

Position: Data Analyst Intern Company: IBM India Private Limited Location: Gurgaon, Haryana and Bangalore, Karnataka, India Job type: Internship (Fixed Term) Job mode: Hybrid Job requisition id: 44158 Years of experience: None Company description: IBM, established in 1911, is one of the world’s oldest and largest technology companies. Known globally for its legacy in computing, IBM has transformed into a hybrid cloud and AI powerhouse. The company serves clients in over 170 countries and collaborates with Fortune 50 companies for digital transformation and modernization. IBM’s consulting wing, IBM Consulting, plays a pivotal role in delivering expert guidance in business and technology services. With innovations across AI, quantum computing, blockchain, and hybrid cloud infrastructure, IBM continues to lead global tech evolution. IBMers, the term for employees, are known for their problem-solving capabilities and innovative mindset. The compan...

Gartner is hiring for a fresher entry level Product Data Analyst role in India

Position: Digital Product Analyst (Data Analyst) Company: Gartner, Inc. Location: Gurgaon, Haryana, India Job type: Full-Time Job mode: Onsite Job requisition id: 100723 Years of experience: 0-2 years Company description Gartner, Inc. is a world-renowned research and advisory company dedicated to helping business leaders make critical decisions. Since its inception in 1979, Gartner has grown into an international powerhouse with more than 21,000 professionals across close to 90 countries. The company thrives on bold ideas and thorough research, driving client success in their most critical initiatives. Its unique culture promotes collaborative teamwork that enables innovation, inclusivity, and personal as well as career growth. The team at Gartner is diverse, including people of different nationalities, orientations, races, and religions, creating an environment that supports everyone. Beyond just work, Gartner is committed to excellence, with a...

NIQ is hiring for a fresher entry level Data Scientist role in India

Position: Data Scientist, Executive Company: NIQ for Retail Location: Vadodara, Gujarat, India Job type: Full-time Job mode: Onsite Job requisition id: Not specified Years of experience: 0 to 1 years (preferred, but can be upto 3 years) Company description NIQ is one of the most established names in consumer intelligence. The company is dedicated to delivering complete insights into consumer behavior across retail and other fast-changing industries. Backed by robust data and advanced technology, NIQ brings a holistic view to help companies understand their customers and make smarter decisions. Operating globally with over 10,000 employees and a presence in more than 100 countries, NIQ sets the standard for retail performance measurement. Their products and services help clients gain competitive advantages in their industries with deep analytics and actionable recommendations. Following its merger with GfK, NIQ has created one of the most compreh...

WeCredit is hiring for an fresher entry level Business Analyst role in India

Position: Business Analyst Company: WeCredit Location: Delhi, India Job type: Full-time Job mode: Onsite Job requisition id: 1499194 Years of experience: 0-1 Years Company description WeCredit is a fast-growing fintech company helping customers navigate the landscape of financial solutions. The company partners with leading Non-Banking Financial Companies to offer seamless financial products. They make the process of loan approvals quick and hassle-free for every applicant. The team at WeCredit takes pride in providing personal assistance to each client at every stage of the process. Customers appreciate their transparent policies, timely updates, and one-stop solutions for their financial needs. Beyond loans, they also aim to deliver data-driven innovations that improve access to credit. With technology at its core, WeCredit simplifies complex financial processes for people. The company culture fosters collaboration, integrity, and continuo...

HiLabs is hiring for a fresher entry level Data Scientist role in India

Position: Data Scientist Company: HiLabs Location: Bangalore, Karnataka, India Job type: Full-time Job mode: Onsite Job requisition id: Not explicitly provided Years of experience: 0–3 years Company description HiLabs is a data-driven company focused on transforming the healthcare ecosystem. It builds AI-powered platforms that clean, refine, and utilize large datasets to solve major inefficiencies in the healthcare sector. With innovation at its core, HiLabs combines cutting-edge machine learning with deep healthcare expertise. The company employs professionals from world-renowned academic institutions such as Harvard, Yale, Carnegie Mellon, Duke, Georgia Tech, IIMs, and IITs. It works at the intersection of AI, big data, and healthcare delivery to create value-based systems. HiLabs fosters a collaborative environment of engineers, clinicians, and data scientists aiming to enhance patient care. Its mission is to unlock the power of dirty and c...

Dentsu is hiring for an fresher entry level Data Science Executive (Associate Data Scientist) role in India

Position: Modelling & Data Science Executive (Associate Data Scientist / Data Science Executive) Company: Dentsu (Brand: Merkle) Location: Pune Job type: Full-time, Permanent Job mode: Onsite Job requisition id: R1095498 Years of experience: 0–3 years Company description: Dentsu is a global marketing and advertising company that thrives on over 120 years of innovation. Dentsu focuses on leveraging integrated growth solutions to support clients with sustainable and long-lasting impact. Its teams partner closely with clients to help them connect with their audiences in new and creative ways, driving measurable business results. The company is committed to sustainability, helping build a better future and leaving a positive social impact. Throughout its long-standing history, Dentsu has kept people at its core and supports clients across the world through diverse offerings. Dentsu celebrates flexibility and collaboration and provides an inclusi...

UiPath is hiring for an fresher entry level Data Scientist role in India

Position: Data Scientist Company: UiPath Location: Pune, India Job type: Full-time Job mode: Onsite (with some hybrid flexibility as per team needs) Job requisition id: Not explicitly listed Years of experience: 0–3 years Company description UiPath is a global software company recognized for its leadership in the field of automation and enterprise solutions. The company is dedicated to transforming how businesses operate using automation technologies. UiPath believes in the power of automating repetitive processes to increase efficiency and allow people to focus on more valuable and creative work. The workplace culture at UiPath is built around values such as curiosity, collaboration, innovation, and personal growth. Employees are encouraged to take initiative, support their colleagues, and contribute meaningfully to team and organizational goals. Diversity and inclusivity are strongly promoted, ensuring opportunities are accessible to everyone ...

Kiwi Kisan Window is hiring for a fresher entry level Associate Data Scientist (in Analytics) role in India

📌 Position: Associate Data Scientist (this is more of a Data Analyst role as per the job description) 🏢 Company: Kiwi Kisan Window 📍 Location: Dehradun, Uttarakhand, India 💼 Job type: Full-time 🖥️ Job mode: Onsite 🧑‍💻 Job requisition ID: Not specified 🕰️ Years of experience: 0-1 year 🏭 Company description Kiwi Kisan Window is an innovative social-entrepreneurship venture working toward making organic food accessible and mainstream. The company bridges local farmers with consumers to promote a healthier, more sustainable lifestyle. It operates retail stores in Dehradun and is expanding into e-commerce for organic and conscious foods. Kiwi aims to present farm-fresh produce and healthy staples with a sensory experience that’s appealing across sight, taste, touch, and smell. They focus on integrity and transparency in the supply chain, bringing high-quality products directly to health-conscious customers. Kiwi’s business model supports loc...

Precision AQ is hiring for a fresher entry level Data Analyst role in India

Position: Data Analyst Company: Precision AQ Location: Pune, Maharashtra, India Job type: Full-time Job mode: Hybrid Job requisition id: 292441 Years of experience: 0-2 years Company description: Precision AQ is a global player in business consulting and services, specializing in helping life sciences companies navigate the complex process of bringing their products to market. The company is highly focused on improving access to new therapies by supporting pricing and reimbursement strategies, as well as offering market access solutions that facilitate commercialization across different geographies. Its teams work across commercial consulting, HEOR, medical communications, data analytics, and international healthcare communications to support clients throughout a product's lifecycle. Driven by the core belief that data-driven decisions and empathy can reduce barriers for patients, Precision AQ fuses scientific excellence with a deep understanding...

Numerator is hiring for an fresher entry level REMOTE Data Scientist role in India

Position: Data Scientist (REMOTE) Company: Numerator Location: Remote, India Job type: Full-Time Job mode: Remote Job requisition id: 602503 Years of experience: 0-3 years Company description: Numerator is a global market research company dedicated to transforming the way companies understand their customers and the marketplaces they compete in. The company specializes in delivering advanced and insightful data to some of the world’s most respected brands and retail organizations. By utilizing cutting-edge technology and a range of advanced data science methodologies, Numerator enables companies to respond to changes in customer behavior and make more informed decisions. Numerator is built on a people-first culture that emphasizes the diverse strengths and talents of its employees and celebrates the unique contributions of each member of its team. The company supports its team with competitive benefits like wellness programs, career development op...