ass2

create table Department(
Depotno integer primary key,
Dname Varchar2(20) unique, Location varchar2(20) not null,
constraint const_loc check (Location in ('Delhi','Puna','Agra')));

create table Employee(
Empno varchar2(5) primary key,
Ename varchar2(20) unique,
designation varchar2(20) not null,
salary number(10) default 25000,
DOB date not null,
Dno number(3) references department,
constraint const_Empno check(empno like 'E%'),
constraint const_sal check(salary>15000 and salary<50000)
);


create table Candidate(
    Candidate_ID number(6) primary key,
    Candidate_Name varchar2(20) not null,
    Candidate_Email varchar2(30) unique check(Candidate_Email like '%@_%._%'),
    Candidate_Dept varchar(2) default 'HR',
    Manager_ID number(30) references Candidate(Candidate_ID));

create table College(cName varchar2(10), state varchar2(10), enrollment integer);

create table Apply(sID integer, cName varchar2(10), major varchar2(20) );

create table Student(sID integer ,cNmae varchar2(10), GPA number(2,1),sizeHS integer );

alter table College add primary key(cName);

alter table student add primary key(sID);

alter table Apply add primary key(sID, cName, major);

ALTER TABLE Apply
ADD CONSTRAINT constraint_fk
FOREIGN KEY(sID) REFERENCES student;

ALTER TABLE Apply
ADD CONSTRAINT constraint_fk2
FOREIGN KEY(cName) REFERENCES college;

alter table Apply modify (major varchar2(25));

alter table Apply add(Decision varchar2(3) not null);

alter table Apply modify (Decision char(1) );



alter table Apply drop constraint constraint_fk2;

alter table student drop column sizeHS;

alter table college drop primary key;

alter table apply add constraint const_cName_major unique(cName, major);



alter table apply constraint constraint_fk on delete set null;

alter table college rename column enrollment to enroll;