agawronski

#!/usr/bin/env python3

import requests  # for getting URL
import json  # for parsing json
from datetime import datetime  # datetime parsing
import pytz  # timezone adjusting
import csv  # for making csv files
import os

#################################################################

agawronski

-- Are there any null values in the EndDate field
-- of the humanresources.employeedepartmenthistory table?
-- If so how many records are there?
select count(*)
from humanresources.employeedepartmenthistory
where EndDate is null;

-- How many records are there where EndDate is not null?
select count(*)
from humanresources.employeedepartmenthistory

agawronski

-- Investigating purchasing.productvendor and purchasing.vendor
\d purchasing.productvendor
\d purchasing.vendor


-- How many records are there in each table?
-- How many different businessentityid?
select
count(*) as num_records,
count(distinct businessentityid) as num_businessentityid

agawronski

select
avg(i.num_orders) as average_num_orders,
avg(i.subtotal) as average_subtotal
from (
  select
  y.name,
  count(distinct x.purchaseorderid) as num_orders,
  sum(x.subtotal) as subtotal
  from purchasing.purchaseorderheader x
  left join purchasing.vendor y

agawronski

from selenium import webdriver #Importing the web driver from selenium library
browser = webdriver.PhantomJS(executable_path=r'C:\Python27\phantomjs-2.1.1-windows\bin\phantomjs.exe') #Setting our virtual browser with PhantomJS. 
#Note: If file path of phantomjs path is not set then we need to pass its executable_path as argument. In windows phantomjs.exe and phantomjs otherwise. 
browser.set_window_size(1120, 550) #Resize the browser to desired size. Try out this code by removing this line and see the screenshot. 
browser.get("http://testing-ground.scraping.pro/login") #Getting the response from http://testing-ground.scraping.pro/login. 
#Go to this URL and see expectations also explore the dom structure of the page.
browser.find_element_by_id('usr').send_keys("admin") #Finding input box of username by its id and sending 'admin' as a user name.
browser.find_element_by_id('pwd').send_keys("12345") #Finding input box of the password by its id and sending '12345' as a password.
browser.find_element_by_xpath("//*

agawronski

import csv
import requests 
from BeautifulSoup import BeautifulSoup
url = 'http://www.worldometers.info/world-population/population-by-country/'
response = requests.get(url) #Getting the response from mentioned URL using get() method of requests 
html = response.content
soup = BeautifulSoup(html)  
table = soup.find('table', attrs={'id': 'example2'}) #From BeautifulSoup of HTML content, finding the tbody(data of table) of the desired table having specific attributes, here desired table has 'example2' as idtbody = table.find('tbody')
list_of_rows = []
for row in tbody.findAll('tr')[0:]:  #line 11-16, Traversing every row('tr') and every cell of a row ('td') in table and making list of rows    list_of_cells = []

agawronski

select count(*)
from purchasing.purchaseorderdetail;
-- 8845

select count(distinct purchaseorderid)
from purchasing.purchaseorderdetail;
-- 4012

select count(*)
from purchasing.purchaseorderheader;

agawronski

select *
from purchasing.purchaseorderdetail
limit 10;

-- what keys might there be?

\dt purchasing.*

\d purchasing.productvendor
-- productid

agawronski

-- what is APT, and UPT, APU
-- average per transaction
-- units per transaction
-- average per unit
select
(sum(orderqty * unitprice)/count(distinct purchaseorderid)) as apt,
(sum(orderqty)/count(distinct purchaseorderid)) as upt,
(sum(orderqty * unitprice)/sum(orderqty)) as apu
from purchasing.purchaseorderdetail;

agawronski

select *
from purchasing.purchaseorderdetail
limit 10;

-- Are there returns?
select *
from purchasing.purchaseorderdetail
where orderqty <= 0
limit 10;