DbSchema Database Designer

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;
  1. 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 )
  1. 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 );
  1. 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;     

DbSchema → Your Trusted Partner in Database Design

Simplify complex database workflows and improve productivity with DbSchema's advanced design and management tools

Visual Design & Modeling
Visual Design & Schema Layout

➤ Create and manage your database schema visually through a user-friendly graphical interface.

➤ Easily arrange tables, columns, and foreign keys to simplify complex database structures, ensuring clarity and accessibility.

GIT & Collaboration
Version Control & Collaboration

➤ Manage schema changes through version control with built-in Git integration, ensuring every update is tracked and backed up.

➤ Collaborate efficiently with your team to maintain data integrity and streamline your workflow for accurate, consistent results.

Data Explorer & Query Builder
Relational Data & Query Builder

➤ Seamlessly navigate and visually explore your database, inspecting tables and their relationships.

➤ Build complex SQL queries using an intuitive drag-and-drop interface, providing instant results for quick, actionable insights.

Interactive Documentation & Reporting
HTML5 Documentation & Reporting

➤ Generate HTML5 documentation that provides an interactive view of your database schema.

➤ Include comments for columns, use tags for better organization, and create visually reports.