DbSchema | SQL Interview Questions
The problems are written for PostgreSql.
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;
- 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 )
- 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 );
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;
