1

I have prepared the database of the college given as

create table depts(
deptcode char(3) primary key,
deptname char(70) not null);

create table students(
rollno number(2) primary key,
name char(50),
bdate date check(bdate < TO_DATE('2004-01-01','YYYY-MM-DD')),
deptcode char(3) references depts(deptcode)
on delete cascade,
hostel number check(hostel<20),
parent_inc number(8,1));

create table faculty(
fac_code char(2) primary key,
fac_name char(50) not null,
fac_dept char(3) references depts(deptcode)
on delete cascade);


create table crs_offrd(
crs_code char(5) primary key,
crs_name char(35) not null,
crs_credits number(2,1),
crs_fac_cd char(2) references faculty(fac_code)
on delete cascade);

create table crs_regd(
crs_rollno number(2) references students(rollno) on delete cascade,
crs_cd char(5) references crs_offrd(crs_code)
on delete cascade,
marks number(5,2),
primary key(crs_rollno,crs_cd));

Now I want to give Grace Marks 5 in subject ‘DBMS’to the students who have scored less than 50 in that subject.

Accordingly I wrote the query as:

update r
set r.marks=r.marks+5
from crs_regd r
inner join crs_offrd o
on r.crs_cd=o.crs_code
where o.crs_name='DBMS' and r.marks<50;

but it shows error as SQL command not properly ended in line 3. I am using ORACLE SQL. What is the problem in this query?

2

The UPDATE syntax differs a lot from DBMS to DBMS, especially when there are joins. For Oracle this would work:

update crs_regd r
set marks = marks + 5
where marks < 50
  and exists
      ( select 1
        from crs_offrd o
        where r. crs_cd = o. crs_code
          and o. crs_name = 'DBMS'
      ) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.