personel_sema

drop table personeller cascade constraint purge;

drop table bolumler cascade constraint purge;

drop table iller cascade constraint purge;

drop table bolgeler cascade constraint purge;

CREATE TABLE personeller (
  personel_id NUMBER,
  adi VARCHAR2(50),
  soyadi VARCHAR2(50),
  email VARCHAR2(50),
  maas NUMBER,
  bolum_id NUMBER,
  mudur_id NUMBER
);

CREATE TABLE bolumler(
  bolum_id NUMBER,
  bolum_adi VARCHAR2(50),
  il_id NUMBER,
  mudur_id NUMBER
);

CREATE TABLE iller(
  il_id NUMBER,
  il_adi VARCHAR2(50),
  bolge_id NUMBER
);

CREATE TABLE bolgeler(
  bolge_id NUMBER,
  bolge_adi VARCHAR2(50)
);

ALTER TABLE personeller ADD CONSTRAINT uq_email UNIQUE (email);

ALTER TABLE personeller ADD CONSTRAINT pk_personelid PRIMARY KEY (personel_id);

ALTER TABLE bolumler ADD CONSTRAINT pk_bolumid PRIMARY KEY (bolum_id);

ALTER TABLE iller ADD CONSTRAINT pk_ilid PRIMARY KEY (il_id);

ALTER TABLE bolgeler ADD CONSTRAINT pk_bolgeid PRIMARY KEY (bolge_id);

ALTER TABLE iller ADD CONSTRAINT fk_bolgeid FOREIGN KEY (bolge_id) REFERENCES bolgeler(bolge_id);


insert into bolgeler values(1,'Akdeniz Bolgesi');
insert into bolgeler values(2,'Karadeniz Bolgesi');
insert into bolgeler values(3,'Marmara Bolgesi');
insert into bolgeler values(4,'Ege Bolgesi');
insert into bolgeler values(5,'Dogu Anadolu Bolgesi');
insert into bolgeler values(6,'Ic Anadolu Bolgesi');
insert into bolgeler values(7,'Guney Dogu Anadolu Bolgesi');

-- iller tablosu
insert into iller values(1,'Antalya',1);
insert into iller values(2,'Mersin',1);
insert into iller values(3,'Adana',1);
insert into iller values(4,'Trabzon',2);
insert into iller values(5,'Rize',2);
insert into iller values(6,'Ordu',2);
insert into iller values(7,'Samsun',2);
insert into iller values(8,'Istanbul',3);
insert into iller values(9,'Kocaeli',3);
insert into iller values(10,'Bursa',3);
insert into iller values(11,'Tekirdag',3);
insert into iller values(12,'Izmir',4);
insert into iller values(13,'Mugla',4);
insert into iller values(14,'Denizli',4);
insert into iller values(15,'Afyon',4);
insert into iller values(16,'Malatya',5);
insert into iller values(17,'Erzurum',5);
insert into iller values(18,'Agri',5);
insert into iller values(19,'Kars',5);
insert into iller values(20,'Ankara',6);
insert into iller values(21,'Kirsehir',6);
insert into iller values(22,'Konya',6);
insert into iller values(23,'Corum',6);
insert into iller values(24,'Gaziantep',7);
insert into iller values(25,'Sanliurfa',7);
insert into iller values(26,'Adiyaman',7);
insert into iller values(27,'Diyarbakir',7);

-- bolumler
insert into bolumler values(1,'Bilgi Islem',8,1);
insert into bolumler values(2,'Pazarlama',8,2);
insert into bolumler values(3,'Satis',9,3);
insert into bolumler values(4,'Muhasebe',20,4);
insert into bolumler values(5,'Insan Kaynaklari',8,5);
insert into bolumler values(6,'Finans',20,6);

-- personeller
insert into personeller values(100,'Murat','Bilgin','mbilgin@gmail.com',20000,null,null);
insert into personeller values(1,'Bilge','Ozturk','bilge@msn.com',4000,1,100);
insert into personeller values(2,'Ahmet','Aslan','ahmeta@gmail.com',3000,2,100);
insert into personeller values(3,'Mustafa','Camci','mustafac@msn.com',5000,3,100);
insert into personeller values(4,'Canan','Duran','canand@hotmail.com',2000,4,100);
insert into personeller values(5,'Baki','Turkoglu','bakit@msn.com',4500,5,100);
insert into personeller values(6,'Serdar','Erdogan','serdare@msn.com',3500,6,100);
insert into personeller values(7,'Hakan','Dogan','hakand@gmail.com',4000,1,1);
insert into personeller values(8,'Deniz','Kaya','kayad@msn.com',3500,2,2);
insert into personeller values(9,'Yusuf','Ozcan','yusufo@msn.com',2800,3,3);
insert into personeller values(10,'Mehmet','Karadag','karadagh@gmail.com',3200,1,1);
insert into personeller values(11,'Mehmet','Bozkurt','mehmetb@msn.com',4000,5,5);
insert into personeller values(12,'Ayse','Suzen','ayses@msn.com',3000,3,3);
insert into personeller values(13,'Esra','Dogulu','esrad@gmail.com',2000,4,4);
insert into personeller values(14,'Kemal','Kilic','kemalk@hotmail.com',2600,6,6);
insert into personeller values(15,'Ozkan','Eraslan','ozkane@msn.com',2900,1,1);
insert into personeller values(16,'Mehmet','Kilic','kilicm@msn.com',2000,6,6);
insert into personeller values(17,'Mustafa','Erdogan','mustafae@hotmail.com',1900,3,3);
insert into personeller values(18,'Ensar','Selcukoglu','ensars@msn.com',2560,4,4);
insert into personeller values(19,'Kadir','Balci','kadirb@gmail.com',3000,5,5);
insert into personeller values(20,'Gulsah','Arslan','gulsaha@hotmail.com',2000,2,2);
insert into personeller values(21,'Hakan','Mertcan','hakanm@msn.com',2300,1,1);
insert into personeller values(22,'Suat','Taskesen','suatt@gmail.com',1980,4,4);
insert into personeller values(23,'Nurhan','Yilmaz','nurhany@hotmail.com',3000,3,3);
insert into personeller values(24,'Harun','Ipek','haruni@gmail.com',2000,3,3);
insert into personeller values(25,'Mahmut','Yildirim','mahmut@msn.com',2500,3,3);


ALTER TABLE personeller ADD CONSTRAINT fk_bolumid FOREIGN KEY (bolum_id) REFERENCES bolumler(bolum_id);

ALTER TABLE bolumler ADD CONSTRAINT fk_mudurid FOREIGN KEY (mudur_id) REFERENCES personeller(personel_id);

ALTER TABLE bolumler ADD CONSTRAINT fk_ilid FOREIGN KEY (il_id) REFERENCES iller(il_id);