SQL Server Database Queries Part 1

Select Statement:

select * from Subjects;

Aliasing Column Name:
you can rename column according to your need using AS keyword.
select subjectname as subject from subjects;


Eliminating duplicate records:(Distinct Clause)
you can eliminate duplicate records using DISTINCT Keyword from resultset.
Select distinct subjectname from subjects;

Filtering Data(Where Clause):

you can filter data using WHERE clause with some condition,
Select subject;name from subjects where subjectid=3;
Select subject;name from subjects where subjectid>3;



The 'Not' Clause:

you can use NOT keyword condition
Select subjectname from subjects where subjectname IS NOT NULL;
SELECT SUBJECTNAME FROM SUBJECTS WHERE SUBJECTID NOT IN (2,3);

Filtering String:
you can filter string using LIKE keyword and %.
'H%' will return all the records start with 'H'.
'%H' will return all the records end with 'H'.
'%H%'  will return all the records start and end with 'H'.

Select subjectname from subject where name like('H%')  ;
  
The In and Between Keywords:

you can use IN clause when you have multiple ids you can get all records which are IN();
select * from subjects where subjectid in (2,3);

you can user BETWEEN clause when you want to get records between some range.

select * from subjects where subjectid BETWEEN 2 and 3;

Sorting Data(Order By Clause):
you can sort resultset using ORDER BY clause.
select * from subjects ORDER BY subjectname;

Limiting Records(Top Clause):

you can limit records by using TOP clause in your query.
select TOP 2 * from subjects ORDERBY subjectname;

OFFSET FETCH:

you can use OFFSET to keep and skip records. It is useful in paging concept.
SELECT * FROM SUBJECTS OFFSET 10 ROWS FETCH NEXT 25 ROWS ONLY;

AGGREGATING DATA; GROUP BY AND HAVING CLAUSE
you can use GROUP BY clause to get record set in Group.
SELECT COUNT(*) FROM SUBJECTS GROUP BY SUBJECTNAME;

Select from multiple tables (Using Joins)

Joins are very useful when you want to get data from multiple tables.
select * from subjects , books;

select a.subjectname,b.bookname from subjects a, books b where a.SubjectID=b.Subject;

There are different type of joins in sql.

Cross Join:

It will return Carthesian Product. 

select * from books CROSS JOIN subjects;

Inner Join:

It is the most useful join. It is optional to use Inner Keyword. It is used in place of where and is a
proper way of join. it will return all the matching rows based on conditon.

select * from subjects INNER JOIN BOOKS ON SUBJECTS.SUBJECTID=BOOKS.SUBJECT;

Outer Join:

Outer joins are used when we want to matching and unmatching data from tables. it is of two types.

Left Outer Join:

It returns all matching of left and all of right records.


SELECT * FROM SUBJECTS LEFT OUTER JOIN BOOKS ON SUBJECTS.SUBJECTID=BOOKS.SUBJECT;

Right Outer Join:

It return all record of left and matching of right.

SELECT * FROM SUBJECTS Right OUTER JOIN BOOKS ON SUBJECTS.SUBJECTID=BOOKS.SUBJECT; 

Ranking Functions:
 
 There are four ranking functions in sql server.

1. ROW_NUMBER
2. RANK
3. DENSE_RANK
4. NTILE

ROW_NUMBER simply select the no of current row.
RANK and DENSE_RANK assisgn unique number to each row.
RANK return total no of rows of same rank plus 1.
DENSE_RANK return rank plus 1.