Write R script For Customer Details

# Step 1: Install and load required packages
# install.packages("RSQLite") # Run this once if not installed
library(RSQLite)

# Step 2: Create a SQLite database and insert sample Indian customer data
# (In exam, database already exists, but for demonstration we'll create one)

# Create a connection to a new SQLite database file
conn <- dbConnect(SQLite(), dbname = "customer_data.db")

# Create the customer table with sample Indian 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")
)

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

# Step 3: Import the customer table from the database
# Query to select all data from customer table
imported_data <- dbGetQuery(conn, "SELECT * FROM customer")

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

# Step 5: Display summary and structure of the data
cat("\n========== Structure of Data ==========\n")
str(imported_data)

cat("\n========== Summary of Data ==========\n")
summary(imported_data)

# Step 6: Display specific columns
cat("\n========== Customer Names and Bills ==========\n")
print(imported_data[, c("CustName", "Bill")])

# Step 7: Display customers from specific city (e.g., Mumbai)
cat("\n========== Customers from Mumbai ==========\n")
mumbai_customers <- subset(imported_data, City == "Mumbai")
print(mumbai_customers)

# Step 8: Close the database connection
dbDisconnect(conn)

# Alternative method: If database already exists, just connect and import
# conn <- dbConnect(SQLite(), dbname = "existing_database.db")
# imported_data <- dbGetQuery(conn, "SELECT * FROM customer")
# print(imported_data)
# dbDisconnect(conn)

# Step 1: Load required libraries library(RSQLite) library(ggplot2) # For advanced plotting # Step 2: Connect to database and import data conn <- dbConnect(SQLite(), dbname = "customer_data.db") customer_data <- dbGetQuery(conn, "SELECT * FROM customer") # Step 3: Create histogram using base R cat("\n========== Creating Histogram (Base R) ==========\n") # Set up graphics parameters for better display par(mfrow = c(1, 2)) # For side-by-side plots # Histogram 1: Basic histogram with default settings hist(customer_data$Bill, main = "Distribution of Customer Bills", xlab = "Bill Amount (₹)", ylab = "Number of Customers", col = "lightblue", border = "black", breaks = 5) # Number of bins # Histogram 2: Enhanced histogram with more details hist(customer_data$Bill, main = "Bill Distribution Analysis", xlab = "Bill Amount (₹)", ylab = "Frequency", col = c("#FF6B6B", "#4ECDC4", "#45B7D1", "#96CEB4", "#FFEAA7"), border = "white", breaks = 6, probability = TRUE) # Show density instead of frequency # Add density curve lines(density(customer_data$Bill), col = "red", lwd = 2) # Add vertical lines for mean and median abline(v = mean(customer_data$Bill), col = "blue", lwd = 2, lty = 2) abline(v = median(customer_data$Bill), col = "green", lwd = 2, lty = 3) # Add legend legend("topright", legend = c("Mean", "Median", "Density"), col = c("blue", "green", "red"), lty = c(2, 3, 1), lwd = 2, cex = 0.8) # Step 4: Create histogram using ggplot2 (More modern and attractive) cat("\n========== Creating Histogram (ggplot2) ==========\n") # Create a data frame with bill categories customer_data$Bill_Category <- cut(customer_data$Bill, breaks = c(0, 2000, 3000, 4000, 5000), labels = c("Low (0-2000)", "Medium (2000-3000)", "High (3000-4000)", "Very High (4000+)")) # Basic ggplot histogram p <- ggplot(customer_data, aes(x = Bill)) + geom_histogram(binwidth = 500, fill = "#2E86AB", color = "white", alpha = 0.7) + labs(title = "Distribution of Customer Bills", subtitle = "Sample Indian Customer Data", x = "Bill Amount (₹)", y = "Number of Customers") + theme_minimal() + theme(plot.title = element_text(hjust = 0.5, face = "bold"), plot.subtitle = element_text(hjust = 0.5)) print(p) # Step 5: Additional analysis - Bill statistics by city cat("\n========== Bill Statistics by City ==========\n") city_stats <- aggregate(Bill ~ City, data = customer_data, FUN = summary) print(city_stats) # Step 6: Create bar plot of average bill by city city_avg <- aggregate(Bill ~ City, data = customer_data, FUN = mean) barplot(city_avg$Bill, names.arg = city_avg$City, col = rainbow(length(city_avg$City)), main = "Average Bill by City", xlab = "City", ylab = "Average Bill (₹)", ylim = c(0, 3500)) # Add text labels on bars text(x = 1:length(city_avg$Bill), y = city_avg$Bill + 100, labels = paste("₹", round(city_avg$Bill, 0)), cex = 0.8) # Step 7: Create boxplot to compare bills across cities boxplot(Bill ~ City, data = customer_data, main = "Bill Distribution by City", xlab = "City", ylab = "Bill Amount (₹)", col = c("lightblue", "lightgreen", "lightpink", "lightyellow", "lightcyan", "lavender", "moccasin", "peachpuff")) # Step 8: Close database connection dbDisconnect(conn) # Step 9: Save plots (optional) # ggsave("customer_bill_histogram.png", plot = p, width = 8, height = 6)