text: # -*- coding: utf-8 -*-
  """Untitled5.ipynb
  
  Automatically generated by Colaboratory.
  
  Original file is located at
      https://colab.research.google.com/drive/1HQTXywuCZVLNNOL2uqNzwDMDSMLtAIY_
  """
  
  import sqlite3
  
  sl_conn.close()
  
  sl_conn = sqlite3.connect('demo_data.sqlite3')
  sl_curs = sl_conn.cursor()
  
  create_demo_table = """
  CREATE TABLE demo (
    s VARCHAR(1),
    x INT,
    y INT
  );
  """
  
  sl_curs.execute(create_demo_table)
  
  test2 = [('g',3,9),('v',5,7),('f',8,7)]
  
  for i in test2:
      insert_var = '''
      INSERT INTO demo
      (s,x,y)
      VALUES ''' + str(i[0:]) + ';'
      sl_curs.execute(insert_var)
  
  querie = """SELECT COUNT(*) FROM demo"""
  sl_curs.execute(querie).fetchall()
  
  querie2 = """SELECT COUNT(*)
            FROM demo
            WHERE x >= 5
            AND y >=5;
            """
  sl_curs.execute(querie2).fetchall()
  
  querie3 = """SELECT COUNT(DISTINCT y)
            FROM demo
            """
  sl_curs.execute(querie3).fetchall()
  
  
  
  
  
  ###PART 2
  
  import sqlite3
  sl2_conn = sqlite3.connect('northwind_small.sqlite3')
  sl2_curs = sl2_conn.cursor()
  
  query = """SELECT UnitPrice, ProductName 
          FROM Product
          ORDER BY UnitPrice DESC
          LIMIT 10;
          """
  sl2_curs.execute(query).fetchall()
  
  query2 = """SELECT HireDate, BirthDate
          From Employee
          """
  test = sl2_curs.execute(query2).fetchall()
  
  empt_list = []
  for i in test:
    x = int(i[0][0:4])-int(i[1][0:4])
    empt_list.append(x)
  
  average = sum(empt_list)/len(empt_list)
  average
  
  
  
  
  
  q = """SELECT p.UnitPrice, p.ProductName, s.CompanyName, p.ID, s.ID
      FROM Product AS p,
      Supplier AS s
      JOIN Product ON s.ID = p.SupplierID
      GROUP BY p.ProductName
      ORDER BY p.UnitPrice DESC
      LIMIT 10;
      """
  
  print('Unit Price, Product Name, Supplier, Product ID, Supplier ID')
  sl2_curs.execute(q).fetchall()
  
  
  
  q2 = """SELECT CategoryID
      FROM Product
      GROUP BY CategoryID
      """
  count = sl2_curs.execute(q2).fetchall()
  len(count)
  
  