365 Days of Daily Coding: Day 16

Since today is a weekend, I had extra time to complete more challenges today unlike weekdays.

Challenge 1:

Given a table ‘World’ with ‘name’, ‘continent’, ‘area’, ‘population’ and ‘gdp’, I had to find out the countries that had population larger than 25 million or area bigger than 3 million square km.

Table: World

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000
Albania	Europe	28748	2831471	12960000
Algeria	Africa	2381741	37100000	188681000
Andorra	Europe	468	78115	3712000
Angola	Africa	1246700	20609294	100990000

Expected Output:

name	population	area
Afghanistan	25500100	20343000
Algeria	37100000	188681000

My solution:

SELECT name, population, area 
FROM world 
WHERE population > 25000000 OR area > 3000000;

It was an easy challenge easily executable by ‘SELECT’ clause with ‘WHERE’ specifying the logical conditions.

Challenge 2:

Given a table ‘courses’ with the column ‘student’ and ‘class’, I had to find out those classes with equal to or more than 5 students.

Table: courses

student	class
A	Math
B	English
C	Math
D	Biology
E	Math
F	Computer
G	Math
H	Math
I	Math

Expected Output:

class
Math

My solution:

SELECT class 
FROM (SELECT DISTINCT class, student FROM courses) 
GROUP BY class HAVING COUNT(student) >= 5 
Runtime = 430 ms

This was also another easy challenge easily completed using ‘SELECT’ clause with ‘GROUP BY’ function to group all the students in a class and lastly using ‘COUNT’ to count the number of students in the group.

Other Submitted Solution from Discussions:

SELECT class FROM courses
GROUP BY class HAVING COUNT(DISTINCT student) >= 5 
Runtime = 454 ms

This is very similar to my solution above for Challenge 2. The only difference is rather than using subquery to identify the distinct students, this query directly counts the distinct number of students. What’s also seen here is that the runtime of this query is longer than the runtime of my solution which indicates subqueries optimises performance.

Challenge 3:

Given a table ‘salary’ with columns ‘id’, ‘name’, ‘sex’ and ‘salary’, I had to update the sexes in the table; change ‘f’ to ‘m’ and ‘m’ to ‘f’ without using the ‘SELECT’ statement.

Table: salary

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

Expected Output:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

My solution:

Update salary
Set sex = CASE sex
    WHEN 'm' Then 'f'
    WHEN 'f' Then 'm'
    ELSE sex
    END
WHERE sex in ('f','m');
Runtime: 519ms

Initially my solution didn’t work so I had to google and I found out that my ‘CASE’ syntax was not correct.

Other Submitted Solution from Discussions:

UPDATE salary SET sex = DECODE(sex,'m','f','m');
Runtime: 426 ms

The ‘DECODE’ function used here has the functionality of IF-THEN-ELSE statement.

• Facebook
• Instagram
• LinkedIn
• GitHub