Skip to content

ravnhq/DB-Nerdery-Challenges2

 
 

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

11 Commits
 
 
 
 
 
 

Repository files navigation

RAVN logo

Postgres logo


A project to show off your skills on databases & SQL using a real database

📝 Table of Contents

🤓 Case

As a developer and expert on SQL, you were contacted by a company that needs your help to manage their database which runs on PostgreSQL. The database provided contains four entities: Employee, Office, Countries and States. The company has different headquarters in various places around the world, in turn, each headquarters has a group of employees of which it is hierarchically organized and each employee may have a supervisor. You are also provided with the following Entity Relationship Diagram (ERD)

ERD - Diagram

Comparison


🛠️ Docker Installation

  1. Install docker

📚 Recover the data to your machine

Open your terminal and run the follows commands:

  1. This will create a container for postgresql:
docker run --name nerdery-container -e POSTGRES_PASSWORD=password123 -p 5432:5432 -d --rm postgres:13.0
  1. Now, we access the container:
docker exec -it -u postgres nerdery-container psql
  1. Create the database:
create database nerdery_challenge;
  1. Restore de postgres backup file
cat /.../src/dump.sql | docker exec -i nerdery-container psql -U postgres -d nerdery_challenge
  • Note: The ... mean the location where the src folder is located on your computer
  • Your data is now on your database to use for the challenge

📊 Excersises

Now it's your turn to write SQL querys to achieve the following results:

  1. Count the total number of states in each country.
Your query here

result_1

  1. How many employees do not have supervisores.
Your query here

result_2

  1. List the top five offices address with the most amount of employees, order the result by country and display a column with a counter.
Your query here

result_3

  1. Three supervisors with the most amount of employees they are in charge.
Your query here

result_4

  1. How many offices are in the state of Colorado (United States).
Your query here

result_5

  1. The name of the office with its number of employees ordered in a desc.
Your query here

result_6

  1. The office with more and less employees.
Your query here

result_7

  1. Show the uuid of the employee, first_name and lastname combined, email, job_title, the name of the office they belong to, the name of the country, the name of the state and the name of the boss (boss_name)
Your query here

result_8

About

No description, website, or topics provided.

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published