DbSchema | SQL Interview Questions

The problems are written for PostgreSql.

1. Consider the following tables and data:

   CREATE TABLE “public”.countries (
   country_code char(2) NOT NULL ,
   country_name varchar(100) NOT NULL ,
   CONSTRAINT pk_countries_country_code PRIMARY KEY ( country_code )
   );

   CREATE TABLE “public”.cities (
   city_id integer NOT NULL ,
   city_name varchar(100) NOT NULL ,
   country_code char(2) NOT NULL ,
   inhabitants bigint NOT NULL ,
   average_salary integer NOT NULL ,
   CONSTRAINT pk_cities_city_id PRIMARY KEY ( city_id )
   );

   ALTER TABLE “public”.cities ADD CONSTRAINT fk_cities_countries FOREIGN KEY ( country_code ) REFERENCES “public”.countries( country_code );

   INSERT INTO “public”.countries( country_code, country_name ) VALUES ( ‘BB’, ‘Bartinia’ );
   INSERT INTO “public”.countries( country_code, country_name ) VALUES ( ‘CF’, ‘Cliffinia’ );
   INSERT INTO “public”.countries( country_code, country_name ) VALUES ( ‘GM’, ‘Gemerani’ );
   INSERT INTO “public”.countries( country_code, country_name ) VALUES ( ‘LT’, ‘Lituania’ );
   INSERT INTO “public”.countries( country_code, country_name ) VALUES ( ‘MF’, ‘Memrish’ );
   INSERT INTO “public”.countries( country_code, country_name ) VALUES ( ‘NR’, ‘Nirania’ );
   INSERT INTO “public”.countries( country_code, country_name ) VALUES ( ‘SC’, ‘Scanji’ );
   INSERT INTO “public”.countries( country_code, country_name ) VALUES ( ‘RM’, ‘Rimani’ );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 0, ‘Fremont’, ‘MF’, 731057, 3653 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 1, ‘Fresno’, ‘BB’, 70992, 4593 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 2, ‘Portland’, ‘SC’, 67120, 4593 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 3, ‘Buffalo’, ‘CF’, 768156, 3397 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 4, ‘Detroit’, ‘GM’, 227334, 391 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 5, ‘Las Vegas’, ‘MF’, 660319, 124 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 6, ‘St. Paul’, ‘SC’, 806673, 3496 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 7, ‘Wichita’, ‘CF’, 29817, 4024 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 8, ‘Oakland’, ‘MF’, 811170, 756 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 9, ‘Las Vegas’, ‘SC’, 945416, 4622 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 10, ‘Norfolk’, ‘SC’, 152733, 3819 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 11, ‘Boston’, ‘GM’, 910276, 16 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 12, ‘St. Louis’, ‘NR’, 999146, 3682 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 13, ‘Norfolk’, ‘GM’, 539992, 4781 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 14, ‘Greensboro’, ‘CF’, 977512, 4118 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 15, ‘Milwaukee’, ‘MF’, 158437, 943 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 16, ‘Tulsa’, ‘MF’, 549088, 1630 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 17, ‘St. Paul’, ‘NR’, 93545, 3757 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 18, ‘Tulsa’, ‘SC’, 443390, 1402 );
   INSERT INTO “public”.cities( city_id, city_name, country_code, inhabitants, average_salary ) VALUES ( 19, ‘Shreveport’, ‘GM’, 412861, 1150 );

Write a query which list all countries with a population over 2.000.000 ( two million ) inhabitants ordered by inhabitants descending.

The query result should look like :

country_name sum     
============ ======= 
Memrish      2910071 
Scanji       2415332 
Gemerani     2090463 

Solution:

select country_name, sum(inhabitants)
from 
  countries co join cities ci on ( co.country_code=ci.country_code ) 
group by  
  co.country_name 
having sum(inhabitants) > 2000000
order by sum(inhabitants) desc;

2. Consider the data from the first problem. List all countries without any city.

The result should return

country_name 
============ 
Lituania     
Rimani       

Solution:

