Security Login, Posgresql

/*
Permision consist of SQL commands--
SELECT, INSERT, UPDATE, EXCECUTE, DELETE, DROP. CREATE, ALTER
Anything that is not granted is denied
A user should be given all the permissions necessary
to do what they need to do in the database
But no more.
*/
Create role tenantrole;
Grant SELECT on "Apartment" to tenantrole;
Grant SELECT on "Lease" to tenantrole;
Grant UPDATE on "Tenant" to Tenantrole;
Grant SELECT on "Tenant" to tenantrole;

Create role johnsmith with password 'password';
Grant tenantrole to johnsmith;
Alter role johnsmith with login;

The test after logging in as johnsmith

Select * from "Apartment";
Update "Apartment"
SET "Bedrooms"=3
WHERE "ApartmentNumber"=102;

SQL for Postgresql

/*
List all the tenants.
List all the current maintenance requests.
Which apartments are currently occupied? join apartment and lease
Which apartments are empty? Left outer join with Apartment and Lease
Update maintenance request 2 to give it a completion date of today.
Insert a new tenant: Tammy Benedict, tbenedict@gmail.com, phone: 2065551200. She has no sub tenants
Add a new 6 month lease starting today on apartment 205, standard deposit, no pets
Record her first rent payment
Turn in the SQL and screenshots of the the answers
You may have to put the column and table names in double quotes if you made your database
from lucidcharts
*/
Select * from "person";
Select * from donation;
Select personlastname, personfirstname, personaddressStreet, personaddressCity
From person
inner join personaddress
on person.personkey=personaddress.personkey;

--outer join
Select granttypename, grantapplication.granttypekey
From granttype
Left outer join grantapplication
On granttype.granttypekey=grantapplication.grantTypekey
Where grantapplication.granttypekey is null;

--updates are to change existing records
Select * from person where personkey=2;
Update person
Set Personlastname='Caroll',
personemail='lindaCaroll@gmail.com'
WHERE personkey=2;

Insert into person(personlastname, personfirstname, personemail, personprimaryphone, persondateadded)
Values('Nelson','Tom', 'tom.nelson@msn.com', '2065553233', current_date);

select * from person where personlastname='Nelson';

Insert into Donation(personkey, donationamount, donationDate)
Values(134, 500.00, current_date);
Select * from donation where personkey =134;

Code for Assignment 8 Video

Customer Class

'''
Class called Customer
Every customer has a name,
number, email, phone and they
can collect rewards points
An object is collection of related
functions. They all relate
to one topic like customer.
The idea is to make it easier
to manage complex code
by breaking it into the kinds
of objects that actually make up
the contents of the program
abstraction
encapsulation
inheritance
polymorphism
'''
class Customer():
    def __init__(self, number, name, phone, email, rewards):
        self.number=number
        self.name=name
        self.phone=phone
        self.email=email
        self.rewards=rewards

    def setPhone(self, phone):
        self.phone=phone

    def getPhone(self):
        return self.phone

    def getName(self):
        return self.name

    def getEmail(self):
        return self.email

    def getRewards(self):
        return self.rewards

    def addRewards(self, points):
        self.rewards += points

    def useRewards(self, points):
        self.rewards = self.rewards-points

    def __str__(self):
        return str(self.number) + ", " + self.name
    

Item Class

'''
class item
It will represent an item to purchase
It will have a number, a name and a price
'''
class Item():
    def __init__(self, number, name, price):
        self.number=number
        self.name=name
        self.price=price

    def getNumber(self):
        return self.number

    def getName(self):
        return self.name

    def getPrice(self):
        return self.price
    

Purchase Class

'''
Purchase class
to show purchase of an item
it will have a list of items
and methods for
Totaling the purchase
and totaling the points
str method that outputs
basically a receipt
'''
class Purchase():
    def __init__(self):
        self.items=[]

    def addItem(self, item):
        self.items.append(item)

    def totalItems(self):
        total=0
        for item in self.items:
            total += item.price
        return total

    def totalPoints(self):
        total=self.totalItems()
        points=int(total)
        return points

    def __str__(self):
        receipt=""
        for item in self.items:
            receipt =receipt + item.name +"\t\t" + str(item.price) + "\n"
        total = self.totalItems()
        receipt=receipt + "\t\t" + str(total)
        return receipt
    
    

Main

from customer import Customer
from item import Item
from purchase import Purchase

