Most Important SQL Server Interview Questions and Answers

Most Important SQL Server Interview Questions and Answers

In this Post We Will Explain About is Most Important SQL Server Interview Questions and Answers With Example and Demo.Welcome on Live24u.com – Examples, The best For Learn web development Tutorials,Demo with Example! Hi Dear Friends here u can know to sql server interview questions for 3 years experience Example

In this post we will show you Best way to implement sql server interview questions for freshers, hear for sql server interview questions and answers for experienced with examples with Download .we will give you demo,Source Code and examples for implement Step By Step Good Luck!.




sql server interview questions

user First of all: How to create table for students?

create table students
(
            studId    int Primary key identity(1,1),
            studFname      varchar(50),
            studLname       varchar(50),
            studfees             decimal(18,2),
            studJoinDate    date,
            studsec   varchar(50)
)

Also read my POST Make table in sql server with auto increment primary key column

How to Insert value in students Table?

 Insert Into students values ('jaydeepkumar', 'Verma', 40000, '2018-05-21', 'IT')
 Insert Into students values ('Krunal', 'shah', 800000, '2017-10-31', 'studfacl')
 Insert Into students values ('charmi', 'Sonkar', 700000, '2017-12-09', 'studentsec')
 Insert Into students values ('Abhishek', 'Gondaliya', 44000, '2017-02-19', 'studfacl')
 Insert Into students values ('Durgesh', 'Tiwari', 33000, '2017-12-07', 'studfacl')
 Insert Into students values ('Ravi', 'shah', 55000, '2018-03-31', 'studDetails')
 Insert Into students values ('Mayur', 'Raghuvanshi', 88000, '2018-09-26', 'studDetails')
 Insert Into students values ('Sandeep', 'shah', 90000, '2017-02-01', 'studfacl')

We have some other options to insert data in table. Please read the Post Multiple ways to insert records in sql table.

How to show table data?

select *  from students

Now create a new table “students”

CREATE TABLE students
(
            studentId          int,
            studRegDate            date,
            studentfees      decimal(18,2)
)

Insert values into this table-

insert into students values(1, '2017-09-21', 10000)
insert into students values(2, '2018-12-25', 8000)
insert into students values(3, '2017-05-30', 6000)
insert into students values(1, '2018-09-12', 3000)
insert into students values(2, '2018-02-25', 11000)

Show table data
select *  from students

Now let’s apply Details some SQL simple queries on the table maked above:

1)Get all students details from the students table?

select * from students

2)Get studId, studFname, studLname from students table?

select studId, studFname, studLname from students

3)Get studFname from students table using alias name “students Name”

select studFname as studFname  from students

4)Get studFname from students table in upper case?

select UPPER(studFname) from students

5)Get studFname from students table in lower case?
select LOWER(studFname) from students

6)Get unique studsec from students table?

select distinct studsec from students

7)Select first 3 characters of studFname from students table.

select SUBSTRING(studFname,0,4) from students

8)Get position of ‘k’ in name ‘jaydeepkumar’ from students table

select CHARINDEX ('k', studFname) from students where studFname='jaydeepkumar'

9)Get studFname from students table after removing white spaces from right side?

select RTRIM(studFname) from students

10) Get studFname from students table after removing white spaces from right side?

select LTRIM(studFname) from students

11) Get length of studFname from students table?

select LEN(studFname) from students

12) Get studFname from students table after replacing ‘V’ with ‘VV’

select REPLACE(studFname,'V','VV') from students

13) Get studFname and studLname as single column from students table separated by
‘-‘ ?select studFname +’ – ‘+ studLname as FullName from students

Also Read :   google adsense create a account setp by step

Also read my Post Concatenating first,middle and last name in sql server

14) Get studFname , Joining year, Joining Month and Joining Date from students table ?

select YEAR(studJoinDate) AS [Year] , MONTH(studJoinDate) as [Month],
DAY(studJoinDate) as [Day] from students

15) Get all student details from the students table order by studFname Ascending?

select * from students order by studFname asc 

16) Get all student details from the students table order by studFname descending?

select * from students order by studFname desc 

17) Get all student details from the students table order by studFname Ascending and studfees descending?

select * from students order by studFname asc, studfees desc 