select country_name 
from countries co 
where 
   not exists ( select 1 from cities ci where ci.country_code=co.country_code )
   

3. List all cities with city_name and country_code belonging to a country with at least 5 cities.

Expected result:

country_code city_name 
============ ========= 
MF           Fremont   
SC           Portland  
MF           Las Vegas 
SC           St. Paul  
MF           Oakland   
SC           Las Vegas 
SC           Norfolk   
MF           Milwaukee 
MF           Tulsa     
SC           Tulsa     

Solution:

select 
   country_code, city_name 
from 
   cities ci 
where 
   country_code in ( select country_code from cities t group by country_code having count(*) >= 5 );

 

4. Consider this two tables

   create table tasks( id integer not null,
   name varchar(40) not null,
   unique(id ));

   create table reports (
   id integer not null,
   task_id integer not null,
   candidate varchar(40) not null,
   score integer not null,
   unique(id ));

Write a query which will list the task difficulty considering the average results from the result table. An average result below equal 20 should return ‘Hard’,
between 20 and below equal 60 ‘Medium’ and higher then 60 ‘Easy’. Display only tasks with at least one report. Order the results by taskid.

For this consider two test data sets:

 insert into tasks( id, name ) values( 101, 'MinDist');
 insert into tasks( id, name ) values( 123, 'MinDist');
 insert into tasks( id, name ) values( 142, 'MinDist');
 insert into tasks( id, name ) values( 300, 'Tricoloring');
 
 
 insert into reports( id, task_id, candidate, score ) values ( 13, 101, 'John Smith', 100 );
 insert into reports( id, task_id, candidate, score ) values ( 24, 123, 'Delaney Lloyd', 34 );
 insert into reports( id, task_id, candidate, score ) values ( 37, 300, 'Monroe Jimenez', 50 );
 insert into reports( id, task_id, candidate, score ) values ( 49, 101, 'Stanley Price', 45 );
 insert into reports( id, task_id, candidate, score ) values ( 51, 142, 'Tanner Sears', 37 );
 insert into reports( id, task_id, candidate, score ) values ( 68, 142, 'Lara Fraser', 3 );
 insert into reports( id, task_id, candidate, score ) values ( 83, 300, 'Tanner Sears', 0 );
 

Should return:

 task_id task_name   dificulty 
 ======= =========== =========     
     101 MinDist     Easy      
     123 MinDist     Medium    
     142 MinDist     Hard      
     300 Tricoloring Medium    
     
     

And the second data set:

 insert into tasks( id, name ) values( 3, 'Cake');
 insert into tasks( id, name ) values( 6, 'GameOfNuts');
 insert into tasks( id, name ) values( 7, 'CircleIntersectionArea');
 insert into tasks( id, name ) values( 9, 'JessicaAndBrian');
  
  
 insert into reports( id, task_id, candidate, score ) values ( 2, 6, 'Paul Sat', 0 );
 insert into reports( id, task_id, candidate, score ) values ( 3, 3, 'Karen M.', 30 );
 insert into reports( id, task_id, candidate, score ) values ( 5, 3, 'Oscar Glad', 10 );
 insert into reports( id, task_id, candidate, score ) values ( 6, 9, 'Karen M.', 60 );
 insert into reports( id, task_id, candidate, score ) values ( 11, 6, 'Paul Sat', 81 );
 insert into reports( id, task_id, candidate, score ) values ( 13, 6, 'Paul Sat', 100 );
 

Should return:

     task_id task_name       dificulty 
     ======= =============== =========       
           3 Cake            Hard      
           6 GameOfNuts      Easy      
           9 JessicaAndBrian Medium   

Solution:

SELECT
  t.id task_id,
  t.name task_name,
  CASE
    WHEN avg(score ) <= 20 THEN 'Hard'
    WHEN avg(score) <= 60 THEN 'Medium'
    ELSE 'Easy'
  END dificulty
FROM tasks t JOIN reports r ON ( t.id=r.task_id )
GROUP BY t.id, t.name
ORDER BY t.id;