dbms_lab_assign

select sname, dob from student;

select sname from student where gpa>3.7;

select sname from student where sizehs>=1000 and dob>'31-dec-1996';

select sname from student where gpa>2.9 and gpa<3.9;

select * from college where state='MA';

select sname from student where gpa>2.0 and gpa<3.5;

select sname from student where dob>'1-jul-1996' order by dob asc;

select sid,cname,decision from apply where decision='Y';

select sid,cname from apply where cname='Stanford';

select cname from college where enrollment>10001;

select cname from college where state!='CA';




create table Student(
stID number(3),
sName varchar2(15),
GPA number(2,1),
sizeHS number(4),
DoB date
)
insert into Student values(123,'Amy',3.9,1000,'26-JUN-96');
insert into Student values(234,'Bob',3.6,1500,'7-Apr-95');
insert into Student values(345,'Craig',3.5,500,'4-Feb-95');
insert into Student values(456,'Doris',3.9,1000,'24-Jul-97');
insert into Student values(567,'Edward',2.9,2000,'21-Dec-96');
insert into Student values(678,'Fay',3.8,200,'27-Aug-96');
insert into Student values(789,'Gary',3.4,800,'8-Oct-96');
insert into Student values(987,'Helen',3.7,800,'27-Mar-97');
insert into Student values(876,'Irene',3.9,400,'7-Mar-96');
insert into Student values(765,'Jay',2.9,1500,'8-Aug-98');
insert into Student values(654,'Amy',3.9,1000,'26-JUN-96');
insert into Student values(543,'Craig',3.4,2000,'27-Aug-98');
select sName , DoB from Student;
select sName from Student where GPA>3.7;
select sName from Student where sizeHS>=1000 and DoB > '31 December, 1996';
select sName from Student where GPA between 2.9 and 3.9;
select * from Student;
create table Apply(
stID number(3),
cName varchar2(10),
major varchar(20),
decision varchar2(2))
insert into Apply values(123,'Standford','CS','Y');
insert into Apply values(123,'Standford','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,'Standford','history','Y');
insert into Apply values(987,'Standford','CS','Y');
insert into Apply values(987,'Berkeley','CS','Y');
insert into Apply values(876,'Standford','CS','N');
insert into Apply values(876,'MIT','biology','Y');
insert into Apply values(876,'MIT','marine biology','N');
insert into Apply values(765,'Standford','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 stID,cName from Apply where decision='Y';
select stID,cName from Apply where cName= 'Standford';


select sName , DoB from Student;
select sName from Student where GPA>3.7;
select sName from Student where sizeHS>=1000 and DoB > '31 December, 1996';
select sName from Student where GPA between 2.9 and 3.9;
select sName from Student where GPA >2.0 and GPA<3.5;
select sName from Student where DoB>'1 July,1996' order by DoB;
select sName from Student2 where sizeHS>17000 and GPA<3.8;
select * from Student2;
select sName from Student2 where sName like '   ';
select sName from Student2 where sName like 'H     ';
select sName from Student2 where sName like '  e e%';
select sName from Student2 where sName like '%y';
select * from Student2 order by GPA;
select * from Student2 order by GPA asc , DoB desc;
update Student2 set GPA=10*GPA/100+GPA;
select * from Student2;
update Student2 set GPA=GPA+1.5 where GPA<3.5 and sizeHS>1500;
select * from Student2;
delete from Student2 where GPA<3.2;
select * from Student2;

select stID,cName from Apply where decision='Y';
select stID,cName from Apply where cName= 'Standford';
selct distinct major from Apply;
select stID from Apply cName in ('Standford', 'Cornell', 'MIT');
delete from Apply where cName = 'Standford';
select * from Apply;
create table College(
cName varchar(15),
state varchar(3),
enrollment number(7))
select * from College;
select cName from College where state = 'MA';
select cName from college where enrollment>10001;
select cName from college where not state='CA';
delete from College where cName = 'Standford';
select * from College;