Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create table College(cName varchar2(10) primary key, state
- varchar2(10), enrollment int);
- create table Student(sID int primary key, sName varchar2(10), GPA
- real, sizeHS int);
- create table Apply(sID int, cName varchar2(10), major varchar2(20),
- decision char(1), primary key(sID, major, cName), constraint sID_fk
- Foreign key(sID) references Student, constraint cName_fk Foreign
- key(cName) references College);
- delete from Student;
- delete from College;
- delete from Apply;
- insert into Student values (123, 'Amy', 3.9, 1000);
- insert into Student values (234, 'Bob', 3.6, 1500);
- insert into Student values (345, 'Craig', 3.5, 500);
- insert into Student values (456, 'Doris', 3.9, 1000);
- insert into Student values (567, 'Edward', 2.9, 2000);
- insert into Student values (678, 'Fay', 3.8, 200);
- insert into Student values (789, 'Gary', 3.4, 800);
- insert into Student values (987, 'Helen', 3.7, 800);
- insert into Student values (876, 'Irene', 3.9, 400);
- insert into Student values (765, 'Jay', 2.9, 1500);
- insert into Student values (654, 'Amy', 3.9, 1000);
- insert into Student values (543, 'Craig', 3.4, 2000);
- insert into College values ('Stanford', 'CA', 15000);
- insert into College values ('Berkeley', 'CA', 36000);
- insert into College values ('MIT', 'MA', 10000);
- insert into College values ('Cornell', 'NY', 21000);
- insert into College values ('Harvard', 'MA', 50040);
- insert into Apply values (123, 'Stanford', 'CS', 'Y');
- insert into Apply values (123, 'Stanford', 'EE', 'N');
- insert into Apply values (123, 'Berkeley', 'CS', 'Y');
- insert into Apply values (123, 'Cornell', 'EE', 'Y');
- insert into Apply values (234, 'Berkeley', 'biology', 'N');
- insert into Apply values (345, 'MIT', 'bioengineering', 'Y');
- insert into Apply values (345, 'Cornell', 'bioengineering', 'N');
- insert into Apply values (345, 'Cornell', 'CS', 'Y');
- insert into Apply values (345, 'Cornell', 'EE', 'N');
- insert into Apply values (678, 'Stanford', 'history', 'Y');
- insert into Apply values (987, 'Stanford', 'CS', 'Y');
- insert into Apply values (987, 'Berkeley', 'CS', 'Y');
- insert into Apply values (876, 'Stanford', 'CS', 'N');
- insert into Apply values (876, 'MIT', 'biology', 'Y');
- insert into Apply values (876, 'MIT', 'marine biology', 'N');
- insert into Apply values (765, 'Stanford', 'history', 'Y');
- insert into Apply values (765, 'Cornell', 'history', 'N');
- insert into Apply values (765, 'Cornell', 'psychology', 'Y');
- insert into Apply values (543, 'MIT', 'CS', 'N');
- select sID,sName from student where sid in(select sid from apply where major='CS' and apply.sid=student.sid);
- select sID,sName from student where sizeHS in(select sizeHS from student where sName='Jay');
- select sID,sName from student where sizeHS in(select sizeHS from student where sName='Jay') and sName not in 'Jay' ;
- select sID,sName,GPA from student where GPA not in (select GPA from student where sName='Irene');
- select distinct cName from Apply where sID in(select sID from student where sName like 'J%');
- select major from Apply where sID in (select sID from Student where sName='Irene');
- select distinct sID,major from Apply where major in (select major from Apply where sid in (select sid from student where sName='Irene'));
- select distinct sID,major from Apply where major in (select major from Apply where sid in (select sid from student where sName='Irene')) and not in 876;
- select count(distinct (cName)) from Apply where sid in (select sid from student where sname='Jay');
- select * from student where sid in (select sid from apply where major='CS') and sid not in (select sid from apply where major='EE');
- select cname from college where state in (select state from college group by state having count(cname>1);
- select cname from college where enrollment in (select max(enrollment) from college);
- select sname from student where gpa in (select min(gpa) from student );
- select major from apply group by major having count(major) in (select max(count(major)) from apply group by major);
- select sid, sname, sizeHS from student where sizeHS not in (select min(sizeHS) from student);
Add Comment
Please, Sign In to add comment