Untitled

2019 DBMS question no 3 .

a) Write DDL statements for creating the relation students, assignment, works_assign.
ANS=>create table Student(Sid int not null,Sname varchar[25], Saddr varchar[30],Sphone int[10],primary key(Sid));
    create table Assignment (Aid varchar[10] not null primary,Aname varchar[25], Adsec varchar[25],Amgr int [10]);
    create table Department (Deptid n int not null,Dname varchar[25],Daddr varchar[25],Dmgr varchar [18] not null,primary key (Deptid));
    create table Works_assign (Sid int not null,Aid int not null,tothrs varchar[10], foreign key(Sid) references Student(Sid),foreign key (Aid) references Assignment(Aid));
    create table Works_dept (Sid int not null, Deptid int not null,tothrs varchar[10], foreign key(Sid) references Student(Sid),foreign key (Deptid) references Department(Deptid));


a)Count the number of students who works in assignment with ASS# A18 & whose total hours worked in department with Dept D10 is more than 40. 
ANS=>select count(Sid) from Work_dept where Aid='A18'and Deptid='D10' having tothrs>40;

c)Retrive those Departments with at least five students who work more than 20 hrs on assignment with ASS# A18
ANS=>select Deptid from Works_dept where count(Sid)>5 and tothrs>20 union all select from Works_assign where Aid='A18';

d)Change managaer # of depatment 5 with manager no #6
ANS=>Replace into Department where Deptid=5 set Dmgr having (Deptid=6);

e)Add a field DOB in Student relation
Ans=>Alter table Student add DOB DATE;