- 24th Nov 2022
- 06:03 am
library(DBI)
library(RSQLite)
if (exists("myDB"))
{ dbDisconnect(myDB) }
myDB <- dbConnect(drv = RSQLite::SQLite(), dbname = "classicmodels.sqlite")
##dbExecute(conn = myDB, " CREATE TABLE customers ( customerNumber INTEGER NOT NULL, customerName TEXT, contactLastName TEXT, contactFirstName TEXT, phone TEXT, addressLine1 TEXT, addressLine2 TEXT, city TEXT, state TEXT, postalCode TEXT, country TEXT, salesRepEmployeeNumber INTEGER, creditLimit REAL, PRIMARY KEY (customerNumber) FOREIGN KEY (salesRepEmployeeNumber) REFERENCES employees(employeeNumber) ); ")
dbGetQuery(conn = myDB, "SELECT name FROM sqlite_master WHERE type='table';")
customers <- read.csv(file = "C:\\Users\\dell\\Downloads\\ClassicModels (1)\\ClassicModels\\customers.csv", header = FALSE, stringsAsFactors = FALSE, na.strings = "")
names(customers) <- c("customerNumber", "customerName", "contactLastName", "contactFirstName", "phone", "addressLine1", "addressLine2", "city", "state", "postalCode", "country", "salesRepEmployeeNumber", "creditLimit")
dbWriteTable(conn = myDB, name = "customers", value = customers, append = TRUE, header = FALSE)
#dbGetQuery(conn = myDB, "SELECT * FROM customer;")
#dbExecute(conn = myDB, "drop table orders")
employees <- read.csv(file = "C:\\Users\\dell\\Downloads\\ClassicModels (1)\\ClassicModels\\employees.csv", header = FALSE, stringsAsFactors = FALSE, na.strings = "")
offices <- read.csv(file = "C:\\Users\\dell\\Downloads\\ClassicModels (1)\\ClassicModels\\offices.csv", header = FALSE, stringsAsFactors = FALSE, na.strings = "")
orderdetails <- read.csv(file = "C:\\Users\\dell\\Downloads\\ClassicModels (1)\\ClassicModels\\orderDetails.csv", header = FALSE, stringsAsFactors = FALSE, na.strings = "")
orders <-read.csv(file = "C:\\Users\\dell\\Downloads\\ClassicModels (1)\\ClassicModels\\orders.csv", header = FALSE, stringsAsFactors = FALSE, na.strings = "")
payments <- read.csv(file = "C:\\Users\\dell\\Downloads\\ClassicModels (1)\\ClassicModels\\payments.csv",header = FALSE, stringsAsFactors = FALSE, na.strings = "")
products <- read.csv(file = "C:\\Users\\dell\\Downloads\\ClassicModels (1)\\ClassicModels\\products.csv", header = FALSE, stringsAsFactors = FALSE, na.strings = "")
names(employees) <- c("employeeNumber", "lastName", "firstName", "extension", "email", "officeCode", "reportsTo", "jobTitle")
names(offices) <- c("officeCode", "city", "phone", "addressLine1", "addressLine2", "state", "country", "postalCode", "territory")
names(orderdetails) <- c("orderNumber", "productCode", "quantityOrdered", "priceEach", "orderLineNumber")
names(orders) <- c("orderNumber","orderDate", "requiredDate", "shippedDate", "status", "comments", "customerNumber")
names(payments) <- c("customerNumber", "checkNumber", "paymentDate", "amount")
names(products) <- c("productCode", "productName", "productLine", "productScale", "productVendor", "productDescription", "quantityInStock", "buyPrice", "MSRP")
dbWriteTable(conn = myDB, name = "employees", value = employees, append = TRUE, header = FALSE)
dbWriteTable(conn = myDB, name = "offices", value = offices, append = TRUE, header = FALSE)
dbWriteTable(conn = myDB, name = "orderdetails", value = orderdetails, append = TRUE, header = FALSE)
dbWriteTable(conn = myDB, name = "orders", value = orders, append = TRUE, header = FALSE)
dbWriteTable(conn = myDB, name = "payments", value = payments, append = TRUE, header = FALSE)
dbWriteTable(conn = myDB, name = "products", value = products, append = TRUE, header = FALSE)
### queestion 1
dbGetQuery(conn = myDB, "SELECT a.employeeNumber from employees as a inner join offices as b on a.officeCode=b.officeCode where b.city='Boston' " )
######## question 2
dbGetQuery(conn = myDB, "SELECT b.quantityOrdered*b.priceEach from orderdetails as b inner join orders as a on a.orderNumber=b.orderNumber where a.status='On Hold' " )
### question 3
dbGetQuery(conn = myDB, "select a.amount from payments as a inner join customers as b on a.customerNumber=b.customerNumber where b.customerName='Atelier graphique'")
