1 MENYAMBUNGKAN R TO SQL

library(RMySQL)   
## Loading required package: DBI
library(DBI)
Jengkolbalado <- dbConnect(MySQL(),
                           user='root',
                           password='',
                           dbname='jengkolbalado',
                           host='localhost')

2 List All order with customer.

SELECT *
  FROM Customers C
    LEFT JOIN Orders O
      ON C.CustomerID = O.CustomerID
Displaying records 1 - 10
row_names CustomerID CustomerName ContactName Address City PostalCode Country row_names OrderID CustomerID EmployeeID OrderDate ShipperID
90 90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland 1 10248 90 5 04/07/1996 3
81 81 Tradição Hipermercados Anabela Domingues Av. Inês de Castro, 414 São Paulo 05634-030 Brazil 2 10249 81 6 05/07/1996 1
34 34 Hanari Carnes Mario Pontes Rua do Paço, 67 Rio de Janeiro 05454-876 Brazil 3 10250 34 4 08/07/1996 2
84 84 Victuailles en stock Mary Saveley 2, rue du Commerce Lyon 69004 France 4 10251 84 3 08/07/1996 1
76 76 Suprêmes délices Pascale Cartrain Boulevard Tirou, 255 Charleroi B-6000 Belgium 5 10252 76 4 09/07/1996 2
34 34 Hanari Carnes Mario Pontes Rua do Paço, 67 Rio de Janeiro 05454-876 Brazil 6 10253 34 3 10/07/1996 2
14 14 Chop-suey Chinese Yang Wang Hauptstr. 29 Bern 3012 Switzerland 7 10254 14 5 11/07/1996 2
68 68 Richter Supermarkt Michael Holz Grenzacherweg 237 Genève 1203 Switzerland 8 10255 68 9 12/07/1996 3
88 88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil 9 10256 88 3 15/07/1996 2
35 35 HILARIÓN-Abastos Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35 San Cristóbal 5022 Venezuela 10 10257 35 4 16/07/1996 3

3 List orders with product names,quantities,price.

SELECT od.orderid, p.productname, od.quantity, p.price from orderdetails od
left join products p on p.ProductID = od.ProductID
order by orderid
Displaying records 1 - 10
orderid productname quantity price
10248 Queso Cabrales 12 21
10248 Singaporean Hokkien Fried Mee 10 14
10248 Mozzarella di Giovanni 5 34,8
10249 Tofu 9 23,25
10249 Manjimup Dried Apples 40 53
10250 Jack’s New England Clam Chowder 10 9,65
10250 Manjimup Dried Apples 35 53
10250 Louisiana Fiery Hot Pepper Sauce 15 21,05
10251 Gustaf’s Knäckebröd 6 21
10251 Ravioli Angelo 15 19,5

4 this will list all customers,whether they placed any order or not.

SELECT CustomerName As all_CustomerName From Customers
Displaying records 1 - 10
all_CustomerName
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Around the Horn
Berglunds snabbköp
Blauer See Delikatessen
Blondel père et fils
Bólido Comidas preparadas
Bon app’
Bottom-Dollar Marketse

5 list customers that have not placed orders

SELECT CustomerName as customerajikOrder From customers
WHERE CustomerID NOT IN (SELECT CustomerID From Orders)
Displaying records 1 - 10
customerajikOrder
Alfreds Futterkiste
Blauer See Delikatessen
Cactus Comidas para llevar
FISSA Fabrica Inter. Salchichas S.A.
France restauration
Great Lakes Food Market
La corne d’abondance
Laughing Bacchus Wine Cellars
Lazy K Kountry Store
Let’s Stop N Shop

6 list all contacts,i.e.,suppliers and customers.

select customername,contactname,address,city,postalcode,country,orders.orderID, orders.OrderDate, shippers.ShipperName, products.ProductName, orderdetails.Quantity, products.Unit, products.Price from customers
inner join orders on customers.CustomerID=orders.CustomerID
inner join orderdetails on orders.OrderID=orderdetails.OrderID
inner join shippers on orders.ShipperID=shippers.ShipperID
inner join products on orderdetails.ProductID=products.ProductID
Displaying records 1 - 10
customername contactname address city postalcode country orderID OrderDate ShipperName ProductName Quantity Unit Price
Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France 10265 25/07/1996 Speedy Express Alice Mutton 30 20 - 1 kg tins 39
Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France 10265 25/07/1996 Speedy Express Outback Lager 20 24 - 355 ml bottles 15
Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden 10278 12/08/1996 United Package Gula Malacca 16 20 - 2 kg bags 19,45
Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden 10278 12/08/1996 United Package Raclette Courdavault 15 5 kg pkg. 55
Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden 10278 12/08/1996 United Package Vegie-spread 8 15 - 625 g jars 43,9
Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden 10278 12/08/1996 United Package Röd Kaviar 25 24 - 150 g jars 15
Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden 10280 14/08/1996 Speedy Express Guaraná Fantástica 12 12 - 355 ml cans 4,5
Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden 10280 14/08/1996 Speedy Express Pâté chinois 20 24 boxes x 2 pies 24
Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden 10280 14/08/1996 Speedy Express Rhönbräu Klosterbier 30 24 - 0.5 l bottles 7,75
B’s Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK 10289 26/08/1996 Federal Shipping Aniseed Syrup 30 12 - 550 ml bottles 10

7 list product with order quantities greater than 80.

select od.orderid, p.productname, od.quantity from orderdetails od
left join products as p
on p.productID = od.ProductID
where quantity > 80
3 records
orderid productname quantity
10286 Steeleye Stout 100
10398 Pâté chinois 120
10440 Sirop d’érable 90

8 which product were sold bay the unit (i.e. quantity=1)

