Tags » SQL Query

Interview Question of the Week #013 - Stored Procedure and Its Advantages - How to Create Stored Procedure

There are some questions which are so old that they should not be asked in the interview and gets old. Here is one of the questions I have spotted so many times in the interview that if co-interviewer asks to the candidate, I often feel bored (well, I was caught yawning last time). 211 more words

SQL Authority

SQL SERVER - System procedures to know SQL Server Version

There are several ways to know the version of SQL Server. But did you know that there are two system procedures through which you can know the version? 102 more words

SQL Authority

SQL SERVER – Find the Growth Size for All files in All Databases

I am a big time advocate of some common best practices that I see in the industry. These have been ingrained into every session that I do be it SQL Server, MySQL or any other databases. 379 more words

SQL Authority

SQL SERVER - Mistake to Avoid: Duplicate and Overlapping Indexes - Notes from the Field #073

: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about indexes and its impact. We often believe that indexes will improve the performance of the query, but it is not true always. 1,028 more words

SQL Authority

t-SQL to find top X toppers in each subject from marks table

rank() function in SQL can be used to find such results in SQL!
Here is the simple example for FUN :)

use tempdb

create table studentMarks(studentName varchar(100), examSubject varchar(100), marks int)

insert studentMarks values ('Mike', 'English', 85)
insert studentMarks values ('Mike', 'History', 54)
insert studentMarks values ('Mike', 'Physics', 65)
insert studentMarks values ('Mike', 'Maths', 65)
insert studentMarks values ('Mike', 'Biology', 98)
insert studentMarks values ('Mike', 'Chemistry', 65)
insert studentMarks values ('Mike', 'Lab', 75)
insert studentMarks values ('Raj', 'English', 86)
insert studentMarks values ('Raj', 'History', 57)
insert studentMarks values ('Raj', 'Physics', 76)
insert studentMarks values ('Raj', 'Maths', 99)
insert studentMarks values ('Raj', 'Biology', 56)
insert studentMarks values ('Raj', 'Chemistry', 89)
insert studentMarks values ('Raj', 'Lab', 44)
insert studentMarks values ('Siri', 'English', 65)
insert studentMarks values ('Siri', 'History', 88)
insert studentMarks values ('Siri', 'Physics', 45)
insert studentMarks values ('Siri', 'Maths', 98)
insert studentMarks values ('Siri', 'Biology', 54)
insert studentMarks values ('Siri', 'Chemistry', 78)
insert studentMarks values ('Siri', 'Lab', 90)
insert studentMarks values ('Cortana', 'English', 92)
insert studentMarks values ('Cortana', 'History', 84)
insert studentMarks values ('Cortana', 'Physics', 76)
insert studentMarks values ('Cortana', 'Maths', 99)
insert studentMarks values ('Cortana', 'Biology', 65)
insert studentMarks values ('Cortana', 'Chemistry', 88)
insert studentMarks values ('Cortana', 'Lab', 100)
insert studentMarks values ('Vijred', 'English', 91)
insert studentMarks values ('Vijred', 'History', 57)
insert studentMarks values ('Vijred', 'Physics', 96)
insert studentMarks values ('Vijred', 'Maths', 98)
insert studentMarks values ('Vijred', 'Biology', 62)
insert studentMarks values ('Vijred', 'Chemistry', 71)
insert studentMarks values ('Vijred', 'Lab', 86)

-- SPOT the different between using ROW_NUMBER() and RANK() function
;WITH CTE AS 
(select studentName, examSubject, marks 
,ROW_NUMBER() OVER (PARTITION BY examSubject order by Marks desc) as myrank
from studentMarks)
select examSubject, studentName, marks from CTE where myrank <=2
order by examSubject, marks desc

;WITH CTE AS 
(select studentName, examSubject, marks 
,rank() OVER (PARTITION BY examSubject order by Marks desc) as myrank
from studentMarks)
select examSubject, studentName, marks from CTE where myrank <=2
order by examSubject, marks desc

drop table studentMarks
… 11 more words
SQL

SQL SERVER - Knowing Nested Transactions Behavior with SQL Server

SQL Server is an interesting database system that baffles me every single day that I get to learn something new. This blog has been my way to share these learnings. 499 more words

SQL Authority

SQL SERVER - Where is ERRORLOG? Various Ways to Find its Location

Whenever someone reports some weird error on my blog comments or sends email to know about it, I always ask to share SQL Server ERRORLOG file. 411 more words

SQL Authority