Thursday, January 31, 2019

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

No comments:

Post a Comment