Database Technologies Hub

-- Employee Management System CREATE TABLE employees ( id SERIAL PRIMARY KEY, employee_id VARCHAR(10) UNIQUE NOT NULL, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, department_id INT REFERENCES departments(id), hire_date DATE DEFAULT CURRENT_DATE, salary DECIMAL(10,2), manager_id INT REFERENCES employees(id), created_at TIMESTAMP DEFAULT NOW() ); -- Complex query for employee hierarchy WITH RECURSIVE employee_hierarchy AS ( SELECT id, first_name, last_name, manager_id, 1 as level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.first_name, e.last_name, e.manager_id, eh.level + 1 FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.id ) SELECT * FROM employee_hierarchy ORDER BY level, last_name;

-- Order Management System CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, order_date DATETIME DEFAULT CURRENT_TIMESTAMP, total_amount DECIMAL(10,2) NOT NULL, status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending', shipping_address TEXT, INDEX idx_customer (customer_id), INDEX idx_status_date (status, order_date) ); -- Get monthly sales report SELECT DATE_FORMAT(order_date, '%Y-%m') as month, COUNT(*) as total_orders, SUM(total_amount) as revenue, AVG(total_amount) as avg_order_value FROM orders WHERE status = 'delivered' GROUP BY DATE_FORMAT(order_date, '%Y-%m') ORDER BY month DESC;

// Employee Profile Management with MongoDB db.employees.insertOne({ employeeId: "EMP001", personalInfo: { firstName: "John", lastName: "Doe", email: "john.doe@company.com", phone: "+1-555-0123" }, jobDetails: { department: "Engineering", position: "Senior Developer", startDate: new Date("2020-01-15"), salary: 95000, skills: ["JavaScript", "Python", "MongoDB", "React"] }, address: { street: "123 Main St", city: "San Francisco", state: "CA", zipCode: "94105" }, performance: [ { year: 2023, rating: 4.5, bonus: 5000 }, { year: 2022, rating: 4.2, bonus: 4000 } ] }); // Complex aggregation for department analytics db.employees.aggregate([ { $match: { "jobDetails.department": "Engineering" } }, { $group: { _id: "$jobDetails.position", count: { $sum: 1 }, avgSalary: { $avg: "$jobDetails.salary" }, topSkills: { $push: "$jobDetails.skills" } }}, { $sort: { count: -1 } } ]);

# Redis for Real-time Order Tracking # Set order status HSET order:12345 status "processing" HSET order:12345 updated_at "2025-06-19T10:30:00Z" HSET order:12345 customer_id "CUST789" # Track order progress LPUSH order:12345:timeline "Order placed" LPUSH order:12345:timeline "Payment confirmed" LPUSH order:12345:timeline "Processing started" # Real-time notifications PUBLISH order_updates "{'orderId': '12345', 'status': 'processing'}" # Session management for employee login SETEX session:emp_john_doe 3600 "{'employeeId': 'EMP001', 'role': 'manager'}" # Rate limiting for API calls INCR api_calls:emp_001:$(date +%Y%m%d%H%M) EXPIRE api_calls:emp_001:$(date +%Y%m%d%H%M) 60

// Employee Search System with Elasticsearch PUT /employees/_mapping { "properties": { "employeeId": { "type": "keyword" }, "fullName": { "type": "text", "analyzer": "standard", "fields": { "suggest": { "type": "completion" } } }, "department": { "type": "keyword" }, "skills": { "type": "keyword" }, "location": { "type": "geo_point" }, "experience": { "type": "integer" }, "bio": { "type": "text" } } } // Advanced employee search GET /employees/_search { "query": { "bool": { "must": [ { "match": { "fullName": "john developer" } } ], "filter": [ { "term": { "department": "engineering" } }, { "range": { "experience": { "gte": 3 } } } ] } }, "aggs": { "skills_distribution": { "terms": { "field": "skills", "size": 10 } }, "department_breakdown": { "terms": { "field": "department" } } }, "highlight": { "fields": { "bio": {} } } }

# Employee Analytics with Apache Spark from pyspark.sql import SparkSession from pyspark.sql.functions import * from pyspark.ml.feature import VectorAssembler from pyspark.ml.regression import LinearRegression spark = SparkSession.builder.appName("EmployeeAnalytics").getOrCreate() # Load employee data employees_df = spark.read.csv("employee_data.csv", header=True, inferSchema=True) # Advanced analytics: Predict employee retention retention_features = employees_df.select( "experience_years", "satisfaction_score", "salary", "projects_completed", "training_hours", "stayed_1_year" # target variable ) # Feature engineering assembler = VectorAssembler( inputCols=["experience_years", "satisfaction_score", "salary", "projects_completed", "training_hours"], outputCol="features" ) # Prepare data for ML ml_data = assembler.transform(retention_features) train_data, test_data = ml_data.randomSplit([0.8, 0.2]) # Train retention prediction model lr = LinearRegression(featuresCol="features", labelCol="stayed_1_year") model = lr.fit(train_data) # Real-time employee performance aggregation performance_metrics = employees_df.groupBy("department") \ .agg( avg("satisfaction_score").alias("avg_satisfaction"), count("employee_id").alias("employee_count"), max("salary").alias("max_salary"), stddev("performance_rating").alias("performance_variance") ) performance_metrics.show()