def main():
    cust=Customer(123, 'Steve', 'steve@spconger.com', '2065551234', 0)
    purch = Purchase()
    cont='y'
    while cont=='y':
        itemNumber=int(input('Enter item Number. '))
        itemName=input("Enter item name ")
        itemPrice=float(input("Enter item price "))
        item=Item(itemNumber, itemName, itemPrice)
        purch.addItem(item)
        cont=input("Add another item? y to continue. " )
        cont=cont.lower()
    print(purch)
    cust.addRewards(purch.totalPoints())
    print ("your total rewards are", cust.getRewards())

main()
                             

Customer and customer test code from peer 8 video

'''
Class called Customer
Every customer has a name,
number, email, phone and they
can collect rewards points
An object is collection of related
functions. They all relate
to one topic like customer.
The idea is to make it easier
to manage complex code
by breaking it into the kinds
of objects that actually make up
the contents of the program
abstraction
encapsulation
inheritance
polymorphism
'''
class Customer():
    def __init__(self, number, name, phone, email, rewards):
        self.number=number
        self.name=name
        self.phone=phone
        self.email=email
        self.rewards=rewards

    def setPhone(self, phone):
        self.phone=phone

    def getPhone(self):
        return self.phone

    def getName(self):
        return self.name

    def getEmail(self):
        return self.email

    def getRewards(self):
        return self.rewards

    def addRewards(self, points):
        self.rewards += points

    def useRewards(self, points):
        self.rewards = self.rewards-points

    def __str__(self):
        return str(self.number) + ", " + self.name
    

Customer test

from customer import Customer

def main():
    c1 = Customer(123, 'Joe Smith', '2065551234', 'js@gmail.com', 10)
    print(c1)
    c1.addRewards(20)
    print(c1.getRewards())
    c1.useRewards(13)
    print(c1.getRewards())
    print(c1.getEmail())
    c2=Customer(234, 'Lynn Jones', '2065553456', 'Lynn@gamail.com',100)
    print("******************")
    print(c1)
    print()
    print(c2)

main()

Intro to MySQL SQL

Use Sakila;
Select * from actor;
Select first_name, last_name from actor;
Select last_name, first_name from actor
order by last_name;
Select last_name, first_name from actor
order by last_name Desc;
Select * from Address;
Select * from Address where district = 'California';
Select * from Customer;
Select * from Customer where  Create_date >'2006-02-14';
Select * from payment;
Select rental_id, amount from payment 
where amount > 5;
/*join tables */
Select first_name, Last_name, Address, district
from customer
inner join address
on customer.address_id=address.address_id
where district='California';

Select first_name, Last_name, Address, city, district
from customer
inner join address
on customer.address_id=address.address_id
inner join City
on city.city_id = address.city_id
where district='California';

/*insert new records */
Select * from store;
Insert into customer(store_id, first_name, last_name, 
address_id, active, create_date, last_update)
Values(1,'Jennifer', 'Juniper', 1,1, current_timestamp, current_timestamp);
Select * from staff;
Select * from customer;
Insert into rental(rental_date, Inventory_id, Customer_id, staff_id, last_update)
Values(current_timestamp, 100, 600, 2, current_timestamp);
Select * from Rental ORDER by rental_id desc;

if and while blocks

'''
a=5
b=10
if a < b:
    print(a, "is smaller thant", b)
elif a > b:
    print (a, " is bigger than ", b)
else:
    print (a, "is equal to ",b)
'''

def getGrade():
    grade=-1
    while grade < 0 or grade > 100:
        grade=int(input("Enter a grade between 0 and 100 "))
    #if grade < 0 or grade > 100:
        #grade=-1       
    return grade
                    
def evaluateGrade():
    g = getGrade()
    #if g == -1:
        #print ("Invalid Grade")
        #return
       
    if g > 90:
        print("you did great")
    elif g > 80:
        print(" you did good")
    elif g > 70:
        print("you passed")
    else:
        print("Sorry, you failed.")

def main():
    choice='y'
    while choice == 'y':
        evaluateGrade()
        choice=input("y to continue")
        choice.lower()

main()

Code from chapter 6 video

'''
Functions divide code into blocks.
Each function should do one thing.
Functions make it easier to debug and manage
program flow.
A function can just execute its code and be done.
A function can take in parameters to work with.
A function can return a value.
We are going to do a very simple program to calculate area
this requires the following steps
1. print out of what the program does
2. get the length and width of the area in feet
3. calculate the area
4. Output the results
Each step will be a separate function.
'''
def intro():
    print("This program calculates area")

