Q. 1. Write R script to connect to a SQLite database and import a table named customer, which contains columns: CustID, CustName, Bill, and City. Display the imported data using R commands

Q. 2. After importing the customer table from the SQLite database, create a histogram of the bill column.

# ============================================
# Q1: Connect to SQLite Database and Import Customer Table
# ============================================

# Load required libraries
library(RSQLite)

# Create SQLite database with Indian customer data
conn <- dbConnect(SQLite(), dbname = "customer_data.db")

# Sample Indian customer data
customer_data <- data.frame(
  CustID = c(101, 102, 103, 104, 105, 106, 107, 108),
  CustName = c("Rajesh Kumar", "Priya Sharma", "Amit Patel", "Sneha Reddy", 
               "Vikram Singh", "Ananya Gupta", "Rahul Joshi", "Meera Nair"),
  Bill = c(1500, 2500, 3200, 1800, 4500, 2100, 3800, 2900),
  City = c("Mumbai", "Delhi", "Ahmedabad", "Hyderabad", 
           "Jaipur", "Kolkata", "Pune", "Chennai"),
  stringsAsFactors = FALSE
)

# Write to database
dbWriteTable(conn, "customer", customer_data, overwrite = TRUE)

# Import the table
imported_data <- dbGetQuery(conn, "SELECT * FROM customer")

# Display imported data
cat("\n========== Q1: Imported Customer Data ==========\n")
print(imported_data)

# Display data structure
cat("\nData Structure:\n")
str(imported_data)

# Display summary
cat("\nData Summary:\n")
summary(imported_data)

# ============================================
# Q2: Create Histogram of Bill Column
# ============================================

cat("\n========== Q2: Creating Histogram ==========\n")

# Set up plot layout
par(mfrow = c(1, 2))

# Plot 1: Basic histogram
hist(imported_data$Bill,
     main = "Distribution of Customer Bills",
     xlab = "Bill Amount (₹)",
     ylab = "Number of Customers",
     col = "lightblue",
     border = "black",
     breaks = 5)

# Plot 2: Enhanced histogram with density curve
hist(imported_data$Bill,
     main = "Bill Distribution with Density",
     xlab = "Bill Amount (₹)",
     ylab = "Frequency",
     col = "lightgreen",
     border = "white",
     breaks = 6,
     probability = TRUE)

# Add density curve
lines(density(imported_data$Bill), col = "red", lwd = 2)

# Add mean and median lines
abline(v = mean(imported_data$Bill), col = "blue", lwd = 2, lty = 2)
abline(v = median(imported_data$Bill), col = "darkgreen", lwd = 2, lty = 3)

# Add legend
legend("topright", 
       legend = c("Mean", "Median", "Density"),
       col = c("blue", "darkgreen", "red"),
       lty = c(2, 3, 1),
       lwd = 2,
       cex = 0.7)

# Additional analysis: Bills by city
cat("\nAverage Bill by City:\n")
print(aggregate(Bill ~ City, data = imported_data, FUN = mean))

cat("\nTotal Bills by City:\n")
print(aggregate(Bill ~ City, data = imported_data, FUN = sum))

# Close connection
dbDisconnect(conn)

cat("\nAnalysis Complete!")