aaaaaaaaaaaa

Create table Alumni
(
	Alumni_ID	Number(5) NOT NULL,
	Name		Varchar2(50) NOT NULL,
	Phone		Number(11) NOT NULL,
	Country		Varchar2(20) NOT NULL,
	Address		Varchar2(50)
);

drop table Alumni;

Create table Donation
(
  Donation_ID Number(5) NOT NULL,
  Alumni_ID Number(5) NOT NULL,
  Amount Number(10) NOT NULL,
  Donation_Date  Date
);
drop table Donation;

Create table Alumni_Company
(
  Alumni_ID NUMBER(5) NOT NULL,
  PoBox NUMBER(5)
);

DROP TABLE Alumni_Company;

Create table Completion
(
  Completion_ID Number(5) NOT NULL,
  Year Varchar(4) NOT NULL,
  Major Varchar(30) NOT NULL,
  School_ID Number(5) NOT NULL
);
drop table completion;

Create Table School
(
  School_ID Number(5) Not Null,
  School_Name Varchar(15) NOT NULL
);

Create table Alumni_Student
(
  Alumni_ID Number(5) NOT NULL,
  Email Varchar(30),
  Employment_ID Number(5) NOT NULL,
  Completion_ID Number(5) NOT NULL

);

Create Table Company
(
  Company_ID Number(5) NOT NULL,
  Name Varchar2(30) NOT NULL,
  Address Varchar2(100) NOT NULL
);
desc Company;

Create Table Employment
(
  Employment_ID Number(5) NOT NULL,
  Alumni_ID Number(5) NOT NULL,
  Company_ID  Number(5) NOT NULL,
  Start_Date Date NOT NULL,
  End_Date Date,
  Salary Number(6) NOT NULL,
  Job_Title Varchar(30) NOT NULL,
  Phone Number(11) NOT NULL
  );


Create Table Invited
(
  Alumni_ID Number(5) NOT NULL,
  Event_ID  Number(5) NOT NULL,
  RSVP    Varchar(1) NOT NULL
);


Create table events
(
  Event_ID Number(5) NOT NULL,
  Location Varchar2(50) NOT NULL,
  Event_Date Varchar2(4)
);


______________________________________________-
________________________________________________



CREATE VIEW ViewA AS
SELECT Name,Email,Alumni.Phone, Employment.Phone as WorkPhone
FROM Alumni, Alumni_Student, Completion, Employment
WHERE Alumni.Alumni_ID = Alumni_Student.Alumni_ID
  AND Alumni_Student.Completion_ID = Completion.Completion_ID
  AND Alumni_Student.Alumni_ID = Employment.Alumni_ID
  AND major = 'ComputerScience'
  AND Year = '2013';

dROP view ViewA;

select * from ViewA;

Create View ViewB AS
Select Name,Phone,Address,Email
  FROM Alumni_Student, Alumni
  where
  AlUMNI_student.Alumni_ID = Alumni.Alumni_ID
  AND
  country = 'Singapore';

  desc alumni;

Create VIEW ViewD AS
Select name, Alumni.Alumni_ID,Donation_Date, Amount
  FROM Alumni, Donation
  WHERE alumni.alumni_ID = donation.Alumni_ID;

Create View ViewE AS
Select alumni.name, Start_Date, Salary, Job_Title
from Alumni,Alumni_Student, Employment, company
  WHERE ALUMNI.ALUMNI_ID = ALUMNI_STUDENT.ALUMNI_ID
  AND
  ALUMNI_STUDENT.ALUMNI_ID = EMPLOYMENT.ALUMNI_ID
  AND
  EMPLOYMENT.COMPANY_ID = COMPANY.COMPANY_ID
  AND COMPANY.NAME = 'Microsoft';

desc company;


desc Employment;



___________________________________________________________________
------------------------------------------------------------------



drop view computerScience;

SELECT Name,Email,Alumni.Phone, Employment.Phone as WorkPhone
FROM Alumni, Alumni_Student, Completion, Employment
WHERE Alumni.Alumni_ID = Alumni_Student.Alumni_ID
  AND Alumni_Student.Completion_ID = Completion.Completion_ID
  AND Alumni_Student.Alumni_ID = Employment.Alumni_ID
  AND major = 'ComputerScience'
  AND Year = '2013'
  ;

  select * from completion;


  Select * from school;

  select * from ViewA;
  Select * from alumni;
  select * from alumni_Student;
  desc alumni_Student;

  insert into alumni_Student values (32321, 'Tal@dubai.com', 12);

  desc employment;

  drop table employment;


  Create Table Employment
(
  Employment_ID Number(5) NOT NULL,
  Alumni_ID Number(5) NOT NULL,
  Company_ID  Number(5) NOT NULL,
  Start_Date Date,
  End_Date Date,
  Salary Number(6) NOT NULL,
  Job_Title Varchar(30) NOT NULL,
  Phone Number(11) NOT NULL
  );
insert into employment (Employment_ID, alumni_ID, company_ID, salary, job_Title, phone) Values (666, 32321, 5,60000, 'System Analyst', 0522222);

  insert into employment values (666, 32321, 5, '2013-1-1' , '2013-4-4' ,60000, 'System Analyst', 0522222);

  desc company;

  insert into company values (5, 'Microsoft', 'BusinessBay');

  select * from ViewE;

  Select Name,Phone,Address,Email
  FROM Alumni_Student, Alumni
  where
  AlUMNI_student.Alumni_ID = Alumni.Alumni_ID
  AND
  country = 'Singapore';

  select * from alumni;

  insert into alumni values(32322, 'Tam', 022222, 'Singapore', 'SingaporeCity');

  insert into alumni_Student values (32322, 'Tam@dubai.com', 15);

  select * from ViewE;
  select * from ViewB;

  create view ViewC AS
  Select Name, Alumni.Alumni_ID, Amount
  from Alumni, Alumni_Company,Donation

  Where Alumni.Alumni_ID = Alumni_Company.Alumni_ID
  And Alumni.Alumni_ID = Donation.Alumni_ID
  And amount > 25000 order by amount desc;

  drop view ViewC;

  desc alumni_Company;

  select * from viewC;

insert into alumni_Company values (3333, 3333);


  insert into alumni values(3333, 'PurpVog', 0425422, 'UAE', 'DAFZA');


  desc donation;

  insert into donation (Donation_ID, Alumni_ID, Amount) values (11, 3333, 30000);

  Select name, Alumni.Alumni_ID,Donation_Date, Amount
  FROM Alumni, Donation
  WHERE alumni.alumni_ID = donation.Alumni_ID;

  select * from ViewD;

  select * from alumni_Student;

  insert into donation (Donation_ID, Alumni_ID, Amount) values (12, 32321, 500);