Insert Update Delete

--inserts updates and deletes

Insert into person(personlastname, personfirstname, 
       personemail, personprimaryphone,persondateadded)
Values('Mouse', 'Mickey', 'mm@disney.com', '2065551470', current_timestamp);

Insert into personaddress(personkey, personaddressstreet, 
        personaddresszipcode)
values(CURRVAL('person_personkey_seq'),'100 South enchanted','98001' );

Insert into logintable(personkey, personusername, personpassword)
Select CURRVAL('person_personkey_seq'), 
LOWER(SUBSTRING(personfirstname, 1,1)|| personlastname), 
createpassword(personlastname || 'Pass')
From person Where personkey=CURRVAL('person_personkey_seq')

Select * from person; 
Select * from personaddress where personkey=133;
Select * from Logintable where personkey=133;

--Update

Select * from person where personkey=2;

Update person
Set personlastname='Hamilton',
personemail='lindahamilton@gmail.com'
Where personkey=2;

Select personlastname, personfirstname, personemail into emaillist from person ;

Select * from emaillist;

Begin transaction;

Update emaillist
Set personlastname='Smith';

Rollback transaction;
Commit Transaction;

Drop table emaillist;

Select * from granttype;

Update Granttype
Set granttypeonetimemax=granttypeonetimemax * 1.05,
granttypelifetimemax=granttypelifetimemax * 1.05

Delete from personaddress where personkey=133;
Delete from logintable where personkey=133;
Delete From Person where personkey =133;

Select * from person where personkey=153;

Select * from jobposition;

Insert into jobposition(positionname)
Values('vice president'),
('cook and bottle washer'),
('garbage dumper')

SQL

--SQL Ansi ISO

Use VincentVinyl;
Select * from Album
Select albumtitle, albumstudion from album
--first generation binary machine language
--2nd assembler
--3rd generation python, c++, C#, Java, C, Fortran, Cobal procedural
--4th generation what you to do not how

Select * from Person
order by personlastname desc;

Select * from employee;

Select * from Album where AlbumID=3

Select * from Album where AlbumStudion='columbia'

Select * from purchase where purchasedate='12/20/2018'

Select purchaseID, year(purchasedate) AS "YEAR"
From purchase

Select * from Saledetail where SaleDetailDiscount > 0

--joins
Select * from Employee

Select EmployeeID, [PersonLastName],[PersonFirstName]
From Employee
Inner Join Person
on employee.PersonID=person.PersonID

Select * from inventory

Select InventoryID, purchasedetail.purchasedetailid, 
Albumtitle, conditionName, purchaseprice,InventorySalePrice,
Inventorysaleprice - purchaseprice AS "Difference"
From Inventory
inner join condition
on Inventory.ConditionID=Condition.ConditionID 
inner join purchaseDetail
on purchaseDetail.PurchaseDetailID=inventory.PurchaseDetailID
inner join Album
on Album.AlbumID=PurchaseDetail.AlbumID

--insert update delete

Insert into album(AlbumTitle, AlbumYear, AlbumStudion)
Values('American Idiot',2004, '80080')

Select * from Album

Insert into purchase(PurchaseDate, PersonID)
values(GetDate(), 2)
Select * from purchase

Insert into PurchaseDetail(purchaseID, AlbumID, PurchasePrice)
Values(4, 6, 10.00)
select * from purchaseDetail

--updates are dangerous

Select * from Person



Insert into inventory(PurchaseDetailID, ConditionID, InventorySalePrice)
Values(8, 1, 20.00)

Select * from Inventory

Update person
Set PersonFirstName='Debby',
personemail='debbybrown@gmail.com'
Where personid=2

begin tran

Update Person
Set personlastname='Smith'

rollback tran

Delete from Person 
Where personid=1

Joins

--joins

Select * from employee;
Select * from jobposition;

--Cross Join
Select * from Employee
Cross join jobposition
order by employeekey;

Select * from Employee, jobposition
Order by employeekey

--inner Join
Select EmployeeKey, employee.personkey, Personlastname, Personfirstname
From Person
Inner Join Employee
On Employee.personkey=Person.personkey;

Select EmployeeKey, employee.personkey, Personlastname, Personfirstname
From Person
Join Employee
On Employee.personkey=Person.personkey;

Select Employee.EmployeeKey, employee.personkey, Personlastname, Personfirstname,
EmployeePositionStartDate, PositionName
From Person
Inner Join Employee
On Employee.personkey=Person.personkey
Inner join EmployeePosition
On EmployeePosition.employeekey=employee.employeekey
Inner Join JobPosition
On Jobposition.positionkey=employeeposition.positionkey;

Select e.EmployeeKey, e.personkey, Personlastname, Personfirstname,
EmployeePositionStartDate, PositionName
From Person p
Inner Join Employee e
On p.personkey=e.personkey
Inner join EmployeePosition ep
On ep.employeekey=e.employeekey
Inner Join JobPosition jp
On ep.positionkey=jp.positionkey;

