나는 이렇게 학습한다/Algorithm & SQL

SQL with LOTR: Elven Wildcards

daco2020 2022. 6. 10. 18:18
반응형

Deep within the fair realm of Lothlórien, you have been asked to create a shortlist of candidates for a recently vacated position on the council.

Of so many worthy elves, who to choose for such a lofty position? After much thought you decide to select candidates by name, which are often closely aligned to an elf's skill and temperament.

Choose those with tegil appearing anywhere in their first name, as they are likely to be good calligraphers, OR those with astar anywhere in their last name, who will be faithful to the role.

Elves table:

  • firstname
  • lastname

all names are in lowercase

To aid the scribes, return the firstname and lastname column concatenated, separated by a space, into a single shortlist column, and capitalise the first letter of each name.

 

 

Solution:

SELECT 
    INITCAP(firstname || ' ' || lastname) AS shortlist 
FROM 
    Elves 
WHERE 
    firstname LIKE '%tegil%' 
    OR lastname LIKE '%star%'

'INITCAP' converts the first letter of a word to uppercase.

 

 

 

Result:

shortlist
Dontegilwe Lasel
Lasiel Astarar
Cetegilwen Iastgilwen
Rasaron Astargil
Agartegilor Garthgilen
Ethirtarwe Fastar

 

 

 

반응형

'나는 이렇게 학습한다 > Algorithm & SQL' 카테고리의 다른 글

Keep Hydrated!  (0) 2022.06.12
Opposite number  (0) 2022.06.11
SQL Basics: Raise to the Power  (0) 2022.06.09
BASICS: Length based SELECT with LIKE  (0) 2022.06.08
SQL Basics: Repeat and Reverse  (0) 2022.06.07