SQL – Employee Name Search

Query database to find employees whose names start with 'S' or end with 'n' and order results by designation.

Understand the Problem

Problem Statement

Given an employee table with columns id, name, and designation, write a SQL query to list the name and designation of employees where the name starts with 'S' (uppercase) or ends with 'n' (lowercase).

The result set must have name as the first column and designation as the second column. Results must be ordered by designation in ascending order.

Constraints

The table name is employee with exactly three columns: id (int), name (VARCHAR(100)), designation (VARCHAR(50))
Names are case-sensitive: must start with uppercase 'S' OR end with lowercase 'n'
Results must be ordered by designation in ascending order
Only return the name and designation columns in that specific order

Examples

Example 1

Input

Table: employee
| id | name     | designation |
|----|----------|-------------|
| 1  | Sarah    | Developer   |
| 2  | John     | Manager     |
| 3  | Sam      | Analyst     |
| 4  | Alice    | Developer   |
| 5  | Steven   | Manager     |
| 6  | Daniel   | Designer    |

Output

| name  | designation |
|-------|-------------|
| Sam   | Analyst     |
| Sarah | Developer   |
| John  | Manager     |
| Steven| Manager     |

Explanation

Names starting with 'S': Sarah, Sam, Steven. Names ending with 'n': John, Steven. Combined unique results ordered by designation: Analyst (Sam), Developer (Sarah), Manager (John, Steven).

Example 2

Input

Table: employee
| id | name    | designation |
|----|---------|-------------|
| 1  | Alice   | Developer   |
| 2  | Bob     | Manager     |
| 3  | Carol   | Designer    |

Output

(No results returned)

Explanation

No names start with 'S' or end with 'n', so the query returns an empty result set.

Solution

/*
This is a SQL query problem, not a programming language problem.
However, here's an example of how you might execute this SQL in C using a database library.
*/

#include <stdio.h>
#include <mysql.h>

int main() {
    MYSQL *conn;
    MYSQL_RES *result;
    MYSQL_ROW row;
    
    // Connect to database
    conn = mysql_init(NULL);
    mysql_real_connect(conn, "localhost", "user", "password", "database", 0, NULL, 0);
    
    // Execute the SQL query
    const char *sql = "SELECT name, designation FROM employee WHERE name LIKE 'S%' OR name LIKE '%n' ORDER BY designation;";
    
    if (mysql_query(conn, sql)) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        return 1;
    }
    
    result = mysql_store_result(conn);
    
    printf("name\t	designation\n");
    printf("----\t	-----------\n");
    
    while ((row = mysql_fetch_row(result))) {
        printf("%s\t\t%s\n", row[0], row[1]);
    }
    
    mysql_free_result(result);
    mysql_close(conn);
    
    return 0;
}

Time:O(n log n) - where n is the number of rows in the table. The sorting operation (ORDER BY) typically has O(n log n) complexity.

Space:O(n) - for storing the result set in memory, where n is the number of matching rows.

Approach:

Since this is primarily a SQL problem, the C solution demonstrates how to execute the SQL query using MySQL C API. The actual logic is in the SQL statement: SELECT name, designation FROM employee WHERE name LIKE 'S%' OR name LIKE '%n' ORDER BY designation;

The C code connects to a MySQL database, executes the query, and displays the results in a formatted table.

Visual Explanation

Loading diagram...

All Problems