All Queries

create database UMS
use ums
 

create table Students
(
 id int identity(1,1),
 RegistrationNum varchar(20),
 Name varchar(50),
 Gender bit,
 Course varchar(50),
 Percentage float
)

    insert into Students(RegistrationNum,Name,Gender,Course,Percentage)
    values('101','Naveen',1,'MCA',60.30)
    insert into Students(RegistrationNum,Name,Gender,Course,Percentage)
    values('102','Uvesh',1,'MCA',65.30)
    insert into Students(RegistrationNum,Name,Gender,Course,Percentage)
    values('103','Wiki',1,'MCA',90.30)
    insert into Students(RegistrationNum,Name,Gender,Course,Percentage)
    values('104','Abhinav',1,'MCA',85.30)
    insert into Students(RegistrationNum,Name,Gender,Course,Percentage)
    values('105','Karamjeet',1,'MCA',70.30)
    insert into Students(RegistrationNum,Name,Gender,Course,Percentage)
    values('106','Victor',1,'MCA',75.30)
    insert into Students(RegistrationNum,Name,Gender,Course,Percentage)
    values('107','Vikas',1,'MCA',95.30)
    insert into Students(RegistrationNum,Name,Gender,Course,Percentage)
    values('108','Vishu',1,'MCA',68.30)

        select * from Students where RegistrationNum='105'

        delete from Students where id=6

        set identity_insert Students on 
        go
        insert into Students(id,RegistrationNum,Name,Gender,Course,Percentage)
        values(6,'106','Victor',1,'MCA',75.30)
        set identity_insert Students off



    DBCC checkident ('Students',reseed,100)

    insert into Students(RegistrationNum,Name,Gender,Course,Percentage)
    values('109','Simran',0,'MCA',68.30)
    insert into Students(RegistrationNum,Name,Gender,Course,Percentage)
    values('110','shiva',1,'BCA',59.30)
    insert into Students(RegistrationNum,Name,Gender,Course,Percentage)
    values('111','meenu',1,'BCA',30.30)
    insert into Students(RegistrationNum,Name,Gender,Course,Percentage)
    values('112','Anil',0,'BCA',25.30)

    create table MasterCourse
    (
    Id int identity(1,1),
    Name varchar(20)
    )

    insert into MasterCourse(Name)values('MCA')
    insert into MasterCourse(Name)values('BCA')

    select * from Students
    select * from MasterCourse

    select st.id,st.Name, 
    Case  st.Gender when 1 then 'M' else 'F' end StuGender,mc.Name StuCourse from Students st 
    left outer join MasterCourse mc on mc.Id=Course

select Course,COUNT(Course) from Students group by Course

select  mc.Name,COUNT(st.Course) from Students st 
left outer join MasterCourse mc on mc.Id=Course
group by mc.Name
        ---------OR------------
select t.Countt,mc.Name from (
	select Course,COUNT(Course) Countt from Students group by Course)
		t  left outer join MasterCourse mc on t.Course=mc.id


select ROW_NUMBER() over(partition by Course order by Percentage desc) Row,* from Students

select * from (select ROW_NUMBER() over(partition by Course order by Percentage desc) Row,* from Students) t  where row=1

 UPDATE st  
       SET st.Course = mc.id 
       FROM Students st   
       join  MasterCourse mc ON mc.Name = st.Course

create view tblStudents
as 
select st.id,st.Name, 
Case  st.Gender when 1 then 'M' else 'F' end StuGender,mc.Name StuCourse from Students st 
left outer join MasterCourse mc on mc.Id=Course

select * from tblStudents

create view tblStudents
as 
select st.id,st.Name, 
Case  st.Gender when 1 then 'M' else 'F' end StuGender,mc.Name StuCourse from Students st 
left outer join MasterCourse mc on mc.Id=Course

select * from tblStudents