Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
You are on page 1/ 10
create table College(
cName varchar2(10), state varchar2(10), enrollment int ); create table Student( sID int, sName varchar2(10), GPA number(2,1), sizeHS int, DoB date ); create table Apply( sID int, cName varchar2(10), major varchar2(20), decision char(1) );
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);
Select * from College;
College Table
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-May-96'); Insert into Student values(543,'Craig',3.4,2000,'27-Aug-98');
Select * from Student;
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 * from Apply;
College Table
1. List the student name, dob from student table.
Ans:- Select sName,DoB from Student;
2. List the name of student scoring more than 3.7 in GPA.
Ans- Select sName from Student where GPA>3.7;
3. List the name of student whose High School size is atleast 1000 and born after 1996. [Hint: check DoB greater than 31st December, 1996] Ans- Select sName from Student where sizeHS>=1000 and DoB>'31-Dec-1996';
4. List the name of student who are scoring GPA in between 2.9 and 3.9 Ans- Select sName from Student where GPA between 2.9 and 3.9;
5. List all the details of colleges who situated in MA.
Ans- Select * from College where state='MA'; 6. List the students who are scored more than 2.0 but less than 3.5. Ans- Select * from Student where gpa > 2.0 and gpa < 3.5;
7. List the students who have born after 1st Jul 96 in the order of the Date of
Birth.
Select * from Student
where DoB>'01-Jul-1996' order by DoB;
8. List the sID, cName, decision of applications that are accepted.
Select sID,cName from Apply
where decision='Y';
9. List the sID, cName of applications which are filled at Stanford.
Select sID,cName from Apply
where cName='Stanford';
10. List the colleges that that has enrollment greater than 10001.
Select * from College
where enrollment>10001;
11. List the colleges not in California.
Select cName from College
where state!='CA';
12. List names of all student who came from high school having size greater
than 1700 and scored GPA less than 3.8.
Select * from Student
where sizeHS>1700 and GPA<3.8;
13. Display the description of the Student table.
desc Student;
14. Display the details of all students.
select * from Student;
15. Display unique majors.
Select distinct major from Apply;
16. List the student names those are having three characters in their Names. Select sName from Student where sName like '___';
17. List the student names those are starting with ‘H’ and with five characters. Select sName from Student where sName like 'H____';
18. List the student names those are having third character and fifth char. must be ‘e’. Select sName from Student where sName like '__e_e%';
19. List the student names ending with ‘y’.
Select sName from Student where sName like '%y';
20. List the Students in the order of their GPA.
Select * from Student order by GPA;
21. List the details of the students in order of the ascending of GPA and descending of DoB. Select * from Student order by GPA , DoB desc;
22. List the sIDs of student who apply in either ‘Stanford’, ‘Cornell’ or ‘MIT’ College. Select sID from Apply where cName='Stanford' or cName='Cornell' or cName='MIT'; 23. Delete all applications filled at Stanford (Choose table wisely) Delete from Apply where cName='Stanford';
24. Delete the college Stanford from college table.
Delete from College where cName='Stanford';
25. Modify the GPA of all students by giving 10% raise in their GPA. Update Student Set GPA=GPA+0.10*GPA;
26. Increment the GPA of the students by 1.5 whose GPA is less than 3.5 and belong to High School having size greater than 1500. Update Student Set GPA=GPA+1.5 where GPA<3.5 and sizeHS>1500;
27. Delete the students who have scored less than 3.2 GPA. Delete from Student where GPA<3.2;
Assignment 2
create table College(
cName varchar2(10), state varchar2(10), enrollment int ); create table Student( sID int, sName varchar2(10), GPA number(2,1), sizeHS int ); create table Apply( sID int, cName varchar2(10), major varchar2(20) );
(i) Add cName as Primary key in College.
Alter table College add primary key(cName);
(ii) Add sID as Primarykey in Student.
Alter table Student add primary key(sID); (iii) Add sID, cName, major as Primarykey in Apply. Alter table Apply add primary key(sID,cName,major);
(iv) Make sID in Apply foreign key referring table student and cName referring table college. Alter table Apply add constraint fs foreign key(sID) references Student (sID) add constraint fc foreign key(cName) references College(cName);
(v) Increase data type size of major from 20 to 25.
Alter table Apply modify (major varchar2(25)); (vi) Add a new column decision in the Apply table keeping a constraint of not null for this column with data type varchar2(3). Alter table Apply add decision varchar2(3) not null;
(vii) Change data type of decision in Apply to char(1).
Alter table Apply modify decision char(1);
(viii) Drop foreign key on column name cName from Apply
table. Alter table Apply drop constraint fc;
(ix) Remove column sizeHS from Student table.
Alter table Student drop column sizeHS;
(x) Drop primary key from College
Alter table College drop primary key; (xi) Make cName, major unique pairwise such as Stanford CS, Stanford EE. ALTER TABLE Apply ADD CONSTRAINT UA UNIQUE (cName,major);
(xii) Add cName as Foreign Key in Apply table referring table
College using on delete cascade.
(xiii) Modify foreign key on sID in Apply table to foreign key on
delete set null.
(xiv) Rename column enrollment to enroll in College Table.
ALTER TABLE College RENAME COLUMN enrollment to enroll; desc College;
