Untitled

mail@pastecode.io avatar
unknown
plain_text
3 years ago
1.0 kB
6
Indexable
Never
2019 DBMS question no 3 .

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));

select count(Sid) from Work_dept where Aid='A18'and Deptid='D10' having tothrs>40;
select Deptid from Works_dept where count(Sid)>5 and tothrs>20 union all select from Works_assign where Aid='A18';
Update Department set Dmgr ='manager 6' where Deptid=5;
Alter table Student add DOB varchar[25];