Advanced SQL Puzzle 1

365 Days of Daily Coding: Day 43

I didn’t have quite a productive day today. It’s a public holiday so I was basically lazying around and web surfing.

I read Bill and Melinda Gates foundation annual letter in the morning. Their letter moved me and made me hopeful of the future that lies ahead for the world. One thing that is reitereated quite frequently is of the need for everyone in the world to dissolve differences and work towards a common goal of serving the humanity.

I have always been of the idea that in this world there is no such thing as mine-yours or ours-theirs. Our lives are all interconnected. Think about the wars which resulted in surge of refugees. I do not want to go to the root cause analysis of refugee crisis. What I want to point out is that, although we may think that his/her suffering or that incident does not affect me, it does. You just don’t see it. That is why the world needs to come together, dissolve their differences and work for the common good.

The idea of interconnectedness can be a difficult concept to digest. The only way to see it is to open your eyes.

As for my today’s challenge, I completed 2 easy challenges in sqlpad and 1 advanced sql challenge. SQLpad UX is bothersome although I had been praising their clean interface in my earlier post. Having been in my 34th challenge, they are becoming quite repetitive so I tried to look for other sites and found Advanced SQL challenge. There is no built in query editor to run queries unlike SQL pad but I use the Live Oracle SQL to build a temporary table and test my query.

Easy SQL Challenge 1:

SELECT title 
FROM (SELECT f.title, COUNT(i.inventory_id) AS counts
	  FROM film f LEFT JOIN inventory i 
	  ON f.film_id = i.film_id 
	  GROUP BY f.film_id) AS k
  WHERE counts > 7;

Easy SQL Challenge 2:

SELECT film_category, count(*) FROM
(SELECT CASE 
WHEN length < 60 THEN 'short'
WHEN length >=60 AND length < 100 THEN 'medium'
ELSE 'long'
END AS film_category
FROM film) AS k
GROUP BY film_category;

Advanced SQL Challenge 1:

WITH temp As (
Select 1001 As ID, 'M' As Gender FROM Dual Union All
Select 2002 As ID, 'M' As Gender FROM Dual Union All
Select 3003 As ID, 'M' As Gender FROM Dual Union All
Select 4004 As ID, 'M' As Gender FROM Dual Union All
Select 5005 As ID, 'M' As Gender FROM Dual Union All
Select 6006 As ID, 'F' As Gender FROM Dual Union All
Select 7007 As ID, 'F' As Gender FROM Dual Union All
Select 8008 As ID, 'F' As Gender FROM Dual Union All
Select 9009 As ID, 'F' As Gender FROM Dual 
), males AS (
Select ID, Gender, Rank() Over(Order by ID) AS rnk from temp Where Gender = 'M'
), females AS (
SELECT ID, Gender, Rank() Over(Order by ID) AS rnk FROM temp Where Gender = 'F')

Select males.ID, males.Gender, females.ID, females.Gender 
From males LEFT JOIN females 
ON males.rnk = females.rnk ;

You could use ROW_NUMBER() instead of the RANK() to generate the serial number instead and it would work equally well.

Karma

Leave a comment