SQL : The PADS problem

Hello All,

We are back with another SQL problem. This problem involves use of CONCAT() function.

You can visit following learning resources related to this particular question :

1. concat function - CONCAT (Transact-SQL) - SQL Server | Microsoft Docs 

The problem below is sourced from HackerRank. You can visit the link: The PADS | HackerRank

Give a try to solve below problem. Solution is provided as usual. Feel free to comment if any queries and also suggest alternate approach to solve this problem. Have a Happy Learning ! 

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 parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).

  2. Query the number of ocurrences 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.

Input Format

The OCCUPATIONS table is described as follows:  Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

An OCCUPATIONS table that contains the following records:

Sample Output

Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are a total of 2 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.

Solution:

(SELECT CONCAT(name, "(",SUBSTRING(occupation, 1,1),")") as name

FROM occupations

ORDER BY name);

(SELECT concat("There are a total of ", count(occupation)," ", lower(occupation),"s.")

FROM occupations

GROUP BY occupation

ORDER BY count(occupation) ASC,occupation ASC)

Comments

Post a Comment

Popular posts from this blog

Answer to SQL Leetcode Question 183. Customers Who Never Order

Answer to SQL Leetcode Question 183 183 .  Customers Who Never Order   SQL Schema Table:  Customers +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id is the primary key column for this table. Each row of this table indicates the ID and name of a customer.   Table:  Orders +-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | customerId | int | +-------------+------+ id is the primary key column for this table. customerId is a foreign key of the ID from the Customers table. Each row of this table indicates the ID of an order and the ID of the customer who ordered it.   Write an SQL query to report all customers who never order anything. Return the result table in  any order . The query result format is in the following example.   Example 1: Input: Customers table: +----+-------+ | id | name | +---...
Read more

SQL : Department Top Three Salaries

 Hello All, We are back with another SQL problem to gain understanding about SQL. This blog-post focuses on implementation of sub-queries in FROM clause. The problem below is sourced from Leetcode. You can visit the link:  Department Top Three Salaries - LeetCode To know more about SQL sub-query in FROM clause , I highly recommend this blog-post. SQL | Sub queries in From Clause - GeeksforGeeks Give a try to solve below problem. Solution is provided as usual. Feel free to comment if any queries and also suggest alternate approach to solve this problem. Have a Happy Learning !   SQL Schema Table:  Employee +--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ id is the primary key column for this table. departmentId is a foreign key of the ID from the Department table. Each row of this table indicates th...
Read more