How to deal with this problem?

[lliu26]

Course Data:

insert into Course values('C001','Programming Fundamentals','1','B1','N','20','10','10','60');
insert into Course values('C002','Data Structures','2','B1','N','20','10','10','60');
insert into Course values('C003','Basics of RDBMS','2','B1','N','20','10','10','60');
insert into Course values('C004','System Software','3','B1','N','20','10','10','60');
insert into Course values('C005','Computer Hardware','3','B2','N','20','10','10','60');
insert into Course values('C006','File Structures','4','B1','N','60','10','10','20');
insert into Course values('C007','Network Computing','4','B3','N','20','10','10','60');
insert into Course values('C008','Data Warehousing','5','B1','Y','20','10','10','60');
insert into Course values('C010','Analysis of Algorithms','5','B1','N','20','10','10','60');
insert into Course values('C009','Neural Networks','6','B2','Y','','10','10','80');


Student data:

insert into Student values('S001','A001','2','raj','tiger');
insert into Student values('S002','A003','3','sam','tiger');
insert into Student values('S003','A004','5','suraj','tiger');
insert into Student values('S004','A006','3','sandra','tiger');
insert into Student values('S005','A007','4','vivek','tiger');
insert into Student values('S006','A008','7','vikas','tiger');
insert into Student values('S007','A009','8','bipin','tiger');
insert into Student values('S008','A010','3','gopi','tiger');
insert into Student values('S009','A012','1','lilly','tiger');
insert into Student values('S010','A013','4','rose','tiger');
insert into Student values('S011','A014','7','megha','tiger');
insert into Student values('S012','A015','6','henry','tiger');
insert into Student values('S013','A016','2','jeol','tiger');


Registration Data:

insert into Registration values('S001','C001','5-Jun-08','20','5','5','50','A');
insert into Registration values('S001','C001','24-Aug-08','15','10','8','32','B');
insert into Registration values('S002','C001','15-Mar-08','20','9','9','55','A');
insert into Registration values('S003','C001','13-Aug-08','20','10','10','60','A');
insert into Registration values('S004','C001','8-Sep-08','15','10','10','50','A');
insert into Registration values('S005','C001','22-Nov-08','10','5','5','30','D');
insert into Registration values('S006','C001','6-Feb-08','45','5','5','15','B');
insert into Registration values('S004','C001','8-Sep-08','20','10','10','40','A');
insert into Registration values('S003','C001','29-Dec-08','18','8','10','46','A');
insert into Registration values('S007','C001','13-Sep-08','20','7','8','55','A');
insert into Registration values('S008','C001','16-Jan-08','35','5','5','20','B');
insert into Registration values('S006','C001','6-Feb-08','47','8','7','18','A');


Department Data:

insert into Department values('10','Information Science','I105');
insert into Department values('20','Computer Science','I102');
insert into Department values('30','Electronics','I104');
insert into Department values('40','Electrical','I107');
insert into Department values('50','IMechanical','I109');
insert into Department values('60','Civil','I106');


Instructor Data:

insert into Instructor values('I101','Bob Hockins','12-Jan-00','10');
insert into Instructor values('I102','Suguru Zikovich','21-Feb-01','20');
insert into Instructor values('I103','Ritivoi','13-Jan-03','10');
insert into Instructor values('I104','David Field','30-Mar-99','30');
insert into Instructor values('I105','Emillie Norton','4-Jun-98','30');
insert into Instructor values('I106','Ron Hardman','1-Jan-05','60');
insert into Instructor values('I107','Scott Urman','8-Apr-08','40');
insert into Instructor values('I108','Daisy Samson','9-Dec-04','50');
insert into Instructor values('I109','Ford Bravo','10-May-08','50');
insert into Instructor values('I110','Rebecca Brown','2-Jan-09','10');
insert into Instructor values('I111','Antario','23-Jun-04','10');
insert into Instructor values('I112','Samuel','25-Jan-02','60');
insert into Instructor values('I113','Justin','14-Apr-03','40');
insert into Instructor values('I114','Nissar','16-Mar-09','10');
insert into Instructor values('I115','Avinash Naren','27-May-07','20');
insert into Instructor values('I116','Micheal Rose','5-Jan-08','40');


CourseAllocation Data:

insert into CourseAllocation values('1001','C001','I101','1-Jun-08','4-Jun-08');
insert into CourseAllocation values('1002','C002','I102','7-Mar-08','14-Mar-08');
insert into CourseAllocation values('1003','C001','I103','20-Aug-08','23-Aug-08');
insert into CourseAllocation values('1004','C001','I101','1-Sep-08','4-Sep-08');
insert into CourseAllocation values('1005','C003','I104','10-Aug-08','12-Aug-08');
insert into CourseAllocation values('1006','C004','I103','4-Sep-08','7-Sep-08');
insert into CourseAllocation values('1007','C003','I104','26-Aug-08','28-Aug-08');
insert into CourseAllocation values('1008','C002','I107','21-Jul-08','27-Jul-08');
insert into CourseAllocation values('1009','C005','I105','12-Nov-08','21-Nov-08');
insert into CourseAllocation values('1010','C006','I106','1-Feb-08','5-Feb-08');
insert into CourseAllocation values('1011','C007','I108','7-Sep-08','12-Sep-08');
insert into CourseAllocation values('1012','C005','I109','12-Nov-08','21-Nov-08');
insert into CourseAllocation values('1013','C008','I110','15-Jan-08','15-Jan-08');


Attendance Data:

insert into Attendance values('S001','C001','4','3');
insert into Attendance values('S002','C001','4','4');
insert into Attendance values('S003','C002','8','5');
insert into Attendance values('S004','C003','3','3');
insert into Attendance values('S005','C004','4','2');
insert into Attendance values('S006','C005','10','9');
insert into Attendance values('S007','C006','5','5');
insert into Attendance values('S010','C004','4','4');
insert into Attendance values('S009','C003','3','1');
insert into Attendance values('S011','C007','6','5');
insert into Attendance values('S012','C008','1','1');


Hostel Data:

insert into Hostel values('Violet','101','S001','3000');
insert into Hostel values('Violet','201','S003','4000');
insert into Hostel values('Violet','301','S004','5000');
insert into Hostel values('Indigo','100','S002','3000');
insert into Hostel values('Indigo','200','S005','4000');
insert into Hostel values('Indigo','300','S006','5000');
insert into Hostel values('Blue','102','S007','3000');
insert into Hostel values('Blue','202','S008','4000');
insert into Hostel values('Blue','302','S009','5000');




Assignment 2.
1.select distinct city from applicant ;
2.select distinct semester from course ;
3.select distinct InstructorId , CourseId from CourseAllocation;
4.select CourseName from course where elective='N';
5.select InstructorName from Instructor where DateOfJoining <'01-jan-2002';
6.select CourseId from CourseAllocation where EndDate < CURRENT_TIMESTAMP ;
7.select ApplicantName from Applicant where City in('Mysore','Bangalore');
8.select ApplicantName from Applicant where City not in('Mysore');
9.select CourseName from Course where Semester in('1','2') AND BranchId='B1';
10.select CourseName from Course where elective='Y' AND ProjectMarks='20';
11.select CourseName from Course where ProjectMarks > SemesterExamMarks;
12.select * from Applicant where City in('Bangalore','Mysore','Hyderabad');
13.select * from Course where Semester in('1','2','3');
14.select StudentId from Registration where (ProjectMarks+AssignmentMarks+InternalMarks+SemesterMarks) between

'80' and '100';
15.select * from student order by ApplicantId;
16.select * from Instructor order by DateOfJoining;
17.select * from Instructor order by DepartmentId DESC,DateOfJoining;
18.select InstructorName from Instructor where InstructorName Like'R%' or InstructorName Like'%n';

?19.select * from Instructor where DateOfJoining Like'%March%';
19. List the details of instructors whose date of joining is in the month of March.

20.select * from Applicant where EmailId
20. List the details of applicant whose email id contains a character ‘_’
21. List the applicant who do not have email id in their applicant detail information.
22. List the course Id which has project Marks in its course detail information.


How to get the month of a Date type variable?
20th how to dail with?

[duqiangcise]

SCOTT@oamis>>select sysdate from dual;

SYSDATE
--------------
19-8月 -10

已用时间:  00: 00: 00.04
SCOTT@oamis>>select to_char(sysdate,'mm') from dual;

TO
--
08

已用时间:  00: 00: 00.01

[duqiangcise]

20th:

select * from Applicant where EmailId  like '%_%';