def getLength():
    length=eval(input("enter the length: "))
    return length

def getWidth():
    width=eval(input("Enter the width: "))
    return width

def calculateArea():
    l=getLength()
    w=getWidth()
    a=l * w
    outputArea(a)

def outputArea(area):
    print("the area is", area)

def main():
    intro()
    calculateArea()

main()


    

Code from chapter 5 Video

Here is the console interactive session

Python 3.6.0 (v3.6.0:41df79263a11, Dec 23 2016, 08:06:12) [MSC v.1900 64 bit (AMD64)] on win32
Type "copyright", "credits" or "license()" for more information.
>>> #Chapter 5 take IV
>>> #strings
>>> #lists of characters
>>> greeting="Hello"
>>> type(greeting)
<class 'str'>
>>> number='17'
>>> type(number)
<class 'str'>
>>> print[greeting[0])
SyntaxError: invalid syntax
>>> print(greeting[0])
H
>>> print(greeting[4])
o
>>> print(greeting[2:3])
l
>>> print(greeting[2:4})
SyntaxError: invalid syntax
>>> print(greeting[2:4])
ll
>>> print(greeting[:4])
Hell
>>> len(greeting)
5
>>> for ch in greeting:
 print(ch)

H
e
l
l
o
>>> 
====== RESTART: C:/Users/SteveConger/Documents/PythonFiles/username.py ======
This program generates user names
enter your first name: Steve
enter your last name: Conger
Your user name is SConger
>>> 
====== RESTART: C:/Users/SteveConger/Documents/PythonFiles/username.py ======
This program generates user names
enter your first name: Steve
enter your last name: Robertson
Your user name is sroberts
>>> #page 148 string functons
>>> ord(a)
Traceback (most recent call last):
  File "<pyshell#19>", line 1, in <module>
    ord(a)
