06.12.2024 - BLP205

BLP205-061224-Sorular.md

"init.sql" dosyasında veritabanı tasarımı ve verileri ile aşağıdaki soruları cevaplayınız. Uygulamasını docker-compose.yml kullanarak gerçekleştiriniz.

1. Kategorilere göre ürünleri fiyatlarına göre sıralayıp, her kategorinin en pahalı ürününü gösteren sorguyu yazın.
2. Her departmandaki çalışanları maaşlarına göre sıralayıp, yüzdelik dilimlerini hesaplayın.
3. Her kategorideki ürünleri fiyatlarına göre sıralayıp ilk 3'e girenleri belirleyin.
4. Her ürünün bir önceki satışına göre miktar farkını hesaplayın.
5. Her departmanda çalışanları maaşlarına göre sıralayıp, kendisinden bir üst sıradaki çalışanla maaş farkını ve sıralamasını gösteren sorguyu yazın.

docker-compose.yml

services:
  db:
    image: postgres:17.1
    environment:
      POSTGRES_DB: employee_db
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres123
    ports:
      - "5432:5432"
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

  data-generator:
    build: .
    depends_on:
      - db

Dockerfile

FROM ruby:3.3.3
WORKDIR /app
COPY Gemfile .
COPY seed_data.rb .
RUN bundle install
CMD ["ruby", "seed_data.rb"]

Gemfile

source 'https://rubygems.org'
gem 'faker'
gem 'pg'

init.sql

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE,
    category VARCHAR(50),
    price NUMERIC
);

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    sale_date DATE,
    amount NUMERIC
);

seed_data.rb

require 'faker'
require 'pg'
require 'set'
require 'securerandom'

def connect_db
  PG.connect(
    host: 'db',
    dbname: 'employee_db',
    user: 'postgres',
    password: 'postgres123'
  )
end

def seed_employees(conn)
  departments = ['IT', 'HR', 'Sales', 'Marketing', 'Engineering']
  used_names = Set.new
  
  while used_names.size < 1000
    name = Faker::Name.unique.name
    next if used_names.include?(name)
    
    department = departments.sample
    salary = rand(30000..120000)
    
    conn.exec_params(
      'INSERT INTO employees (name, department, salary) VALUES ($1, $2, $3)',
      [name, department, salary]
    )
    used_names.add(name)
  end
end

def seed_products(conn)
  categories = ['Electronics', 'Books', 'Clothing', 'Food', 'Home', 'Sports', 'Beauty', 'Toys']
  used_names = Set.new
  
  while used_names.size < 1000
    name = "#{Faker::Commerce.unique.product_name} #{SecureRandom.hex(4)}"
    next used_names.include?(name)
    
    category = categories.sample
    price = rand(10.0..1000.0).round(2)
    
    conn.exec_params(
      'INSERT INTO products (name, category, price) VALUES ($1, $2, $3)',
      [name, category, price]
    )
    used_names.add(name)
  end
end

def seed_sales(conn)
  # İlk 100 ürün için 12 aylık satış verisi oluştur
  (1..100).each do |product_id|
    (0..11).each do |month_offset|
      sale_date = Date.new(2023, 1, 1) >> month_offset # Her ayın ilk günü
      amount = rand(100.0..5000.0).round(2)
      
      conn.exec_params(
        'INSERT INTO sales (product_id, sale_date, amount) VALUES ($1, $2, $3)',
        [product_id, sale_date, amount]
      )
    end
  end

  # Diğer ürünler için rastgele satışlar
  500.times do
    product_id = rand(101..1000)
    sale_date = Faker::Date.between(from: '2023-01-01', to: '2023-12-31')
    amount = rand(100.0..5000.0).round(2)
    
    conn.exec_params(
      'INSERT INTO sales (product_id, sale_date, amount) VALUES ($1, $2, $3)',
      [product_id, sale_date, amount]
    )
  end
end


begin
  # Veritabanı bağlantısı için 5 deneme
  retries = 5
  conn = nil
  
  begin
    puts "Veritabanına bağlanılıyor..."
    conn = connect_db
  rescue PG::ConnectionBad
    if (retries -= 1) > 0
      puts "Bağlantı başarısız. Tekrar deneniyor... (#{retries} deneme kaldı)"
      sleep(5)
      retry
    else
      raise "Veritabanına bağlanılamadı!"
    end
  end

  puts "Çalışan verileri oluşturuluyor..."
  seed_employees(conn)
  
  puts "Ürün verileri oluşturuluyor..."
  seed_products(conn)
  
  puts "Satış verileri oluşturuluyor..."
  seed_sales(conn)
  
  puts "Veri oluşturma tamamlandı!"
rescue => e
  puts "Hata: #{e.message}"
ensure
  conn&.close
end

• ilk defa oluşturmak için: docker-compose up --build
• psql çalıştırmak için: docker-compose exec db psql -U postgres -d employee_db
• şayet service "db" is not running hatası alınırsa: docker-compose up db ve diğer terminalden psql çalıştırılır