sqlexc1src.sql

create table employee ( 
	fname varchar2(15),
	mname char(2),
	lname varchar2(15),
	ssn char(9),
	bdate date,
	address varchar2(50),
	sex char(1),
	salary number(7),
	superssn char(9),
	depno number(5)
);

insert into employee values ('Doug','E','Gilbert',554433221,'09-JUN-60','11-S-59,E,SaltLakeCity,UT','M',80000,'NULL',3);
insert into employee values ('Joyce','','PAN',543216789,'07-FEB-78','35-S,18,E,SaltLakeCity,UT','F',70000,'NULL',2);
insert into employee values ('Frankin','T','Wong',333445555,'08-DEC-45','638,Voss,Houston,TX','M',40000,'554433221',5);
insert into employee values ('Jennifer','S','Wallace',987654321,'20-JUN-31','291,Berry,Bellaire,TX','F',43000,'554433221',4);
insert into employee values ('John','B','Smith',123456789,'09-JAN-55','731,Fondren,Houston,TX','M',30000,'333445555',5);
insert into employee values ('Ramesh','K','Narayan',666884444,'15-SEP-52','975,FireOak,Humble,TX','M',38000,'333445555',5);
insert into employee values ('Joyce','A','English',453453453,'31-JUL-62','5631,Rice,Houston,,TX','F',25000,'333445555',5);
insert into employee values ('James','E','Borg',888665555,'10-NOV-27','50,Stone,Houston,TX','M',55000,'543216789',1);
insert into employee values ('Alicia','J','Zelaya',999887777,'19-JUL-58','3321,Castle,Spring,TX','F',25000,'987654321',4);
insert into employee values ('Ahmad','V','Jabbar',987987987,'29-MAR-59','980,Dallas,Houston,TX','M',25000,'987654321',4);

create table department (
	dname varchar(15),
	depno number(5),
	mgrssn varchar(15),
	mgrstartdate date
);

insert into department values ('Manufacture',1,'888665555','19-JUN-71');
insert into department values ('Administration',2,'543216789','04-JAN-99');
insert into department values ('Headquarter',3,'554433221','22-SEP-55');
insert into department values ('Finance',4,'987654321','01-JAN-85');
insert into department values ('Research',5,'333445555','22-MAY-78');

create table project (
	pname varchar2(15),
	pnumber number(5),
	plocation varchar2(15),
	depno number(5)
);

insert into project values ('ProjectA',3388,'Houston',1);
insert into project values ('ProjectB',1945,'Salt-Lake-City',3);
insert into project values ('ProjectC',6688,'Houston',5);
insert into project values ('ProjectD',2423,'Bellaire',4);
insert into project values ('ProjectE',7745,'Sugarland',5);
insert into project values ('ProjectF',1566,'Salt-Lake-City',3);
insert into project values ('ProjectG',1234,'New-York',2);
insert into project values ('ProjectH',3467,'Stafford',4);
insert into project values ('ProjectI',4345,'Chicago',1);
insert into project values ('ProjectJ',2212,'San-Francisco',2);

select * from employee;

select ssn,superssn from employee;

select fname, mname,lname from employee where bdate='29-MAR-1959';

select distinct salary from employee;

select mgrssn,mgrstartdate from department where dname='finance';

update employee set depno=5 where fname='Joyce';

alter table department add departmentphonenum number(12);
update department set departmentphonenum=123456789 where dname='finance';

alter table department modify departmentphonenum number(10);

alter table department rename column departmentphonenum to phno;

alter table department rename to dept;

alter table dept drop column phno;

create table copyofdept as select * from dept;

delete from copyofdept;

drop table copyofdept;