Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Japanese language
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); */ 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 (5021,'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;