# Load package
install.packages("sqldf")
library(sqldf)

# Create dataset 1: individual performance data
df1 <- data.frame(
  ID = c(1, 2, 3, 4, 5, 6),
  Name = c("John", "Alice", "Bob", "Mary", "Tom", "Max"),
  Age = c(25, 30, 35, 40, 45, 27),
  Score = c(80, 85, 90, 95, 100, 99)
)

# Create dataset 2: department and salary data
df2 <- data.frame(
  ID = c(1, 2, 3, 5),
  Department = c("HR", "Finance", "IT", "Operations"),
  Salary = c(50000, 60000, 70000, 90000)
)

# Display datasets
print("=== DATASET 1: PERFORMANCE DATA ===")
print(df1)

print("=== DATASET 2: DEPARTMENT DATA ===")
print(df2)

score_asc <- sqldf("
  SELECT *
  FROM df1
  ORDER BY Score ASC
")

print("=== SORTED DATA (ASCENDING SCORE) ===")
print(score_asc)

score_sum <- sqldf("
  SELECT SUM(Score)
  FROM df1
")

print("=== TOTAL SCORE (SUM) ===")
print(score_sum)

n_ids <- sqldf("
  SELECT COUNT(DISTINCT ID)
  FROM df1
")

print("=== DISTINCT ID COUNT ===")
print(n_ids)

inner_join <- sqldf("
SELECT df1.ID, df1.Name, df1.Age, df1.Score,
df2.Department, df2.Salary
FROM df1
INNER JOIN df2 ON df1.ID = df2.ID
")

print("=== INNER JOIN RESULT ===")
print(inner_join)

left_join <- sqldf("
  SELECT df1.ID, df1.Name, df1.Age, df1.Score,
         df2.Department, df2.Salary
  FROM df1
  LEFT JOIN df2 ON df1.ID = df2.ID
")

print("=== LEFT JOIN RESULT ===")
print(left_join)

# Load package
library(sqldf)

# Create dataset
employees <- data.frame(
  ID = c(1, 2, 3, 4, 5, 6, 7, 8),
  Name = c("Anna", "Brian", "Cathy", "David", "Ella", "Frank", "Grace", "Henry"),
  Department = c("HR", "Finance", "IT", "IT", "HR", "Marketing", "Finance", "IT"),
  Age = c(25, 32, 29, 41, 38, 26, 45, 31),
  Score = c(78, 88, 92, 85, 90, 76, 95, 89),
  Salary = c(50000, 62000, 71000, 68000, 59000, 52000, 80000, 75000)
)

# View dataset
print(employees)