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