#install.packages("DBI")
library(DBI)

Set up the connection

# Set up the ODBC connection
library(DBI)
library(odbc)
con <- dbConnect(odbc(),
                 driver = "---",
                 server = "---",
                 database = "BikeStores",
                 port = ---)

# Test the connection
#dbListTables(con)

SQL Queries

# Run the SQL query and store the result in a data frame
query_result <- dbGetQuery(con, "SELECT 
    ord.order_id,
    CONCAT(cus.first_name, ' ', cus.last_name) AS customer_name,
    cus.city,
    cus.state,
    ord.order_date
FROM sales.orders ord
JOIN sales.customers cus ON ord.customer_id = cus.customer_id")

# Print the result
query_result
# Run the SQL query and store the result in a data frame
query_result <- dbGetQuery(con, "SELECT 
    ord.order_id,
    CONCAT(cus.first_name, ' ', cus.last_name) AS customer_name,
    cus.city,
    cus.state,
    ord.order_date,
    SUM(ite.quantity) AS total_units,
    SUM(ite.quantity * ite.list_price) AS revenue
FROM sales.orders ord
JOIN sales.customers cus ON ord.customer_id = cus.customer_id
JOIN sales.order_items ite ON ord.order_id = ite.order_id
GROUP BY 
    ord.order_id,
    CONCAT(cus.first_name, ' ', cus.last_name),
    cus.city,
    cus.state,
    ord.order_date")

# Print the result
query_result
# Run the SQL query and store the result in a data frame
query_result <- dbGetQuery(con, "SELECT 
    ord.order_id,
    CONCAT(cus.first_name, ' ', cus.last_name) AS customer_name,
    cus.city,
    cus.state,
    ord.order_date,
    SUM(ite.quantity) AS total_units,
    SUM(ite.quantity * ite.list_price) AS revenue,
    pro.product_name
FROM sales.orders ord
JOIN sales.customers cus ON ord.customer_id = cus.customer_id
JOIN sales.order_items ite ON ord.order_id = ite.order_id
JOIN production.products pro ON ite.product_id = pro.product_id
GROUP BY 
    ord.order_id,
    CONCAT(cus.first_name, ' ', cus.last_name),
    cus.city,
    cus.state,
    ord.order_date,
    pro.product_name")

# Print the result
query_result
# Run the SQL query and store the result in a data frame
query_result <- dbGetQuery(con, "SELECT 
    ord.order_id,
    CONCAT(cus.first_name, ' ', cus.last_name) AS 'customers',
    cus.city,
    cus.state,
    ord.order_date,
    SUM(ite.quantity) AS 'total_units',
    SUM(ite.quantity * ite.list_price) AS 'revenue',
    pro.product_name,
    cat.category_name,
    sto.store_name
FROM sales.orders ord
JOIN sales.customers cus ON ord.customer_id = cus.customer_id
JOIN sales.order_items ite ON ord.order_id = ite.order_id
JOIN production.products pro ON ite.product_id = pro.product_id
JOIN production.categories cat ON pro.category_id = cat.category_id
JOIN sales.stores sto ON ord.store_id = sto.store_id
GROUP BY 
    ord.order_id,
    CONCAT(cus.first_name, ' ', cus.last_name),
    cus.city,
    cus.state,
    ord.order_date,
    pro.product_name,
    cat.category_name,
    sto.store_name")

# Print the result
query_result
library(knitr)

# Set the table width to 100% of the output
options(knitr.table.format = "html")
options(knitr.table.width = "100%")



# Run the SQL query and store the result in a data frame
query_result <- dbGetQuery(con, "SELECT 
    ord.order_id,
    CONCAT(cus.first_name, ' ', cus.last_name) AS 'customers',
    cus.city,
    cus.state,
    ord.order_date,
    SUM(ite.quantity) AS 'total_units',
    SUM(ite.quantity * ite.list_price) AS 'revenue',
    pro.product_name,
    cat.category_name,
    bra.brand_name,
    sto.store_name,
    CONCAT(sta.first_name, ' ', sta.last_name) AS 'sales_rep'
FROM sales.orders ord
JOIN sales.customers cus ON ord.customer_id = cus.customer_id
JOIN sales.order_items ite ON ord.order_id = ite.order_id
JOIN production.products pro ON ite.product_id = pro.product_id
JOIN production.categories cat ON pro.category_id = cat.category_id
JOIN sales.stores sto ON ord.store_id = sto.store_id
JOIN sales.staffs sta ON ord.staff_id = sta.staff_id
JOIN production.brands bra ON pro.brand_id = bra.brand_id
GROUP BY 
    ord.order_id,
    CONCAT(cus.first_name, ' ', cus.last_name),
    cus.city,
    cus.state,
    ord.order_date,
    pro.product_name,
    cat.category_name,
    bra.brand_name,
    sto.store_name,
    CONCAT(sta.first_name, ' ', sta.last_name)")

# Print the result
#head(query_result,10)

# Print the result using kable()
kable(head(query_result,10))
order_id customers city state order_date total_units revenue product_name category_name brand_name store_name sales_rep
1 Johnathan Velazquez Pleasanton CA 2016-01-01 2 1199.98 Electra Townie Original 7D EQ - 2016 Cruisers Bicycles Electra Santa Cruz Bikes Mireya Copeland
1 Johnathan Velazquez Pleasanton CA 2016-01-01 1 599.99 Electra Townie Original 7D EQ - Women’s - 2016 Cruisers Bicycles Electra Santa Cruz Bikes Mireya Copeland
1 Johnathan Velazquez Pleasanton CA 2016-01-01 2 3098.00 Surly Straggler - 2016 Cyclocross Bicycles Surly Santa Cruz Bikes Mireya Copeland
1 Johnathan Velazquez Pleasanton CA 2016-01-01 1 2899.99 Trek Fuel EX 8 29 - 2016 Mountain Bikes Trek Santa Cruz Bikes Mireya Copeland
1 Johnathan Velazquez Pleasanton CA 2016-01-01 2 3599.98 Trek Remedy 29 Carbon Frameset - 2016 Mountain Bikes Trek Santa Cruz Bikes Mireya Copeland
2 Jaqueline Cummings Huntington Station NY 2016-01-01 2 1199.98 Electra Townie Original 7D EQ - 2016 Cruisers Bicycles Electra Baldwin Bikes Marcelene Boyer
2 Jaqueline Cummings Huntington Station NY 2016-01-01 1 599.99 Electra Townie Original 7D EQ - Women’s - 2016 Cruisers Bicycles Electra Baldwin Bikes Marcelene Boyer
3 Joshua Robertson Patchogue NY 2016-01-02 1 599.99 Electra Townie Original 7D EQ - Women’s - 2016 Cruisers Bicycles Electra Baldwin Bikes Venita Daniel
3 Joshua Robertson Patchogue NY 2016-01-02 1 999.99 Surly Wednesday Frameset - 2016 Mountain Bikes Surly Baldwin Bikes Venita Daniel
4 Nova Hess Duarte CA 2016-01-03 2 1499.98 Ritchey Timberwolf Frameset - 2016 Mountain Bikes Ritchey Santa Cruz Bikes Genna Serrano 
# Close the connection
dbDisconnect(con)