# set up the connection and save it into the workspace
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
library(RMySQL)

library(DBI)
naftali <- dbConnect(RMySQL::MySQL(),
                   dbname='new_mariadb',
                   username='root',
                   password='',
                   host='localhost',
                   port=3306)
knitr::opts_chunk$set(connection = "naftali")  #set up the connection

SELECT CustomerName, Address, City, Country
  FROM customers;

SELECT *
  FROM customers;

SELECT DISTINCT Country
  FROM customers;

SELECT *
  FROM Customers
    WHERE Country='Mexico';

SELECT *
  FROM Products
    WHERE Price
      BETWEEN 10 AND 20;

SELECT *
  FROM Orders
    WHERE OrderDate
      BETWEEN '1996-07-01' AND '1996-07-31';

SELECT *
  FROM Customers
    WHERE Country
      IN ('Germany', 'France', 'UK');

SELECT *
  FROM Customers
    WHERE Country
      IN (SELECT Country FROM Suppliers);

SELECT *
  FROM Customers
    WHERE CustomerName
      LIKE 'a%';

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München')
  ORDER BY Country, City;

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München')
  ORDER BY Country, City
    LIMIT 3;

SELECT CustomerName, Address, City, Country
  FROM customers
  ORDER BY City, Country DESC
    LIMIT 3, 5;

SELECT MIN(Price) AS SmallestPrice
  FROM Products;

SELECT MAX(Price) AS LargestPrice
  FROM Products;

SELECT AVG(Price)
  FROM Products;

SELECT COUNT(CustomerID), Country
  FROM Customers
    GROUP BY Country
      HAVING COUNT(CustomerID) > 5
        ORDER BY COUNT(CustomerID) DESC;

SELECT OrderID, Quantity,
CASE
  WHEN Quantity > 30 THEN 'The quantity is greater than 30'
  WHEN Quantity = 30 THEN 'The quantity is 30'
  ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);