18) Get student details from students table whose student name is “jaydeepKumar”?

select * from EMPLOYEE where studFname='jaydeepkumar'

19) Get student details from students table whose student name are “jaydeepKumar” and “vijay”?

select * from students where studFname in ('jaydeepkumar','Mayur')

20) Get student details from students table whose student name are not “jaydeepKumar” and “vijay”?

select * from students where studFname not in ('jaydeepkumar','Mayur')

21) Get student details from students table whose studFname starts with ‘V’

select * from students where studFname like 'V%'

22) Get student details from students table whose studFname contains ‘k’

select * from students where studFname like '%k%'

23) Get student details from students table whose studFname ends with ‘s’
select * from students where studFname like '%s'

24) Get student details from students table whose studfees is greater than 90000?
select studfees from students where studfees>90000

25) Get student details from students table whose studfees is less than 90000?
select studfees from students where studfees<90000

26) Get student details from students table whose studfees is between 50000 and 90000?

select studfees from students where studfees between 50000 and 90000

27) Get student details from students table whose joining year is “2015”?

select * from students where YEAR(studJoinDate)='2017'

28) Get student details from students table whose joining month is “February”?

select * from students where DATENAME(month, studJoinDate)='February'

29) Get student details from students table who joined before 20th June, 2017?

select * from EMPLOYEE where studJoinDate > '2017-06-20'

30) Get student details from students table who joined before 20th June, 2017?

select * from EMPLOYEE where studJoinDate < '2017-06-20'

31) Get studFname, studJoinDate and studRegDate from students and students table?

select studFname, studJoinDate, Incentivedate from students EMP INNER JOIN incentives INC on EMP.studId = INC.studentId

32) Get Last Name from students table after replacing special character (e.g. % (if any)) with white space?

select REPLACE(studLname,'%',' ') as studLname from students

33) Get studsec, total salary with respect to a studsec from students table?

select studsec,SUM(SALARY) totalfees from students group by studsec

34) Get studsec, totalfees with respect to a studsec from students table order by total salary descending?

select studsec, SUM(studfees) as totalfees from students group by studsec order by studfees desc

35) Get studsec, no of students in a studsec, salary with respect to a studsec from students table order by salary descending?

select studsec,COUNT(studFname) as totalstudent, SUM(studfees) as studfees from students group by DEPARTMENT order by studfees desc

36) Get studsec wise average salary from student table order by salary ascending?

select studsec,AVG(studfees) as AvgSalary from students group by studsec order by AvgSalary asc

37) Get studsec wise maximum salary from students table order by salary ascending?

select studsec, MAX(studfees) as MaxSalary from students group by studsec order by MaxSalary asc

38) Get studsec wise minimum salary from students table order by salary ascending?

select studsec,MIN(SALARY) MinSalary from students group by studsec order by MinSalary asc

39) Select number of students joined with respect to year and month from students table?

select DATEPART (YYYY,studJoinDate) JoiningYear,DATEPART (MM,studJoinDate) JoiningMonth,COUNT(*) totalstudent from
students group by DATEPART(YYYY,studJoinDate), DATEPART(MM,studJoinDate)

40) Select studsec, salary with respect to a studsec from students table where salary greater than 60000 order by studfees descending

select studsec,SUM(SALARY) totalfees from students group by studsec having SUM(SALARY) > 60000 order by totalfees desc

41) Select studFname, studactivity amount from students and incentives table for those students who have incentives?

select studFname,studentfees from students EMP INNER JOIN students INC on EMP.studId = INC.studentId

42) Select user First Name, studactivity amount from students and incentives table for those students who have incentives and studactivity amount greater than 5000

select studFname,studentfees from students EMP INNER JOIN students INC on EMP.studId = INC.studentId and studentfees > 5000

43) Select user First Name, studactivity amount from students and students table for all students even if they didn’t get incentives?

Select studFname,studentfees from students EMP LEFT JOIN students INC on EMP.studId = INC.studentId

44) Select user First Name, studactivity amount from students and students table for all students even if they didn’t get incentives and set studactivity amount as 0 for those students who didn’t get students?

Select studFname, ISNULL (studentfees,0) AS studentfees from students EMP LEFT JOIN students INC on EMP.studId = INC.studentId

