QUESTION 2

Question 2
STUDENT(rollno,name,class,birthdate)
COURSE(courseno, coursename, max_marks, pass_marks)
SC(rollno,courseno,marks)

CREATE TABLE

Student:

create table student

(

      rollno number(10) primary key,

      name varchar2(10) NOT NULL,

      class varchar2(10) NOT NULL,

      bod date   

);

Course:

create table course

(

      courseno number(10) primary key,

      coursename varchar(10) NOT NULL,

      max_marks number(4) NOT NULL,

      pass_marks number(4)  NOT NULL

);

Sc:

create table sc

(

          rollno number(10) references student(rollno),

          courseno number(10) references course(courseno),

          marks varchar(10) NOT NULL

);

INSERT RECORDS

-- INSERT INTO STUDENT

insert into student values(0001,'pankaj','fymca','27-jan-1989');

insert into student values(0002,'rahul','fymca','15-aug-1988');

insert into student values(0003,'priyesh','tymca','30-apr-1990');

insert into student values(0004,'ravinder','fymca','27-jan-1989');

insert into student values(0005,'dheru','symca','15-feb-1987');

insert into student values(0006,'amit','fymca','19-jun-1989');

insert into student values(0007,'deepak','symca','25-dec-1989');

-- INSERT INTO COURSE

insert into course values(01,'dm',100,50);

insert into course values(02,'fop',100,50);

insert into course values(03,'dbms',100,50);

insert into course values(04,'erpfm',100,50);

insert into course values(05,'faco',100,50);

-- INSERT INTO SC

insert into sc values(0007,04,75);

insert into sc values(0004,03,45);

insert into sc values(0005,03,70);

insert into sc values(0004,02,90);

insert into sc values(0001,01,75);

insert into sc values(0004,05,80);

insert into sc values(0006,04,55);

insert into sc values(0001,03,68);

insert into sc values(0004,01,36);

insert into sc values(0003,03,59);

insert into sc values(0001,05,48);

insert into sc values(0002,04,99);

1. Add constraint that marks entered are between 0 to 100 only.
   
   alter table sc add constraint abc check(marks between 0 and 100);
   
2. While creating COURSE table, primary key constraint was forgotten. Add the primary key now.
   
   alter table course add primary key(courseno);
   
3. Display details of student where course is ‘Data Base Management System’.
   
   
   select sc.rollno,name from sc,course,student
   where course.coursename='dbms' and sc.courseno=course.courseno and sc.rollno=student.rollno;
   
4. Select student names who have scored more than 70% in Computer Networks and have not failed in any subject.
   
   select sc.rollno, coursename, student.name from course,sc, student where marks>70 and coursename='erpfm' and sc.rollno in( select rollno from sc where rollno not in(select rollno from sc, course where marks<course.pass_marks )) and student.rollno=sc.rollno;
   
   
5. Select names and class of students whose names begin with ‘A’ or ‘B’.
   
   select name,class from student where name LIKE 'r%' OR name LIKE 'p%';
   
6. Display average marks obtained by each student.
   
   select a.rollno, avg(marks) from sc a, student b where a.rollno=b.rollno group by a.rollno;
   
7. Select all course where passing marks are more than 30% of average maximum marks.
   
   select coursename from course where pass_marks>(select avg(max_marks)*30/10 0 from course);
   
8. Select the course where second and third characters are ‘AT’.
   select * from course where coursename lIKE '_bm%';
   
9. Display details of students born in 1975 or 1976.
   
   select * from student where to_char(bod,'yyyy') BETWEEN 1988 and 1990;