NameError: name 'a' is not defined
>>> ord("a")
97
>>> ord("A")
65
>>> chr(97)
'a'
>>> weekdays=["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
>>> print(weekdays[3])
Thu
>>> print(weekends[3-1])
Traceback (most recent call last):
  File "<pyshell#25>", line 1, in <module>
    print(weekends[3-1])
NameError: name 'weekends' is not defined
>>> print (weekdays[3-1])
Wed
>>> num=1234.33939020229202
>>> print("the formatted value = {0000.2f}".format(num))
Traceback (most recent call last):
  File "<pyshell#28>", line 1, in <module>
    print("the formatted value = {0000.2f}".format(num))
AttributeError: 'float' object has no attribute '2f'
>>> print("the formatted value={0:0.2f}".format(num))
the formatted value=1234.34
>>> print("The formatted value=${0.0.2f}".format(num))
Traceback (most recent call last):
  File "<pyshell#30>", line 1, in <module>
    print("The formatted value=${0.0.2f}".format(num))
AttributeError: 'float' object has no attribute '0'
>>> print("the formatted value=${0:0.2f}",format(num))
the formatted value=${0:0.2f} 1234.339390202292
>>> print("the formatted value=${0:0.2f}".format(num))
the formatted value=$1234.34
>>> 
====== RESTART: C:/Users/SteveConger/Documents/PythonFiles/userfile.py ======
this program creates a file of usernames in batch mode
from a file of names
Enter the file name with the names: name.txt
Enter the name of the output file: unames.txt
Traceback (most recent call last):
  File "C:/Users/SteveConger/Documents/PythonFiles/userfile.py", line 26, in <module>
    main()
  File "C:/Users/SteveConger/Documents/PythonFiles/userfile.py", line 12, in main
    infile=open(infileName, "r")
FileNotFoundError: [Errno 2] No such file or directory: 'name.txt'
>>> 
====== RESTART: C:/Users/SteveConger/Documents/PythonFiles/userfile.py ======
this program creates a file of usernames in batch mode
from a file of names
Enter the file name with the names: names.txt
Enter the name of the output file: unames.txt
Traceback (most recent call last):
  File "C:/Users/SteveConger/Documents/PythonFiles/userfile.py", line 26, in <module>
    main()
  File "C:/Users/SteveConger/Documents/PythonFiles/userfile.py", line 19, in main
    print(username, file=outfile)
NameError: name 'outfile' is not defined
>>> 
====== RESTART: C:/Users/SteveConger/Documents/PythonFiles/userfile.py ======
this program creates a file of usernames in batch mode
from a file of names
Enter the file name with the names: names.txt
Enter the name of the output file: unames.txt
the user names have been written to unames.txt
>>> 

Here is the first version of the Username program

#username.py
#Steve Conger
#10/8/2019

def main():
    print("This program generates user names")

    #get user first and last names
    first=input("enter your first name: ")
    last=input("enter your last name: ")

    #concatinate user name first letter of first name
    #first 7 letters of the last name
    username=first[0] + last[:7]
    print("Your user name is",username.lower())

main()

Here is the file version

#create a file of usernames
#read the file

def main():
    print("this program creates a file of usernames in batch mode")
    print("from a file of names")

    # get file names
    infileName=input("Enter the file name with the names: ")
    outfileName=input("Enter the name of the output file: ")

    infile=open(infileName, "r")
    outfile=open(outfileName, "w")

    # loop through the file, process and write
    for line in infile:
        first, last = line.split()
        username=(first[0] + last[:7]).lower()
        print(username, file=outfile)

    infile.close()
    outfile.close()

    print ("the user names have been written to", outfileName)

main()

        

SQL From video on Data And Entities

Create table topping
(
 toppingID serial primary key,
    toppingName text,
 toppingPrice decimal(5,2)
);

Create table crust
(
 crustID serial primary key,
 crustName text,
    crustPrice Decimal(5,2)
);

Create table pizzasize
(
 sizeID serial primary key,
    sizeName text,
    sizeBasePrice decimal(5,2)
);

Create table pizza
(
 pizzaID serial primary key,
 crustID integer references crust(crustID),
 sizeID integer references pizzasize(sizeID)
);

create table pizzatopping
(
 pizzaID integer references pizza(pizzaID),
 toppingID integer references topping(toppingID),
 primary key (pizzaID, toppingID)
);

create table "order"
(
 orderID serial primary key,
 orderdate timestamp default current_timestamp
);

Create table orderPizza
(
   orderID integer references "order"(orderID),
   pizzaID integer references pizza(pizzaID),
   primary key (orderID, pizzaID)
 
);

Insert into topping(toppingname, toppingprice)
Values('pepperoni', .50),
('olives', .50),
('pinapple', .50),
('tomatoes', .30),
('sausage', .50);

Insert into pizzasize(sizename, sizebaseprice)
values('large', 15.00),
('medium', 13.00),
('small', 10.00),
('personal', 9.00);

Insert into crust(crustname, crustprice)
values('regular', 0.00),
('deep dish', 1.00),
('cheese stuffed', 2.00);

Insert into Pizza(crustID, sizeID)
Values(1,2),
(2,2);

Insert into pizzatopping(pizzaid, toppingid)
values(1, 1),
(1,5),
(2, 2),
(2,3),
(2,4);

Insert into "order"(orderdate)
values('2019-10-07');

Insert into OrderPizza(orderID, PizzaID)
Values(1, 1),
(1,2);

Select * from Topping;
Select * from Crust;
Select * from PizzaSize;
Select * from Pizza;
Select * from PizzaTopping;
Select * from "order";
Select * from Orderpizza;

ERD for Car Shop Afternoon Class

In_Class ERD for Oil Change Shop

Python from First Class

First Program

'''
This is a python program
showing some basic elements
Steve 9-26-2019
'''

def main():
    #variables and assignments
    number1 = 7
    number2 = 12

    #print results
    print(number1+number2)

main()
    

Math Operators

'''
This program will ouput some
math based on user inputs
Steve Conger 9-26-2019
'''
def mathOperators():
    #getting input
    nameOfUser=input('Enter your name: ')
    number1, number2 = eval(input("Please enter two numbers divided with a comma: "))
    
    addition =number1 + number2
    subtraction=number1 - number2
    mult = number1 * number2
    division = number1 / number2
    intdivision = number1 // number2
    remainder = number1 % number2 #modulus

    print ("sum",addition)
    print ("difference",subtraction)
    print ("product", mult)
    print("Quotient", division)
    print("integer",intdivision)
    print("remainder", remainder)
    print(nameOfUser)


mathOperators()

Loop

def main():
    word = input("Enter a word")
    for i in range(10):
        print(i, word)

main()

Brainstorming arranging Aftrernoon

Different types pizza sizes crusts toppings sauces cheeses
side (bread sticks)
beverages
Desert
Customer address
price
supplies (ingredients) inventory boxes
locations
employees 
rent mortgage taxes ()
orders refunds  Payment types
specials
Security
Future promotions--merchandise
Delivery method take out, in house, delivery
inventory orders 

--organize it

Pizza
price
crust
sauce
toppings
cheese
ingredients?

Supplies supplyType Quantity priceperUnit
flour  flour  26lbs  1.99
eggs  eggs
cups
plates
boxes
spoons

Customer
name
address
usual
phonenumber
email
blacklist

Order
payment type

Brainstorming First organization

Customer name email address
ingredients
no food supplies
overhead charges, advertisements
employees, orders, delivery, on-line,
Pizzas, sides, drinks, toppings,
specials
alchohol, crusts, 


2nd Part organizing:

Pizza  Customer
crust  name
toppings email
price  address
size  phone

Orders
pizza(s)-sizes
toppings
sides
drinks
carry-out -delivery--eat in
customer
employee
date time
cooking time
delivery time

Employee
name
position
schedule

Inventory

Classes and tests Code Testing video

lock.py

class Lock():
    def __init__(self, door, location,securitylevel):
        self.door=door
        self.location=location
        self.securitylevel=securitylevel
        self.status='locked'

    def setStatus(self, status):
        self.status=status

    def getStatus(self):
        return self.status

    def getDoor(self):
        return self.door

    def getLoction(self):
        return self.location

    def getSecurityLevel(self):
        return self.securitylevel

    def __str__(self):
        return str(self.door) + " " + self.status

scan.py

from lock import Lock
import datetime

class Scan():
    def __init__(self, door, location, securitylevel, card):
        self.lock=Lock(door,location,securitylevel)
        self.card=card
        self.scantime=datetime.datetime.now()
    
    def getLock(self):
        return self.lock
    
    def getCard(self):
        return self.card

    def getScanTime(self):
        return self.scantime

The Tests: test.py

import unittest
from lock import Lock
from scan import Scan

class LockTest(unittest.TestCase):
    def setUp(self):
        self.lock=Lock('3176','BE', 'normal')

    def test_lockstring(self):
        self.assertEqual(str(self.lock), '3176 locked')

    def test_getStatus(self):
        self.assertEqual(self.lock.getStatus(), 'locked')

    def test_setStatus(self):
        self.lock.setStatus('unlocked')
        self.assertEqual(self.lock.getStatus(), 'unlocked')
    
    def test_GetDoor(self):
        self.assertEqual(self.lock.getDoor(), '3176')

class ScanTest(unittest.TestCase):
    def setUp(self):
        self.scan=Scan('3176', 'BE', 'normal', 315643)

    def test_GetCard(self):
        self.assertEqual(self.scan.getCard(), 315643)

System Queries

Select * from information_schema.tables;
Select Table_name from information_schema.tables
Where table_schema='public';
Select * from information_schema.columns;
Select column_name, data_type from information_schema.columns
where table_name='grantapplication';
Select sequence_name from information_schema.sequences;

Select column_name, data_type, constraint_name, constraint_Type
From information_schema.columns
Join information_schema.table_constraints
on information_schema.columns.table_name=information_schema.table_constraints.table_name
where information_schema.columns.table_name='grantapplication'
Order by column_name;

Select * from pg_catalog.pg_tablespace;
Select * from pg_catalog.pg_extension;

Advanced Query code

/******************************
* Set Operations
******************************/
CREATE TEMP TABLE email
(firstname text,
lastname text,
email text);

INSERT INTO email(firstname, lastname, email)
values('Jordan', 'Lawrence', 'jordanl@gmail.com'),
('Tammy', 'Standish', 'tstandish@msn.com'),
('Lester', 'Roberts', 'lr@yahoo.com'),
('Lynn', 'Kellerman', 'kellerman@gmail.com');

SELECT lastname, firstname, email, 'temptable' as tblSource from email
UNION
SELECT PersonLastname, personfirstname, personemail, 'Persontable'
FROM person
JOIN personaddress
USING (personkey)
WHERE personaddressCity='Bellevue';

SELECT Personlastname lastname,
personfirstname firstname,
personemail email,
'donor' "role"
FROM person
JOIN donation USING(personkey)
Where donationamount >=2000
UNION
SELECT Personlastname lastname,
personfirstname firstname,
personemail email,
'client'
FROM person
JOIN grantapplication USING(personkey)
WHERE granttypekey=2;

SELECT personkey, personlastname, personfirstname
FROM person
JOIN donation USING(personkey)
INTERSECT
SELECT personkey, personlastname, personfirstname
FROM person
JOIN grantapplication USING(personkey);

SELECT personaddresscity
FROM personaddress
JOIN person USING (personkey)
JOIN donation USING (personkey)
INTERSECT
SELECT personaddresscity
FROM personaddress
JOIN person USING (personkey)
JOIN grantapplication USING (personkey);

SELECT personaddresscity
FROM personaddress
JOIN person USING (personkey)
JOIN donation USING (personkey)
EXCEPT
SELECT personaddresscity
FROM personaddress
JOIN person USING (personkey)
JOIN grantapplication USING (personkey);

SELECT granttypename FROM granttype
EXCEPT
SELECT granttypename FROM grantapplication
JOIN granttype USING (granttypekey);
/*****************************
* Windows Functions
*****************************/

SELECT granttypename, grantapplicationkey, grantapplicationamount,
RANK() OVER (PARTITION BY granttypeName ORDER BY Grantapplicationamount DESC)
FROM grantapplication
JOIN granttype ON granttype.granttypekey=grantapplication.granttypekey
WHERE granttypename='Food';

SELECT granttypename, grantapplicationkey, grantapplicationamount,
DENSE_RANK() OVER (PARTITION BY granttypeName ORDER BY Grantapplicationamount
DESC)
FROM grantapplication
JOIN granttype ON granttype.granttypekey=grantapplication.granttypekey
WHERE granttypename='Food';

SELECT grantapplicationkey, granttypename, grantapplicationamount,
ROW_NUMBER() OVER(ORDER BY grantapplicationkey)
FROM grantapplication
JOIN granttype using(granttypekey);

SELECT grantapplicationkey, granttypename, grantapplicationamount,
ROW_NUMBER() OVER(ORDER BY grantapplicationamount)
FROM grantapplication
JOIN granttype using(granttypekey);

SELECT *
FROM
(SELECT grantapplicationkey, granttypename, grantapplicationamount,
ROW_NUMBER() OVER(ORDER BY grantapplicationamount)
FROM grantapplication
JOIN granttype using(granttypekey))grants
WHERE ROW_NUMBER BETWEEN 20 and 30;

SELECT *
FROM
(SELECT grantapplicationkey, granttypename, grantapplicationamount,
ROW_NUMBER() OVER(ORDER BY grantapplicationamount)
FROM grantapplication
JOIN granttype using(granttypekey))grants
WHERE ROW_NUMBER BETWEEN 20 and 30;

SELECT granttypename, grantapplicationamount,
LAST_VALUE(grantapplicationamount) OVER
(PARTITION BY granttypekey ORDER BY Grantapplicationamount
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM grantapplication
JOIN granttype using(granttypekey);

/**************************************
* Pivot table with CROSSTAB
*************************************/

CREATE TEMP TABLE applications2018
(
GranttypeName TEXT,
applicationdate DATE,
applciationamount NUMERIC
);



INSERT INTO applications2018
SELECT Granttypename, grantapplicationDate, grantapplicationamount
FROM grantapplication
JOIN granttype ON granttype.granttypekey = grantapplication.granttypekey
WHERE EXTRACT (YEAR FROM grantapplicationdate)=2018;

SELECT *
FROM CROSSTAB('SELECT EXTRACT(MONTH FROM applicationdate)::INTEGER,
granttypename, SUM(applciationamount)
FROM applications2018
GROUP BY 1,2 ORDER BY 1, 2')
FINAL_RESULT(Month INTEGER, Food NUMERIC, Rent NUMERIC, School NUMERIC,
Dental NUMERIC, Medical NUMERIC, Childcare NUMERIC ,Misc NUMERIC );

CREATE TEMP TABLE citydonations
(
"Month" Integer,
city text,
amount numeric
);

CREATE TEMP TABLE citydonations
(
"Month" Integer,
city text,
amount numeric
);

INSERT INTO citydonations
SELECT EXTRACT(MONTH FROM Donationdate), PersonaddressCity, donationamount
FROM (
SELECT DINSTINCT ON(donationkey)
donationkey,
donationdate,
personaddresscity,
donationamount
FROM donation
JOIN personaddress
USING (personkey)
) donations;


SELECT *
FROM CROSSTAB('SELECT "Month" :: INTEGER,

City, SUM(amount) FROM citydonations
GROUP BY 1,2
ORDER BY 1, 2')
FINAL_RESULT(Month INTEGER,
Seattle NUMERIC,
Redmond NUMERIC,
"New York" NUMERIC,
Bellevue NUMERIC,
Tukwilla NUMERIC,
Kent NUMERIC);