Sorting SQL Query Exercises

Two results set queries

Generate the following two result sets:

1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a subordinate (i.e., enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
2. Query the number of occurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order and output them in the following format:
   There are a total of [occupation_count] [occupation]s. where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.

Note: There will be at least two entries in the table for each type of occupation.

The OCCUPATIONS table is described as follows:

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

To generate the two result sets as described, we will need to write two separate SQL queries. Below are the queries that achieve the desired results.

Query 1: Alphabetically Ordered List of Names with Profession Initials

This query will select the names from the OCCUPATIONS table and append the first letter of each profession in parentheses, ordered alphabetically by the name.

SELECT CONCAT(Name, '(', LEFT(Occupation, 1), ')') AS NameWithProfessionInitial
FROM OCCUPATIONS
ORDER BY Name;

Explanation:

• CONCAT(Name, '(', LEFT(Occupation, 1), ')'): This concatenates the Name, the first letter of the Occupation (using LEFT(Occupation, 1)), and the parentheses around the initial.
• ORDER BY Name: This sorts the result alphabetically by Name.

Query 2: Number of Occurrences of Each Occupation

This query will count the occurrences of each occupation in the OCCUPATIONS table, sort them in ascending order by count, and then by occupation name alphabetically if counts are equal.

SELECT 
    CONCAT('There are a total of ', COUNT(*), ' ', LOWER(Occupation), 's.') AS OccupationCountStatement
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(*), Occupation;

Explanation:

• COUNT(*): This counts the number of entries for each Occupation.
• LOWER(Occupation): This converts the occupation name to lowercase.
• CONCAT('There are a total of ', COUNT(*), ' ', LOWER(Occupation), 's.'): This formats the output string as required.
• GROUP BY Occupation: This groups the results by Occupation.
• ORDER BY COUNT(*), Occupation: This sorts the results by the count of each occupation in ascending order and then by the occupation name alphabetically if the counts are the same.

Combined SQL for Both Queries

You would typically run these queries separately, but here they are together for reference:

-- Query 1
SELECT CONCAT(Name, '(', LEFT(Occupation, 1), ')') AS NameWithProfessionInitial
FROM OCCUPATIONS
ORDER BY Name;

-- Query 2
SELECT 
    CONCAT('There are a total of ', COUNT(*), ' ', LOWER(Occupation), 's.') AS OccupationCountStatement
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(*), Occupation;

These queries will provide the required results as specified in the prompt.

Each Name is sorted alphabetically and displayed underneath

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

Note: Print NULL when there are no more names corresponding to an occupation.

To pivot the OCCUPATIONS table so that each name is sorted alphabetically and displayed under its corresponding occupation, we can use the CASE statement along with the ROW_NUMBER() window function to create the necessary rows for each occupation. Here’s the SQL query to achieve this:

WITH NumberedOccupations AS (
    SELECT 
        Name,
        Occupation,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum
    FROM OCCUPATIONS
)
SELECT 
    MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor,
    MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor,
    MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer,
    MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor
FROM NumberedOccupations
GROUP BY RowNum
ORDER BY RowNum;

Explanation:

1. NumberedOccupations CTE:
   • This Common Table Expression (CTE) assigns a row number to each name within its occupation group, ordered alphabetically by name.
   • ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum creates a unique row number for each name within each occupation.
2. Pivoting and Grouping:
   • The main SELECT statement pivots the data by creating columns for each occupation.
   • MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor checks the Occupation and assigns the Name to the corresponding column. The MAX function is used to aggregate the names correctly.
   • GROUP BY RowNum ensures that names with the same row number (i.e., same position in their sorted order) are grouped together in the result set.
   • ORDER BY RowNum orders the final result by the row number, ensuring the names are listed in alphabetical order within each occupation.

Running the Query:

When you run this query, it will pivot the data so that each row contains the names of individuals from different occupations, sorted alphabetically within each occupation.

Sample Output:

Assuming the OCCUPATIONS table has the following data:

Name         | Occupation
-------------|-------------
Alice        | Doctor
Bob          | Professor
Charlie      | Singer
David        | Actor
Eve          | Doctor
Faythe       | Singer
Grace        | Professor

The query would produce the following output:

Doctor   | Professor | Singer  | Actor
---------|-----------|---------|--------
Alice    | Bob       | Charlie | David
Eve      | Grace     | Faythe  | NULL
NULL     | NULL      | NULL    | NULL

This ensures that each name is correctly listed under its corresponding occupation, with NULL values filling in where there are no more names for an occupation.

Node type of Binary Tree

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Write a query to find the node type of Binary Tree ordered by the node’s value. Output one of the following for each node:

• Root: If the node is root node.
• Leaf: If node is leaf node.
• Inner: If node is neither root nor leaf node.

We’ll use subqueries and joins to identify the root, leaf, and inner nodes properly.

Query to determine the node types in the Binary Tree:

SELECT 
    N,
    CASE
        WHEN P IS NULL THEN 'Root'
        WHEN N NOT IN (SELECT DISTINCT P FROM BST WHERE P IS NOT NULL) THEN 'Leaf'
        ELSE 'Inner'
    END AS NodeType
FROM 
    BST
ORDER BY 
    N;

Explanation:

1. Identifying the Root Node:
   • WHEN P IS NULL THEN 'Root': This condition identifies the root node, which has no parent (P is NULL).
2. Identifying Leaf Nodes:
   • WHEN N NOT IN (SELECT DISTINCT P FROM BST WHERE P IS NOT NULL) THEN 'Leaf': This subquery checks if a node N is not present as a parent in the BST table, which means it has no children and thus is a leaf node.
3. Identifying Inner Nodes:
   • ELSE 'Inner': If the node is neither a root nor a leaf, it must be an inner node.
4. Ordering the Results:
   • ORDER BY N: This sorts the results by the node value.

Complete Example:

Let’s assume the BST table has the following data:

CREATE TABLE BST (N INT, P INT);
INSERT INTO BST (N, P) VALUES 
(1, NULL),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 3);

SELECT 
    N,
    CASE
        WHEN P IS NULL THEN 'Root'
        WHEN N NOT IN (SELECT DISTINCT P FROM BST WHERE P IS NOT NULL) THEN 'Leaf'
        ELSE 'Inner'
    END AS NodeType
FROM 
    BST
ORDER BY 
    N;

Expected Output:

N  | NodeType
---|---------
1  | Root
2  | Inner
3  | Inner
4  | Leaf
5  | Leaf
6  | Leaf

This query should run correctly in MySQL and provide the desired result, categorizing each node as ‘Root’, ‘Inner’, or ‘Leaf’ based on its position in the binary tree.