0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

CREATE TABLE #Students (
StudentId int,
FirstName varchar(20),
LastName Varchar(20),
Email Varchar(20),
Gender Char(1),
PNo int constraint mob_uq unique,
Address Varchar(30),
BranchId int,
constraint uq_Add unique(Address)
)

/*Alter table #Students ALTER column StudentId int not null;
Alter table #Students ADD  constraint Check_Gender check(Gender = 'M' or Gender = 'F');
Alter table #Students ADD  constraint Primary_sidkey primary key(StudentId);
*/

Create nonclustered index nonclust_Fname on #Students(FirstName); 

insert into #Students values (101,'Deeps','Gurr','[email protected]','F',232323,'Peets street',501);
insert into #Students values (102,'Anu','Mer','[email protected]','F',546789,'Hello street',501);
insert into #Students values (103,'Apps','Gurr','[email protected]','F',546343,'Velo street',502);
insert into #Students values (104,'Sur','Gur','[email protected]','F',762323,'Indiana street',501);
insert into #Students (StudentId,FirstName,Gender)
values(105, 'Chaithu','M') 
insert into #Students values (106,'Sur','Gurla','Surn@gur;a.com','M',8772323,'Henda street',503);
insert into #Students values (107,'Len','Gurla','Len@gur;a.com','M',8546893,'Volga street',503);


CREATE TABLE #Branch (
BranchId int not null,
BranchName Varchar(25),
HODId int
)

insert into #Branch values (501,'ECE',201);
insert into #Branch values (502,'CSE',204);
insert into #Branch values (503,'EEE',205);
insert into #Branch values (504,'EIE',203);

Alter table #Students ADD  constraint F_Bidkey Foreign key(BranchId) references #Branch(BranchId) on update cascade;




update #Students set Address = 'Hythnagar Apts' where StudentId = 105;

/* in Group by  having  clause is used to filter by groups and Where is used to filter by rows
for group by where can't be used*/

/*select  FirstName, LastName BranchI from #Students group by StudentId;
*/

select * from #Students full outer join #Branch
on #Students.BranchId = #Branch.BranchId;

select * from #Students right outer join #Branch
on #Students.BranchId = #Branch.BranchId;

select StudentId, FirstName + ' ' + isnull(LastName, ' ') as StudentName,  BranchName from #Students, #Branch
where #Students.BranchId = #Branch.BranchId;

select * from #Students where 1= ' 101';
select FirstName, LastName from #Students where StudentId > =
(select StudentId from #Students where  FirstName = 'Apps') and Gender =
(select Gender from #Students where  Address =  'Velo street');
/* sub queries can be done on different tables */

select FirstName,LastName, Address from #Students order by 3 Asc; 
/* orders by Address  & order by statment execute last
If they are two same address then you can order by FistName also*/
select * from #Students where Address is not null;
select * from #Students where FirstName like 'D__p%s';
select * from #Students where StudentId between 102 and 105;
select  * from #Students where   StudentId in (102,103,104) ;
select  * from #Students where  Not StudentId = 102 ;
select distinct FirstName from #Students;
select top 10 percent *  from #Students;

fork query

edited sep 19 20

created sep 18 20 anonymous