Brain Teasers - Advanced SQL
If you are a SQL enthusiast like me and have implemented Complex scenarios in SQL, then this article is for you. I have been working on some very typical scenarios lately and thought it would be a good brain teasers for you guys. I am throwing some 6 scenarios questions at you, I would recommend, first try it yourselves, If you are not able to do, then look down for the solutions. Also, there could be multiple ways of solving a problem, so by no means this is the only solution. The solutions provided are from oracle sql, however you can use any other db.
Please do put your solution in the comments below, so that we all get to learn from each other.
SCENARIO QUESTIONS >>>
Scenario 1. Write a SQL, which takes SYSDATE as input and gives out the last day of the previous Month. Sample output with SYSDATE as input.
Scenario 2. Prompt the user to input the Year and print the last day of each month of that year. Sample output as below for 2019 input
Scenario 3. Prompt user to input a string, print the reverse of that string without using direct "reverse" function. Sample output with 'rahul' as input.
Scenario 4. Print the Arithmetic Progression Series. Prompt the user to enter the no of terms, first term, and incremental value. Sample output as below
Scenario 5. Prompt the user to get the number of terms and print the fibonacci series with those many terms. Sample output as below.
Since Cricket world cup concluded today, I thought, it would be unfair to not include a problem from the same. So here it goes.
Scenario 6. You are an ICC official and you have to put together the fixtures for the world cup. There are league matches to be played where in every team will play against every other team. The matches are supposed to start from 1st, Aug, 2019 and each subsequent match will be played with a gap of one day. The sequence doesn't matter, you can have single team playing all its matches together. Write a program which prompts the user to enter the number of teams playing the world cup and it generates the fixtures. Sample output as below.
scroll
scroll
scroll
scroll
If you are ready with your answers then you can scroll down.
scroll
scroll
scroll
scroll
SCENARIO SOLUTIONS >>>
Solution 1.
Here, I have made use of last_day function, which gives the last day of that month. I have then subtracted 31 from it. This will ensure that I will definitely land up in the date from last month, then again I have used Last_day function to get to the last day of previous month.
Solution 2.
Here, I have created a series of 1 to 12, using them as the month part of the date, and using the user prompt as the year and then using last Day function to get the last day of each month in that year.
Solution 3.
Here, I have first used a substr function to enumerate all the characters of the string in different rows, Then I am using listagg function to aggregate them back in reverse order of rownum. Finally to restrict the number of terms for
the string, i had used level < length(str) in the connect by clause.
Solution 4.
This is a simple one, I am using connect by to iterate through the number of terms, and then putting the formula as a + (n-1)d. Here i had input a as 5, d as 10 and no of terms as 10.
Solution 5.
I have used a with clause with recursion where in I have initiated the series with prev and stillprev values as 1 and 0 respectively. Subsequently I am advancing the seq value with one and setting prev value with prev+stillprev and setting the stillprev value as prev. Which is the property of fibonacci series, that any given term is the sum of previous two terms. While I gave 10 as input prompt, i got 10 terms of fibonacci series printed. Finally i have used a seq<num filter to restrict the number of terms with input prompt.
Solutions 6.
Here I have created couple of tables. N_Team to capture the number of teams. Dates to generate the alternate dates starting from 1st Aug. Teams to generate rows with 1 to N terms. Matches to create a cross product from two alias of teams with a constraint that one seq is less than the other to have only one match
between any two teams. Finally I am joining the Dates and Matches tables to get the fixtures generated.
Hope you had fun solving the above problems. Happy Learning !!