Top 50 SQL interview Questions and Answers
In this Post We Will Explain About is Top 50 SQL interview Questions and Answers With Example and Demo.Welcome on infinityknow.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?
[php]
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)
)
[/php]
Also read my POST Make table in sql server with auto increment primary key column
How to Insert value in students Table?
[php]
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’)
[/php]
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?
[php]
select * from students
[/php]
Now create a new table “students”
[php]
CREATE TABLE students
(
studentId int,
studRegDate date,
studentfees decimal(18,2)
)
[/php]
Insert values into this table-
[php]
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)
[/php]
Show table data
[php]
select * from students
[/php]
Now let’s apply Details some SQL simple queries on the table maked above:
1)Get all students details from the students table?
[php]
select * from students
[/php]
2)Get studId, studFname, studLname from students table?
[php]
select studId, studFname, studLname from students
[/php]
3)Get studFname from students table using alias name “students Name”
[php]
select studFname as studFname from students
[/php]
4)Get studFname from students table in upper case?
[php]
select UPPER(studFname) from students
[/php]
5)Get studFname from students table in lower case?
select LOWER(studFname) from students
6)Get unique studsec from students table?
[php]
select distinct studsec from students
[/php]
7)Select first 3 characters of studFname from students table.
[php]
select SUBSTRING(studFname,0,4) from students
[/php]
8)Get position of ‘k’ in name ‘jaydeepkumar’ from students table
[php]
select CHARINDEX (‘k’, studFname) from students where studFname=’jaydeepkumar’
[/php]
9)Get studFname from students table after removing white spaces from right side?
[php]
select RTRIM(studFname) from students
[/php]
10) Get studFname from students table after removing white spaces from right side?
[php]
select LTRIM(studFname) from students
[/php]
11) Get length of studFname from students table?
[php]
select LEN(studFname) from students
[/php]
12) Get studFname from students table after replacing ‘V’ with ‘VV’
[php]
select REPLACE(studFname,’V’,’VV’) from students
[/php]
13) Get studFname and studLname as single column from students table separated by
‘-‘ ?select studFname +’ – ‘+ studLname as FullName from students
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 ?
[php]
select YEAR(studJoinDate) AS [Year] , MONTH(studJoinDate) as [Month],
DAY(studJoinDate) as [Day] from students
[/php]
15) Get all student details from the students table order by studFname Ascending?
[php]
select * from students order by studFname asc
[/php]
16) Get all student details from the students table order by studFname descending?
[php]
select * from students order by studFname desc
[/php]
17) Get all student details from the students table order by studFname Ascending and studfees descending?
[php]
select * from students order by studFname asc, studfees desc
[/php]
18) Get student details from students table whose student name is “jaydeepKumar”?
[php]
select * from EMPLOYEE where studFname=’jaydeepkumar’
[/php]
19) Get student details from students table whose student name are “jaydeepKumar” and “vijay”?
[php]
select * from students where studFname in (‘jaydeepkumar’,’Mayur’)
[/php]
20) Get student details from students table whose student name are not “jaydeepKumar” and “vijay”?
[php]
select * from students where studFname not in (‘jaydeepkumar’,’Mayur’)
[/php]
21) Get student details from students table whose studFname starts with ‘V’
[php]
select * from students where studFname like ‘V%’
[/php]
22) Get student details from students table whose studFname contains ‘k’
[php]
select * from students where studFname like ‘%k%’
[/php]
23) Get student details from students table whose studFname ends with ‘s’
[php]
select * from students where studFname like ‘%s’
[/php]
24) Get student details from students table whose studfees is greater than 90000?
[php]
select studfees from students where studfees>90000
[/php]
25) Get student details from students table whose studfees is less than 90000?
[php]
select studfees from students where studfees<90000
[/php]
26) Get student details from students table whose studfees is between 50000 and 90000?
[php]
select studfees from students where studfees between 50000 and 90000
[/php]
27) Get student details from students table whose joining year is “2015”?
[php]
select * from students where YEAR(studJoinDate)=’2017′
[/php]
28) Get student details from students table whose joining month is “February”?
[php]
select * from students where DATENAME(month, studJoinDate)=’February’
[/php]
29) Get student details from students table who joined before 20th June, 2017?
[php]
select * from EMPLOYEE where studJoinDate > ‘2017-06-20’
[/php]
30) Get student details from students table who joined before 20th June, 2017?
[php]
select * from EMPLOYEE where studJoinDate < '2017-06-20'
[/php]
31) Get studFname, studJoinDate and studRegDate from students and students table?
[php]
select studFname, studJoinDate, Incentivedate from students EMP INNER JOIN incentives INC on EMP.studId = INC.studentId
[/php]
32) Get Last Name from students table after replacing special character (e.g. % (if any)) with white space?
[php]
select REPLACE(studLname,’%’,’ ‘) as studLname from students
[/php]
33) Get studsec, total salary with respect to a studsec from students table?
[php]
select studsec,SUM(SALARY) totalfees from students group by studsec
[/php]
34) Get studsec, totalfees with respect to a studsec from students table order by total salary descending?
[php]
select studsec, SUM(studfees) as totalfees from students group by studsec order by studfees desc
[/php]
35) Get studsec, no of students in a studsec, salary with respect to a studsec from students table order by salary descending?
[php]
select studsec,COUNT(studFname) as totalstudent, SUM(studfees) as studfees from students group by DEPARTMENT order by studfees desc
[/php]
36) Get studsec wise average salary from student table order by salary ascending?
[php]
select studsec,AVG(studfees) as AvgSalary from students group by studsec order by AvgSalary asc
[/php]
37) Get studsec wise maximum salary from students table order by salary ascending?
[php]
select studsec, MAX(studfees) as MaxSalary from students group by studsec order by MaxSalary asc
[/php]
38) Get studsec wise minimum salary from students table order by salary ascending?
[php]
select studsec,MIN(SALARY) MinSalary from students group by studsec order by MinSalary asc
[/php]
39) Select number of students joined with respect to year and month from students table?
[php]
select DATEPART (YYYY,studJoinDate) JoiningYear,DATEPART (MM,studJoinDate) JoiningMonth,COUNT(*) totalstudent from
students group by DATEPART(YYYY,studJoinDate), DATEPART(MM,studJoinDate)
[/php]
40) Select studsec, salary with respect to a studsec from students table where salary greater than 60000 order by studfees descending
[php]
select studsec,SUM(SALARY) totalfees from students group by studsec having SUM(SALARY) > 60000 order by totalfees desc
[/php]
41) Select studFname, studactivity amount from students and incentives table for those students who have incentives?
[php]
select studFname,studentfees from students EMP INNER JOIN students INC on EMP.studId = INC.studentId
[/php]
42) Select user First Name, studactivity amount from students and incentives table for those students who have incentives and studactivity amount greater than 5000
[php]
select studFname,studentfees from students EMP INNER JOIN students INC on EMP.studId = INC.studentId and studentfees > 5000
[/php]
43) Select user First Name, studactivity amount from students and students table for all students even if they didn’t get incentives?
[php]
Select studFname,studentfees from students EMP LEFT JOIN students INC on EMP.studId = INC.studentId
[/php]
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?
[php]
Select studFname, ISNULL (studentfees,0) AS studentfees from students EMP LEFT JOIN students INC on EMP.studId = INC.studentId
[/php]
45) Select user First Name, studactivity amount from students and incentives table for all students who got incentives using right join?
[php]
Select studFname, ISNULL (studentfees,0) AS studentfees from students EMP RIGHT JOIN students INC on EMP.EMPLOYEEID =INC.studentId
[/php]
46) Select max studactivity with respect to student from students and incentives table using sub query?
[php]
select studsec,(select ISNULL(MAX(studentfees),0) from INCENTIVES where studentId = studId)
MaxIncentive from students
[/php]
47) Select TOP 2 salary from students table?
[php]
select top 2 * from students order by salary desc
[/php]
48) Select second, third, fourth or nth highest/maximum salary of student from students table?
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?
[php]
select studFname from students
union
select studLname from students
[/php]
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.
[php]
UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
UNION ALL does not remove duplicates and it therefore faster than UNION.
[/php]
Note: While using this commands all selected columns need to be of the same data type.
51) Select students details from student table if data exists in studactivity table?
[php]
select * from students where exists (select * from students)
[/php]
52) Get students Id’s of those students who didn’t receive incentives without using sub query?
[php]
select studId from students
MINUS
select studentId from students
[/php]
53) Select 20 % of salary from jaydeepkumar, 10% of studfees for Krunal and for other 15 % of salary from student table?
[php]
SELECT studFname, CASE studFname WHEN ‘jaydeepkumar’ THEN studfees * .20 WHEN ‘Krunal’ THEN studfees * .10 ELSE studfees * .15 END “SalaryAmount” FROM
students
[/php]
54) Select studentsec as ‘fact Dept’, studfacl as ‘studfacl Dept’ and studDetails as ‘studDetails Dept’ from student table?
[php]
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
[/php]
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?
[php]
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
[/php]
56) Update studentfees to 9000 in studactivity table where student user First Name is charmi in students table?
[php]
update students set studentfees = ‘9000’ where studentId =(select studId from students where studFname = ‘charmi’)
[/php]
Now lets understand how to add or drop foreign key constraint on table
57) Write syntax to create students table?
[php]
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
)
[/php]
58) Write syntax to set studId as primary key in students table?
[php]
ALTER TABLE students ADD CONSTRAINT EMPLOYEE_PK PRIMARY KEY(studId)
[/php]
59) Write SQL syntax to set 2 fields(studId ,studFname) as primary key in students table
[php]
ALTER TABLE students ADD CONSTRAINT EMPLOYEE_PK PRIMARY KEY(studId, studFname)
[/php]
60) Write syntax to drop primary key on students table?
[php]
ALTER TABLE students DROP CONSTRAINT EMPLOYEE_PK;
[/php]
61) Write SQL syntax to create studentId in students table as foreign key with respect to studId in student table?
[php]
ALTER TABLE students ADD CONSTRAINT INCENTIVES_FK FOREIGN KEY (studentId) REFERENCES students(studId)
[/php]
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?
[php]
ALTER TABLE students DROP CONSTRAINT INCENTIVES_FK;
[/php]
63) Write syntax to delete student table?
[php]
DROP table students
[/php]
You are Most welcome in my youtube Channel Please shubscibe my channel. and give me feedBackMore Details……
Angularjs 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(infinityknow.com) readers.Your Valuable FeedBack,Any Question,or any Comments abaout This Article(infinityknow.com) Are Most Always Welcome.