--older syntax
Select employee.EmployeeKey, employee.personkey, Personlastname, Personfirstname,
EmployeePositionStartDate, PositionName
From person, Employee, employeeposition, jobposition
Where person.personkey=employee.personkey
And employee.employeekey=employeeposition.employeekey
and jobposition.positionkey =employeeposition.positionkey;

--accidental cross join by leaving out a relationship
Select employee.EmployeeKey, employee.personkey, Personlastname, Personfirstname,
EmployeePositionStartDate, PositionName
From person, Employee, employeeposition, jobposition
Where person.personkey=employee.personkey
and jobposition.positionkey =employeeposition.positionkey;

--aggregate
Select extract('year' from grantapplicationdate)"Year",
GrantTypeName, Sum(Grantapplicationamount) "total",
avg(Grantapplicationamount) Average
From Grantapplication
Inner Join Granttype
On GrantType.granttypekey=grantapplication.granttypekey
Group by extract('year' from grantapplicationdate), granttypename
Order by extract('year' from grantapplicationdate), granttypeName;

Select extract('year' from grantapplicationdate)"Year",
Sum(grantapplicationamount) from grantapplication
Group by extract('year' from grantapplicationdate);

---Danger!!
--total donation by city(person personaddress-donation)

Select personaddresscity "City", Sum(donationamount) "total"
From Person
Inner Join personaddress
On person.personkey=personaddress.personkey
Inner Join donation
on person.personkey=donation.personkey
Group by personaddresscity;

Select Sum(donationamount) from Donation;

--outer joins about not matching data

Select distinct granttypename, grantapplication.granttypekey
From GrantType
left outer join Grantapplication
On granttype.granttypekey=grantapplication.granttypekey;

Select distinct granttypename, grantapplication.granttypekey
From GrantType
left outer join Grantapplication
On granttype.granttypekey=grantapplication.granttypekey
Where grantapplication.granttypekey is null;

Select distinct granttypename, grantapplication.granttypekey
From GrantApplication
Right outer join GrantType
On granttype.granttypekey=grantapplication.granttypekey;
--

Select personaddressCity, donationamount from personaddress
Inner join Donation
on personaddress.personkey=donation.personkey
where donation.personkey=7

--fulljoin
Select distinct granttypename, grantapplication.granttypekey
From GrantApplication
Full join GrantType
On granttype.granttypekey=grantapplication.granttypekey;

--using
Select EmployeeKey, employee.personkey, Personlastname, Personfirstname
From Person
Inner Join Employee
using(personkey);

Select EmployeeKey, employee.personkey, Personlastname, Personfirstname, personaddressCity
From Person
natural join Employee
natural join personaddress;

SELECT Personlastname, personfirstname, Donationdate, Donationamount, grantapplicationdate,
grantapplicationamount
From donation
Join person
On person.personkey=donation.personkey
Join grantapplication
On grantapplicationdate between donationdate And DonationDate + interval '7 day'
Where personlastname='Baker'
order by personlastname, personfirstname;

Built in functions

--functions
Select 5.0 * ((3 + 2) -1 )/3;
Select donationamount, donationamount * .77 AS Charity,
donationamount * .23 as Maintenance
From Donation;
Select donationamount, donationamount /9 as Charity
From Donation;
Select donationamount, round(donationamount /9,2) as Charity
From Donation;
Select donationamount, Ceiling(donationamount /9) as Charity
From Donation;
Select donationamount, floor(donationamount /9) as Charity
From Donation;
Select donationamount, to_char(donationamount /9,'$ 9,999.99') as Charity
From Donation;
Select Random() as Rand;

Select * from person where personkey=ceiling(Random() * ((132-1)+1)+1);

Select * from logintable order by personkey;
Select crypt('SmithPass', Gen_salt('bf', 8)) as password;
Select * from Logintable where personusername='janderson'
and personpassword=crypt('AndersonPass', personpassword);
           
--Datetime functions
Select current_date;
Select current_timestamp;
Select current_time;
Select donationdate, extract('year' from donationdate) as "Year"
From donation; 
Select donationdate, extract('month' from donationdate) as "Month"
From donation;            
Select donationdate, extract('day' from donationdate) as "Month"
From donation;
Select donationdate, extract('month' from donationdate) as "Month"
From donation; 
Select * from employeeposition;
Select employeekey, age(employeepositionstartdate) 
from employeeposition
order by age(employeepositionstartdate) desc;
Select grantapplicationkey, grantapplicationdate, 
grantapplicationdate + interval '2 days' "read by"
From grantapplication;
           
Select grantapplicationkey, grantapplicationdate, 
grantapplicationdate + interval '48 hours' "read by"
From grantapplication; 

--concatination
Select personlastname || ', ' || personfirstname
As "Name" 
From Person;          
Select position ('@' in personemail) from person;
Select substring(personemail from 1 for position('@' in personemail)-1)
As username from person;
--aggregate funtions
Select Avg(donationamount) from donation;
Select Sum(donationamount) from donation;
Select Max(donationamount) from donation;
Select MIN (donationamount) from donation;
Select Count(donationamount) from donation; 
           
Select granttypekey, sum(grantapplicationAmount) as Total
From grantapplication
group by granttypekey
order by granttypekey; 

