Day 2 Practice: Querying Data

Hands-On SQL Assignments

Scenario: You are analyzing data for a global electronics company. You have access to two main tables: Employees (contains EmployeeID, FirstName, LastName, Department, Salary, age) and Products (contains ProductID, ProductName, Category, Price, Stock). Write the SELECT queries to solve the following business requests.

Task 1: Basic Retrieval & DISTINCT

Write a query to retrieve all columns and all rows from the Products table.
Write a query to retrieve only the FirstName, LastName, and Salary of all employees.
The HR department wants to know exactly which departments exist in the company. Write a query to return a list of unique departments from the Employees table.

Task 2: Filtering Data (WHERE & Logical Operators)

Find all products where the Price is greater than or equal to 500.
Find all employees who work in the 'IT' Department AND earn a Salary greater than 75000.
Use the IN operator to find all products that belong to the 'Laptops', 'Tablets', or 'Smartphones' categories.
Use the BETWEEN operator to find all employees whose age falls between 25 to 35.
Write a query using the NOT operator to find all employees who do not work in the 'HR' department.

Task 3: Pattern Matching (LIKE & Wildcards)

The % Wildcard: Find all employees whose LastName ends with 'son' (e.g., Johnson, Anderson).
The _ Wildcard: Find all products whose ProductName is exactly 5 characters long and starts with 'Phone' (e.g., Phone X, Phone 8). Hint: Use an underscore for the missing character.
The [] Wildcard: Find all employees whose FirstName starts with the letter A, B, or C.
The [^] Wildcard: Find all employees whose FirstName does not start with the letters X, Y, or Z.

Task 4: Sorting & Limiting (ORDER BY & TOP)

Write a query to list all employees, sorted by their Department in ascending alphabetical order, and then by their Salary in descending order.
The sales team wants to feature the most expensive items. Write a query to retrieve the TOP 5 products with the highest Price.
Management wants to give a bonus to the top earners. Write a query to retrieve the TOP 10 PERCENT of employees based on their Salary (highest to lowest).