Monday, February 11, 2019

Create and alter tables

Create table location
(
 locationkey serial primary key,
 locationname text not null,
 locationaddress text not null,
 locationcity text default 'Seattle',
 locationstate char(2) default 'WA',
 locationzip varchar(11) Not Null,
 locationphone varchar(13) Not null,
 Locationemail text not null unique,
 locationURL text

);

Create table event
(
 eventkey serial primary key,
 eventname text not null,
 eventdate Date not null,
 eventtime Time not null,
 eventdescription text,
 employeekey int references employee(Employeekey)
);

Create table locationroom
(
 locationroomkey serial,
 locationkey int,
 roomnumber int not null,
 eventkey int
);

Alter table locationroom
add primary key(locationroomkey);

Alter table locationroom
Add constraint fk_location Foreign key(locationkey)
    references location(locationkey);
 
Alter table locationroom
Add constraint fk_eventForeign Foreign Key(eventkey)
    references Event(eventkey);
 
Create table personevent
(
  personkey int not null,
  eventkey int not null
);

Alter table personevent 
Add primary key (personkey, eventkey);

Alter table personevent
Add constraint fk_personevent_person Foreign key (personkey)
   references person(personkey),
Add constraint fk_personevent_event Foreign key (eventkey)
   references event(eventkey);  
   
 Alter table person
 add announcement Boolean Default true;
 
 Select * from person;
 
 Alter table person
drop column announcement;

--temp tables
Create temp table emaillist
(
 lastname text,
 firstname text,
 email text
);

Insert into emaillist(lastname, firstname, email)
Select personlastname, personfirstname, personemail from person;

select * from emaillist;

Drop table emaillist;

Select personlastname, personfirstname, personemail 
into temp emaillist from person;

No comments:

Post a Comment