Select extract('Year' from donationdate) as "year",
sum(donationamount)as total
From donation
Group by extract('Year' from donationdate); 
           
Select extract('Year' from donationdate) as "year",
sum(donationamount)as total,
avg(donationamount) as average,
count(donationamount) as "number"          
From donation
Group by extract('Year' from donationdate);            
           
Select extract('Year' from donationdate) as "year",
sum(donationamount)as total,
avg(donationamount) as average,
count(donationamount) as "number"          
From donation
Group by extract('Year' from donationdate)
having avg(donationamount) > 500; 

Select extract('Year' from donationdate) as "year",
sum(donationamount)as total,
avg(donationamount) as average,
count(donationamount) as "number"          
From donation
Where extract('Year' from donationdate) > 2017
Group by extract('Year' from donationdate)
having avg(donationamount) > 500;            
           
Select count(personkey) from donation; 
Select count(Distinct personkey)from donation;            
           

Normalization

Here are pictures of diagrams and spreadsheets that we created in class

Setup and Configure Django --steps

The first two assignments involved a lot of steps, so I though it might be useful to list them. Most of these steps only need to be done once. Also I want to reiterate that the tech review project and app are the in-class examples, the assignments are all with the python club project and app.

Initial set up

1. Create a directory for the django projects
2. Install django into that directory
3. Install venv, the virtual environment
4. Activate the virtual environment
5. Create the project techreviews
6. change directory into techreviews and start the app tech
7. (do the same two steps with pythonclub)

Configuration

1. Create the techreviewdb in pgadmin (also create pythonclubdb)
2. Open VSCode, Open a folder to the outermost techteview folder
3. Also open a terminal in VSCode
4. Open the settings.py file in the project folder.
5. In the settings.py file add 'tech' to the registered apps list.
6. Also in the settings.py change the database to postgresql_psycopg2.
7. You will need to install psycopg2 using pip, using the terminal in VSCode
8. Migrate the databases
9. In the project lever urls.py add an include that points to the app's url
10. Add a urls.py file to the app directory

At this point you will have completed the setup and configuration

Selects

--Selects
Select * From Person; -- this takes more processing time since it will need to figure out what all the columns are
Select personlastname, personfirstname, personemail -- this will run faster since columns are specified
From Person;
Select personfirstname As "First Name", --you can use "as" to label your output table columns
personlastname As "Last Name", 
personemail As "Email" From Person;

Select personkey, personfirstname, personlastname, personemail
From Person
order by personlastname, personfirstname Desc;

select * From donation where personkey = 6;

Select * From personaddress
Where personaddresscity = 'Bellevue'; -- not typically case sensitive but ours is

Select * From personaddress
Where NOT personaddresscity = 'Seattle';

Select personkey, personaddressstreet, personaddressapt
From personaddress
Where personaddressapt is null OR personaddressapt = ' ';

Select personaddressstreet, personaddressapt
From Personaddress
Where personaddressapt is null or personaddressapt='';

--11
Select personaddressstreet, personaddressapt
From Personaddress
Where personaddressapt is not null; 

Select personaddressstreet, personaddressapt
From Personaddress
Where personaddressapt = null; 

Select * from Donation;

Select * from donation where donationamount >1000
order by donationamount desc;

Select * from donation where donationamount between 1000 and 1500
order by donationamount desc;

Select * from donation where donationdate between '2017-06-01' And '2017-07-31';

Select * from donation where donationamount <= 1000;

Insert into donation (personkey, donationamount, donationdate)
Values (6, 500, current_timestamp);

Select * from donation where donationdate = '2019-01-08';

Select Personfirstname, personlastname 
from person
where personlastname like 'L%n';

Select Personfirstname, personlastname 
from person
where personlastname like 'Car_l';

--Distinct
Select distinct personkey from donation
order by personkey;

Database Notes

Process

How know you need a database?
The amount of data
Current methods no longer work

Talk to people who need the database

Not too technichal

Really general requirements

Vince's Vinyl used record store

Interview with management

Why do you need a database? What information do you need to keep?

How do want to enter data? How are you doing it now?

Inventory, Sales (discounts), Customer--requests, purchases, Employees, who purchased from

Who will use the database? Limits, Permissions

Statement Of Scope

information discovery

Forms --receipts sales and purchase, notebook, requests

Reports--reports- tax statements, monthly and yearly earnings

manuals, procedures

Job shadowing--you watch how people do the job. finding exceptions.

Interview stakeholders

Review--look at all the nouns

Inventory --quality (good fair poor mint) title, genre(s), artist(s), playtime, edition, print year, who purchased, (songs) quantity, purchasedate, purchaseprice, Suggest sale price, location, requested, availablity,

Customer Name, contact info, phonenumber email, purchase history, requests, membership status, discounts, store credit, perferences

Sale customer, date of sale, employee, sale price, inventory item(s) sold tax, discounts, (profit--don't store calculated fields)

request Customer, condition, Album requested--artist requesting, price range, date requested, data fulfilled

Employees name, datehired, position, sales, address, phone, email

Purchase (store purchasing) item names, quality, purchase date, price paid, edition, vender,