select od.orderid, P.ProductName, P.unit, od.quantity from orderdetails od
left join products p
on p.ProductID = od.ProductID
where quantity=1
3 records
orderid ProductName unit quantity
10281 Teatime Chocolate Biscuits 10 boxes x 12 pieces 1
10259 Gravad lax 12 - 500 g pkgs. 1
10308 Gudbrandsdalsost 10 kg pkg. 1

9 list customer who placed orders that are large than the average of each customer order.

select customers.CustomerName, count(orders.orderid) as total_ordered from customers
inner join orders on customers.CustomerID=orders.CustomerID
group by customername
having count(orders.orderid)
order by count(orders.orderid) desc
Displaying records 1 - 10
CustomerName total_ordered
Ernst Handel 10
Rattlesnake Canyon Grocery 7
Wartian Herkku 7
QUICK-Stop 7
Split Rail Beer & Ale 6
Hungry Owl All-Night Grocers 6
LILA-Supermercado 5
Mère Paillarde 5
La maison d’Asie 5
Old World Delicatessen 4

10 Find best selling Product base on quantity.

Select products.ProductName, sum(orderdetails.quantity) as best_products 
from orderdetails 
Inner join products on orderdetails.ProductID = products.ProductID
group by ProductName
order by best_products desc
Limit 1
1 records
ProductName best_products
Gorgonzola Telino 458

11 Find best selling Product based on revenue.

select products.productname, products.price*orderdetails.quantity as total_amount from orderdetails
join Products on orderdetails.ProductID=products.ProductID
group by productname
Order by Total_amount DESC
Limit 1
1 records
productname total_amount
Côte de Blaye 5260

12 Find best selling Product based on revenue for each country.

select country from customers
group by country
Displaying records 1 - 10
country
Argentina
Austria
Belgium
Brazil
Canada
Denmark
Finland
France
Germany
Ireland

13 Find suppliers with a product price less than $50

select suppliername from suppliers
where exists (select productname from products where products.SupplierID = suppliers.SupplierID and price<50)
Displaying records 1 - 10
suppliername
Exotic Liquid
New Orleans Cajun Delights
Grandma Kelly’s Homestead
Tokyo Traders
Cooperativa de Quesos ‘Las Cabras’
Mayumi’s
Pavlova, Ltd.
Specialty Biscuits, Ltd.
PB Knäckebröd AB
Refrescos Americanas LTDA

14 Find top 10 best employees base on their sales quantity.

select employees.FirstName, sum(orderdetails.quantity) as top10sales from orders
inner join Employees on orders.EmployeeID = Employees.EmployeeID
inner join orderdetails on orderdetails.OrderID = orders.OrderID
group by firstname
order by top10sales desc
9 records
FirstName top10sales
Margaret 3232
Nancy 1924
Janet 1725
Andrew 1315
Laura 1293
Michael 1094
Steven 778
Robert 733
Anne 649

15 Find top 10 best supplier countries based on quantity.

Select suppliers.Country, sum(Orderdetails.quantity) top10country_supplier from orders
inner join orderdetails on orders.OrderID = orderdetails.OrderID
inner join products on orderdetails.ProductID = products.ProductID
inner join suppliers on products.SupplierID = suppliers.SupplierID
group by country
order by top10country_supplier desc
limit 10
Displaying records 1 - 10
Country top10country_supplier
Australia 1610
USA 1581
France 1436
Germany 1339
Italy 1217
UK 1213
Canada 949
Norway 836
Japan 560
Sweden 465

16 FInd top 10 best customer countries base on quantity.

select customers.Country,sum(orderdetails.quantity) 10topcountry_sales from orders
inner join customers on orders.CustomerID = customers.CustomerID
inner join orderdetails on orderdetails.OrderID = orders.OrderID
group by country
order by 10topcountry_sales desc
limit 10
Displaying records 1 - 10
Country 10topcountry_sales
USA 2139
Germany 2015
Austria 1565
Brazil 1117
France 889
Canada 772
UK 698
Ireland 565
Venezuela 492
Sweden 399

17 Find top 10 best selling product based on quantity in every year.

select products.ProductName, sum(orderdetails.quantity) as top10_products  from orderdetails
inner join products on orderdetails.ProductID = products.ProductID
group by productname
order by top10_products desc
limit 10
Displaying records 1 - 10
ProductName top10_products
Gorgonzola Telino 458
Camembert Pierrot 430
Steeleye Stout 369
Raclette Courdavault 346
Chang 341
Pavlova 338
Fløtemysost 336
Alice Mutton 331
Tarte au sucre 325
Geitost 316 
select products.ProductName, sum(orderdetails.quantity) as top10_products1996 from orderdetails
inner join products on orderdetails.ProductID = products.ProductID
inner join orders on orderdetails.OrderID = orders.OrderID
where orderdate like '%1996%'
group by productname
order by top10_products1996 desc
limit 10
Displaying records 1 - 10
ProductName top10_products1996
Gorgonzola Telino 444
Camembert Pierrot 370
Steeleye Stout 274
Chartreuse verte 266
Fløtemysost 261
Mozzarella di Giovanni 260
Pavlova 252
Tarte au sucre 250
Alice Mutton 234
Raclette Courdavault 231 
select products.ProductName, sum(orderdetails.quantity) as top10_products1997 from orderdetails
inner join products on orderdetails.ProductID = products.ProductID
inner join orders on orderdetails.OrderID = orders.OrderID
where orderdate like '%1997%'
group by productname
order by top10_products1997 desc
limit 10
Displaying records 1 - 10
ProductName top10_products1997
Gnocchi di nonna Alice 173
Tourtière 126
Geitost 119
Raclette Courdavault 115
Chang 115
Sirop d’érable 106
Vegie-spread 100
Côte de Blaye 99
Alice Mutton 97
Steeleye Stout 95