45) Select user First Name, studactivity amount from students and incentives table for all students who got incentives using right join?

Select studFname, ISNULL (studentfees,0) AS studentfees from students EMP RIGHT JOIN students INC on EMP.EMPLOYEEID =INC.studentId

46) Select max studactivity with respect to student from students and incentives table using sub query?

select studsec,(select ISNULL(MAX(studentfees),0) from INCENTIVES where studentId = studId)
MaxIncentive from students

47) Select TOP 2 salary from students table?

select top 2 * from students order by salary desc

48) Select second, third, fourth or nth highest/maximum salary of student from students table?

Also Read :   Top 10 AngularJS interview questions and answers

For this Read Post Query to get second, third,fourth or nth highest/maximum salary of student.

49) Select studFname, studLname from students table as separate rows?

select studFname from students
union
select studLname from students

50) What is the difference between UNION and UNION ALL?

Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.

  UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
  UNION ALL does not remove duplicates and it therefore faster than UNION.

Note: While using this commands all selected columns need to be of the same data type.

Also Read :   Top 10 Best Free PHP IDE for Website Developers

51) Select students details from student table if data exists in studactivity table?

select * from students where exists (select * from students)

52) Get students Id’s of those students who didn’t receive incentives without using sub query?

select studId from students
MINUS
select studentId from students

53) Select 20 % of salary from jaydeepkumar, 10% of studfees for Krunal and for other 15 % of salary from student table?

SELECT studFname, CASE studFname WHEN 'jaydeepkumar' THEN studfees * .20 WHEN 'Krunal' THEN studfees * .10 ELSE studfees * .15 END "SalaryAmount" FROM
students

54) Select studentsec as ‘fact Dept’, studfacl as ‘studfacl Dept’ and studDetails as ‘studDetails Dept’ from student table?

SELECT case studsec when 'IT' then 'Information technology' when 'studentsec' then 'fact Dept' when 'studfacl' then 'studfacl Dept' when
'studDetails' then 'studDetails Dept' END as studsec FROM students

To know more about SELECT CASE in Sql read Post: Examples to use case expression in select statement in sql server

55) Write a query to rank students based on their incentives for a month?

select studFname,studentfees,DENSE_RANK() OVER (PARTITION BY studRegDate ORDER BY studentfees DESC) AS [Rank] from students a,
students b where a.studId = b.studentId

56) Update studentfees to 9000 in studactivity table where student user First Name is charmi in students table?

update students set studentfees = '9000' where studentId =(select studId from students where studFname = 'charmi')

Now lets understand how to add or drop foreign key constraint on table

57) Write syntax to create students table?

CREATE TABLE students
(
studId int NOT NULL,
studFname varchar(50) NULL,
studLname varchar(50) NULL,
studfees decimal(18, 0) NULL,
studJoinDate datetime2(7) default getdate(),
studsec varchar(50) NULL
)

58) Write syntax to set studId as primary key in students table?

ALTER TABLE students ADD CONSTRAINT EMPLOYEE_PK PRIMARY KEY(studId)

59) Write SQL syntax to set 2 fields(studId ,studFname) as primary key in students table

ALTER TABLE students  ADD CONSTRAINT EMPLOYEE_PK PRIMARY KEY(studId, studFname)

60) Write syntax to drop primary key on students table?

ALTER TABLE students  DROP CONSTRAINT EMPLOYEE_PK;

61) Write SQL syntax to create studentId in students table as foreign key with respect to studId in student table?

ALTER TABLE students ADD CONSTRAINT INCENTIVES_FK FOREIGN KEY (studentId) REFERENCES students(studId)

To know more about Primary and Foreign key read Post Difference between primary key and foreign key in sql server

62) Write SQL syntax to drop foreign key on student table?

ALTER TABLE students DROP CONSTRAINT INCENTIVES_FK;

63) Write syntax to delete student table?

DROP table students 

You are Most welcome in my youtube Channel Please shubscibe my channel. and give me feedBackMore Details……
Angularjs Example




Example

I hope you have Got What is sql server interview questions and answers for experienced with examples And how it works.I would Like to have FeaeBack From My Blog(live24u.com) readers.Your Valuable FeedBack,Any Question,or any Comments abaout This Article(live24u.com) Are Most Always Welcome.