Assignment 2-A

Query Real Data

Practice extracting insights from a real-world dataset using SELECT statements, WHERE clauses, comparison operators, and sorting techniques. You'll work with an employee database to answer business questions.

3-4 hours
Beginner
75 Points
What You'll Practice
  • SELECT with specific columns
  • WHERE with comparison operators
  • AND, OR, NOT logical operators
  • LIKE pattern matching
  • ORDER BY and LIMIT clauses
Contents
01

Assignment Overview

In this assignment, you will work with an Employee Management Database to answer various business questions. You'll write 15 queries that demonstrate mastery of SELECT statements, filtering conditions, and result sorting.

Skills Applied: This assignment tests your understanding of Basic SELECT (Topic 2.1), WHERE Clause (Topic 2.2), and Sorting (Topic 2.3) from Module 2.
Data Retrieval

SELECT, column aliases, DISTINCT

Filtering

WHERE, IN, BETWEEN, LIKE, NULL

Sorting & Limiting

ORDER BY, LIMIT, OFFSET

02

The Dataset

You will work with an employees table containing the following structure: 

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE,
    manager_id INT,
    is_active BOOLEAN
);
Dataset: Download the sample dataset from the assignment resources to practice your queries.
03

Query Requirements

1
Basic SELECT Queries (5 queries)
  • Select all employees with their full names
  • Get distinct departments in the company
  • List employee names and salaries with aliases
  • Find all active employees
  • Get email addresses for a specific department
2
Filtering Queries (5 queries)
  • Find employees earning above a certain salary
  • Get employees hired between two dates
  • Find employees in multiple departments using IN
  • Search for employees by name pattern using LIKE
  • Find employees without a manager (NULL check)
3
Sorting & Pagination Queries (5 queries)
  • List employees sorted by salary descending
  • Get top 10 highest paid employees
  • Multi-column sorting by department and name
  • Pagination: Get employees 11-20 by hire date
  • Complex query combining WHERE, ORDER BY, and LIMIT
04

Submission Guidelines

GitHub Repository
  • Create a repository named sql-assignment-2-queries
  • Include a queries.sql file with all 15 queries
  • Each query should have a comment explaining what it does
  • Include expected output screenshots in a results/ folder
05

Grading Rubric

Criteria Points
Basic SELECT queries (5 × 5 pts) 25
Filtering queries (5 × 5 pts) 25
Sorting & pagination queries (5 × 5 pts) 25
Total 75