20181CSE0041, Sql Server

20181CSE0041

create table STUDENT (USN int primary key, SName varchar(20), Address varchar(30), Phone int, Gender varchar(10));
create table SEMSEC (SSID int primary key, Sem int, Sec varchar(5)) ;
create table CLASS (USN int , SSID int,FOREIGN KEY(USN) REFERENCES STUDENT(USN) ON DELETE CASCADE,FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID) ON DELETE CASCADE);
create table SUBJECT (Subcode varchar(10) primary key, Title varchar(30), Sem int, Credits int, SSID int,FOREIGN KEY(SSID) REFERENCES SEMSEC(SSID) ON DELETE CASCADE);
insert into STUDENT values(100,'akshy','yelhanka new town',1234567890,'M');
insert into STUDENT values(101,'prasanth','yelhanka old town',1233347890,'M');
insert into STUDENT values(102,'arya','hebbla',1255567890,'F');
insert into STUDENT values(103,'laxmi','dairy cross',1234566690,'F');
insert into STUDENT values(104,'sunil','kormangala',1234569990,'M');
select * from STUDENT;
insert into SEMSEC values(11,4,'A');
insert into SEMSEC values(12,3,'B');
insert into SEMSEC values(13,4,'C');
insert into SEMSEC values(14,6,'C');
insert into SEMSEC values(15,6,'B');
select * from SEMSEC;
insert into CLASS values(100,13);
insert into CLASS values(101,13);
insert into CLASS values(102,11);
insert into CLASS values(103,14);
insert into CLASS values(104,12);
select * from CLASS;
insert into SUBJECT values('cse200','DBMS',4,3,13);
insert into SUBJECT values('cse201','DBMS',4,3,13);
insert into SUBJECT values('cse202','COA',5,4,11);
insert into SUBJECT values('cse203','EM4',6,3,12);
insert into SUBJECT values('cse204','MP',6,3,14);
select * from SUBJECT;
select s.* from STUDENT s, SEMSEC e, CLASS c where s.USN=c.USN and e.SSID=c.SSID and e.Sem=4 and e.Sec='C';
select count(*) as "Total number of students",e.Sem from STUDENT s, SEMSEC e, CLASS c where s.USN=c.USN and e.SSID=c.SSID group by e.Sem order by e.Sem;
select t.*,s.SName from STUDENT s join SUBJECT t on t.sem=4;
select SSID from SUBJECT where Title<>'DBMS';
select s.* from STUDENT s, SEMSEC e, CLASS c where e.SSID=c.SSID and s.USN=c.USN and e.Sem=4 union select s.* from STUDENT s, SEMSEC e, CLASS c where e.SSID=c.SSID and s.USN=c.USN and e.Sem=6;


run \| edit \| history \| help	0