# -*- coding: utf-8 -*-
"""northwind.ipynb

Automatically generated by Colaboratory.

Original file is located at
    https://colab.research.google.com/drive/16nHinAucigVGDm-zE8BraBpbCMs7dI8z
"""

import sqlite3

conn = sqlite3.connect('northwind_small.sqlite3')

conn

curs = conn.cursor()

curs.execute([('CREATE TABLE "Customer" ,"Id" VARCHAR(8000) PRIMARY KEY,"CompanyName" VARCHAR(8000) NULL, "ContactName" VARCHAR(8000) NULL,"ContactTitle" VARCHAR(8000) NULL, "Address" VARCHAR(8000) NULL, "City"VARCHAR(8000) NULL,  "Region" VARCHAR(8000) NULL,  "PostalCode"VARCHAR(8000) NULL, "Country" VARCHAR(8000) NULL, "Phone" VARCHAR(8000)NULL, "Fax" VARCHAR(8000) NULL )',)])

curs.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;").fetchall()

query = curs.execute('SELECT sql FROM sqlite_master WHERE name="Customer";').fetchall()

query

curs.execute('SELECT COUNT(*) FROM sqlite_master;').fetchall()

most_expensive_items= '''
SELECT *
FROM Product
ORDER BY Unitprice
DESC LIMIT 10;
'''

average_age= '''
SELECT AVG(HireDate - BirthDate)
FROM Employee;
'''

average_age_at_hire='''

'''

ten_most_expensive = '''
SELECT ProductName, UnitPrice, CompanyName
FROM Product, Supplier
WHERE SupplierID = Id
ORDER BY UnitPrice DESC LIMIT 10;
'''

largest_category = '''
SELECT CategoryName, COUNT
FROM Category, Product
WHERE Id = CategoryId
GROUP BY 1
ORDER BY 2 DESC LIMIT 1
'''

employee_most_territory = '''

'''

## A document store like MongoDB is appropriate when scalability is important ie. when storing huge amount of databases. 
## MongoDb is not approriate for soring smaller data like password, bookmarks etc

#NewSQL is a class of relational database management system with a focus on having the scalability of NoSQL whilst at the same time
#maintaning the ACID guarantee of traditional database system.