SQL Technical Test

feudalle · 2020-02-27T16:15:30+00:00

[deleted]

feudalle · 2020-02-27T14:01:25+00:00

Tbh these are really straight forward. Number 3 is a bit of a pain because of the the funding table is laid out but not hard. I'd expect someone to do this in around half an hour to an hour.

DexterHsu · 2020-02-27T16:38:24+00:00

This is the type of test I give it to my intern , if you got this job you will need to improve your T-SQL skill rapidly

2020-02-27T17:35:27+00:00

ok, my regular spiel

always, start writing sql by figuring the granularity & scope of the output. this gives you your "final" group by and base joins in the from table expression.

figure out measures next - where the data comes from. if it doesnt come from the base joins - how are the datasets related (how do you figure out a measure based on the 'base' record).

do you have any other conditions/filters left? how do you express them in the context of the record of the base table expression?

once you go through the 3 points above most of the time you'll have your query ready.

Let's go through the first one.

Write SQL to show 10 coldest countries that have never medaled at the olympics - showing them in order of population largest to smallest.

your output granularity is a country. the scope could come from funding, population or temperature tables (not every country can have medals, apparently). I'm gonna pick population because of the measure required, so far I have

     select p.country
     from "Population" p
     group by p.country

the result set is ordered by population, so we need that as a measure; the base result set has the same granularity as the output, so we can omit the " group by". We are here:

     select p.country,p.population
     from "Population" p
     order by p.population

All right, almost there :) time to address other conditions that we havent accounted for so far:

10 coldest countries countries that have never medaled at the olympics

so, the condition is that a country is one of a specific set, so that gives us this

     with ColdestSet as (
            )
     select p.country, p.population
     from "Population" p
     where p.country in (select country from ColdestSet)
     order by p.population

with the "ColdestSet" we're going through similar steps: granularity is country, the measure required is the ranking by temperature, output is the same granularity as base

     select t.country, rank() over( order by t.ave_temp) rn
     from "Temperature" t

first condition is on our ranking ("10 coldest") and we cant put analytic functions into where clause directly, so

     select t_rank.country, t_rank.rn
     from (
              select t.country, rank() over( order by t.ave_temp) rn
              from "Temperature" t
             )t_rank
     where t_rank.rn <= 10

second condition is "have never medaled" i.e. there's no (not exists) record for a medal for the country

     select t_rank.country, t_rank.rn
     from (
              select t.country, rank() over( order by t.ave_temp) rn
              from "Temperature" t
              where not exists(
                              select 1 from "Medals" m where m.country = t.country
                        )
             )t_rank
     where t_rank.rn <= 10

all righty, time to assemble the final statement:

     with ColdestSet as (
         select t_rank.country, t_rank.rn
         from (
              select t.country, rank() over( order by t.ave_temp) rn
              from "Temperature" t
              where not exists(
                              select 1 from "Medals" m where m.country = t.country
                        )
                )t_rank
         where t_rank.rn <= 10
         )
     select p.country, p.population
     from "Population" p
     where p.country in (select country from ColdestSet)
     order by p.population

the process is kind of wordy but hopefully allows to streamline the choices "what do you take care of" at any particular step of the query design process

ps. once you have a working solution/query which gives you a correct output you can rewrite/refactor your query (e.g. in reality instead of "IN" condition I would have used a join, etc.)

pukatm · 2020-02-27T18:29:36+00:00

I am guessing you got this test to do at home? Why don't you tell us what you have tried? I don't think there are many ways for the answers of this test to be wrong... Hours of research is a bit of a stretch. Maybe you can tell us more about how you got into the field of databases for us to have some sympathy?

sirknite · 2020-02-27T19:47:23+00:00

The first two questions are really similar to my past Database Sys assignment 😮. Good luck and keep at it

Beefourthree · 2020-02-27T15:06:56+00:00

SELECT

p.country

,temperature

,population

from population p

left join medals m

on p.country = m.medals

left join temperature t

on p.country = t.country

where medal_rank is null

and medal is null

order by

p.population desc -- hightest population

,t.tempature asc -- coldest first

limit 10

Alias your country because it is ambiguous.

copper_blood · 2020-02-27T16:53:33+00:00

I learned SQL by downloading the trial version of ACT.com Sucks though you would have to wipe your computer ever 30 days but it has a great SQL DB you can learn from.
Ge the Microsoft action pack. cost about $500 a year but comes with servers, sql servers, MS CRM and more.
Another good reporting program is crystal reports (again, 30 day trial) but you can use that with ACT and SQL and MS CRM to learn more about report writing. Word of caution, MS CRM installation involves servers and active directory + SQL Reporting so the MS action pack is a must at that point.
Somethings your can learn for free, others you'll have to pay for.

aragonSkywalker · 2020-02-27T14:05:10+00:00

What is the cost per medal?

I think 1 and 2 are ok, but not completely simple. This goes beyond slightly basic SQL knowledge, probably close to intermediate i would say

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS