-- John Aromando -- SQL Build & Edit Tables --

use ogcPubLev2

--create Applicants table
create table Applicants
(
aptid varchar(5) not null,
lname varchar(15) not null,
fname varchar(20) null,
aptaddress varchar(40),
bthdate smalldatetime,
age int null,
ethnicbkgrd varchar(20),
phone varchar(12)
)

--edit Applicants table structure
alter table Applicants
add city varchar(40)
alter table Applicants
add state varchar(40)
alter table Applicants
drop column ethnicbkgrd
alter table Applicants
add gender varchar(1)
alter table Applicants
add check(len(phone)=12)
alter table Applicants
add zipcode varchar(5)

--verify structure
INSERT applicants (aptid, lname, fname, aptaddress, bthdate, age, phone, city, state, gender)
VALUES ('pvanr', 'van Rossum', 'Peter', '55 Main Street', '3/7/1951', 63, '555-555-1212', 'Washington', 'NJ', 'M')

select * from Applicants

drop table Applicants

--no primary or foreign keys
select * from sales
select * from titles

alter table sales
add constraint pkeysales primary key (ordnum) -- doesn't work

alter table sales
alter column ordnum nvarchar(5) not NULL

alter table sales
add constraint pkeysales primary key (ordnum)-- works

--edit titles tbl to create primary key
--run in sequence, not simultaneously
begin 
alter table titles
alter column partnum nvarchar(5) not NULL
end;

begin 
alter table titles
add constraint pkeyttl PRIMARY KEY (partnum)
end;

--add foreign key field linked to Titles table
alter table sales
add constraint fkeytitles foreign key (partnum) references titles (partnum)

sp_help sales

sp_help titles

create index TitlesIndexSort
on titles(bktitle)

sp_help titles

--won't allow dupe titles
create unique index TitlesUniqueName
on titles(bktitle)

sp_help titles

--remove indexes
drop index titles.TitlesUniqueName

sp_help titles