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()